Pandas rolling average window for dates excluding row-CodePudding

df = pd.DataFrame(
    {"date": [pd.Timestamp("2022-01-01"), pd.Timestamp("2022-01-01"), pd.Timestamp("2022-01-01"), pd.Timestamp("2022-01-03"), pd.Timestamp("2022-01-05")],
    "numbers": [1,2,3,4,5]
    }
)

If I have the following df and I would like to get the rolling mean for the values of numbers that are before each rows date column, how would I do that?

I know I can do

df["av"] = df.shift(1).rolling(window=3).mean()

but this does not shift dynamically so it includes today.

My expected output for the new av column for a 3 day window over the sample df would be

    date    numbers av
0   2022-01-01  1   NaN
1   2022-01-01  2   NaN
2   2022-01-01  3   NaN
3   2022-01-03  4   2.0
4   2022-01-03  7   2.0
5   2022-01-05  5   5.5

CodePudding user response：

I think you need rolling means per unique dates with add excluded dates shifted by 1 day.

Here is used alternative solution for means by definition - sum / count.

df1 = (df.groupby('date')['numbers']
         .agg(['sum','size'])
         .asfreq('d', fill_value=0)
         .rolling(window=3, min_periods=1)
         .sum())

df['av'] = df['date'].map(df1['sum'].div(df1['size']).shift())
print (df)
        date  numbers   av
0 2022-01-01        1  NaN
1 2022-01-01        2  NaN
2 2022-01-01        3  NaN
3 2022-01-03        4  2.0
4 2022-01-03        7  2.0
5 2022-01-05        5  5.5

Explanation:

First are aggregate sum and size for count:

print (df.groupby('date')['numbers'].agg(['sum','size']))
            sum  size
date                 
2022-01-01    6     3
2022-01-03   11     2
2022-01-05    5     1

Added missing consecutives dates by DataFrame.asfreq:

print (df.groupby('date')['numbers']
         .agg(['sum','size'])
         .asfreq('d', fill_value=0))
            sum  size
date                 
2022-01-01    6     3
2022-01-02    0     0
2022-01-03   11     2
2022-01-04    0     0
2022-01-05    5     1

Use rolling per 3 days by sum:

df1 = (df.groupby('date')['numbers']
         .agg(['sum','size'])
         .asfreq('d', fill_value=0)
         .rolling(window=3, min_periods=1)
         .sum())
print (df1)
             sum  size
date                  
2022-01-01   6.0   3.0
2022-01-02   6.0   3.0
2022-01-03  17.0   5.0
2022-01-04  11.0   2.0
2022-01-05  16.0   3.0

Divide columns from df1 for averages:

print (df1['sum'].div(df1['size']))
date
2022-01-01    2.000000
2022-01-02    2.000000
2022-01-03    3.400000
2022-01-04    5.500000
2022-01-05    5.333333
Freq: D, dtype: float64

Exclude day by Series.shift by one day:

print (df1['sum'].div(df1['size']).shift())
date
2022-01-01    NaN
2022-01-02    2.0
2022-01-03    2.0
2022-01-04    3.4
2022-01-05    5.5
Freq: D, dtype: float64

Last for new column use Series.map:

print (df['date'].map(df1['sum'].div(df1['size']).shift()))
0    NaN
1    NaN
2    NaN
3    2.0
4    2.0
5    5.5
Name: date, dtype: float64