So you need a timeline chart and you need it for work. No worries, services exist for creating beautiful charts online, Lucidchart being one of the best examples (not a sponsor). But… You need the chart for work, you need it to contain sensitive data for internal use only, you may even need to include the dreaded PII (Personally Identifiable Information).
Now, are any of these chart producing services going to leak your data? It’s not likely to be honest. Am I saying don’t trust Lucidchart for example? Not necessarily. But I am particularly sensitive to the risk of data leaks due to having come from a Law Enforcement background where a PII leak could lead to loss of employment. Also I have recently completed a course in Cyber Security. These are both huge factors in my sensitivity to the risk of a data breach that could occur by using a third party service. Just think, if Lucidchart were to suffer a breach that in turn led to breach of data contained in charts created by you on that service, you might in turn also have to report a breach, it’s all very complicated at that stage.
The solution? Roll your own, read on for a relatively easy way to create your own timeline chart without ever having to reach out to an online service with data you don’t want exposed.
The Plan
The plan is pretty straightforward. Come up with a way to create a usable timeline chart without data ever leaving the control of your company’s environs. That sounded like a lot of work, so instead of creating the charts I defaulted to the time honoured tradition of coders everywhere: find something that already works and use that as the basis of a custom solution.
In comes Google Charts, a collection of chart code samples that are “are powerful, simple to use, and free“. Now you’re talking my language!
In actuality the collection of charts available are amazingly broad, and I can see myself returning to this repository of chart code over and over again. I can highly recommend.
To the timline charts I went. The timeline chart I found was very much usable, and was very configurable. But the first thing I noticed was that in order to get it working your data had to be in this format:
dataTable.addColumn({ type: 'string', id: 'President' });
dataTable.addColumn({ type: 'date', id: 'Start' });
dataTable.addColumn({ type: 'date', id: 'End' });
dataTable.addRows([
[ 'Washington', new Date(1789, 3, 30), new Date(1797, 2, 4) ],
[ 'Adams', new Date(1797, 2, 4), new Date(1801, 2, 4) ],
[ 'Jefferson', new Date(1801, 2, 4), new Date(1809, 2, 4) ]]);Now, it’s not completely unreasonable to create your chart like this, but it is a bit fussy, especially if you have a lot of entities to chart.
What if we could have a text file of entities with start date and end date and could run a script to transform it into the format above. That’s a solution I can get down with. So we have this sample:
Entity 1A,2014-03-18,2017-10-19,Entity 1B,2017-10-19,2018-05-22
Entity 2A,2015-08-01,2019-11-10
Entity 3A,2013-02-28,2015-9-11,Entity 3B,2015-9-11,2016-12-25,Entity 3C,2016-12-25,2019-07-21
Entity 4,2016-03-18,2020-05-13
Entity 5,2017-04-01,2020-10-21And we want to feed that into a script to transform it into the format above. A doddle! For that I have some Python code in the form of a Jupyter notebook. Read below for the details.
But first, let me take a few lines to explain the text input file. The idea of the file is to have a line per entity you want to chart. The first line is Entity 1, and there is a name change for Entity 1 at some point in time. The simplest line looks like what we have on line 2, an entity with no name change. The format for that is entity_name,start_date,end_date. As you can then see in line one, to represent a name change you add a comma and another set of name and dates data like so: entity_name_1,start_date_1,end_date_1,entity_name_2,start_date_2,end_date_2. You can add as many entity name changes as you want as long as you add a comma to separate the entities. The Python code should raise an exception if any of your input lines are malformed. There is some more discussion of the text file further on, but I needed to explain that much at this point.
The Result
The chart I ended up making was very similar to Figure 1 above. It allowed for multiple entity names per line to accommodate name changes of an entity. It allowed for each entity to have different colours per name change as a visual marker of the change and the colour scheme rotates so your chart can contain as many entity lines as you need, and apart from the eventual reuse of colours after 9 entities are added, they should still be colour differentiated enough to still be useful. Best of all, I’ll be providing all the code below so you can extend upon it if you need to.
The Execution
Before we get too deep into the execution let’s define our asks more concretely.
We want a timeline chart. We want to be able to plot an entity per line. We want to accommodate an entity’s multiple name changes.
In order to facilitate that, and you’ll see it in the colorsList variable in the code later on, I have a list of 9 colour schemes, each scheme having three different variants of the same colour. So if your entity has more than three name changes it starts to reuse the colour variants. And if your chart has more than nine entities it will start to reuse the schemes.
If you find this post useful but the colour schemes limiting feel free to expand as your heart desires.
If you visited the Google Charts timeline section you will see that very simply what you need to do is edit some javascript to control what appears in the chart. Editing the javascript in the form it is currently in is unduly fussy in my opinion, hence the use of Python to help automate it. However, there’s no getting around the fact that the data has to be entered somewhere. Adding to the fussiness of editing the javascript is a trap I fell into where javascript indexes months from 0. I mean come on…
So instead you just need a text file. Each line of the text file represents an entity to be charted. With the simplest line having the format “entity_name, start_date,end_date”. Dates are expected to be in the form YYYY-MM-DD.
If you want to represent an entity name change you need only add a similarly formatted entity on the same line as the original entity name separated by another comma like so “entity_name_a, start_date_a,end_date_a,entity_name_b, start_date_b,end_date_b”. The script expects that the dates won’t overlap, I don’t know what happens if they do, and I will only care when I come across an example of when that makes sense.
Let’s talk chart options. The timeline chart allows for certain options to be used. I won’t go into them in detail here, but to allow for multiple entity names we need to allow for a row label. But we don’t want to display the row label since in every case the row label will be the initial name for each entity in the text file. To suppress that row label we need to use the option “timeline: { showRowLabels: false }”. The script also automatically generates the colours to be displayed for each entity name.
The basic structure of the HTML code that needs to be edited to include the generated javascript is as follows, simply add the javascript code between the two comments and save the html file and open it to view the results:
<html>
<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {'packages':['timeline']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var container = document.getElementById('timeline');
var chart = new google.visualization.Timeline(container);
var dataTable = new google.visualization.DataTable();
//ENTER GENERATED JAVASCRIPT BELOW HERE
//ENTER GENERATED JAVASCRIPT ABOVE HERE
chart.draw(dataTable, options);
}
</script>
</head>
<body>
<div id="timeline" style="height: 100%;"></div>
</body>
</html>Now comes the main work. I called this solution relatively simple earlier on. And it is. But if you’ve never worked with Jupyter Notebook before, it nearly takes a paradigm shift in your thinking to see it as simple. In that case, I cannot advocate strongly enough how worthwhile going through that shift will ultimately be.
Here is the Jupyter notebook code that transforms the text file above into the javascript code to be pasted into the appropriately marked position in the HTML code above:
The first section of the Notebook sets up the various functions needed to transform the text file to the required javascript. The second section reads in the text file to a list and prints it to that sections output so you can see if it was successful or not. The third section then generates the javascript by passing the list data to the functions defined earlier to transform them as required, it then outputs the generated javascript code.
Now if you take the genertated code starting with dataTable.addColumn…, copy it and paste it into the HTML code above at the appropriate section, you will then have a timeline chart equivalent to the image at the start of the post.
Powerful stuff from Google (with a little help from me) to generate charts that most definitely paint a thousand words.
Visit my Time Chart JSFiddle to see the chart in action and play around with the settings.
Enjoy!