Parsing multidimentional Json in python

Question

I have issue with parsing Json file. here the format i have:

    {'metadata': {'timezone': {'location': 'Etc/UTC'},
  'serial_number': '123456',
  'device_type': 'sensor'},
 'timestamp': '2019-08-21T13:57:12.500Z',
 'framenumber': '4866274',
 'tracked_objects': [{'id': 2491,
   'type': 'PERSON',
   'position': {'x': -361,
    'y': -2933,
    'type': 'FOOT',
    'coordinate_system': 'REAL_WORLD_IN_MILLIMETER'},
   'person_data': {'height': 1295}},
  {'id': 2492,
   'type': 'PERSON',
   'position': {'x': -733,
    'y': -2860,
    'type': 'FOOT',
    'coordinate_system': 'REAL_WORLD_IN_MILLIMETER'},
   'person_data': {'height': 1928}},
  {'id': 2495,
   'type': 'PERSON',
   'position': {'x': -922,
    'y': -3119,
    'type': 'FOOT',
    'coordinate_system': 'REAL_WORLD_IN_MILLIMETER'},
   'person_data': {'height': 1716}}]}

And I am trying to get next columns into dataframe: timezone, serial_number,id, x , y which are part of position, and height.

This is the code i used so far:

    # Import Dependencies
import pandas as pd
import json
from pandas.io.json import json_normalize

# loading json file. In your case you will point the data stream into json variable
infile = open("C:/Users/slavi/Documents/GIT/test2.json")
json_raw = json.load(infile)
# Functions to flaten multidimensional json file
def flatten_json(nested_json):
    out = {}
    def flatten(x, name=''):
        if type(x) is dict:
            for a in x:
                flatten(x[a], name + a + '_')
        elif type(x) is list:
            i = 0
            for a in x:
                flatten(a, name + str(i) + '_')
                i += 1
        else:
            out[name[:-1]] = x
    flatten(nested_json)
    return out
# Use Function to flaten json
json_flat = flatten_json(json_raw)
# Create panda dataframe from dictionary sine json itself is list of dictionaries or dictiornary of dictionaries
df = pd.DataFrame.from_dict(json_flat, orient='index')
# Reseting index
df.reset_index(level=0, inplace=True)
df.set_index('index', inplace=True)
df

I used the function to flaten the json however when i run the code I am getting results like this:

So there should be 3 lines of data for each tracked object and i should retrieve those columns with 3 lines of data under.

Any suggestion on how to adjust my code?

Code Different · Accepted Answer · 2019-08-21 19:24:37Z

1

For any kind of JSON parsing to DtaFrame, get acquanited to json_normalize:

import json
from pandas.io.json import json_normalize

with open('...', r) as f:
    json_raw = json.load(f)

df = json_normalize(json_raw, record_path='tracked_objects', meta=[
    ['metadata', 'serial_number'],
    'timestamp'
])

Result:

     id    type  position.x  position.y position.type position.coordinate_system  person_data.height metadata.serial_number                 timestamp
0  2491  PERSON        -361       -2933          FOOT   REAL_WORLD_IN_MILLIMETER                1295                 123456  2019-08-21T13:57:12.500Z
1  2492  PERSON        -733       -2860          FOOT   REAL_WORLD_IN_MILLIMETER                1928                 123456  2019-08-21T13:57:12.500Z
2  2495  PERSON        -922       -3119          FOOT   REAL_WORLD_IN_MILLIMETER                1716                 123456  2019-08-21T13:57:12.500Z

Rename the columns as you wish.

edited Aug 21, 2019 at 19:24

answered Aug 21, 2019 at 18:33

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Slavisha84 Over a year ago

Excellent. This was so simple. Thank you so much.

Slavisha84 Over a year ago

How do i get time and serial number? I tried using df = json_normalize(json_raw, record_path='metadata') to access that part of the json but it gives me an error: JSONDecodeError: Expecting value: line 1 column 1 (char 0)

Code Different Over a year ago

Use json_normalize(..., meta=[...]). See my edited answer

Slavisha84 Over a year ago

@ Code Different - this works however i still dont see time. I have tried using df = json_normalize(json_raw, record_path='tracked_objects', meta=[ ['metadata', 'serial_number', 'timestamp'] but it didnt like it.

Code Different Over a year ago

@Slavisha84 that's because Timestamp is on a different path. The json_normalize documentation is worth a read. Edited my answer again

Collectives™ on Stack Overflow

Parsing multidimentional Json in python

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related