'Merge and compute moving average based on full past and also window

I have 2 dataframes like as shown below

df = pd.DataFrame(
    {'stud_name' : ['ABC', 'ABC','ABC','ABC', 
                  'DEF'],
     'ques_date' : ['13/11/2020', '10/1/2018','11/11/2017', '27/03/2016', 
                '13/05/2010']})

df_score = pd.DataFrame(
    {'stud_name' : ['ABC', 'ABC','ABC','ABC','ABC','ABC','ABC','DEF','DEF','DEF','DEF'],
     'qtr':['Q1','Q2','Q3','Q4','Q1','Q2','Q3','Q3','Q4','Q2','Q4'],
     'year' : [2015,2015,2015,2015,2016,2017,2017,2017,2017,2018,2017],
     't_score':[11,13,15,17,12,312,14,15,18,43,32],
     'p_score':[32,45,32,21,56,87,32,786,213,32,11]})

I would like to do the below

a) For each stud_name, compute two moving average (of t_score) columns as output

mov_avg_full = use all past data of a stud_name. (all past quarters info from df_score) mov_avg_2qtr = use data from past 2 quarters (only past 2 quarters info from df_score)

ex: if the year is 2020 and it is 3rd qtr, I would like to compute moving average of all past data (before 2020 Q3) and moving average of last 2 quarters (2020 Q1 and 2020 Q2)

If there is no past data for a specific stud_name, we just put NA (ex: DEF has no past data in df_score)

I tried the below

df['ques_date'] = pd.to_datetime(df['ques_date'], dayfirst=True)
df.sort_values(by=['stud_name','ques_date'],inplace=True)
df['act_qtr'] = df['ques_date'].dt.to_period('Q').dt.strftime('Q%q')
df['year'] = df['ques_date'].dt.year
df_score.sort_values(by=['year','qtr'],inplace=True)
df_full = df.merge(df_score,on=['stud_name'])
df_full['mov_avg_2qtr'] = df_full['t_score'].rolling(2).mean() # this is incorrect

I expect my output to be like as shown below

enter image description here



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source