How to Load JSON String into Pandas DataFrame

To load a JSON string into Pandas DataFrame:

import pandas as pd

df = pd.read_json(r'Path where the JSON file is stored\File Name.json')

print(df)

Steps to Load JSON String into Pandas DataFrame

Step 1: Prepare the JSON String

To start with a simple example, let’s say that you have the following data about different products and their prices:

You can then capture the above data as a JSON string:

{"product":{"0":"computer","1":"tablet","2":"printer","3":"keyboard"},"price":{"0":800,"1":350,"2":200,"3":120}}

Step 2: Create the JSON File

Once you have your JSON string ready, save it as a JSON file.

For example, you may copy the JSON string into Notepad, and then save that file with a .json file extension.

For demonstration purposes, let’s assume that the JSON file name is data.json, and the path where the JSON file is stored is: C:\Users\Ron\Desktop\data.json

Step 3: Load the JSON File into Pandas DataFrame

Finally, load the JSON file into Pandas DataFrame using this generic syntax:

import pandas as pd

df = pd.read_json(r'Path where the JSON file is stored\File Name.json')

print(df)

For our example:

import pandas as pd

df = pd.read_json(r'C:\Users\Ron\Desktop\data.json')

print(df)

Run the code in Python (adjusted to your path), and you’ll get the following DataFrame:

    product  price
0  computer    800
1    tablet    350
2   printer    200
3  keyboard    120

3 different JSON strings

Below are 3 different ways to capture the data as JSON strings.

Each of those strings would generate a DataFrame with a different orientation when loading the files into Python.

(1) Index orientation

{"0":{"product":"computer","price":800},"1":{"product":"tablet","price":350},"2":{"product":"printer","price":200},"3":{"product":"keyboard","price":120}}

                0       1        2         3
product  computer  tablet  printer  keyboard
price         800     350      200       120

(2) Values orientation

[["computer",800],["tablet",350],["printer",200],["keyboard",120]]

          0    1
0  computer  800
1    tablet  350
2   printer  200
3  keyboard  120

(3) Columns orientation

{"product":{"0":"computer","1":"tablet","2":"printer","3":"keyboard"},"price":{"0":800,"1":350,"2":200,"3":120}}

    product  price
0  computer    800
1    tablet    350
2   printer    200
3  keyboard    120

You may pick the JSON string that generates your desired DataFrame. You can learn more about read_json by visiting the Pandas Documentation.

Alternatively, you may check the following guide for the steps to export Pandas DataFrame to a JSON file.