Converting JSON to CSV and back again using Python

Hand pointing at a drawing on a table

When working in with data in any programming language, it is very common to use both JSON and CSV data structures.  Today we will discuss how you can convert your JSON files to CSV files.  And how to convert your CSV files into JSON files.

There are a variety of reasons you might want to convert your JSON file into a CSV file and vice-versa.  Generally you would do this for compatibilty with whatever application you are trying to work with.  For example, if you have a powershell script that you want to consume this data, it is much easier to use CSV files with powershell than it is to use JSON.

But, if you have a CSV file and you need to post some data to an API, it may be more appropriate to convert your data into JSON format since JSON is more common when interacting with web apis.

Convert JSON to CSV

Before starting this section, I recommend you read our article explaining What is JSON?  In that article, I go into detail about what JSON is, and some basics for working with JSON.

Before we can start, we need some example JSON data.  Let’s save the following into test.json:

We will use this dataset for the rest of the examples in this article.  But, before we continue, notice that this is simply an array of JSON objects.  If you have nested JSON objects, this is a much harder process to accomplish.  Nexted JSON objects would be something similar to this:

Notice in the JSON above, you have a first level key of the users name, next you have the JSON object returned as the value for each user.  By nesting the JSON in this fashion, it makes it a lot more difficult to convert to a CSV.  You will need to convert it into the JSON array like I mentioned earlier before you can proceed.
Now that we have covered the structure of the JSON, the first thing we need to do is import our JSON and CSV libriaries at the top of our python script:

Next we read this file into a variable and parse it into a JSON object:

At this stage we can test if things are working as expected by running:

If things went according to plan, you should see “Bob” printed on your screen.
This means we now have our JSON object in memory.  Next step, we need to write our code to convert the JSON into CSV.
Create list of keys in the JSON

Now that we have our list of keys from the JSON, we are going to write the CSV Header

The Result of this code will be a CSV that looks like this:

Now that we have some working code, let’s convert it into a function so we can pass in some JSON and it will output a csv file

To use this function, we can call:

Converting CSV into JSON

In the previous section we covered reading in some JSON and writing out a CSV file.  In this section our aim is to do the opposite. We are going to read in a CSV file and write out a JSON file.

For this example we will read in the CSV file w created in the previous section.  But first we need to import our JSON and CSV libraries:

Now we declare a couple of variables to specify the path to the csv file we want to read from, and the JSON file we want to write to:

Next we convert our CSV file into a dictionary

In the final step, we iterate through each row in the reader object and write it out to our new JSON file using the json.dump command

If everything went as planned, you should now have test2.json sitting on your filesystem. The contens of the file should be like this:

Next let’s turn this into a function so it is a bit easier to call.  We will create a function that takes the path to the CSV file as the first argument, and the path to the output JSON file as the second argument.
Here is what that function might look like:

You would call the function like this:

Summary

Today we have discussed what it takes to convert a JSON document into a CSV file. And how to convert a CSV file into a JSON document.  As you can see there is not a built-in function for doing this. You have to do a bit of work to manipulate the data into the right formats.  You can only do this if you have an array of JSON documents, and all the documents have the same structure.  If your original JSON has nested objects inside it, you will need to do additional manipulation of the JSON before you can convert it to a CSV.

If you are starting with a CSV file and converting into a JSON document, the process is much more straight forward.  since they are less likely to have nested documents inside of them.

Be the first to comment on "Converting JSON to CSV and back again using Python"

Leave a comment

%d bloggers like this: