Fighting Ebola with Tableau

In October of last year Anya A’Hearn sent an email around to the rest of the Tableau Zen Masters to let us know about upcoming projects sponsored by the Tableau Foundation, which had recently been set up. 

Tableau was going to provide free software and financial support to a few select charitable projects aiming to use data to tackle problems. Tableau wanted to know if the Zens was would we be willing to volunteer our time and skills to help the project teams fully utilize their Tableau resources.

Because of my brief background working in Infectious Disease Epidemiology, and because it made sense geographically, I put myself forward to assist with a  collaboration between the Columbia University Earth Institute and a company called Dimagi. The goal of the project was to utilize mobile phone technology and teams of contact tracers to conduct contact tracing to combat the spread of Ebola in Guinea, West Africa. 

Contact tracing works more or less like this: After a patient is diagnosed with Ebola, the people who had been in contact with that patient (the "source case") are identified and monitored each day for 21 days, which is the incubation period for Ebola. Contact tracing is an established method for controlling the spread of a disease.

Understanding the project

I met up with Dr Jilian Sacks, part of the team from Columbia who were spearheading the project. Jilian explained to me how in this situation the contact tracers would be paid volunteers from the communities in Guinea, and the recording of information would be captured on basic smart phones via a questionnaire app created by Dimagi, called CommCare. The app would lead the contact tracers through a series of questions designed to capture information about the contact and record signs of symptoms. The app also provides prompts for health information or advice messages to be delivered.  That data is then submitted from the phone and loaded onto a database. And that’s where Tableau comes in.

(Aboubacar Diallo, mHealth Country Coordinator of the Program, leading a training on CommCare for Contact Tracers in one of the communes of Conakry).

Given that all this information about Ebola contacts was being captured, the goal was to utilize Tableau to visualize the information to better understand the overall picture, and more importantly to help manage the operation on a day-to-day basis.

I created a few dashboards which captured information about the contact registrations, follow up visits and closures. Creating these dashboards was in many ways different from what I normally do as a consultant and presented some specific challenges:

- We had to get up and running very quickly: People working on the ground needed to see information immediately and so there was little time for extensive requirements gathering, wire framing and design phases.
- Survey data can be tricky to work with: The data design had been set up to service Dimagi’s systems, and the translation to Tableau was quite tricky.
- I had a lack of familiarity with the topic: I had to learn about what was and wasn’t important as we progressed. For example, one element that turned out to be of importance was measuring the amount of resistance (even violence) the contact tracers faced when carrying out their work.
- The dataset was continually evolving: As the work progressed, new fields were added to the survey data, or changes were made to existing data to address data quality issues.
- There were language barriers: The database was in English (and English is the only language I really know) but the dashboards needed to be in French.
- Time was in short supply: I still had to hold down a very demanding day job and do this in the evenings and weekends.

However, working with Jilian Sacks and Elizabeth Zehe from the Columbia team on the requirements and with Tyler Sheffels at Dimagi on the database and Tableau Server, we managed to get some dashboards off the ground and into the hands of healthcare workers and officials in Guinea.

(A doctor in charge of health for one of the communes in Conakry, the Capital of Guinea, at a training.)

These dashboards covered summary views of the situation on the ground, but it quickly emerged that some views giving more detailed analysis of the activity of the contact tracers was required to really add value to the management of the operation. 

Enter the cavalry

Fortunately for me, I have some great colleagues at Slalom and they came in to really drive these new dashboards forward.

Firstly John Mathis (of Iron Viz fame) not only helped to refine and organize the existing dashboards, but also created a couple of brilliant views that would enable daily management of the operations and the activities of the contact tracers. The views that John created proved to be extremely helpful to the project teams and are viewed every day. John also worked very closely with Tyler to get the database into the best shape possible for use within Tableau.

Next up we needed someone to help create a single overall KPI view, and for this fellow Zen Master Nelson Davis stepped up to the plate. Nelson put his Tableau skills to work to merge multiple datasets into a single page view of what had happened in the last 7 days.

