How can I add these data points clearly to a chart without them looking the way they do?

Question

I have two data frames (dput()s at the end of this question) that I wish to plot onto the same chart.

I want to be able to show first and second appointment numbers on any given date (columns), as well as the number of each type of vaccination given on each date, broken down by location. I have performed a count of the original data (using dplyr), but I think that by plotting by site per day, it has caused my chart to show stacked values rather than a single/total value:

I highly suspect that my approach to this is wrong, which is what is causing columns and lines to look the way they do; it just seems wrong on a number of levels.

I think that the columns are broken down into segments (because they are a combination of numerous values), all stacked on top of one another, and I believe that the same is true of the line too.

In terms of the line, there is clearly something wrong as it appears to jump around from one column to the next; there is no smooth/flowing transition. I have split the data down by single day values, yet this still happens.

(I have added bold colours for the sake of this example; this chart is not in its final form.)

I have tried using merge to combine the datasets but am still receiving the same results; I am sure that there is a better way to do this.

Any advice would be great.

Code to merge data frames:

merged <- merge(df, df2, by = 1)
colnames(merged)[1] <- "apptDTS" # Change first column name

Code for chart:

ggplot(merged) +
geom_col(aes(apptDTS, n.x), fill = "yellow", colour = "black") +
geom_col(aes(apptDTS, n.y), fill = "blue", colour = "black") +
geom_line(aes(x = apptDTS, y = n.x),
          colour = "green") +
geom_line(aes(x = apptDTS, y = n.y),
          colour = "red")

dputs:

