如何使用groupby计算和应用vwap(成交量加权平均价格)? [英] How to calculate vwap (volume weighted average price) using groupby and apply?

问题描述

我已经阅读了多篇类似我的问题的文章，但我仍然无法弄清楚.我有一个熊猫df，看起来像以下(连续数天):

I have read multiple post similar to my question, but I still can't figure it out. I have a pandas df that looks like the following (for multiple days):

Out[1]: 
                     price  quantity
time                                
2016-06-08 09:00:22  32.30    1960.0
2016-06-08 09:00:22  32.30     142.0
2016-06-08 09:00:22  32.30    3857.0
2016-06-08 09:00:22  32.30    1000.0
2016-06-08 09:00:22  32.35     991.0
2016-06-08 09:00:22  32.30     447.0
...

要计算我可以做的vwap:

To calculate the vwap I could do:

df['vwap'] = (np.cumsum(df.quantity * df.price) / np.cumsum(df.quantity))

但是，我想每天(groupby)重新开始，但是我不知道该如何使用(lambda?)函数来工作.

However, I would like to start over every day (groupby), but I can't figure out how to make it work with a (lambda?) function.

df['vwap_day'] = df.groupby(df.index.date)['vwap'].apply(lambda ...

速度至关重要.将不胜感激:)

Speed is of essence. Would appreciate any help:)

推荐答案

选项0
普通香草方式

def vwap(df):
    q = df.quantity.values
    p = df.price.values
    return df.assign(vwap=(p * q).cumsum() / q.cumsum())

df = df.groupby(df.index.date, group_keys=False).apply(vwap)
df

                     price  quantity       vwap
time                                           
2016-06-08 09:00:22  32.30    1960.0  32.300000
2016-06-08 09:00:22  32.30     142.0  32.300000
2016-06-08 09:00:22  32.30    3857.0  32.300000
2016-06-08 09:00:22  32.30    1000.0  32.300000
2016-06-08 09:00:22  32.35     991.0  32.306233
2016-06-08 09:00:22  32.30     447.0  32.305901

选项1
扔一点eval

Option 1
Throwing in a little eval

df = df.assign(
    vwap=df.eval(
        'wgtd = price * quantity', inplace=False
    ).groupby(df.index.date).cumsum().eval('wgtd / quantity')
)
df

                     price  quantity       vwap
time                                           
2016-06-08 09:00:22  32.30    1960.0  32.300000
2016-06-08 09:00:22  32.30     142.0  32.300000
2016-06-08 09:00:22  32.30    3857.0  32.300000
2016-06-08 09:00:22  32.30    1000.0  32.300000
2016-06-08 09:00:22  32.35     991.0  32.306233
2016-06-08 09:00:22  32.30     447.0  32.305901

这篇关于如何使用groupby计算和应用vwap(成交量加权平均价格)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！