Improving on a graphic in a news article

1. Problem Statement

We see ever more graphics around us – in newspapers, online, and social media.  These inevitably range in quality – most of them are very good, but occasionally we see examples which obfuscate rather than illuminate.  On the positive side these present an opportunity to think about how they can be improved, and as a case study, actually to show what the improvement(s) might be. 

The offending graphic in this case is the one towards the end of the following online article: https://www.theguardian.com/commentisfree/2019/jul/04/post-brexit-election-boris-johnson-polls-jeremy-corbyn

This is one such example – we take the original graphic, replicate it, then iteratively improve it.

2. Suggested Approach

Critiquing an existing graphic is essentially similar to creating a new one.  Start off by asking what the purpose of it is – what is the key message which the author is seeking to convey?  In this case the answer is clearly given above the graphic – “How Brexit would suit Boris Johnson”.  In this case there is probably no need to ask whether this is the right message – the key question is given that that is the message, how well does the graphic convey it, and how could it be better conveyed?

Here is the original graphic:

Why is it bad?

• The main issue is that the bars add up three things that are mutually exclusive - they are results from three different polls. So total Conservative support from three different polls add up to well over 60%, Labour to just over 60%, etc. This is meaningless.  The core message, about the impact of Brexit on the respective shares of the parties, requires comparison between the three polls, which is nearly impossible if they are stacked in this way.
• The colour scheme is unhelpful – the use of red and two shades of grey.  Given that red is associated already with the Labour Party, using it in a different way on a chart which includes Labour is confusing.  It might only take a second or two for the reader to figure this out, but those seconds are an unnecessary waste.

It’s also worth recognising the good aspects of the graphic, however:

• Good use of title and subtitle – it is good practice to give the key conclusion/message in the title or the subtitle of a chart – it is clearly stated here (“How Brexit would suit Boris Johnson”), along with the description of what the numbers actually represent, ie “Respondents were asked:…”.
• Good placement of the legend – knowing what the three different polls/scenarios are is core to understand this data, so placing them above the chart is helpful.
• Using a bar chart rather than a column chart – this enables the data labels (ie party names) to be shown horizontally and hence be more legible than on a column chart.

3. Rationale and Commentary

This section runs through the iterations of the chart, each one trying to improve it.  To put things in context, these iterations took about 15 minutes – ie it wasn’t a time consuming exercise.

1. Original Guardian presentation 

This is the same presentation replicated in Excel:

1. Iteration 1 - unstack the bars 

This at least enables the three different polls/scenarios to be more easily compared, as they all now start at 0% on the same axis.  However the number of bars, and the colour scheme, still get in the way of interpreting it.

1. Iteration 2 - flip rows/columns and recolour the segments 

 Stacking the bars in the other way is a fairly obvious way of reconfiguring the data, given that the results of each poll add up to 100%.  The three polls/scenarios read logically down the left hand axis.  And using the colours associated with each political party makes the presentation more intuitive. 

Returning to the core message of the graphic, the impact on the Conservative share of support in the third scenario is much clearer.

1. Iteration 3 - flip rows/columns, recolour the segments, and reorder the parties:

As a final improvement to emphasise the core message, shifting the Brexit party segments to be next to the Conservative segments shows that the increase in the latter seems to be a direct result of the decrease in the former, with the shares of the other parties staying roughly the same (which is clearer because their segments now line up).

4. Applicability and Alternatives

There may well be further iterations and alternative presentations which get the message across better, and which bring out other messages altogether – this was intentionally a “quick and dirty” exercise to show what can be done in a short period of time, so broader alternatives weren’t considered.

5. Implementation

The original graphic was replicated in Excel, which was then used to create the iterative improvements (the original spreadsheet is available on request). Excel 2016 was used for the creation of both charts, however the column/bar charts will work in any version of Excel.

6. Context

It is often quick, easy and instructive to critique graphics/visualisations which you come across in any sphere of life, and can be even more useful if you take a short amount of time to make improvements to it, if you feel that the original doesn’t convey its central message very effectively. 

Overview of blog

This blog currently has blog posts on:

• Creating a waterfall chart (in Excel)
• Feedback on our Data Visualisation talk (Life Conference, November 2018)
• Visualising mortality improvements
• Visualising daily incidences (eg, claims)
• Displaying a large number of client recommendations
• Display a matrix of correlation assumptions

Upcoming blog posts will include:

• Visualising the results of stochastic modelling
• General insurance triangles
• Expanded hints-and-tips
• Visualising the parameters used by a model
• Feedback from DataViz talks delivered to Knowledge Sharing Scotland (KSS) forum in April
• Visualising higher-order datasets (eg, beyond two dimensions)

