CODEWITHSUNDEEP
pythonjsonpandasdataframe
venkat
venkat

Reputation: 1263

Convert the dataframe to JSON Based on Column name

I have a dataframe which contains like this below, Am just providing one row !

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

I want to convert the dataframe to json in this format below,

{ 
   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
   }
}

how to join all those columns starting with good into a WeatherGood and convert to JSON?

Upvotes: 1

Views: 594

Answers (1)

Alexander
Alexander

Reputation: 109546

You can first convert the dataframe to a dictionary of records, then transform each record to your desired format. Finally, convert the list of records to 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}}]'

Upvotes: 1

Related Questions