Pandas converting list of variable in dummies in a wider dataframe

Question

I have imported a json file and I now have a data frame where one column (code) that is a list.

index year   gvkey    code
0    1998    15686    ['TAX', 'ENVR', 'HEALTH']
1    2005    15372    ['EDUC', 'TAX', 'HEALTH', 'JUST']
2    2001    27486    ['LAB', 'TAX', 'HEALTH']
3    2008    84967    ['HEALTH','LAB', 'JUST']

What I want to get is something as follow:

index year   gvkey  TAX  ENVR HEALTH EDUC JUST LAB
0    1998    15686   1     1     1    0    0    0
1    2005    15372   1     0     1    0    1    0
2    2001    27486   1     0     1    0    1    0
3    2008    84967   0     0     1    0    1    1

Following Pandas convert a column of list to dummies I tried the following code (where df is my data frame):

s = pd.Series(df["code"])
l = pd.get_dummies(s.apply(pd.Series).stack()).sum(level=0)

I get the second part of the data right (variables TAX, ENVR, HEALTH, EDUC, JUST and LAB), but loose the first (year and gvkey).

How can I keep the year and gvkey variable?

I get the error `concat() got multiple values for argument 'axis'`... — ocut, Feb 02 '20 at 05:09
Oops , `pd.concat((df[['year','gvkey']],l),axis=1)` try this, I had a typo — anky, Feb 02 '20 at 05:10

jezrael · Accepted Answer · 2020-02-02T05:33:04.650

I think better solution here is use DataFrame.pop with Series.str.join and Series.str.get_dummies:

df = df.join(df.pop('code').str.join('|').str.get_dummies())
print (df)
       year  gvkey  EDUC  ENVR  HEALTH  JUST  LAB  TAX
index                                                 
0      1998  15686     0     1       1     0    0    1
1      2005  15372     1     0       1     1    0    1
2      2001  27486     0     0       1     0    1    1
3      2008  84967     0     0       1     1    1    0

If performance is important use MultiLabelBinarizer:

from sklearn.preprocessing import MultiLabelBinarizer

mlb = MultiLabelBinarizer()
df1 = pd.DataFrame(mlb.fit_transform(df.pop('code')),columns=mlb.classes_)

df = df.join(df1)
print (df)
       year  gvkey  EDUC  ENVR  HEALTH  JUST  LAB  TAX
index                                                 
0      1998  15686     0     1       1     0    0    1
1      2005  15372     1     0       1     1    0    1
2      2001  27486     0     0       1     0    1    1
3      2008  84967     0     0       1     1    1    0

Your solution is possible, but slow, so better avoid it, also sum working only for unique values, for general solution need max:

df = df.join(pd.get_dummies(df.pop('code').apply(pd.Series).stack()).max(level=0))
print (df)
       year  gvkey  EDUC  ENVR  HEALTH  JUST  LAB  TAX
index                                                 
0      1998  15686     0     1       1     0    0    1
1      2005  15372     1     0       1     1    0    1
2      2001  27486     0     0       1     0    1    1
3      2008  84967     0     0       1     1    1    0

Thanks, this is actually very nice! But for some reason I loose the text and instead of getting EDUC ENVR HEALTH I get A B C D ... — ocut, Feb 02 '20 at 05:26
Ohh, actually I previously added 'df["code"] = ["; ".join(map(str, l)) for l in df["code"]]' to be later able to export it in stata and that was the problem. Deleting it works just fine, thank you! — ocut, Feb 03 '20 at 01:15

anky · Answer 2 · 2020-02-02T05:25:42.160

2

You can do this by below methods:

Method 1: Convert the column to a dataframe and get dummies , then groupby on axis=1 and get max:

m = pd.get_dummies(pd.DataFrame(df['code'].tolist())).groupby(lambda x:
    x.split('_')[1],axis=1).max()
final1 = df.drop('code',1).assign(**m)

Method 2: Join the list of columns with a | and use series.str.get_dummies

final2 = df.drop('code',1).assign(**df['code'].str.join('|').str.get_dummies())

Method 3: Your method with concat

s = pd.Series(df["code"])
l = pd.get_dummies(s.apply(pd.Series).stack()).max(level=0)
final3 = pd.concat((df.drop('code',1),l),axis=1)
#or final = df.drop('code',1).assign(**l)

edited Feb 02 '20 at 05:25

answered Feb 02 '20 at 05:21

anky

74,114
11
41
70

`.sum(level=0)` ? why? – jezrael Feb 02 '20 at 05:21
without `pop` it is a bit overomplicated ;) – jezrael Feb 02 '20 at 05:30
1

@jezrael I like `pop` , but sometimes it doesnot serve well especially when you are in a testing phase since it deletes the column from the dataframe inplace. I agree once you are comfortable using it , it is nice :) – anky Feb 02 '20 at 05:34
1

yop, agree, first is necessary understand it. – jezrael Feb 02 '20 at 05:35

Pandas converting list of variable in dummies in a wider dataframe

2 Answers2