CODEWITHSUNDEEP
pythonpandaspandas-groupby
ruedi
ruedi

Reputation: 5545

Calculate Mean on Multiple Groups

I have a Table

Sex     Value1   Value2    City
M       2        1         Berlin
W       3        5         Paris
W       1        3         Paris
M       2        5         Berlin
M       4        2         Paris

I want to calculate the average of Value1 and Value2 for different groups. In my origial Dataset I have 10 Group variables (with a max of 5 characteristics like 5 Cities) that I have shortened to Sex and City (2 Characteristics) in this example. The result should look like this

       AvgOverall   AvgM    AvgW    AvgBerlin    AvgParis
Value1 2,4          2,6     2       2            2,66   
Value2 3,2          2,6     4       3            3,3

I am familiar with the group by and tried

df.groupby('City').mean()

But here we have the problem that Sex is getting also into the calculation. Does anyone has an idea how to solve this? Thanks in advance!

Upvotes: 1

Views: 570

Answers (1)

jezrael
jezrael

Reputation: 862601

You can grouping by 2 columns to 2 dataframes and then use concat also with means of numeric columns (non numeric are excluded):

df1 = df.groupby('City').mean().T
df2 = df.groupby('Sex').mean().T

df3 = pd.concat([df.mean().rename('Overall'), df2, df1], axis=1).add_prefix('Avg')
print (df3)
        AvgOverall      AvgM  AvgW  AvgBerlin  AvgParis
Value1         2.4  2.666667   2.0        2.0  2.666667
Value2         3.2  2.666667   4.0        3.0  3.333333

Upvotes: 5

Related Questions