Search code examples
pythonjsondictionarykeyflat

Flattening and mapping of keys of a nested JSON using python

I am very new to JSON. I am unable to grasp the structuring of JSON file.

I have a Json file that resembles like this,

{"employeeId":{"0":02100, "1":02101, "2":02102,... "1000000":021000000},
 "employeeName":{"0":"Smith", "1":"John", "2":"Mark",... "1000000":"Dave"},
 "employeeDept":{"0":"Work", "1":"Art", "2":"Mop",... "1000000":"Clean"},
 "employeeAddress":"0":"CA", "1":"TX", "2":"UT",... "1000000":"DC"}

I need to convert this to a flattened JSON using python language by mapping the integers indices of each key respectively as shown below

{"employeeId": 02100,
 "employeeName":"Smith",
 "employeeDept":"Work",
 "employeeAddress":"CA"},

{"employeeId": 02101,
 "employeeName":"John",
 "employeeDept":"Art",
 "employeeAddress":"TX"},

{"employeeId": 02102,
 "employeeName":"Mark",
 "employeeDept":"Mop",
 "employeeAddress":"UT"},
.
.
.
{"employeeId": 021000000,
 "employeeName":"Dave",
 "employeeDept":"Clean",
 "employeeAddress":"DC"}

I tried this 

sample_object1 = {"employeeId":{"0":"02100", "1":"02101", "2":"02102", "1000000":"021000000"},
 "employeeName":{"0":"Smith", "1":"John", "2":"Mark", "1000000":"Dave"},
 "employeeDept":{"0":"Work", "1":"Art", "2":"Mop", "1000000":"Clean"},
 "employeeAddress":{"0":"CA", "1":"TX", "2":"UT", "1000000":"DC"}}

from pandas.io.json import json_normalize
json_normalize(sample_object1)

And I got this


employeeAddress.0   employeeAddress.1   employeeAddress.1000000 employeeAddress.2   employeeDept.0  employeeDept.1  employeeDept.1000000    employeeDept.2  employeeId.0    employeeId.1    employeeId.1000000  employeeId.2    employeeName.0  employeeName.1  employeeName.1000000    employeeName.2
0   CA  TX  DC  UT  Work    Art Clean   Mop 02100   02101   021000000   02102   Smith   John    Dave    Mark
Solution

  • Here is the answer,

    sample_object = {"employeeId":{"0":"02100", "1":"02101", "2":"02102", "1000000":"021000000"},
 "employeeName":{"0":"Smith", "1":"John", "2":"Mark", "1000000":"Dave"},
 "employeeDept":{"0":"Work", "1":"Art", "2":"Mop", "1000000":"Clean"},
 "employeeAddress":{"0":"CA", "1":"TX", "2":"UT", "1000000":"DC"}}

    import pandas as pd
d = pd.DataFrame(sample_object)
d.to_json(orient = "records")

    Here is the intended output

    '[{"employeeId":"02100","employeeName":"Smith","employeeDept":"Work","employeeAddress":"CA"},{"employeeId":"02101","employeeName":"John","employeeDept":"Art","employeeAddress":"TX"},{"employeeId":"021000000","employeeName":"Dave","employeeDept":"Clean","employeeAddress":"DC"},{"employeeId":"02102","employeeName":"Mark","employeeDept":"Mop","employeeAddress":"UT"}]'