CODEWITHSUNDEEP
jsonpandas
Learner
Learner

Reputation: 393

How to loop through json and create a dataframe

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

Upvotes: 0

Views: 3089

Answers (2)

Ynjxsjmh
Ynjxsjmh

Reputation: 30002

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']]

Upvotes: 3

Andrew Mascillaro
Andrew Mascillaro

Reputation: 948

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.

Upvotes: 2

Related Questions