5 Best Ways to Convert Pandas DataFrame into JSON in Python

by

💡 Problem Formulation: Converting a Pandas DataFrame into JSON format is common in data processing and API development, where you might need to pass data onwards in a web-friendly format. Imagine you have a DataFrame with user data you need to serialize into JSON to send it to a web service. You want this conversion to be efficient and customizable based on different requirements, such as orienting the JSON output or handling date formatting.

Method 1: Using to_json() function

The Pandas to_json() function is the most straightforward way to convert a DataFrame into a JSON object or file. It offers various parameters to control the serialization, like ‘orient’, which can be set to ‘columns’, ‘records’, ‘index’, ‘values’, and ‘split’ to structure the JSON in different ways.

Here’s an example:

import pandas as pd

df = pd.DataFrame({'name': ['Alice', 'Bob'], 'age': [30, 28]})
json_data = df.to_json(orient='records', lines=True)
print(json_data)

The output of this code snippet:

{"name":"Alice","age":30}
{"name":"Bob","age":28}

This code snippet creates a simple DataFrame with user names and ages and converts it to JSON with each record on a new line, which is convenient for newline-delimited JSON streams that are often used in streaming APIs.

Method 2: Formatting Date Columns

When your DataFrame contains date or datetime objects, it can be crucial to format these correctly in your JSON output. The to_json() function accepts a ‘date_format’ parameter, which can be either ‘epoch’ or ‘iso’. Using ‘iso’ will output dates in ISO8601 format.

Here’s an example:

import pandas as pd

df = pd.DataFrame({
    'event': ['Concert', 'Football Match'], 
    'date': [pd.Timestamp('2023-01-02'), pd.Timestamp('2023-02-03')]
})
json_data = df.to_json(date_format='iso')
print(json_data)

The output of this code snippet:

{"event":{"0":"Concert","1":"Football Match"},"date":{"0":"2023-01-02T00:00:00.000Z","1":"2023-02-03T00:00:00.000Z"}}

This code snippet illustrates how dates within a DataFrame can be converted to a JSON-friendly format. The ‘iso’ parameter ensures that the dates are in a globally recognized string format.

Method 3: Excluding NULL Values

In situations where the dataset may include missing or NULL values and you prefer to exclude them from the JSON output, the to_json() function offers the ‘default_handler’ parameter, which can be set to ‘ignore’.

Here’s an example:

import pandas as pd
import numpy as np

df = pd.DataFrame({'name': ['Alice', np.nan], 'age': [30, np.nan]})
json_data = df.to_json(default_handler=str)
print(json_data)

The output of this code snippet:

{"name":{"0":"Alice","1":"NaN"},"age":{"0":30.0,"1":"NaN"}}

By setting the ‘default_handler’ parameter to str, this example demonstrates how to handle NULL values during JSON conversion. The NULL values are converted to strings in the resulting JSON.

Method 4: Compression and Encoding

For large datasets, it could be useful to compress the resulting JSON. The to_json() function allows you to directly compress the JSON output by specifying a compression type (‘infer’, ‘gzip’, ‘bz2’, ‘zip’, ‘xz’, ‘zstd’). Also, you can define encoding for the output.

Here’s an example:

import pandas as pd

df = pd.DataFrame({'name': ['Alice', 'Bob'], 'age': [30, 28]})
df.to_json('data.json.gz', orient='records', compression='gzip', encoding='utf-8')

This example doesn’t produce a standard printout but rather a compressed file named ‘data.json.gz’ that contains the JSON data in a file that takes up less space, making it easier to store or transmit.

Bonus One-Liner Method 5: to_json() with URL

Directly sending your JSON data to a specific URL is a powerful one-liner when dealing with web APIs. By using the to_json() function with a URL argument, the DataFrame will be serialized into JSON and POSTed to the supplied endpoint.

Here’s an example:

import pandas as pd

df = pd.DataFrame({'name': ['Alice', 'Bob'], 'age': [30, 28]})
df.to_json('http://example.com/api/submit', orient='records')

This snippet shows how you can serialize and send JSON data directly to an API endpoint in one step. The example assumes the API accepts the data in the ‘records’ orientation.

Summary/Discussion

  • Method 1: Using to_json(). Simple and straightforward. Does not handle large datasets efficiently.
  • Method 2: Formatting Date Columns. Essential for proper date-time serialization. Requires Pandas to recognize date formats.
  • Method 3: Excluding NULL Values. Helps in generating cleaner JSON. May lead to loss of information where NULLs are significant.
  • Method 4: Compression and Encoding. Great for large datasets. Adds complexity in decompression and decoding steps on the receiving end.
  • Bonus Method 5: Direct to URL. Simplifies workflows involving web APIs. Depends on network reliability and API availability.