Pandas pivot table / groupby to calculate weighted average

Question

I'm using pandas version 0.25.0 to calculate weighted averages of priced contracts.

Data:

{'Counterparty': {0: 'A',
  1: 'B',
  2: 'B',
  3: 'A',
  4: 'A',
  5: 'C',
  6: 'D',
  7: 'E',
  8: 'E',
  9: 'C',
  10: 'F',
  11: 'C',
  12: 'C',
  13: 'G'},
 'Contract': {0: 'A1',
  1: 'B1',
  2: 'B2',
  3: 'A2',
  4: 'A3',
  5: 'C1',
  6: 'D1',
  7: 'E1',
  8: 'E2',
  9: 'C2',
  10: 'F1',
  11: 'C3',
  12: 'C4',
  13: 'G'},
 'Delivery': {0: '1/8/2019',
  1: '1/8/2019',
  2: '1/8/2019',
  3: '1/8/2019',
  4: '1/8/2019',
  5: '1/8/2019',
  6: '1/8/2019',
  7: '1/8/2019',
  8: '1/8/2019',
  9: '1/8/2019',
  10: '1/8/2019',
  11: '1/8/2019',
  12: '1/8/2019',
  13: '1/8/2019'},
 'Price': {0: 134.0,
  1: 151.0,
  2: 149.0,
  3: 134.0,
  4: 132.14700000000002,
  5: 150.0,
  6: 134.566,
  7: 153.0,
  8: 151.0,
  9: 135.0,
  10: 149.0,
  11: 135.0,
  12: 147.0,
  13: 151.0},
 'Balance': {0: 200.0,
  1: 54.87,
  2: 200.0,
  3: 133.44,
  4: 500.0,
  5: 500.0,
  6: 1324.05,
  7: 279.87,
  8: 200.0,
  9: 20.66,
  10: 110.15,
  11: 100.0,
  12: 100.0,
  13: 35.04}}

Method 1:

df.pivot_table(
    index=['Counterparty', 'Contract'],
    columns='Delivery',
    values=['Balance', 'Price'],
    aggfunc={
        'Balance': sum,
        'Price': np.mean
    },
    margins=True
).fillna('').swaplevel(0,1,axis=1).sort_index(axis=1).round(3)

Result 1:

Is there any way in which I can use np.average in pandas pivot table? Thinking along the lines of

aggfunc = {
    'Balance': sum,
    'Price': lambda x: np.average(x, weights='Balance')
}

Current result: 143.265, which is computed by np.mean.

Desired result: 140.424, which is the weighted average of Price by Balance.

Method 2:

df_grouped = df.groupby(['Counterparty', 'Contract', 'Delivery']).apply(lambda x: pd.Series(
                {
                    'Balance': x['Balance'].sum(),
                    'Price': np.average(x['Price'], weights=x['Balance']),
                }
                )).round(3).unstack().swaplevel(0,1, axis=1).sort_index(axis=1)

Result 2:

Using groupby, I would need to pd.concat and append sum by level to get grand totals with aggfunc = {Balance: sum, Price: np.average}.

The expected result is:

Balance: 3758.08 (using sum)
Price: 140.424 (using np.average)

Which is displayed in a Grand Total row beneath all the rows of data.

score 2 · Accepted Answer · answered Aug 26 '19 at 04:53

Just define a custom function to calculate weighted average, and use it with aggfunc instead of np.mean in your code as follows:

wa_func =lambda x: np.average(x, weights=df.loc[x.index, 'Balance'])

df1 = df.pivot_table(
    index=['Counterparty', 'Contract'],
    columns='Delivery',
    values=['Balance', 'Price'],
    aggfunc={
        'Balance': sum,
        'Price': wa_func
    },
    margins=True
).fillna('').swaplevel(0,1,axis=1).sort_index(axis=1).round(3)

Out[35]:
Delivery              1/8/2019               All
                       Balance    Price  Balance    Price
Counterparty Contract
A            A1         200.00  134.000   200.00  134.000
             A2         133.44  134.000   133.44  134.000
             A3         500.00  132.147   500.00  132.147
B            B1          54.87  151.000    54.87  151.000
             B2         200.00  149.000   200.00  149.000
C            C1         500.00  150.000   500.00  150.000
             C2          20.66  135.000    20.66  135.000
             C3         100.00  135.000   100.00  135.000
             C4         100.00  147.000   100.00  147.000
D            D1        1324.05  134.566  1324.05  134.566
E            E1         279.87  153.000   279.87  153.000
             E2         200.00  151.000   200.00  151.000
F            F1         110.15  149.000   110.15  149.000
G            G           35.04  151.000    35.04  151.000
All                    3758.08  140.424  3758.08  140.424

just to follow up with this question.. if i wanted to sort the df by 'Balance' from largest to smallest, how would I accomplish it ? Have tried to do .sort_values([('Balance', 'Price')], ascending=False), but it says none of the keys were found. Reference: https://stackoverflow.com/questions/14733871/multi-index-sorting-in-pandas — yongsheng, Sep 05 '19 at 02:43

Pandas pivot table / groupby to calculate weighted average

1 Answers1