Export Pandas Dataframe to JSON

In this article, we will discuss how to export a Pandas Dataframe to JSON file in Python.

Table of Contents

• Convert Dataframe to JSON in pretty format
• Convert Dataframe to JSON file with different orientations
  • Convert Dataframe to JSON with ‘split’ orientation
  • Convert Dataframe to JSON with ‘records’ orientation
  • Convert Dataframe to JSON with ‘index’ orientation
  • Convert Dataframe to JSON with ‘values’ orientation
  • Convert Dataframe to JSON with ‘table’ orientation
  • Convert Dataframe to JSON with ‘columns’ orientation

JSON stands for Java Script Object Notation. A JSON file stores the data in key value pair format stored inside the list/dictionary data structure.

A DataFrame is a data structure that stores the data in rows and columns. We can create a DataFrame using pandas.DataFrame() method. Let’s create a dataframe with 4 rows and 4 columns

import pandas as pd

# Create a Dataframe from a dictionary
df=pd.DataFrame({'id'       :[58,59,60,61],
                 'name'     :['sravan','jyothika','preethi','srinadh'],
                 'age'      :[22,21,22,23],
                 'subjects' :['java','php','sql','python']})

# Display the Dataframe
print(df)

Output:

   id      name  age  subjects
0  58    sravan   22      java
1  59  jyothika   21       php
2  60   preethi   22       sql
3  61   srinadh   23    python

We can export pandas dataframe to json using to_json() method. This will convert the given dataframe into json with different orientations based on the parameters given.

Syntax of dataframe.to_json():

