Monthly data summary at daily timestep in Pandas

Asked Apr 16 '19 at 00:54

Active Apr 16 '19 at 00:54

Viewed 159 times

I have daily time series data in a Pandas dataframe - I'd like to get monthly statistics, but preserve the daily timestep.

df = pd.Series(np.arange(365), index=pd.date_range("1900-01-01", periods=365)).to_frame('val')
df_mean = df.resample('M').std().add_suffix('_mth_mean')

In this instance, df_mean has a row for each month:

            val_mth_mean
1900-01-31          15.0
1900-02-28          44.5
1900-03-31          74.0

I'd like those monthly values to be daily, ideally in the same dataframe:

            val    val_mth_mean
1900-01-01    0            15.0
1900-01-02    1            15.0 
...
1900-01-31   31            15.0
1900-02-01   32            44.5
1900-02-02   33            44.5

Looking for a Pythonic/Pandonic solution without iteration.

asked Apr 16 '19 at 00:54

triphook

2,915
3
25
34

2

You are looking for `resample` + `transform`: `df['val_mth_mean'] = df.resample('M').transform('mean')` – ALollz Apr 16 '19 at 01:03
1

Relevant post (with sum instead of mean): https://stackoverflow.com/questions/30244952/python-pandas-create-new-column-with-groupby-sum – ALollz Apr 16 '19 at 01:06
Do you need `val` column too? or only `val_mth_mean` with daily. – ResidentSleeper Apr 16 '19 at 01:52
I was able to do what I needed (add a column while retaining val) with ALollz's comment. – triphook Apr 16 '19 at 20:25

Monthly data summary at daily timestep in Pandas

0 Answers0