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, 2019 in Python by John
4,955 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, 2019 by Jimmy

Related Questions In Python

0 votes
1 answer
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
• 58,140 points
3,656 views
0 votes
0 answers

I want to extract particular field from nested streaming json that is published through kafka bus.

I have created my kafka consumer. Now ...READ MORE

Apr 17, 2019 in Python by Ash
254 views
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 22, 2019 in Python by Taj
• 1,080 points
310 views
0 votes
2 answers
+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 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 2,402 views
0 votes
0 answers
+5 votes
6 answers

Lowercase in Python

You can simply the built-in function in ...READ MORE

answered Apr 11, 2018 in Python by hemant
• 5,810 points
1,803 views
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, 2019 in Python by Raj
1,652 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, 2019 in Python by Jisha
2,925 views