如何将嵌套JSONL文件加载为扁平化Pandas DataFrame？

问题背景 #

耗时两天尝试将嵌套JSONL文件加载为DataFrame，需要转换为扁平化结构以便入库后执行关联与聚合操作，但现有方法无法完全展开嵌套的行为数据。

示例JSONL数据 #

{"metadata": {"timestamp": "2022-02-19T02:55:54", "collection_id": "a8b7c401-fafd-4e4c-924a-5935526722fd", "session_id": "452eb9e8-e090-4a08-b836-d23d05b1f400", "profile_id": "21636369-8b52-4b4a-97b7-50923ceb3ffd"}, "behaviour": {"mobile": {"swipe": [{"timestamp": 0, "x": 0.4230440650862826, "y": -1.1079966897549942}, ...]}}}
{"metadata": {"timestamp": "2022-01-20T11:58:31", "collection_id": "b29d1647-684e-4c5f-856a-87fbabdfcd7e", "session_id": "43dbf234-6207-4ba8-a32f-64bccb8948be", "profile_id": "21636369-8b52-4b4a-97b7-50923ceb3ffd"}, "behaviour": {"mobile": {"pin": [{"timestamp": 0, "x": -1.635364533608917, "y": -0.9233169601939333}, ...]}}}
{"metadata": {"timestamp": "2022-01-04T02:15:37", "collection_id": "781aa808-074f-4f1f-af27-667a490a55ea", "session_id": "de8877cb-3e8e-4713-8403-e4fea7cd0a38", "profile_id": "6018366c-f658-47a7-9ed3-4fe53a096533"}, "behaviour": {"mobile": {"keystrokes": [{"timestamp": 0, "key_hash": -1.2626154136500727}, ...]}}}

尝试的代码 #

import json
import pandas as pd

collections = '../test/input/collections.jsonl'
collections_data = [json.loads(line) for line in open(collections, 'r')]
collections_df = pd.json_normalize(collections_data)
print(collections_df)

当前问题 #

上述代码仅能扁平化metadata部分，behaviour.mobile下的swipe、pin、keystrokes仍为数组形式，无法满足后续入库需求。期望的输出Schema为：

['metadata.timestamp','metadata.collection_id','metadata.session_id','metadata.profile_id','behaviour.mobile.swipe.timestamp','behaviour.mobile.swipe.x','behaviour.mobile.swipe.y','behaviour.mobile.pin.timestamp','behaviour.mobile.pin.x','behaviour.mobile.pin.y','behaviour.mobile.keystrokes.timestamp','behaviour.mobile.keystrokes.key_hash']

解决方案 #

由于每个JSON条目仅包含一种行为类型（swipe/pin/keystrokes中的一个），无法通过单一record_path参数处理，需遍历每个条目展开行为数据并与元数据合并：

import json
import pandas as pd

collections_path = '../test/input/collections.jsonl'
processed_data = []

with open(collections_path, 'r') as f:
    for line in f:
        item = json.loads(line)
        # 提取元数据作为基础字段
        metadata = item['metadata']
        # 获取当前条目的行为类型
        mobile_behaviour = item['behaviour']['mobile']
        behaviour_type = next(iter(mobile_behaviour.keys()))
        # 遍历行为数组，将每个行为条目与元数据合并
        for behaviour_item in mobile_behaviour[behaviour_type]:
            # 构造符合期望Schema的键名
            behaviour_fields = {
                f'behaviour.mobile.{behaviour_type}.{key}': value 
                for key, value in behaviour_item.items()
            }
            # 合并元数据与行为字段
            combined_entry = {**metadata, **behaviour_fields}
            processed_data.append(combined_entry)

# 生成扁平化DataFrame
collections_df = pd.DataFrame(processed_data)

# 按期望的列顺序调整（可选，缺失字段自动填充NaN）
desired_columns = [
    'metadata.timestamp','metadata.collection_id','metadata.session_id','metadata.profile_id',
    'behaviour.mobile.swipe.timestamp','behaviour.mobile.swipe.x','behaviour.mobile.swipe.y',
    'behaviour.mobile.pin.timestamp','behaviour.mobile.pin.x','behaviour.mobile.pin.y',
    'behaviour.mobile.keystrokes.timestamp','behaviour.mobile.keystrokes.key_hash'
]
collections_df = collections_df.reindex(desired_columns, axis=1)

print(collections_df)

代码说明 #

1. 遍历每个JSONL条目，提取metadata作为基础数据；
2. 识别当前条目的行为类型（swipe/pin/keystrokes）；
3. 将行为数组中的每个元素转换为符合期望Schema的键值对，与元数据合并；
4. 收集所有合并后的条目，生成DataFrame；
5. 通过reindex调整列顺序，缺失的行为字段会自动填充NaN，适配入库需求。

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