Convert pandas DataFrame to JSON

In this article, we will discuss how to convert JSON to pandas DataFrame.

Introduction

JSON stands for Java Script Object Notation which is in the form of key:value pair.

Structure:

'''{
    { key:value },
    { key:value },
    -------------,
    { key:value },
} '''

DataFrame in pandas is an two dimensional data structure that will store data in two dimensional format. One dimension refers to a row and second dimension refers to a column, So It will store the data in rows and columns.

We can able to create this DataFrame using DataFrame() method. But this is available in pandas module, so we have to import pandas module.

Syntax:

pandas.DataFrame(data)

Where, data is the input dataframe, The data can be a dictionary that stores list of values with specified key

Scenario 1 : Convert dataframe to json in pretty format.

Here, we will use to_json() method to convert dataframe to json.

Syntax:

dataframe_input.to_json('file_name.json')

where, dataframe_input is the input dataframe,

file_name is the json file that is converted.

Example:

In this example, we will create pandas dataframe with 4 rows and 4 columns and convert into json.

import pandas as pd

#create dataframe from the college data
data= pd.DataFrame({'college_id':['c-001','c-021','c-002','c-004'],

                    'college_name':["vignan university","vvit","RVR - JC","Andhra University"],

                   "college_address":["guntur","guntur","guntur","guntur"],

                    "Total Staff":[1200,3422,5644,670]

                   },index=['one','two','three','four'])

#display the dataframe
print(data)


#convert to json 
data.to_json('converted.json')

Output:

      college_id       college_name college_address  Total Staff
one        c-001  vignan university          guntur         1200
two        c-021               vvit          guntur         3422
three      c-002           RVR - JC          guntur         5644
four       c-004  Andhra University          guntur          670

The file will be downloaded, if you open the file, the converted json will be:

{"college_id":{"one":"c-001","two":"c-021","three":"c-002","four":"c-004"},"college_name":{"one":"vignan university","two":"vvit","three":"RVR - JC","four":"Andhra University"},"college_address":{"one":"guntur","two":"guntur","three":"guntur","four":"guntur"},"Total Staff":{"one":1200,"two":3422,"three":5644,"four":670}}

Scenario 2 : Convert dataframe to json with different orientations.

Here, we will use to_json() method to convert dataframe to json by specifying the orientations.

Syntax:

dataframe_input.to_json('file_name.json',orinet)

where, dataframe_input is the input dataframe,

file_name is the json file that is converted and orient specifies the type of orient that the json will be converted.

Example 1:

In this example, we will create pandas dataframe with 4 rows and 4 columns and convert into json with records orient..

Syntax:

dataframe_input.to_json('file_name.json',orinet='records')

This will return the output in the following format,

[{column_name :value},......, {column_name : value}]

Code:

import pandas as pd

#create dataframe from the college data
data= pd.DataFrame({'college_id':['c-001','c-021','c-002','c-004'],

                    'college_name':["vignan university","vvit","RVR - JC","Andhra University"],

                   "college_address":["guntur","guntur","guntur","guntur"],

                    "Total Staff":[1200,3422,5644,670]

                   },index=['one','two','three','four'])


#convert to json 
data.to_json('converted.json',orient='records')

Output:

The file will be downloaded, if you open the file, the converted json will be:

[{"college_id":"c-001","college_name":"vignan university","college_address":"guntur","Total Staff":1200},{"college_id":"c-021","college_name":"vvit","college_address":"guntur","Total Staff":3422},{"college_id":"c-002","college_name":"RVR - JC","college_address":"guntur","Total Staff":5644},{"college_id":"c-004","college_name":"Andhra University","college_address":"guntur","Total Staff":670}]

Example 2:

In this example, we will create pandas dataframe with 4 rows and 4 columns and convert into json with index orient..

Syntax:

dataframe_input.to_json('file_name.json',orinet='index')

This will return the output in the following format.

{index : {column_name : value}}

Code:

import pandas as pd

#create dataframe from the college data
data= pd.DataFrame({'college_id':['c-001','c-021','c-002','c-004'],

                    'college_name':["vignan university","vvit","RVR - JC","Andhra University"],

                   "college_address":["guntur","guntur","guntur","guntur"],

                    "Total Staff":[1200,3422,5644,670]

                   },index=['one','two','three','four'])


#convert to json 
data.to_json('converted.json',orient='index')

Output:

The file will be downloaded, if you open the file, the converted json will be:

{"one":{"college_id":"c-001","college_name":"vignan university","college_address":"guntur","Total Staff":1200},"two":{"college_id":"c-021","college_name":"vvit","college_address":"guntur","Total Staff":3422},"three":{"college_id":"c-002","college_name":"RVR - JC","college_address":"guntur","Total Staff":5644},"four":{"college_id":"c-004","college_name":"Andhra University","college_address":"guntur","Total Staff":670}}

Example 3:

In this example, we will create pandas dataframe with 4 rows and 4 columns and convert into json with columns orient..

Syntax:

dataframe_input.to_json('file_name.json',orinet='columns')

Code:

import pandas as pd

#create dataframe from the college data
data= pd.DataFrame({'college_id':['c-001','c-021','c-002','c-004'],

                    'college_name':["vignan university","vvit","RVR - JC","Andhra University"],

                   "college_address":["guntur","guntur","guntur","guntur"],

                    "Total Staff":[1200,3422,5644,670]

                   },index=['one','two','three','four'])


#convert to json 
data.to_json('converted.json',orient='columns')

Output:

The file will be downloaded, if you open the file, the converted json will be:

{"college_id":{"one":"c-001","two":"c-021","three":"c-002","four":"c-004"},"college_name":{"one":"vignan university","two":"vvit","three":"RVR - JC","four":"Andhra University"},"college_address":{"one":"guntur","two":"guntur","three":"guntur","four":"guntur"},"Total Staff":{"one":1200,"two":3422,"three":5644,"four":670}}