如何将Pandas DataFrame按date和media分组并转为指定JSON格式？

问题背景 #

我创建了如下的DataFrame：

media = [
    {"date": "20231101", "media": "youtube", "view_count": 3, "url": "http://blah.com/a"},
    {"date": "20231101", "media": "facebook", "view_count": 3, "url": "http://blah.com/b"},
    {"date": "20231101", "media": "youtube", "view_count": 31, "url": "http://blah.com/c"},
    {"date": "20231101", "media": "instagram", "view_count": 6, "url": "http://blah.com/d"},
    {"date": "20231102", "media": "youtube", "view_count": 7, "url": "http://blah.com/e"},
    {"date": "20231103", "media": "facebook", "view_count": 12, "url": "http://blah.com/f"},
    {"date": "20231103", "media": "youtube", "view_count": 4, "url": "http://blah.com/g"},
    {"date": "20231104", "media": "youtube", "view_count": 5, "url": "http://blah.com/h"},
    {"date": "20231104", "media": "facebook", "view_count": 91, "url": "http://blah.com/i"},
    {"date": "20231104", "media": "facebook", "view_count": 2, "url": "http://blah.com/j"},
    {"date": "20231104", "media": "youtube", "view_count": 0, "url": "http://blah.com/k"},
    {"date": "20231105", "media": "instagram", "view_count": 1, "url": "http://blah.com/l"}
]

期望先按date分组，再按media分组，最终生成如下格式的字典/JSON：

{
    "20231101": {
        "youtube": [{"view_count": 3,"url": "http://blah.com/a"}, {"view_count": 31, "url": "http://blah.com/c"}],
        "facebook": [{"view_count": 3, "url": "http://blah.com/b"}],
        "instagram": [{"view_count": 6, "url": "http://blah.com/d"}]
    },
    "20231102": {
        "youtube": [{"view_count": 7, "url": "http://blah.com/e"}]
    },
    "20231103": {
        "youtube": [{"view_count": 4, "url": "http://blah.com/g"}],
        "facebook": [{"view_count": 12, "url": "http://blah.com/f"}]
    },
    "20231104": {
        "youtube": [{"view_count": 5, "url": "http://blah.com/h"}, {"view_count": 0, "url": "http://blah.com/k"}],
        "facebook": [{"view_count": 91, "url": "http://blah.com/i"}, {"view_count": 2, "url": "http://blah.com/j"}]
    },
    "20231105": {
        "instagram": [{"view_count": 1, "url": "http://blah.com/l"}]
    }
}

尝试的代码与问题 #

我尝试了以下代码：

df = pd.DataFrame(media)
rst = df.groupby('date').apply(lambda x : x.groupby('media')['view_count', 'url'].agg(list)).to_dict()

但得到的结果格式不符合预期，view_count和url被拆分为顶层键，而非整合到每个媒体对应的条目字典中：

{
    'view_count': {
        ('20231101', 'facebook'): [3], 
        ('20231101', 'instagram'): [6], 
        ('20231101', 'youtube'): [3, 31], 
        ('20231102', 'youtube'): [7], 
        ('20231103', 'facebook'): [12], 
        ('20231103', 'youtube'): [4], 
        ('20231104', 'facebook'): [91, 2], 
        ('20231104', 'youtube'): [5, 0], 
        ('20231105', 'instagram'): [1]
    }, 
    'url': {
        ('20231101', 'facebook'): ['http://blah.com/b'], 
        ('20231101', 'instagram'): ['http://blah.com/d'], 
        ('20231101', 'youtube'): ['http://blah.com/a', 'http://blah.com/c'], 
        ('20231102', 'youtube'): ['http://blah.com/e'], 
        ('20231103', 'facebook'): ['http://blah.com/f'], 
        ('20231103', 'youtube'): ['http://blah.com/g'], 
        ('20231104', 'facebook'): ['http://blah.com/i', 'http://blah.com/j'], 
        ('20231104', 'youtube'): ['http://blah.com/h', 'http://blah.com/k'], 
        ('20231105', 'instagram'): ['http://blah.com/l']
    }
}

问题分析与解决 #

问题所在 #

原代码中，x.groupby('media')['view_count', 'url'].agg(list)的逻辑是对每个媒体分组，将view_count和url分别聚合成独立的列表，最终得到的是一个以媒体为索引、两个字段为列的DataFrame。外层按日期分组后转字典时，就会把view_count和url作为顶层键，完全打乱了预期的层级结构。

正确实现 #

我们需要将每个媒体分组内的行，直接转换为包含view_count和url的字典列表，而非拆分字段聚合。可以通过两种方式实现：

方式1：嵌套循环分组处理

import pandas as pd

df = pd.DataFrame(media)
result = {}

# 先按日期分组
for date, date_group in df.groupby('date'):
    media_data = {}
    # 再按媒体分组
    for platform, platform_group in date_group.groupby('media'):
        # 将每个行转换为指定字段的字典，组成列表
        media_data[platform] = platform_group[['view_count', 'url']].to_dict('records')
    result[date] = media_data

方式2：链式apply调用

用更简洁的链式操作实现相同逻辑：

import pandas as pd

df = pd.DataFrame(media)
result = df.groupby('date').apply(
    lambda date_group: date_group.groupby('media').apply(
        lambda platform_group: platform_group[['view_count', 'url']].to_dict('records')
    ).to_dict()
).to_dict()

两种方式最终都会生成符合预期格式的字典，你可以根据习惯选择其中一种。

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