pandas groupby at row and column level-CodePudding

My dataframe is in the below format:

col1 col2 col3
A1    B1    t1
A2    B2    t2
A1    B1    t1
A1    B2    t2

I am grouping a dataframe as below:

df.groupby(['col1', 'col2'])['col3'].count()

which gives me the stats as:

A1 B1 2
A1 B2 1
A2 B2 1

What I would like the count be split on the basis of col3 like:

      t1   t2 
A1 B1 1    1
A1 B2 0    1
A2 B2 0    1

How could I achieve something like this?

CodePudding user response：

You can use a pivot_table:

out = (df.assign(count=1)
         .pivot_table(index=['col1', 'col2'], columns='col3', values='count',
                      aggfunc='count', fill_value=0)
      )

output:

col3       t1  t2
col1 col2        
A1   B1     2   0
     B2     0   1
A2   B2     0   1

CodePudding user response：

`crosstab`

pd.crosstab([df['col1'], df['col2']], df['col3'])

col3       t1  t2
col1 col2        
A1   B1     2   0
     B2     0   1
A2   B2     0   1