Grouped Stacked Bar Chart with Line

Question

Trying to get a chart with bars stacked by different years and categories, grouped by month, and with two lines over top (one line for each year). The chart should end up something like the chart below made in Excel:

Sample data below:

df <- structure(list(cat1 = c(813392.8465, 835451.0911, 879161.0253, 
494869.9946, 354418.5719, 434533.4505, 552825.1066, 536814.0529, 
547884.7895, 599198.3688, 618577.5224, 770886.8232, 778509.3569, 
891851.6837), cat2 = c(1366565.153, 1338401.909, 1392746.975, 
644118.0054, 589076.4281, 647747.5495, 759816.8934, 810880.9471, 
973262.2105, 710650.6312, 692854.4776, 796064.1768, 734960.6431, 
756644.3163), line_data = c(3973, 3829, 3802.1, 2970.7, 1877.8, 
2378, 2578, 2378, 3245, 2955, 2893, 2999, 2950.1, 3035), Month = structure(c(1L, 
2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 1L, 2L), .Label = c("Jan", 
"Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", 
"Nov", "Dec"), class = c("ordered", "factor")), Year = structure(c(2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L), .Label = c("2019", 
"2020", "2021"), class = "factor")), row.names = c(NA, -14L), class = "data.frame")

So far I have managed to get the stacking and the grouping by using a facet wrap, but I can't get a line to cross over all the facets and display properly. If I remove the facet wrap I can get stacking or grouping with the line, but not all three at the same time. See code below

require(tidyverse)
require(lubridate)
require(reshape2)

    df <- df %>% melt(id.vars = c("Month", "Year", "line_data"), variable.name = "stack_category", value.name = "stack_category_value")
    
    ggplot(df, aes(fill = stack_category, y=stack_category_value, x=Year)) +
      geom_bar(position="stack", stat="identity", width = .9) +
      facet_wrap(~Month, nrow=1, strip.position = "bottom") +
      geom_line(aes(y = 625*line_data, group = Year, colour = Year), size = 1.5) +
      scale_y_continuous(sec.axis = sec_axis(~./625, name = "line_data")) +
      labs(y = "stack_category_value", x = NULL, title = "Category and Line Data", fill = "Category Data", colour = "Line Data") +
      theme(legend.position = "bottom", plot.title = element_text(size = 20, face= "bold", hjust = .5)) +
      theme(strip.background = element_blank(), strip.placement = "outside", panel.spacing = unit(0, "mm")) +
      theme(panel.background = NULL, panel.grid = element_line(linetype = "blank"), panel.grid.major.y = element_line(linetype = "solid", color = "black"))

Getting a line to go over all the facets and look continuous seems like it would be a non-starter, but I'm not sure how to elegantly achieve the grouping and stacking otherwise. So far my chart looks like this:

Code Output Example

Please review how to create a [reproducible](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) example. Specifically, can you include a sample of your data using `dput` so we can run your code — Conor Neilson, Mar 17 '21 at 04:12
You definitely don't want to use facets here. There's a far easier way using grouping and dodging, but we'll need you to share some data. `dput` is the easiest way, and then paste the resulting output as code in the body of your question. — chemdork123, Mar 17 '21 at 20:29
Added sample data and full theming code, thanks guys, did not know about `dput`! — Sean M, Mar 18 '21 at 14:05

Grouped Stacked Bar Chart with Line

0 Answers0