12 June 2017 |
The Magic Trick that Highlights Interesting Results on Any Table
This post describes the single biggest time saving technique that I know about for highlighting significant results on a table. The table below, which shows the results of a MANOVA, illustrates the trick. The coloring and bold fonts on the table quickly draw the eye to the two key patterns in the results: people aged 50 or more have lower averages, and the result is strongest for Intel. This is revealed by the coral-colored shading and the bolding.
The same principles can be applied to any table containing numbers. The table below shows people’s attitude towards Microsoft by age. The attitude is split into three categories: Detractors, Neutrals, and Promoters. What story does it tell? If you are diligent you can work it out. But, it is hard work to do so because it requires you to compare and contrast 15 different numbers on the table.
Now look at the table below. The colors and arrow tell us people aged 50 or more are less likely to be Promoters than people in the other age groups. Sure we could get to this insight by reading the first table and doing a whole lot of significance tests. But, the simple use of coloring and an arrow makes the key result much easier to find.
Formatting the table to emphasize differences in residuals is the basic principle used in both examples illustrated above.
A simple approach to computing residuals
How do we work out which cells are interesting automatically? To illustrate this, I will walk through the calculations used in the table showing attitude towards Microsoft by age, returning to more complex analyses later in this post.
We start with a table showing the total percentages (i.e., the number of people in each cell of the table, divided by the total sample size). With this table, our cell showing Promoters aged 50 or more shows a value of 3%. Is this result interesting?
To answer this question we should look at the TOTALs. We can see that 27% is the TOTAL percentage for 50 or more. So, all else being equal we should expect that 27% of Promoters will be aged 50 or more. As 21% of the respondents are Promoters, we would then expect, all else being equal, that the percentage that are Promoters aged 50 or more will be 27% * 21% = 6%. The actual percentage shown on the table is only 3%, so we can conclude that the proportion of people that are Promoters and aged 50 or more is roughly half what we would expect if there was no relationship between age and attitude.
The 6% value that I computed above for Promoters aged 50 or more is called the expected value. It is an estimate of the percentage of people in the sample who would be Promoters aged 50 or more, in the event that age and attitude are unrelated. The table below shows the expected values for all the cells in the table. That is, the values computed by multiplying together all the row and column totals for each cell. It is showing 5.6% rather than 6% for the Promoters aged 50 or more as I have used all the decimal places in the data when performing the calculations.
This next table shows the beginning of the magic. Now I have subtracted the expected values from the actual (observed) percentages . The resulting table shows the residuals (residuals = observed – expected values). Cells close to 0% tend to be uninteresting.
Using statistical significance to save more time: standardized residuals
The residuals are difficult to correctly interpret. They have two problems. First, how big is “big”? Second, and less obviously, it is often not appropriate to compare them. We can readily see both of these problems by looking at the residual of -2.6 for Neutral and 30 to 49. This is the biggest residual on the table. It is bigger than the residual for Promoters aged 50 or more. However, the expected value for this the Neutrals aged 30 to 49 is much closer to 50% than it is for the Promoters aged 50 or more. If you have studied your introductory stats, you will recall that this means that there is more uncertainty about the estimate (i.e., a higher sampling error).
There is a simple solution to this problem: scale the residuals so that they show statistical significance. This is straightforward to do (to see the underlying R code, click here to go into Displayr and click on the tables). These standardized residuals are z-scores. This means that values of more than 1.96, or less than -1.96, are significant at the 0.05 level. The only cell in this table that is significant at the 0.05 level is Promoters in the 50 or more age group. This tells us that this is the first cell we should look at when exploring the table.
The standardized residuals provide two types of information that allow us to quickly see patterns on a table. First, we have the standardized residuals themselves. On the table below, negative residuals are shaded in coral and positive values in blue, with the degree of shading proportional to the values. The second bit of information is the statistical significance of the cells (e.g., whether the z-scores are significant at the 0.05 level). This has been shown via bolding.
The table below uses the standardized residuals in a slightly different way:
- Arrow lengths are proportional to to the standardized residuals.
- Arrows only appear when the residual is significant.
- The color of the fonts and the arrows indicates whether the residuals are positive or negative.
- The order of the rows and columns follows the values of the standardized residuals (for Google and Fun).
The net effect of these formatting choices is that it becomes relatively easy to extract key conclusions from the table. For example, Apple does very well on Stylish, but less so on the price-related dimensions.
Techniques for other types of data
The “magic” consists of two separate things: (1) emphasizing differences in residuals and (2) statistical significance. There are lots of different ways to achieve these outcomes.
The basic method for calculating the residuals illustrated above is technically only “correct” for tables with mutually exclusive categorical variables in the rows and columns. However, it is widely used with pretty much all types of data and does a good job. However, there are no shortage of alternative approaches. For example, in the MANOVA example the residuals are t-statistics. The table immediately above uses a log-linear model to compute the expected values.
Computing statistical significance
The method to use to compute statistical significance depends both on the data and the assumptions you want to make. Provided that you can compute the standard error for every cell on the table, you can compute a z- or t-statistic by dividing the residual by the standard error. You can of course get more rigorous. In the MANOVA example above, for example, I have computed robust standard errors and applied the false discovery rate correction.
For this post, I calculated and formatted the standardized residuals and the MANOVA using R (the formatting uses the formattable package). Click here to go into Displayr, then click on the MANOVA output table in Displayr and you will see the R CODE.
To create the two tables that show standardized residuals with arrows and font colors, I used Displayr, which automatically computes standardized residuals on all tables, and formats them in this way.
Author: Tim Bock
Tim Bock is the founder of Displayr. Tim is a data scientist, who has consulted, published academic papers, and won awards, for problems/techniques as diverse as neural networks, mixture models, data fusion, market segmentation, IPO pricing, small sample research, and data visualization. He has conducted data science projects for numerous companies, including Pfizer, Coca Cola, ACNielsen, KFC, Weight Watchers, Unilever, and Nestle. He is also the founder of Q www.qresearchsoftware.com, a data science product designed for survey research, which is used by all the world’s seven largest market research consultancies. He studied econometrics, maths, and marketing, and has a University Medal and PhD from the University of New South Wales (Australia’s leading research university), where he was an adjunct member of staff for 15 years.