Search code examples
pythonpandasdataframegroup-by

Group by multiple columns to create frequency table in pandas

I have a dataframe as below:

data = [['A', 1], ['A', 0], ['A', 1], ['B', 0], ['B', 1], ['C', 1], ['C', 1], ['C', 1]]
temp_df = pd.DataFrame(data, columns = ['Name', 'effect'])

  Name  effect
0    A       1
1    A       0
2    A       1
3    B       0
4    B       1
5    C       1
6    C       1
7    C       1

After doing a groupby I'm getting:

temp_df.groupby(['Name','effect']).size().reset_index(name='count')

  Name  effect  count
0    A       0      1
1    A       1      2
2    B       0      1
3    B       1      1
4    C       1      3

But I need my result to look like a frequency table:

Name e0 e1
A 1 2
B 1 1
C 0 3
Solution

  • You can cross-tabulate with crosstab(). To add e to the column names, chain add_prefix():

    pd.crosstab(temp_df.Name, temp_df.effect).add_prefix('e')

# effect  e0  e1
# Name          
# A        1   2
# B        1   1
# C        0   3