3 Best Ways to Convert JSON to CSV in Python

JSON (JavaScript Object Notation) and CSV (Comma Separated Values) are two very popular and simple-to-use file formats for storing and exchanging data. In this article, we’ll see a few simple methods to convert JSON to CSV in Python. Before that let’s see what JSON and CSV are.

(If you are already familiar with JSON and CSV formats, please skip the introductory sections and jump to how we convert JSON to CSV in Python.)

What is JSON?

JSON stands for JavaScript Object Notation.

It is a lightweight data-interchange format that is easy for humans to read and write and easy for machines to parse and generate. JSON data is a set of key-value pairs. It is often used in passing or exchanging data between web applications and servers.

And in most of the REST APIs, we commonly send request body and response in JSON format. And its values can be in the below types.

• string
• number
• boolean
• null
• array
• JSON object

Ex:

[
   {
       "name":"Cristiano Ronaldo",
       "game":"football",
       "personalInformation":{
           "born":1985,
           "heightInMeters":1.87
       }
   },
   {
       "name":"Virat Kohli",
       "game":"cricket",
       "personalInformation":{
           "born":1988,
           "heightInMeters":1.75
       }
   },
   {
       "name":"Roger Federer",
       "game":"tennis",
       "personalInformation":{
           "born":1981,
           "heightInMeters":1.85
        }
   }
]

CSV stands for comma-separated values. 

It’s a plain text file format used to represent tabular formatted data. And if you compare CSV data with table data in SQL, every data record in CSV is equivalent to a row in a table and the first data record (header) in the CSV file is equivalent to columns in that table.

CSV file format is widely used in many databases and applications like spreadsheets. It is used by many people and organizations for many purposes.

And It supports many varieties of data types like BIT, DATE, TIME, TIMESTAMP, DECIMAL, DOUBLE, INTEGER, NUMERIC, and VARCHAR.

Ex:

id,name,game,born,height_in_meters

1,'Cristiano Ronaldo','football',1985,1.87

2,'Virat Kohli','cricket',1988,1.75

3,'Roger Federer','tennis',1981,1.85

Now Let’s see What are the advantages of CSV over JSON.

Why CSV over JSON?

1. Compatibility: CSV is a widely used file format in many applications like spreadsheets, databases, and statistics applications. And importing and exporting CSV files is easy.
2. Easy to Use: CSV files can be easily opened in spreadsheet applications which will be used for a wide variety of reasons like storing, organizing, and analyzing data. And these spreadsheets make the job easier for many business professionals, researchers, and product managers.
3. Performance: CSV files are generally smaller and faster to read and write data compared to JSON. And this can be very crucial in dealing with large data sets in the real world.

How do we convert JSON to CSV in python?

There are many different ways to convert JSON to CSV in python. Now let’s see two simple methods to convert JSON to CSV.

1. Using the CSV module(writing data to CSV file row by row)
2. Using the CSV module(writing all rows in one go)
3. Using Pandas Library

Method 1: Using the CSV module (writing data to CSV file row by row)

For this method, we will take a JSON file with the below player’s data.

[
	{
		"name": "Cristiano Ronaldo",
		"game": "football",
		"country": "Portuguese"
	},
	{
		"name": "Virat Kohli",
		"game": "cricket",
		"country": "India"
	},
	{
		"name": "Roger Federer",
		"game": "tennis",
		"country": "Switzerland"
	}
]

And now we’ll use the CSV module to convert JSON into CSV format using the below steps.

Step 1: Loading JSON file data into a python dictionary by utilizing the json module.

import json

# Open the JSON file
with open('input.json') as input_file:
    # loading a json file as a dict
    data = json.load(input_file)

print("json data loaded:")
print(data)

Output:

json data loaded:
[{‘name’: ‘Cristiano Ronaldo’, ‘game’: ‘football’, ‘country’: ‘Portuguese’}, {‘name’: ‘Virat Kohli’, ‘game’: ‘cricket’, ‘country’: ‘India’}, {‘name’: ‘Roger Federer’, ‘game’: ‘tennis’, ‘country’: ‘Switzerland’}]

