[FIXED] How to calculate Volume Weighted Average Price (VWAP) using a pandas dataframe with ask and bid price?

Issue

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   

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.

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']?

My idea is to use np.cumsum().

Solution

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

Answered By - Alexander