A pair of python scripts to create a flat json file from a given csv file that can be used in a handlebars.js template.

README.md

Snippets: Build a flat json file for handlebars.js template or jquery-vertical-timelines

UPDATED I've made some updates to the handlebars-json.py script.

• Script now accepts a "usage" argument for "handlebars" or "timeline" or "array"
• Script assumes a header row and strips underscores and spaces and converts header fields to lowercase when creating keys.
• Because the resulting JSON file can also be used to power Vertical Timelines, I've re-named the script to csv-to-json.py.
• The handlebars-json-csvkit.py script remains unchanged. I will attempt to re-work it to create handlebars or timeline JSON in the coming weeks.

See more in the Usage section below.

---

The csv-to-json.py script creates a flat json file from a given csv file that can be used in either a handlebars.js template or a vertical timeline.

The handlebars-json-csvkit.py script creates a flat json file from a given csv file that can be used in a handlebars.js template and assumes the use of Christopher Groskopf's csvkit.

I had Andy Boyle's python script from a year ago that creates json from a csv. And I noticed in the comments section on Andy's post that Christopher Groskopf's csvkit has a csvjson utility that can also convert a csv to a json file.

After flirting with the idea of adding a custom argument to the csvjson utility to allow me to specify a key, I simply adjusted Andy's script to make handlebars-json.py csv-to-json.py. Meanwhile handlebars-json-csvkit.py requires Christopher Groskopf's csvkit to be installed, and uses its csvjson utility

Usage

• The csv-to-json.py script accepts two command line arguments -- the name of the csv file and the usage. Right now the usage is either handlebars or timeline.

• The csv-to-json.py script assumes the csv file has a header row to use when creating json keys.

• The script also strips out underscores and spaces, and converts header fields to lowercase when creating keys.

Handlebars

To generate a flat json file to use with Handlebars.js, change into the directory containing this script and the target csv file and run the following from the command line where working-handlebars.csv is the name of your csv file.

	python csv-to-json.py working-handlebars.csv handlebars

The resulting json file -- working-data-file-handlebars.json -- looks like this:

	{"objects": [{"sourcelink": "http://www.nelp.org/", "dataorder": "1", "data": "12.29.2012", "source": "National Employment Law Project", "title": "The last day anyone will receive benefits from the Emergency Unemployment Compensation program unless Congress acts to renew it."}, {"sourcelink": "", "dataorder": "2", "data": "$30,000,000,000", "source": "Congressional Budget Office", "title": "Estimated cost to renew the Emergency Unemployment Compensation program through the end of 2013."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "3", "data": "400,000", "source": "National Employment Law Project", "title": "Estimated number of Californians receiving benefits from the Emergency Unemployment Compensation program, which is set to expire Jan. 2."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "4", "data": "2,100,000", "source": "National Employment Law Project", "title": "Estimated number of Americans receiving benefits under the Emergency Unemployment Compensation program that would lose their unemployment benefits come January if Congress doesn\u2019t act."}]}

The path to working-data-file-handlebars.json can then be dropped into a handlebars.js function and displayed on a template.

Timeline

The option to create JSON to power a vertical timeline assumes a couple things.

• That you are using the vertical timeline spreadsheet template to store your data.

• That you're using the jQuery vertical timeline plugin created by MinnPost and Alan Palazzolo that allows for a javascript array of objects to substitued for getting data from a Google Spreadsheet.

To generate a flat json file to use with the jQuery vertical timeline plugin, change into the directory containing this script and the target csv file and run the following from the command line where working-timeline.csv is the name of the csv file you downloaded from the vertical timeline spreadsheet template.

	python csv-to-json.py working-timeline.csv timeline

The resulting json file -- working-data-file-timeline.json -- looks like this:

    [{"body": " Dorner born in New York", "photourl": "", "readmoreurl": "", "title": "Dorner born", "caption": "", "displaydate": "June 4", "date": "June 4, 1979"}, {"body": "Christopher Dorner graduates from Southern Utah University in Cedar City, Utah and earns a Bachelor of Science in Political Science. (Facebook Photos)", "photourl": "http://a.scpr.org/i/f04b9fcd282822a9b38aa371f1a37c66/54656-wide.jpg", "readmoreurl": "", "title": "Graduation day from Southern Utah", "caption": "Photo: Facebook", "displaydate": "May 5", "date": "May 5, 2001"}, {"body": "Dorner achieves the rank of Ensign in the Navy. (Facebook Photos)", "photourl": "", "readmoreurl": "", "title": "Dorner receives Naval promotion", "caption": "", "displaydate": "Juy 3", "date": "July 3, 2002"}]