John and Nelson both managed to not only show what did happen but also show what didn’t happen—a skill that anyone who has worked with data knows to be extremely difficult. 

(Some of the dashboards.)


The results

I’m very pleased to say that our hard work has been well-received, not just by the immediate project teams but also by the health management teams and frontline contact tracing supervisors in Guinea and even the government's National Ebola Response Committee. The most rewarding part of this process has been to see pictures like those below and know that we did something that is really being put to use.

(Dr. Pépé Bilivogui, Lead of the Surveillance Pillar of the National Ebola Response Committee in Guinea.)

(Dr. Alain Achi, one of the Guinea-based project staff, leading a training on CommCare and Tableau for Contact Tracers in one of the Communes of Conakry.)

We also received this email from Neal Myrick, who heads up the Tableau Foundation:

“Was at the Ebola Innovation Summit in SFO yesterday. Randomly met an advisor to the President of Guinea.  He saw my name tag, swung his computer around and showed me the live Tableau visualizations from the project with Dimagi and Columbia University.  Really stellar!”

It doesn’t get much better than that.

I presented our work along with Jilian and Beth from the Columbia team and Tyler from Dimagi at the Do Good Data conference in Chicago, which was a great experience.

What’s next?

Unfortunately the work is not done as Ebola is still present in Guinea (as well as in neighboring Sierra Leone and Liberia).  Despite the fact that the world’s tv cameras have stopped filming it, the epidemic is still going and we need to keep supporting efforts to slow it. We’ll continue to do our small part by improving the information flow and hope that that makes a difference.

I’m pleased to say that we will be presenting this story at the Tableau Conference in October, so come and find out more.

The team

Real Estate of Mind: Exploring New York City real estate data

What's this all about?

We've recently bought an apartment here in New York, and it got me thinking a bit about the property market. There are lots of websites like http://streeteasy.com/ for finding properties, and there are of course resources like http://www.nytimes.com/pages/realestate/index.html for reading about the market, but you never really get the full picture all at once.

I was interested in seeing what an entire years worth of residential property sales looks like In New York, and so I went in search of a data source. Fortunaltey New York City is actually really good at making data publicly available through the its open data initiative https://data.cityofnewyork.us/. And I specifically found this site http://www1.nyc.gov/site/finance/taxes/property-rolling-sales-data.page which provides data for EVERY SINGLE PROPERTY SALE in New York City for the last 12 months.

I decided to start with just Manhattan and downloaded the Excel. And it looks like this:

Oh no, stupid Excel format - guess I'll have to edit it before loading into Tableau.....

Bananarama

On last week's episode of the Tableau Wanna Be Podcast with Emily and Matt, Jewel Loree shared that her go-to strategy for developing a new viz was to think of the name first and go from there. I thought I'd give this approach a shot, and for some reason the word 'Bananarama' came into my head - maybe because I like bananas or maybe because I subconsciously love 80's pop music. Who knows.

So anyway, here's a viz on the worldwide banana trade, with data taken from http://faostat.fao.org/.

The most interesting thing I see here is what's going on with Belgium - lots of bananas go through Belgium on their way to Germany.

Making 'Dieu et mon droit' and some tips for using Wikipedia data

The last viz I made, see below, was all about the history of English Kings and Queens. And the data source for that viz was a Wikipedia list page http://en.wikipedia.org/wiki/List_of_English_monarchs.

Now it just so happens that the next Tableau Public Iron Viz feeder contest is all about making something with Wikipedia data. So although I'm not going to be entering my viz, I thought it might be a good opportunity to write a few things about how it was made to show some of the things you can do with a simple Wikipedia list. And in fact if you look across my blog, you might notice that a healthy proportion of the vizes I make have taken their data from Wikipedia.


So on to the viz:

