Pandas dataframe: Group by two columns and then average over another column

Question

Assuming that I have a dataframe with the following values:

df:
col1    col2    value
1       2       3
1       2       1
2       3       1

I want to first groupby my dataframe based on the first two columns (col1 and col2) and then average over values of the thirs column (value). So the desired output would look like this:

col1    col2    avg-value
1       2       2
2       3       1

I am using the following code:

columns = ['col1','col2','avg']
df = pd.DataFrame(columns=columns)
df.loc[0] = [1,2,3]
df.loc[1] = [1,3,3]
print(df[['col1','col2','avg']].groupby('col1','col2').mean())

which gets the following error:

ValueError: No axis named col2 for object type <class 'pandas.core.frame.DataFrame'>

Any help would be much appreciated.

Solution 1

You need to pass a list of the columns to groupby, what you passed was interpreted as the axis param which is why it raised an error:

In [30]:
columns = ['col1','col2','avg']
df = pd.DataFrame(columns=columns)
df.loc[0] = [1,2,3]
df.loc[1] = [1,3,3]

print(df[['col1','col2','avg']].groupby(['col1','col2']).mean())
           avg
col1 col2     
1    2       3
     3       3

Solution 2

If you want to group by multiple columns, you should put them in a list:

columns = ['col1','col2','value']
df = pd.DataFrame(columns=columns)
df.loc[0] = [1,2,3]
df.loc[1] = [1,3,3]
df.loc[2] = [2,3,1]
print(df.groupby(['col1','col2']).mean())

Or slightly more verbose, for the sake of getting the word 'avg' in your aggregated dataframe:

import numpy as np
columns = ['col1','col2','value']
df = pd.DataFrame(columns=columns)
df.loc[0] = [1,2,3]
df.loc[1] = [1,3,3]
df.loc[2] = [2,3,1]
print(df.groupby(['col1','col2']).agg({'value': {'avg': np.mean}}))

Comments

[EdChum]

How is this different to my answer?

[EdChum]

No worries but it's good to not post duplicate answers unless there is a real difference