How To Parse and Convert JSON to CSV using Python

by Shahid Ayoub

JSON is an acronym standing for JavaScript Object NotationThe json library in python can parse JSON from strings or files. The library parses JSON into a Python dictionary or list. We come across various circumstances where we receive data in json format and we need to send or store it in csv format. Since the csv files can easily be opened using LibreOffice Calc in ubuntu or Microsoft Excel in windows the need for json to csv conversion usually increases.

Parsing JSON

Let us take an example of  JSON in string form as shown below :

{

json_data = ‘{“name”: “smith”, “email”: “smithjack@gmail.com”}’

The json_data will be parsed like this in python:

import json

json_parsed = json.loads(json_data)

Now json.loads() function parses the json string data and it can be used as a normal dictionary in python. And we can access the values using keys. For example, here we can access email Id using key ‘email’ as shown below:

print json_parsed[‘email’]

smithjack@gmail.com

This is a simple example of parsing JSON, but we can have JSON file where data is much more complex . For example JSON data where values are lists and are accessed using list index as shown below :

Data = '{"employee_name": "James", "email": "james@gmail.com", "job_profile": ["Team Lead", "Sr. Developer"]}'

It can be parsed like this :

Parsed_data = json.loads(Data)

print Parsed_data[‘job_profile’][0]

"Team Lead"

The complexity increases further for JSON data where values are lists having dictionaries within. Again values will be accessed using list index and key value pair as shown below:

 

Data = '{"employee_name": "James", "email": "james@gmail.com", "job_profile": [{"title1":"Team Lead", "title2":"Sr. Developer"}]}'

It can be parsed like this:

Parsed_data = json.loads(Data)

print Parsed_data[‘job_profile’][0][‘title1’]

"Team Lead"

Similarly other fields can be accessed from the parsed data.

Converting JSON to CSV using Python:

CSV (Comma Separated Values) format is the most common import and export format for spreadsheets and databases. CSV file format separates values using commas as delimiters . For simple JSON data, keys will be headers for the CSV file and values the descriptive data. For example we have thousands of records about employees working in an organisation as shown below:

employee_data = '{"employee_details":[{"employee_name": "James", "email": "james@gmail.com", "job_profile": "Sr. Developer"},{"employee_name": "Smith", "email": "Smith@gmail.com", "job_profile": "Project Lead"},.....]}'

This can be parsed and converted to CSV using python as shown below:

import json

import csv

employee_parsed = json.loads(employee_data)

emp_data = employee_parsed['employee_details']

# open a file for writing

employ_data = open('/tmp/EmployData.csv', 'w')

# create the csv writer object

csvwriter = csv.writer(employ_data)

count = 0

for emp in emp_data:

      if count == 0:

             header = emp.keys()

             csvwriter.writerow(header)

             count += 1

      csvwriter.writerow(emp.values())

employ_data.close()


The CSV file will look like this for few records  :

employee_name email job_profile
James james@gmail.com Sr. Developer
Smith Smith@gmail.com Project Lead

 

The python program written above will open a csv file in tmp folder and write the content of JSON file into it and close it at the end. Make sure to close the file at the end in order to save the contents.

JSON files can have much more complex structures than CSV files, so a direct conversion is not always possible. The above mentioned examples will lay the foundation for converting JSON data with high complexity to CSV. 

2 thoughts on “How To Parse and Convert JSON to CSV using Python”

Leave a Reply to Anil Cancel reply

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

Tools & Practices

Tools and Technologies we use at Applied

Contact us now

Popular Posts