R rescale 2nd Y-axis according to a variable

I have been trying to get a histogram and a line graph into one graph. The histogram is for exact amount of an antibiotic prescription (left y-axis). The line graph is the percentage of how much that antibiotic is used (right axis). So far, I have merged them together, moved the right axis to the right, but the right y-axis uses the same scale as the left. The scales of the histogram and the line graph are of course very very different.

Some information before my question starts:

dataframe name = Grafiek3
n_fosfo = the amount of perscriptions (used for the left y-axis)
Jaren = years, the amount and percentage are visible per year (used for the x-axis)
per_fos = percentage (used for the right y-axis), goes to approximately 11%

The code that I have now is:

  ggplot(Grafiek3, aes(x = Jaren, y =n_fosfo)) + #samenvoegen
geom_bar(stat="identity", fill="#69b3a2" ) +
scale_x_continuous(labels = as.character(Grafiek3$Jaren), breaks = Grafiek3$Jaren) +
xlab ("Year") +
theme(plot.title = element_text(hjust = 0.5)) +
ggtitle("Increase fosfomycin prescriptions per year")+
geom_line(aes(x=Jaren, y=per_fos), color="black") +
geom_point(aes(x=Jaren, y=per_fos), shape=21, color="black", fill="#69b3a2", size=2) +
scale_y_continuous(
  name = expression("Total amount of prescriptions"), 
  sec.axis = sec_axis(~., name = "Percentage")) +
theme(plot.title = element_text(hjust = 0.5)) +
ggtitle("Percentage of fosfomycin prescriptions per year")

Output is as follows: Unfinished graph

As is seen, the line graph sits at the bottom, but should look something like this:

  ggplot(Grafiek3, aes(x=Jaren, y =per_fos)) + #line graph
  geom_line(color="grey") +
  geom_point(shape=21, color="black", fill="#69b3a2", size=2) +
  scale_x_continuous(labels = as.character(Grafiek3$Jaren), breaks = Grafiek3$Jaren) +
  scale_y_continuous(position = "right") +
  xlab ("Year") +
  ylab ("Percentage") +
  theme(plot.title = element_text(hjust = 0.5)) +
  ggtitle("Percentage of fosfomycin prescriptions per year")

Picture: Line graph

I have seen that changing the code:

sec.axis = sec_axis(~., name = "Percentage"))

into something like this:

sec.axis = sec_axis(~./42, name = "Percentage"))

Will fix it, but unfortunately that changes only the right axis, but the line graph doesn't move with it.

So, does anybody have an idea how I can move the line graph (picture) into the histogram? Or how to change the right axis so that it uses the information of the "per_fos" variable?

Made up dataset that can be of use:

years <- c(2013, 2014, 2015, 2016, 2017, 2018, 2019)
amount <- c(120, 150, 200, 170, 180, 240, 80)
percentage <- c(5.4, 5.9, 6.3, 7.1, 7.8, 8.4, 10.4)
df <- data.frame(years, amount, percentage)

Solution

Just to let you know that two y-axis with different scales is not considered good practice. This is why it is not easy to do what you are trying to do with ggplot2. See this link for a good discussion on the issue: ggplot with 2 y axes on each side and different scales



library(ggplot2)
library(dplyr)
library(scales)


years <- c(2013, 2014, 2015, 2016, 2017, 2018, 2019)
amount <- c(120, 150, 200, 170, 180, 240, 80)
percentage <- c(5.4, 5.9, 6.3, 7.1, 7.8, 8.4, 10.4)

df <- data.frame(years, amount, percentage)
# create a modified percentage column to fit in with the scale of the columns:

df <- 
  df %>% 
  mutate(pc_rescale = rescale(percentage, to = c(20, 200)))

# plot and add suitable labels; if you want the labels scaled evenly you need to unpick the rescaling function. 

ggplot(df, aes(years, amount)) +
  geom_col()+
  geom_line(aes(years, pc_rescale))+
  scale_y_continuous(sec.axis = sec_axis(~., labels = df$percentage, breaks = df$pc_rescale, name = "Percentage" ))

^{Created on 2020-05-21 by the reprex package (v0.3.0)}