Pandas read_json — Convert a JSON string to pandas object.

Pandas read_json

In this article, we implement a python library that works with the labeled data ie pandas. It focuses on operations on relational data, here we would convert JSON string pandas DataFrame with the help of the read_json() function. Multiple manipulations of the function will be carried out to receive the desired output.

Also read: How to Read a JSON File in Python

What is read_json()?

JSON is the most used format to exchange data between two systems or web applications hence often processing of JSON files is needed. read_json() function allows us to read a JSON file and display it in grid format.

Syntax of read_json()

pandas.read_json('string/path/file_name', orient)


string/path/file_nameAccepts JSON input as the first parameter. Could be in three formats,
1. A JSON string: we can input a JSON string.
2. A path to the JSON file: specifying JSON file name along with the path.
3. The JSON file’s name: If the JSON file is in the current directory, we can specify its name only.
orientThe orientation of the JSON string.
Possible formats: index, record, values, or columns
read_json() syntax parameter

Return value
It returns JSON data as DataFrame or series also it depends on the orient value.

Also read: How to convert Dictionary into JSON in Python?

Examples of read_json()

We begin by importing the pandas library.

import pandas as pd

Example 1: record-oriented JSON string into Pandas Dataframe

json_data ='''[{ "id": "101", "Name": "Emily","Age":22 ,"Department" : "HR" },
    { "id": "102", "Name": "Charles","Age":22,"Department" : "IT"},
    { "id": "103", "Name": "George","Age":21,"Department" : "Finance"},
    { "id": "104", "Name": "Emma","Age":23,"Department" : "HR"}]'''

df= pd.read_json(json_data,orient='records')

We input a JSON string that stores the data as a dictionary of values with another list. Such input can be reserved by ‘records’ orientation. We would convert the JSON string containing four key-value pairs in 4 rows to Pandas Dataframe.

Orient Records

Example 2: index-oriented JSON string into Pandas Dataframe

{"employee-1": {"id": "101", "Name": "Emily","Age":22 ,"Department" : "HR" },
  "employee-2": { "id": "102", "Name": "Charles","Age":22,"Department" : "IT"},
  "employee-3":  { "id": "103", "Name": "George","Age":21,"Department" : "Finance"},
    "employee-4": { "id": "104", "Name": "Emma","Age":23,"Department" : 
df= pd.read_json(json_data,orient='index')

For the input JSON string containing data in a dictionary of values surrounded by a dictionary with a key as an index, we would be using the ‘index’ orientation.

In this example, we created the JSON string with four key-value pairs in 4 rows with each row specified by index ie the employee number. In the output, the indices will be the rows in the data frame.

Orient Index

Example 3: column-oriented JSON string into Pandas Dataframe

{"employee-1": {"id": "101", "Name": "Emily","Age":22 ,"Department" : "HR" },
  "employee-2": { "id": "102", "Name": "Charles","Age":22,"Department" : "IT"},
  "employee-3":  { "id": "103", "Name": "George","Age":21,"Department" : "Finance"},
    "employee-4": { "id": "104", "Name": "Emma","Age":23,"Department" : 

df= pd.read_json(json_data,orient='columns')

When orient = columns the column indices will be the column in the data frame. Here the column name id, name,age, and department are the indices.

Orient Columns

Example 4: values-oriented JSON string into Pandas Dataframe

    [ "101","Emily",22 ,"HR" ],
    [ "102","Charles",22,"IT" ],
    [ "103","George",21, "Finance" ],
    [ "104", "Emma",23,"HR" ]

df = pd.read_json(json_data,orient='values')

In this example, the four key-values paired data have their column and row by default starting with 0.

Orient Value


In this article, we have learned how to convert a JSON from the string into Panda DataFrame and also use different optional parameters with examples. Browse more articles at AskPython.