This is my first ever question here :)
I want to flatten a Json file with multiple levels using pandas.json_normalize (or anything else that works!) in order to parse through all the levels. I tried many ways, such as adding record_path, max_levels but nothing works. the result I obtain is only 2 columns: one with time and the other is a string with all the rest of data. this is part of the data as it shows in the file:
When visualizing the following data you get this, belwo is the raw data from the file
{"data":[{"time":"1630914886924","Responses":[{ "lines": [ { "lineId": "1", "vehiclePositions": [ { "directionId": "8161", "distanceFromPoint": 1, "pointId": "8012" }, { "directionId": "8162", "distanceFromPoint": 0, "pointId": "8142" }, { "directionId": "8162", "distanceFromPoint": 0, "pointId": "8282" }, { "directionId": "8731", "distanceFromPoint": 0, "pointId": "8111" }, { "directionId": "8162", "distanceFromPoint": 1, "pointId": "8062" }, { "directionId": "8731", "distanceFromPoint": 0, "pointId": "8281" }, { "directionId": "8161", "distanceFromPoint": 1, "pointId": "8102" },
and this is my code:
import pandas as pd
import json
file = open("vp1.json")
json_obj = json.load(file)
df = pd.json_normalize(json_obj['data'])
This is the output when I do df.head():
time Responses
0 1630914886924 [{'lines': [{'lineId': '1', 'vehiclePositions'...
1 1630914917746 [{'lines': [{'lineId': '1', 'vehiclePositions'...
2 1630914948365 [{'lines': [{'lineId': '1', 'vehiclePositions'...
3 1630914978881 [{'lines': [{'lineId': '1', 'vehiclePositions'...
4 1630915011138 [{'lines': [{'lineId': '1', 'vehiclePositions'...