基于列名称将数据帧转换为JSON

Question

我有一个数据帧，其中包含如下，我只是提供一行

Vessel_id,Org_id,Vessel_name,Good_Laden_Miles_Min,Good_Ballast_Miles_Min,Severe_Laden_Miles_Min,Severe_Ballast_Miles_Min


1,5,"ABC",10,15,25,35

我想将dataframe转换成如下格式的json

{ 
   Vessel_id:1,
   Vessel_name:"ABC",
   Org_id:5,
   WeatherGood:{ 
      Good_Laden_Miles_Min:10,
      Good_Ballast_Miles_Min:15
   },
   weatherSevere:{ 
      Severe_Laden_Miles_Min:25,
      Severe_Ballast_Miles_Min:35
   }
}

如何将所有以good开头的列连接到WeatherGood并转换为JSON

Answer 1

您可以首先将数据帧转换为记录字典，然后将每条记录转换为所需的格式。最后，将记录列表转换为JSON

import json

records = df.to_dict('records')
for record in records:
    record['WeatherGood'] = {
        k: record.pop(k) for k in ('Good_Laden_Miles_Min', 'Good_Ballast_Miles_Min')
    }
    record['WeatherSevere'] = {
        k: record.pop(k) for k in ('Severe_Laden_Miles_Min', 'Severe_Ballast_Miles_Min')
    }
>>> json.dumps(records)
'[{"Vessel_id": 1, "Org_id": 5, "Vessel_name": "ABC", "WeatherGood": {"Good_Laden_Miles_Min": 10, "Good_Ballast_Miles_Min": 15}, "WeatherSevere": {"Severe_Laden_Miles_Min": 25, "Severe_Ballast_Miles_Min": 35}}]'