Convert Pandas DataFrame to JSON

1. orient = 'columns'
2. orient = 'records'
3. orient = 'index'
4. orient = 'split'
5. orient = 'table'

JSON stands for JavaScript Object Notation. It is based on the format of objects in JavaScript and is an encoding technique for representing structured data. It is widely used these days, especially for sharing data between servers and web applications.

We will introduce how to convert a DataFrame to a JSON string in this article.

We will work with the following DataFrame:

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 DataFrame has a method dataframe.to_json() which converts a DataFrame to a JSON string or store it as an external JSON file. The final JSON format depends on the value of the orient parameter, which is 'columns' by default but can be specified as 'records', 'index', 'split', 'table', and 'values'.

All formats are covered below:

orient = 'columns'

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"}}

orient = 'records'

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"}]

orient = 'index'

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"}}

orient = 'split'

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"]]}

orient = 'table'

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"}]}

As discussed earlier, we can also export the JSON directly to an external file. It can be done as shown below by providing the file’s path in the dataframe.to_json() function.

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')

The above code exports a JSON file to the specified path.