I have a small dataset that shows the number of young patients in a disease registry is increasing over time. I suspect that this is just because the registry has become more successful over time and now captures a greater proportion of cases.

I would therefore like to plot the number of young patients in the registry each year, e.g. on a line graph, alongside the total number of patients (i.e. all ages) included within the registry each year and demonstrate whether or not

I have done this crudely in Excel and the trends are not identical. I would therefore like to demonstrate whether or not the trends are consistent with each other statistically/graphically. Can anyone suggest a good way to do this using either Stata or Excel?

Data sample

  • $\begingroup$ Is your question really asking "how do I tell if a proportion is changing over time"? $\endgroup$ – Silverfish Aug 7 '17 at 14:10
  • $\begingroup$ Have you looked into the dynamic time warping algorithm? $\endgroup$ – Bruno Wu Aug 8 '17 at 22:30

Because the variance in a count or proportion tends to be proportional to the count or proportion itself, theory (and much experience) suggest analyzing the square roots of the data.

See for yourself by plotting the proportions and overall counts on square-root axes.


So that each column has a visual impact directly proportional to the count it represents, the column widths (as well as their heights) are also proportional to the square roots of the counts: this makes the areas of the columns directly proportional to the counts. The columns are only lightly drawn because they are of secondary interest in this visualization of proportions, as the title states.

The apparently random variation of points (representing the proportions) around their smooth (shown as the blue line), as well as the approximate symmetry of that variation around the smooth, attest to the appropriateness of the square root scale. They also suggest that a more sophisticated analysis of temporal correlation is unnecessary: you can be confident that the trends you see in this plot are real. They present a subtler picture than suggested in the question: the proportions do increase, but only through the first seven years.

Creating such a combined plot can be done in Excel or Stata, but is difficult, fussy, and time-consuming in either program. This example was produced with the ggplot2 package in R (version 3.4.0).

To illustrate the process, here is the full R code.

X <- data.frame(Year=2003:2016,
scale.dup <- 0.5e6 # Proportional to column heights in the plot
ggplot(X, aes(Year, 100 * scale.dup * Young/All)) +
  geom_col(aes(Year, All, width=2.25*sqrt(All/scale.dup)),
           fill="#ffffe0", alpha=0.75, color="Gray") +
  geom_smooth(size=1.25) +
  geom_point(size=2) +
  ylab("All") +
  scale_y_continuous(sec.axis=dup_axis(~. / scale.dup, "Young / All (%)"), trans="sqrt") +
  ggtitle("Disease Registry Patient Proportions", "2003 - 2016 (square root scales)")
  • $\begingroup$ That's wonderful - thank you. I only have a passing acquaintance with R. Would you be willing to share the code you used to produce that figure or a direction to a focussed help resource that might help me achieve something similar? $\endgroup$ – MonteCristo Aug 8 '17 at 11:50
  • $\begingroup$ Can you expand on why you use square roots? I tried remaking with linear scales (I just removed the two references to sqrt) and the shape of both the yellow bars and the blue line remain the same, so it seems like you would draw the same conclusion. $\endgroup$ – Darren Cook Aug 14 '17 at 17:55
  • $\begingroup$ @Darren, counted data typically follow Binomial distributions. Counts that are small fractions of a total will therefore have variances that are close to the counts themselves. The square root is the variance-stabilizing transformation in such cases: that is, the amount of likely (vertical) variation in the plot will be the same on a square root scale, regardless of how the counts might vary, whereas the amounts will vary on a linear scale. Achieving such homoscedasticity is helpful in exploratory analysis and in choosing statistical procedures. $\endgroup$ – whuber Aug 14 '17 at 17:59
  • $\begingroup$ @whuber Thanks. I still don't really get it, but I'll try googling around binomial distributions and see if enlightenment comes. :-) $\endgroup$ – Darren Cook Aug 14 '17 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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