Python Pandas - Read JSON

JavaScript Object Notation (JSON) is a popular data-interchange format for exchanging structured data. Python's Pandas library provides an easy-to-useread_json() method for reading JSON data into its powerful DataFrame or Series objects.

In this tutorial, we see advanced options for reading JSON data into Pandas objects, which includes working with nested JSON structures, handling line-delimited JSON, managing data types, and parsing dates using thepandas.read_json() method efficiently and effectively.

Introduction to read_json() Method

Thepandas.read_json() method reads JSON data from various sources (like local file, URL, or JSON string) and converts it into a Pandas object.

Syntax

The syntax of this method is as follows −

pandas.read_json(path_or_buf, *, orient=None, typ='frame', dtype=None, lines=False)

Below are the key Parameters of the method −

• path_or_buf: The input JSON string, file path, URL, or file-like object.

• typ: Specifies the type of Pandas object to return ("frame" for DataFrame or "series" for Series).

• orient: Determines the expected format of the JSON string.

• dtype: Defines the data type of columns.

• lines: Indicates whether the file should be read as one JSON object per line.

Example

Let's see an example of reading a JSON data from a.json file.

import pandas as pd# Reading a JSON filedf = pd.read_json('json_file.json')print("DataFrame from JSON File:")print(df)

When we run above program, it produces following result −

DataFrame from JSON File:

Pandas Reading JSON With Different Orient Options

Theorient parameter of thepandas.read_json() method controls the expected JSON string format, which effects how Pandas reads the data. Below are the supported formats −

• split: Dict with keys index, columns, and data.

• records: Reads the JSON data into list of dictionaries.

• index: Dict of dicts with keys as row indices.

• columns: Dict of dicts with keys as columns labels.

• table: Adhering to the JSON Table Schema

Example

The following example demonstrates specifying the 'index' orient option for reading the JSON data into a Pandas DataFrame object using thepandas.read_json() method.

import pandas as pdfrom io import StringIO# Input JSON datajson_data = '{"col1": {"row1": "a", "row2": "b"}, "col2": {"row1": "c", "row2": "d"}}'# JSON with 'index' orientationdf = pd.read_json(StringIO(json_data), orient='index')print("Index Orient:")print(df)

Following is an output of the above code −

Index Orient:

Pandas Reading Line-Delimited JSON

Line-delimited JSON contains multiple JSON objects separated by newline. In other words, each line of the line-delimited JSON file contains a piece of JSON Data. It is commonly used in streaming APIs and log data. Pandas supports this format with thelines=True argument of thepandas.read_json() method.

Example

This example demonstrates reading line-delimited JSON file using thelines=True parameter of thepandas.read_json() method.

import pandas as pdfrom io import StringIO# Input JSON datajson_data = """    {"Col1": "a", "Col2": "b"}    {"Col1": "c", "Col2": "d"}"""# Reading Line-Delimited JSONdf = pd.read_json(StringIO(json_data), lines=True)print("DataFrame from Line-Delimited JSON:")print(df)

Following is an output of the above code −

DataFrame from Line-Delimited JSON:

Handling Data Types

While importing JSON, Pandas attempts to infer data types automatically. However, some fields, like dates, may require explicit handling to ensure correct parsing. Thedtype parameters provide fine control over specifying the data types for each field.

Example

The following example demonstrates reading the JSON data with specifying the data type using thedtype parameter of theread_json() method.

import pandas as pdfrom io import StringIO# Reading JSON with specified dtypesjson_data = '[{"A": 1.5, "B": "text", "C": true, "D": "2025-01-01"}]'# Reading JSON with specified dtypesdf = pd.read_json(StringIO(json_data), dtype={"A": "float32", "B": "string"})# Display the output DataFrame from specified data typesprint("Data type of the output DataFrame")print(df.dtypes)

Following is an output of the above code −

Data type of the output DataFrameA           float32B    string[python]C              boolD            objectdtype: object

Reading Nested JSON Structures with Pandas

Nested JSON structures occur when JSON objects have arrays or dictionaries as values. Flattening these structures into a tabular format can be possible by using the Pandasjson_normalize() method.

Example

In this example we will read the nested JSON structure into Pandas DataFrame using thejson_normalize() method.

from pandas import json_normalize# Sample Nested JSON Structuresnested_json = [{"id": 1,    "name": "kiran",    "details": {"age": 30, "city": "Hyderabad"}},{"id": 2,    "name": "Priya",    "details": {"age": 25, "city": "Mumbai"}}]# Reading Nested JSON into DataFramedf = json_normalize(nested_json)print("DataFrame from Nested JSON:")print(df)

While executing the above code we get the following output −

DataFrame from Nested JSON: