CODEWITHSUNDEEP
pythonpandas
Mike
Mike

Reputation: 1844

Aggregating rows and creating columns as count of values

I have a dataframe that looks something like:

   tt  oo
0  g  gh
1  g  jj
2  g  gh
3  t  gh
4  t  gh

I'd like to end up with a new dataframe that aggregates on 'tt', giving counts of the 'oo' column so that it looks like:

   gh  jj
g  2   1
t  2   0

I tried a pivot table but ended up with an 'Index contains duplicate entries error'. t

Upvotes: 1

Views: 59

Answers (2)

ely
ely

Reputation: 77414

dfrm1 = pandas.DataFrame({'tt':['g', 'g', 'g', 't', 't'], 
                          'oo':['gh', 'jj', 'gh', 'gh', 'gh']})

dfrm1.groupby('tt')['oo'].value_counts().unstack(level=1).fillna(0.0)

Upvotes: 3

Marius
Marius

Reputation: 60070

You can do this in one line using groupby:

df.groupby('tt')['oo'].value_counts()
Out[8]: 
tt    
g   gh    2
    jj    1
t   gh    2
dtype: int64

Upvotes: 0

Related Questions