after group by I want to do aggregation on those groups with more than 5 members in python, how should I do that?

user_16290310 0
Viewed 42

I have Value of Energy Cost Saving Program dataset

And I want to answer this question: Considering only NTAs with at least 5 listed businesses, what is the average total savings and the total jobs created for each NTA?

So I used the following code for the first parts:

df['NTA_mod']=df['NTA'].str.split('-')
df=df.explode('NTA_mod').reset_index(drop=True)

df_NTA_grp=df.groupby(['NTA_mod'])

now I have to pick those with NTA greater than 5 and I used following code

df.groupby('NTA_mod').filter(lambda x: len(x) >= 5)

However, I don't get any response and I don't know how to continue to answer the question. How should I pick those NTA with businesses with at least 5 businesses? is my approach correct? If yes now how should I aggregate to get mean and sum for next step?

pandas-groupby filter count group-by python
1 Answers

You are going in the right direction. Use aggregate method to get mean and sum. aggregate can perform different operations on different columns.

df_ge_5 = df_NTA_grp.filter(lambda x: len(x) >= 5)

df_ge_5.groupby('NTA_mod').agg({ 
    'Total Savings': 'mean',
    'Job created': 'sum',
})
# Or, renaming columns with named aggregation
aggn = {
    'Average Total Savings': ('Total Savings', 'mean'),
    'Total Jobs Created': ('Job created','sum'),
}
df_ge_5.groupby('NTA_mod').agg(**aggn)
user_72832010
Related