How to convert a list of OrderedDict to nested json with grouped keys in python

Question

I'm working on a project where I need to convert a set of data rows from database into list of OrderedDict for other purpose and use this list of OrderedDict to convert into a nested JSON format in python. I'm starting to learn python. I was able convert the query response from database which is a list of lists to list of OrderedDict.

I have the list of OrderedDict as below:

    {
'OUTBOUND': [
OrderedDict([('Leg', 1), ('SessionID', 'W12231fwfegwcaa2'),('FeeCode', 'ATO'),('SeatGroup', '2'),
               ('Currency', 'MXN'),('Modality', 'VB'),('BookingClass', 'A'),('Price', 145.0),('Num_Pax', 1),('Channel', 'Web')]),
  OrderedDict([('Leg', 1),('SessionID', 'W12231fwfegwcaa2'),('FeeCode', 'ATO'),('SeatGroup', '4'),
               ('Currency', 'MXN'),('Modality', 'VB'),('BookingClass', 'A'),('Price', 111.0),('Num_Pax', 1),('Channel', 'Web')]),
  OrderedDict([('Leg', 1),('SessionID', 'W12231fwfegwcaa2'),('FeeCode', 'BDM'),('SeatGroup', 'null'),
               ('Currency', 'MXN'),('Modality', 'VB'),('BookingClass', 'A'),('Price', 111.0),('Num_Pax', 1),('Channel', 'Web')]),
  OrderedDict([('Leg', 2),('SessionID', 'W12231fwfegwcaa2'),('FeeCode', 'ATO'),('SeatGroup', '1'),
                ('Currency', 'MXN'),('Modality', 'VB'),('BookingClass', 'U'),('Price', 180.0),('Num_Pax', 1),('Channel', 'Web'))]),
  OrderedDict([('Leg', 2),('SessionID', 'W12231fwfegwcaa2'),('FeeCode', 'ATO'),('SeatGroup', '4'),
                ('Currency', 'MXN'),('Modality', 'VB'),('BookingClass', 'U'),('Price', 97.0),('Num_Pax', 1),('Channel', 'Web')]),
  OrderedDict([('Leg', 2),('SessionID', 'W12231fwfegwcaa2'),('FeeCode', 'BDM'),('SeatGroup', 'null'),
                ('Currency', 'MXN'),('Modality', 'VB'),('BookingClass', 'U'),('Price', 97.0),('Num_Pax', 1),('Channel', 'Web')])
            ]
}

And I needed the nested format like below:

{
"OUTBOUND": [
    {
      "Leg": 1,
      "SessionID": "W12231fwfegwcaa2",
      "Modality": "VB",
      "BookingClass": "A",
      "FeeCodes":[
                    {
                        "FeeCode": "ATO",
                        "Prices":
                        [
                            {
                                "SeatGroup": "2",
                                "Price": 145.0,
                                "Currency": "MXN"
                            },
                            {
                                "SeatGroup": "4",
                                "Price": 111.0,
                                "Currency": "MXN"
                            }
                        ]
                    },
                    {
                        "FeeCode": "VBABDM",                
                        "Prices":
                        [ 
                            {
                                "SeatGroup": "null",
                                "Price": 111.0,
                                "Currency": "MXN"                   
                            }
                        ]
                    }
                ],
      "Num_Pax": 1,
      "Channel": "Web"
    },
    {
      "Leg": 2,
      "SessionID": "W12231fwfegwcaa2",
      "Modality": "VB",
      "BookingClass": "U",
      "FeeCodes":[
                    {
                        "FeeCode": "ATO",
                        "Prices":
                        [
                            {
                                "SeatGroup": "1",
                                "Price": 180.0,
                                "Currency": "MXN"
                            },
                            {
                                "SeatGroup": "4",
                                "price": 97.0,
                                "Currency": "MXN"
                            }
                        ]
                    },
                    {
                        "FeeCode": "VBABDM",                
                        "Prices":
                        [ 
                            {
                                "SeatGroup": "null",
                                "price": 97.0,
                                "Currency": "MXN"                   
                            }
                        ]
                    }
                ],
      "Num_Pax": 1,
      "Channel": "Web"
    }
    ]
}

If I'm not wrong, I need to group by Leg, SessionID, Modality, BookingClass, NumPax and Channel and group the FeeCode, SeatGroup, Price and Currency into nested format as above but unable to move ahead with how to loop and group for nesting.

It would be great if I could get some help. Thanks

