如何用Pandas将多工作表Excel转换为含嵌套结构的单JSON

实现步骤 #

核心逻辑是通过关联字段（此处为app_id_c与lsi_app_id_c）将Sheet2的数据分组，再嵌套到Sheet1对应行中，最终导出符合要求的JSON结构。

1. 读取两个工作表的数据
   分别读取Sheet1和Sheet2的内容：

   import pandas as pd
   
   sheet1 = pd.read_excel('AppTest.xlsx', sheet_name='Sheet1')
   sheet2 = pd.read_excel('AppTest.xlsx', sheet_name='Sheet2')
   

2. 对Sheet2数据按关联字段分组，转换为嵌套列表
   以lsi_app_id_c为分组键，将每组数据转为字典列表，匹配目标JSON中的嵌套数组结构：

   # 分组后将每组数据转为字典列表，命名为目标字段名
   grouped_sheet2 = sheet2.groupby('lsi_app_id_c').apply(lambda x: x.to_dict('records')).reset_index(name='LOS_INPUT_FROMSAS')
   

3. 合并Sheet1与分组后的Sheet2数据
   通过关联字段将嵌套数据合并到Sheet1对应行，同时处理空值情况：

   # 左连接保留Sheet1所有行，关联字段匹配
   merged_data = pd.merge(sheet1, grouped_sheet2, left_on='app_id_c', right_on='lsi_app_id_c', how='left')
   # 无对应数据的行填充为空数组，避免出现null
   merged_data['LOS_INPUT_FROMSAS'] = merged_data['LOS_INPUT_FROMSAS'].fillna(value=pd.Series([[]], index=merged_data.index))
   # 移除多余的关联字段列
   merged_data = merged_data.drop('lsi_app_id_c', axis=1)
   

4. 导出为目标格式的JSON
   生成每行一个JSON对象的格式：

   merged_data.to_json('AppTest_json.json', orient='records', lines=True, force_ascii=False)
   

关键说明 #

• groupby.apply(lambda x: x.to_dict('records'))：直接将分组后的DataFrame转为字典列表，完美契合嵌套数组的结构需求。
• 左连接（how='left'）：确保Sheet1的所有数据行都被保留，不会因无对应Sheet2数据而丢失。
• 空值填充：避免无对应数据的行出现null，统一替换为空数组[]，符合JSON结构规范。

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