Parsing JSON to Dataframe python

Question

I have a JSON file of the format

{
    "keyone": {
      "col1": "or",
      "col2": "abc",
      "col3": "bcd",
      "col4": "false"
    },
    "keytwo": {
      "col1": "aaa",
      "col2": "bbb",
      "col3": "ccc",
      "col4": "true"
    },
    "keythree": {
      "col1": "zor",
      "col2": "hhh",
      "col3": "lll",
      "col4": "false"
    }
  }

And I wanna convert in to Pandas dataframe suck that for each key we have a row with the required values

key       col1    col3 
keyone    or      bcd
keytwo    and     ccc
keythree  zor     lll

I have looked at JSON parsers but most of them are kinda complicated.

How does pd.read_json(json_data, orient='index') work for you? — cs95
– cs95, Commented Feb 14, 2019 at 21:14

mad_ · Accepted Answer · 2019-02-14 21:22:42Z

8

You can read json data like below

import pandas as pd
df = pd.read_json("""{
    "keyone": {
      "col1": "or",
      "col2": "abc",
      "col3": "bcd",
      "col4": "false"
    },
    "keytwo": {
      "col1": "aaa",
      "col2": "bbb",
      "col3": "ccc",
      "col4": "true"
    },
    "keythree": {
      "col1": "zor",
      "col2": "hhh",
      "col3": "lll",
      "col4": "false"
    }
  }""")
df.T

OUTPUT

edited Feb 14, 2019 at 21:22

answered Feb 14, 2019 at 21:16

mad_

8,2832 gold badges31 silver badges46 bronze badges

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

Comments

m13op22 · Accepted Answer · 2019-02-14 23:50:12Z

-1

You can use pandas to read the file in directly like

import pandas as pd    

df = pd.read_json("""{
    "keyone": {
      "col1": "or",
      "col2": "abc",
      "col3": "bcd",
      "col4": "false"
    },
    "keytwo": {
      "col1": "aaa",
      "col2": "bbb",
      "col3": "ccc",
      "col4": "true"
    },
    "keythree": {
      "col1": "zor",
      "col2": "hhh",
      "col3": "lll",
      "col4": "false"
    }
  }""")

df.T

edited Feb 14, 2019 at 23:50

m13op22

2,3792 gold badges20 silver badges41 bronze badges

answered Feb 14, 2019 at 21:24

sdf

11 bronze badge

1 Comment

d_kennetz Over a year ago

How is this answer different from the other one posted?

Collectives™ on Stack Overflow

Parsing JSON to Dataframe python

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related