如何将嵌套JSON数组展开为目标结构的Pandas DataFrame？

这需求太常见啦！要把每个用户的多个location拆成单独行，同时保留id和name，用Pandas的explode()+json_normalize()组合就能轻松搞定，我给你写完整的实现步骤：

步骤1：加载JSON数据到初始DataFrame #

首先把你的JSON数据转换成Pandas DataFrame，直接用列表初始化就行：

import pandas as pd

# 你的原始JSON数据
json_data = [
    {
        "id": "0001",
        "name": "Stiven",
        "location": [
            {"country": "Colombia", "department": "Chocó", "city": "Quibdó"},
            {"country": "Colombia", "department": "Antioquia", "city": "Medellin"},
            {"country": "Colombia", "department": "Cundinamarca", "city": "Bogotá"}
        ]
    },
    {
        "id": "0002",
        "name": "Jhon Jaime",
        "location": [
            {"country": "Colombia", "department": "Valle del Cauca", "city": "Cali"},
            {"country": "Colombia", "department": "Putumayo", "city": "Mocoa"},
            {"country": "Colombia", "department": "Arauca", "city": "Arauca"}
        ]
    },
    {
        "id": "0003",
        "name": "Francisco",
        "location": [
            {"country": "Colombia", "department": "Atlántico", "city": "Barranquilla"},
            {"country": "Colombia", "department": "Bolívar", "city": "Cartagena"},
            {"country": "Colombia", "department": "La Guajira", "city": "Riohacha"}
        ]
    }
]

# 初始DataFrame
df = pd.DataFrame(json_data)

此时的df里，location列是嵌套的数组，接下来处理拆分。

步骤2：拆分location数组为单行 #

用explode()方法把location列的数组拆成每行对应一个地址对象：

# 拆分location数组，每个元素占一行
df_exploded = df.explode('location', ignore_index=True)

这一步之后，location列不再是数组，而是单个的字典对象，每个用户的每一个地址都单独成行了。

步骤3：展开location字典为独立列 #

最后用pd.json_normalize()把location列的字典展开成单独的country、department、city列，再和原有的id、name列合并：

# 展开location字典为列，并合并到原DataFrame
final_df = pd.concat([
    df_exploded[['id', 'name']],
    pd.json_normalize(df_exploded['location'])
], axis=1)

最终效果 #

运行完上面的代码后，final_df就是你想要的结构化数据，每行对应一个用户的一个地址，结构如下：

这样就完美实现了你的需求，把嵌套的JSON数组完全展开成结构化的表格啦！

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