How to Load JSON String into Pandas DataFrame

Looking to load a JSON string into pandas DataFrame?

If so, you can apply the following generic structure 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 tutorial, 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 Computer$700
Tablet$250
iPhone$800
Laptop$1200

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 chose to name 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 generic structure that you saw at the beginning of this tutorial:

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\doron\Desktop\data.json

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

import pandas as pd
df = pd.read_json (r'C:\Users\doron\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 read more about loading JSON strings into the DataFrame by visiting the pandas documentation.