Pandas DataFrame to_json() Method

5/5 - (1 vote)

Preparation

Before any data manipulation can occur, two (2) new libraries will require installation.

  • The Pandas library enables access to/from a DataFrame.
  • The NumPy library supports multi-dimensional arrays and matrices in addition to a collection of mathematical functions.

To install these libraries, navigate to an IDE terminal. At the command prompt ($), execute the code below. For the terminal used in this example, the command prompt is a dollar sign ($). Your terminal prompt may be different.

$ pip install pandas

Hit the <Enter> key on the keyboard to start the installation process.

$ pip install numpy

Hit the <Enter> key on the keyboard to start the installation process.

If the installations were successful, a message displays in the terminal indicating the same.


Feel free to view the PyCharm installation guide for the required libraries.


Add the following code to the top of each code snippet. This snippet will allow the code in this article to run error-free.

import pandas as pd
import numpy as np

DataFrame.to_json()

The to_json() method converts a DataFrame object to a JSON string.

💡 Note: Any NaN/None values will convert to NULL values.
Any DateTime objects will convert to UNIX timestamps.

The syntax for this method is as follows:

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)
ParameterDescription
path_or_bufThis parameter is a string, path, or file object with a write function.
orientThis parameter is the expected JSON format. The options are a:
Series:
– default is 'index'
– values are: ‘split’, ‘records’, ‘index’, ‘table’
DataFrame:
– default is 'columns'
– values are: ‘split’, ‘records’, ‘index’, ‘columns’, ‘values’, ‘table’
JSON:
'dict': dictionary: {column -> {index -> value}}
‘list’: dictionary: {column -> [values]}
‘series’: dictionary: {column -> Series(values)}
‘split’: dictionary: {‘index’ -> [index], ‘columns’, etc.}
‘tight’: dictionary: {‘index’ -> [index], etc.}
‘records’: list: [{column -> value}, … , {column -> value}]
‘index’: dictionary: {index -> {column -> value}}
date_formatThis is the format of the date conversion. The options are:
'epoch' or 'iso'.
double_precisionThe decimal places to use when encoding float values.
force_asciiWhether to force the encoded string to be valid ASII.
date_unitThe unit of time for encoding.
default_handlerThe handler to call if the string can not be converted to JSON.
linesIf orient is ‘records’, then write a line delimited JSON string.
compressionIf 'infer'/‘path_or_buf’, use: ‘.gz’, ‘.bz2’, ‘.zip’, ‘.xz’, or ‘.zst’ ext.
indexIf True, this parameter includes index values in the JSON string.
indentThis parameter determines the length of the indent for a record.
storage_optionsThis parameter contains extra options (dictionary format), such as host, port, username, etc.

This example reads in the countries.csv file to a DataFrame. This DataFrame then converts to JSON. Click here to save this CSV file and move it to the current working directory.

df = pd.read_csv('countries.csv').head()
result = df.to_json(indent=4, orient='records', lines=True)
print(result)
  • Line [1] reads in the first five (5) rows (head) of the countries.csv file. The output saves to a DataFrame (df).
  • Line [2] does the following:
    • converts the DataFrame to a JSON format
    • formats the output by indenting each record four (4) spaces from the left
    • sets the orient parameter to records and lines to True (see above definition).
    • saves the output to result.
  • Line [3] outputs the result to the terminal.

Output – result

{
"Country":"Germany",
"Capital":"Berlin",
"Population":83783942,
"Area":357021
}
{
"Country":"France",
"Capital":"Paris",
"Population":67081000,
"Area":551695
}
{
"Country":"Spain",
"Capital":"Madrid",
"Population":47431256,
"Area":498511
}
{
"Country":"Italy",
"Capital":"Rome",
"Population":60317116,
"Area":301338
}
{
"Country":"Poland",
"Capital":"Warsaw",
"Population":38383000,
"Area":312685
}

More Pandas DataFrame Methods

Feel free to learn more about the previous and next pandas DataFrame methods (alphabetically) here:

Also, check out the full cheat sheet overview of all Pandas DataFrame methods.