Pandas – Convert JSON to CSV

Convert JSON to CSV using Pandas, Pandas is a library in Python that can be used to convert JSON (String or file) to CSV file, all you need is first read the JSON into a pandas DataFrame and then write pandas DataFrame to CSV file.

The JSON stands for JavaScript Object Notation which is used to store and transfer the data between two applications. To use JSON in Python you have to use Python supports JSON through a built-in package called JSON. To use this feature, we import the JSON package in Python script. The text in JSON is done through quoted-string which contains the value in key-value mapping within { }. It is similar to the dictionary in Python.

CSV stands for Comma Separated Values that are used to store tabular data in a text format. Each file line is a row consisting of several fields and each field is separated by a delimiter. This is a very common format in the industry to exchange data between two organizations or different groups in the same organization.

Following are the detailed steps involved in converting JSON to CSV in pandas. In this article, I will cover these steps with several examples.

1. Create a JSON file
2. Install pandas
3. Load the JSON into pandas DataFrame
4. Apply any transformations you want
5. Convert JSON to CSV file

Key Points –

• Use pd.read_json() to load JSON data into a Pandas DataFrame, making it easy to work with tabular data.
• Use the to_csv() function to export the DataFrame to a CSV file after loading and structuring the JSON data.
• When reading JSON, specify the orientation (like records or columns) based on the JSON structure for accurate parsing.
• Use caution when specifying file paths in to_csv() to avoid accidentally overwriting existing files.
• Specify an encoding format (e.g., UTF-8) when exporting to ensure compatibility, especially for international characters.
• When exporting to CSV, specify custom delimiters if needed for compatibility with other applications or formats.

1. Quick Examples of Convert JSON to CSV

If you are in a hurry, below are some quick examples of how to convert JSON string or file to CSV file.

# Quick examples of convert JSON to CSV

# Example 1: Convert JSON file to CSV file
# pandas read JSON file
df = pd.read_json('courses_data.json')
df.to_csv('courses.csv')

# Example 2: Convert JSON String to CSV file
# Read json from String
json_str = '{"Courses":{"r1":"Spark"},"Fee":{"r1":"25000"},"Duration":{"r1":"50 Days"}}'
df = pd.read_json(json_str)
df.to_csv('courses.csv')

Now let’s follow the steps specified above to convert JSON to CSV file using the Python pandas library.

2. Create a JSON file

First, let’s create a JSON file that you want to convert to a CSV file. Pandas by default support JSON in single lines or in multiple lines. The following file contains JSON in a Dict-like format.

Find this JSON file on GitHub. In case you want to use the JSON string, let’s use the below.

# Read json from String
json_str = '{"Courses":{"r1":"Spark"},"Fee":{"r1":"25000"},"Duration":{"r1":"50 Days"}}'

3. Install Pandas

If you don’t have pandas on your system, install Python Pandas using the pip command. skip this step. If you already have pandas installed.

# Install pandas using pip
pip install pandas
(or)
pip3 install pandas

4. Load the JSON file

You can represent data in a JSON in multiple ways, I have written a complete article on how to read JSON files into DataFrame with several JSON types.

In the below example, I am loading JSON from a file courses_data.json file.

# Pandas read JSON File
df = pd.read_json('courses_data.json')
print(df)

# Output:
#  Courses    Fee Duration
# 0   Spark  25000  50 Days
# 1  Pandas  20000  35 Days
# 2    Java  15000 

To read a JSON from a string variable.

# Read json from String
json_str = '{"Courses":{"r1":"Spark"},"Fee":{"r1":"25000"},"Duration":{"r1":"50 Days"}}'
df = pd.read_json(json_str)
print(df)

# Output:
#   Courses    Fee Duration
# r1   Spark  25000  50 Days

5. Apply Transformations

This is an optional step. In real-time applications, we are often required to transform the data and write the DataFrame result to a CSV file.

The transformation can be changing the data on the DataFrame that was created from JSON for example, replacing NaN with string, replacing empty with NaN, converting one value to another e.t.c

6. Convert JSON to CSV

Now write the Pandas DataFrame to CSV file, with this, we have converted the JSON to a CSV file.

# Write DataFrame to CSV file
df.to_csv()

7. Complete Example

import pandas as pd
# Convert JSON File to CSV File
# pandas read JSON File
df = pd.read_json('courses_data.json')
df.to_csv('courses.csv')

# Convert JSON String to CSV File
# Read json from String
json_str = '{"Courses":{"r1":"Spark"},"Fee":{"r1":"25000"},"Duration":{"r1":"50 Days"}}'
df = pd.read_json(json_str)
df.to_csv('courses.csv')

Frequently Asked Questions on Pandas Convert JSON to CSV

Conclusion

In this article, you have learned steps on how to convert JSON to CSV in pandas using the pandas library. steps include installing pandas, loading JSON files, applying transformations (optional), and finally converting to CSV files.

Related Articles

• Pandas Read JSON File with Examples
• Pandas – Convert DataFrame to JSON String
• How to Convert Pandas Uppercase Column
• Pandas DataFrame quantile() Function
• How to Read CSV from String in Pandas
• Pandas Read Text with Examples
• Pandas read_csv() with Examples
• Pandas Write DataFrame to CSV
• Convert Pandas Column to Lowercase
• Export Pandas to CSV without Index & Header
• Pandas Read Multiple CSV Files into DataFrame
• How to read CSV without headers in pandas

References

• https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_csv.html