Reset Window for EWM in Pandas Dataframe

user_11239666 0
Viewed 206

I’m trying to calculate the ewm using the following pandas dataframe:

Parameter new_file EWM
0 495.56912 0 495.569120
1 494.51257 1 495.505727
2 493.65040 1 495.394407
3 492.95905 1 495.248286
4 492.31470 1 495.072271
5 491.66367 0 491.663670
6 491.05520 1 494.639001
7 490.38733 1 494.383901

What I want is for the window to reset every time the value in the ‘new_file’ column is 0. This dataframe is created from multiple input files and every time the new_file column is 0 it represents the start of a new file. As such I don’t want the EWM to calculate using the last value of the previous input file, I need it to reset.

My code to create the EWM column is as follows:

  df['EWM'] = np.where(df[‘new_file’] ==1, df['Parameter'].ewm(alpha=0.06,adjust=False).mean() , df['Parameter'])

You can probably see from the dataframe extract above that the 6th row that is calculated isn’t what it should be if that were to be start of a new window. It should produce the values below (which I obtained from excel):

Parameter new_file EWM
0 495.56912 0 495.5691
1 494.51257 1 495.5057
2 493.6504 1 495.3944
3 492.95905 1 495.2483
4 492.3147 1 495.0723
5 491.66367 0 491.6637
6 491.0552 1 491.6272
7 490.38733 1 491.5528
pandas exponential window python
1 Answers

We can create a boolean mask to check for the start of new file, then calculate the cumulative sum on this mask to identify the blocks of rows that belong to same file, then group the Parameter column on these blocks and calculate the exponential weighted average

b = df['new_file'].eq(0).cumsum()
df['EWM'] = df['Parameter'].groupby(b, group_keys=False)\
                           .ewm(alpha=0.06, adjust=False).mean()

   Parameter  new_file         EWM
0  495.56912         0  495.569120
1  494.51257         1  495.505727
2  493.65040         1  495.394407
3  492.95905         1  495.248286
4  492.31470         1  495.072271
5  491.66367         0  491.663670
6  491.05520         1  491.627162
7  490.38733         1  491.552772
user_128331660
Related