Pandas : groupby function + rolling mean + reset index returning Nan

user_9602387 0
Viewed 326

I have the following df:

     Symbol       Time   Close  Sessions     DR  ADR
0       AMD 2019-11-18   39.88       387   1.39  NaN
1       AMD 2019-11-19   41.29       388   2.10  NaN
2       AMD 2019-11-20   40.98       389   1.68  NaN
3       AMD 2019-11-21   39.52       390   2.07  NaN
4       AMD 2019-11-22   39.15       391   1.70  NaN
...     ...        ...     ...       ...    ...  ...
1600   UPST 2021-09-03  247.29      1597  14.13  NaN
1601   UPST 2021-09-07  262.70      1598  21.90  NaN
1602   UPST 2021-09-08  274.33      1599  15.64  NaN
1603   UPST 2021-09-09  289.60      1600  29.16  NaN
1604   UPST 2021-09-10  270.46      1605  25.98  NaN

I would like to get the rolling 20 day average of DR in the ADR column.

My code is:

    df_day['ADR'] = df_day.groupby('Sessions')['DR'].rolling(20).mean().reset_index(0,drop=True)

Returns:

     Symbol       Time   Close  Sessions  DR  ADR
0       AMD 2019-11-18   39.88       NaN NaN  NaN
1       AMD 2019-11-19   41.29       NaN NaN  NaN
2       AMD 2019-11-20   40.98       NaN NaN  NaN
3       AMD 2019-11-21   39.52       NaN NaN  NaN
4       AMD 2019-11-22   39.15       NaN NaN  NaN
...     ...        ...     ...       ...  ..  ...
1600   UPST 2021-09-03  247.29       NaN NaN  NaN
1601   UPST 2021-09-07  262.70       NaN NaN  NaN
1602   UPST 2021-09-08  274.33       NaN NaN  NaN
1603   UPST 2021-09-09  289.60       NaN NaN  NaN
1604   UPST 2021-09-10  270.46       NaN NaN  NaN
pandas-groupby pandas python
2 Answers

Problem is you groupby by column with less like 20+ unique values per groups in column Sessions, so ouput is always NaN.

I think you need groupby by Symbol instead column Sessions, but also need groups with 20+ values.

df_day['ADR'] = df_day.groupby('Symbol')['DR'].rolling(20).mean().reset_index(0,drop=True)

Solution with align by 20D is create first DatetimeIndex:

df_day['Time'] = pd.to_datetime(df_day['Time'])

df_day = df_day.set_index('Time')
df_day['ADR'] = df_day.groupby('Symbol')['DR'].rolling(20, freq='D').mean().reset_index(0,drop=True)

EDIT:

Test with 3days:

df_day['ADR'] = df_day.groupby('Symbol')['DR'].rolling(3).mean().reset_index(0,drop=True)
print (df_day)
     Symbol        Time   Close  Sessions     DR        ADR
0       AMD  2019-11-18   39.88       387   1.39        NaN
1       AMD  2019-11-19   41.29       388   2.10        NaN
2       AMD  2019-11-20   40.98       389   1.68   1.723333
3       AMD  2019-11-21   39.52       390   2.07   1.950000
4       AMD  2019-11-22   39.15       391   1.70   1.816667
1600   UPST  2021-09-03  247.29      1597  14.13        NaN
1601   UPST  2021-09-07  262.70      1598  21.90        NaN
1602   UPST  2021-09-08  274.33      1599  15.64  17.223333
1603   UPST  2021-09-09  289.60      1600  29.16  22.233333
1604   UPST  2021-09-10  270.46      1605  25.98  23.593333

df_day['Time'] = pd.to_datetime(df_day['Time'])

df_day = df_day.set_index('Time')
df_day['ADR'] = df_day.groupby('Symbol')['DR'].rolling(3, freq='D').mean().reset_index(0,drop=True)
print (df_day)
           Symbol   Close  Sessions     DR        ADR
Time                                                 
2019-11-18    AMD   39.88       387   1.39        NaN
2019-11-19    AMD   41.29       388   2.10        NaN
2019-11-20    AMD   40.98       389   1.68   1.723333
2019-11-21    AMD   39.52       390   2.07   1.950000
2019-11-22    AMD   39.15       391   1.70   1.816667
2021-09-03   UPST  247.29      1597  14.13        NaN
2021-09-07   UPST  262.70      1598  21.90        NaN
2021-09-08   UPST  274.33      1599  15.64  17.223333
2021-09-09   UPST  289.60      1600  29.16  22.233333
2021-09-10   UPST  270.46      1605  25.98  23.593333
user_29010020

Try this:

df_day['ADR'] = df_day.groupby('Sessions').rolling(20, on='DR').mean().astype(float).reset_index(0, drop=True)
user_87083640
Related