user113531 user113531 - 4 months ago 13
Python Question

Value counts for all combinations of groups

I have a dataframe with multiple group columns and a value column.

a b val
0 A C 1
1 A D 1
2 A D 1
3 A D 2
4 B E 0


For any one group, for eg
a==A
,
b==C
I can do
value_counts
on the series slice. How can I get the value counts of all possible combinations of the group columns in a dataframe format similar to:

a b val counts
0 A C 1 1
1 A D 1 2
2 A D 2 1
2 B E 0 1

Answer

is that what you want?

In [47]: df.groupby(['a','b','val']).size().reset_index()
Out[47]:
   a  b  val  0
0  A  C    1  1
1  A  D    1  2
2  A  D    2  1
3  B  E    0  1

or this?

In [43]: df['counts'] = df.groupby(['a','b'])['val'].transform('size')

In [44]: df
Out[44]:
   a  b  val  counts
0  A  C    1       1
1  A  D    1       3
2  A  D    1       3
3  A  D    2       3
4  B  E    0       1