Convert Pandas DataFrame to JSON
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.
