如何将嵌套JSON提取转换为DataFrame并导入SQL表？

问题描述 #

已将指定结构的JSON存入Python列表变量，需提取数据转换为DataFrame（期望表格为Wise中每条记录与List中所有年份、标题组合的形式），后续导入SQL表，但现有Pandas代码未成功，寻求正确实现方法。

原始JSON结构 #

{
    "Details": [
        {
            "List": [
                {
                    "year": "2018-19",
                    "Title": "PLANTN-OTRWRKS"
                },
                {
                    "year": "2018-19",
                    "Title": "EXTERNAL"
                },
                {
                    "year": "2019-20",
                    "Title": "INTERNAL"
                },
                {
                    "year": "2020-21",
                    "Title": "BIANNUAL"
                },
                {
                    "year": "2022-23",
                    "Title": "WORKS OF 2017-18 AND 2018-19"
                }
            ],
            "Wise": [
                {
                    "Circle": "dgrgr",
                    "ID": "912",
                    "total_seedlings_evaluated": "5270",
                    "Average_height_in_meters": "2.53",
                    "Average_collar_girth_in_cms": "11.32",
                    "Survival_perc": "86.79",
                    "Condition": "Very Good"
                },
                {
                    "Circle": "hgrj",
                    "ID": "4654",
                    "total_seedlings_evaluated": "117206",
                    "Average_height_in_meters": "3.04",
                    "Average_collar_girth_in_cms": "22.61",
                    "Survival_perc": "71.61",
                    "Condition": "Good"
                }
                ]
        }
        ]
}

尝试的错误代码 #

df_table = pd.json_normalize(jsonObj['Details'])
df_table1 = pd.DataFrame(df_table['Wise'],index=df_table.index)

正确实现方法 #

步骤1：提取并转换List和Wise数据 #

分别将List和Wise数组转为独立DataFrame，再生成两者的笛卡尔积（实现每个Wise记录对应所有List中的年份和标题）：

import pandas as pd

# 假设jsonObj是存储JSON数据的变量
details = jsonObj['Details'][0]

# 转换List为DataFrame
df_list = pd.DataFrame(details['List'])
# 转换Wise为DataFrame
df_wise = pd.DataFrame(details['Wise'])

# 添加辅助列生成笛卡尔积
df_list['key'] = 1
df_wise['key'] = 1

# 合并两个DataFrame并移除辅助列
df_final = pd.merge(df_list, df_wise, on='key').drop('key', axis=1)

步骤2：转换数值列类型（可选，适配SQL导入） #

JSON中数值字段为字符串类型，建议转为对应数值类型：

# 指定需要转换的数值列
numeric_cols = ['total_seedlings_evaluated', 'Average_height_in_meters', 
                'Average_collar_girth_in_cms', 'Survival_perc']
df_final[numeric_cols] = df_final[numeric_cols].apply(pd.to_numeric)

步骤3：导出到SQL表 #

使用pandas.DataFrame.to_sql方法将数据导入SQL表（以下为SQLite示例，其他数据库需替换连接字符串）：

from sqlalchemy import create_engine

# 创建数据库连接
engine = create_engine('sqlite:///your_database.db')

# 将数据写入SQL表，可根据需求调整if_exists参数（replace/append/fail）
df_final.to_sql('seedling_data', engine, if_exists='replace', index=False)

最终DataFrame结构示例 #

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