如何使用带有卖价和买价的 pandas 数据框计算成交量加权平均价(VWAP)? [英] How to calculate Volume Weighted Average Price (VWAP) using a pandas dataframe with ask and bid price?
问题描述
如果我的表如下所示，我如何创建另一个称为vwap的列来计算vwap?
How do i create another column called vwap which calculates the vwap if my table is as shown below?
time bid_size bid ask ask_size trade trade_size phase
0 2019-01-07 07:45:01.064515 495 152.52 152.54 19 NaN NaN OPEN
1 2019-01-07 07:45:01.110072 31 152.53 152.54 19 NaN NaN OPEN
2 2019-01-07 07:45:01.116596 32 152.53 152.54 19 NaN NaN OPEN
3 2019-01-07 07:45:01.116860 32 152.53 152.54 21 NaN NaN OPEN
4 2019-01-07 07:45:01.116905 34 152.53 152.54 21 NaN NaN OPEN
5 2019-01-07 07:45:01.116982 34 152.53 152.54 31 NaN NaN OPEN
6 2019-01-07 07:45:01.147901 38 152.53 152.54 31 NaN NaN OPEN
7 2019-01-07 07:45:01.189971 38 152.53 152.54 31 ask 15.0 OPEN
8 2019-01-07 07:45:01.189971 38 152.53 152.54 16 NaN NaN OPEN
9 2019-01-07 07:45:01.190766 37 152.53 152.54 16 NaN NaN OPEN
10 2019-01-07 07:45:01.190856 37 152.53 152.54 15 NaN NaN OPEN
11 2019-01-07 07:45:01.190856 37 152.53 152.54 16 ask 1.0 OPEN
12 2019-01-07 07:45:01.193938 37 152.53 152.55 108 NaN NaN OPEN
13 2019-01-07 07:45:01.193938 37 152.53 152.54 15 ask 15.0 OPEN
14 2019-01-07 07:45:01.194326 2 152.54 152.55 108 NaN NaN OPEN
15 2019-01-07 07:45:01.194453 2 152.54 152.55 97 NaN NaN OPEN
16 2019-01-07 07:45:01.194479 6 152.54 152.55 97 NaN NaN OPEN
17 2019-01-07 07:45:01.194507 19 152.54 152.55 97 NaN NaN OPEN
18 2019-01-07 07:45:01.194532 19 152.54 152.55 77 NaN NaN OPEN
19 2019-01-07 07:45:01.194598 19 152.54 152.55 79 NaN NaN OPEN
很抱歉，该表不清楚，但是最右边的第二列是trade_size，其左边是trade，它显示了交易的一侧(买入或卖出).如果trade_size和trade均为NaN，则表示在该时间戳下没有交易发生.
Sorry, the table is not clear, but the second most right column is trade_size, on its left is trade, which shows the side of the trade( bid or ask). if both trade_size and trade are NaN, it indicates that no trade occur at that timestamp.
如果df ['trade'] ==问"，则交易价格将为'ask'列中的价格；如果df ['trade] ==出价"，则交易价格将为''列中的价格.出价'.既然有2个价格，请问我该如何计算vwap df ['vwap']?
If df['trade'] == "ask", trade price will be the price in column 'ask' and if df['trade] == "bid", the trade price will be the price in column 'bid'. Since there are 2 prices, may I ask how can i calculate the vwap, df['vwap']?
我的想法是使用np.cumsum().谢谢！
My idea is to use np.cumsum(). Thank you!
推荐答案
您可以使用 np.where ，以便根据trade列中的值为您提供来自正确列(bid或ask)的价格.请注意，这将在没有交易发生时为您提供买入价，但是因为随后将其乘以NaN交易规模就没有关系了.我也提前填写了VWAP.
You can use np.where to give you the price from the correct column (bid or ask) depending on the value in the trade column. Note that this gives you the bid price when no trade occurs, but because this is then multiplied by a NaN trade size it won't matter. I also forward filled the VWAP.
volume = df['trade_size']
price = np.where(df['trade'].eq('ask'), df['ask'], df['bid'])
df = df.assign(VWAP=((volume * price).cumsum() / vol.cumsum()).ffill())
>>> df
time bid_size bid ask ask_size trade trade_size phase VWAP
0 2019-01-07 07:45:01.064515 495 152.52 152.54 19 NaN NaN OPEN NaN
1 2019-01-07 07:45:01.110072 31 152.53 152.54 19 NaN NaN OPEN NaN
2 2019-01-07 07:45:01.116596 32 152.53 152.54 19 NaN NaN OPEN NaN
3 2019-01-07 07:45:01.116860 32 152.53 152.54 21 NaN NaN OPEN NaN
4 2019-01-07 07:45:01.116905 34 152.53 152.54 21 NaN NaN OPEN NaN
5 2019-01-07 07:45:01.116982 34 152.53 152.54 31 NaN NaN OPEN NaN
6 2019-01-07 07:45:01.147901 38 152.53 152.54 31 NaN NaN OPEN NaN
7 2019-01-07 07:45:01.189971 38 152.53 152.54 31 ask 15.0 OPEN 152.54
8 2019-01-07 07:45:01.189971 38 152.53 152.54 16 NaN NaN OPEN 152.54
9 2019-01-07 07:45:01.190766 37 152.53 152.54 16 NaN NaN OPEN 152.54
10 2019-01-07 07:45:01.190856 37 152.53 152.54 15 NaN NaN OPEN 152.54
11 2019-01-07 07:45:01.190856 37 152.53 152.54 16 ask 1.0 OPEN 152.54
12 2019-01-07 07:45:01.193938 37 152.53 152.55 108 NaN NaN OPEN 152.54
13 2019-01-07 07:45:01.193938 37 152.53 152.54 15 ask 15.0 OPEN 152.54
14 2019-01-07 07:45:01.194326 2 152.54 152.55 108 NaN NaN OPEN 152.54
15 2019-01-07 07:45:01.194453 2 152.54 152.55 97 NaN NaN OPEN 152.54
16 2019-01-07 07:45:01.194479 6 152.54 152.55 97 NaN NaN OPEN 152.54
17 2019-01-07 07:45:01.194507 19 152.54 152.55 97 NaN NaN OPEN 152.54
18 2019-01-07 07:45:01.194532 19 152.54 152.55 77 NaN NaN OPEN 152.54
19 2019-01-07 07:45:01.194598 19 152.54 152.55 79 NaN NaN OPEN 152.54
这篇关于如何使用带有卖价和买价的 pandas 数据框计算成交量加权平均价(VWAP)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
