Pandas – Convert JSON to CSV

Convert JSON to CSV using pandas in python? 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 that 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 line of the file is a row consisting of several fields and each field is separated by any 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 transformatios you want
  5. Convert JSON to CSV file

Quick Examples of Convert JSON to CSV

Following are quick examples of how to convert JSON string or file to CSV file.


# 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')

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

1. Create a JSON file

First, let’s create a JSON file that you wanted 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.

pandas convert json to csv
JSON File

Find this JSON file at GitHub. In case you wanted 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"}}'

2. Install Pandas

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


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

3. Load the JSON file

You can represent data in a JSON multiple ways, I have written a complete article on how to read JSON file 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)

# Outputs
#  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)

# Outputs
#   Courses    Fee Duration
#r1   Spark  25000  50 Days

4. 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 created from JSON for example, replace NaN with string, replace empty with NaN, converting one value to another e.t.c

5. Convert JSON to CSV

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


# Write DataFrame to CSV file
df.to_csv()

6. 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')

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 file, applying transformations (optional), and finally converting to CSV file.

References

You may also like reading:

  1. Pandas Drop Columns from DataFrame
  2. Pandas Normalize Columns of DataFrame
  3. Pandas Merge DataFrames Explained Examples
  4. pandas reset_index() – Rest Index on DataFrame
  5. Pandas Read Excel with Examples
  6. Create Pandas Plot Bar Explained with Examples
  7. Pandas Get Floor or Ceil of Series
  8. Count NaN Values in Pandas DataFrame
  9. Sort Pandas DataFrame by Date (Datetime)
  10. Pandas Rolling Sum

Leave a Reply

You are currently viewing Pandas – Convert JSON to CSV