Convert Pandas DataFrame to JSON

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.

Author: Manav Narula
Manav Narula avatar Manav Narula avatar

Manav is a IT Professional who has a lot of experience as a core developer in many live projects. He is an avid learner who enjoys learning new things and sharing his findings whenever possible.

LinkedIn

Related Article - Pandas DataFrame

  • Get Pandas DataFrame Column Headers as a List
  • Delete Pandas DataFrame Column
  • Convert Pandas Column to Datetime
  • Convert a Float to an Integer in Pandas DataFrame
  • Sort Pandas DataFrame by One Column's Values
  • Get the Aggregate of Pandas Group-By and Sum
  • Related Article - Pandas JSON

  • Convert JSON to a Pandas DataFrame