Skip to content
Advertisement

Pandas Aggregate Daily Data to Monthly Timeseries

I have a time series that looks like this (below)

And I want to resample it monthly, so it has 2019-10 is equal to the average of all the values of october, November is the average of all the PTS values for November, etc.

However, when i use the pd.resample(‘M’).mean() method, if the final day for each month does not have a value, it fills in a Nan in my data frame. How do I solve this?

Date        PTS    
2019-10-23  14.0
2019-10-26  14.0
2019-10-27   8.0
2019-10-29  29.0
2019-10-31  17.0
2019-11-03  12.0
2019-11-05   2.0
2019-11-07  15.0
2019-11-08   7.0
2019-11-14  16.0
2019-11-16  12.0
2019-11-20  22.0
2019-11-22   9.0
2019-11-23  20.0
2019-11-25  18.0```

Answer

Would this work?

pd.resample('M').mean().dropna()
Advertisement