Grouping the JSON based on multiple Keys using Python

Question

I have a data like this,

data = [{'vesselId': '1',"vesselName": "ALPHA 01",'weatherStatus': 'Good','ballastFlag':'B','milesPerMT_min': 'cycling','milesPerMT_max': 3,'milesPerMT_avg':6,"fuelPerMilesPerCargo": nan,'milesPerMT_avg': 3,'speedRange':'10-15'},
    {'vesselId': '1',"vesselName": "ALPHA 01",'weatherStatus': 'Good','ballastFlag':'L','milesPerMT_min': 'cycling','milesPerMT_max': 45,"fuelPerMilesPerCargo": nan,'milesPerMT_avg': 3,'speedRange':'5-10'},
    {'vesselId': '1',"vesselName": "ALPHA 01", 'weatherStatus': 'ROUGH','ballastFlag':'L','milesPerMT_min': 'reading', 'milesPerMT_max': 3.0,"fuelPerMilesPerCargo": nan,'milesPerMT_avg': 3,'speedRange':'10-15'},
    {'vesselId': '1',"vesselName": "ALPHA 01", 'weatherStatus': 'ROUGH','ballastFlag':'L','milesPerMT_min': 'reading', 'milesPerMT_max': 3.0,"fuelPerMilesPerCargo": nan,'milesPerMT_avg': 3,'speedRange':'15-20'}]

I have written code to convert the data into json format:

new_data = []
not_found = True
for item in data:
    for vesselId in new_data:
        not_found = True
        if item['vesselId'] == vesselId['vesselId']:
            not_found = False
            for weatherStatus in vesselId['Fuel_Performance']:
                if item['weatherStatus'] == weatherStatus['weatherStatus'] :
                    weatherStatus['milesPerMT'].append({'milesPerMT_min':item['milesPerMT_min'], 'milesPerMT_max':item['milesPerMT_max'],'milesPerMT_avg':item['milesPerMT_avg']})
                else:
                    vesselId['Fuel_Performance'].append({'weatherStatus':item['weatherStatus'],'ballastFlag':item['ballastFlag'], 'milesPerMT':[{'milesPerMT_min':item['milesPerMT_min'], 'milesPerMT_max':item['milesPerMT_max'],'milesPerMT_avg':item['milesPerMT_avg'],'Speed':item['speedRange']}]})
            break
    if not_found:
        new_data.append({'vesselId':item['vesselId'],'vesselName':item['vesselName'] , 'Fuel_Performance':[{'weatherStatus':item['weatherStatus'], \
            'ballastFlag':item['ballastFlag'],'milesPerMT':[{'milesPerMT_min':item['milesPerMT_min'], 'milesPerMT_max':item['milesPerMT_max'],'milesPerMT_avg':item['milesPerMT_avg']}],'fuelPerMilesPerCargo': item['fuelPerMilesPerCargo'],'Speed':item['speedRange']}]})

The output which am getting is like this ,

[{'Fuel_Performance': [{'Speed': '10-15',
                    'ballastFlag': 'B',
                    'fuelPerMilesPerCargo': nan,
                    'milesPerMT': [{'milesPerMT_avg': 6,
                                    'milesPerMT_max': 3,
                                    'milesPerMT_min': 8},
                                   {'milesPerMT_avg': 3,
                                    'milesPerMT_max': 45,
                                    'milesPerMT_min': 9}],
                    'weatherStatus': 'Good'},
                   {'ballastFlag': 'L',
                    'milesPerMT': [{'Speed': '10-15',
                                    'milesPerMT_avg': 3,
                                    'milesPerMT_max': 3.0,
                                    'milesPerMT_min': 10},
                                   {'milesPerMT_avg': 3,
                                    'milesPerMT_max': 3.0,
                                    'milesPerMT_min': 10},
                                   {'milesPerMT_avg': 3,
                                    'milesPerMT_max': 3.0,
                                    'milesPerMT_min': 11}],
                    'weatherStatus': 'ROUGH'},
                   {'ballastFlag': 'L',
                    'milesPerMT': [{'Speed': '15-20',
                                    'milesPerMT_avg': 3,
                                    'milesPerMT_max': 3.0,
                                    'milesPerMT_min': 11},
                                   {'milesPerMT_avg': 3,
                                    'milesPerMT_max': 3.0,
                                    'milesPerMT_min': 11}],
                    'weatherStatus': 'ROUGH'}],


 'vesselId': '1',
  'vesselName': 'ALPHA 01'}]

