Conditional Moving Average

Question

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]})

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:

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.

You have a nested for loop giving you O(n)^2. Start by breaking that into two spearate things. — hrokr
@hrokr So how can I avoid for loops? or better say, is there a bulit in function which could handle conditional moving average? — mpy
df['flg'] = np.where(df['qty'] == 0, 0, 1) Is it against your intent to add this conditional column and aggregate the moving average for condition = 1 only on a 10-day time series? — r-beginners

4.Pi.n 4.Pi.n · Accepted Answer · 2020-06-28T06:41:25

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.

Conditional Moving Average

1 Answers