Pandas如何提取id_1重复值对应行并生成指定结构JSON对象

核心思路 #

• 不要逐行遍历DataFrame做重复判断，直接用groupby按id_1（同时带上同组固定值id_2、id_3）分组，性能远高于逐行循环判断
• 每个分组内，id_1/id_2/id_3值唯一，直接取分组键即可，组内所有行的id_4/id_5直接转成字典列表作为items字段值
• 若只需要保留id_1重复出现的条目，最后对结果做一次长度过滤即可，不需要在遍历阶段做判断
• 你给出的示例输出存在JSON语法错误（缺键、键名写错、多余逗号），最终输出要保证JSON格式合法，避免后续解析失败

实现代码 #

import pandas as pd
import json

# 替换为你自己的DataFrame读取逻辑，这里用示例数据构造
df = pd.DataFrame(
    [
        ["0133", "11", "kelly", "AA-1", "1"],
        ["2119", "22", "Wade", "AA-2", "1"],
        ["3903", "33", "John", "BB-1", "1"],
        ["3903", "33", "John", "BB-2", "1"],
        ["3903", "33", "John", "BB-3", "1"],
        ["5133", "44", "Emily", "C-1", "1"],
        ["9148", "99", "Pete", "BB-34", "1"],
        ["9148", "99", "Pete", "BB-23", "1"],
        ["2910", "111", "Mark", "DD-3", "1"]
    ],
    columns=["id_1", "id_2", "id_3", "id_4", "id_5"]
)

result_list = []
for (id1, id2, id3), group in df.groupby(["id_1", "id_2", "id_3"], as_index=True):
    # 组装当前分组的条目
    item_entry = {
        "id_1": id1,
        "id_2": id2,
        "id_3": id3,
        "items": group[["id_4", "id_5"]].to_dict("records")
    }
    result_list.append(item_entry)

# 取消下方注释即可仅保留id_1出现次数大于1（即items长度>1）的条目
# result_list = [entry for entry in result_list if len(entry["items"]) > 1]

# 按JSON Lines格式逐行输出，和示例输出格式对齐
for entry in result_list:
    print(json.dumps(entry, ensure_ascii=False))

合法输出结果 #

{"id_1": "0133", "id_2": "11", "id_3": "kelly", "items": [{"id_4": "AA-1", "id_5": "1"}]}
{"id_1": "2119", "id_2": "22", "id_3": "Wade", "items": [{"id_4": "AA-2", "id_5": "1"}]}
{"id_1": "2910", "id_2": "111", "id_3": "Mark", "items": [{"id_4": "DD-3", "id_5": "1"}]}
{"id_1": "3903", "id_2": "33", "id_3": "John", "items": [{"id_4": "BB-1", "id_5": "1"}, {"id_4": "BB-2", "id_5": "1"}, {"id_4": "BB-3", "id_5": "1"}]}
{"id_1": "5133", "id_2": "44", "id_3": "Emily", "items": [{"id_4": "C-1", "id_5": "1"}]}
{"id_1": "9148", "id_2": "99", "id_3": "Pete", "items": [{"id_4": "BB-34", "id_5": "1"}, {"id_4": "BB-23", "id_5": "1"}]}

提示：如果需要输出为单个JSON数组而非逐行JSON，直接对result_list做json.dumps即可。

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