Read Nested Json as...
 
Notifications
Clear all

Read Nested Json as DataFrame  

  RSS

Abhijith
(@abhijith)
Noble Member
Joined: 4 months ago
Posts: 1161
31/03/2021 10:13 am  

I have a nested json and want to read as a dataframe. I tried multiple options but the data is not coming into separate columns.

Below is the Josn followed by expected output or similar output in such a way that all the data can be represented in one data frame.(table format)

JSON:
{"age":34, 
"id":1, 
"name":"Tony", 
"salary":1400, 
"dept_no":[10,20], 
"Cntct":{"Phone":[1234567890,9876543210], "state":"NY", "Pin":12345}}
{"age":18, 
"id":2, 
"name":"Peter", 
"salary":1000, 
"dept_no":[20], 
"Cntct":{"Phone":[7890123456], "state":"NY", "Pin":12345}}

EXPECTED OUTPUT: 
+---+---+-----+------+-------+----------+------+-----+
|age|id |name |salary|dept_no|Phone |State |Pin | 
+---+---+-----+------+-------+----------+------+-----+
|34 |1 |Tony |1400 |10 |1234567890|NY |12345|
|34 |1 |Tony |1400 |20 |9876543210|NY |12345|
|18 |2 |Peter|1000 |20 |7890123456|NY |12345|
+---+---+-----+------+-------+----------+------+-----+

Quote
Abhijith
(@abhijith)
Noble Member
Joined: 4 months ago
Posts: 1161
31/03/2021 10:14 am  

Here is the code for it.

da=[

{"age":34,

"id":1,

"name":"Tony",

"salary":1400,

"dept_no":[10,20],

"Cntct":{"Phone":[1234567890,9876543210], "state":"NY", "Pin":12345}},

{"age":18,

"id":2,

"name":"Peter",

"salary":1000,

"dept_no":[20],

"Cntct":{"Phone":[7890123456], "state":"NY", "Pin":12345}}]


from pandas.io.json import json_normalize

df = json_normalize(da)

df.head()

ReplyQuote
Share: