JSON API -> CSV

README.md

Howto download a JSON API array and spit out a CSV

Tools needed

• httpie -- easy_install httpie (probably you want to sudo easy_install httpie
• this script -- curl -o /usr/local/bin/json-to-csv.py https://raw.github.com/gist/4119347/24adb5dcd9c025c272042653a70d25b73b485958/json-to-csv.py
• make that executable -- chmod +x /usr/local/bin/json-to-csv.py

Here we go

This example calls the Altmetric API, asking for the 2 most cited articles in blogs and news and prints a CSV on standard output containing the altmetric_id and score of each article.

% http http://api.altmetric.com/v1/citations/1d\?num_results\=2\&cited_in\=blogs,news | python json-to-csv.py -o altmetric_id,score -r results
altmetric_id,score
1077374,124.2
1066154,971.274

Save it to a file:

% http http://api.altmetric.com/v1/citations/1d\?num_results\=2\&cited_in\=blogs,news | python json-to-csv.py -o altmetric_id,score -r results > top-cited-news-blogs.csv

json-to-csv.py

#!/usr/bin/env python

import sys
import json
from optparse import OptionParser

if __name__ == '__main__':
    parser = OptionParser()
    parser.add_option("-o", "--output", action="store",
                      help="Format to output, e.g. -o 'id,name,age,some_other_field'")
    parser.add_option("-r", "--root", action="store",
                      help="Root JSON object to refer to fields from, e.g. -r 'results'")
    (options, args) = parser.parse_args()

    jsonstr = sys.stdin.read()
    data = json.loads(jsonstr)

    # print options
    root_obj = data[options.root]
    # print root_obj

    headers = options.output.split(',')
    print options.output
    rows = [map(lambda header: str(values[header]), headers) for values in root_obj]
    for row in rows:
        print ','.join(row)

thanks for this!