JSON is an acronym standing for JavaScript Object Notation. The 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’]
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 | 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.
Should
csvwriter = csv.writer(employdata)
actually be
csvwriter = csv.writer(employ_data)
Thanks for pointing out the typo error.
getting following error.
csvwriter.writerow(header)
_csv.Error: sequence expected
It seems you are using the employee JSON data as it is. You have to strip dots(…) at the end it is an example or you can add more instances there.
This worked for me! Thanks Shahid!
There seems an encoding issue, use proper quotes for the the key and string values. I was able to parse the data after replacing the existing quotes with normal double quotes or use proper encoding.
Hi,
I have a variable called user_info where profile data of users in stored. The output is supposed to in json.
When I run the program (it’s a bot), I’d like to generate a json file to store all the data as json, and later export the data as CSV. I’ve been looking for a solution for hours now and I’ve found nothing. I’m new to Python. Do you know how to do that?
Thanks
Which one is easier to parse and convert into CSV: JSON or XML?
I can get my data in either of them and would like to make a decision which one to proceed with.
If you have option go for JSON as it is data oriented and can be mapped more easily. For XML parsing and conversion to CSV you can have a look on my blog:
http://blog.appliedinformaticsinc.com/how-to-parse-and-convert-xml-to-csv-using-python/
Hello Shahid pretty well your post, actually that helps me a lot.
You can check my JSON example here https://secure.bixi.com/data/stations.json following your approach I have already created the CSV file. however and that could be a very stupid question I have tried to do two things:
1. just get a couple of values from ‘stations’ (id,n,da,dx,ba,bx)
1.1 rename the output with a different name header
2. I don’t know why my CSV file has an enter after each row 🙁
Probably you are busy with another stuff but my friend I would really appreciate if you can guide me
thanks so much
Andres
Buddy i played a little bit and i found that:
Script
***************************
import json
import csv
#read the json file
with open(‘stations.json’,’r’) as bixi_stations:
data = json.load(bixi_stations)
data_csv = data[‘stations’]
file_data= open( ‘C:/Users/axu30/PycharmProjects/First/stations.csv’,’w’)
csvwriter = csv.writer(file_data)
liste = {}
i =0
for count in data_csv:
if i == 0:
header = ‘Id’,’Station Name’,’Station Id’,’Empty places’,’Qty Avail Bikes’
csvwriter.writerow(header)
i += 1
liste[‘id’]=count[‘id’]
liste[‘s’] = count[‘s’]
liste[‘n’] = count[‘n’]
liste[‘da’] = count[‘da’]
liste[‘ba’] = count[‘ba’]
csvwriter.writerow(liste.values())
file_data.close()
hi I want to convert one json file to csv , below is file
{
“data”:{
“ABC”:[
109.4,
167.18,
196.88,
219.2,
240.8,
274.64,
329
],
“XYZ”:[
“D86”
]
}
}
i am trying all possible way but could not get it. please help me.
Hi ,
Any idea how to parse JASON file with multiple loops?
Hi, I want to convert my geojson into csv. Can you help me with that?
Hi,
First, thank you for this. Its really helpful. I’m having an issue where one row in my data will occasionally be missing a column and columns past that one are now shifted one to the left. Any thoughts on how to fix that?
thank you very much it helped me a lot
Wayy cool! Some extremely alid points!I appreciate you writing this post and also the rest
of the website is extremely good.
great help! Worked for me !