How to compare average value from recent 2 weeks to average of previous 6 weeks by group

I understood that your issue is that you don’t manage to get the rolling average based on the category “merchant”. If that is it, one way would be this one:

  1. First groupby with resample Write the resample, the same way that you already had, I just added a ffill (fill down to avoid nans if you don’t have one of the datapoints):
    df.index =
    grouped = df.groupby(['merchant']).resample('W')[['amount']].mean()
    grouped.ffill(axis = 0,inplace = True)

  2. Apply groupby again, with rolling on the column “amount” for 2 and 6 weeks. Note that there will be nans in this example because for example to calculate the rolling of 2 weeks if will need at least the previous week:

grouped = grouped.reset_index()
grouped.index =
mean_2 = grouped.groupby(["merchant"]).rolling(2).amount.mean()
mean_6 = grouped.groupby(["merchant"]).rolling(6).amount.mean()

  1. Concatenate series
    result.columns = ["mean2","mean6"]

The result is like this:
enter image description here

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top