How do we replace multiple rows of data with the mean of those rows for every change in the other columns?

user_10591246 0
Viewed 54

I have a data frame with a column 'Medals' containing either Gold, Silver or Bronze. There is a height column and a year column. It looks something like this -

Medal   Year    Height      0
Bronze  1896    -2.352063   1
Bronze  1896    -0.435173   1
Bronze  1896    0.220606    1
Bronze  1896    0.304680    1
Bronze  1896    0.607347    1
Bronze  1900    -1.847618   1
Bronze  1900    -1.410432   1
Bronze  1900    -0.334284   1
Bronze  1900    -0.182950   1
Bronze  1900    -0.031617   3
Bronze  1900    0.136532    2
Silver  2016    1.078162    9
Silver  2016    1.179051    2
Silver  2016    1.279940    1
Silver  2016    1.380829    4
Silver  2016    1.481718    3
Silver  2016    1.582607    3
Silver  2016    1.683495    8
Silver  2016    1.784384    4
Silver  2016    1.885273    3
Silver  2016    2.087051    1
Silver  2016    2.187940    1
Silver  2016    2.288829    1
Silver  2016    2.591496    1
Silver  2016    2.692385    1
Silver  2016    2.995052    1

What I want is simple - 

Medal   Year    Height      
Bronze  1896    [Mean of heights having Bronze and 1896] 
Bronze  1896    [Mean of heights having Bronze and 1900]   
Bronze  1896    [Mean of heights having Silver and 2016]

Also [0] column represents the frequency. So we have to multiply it with the height before calculating mean.

I tried using np.einsum but I wasn't able to make it work for my case. There are a few similar questions but none of the answers suit my requirements. Any hints would be helpful.

PS: I have normalized the heights column and hence the negative values

pandas-groupby pandas dataframe numpy python
2 Answers

One way is to create 2 series via Pandas groupby and divide one by the other:

group_cols = ['Medal', 'Year']
observations = df.groupby(group_cols)[0].sum()
total_height = df.assign(total=df['Height']*df[0]).groupby(group_cols)['total'].sum()

res = total_height / observations

print(res.reset_index())

    Medal  Year         0
0  Bronze  1896 -0.330921
1  Bronze  1900 -0.399675
2  Silver  2016  1.608415

More succinctly (thanks @piRSquared):

df = df.rename(columns={0: 'Count'})

res = df.assign(Total=df['Height']*df['Count'])\
        .groupby(['Medal', 'Year']).sum()\
        .eval('Total / Count')\
        .rename('Mean').reset_index()

print(res)

    Medal  Year      Mean
0  Bronze  1896 -0.330921
1  Bronze  1900 -0.399675
2  Silver  2016  1.608415
user_92095460

pandas.Index.repeat

  • Use '0' column to repeat the index
  • Use loc to reindex by it
  • Then groupby
df.loc[df.index.repeat(df['0'])].groupby(['Medal', 'Year'])['Height'].mean()

Medal   Year
Bronze  1896   -0.330921
        1900   -0.399675
Silver  2016    1.608415
Name: Height, dtype: float64
user_23366540
Related