5 Best Ways to Convert Pandas DataFrame to JSON

by

💡 Problem Formulation: Converting a Pandas DataFrame to a JSON format is a common requirement when dealing with data in Python, especially for web applications, APIs, or data interchange between systems. For instance, you might have a DataFrame containing user data that you want to serialize into a JSON format to send as a HTTP response or to save for later use. The desired output is a JSON string or file that represents the original DataFrame’s data.

Method 1: Using to_json()

The to_json() function in Pandas is a straightforward method to convert a DataFrame to a JSON string. It offers parameters to customize the format such as ‘orient’, ‘date_format’, and ‘double_precision’.

Here’s an example:

import pandas as pd

# Create a simple DataFrame
df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie'],
    'age': [25, 30, 35],
    'city': ['NY', 'LA', 'Chicago']
})

# Convert the DataFrame to JSON
json_data = df.to_json()

print(json_data)

The output of this code snippet:

{"name":{"0":"Alice","1":"Bob","2":"Charlie"},"age":{"0":25,"1":30,"2":35},"city":{"0":"NY","1":"LA","2":"Chicago"}}

This code snippet creates a simple DataFrame with some user data and converts it into a JSON string using the to_json() method. The default orientation is ‘columns’ which means the columns are preserved in JSON object keys.

Method 2: Orienting as Records

By setting the ‘orient’ parameter as ‘records’, the to_json() function outputs a JSON array of objects, with each object representing a row from the DataFrame, including its index as a part of the object.

Here’s an example:

json_data = df.to_json(orient='records')
print(json_data)

The output of this code snippet:

[{"name":"Alice","age":25,"city":"NY"},{"name":"Bob","age":30,"city":"LA"},{"name":"Charlie","age":35,"city":"Chicago"}]

This modifies the default behavior of to_json() to output a list of dictionaries, where each dictionary corresponds to a DataFrame row, making it easier to parse on the client-side when used in web applications.

Method 3: Split Format

In the split format, the JSON object contains separate keys for indexes, columns, and data. This format can be beneficial when you have large datasets with redundant values, potentially reducing the size of the resulting JSON string.

Here’s an example:

json_data = df.to_json(orient='split')
print(json_data)

The output of this code snippet:

{"columns":["name","age","city"],"index":[0,1,2],"data":[["Alice",25,"NY"],["Bob",30,"LA"],["Charlie",35,"Chicago"]]}

By using the ‘split’ orientation format, the DataFrame is split into separate sections for columns and data, aiding in preserving the DataFrame structure while also ensuring that the same repeated column names are not unnecessarily duplicated in the JSON array.

Method 4: Exporting to a JSON file

The to_json() function can also write the JSON data directly to a file. This is useful for either saving the transformed DataFrame on disk or preparing the data for distribution.

Here’s an example:

df.to_json('data.json', orient='records')

The output is a file named ‘data.json’ with the following content:

[
    {"name":"Alice","age":25,"city":"NY"},
    {"name":"Bob","age":30,"city":"LA"},
    {"name":"Charlie","age":35,"city":"Chicago"}
]

This example does not provide a visual output in the console but instead writes the JSON in ‘records’ format directly into a file called ‘data.json’ that can be shared or stored for later use.

Bonus One-Liner Method 5: DataFrame as a JSON object directly in Python

With a Python one-liner, we can use a combination of Pandas and built-in Python features to immediately transform and retrieve a DataFrame into a JSON object, which can then be used within a program.

Here’s an example:

import json

json_object = json.loads(df.to_json(orient='records'))
print(json_object)

The output of this code snippet:

[
    {'name': 'Alice', 'age': 25, 'city': 'NY'},
    {'name': 'Bob', 'age': 30, 'city': 'LA'},
    {'name': 'Charlie', 'age': 35, 'city': 'Chicago'}
]

This one-liner reads the JSON string produced by the to_json() method into an actual Python list of dictionaries, using the built-in json.loads() function, without requiring an intermediary step of saving into a file or variable.

Summary/Discussion

In this article, we’ve explored different ways to convert a Pandas DataFrame to JSON:

  • Method 1: to_json() function. Straightforward and versatile. May require additional manipulation for custom formats.
  • Method 2: Orient as records. Produces a list of rows as JSON objects. Very useful for APIs. Loses the DataFrame index unless it’s included as a column.
  • Method 3: Split format. Organizes JSON with keys for index, columns, and data. Efficient for large DataFrames. Requires client-side reconstruction for use.
  • Method 4: Writing to a file. Convenient for saving and sharing data. Removes the need for manual file operations. Not for direct data manipulation.
  • Bonus Method 5: One-liner JSON object. Quick in-memory conversion for immediate use. Does not persist the data but great for script-based processing.