How to Convert JSON to CSV in Python

Converting from JSON to CSV in Python? We’ll show you how to do it quickly and easily!

Depending on what project you happen to be working on, your data can come in multiple formats. So, it’s important to be comfortable handling data in a variety of formats. JSON and CSV are two very popular formats. In this article, we’ll show you how to convert the JSON format into CSV in Python.

For some background reading, you may want to check out Learn How to Work with Files and Directories in Python, and How to Write to File in Python. These articles contain some useful information and examples which we’ll build on here.

The JSON File Format

JSON (JavaScript Object Notation) is a lightweight and easy-to-understand file format used to store and transfer data. It’s human readable – if you open the file in a text editor, you’ll be able to understand and interpret the structure of the file and the data within it.

If you know Python dictionaries, JSON’s structure may look familiar. The contents of a JSON file look something like this:

{
  "first_name": "Arna",
  "last_name": "Gunnarsson",
  "age": 38,
  "email":"arna.gunnarsson@email.com",
  "hobbies":["tennis", "cooking"],
  "married":false
}

JSON files contain name:value pairs. There are several allowed data types, such as strings, numbers, arrays (ordered lists inside square brackets), and Booleans (true, false). Nested objects (name:value pairs) and empty values (nulls) are also allowed. For some relevant learning material, check out our How to Read and Write JSON Files in Python course, which includes 35 interactive exercises.

The CSV File Format

CSV (Comma Separated Value) files are also a very popular way of storing and transferring data. The format is similar to an Excel spreadsheet; the data instances are rows in the file, and the values are separated by a comma. The files can also be viewed in a text editor and look a little like this:

first_name,last_name,age,email,hobbies,married
Arna,Gunnarsson,38,arna.gunnarsson@email.com,"tennis,cooking",false

There is less standardization with CSV files. Some implementations allow or require quotation marks around the fields. Having a comma in your data requires quotes around the entry. Some files may even have a different delimiter (such as a tab, semicolon, or white space) but still be given the .csv file handle.

Converting from JSON to CSV

There may be cases where you want to convert from JSON to CSV. Perhaps you’re working on a project that collects data from different sources and each source has a different format. Or maybe you only have data in JSON, but the script your colleague gave you requires data in the CSV format. In this case, you’ll have to convert from one format to the other.

Below is an example JSON file we’ll work with. Copy the following data into a text file and save it as data.json. We’ll show you two methods to go from a JSON file to the same data in the CSV format. Here’s the JSON file we’ll  use:

[
    {
        "first_name":"Arna",
        "last_name":"Gunnarsson",
        "age":38,
        "email":"arna.gunnarsson@email.com"
    },
    {
        "first_name":"Inga",
        "last_name":"Hagen",
        "age":32,
        "email":"ihgn_91@webmail.dk"
    }
]

The json and csv Modules

Python’s json module can be used to read and write JSON data. Its main functions are load() and loads() to load a file object or a string and dump() and dumps() to write to a JSON file. Sound complicated? The article How to Convert a String to JSON in Python may clear things up.

We’ll start by reading in our JSON data using open():

>>> import json
>>> with open('data.json') as f:
...     json_data = json.load(f)

The json_data object is a list of dictionaries; each dictionary corresponds to a row we’ll write to the CSV file. To do this, we’ll take advantage of Python’s csv module:

>>> import csv
>>> headers = json_data[0].keys()
>>> with open('data1.csv', 'w', newline='\n') as f:
...     writer = csv.DictWriter(f, fieldnames=headers)
...     writer.writeheader()
...     writer.writerows(json_data)

Here, we first extract the headers as the keys of the first dictionary in the list. Just be aware that we’re assuming all dictionaries in the list have the same keys. It could be useful to explicitly check this. Try building this functionality yourself.

Next, we open a file object with the name data1.csv and the newline character (defined as '\n') so we can write data into this file. In the following line, we create a DictWriter object, which maps the dictionaries into output rows. Finally, we write our header data, followed by each row using the writeheader() and writerows() functions.

For more information on the csv module, check out A Guide to the Python csv Module, How to Read CSV Files in Python, and Read a CSV File Into a List in Python.

pandas Is Your Friend

There is a way to achieve the same result using only one module and far fewer lines of code. It makes the process of converting JSON to CSV easier, more readable, and less error prone. The secret is the pandas library:

>>> import pandas as pd
>>> df = pd.read_json('data.json')
>>> df.to_csv('data2.csv', index=False)

Here we simply read the data directly from JSON into a pandas DataFrame. Then we convert it to CSV and write it to a file in one line. Setting index=False ensures the index from the pandas DataFrame is not written to the file data2.csv.

After trying out these two methods, in both cases the CSV file should look like this:

first_name,last_name,age,email
Arna,Gunnarsson,38,arna.gunnarsson@email.com
Inga,Hagen,32,ihgn_91@webmail.dk

Where to Next with JSON and CSV Data?

The json and csv modules are pretty fundamental. They’ve earned a place in our Top 15 Python Libraries for Data Science.

Once you have mastered the basics of Python, it’s important to keep adding new skills. Check out our Data Processing with Python track, which covers some advanced aspects of working with data. When you’re comfortable working with data in Python, you’ll need to use it to produce visualizations and tables to convey information. The article How to Pretty-Print Tables in Python could provide some inspiration on how to better present information to people who need it to make decisions.