Merge two facet_grid plots into one with double axis in R using ggplot2 and other packages

Question

I need to find a way to merge the following face_grid-based plots (geom_col and geom_line) into one where the second y-axis in the right contains the axis values from the geom_line plot.

geom_line plot to merge into geom_col plot:

Code:

geomlineplot <- ggplot(s1, aes(year, val, colour=scenario)) +   
   geom_line(size=1) + 
   facet_grid(metric + units ~scenario, 
              scales="free", 
              space="fixed" ,
              switch = "y" #,
  ) +
   scale_x_continuous(breaks=c(2010, 2030, 2050)) +
   ylab(NULL) +
   theme_bw()+
   theme(strip.background = element_blank(),
         strip.placement = "outside",
         panel.grid = element_blank(),
         axis.text.x = element_text(color="black"),
         axis.text.y = element_text(color="black"),
         panel.grid.major.x = element_line( size=.1, color="grey", linetype = 2 ),
         panel.grid.major.y = element_line( size=.1, color="grey", linetype = 2 ),
         text = element_text(size = 14))+
   scale_fill_nejm()

geomlineplot 


geomcolplot <- ggplot(d1, aes(year, val, fill=fuel)) + 
  geom_col() + 
  facet_grid(metric + units ~scenario, 
             scales="free", 
             space="fixed" ,
             switch = "y" #,
  ) +
  scale_x_continuous(breaks=c(2010, 2030, 2050)) +
  ylab(NULL) +
  theme_bw()+
  theme(strip.background = element_blank(),
        strip.placement = "outside",
        panel.grid = element_blank(),
        axis.text.x = element_text(color="black"),
        axis.text.y = element_text(color="black"),
        panel.grid.major.x = element_line( size=.1, color="grey", linetype = 2 ),
        panel.grid.major.y = element_line( size=.1, color="grey", linetype = 2 ),
        text = element_text(size = 14))+
  scale_fill_nejm()

geomcolplot

Reproducible example in the following link

Data example

head(s)

ind scenario metric year technology units       val
1      capex    CO2 2010     boiler    kt   2.7076010
2      capex    CO2 2020     boiler    kt   3.3035754
3      capex    CO2 2030     boiler    kt -13.7159506
4      capex    CO2 2040     boiler    kt  -0.7460066
5      capex    CO2 2050     boiler    kt  -0.8332908
6 capex_opex    CO2 2010     boiler    kt   2.7076010


head(d)
  scenario metric year technology fuel units     val
1   no_gas    CO2 2010     boiler Coal  [kt] 18.32111
2   no_gas    CO2 2020     boiler Coal  [kt] 24.73643
3   no_gas    CO2 2030     boiler Coal  [kt] 39.11132
4   no_gas    CO2 2040     boiler Coal  [kt] 36.91286
5   no_gas    CO2 2050     boiler Coal  [kt] 42.62070
6   no_gas    CO2 2010     boiler  HFO  [kt] 43.45440

Both dataframes have different dimensions (dim). I believe the solution is around there.

Also, the geom_line plot does not contain values for no_gas as in the geom_col plot because the results in the first one is a comparison with no_gas values.

Edited:

I've done some data manipulation and ended up with a new data frame:

head(df_m)
  scenario metric year technology fuel units.x   value.x  value.y
1    capex    CO2 2010     boiler Coal    [kt] 11.856870 2.191325
2    capex    CO2 2010     boiler  HFO    [kt] 28.495898 2.191325
3    capex    CO2 2010     boiler  PNG    [kt] 22.314777 2.191325
4    capex    CO2 2010     boiler  LDO    [kt]  3.841344 2.191325
5    capex    CO2 2020     boiler  PNG    [kt] 38.827712 2.673661
6    capex    CO2 2020     boiler Coal    [kt] 16.006775 2.673661


ggplot(df_m, aes(year, value.x, fill=fuel)) + 
  geom_col() + 
  ## ATEMPT 1
  geom_line(aes(x=year, y=value.y*(1)), size=1, colour = "grey")+
  ## ATEMPT 2
  #geom_line(aes(x=year, y=value.y*(-1)), size=1, colour = "grey")+
  facet_grid(metric + units.x ~scenario, 
             scales="free", 
  ## ATEMPT 3
             #scales="free_y", 
             space="fixed" ,
             switch = "y" #,
  ) +
  scale_x_continuous(breaks=c(2010, 2030, 2050)) +
  scale_y_continuous(sec.axis = sec_axis(~./(1), name = "Emission savings comparison"))+
  ylab(NULL) +
  theme_bw()+
  theme(strip.background = element_blank(),
        strip.placement = "outside",
        panel.grid = element_blank(),
        axis.text.x = element_text(color="black"),
        axis.text.y = element_text(color="black"),
        panel.grid.major.x = element_line( size=.1, color="grey", linetype = 2 ),
        panel.grid.major.y = element_line( size=.1, color="grey", linetype = 2 ),
        text = element_text(size = 14),
        legend.position="bottom")+
  scale_fill_nejm()

And I got the following plots:

I cannot find a way to adjust the secondary Y axis to be different from the primary on the left. I need to show the negative values as the following:

Edition 2: Code to get df_m:

df_m <- merge(d1, savings, by = c("scenario", "metric", "year",  "technology"), all = T)
df_m  <- mutate(df_m, value.y = ifelse(is.na(value.y), 0, value.y))
df_m <- df_m %>% select(-units.y)

#savings is quite long and comes from other dataframes

Any suggestion is very welcome.