Are you just trying to print the dictionary in that formatted way? — Joe, Feb 26 '21 at 12:01
I need to structure the dictionary into the given nested json format and pass on to next step. — Hariprasad Rao, Feb 26 '21 at 12:05

score 0 · Answer 1 · answered Mar 05 '21 at 08:14

I was able to write a python code to get the format as I needed using simple looping with a couple of changes in the output like the fields SessionID, Num_Pax and Channel is taken outside then the OUTBOUND field and fields within are generated.

Instead of OrderedDict, I used a list of lists as input which I convert into Pandas DataFrame and work with the DataFrame to get the nested format.

Below is the code I used:

outbound_df = pd.DataFrame(response_outbound,columns=All_columns)
Common_columns = ['Leg', 'Modality', 'BookingClass']

### Taking SessionID, AirlineCode,Num_Pax and Channel outside OUTBOUND part as they are common for all the leg level data
response_data['SessionID'] = outbound_df['SessionID'].unique()[0]   
response_data['Num_Pax'] = int(outbound_df['Num_Pax'].unique()[0])
response_data['Channel'] = outbound_df['Channel'].unique()[0]

temp_data = []
Legs = outbound_df['Leg'].unique()

for i in Legs:
    subdata = outbound_df[outbound_df['Leg']==i]
    
    ### Initializing leg_data dict
    leg_data = collections.OrderedDict()
        
    ### Populating common fields of the leg (Leg, Modality,BookingClass)
    for j in Common_columns: 
        if(j=='Leg'):
            leg_data[j] = int(subdata[j].unique()[0])
        else:
            leg_data[j] = subdata[j].unique()[0]
    
    leg_data['FeeCodes'] = []
    FeeCodes = subdata['FeeCode'].unique()
    
    for fc in FeeCodes:
        subdata_fees = subdata[subdata['FeeCode']==fc]
        
        Prices = {'FeeCode':fc, "Prices":[]}
        
        for _,rows in subdata_fees.iterrows():
            data = {}
            data['SeatGroup'] = rows['SeatGroup']
            data['Price'] = float(rows['Price'])
            data['Currency'] = rows['Currency']
            
            Prices["Prices"].append(data)
        
        leg_data["FeeCodes"].append(Prices)
    temp_data.append(leg_data)

response_data["OUTBOUND"] = temp_data

I can just do json.dumps on response_data to get json format which will be sent to the next steps.

Below is the output format I get:

{
   "SessionID":"W12231fwfegwcaa2",
   "Num_Pax":1,
   "Channel":"Web",
   "OUTBOUND":[
      {
         "Leg":1,
         "Modality":"VB",
         "BookingClass":"A",
         "FeeCodes":[
            {
               "FeeCode":"ATO",
               "Prices":[
                  {
                     "SeatGroup":"2",
                     "Price":145.0,
                     "Currency":"MXN"
                  },
                  {
                     "SeatGroup":"4",
                     "Price":111.0,
                     "Currency":"MXN"
                  }
               ]
            },
            {
               "FeeCode":"VBABDM",
               "Prices":[
                  {
                     "SeatGroup":"null",
                     "Price":111.0,
                     "Currency":"MXN"
                  }
               ]
            }
         ]
      },
      {
         "Leg":2,
         "Modality":"VB",
         "BookingClass":"U",
         "FeeCodes":[
            {
               "FeeCode":"ATO",
               "Prices":[
                  {
                     "SeatGroup":"1",
                     "Price":180.0,
                     "Currency":"MXN"
                  },
                  {
                     "SeatGroup":"4",
                     "price":97.0,
                     "Currency":"MXN"
                  }
               ]
            },
            {
               "FeeCode":"VBABDM",
               "Prices":[
                  {
                     "SeatGroup":"null",
                     "price":97.0,
                     "Currency":"MXN"
                  }
               ]
            }
         ]
      }
   ]
}

Please let me know if we can shorten the code in terms of lengthy iterations or any other changes. Thanks. PS: Sorry for my editing mistakes

Ersain · Answer 2 · 2021-02-26T12:05:27.470

-1

Assuming that you stored the dictionary to some variable foo, you can do:

import json

json.dumps(foo)

And be careful, you added extra bracket in the 4th element OUTBOUND list

edited Feb 26 '21 at 12:05

answered Feb 26 '21 at 12:03

Ersain

627
3
14

I believe he is looking for the `indent` parameter to be passed here – Joe Feb 26 '21 at 12:04
I suppose my indentation is incorrect but I checked online, there seems be no error with json structure. – Hariprasad Rao Feb 26 '21 at 12:08

How to convert a list of OrderedDict to nested json with grouped keys in python

2 Answers2