json to csv in python

How to Convert JSON to CSV in Python

JSON is a commonly used data by applications and websites to transfer information between server & clients. Python allows you to easily work with JSON data using in-built library. Often you may need to convert JSON to CSV in python to be able to import it into MS Excel or other applications. This is a much required feature in most applications and websites, often seen as ‘export as csv’ button or link. In this article, we will learn how to convert JSON to CSV in Python.


How to Convert JSON to CSV in Python

Here are the steps to convert JSON to CSV in Python. JSON data is quite flexible and can become quite complex so we will look at a couple of use cases with simple JSON key-value pairs. JSON keys will become CSV headers.

Let us say your JSON file data.json looks like the following.

{
"emp_details":[
   {
    "emp_name":"John",
    "email":"john.doe@gmail.com",
    "job":"developer"
},
{
    "emp_name":"Jane",
    "email":"jane.doe@gmail.com",
    "job":"analyst"
}
]
}

Here is the code to convert this JSON into CSV.

# Python program to convert
# JSON file to CSV


import json
import csv


# Opening JSON file and loading the data
# into the variable data
with open('data.json') as json_file:
	data = json.load(json_file)

employee_data = data['emp_details']

# now we will open a file for writing
data_file = open('data_file.csv', 'w')

# create the csv writer object
csv_writer = csv.writer(data_file)

# Counter variable used for writing
# headers to the CSV file
count = 0

for emp in employee_data:
	if count == 0:

		# Writing headers of CSV file
		header = emp.keys()
		csv_writer.writerow(header)
		count += 1

	# Writing data of CSV file
	csv_writer.writerow(emp.values())

data_file.close()

In the above code, we basically import json and csv libraries to load json and write to csv file respectively. We will use json.load() function to load the JSON file data.json for reading. Next, we will open data_file.csv file for writing using csv.writer() function. When JSON file is loaded into python object it can be treated as a python dictionary object. So we use its ’emp_details’ key to get the JSON array of objects.

employee_data = data['emp_details']

Now we simply loop through the array of JSON objects in employee_data. We keep a counter count and initiate it to zero. We use emp variable to point to each array object in our for loop.

For 1st JSON object, we use .keys() function to retrieve the list of key values in a comma separated manner, and use it to write the CSV header (emp_name,email,job). We call writerow() function to write this to CSV file. For each row including 1st row, we call .values() function to retrieve the values of JSON object in a comma-separated manner. We call writerow() function to write the information to CSV file.

Finally, we close the CSV file.

When you run the above code, you will get the following output in CSV file.

emp_name,email,job
John,john.doe@gmail.com,developer
Jane,jane.doe@gmail.com,analyst
    

However, if your JSON file looks like the following.

[{
    "emp_name":"John",
    "email":"john.doe@gmail.com",
    "job":"developer"
},
{
    "emp_name":"Jane",
    "email":"jane.doe@gmail.com",
    "job":"analyst"
}]

Then you can just omit the following line from your code.

employee_data = data['emp_details']

In this case, the python object created as a result of json.load() function is a python array, that you can directly loop through.

In this article, we have learnt how to convert JSON data into CSV format and write it to CSV file. You can easily add this code to your python application or website as per your requirement.

Converting JSON to CSV is a common requirement in applications since most of application or websites need to offer ‘export as csv’ format whereas their stored data is retrieved as JSON. You can customize the above scripts as per your requirement. If your JSON structure is more complicated with nested structures, you will need to decide first how to show it in CSV and then modify the code of your for loop to accordingly write information to CSV file.

Also read:

How to Find All Sudo Users in Linux
How to Check if Program Exists in Linux
How to Insert Text to Beginning of File
How to Get My Public SSH Key
How to Revoke SSH Access & Keys

Leave a Reply

Your email address will not be published. Required fields are marked *