The way I want is like below,

    [
  {
    "vesselId": 1,
    "vesselName": "ALPHA 01",
    "fuelPerformance": {
      "Good": {
        "B": [
          {
            "speed": "10 - 15",
            "milesPerMT": {
              "Min": 8,
              "Max": 3,
              "Avg": 6
            },
            "fuelPerMilesPerCargo": nan
          }
        ],
        "L": [
          {
            "speed": "5 - 10",
            "milesPerMT": {
              "Min": 9,
              "Max": 45,
              "Avg": 3
            },
            "fuelPerMilesPerCargo": nan
          }
        ]
      },
      "Rough": {
        "L": [
          {
            "speed": "10 - 15",
            "milesPerMT": {
              "Min": 10,
              "Max": 3,
              "Avg": 3
            },
            "fuelPerMilesPerCargo": nan
          },
          {
            "speed": "15 - 20",
            "milesPerMT": {
              "Min": 11,
              "Max": 3,
              "Avg": 3
            },
            "fuelPerMilesPerCargo": nan
          }
        ]
      }
    }
  }
]

Am trying to group the values based on this below logic

If the weather_status is "good" and ballast_flag can be "B" or "L" Group all the key and values for B and L . Similarly when the weather_status is "Rough".

In my original output am not able to bring three things (speed, milesPerMT, fuelPerMilesPerCargo) inside the ballast_flag.

In my current code am not getting how to group those based on weather_status and ballast_flag

