Adding a second y-axis and line over a bar plot

Question

Following is my data

df <- data.frame(Lab = c("Queen II", "MMH", "Berea", "Maluti", "Motebang"),
             Expected = c(13200, 5280, 5280, 2640, 5280),
             Actual = c(8759, 761, 2263, 2210, 6100),
             utili_pct = c(66.35, 14.41, 42.86, 83.71, 115.53))

and I have tried to plot a bar chat which includes a line over the chart.

step 1

# I Converted numeric variable "Actual" to a factor 
df$Actualx <- as.factor(df$Actual)

This was so that I could plot a chart with two-factor variables vs one numeric So I tidy the data and ran the plot this way but the axis scale became no ordered.

tidy_Data = df %>% gather(key, value, Actualx, Expected)

ggplot(tidy_Data, aes(x=Lab, y=value, fill=key)) +
         geom_bar(stat = "identity", position = position_dodge(0.8)) `

Futhermore, I tried to add a line utili_ptc and the second axis, but the scale is giving me a hard time, The line does not align with the bars.

ggplot(tidy_Data, aes(x=Lab, y=value, fill=key)) +
   geom_bar(stat = "identity", position = position_dodge(0.8)) + 
   geom_line(aes(x=Lab, y=utili_pct), color = "green", group = 1)

Answer 1

Thank you very very much for your guidance. I have finally been able to plot the chart the way I wanted using your code.

library("tidyverse")

library("ggplot2")

library("gtable")

library("grid")

df <- data.frame(Lab = c("Queen II", "MMH", "Berea", "Maluti", "Motebang"), Actual = c(8759, 761, 2263, 2210, 5100), utili_pct = c(66.35, 14.41, 42.86, 83.71, 96.59), Expected = c(13200, 5280, 5280, 2640, 5280),stringsAsFactors = F)

tidy the data

tidy_Data <- df%>% gather(key,value, Actual, Expected)

grid.newpage()

two plots

p1 <- ggplot(tidy_Data, aes(x=Lab, y=value, fill=key)) + geom_bar(stat = "identity", position = position_dodge(0.8)) + theme(legend.position = "bottom")

p2 <- ggplot(df, aes(x=1:5, y=utili_pct)) + geom_line() + ylim(10,100) + theme_bw() + theme(panel.background = element_rect(fill = NA))

extract gtable

g1 <- ggplot_gtable(ggplot_build(p1)) g2 <- ggplot_gtable(ggplot_build(p2))

Overlap the panel of the 2nd plot on that of the 1st plot

pp <- c(subset(g1$layout, name == "panel", se = t:r)) g <- gtable_add_grob(g1, g2$grobs[[which(g2$layout$name == "panel")]], pp$t,pp$l, pp$b, pp$l)

axis tweaks

ia <- which(g2$layout$name == "axis-l") ga <- g2$grobs[[ia]] ax <- ga$children[[2]] ax$widths <- rev(ax$widths) ax$grobs <- rev(ax$grobs) ax$grobs[[1]]$x <- ax$grobs[[1]]$x - unit(1, "npc") + unit(0.15, "cm") g <- gtable_add_cols(g, g2$widths[g2$layout[ia, ]$l], length(g$widths) - 1) g <- gtable_add_grob(g, ax, pp$t, length(g$widths) - 1, pp$b)

draw it

grid.draw(g)

Answer 2

There's a post about why using a seconday y-axis is frowned upon here

ggplot with 2 y axes on each side and different scales

To answer your specific question, I did a quick search and found this post and used it construct your diagram. Please have a look at it and see if you can understand

https://rpubs.com/kohske/dual_axis_in_ggplot2

  library(ggplot2)
  library(gtable)
  library(grid)

  grid.newpage()

  # two plots
  p1 <- ggplot(tidy_Data, aes(x=Lab, y=value, fill=key)) +
        geom_bar(stat = "identity", position = position_dodge(0.8)) +
        theme(legend.position = 'top')

  p2 <- ggplot(tidy_Data, aes(x = 1:10, y = utili_pct)) + geom_line() + 
        theme_bw() +
        theme(panel.background = element_rect(fill = NA))

  g1 <- ggplot_gtable(ggplot_build(p1))
  g2 <- ggplot_gtable(ggplot_build(p2))            

  pp <- c(subset(g1$layout, name == "panel", se = t:r))
  g <- gtable_add_grob(g1, g2$grobs[[which(g2$layout$name == "panel")]], pp$t, 
                       pp$l, pp$b, pp$l)

  # axis tweaks
  ia <- which(g2$layout$name == "axis-l")
  ga <- g2$grobs[[ia]]
  ax <- ga$children[[2]]
  ax$widths <- rev(ax$widths)
  ax$grobs <- rev(ax$grobs)
  ax$grobs[[1]]$x <- ax$grobs[[1]]$x - unit(1, "npc") + unit(0.15, "cm")
  g <- gtable_add_cols(g, g2$widths[g2$layout[ia, ]$l], length(g$widths) - 1)
  g <- gtable_add_grob(g, ax, pp$t, length(g$widths) - 1, pp$b)

 # draw it
 grid.draw(g)