How to Load JSON String into Pandas DataFrame

Looking to load a JSON string into Pandas DataFrame?

If so, you can use the following template to load your JSON string into the DataFrame:

import pandas as pd
pd.read_json (r'Path where you saved the JSON file\File Name.json')

In this short guide, I’ll review the steps to load different JSON strings into Python using pandas.

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:

ProductPrice
Desktop Computer700
Tablet250
iPhone800
Laptop1200

This data can be captured as a JSON string:

{"Product":{"0":"Desktop Computer","1":"Tablet","2":"iPhone","3":"Laptop"},"Price":{"0":700,"1":250,"2":800,"3":1200}}

Step 2: Create the JSON File

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

Alternatively, you can copy the JSON string into Notepad, and then save that file with a .json file extension.

For example, open Notepad, and then copy the JSON string into it:

copy json string to notepad

Then, save the notepad with your desired file name and add the .json extension at the end of the file name. Here, I named the file as data.json:

create json file using notepad

Step 3: Load the JSON File into Pandas DataFrame

Finally, load your JSON file into Pandas DataFrame using the template that you saw at the beginning of this guide:

import pandas as pd
pd.read_json (r'Path where you saved the JSON file\File Name.json')

In my case, I stored the JSON file on my Desktop, under this path:

C:\Users\Ron\Desktop\data.json

So this is the code that I used to load the JSON file into the DataFrame:

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:

How to Load JSON String into Pandas DataFrame

3 different JSON strings

Below are 3 different ways that you could 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":"Desktop Computer","Price":700},"1":{"Product":"Tablet","Price":250},"2":{"Product":"iPhone","Price":800},"3":{"Product":"Laptop","Price":1200}}

Load JSON String into Pandas DataFrame

(2) Values orientation

[["Desktop Computer",700],["Tablet",250],["iPhone",800],["Laptop",1200]]

Load JSON String into the DataFrame

(3) Columns orientation

{"Product":{"0":"Desktop Computer","1":"Tablet","2":"iPhone","3":"Laptop"},"Price":{"0":700,"1":250,"2":800,"3":1200}}

How to Load JSON String into Pandas DataFrame

You may then pick the JSON string that would generate your desired DataFrame. You can learn more about read_json by visiting the pandas documentation.

Now what if you want to export your DataFrame to JSON?

If that’s the case, you may want to check the following guide for the steps to export Pandas DataFrame to a JSON file.