The path to working-data-file-timeline.json can then be dropped into the vertical timeline configuration function.

    <script type="text/javascript">
        $(document).ready(function() {
            $.getJSON('PATH-TO/working-data-file-timeline.json', function(data) {
                $('#timeline-display').verticalTimeline({
                    data: data,
                    width: '75%'
                });
            });
        });
    </script>

For more on the jQuery vertical timeline plugin created by MinnPost and Alan Palazzolo read the docs here.

Snags

Now, admittedly csv-to-json.py works because most of my handlebars work thus far has used simple data structures -- largely an object that contains an array of objects -- but my workflow might fall apart quickly should I need to access tiered data. (UPDATE: This has already happened!):

    {"objects": [{
        award_agency: "New York Film Critics",
            source_link: "http://www.nyfcc.com/awards/",
            awards: [
                {award: "Best Actor", winner: "Daniel Day-Lewis", movie: "(Lincoln)"},
                {award: "Best Actress", winner: "Rachel Weisz", movie: "(Deep Blue Sea)"},
                {award: "Best Director", winner: "Kathryn Bigelow", movie: "(Zero Dark Thirty)"},
                {award: "Best Film", winner: "Kathryn Bigelow", movie: "(Zero Dark Thirty)"}
            ]
        }]
    }

One thing I have noticed through this experiment is tabletop.js strips uppercase letters and underscores from object keys, while the csv to json version doesn't. So that's a thing as it's the difference between a handlebars.js template that uses…

{{projecttitle}}

and

{{Project_Title}}

I also took the step of normalizing the JSON output in that spaces, underscores and uppercase letters are removed from the header row. This is something that I've encountered in creating tabletop.js-driven presentations with handlebars.js -- and happens when timeline presentations -- so I felt the want to standardize things.

Finally, I've noticed csv-to-json.py renders everything as a string, which has caused some issues when using something like highcharts, which requires is data objects to be integers. This needs a fix and I'll see that I can do.

Links & Resources

• Some thoughts after a couple months with tabletop and handlebars
• Repo
• Display data from a flat JSON file on a Handlebars.js template file rendered with AJAX
• Repo

blog_post.md

Some thoughts after a couple months with tabletop and handlebars

tl;dr - Learning how I can use tabletop.js & handlebars.js together has opened up some really cool custom mini-CMS possibilities and ways to manage data projects.

But thanks to the experiments of others, I have learned that flat json files can be better to use in production, especially for small one-off projects.

While I'd like to eventually write a script to take the tabletop object and write it to a flat file, I made some adjustments to some python code so I can take a csv, create a flat json file from it and drop that file into a handlebars function with minimal effort.

While it works for me, I'm curious to know if it works for others…

• Blog Post
• Repo

---

I'm happy to say that I still have access to some gray days since moving to Southern California, and on a recent one I set about streamlining some project structures, code snippets & templates for two code libraries that I've been using more and more since coming to Southern California Public Radio: tabletop.js and handlebars.js.

I'm only now getting back to the write up and posting code.

Overview

tabletop.js allows you to use a Google spreadsheet as a data backend. By simply publishing the spreadsheet and adding its key to a tabletop javascript function you can retrieve an an array of objects to be used on a webpage or in a web application. The library was released back in February -- right around the time of NICAR -- thanks to work by WNYC and Balance Media.

handlebars.js is a templating library -- much like mustache.js -- that "provides the power necessary to let you build semantic templates" based on data that is formatted as -- get this -- javascript objects. Using an example from the handlebar.js website, the library allows you to do things like this...

<div class="entry">
	<h1>{{title}}</h1>
	<div class="body">
		{{body}}
	</div>
</div>

… where {{title}} and {{body}} represents information stored in an object like this:

var context = {title: "My New Post", body: "This is my first post!"}

Practical Applications

Learning how these libraries can work with each other has been a lot of fun, especially in the context of a newly-created position at a new-to-me news organization where I have had a role to play in building a foundation for the presentation of data projects.

For instance, using handlebars.js ability to load precompiled handlebars templates from file we've been able to construct a basic project wrapper that can live outside CMS, minimizes the amount of code that needs to be changed at any given time and allows us to manage a single header and one footer file. DRY FTW.

I've also been able to use the combination of these libraries to come to having a custom mini-CMS that can run some of these projects, which can then be updated by anyone that can use a spreadsheet.

For instance, one of my first collaborations was with our business and economy reporter Matt DeBord on this project for Explaining the Monthly Jobs Report.

This project combines tabletop.js with handlebars.js -- and some highcharts.js for good measure -- to show the differences between "Initial," "Revised," and Final monthly jobs numbers released by the U.S. Bureau of Labor Statistics.

