如何使用pandas将CSV转换为指定层级的嵌套JSON格式

• 业务需求：将记录服务器配置的CSV文件转换为指定结构的多层嵌套JSON，目前已实现单台服务器JSON明细导出，但无法完成多层嵌套结构组装。CSV实际包含20余项配置参数，以下为6项核心参数示例。

示例CSV原始内容 #

,How many servers are required?*  ,3,,,
    
    ,Server Type,AWS,,,
    
    ,,,,,
    
    ,,,Server 1,Server 2,Server 3
    
    ,,,,,
    
    1,OS Type*,,Windows,Unix,Windows
    
    ,,,,,
    
    2,OS Version*,,Windows Server 2019,RHEL 
    7.9,Windows Server 2019
    
    ,,,,,
    
    ,,,,,
    
    3,Environment Tags ,,,,
    
    ,Name,,A,B,C
    
    ,Contact*,,abc@gmail.com,abc@gmail.com,a
    bc@gmail.com
    
    ,Owner,,ABC,EFG,HIJ
    
    ,,,,,
    
    4,EC2 Instance Type*,,t2.large,t2.large,
    t3.small
    
    ,,,,,
    
    5,Server environment*,,Non-production,No
    n-production,Non-production
    
    ,,,,,
    
    6,AWS Region*,,USEast_NorthVirginia,APAC
    _Singapore,USWest_Oregon

注：原始CSV存在单元格内容被换行拆分的情况，实际读取时需先做换行拼接处理。

期望输出的嵌套JSON结构 #

{
"How many servers required" : 3,
"server type": "AWS",

"Server1":{

   "Build_data": { 
  
      "OSType": "Windows",
      "OSVersion": "Windows Server 2019",
      "EnvironmentTags": 
       {
         "Name": "sasaasasa",
         "Contact": "abc@gmail.com",
         "Owner": "NA"
        },
      "EC2 instance Type": "t2.large"
               },

    "Post_build_data": {

       "Serverenvironment": "Non-production",
       "AWSRegion": "USEast_NorthVirginia"
                }
},
"Server2":{

   "Build_data": { 
  
      "OSType": "Windows",
      "OSVersion": "Windows Server 2019",
      "EnvironmentTags": 
       {
         "Name": "sasaasasa",
         "Contact": "abc@gmail.com",
         "Owner": "NA"
        },
      "EC2 instance Type": "t2.large"
               },

    "Post_build_data": {

       "Serverenvironment": "Non-production",
       "AWSRegion": "USEast_NorthVirginia"
                }
}
// 其余服务器结构以此类推
}

当前已实现代码 #

目前基于pandas编写的处理代码可正常读取并清洗所有服务器的基础平铺数据，但无法直接输出上述嵌套结构，代码如下：

import pandas as pd
df = pd.read_csv('C:/Users/Downloads/Three_servers_2022_06_16_18_50_04.csv',engine ='python',encoding= 'unicode_escape').dropna(how='all', axis=1).dropna(how='all')
df.replace("\u00a0","", inplace=True)
df = df.drop(df.columns[[0]], axis=1).fillna("NA")
df = df.rename(columns=df.iloc[0]).drop(df.index[0])
df["NA"] = df["NA"].str.replace(' ', '')
df["NA"] = df["NA"].str.replace('*', '')
df["NA"] = df["NA"].str.replace('?', '')
df = df.rename(index= df["NA"]).drop(df.columns[[0]], axis=1)
result = df.to_json('C:/Users/Downloads/check.json', orient="columns")

• 诉求：因多层嵌套JSON组装经验不足，需要可落地的实现思路，若问题描述缺少必要信息可补充说明。

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