Pandas合并嵌套字典列表时如何按indice分组items子字段

实现方案 #

你可以通过自定义聚合函数处理嵌套的items分组，完整可运行代码如下：

import pandas as pd

# 原有列表定义保持不变
first_list =[
  {
    "name": "maria",
    "code": "MI",
    "cmd_count": 6,
    "indice": 1728205,
    "deal": 82,
    "items": [
      {
        "name": "papiers",
        "cmd_count": 2,
        "indice": 5950627,
        "deal": 68
      },
      {
        "name": "pens",
        "cmd_count": 1,
        "indice": 6940663,
        "deal": 74
      }
    ]
  },
  {
    "name": "Uzuno",
    "code": "UZ",
    "cmd_count": 1,
    "indice": 3232,
    "deal": 125,
    "items": [
      {
        "name": "printers",
        "cmd_count": 1,
        "indice": 28159,
        "deal": 9440
      }
    ]
  }
]

second_list =[
  {
    "name": "maria",
    "code": "MI",
    "cmd_count": 10,
    "indice": 1728205,
    "deal": 82,
    "items": [
      {
        "name": "glue",
        "cmd_count": 2,
        "indice": 5950627,
        "deal": 68
      },
      {
        "name": "pens",
        "cmd_count": 1,
        "indice": 6940663,
        "deal": 74
      }
    ]
  },
  {
    "name": "Fanky",
    "code": "FA",
    "cmd_count": 2,
    "indice": 46.603354,
    "deal": 1.8883335,
    "items": [
      {
        "name": "paint",
        "cmd_count": 1,
        "indice": 15987563,
        "deal": 465
      }
    ]
  },
  {
    "name": "Kaily",
    "code": "KA",
    "cmd_count": 2,
    "indice": 45,
    "deal": 789,
    "items": [
      {
        "name": "books",
        "cmd_count": 2,
        "indice": 3578,
        "deal": 74153
      }
    ]
  },
  {
    "name": "Lina",
    "code": "LI",
    "cmd_count": 1,
    "indice": 709,
    "deal": 5555,
    "items": [
      {
        "name": "rulers",
        "cmd_count": 1,
        "indice": 98,
        "deal": 96
      }
    ]
  }
]

# 自定义items字段聚合逻辑
def aggregate_items(items_group):
    # 展开当前分组下所有嵌套的items
    all_items = []
    for sub_items in items_group:
        all_items.extend(sub_items)
    if not all_items:
        return []
    # 按indice分组聚合，规则和外层保持一致，可按需修改
    items_df = pd.DataFrame(all_items)
    return items_df.groupby('indice').agg(
        name=('name', 'last'),
        cmd_count=('cmd_count', 'sum'),
        deal=('deal', 'last')
    ).reset_index().to_dict('records')

list_to_group = first_list + second_list
single_list = pd.DataFrame(list_to_group).groupby(['code',]).agg(
    name=('name', 'last'),
    cmd_count=('cmd_count','sum'),
    deal=('deal','last'),
    indice=('indice','last'),
    items=('items', aggregate_items)
).reset_index().to_dict('records')

效果说明 #

以code为MI的分组为例，聚合后的items会生成两条记录：

• indice为5950627的条目：cmd_count总和为4，name取第二个列表中出现的glue
• indice为6940663的条目：cmd_count总和为2，name保持pens

如果聚合规则不符合需求，比如name需要取第一个出现的值，将agg参数中的last修改为first即可。

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