Group by and resample in specified time interval in Python

Question

How can I resample for df below at 1min frequency using forward fill ffill and backward fill bfill for each id(using groupby('id')) for the time interval 2017-01-01 00:00:00 and 2017-01-05 00:00:00, ie., the first timestamp is 2017-01-01 00:00:00 and the last timestamp is 2017-01-05 00:00:00?

          id   timestamp                data  

      1    1   2017-01-02 13:14:53.040  10.0
      2    1   2017-01-02 16:04:43.240  11.0  
                           ...
      4    2   2017-01-02 15:22:06.540   1.0  
      5    2   2017-01-03 13:55:34.240   2.0  
                           ...

I tried:

pd.DataFrame(df.set_index('timestamp').groupby('id', sort=True)['data'].resample('1min').ffill().bfill())

but this does not specify the desired time interval of 2017-01-01 00:00:00 -2017-01-05 00:00:00.

Then I tried:


r = pd.to_datetime(pd.date_range(start='2017-01-01 00:00:00', end='2017-01-05 00:00:00', freq='1min'))

pd.DataFrame(df.reset_index().set_index('timestamp').groupby('id', sort=True).reindex(r)['data'].resample('1min').ffill().bfill())

and caught error:


---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-202-344bb3281e2e> in <module>
      5 r = pd.to_datetime(pd.date_range(start='2017-01-01 00:00:00', end='2017-01-05 00:00:00', freq='1min'))
      6 
----> 7 pd.DataFrame(df.reset_index().set_index('timestamp').groupby('id', sort=True).reindex(r)['data'].resample('1min').ffill().bfill())
      8 

~\AppData\Roaming\Python\Python38\site-packages\pandas\core\groupby\groupby.py in __getattr__(self, attr)
    701             return self[attr]
    702 
--> 703         raise AttributeError(
    704             f"'{type(self).__name__}' object has no attribute '{attr}'"
    705         )

AttributeError: 'DataFrameGroupBy' object has no attribute 'reindex'

Update:

For sample data df_sub_data:

{'timestamp': {781681: Timestamp('2021-03-11 17:17:19.920000'),
  1036818: Timestamp('2021-03-11 17:59:56.040000'),
  677981: Timestamp('2021-03-11 19:25:59.090000')},
 'data': {781681: 25.0, 1036818: 24.0, 677981: 23.0},
 'id': {781681: 100, 1036818: 100, 677981: 100}}

I tried:

start = datetime.datetime.now() - pd.to_timedelta("7day")
end = datetime.datetime.now()


def f(x):
    r = pd.date_range(start=start, end=end, freq='1min')
    return x.reindex(r, method='ffill').bfill()

df_sub = (df_sub_data.reset_index()
        .set_index('timestamp')
        .groupby(['index','id'], sort=False)['data']
        .apply(f)
        .reset_index(level=0, drop=True)
        .rename_axis(['id','timestamp'])
        .reset_index()
        )

and it returned a dataframe of shape (30243, 3)

I am wondering, shouldn't I expect the shape to be (10080, 3) which is given by 7 x 24 x 60 the number of minute in 7 days? The sample data consists of data of one id == 100.

jezrael · Accepted Answer · 2021-03-15T08:37:38.170

1

You can use cusom lambda function with reindex by date_range:

def f(x):
    r = pd.date_range(start=start, end=end, freq='1min')
    return x.reindex(r, method='ffill').bfill()

df_sub = (df_sub_data
        .set_index('timestamp')
        .groupby('id', sort=False)['data']
        .apply(f)
        .rename_axis(['id','timestamp'])
        .reset_index()
        )
print (df_sub)
        id                  timestamp  data
0      100 2021-03-08 09:37:13.096029  25.0
1      100 2021-03-08 09:38:13.096029  25.0
2      100 2021-03-08 09:39:13.096029  25.0
3      100 2021-03-08 09:40:13.096029  25.0
4      100 2021-03-08 09:41:13.096029  25.0
   ...                        ...   ...
10076  100 2021-03-15 09:33:13.096029  23.0
10077  100 2021-03-15 09:34:13.096029  23.0
10078  100 2021-03-15 09:35:13.096029  23.0
10079  100 2021-03-15 09:36:13.096029  23.0
10080  100 2021-03-15 09:37:13.096029  23.0

[10081 rows x 3 columns]

edited Mar 15 '21 at 08:37

answered Mar 15 '21 at 06:21

jezrael

822,522
95
1,334
1,252

Thank you for the solution! I tested the code but the shape is not what I expected, could you please see the question update? – nilsinelabore Mar 15 '21 at 08:11
Thank you it worked! Can I please ask: 1. Are we using `.set_index('timestamp')` so that we can apply functions to `grouped` object? 2. Does it matter if `sort=False or True` in `.groupby('id', sort=False)`? Thanks! – nilsinelabore Mar 15 '21 at 09:13
1

@nilsinelabore - `.set_index('timestamp')` is used for possible `reindex` by datetimes in `groupby.apply`. 2. `sort=False or True` - if set `sort=False` it means not sorting ouput by `id`, because default in groupby is `sort=True`. In my opinion if data are sorted by `id` should be removed. – jezrael Mar 15 '21 at 09:16

Group by and resample in specified time interval in Python

1 Answers1