Hey viz fans.

I’m baaaaack. Thanks to everyone that send good wishes for my shoulder surgery last week. It was a frustrating few days so I thought I’d get vizzical as quickly as possible.

I’m gonna start this post with a caveat – Don’t treat this viz as authoritative! Please! I had some massive challenges with the data and tried a few things for the first time so I just can’t guarantee that it is as accurate as some would like. As with most of these vizzes, they are as much to showcase the capabilities of Tableau / Alteryx as they are intended to be informative. If you’ve got an issue with that then proceed no further.

I love sport. All sport. Especially the Olympics. And as you will know we are coming up to the start of the 22nd Winter Olympic Games in Sochi, Russia. So I thought I’d viz up the medal history going back to the first competition in 1924.

And here it is!

So how’d I do this?

1. Data

Now this was *almost* a great success story. Let me explain.

All the medal history was held at http://www.databaseolympics.com, see the Winter Olympics section. The trouble is, you have to navigate through a couple of levels to get to the sports you need, there’s no way to view all the results in one go. When you do get to the data it’s in a nice format for copying into Excel, see below.

If only I could get it all into one place. Then I had an idea.

First off I managed to get a list of all the base level URLs I needed to cover each sport for each games. There were about 90 of them. e.g. www.databaseolympics.com/sport/sportevent.htm?sp=ALP&enum=110

Then it was dadda!! – Alteryx time. Let’s see what you got eh. Erm… Well I was pretty confident that this could be done but I’m far too inexperienced with the tool to get anywhere. Luckily a good friend of us at VN is the current Alteryx Grand Prix Champion – Chris Love (@chrisluv) of The Information Lab. I thought I’d see if he could solve the issue.

This is what he sent back to me… Turned out to be fairly tricky.

And here’s the Alteryx module that cracked it. Happy to send it to anyone if you want to take a closer look.

Absolutely awesome. I’ll be honest, I don’t yet fully understand how it works but I’ll be looking into that in more detail. I’m a little disappointed that Alteryx doesn’t seem to do web queries a little easier, nor can it process html files (even though it’s ok with xml).

So there we are – almost. I now had good data for all competitions up to 2006. Trouble is there was no data on that website for the Vancouver 2010 event (argh!). I did manage to locate the information from Wikipedia but it was a totally different format and had a number of other variances from the other data.

Another caveat is that I’ve treated every team medal won by an individual as an individual medal. For example, the bronze medal won by the GBR Ice Hockey team in 1924 counts as 10 bronze medals – not strictly accurate I know.

I managed to use Alteryx again to transform some of the 2010 data into a better format but I couldn’t get away from the fact that I had to do a lot of manual crap to get the info into the format I needed. With that, there are sure to be some mistakes – hence the above caveats.

Anyway….

UPDATE: Due to some pretty bad data quality issues with the source detailed above (missing whole events etc), I switched to this alternative. It looks to be a lot more accurate but again I can’t vouch for the quality. Let me know if you see any issues.

http://www.olympic.org/medallists-results

2. Viz design

Timeline – Quite like this view. Simple plot using an Olympic Ring image as the mark, plotting the year of the competition.

One thing I really wanted to do was to represent the official flyer of the competitions and have that change with user selection. I’ve been meaning to try out Shawn Wallwork’s tip on how to do this. http://community.tableausoftware.com/thread/119079.

In summary it’s a tip for creating a dynamic background map, based on user selection. Check it out. Very smart.

Filters – I then created a couple of worksheets to act as filters for Sport and Medal type. Simple enough, using shapes. Needed to make sure the medals were consistent in size and style.

For the sport selector I used the official pictograms for the Sochi games. I think they’re awesome and look perfect for this style of viz. I positioned them centrally to act as a central selection panel. Love the look and feel of the images themselves. Give it a bit of use as filter action and job done.

Medal Table & Individual Records – These were simple enough. The only cool bit was the use of small medal shapes for the gold, silver and bronze medals. Looks smarter than a basic text or highlight table.

Final touch was to add an athlete wildcard search, and then a mouseover info sheet to highlight the caveats discussed in the post. I also added a country code custom list filter so you can compare how one nation has performed compared to another.

3. The Finished Product

So there you go. A viz of the Winter Olympics. Fun to do that one and great to be back in vizzing action. Lots of ways this one could be improved but I didn’t have the time on this occasion.

Regards, Paul