The idea came from three places. First I really wanted to collaborate with my friend Dan Miller on something. Dan is a great illustrator and calligrapher and I wanted to bring in some of his expertise to help produce a viz that contained some custom artwork. Secondly, I was feeling a bit homesick and wanted to do something with a British theme. And thirdly I was browsing Ben Jones blog and came back across this viz of US Presidents in the form of a Gantt chart which is rather excellent.

So it hit me, why not copy Ben's idea but do it for English monarchs? This would also enable Dan to bust out some sweet looking calligraphy.


So first I went off to Wikipedia to see what data they had, and as usual it was perfect for my needs and in a list form, so I just copy and pasted that bad boy into Excel

BUT..... as you can see above, the boxes contain different bits of info, e.g. the name and date of birth and death are in the same box. And also because the formatting was a bit different for each monarch it ended up kind of messy. I also found that much of the information that I needed was on the detail page about each Monarch:

I also tried some web scraping, but for the life of me I still haven't got this to work the way I want it to. So I went old school, and manually entered everything into an Excel spreadsheet.

That's right. Manual data entry.

And here's my first tip for anyone making a viz for the contest: Don't be afraid of manual data entry! Yes its very boring and yes it seems archaic - but sometimes its the only way that works for what you need, and really if you're watching tv and having a beer at the same time, its not that bad.

The second piece of data I decided to get was which countries they were the Kings or Queens of. This was painful. I put this in a secondary data source (see more below in the section about maps).

You will also see that I grabbed a picture URL from Wikipedia for each monarch. One of the things I love about Wikipedia lists is that thanks to those people who created them, there are often pictures there of exactly what you need, and they have one great quality - they can be resized!

How to resize a picture from wikipedia:

You need to do this if you want your pictures to all be the same size, and because images in the Tableau browser window do not resize once they are up on Server/Public.

A Wikipedia picture URL typically looks like this if you copy it from within the page:

http://upload.wikimedia.org/wikipedia/commons/thumb/7/77/Eadred_-_MS_Royal_14_B_VI.jpg/220px-Eadred_-_MS_Royal_14_B_VI.jpg

See that bit in bold and italics? You can change that. I changed all mine to 180px

http://upload.wikimedia.org/wikipedia/commons/thumb/7/77/Eadred_-_MS_Royal_14_B_VI.jpg/180px-Eadred_-_MS_Royal_14_B_VI.jpg


And now, although the pictures might be different shapes, they are all at least the same width.


Mock-ups

I don't normally create mock-ups for my vizzes, I usually just dive straight in, bit since I was working with Dan I first produce a 'data only' version of the viz, which looked like this:

(but without the background on the scatter plot, that came later)

I sent it to Dan and he came back with this which he mocked up:

which was starting to look pretty good. So we developed it from there. But here's where the compromises started coming in.

Backgrounds

Transparent backgrounds are not available for Tableau sheets - but we really wanted to use the distressed paper scroll look. Background images can be used, but only it seems with scatter plots, and so I had to abandon the Gantt chart (which I liked) to a scatter plot that looked something like a Gantt chart. I did this by placing the order of the monarchs in reverse (i.e. negative order) on the rows. And I couldn't figure out a way to get properly starting and ending Gantt bars, so I switched to crowns.

Dan gave me a big jpeg file with all the illustration on it and I used this as the background map.


Now the actual scatter plot on the viz I used a plain background image for because it was possible, but the bar charts I had to leave the background white. So I wrapped them all in transparent .png files of picture frames so that the borders would not be as visible or noticeable.

And then I added lots of dashboard actions. Again, a tip for folks doing a wikipedia viz - because of the nature of the data sets they lend well to exploratory vizes which are controlled by dashboard actions. And now that Tableau 9 is in play, tooltips and action responsiveness is much improved so I recommend making all actions driven from Hover.


Dual Axis Maps

Lastly, I wanted to create a map that kept England, Scotland, Wales and Northern Ireland separate, but include other countries. The default geocoding in Tableau has the United Kingdom as a whole, and England, Scotland etc... as states. So I decided to use a dual axis map to get around this and place 'State' on one axis, and 'Country' on the other and blended a secondary data source that looked like this:

