How to Convert JSON to CSV in Python

How to Convert JSON to CSV in Python
Image Credit: oatawa/iStock/GettyImages

You can convert JSON to CSV in the Python programming language using built-in libraries that come with the language. You can loop through the JSON object and output its keys and values as rows in a comma-separated value file. Keep in mind that not all JSON files can easily be represented as single CSVs, so, depending on the format, you may need to use multiple CSV files to represent all the data in a useful way.

Understanding JSON and CSV

JSON and CSV files are two common ways to represent digital data. JSON stands for JavaScript Object Notation, and it's a way of representing data as nested mappings of keys to values as well as lists of data. It's inspired by how data is represented in the JavaScript programming language, but many modern programming languages including Python have tools for processing JSON data.

A comma-separated value file is another way to represent structured data. These files are often used with spreadsheets and similar types of programs, so if you want to import JSON to Excel it can be useful to convert it to a CSV file first. Generally, CSV files include column headers at the top of the file indicating what is stored in each column. Then, each line in the file consists of multiple values, one for each column, separated by commas. There are special rules for handling data with commas in it.

Just as with JSON files, there are many tools in many programming languages, including Python, for generating and reading CSV files.

Convert JSON to CSV

You can convert JSON to CSV using the built-in JSON and CSV libraries in Python. Import them into your code by adding lines saying "import json" and "import csv" near the top of your code.

Then, use the JSON library's "load" method to import the data from a JSON file. It will be loaded as a Python dictionary. Use the dictionary's "keys" method to determine the data fields in the JSON file that will become the columns in your CSV file.

Use the CSV library's "DictWriter" class to create a CSV file with those columns, specifying them using the DictWriter's "fieldnames" argument. Use its "writeheaders" method to output the column names at the top of the CSV.

Then, loop through the JSON object with a for loop. Call the CSV object's "writerow" method on each data element in the JSON file to create a corresponding row in the CSV file.

Complex JSON Files

Some complex JSON files can be harder to represent as CSVs. This can include files that don't immediately line up to a standard row and column structure. If you're dealing with such a file and want to convert it to a CSV, you have a few options.

First, you can look for the total set of data fields that occur in the JSON file and make these the columns of your CSV. Leave a column blank in a particular row if it doesn't appear there. Use the option "extrasaction='ignore'" in setting up the Python CSV DictWriter to automatically do this for fields that don't occur in a particular piece of data.

You can also consider representing the JSON file as multiple CSVs, one for each type of data that is represented in it. Use Python if statements to detect which CSV file a particular piece of data should be written to.

This technique can also be useful for nested JSON files, in which JSON objects contain other objects. These sub-objects can be put into their own CSV file with an indication of what row in the primary CSV they correspond to.

references