How to Export Pandas DataFrame to JSON File

In this tutorial, I’ll show you how to export pandas DataFrame to a JSON file using a simple example. I’ll also review the different JSON formats that you may apply.

To provide you some context, here is a template that you may use in Python to export pandas DataFrame to JSON:

df.to_json(r'Path to store the exported JSON file\File Name.json')

Next, you’ll see the steps to apply this template in practice.

Steps to Export Pandas DataFrame to JSON

Step 1: Gather the Data

Let’s suppose that you have the following data about different products and their prices:

ProductPrice
Desktop Computer700
Tablet250
iPhone800
Laptop1200

The goal is to export the above data into JSON.

But before you can export that data, you’ll need to capture it in Python.

One way to accomplish this task is by creating pandas DataFrame.

Step 2: Create the DataFrame

You may then use the following code to capture the data about the products and prices:

from pandas import DataFrame

data = {'Product': ['Desktop Computer','Tablet','iPhone','Laptop'],
        'Price': [700,250,800,1200]
        }

df = DataFrame(data, columns= ['Product', 'Price'])

print (df)

Once you run the code in Python, you’ll get this DataFrame:

DF Python

Step 3: Export Pandas DataFrame to JSON File

Finally, you may use the following template to export pandas DataFrame to JSON:

df.to_json(r'Path to store the exported JSON file\File Name.json')

For example, the path where I’ll be storing the exported JSON file is:

C:\Users\Ron\Desktop\Export_DataFrame.json

You’ll need to adjust the path (in the Python code below) to reflect the location where you’d like to store the JSON file on your computer:

from pandas import DataFrame

data = {'Product': ['Desktop Computer','Tablet','iPhone','Laptop'],
        'Price': [700,250,800,1200]
        }

df = DataFrame(data, columns= ['Product', 'Price'])

df.to_json (r'C:\Users\Ron\Desktop\Export_DataFrame.json')

Run the code (adjusted to your path), and the JSON file will be created at your specified location.

There are few ways in which you could view the JSON content. An easy way is to drag the file created into your web browser. You should then get the following result:

{"Product":{"0":"Desktop Computer","1":"Tablet","2":"iPhone","3":"Laptop"},"Price":{"0":700,"1":250,"2":800,"3":1200}}

You may then use online tools, such as the JSON Formatter, in order to format your JSON file into a more readable form:

How to Export Pandas DataFrame to JSON File

In the next section, I’ll review the different JSON formats that you may apply.

Different JSON Formats:

There are different ways to format the JSON string. You’ll need to set the orient to your desired format. Here are the options:

  • split
  • records
  • index
  • values
  • table
  • columns (the default format)

For example, this is how the general syntax would look like if you set orient=’split’

df.to_json(r'Path to store the JSON file\File Name.json',orient='split')

And here is the full Python code for our example:

from pandas import DataFrame

data = {'Product': ['Desktop Computer','Tablet','iPhone','Laptop'],
        'Price': [700,250,800,1200]
        }

df = DataFrame(data, columns= ['Product', 'Price'])

df.to_json (r'C:\Users\Ron\Desktop\Export_DataFrame.json', orient='split')

Once you run the above code, you’ll get this output:

{"columns":["Product","Price"],"index":[0,1,2,3],"data":[["Desktop Computer",700],["Tablet",250],["iPhone",800],["Laptop",1200]]}

And if you format that output using the JSON Formatter, you’ll see:

Export Pandas DataFrame to JSON File

In the context of our example, here is the output that you’ll get for each of the other formats:

orient=’records’

[{"Product":"Desktop Computer","Price":700},{"Product":"Tablet","Price":250},{"Product":"iPhone","Price":800},{"Product":"Laptop","Price":1200}]

Pandas DataFrame to JSON File

orient=’index’

{"0":{"Product":"Desktop Computer","Price":700},"1":{"Product":"Tablet","Price":250},"2":{"Product":"iPhone","Price":800},"3":{"Product":"Laptop","Price":1200}}

How to Export Pandas DataFrame to JSON

orient=’values’

[["Desktop Computer",700],["Tablet",250],["iPhone",800],["Laptop",1200]]

How to Export Pandas DataFrame to JSON File

orient=’table’

{"schema": {"fields":[{"name":"index","type":"integer"},{"name":"Product","type":"string"},{"name":"Price","type":"integer"}],"primaryKey":["index"],"pandas_version":"0.20.0"}, "data": [{"index":0,"Product":"Desktop Computer","Price":700},{"index":1,"Product":"Tablet","Price":250},{"index":2,"Product":"iPhone","Price":800},{"index":3,"Product":"Laptop","Price":1200}]}

How to Export Pandas DataFrame to JSON File

orient=’columns’ (default)

{"Product":{"0":"Desktop Computer","1":"Tablet","2":"iPhone","3":"Laptop"},"Price":{"0":700,"1":250,"2":800,"3":1200}}

Example of data

You may refer to the pandas documentation to see the different formatting options that you may apply.