Pandas按Category汇总透视表数据因日期列报错的解决方法

你的核心问题是：透视表生成后的数据框df2包含了JDate、Code等非数值列，直接执行groupby('Category').sum()会尝试对这些列求和，而日期/字符串列无法进行求和运算，因此报错。

解决步骤： #

• 先筛选出透视表中所有月度数值列（即由JYearMonth生成的列）
• 仅对这些列按Category分组求和，保留Category作为分组键

修改后的完整代码： #

# test groupbysum
import pandas as pd
import numpy as np

df = pd.DataFrame({
        'JDate':["2022-01-31","2022-12-05","2023-11-10","2023-12-03","2024-01-16","2024-01-06","2011-01-04"],
        'Code':[None,'John Johnson',np.nan,'John Smith','Mary Williams','ted bundy','George Lucas'],
        'Unit Price':[np.nan,200,None,56,75,65,60],
        'Quantity':[1500, 140000, 1400000, 455, 648, 759,1000],
        'Amount':[100, 10000, 100000, 5, 48, 59,449],
        'Invoice':['soccer','basketball','baseball','football','baseball','ice hockey','football'],
        'energy':[100.,100,100,54,98,3,45],
        'Category':['alpha','bravo','kappa','alpha','bravo','bravo','kappa']
})

df["JDate"] = pd.to_datetime(df["JDate"])
df["JYearMonth"] =  df['JDate'].dt.to_period('M')

index_to_use = ['Category','Code','Invoice','Unit Price','JDate']
values_to_use = ['Amount']
columns_to_use = ['JYearMonth']

df2 = df.pivot_table(index=index_to_use,
                            values=values_to_use,
                            columns=columns_to_use)
df2 = df2['Amount'].reset_index()

# 关键修改：筛选出需要求和的月度列（数值型列），再按Category分组求和
numeric_cols = df2.select_dtypes(include=['number']).columns
df2_sum = df2.groupby('Category')[numeric_cols].sum()

writer= pd.ExcelWriter(
        "t2test18.xlsx",
        engine='xlsxwriter'
    )

df.to_excel(writer,sheet_name="t2",index=True)
df2.to_excel(writer,sheet_name="t2test",index=True)
df2_sum.to_excel(writer,sheet_name="t2testsum",index=True)

workbook = writer.book
worksheet = writer.sheets["t2"]

fmt_header = workbook.add_format({
    'bold':True,
    'text_wrap':True,
    'valign':'top',
    'fg_color': '#5DADE2',
    'font_color':'#2659D9',
    'border':1
})

writer.close()

补充说明： #

• df2.select_dtypes(include=['number'])会自动筛选出所有数值类型的列，也就是透视生成的各个月度Amount列，避免对JDate、Code等列进行无效求和。
• 如果需要更精准地筛选月度列（比如担心其他数值列混入），也可以用列名判断：

  # 筛选列名属于Period类型的列（即JYearMonth生成的列）
  month_cols = [col for col in df2.columns if isinstance(col, pd.Period)]
  df2_sum = df2.groupby('Category')[month_cols].sum()
  

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