df.to_json('file name/path, indent, orient, index)

where, df is the input dataframe. Parameters are as follows,

• file_name is the path or name of the json file where dataframe will be stored. We can also specify the path of our current location, if the json file should be placed in that folder/location.
• indent is used to specify white spaces between each record
• orient is to specify the json format
• index is used to get or remove the index in json file
  • it will get index if it set to true, otherwise it will not get index if it is false.
    

Let’s see the examples by setting different parameters.

Convert Dataframe to JSON in pretty format

Here we will use indent parameter to specify the white space. We have to pass an integer value to this parameter, which refers
number of spaces between each record.

Example: In this example, we will specify indent as 2 to export dataframe to json file,

# Export dataframe to json
# with indent = 2
df.to_json('data.json',indent=2)

It will create a file data.json. This file contains the Dataframe contents in the json format. Where column names are stored as the keys and values contains the column content contents as a nested dictionary, where each value is associated with the index key.

Contents of the data.json file will be like this,

{
  "id":{
    "0":58,
    "1":59,
    "2":60,
    "3":61
  },
  "name":{
    "0":"sravan",
    "1":"jyothika",
    "2":"preethi",
    "3":"srinadh"
  },
  "age":{
    "0":22,
    "1":21,
    "2":22,
    "3":23
  },
  "subjects":{
    "0":"java",
    "1":"php",
    "2":"sql",
    "3":"python"
  }
}

Convert Dataframe to JSON file with different orientations

A JSON file can store the dataframe contents in different format. We can use the orient parameter to save Dataframe in different json formats. Let’s see all those formats using different values of this orient parameter.

Convert Dataframe to JSON with ‘split’ orientation

Pass the ‘split’ value in the ‘orient’ parameter of the to_json() function. It will store the dataframe in json file in a dictionary format.

Example: In this example, we will specify the split format in the orient parameter of to_json() function

# export dataframe to json
# with split orient
df.to_json('data.json',orient='split')

It will create a file data.json. This file contains the Dataframe contents in a json format like this,

{"columns":["id","name","age","subjects"],"index":[0,1,2,3],"data":[[58,"sravan",22,"java"],[59,"jyothika",21,"php"],[60,"preethi",22,"sql"],[61,"srinadh",23,"python"]]}

split orient with index parameter

This will results the json string as dictionary format wiht out index values.

{‘columns’ -> [columns], ‘data’ -> [values]}

Syntax:

df.to_json('file_name.json',orient='split',index=False)

We have to set the index parameter to False.

Example: In this example, we will specify the split format

# export dataframe to json
# with split orient and without index
df.to_json( 'json_data.json',
            orient='split',
            index=False)

The output results into split string json structure.

{"columns":["id","name","age","subjects"],"data":[[58,"sravan",22,"java"],[59,"jyothika",21,"php"],[60,"preethi",22,"sql"],[61,"srinadh",23,"python"]]}

Convert Dataframe to JSON with ‘records’ orientation

This will results the json string as list format.

 [{column -> value},.......... , {column -> value}]

Syntax:

df.to_json('file_name.json',orient='records')

Example: In this example, we will specify the records format

# Export dataframe to json
# with records orient
df.to_json( 'json_data.json',
            orient='records')

The output results into a json file, in which json data is stored as dictionaries in the list.

[{"id":58,"name":"sravan","age":22,"subjects":"java"},{"id":59,"name":"jyothika","age":21,"subjects":"php"},{"id":60,"name":"preethi","age":22,"subjects":"sql"},{"id":61,"name":"srinadh","age":23,"subjects":"python"}]

Convert Dataframe to JSON with ‘index’ orientation

This will results the json string as dictionary format.

{index -> {column -> value}}

Syntax:

df.to_json('file_name.json',orient='index')

Example: In this example, we will specify the index format

# Export dataframe to json
# with index orient
df.to_json( 'json_data.json',
            orient='index')

The output results into a json file, in which json data is stored as dictionaries of strings.

{"0":{"id":58,"name":"sravan","age":22,"subjects":"java"},"1":{"id":59,"name":"jyothika","age":21,"subjects":"php"},"2":{"id":60,"name":"preethi","age":22,"subjects":"sql"},"3":{"id":61,"name":"srinadh","age":23,"subjects":"python"}}

Convert Dataframe to JSON with ‘values’ orientation

This will results the json string as array format.

Syntax:

df.to_json('file_name.json',orient='values')

Example: In this example, we will specify the values format

# Export dataframe to json
# with values orient
df.to_json( 'json_data.json',
            orient='values')

The output results into a json file, in which json data is stored as nested array.

[[58,"sravan",22,"java"],[59,"jyothika",21,"php"],[60,"preethi",22,"sql"],[61,"srinadh",23,"python"]]

Convert Dataframe to JSON with ‘table’ orientation

This will results the json string as dictionary format along with schema.

{‘schema’: {schema}, ‘data’: {data}}

Syntax:

df.to_json('file_name.json',orient='table')

Example: In this example, we will specify the table format

# Export dataframe to json
# with table orient
df.to_json( 'json_data.json',
            orient='table')

The output results into a json file, in which json data is stored in following format,

{"schema":{"fields":[{"name":"index","type":"integer"},{"name":"id","type":"integer"},{"name":"name","type":"string"},{"name":"age","type":"integer"},{"name":"subjects","type":"string"}],"primaryKey":["index"],"pandas_version":"0.20.0"},"data":[{"index":0,"id":58,"name":"sravan","age":22,"subjects":"java"},{"index":1,"id":59,"name":"jyothika","age":21,"subjects":"php"},{"index":2,"id":60,"name":"preethi","age":22,"subjects":"sql"},{"index":3,"id":61,"name":"srinadh","age":23,"subjects":"python"}]}

table orient with index parameter

This will results the json string as dictionary format along with schema with out index if the index set to False.

{‘schema’: {schema}, ‘data’: {data}}

Syntax:

df.to_json('file_name.json',orient='table',index=False)

Example: In this example, we will specify the table format

# Export dataframe to json
# with table orient
df.to_json( 'json_data.json',
            orient='table',
            index=False)

The output results into a json file, in which json data is stored in following format,

{"schema":{"fields":[{"name":"id","type":"integer"},{"name":"name","type":"string"},{"name":"age","type":"integer"},{"name":"subjects","type":"string"}],"pandas_version":"0.20.0"},"data":[{"id":58,"name":"sravan","age":22,"subjects":"java"},{"id":59,"name":"jyothika","age":21,"subjects":"php"},{"id":60,"name":"preethi","age":22,"subjects":"sql"},{"id":61,"name":"srinadh","age":23,"subjects":"python"}]}

Convert Dataframe to JSON with ‘columns’ orientation

This will results the json string as dictionary format with column name as key

{column -> {index -> value}}

Syntax:

df.to_json('file_name.json',orient='columns')

Example: In this example, we will specify the columns orientation

# Export dataframe to json
# with columns orient
df.to_json( 'json_data.json',
            orient='columns')

This will results into a json file, which contains the dataframe in following format,

{"id":{"0":58,"1":59,"2":60,"3":61},"name":{"0":"sravan","1":"jyothika","2":"preethi","3":"srinadh"},"age":{"0":22,"1":21,"2":22,"3":23},"subjects":{"0":"java","1":"php","2":"sql","3":"python"}}

Summary

In this article we discussed how to export pandas dataframe to json with different parameters using to_json() method.