Adding a 'count' column to the result of a groupby in pandas?

Question

I think this is a fairly basic question, but I can't seem to find the solution.

I have a pandas dataframe similar to the following:

import pandas as pd  df = pd.DataFrame({'A' : ['x','x','y','z','z'],                    'B' : ['p','p','q','r','r']}) df 

which creates a table like this:

    A   B 0   x   p 1   x   p 2   y   q 3   z   r 4   z   r 

I'm trying to create a table that represents the number of distinct values in that dataframe. So my goal is something like this:

    A   B   c 0   x   p   2 1   y   q   1 2   z   r   2 

I can't find the correct functions to achieve this, though. I've tried:

df.groupby(['A','B']).agg('count') 

This produces a table with 3 rows (as expected) but without a 'count' column. I don't know how to add in that count column. Could someone point me in the right direction?

Answer 1

You can using size

df.groupby(['A','B']).size() Out[590]:  A  B x  p    2 y  q    1 z  r    2 dtype: int64 

For your solution adding one of the columns

df.groupby(['A','B']).B.agg('count') Out[591]:  A  B x  p    2 y  q    1 z  r    2 Name: B, dtype: int64 

Update :

df.groupby(['A','B']).B.agg('count').to_frame('c').reset_index()  #df.groupby(['A','B']).size().to_frame('c').reset_index() Out[593]:     A  B  c 0  x  p  2 1  y  q  1 2  z  r  2 

Answer 2

pandas >= 1.1: DataFrame.value_counts

This is an identical replacement for df.groupby(['A', 'B']).size().

df.value_counts(['A', 'B'])  A  B z  r    2 x  p    2 y  q    1 dtype: int64 

df.value_counts(['A', 'B']).reset_index(name='c')     A  B  c 0  z  r  2 1  x  p  2 2  y  q  1