Create a Waterfall Chart in Excel

1. Problem Statement

Waterfall charts are an extremely useful tool in an actuarial context, for example to visualise an analysis of change in insurance reserves over a year. In Excel 2013 and earlier, there is no inbuilt way to create a waterfall chart. This exists in Excel 2016, however the inbuilt graphing options currently lack much of the functionality that is desirable in a waterfall chart.

In this post we show how to achieve the same waterfall functionality that exists in Excel 2016, but using a stacked column chart and Excel’s regular charting functionality. This should allow waterfall charts to be created in all versions of Excel, and also allows for some additional functionality over that offered in Excel 2016’s in-built waterfall chart.

2. Suggested Approach

We show how a waterfall chart can be built as a clustered bar or column chart, which allows for additional functionality in the graphing (such as more flexibility over colouring, detailed analysis of largest changes, more control over end points) with only a little added complexity.

Firstly, some data manipulation is required. The table below shows the data for a typical analysis of change needed to use Excel 2016’s inbuilt waterfall chart, and the additional columns needed to create this as a stacked column chart.

To create the stacked column chart:

• The blank space column is the running total of all the values so far, PLUS where the next value is negative, that value should be added on. This allows us to record negative values as positive values starting at the correct height, otherwise Excel would show them below the axis.
• The col up and col down values are then just the absolute values of the change, split out into those that are positive and those that are negative. We will assign a different colour to the up and down values.
• The final column – col label – is not strictly necessary but useful for creating neat data labels. We can treat these in the same way as the blank space column and assign data labels with values from the amount column.

The two approaches are shown below, with the blank space column outlined to demonstrate how the approach works and then with outlining removed to match the Excel 2016 chart:

3. Rationale and Commentary

The advantages of creating a chart in this way are discussed below with examples.

3.1 More control over colours

The standard Excel Waterfall can only use different colours for ups and downs. Having now created separate columns for ups and downs, we can add further columns at will and change their colours. Here we highlight the large increase in reserves from writing New Business:

As a new series, we can now easily assign a new colour to the one value in this series:

3.2 More control over content of each bar

Having highlighted New Business as a particular area of strain for this company, it’s possible we’ll want to display a chart which breaks this down by product. Again, with Excel’s in-built waterfall functionality this wouldn’t be possible, whereas we can now split it into further columns and use the in-built Key to show this in more detail. As before, we are just creating several new series, each with one value, and assigning these a different colour:

3.3 Adding an end point

As well as the changes applicable to each factor, it is helpful to show the end point – in our case the final reserves – within the waterfall. Whilst Excel can do this, it will default to treating this as another change. It is very easily handled within the column chart:

3.4 Other sensible changes

As well as the above changes to enhance clarity, the best examples of waterfall charts we have seen often include the following:

• Sensible groupings of changes – e.g., in the above example demographic changes are mixed in with market changes. We would recommend grouping by type of change (e.g., using the components of the standard formula SCR as a guide) or even by changes to each product
• Neutral colours – since waterfall charts are often used to show changes in value, Excel defaults to non-neutral colours (blue for up, orange for down). We would recommend using neutral colours, with no connotations of “good” or “bad” when showing an analysis of change in reserves.

4. Applicability and Alternatives

4.1 Intermediate columns

If grouping by type of change, it can be helpful to add an intermediary column to show the total changes of, for example, assumptions changes (things within the control of the modeller) vs other changes. The below chart shows how this can be done:

4.2 Bar Chart

Some users may prefer a bar instead of a column. Whilst not possible using Excel’s inbuilt waterfall chart, this is a simple change having created the column chart, although it is important to note that when simply changing chart type Excel will default to a bottom-to-top approach for the values; we would recommend reversing all the values in the chart when plotting this (see below):

5. Implementation

The case study was produced using Excel and an example workbook is at available from the Working Party. Excel 2016 was used for the creation of both charts, however the column/bar charts will work in any version of Excel.

The implementation requires creating a few new columns of data which are easily calculated from the existing data.

6. Context

A common problem in actuarial science is showing how a value has changed over time, splitting it into component changes to help people visualise the relative importance of each change.

Feedback from the Life Conference

Feedback

We talked about Data Visualisation, and our working party's objectives, at the 2018 Life Conference in Liverpool on November 23rd.  The presentation is linked below and was well received with some good audience engagement.

Presentation slides

Data Visualisation as a Powerful Means of Communication

(Download via GoogleDrive)

Feedback from audience

We received the following feedback from the audience Q&A:


Can we have some case studies which show how visualisations can be used to explore the hidden meaning, rather than present a message which the presenter is already aware of?

