Many still struggle while they convert DataFrame Pandas to JSON. So, is there a proper yet simple method to complete this work with little effort? This tutorial will help you with this task.

DataFrames in Pandas are tabular data representations where each row represents a distinct data entry, and the columns indicate numerous data points in separate data input.

JSON is based on the JavaScript object format and is a method of encoding used to represent structured data. This method is frequently utilized, notably for data sharing between online applications and servers.

We will use the DataFrame below as an example:

import pandas as pd
df = pd.DataFrame([['Jay',16,'BBA'],
                   ['Jack',19,'BTech'],
                   ['Mark',18,'BSc']],
                  columns = ['Name','Age','Course'])

print(df)

Output:

  Name  Age Course
0   Jay   16    BBA
1  Jack   19  BTech
2  Mark   18    BSc

Pandas has the dataframe.to_json() method that can convert a DataFrame Pandas to JSON or save it as a separate JSON file. The ultimate JSON form relies on the orient argument’s value, which is set to ‘columns’ by default. Still, it can also be defined as ‘table’, ‘split’, ‘index’, or ‘records’.

Method 1: Using orient = ‘columns’

The orient parameter is the indication of the JSON format to be used. Notice that this parameter is necessary.

Below is an example.

import pandas as pd
df = pd.DataFrame([['Jay',16,'BBA'],
                   ['Jack',19,'BTech'],
                   ['Mark',18,'BSc']],
                  columns = ['Name','Age','Course'])

js = df.to_json(orient = 'columns')
print(js)

Output:

{"Name":{"0":"Jay","1":"Jack","2":"Mark"},
 "Age":{"0":16,"1":19,"2":18},
 "Course":{"0":"BBA","1":"BTech","2":"BSc"}}

Method 2: Using orient = ‘records’

Here is an example of using orient = ‘records’ to convert DataFrame Pandas to JSON format.

import pandas as pd
df = pd.DataFrame([['Jay',16,'BBA'],
                   ['Jack',19,'BTech'],
                   ['Mark',18,'BSc']],
                  columns = ['Name','Age','Course'])

js = df.to_json(orient = 'records')
print(js)

Output:

[{"Name":"Jay","Age":16,"Course":"BBA"},{"Name":"Jack","Age":19,"Course":"BTech"},{"Name":"Mark","Age":18,"Course":"BSc"}]

Method 3: Using orient = ‘index’

You can find the sample of applying this function below.

import pandas as pd
df = pd.DataFrame([['Jay',16,'BBA'],
                   ['Jack',19,'BTech'],
                   ['Mark',18,'BSc']],
                  columns = ['Name','Age','Course'])
js = df.to_json(orient = 'index')
print(js)

Output:

{"0":{"Name":"Jay","Age":16,"Course":"BBA"},
 "1":{"Name":"Jack","Age":19,"Course":"BTech"},
 "2":{"Name":"Mark","Age":18,"Course":"BSc"}}

Method 4: Using orient = ‘split’

The following is an example of using this method.

import pandas as pd
df = pd.DataFrame([['Jay',16,'BBA'],
                   ['Jack',19,'BTech'],
                   ['Mark',18,'BSc']],
                  columns = ['Name','Age','Course'])

js = df.to_json(orient = 'split')
print(js)

Output:

{"columns":["Name","Age","Course"],
 "index":[0,1,2],
 "data":[["Jay",16,"BBA"],["Jack",19,"BTech"],["Mark",18,"BSc"]]}

Method 5: Using orient = ‘table’

You can use the following sample as a reference.

import pandas as pd
df = pd.DataFrame([['Jay',16,'BBA'],
                   ['Jack',19,'BTech'],
                   ['Mark',18,'BSc']],
                  columns = ['Name','Age','Course'])

js = df.to_json(orient = 'table')
print(js)

Output:

{"schema": {"fields":[{"name":"index","type":"integer"},{"name":"Name","type":"string"},{"name":"Age","type":"integer"},{"name":"Course","type":"string"}],"primaryKey":["index"],"pandas_version":"0.20.0"}, "data": [{"index":0,"Name":"Jay","Age":16,"Course":"BBA"},{"index":1,"Name":"Jack","Age":19,"Course":"BTech"},{"index":2,"Name":"Mark","Age":18,"Course":"BSc"}]}

We can also export the JSON straight to a separate file. By including the file’s path in dataframe.to json(), you can do it as demonstrated below.

import pandas as pd
df = pd.DataFrame([['Jay',16,'BBA'],
                   ['Jack',19,'BTech'],
                   ['Mark',18,'BSc']], columns = ['Name','Age','Course'])

df.to_json("path\example.json", orient = 'table')

A JSON format file is exported to the designated path by the code above.

Notice that when using the to_json() function to write a DataFrame with the name of literal Index, the name of Index will be wrongly set to None in the next read operation.

This happens since the DataFrame can also utilize the .to_json() index to indicate that an Index name is lacking, and the ensuing read_json() process is unable to differentiate the two.

Keep in mind that any Index names that start with level_ and a MultiIndex have the same restriction.

The Bottom Line

DataFrame stores the information and arranges it in a tabular format, making it a data structure of two dimensions. Suppose you wish to interact with the servers; you need to convert DataFrame Pandas to JSON format.

In this guide, we have shown you different methods to perform this task. We also include many examples to help you better understand each method. Hopefully, this guide helped you gain some further important knowledge.Bonus: if you want to rename an index of a DataFrame in Pandas, check out our detailed tutorial here. Or, suppose a guide on converting DataFrame to Dictionary is what you are looking for; we also got you covered.