Pandas中Datetime64类型分组求和报错问题求助

问题描述 #

读取CSV计算期权Gamma Exposure时，执行df.groupby(['StrikePrice']).sum()触发报错：TypeError: datetime64 type does not support sum operations，重装Python 3.11.6后问题依旧。

核心报错代码片段 #

# ---=== CALCULATE SPOT GAMMA ===---
df['CallGEX'] = df['CallGamma'] * df['CallOpenInt'] * 100 * spotPrice * spotPrice * 0.01
df['PutGEX'] = df['PutGamma'] * df['PutOpenInt'] * 100 * spotPrice * spotPrice * 0.01 * -1

df['TotalGamma'] = (df.CallGEX + df.PutGEX) / 10**9
dfAgg = df.groupby(['StrikePrice']).sum()  # 触发报错的代码行
strikes = dfAgg.index.values

完整报错信息 #

C:\Users\v_ichase\Desktop\New folder (3)>py "C:\Users\v_ichase\Desktop\New folder (3)\Gex.py"
Traceback (most recent call last):
  File "C:\Users\v_ichase\Desktop\New folder (3)\Gex.py", line 78, in <module>
    dfAgg = df.groupby(['StrikePrice']).sum()
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\v_ichase\AppData\Roaming\Python\Python311\site-packages\pandas\core\groupby\groupby.py", line 3053, in sum
    result = self._agg_general(
             ^^^^^^^^^^^^^^^^^^
  File "C:\Users\v_ichase\AppData\Roaming\Python\Python311\site-packages\pandas\core\groupby\groupby.py", line 1835, in _agg_general
    result = self._cython_agg_general(
             ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\v_ichase\AppData\Roaming\Python\Python311\site-packages\pandas\core\groupby\groupby.py", line 1926, in _cython_agg_general
    new_mgr = data.grouped_reduce(array_func)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\v_ichase\AppData\Roaming\Python\Python311\site-packages\pandas\core\internals\managers.py", line 1431, in grouped_reduce
    applied = blk.apply(func)
              ^^^^^^^^^^^^^^^
  File "C:\Users\v_ichase\AppData\Roaming\Python\Python311\site-packages\pandas\core\internals\blocks.py", line 366, in apply
    result = func(self.values, **kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\v_ichase\AppData\Roaming\Python\Python311\site-packages\pandas\core\groupby\groupby.py", line 1902, in array_func
    result = self.grouper._cython_operation(
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\v_ichase\AppData\Roaming\Python\Python311\site-packages\pandas\core\groupby\ops.py", line 815, in _cython_operation
    return cy_op.cython_operation(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\v_ichase\AppData\Roaming\Python\Python311\site-packages\pandas\core\groupby\ops.py", line 525, in cython_operation
    return values._groupby_op(
           ^^^^^^^^^^^^^^^^^^^
  File "C:\Users\v_ichase\AppData\Roaming\Python\Python311\site-packages\pandas\core\arrays\datetimelike.py", line 1637, in _groupby_op
    raise TypeError(f"datetime64 type does not support {how} operations")
TypeError: datetime64 type does not support sum operations

问题原因 #

DataFrame中包含ExpirationDate（datetime64类型）列，调用groupby.sum()时，pandas会默认对所有列执行求和操作，而datetime类型不支持求和运算，因此触发错误。重装Python无法解决，这属于代码逻辑问题，与Python版本无关。

解决方案 #

有两种可行的解决方式：

方式一：指定需要求和的数值列 #

明确指定要对哪些列执行求和，排除datetime类型列和不需要求和的字符串列：

# 定义需要求和的数值列列表
sum_columns = ['CallLastSale', 'CallNet', 'CallBid', 'CallAsk', 'CallVol', 'CallIV',
               'CallDelta', 'CallGamma', 'CallOpenInt', 'PutLastSale', 'PutNet',
               'PutBid', 'PutAsk', 'PutVol', 'PutIV', 'PutDelta', 'PutGamma',
               'PutOpenInt', 'CallGEX', 'PutGEX', 'TotalGamma']

# 仅对指定列执行groupby求和
dfAgg = df.groupby(['StrikePrice'])[sum_columns].sum()
strikes = dfAgg.index.values

方式二：删除不需要参与求和的列 #

先移除ExpirationDate等不需要求和的列，再执行groupby求和：

# 删除datetime列和无关字符串列
df_processed = df.drop(['ExpirationDate', 'Calls', 'Puts'], axis=1)

# 对处理后的DataFrame执行groupby求和
dfAgg = df_processed.groupby(['StrikePrice']).sum()
strikes = dfAgg.index.values

两种方式都能避免pandas尝试对datetime列求和，从而解决报错问题。

内容的提问来源于stack exchange，提问作者Lion