Rolling Average for last 3 years for same week number Python Pandas

Question

I am finding average of the same weeks whenever there is data available for example 201932, using the average of the data from 201632, 201732 and 201832. Example : 2019 is year and 32 is week number

Answer 1

Generalized sample, not tested fully, you can update as per your needs, please pardon the syntax/compile errors

# 1 load your data here
myYearlyWeekAvgList = [[Calendar, WkNumber, France, 0, 201538], [....]]

# 2 initialize variables here
totalSum = 0
movingAves = 0  # track total for that yr
myYear = currentYear #  input, for e.g. 2018

# 3 start totals & averages here
for i, x in enumerate(myYearlyWeekAvgList, 1):
    T = x[i]            # get each row
    if x == currentYear # for e.g. if yr is 2018, then sum value
        totalSum.append(T[3]) # your revenue in 3rd col I guess

        moving_ave = totalSum/i  # your average for that col

Answer 2

import pandas as pd
import datetime as datetime

df['Calender Week'] = pd.to_datetime(df['Calender Week'] + '-0', format = '%Y%W-%w')
df.groupby([df['C'], df['Calender Week'].dt.week])['Revenue'].mean()

This does what you want. First you have to change the 'Calender Week' column to datetime. In the conversion I had to add a '-0' in order for it to convert correctly. All it does is decide if the week starts on a sunday or monday, which really doesnt matter in our case.

In the second line we use groupby. We groupby the country, and the actual calender week (1 through 52) as represented by .dt.week. We look at the Revenue column, and finally take the mean.

The result from my randomly created test data looks like the following:

  C      Calender Week
France        50      16.50
              51      25.00
              52      30.00
USA           50      14.00
              51      13.25
              52      30.50