df <- structure(list(FirstApptDTS = structure(c(1609718400, 1609718400, 
1609718400, 1609718400, 1609804800, 1609804800, 1609804800, 1609804800, 
1609891200, 1609891200, 1609891200, 1609891200, 1609977600, 1609977600, 
1609977600, 1609977600, 1610064000, 1610064000, 1610064000, 1610064000, 
1610150400, 1610150400, 1610150400, 1610150400, 1610409600, 1610409600, 
1610409600, 1610409600, 1610409600, 1610496000, 1610496000, 1610496000, 
1610496000, 1610496000, 1610582400, 1610582400, 1610582400, 1610582400, 
1610582400, 1610668800, 1610668800, 1610668800, 1610668800, 1610668800, 
1610755200, 1610755200, 1610755200, 1610755200, 1610755200, 1610928000, 
1610928000, 1610928000, 1610928000, 1610928000, 1610928000, 1611014400, 
1611014400, 1611014400, 1611014400, 1611014400, 1611014400, 1611100800, 
1611100800, 1611100800, 1611100800, 1611100800, 1611100800, 1611187200, 
1611187200, 1611187200, 1611187200, 1611187200, 1611273600, 1611273600, 
1611273600, 1611273600, 1611273600, 1611360000, 1611360000, 1611360000, 
1611360000, 1611360000, 1611360000, 1611532800, 1611532800, 1611532800, 
1611532800, 1611532800, 1611532800, 1611532800, 1611619200, 1611619200, 
1611619200, 1611619200, 1611619200, 1611705600, 1611705600, 1611705600, 
1611705600, 1611705600, 1611792000, 1611792000, 1611792000, 1611792000, 
1611792000, 1611878400, 1611878400, 1611878400, 1611878400, 1611878400, 
1611964800, 1611964800, 1611964800, 1611964800, 1611964800), class = c("POSIXct", 
"POSIXt"), tzone = ""), firstSiteLocation = c("GHGA", "LBVC1", 
"STHSTVC", "STHSTVC", "GHGA", "LBVC1", "STHSTVC", "STHSTVC", 
"GHGA", "LBVC1", "STHSTVC", "STHSTVC", "GHGA", "LBVC1", "STHSTVC", 
"STHSTVC", "GHGA", "LBVC1", "STHSTVC", "STHSTVC", "GHGA", "LBVC1", 
"STHSTVC", "STHSTVC", "GHGA", "LBVC1", "LBVC2", "STHSTVC", "STHSTVC", 
"GHGA", "LBVC1", "LBVC2", "STHSTVC", "STHSTVC", "GHGA", "LBVC1", 
"LBVC2", "STHSTVC", "STHSTVC", "GHGA", "LBVC1", "LBVC2", "STHSTVC", 
"STHSTVC", "GHGA", "LBVC1", "LBVC2", "STHSTVC", "STHSTVC", "GHGA", 
"LBVC1", "LBVC2", "STHSTVC", "STHSTVC", "WBVC1", "GHGA", "LBVC1", 
"LBVC2", "STHSTVC", "STHSTVC", "WBVC1", "GHGA", "LBVC1", "LBVC2", 
"STHSTVC", "STHSTVC", "WBVC1", "GHGA", "LBVC1", "LBVC2", "STHSTVC", 
"WBVC1", "GHGA", "LBVC1", "LBVC2", "STHSTVC", "WBVC1", "GHGA", 
"LBVC1", "LBVC2", "STHSTVC", "STHSTVC", "WBVC1", "GHGA", "LBVC1", 
"LBVC2", "STHSTVC", "STHSTVC", "VC2", "WBVC1", "GHGA", "LBVC1", 
"LBVC2", "STHSTVC", "WBVC1", "GHGA", "LBVC1", "LBVC2", "STHSTVC", 
"WBVC1", "GHGA", "LBVC1", "LBVC2", "STHSTVC", "WBVC1", "GHGA", 
"LBVC1", "LBVC2", "STHSTVC", "WBVC1", "GHGA", "LBVC1", "LBVC2", 
"STHSTVC", "WBVC1"), VaccineTypeCD = c("DEF", "DEF", "ABC", "DEF", 
"DEF", "DEF", "ABC", "DEF", "DEF", "DEF", "ABC", "DEF", "DEF", 
"DEF", "ABC", "DEF", "DEF", "DEF", "ABC", "DEF", "DEF", "DEF", 
"ABC", "DEF", "DEF", "DEF", "DEF", "ABC", "DEF", "DEF", "DEF", 
"DEF", "ABC", "DEF", "DEF", "DEF", "DEF", "ABC", "DEF", "DEF", 
"DEF", "DEF", "ABC", "DEF", "DEF", "DEF", "DEF", "ABC", "DEF", 
"DEF", "DEF", "DEF", "ABC", "DEF", "DEF", "DEF", "DEF", "DEF", 
"ABC", "DEF", "DEF", "DEF", "DEF", "DEF", "ABC", "DEF", "DEF", 
"DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", 
"DEF", "DEF", "DEF", "DEF", "ABC", "DEF", "DEF", "DEF", "DEF", 
"DEF", "ABC", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", 
"DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", 
"DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", 
"DEF", "DEF", "DEF"), n = c(134L, 283L, 3L, 10L, 122L, 120L, 
18L, 128L, 148L, 534L, 481L, 22L, 151L, 520L, 529L, 7L, 174L, 
539L, 535L, 3L, 185L, 540L, 494L, 3L, 91L, 321L, 491L, 12L, 495L, 
82L, 329L, 493L, 6L, 534L, 86L, 423L, 517L, 2L, 496L, 111L, 394L, 
505L, 2L, 498L, 401L, 547L, 518L, 2L, 362L, 443L, 481L, 555L, 
1L, 524L, 153L, 446L, 452L, 493L, 1L, 426L, 288L, 472L, 463L, 
558L, 1L, 381L, 317L, 491L, 592L, 610L, 566L, 471L, 496L, 606L, 
615L, 572L, 561L, 472L, 564L, 557L, 1L, 577L, 584L, 534L, 598L, 
570L, 1L, 594L, 1L, 553L, 492L, 581L, 570L, 610L, 573L, 484L, 
580L, 575L, 571L, 554L, 482L, 590L, 596L, 533L, 395L, 489L, 570L, 
606L, 486L, 413L, 495L, 497L, 538L, 441L, 264L)), row.names = c(59L, 
61L, 63L, 64L, 66L, 68L, 70L, 71L, 73L, 74L, 76L, 77L, 79L, 81L, 
83L, 84L, 86L, 88L, 90L, 91L, 93L, 95L, 97L, 98L, 109L, 111L, 
113L, 115L, 116L, 118L, 120L, 122L, 124L, 125L, 127L, 129L, 131L, 
133L, 134L, 136L, 138L, 140L, 142L, 143L, 145L, 147L, 149L, 151L, 
152L, 154L, 156L, 158L, 160L, 161L, 163L, 165L, 167L, 169L, 171L, 
172L, 174L, 176L, 178L, 180L, 182L, 183L, 185L, 187L, 189L, 191L, 
193L, 195L, 197L, 199L, 201L, 203L, 205L, 207L, 209L, 211L, 213L, 
214L, 216L, 218L, 220L, 222L, 224L, 225L, 228L, 229L, 231L, 233L, 
235L, 237L, 239L, 241L, 243L, 245L, 247L, 249L, 251L, 253L, 255L, 
257L, 259L, 261L, 263L, 265L, 267L, 269L, 271L, 273L, 275L, 277L, 
279L), class = "data.frame")