Does this answer your question? [How to prettyprint a JSON file?](https://stackoverflow.com/questions/12943819/how-to-prettyprint-a-json-file) — TheMisir, Jan 08 '20 at 10:27
@Rakesh, changed the formatting of output !! Sorry for that ! — venkat, Jan 08 '20 at 10:38
If you want Python to write inconsistent output, you really need to hand-code that Python code yourself. — tripleee, Jan 08 '20 at 10:47
What does this have to do with JSON other than that you want to encode it as JSON at some point? Your "code to convert the data into json format" doesn't convert anything to JSON, because JSON would be a string while you still have Python `lists` and `dicts`. — Ulrich Eckhardt, Jan 08 '20 at 11:37

score 1 · Accepted Answer · answered Jan 14 '20 at 14:55

For the problem of looping through the new_data and check if some elements are present I took the approach of maintaining a small meta dict which holds the information of vessleId, weatherStatus & ballastFlag like as follow

{'1': {'Good': ['B', 'L'], 'ROUGH': ['L']}}

The python script:

new_data = []
new_data_meta = {}


def get_vessel(_id):
    return list(filter(lambda vessel: vessel["vesselId"] == _id, new_data))[0]


for item in data:
    if item["vesselId"] in new_data_meta:
        _data = get_vessel(item["vesselId"])
        if item["weatherStatus"] in new_data_meta[item["vesselId"]]:
            if (
                item["ballastFlag"]
                in new_data_meta[item["vesselId"]][item["weatherStatus"]]
            ):
                _data["fuelPerformance"][item["weatherStatus"]][
                    item["ballastFlag"]
                ].append(
                    {
                        "speed": item["speedRange"],
                        "milesPerMT": {
                            "Min": item["milesPerMT_min"],
                            "Max": item["milesPerMT_max"],
                            "Avg": item["milesPerMT_avg"],
                        },
                        "fuelPerMilesPerCargo": item["fuelPerMilesPerCargo"],
                    }
                )
            else:
                _data["fuelPerformance"][item["weatherStatus"]][item["ballastFlag"]] = [
                    {
                        "speed": item["speedRange"],
                        "milesPerMT": {
                            "Min": item["milesPerMT_min"],
                            "Max": item["milesPerMT_max"],
                            "Avg": item["milesPerMT_avg"],
                        },
                        "fuelPerMilesPerCargo": item["fuelPerMilesPerCargo"],
                    }
                ]
                new_data_meta[item["vesselId"]][item["weatherStatus"]].append(
                    item["ballastFlag"]
                )
        else:
            _data["fuelPerformance"][item["weatherStatus"]] = {
                item["ballastFlag"]: [
                    {
                        "speed": item["speedRange"],
                        "milesPerMT": {
                            "Min": item["milesPerMT_min"],
                            "Max": item["milesPerMT_max"],
                            "Avg": item["milesPerMT_avg"],
                        },
                        "fuelPerMilesPerCargo": item["fuelPerMilesPerCargo"],
                    }
                ]
            }
            new_data_meta[item["vesselId"]][item["weatherStatus"]] = [
                item["ballastFlag"]
            ]

    else:
        new_data.append(
            {
                "vesselId": item["vesselId"],
                "vesselName": item["vesselName"],
                "fuelPerformance": {
                    item["weatherStatus"]: {
                        item["ballastFlag"]: [
                            {
                                "speed": item["speedRange"],
                                "milesPerMT": {
                                    "Min": item["milesPerMT_min"],
                                    "Max": item["milesPerMT_max"],
                                    "Avg": item["milesPerMT_avg"],
                                },
                                "fuelPerMilesPerCargo": item["fuelPerMilesPerCargo"],
                            }
                        ]
                    }
                },
            }
        )
        new_data_meta[item["vesselId"]] = {item["weatherStatus"]: [item["ballastFlag"]]}

Returns the following output:

[{
    "vesselId": "1",
    "vesselName": "ALPHA 01",
    "fuelPerformance": {
        "Good": {
            "B": [{
                "speed": "10-15",
                "milesPerMT": {
                    "Min": "cycling",
                    "Max": 3,
                    "Avg": 3
                },
                "fuelPerMilesPerCargo": nan
            }],
            "L": [{
                "speed": "5-10",
                "milesPerMT": {
                    "Min": "cycling",
                    "Max": 45,
                    "Avg": 3
                },
                "fuelPerMilesPerCargo": nan
            }]
        },
        "ROUGH": {
            "L": [{
                    "speed": "10-15",
                    "milesPerMT": {
                        "Min": "reading",
                        "Max": 3.0,
                        "Avg": 3
                    },
                    "fuelPerMilesPerCargo": nan
                },
                {
                    "speed": "15-20",
                    "milesPerMT": {
                        "Min": "reading",
                        "Max": 3.0,
                        "Avg": 3
                    },
                    "fuelPerMilesPerCargo": nan
                }
            ]
        }
    }
}]

score 0 · Answer 2 · answered Jan 08 '20 at 10:37

0

Here is how I do it:

import json

adict = {'key2': 'value2', 'key1': 'value1'}
print(json.dumps(adict, indent=4, sort_keys=True))

Output:

{
    "key1": "value1",
    "key2": "value2"                                                                                                                                                                                                                                                           
}

answered Jan 08 '20 at 10:37

V-R

1,309
16
32

I am not trying to do this !! – venkat Jan 08 '20 at 10:38
2

If that doesn't give you want you want, your question is bad. Please read [ask]. – Ulrich Eckhardt Jan 08 '20 at 10:40
@UlrichEckhardt, Improved the Question. Got confused !! My bad ! – venkat Jan 08 '20 at 10:48

Grouping the JSON based on multiple Keys using Python

2 Answers2

Linked