Nested JSON to Dataframe in Python 3.7

Question

Having difficulty trying to convert a nested JSON object to a dataframe in my desired format. Assumed this would be really simple, but nearly tearing my hair out!

Here is an example of my JSON structure.

{
  "recipe1" : {
       "abbie" : 2,
       "ben" : 3,
       "chris" : 1
       },
  "recipe2" : {
       "abbie" : 1,
       "ben" : 5,
       "chris" : 5
       }
}

After trying a few different options I decided to use the pandas library as it seems the easiest to use.

Result in this example is a query to my database fetching the JSON data which I managed to get working.

 dataframe = json_normalize(result)
 print(dataframe)

Gives me the following on a single line:

 recipe1.abbie | recipe1.ben | recipe1.chris | recipe2.abbie | ..
      2        |      3      |       1       |       1       | ..

Although ideally I would like the data frame to look like this:

               |   Recipe 1  |   Recipe 2    |  
  Abbie        |      2      |       1       |
  Ben          |      3      |       5       |
  Chris        |      1      |       5       |

After looking on this site and elsewhere I believe the Pivot function is what I need to use, but after trying all morning I'm no closer to the solution unfortunately.

thanks in advance for any help, would greatly appreciate it.

blue note · Accepted Answer · 2019-04-03 14:27:46Z

2

Just use

frame = pd.DataFrame.from_records(data)

The normalize you are using flattens the dictionary and creates a Series.

answered Apr 3, 2019 at 14:27

blue note

29.5k10 gold badges83 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RiceCrispy Over a year ago

Well now I'm feeling pretty dumb! Thankyou. Will accept your answer in a bit, apparently I need to wait 7 minutes.

Anzel · Accepted Answer · 2019-04-03 14:33:40Z

0

You can use pd.read_json and pass orient=records (it's default anyway) to read your json file either in "filename" or a file buffer.

>>> import pandas as pd
>>> df = pd.read_json("sample.json", orient="records")
>>> df
       recipe1  recipe2
abbie        2        1
ben          3        5
chris        1        5

Comments

user7394313 · Accepted Answer · 2019-04-03 14:38:05Z

0

I assume your json continues with 'recipe3' and so on? Can there be other people than Abbie, Ben and Chris?

In any case, I doubt you can get what you want without looping through the data and format it as you want. I also assume that the data you get originally is an array, you should work from that. Without knowing more about your db structure and what possible data you'd be working with, I cannot help you further.

answered Apr 3, 2019 at 14:38

user7394313

5625 silver badges4 bronze badges

Collectives™ on Stack Overflow

Nested JSON to Dataframe in Python 3.7

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related