The Absurdly Illustrated Guide To Sortable, Searchable Online Data Tables

Visual approaches to data are great — they can allow us to grasp complex issues at a glance, just the way this map from Clear Health Costs shows us the dramatic differences between what different hospitals charge for the same procedure.

But sometimes a simple, sortable and searchable table of data is all that’s really needed.  Using  code written by Chris L. Keller, I was able to create this sortable, searchable table of law enforcement agencies in Middlesex County, Massachusetts, along with the populations they serve, and how many full-time officers per capita there are.

As always, click on any image in this blog to see it full size.  I leave helpful annotations in the illustrations to these tutorials — so if ya can’t see em, click ‘em!

middlesex-county-law-enforcement

 

One of the nice things about this way of presenting data is that even though it’s simple, it can be quite revealing.  Click here to go to the live data table and use the up/down arrows to sort by the center column, which displays how many full-time officers per 1,000 people served there are.

7 out of the top ten are colleges and universities.  Tiny Lasell College has 7.8 officers per 1,000 students — though with only 1,800 students, their high ratio may have more to do with a minimum number of officers needed to staff three shifts to make sure there are officers on duty 24 hours.  But scroll down to the bottom — two of the departments with the lowest ratio are also colleges.

Public colleges.  

Surprising?  Maybe not, but even so, it was a fact I did not immediately pick out when I downloaded the origincal data from  UCRStats.com.  And the differences are dramatic; state colleges have among the lowest number of full time officers per 1,000 people served of all of the departments listed.  So one of the things that higher tuition cost buys?  Bigger campus police forces.

Now I will teach you how to make your own sortable, searchable web data tables!

As with our Absurdly Illustrated Guide To Creating Your First Dynamic, Data-Driven Timeline,  today’s tutorial will use Tabletop.js.  Tabletop.js allows us to use Google Spreadsheets as the data source for fast, flexible web and mobile apps without having to go through the extra work of setting up a relational database like MySQL.  Even better, there are probably far more people on your team who feel confident about entering data into a Google Docs spreadsheet than going mano-a-mano with a database.

1.  Download the tabletop-to-datatables code from Github: Tabletop To Datatables.   Click on the ZIP file icon.

download-files

 

2. Unzip tabletop_to_datatables-master.zip.  You will see files and folders inside the zip file that look like this:

unzip-tabletop-file

3. Set up your data source.

First, log in to Google Docs and create a new spreadsheet.

Tile_Mill_4

Visit Law-Enforcement-MA and copy the data in this spreadsheet.

Select the data with Command-A (Ctrl-A on a Windows PC) then copy the data (Command C or Ctrl-C) into your new spreadsheet.

select-data

Change the name of your spreadsheet to LawEnforcementMA.

rename-spreadsheet

Rename the *worksheet*.  Spreadsheets can have multiple worksheets, represented by tabs at the bottom of the spreadsheet.  If you haven’t changed the name of your worksheet, the default name is Sheet1.  Click on that and change the name of your worksheet to LawEnforcementMA.

Rename-sheet

 

 

Make your spreadsheet’s data public so your script can access it.  In the upper right corner, click the Share button.

share-button

Change the privacy settings on your spreadsheet like this:

change-settings-2

 

Just changing the privacy settings on your spreadsheet is not enough.  You must also publish your spreadsheet to the web.  Click File >> Publish To Web.   When you do, you will see this dialog screen.  Click “Start Publishing.”

start-publishing

 

When you click “Start Publishing” Google Docs will give your spreadsheet a publicly accessible URL.  Copy this URL.

copy-public-url

4. Modify your script to grab the data you want from your spreadsheet.  

Open tabletop_to_datatables-master.  Inside, you will find a folder called “scripts.”  Open that folder and you will find tabletop-feed.js.

open-tabletop-feed

Open that file in a programmer’s text editor.  If you don’t have a programmer’s text editor,  visit the Journalist’s Learn-to-Code Resource Guide and scroll down to the section on text editors, where you will find free-to-download text editors for all platforms.  You will need one of these to edit your code!

In order to tell our script which spreadsheet to take data from, we need to give it the “key” from the public spreadsheet URL we saw in the last step.

 

Here is the public URL for my LawEnforcementMA spreadsheet:

https://docs.google.com/spreadsheet/pub?key=0ApLwuu1HQNO7dHJVY1dsdWZlT2lNRjZ4eXluY1BWdHc&output=html

And the key from that spreadsheet is the portion between “key=” and the ampersand (&):

0ApLwuu1HQNO7dHJVY1dsdWZlT2lNRjZ4eXluY1BWdHc

Take the key from your public spreadsheet URL and use it to replace the one on line six of tabletop-feed.js.

insert-key

 

Now that our script knows what spreadsheet to look at, we need to tell it what columns we want it to grab data from, and where we’re going to put it.  In the same file — tabletop-feed.js — look at lines 28-30.

Each one of these lines represents one of the three columns of the data table we’re building.  We won’t have to change much, but we will have to let our script know what columns in our spreadsheet we want to use, and what we want to call them in our finished web data table.

Line 28 reads like this:

{‘mDataProp’: ‘name’, ‘sTitle’: ‘Name’, ‘sClass’: ‘center’},

When we’re done, it will read like this:

{‘mDataProp’: ‘policedepartment’, ‘sTitle’: ‘Police Department’, ‘sClass’: ‘center’},

The first change, ‘policedepartment’, tells the script which column to look at.  One thing that confused me was that we need to tell it the name of the column in all lowercase — even  if the original column is not (in fact, it’s PoliceDepartment).

Next, we want our web data table’s column to have an appropriate name.  So after ‘sTitle’, enter ‘Police Department’ — with capitals and spaces, the way you’d want it to read on the page.

Now we’ll fill out and name our next two columns.  At the end, they should look like this:

column-data

 

Click “Save” to save your newly-modified script.

Now, go back to your tabletop_to_datatables-master folder and look for index.html.

open-index-html

 

Open index.html in a web browser (on my machine, just clicking on any file with an .html or .htm file extension automatically opens it in a browser).  Now you’ll see a web page with data from our spreadsheet!  Go ahead and play around with the arrow keys at the top of each column for sorting, or use the search function in the upper left.

There’s only one problem — that isn’t really the title we want, is it?

change-title-web-page

 

5. Change the title of your data table and add other information.  

Open index.html in your text editor.  On line 3o, you will find some text — ‘Demo: tabletop to datatables’ wrapped in H1 tags.  Change that to ‘Law Enforcement Agencies in Middlesex County’.

I always think it’s a good idea to let people know where our data came from, so let’s add a link to the original on the line below our title.

<p><a href =”http://www.ucrstats.com/>Source: UCRStats</a></p>

Now the source code in your index.html file should look like this (remember, you can click on any image to make it bigger):

title-source-after

 

Save your index.html file and go back to your browser and reload your page.  Now it will have the right title!

You can use this script to pull data from any Google spreadsheet you want, just by changing the spreadsheet key and changing the lines of code that tell the script which columns to pull data from.  So table that data!

6. Savor Your Epic Win.

obama-clap

If you enjoyed this Absurdly Illustrated Tutorial, you may also enjoy:

 

5 comments

  1. Pingback: The Absurdly Illustrated Guide To Sortable, Sea...
  2. Pingback: Data Viz News [6] | Visual Loop
  3. Patrick Hogan

    Love the guide, but is there a way to do this to display more than three columns, I tried adding an extra

    {‘mDataProp’: ‘name’, ‘sTitle’: ‘Name’, ‘sClass’: ‘center’},

    line for each additional column, but if I have more than three that just seems to keep the table from rendering.

    • Skip

      Make sure you have a comma at the end of each line, EXCEPT the last.

      var tableColumns = [
      {'mDataProp': 'class', 'sTitle': 'Class Name', 'sClass': 'left'},
      {'mDataProp': 'day', 'sTitle': 'Day', 'sType': 'weekdays-sort', 'sClass': 'left'},
      {'mDataProp': 'time', 'sTitle': 'Time', 'sType': 'string-case', 'sClass': 'left'},
      {'mDataProp': 'instructor', 'sTitle': 'Instructor', 'sClass': 'left'},
      {'mDataProp': 'category', 'sTitle': 'Category', 'sClass': 'left'}
      ];
      return tableColumns;

Post a comment