While my "design" judgement and sensibilities continue to evolve, for my money, the magic is in the fact that Matt can update this presentation for our audience by opening a Google spreadsheet and adding the new numbers. Lightweight, custom mini-CMS!

Tasneem Raja, the Interactive Editor at Mother Jones, has written a solid explainer on how the magazine has leveraged tabletop.js and a Google spreadhseet for collaborative projects such as their You Might Be the 47 Percent If… scoop.

Some of the projects I've worked on over the past couple months that use tabletop.js and handlebars.js include a presentation on the purchasing of Capital Appreciation Bonds by local school districts, a "By The Numbers" look at the impact of the so-called fiscal cliff on Emergency Unemployment Compensation benefits and a comparison of California and federal gun laws

But a post on NewsBeast Labs I found while writing this pointing to a post from Jeremy Singer-Vine -- appropriately titled "Why I love Tabletop.js but don't use it in production" -- and some words from Mark Boas on an Open News call a couple weeks ago reminded me of potential obstacles and issues with relying too much on tabletop.js for projects.

And a tweet this week a few weeks back from Aaron Williams, and one today from Matt Waite, reminded me I have to finish this post you are reading.

All of the issues that Jeremy listed are real, and Aaron's experience is real. Thinking about the projects I listed above, none of them really require the kind of dynamic data display that tabletop.js can pull off. It's a nice feature to have available on the monthly jobs report, but it isn't crucial, and certainly isn't required on the others.

A flat file very much would work just fine.

And so I went about finding a way to create a flat json file from a Google spreadsheet that I could use in a handlebars.js template.

My Workaround

I already had Andy Boyle's python script from a year ago that creates json from a csv. And I noticed in the comments section on Andy's post that Christopher Groskopf's csvkit has a csvjson utility that can also convert a csv to a json file.

After flirting with the idea of adding a custom argument to the csvjson utility to allow me to specify a key, I adjusted Andy's script to create a file that looks like this…

