DataFrame calculating average purchase price

user_3225309 0
Viewed 5260

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?

pandas dataframe python
3 Answers

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.

user_20626630
Related