How to find the average of previous sales at each time in python

Question

I have a csv file with four columns: date, wholesaler, product, and sales. I am looking for finding average of previous sales for each Product and Wholesaler combination at each date. It means what is the average previous sales of product 'A' at wholesaler 'B' at time 'C'.

For instance we know sales of product 'A' at wholesaler 'B' at Jan, Apr, May, Aug that are 100, 200, 300, 400 respectively. Let assume we do not have any record before Jan. So the average of previous sale of product 'A' in wholesaler 'B' at Apr is equal to 100/1, and at May is equal to (200+100)/2 and at Aug is (300+200+100)/3.

The following table shows my data:

date    wholesaler product  sales
12/31/2012  53929  UPE54     4
12/31/2012  13131  UPE55     1
2/23/2013   13131  UPE55  1156
4/24/2013   13131  UPE55     1
12/1/2013   83389  UPE54     9
12/17/2013  83389  UPE54     1
12/18/2013  52237  UPE54     9
12/19/2013  53929  UME24     1
12/31/2013  82204  UPE55     9
12/31/2013  11209  UME24     4
12/31/2013  52237  UPE54     1

Now I am using this code:

df = pd.read_csv('Sample.csv',index_col='date')
df2 = df.groupby(['wholesaler','product'])['sales'].mean()

That gives an average sales for each wholesaler-product while I am looking for average of previous sales at each date.

wholesaler product   avg sales
    11209  UME24      4.00
    13131  UPE55    713.00
    22423  UME24      1.00
    24302  U4E16    121.00 

Thank you for your help!

Answer 1

It was quite tricky for me but works anyhow. Expecting more elegant solution from others.

import pandas as pd
import datetime

dateparse = lambda x: pd.datetime.strptime(x, '%m/%d/%Y')
df = pd.read_csv('Sample.csv',index_col='date', parse_dates=[0], date_parser=dateparse)

expd_gb = df.reset_index().groupby(['wholesaler', 'product'])['sales'].apply(pd.Series.expanding)
idx = df.reset_index().groupby(['wholesaler', 'product', 'date'])['sales'].count().index

cnct = pd.concat([expd_gb.iloc[n].mean().shift(1) for n in range(len(expd_gb))])
cnct.index = idx

cnct.to_csv('TotalAvg.csv')

Result,

wholesaler  product  date      
11209       UME24    2013-12-31     NaN
13131       UPE55    2012-12-31     NaN
                     2013-02-23     1.0
                     2013-04-24     578.5
52237       UPE54    2013-12-18     NaN
                     2013-12-31     9.0
53929       UME24    2013-12-19     NaN
            UPE54    2012-12-31     NaN
82204       UPE55    2012-12-31     NaN
83389       UPE54    2013-12-01     NaN
                     2013-12-17     9.0