How to Load JSON File in Pandas

1. Load JSON file into Pandas DataFrame
2. Load an index-oriented JSON file into Pandas DataFrame
3. Load a Column-oriented JSON file into Pandas DataFrame

This tutorial explains how we can load a JSON file into Pandas DataFrame using the pandas.read_json() method.

Load JSON file into Pandas DataFrame

We can load JSON file into Pandas DataFrame using the pandas.read_json() function by passing the path of JSON file as a parameter to the pandas.read_json() function.

{
    "Name": {"1": "Anil", "2": "Biraj", "3": "Apil", "4": "Kapil"},
    "Age": {"1": 23, "2": 25, "3": 28, "4": 30},
}

The content of the example data.json file is shown above. We will create a DataFrame from the above JSON file.

import pandas as pd

df = pd.read_json("data.json")

print("DataFrame generated using JSON file:")
print(df)

Output:

DataFrame generated using JSON file:
    Name  Age
1   Anil   23
2  Biraj   25
3   Apil   28
4  Kapil   30

It displays the DataFrame generated from the data in the data.json file. We must make sure that we have the data.json file in our current working directory to generate the DataFrame; otherwise we need to provide the JSON file’s full path as an argument to the pandas.read_json() method.

The DataFrame formed from the JSON file depends upon the orientation of the JSON file. We have three different orientations of the JSON file in general.

• Index Oriented
• Value-Oriented
• Column Oriented

Load an index-oriented JSON file into Pandas DataFrame

{
    "0": {"Name": "Anil", "Age": 23},
    "1": {"Name": "Biraj", "Age": 25},
    "2": {"Name": "Apil", "Age": 26},
}

It is an example of an index-oriented JSON file where top-level keys represent the indices of the data.

import pandas as pd

df = pd.read_json("data.json")

print("DataFrame generated from Index Oriented JSON file:")
print(df)

Output:

DataFrame generated from Index Oriented JSON file:
         0      1     2
Name  Anil  Biraj  Apil
Age     23     25    26

It will create a DataFrame from the data.json file with top-level keys represented as columns in the DataFrame.

Load a value-oriented JSON file into Pandas DataFrame

[["Anil", 23], ["Biraj", 25], ["Apil", 26]]

It is an example of a value-oriented JSON file where each element of the array represents each row’s values.

import pandas as pd

df = pd.read_json("data.json")

print("DataFrame generated from Value Oriented JSON file:")
print(df)

Output:

DataFrame generated from Value Oriented JSON file:
       0   1
0   Anil  23
1  Biraj  25
2   Apil  26

It will create a DataFrame from the data.json file where each element of the array in the JSON file will be represented as a row in the DataFrame.

Load a Column-oriented JSON file into Pandas DataFrame

{"Name": {"1": "Anil", "2": "Biraj", "3": "Apil"}, "Age": {"1": 23, "2": 25, "3": 28}}

It is an example of a column-oriented JSON file top-level index representing the column’s name for the data.

import pandas as pd

df = pd.read_json("data.json")

print("DataFrame generated from  Column Oriented JSON file:")
print(df)

Output:

DataFrame generated from Column Oriented JSON file:
    Name  Age
1   Anil   23
2  Biraj   25
3   Apil   28

It will create a DataFrame from the data.json file where the JSON file’s top-level key will be represented as the column name in the DataFrame.