如何拆分Pandas DataFrame中含多列表的JSON字符串列？

你的Pandas DataFrame包含一列JSON格式的字符串，该字符串结构固定：包含columns字段（存储目标列名列表）和data字段（存储对应列的数值列表），示例格式如下：

{"columns":["ApplicationNum","eads59Us01S","HouseDeal_flag","Liability_Asset_Ratio","CBRAvailPcnt","CMSFairIsaacScore","OweTaxes_or_IRAWithdrawalHistry","eads14Fi02S","GuarantorCount","CBRRevMon","CBRInstalMon","CMSApprovedToRequested","SecIncSource","eads59Us01S_4","Liability_Asset_Ratio_40_90","CBRAvailPcnt_20_95","CMSFairIsaacScore_Fund","eads14Fi02S_2","InstalMonthlyPayments_400_3k","RevolvingMonthlyPayments_1k_cap","ApprovedToRequested_0_100","NoSecIncome","coef_eads59Us01S_4","coef_HouseDeal_flag","coef_Liability_Asset_Ratio_40_90","coef_CBRAvailPcnt_20_95","coef_CMSFairIsaacScore_Fund","coef_OweTaxes_or_IRAWithdrawalHistry","coef_eads14Fi02S_2","coef_GuarantorCount","coef_RevolvingMonthlyPayments_1k_cap","coef_InstalMonthlyPayments_400_3k","coef_ApprovedToRequested_0_100","coef_NoSecIncome","coef_Intercept"],"data":[[569325.0,2,0.0,1,92,825,0.0,4,1.0,74,854,0.51,2,2.0,0.9,92.0,825.0,4.0,854.0,1000.0,0.51,0.0,0.11716245,0.299528064,0.392119645,-0.010826643,-0.004957868,0.339407077,0.061509795,0.3685047,0.000167603,0.000225742,0.902205454,-0.371734864,2.788087559]]}

解决方案 #

假设原DataFrame名为df，JSON字符串列名为json_col，提供两种简洁实现方式：

方法一：使用pd.json_normalize

import pandas as pd
import json

# 1. 将JSON字符串解析为字典格式
df['json_dict'] = df['json_col'].apply(json.loads)

# 2. 解析字典中的data列表，生成新的DataFrame
parsed_df = pd.json_normalize(df['json_dict'], record_path='data')

# 3. 为新DataFrame设置对应列名
parsed_df.columns = df['json_dict'].iloc[0]['columns']

# 4. 合并原DataFrame与解析后的结果，移除中间临时列
final_df = pd.concat([df.drop(['json_col', 'json_dict'], axis=1), parsed_df], axis=1)

方法二：使用apply结合pd.Series

import pandas as pd
import json

def parse_json_row(row):
    # 解析单条JSON字符串
    json_data = json.loads(row['json_col'])
    # 将data中的行数据转为Series，用columns作为索引
    return pd.Series(json_data['data'][0], index=json_data['columns'])

# 对每行应用解析函数，生成拆分后的列
parsed_df = df.apply(parse_json_row, axis=1)

# 合并到原DataFrame
final_df = pd.concat([df.drop('json_col', axis=1), parsed_df], axis=1)

补充说明 #

• 两种方法都能将JSON中的data数值与columns列名一一对应，最终得到包含所有拆分列的DataFrame。
• 如果JSON中data字段包含多行数据（即data是多行嵌套列表），可调整方法一中pd.json_normalize的参数，或在方法二中循环处理多行数据。

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