I have a pandas dataframe as seen below:
Date (DMY) Total 3-day-min
01/03/2013 0.102 -
02/03/2013 0.122 -
03/03/2013 0.047 0.047
04/03/2013 0.050 0.047
05/03/2013 0.052 0.049
06/03/2013 0.049 0.048
07/03/2013 0.048 0.048
08/03/2013 0.048 0.047
The first column is the date, the second is the total value, and the third column is a rolling three day minimum of the values in the [Total] column.
I'm trying to create a new column that is a rolling 3-day mean/average of the [3-day min] column, which I have done using this line:
df['rolling_mean'] = df['3-day-min'].rolling(3).mean()
However, what I'd like to do is introduce a condition where, in the new [rolling_mean] row, there is a check whether the value is higher than the value in the [Total] column. If the mean value is higher, then the value in the [Total] column should be checked instead.
Hope that makes sense. Any help would be appreciated.
Use Series.where
df['rolling_mean'] = df['3-day-min'].rolling(3).mean().where(lambda x: x.le(df['3-day-min']), df['3-day-min'])
Or:
df['rolling_mean'] = df['3-day-min'].rolling(3).mean().mask(lambda x: x.gt(df['3-day-min']), df['3-day-min'])