How to aggregate and then expand frequency values in pandas dataframe

user_2736889 0
Viewed 829

I have a dataframe of the following shape:

import pandas as pd
l = []

l.append({"t":'a', 'w': 'x'})
l.append({"t":'a', 'w': 'x'})
l.append({"t":'a', 'w': 'y'})
l.append({"t":'b', 'w': 'y'})
l.append({"t":'b', 'w': 'y'})
l.append({"t":'b', 'w': 'z'})
l.append({"t":'b', 'w': 'y'})
df = pd.DataFrame(l)

I want to first aggregate based on t colmun and then expand to show each items frequency in w column. In other words I want the following result:

   t  w  freq
0  a  x     2
1  a  y     1
2  b  y     3
3  b  z     1

How is this possible? I tried so many different ways with no result.

pandas dataframe aggregate python
2 Answers

value_counts now accept two columns

df.value_counts(['t','w'])
Out[6]: 
t  w
b  y    3
a  x    2
b  z    1
a  y    1
dtype: int64
user_79645270

dfLet's try groupby().value_counts():

df.groupby(['t'])['w'].value_counts().reset_index(name='freq')

Alse, groupby().size():

df.groupby(['t','w']).size().reset_index(name='freq')

Output:

   t  w  freq
0  a  x     2
1  a  y     1
2  b  y     3
3  b  z     1
user_42384080
Related