Return groupby weighted average for multiple pandas dataframe columns as a dataframe

Question

My question is related to this one. However, the solution there isn't working for me.

I have a dataframe, df, as shown below. I want to take the weighted averages of elevation and width with counts as the weights, grouped by building and day. How can I do that?

#Sample data
import pandas as pd
df = pd.DataFrame({
  'building': ['A1', 'A1', 'A1', 'A1'],
  'day': ['2019-07-02', '2019-07-02', '2019-07-03', '2019-07-03'],
  'id': ['alak', 'ldau', 'laud', 'lkdu'],
  'counts': [1, 2, 3, 7],
  'elevation': [5.7, 7.8, 8.7, 6.9],
  'width':[1.2, 2.4, 3.4, 2.7]
})

 df
    building    day      id   counts elevation  width
  0  A1      2019-07-02  alak   1      5.7       1.2
  1  A1      2019-07-02  ldau   2      7.8       2.4
  2  A1      2019-07-03  laud   3      8.7       3.4
  3  A1      2019-07-03  lkdu   7      6.9       2.7

# What I want to get:
    building    day     elevation   width
  0  A1      2019-07-02   7.1        2.0
  1  A1      2019-07-03   7.4        2.9

score 4 · Accepted Answer · answered Sep 03 '19 at 21:19

You can using the trick of reindex and repeat

df.reindex(df.index.repeat(df.counts)).drop('counts',1).\
     groupby(['building','day'],as_index=False).mean()
Out[110]: 
  building         day  elevation  width
0       A1  2019-07-02       7.10   2.00
1       A1  2019-07-03       7.44   2.91

score 1 · Answer 2 · answered Sep 03 '19 at 21:15

I'm guessing there's a better way, but this does it:

df = pd.DataFrame({
  'building': ['A1', 'A1', 'A1', 'A1'],
  'day': ['2019-07-02', '2019-07-02', '2019-07-03', '2019-07-03'],
  'id': ['alak', 'ldau', 'lauid', 'lkdu'],
  'counts': [1, 2, 3, 7],
  'elevation': [5.7, 7.8, 8.7, 6.9],
  'width':[1.2, 2.4, 3.4, 2.7]
})

df = df.set_index(['building','day'])
sum_count = df.groupby(['building','day']).counts.sum()
df['w_elevation'] = df.elevation*df.counts /sum_count
df['w_width'] = df.width*df.counts / sum_count
df.groupby(['building','day']).sum()

Output:

                     counts  elevation  width  w_elevation  w_width
building day                                                       
A1       2019-07-02       3       13.5    3.6         7.10     2.00
         2019-07-03      10       15.6    6.1         7.44     2.91

score 1 · Answer 3 · answered Sep 03 '19 at 21:19

1

You can do that as follows:

df_sum= df.copy()
df_sum['elevation']*= df_sum['counts']
df_sum['width']*= df_sum['counts']

df_sum= df_sum.groupby(['building', 'day']).agg(dict(elevation=sum, width=sum, counts=sum))
df_sum['elevation']/= df_sum['counts']
df_sum['width']/= df_sum['counts']
df_sum.reset_index(inplace=True)
df_sum.drop('counts', axis='columns', inplace=True)

The result is:

  building         day  elevation  width
0       A1  2019-07-02       7.10   2.00
1       A1  2019-07-03       7.44   2.91

answered Sep 03 '19 at 21:19

jottbe

4,228
1
15
31

Is there are reason you used .`agg(dict..)` summed individually instead of just `sum`? Thanks. – Moondra Sep 03 '19 at 22:15
1

It's just because there is also the `id` which is not summable. But you can also restrict the columns before you apply it. It's probably more a matter of taste, what you use. – jottbe Sep 03 '19 at 22:19

mucio · Answer 4 · 2019-09-04T09:45:23.927

In case you need something a bit more verbose than the previous answers:

result = df

# normalize for the weight
result['elevation'] = result['elevation'] * result['counts']
result['width'] = result['width'] * result['counts']

# let's sum all values per our dimensions
result = result.groupby(['building', 'day']).sum()
# and the get the weighted averages
result['elevation'] = result['elevation'] / result['counts']
result['width'] = result['width'] / result['counts']

# final results
result[['elevation', 'width']]

Or in another way:

result = df

# first of all let's get the sum of counts by building and day
# the indexes will return useful later
result = result.set_index(['building', 'day'])
counts = result['counts'].groupby(['building', 'day']).sum()

# let's normalize the values for elevation and width
result[['elevation', 'width']] = result[['elevation', 'width']].apply(lambda x: x * result['counts'])

# finally calculate the wigthed average
result = result[['elevation', 'width']].groupby(['building', 'day']).sum()
result = result[['elevation', 'width']].apply(lambda x: x / counts)

result

Return groupby weighted average for multiple pandas dataframe columns as a dataframe

4 Answers4

Linked