如何按event_type拆分异构JSON并导入pandas DataFrame

问题描述 #

我有一个包含异构JSON对象的文件，每个对象均包含event_type字段（取值包括impression、pageread、pagereadtime等），不同event_type对应不同的附加字段。直接导入Pandas DataFrame时，未在所有对象中出现的字段会丢失，需要按event_type拆分该JSON文件进行处理。

示例JSON内容：

{"ts":1393631983,"visitor_uuid":"04daa9ed9dde73d3","visitor_source":"external","visitor_device":"browser","event_type":"impression","subject_type":"doc","subject_doc_id":"140224195414-e5a9acedd5eb6631bb6b39422fba6798","subject_page":0,"cause_type":"impression"}
{"ts":1393631983,"visitor_uuid":"c08fc48b49f0e1be","visitor_source":"external","visitor_device":"browser","env_type":"reader","env_doc_id":"140206010823-b14c9d966be950314215c17923a04af7","event_type":"pageread","subject_type":"doc","subject_doc_id":"140206010823-b14c9d966be950314215c17923a04af7","subject_page":110}
{"ts":1393631983,"visitor_uuid":"d6e0ca14e08bb49f","visitor_source":"external","visitor_device":"browser","env_type":"reader","env_doc_id":"110727005030-000000009cca70787e5fba1fda005c85","event_type":"pagereadtime","event_readtime":4000,"subject_type":"doc","subject_doc_id":"110727005030-000000009cca70787e5fba1fda005c85","subject_page":14}

解决方案 #

方法一：逐行读取分类（适合大文件） #

这种方法避免一次性加载全部数据到内存，适合处理大体积JSON文件：

import pandas as pd
import json

# 初始化字典，存储不同event类型的数据列表
event_data = {}

# 逐行读取并解析JSON
with open('your_file.json', 'r', encoding='utf-8') as f:
    for line in f:
        line = line.strip()
        if not line:
            continue
        data = json.loads(line)
        event_type = data['event_type']
        # 为对应event类型添加数据
        if event_type not in event_data:
            event_data[event_type] = []
        event_data[event_type].append(data)

# 生成各event类型对应的DataFrame
dfs = {event: pd.DataFrame(data_list) for event, data_list in event_data.items()}

之后可以通过dfs['impression']、dfs['pageread']等直接访问对应类型的DataFrame，每个DataFrame会完整保留该类型的所有字段。

方法二：整体导入后拆分（适合小文件） #

如果文件体积较小，可以先整体导入生成包含所有字段的DataFrame，再按event_type拆分：

import pandas as pd
import json

# 读取所有JSON行并解析
with open('your_file.json', 'r', encoding='utf-8') as f:
    all_data = [json.loads(line.strip()) for line in f if line.strip()]

# 生成包含所有字段的DataFrame，缺失字段会填充为NaN
full_df = pd.DataFrame(all_data)

# 按event_type拆分得到多个DataFrame
dfs = {event: group for event, group in full_df.groupby('event_type')}

# 可选：清理每个DataFrame中全为空的列
cleaned_dfs = {
    event: group.dropna(axis=1, how='all').reset_index(drop=True) 
    for event, group in dfs.items()
}

这种方法操作更简洁，拆分后的DataFrame可以通过清理步骤移除无意义的全空列。

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