Python - How to convert JSON File to Dataframe

Question

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

Answer 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

Answer 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