{"id":1509,"date":"2013-07-29T11:40:21","date_gmt":"2013-07-29T11:40:21","guid":{"rendered":"http:\/\/garysieling.com\/blog\/?p=1509"},"modified":"2013-07-29T11:40:21","modified_gmt":"2013-07-29T11:40:21","slug":"converting-json-to-a-csv-file-with-python","status":"publish","type":"post","link":"https:\/\/www.garysieling.com\/blog\/converting-json-to-a-csv-file-with-python\/","title":{"rendered":"Converting JSON to a CSV file with Python"},"content":{"rendered":"

In a previous post<\/a>, I showed how to extract data from the Google Maps API, which leaves a series of JSON files, like this:<\/p>\n

\n{\"address_components\":\n[{\"long_name\":\"576\",\"short_name\":\"576\",\"types\":[\"street_number\"]},\n{\"long_name\":\"Concord Road\",\"short_name\":\"Concord Road\",\"types\":[\"route\"]},{\"long_name\":\"Glen Mills\",\"short_name\":\"Glen Mills\",\"types\":[\"locality\",\"political\"]},{\"long_name\":\"PA\",\"short_name\":\"PA\",\"types\":...\n<\/pre>\n
Ideally we want selections from these as a CSV for manual review, and import into mapping software. First, we load a list of files:<\/p>\n
\nimport os\n\nfolder = 'G:\\\\maps\\\\churches\\\\db.tar\\\\db\\\\db'\n\nfiles = []\nfor root, dirs, f in os.walk(folder):\n    files = files + f\n<\/pre>\n
Then, we define a function which reads the contents of each file, and parses it as JSON, skipping non-JSON files:<\/p>\n
\nimport json\n\ndef js(fn):\n  try:\n    with open(fn, 'r') as f:\n      contents = f.read()\n      f.close()\n      return json.loads(contents)\n  except:\n    return ''\n\ntext = [js(f) for f in files]\n<\/pre>\n
We can then easily retrieve rows as tuples – since JSON is typically a hierarchical format, you may need to manually filter these in some way.<\/p>\n
\ncsv_rows = \\\n  [(j['name'], \\\n   get(j, 'website'), \\\n   j['geometry']['location']['kb'], \\\n   j['geometry']['location']['jb'], \\\n   j['formatted_address']) for j in text]\n<\/pre>\n
Finally, we use a simple CSV API provided by Python to write the file. Typically values in CSV files are quoted where necessary and separated by commas, although this API lets you control that:<\/p>\n
\nwith open('maps.csv', 'wb') as csvfile:\n mapswriter = csv.writer(csvfile, delimiter=',',\n                         quotechar='\"', quoting=csv.QUOTE_MINIMAL)\n  for r in csv_rows:\n    mapswriter.writerow(r)\n<\/pre>\n
One of the great things about Python as a scripting language for data analysis is the host of APIs available to make this operation simple – as you can see this is quite easy, as it doesn’t require downloading any additional add-on libraries.<\/p>\n","protected":false},"excerpt":{"rendered":"
In a previous post, I showed how to extract data from the Google Maps API, which leaves a series of JSON files, like this: {“address_components”: [{“long_name”:”576″,”short_name”:”576″,”types”:[“street_number”]}, {“long_name”:”Concord Road”,”short_name”:”Concord Road”,”types”:[“route”]},{“long_name”:”Glen Mills”,”short_name”:”Glen Mills”,”types”:[“locality”,”political”]},{“long_name”:”PA”,”short_name”:”PA”,”types”:… Ideally we want selections from these as a CSV for manual review, and import into mapping software. First, we load a list of files: … <\/p>\n
Continue reading “Converting JSON to a CSV file with Python”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[4],"tags":[255,447,495],"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts\/1509"}],"collection":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/comments?post=1509"}],"version-history":[{"count":0,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts\/1509\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/media?parent=1509"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/categories?post=1509"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/tags?post=1509"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}