Can there be guidance on/reference to whether and how to cut down the amount of information which needs to be presented, rather than just presenting what is there (eg, the correlation matrix could have been cut down instead of/in addition to the presentational improvements).

Heatmaps could be used in the correlation matrix instead of the ellipses.

People need to consider those with colour blindness, especially using red and green for heatmaps.

Should the working party also consider wider presentational techniques, eg Powerpoint skills?

Simple rules of thumb would be very useful as a resource on the blog, eg for displaying daily data, use a grid like the one in the example.

We need to help actuaries minimising the time spent considering and creating ways of presenting data – that is the main constraint.  

Does WP have any ideas on how to promote a good data visualisation culture within a company?


So, watch out for developments in these areas soon...

Visualise Mortality Improvements

Click here to download the R-code (R Markdown notebook with text and code; to be opened in RStudio), and here to download the corresponding rendered html document with properly formatted text and all results from the code (includes interactive figures). Note that you will have to download the file (use download button in the top right corner) and open it in a browser (Chrome, Firefox, Edge, Internet Explorer, ...), as the Google Drive preview will show the file as text document. Be aware that your firewall may block the download of html/Rmd files.

For more information on R Markdown, see https://rmarkdown.rstudio.com/lesson-1.html.

1. Problem Statement

The visualisation of mortality rates and mortality improvements can be tricky due to the fairly high number of dimensions of interest (age, time, gender, country, etc.). Moreover, traditional visualisations usually illustrate either mortality rates, or mortality improvements, but rarely both together. We propose some approaches on how the various dimensions can be appropriately reflected in plots of mortality rates/improvements, and how it is possible to combine rates and improvements in a visually appealing way.

2. Suggested Approach

We suggest the use of different types of visualisations to deliver a full picture of mortality rates and developments. Figs. 2 and 4 are interactive – we suggest viewing them in the attached html file. 

• Heatmaps (Fig. 1): These are often used to depict mortality improvement rates, and provide an excellent overview of the age- and time-dependence of improvements. Alternatively, a 3d surface plot can be used (Fig. 2).
• "Trajectory plots” (Figs. 3 & 4): These are very common e.g. in physics, however they are rather unconventional in the actuarial field. The idea is to plot the development of a variable as a function of time in the form of a trajectory with the current value of the variable on the x-axis, and the rate of change on the y-axis (mechanics: space on the x-axis and velocity or momentum on the y-axis).
• Combined line/scatter/bar plots (Fig. 5) for a more conventional illustration. In combination with faceting and a well-designed plot arrangement, a lot of information can be packed into these plots. 

Fig. 1: Heatmap with annual mortality improvement values.

Fig. 2: (Interactive) two-dimensional surface representation of mortality improvements.

Fig. 3: Trajectories with mortality rates and improvements for females of four selected countries.

Fig. 4: (Interactive) trajectories for 10 developed countries (UK, Japan, France, Italy, Span, Sweden, Switzerland, Austria, Norway, USA) and both genders. The grey band covers approx. 80% of the data.

Fig. 5: Mortality rates, improvements, and population exposure for Japan.

3. Rationale and Commentary

Heatmaps

These are powerful visualisations and particularly handy in the context of mortality improvements as a function of time and age (gender and country fixed). Quite often, diagonal structures appear in these heatmaps, which can point to cohort effects or data errors (e.g. exposure miscounts). Both observations (cohorts / data issues) are highly relevant for actuarial applications.

The mortality (improvement) surface in time-age space typically forms the basis for projecting mortality rates into the future. In particular, the heatmaps are used to set the starting (i.e. current) improvement rates for these projections. For example, if improvements in the latest year appear too high or too low (which is often due to boundary effects in the underlying modelling), one may go back one or two years and start the projection at these older improvement rates.

Trajectories 

Visualising mortality rates or mortality improvements alone without combining the two leaves various interesting questions open. For example:

• Country X currently shows much higher improvements than country Y. Is this a “catch-up” effect, i.e. country X still has lower current mortality rates, and there is a lot of room for improvement? Or is it a genuinely different development patterns for two countries with a similar current mortality profile? 
• Can we identify a common trend across all (developed) countries, and infer a long-term improvement rate?

Trajectories help addressing such questions, and moreover give a strong visual impression. For example, it becomes evident how the 1970s and (even more pronounced) the 1990s saw detrimental changes to mortality rates and life expectancy in Russia. In the visualisation, the negative improvements make the trajectories go in the “wrong” x-direction, and one can easily see and understand how many years of positive improvements it takes to recover from such a crisis. Note that in our visualisation, the x-axis is reversed – this makes it more natural to understand the development of a country (movement from left to right, with decreasing mortality rates).

