I have a 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
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)
我需要一个adjust_lots的总和,价格是加权平均值,价格和ajusted_lots,按所有其他列分组,即。按(合同,月份,年份和购买)分组
Similiar solution on R was achieved by following code, using dplyr, however unable to do the same in pandas.
使用dplyr通过以下代码实现对R的类似解决方案,但无法在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
is the same possible by groupby or any other solution ?
groupby或任何其他解决方案是否可能相同?
2 个解决方案
#1
44
To pass multiple functions to a groupby object, you need to pass a dictionary with the aggregation functions corresponding to the columns:
要将多个函数传递给groupby对象,需要传递一个字典,其中包含与列对应的聚合函数:
# 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:
f = {'adjusted_lots': ['sum'], 'price': {'weighted_mean' : wm} }
# Groupby and aggregate with your dictionary:
df.groupby(["contract", "month", "year", "buys"]).agg(f)
adjusted_lots price
sum 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
- http://pandas.pydata.org/pandas-docs/stable/groupby.html#applying-multiple-functions-at-once
and in a similar question here:
在这里类似的问题:
- Apply multiple functions to multiple groupby columns
- 将多个函数应用于多个groupby列
Hope this helps
希望这可以帮助
#2
2
Doing weighted average by groupby(...).apply(...) can be very slow (100x from the following). See my answer (and others) on this thread.
通过groupby(...)执行加权平均.applied(...)可能非常慢(从以下100倍)。在这个帖子上查看我的答案(和其他人)。
def weighted_average(df,data_col,weight_col,by_col):
df['_data_times_weight'] = df[data_col]*df[weight_col]
df['_weight_where_notnull'] = df[weight_col]*pd.notnull(df[data_col])
g = df.groupby(by_col)
result = g['_data_times_weight'].sum() / g['_weight_where_notnull'].sum()
del df['_data_times_weight'], df['_weight_where_notnull']
return result
#1
44
To pass multiple functions to a groupby object, you need to pass a dictionary with the aggregation functions corresponding to the columns:
要将多个函数传递给groupby对象,需要传递一个字典,其中包含与列对应的聚合函数:
# 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:
f = {'adjusted_lots': ['sum'], 'price': {'weighted_mean' : wm} }
# Groupby and aggregate with your dictionary:
df.groupby(["contract", "month", "year", "buys"]).agg(f)
adjusted_lots price
sum 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
- http://pandas.pydata.org/pandas-docs/stable/groupby.html#applying-multiple-functions-at-once
and in a similar question here:
在这里类似的问题:
- Apply multiple functions to multiple groupby columns
- 将多个函数应用于多个groupby列
Hope this helps
希望这可以帮助
#2
2
Doing weighted average by groupby(...).apply(...) can be very slow (100x from the following). See my answer (and others) on this thread.
通过groupby(...)执行加权平均.applied(...)可能非常慢(从以下100倍)。在这个帖子上查看我的答案(和其他人)。
def weighted_average(df,data_col,weight_col,by_col):
df['_data_times_weight'] = df[data_col]*df[weight_col]
df['_weight_where_notnull'] = df[weight_col]*pd.notnull(df[data_col])
g = df.groupby(by_col)
result = g['_data_times_weight'].sum() / g['_weight_where_notnull'].sum()
del df['_data_times_weight'], df['_weight_where_notnull']
return result