pandas 数据帧中的groupby加权平均值和总和 [英] groupby weighted average and sum in pandas dataframe

问题描述

我有一个数据框，

    Out[78]: 
   contract month year  buys  adjusted_lots    price
0         W     Z    5  Sell             -5   554.85
1         C     Z    5  Sell             -3   424.50
2         C     Z    5  Sell             -2   424.00
3         C     Z    5  Sell             -2   423.75
4         C     Z    5  Sell             -3   423.50
5         C     Z    5  Sell             -2   425.50
6         C     Z    5  Sell             -3   425.25
7         C     Z    5  Sell             -2   426.00
8         C     Z    5  Sell             -2   426.75
9        CC     U    5   Buy              5  3328.00
10       SB     V    5   Buy              5    11.65
11       SB     V    5   Buy              5    11.64
12       SB     V    5   Buy              2    11.60

我需要price和ajusted_lots的adjusted_lots和加权总和之和，并按所有其他列进行分组，即按(合同，月，年和购买)分组

I need a sum of adjusted_lots , price which is weighted average , of price and ajusted_lots , grouped by all the other columns , ie. grouped by (contract, month , year and buys)

R的类似解决方案是使用dplyr通过以下代码实现的，但是在熊猫中却无法做到这一点.

Similiar solution on R was achieved by following code, using dplyr, however unable to do the same in pandas.

> newdf = df %>%
  select ( contract , month , year , buys , adjusted_lots , price ) %>%
  group_by( contract , month , year ,  buys) %>%
  summarise(qty = sum( adjusted_lots) , avgpx = weighted.mean(x = price , w = adjusted_lots) , comdty = "Comdty" )

> newdf
Source: local data frame [4 x 6]

  contract month year comdty qty     avgpx
1        C     Z    5 Comdty -19  424.8289
2       CC     U    5 Comdty   5 3328.0000
3       SB     V    5 Comdty  12   11.6375
4        W     Z    5 Comdty  -5  554.8500

是否可以通过groupby或任何其他解决方案?

is the same possible by groupby or any other solution ?

推荐答案

更新聚合使其与最新版本的熊猫一起使用

update aggregation so it works with recent version of pandas

要将多个函数传递给groupby对象，您需要传递带有聚合函数和该函数适用的列的元组:

To pass multiple functions to a groupby object, you need to pass a tuples with the aggregation functions and the column to which the function applies:

# Define a lambda function to compute the weighted mean:
wm = lambda x: np.average(x, weights=df.loc[x.index, "adjusted_lots"])

# Define a dictionary with the functions to apply for a given column:
# the following is deprecated since pandas 0.20:
# f = {'adjusted_lots': ['sum'], 'price': {'weighted_mean' : wm} }
# df.groupby(["contract", "month", "year", "buys"]).agg(f)

# Groupby and [aggregate with namedAgg][1]:
df.groupby(["contract", "month", "year", "buys"]).agg(adjusted_lots=("adjusted_lots", "sum"),  
                                                      price_weighted_mean=("price", wm))

                          adjusted_lots  price_weighted_mean
contract month year buys                                    
C        Z     5    Sell            -19           424.828947
CC       U     5    Buy               5          3328.000000
SB       V     5    Buy              12            11.637500
W        Z     5    Sell             -5           554.850000

您可以在此处看到更多信息:

You can see more here:

• http://pandas .pydata.org/pandas-docs/stable/groupby.html#applying-multiple-functions-at-once

，这里还有一个类似的问题:

and in a similar question here:

• 将多个函数应用于多个groupby列

希望这会有所帮助

这篇关于 pandas 数据帧中的groupby加权平均值和总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！