trying to aggregate multiple columns in groupy by using python similiar to SQL

Question

I am currently looking to aggregate a dataframe by many categorical columns and sum up several metric columns as well. I am trying to do this similiar to how I would in SQL but I cant seem to find a simple method. I also am not sure if I am at the limits of pandas group by as the code below returns a keyerror on the second metric column. the code will run if I only aggregate one column. How do I aggregate multiple columns?

df_agg = pd.DataFrame(data = df.groupby(['House', 'cat1', 'cat2', 'cat3'])
['points'].mean()
['counts'].count()
['value'].sum()
['metric'].sum()
['metric2'].sum()
['metric3'].sum())

If get keyerror it means some column is missing, or whitespace in column name. the best check it by print (df.columns.tolist()) — jezrael
– jezrael, Commented May 9, 2018 at 13:10

jezrael · Accepted Answer · 2018-05-09 13:02:22Z

2

Use agg by dictionary of columns with aggregate functions, DataFrame contructor is not necessary:

d = {'points':'mean', 'counts':'count','value':'sum','metric':'sum','metric1':'sum','metric2':'sum'}
df_agg = df.groupby(['House', 'cat1', 'cat2', 'cat3']).agg(d).reset_index()
print (df_agg)

answered May 9, 2018 at 13:02

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

trying to aggregate multiple columns in groupy by using python similiar to SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related