Dataframe: get_dummies for arrays in coll

user_4000955 0
Viewed 597

Working with Python/Pandas

I have a csv file pretty simple except for one column: the source is an array.

An example of my table:

Column A                  |Column B  |Column C  |Column D  |  
__________________________|__________|__________|__________|
[Water, Food, Groceries]  |  0       |true      |9         |
[Water, Desert, Sand]     |  1       |false     |1         |
[Earth, Groceries]        |  2       |null      |12        |
[Air, Food, Car]          |  3       |true      |8         |
[Cristal, Love, Groceries]|  4       |false     |0         |

What I want to accomplish:

Column B  |Column C  |Column D  |column_a_water |column_a_food  | column_a_groceries |
__________|__________|__________|_______________|_______________|____________________|
  0       |true      |9         |  1            |  1            |  1                 |
  1       |false     |1         |  1            |  0            |  0                 |
  2       |null      |12        |  0            |  0            |  1                 |
  3       |true      |8         |  0            |  1            |  0                 |
  4       |false     |0         |  0            |  0            |  1                 |

With pandas get_dummies I can make it work with Column C, but not with Column A. Using the same technique it does not work.

What can I do to deal with this situation?

pandas dataframe python
2 Answers

Use, Series.explode on Column A, then use Series.str.get_dummies on this exploded column then use DataFrame.sum on level=0 then using DataFrame.add_prefix add prefix Column A to each of the dummy columns, finally using DataFrame.join join the the original dataframe with the dataframe containing dummy columns:

# Use this line IF the values in Column A are type of `string` instead of lists.
df['Column A'] = df['Column A'].str.strip('[]').str.split('\s*,\s*')

df1 = (
    df['Column A'].explode()
    .str.get_dummies().sum(level=0).add_prefix('Column A_')
)

df1 = df.drop('Column A', 1).join(df1)

Result:

# print(df1)

   Column B Column C  Column D  Column A_Air  ...  Column A_Groceries  Column A_Love  Column A_Sand  Column A_Water
0         0     True         9             0  ...                   1              0              0               1
1         1    False         1             0  ...                   0              0              1               1
2         2      NaN        12             0  ...                   1              0              0               0
3         3     True         8             1  ...                   0              0              0               0
4         4    False         0             0  ...                   1              1              0               0
user_128331660

Similar to what Shubham has, just slightly different way of writing it,

ohe = pd.get_dummies(df['Column A'].explode())
result = pd.concat([df, ohe.groupby(level=0).sum()], axis=1).drop('Column A', axis=1)
user_44923750
Related