如何用Pandas将JSON数据展开为带预设表头的多列？

需求说明 #

现有包含MonthWise列的DataFrame，该列存储JSON对象，需要将其扩展为12个形如MMYYYY的年度月份列，每个月份对应.val和.count两个子列，缺失的月份数据用NaN填充。

输入示例 #

id   |Name | MonthWise 
0    |ABC  |{'102022':{'val':100, 'count':1}}
1    |XYZ  |{'102022':{'val':20,'count':5},'092022':{'val':20,'count':2}}
2    |DEF  |{}
3    |PQR  |{'082022':{'val':50,'count':3}}

期望输出示例 #

id |Name |042022.val | 042022.count |....|102022.val | 102022.count|....| 032023.val| 032023.count
0  |ABC  |nan|nan|....|100|1|....|nan|nan
1  |XYZ  |nan|nan|....|20|5|....|nan|nan
2  |DEF  |nan|nan|....|nan|nan|....|nan|nan
3  |PQR  |nan|nan|....|nan|nan|....|nan|nan

解决方案 #

你之前尝试的df['MonthWise'].apply(pd.json_normalize(x, max_level=1))写法有误，apply需要传入函数而非直接调用pd.json_normalize。正确的处理步骤如下：

1. 构造示例数据 #

首先还原输入的DataFrame：

import pandas as pd

data = [
    {"id": 0, "Name": "ABC", "MonthWise": {'102022':{'val':100, 'count':1}}},
    {"id": 1, "Name": "XYZ", "MonthWise": {'102022':{'val':20,'count':5},'092022':{'val':20,'count':2}}},
    {"id": 2, "Name": "DEF", "MonthWise": {}},
    {"id": 3, "Name": "PQR", "MonthWise": {'082022':{'val':50,'count':3}}}
]
df = pd.DataFrame(data)

2. 展开JSON列 #

对MonthWise列的每个JSON对象应用pd.json_normalize，然后将所有展开后的小DataFrame拼接在一起：

# 逐个展开JSON对象并拼接
expanded_df = pd.concat([pd.json_normalize(item) for item in df['MonthWise']], ignore_index=True)
# 合并原数据（移除MonthWise列）和展开后的数据
temp_result = pd.concat([df.drop('MonthWise', axis=1), expanded_df], axis=1)

3. 补全目标月度列 #

如果需要固定的12个月份范围（比如示例中的042022到032023），先生成所有需要的列名，再补全缺失列并填充NaN：

# 生成目标月份范围（可根据需求调整start和end）
date_range = pd.date_range(start='2022-04', end='2023-03', freq='MS')
# 生成所有需要的列名：MMYYYY.val 和 MMYYYY.count
target_columns = [f"{dt.strftime('%m%Y')}.{subcol}" for dt in date_range for subcol in ['val', 'count']]

# 补全缺失的列，填充NaN
for col in target_columns:
    if col not in temp_result.columns:
        temp_result[col] = pd.NA

# 按指定顺序排列列
final_result = temp_result[['id', 'Name'] + target_columns]

4. 查看结果 #

执行后final_result就会符合你的期望输出，缺失的月度数据自动填充为NaN。

关键说明 #

• 之前的错误在于apply的用法：正确的写法应该是df['MonthWise'].apply(lambda x: pd.json_normalize(x))，但这种方式会得到一个由DataFrame组成的Series，仍需要用pd.concat拼接才能和原DataFrame合并。
• 若不需要固定的12个月份范围，仅展开现有JSON中的所有键，可跳过第3步，直接使用temp_result即可。

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