Problem description: I have an Excel sheet containing a table of true and estimated values. I want to somehow capture this information over a plot of actual (horizontal axis) vs estimated values (vertical axis). The excel sheet looks somewhat as follows:

(Link to snapshot of the table)

I am thinking of a scatter plot. But I don't know how to obtain such a plot. In this case it is not a simple scatter plot. Also, the axes need to be handled differently. If there is some other appropriate way, please feel free to suggest.

  • $\begingroup$ Wow, that's some really lousy estimation. $\endgroup$ Jul 2, 2016 at 23:39
  • $\begingroup$ Can you say more about your situation & your data? What are these? Are the values ordered (eg, alphabetical)? What was the estimation process? What do the "number of times" columns refer to? Are the values ordered (eg, alphabetical)? Etc. $\endgroup$ Jul 2, 2016 at 23:47
  • $\begingroup$ @gung: The values are not ordered. You could take A, F, J, P, T and Z as initials of names of 6 different objects. A value, say A, occurred 30 times but was detected only 10 times correctly as A, 10 times as J and 10 times as P. $\endgroup$
    – TRK
    Jul 3, 2016 at 7:05
  • $\begingroup$ @MarkL.Stone: It is lousy indeed :). $\endgroup$
    – TRK
    Jul 3, 2016 at 7:05

3 Answers 3


Since the question is looking for suggestions, here are a few possibilities, without claiming any as "best".

Building on Gung's heatmap, we can add marginal values, such as showing the percent correct for each class (and overall).

enter image description here

Some kind of Sankey / riverplot / parallel sets seems promising:

enter image description here

Gung mentions a mosaic as an option but that the usual coloring can obscure the correct/incorrect information. If you're willing to instead lose the differentiation of incorrect values, a mosaic can be useful for understanding the percent correct for each class.

enter image description here

  • 1
    $\begingroup$ My larger point was that the confusion matrix & some simple numbers are probably better than any visualization. But your modification of the mosaic plot is a pretty nice option here, I think. $\endgroup$ Jul 4, 2016 at 21:03

The problem is that the way you have the data set up isn't really appropriate for what is being represented. To start with, you have categorical data (so you won't be able to make a scatterplot). Moreover, you have actual classes and the predicted classes from some classifier. So you should represent this via a confusion matrix (i.e., a contingency table of counts for the class combinations). Often a properly set up table is enough to see what is happening in your data. Here I set one up (coded with R):

av = rep(c("A","F","J","P","T","Z"), times=c(30,40,10,20,50,10))
ev = c(rep("P",10), rep("J",10), rep("A",10), 
       rep("P",10), rep("T",10),
       rep("Z",30), rep("P",20),
       rep("T",10) )
av = as.factor(av)
ev = as.factor(ev)
print(tab <- table(ev,av), zero.print="")
#    av
# ev   A  F  J  P  T  Z
#   A 10               
#   F    40 10         
#   J 10               
#   P 10       10 20   
#   T          10    10
#   Z             30   

It may be more useful to view the conditional probabilities. Since the true categories are in columns here, we can get 'column-wise' proportions:

print(round(prop.table(tab, 2), 2), zero.print="")
#    av
# ev     A    F    J    P    T    Z
#   A 0.33                         
#   F      1.00 1.00               
#   J 0.33                         
#   P 0.33           0.50 0.40     
#   T                0.50      1.00
#   Z                     0.60     

This doesn't quite tell you the proportion correct for each category, so we can do that:

round(mapply(function(x,y){mean(y==x)}, split(ev,av), list("A","F","J","P","T","Z")), 2)
#    A    F    J    P    T    Z 
# 0.33 1.00 0.00 0.50 0.00 0.00 
sum(diag(tab))/sum(tab)         # overall proportion correct
# [1] 0.375
sum((colSums(tab)/sum(tab))^2)  # proportion correct for naive classifier
# [1] 0.21875

Your classifier does as well as 100% correct for F, and as little as 0% correct for J, T, and Z. Overall, you get 37.5% correct. A naive classifier that just assigned labels according to the marginal probability of the classes would achieve 21.9% correct, which isn't that much worse. As @MarkL.Stone notes, this classifier isn't very good.

We might wonder if this classifier is actually better than a naive version. These are a kind of agreement data. We can test if the agreement is better than chance using Cohen's kappa:

