1

How to create pandas DataFrame from nested Json with list?

Expected output will have 9 columns in DataFrame (data is retrieved from server ), have tried pd.json_normalize() but it didn't work

'{\n
  "a": "1",\n
  "b": "2",\n
  "c": "3",\n
  "d": "4",\n
  "cd": [\n
    {\n 
      "i": "1",\n
      "ii": "2",\n
      "iii": "3",\n
      "iv": "4",\n
      "v": "5"
    }\n
  ]\n
}'
vervit khandelwal
  • 29
  • 5

2 Answers2

2

Here is a simple solution. You need to use json_normalize and set the record path as the 'cd' column.

import json
import pandas as pd


js = '{\n "a": "1",\n "b": "2",\n "c": "3",\n "d": "4",\n "cd": [{"i": "1",\n "ii": "2",\n "iii": "3",\n "iv": "4",\n "v": "5"}]}'
js = json.loads(js)

df = pd.json_normalize(js, record_path=['cd'], meta=['a', 'b', 'c', 'd'])
print(df)
# >    i ii iii iv  v  a  b  c  d
# > 0  1  2   3  4  5  1  2  3  4

If you need want a more in-depth explanation of the json_normalize function, I would suggest you to read this article.

Manas Sambare
  • 1,158
  • 2
  • 11
  • 22
0

See, if you find this method useful.

import pandas as pd
temp="""{\n
  "a": "1",\n
  "b": "2",\n
  "c": "3",\n
  "d": "4",\n
  "cd": [\n
    {\n 
      "i": "1",\n
      "ii": "2",\n
      "iii": "3",\n
      "iv": "4",\n
      "v": "5"
    }\n
  ]\n
}"""
temp=eval(temp.replace("\n",""))
df=pd.DataFrame()
i=0
for key,val in temp.items():
    if len(temp[key][0])>1:
        for k,v in temp[key][0].items():
            df.loc[i,k]=v
    else:
        df.loc[i,key]=val
        
print(df)
Hasan Bhagat
  • 395
  • 1
  • 8