Pandas: Dataframe Upsampling Fails for First Period

user_4518857 0
Viewed 130

I have a series of daily meteo data, and I would like to replace each daily value with the mean value for the month the day belongs to. To achieve this, I want to first downsample the dataframe to monthly mean, upsample it to daily frequency again. All this using Pandas 1.0.1.

The dataframe looks like this:

dframe = 
2001-01-01  23.00000
2001-01-02  19.18034
2001-01-03  9.18034
2001-01-04  0.00000
2001-01-05  0.00000
2001-01-06  0.00000
2001-01-07  0.00000
2001-01-08  0.00000
2001-01-09  9.18034
2001-01-10  19.18034
2001-02-01  20.18034
2001-02-02  10.18034
2001-02-03  0.00000
2001-02-04  0.00000
2001-02-05  0.00000
2001-02-06  0.00000
2001-02-07  0.00000
2001-02-08  10.18034
2001-02-09  20.18034
2001-02-10  24.00000

After downsampling, things look alright (values may not be matching, these are dummy numbers):

means = dframe.resample(rule = 'M').mean()

means = 
2001-01-31   8.456906
2001-02-28   7.499419

But the subsequent upsampling does not work as I would like it to:

segmented = means.resample(rule = 'D').bfill()

segmented = 
2001-01-31   8.456906
2001-02-01   7.499419
2001-02-02   7.499419
2001-02-03   7.499419
2001-02-04   7.499419
...               ...

The first period (January 2001) is not upsampled, while the second is.

I tried all combinations with the arguments closed and label and loffset and with bfill() and ffill(), but to no avail; sometimes it's the first month to be wrong and sometiems the last, but there's always a wrong one.

Help would be appreciated.

pandas dataframe python
3 Answers

Maybe you can use groupby, using pd.Grouper to do it per month and tranform like:

segmented = dframe.groupby(pd.Grouper(level=0, freq='M')).transform('mean')

assuming the dates are in index, you would have all the dates from dframe and the mean over the month associated to each of them

user_92747320

This works:

pd.Series(
    [1,2,3,4],index=pd.date_range('2018-01-30', periods=4, freq='D')
).resample(rule = 'M').transform(lambda x: x.mean())

This issue with your approach is that when you made means, the range of the indexes is no longer inclusive of all the dates you originally had. transform (or apply) is a good way to get the same number of rows back as you put in.

user_50896440

Thanks, both answers work perfectly.

" This issue with your approach is that when you made means, the range of the indexes is no longer inclusive of all the dates you originally had "

I suspected something of the sort, but I didn't know how to overcome it.

user_45188570
Related