Conditional Moving Average

user_8794465 0
Viewed 575

I have a dataframe as below:

data = pd.DataFrame({'Date':['2020-06-17','2020-06-18','2020-06-19','2020-06-20','2020-06-21','2020-06-22','2020-06-23','2020-06-24','2020-06-25','2020-06-26','2020-06-27','2020-06-17','2020-06-18','2020-06-19','2020-06-20','2020-06-21','2020-06-22','2020-06-23','2020-06-24','2020-06-25','2020-06-26','2020-06-27'],
                     'Store': ['a','a','a','a','a','a','a','a','a','a','a','b','b','b','b','b','b','b','b','b','b','b'],
                     'value':[1,2,0,5,0,2,0,8,1,0,1,4,0,0,2,0,3,6,0,9,2,0],
                      'qty':[1,0,1,4,2,4,6,0,3,0,5,8,0,0,1,0,1,9,3,0,4,1]})

data

I want to calculate the average of "value" column for each store with a window of length 10, but ignoring the 0 qtys. It means that in the window of length 10, the records which have positive qty should be considered in calculating the average of value. The desired data would be as follow:

enter image description here

I wrote a solution as bellow, however since my original dataframe has 21 million records and I have almost 2 million stores and I want to calculate this moving average for next 15 days, my solution runs for years and it is totally impractical.

for s in range(3):
    adding_date = datetime.date.today() + datetime.timedelta(days = s)
    start_date = adding_date - datetime.timedelta(days = 10)
    adding_date = adding_date.strftime('%Y-%m-%d')
    start_date = start_date.strftime('%Y-%m-%d')
    sub_data = data[(data.Date < adding_date) & (data.Date >= start_date)]
    for index, group in sub_data.groupby(['Store']):
        if group.qty.sum() != 0:
            ma = group[group.qty != 0]['value'].mean()
            row = pd.DataFrame({'Date':[adding_date], 'Store': index[0], 'value': [ma], 'qty': 1})
            data = pd.concat((data,row), ignore_index = True)
        else:
            ma = 0
            row = pd.DataFrame({'Date':[adding_date], 'Store': index[0], 'value': [ma],'qty': 1})
            data = pd.concat((data,row), ignore_index = True)

So any help to improve my code would be awesome.

pandas moving-average python
1 Answers
w_size = 10

sub_df = df.query(f'qty != {0}')

sub_df.ewm(com = w_size).mean() # weighted average 

sub_df.rolling(window=w_size).mean() # average (over window size)

For just excluding some values ​​from calculations, if value = 0, then exclude row with zeros.

df[df['qty'] == value] = 0

df.ewm(com = w_size).mean() # weighted average 

df.rolling(window=w_size).mean() # average (over window size)

By setting entirely row, which has zeros qty , moving average, just sum zeros for those rows, which same as do nothing (condtion), but divided by the window size anyway, if you want exact mean (divide by the number of non zero) use the first one.

user_112731240
Related