如何将Pandas DataFrame转换为指定结构的JSON格式？

我有如下Pandas DataFrame数据：

import pandas as pd

data = {
  "mode": ["single_table_list", "single_table_list", "single_table_list", "relational_table_list", "relational_table_list"],
  "type": ["type_a", "type_b", "type_c", "parent_table", "child_table"],
  "file_name": ["file_a", "file_b", "file_c", "file_d", "file_e"],
  "file_path": ["path_a", "path_b", "path_c", "path_d", "path_e"],
  "file_sample": ["sample_a", "sample_b", "sample_c", "sample_d", "sample_e"],
  "target_file_name": ["tgt_name_a", "tgt_name_b", "tgt_name_c", "tgt_name_d", "tgt_name_e"],
  "target_file_path": ["tgt_path_a", "tgt_path_b", "tgt_path_c", "tgt_path_d", "tgt_path_e"],
  "child_table": ["","","","child_d",""],
  "parent_key": ["","","","key_d",""],
  "parent_table": ["","","","","parent_e"],
  "child_key": ["","","","","key_e"]
}
df = pd.DataFrame(data)

需要将其转换为如下结构的JSON：

{
  "single_table_list": {
    "1":{
    "type": "type_a",
    "file_name": "file_a",
    "file_path": "path_a",
    "file_sample": "sample_a",
    "target_file_name": "tgt_name_a",
    "target_file_path": "tgt_path_a"
    },
    "2":{
    "type": "type_b",
    "file_name": "file_b",
    "file_path": "path_b",
    "file_sample": "sample_b",
    "target_file_name": "tgt_name_b",
    "target_file_path": "tgt_path_b"
    },
    "3":{
    "type": "type_c",
    "file_name": "file_c",
    "file_path": "path_c",
    "file_sample": "sample_c",
    "target_file_name": "tgt_name_c",
    "target_file_path": "tgt_path_c"
    }
  },
  "relational_table_list": {
    "1":{
      "parent_table_list":{
        "type": "type_parent",
        "file_name": "file_d",
        "file_path": "path_d",
        "file_sample": "sample_d",
        "target_file_name": "tgt_name_d",
        "target_file_path": "tgt_path_d",
        "child_table": "child_d",
        "parent_key": "key_d"
        },
      "child_table_list":{
        "type": "type_child",
        "file_name": "file_e",
        "file_path": "path_e",
        "file_sample": "sample_e",
        "target_file_name": "tgt_name_e",
        "target_file_path": "tgt_path_e",
        "parent_table": "parent_e",
        "child_key": "key_e"
        }
    }  
  }
}

实现步骤 #

1. 拆分数据并处理单表部分 #

筛选mode为single_table_list的行，剔除全空列，将每行转换为字典并以字符串序号作为键：

# 处理single_table_list部分
single_df = df[df['mode'] == 'single_table_list'].drop(columns=['mode'])
# 移除所有值为空的列
single_df = single_df.dropna(axis=1, how='all')
# 转换为目标结构
single_table_data = {str(i+1): row.dropna().to_dict() for i, row in single_df.iterrows()}

2. 处理关联表部分 #

筛选mode为relational_table_list的行，分别提取父表、子表数据，替换type字段值并保留非空列：

# 处理relational_table_list部分
relational_df = df[df['mode'] == 'relational_table_list'].drop(columns=['mode'])

# 提取父表数据并修改type字段
parent_row = relational_df[relational_df['type'] == 'parent_table'].dropna(axis=1, how='all').iloc[0]
parent_row['type'] = 'type_parent'
parent_data = parent_row.to_dict()

# 提取子表数据并修改type字段
child_row = relational_df[relational_df['type'] == 'child_table'].dropna(axis=1, how='all').iloc[0]
child_row['type'] = 'type_child'
child_data = child_row.to_dict()

# 组合关联表结构
relational_table_data = {
    "1": {
        "parent_table_list": parent_data,
        "child_table_list": child_data
    }
}

3. 合并结果并生成JSON #

将两部分数据合并为最终字典，再转换为格式化JSON：

import json

# 合并最终结构
final_result = {
    "single_table_list": single_table_data,
    "relational_table_list": relational_table_data
}

# 生成带缩进的JSON字符串
json_output = json.dumps(final_result, indent=2)
print(json_output)

运行上述代码即可得到目标格式的JSON输出。

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