使用Python pandas反转Excel两列映射 聚合关联Asset ID

需求说明 #

原始Excel表结构：

• 共2列：Asset Ids、FADEL Ids
• 每行Asset Ids为单个资产ID值
• 每行FADEL Ids为单个ID，或逗号分隔的多个ID值

目标输出：

• 新Excel每行对应1个唯一的FADEL ID
• Asset Ids列存储所有关联到当前FADEL ID的资产ID，多个ID用逗号分隔

依赖安装 #

先安装需要的第三方库，执行命令：
pip install pandas openpyxl

完整可运行代码 #

import pandas as pd
from collections import defaultdict

# 替换为你自己的文件路径
INPUT_FILE = "原始数据表.xlsx"
OUTPUT_FILE = "映射反转结果.xlsx"

# 读取原始数据，仅加载需要的两列
raw_df = pd.read_excel(INPUT_FILE, usecols=["Asset Ids", "FADEL Ids"])

# 构建FADEL到资产ID的映射，用集合自动去重
id_mapping = defaultdict(set)

for _, row in raw_df.iterrows():
    # 清洗资产ID，跳过空值
    asset_id = str(row["Asset Ids"]).strip()
    if asset_id in ("", "nan"):
        continue
    
    # 拆分FADEL列的多ID，清洗每个ID的前后空格
    fadel_raw = str(row["FADEL Ids"]).strip()
    if fadel_raw in ("", "nan"):
        continue
    fadel_list = [f.strip() for f in fadel_raw.split(",") if f.strip()]
    
    # 给每个FADEL ID绑定当前资产
    for fadel_id in fadel_list:
        id_mapping[fadel_id].add(asset_id)

# 整理成输出格式
output_rows = []
for fadel_id, assets in id_mapping.items():
    output_rows.append({
        "FADEL Ids": fadel_id,
        "Asset Ids": ",".join(sorted(assets)) # 不需要排序可去掉sorted
    })

# 导出Excel
result_df = pd.DataFrame(output_rows, columns=["FADEL Ids", "Asset Ids"])
result_df.to_excel(OUTPUT_FILE, index=False)
print(f"处理完成，共输出{len(result_df)}条FADEL映射记录，文件已保存至{OUTPUT_FILE}")

注意事项 #

• 代码会自动跳过原始表中ID为空的无效行
• 自动处理ID前后、逗号前后的多余空格，避免格式问题导致ID匹配错误
• 同一FADEL ID关联的资产ID会自动去重，默认按ID升序拼接，不需要排序直接删除sorted()即可
• 逐行遍历构建映射的方式对十万行级别的大表也友好，不会出现pandas多步转换的内存占用过高问题

内容的提问来源于stack exchange，提问作者Venkatesh Prasad TELUGU