kappa2(data.frame(av=av, ev=ev))
#  Cohen's Kappa for 2 Raters (Weights: unweighted)
#  Subjects = 160 
#    Raters = 2 
#     Kappa = 0.242 
#         z = 7.06 
#   p-value = 1.73e-12 

Although the agreement isn't very good, it is clear that it's better than chance.

Once you have the data represented correctly (as a confusion matrix), and are thinking in these terms, there are various ways of trying to visualize a contingency table, if you really want a plot. Some methods, like mosaic plots, might be reasonable, but I think it will be hard to pick out the correct classifications. My first hunch would be to try a heatmap. To make it more immediately useful, I outlined the cells where correct classifications go. In addition, because your data are categorical, classes that are closer alphabetically aren't actually closer to correct, but you have a number of cases where what appears to be the adjacent category is highlighted. That makes your classifier look better than it is. So I shuffled the orderings of the labels to break that illusion:

  image(t(tab[c(1,6,4,2,5,3),c(1,6,4,2,5,3)]), axes=F, 
        xlab="Actual classes", ylab="Estimated classes", 
  axis(side=1, at=seq(0,1,.2), labels=c("A","F","J","P","T","Z")[c(1,6,4,2,5,3)])
  axis(side=2, at=seq(0,1,.2), labels=c("A","F","J","P","T","Z")[c(1,6,4,2,5,3)])
  polygon(x=c(-.1,rep(seq(.1,1.1,.2),each=2), rep(seq(.9,-.1,-.2),each=2)),
          y=c(rep(seq(-.1,1.1,.2),each=2), rep(seq(.9,.1,-.2),each=2),-.1),
          border="black", col=NA, lwd=2)

![enter image description here


Without detracting from the excellent ideas to date, I want to suggest that a scatter plot is possible, with a mapping from A F J P T Z to integers 1 to 6. If some other order makes more sense, it should be used.

Naturally point or marker symbols should be at least approximately proportional in size to the frequency in each combination. Although some would object to the repetition, there is plenty of space to show frequencies as well, in a hybrid graph and table.

In this example a thin reference line marks actual and estimated agreeing.

I tend to follow a personal convention of observed on the $y$ axis and estimated on the $x$ axis, but others may choose as they wish.

enter image description here

For the record, the Stata code below inputs data and draws the graph:


input str1(estimated actual) long(nactual nestimated) float freq
"A" "A" 1 1 10
"F" "F" 2 2 40
"F" "J" 3 2 10
"J" "A" 1 3 10
"P" "A" 1 4 10
"P" "P" 4 4 10
"P" "T" 5 4 20
"T" "P" 4 5 10
"T" "Z" 6 5 10
"Z" "T" 5 6 30

label def nactual 1 "A", modify
label def nactual 2 "F", modify
label def nactual 3 "J", modify
label def nactual 4 "P", modify
label def nactual 5 "T", modify
label def nactual 6 "Z", modify
label values nactual nactual
label values nestimated nactual

line nactual nactual, lw(vvthin) lc(gs12) ///
|| scatter nactual nactual nestimated [fw=freq], ///
ms(Sh none) mla(. freq) mlabpos(0 0) mlabsize(*1.5 *1.5) msize(*3) ///
xla(1/6, labsize(*1.2) value) yla(1/6, labsize(*1.2) value) ///
ysc(r(0.5, 6.5) titlegap(0)) xsc(alt r(0.5, 6.5)) ///
ytitle(actual, orient(horiz) size(*1.2)) ///
xtitle(estimated, size(*1.2)) ///
xla(, tlcolor(none) tlength(*.5)) /// 
yla(,tlcolor(none) tlength(*0.5) ang(h)) ///
legend(off) aspect(1) 
  • $\begingroup$ The default (alphabetical) ordering cannot help but visually imply that most classifications are 'close' to correct. I gather that isn't true (the comment at the top suggests the values are not ordered). If you want to go w/ this approach (which I do think is potentially interesting), I think you should scramble the ordering to break that illusion. But my larger point was that the confusion matrix & some simple numbers are probably better than any visualization. $\endgroup$ Jul 4, 2016 at 21:01
  • $\begingroup$ I don't see a case for jumbling the order, so long as it is understood that on the diagonal and off it are qualitatively different. $\endgroup$
    – Nick Cox
    Jul 4, 2016 at 23:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.