0

Suppose I have this dataframe and I and I am asked to calculate weighted average. I looked at this solution: How to calculate vwap (volume weighted average price) using groupby and apply?

But this does not exactly answer my question.

    ticker  date       quantity     price        holdings
    VLO     2014-02-19  5500.0      50.852800    5500
    VLO     2016-03-29  -1350.0     65.138200    4150
    VLO     2017-04-21   900.0      64.91643     5050
    VLO     2017-11-27  -985.0      81.886800    4065
  1. Quantity can be positive, which means it is a buy, If negative, it is sell.
  2. wapp = previous wapp if quantity is negative.
  3. Otherwise, wapp = (quantity*price) + (previous_holdings *previous_wapp)/ current_holdings

So, this is the correct wapp for the above dataframe:

    ticker  date       quantity     price        holdings   wapp
    VLO     2014-02-19  5500.0      50.852800    5500       50.8528
    VLO     2016-03-29  -1350.0     65.138200    4150       50.8528
    VLO     2017-04-21   900.0      64.91643     5050       53.35919
    VLO     2017-11-27  -985.0      81.886800    4065       53.35919

So, when there is a sell: no wapp change but holdings do change. Subsequently, the next wapp gets affected.

Is there a way to do so in pandas, or should I resort to python?

Thanks a lot for your help.

sanrio
  • 1
  • I don't think you can do "recursive" transformations like this with pure numpy/pandas – maxymoo Feb 12 '18 at 22:02
  • Possible duplicate of [Is a "for" loop necessary if elements of the a numpy vector are dependent upon the previous element?](https://stackoverflow.com/questions/4407984/is-a-for-loop-necessary-if-elements-of-the-a-numpy-vector-are-dependent-upon-t) – maxymoo Feb 12 '18 at 22:02
  • you can look at something like numba or cython to speed up the loop – maxymoo Feb 12 '18 at 22:03
  • Thanks maxymoo. I guess will have to iterate over it to get the result I want. That is what I thought originally, but wanted to clarify. – sanrio Feb 13 '18 at 18:22

0 Answers0