Python - How to convert JSON File to Dataframe

122,888

Solution 1

Creating dataframe from dictionary object.

import pandas as pd
data = [{'name': 'vikash', 'age': 27}, {'name': 'Satyam', 'age': 14}]
df = pd.DataFrame.from_dict(data, orient='columns')

df
Out[4]:
   age  name
0   27  vikash
1   14  Satyam

If you have nested columns then you first need to normalize the data:

data = [
  {
    'name': {
      'first': 'vikash',
      'last': 'singh'
    },
    'age': 27
  },
  {
    'name': {
      'first': 'satyam',
      'last': 'singh'
    },
    'age': 14
  }
]

df = pd.DataFrame.from_dict(pd.json_normalize(data), orient='columns')

df    
Out[8]:
age name.first  name.last
0   27  vikash  singh
1   14  satyam  singh

Source:

Solution 2

import pandas as pd
print(pd.json_normalize(your_json))

This will Normalize semi-structured JSON data into a flat table

Output

  FirstName LastName MiddleName password    username
      John     Mark      Lewis     2910  johnlewis2

Solution 3

jsondata = '{"0001":{"FirstName":"John","LastName":"Mark","MiddleName":"Lewis","username":"johnlewis2","password":"2910"}}'
import json
import pandas as pd
jdata = json.loads(jsondata)
df = pd.DataFrame(jdata)
print df.T

This should look like this:.

         FirstName LastName MiddleName password    username
0001      John     Mark      Lewis     2910  johnlewis2

Solution 4

There are 2 inputs you might have and you can also convert between them.

  1. input: listOfDictionaries --> use @VikashSingh solution

example: [{"":{"...

The pd.DataFrame() needs a listOfDictionaries as input.

  1. input: jsonStr --> use @JustinMalinchak solution

example: '{"":{"...

If you have jsonStr, you need an extra step to listOfDictionaries first. This is obvious as it is generated like:

jsonStr = json.dumps(listOfDictionaries)

Thus, switch back from jsonStr to listOfDictionaries first:

listOfDictionaries = json.loads(jsonStr)
Share:
122,888
Techno04335
Author by

Techno04335

Always a student in this reality we call life :)

Updated on November 06, 2020

Comments

  • Techno04335
    Techno04335 about 3 years

    How can I convert a JSON File as such into a dataframe to do some transformations.

    For Example if the JSON file reads:

    {"FirstName":"John",
    
    "LastName":"Mark",
    
    "MiddleName":"Lewis",
    
    "username":"johnlewis2",
    
    "password":"2910"}
    

    How can I convert it to a table like such

    Column -> FirstName | LastName | MiddleName | username | password
    
    
    
    Row ----->    John | Mark |Lewis | johnlewis2 |2910
    
  • Techno04335
    Techno04335 almost 7 years
    Thanks! Very Useful
  • Techno04335
    Techno04335 almost 7 years
    Thanks, @Marlon Abeykoon, very useful especially if I want to import a JSON
  • Marlon Abeykoon
    Marlon Abeykoon almost 7 years
    Glad that it helped you