How to convert pandas DataFrame into JSON in Python?

by

In this Python tutorial, we will learn how to convert Python DataFrame to JSON file.

There are four ways to convert dataframe to JSON in Python, which are shown below:

  • Using the to_json() method
  • Using the json module
  • Using the simplejson module
  • Using the json_normalize() function

Python Convert Dataframe to Json

In Python, there are several ways to convert a DataFrame to JSON format. Here are some of the most common methods:

Method-1: Python Convert Dataframe to Json using the to_json() method

The simplest and most straightforward method of converting a Pandas DataFrame to JSON is by using the to_json() method. The to_json() method converts the DataFrame to a JSON.

# Import the pandas library with an alias pd
import pandas as pd

# Create a DataFrame object with two columns 'name' and 'population', and two rows of data, representing the USA and Brazil
df = pd.DataFrame({'name': ['USA', 'Brazil'], 'population': [336157983, 30216422446]})

# Use the to_json() method of the DataFrame object to convert it into a JSON string, with 'records' as the orientation 
json_data = df.to_json(orient='records')

# Print the JSON string
print(json_data)

The above code imports the pandas library with the alias pd. Then, it creates a DataFrame object with two columns, name and population, and two rows of data, representing the USA and Brazil.

  • Next, the to_json() method of the DataFrame object is used to convert it into a JSON string with ‘records’ as the orientation. The resulting JSON string is assigned to the json_data variable.
  • Finally, the print() function is used to display the json_data variable, which contains the JSON representation of the df DataFrame object.
Output: [{"name":"USA","population":336157983},{"name":"Brazil","population":30216422446}]

Read: Python Pandas Write to Excel 

Method-2: Python Convert Dataframe to JSON using the json module

Another method of converting a DataFrame to JSON is by using the json module. This method allows for more customization and control over the output JSON.

# Import the pandas library with an alias pd
import pandas as pd

# Import the json library
import json

# Create a DataFrame object with two columns 'name' and 'population', and two rows of data, representing the USA and Brazil
df = pd.DataFrame({'name': ['USA', 'Brazil'], 'population': [336157983, 30216422446]})

# Convert the DataFrame object into a dictionary with two keys: 'data' and 'columns'. 'data' maps to the data in the DataFrame, 
# converted to a list of lists, and 'columns' maps to a list of column names.
# The resulting dictionary is then converted to a JSON string using the json.dumps() method.
json_data = json.dumps({'data': df.values.tolist(), 'columns': df.columns.tolist()})

# Print the resulting JSON string.
print(json_data)

The code imports pandas and json libraries. It then creates a DataFrame object with two columns, name and population, and two rows of data, representing the USA and Brazil.

  • Next, the to_dict() method of the DataFrame object is used to convert it into a dictionary, where each key represents a column name and each value represents a list of the column’s values.
  • This dictionary is then transformed into a new dictionary with two keys: data and columns. The data key maps to a list of lists that contains the data in the DataFrame, and the columns key maps to a list of column names.
  • Finally, the json.dumps() method is used to convert this dictionary into a JSON string
Output: {"data": [["USA", 336157983], ["Brazil", 30216422446]], "columns": ["name", "population"]}

Read: How to update column values in Python Pandas

Method-3: Python Convert Dataframe to Json using the simplejson module

The simplejson module is a third-party module in Python that provides a faster and more efficient way to encode and decode JSON in Python.

#Install the simplejson library
!pip install simplejson

#Import pandas and simplejson libraries
import pandas as pd
import simplejson as json

#Create a dataframe
df = pd.DataFrame({'name': ['USA', 'Brazil'], 'population': [336157983, 30216422446]})

#Convert the dataframe to a JSON string using simplejson
json_d = json.dumps(df.to_dict(orient='records'))

#Print the JSON string
print(json_d)
Output: [{"name": "USA", "population": 336157983}, {"name": "Brazil", "population": 30216422446}]

Read: How to delete a column in pandas

Method-4: Python Convert Dataframe to Json using the json_normalize() function

The json_normalize() function from the Pandas library can be used to flatten a JSON object into a DataFrame. You can then convert the DataFrame to JSON using the to_json() method.

import pandas as pd
import json

# Define a sample JSON object
json_data = '[{"name":"USA","pop":336157983},{"name":"Brazil","pop":30216422446}]'

# Load the JSON data into a Pandas DataFrame
df = pd.json_normalize(json.loads(json_data))

# Convert the DataFrame to JSON
json_output = df.to_json(orient='records')

print(json_output)

The above code loads a JSON object containing country names and their populations. It then converts this JSON object into a Pandas DataFrame using the pd.json_normalize() function.

  • After that, it converts the DataFrame back into JSON using the to_json() method with the orient=’records’ parameter to create a JSON array of records. Finally, it prints the resulting JSON output.
Output: [{"name":"USA","pop":336157983},{"name":"Brazil","pop":30216422446}]

You may also like to read the following Python tutorials.

In this tutorial, we have covered how to convert dataframe to json using the following methods:

  • Using the to_json() method
  • Using the json module
  • Using the simplejson module
  • Using the json_normalize() function