DataFrame calculating average purchase price

Question

I have a dataframe with two columns: quantity and price.

df = pd.DataFrame([
[ 1, 5],
[-1, 6],
[ 2, 3],
[-1, 2],
[-1, 4],
[ 1, 2],
[ 1, 3],
[ 1, 4],
[-2, 5]], columns=['quantity', 'price'])

df['amount'] = df['quantity'] * df['price']
df['cum_qty'] = df['quantity'].cumsum()

I have added two new columns amount and cum_qty (cumulative quantity). Now dataframe looks like this (positive quantity represents buys, negative quantity represents sells):

   quantity  price  amount  cum_qty
0         1      5       5        1
1        -1      6      -6        0
2         2      3       6        2
3        -1      2      -2        1
4        -1      4      -4        0
5         1      2       2        1
6         1      3       3        2
7         1      4       4        3
8        -2      5     -10        1

I would like to calculate average buy price.

Every time when cum_qty = 0, qantity and amount should be reset to zero. So we are looking at rows with index = [5,6,7]. For each row one item is bought at prices 2, 3 and 4, which means I have on stock 3 each at average price of 3 [(2 + 3 + 4)/3].

After sell at index = 8 has happened (sell transactions doesn't change buy price), I will have one each at price 3.

So, basically, I have to divide all cumulative buy amounts by cumulative quantities from last cumulative quantity that is not zero.

How to calculate buy on hand as result of all transactions with pandas DataFrame?

Answer 1

df[df['cum_qty'].map(lambda x: x == 0)].index

will give you at which rows you have a cum_qty of 0

df[df['cum_qty'].map(lambda x: x == 0)].index.max()

gives you the last row with 0 cum_qty

start = df[df['cum_qty'].map(lambda x: x == 0)].index.max() + 1
end = len(df) - 1

gives you the start and end row numbers that are the range you are referring to

df['price'][start:end].sum() / df['quantity'][start:end].sum()

gives you the answer you did in the example you gave

If you want to know this value for each occurrence of cum_qty 0, then you can apply the start/end logic by using the index of each (the result of my first line of code).

Answer 2

Base on my understanding , you need buy price for each trading circle, then you can try this.

df['new_index'] = df.cum_qty.eq(0).shift().cumsum().fillna(0.)#give back the group id for each trading circle.*
df=df.loc[df.quantity>0]# kick out the selling action
df.groupby('new_index').apply(lambda x:(x.amount.sum()/x.quantity.sum()))

new_index
0.0    5.0# 1st ave price 5
1.0    3.0# 2nd ave price 3
2.0    3.0# 3nd ave price 3 ps: this circle no end , your position still pos 1
dtype: float64

EDIT1 for you additional requirement

DF=df.groupby('new_index',as_index=False).apply(lambda x : x.amount.cumsum()/ x.cum_qty).reset_index()
DF.columns=['Index','AvePrice']
DF.index=DF.level_1
DF.drop(['level_0',  'level_1'],axis=1,inplace=True)
pd.concat([df,DF],axis=1)

Out[572]: 
         quantity  price  amount  cum_qty  new_index    0
level_1                                                  
0               1      5       5        1        0.0  5.0
2               2      3       6        2        1.0  3.0
5               1      2       2        1        2.0  2.0
6               1      3       3        2        2.0  2.5
7               1      4       4        3        2.0  3.0

Answer 3

Here is a different solution using a loop:

import pandas as pd
import numpy as np

# Original data
df = pd.DataFrame({
    'quantity': [ 1, -1,  2, -1, -1,  1,  1,  1, -2],
    'price': [5, 6, 3, 2, 4, 2, 3, 4, 5]
})

# Process the data and add the new columns
df['amount'] = df['quantity'] * df['price']
df['cum_qty'] = df['quantity'].cumsum()
df['prev_cum_qty'] = df['cum_qty'].shift(1, fill_value=0)
df['average_price'] = np.nan
for i, row in df.iterrows():
    if row['quantity'] > 0:
        df.iloc[i, df.columns == 'average_price' ] = (
            row['amount'] +
            df['average_price'].shift(1, fill_value=df['price'][0])[i] *
            df['prev_cum_qty'][i]
        )/df['cum_qty'][i]
    else:
        df.iloc[i, df.columns == 'average_price' ] = df['average_price'][i-1]
df.drop('prev_cum_qty', axis=1)

An advantage of this approach is that it will also work if there are new buys before the cum_qty gets to zero. As an example, suppose there was a new buy of 5 at the price of 3, that is, run the following line before processing the data:

# Add more data, exemplifying a different situation
df = df.append({'quantity': 5, 'price': 3}, ignore_index=True)

I would expect the following result:

   quantity  price  amount  cum_qty  average_price
0         1      5       5        1            5.0
1        -1      6      -6        0            5.0
2         2      3       6        2            3.0
3        -1      2      -2        1            3.0
4        -1      4      -4        0            3.0
5         1      2       2        1            2.0
6         1      3       3        2            2.5
7         1      4       4        3            3.0
8        -2      5     -10        1            3.0
9         5      3      15        6            3.0 # Not 4.0

That is, since there was still 1 item bought at the price 3, the cum_qty is now 6, and the average price is still 3.