'pandas fill NA but not all based on recent past record

I have a dataframe like as shown below

stud_name   act_qtr year    yr_qty  qtr mov_avg_full    mov_avg_2qtr_min_period
0   ABC Q2  2014    2014Q2  NaN NaN NaN
1   ABC Q1  2016    2016Q1  Q1  13.0    14.5
2   ABC Q4  2016    2016Q4  NaN NaN NaN
3   ABC Q4  2017    2017Q4  NaN NaN NaN
4   ABC Q4  2020    2020Q4  NaN NaN NaN

OP = pd.read_clipboard()

stud_name   qtr year    t_score p_score yr_qty  mov_avg_full    mov_avg_2qtr_min_period
0   ABC Q1  2014    10  11  2014Q1  10.000000   10.0
1   ABC Q1  2015    11  32  2015Q1  10.500000   10.5
2   ABC Q2  2015    13  45  2015Q2  11.333333   12.0
3   ABC Q3  2015    15  32  2015Q3  12.250000   14.0
4   ABC Q4  2015    17  21  2015Q4  13.200000   16.0
5   ABC Q1  2016    12  56  2016Q1  13.000000   14.5
6   ABC Q2  2017    312 87  2017Q2  55.714286   162.0
7   ABC Q3  2018    24  90  2018Q3  51.750000   168.0

df = pd.read_clipboard()

I would like to fillna() based on below logic

For ex: let's take stud_name = ABC. He has multipple NA records. Let's take his NA for 2020Q4. To fill that, we pick the latest record from df for stud_name=ABC before 2020Q4 (which is 2018Q3). Similarly, if we take stud_name = ABC. His another NA record is for 2014Q2. We pick the latest (prior) record from df for stud_name=ABC before 2014Q2 (which is 2014Q1). We need to sort based on yearqty values to get the latest (prior) record correctly

We need to do this for each stud_name and for a big dataset

So, we fillna in mov_avg_full and mov_avg_2qtr_min_period

If there are no previous records to look at in df dataframe, leave NA as it is

I was trying something like below but it doesn't work and incorrect

Filled = OP.merge(df,on=['stud_name'],how='left')
filled.sort_values(['year','Qty'],inplace=True)
filled['mov_avg_full'].fillna(Filled.groupby('stud_name']['mov_avg_full'].shift())
filled['mov_avg_2qtr_min_period'].fillna(Filled .groupby('stud_name']['mov_avg_2qtr_min_period'].shift())

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