Resample after rolling mean in pandas-CodePudding

I have a timeseries dataframe with a column volt. I have applied rolling window operation on this dataframe with wondow of 24H. The code to rolling window is

telemetry['datetime'] = pd.to_datetime(telemetry['datetime'])

a = telemetry.set_index('datetime').groupby('machineID').rolling(window=1, freq='24h').mean()['volt']

and the dataframe looks like this after rolling mean

machineID  datetime           
1          2015-01-01 06:00:00    151.919999
           2015-01-01 07:00:00    174.522001
           2015-01-01 08:00:00    146.912822
           2015-01-01 09:00:00    179.530561
           2015-01-01 10:00:00    180.544277
                                     ...    
1000       2016-01-01 02:00:00    174.076779
           2016-01-01 03:00:00    130.151378
           2016-01-01 04:00:00    140.423844
           2016-01-01 05:00:00    160.007424
           2016-01-01 06:00:00    164.590354
Name: volt, Length: 8761000, dtype: float64

The last column is mean of volt.

Now I want to apply resampling for 12H frequency on datetime column.

a = a.resample('12H', on='datetime')

But get this error

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-114-d1e27c8b07ce> in <module>
----> 1 a = a.resample('12h', on='datetime')

/anaconda/envs/azureml_py36/lib/python3.6/site-packages/pandas/core/generic.py in resample(self, rule, how, axis, fill_method, closed, label, convention, kind, loffset, limit, base, on, level)
   8447             base=base,
   8448             key=on,
-> 8449             level=level,
   8450         )
   8451         return _maybe_process_deprecations(

/anaconda/envs/azureml_py36/lib/python3.6/site-packages/pandas/core/resample.py in resample(obj, kind, **kwds)
   1304     """
   1305     tg = TimeGrouper(**kwds)
-> 1306     return tg._get_resampler(obj, kind=kind)
   1307 
   1308 

/anaconda/envs/azureml_py36/lib/python3.6/site-packages/pandas/core/resample.py in _get_resampler(self, obj, kind)
   1428 
   1429         """
-> 1430         self._set_grouper(obj)
   1431 
   1432         ax = self.ax

/anaconda/envs/azureml_py36/lib/python3.6/site-packages/pandas/core/groupby/grouper.py in _set_grouper(self, obj, sort)
    171             else:
    172                 if key not in obj._info_axis:
--> 173                     raise KeyError("The grouper name {0} is not found".format(key))
    174                 ax = Index(obj[key], name=key)
    175 

KeyError: 'The grouper name datetime is not found'

I am not getting how to apply resampling after rolling window mean.

CodePudding user response：

Use level instead on and add aggregate function:

a = a.resample('12H', level='datetime').mean()

EDIT:

a = (telemetry.set_index('datetime')
              .groupby('machineID')
              .rolling(window=1, freq='24h')['volt']
              .mean())


b = a.groupby(['machineID', pd.Grouper(freq='12H', level='datetime')]).mean()