How to convert pandas DataFrame into JSON in Python?

We are given a pandas DataFrame, and our task is to convert it into JSON format using different orientations and custom options. JSON (JavaScript Object Notation) is a lightweight, human-readable format used for data exchange. With Pandas, this can be done easily using the to_json() method. For example:

import pandas as pd

df = pd.DataFrame({
    'Name': ['John', 'Anna', 'Peter'],
    'Age': [28, 24, 35],
    'City': ['New York', 'Paris', 'Berlin']
})

a = df.to_json()
print(a)

Output:

{"Name":{"0":"John","1":"Anna","2":"Peter"},"Age":{"0":28,"1":24,"2":35},"City":{"0":"New York","1":"Paris","2":"Berlin"}}

This example shows how easy it is to convert a DataFrame into JSON format using the to_json() method where both columns and rows are represented as key-value pairs. Now, let’s dive deeper into the process and explore different options available for customization.

Using the to_json() Method

The to_json() function converts a DataFrame into a JSON string or file. Key parameters include:

path_or_buf: File path or buffer. If not specified, JSON string is returned.

orient: Defines JSON structure (e.g., 'records', 'index', 'columns', etc.).

date_format: 'iso' or 'epoch' format for datetime values.

double_precision: Controls decimal places for floats.

force_ascii: Escapes non-ASCII characters if True.

date_unit: Sets time unit for datetime ('ms', 's', 'us', 'ns').

indent: Pretty prints JSON with indentation.

Customizing The JSON Method with orient

The orient parameter allows you to control how the DataFrame is structured in the resulting JSON. Pandas to_json() provides multiple format options through the orient parameter. Here are some common orientations:

1. 'records' (Row-wise Objects)

Converts each row into a dictionary, making it ideal for JSON structures that prioritize row-based data. Each row is a JSON object in an array.

df.to_json(orient='records')

Output:

[{"Name":"John","Age":28,"City":"New York"}, {"Name":"Anna","Age":24,"City":"Paris"}, {"Name":"Peter","Age":35,"City":"Berlin"}]

2. 'index' – Index as Keys

Uses the DataFrame index as JSON keys, with each index mapping to a dictionary representing a row. This structure is useful for indexed data.

df.to_json(orient='index')

Output:

{"0":{"Name":"John","Age":28,"City":"New York"}, "1":{"Name":"Anna","Age":24,"City":"Paris"}, "2":{"Name":"Peter","Age":35,"City":"Berlin"}}

3. 'columns' – Default Format

Converts each column into a key with an array of values, creating a dictionary that maps column names to lists of their values.

df.to_json(orient='columns')

Output:

{"Name":{"0":"John","1":"Anna","2":"Peter"}, "Age":{"0":28,"1":24,"2":35}, "City":{"0":"New York","1":"Paris","2":"Berlin"}}

4. 'split' – Structured Parts

Organizes the output into three distinct parts—index, columns, and data—which helps reconstruct the DataFrame more easily.

df.to_json(orient='split')

Output:

{"columns":["Name","Age","City"], "index":[0,1,2], "data":[["John",28,"New York"],["Anna",24,"Paris"],["Peter",35,"Berlin"]]}

5. 'values' – Values Only

df.to_json(orient='values')

Output:

[["John",28,"New York"],["Anna",24,"Paris"],["Peter",35,"Berlin"]]

6. 'table' – Table Schema

Follows a table schema with metadata, which includes schema details and the data. This format is suitable for preserving DataFrame structure.

df.to_json(orient='table')

Output:

{
"schema": {
"fields": [...],
"primaryKey": ["index"],
"pandas_version": "1.4.0"
},
"data": [
{"index": 0, "Name": "John", "Age": 28, "City": "New York"},
...
]
}

Customizing Output with Parameters

Pandas gives several powerful options for customizing the JSON output using to_json() parameters. These options let you choose how precise, readable, compact, or compatible your JSON output should be:

• orient: Controls the JSON structure. Options include 'records', 'index', 'columns', 'split', 'values', 'table'.
• date_format: Convert datetime values to either ISO ('iso') or Unix timestamp ('epoch').
• double_precision: Set how many decimal points to include for float values.
• force_ascii: If True, non-ASCII characters are escaped (e.g., for ASCII-only systems).
• date_unit: Sets the unit for datetime output—milliseconds ('ms'), seconds ('s'), microseconds ('us'), or nanoseconds ('ns').
• indent: Pretty-prints the output with indentation, useful for logging or readability.
• path_or_buf: If a file path is passed here, the output is saved directly to that file instead of returned as a string.

These parameters work together to make the output more suitable for your specific use case—whether for APIs, config files, storage, or human review.

Example: All orient types on a simple DataFrame

import numpy as np
import pandas as pd

data = np.array([["1", "2"], ["3", "4"]])
df = pd.DataFrame(data, columns=['col1', 'col2'])

print(df.to_json())                      # Default (columns)
print(df.to_json(orient='split'))
print(df.to_json(orient='records'))
print(df.to_json(orient='index'))
print(df.to_json(orient='columns'))
print(df.to_json(orient='values'))
print(df.to_json(orient='table'))

{"col1":{"0":"1","1":"3"},"col2":{"0":"2","1":"4"}}
{"columns":["col1","col2"],"index":[0,1],"data":[["1","2"],["3","4"]]}
[{"col1":"1","col2":"2"},{"col1":"3","col2":"4"}]
{"0":{"col1":"1","col2":"2"...

Example: Using Other Parameters

import pandas as pd

data = {
    'Name': ['John', 'Jane', 'Bob'],
    'Age': [30, 25, 40],
    'Salary': [50000.0, 60000.0, 70000.0],
    'Join_date': ['2022-01-01', '2021-06-15', '2020-11-30']
}
df = pd.DataFrame(data)

print(df.to_json())                                      # Basic
print(df.to_json(orient='records'))                      # Row-wise
print(df.to_json(date_format='iso'))                     # ISO date format
print(df.to_json(double_precision=2))                    # Limit float decimals
print(df.to_json(force_ascii=False))                     # Allow Unicode
print(df.to_json(date_unit='ms'))                        # Milliseconds unit
print(df.to_json(indent=4))                              # Pretty print
df.to_json(path_or_buf='output.json')                    # Save to file

{"Name":{"0":"John","1":"Jane","2":"Bob"},"Age":{"0":30,"1":25,"2":40},"Salary":{"0":50000.0,"1":60000.0,"2":70000.0},"Join_date":{"0":"2022-01-01","1":"2021-06-15","2":"2020-11-30"}}
[{"Name":"John",...

Key Takeaways:

• The to_json() method in Pandas provides a flexible way to convert a DataFrame into different JSON formats.
• The orient parameter allows you to customize how rows and columns are represented in the output.
• Special data types like missing values (NaN, None) are handled gracefully by converting them to null.
• Depending on your use case (e.g., web APIs or data storage), you can choose from various orientations like 'records', 'index', 'columns', etc.