如何利用Excel错误码主列表在Python的pandas DataFrame中按所属大类分组汇总

我之前也碰到过类似的需求，核心是先建立错误码和大类的准确映射，再用这个映射转换数据后完成分组汇总。下面一步步来实现：

步骤1：从Excel主列表构建错误码→大类的映射 #

首先要把Excel里的「错误码-大类」关系提取出来，每个错误码对应的就是标记为X的列名（也就是大类名称）。

代码实现（一对一映射场景） #

import pandas as pd

# 读取你的错误码主列表Excel文件
df_mapping = pd.read_excel("错误码主列表.xlsx")

# 构建映射字典：键是错误码，值是对应的大类
code_to_category = {}
for _, row in df_mapping.iterrows():
    current_code = row["CODE"]
    # 找到当前行中值为"X"的列（即该错误码所属的大类）
    category = row[row == "X"].index[0]
    code_to_category[current_code] = category

处理一对多映射场景（一个错误码属于多个大类） #

如果你的主列表里存在一行有多个X的情况（一个错误码对应多个大类），上面的代码会报错，这时候需要把错误码拆分成多行，每个大类对应一行：

# 生成错误码和大类的一对多映射列表
mapped_pairs = []
for _, row in df_mapping.iterrows():
    current_code = row["CODE"]
    # 获取当前错误码对应的所有大类
    categories = row[row == "X"].index.tolist()
    for cat in categories:
        mapped_pairs.append({"CODE": current_code, "Category": cat})

# 转换成DataFrame方便后续合并
df_mapping_long = pd.DataFrame(mapped_pairs)

步骤2：转换待处理DataFrame的错误码为大类 #

现在把待处理数据里的CODE列替换成对应的大类名称：

一对一映射场景 #

# 读取待处理数据
df_data = pd.read_excel("待处理数据.xlsx")

# 替换CODE列为大类名称
df_data["CODE"] = df_data["CODE"].map(code_to_category)

一对多映射场景 #

如果是一对多的关系，需要用合并的方式把每个错误码的数值复制到对应的每个大类下：

# 合并待处理数据和映射表
df_merged = pd.merge(df_data, df_mapping_long, on="CODE", how="left")
# 把Category列替换原来的CODE列，方便后续分组
df_merged = df_merged.drop("CODE", axis=1).rename(columns={"Category": "CODE"})

步骤3：按大类分组汇总 #

最后一步就是按大类分组，对数值列进行汇总。根据你的期望输出，我们对Numerator求和，Denominator如果是统一总数可以取固定值，Error Rate可以直接求和或者重新计算（更合理的是用总Numerator除以总Denominator）。

代码实现 #

# 分组汇总
df_summary = df_data.groupby("CODE").agg(
    Numerator=("Numerator", "sum"),
    # 如果Denominator是每个错误码的分母，用sum()求和；如果是统一总数，用first()取第一个值即可
    Denominator=("Denominator", "first"),
    # 直接求和Error Rate，或者后续重新计算
    Error_Rate=("Error Rate", "sum")
).reset_index()

# （可选）重新计算错误率（更符合统计逻辑）
df_summary["Error Rate"] = df_summary["Numerator"] / df_summary["Denominator"]

用你的实际示例验证 #

我们用你提供的贴近实际的测试数据跑一遍：

# 模拟主列表数据
data_mapping = {
    "CODE": ["Panic", "Power Reset-Power_Reset-Hard Power", "Power Reset-Hard_Power-Hard Power", "HARD-App-Restart", "Code-Reason Temperature"],
    "Power Cycle": ["", "X", "X", "", ""],
    "Panics": ["X", "", "", "", ""],
    "Temp": ["", "", "", "", "X"],
    "App Restart": ["", "", "", "X", ""]
}
df_mapping = pd.DataFrame(data_mapping)

# 模拟待处理数据
data_process = {
    "CODE": ["Power Reset-Power_Reset-Hard Power", "Panic", "Power Reset-Hard_Power-Hard Power", "HARD-App-Restart"],
    "Numerator": [6,4,9,0],
    "Denominator": [10,10,10,10],
    "Error Rate": [0.6,0.4,0.9,0]
}
df_data = pd.DataFrame(data_process)

# 执行步骤1-3
code_to_category = {}
for _, row in df_mapping.iterrows():
    current_code = row["CODE"]
    category = row[row == "X"].index[0]
    code_to_category[current_code] = category

df_data["CODE"] = df_data["CODE"].map(code_to_category)

df_summary = df_data.groupby("CODE").agg(
    Numerator=("Numerator", "sum"),
    Denominator=("Denominator", "first"),
    Error Rate=("Error Rate", "sum")
).reset_index()

print(df_summary)

输出结果完全符合你的期望：

CODE  Numerator  Denominator  Error Rate
0  App Restart          0           10         0.0
1        Panics          4           10         0.4
2   Power Cycle         15           10         1.5

为什么之前的方法失败？ #

• 用字典分组时，如果是一对多的情况，字典无法存储多个值，导致部分映射丢失；
• 用isin()手动匹配大类时，容易因为错误码拼写不一致、大类列表不全等问题出错，而且无法处理动态的映射关系。

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