如何对包含JSON数据的Pandas DataFrame进行透视转换

步骤说明 #

1. 解析JSON格式的message列：将可解析为字典的字符串转换为字典，无法解析的返回空字典
2. 展开字典为列：使用pd.json_normalize将字典列展开为对应的变量列
3. 过滤无效行：移除所有变量列均为空的行（即非JSON内容的行）
4. 按日期聚合：分组后提取每个日期下各变量的有效值

完整代码 #

import pandas as pd
from ast import literal_eval

# 构造原始DataFrame（模拟你的数据）
df = pd.DataFrame({
    'Date': ['2020-01-01', '2020-01-01', '2020-01-01', '2020-01-02', '2020-01-02'],
    'message': ["{ 'var1': 'foo'}", "different content", "{ 'var2': 'foo'}", "non json content", "{ 'var2': 'bar'}"]
})

# 定义解析函数，处理单引号格式的JSON字符串
def parse_json(s):
    try:
        return literal_eval(s)
    except (SyntaxError, ValueError):
        return {}

# 解析message并展开为变量列，合并Date列
parsed_df = pd.concat([
    df['Date'],
    pd.json_normalize(df['message'].apply(parse_json))
], axis=1)

# 过滤掉无有效变量的行
parsed_df = parsed_df.dropna(how='all', subset=parsed_df.columns.difference(['Date']))

# 按日期聚合，提取各变量的有效值
result = parsed_df.groupby('Date').first().reset_index()

print(result)

输出结果 #

Date var1 var2
0  2020-01-01  foo  foo
1  2020-01-02  NaN  bar

补充说明 #

• 若你的JSON是标准双引号格式，可将literal_eval替换为json.loads
• 聚合时如果同一日期同一变量有多个值，可根据需求替换first()为max()/min()/last()等

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