Dataframe aggregated to Dataframe

Question

From the following dataframe agregation I would like to give the 'traditional' DataFrame format. It seems pretty to solve easy but I'm struggling to get it.

data sample

import pandas as pd
df = pd.DataFrame({'col1':['A','A','A','A','B','B','B','A','C','C','C'],'col2':['foo','zzz','bar','foo','car','car','dog','bar','bar','zzz','bar']})
df

   col1 col2
0   A   foo
1   A   zzz
2   A   bar
3   A   foo
4   B   car
5   B   car
6   B   dog
7   A   bar
8   C   bar
9   C   zzz
10  C   bar

Output

df.groupby(['col1','col2'])['col1'].agg({'Frequency':'count'})

    Frequency
col1    col2    
A       bar  2
        foo  2
        zzz  1
B       car  2
        dog  1
C       bar  2
        zzz  1

Desired Output

col1    col2   freq 
A       bar    2
A       foo    2
A       zzz    1
B       car    2
B       dog    1
C       bar    2
C       zzz    1

Answer 1

Solution 1:

Try simply reseting the index on your groupby using this:

df.groupby(['col1','col2'])['col1'].agg({'Frequency':'count'}).reset_index()

  col1 col2  Frequency
0    A  bar          2
1    A  foo          2
2    A  zzz          1
3    B  car          2
4    B  dog          1
5    C  bar          2
6    C  zzz          1

Solution 2:

use as_index=False when creating your groupby:

df.groupby(['col1','col2'], as_index=False)['col1'].agg({'Frequency':'count'})

  col1 col2  Frequency
0    A  bar          2
1    A  foo          2
2    A  zzz          1
3    B  car          2
4    B  dog          1
5    C  bar          2
6    C  zzz          1