How to loop through json and create a dataframe

Question

I have a JSON file like below, how can I make a dataframe out of this. I want to make the main key an index and subkey as a column.

{
  "PACK": {
    "labor": "Recycle",
    "actual": 0,
    "Planned": 2,
    "max": 6
  },
  "SORT": {
    "labor": "Mix",
    "actual": 10,
    "Planned": 4,
    "max": 3
  }
}

The expected output is something like, I tried to use df.T but does not work. Any help on this is appreciated.

        actual  planned
PACK      0       2
SORT      10      4

i did df1 = pd.json_normalize(df) and its giving me 1 row with subkey as column — Learner
– Learner, Commented May 12, 2021 at 14:46

Ynjxsjmh · Accepted Answer · 2021-05-12 15:06:47Z

3

You can read your json file to dict. Then create dataframe with dict values as data and dict keys as index.

import json
import pandas as pd


with open('test.json') as f:
    data = json.load(f)

df = pd.DataFrame(data.values(), index=data.keys())

print(df)

        labor  actual  Planned  max
PACK  Recycle       0        2    6
SORT      Mix      10        4    3

The select columns with

df = df[['actual', 'planned']]

edited May 12, 2021 at 15:06

answered May 12, 2021 at 14:59

Ynjxsjmh

30.3k7 gold badges43 silver badges64 bronze badges

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

5 Comments

Andrew Mascillaro Over a year ago

It's significantly faster to use json.load(f) instead of json.loads(f.read()) because it doesn't need to create an intermediate string for the file contents

Ynjxsjmh Over a year ago

@AndrewMascillaro Thank you for your suggestion. BTW, your method is really a good method. Don't know that there is already built-in function for that.

Oxford_orange Over a year ago

If you have a lot of columns that you want to drop, you can drop all columns except those in a specific list. See stackoverflow.com/questions/45846189/…

Ynjxsjmh Over a year ago

@Oxford_orange Thank you for your suggest. But I think selecting the targeted columns and dropping the unwanted column have the same effect.

Oxford_orange Over a year ago

Yes, the alternative of only keeping columns in a list is helpful with complex and variable datasets.

Andrew Mascillaro · Accepted Answer · 2021-05-12 14:55:10Z

2

Pandas can read JSON files in many formats. For your use case, the following option should read your data the way you want:

pd.read_json(json_file, orient="index")

More information about the orient option can be found at the official documentation.

answered May 12, 2021 at 14:55

Andrew Mascillaro

9562 gold badges9 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to loop through json and create a dataframe

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related