plot with two Y-axis of different scales

Question

I'm trying to build a plot in R with 2 Y-axis. It is a rather regular task in Excel, but a good adventure in R.

So, here is my dataset:

Date	Latvia	Lithuania	Poland	Russian Federation	Ukraine
2012	77.21	67.97	72.97	71.41	148.29
2013	75.40	65.62	72.83	71.03	149.45
2014	75.10	63.86	71.55	72.95	153.65
2015	68.77	59.54	65.86	71.61	162.92
2016	64.80	55.83	62.14	69.70	154.51
2017	63.81	54.14	60.80	70.29	153.99
2018	62.88	53.31	59.62	70.82	153.07
2019	62.36	51.94	58.12	71.18	150.26
2020	63.89	51.69	58.94	73.00	154.26
1Q2021	65.36	51.25	57.78	72.69	156.25

I need to build a chart with 5 lines:

• data points for Latvia, Lithuania, Poland and Russia on one Y axis,
• data points for Ukraine - on another.

I use the following part of the code to read and prepare the data for the chart:

1. Rear data from the Excel file:

data_3.2 <- read.xlsx(
"BEO_charts.xlsx", 
sheet = 22,
rows = c(25:33),
cols = c(2:7),
colNames = FALSE,
skipEmptyRows = TRUE,
skipEmptyCols = TRUE,
detectDates = TRUE
)

colnames(data_3.2) <- c(
  "date",
  "Latvia",
  "Lithuania",
  "Poland",
  "RF",
  "Ukraine"
)

2. Then I prepare a dataset for the part of the chart with 4 data points for the primary Y axis:

p3.2left <- 
  subset.data.frame(
  data_3.2, 
  select = c(
    "date",
    "Latvia",
    "Lithuania",
    "Poland",
    "RF")
  ) %>% 
  
  melt(
    id.vars = 'date', 
    variable.name = "GDP_var",
    value.name = "GDP_val",
    measure.vars = c(
      "Latvia",
      "Lithuania",
      "Poland",
      "RF")
    ) %>% 
  
  ggplot(
    aes (
      x = date, 
      y= GDP_val,
      group = GDP_var,
      colour = GDP_var
      )
    ) +
  scale_y_continuous(limits = c(50,80), breaks = seq(50,80,5), expand = c(0.025,0), position = "left")+
  #scale_x_date(date_breaks = )
  labs (x = "", y = "") +
  geom_line(size = 1)+ 
  scale_colour_manual (
    guide = "legend", 
    name = NULL,
    breaks = c(
      "Latvia",
      "Lithuania",
      "Poland",
      "RF"),
    labels = c(
      "Латвия (левая ось)", 
      "Литва (левая ось)",
      "Польша (левая ось)",
      "Россия (левая ось)"), 
    values = c(
      "#332288", 
      "#88CCEE",
      "#44AA99",
      "#117733"
      )) +
  theme(
    axis.text.x = element_text(angle = 90, vjust = .5, size = 5, colour = "black"),
    axis.text.y = element_text(size = 5, colour = "black"),
    panel.background = element_rect(fill = NA),
    panel.grid = element_blank(),
    axis.line.y = element_line(colour= "#ABABAB"),
    axis.ticks.length = unit(0,"cm"),
    axis.title.y = element_text(size = 5, angle = 90, colour = "black", margin = margin(t = 0, r = 0, b = 0, l = 0)),
    legend.position = "bottom",
    legend.direction = "vertical",
    legend.title = element_blank(),
    legend.spacing.x = unit(0,"cm"),
    legend.key = element_blank(),
    legend.key.height = unit(.5, "cm"),
    legend.text = element_text(size = 5),
    legend.background = element_rect(fill = "transparent", colour = NA),
    legend.box.margin = unit (c(-9,1,1,1), "mm"),
    plot.margin = unit (c(0,5,0,0), "mm")
    )

3. Then - for data points for the secondary Y axis:

p3.2right <- 
  subset.data.frame(
  data_3.2, 
  select = c(
    "date",
    "Ukraine")
  ) %>%
  
  ggplot(
    aes (
      x = date, 
      y= Ukraine,
      colour = "#999933"
      )
    ) +
  scale_y_continuous(limits = c(145,165), breaks = seq(145,165,5), expand = c(0.025,0)) +
  scale_x_date(date_labels = "%Y", date_breaks = "1 year") +
  geom_line(size = 1)+ 
  scale_colour_identity (
    guide = "legend", 
    label = "Украина (правая ось)",
      ) +
  labs(x=NULL, y=NULL) +
  theme(
    axis.text.x = element_text(angle = 90, vjust = .5, size = 5, colour = "black"),
    axis.text.y = element_text(size = 5, colour = "black"),
    panel.background = element_rect(fill = NA),
    panel.grid = element_blank(),
    axis.line.y = element_line(colour= "#ABABAB"),
    axis.ticks.length = unit(0,"cm"),
    axis.title.y = element_text(size = 5, angle = 90, colour = "black", margin = margin(t = 0, r = 0, b = 0, l = 0)),
    legend.position = "bottom",
    legend.direction = "vertical",
    legend.title = element_blank(),
    legend.spacing.x = unit(0,"cm"),
    legend.key = element_blank(),
    legend.key.height = unit(.5, "cm"),
    legend.text = element_text(size = 5),
    legend.box.margin = unit (c(0,1,1,1), "mm"),
    plot.margin = unit (c(0,5,0,0), "mm")
    )

4. And finally I used the code that worked nicely before (but for some reasons stopped working now) that did what I need - put 2 plots on one chart:

# extract gtable
g1 <- ggplot_gtable(ggplot_build(p3.2left))
g2 <- ggplot_gtable(ggplot_build(p3.2right))

# overlap the panel of 2nd plot on that of 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-r")
ga <- g2$grobs[[ia]]
ax <- ga$children[[2]]
ax$widths <- rev(ax$widths)
ax$grobs <- rev(ax$grobs)

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)


#add legend to the code
leg1 <- g1$grobs[[which(g1$layout$name == "guide-box")]]
leg2 <- g2$grobs[[which(g2$layout$name == "guide-box")]]

leg = gtable:::cbind_gtable(leg1, leg2, "first")            
leg$widths[5:6] = unit(0, "cm")

g$grobs[[which(g$layout$name == "guide-box")]] <- 
  gtable:::cbind_gtable(leg1, leg2, "first")
grid.draw(g)

I got the following error in the following line of the code:

g <- gtable_add_grob(g, ax, pp$t, length(g$widths) - 1, pp$b)

Error: grobs must either be a single grob or a list of grobs

I'm not good at 'gtable' package of R and would appreciate any support in correcting my code (or workflow) so that to finally build this chart and enhance my programming skills in R.

P.S. I use R (Version 1.4.1717) on Ubuntu 20.04.2 LTS

Answer 1

If all you want is a secondary y axis for the Ukraine data, then the following might solve the problem.
In order to make the main point more clear, I have simplified the code and removed the scale_color_manual and the theme from the plotting code. Instead, I have created a variable for the color and a custom theme.

The trick is to precompute a scaling factor mult.

library(ggplot2)
library(dplyr)
library(tidyr)

mult <- max(data_3.2[[6]])/max(data_3.2[2:5])

data_3.2 %>%
  pivot_longer(-date) %>%
  ggplot(aes(date, color = name)) +
  geom_line(
    data = . %>% filter(name != "Ukraine"),
    aes(y = value),
    size = 1
  ) +
  geom_line(
    data = . %>% filter(name == "Ukraine"),
    aes(y = value/mult),
    size = 1
  ) +
  scale_y_continuous(
    limits = c(50, 80), 
    breaks = seq(50, 80, 5), 
    expand = c(0.025, 0),
    sec.axis = sec_axis(
      ~ . * mult, 
      name = "Ukraine",
      breaks = seq(70, 165, 5)
    )
  ) +
  labs(x = NULL, y = NULL) +
  scale_color_dkolkin +
  theme_dkolkin()

---

Color

scale_color_dkolkin <- scale_colour_manual (
  guide = "legend", 
  name = NULL,
  breaks = c(
    "Latvia",
    "Lithuania",
    "Poland",
    "RF",
    "Ukraine"
  ),
  labels = c(
    "Латвия (левая ось)", 
    "Литва (левая ось)",
    "Польша (левая ось)",
    "Россия (левая ось)",
    "Украина (правая ось)"
  ), 
  values = c(
    "#332288", 
    "#88CCEE",
    "#44AA99",
    "#117733",
    "#999933"
  ))

---

Custom theme

