Unable to adjust the secondary axis scale in ggplot

Question

I have been trying to plot bar plot and line chart on the same plot using ggplot2 in R, However I have not been able to adjust the secondary axis. Below is how the data looks like.

Month       Domestic Visitors   Var1         Var2  Var3     Var4     Var5    Var6       Var7
2016-01-01  140018225              0          0      0        0       0        0        1500
2016-02-01  157837334              0          0      0        0       1473     0        1500
2016-03-01  153520903              0          0      0        0       114      0        1500
2016-04-01  155965472              148935   48575   11324   6075      11319    1200     19468
2016-05-01  133629040              0        74037   11324   19120     41015    1200     24468
2016-06-01  130395546              0        13500    0      12184     3311     1200     16500
2016-07-01  134910131              0          0      0        0       250      8405     1500
2016-08-01  123513886              0          0      0        0       399      8272     1500
2016-09-01  138056952              0          0      4814     0       8354     5612     5612
2016-10-01  156491556              0          0      0        0       740      7975     5724
2016-11-01  123124539              0          0      9603     0       2000     10567    8128
2016-12-01  146472839              0          0      0        0       8046     8087     4334

This is how I am trying to plot the data above

mdf <- tidyr::gather(mdf, variable, value, -Month, -one_of(colnames(mdf[2])))
ggplot() + 
  geom_bar(aes(y=mdf$value, x = as.Date(mdf$Month), fill = variable), data = mdf,stat="identity") +
  geom_line(aes(y = mdf$Domestic.Visitors, x = as.Date(mdf$Month)), data = mdf,stat="identity", lty = 2) +
  ggtitle("Weekly Spend Timeline") +
  labs(fill = "Channels") +
  scale_x_date(name = "Time Period", labels = NULL) +
  scale_y_continuous(sec.axis = sec_axis((~./max(mdf$Domestic.Visitors)))) +
  theme_grey() +
  theme(
    plot.title = element_text(color="black", size=16, face="bold", hjust = 0.5),
    axis.title.x = element_text(color="black", size=12, face="bold"),
    axis.title.y = element_text(color="black", size=12, face="bold"))

Data after gather

structure(list(Month = structure(c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 
8L, 9L, 10L, 11L, 12L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 
11L, 12L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 
1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 1L, 2L, 3L, 
4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 1L, 2L, 3L, 4L, 5L, 6L, 
7L, 8L, 9L, 10L, 11L, 12L), .Label = c("2016-01-01", "2016-02-01", 
"2016-03-01", "2016-04-01", "2016-05-01", "2016-06-01", "2016-07-01", 
"2016-08-01", "2016-09-01", "2016-10-01", "2016-11-01", "2016-12-01"
), class = "factor"), Domestic.Visitors = c(140018225L, 157837334L, 
153520903L, 155965472L, 133629040L, 130395546L, 134910131L, 123513886L, 
138056952L, 156491556L, 123124539L, 146472839L, 140018225L, 157837334L, 
153520903L, 155965472L, 133629040L, 130395546L, 134910131L, 123513886L, 
138056952L, 156491556L, 123124539L, 146472839L, 140018225L, 157837334L, 
153520903L, 155965472L, 133629040L, 130395546L, 134910131L, 123513886L, 
138056952L, 156491556L, 123124539L, 146472839L, 140018225L, 157837334L, 
153520903L, 155965472L, 133629040L, 130395546L, 134910131L, 123513886L, 
138056952L, 156491556L, 123124539L, 146472839L, 140018225L, 157837334L, 
153520903L, 155965472L, 133629040L, 130395546L, 134910131L, 123513886L, 
138056952L, 156491556L, 123124539L, 146472839L, 140018225L, 157837334L, 
153520903L, 155965472L, 133629040L, 130395546L, 134910131L, 123513886L, 
138056952L, 156491556L, 123124539L, 146472839L), variable = c("Var1", 
"Var1", "Var1", "Var1", "Var1", "Var1", "Var1", "Var1", "Var1", 
"Var1", "Var1", "Var1", "Var2", "Var2", "Var2", "Var2", "Var2", 
"Var2", "Var2", "Var2", "Var2", "Var2", "Var2", "Var2", "Var4", 
"Var4", "Var4", "Var4", "Var4", "Var4", "Var4", "Var4", "Var4", 
"Var4", "Var4", "Var4", "Var5", "Var5", "Var5", "Var5", "Var5", 
"Var5", "Var5", "Var5", "Var5", "Var5", "Var5", "Var5", "Var6", 
"Var6", "Var6", "Var6", "Var6", "Var6", "Var6", "Var6", "Var6", 
"Var6", "Var6", "Var6", "Var7", "Var7", "Var7", "Var7", "Var7", 
"Var7", "Var7", "Var7", "Var7", "Var7", "Var7", "Var7"), value = c(1500L, 
1500L, 1500L, 19468L, 24468L, 16500L, 1500L, 1500L, 5612L, 5724L, 
8128L, 4334L, 0L, 0L, 0L, 48575L, 74037L, 13500L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 11324L, 11324L, 0L, 0L, 0L, 4814L, 0L, 
9603L, 0L, 0L, 0L, 0L, 6075L, 19120L, 12184L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 1473L, 114L, 11319L, 41015L, 3311L, 250L, 399L, NA, 
740L, 2000L, 8046L, 0L, 0L, 0L, 1200L, 1200L, 1200L, 8405L, 8272L, 
8354L, 7975L, 10567L, 8087L)), row.names = c(NA, -72L), class = "data.frame")

I was hoping if some one could please help me figure out what I could be doing wrong and help to plot this data.

Thanks a lot in advance !!

Answer 1

Let's just start with a simple transformation to put two together.

ggplot() + 
  geom_bar(aes(y= value, x = as.Date(Month), fill = variable), data = mdf,stat="identity") +
  geom_line(aes(y = (Domestic.Visitors-100000000)/500, x = as.Date(Month)), data = mdf,stat="identity", lty = 2) +
  ggtitle("Weekly Spend Timeline") +
  labs(fill = "Channels") +
  scale_x_date(name = "Time Period", labels = NULL) +
  scale_y_continuous(sec.axis = sec_axis(~(.+100000000)*500)) +
  theme_grey() +
  theme(
    plot.title = element_text(color="black", size=16, face="bold", hjust = 0.5),
    axis.title.x = element_text(color="black", size=12, face="bold"),
    axis.title.y = element_text(color="black", size=12, face="bold"))

The main y axis is for bar chart of value variable and secondary y for the line Domestic.Visitors.

You can adjust those values for transformation to re-scale secondary y, for example, if you use 120000000 and 300 instead of 100000000 and 500, the graph looks like this: