Zaibi Zaibi - 1 year ago 66
JSON Question

Converting a json to csv. (A bit tedious one)

So I have tried this script which kinda makes my way more hard, I want to write Funding details more simplified, like one single row for each year(or any other good way to present it)

import csv

import json
json_data = [{"Founded": "2004", "Company": "","Funding": "$201.2M", "Funding Details": [[" 2015", "$60.65M "], [" 2014", "$100M "], [" 2013", "$36.55M "], [" 2012", "$4M "]]}]

f = csv.writer(open("CRUNCHBASEDATA.csv", "wb+"))

f.writerow(['Founded','Company','Funding','Funding Details'])

for obj in indata:
f.writerow([obj['Founded'],obj['Company'],obj['Funding'],obj['Funding Details']])

This give me an excel column for Funding details like

[[u' 2015', u'$60.65M '], [u' 2014', u'$100M '], [u' 2013', u'$36.55M '], [u' 2012', u'$4M ']]

This format is hard to understand, can anyone please suggest a better way to represent this piece of information?

Answer Source

Untested, but rearrange your data. Something like

funding_details = json_data[0]["Funding Details"]
f.writerow( ... 'Funding', 'Funding_2014', 'Funding_2013', ... )
f.writerow( ... obj['Funding'], funding_details[0][1], funding_details[1][1], ... )

If the funding years are not always 2014, 2013, 2012 in that order you may need to build a dict with entries for the years of interest set to blank, then overwrite the ones that are present:

funding = { u' 2014':'', u' 2013':'', u' 2012':'', u' 2011':'' }

for elem in funding_details:
   funding[ elem[0] ] = elem[1]

and then the data you pass to writerow will include funding[ u' 2014'] etc. which will still be blank if it did not exist in the JSON funding_details list.

You might also want to investigate csv.dictwriter

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download