double y-axes plot in R

Question

I want to build plot with double y-axes.

In image you can see my dataframe and plot. It was done in Excel, I need to do the sames in R. I tried to use latticeExtra library, but it doesn't show any lines and boxes

library(latticeExtra)
obj1 <- xyplot(Q_TY_PAPER ~ PU, df, type = "h")
obj2 <- xyplot(COM_USD ~ PU, df, type = "l")
doubleYScale(obj1, obj2, text = c("obj1", "obj2"))`

Can you please help me?

Here the capture of my dataset and the plot that I would like to get:

Answer 1

You need to separate your dataframe in two, one that will be used for the barchart and need to be reshape and the second one to be used for the line that need to be scaled.

Basically, the line will be plot on the same y axis that the barchart, however, we will add a secondary y axis that will have mark corresponding to the "real" value of the line.

So, first, we need to rescale the value plot as a line. As, we saw in your example that a value of 8 in the barchart match a value of 500 for the line, we can rescale by applying a ratio of 8/500:

df_line = df[,c("PU","COM_USD")]
df_line$COM_USD_2 = df_line$COM_USD * 8/500

> df_line
       PU COM_USD COM_USD_2
1 Client1     464     7.424
2 Client2     237     3.792
3 Client3     179     2.864
4 Client4      87     1.392
5 Client5      42     0.672
6 Client6      27     0.432
7 Client7      10     0.160

For the barchart, we need to pivot the data in a longer format in order to fit the grammar of ggplot2. For doing that, we can use pivot_longer from tidyr packages (loaded with tidyverse):

library(tidyverse)
df_bar <- df %>% select(-COM_USD) %>% pivot_longer(., - PU, names_to = "Variable", values_to = "Value")

# A tibble: 21 x 3
   PU      Variable    Value
   <fct>   <chr>       <dbl>
 1 Client1 Q_TY_PAPER    7.1
 2 Client1 Q_TY_ONLINE   7.1
 3 Client1 CURR          6  
 4 Client2 Q_TY_PAPER    3.8
 5 Client2 Q_TY_ONLINE   3.8
 6 Client2 CURR          3.9
 7 Client3 Q_TY_PAPER    4.4
 8 Client3 Q_TY_ONLINE   4.4
 9 Client3 CURR          2.3
10 Client4 Q_TY_PAPER    2.6
# … with 11 more rows

Now, you can plot both of them by doing:

library(tidyverse)

ggplot(df_bar, aes(x = PU, y = Value))+ 
  geom_bar(aes(fill = Variable), stat = "identity", position = position_dodge(), alpha = 0.8)+
  geom_line(data = df_line, aes(x = PU, y = COM_USD_2, group = 1), size = 2, color = "blue")+
  scale_y_continuous(name = "Quantity", limits = c(0,8), sec.axis = sec_axis(~(500/8)*., name = "USD"))+
  theme(legend.title = element_blank(),
        axis.title.x = element_blank())

As you can see, in scale_y_continuous, we are setting a second axis that will have the value of its ticks multiply by the reverse ratio (500/8). Like that, it will match values of the line plotted.

Finally, you get the following plot:

DATA

PU = paste0("Client",1:7)
COM_USD = c(464,237,179,87,42,27,10)
Q_TY_PAPER = c(7.1,3.8,4.4,2.6,1.2,1.1,0.5)
Q_TY_ONLINE = c(7.1,3.8,4.4,2.6,1.2,1.1,0.5)
CURR = c(6.0,3.9,2.3,0.2,0.2,0.1,0)

df = data.frame(PU,COM_USD, Q_TY_PAPER, Q_TY_ONLINE, CURR)

---

EDIT: Dealing with long names as x-axis labels

If your real data names of clients is too long, you can use this solution (Two lines of X axis labels in ggplot) to write them on two lines.

So, first modifying the PU variables:

PU = c("Jon Jon", "Bob Bob", "Andrew Andrew", "Henry Henry", "Alexander Alexander","Donald Donald", "Jack Jack")
COM_USD = c(464,237,179,87,42,27,10)
Q_TY_PAPER = c(7.1,3.8,4.4,2.6,1.2,1.1,0.5)
Q_TY_ONLINE = c(7.1,3.8,4.4,2.6,1.2,1.1,0.5)
CURR = c(6.0,3.9,2.3,0.2,0.2,0.1,0)

df = data.frame(PU,COM_USD, Q_TY_PAPER, Q_TY_ONLINE, CURR)

Then, we apply the same code as described above:

df_line = df[,c("PU","COM_USD")]
df_line$COM_USD_2 = df_line$COM_USD * 8/500

library(tidyverse)
df_bar <- df %>% select(-COM_USD) %>% pivot_longer(., - PU, names_to = "Variable", values_to = "Value")

But for the plot, you can use scale_x_discrete and specify labels by adding \n to indicate R to write x-labels on multiple lines:

ggplot(df_bar, aes(x = PU, y = Value))+ 
  geom_bar(aes(fill = Variable), stat = "identity", position = position_dodge(), alpha = 0.8)+
  geom_line(data = df_line, aes(x = PU, y = COM_USD_2, group = 1), size = 2, color = "blue")+
  scale_y_continuous(name = "Quantity", limits = c(0,8), sec.axis = sec_axis(~(500/8)*., name = "USD"))+
  theme(legend.title = element_blank(),
        axis.title.x = element_blank())+
  scale_x_discrete(labels = gsub(" ","\n",PU), breaks = PU)

And you get this: