Read Nested Json as DataFrame

0 votes

Hi,

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|
+---+---+-----+------+-------+----------+------+-----+
Jul 25 in Python by John
115 views

1 answer to this question.

0 votes

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()
answered Jul 25 by Jimmy

Related Questions In Python

0 votes
1 answer

how to read a JSON from a file?

You can use with statement with open('strings.json') as ...READ MORE

answered Oct 24, 2018 in Python by Priyaj
• 56,900 points
77 views
0 votes
0 answers
0 votes
1 answer

Is there a way to store a huge dataset as a dataframe using Pandas?

This can be easliy done by using ...READ MORE

answered Jun 21 in Python by Taj
• 1,040 points
26 views
0 votes
1 answer
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 6 in Python by Neha
• 330 points

edited Jul 8 by Kalgi 283 views
+4 votes
6 answers
0 votes
1 answer

How to append a row to a Pandas dataframe?

You can use the append method provided by pandas ...READ MORE

answered May 9 in Python by Raj
166 views
0 votes
1 answer

How to find the sum of rows in Pandas dataframe?

You can use a combination groupby function with the sum() method. ...READ MORE

answered May 9 in Python by Jisha
253 views