Plotting a multitude of countries (and genders) together may help dealing with the second question, which is whether we should assume a long-term improvement rate, and if yes, what value it should take. The plot above with a multitude of countries and both genders would suggest that there is an overall downward trend in improvements for those countries that are already well-advanced on their improvement journey. Yet, of course we do not know what will drive mortality improvements in the future, i.e. the next 30 years may be entirely different to the past.

Combined line/scatter/bar plots
These more conventional illustrations, when designed well, can convey a lot of information. One nice feature is shown in the third row: the visualisation of a multitude of densities on top of each other (here x- and y-axes are flipped, hence the curves are arranged horizontally). Such plots are sometimes called ridgeline plots.

For the example presented here, arranging mortality rates and improvements vertically helps in linking the two (steep curve in the top plot = high improvements in the bottom plot). In the top row, the combined plotting of raw data as crosses and the fitted values as lines adds a lot of transparency with respect to the fitting process. As a third row, we add the population exposure (how many people are alive in a certain year for a given age), which gives some additional information about the shift in the population structure.

Comments on the data and pre-processing
We use deaths and exposure data from the publicly available Human Mortality Database (https://www.mortality.org/). A key pre-processing step is smoothing – without it, almost nothing of interest would be visible in the plots above. We use a 2-dimensional (Year + Age) spline-smoothing approach, in the form of a general additive model (GAM) with “thin-plate” regression splines. We make use of the easy-to-use implementation of GAMs in the R-package “mgcv” (function mgcv::gam). Note that the results above can be quite sensitive to the assumptions (ages and years used, # degrees of freedom for the splines, …).

4. Applicability and Alternatives

The plots presented above can also be used in generic contexts. 

Heatmaps are commonly used whenever there is a scalar function of interest which depends on two variables (or more, but the rest of them are kept constant). As an alternative (or in addition) to colour coding, the z-coordinate can be represented by contour lines. Classical application areas are bivariate probability distributions, or fitness landscapes in optimisation problems. 

Trajectories are common whenever one studies dynamical systems (where a rate of change is interesting). One can also relax the assumption of a rate of change on the y-axis and use an independent variable as y-coordinate; these visualisations can be used as alternatives to plots with secondary axes (see https://blog.datawrapper.de/dualaxis/, Solution 4).

Scatter plots, line charts or bars form the backbone of data visualisation, and can be used in almost any context. Quite often it is worth thinking about particularly well-suited arrangements and combinations of such plots (vertical/horizontal arrangements, or using insets).

5. Implementation

See the attached R-Markdown file for the full code for this blog post, and the HTML-file for a rendered version including the interactive visualisations.

We recommend the use of R and the package ggplot2. In particular, the use of the following commands:

• geom_raster/geom_tile for heatmaps 
• geom_point, geom_path, geom_line for tractories and line/scatter plots 

We have used the ggplot2 theme from the package cowplot. Also, the arrangement of plots is facilitated by the use of cowplot (command cowplot::plot_grid). The arrangement of multiple densities/histograms in one plot (used for the population exposure above) is facilitated by the package ggridges.

For the interactive visualisations, we have used the package plotly (also available for python, e.g.). Plotly has quite a different syntax to ggplot2, and unfortunately, the two packages do not (yet) work well together.

6. Context

The context is the analysis of past mortality improvement rates, and the assumption setting for future improvement projections. The visualisations have been used in the context of country-specific analyses for pricing, and in more general background studies on the topic.

7. Tags

Trajectories
Trajectories illustrate the evolution of a variable and its rate of change, here mortality rates and annual improvement values.

Heatmap
2-dimensional representations, here used for mortality improvements as a function of age and year

Interactive
Interactive visualisations are used to facilitate the visualisation of a larger amount of data.

Visualise Daily Incidences

1 Problem Statement

A large number of incidences occurring over a long timeframe can make it challenging to spot trends.
The example shown is for daily incidence reporting over a year, but is adaptable to other timeframes.

2 Suggested Approach

We suggest a hybrid of the following techniques:

A	Listing Listing the number of incidences each day, with days arranged in a calendar format. Conditional formatting is used to identify at a glance relatively better/worse days
B.	A calendar is included alongside to enable quick references to specific dates
C.	Daily/weekly averages are shown above/beside the calendar listing
D.	Daily/weekly average charts are shown at the top

3. Rationale and Commentary

4. Applicability and Alternatives

5. Implementation

The case study was produced using Excel and an example workbook is at TODO LINK.

• The data consists of a date and number of incidences assigned to each date
• Sparklines are used to produce daily/weekly averages
• Conditional formatting is used to colour the number of incidences
• More complex conditional formatting is used to create the calendar so that other years or calendars starting on  other days can be quickly shown

6. Context

7. Tags

8. Document Control

Table