如何将含动态键与数组的嵌套JSON转换为Pandas DataFrame

需求说明 #

• 静态键data、label、units、date、val、num可直接硬编码使用
• data_1_a、data_1000_xyz、name_1a、A、B等层级键为动态生成，数量最多可达上万条，不可硬编码处理

输入JSON样例 #

{
    "id": 1,
    "data": {
        "data_1_a": {
            "name_1a": {
                "label": "label_1",
                "units": {
                       "A": [{"date": 2020, "val": 1}]}}
        },
        "data_1000_xyz": {
            "name_1b": {
                "label": "null",
                "units": {
                        "B": [{"date": 2019, "val": 2},
                          {"date": 2020, "val": 3}]},
            },
            "name_10000_xyz": {
                "label": "null",
                "units": {
                       "A": [
                        {"date": 2018, "val": 4, "num": "str"},
                        {"date": 2019, "val": 5},
                        {"date": 2020, "val": 6, "num": "str"},
                    ]
                },
            },
        },
    },
}

目标输出格式 #

+---+--------------+----------------+---------+-------+------+-----+------+
|id |level_1       |level_2         |level_3  |level_4| date | val | num  | 
+---+--------------+----------------+---------+-------+------+-----+------+
|1  |data_1_a      | name_1a        | unit    | A     | 2020 |  1  | null |
|1  |data_1000_xyz | name_1b        | unit    | B     | 2019 |  2  | null |
|1  |data_1000_xyz | name_1b        | unit    | B     | 2020 |  3  | null |
|1  |data_1000_xyz | name_10000_xyz | unit    | A     | 2018 |  4  | str  |
|1  |data_1000_xyz | name_10000_xyz | unit    | A     | 2019 |  5  | null |
|1  |data_1000_xyz | name_10000_xyz | unit    | A     | 2020 |  6  | str  |
+-------------------------------------------------------------------------+

实现代码 #

import pandas as pd

# 实际使用时可将input_json替换为读取到的JSON对象
input_json = {
    "id": 1,
    "data": {
        "data_1_a": {
            "name_1a": {
                "label": "label_1",
                "units": {
                       "A": [{"date": 2020, "val": 1}]}}
        },
        "data_1000_xyz": {
            "name_1b": {
                "label": "null",
                "units": {
                        "B": [{"date": 2019, "val": 2},
                          {"date": 2020, "val": 3}]},
            },
            "name_10000_xyz": {
                "label": "null",
                "units": {
                       "A": [
                        {"date": 2018, "val": 4, "num": "str"},
                        {"date": 2019, "val": 5},
                        {"date": 2020, "val": 6, "num": "str"},
                    ]
                },
            },
        },
    }
}

rows = []
base_id = input_json["id"]
# 遍历第一层动态键
for level1_key, level1_val in input_json["data"].items():
    # 遍历第二层动态键
    for level2_key, level2_val in level1_val.items():
        # 遍历units下的动态单位键
        for level4_key, val_arr in level2_val["units"].items():
            # 展开时间序列数组
            for item in val_arr:
                rows.append({
                    "id": base_id,
                    "level_1": level1_key,
                    "level_2": level2_key,
                    "level_3": "unit",
                    "level_4": level4_key,
                    "date": item.get("date"),
                    "val": item.get("val"),
                    "num": item.get("num")
                })

# 生成目标DataFrame
df = pd.DataFrame(rows)
print(df)

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