We just pushed out an update to our NCAA coach salary data table that includes a third tab for assistant coaches. You can find the data table and the methodology we used for our analysis here. The most recent story, which is about assistant coach pay, can be found here.
I’m about to update my last two blog posts to note this addition and provide links to two additional data files.
Note: We just pushed out an update to the data table that includes a third tab for assistant coach salaries. I have updated my blog post to include links to two additional data files but otherwise have not discussed this new tab. If you are reviewing my JavaScript code, however, you may find it more helpful to look at the fnFormatAsstDetails function used by this new tab rather than the fnFormatDetails function discussed below. The code in this function is less complex because the assistant coach data lacked some of the challenges found in the head coach data.
In my last blog post, I talked about how I built a jQuery DataTable with expandable accordion rows to run with a USA Today story to highlight NCAA football coach salaries. If you check out the DataTable, you’ll see that when you expand a row, you get a picture of the head coach and up to five years of salary data. You’ll also see a dynamically generated Google chart that maps salary data and compares it with the median salaries of other coaches.
In this blog post, I’ll talk about how I embedded that Google chart.
DataTables is a jQuery plugin that enables you to create a richly interactive and highly customizable HTML table which users can sort and filter. Allan Jardine’s open-source software is extremely complex yet simple to use, and he has attracted a large following of users over the years who have built just about every kind of DataTable imaginable. The code is regularly updated and very well documented, with tons of examples, and Jardine frequently chimes in when someone posts a question on the DataTables forums. At USA Today, we routinely throw up a data table to run with a story in as little as 20 minutes.
If you’d like to download all the source code and JSON data files for the NCAA coaches DataTable, click here to get the .htm file and here, here, here, here and here to get the data files.
I got good feedback on the Google chart and was encouraged to blog about it. You’ll be happy to know it was not at all difficult. It’s also not elegant. The JavaScript function that fires when the user clicks a row to expand just builds a long string of html that is rendered once that string is returned by the function. That html code includes a dynamically generated <img> tag that houses the Google chart.
The html for an expanding row and, with it, the Google chart <img> tag is constructed in the fnFormatDetails function, which is about 300 lines of code. All but about 25 lines of that code are used to create the Google chart.
If that discourages you, read on because chances are good you can develop a Google chart of your own with far less code. In fact, if you hard-coded those image tags in your data, you’d need almost no JavaScript code at all!
In this case, the dataset posed some challenges that required some fairly heavy client-side JavaScript code to handle. For example, all coaches have salary data for 2011 but not necessarily as far back as 2006. (In fact, we don’t have any salary data at all for 2008.) In order to plot a line from 2006 through 2011, I had to have data points for every year. That meant the code had to calculate average salaries for missing years. I also added code to put markers over all salaries that were not calculated.
Finally, and most importantly, the chart graphs median salaries for whatever conference the coach is in as well as median salaries overall. Those complexities led to the heavy coding.
(Note: During development, I considered shifting gears and handling all these calculations on the server so I could output a JSON file that had all the data for the Google chart preset, leaving little or no calculations for the browser to do client-side and greatly simplifying the JavaScript. I’m still leaning toward this route for future data releases, but I also think a little client-side defensive coding is beneficial as well. In the end, I did not see any significant performance hit by making the browser calculate the Google chart data.)
To get started, I first used the Google Chart wizard to create a mock up of one of the Google charts. Once I had that, I copied the code generated by the wizard and started picking apart the various parameters to see what they were doing and how they were set.
Once I did that, the JavaScript code was just a simple matter of dynamically generating an<img> tag like this:
To generate a chart that looks like this:
Note that seven of the 11 lines in the chart definition are for the chd and chm parameters. These are the parameters that map the data
and the data point markers. You’ll also see if you look at the JavaScript code that assembling these two parameters took the most work. Most of the other parameters just took hard-coded values. A few, such as chxl, required a small amount of code to produce. (For this parameter, you can see that I just looped through an array of salaries and built the chxl parameter during that iteration.)
The only semi-challenging part about this parameter was creating dollar figures to label the y-axis that included $-signs and comma separators. Elsewhere in the code, I looped through the three arrays of coach salaries (for the coach, the conference and all coaches combined) to determine the upper limit of the salary range and assigned that value to the variable maxsalrng. I used the salbit variable to keep track of the remainder of the salary that remained to be formatted as the code executed. Then I formatted the upper range like this:
You can see that adding a Google chart to a jQuery data table is a simple, straightforward process that requires very little coding, depending on how much of that chart must be dynamically generated client-side. It’s a convenient way to visualize your data and provide a small, clean graphic that can break up a sea of numbers.
Have fun, good luck and Happy Coding!
Note: We just pushed out an update to the data table that includes a third tab for assistant coach salaries. I have updated my blog post to include links to two additional data files but otherwise have not discussed this new tab. If you are reviewing my JavaScript code, however, you may find it more helpful to look at the fnFormatAsstDetails function used by this new tab rather than the fnFormatDetails function discussed below. The code in this function is less complex because the assistant coach data lacked some of the challenges found in the head coach data.
At USA Today, the newsroom’s data team has had a recent fascination with DataTables, a jQuery plugin that enables you to embed a richly interactive and highly customizable HTML table which users can sort and filter. Allan Jardine has developed a first-rate piece of open source software that is extremely complex yet so simple to use, you can throw up a basic data table in 20 minutes.
The most complex data table we’ve built to date was launched today: an interactive table providing salary data for NCAA football coaches at 120 schools. I won’t go into a lot of detail describing it, since you can see it here, but the interactive includes expandable “accordion” rows that let you see much more detail when clicked, including photos of each coach, five years of salary data and a Google chart that graphs salary information by coach and conference. One of our designers, Kristin DeRamus, developed the CSS.
There are plenty of tutorials on the DataTables website, and the forums include a wealth of information, so I won’t talk about the basics here. Instead, I’m going to cover how to build a data table with accordion rows. In another post, I’ll talk about how to embed a Google chart.
I wanted to blog about this interactive to share our code with an ever-growing community of journalists and interested citizens who use data and a myriad of technologies to promote better storytelling. Matt Thompson over at NPR recently posted an article on Poynter about the merits of journalists sharing their data and code with the open-source community. He makes some compelling arguments for why this is a good thing: It increases the transparency of our work, which is rightfully and routinely subject to intense scrutiny. And given the reality of ever-shrinking newsrooms, anything we can do to make data more accessible to others furthers the watchdog role of journalism.
And, admittedly, it’s just fun and cool to show off your bling.
Back to the data table. If you want to see the source code for the data table independent of all the content wrapped around it, you can go here. In the code for this page, you’ll see full-path links to all the CSS and jQuery plugins you need, including our standard CSS for all our data tables. Custom CSS for this data table is embedded in the .htm file.
You’ll also need the five files that house all the data in JSON format. You can get those files here, here, here, here and here.
To build this table, I started with Jardine’s tutorial for building tables with drill-down rows. I’m going to ignore the fact this page has tabs and just focus on the Coaches table, which I created like this:
I’m posting this code above for reference but don’t want to go into any detail, since it’s just bread-and-butter DataTables. There’s plenty of documentation on the DataTables site about how to build one if you’re not sure and how each property is used.
Let’s go to the code that handles the Click event for the table:
This function captures the row that has been clicked. If it’s already open, the “else” clause fires and closes the row. Otherwise, the data for the row is passed to the fnFormatDetails function (which dynamically builds the HTML for the expanding row). Once that code is returned, a couple lines of jQuery code provide animation to expand the clicked row and slide it to the top of the table.
The fnFormatDetails function is just simple JavaScript. While it is a long function, you can see it is just building a long string of HTML to create <div class=”innerDetails”>. That HTML string is housed in the variable sOut.
Tip: To see what HTML is being generated by your function during development, just insert the line alert(sOut); immediately before return sOut;
If you look at this function and your jaw drops because it’s almost 300 lines of code, keep in mind that the vast majority of the code is needed to produce the Google chart. Aside from that chart, only the first dozen or so lines of the function and the last 25 lines or so produce the rest of the HTML. The Google chart, which I’ll cover in another blog post, was difficult to create because each coach includes additional contract data for zero to four years (housed in nested arrays). Were it not for the chart, this function would have been less than 50 lines of code!
If you want to see simpler examples of data tables that can be posted quickly, check out: 58 members of Congress among wealthy 1%, Megadonors pump money into SuperPACs and Hospital death and readmission rates.
Two side notes: First, you certainly can wire a data table to a database, if you like, but I have yet to do this. We’ve used DataTables exclusively with smaller datasets where it’s not impractical to pass the entire dataset to the browser. This speeds up the page by eliminating database calls completely and allows for rapid client-side processing of the data.
Second, if you’ve got your data in Excel and want to convert it to JSON, Mr. Data Converter is your friend. I should note that I did not use Mr. Data Converter here only because it wouldn’t work with the nested Contracts data. (Instead, I wrote a SQL Server stored procedure that exported the data in XML format, then wrote a quick Python script to convert that XML data to JSON format.) But most of the time, I use the Mr. Data Converter site to build well-formed JSON data for my DataTables.
Have fun, good luck and Happy Coding!