Python – How to convert JSON File to Dataframe

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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:

• pandas.DataFrame.from_dict
• pandas.json_normalize

Method 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

Method 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

Method 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.

2. 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)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0