apologies from creating what appears to be a duplicate of this question. I have a dataframe that is shaped more or less like the one below:
df_lenght = 240
df = pd.DataFrame(np.random.randn(df_lenght,2), columns=['a','b'] )
df['datetime'] = pd.date_range('23/06/2017', periods=df_lenght, freq='H')
unique_jobs = ['job1','job2','job3',]
job_id = [unique_jobs for i in range (1, int((df_lenght/len(unique_jobs))+1) ,1) ]
df['job_id'] = sorted( [val for sublist in job_id for val in sublist] )
df.set_index(['job_id','datetime'], append=True, inplace=True)
print(df[:5])
returns:
a b
job_id datetime
0 job1 2017-06-23 00:00:00 -0.067011 -0.516382
1 job1 2017-06-23 01:00:00 -0.174199 0.068693
2 job1 2017-06-23 02:00:00 -1.227568 -0.103878
3 job1 2017-06-23 03:00:00 -0.847565 -0.345161
4 job1 2017-06-23 04:00:00 0.028852 3.111738
I will need to resample df['a']
to derive a daily rolling mean, i.e. apply a .resample('D').mean().rolling(window=2).mean()
.
I have tried two methods:
1 - unstacking and stacking, as recommended here
df.unstack('job_id','datetime').resample('D').mean().rolling(window=2).mean().stack('job_id', 'datetime')
this returns an error
2 - using pd.Grouper
, as recommended here
level_values = df.index.get_level_values
result = df.groupby( [ level_values(i) for i in [0,1] ] + [ pd.Grouper(freq='D', level=2) ] ).mean().rolling(window=2).mean()
this does not return an error but it does not seem to resample/group the df appropriately. Result seems to contain hourly data points, rather than daily:
print(result[:5])
a b
job_id datetime
0 job1 2017-06-23 NaN NaN
1 job1 2017-06-23 0.831609 1.348970
2 job1 2017-06-23 -0.560047 1.063316
3 job1 2017-06-23 -0.641936 -0.199189
4 job1 2017-06-23 0.254402 -0.328190
See Question&Answers more detail:
os