theme_dkolkin <- function(){ 
  theme_bw() %+replace%    #replace elements we want to change
    theme(
      axis.text = element_text(size = 5, colour = "black"),
      axis.text.x = element_text(angle = 90, vjust = .5),
      axis.line.y = element_line(colour= "#ABABAB"),
      axis.title.y = element_text(size = 5, angle = 90, colour = "black", margin = margin(t = 0, r = 0, b = 0, l = 0)),
      axis.ticks.length = unit(0,"cm"),
      panel.background = element_rect(fill = NA),
      panel.grid = element_blank(),
      legend.position = "bottom",
      legend.direction = "vertical", 
      legend.title = element_blank(),
      legend.spacing.x = unit(0,"cm"),
      legend.key = element_blank(),
      legend.key.height = unit(.5, "cm"),
      legend.text = element_text(size = 5),
      legend.box.margin = unit (c(0,1,1,1), "mm"),
      plot.margin = unit (c(0,5,0,0), "mm")
    )
}

---

Data

data_3.2 <-
structure(list(date = 2012:2021, Latvia = c(77.21, 75.4, 75.1, 
68.77, 64.8, 63.81, 62.88, 62.36, 63.89, 65.36), Lithuania = c(67.97, 
65.62, 63.86, 59.54, 55.83, 54.14, 53.31, 51.94, 51.69, 51.25
), Poland = c(72.97, 72.83, 71.55, 65.86, 62.14, 60.8, 59.62, 
58.12, 58.94, 57.78), RF = c(71.41, 71.03, 72.95, 71.61, 69.7, 
70.29, 70.82, 71.18, 73, 72.69), Ukraine = c(148.29, 149.45, 
153.65, 162.92, 154.51, 153.99, 153.07, 150.26, 154.26, 156.25
)), class = "data.frame", row.names = c(NA, -10L))

Answer 2

You can pivot your data longer, and use facet_wrap like this. First create the initial data frame:

## data
library(tidyverse)
df = rbind(c(2012,  77.21, 67.97,   72.97, 71.41,   148.29),
           c(2013,  75.40, 65.62,   72.83, 71.03,   149.45),
           c(2014,  75.10, 63.86,   71.55, 72.95,   153.65),
           c(2015,  68.77, 59.54,   65.86, 71.61,   162.92),
           c(2016,  64.80, 55.83,   62.14, 69.70,   154.51),
           c(2017,  63.81, 54.14,   60.80, 70.29,   153.99),
           c(2018,  62.88, 53.31,   59.62, 70.82,   153.07),
           c(2019,  62.36, 51.94,   58.12, 71.18,   150.26),
           c(2020,  63.89, 51.69,   58.94, 73.00,   154.26),
           c(2021,  65.36, 51.25,   57.78, 72.69,   156.25)) %>% 
  data.frame()
colnames(df) = c('Date', 'Latvia', 'Lithuania', 'Poland',   'Russian Federation',   'Ukraine')
df

That gives a data frame that looks like this

   Date Latvia Lithuania Poland Russian Federation Ukraine
1  2012  77.21     67.97  72.97              71.41  148.29
2  2013  75.40     65.62  72.83              71.03  149.45
3  2014  75.10     63.86  71.55              72.95  153.65
4  2015  68.77     59.54  65.86              71.61  162.92
5  2016  64.80     55.83  62.14              69.70  154.51
6  2017  63.81     54.14  60.80              70.29  153.99
7  2018  62.88     53.31  59.62              70.82  153.07
8  2019  62.36     51.94  58.12              71.18  150.26
9  2020  63.89     51.69  58.94              73.00  154.26
10 2021  65.36     51.25  57.78              72.69  156.25

Then put this data in long form

## Pivot longer
df.long = df %>% 
  pivot_longer(cols=-Date,
               names_to='Country', 
               values_to='value') 

## Create a column that is Ukraine or other
df.long = df.long %>%
  mutate(Category = ifelse(Country=='Ukraine', 'Ukraine', 'Other Countries'))
head(df.long)

That gives this

A tibble: 6 x 4
   Date Country            value Category       
  <dbl> <chr>              <dbl> <chr>          
1  2012 Latvia              77.2 Other Countries
2  2012 Lithuania           68.0 Other Countries
3  2012 Poland              73.0 Other Countries
4  2012 Russian Federation  71.4 Other Countries
5  2012 Ukraine            148.  Ukraine        
6  2013 Latvia              75.4 Other Countries

Now plot this using facet_wrap, which creates a new window for each value of "Category". Specify that you don't want to y-axes to be on the same scale by using scales = 'free_y':

ggplot(df.long, aes(x=Date, y=value, group=Country, color=Country))+
  geom_line()+
  facet_wrap(~Category, nrow=1, scales='free_y')

That gives this plot