Need Help, Pandas read nested json file

Solution for Need Help, Pandas read nested json file
is Given Below:

I was wondering if someone can help me with reading a nested json file like below into a dataframe. Any help is greatly appreciated.

[
      {
        "cust_batch_id": "1",
        "warning_count": 0,
        "used_vehicles": {
          "used_vehicle_list": [
            {
              "publish_date": "7/30/2021",
              "road_assist_warranty": "5-year/60,000-mile",
              "model_number_list": [
                "K8H"
              ]
            }
          ],
          "template": 4,
         "data_available": true
        }
      },
      {
        "cust_batch_id": "1",
        "warning_count": 0,
        "used_vehicles": {
          "used_vehicle_list": [
            {
              "publish_date": "7/30/2021",
              "road_assist_warranty": "3-year/36,000-mile, ",
              "model_number_list": [
                "FC2F5JEW"
              ]
            }
          ],
          "template": 4,
          "data_available": true
        }
      },
      {
        "cust_batch_id": "1",
        "warning_count": 0,
        "used_vehicles": {
          "used_vehicle_list": [
            {
              "publish_date": "7/30/2021",
              "road_assist_warranty": "5-year/60,000-mile",
              "model_number_list": [
                "P0L"
              ]
            }
          ],
          "template": 4,
          "data_available": true
        }
      }
    ]

  • js is the sample JSON you have provided
  • simple case of normalize JSON, explode() the list, then map embedded dict to columns
df = pd.json_normalize(js)
df.join(df.loc[:,"used_vehicles.used_vehicle_list"].explode().apply(pd.Series))
cust_batch_id warning_count used_vehicles.used_vehicle_list used_vehicles.template used_vehicles.data_available publish_date road_assist_warranty model_number_list
0 1 0 [{‘publish_date’: ‘7/30/2021’, ‘road_assist_warranty’: ‘5-year/60,000-mile’, ‘model_number_list’: [‘K8H’]}] 4 True 7/30/2021 5-year/60,000-mile [‘K8H’]
1 1 0 [{‘publish_date’: ‘7/30/2021’, ‘road_assist_warranty’: ‘3-year/36,000-mile, ‘, ‘model_number_list’: [‘FC2F5JEW’]}] 4 True 7/30/2021 3-year/36,000-mile, [‘FC2F5JEW’]
2 1 0 [{‘publish_date’: ‘7/30/2021’, ‘road_assist_warranty’: ‘5-year/60,000-mile’, ‘model_number_list’: [‘P0L’]}] 4 True 7/30/2021 5-year/60,000-mile [‘P0L’]

prerequisites

  • how to save JSON to a file and how to load JSON from a file
  • this is your sample JSON
  • you can also decode JSON encoded in a string with json.loads(str)
import json
from pathlib import Path

js = [{'cust_batch_id': '1',
  'warning_count': 0,
  'used_vehicles': {'used_vehicle_list': [{'publish_date': '7/30/2021',
     'road_assist_warranty': '5-year/60,000-mile',
     'model_number_list': ['K8H']}],
   'template': 4,
   'data_available': True}},
 {'cust_batch_id': '1',
  'warning_count': 0,
  'used_vehicles': {'used_vehicle_list': [{'publish_date': '7/30/2021',
     'road_assist_warranty': '3-year/36,000-mile, ',
     'model_number_list': ['FC2F5JEW']}],
   'template': 4,
   'data_available': True}},
 {'cust_batch_id': '1',
  'warning_count': 0,
  'used_vehicles': {'used_vehicle_list': [{'publish_date': '7/30/2021',
     'road_assist_warranty': '5-year/60,000-mile',
     'model_number_list': ['P0L']}],
   'template': 4,
   'data_available': True}}]

fn = Path.cwd().joinpath("SO_js.json")
# save to a file
with open(fn, "w") as f: json.dump(js, f)
# load from a file
with open(fn) as f: js = json.load(f)