5

I have a data frame similar to the following

+----------------+-------+
| class          | year  |
+----------------+-------+
| ['A', 'B']     | 2001  |
| ['A']          | 2002  |
| ['B']          | 2001  |
| ['A', 'B', 'C']| 2003  |
| ['B', 'C']     | 2001  |
| ['C']          | 2003  |
+----------------+-------+

I want to create a data frame using this so that the resulting table shows the count of each category in class per yer.

+-----+----+----+----+
|year | A  | B  | C  |
+-----+----+----+----+
|2001 | 1  | 3  | 1  |
|2002 | 1  | 0  | 0  |
|2003 | 1  | 1  | 2  |
+-----+----+----+----+

What's the easiest way to do this?

ksaliya
  • 182
  • 7

1 Answers1

5

Try unnesting

s=unnesting(df,['class'])

Then, we do crosstab

pd.crosstab(s['year'],s['class'])

Method from sklearn 

from sklearn.preprocessing import MultiLabelBinarizer
mlb = MultiLabelBinarizer()
pd.DataFrame(mlb.fit_transform(df['class']),columns=mlb.classes_, index=df.year).sum(level=0)
Out[293]: 
      A  B  C
year         
2001  2  2  1
2002  1  1  1
2003  0  1  1

Method of get_dummies

df.set_index('year')['class'].apply(','.join).str.get_dummies(sep=',').sum(level=0)
Out[297]: 
      A  B  C
year         
2001  2  2  1
2002  1  1  1
2003  0  1  1

def unnesting(df, explode):
    idx = df.index.repeat(df[explode[0]].str.len())
    df1 = pd.concat([
        pd.DataFrame({x: np.concatenate(df[x].values)}) for x in explode], axis=1)
    df1.index = idx

    return df1.join(df.drop(explode, 1), how='left')
BENY
  • 317,841
  • 20
  • 164
  • 234
  • What if class column contains a list of strings, instead of list of characters. For instance ['above_average', 'average', 'below_average']. MultiLabelBinarizer doesn't work in this case. – ksaliya Apr 16 '19 at 01:53
  • @ksaliya I think all above method still work for that case – BENY Apr 16 '19 at 01:55
  • MultiLabelBinarizer created columns per each character in the the category in the data set I've tested. But, your answer was extremely helpful. Thank you. – ksaliya Apr 16 '19 at 02:02