and

df2 <- structure(list(SecondApptDTS = structure(c(1609545600, 1609804800, 
1609891200, 1609977600, 1610064000, 1610150400, 1610409600, 1610409600, 
1610496000, 1610496000, 1610496000, 1610582400, 1610582400, 1610668800, 
1610668800, 1610668800, 1610755200, 1611014400, 1611187200, 1611705600, 
1611878400, 1611964800, NA), class = c("POSIXct", "POSIXt"), tzone = ""), 
    secondSiteLocation = c("GHGA", "GHGA", "GHGA", "GHGA", "GHGA", 
    "GHGA", "GHGA", "LBVC1", "GHGA", "LBVC1", "STHSTVC", "GHGA", 
    "LBVC1", "GHGA", "LBVC1", "LBVC2", "GHGA", "LBVC1", "GHGA", 
    "GHGA", "STHSTVC", "GHGA", NA), VaccineType2CD = c("DEF", 
    "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", 
    "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", 
    "DEF", "DEF", "DEF", NA), n = c(1L, 1L, 254L, 199L, 274L, 
    269L, 325L, 157L, 284L, 197L, 2L, 295L, 123L, 257L, 123L, 
    1L, 1L, 1L, 4L, 2L, 1L, 3L, NA)), row.names = c("24", "28", 
"31", "34", "37", "40", "47", "49", "51", "53", "55", "57", "59", 
"62", "64", "66", "67", "68", "73", "75", "77", "78", "NA"), class = "data.frame")

user_3817004 · Answer

If I understand correctly, the OP wants to show

first and second appointment numbers on any given date
the number of each type of vaccination given on each date
broken down by location.

However, I am not sure I have fully understood the requirements. So, my answer might nned to be adjusted on OP's feedback.

Here is what I would do with my preferred tools (I am more familar and faster with data.table than with dplyr). Most important, I do not merge() but rbind() both input datasets with an id column for first and second appointment.

library(data.table)
library(magrittr)
cols <- c("appDTS", "siteLocation", "vaccineType", "n")
combi <- list(df, df2) %>% 
  lapply(setDT) %>% 
  lapply(setnames, cols) %>% 
  rbindlist(idcol = "appt") %>%
  .[, appt := factor(appt, labels = c("First", "Second"))]

# 1st plot
ggplot(combi) + 
  aes(appDTS, n, fill = appt) + 
  geom_col() +
  scale_fill_brewer(palette = "Paired")

# 2nd plot
ggplot(combi) + 
  aes(appDTS, n, fill = vaccineType) + 
  geom_col() +
  scale_fill_brewer(palette = "Accent")

# 3rd plot
ggplot(combi) + 
  aes(appDTS, n, fill = siteLocation) + 
  geom_col()

Note that I have choosen a different palette for each plot to visualize that different variables are colour coded.

EDIT

The OP has clarified:

I would like to have a single plot which shows the date on the x-axis, a count on the y-axis, complete with bars and also two lines indicating how many of each vaccination was given on each day.

In order to plot the number of vaccines given on each day, we need to further aggregate the data. With data.table this is done by

combi[!is.na(n), .(n = sum(n)), by = .(appDTS, vaccineType)]

Now, the plot with lines overlay can be created by

ggplot(combi) + 
  aes(appDTS, n, fill = appt) + 
  geom_col() +
  scale_fill_brewer(palette = "Paired") + 
  geom_line(
    aes(appDTS, n, colour = vaccineType),
    data = combi[!is.na(n), .(n = sum(n)), by = .(appDTS, vaccineType)],
    inherit.aes = FALSE, size = 1) +
  scale_color_brewer(palette = "Set1")

inherit.aes = FALSE is required to avoid an error message due to the appt variable (which was mapped on the fill aesthetics) is missing from the aggregated dataset.

How can I add these data points clearly to a chart without them looking the way they do?

1 Answers

EDIT