Convert DataFrame to JSON in Pandas

Convert DataFrame to JSON

To convert DataFrame to a JSON string in Pandas, call to_json() method on this DataFrame object.

Any NaN values in this DataFrame will be converted to null in the JSON string. Also the datatime objects will be converted to UNIX timestamps in the resulting JSON string.

Syntax

The syntax of pandas DataFrame.to_json() method is

DataFrame.to_json(path_or_buf=None, orient=None, date_format=None, double_precision=10, force_ascii=True, date_unit='ms', default_handler=None, lines=False, compression='infer', index=True, indent=None, storage_options=None)

where

Parameter	Value	Description
path_or_buf	str or file handle, optional	File path or object. If not specified, the result is returned as a string.
orient	str	Indication of expected JSON string format. Series: default is ‘index’ allowed values are: {‘split’, ‘records’, ‘index’, ‘table’}. DataFrame: default is ‘columns’ allowed values are: {‘split’, ‘records’, ‘index’, ‘columns’, ‘values’, ‘table’}. The format of the JSON string: ‘split’ : dict like {‘index’ -> [index], ‘columns’ -> [columns], ‘data’ -> [values]} ‘records’ : list like [{column -> value}, … , {column -> value}] ‘index’ : dict like {index -> {column -> value}} ‘columns’ : dict like {column -> {index -> value}} ‘values’ : just the values array ‘table’ : dict like {‘schema’: {schema}, ‘data’: {data}} Describing the data, where data component is like orient=’records’.
date_format	{None, ‘epoch’, ‘iso’}	Type of date conversion. ‘epoch’ = epoch milliseconds, ‘iso’ = ISO8601. The default depends on the orient. For orient=’table’, the default is ‘iso’. For all other orients, the default is ‘epoch’.
double_precision	int, default 10	The number of decimal places to use when encoding floating point values.
force_ascii	bool, default True	Force encoded string to be ASCII.
date_unit	str, default ‘ms’ (milliseconds)	The time unit to encode to, governs timestamp and ISO8601 precision. One of ‘s’, ‘ms’, ‘us’, ‘ns’ for second, millisecond, microsecond, and nanosecond respectively.
default_handler	callable, default None	Handler to call if object cannot otherwise be converted to a suitable format for JSON. Should receive a single argument which is the object to convert and return a serialisable object.
lines	bool, default False	If ‘orient’ is ‘records’ write out line-delimited json format. Will throw ValueError if incorrect ‘orient’ since others are not list-like.
compression	{‘infer’, ‘gzip’, ‘bz2’, ‘zip’, ‘xz’, None}	A string representing the compression to use in the output file, only used when the first argument is a filename. By default, the compression is inferred from the filename.
index	bool, default True	Whether to include the index values in the JSON string. Not including the index (index=False) is only supported when orient is ‘split’ or ‘table’.
indent	int, optional	Length of whitespace used to indent each record. New in version 1.0.0.
storage_options	dict, optional	Extra options that make sense for a particular storage connection, e.g. host, port, username, password, etc. For HTTP(S) URLs the key-value pairs are forwarded to urllib as header options. For other URLs (e.g. starting with “s3://”, and “gcs://”) the key-value pairs are forwarded to fsspec. Please see fsspec and urllib for more details. New in version 1.2.0.

Return Value

None or str.

Examples

Convert DataFrame to JSON String

In the following program, we demonstrate a simple example of converting a DataFrame to a JSON String. We take a DataFrame, and call to_json() method on this DataFrame.

Example.py

import pandas as pd

df = pd.DataFrame(
    {'name': ["apple", "banana", "cherry"], 'quant': [40, 50, 60]})

result = df.to_json()
print(result)

Output

{"name":{"0":"apple","1":"banana","2":"cherry"},"quant":{"0":40,"1":50,"2":60}}

Orient = ‘records’

We take a DataFrame, and convert this DataFrame to JSON string with orient = ‘records’.

Example.py

import pandas as pd

df = pd.DataFrame(
    {'name': ["apple", "banana", "cherry"], 'quant': [40, 50, 60]})

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

Output

[{"name":"apple","quant":40},{"name":"banana","quant":50},{"name":"cherry","quant":60}]

We may specify lines=’True’ when orient=’records’.

Example.py

import pandas as pd

df = pd.DataFrame(
    {'name': ["apple", "banana", "cherry"], 'quant': [40, 50, 60]})

result = df.to_json(orient='records', lines=True)
print(result)

Output

{"name":"apple","quant":40}
{"name":"banana","quant":50}
{"name":"cherry","quant":60}

JSON Indentation

We may also specify the indentation for the resulting JSON String.

Example.py

import pandas as pd

df = pd.DataFrame(
    {'name': ["apple", "banana", "cherry"], 'quant': [40, 50, 60]})

result = df.to_json(orient='records', indent=4)
print(result)

Output

[
    {
        "name":"apple",
        "quant":40
    },
    {
        "name":"banana",
        "quant":50
    },
    {
        "name":"cherry",
        "quant":60
    }
]

Conclusion

In this Pandas Tutorial, we learned how to convert a DataFrame object to JSON String using pandas DataFrame.to_json() method.