Well I hope that was useful and if you are entering the contest - good luck :-)

Dieu et mon droit - Monarchy in the UK

Well its been a while, but here's a new viz showing the Kings and Queens of England.

This one was a labour of love and was made with help from my friend and pinball team captain Dan Miller who did the illustration and came up with the overall look and feel. You can see more of Dan's work here http://toothpicksandgum.com/ and here http://instagram.com/manmiller and follow him on twitter @metropolus.

Learn About Tableau

One thing that I would have liked while making this viz is transparent backgrounds for sheets.

Oh and please note that the data collection took bloody ages, so if you spot any errors please let me know. And the countries are the modern equivalents - identifying individual US states under different monarchs got a bit much to deal with.

One of the things Tableau is.....

A few days ago Paul Banoub posted this on Twitter:

and yesterday Dan Murray of Interworks posted this blog entry describing his thoughts on what Tableau is and what Tableau isn't. And I agree with everything Dan wrote (he is a smart man after all!).

However there was one thing missing from Dan's description of what Tableau is and it got me thinking that I need to blog about it.

One of the things Tableau is is the best tool yet created for data exploration

At this point you might be thinking "no shit Sherlock" but I think this is something I need to write about, so first a little history of my journey with Tableau.....

I first came across Tableau in 2010 when working at Barclaycard in the customer insight team. A lot of my job revolved around extracting information about our customers from transactional databases, finding the interesting nuggets in the data and basically turning that data into stories to provide 'insight' for use in product development, strategy and marketing decisions. In practical terms what that meant was writing code in SQL or in SAS, exporting aggregations of data to Excel to produce charts and then converting these to PowerPoints. 

The thing is that this process is not particularly forgiving when there is an error in the data, or when the data story you were looking into turned out to be not very interesting or insightful. Starting the whole process again was a time consuming effort and on the fly analysis, while possible, was sometimes painful.

When I first started using Tableau, thanks to an introduction from my then new colleague Brian O'Connor, I actually had the 'personal' version which did not allow direct connection to databases. And so I was using Tableau only against Excel files. It made my life easier even then, but I was mostly using it as simply a way to make charts a lot prettier than they were coming out of Excel.

And then Brian and I had our Eureka moment. We attended a Tableau meet-up in London for a preview of version 6.0 and saw what the then small but enthusiastic group of users (including one Tom Brown) were doing with it and we realised we had only scratched the surface.

So after that my job and career took a dramatic turn for the better. I was plugging Tableau directly into Teradata, analyising data on the fly, finding outliers, spotting possible data errors, seeing patterns  and relationships and testing out scenarios at 100 times the speed I had before. I started being able to turn around answers to questions in seconds rather than minutes and began doing analysis sat side by side with my colleagues from different business teams, working together to find the stories in the data before shipping off the results to PowerPoint. 

And for a long time that's the only way I used Tableau. I am very happy to admit that I had never even heard of the words Business Intelligence, had no background in IT and had no concept of things like Cognos and Business Objects. I did not produce dashboards, or manage servers, or consider how other people would use Tableau, I was using it for my own needs.

That is what I mean by 'data exploration'. Its the process of plugging directly into some data and experimenting until you find an answer to a question you may have never known existed. I think its the most important part of using Tableau (and its the reason why I still do not plan out a dashboard before I play with and understand the distribution and relationships of the data).

And that's why I'm writing this blog post. Because when I look at everything I've done so far on this blog, its mostly been in the context of using Tableau to produce nice looking and user friendly dashboards for other people to consume. But if you took all that away, if took away Tableau Server, Tableau Public, Tableau Online, took away Story Points and even just the ability to produce dashboards in Tableau desktop - it would still be my favourite piece of software ever produced. 

PS - have a nosey around here for some interesting background on the development of Tableau