Step 2: Opening a CSV File in write mode with csv module:

import csv

output_file = open('output.csv', 'w')
csv_writer = csv.writer(output_file)

Step 3: Now in step 3 we will use both step 1 and step 2 along with some simple code to write already read JSON data into the opened CSV file.

import json
import csv

# Open the JSON file
with open('input.json') as input_file:
    # loading a json file as a dict
    players_data = json.load(input_file)

output_file = open('output.csv', 'w')
csv_writer = csv.writer(output_file)

count = 0
for player in players_data:
    if count == 0:
        # taking dictionary keys and writing it in the first row in csv file as header.
        header = player.keys()
        csv_writer.writerow(header)
        count += 1
    # writing every player values from dictionary into csv file.
    csv_writer.writerow(player.values())

# closing the output file
output_file.close()

The output of the above program:

Method 2: With CSV module (writing all rows at once)

step 1 and step 2 for this method are same as method 1 but in step 3 we’ll be writing the all data in the dictionary to a single in one go using csv module’s DictWriter class.

import json
import csv

# Open the JSON file
with open('input.json') as input_file:
    # loading a json file as a dict
    players_data = json.load(input_file)

# taking all keys in dictionary
player_key_names = players_data[0].keys()
with open('output.csv', 'w') as csv_file:
    writer = csv.DictWriter(csv_file, fieldnames=player_key_names)
    writer.writeheader()
    writer.writerows(players_data)

Let’s see what’s happening in the above code line by line

Line 10: Hereafter loading the JSON file data into players_data dictionary, we have taken all key names into a variable named ‘players_key_names’

Line 11: opening a CSV file in write mode and as we are opening the file using open() and with the statement, we don’t need to explicitly close the file after writing the data. It will be closed after your file operation is completed. This is because internally with statement comes with two built-in methods __enter()__ and __exit()__. And the exit() method will close the file without you telling it specifically when the specified operation is completed.

Line 12: Creating the object for DictWriter by passing the filename and field names to its constructor.

Line 13: This will write a row with filed names given in the fieldnames parameter while constructing DictWriter object.

Line 14: This statement will write all data in players_data dictionary into the given CSV file.

Method 3: Use Pandas library

We all know how powerful and easy-to-use tool pandas is and its role in data analysis and manipulation. In this method use the pandas library to convert JSON file into CSV file. Before we do that, let’s install pandas using below pip command.

pip install pandas

In this section, We’ll try this method on two different input JSON formats.

For Input 1 :

[
      {
         "name":"Cristiano Ronaldo",
         "game":"football",
         "country":"Portuguese"
      },
      {
         "name":"Virat Kohli",
         "game":"cricket",
         "country":"India"
      },
      {
         "name":"Roger Federer",
         "game":"tennis",
         "country":"Switzerland"
      }
]

Step 1: Here we’ll use pandas read_json() function to convert the JSON file in the given path to pandas dataframe.

import pandas as pd

# open and read json file using pandas read_json method
df = pd.read_json('input1.json')

Step 2: Now, We’ll simply pandas to_csv() method to convert the dataframe to specified csv file.

df.to_csv('output3.csv', index=None)

to_csv() method takes the below arguments as input.

