Python Pandas to_json() Method

The to_json() method in Python's Pandas library is used to convert a Pandas DataFrame or Series into a JSON string or save it to a JSON file. This method provides a number of options to control the format of the resulting JSON string, such as the orientation of JSON data and the formatting of date and time. This method can be useful for working with APIs, exporting data, or storing data in a portable and structured format. It can also handle various cases like NaN values, datetime objects, and custom unsupported data types.

JSON (JavaScript Object Notation) is a popular lightweight data-interchange format for storing structured information. It uses plain-text formatting, where each element is represented in a hierarchical structure, making it easy to read and process. JSON files have the .json extension and are commonly used in web applications, APIs, and data pipelines.

Syntax

Following is the syntax of the Python to_json() method −

 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=None, indent=None, storage_options=None, mode='w') 

Note: When using the to_json() method on a Pandas Series object, you should call it as Series.to_json().

Parameters

The to_json() method accepts the following parameters −

• path_or_buf: A string that specifies the location for the output JSON file. It can be a file path or a buffer. If set to None, the result is returned as JSON string.

• orient: Defines the format of the JSON string. Possible values are: 'split', 'records', 'index', 'columns', 'values', 'table'. The default value is 'columns' for a DataFrame, and 'index' for a Series.

• date_format: Specifies the format for dates. It can be 'epoch' (default) or 'iso' for ISO8601 date format, this option is default for table orientation.

• double_precision: Sets the number of decimal places for floating-point values. The default is 10.

• force_ascii: Forces all strings in the output to be ASCII-encoded. Default is True.

• date_unit: Specifies the time unit for encoding dates, such as 'ms' (milliseconds), 's' (seconds), etc. Default is 'ms'.

• default_handler: A callable function that handles objects which cannot be automatically converted to JSON format. It returns a serializable object.

• lines: If True and orient is 'records', and the output is in a line-delimited JSON format. Default is False.

• compression: Defines compression for the output file. Accepted values include 'infer', 'gzip', 'bz2', 'zip', and others.

• index: Specifies whether the index should be included in the output JSON. Default is None.

• indent: Sets the number of spaces used for indentation in the JSON output. Default is None (no indentation).

• storage_options: Allows passing additional options for specific storage backends like HTTP or cloud storage (e.g., 's3://', and 'gcs://').

• mode: Defines the IO mode. Accepted values are 'w' (write) and 'a' (append). Default is 'w'.

Return Value

The to_json() method returns a JSON string if the path_or_buf parameter is set to None. If a file or buffer is provided, it writes the JSON string to the given location and returns None.

Example: Converting DataFrame to JSON String

Here is a basic example of using the to_json() method to convert a DataFrame into a JSON string with the default 'columns' orientation.

 import pandas as pd # Create a DataFrame df = pd.DataFrame( [["a", "b"], ["c", "d"]], index=["row_1", "row_2"], columns=["col_1", "col_2"], ) # Convert to JSON result = df.to_json() # Print the resulting JSON print(result) 

When we run the above program, it produces the following result:

 {"col_1":{"row_1":"a","row_2":"c"},"col_2":{"row_1":"b","row_2":"d"}} 

Example: DataFrame to JSON String with 'records' Orientation

Now let's use the 'records' orientation option of the DataFrame.to_json() method, which will result in a list of dictionaries where each dictionary represents a row of the DataFrame.

 import pandas as pd # Create a DataFrame df = pd.DataFrame( [["a", "b"], ["c", "d"]], index=["row_1", "row_2"], columns=["col_1", "col_2"], ) # Convert to JSON with 'records' orientation result = df.to_json(orient="records") # Print the resulting JSON print(result) 

Following is an output of the above code −

 [{"col_1":"a","col_2":"b"},{"col_1":"c","col_2":"d"}] 

Example: Exporting DataFrame to JSON with Table Schema

In this example, we will use the 'table' orientation, which includes additional schema information along with the data.

 import pandas as pd # Create a DataFrame df = pd.DataFrame( [["a", "b"], ["c", "d"]], index=["row_1", "row_2"], columns=["col_1", "col_2"], ) # Convert to JSON with 'table' orientation result = df.to_json(orient="table") # Print the resulting JSON print(result) 

Output of the above code is as follows:

 {"schema":{"fields":[{"name":"index","type":"string"},{"name":"col_1","type":"string"},{"name":"col_2","type":"string"}],"primaryKey":["index"],"pandas_version":"1.4.0"},"data":[{"index":"row_1","col_1":"a","col_2":"b"},{"index":"row_2","col_1":"c","col_2":"d"}]} 

Example: Saving DataFrame to JSON File

In this example, we will save the Pandas DataFrame into a JSON file using the DataFrame.to_json() method, by specifying the string representing the file path.

 import pandas as pd # Create a DataFrame df = pd.DataFrame( [["a", "b"], ["c", "d"]], index=["row_1", "row_2"], columns=["col_1", "col_2"], ) # Convert to JSON file df.to_json('dataframe_to_json_data.json') print("DataFrame has been saved to 'dataframe_to_json_data.json'") 

Output of the above code is as follows −

 DataFrame has been saved to 'dataframe_to_json_data.json' 

Example: Customizing JSON Indentation

This example demonstrates how to use the to_json() method to convert a DataFrame into a JSON string with custom indentation.

 import pandas as pd # Create a DataFrame df = pd.DataFrame( [["a", "b"], ["c", "d"]], index=["row_1", "row_2"], columns=["col_1", "col_2"], ) # Convert to JSON with Custom Indentation result = df.to_json(indent=3) # Print the resulting JSON string print(result) 

When we run the above program, it produces the following result:

 { "col_1":{ "row_1":"a", "row_2":"c" }, "col_2":{ "row_1":"b", "row_2":"d" } }