{"objects": [{"Source": "National Employment Law Project", "DataOrder": "1", "SourceLink": "http://www.nelp.org/", "Data": "12.29.2012", "Title": "The last day anyone will receive benefits from the Emergency Unemployment Compensation program unless Congress acts to renew it."}, {"Source": "Congressional Budget Office", "DataOrder": "2", "SourceLink": "", "Data": "$30,000,000,000", "Title": "Estimated cost to renew the Emergency Unemployment Compensation program through the end of 2013."}, {"Source": "National Employment Law Project", "DataOrder": "3", "SourceLink": "http://www.nelp.org/", "Data": "400,000", "Title": "Estimated number of Californians receiving benefits from the Emergency Unemployment Compensation program, which is set to expire Jan. 2."}, {"Source": "National Employment Law Project", "DataOrder": "4", "SourceLink": "http://www.nelp.org/", "Data": "2,100,000", "Title": "Estimated number of Americans receiving benefits under the Emergency Unemployment Compensation program that would lose their unemployment benefits come January if Congress doesn\u2019t act."}]

… which is something that allows me to plug my new file name into a variable in this script and fly.

Snags

Now, admittedly it works because most of my handlebars work thus far has used simple data structures -- largely an object that contains an array of objects -- but my workflow might fall apart quickly should I need to access multiple data streams, etc. (UPDATE: This has already happened!)

One thing I have noticed through this experiment is tabletop.js strips uppercase letters and underscores from object keys, while the csv to json version doesn't. So that's a thing as it's the difference between a handlebars.js template that uses…

//what tabletop brings
{{projecttitle}}

and

// created from a csv to json method
{{Project_Title}}

Another thing I've noticed is the python script that converts the csv to json renders everything as a string, which has caused some issues when using something like highcharts, which requires is data objects to be integers. So I continue to explore a way to fix this.

csv-to-json.py

#!/usr/local/bin/python

import os
import sys
import argparse
import re
import types
import csv
import json
import logging

logging.basicConfig(format='\033[1;36m%(levelname)s:\033[0;37m %(message)s', level=logging.DEBUG)

parser = argparse.ArgumentParser()
parser.add_argument('csv_filename', help='Name of the csv file you want to convert to json')
parser.add_argument('usage', help='Add \'timeline\' if you want json for a vertical timeline, or \'handlebars\' if you want json for a handlebars template')
args = parser.parse_args()

def processCsvForConversion(filename, output):
    print "parsing %s \n" % (filename)
    file = open(filename, 'rU')
    fieldnames = []
    dict_keys = file.readline()
    split_keys = dict_keys.rstrip('\r\n').split(',')
    for key in split_keys:
        if ' ' in key:
            edited_key = key.replace(" ", "").lower()
            fieldnames.append(edited_key)
        elif '_' in key:
            edited_key = key.replace("_", "").lower()
            fieldnames.append(edited_key)
        else:
            edited_key = key.lower()
            fieldnames.append(edited_key)
    fieldnames = tuple(fieldnames)
    csvInstance = csv.DictReader(file, fieldnames = fieldnames)
    process(csvInstance, filename)
    file.close()


def process(csvInstance, filename):
    jsonOutput = json.dumps([row for row in csvInstance])




    if args.usage == 'timeline':
        print "creating json for timeline"
        data_to_write = '%s' % (jsonOutput)
        json_flat_file = os.path.splitext(filename)[0] + '_timeline.json'

    elif args.usage == 'array':
        print "creating array of objects"
        data_to_write = '%s' % (jsonOutput)
        json_flat_file = os.path.splitext(filename)[0] + '_array.json'


    else:
        print "creating json for handlebars template"
        data_to_write = '{"objects": %s}' % jsonOutput
        json_flat_file = os.path.splitext(filename)[0] + '_handlebars.json'




    new_file = open(json_flat_file, 'w')
    new_file.write(data_to_write)
    new_file.close()
    print '%s converted to %s \n' % (filename, json_flat_file)
    return

if __name__ == '__main__':
    processCsvForConversion(args.csv_filename, args.usage)

handlebars-json-csvkit.py

import os, sys, argparse
from subprocess import Popen, PIPE, STDOUT

parser = argparse.ArgumentParser()
parser.add_argument("csv_filename", help="Name of the csv file you want to convert to json")
args = parser.parse_args()

def convertToJson(filename):

    # take argument run command in the shell, capturing output
    cmd = 'csvjson %s' % filename
    p = Popen(cmd, shell=True, stdin=PIPE, stdout=PIPE, stderr=STDOUT, close_fds=True)

    print "parsing csv"

    # set command line output to variable
    jsonOutput = p.stdout.read()

    # wrap the json
    writeTestData = '{"objects": %s}' % jsonOutput

    # get file name by stripping csv extension and rename as json
    jsonDataFile = os.path.splitext(filename)[0] + '.json'

    # open output file
    f = open(jsonDataFile, 'w')

    # write to the output file
    f.write(writeTestData)

    # close the output file
    f.close()

    print "csv converted to json"

    return

convertToJson(args.csv_filename)

working-data-file-handlebars.json

{"objects": [{"sourcelink": "http://www.nelp.org/", "dataorder": "1", "data": "12.29.2012", "source": "National Employment Law Project", "title": "The last day anyone will receive benefits from the Emergency Unemployment Compensation program unless Congress acts to renew it."}, {"sourcelink": "", "dataorder": "2", "data": "$30,000,000,000", "source": "Congressional Budget Office", "title": "Estimated cost to renew the Emergency Unemployment Compensation program through the end of 2013."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "3", "data": "400,000", "source": "National Employment Law Project", "title": "Estimated number of Californians receiving benefits from the Emergency Unemployment Compensation program, which is set to expire Jan. 2."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "4", "data": "2,100,000", "source": "National Employment Law Project", "title": "Estimated number of Americans receiving benefits under the Emergency Unemployment Compensation program that would lose their unemployment benefits come January if Congress doesn\u2019t act."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "5", "data": "940,000", "source": "National Employment Law Project", "title": "Estimated number of Americans whose state unemployment benefits will end in the first quarter of 2013, and would be eligible for benefits under the Emergency Unemployment Compensation program."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "6", "data": "February 2012", "source": "National Employment Law Project", "title": "The date when the Emergency Unemployment Compensation program was last renewed by Congress."}, {"sourcelink": "http://www.ows.doleta.gov/unemploy/supp_act.asp", "dataorder": "7", "data": "June 30, 2008", "source": "U.S. Department of Labor", "title": "The date the Emergency Unemployment Compensation program was created."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "8", "data": "10", "source": "National Employment Law Project", "title": "The number of times Congress has renewed the Emergency Unemployment Compensation program since it was first created in the summer of 2008."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "9", "data": "37 percent", "source": "National Employment Law Project", "title": "Estimated percent of Californians that have been unemployed more than a year since 2008, when the Emergency Unemployment Compensation program was created to help the long-term unemployed."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "10", "data": "5,000,000", "source": "National Employment Law Project", "title": "Estimated number of Americans that have been without work for six months or longer"}, {"sourcelink": "http://workforcesecurity.doleta.gov/unemploy/docs/potential_weeks_map.pdf", "dataorder": "11", "data": "26 weeks", "source": "U.S. Department of Labor", "title": "Cap on unemployment benefits by the least generous states."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "12", "data": "14 to 47 weeks", "source": "National Employment Law Project", "title": "The range of maximum benefits states offer, subsidized by the Emergency Unemployment Compensation program."}, {"sourcelink": "http://workforcesecurity.doleta.gov/unemploy/docs/potential_weeks_map.pdf", "dataorder": "13", "data": "73 weeks", "source": "U.S. Department of Labor", "title": "Maximum length of benefits for new claimants in California under the Emergency Unemployment Compensation program."}, {"sourcelink": "http://www.cbpp.org/cms/index.cfm?fa=view&id=3164", "dataorder": "14", "data": "Nine states", "source": "Center on Budget & Policy Priorities", "title": "Number of states with unemployment rates above 9 percent in which workers are eligible for up to 47 weeks of additional benefits under the Emergency Unemployment Compensation program."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "15", "data": "$40,000,000,000", "source": "U.S. Department of Labor", "title": "Estimated amount of money brought into the state of California by those receiving benefits from the Emergency Unemployment Compensation program since it began in July 2008."}]}

working-data-file-timeline.json

[{"sourcelink": "http://www.nelp.org/", "dataorder": "1", "data": "12.29.2012", "source": "National Employment Law Project", "title": "The last day anyone will receive benefits from the Emergency Unemployment Compensation program unless Congress acts to renew it."}, {"sourcelink": "", "dataorder": "2", "data": "$30,000,000,000", "source": "Congressional Budget Office", "title": "Estimated cost to renew the Emergency Unemployment Compensation program through the end of 2013."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "3", "data": "400,000", "source": "National Employment Law Project", "title": "Estimated number of Californians receiving benefits from the Emergency Unemployment Compensation program, which is set to expire Jan. 2."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "4", "data": "2,100,000", "source": "National Employment Law Project", "title": "Estimated number of Americans receiving benefits under the Emergency Unemployment Compensation program that would lose their unemployment benefits come January if Congress doesn\u2019t act."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "5", "data": "940,000", "source": "National Employment Law Project", "title": "Estimated number of Americans whose state unemployment benefits will end in the first quarter of 2013, and would be eligible for benefits under the Emergency Unemployment Compensation program."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "6", "data": "February 2012", "source": "National Employment Law Project", "title": "The date when the Emergency Unemployment Compensation program was last renewed by Congress."}, {"sourcelink": "http://www.ows.doleta.gov/unemploy/supp_act.asp", "dataorder": "7", "data": "June 30, 2008", "source": "U.S. Department of Labor", "title": "The date the Emergency Unemployment Compensation program was created."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "8", "data": "10", "source": "National Employment Law Project", "title": "The number of times Congress has renewed the Emergency Unemployment Compensation program since it was first created in the summer of 2008."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "9", "data": "37 percent", "source": "National Employment Law Project", "title": "Estimated percent of Californians that have been unemployed more than a year since 2008, when the Emergency Unemployment Compensation program was created to help the long-term unemployed."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "10", "data": "5,000,000", "source": "National Employment Law Project", "title": "Estimated number of Americans that have been without work for six months or longer"}, {"sourcelink": "http://workforcesecurity.doleta.gov/unemploy/docs/potential_weeks_map.pdf", "dataorder": "11", "data": "26 weeks", "source": "U.S. Department of Labor", "title": "Cap on unemployment benefits by the least generous states."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "12", "data": "14 to 47 weeks", "source": "National Employment Law Project", "title": "The range of maximum benefits states offer, subsidized by the Emergency Unemployment Compensation program."}, {"sourcelink": "http://workforcesecurity.doleta.gov/unemploy/docs/potential_weeks_map.pdf", "dataorder": "13", "data": "73 weeks", "source": "U.S. Department of Labor", "title": "Maximum length of benefits for new claimants in California under the Emergency Unemployment Compensation program."}, {"sourcelink": "http://www.cbpp.org/cms/index.cfm?fa=view&id=3164", "dataorder": "14", "data": "Nine states", "source": "Center on Budget & Policy Priorities", "title": "Number of states with unemployment rates above 9 percent in which workers are eligible for up to 47 weeks of additional benefits under the Emergency Unemployment Compensation program."}, {"sourcelink": "http://www.nelp.org/", "dataorder": "15", "data": "$40,000,000,000", "source": "U.S. Department of Labor", "title": "Estimated amount of money brought into the state of California by those receiving benefits from the Emergency Unemployment Compensation program since it began in July 2008."}]

working-handlebars.csv

working-timeline.csv