如何在DataFrame中按2小时/天时间间隔分组并解决resample报错

有如下DataFrame：

print(DFrame)

dispatch_time   count
0   2018-08-13 00:02:27 26
1   2018-08-13 00:03:47 24
2   2018-08-13 00:19:36 25
3   2018-08-13 00:21:12 25
4   2018-08-13 00:22:47 25
... ... ...
636 2018-08-14 23:16:44 33
637 2018-08-14 23:30:33 25
638 2018-08-14 23:34:22 33
639 2018-08-14 23:41:14 79
640 2018-08-14 23:47:29 35

查看数据类型：

DFrame.dtypes

dispatch_time    object
count             int64
dtype: object

因部分时间存在毫秒问题，已用以下代码去除毫秒：

Splot = []
for i in DFrame['dispatch_time']:
    d = i.split(".")[0]
    Splot.append(d)

DFrame['dispatch_time'] = Splot

尝试按2小时和天的时间间隔分组求和，步骤如下：

1. 将dispatch_time转换为datetime类型：DFrame['dispatch_time'] = pd.to_datetime(DFrame['dispatch_time'])
2. 使用resample分组求和：DFrame = DFrame.resample('2H').sum()

运行后出现TypeError：

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[150], line 1
----> 1 DFrame = DFrame.resample('2H').sum()

File c:\users\roexz\appdata\local\programs\python\python39\lib\site-packages\pandas\core\frame.py:10999, in DataFrame.resample(self, rule, axis, closed, label, convention, kind, on, level, origin, offset, group_keys)
  10984 @doc(NDFrame.resample, **_shared_doc_kwargs)
  10985 def resample(
  10986     self,
   (...)
  10997     group_keys: bool = False,
  10998 ) -> Resampler:
> 10999     return super().resample(
  11000         rule=rule,
  11001         axis=axis,
  11002         closed=closed,
  11003         label=label,
  11004         convention=convention,
  11005         kind=kind,
  11006         on=on,
  11007         level=level,
  11008         origin=origin,
  11009         offset=offset,
  11010         group_keys=group_keys,
  11011     )

File c:\users\roexz\appdata\local\programs\python\python39\lib\site-packages\pandas\core\generic.py:8888, in NDFrame.resample(self, rule, axis, closed, label, convention, kind, on, level, origin, offset, group_keys)
   8885 from pandas.core.resample import get_resampler
   8887 axis = self._get_axis_number(axis)
-> 8888 return get_resampler(
   8889     cast("Series | DataFrame", self),
   8890     freq=rule,
   8891     label=label,
   8892     closed=closed,
   8893     axis=axis,
   8894     kind=kind,
   8895     convention=convention,
   8896     key=on,
   8897     level=level,
   8898     origin=origin,
   8899     offset=offset,
   8900     group_keys=group_keys,
   8901 )

File c:\users\roexz\appdata\local\programs\python\python39\lib\site-packages\pandas\core\resample.py:1523, in get_resampler(obj, kind, **kwds)
   1519 """
   1520 Create a TimeGrouper and return our resampler.
   1521 """
   1522 tg = TimeGrouper(**kwds)
-> 1523 return tg._get_resampler(obj, kind=kind)

File c:\users\roexz\appdata\local\programs\python\python39\lib\site-packages\pandas\core\resample.py:1713, in TimeGrouper._get_resampler(self, obj, kind)
   1704 elif isinstance(ax, TimedeltaIndex):
   1705     return TimedeltaIndexResampler(
   1706         obj,
   1707         timegrouper=self,
   (...)
   1710         gpr_index=ax,
   1711     )
-> 1713 raise TypeError(
   1714     "Only valid with DatetimeIndex, "
   1715     "TimedeltaIndex or PeriodIndex, "
   1716     f"but got an instance of '{type(ax).__name__}'"
   1717 )

TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'RangeIndex'

错误原因是resample默认要求DataFrame的索引为时间类型（DatetimeIndex、TimedeltaIndex等），而当前DataFrame的索引仍是默认的RangeIndex（0、1、2...），以下两种方法可解决问题：

方法一：指定on参数（无需修改索引） #

转换dispatch_time为datetime类型后，直接在resample中指定按该列分组：

# 转换时间列为datetime类型
DFrame['dispatch_time'] = pd.to_datetime(DFrame['dispatch_time'])
# 按2小时间隔分组求和
df_2h = DFrame.resample('2H', on='dispatch_time').sum()
# 按天间隔分组求和
df_daily = DFrame.resample('D', on='dispatch_time').sum()

方法二：将dispatch_time设置为索引 #

先把时间列设为DataFrame的索引，再执行resample操作：

# 转换时间列并设置为索引
DFrame['dispatch_time'] = pd.to_datetime(DFrame['dispatch_time'])
DFrame = DFrame.set_index('dispatch_time')
# 按2小时间隔分组求和
df_2h = DFrame.resample('2H').sum()
# 按天间隔分组求和
df_daily = DFrame.resample('D').sum()

额外优化：简化去除毫秒的步骤 #

无需手动循环拆分字符串，可直接借助pd.to_datetime的参数或dt.floor实现：

# 方法一：转换时直接匹配无毫秒的格式
DFrame['dispatch_time'] = pd.to_datetime(DFrame['dispatch_time'], format='%Y-%m-%d %H:%M:%S')
# 方法二：转换后将时间向下取整到秒（自动去除毫秒）
DFrame['dispatch_time'] = pd.to_datetime(DFrame['dispatch_time']).dt.floor('S')

内容的提问来源于stack exchange，提问作者BENJAMÍN IBÁÑEZ