在 Python 中按指定的时间间隔分组并重新采样

Question

如何使用前向填充ffillbfill频率以1min频率对下面的df进行重新采样，并在时间间隔内为每个id(使用groupby('id'))重新采样2017-01-01 00:00:00和2017-01-05 00:00:00，即第一个时间戳2017-01-01 00:00:00，最后一个时间戳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  
...

我试过了：

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

但这并没有指定所需的时间间隔2017-01-01 00:00:00-2017-01-05 00:00:00.

然后我尝试了：

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())

并捕获错误：

---------------------------------------------------------------------------
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 
~AppDataRoamingPythonPython38site-packagespandascoregroupbygroupby.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'

---

更新：

对于示例数据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}}

我试过了：

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()
)

它返回了形状(30243, 3)的数据帧

我想知道，我不应该期望形状是(10080, 3)这是通过7 x 24 x 607 天内的分钟数给出的吗？示例数据由一个id == 100的数据组成。

Answer 1

您可以将 cusom lambda 函数与reindexbydate_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]