DataFrame.to_csv(path_or_buf=None, sep=’,’, na_rep=”, float_format=None, columns=None, header=True, index=True, index_label=None, mode=’w’, encoding=None, compression=’infer’, quoting=None, quotechar='”‘, lineterminator=None, chunksize=None, date_format=None, doublequote=True, escapechar=None, decimal=’.’, errors=’strict’, storage_options=None)

For more information please refer to this pandas documentation.

And the final program looks like below:

import pandas as pd

# open and read json file using pandas read_json method
df = pd.read_json('input-1.json')
print(type(df))
# convert pandas data frame into CSV file
df.to_csv('output3.csv', index=None)

Output of the above program:

And now we’ll try to convert another JSON format data file into CSV file in python.

For Input 2:

{
   "name":{
      "0":"Cristiano Ronaldo",
      "1":"Virat Kohli",
      "2":"Roger Federer"
   },
   "country":{
      "0":"Portuguese",
      "1":"India",
      "2":"Switzerland"
   },
   "game":{
      "0":"football",
      "1":"cricket",
      "2":"tennis"
   }
}

If we run the same program for the above example also, we’ll get the same output.

import pandas as pd
#open and read json file using pandas read_json method
df = pd.read_json ('input-2.json')
#convert pandas data frame into CSV file
df.to_csv ('output2.csv', index = None)

Output:

pandas module also provides different options to convert CSV to JSON with a lot of customizations.

Convert Nested JSON to CSV in Python:

Till now we have seen how to convert simple or plain JSON to CSV in python. Now with the knowledge from the above methods, let’s see how can we convert nested JSON to CSV in python using the pandas library.

Examples of nested JSON:

[
   {
       "name":"Cristiano Ronaldo",
       "game":"football",
       "personalInformation":{
           "born":1985,
           "heightInMeters":1.87
       }
   },
   {
       "name":"Virat Kohli",
       "game":"cricket",
       "personalInformation":{
           "born":1988,
           "heightInMeters":1.75
       }
   },
   {
       "name":"Roger Federer",
       "game":"tennis",
       "personalInformation":{
           "born":1981,
           "heightInMeters":1.85
        }
   }
]

We have personalInformation JSON inside the Player JSON object. So we can treat this as nested JSON. Let’s see what will happen if apply method 3 to this example.

import pandas as pd

# open and read json file using pandas read_json method
df = pd.read_json('input3.json')
# convert pandas data frame into CSV file
df.to_csv('output3.csv', index=None)

Output:

name,game,personalInformation
Cristiano Ronaldo,football,"{'born': 1985, 'heightInMeters': 1.87}"
Virat Kohli,cricket,"{'born': 1988, 'heightInMeters': 1.75}"
Roger Federer,tennis,"{'born': 1981, 'heightInMeters': 1.85}"

While reading the JSON file, pandas will treat the personalInformation object as one key and the whole object as one value. If we want to avoid this and want to flatten this personalInformation object into multiple columns, we need to use json_normalize() method.

json_normalize() expects the dictionary as input and it returns dataframe. It also supports below input parameters.

pandas.json_normalize(data, record_path=None, meta=None, meta_prefix=None, record_prefix=None, errors=’raise’, sep=’.’, max_level=None)

import pandas as pd
import json

# open and read json file using pandas read_json method
with open('input3.json', 'r') as file:
    players_dict = json.load(file)

df = pd.json_normalize(players_dict)
# convert pandas data frame into CSV file
df.to_csv('output3.csv', index=None)

Output:

name,game,personalInformation.born,personalInformation.heightInMeters
Cristiano Ronaldo,football,1985,1.87
Virat Kohli,cricket,1988,1.75
Roger Federer,tennis,1981,1.85

csv module vs pandas library

Choosing one between csv module and pandas library is dependent upon your specific need and use case.

On one side, csv module offers methods to read and write into CSV files. And it is a very convenient and lightweight option to deal with basic CSV parsing and generation tasks. But it doesn’t offer any advanced features like handling different data types and handling missing values from CSV files.

On the other side, pandas is a powerful and flexible library that comes with many advanced functions to analyze and manipulate CSV data.

So the choice between these two purely depends upon your requirement. If you just want a simple lightweight solution to read and write CSV files, go for the csv module. But along with reading and writing data into CSV file if you want powerful and advanced functions to analyze and manipulate data go for pandas library.

For more interesting and useful posts, please follow us here.

Frequently Asked Questions