How to Export Pandas DataFrame to JSON File

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

To provide you some context, here is the generic structure that you may use in Python to export pandas DataFrame to JSON:

 

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

 

We’ll now see the steps to apply this structure 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 Computer$700
Tablet$250
iPhone$800
Laptop$1200

 

Your goal is to export that 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 that will capture the data about our 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 Python code, you’ll get:

 

DF Python

 

Step 3: Export Pandas DataFrame to JSON File

At the beginning of this tutorial, we saw the generic structure to export pandas DataFrame to JSON:

 

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

 

In my case, the path where I’ll be storing the exported JSON file is:

C:\Users\Doron E\Desktop\Export_DataFrame

You’ll need to adjust the path in the Python code below to match with the location where you’d like to export 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'])

Export = df.to_json (r'C:\Users\Doron E\Desktop\Export_DataFrame')

 

Run the code (adjusted to your path), and a file will get generated in 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 like 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',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'])


Export = df.to_json (r'C:\Users\Doron E\Desktop\Export_DataFrame', 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}}

 

Steps to Export Pandas DataFrame to JSON File

 

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