According to data released by Britain’s Office for National Statistics, and a recent BBC article, Brits drinking habits are changing. Are the British falling out of love with booze? The data and graphs involved were the topic for Makeover Monday this week.

Thoughts on original charts

The data set provided focussed on the first section and chart in the article. It’s a good simple bar chart clearly showing the changes in certain categories between 2005 and 2016. What we can’t see is the pattern in the intervening years. We also cannot see the parts that make up the whole in any given year; we can see the proportion drinking at least once during the week before the survey, and the proportion that do not drink at all, but there is no mention of those that drink but not during the week in question. Finally the response of drinking on 5 or more days of the week is presumably a subset of those drinking at least once during the week. The bar chart does not represent these sets fully.

The makeover

An area chart seemed to be the best way to represent parts of a whole over time. I started out with the overall results before breaking the same graph down by gender and then gender and age bracket. Large labels containing headlines outline the changes at a glance:

 .

The viz is available on Tableau Public.

Credits

The wine glass, male and female icons were sourced from www.flaticon.com/authors/simpleicon.

Challenges

The biggest challenge was one I almost didn’t spot! Initially I misinterpreted one of the source data fields, which resulted in the graphs being wrong. The clue was that my numbers were adding up to more than 100% in some cases. Discussion on twitter about the figures not necessarily adding up to 100% and comments in the ONS source about 95% accuracy meant that I attributed my mistake to the source data. The issue kept nagging at me though and I’m glad I went back and checked as I’d double counted those that drank on 5 more days!

The way the data was structured meant that I needed to reshape it to create the four categories that made up the whole. A pretty simple exercise within Tableau. I created calculated fields to grab the proportion when the question was relevant. For example:

IF [Question] = 'Drank alcohol in the last week' THEN [Proportion] END

Then I could SUM these up with [Question] excluded from the view and stack the measures. [Aside: I actually made my life harder than it needed to be here and used LOD calculations excluding the question dimension. This was unnecessary given that I didn't need the question dimension in the view in the end].

Calculating the missing categories was just a matter of subtracting the proportions we did have from 1:

 1 - ([Drinks on 1 or more days] + [Does not drink at all])

There was quite a bit of fiddling with the label content and font sizes to get them to fit nicely into the space available. They’re applied to the last point on the (synchronised dual axis) lines that highlight those that drank at least once during the week. If you download the workbook you’ll spot a space and full stop character at the end of each line of the label. These act as padding so that the label is not flush with the edge of the area chart. The full stop is needed as spaces on their own are ignored. To get around this the full stop is given the same colour as the chunk of the area chart that the label sits over. Not a robust option if the area chart components can vary more dramatically over time but okay in this context.

Conclusion

I’m pretty happy with my made over chart. I think it represents the part of the story I’ve focussed on clearly and cleanly. I’m less happy with the mistake I made and the unnecessary LOD calculations – but in some ways that’s reinforced a good habit of going back and checking things if they seem wrong or overly complex!

We’re redoing a list based on Dutch car registration data this week for #MakeoverMonday. The list doesn’t really do the data set justice, but I don’t speak Dutch so haven’t dug into the rest of the story! The actual figures were hard to reproduce and seemed like a niche part of the data, so I looked for a different story and decided to focus on the most popular makes of car. Headline figures give the reader some context as to how many registrations there were in 2015 and 2016, as well as the general growth rate. The slope chart then shows registrations for the top 5 makes and how these have changed:

.

The visualisation is also available on Tableau Public.

It was great to try a slope chart this week. Also a quick shout out / credit to Charlie Hutcheson and Pooja Gandhi re the dashed lines in the top section of the viz – thanks for the write up on this technique.

A look at Sydney ferry patronage for week 18 of Makeover Monday based on Transport for New South Wales Open Data.

Thoughts on the original chart

The chart being made over is actually a series of Tableau dashboards within a story (set of tabs). I like the way I can work through the story from an overview of the data to some summary charts and then down to some detail. The card type dashboard interested me. They key story that jumped out to me was that around 70% of trips were made using an Adult Opal Card. I don’t think we need to see this proportion visually per month and then again per line. Perhaps other angles from the data could have been visually represented too? Nice dashboard though and I enjoyed clicking through to the map for some context.

My makeover

I wanted to try getting the breakdown by ferry line, card type and month onto one viz so targeted an iPad portrait layout. First up I tried a heat map. This was okay but not quite what I was looking for.

A line or bar chart of trips per month, with a panel per ferry line and card type worked well but there were too many card types to show nicely! Also the trips using certain card types (e.g. employee) were negligible compared to the main types. Grouping the types together allowed me to fit everything in. School and Concession (concession being for tertiary students) seemed to bundle in with Child / Youth quite nicely, and single trips could be bundled in with other outliers.

The viz ended up looking like this:

.

This allows me to see at a glance the top lines and card types, plus I get a brief idea of trends over time from the monthly bars (with labels on the first and max values). In some ways it’s working a little bit like a heat map if you consider whitespace a lack of heat in comparison to the space take up by the bars. In some ways the bottom right part is a little empty. Still that does tell a story.

The viz is also available on Tableau Public.

A note on image reuse

My original design concept had a different image at the top of each column; I wanted to use images of the main Opal card types. My thoughts were that this would add useful context for those users who were familiar with Sydney ferries or similar networks. It would be clear at a glance that column one related to the ferry lines and the subsequent columns to the various card types.

Unfortunately whilst the data is open access under a creative commons licence, their logos and trademarks are not. I interpreted this to mean that I couldn’t use the Opal card type images, because they included trademarked logos. I followed up with the relevant department (who were very helpful) just to be sure and had those thoughts confirmed. No drama – it was useful experience to research this angle and I’ve still been able to use coloured rectangles to give my viz some context. In fact if I were redoing the viz I’d probably use that space for some summary numbers whilst retaining the colour for context. I’d also revisit my column headings to make the groupings clearer.