Creating a grouped barplot with two Y axes in R

Question

I have the following data frame which consists of visual / sightings per hour (SPH) and acoustic / clicks per hour (CPH) data sets for a time span of 8 weeks. I'd like to to have a grouped bar plot which groups the two values for one week together. So e.g. it's 2.676 sightings/h in week 1 and 75.35 clicks/h in week 1. Because of the different units for both variables I also need two y-axes, one for sightings/h on the left and one for clicks/h on the right.

data.frame(Week = c(1, 2, 3, 4, 5, 6, 7, 8), SPH = c(2.676, 2.660, 4.175, 2.134, 
    3.742, 1.395, 4.739, 2.756), CPH = c(75.35, 29.58, 20.51, 80.43, 97.94, 85.39, 168.61, 142.19))

I tried to create a matrix to plot the data, but I don't know how to tell R properly that the first two variables belong to week 1, the second two to week 2 etc.

Hope you guys can help me with this.

Best regards and thanks in advance. :)

Does this answer your question? [Grouped boxplot with two Y axis](https://stackoverflow.com/questions/63677441/grouped-boxplot-with-two-y-axis) — Skaqqs, Jan 18 '22 at 17:24

DaveArmstrong · Accepted Answer · 2022-01-20T12:34:58.320

How about this:

dat <- data.frame(Week = c(1, 2, 3, 4, 5, 6, 7, 8), 
                  SPH = c(2.676, 2.660, 4.175, 2.134, 3.742, 1.395, 4.739, 2.756), 
                  CPH = c(75.35, 29.58, 20.51, 80.43, 97.94, 85.39, 168.61, 142.19))

library(tidyr) 
library(dplyr)

## Find minimum and maximum values for each variable
tmp <- dat %>% 
  summarise(across(c("SPH", "CPH"), ~list(min = min(.x), max=max(.x)))) %>% 
  unnest(everything())

## make mapping from CPH to SPH 
m <- lm(SPH ~ CPH, data=tmp)
## make mapping from SPH to CPH
m_inv <- lm(CPH ~ SPH, data=tmp)


## transform CPH so it's on the same scale as SPH. 
## to do this, you need to use the coefficients from model m above
dat <- dat %>% 
  mutate(CPH = coef(m)[1] + coef(m)[2]*CPH) %>%
  ## pivot the data so all plotting values are in a single column
  pivot_longer(c("SPH", "CPH"), 
               names_to="var", values_to="vals") %>% 
  mutate(var = factor(var, levels=c("SPH", "CPH"), 
                      labels=c("Sightings", "Clicks")))


ggplot(dat, aes(x=as.factor(Week), y=vals, fill=var)) + 
  geom_bar(position="dodge", stat="identity") + 
  ## use model m_inv from above to identify the transformation from the tick values of SPH 
  ## to the appropriate tick values of CPH
  scale_y_continuous(sec.axis=sec_axis(trans = ~coef(m_inv)[1] + coef(m_inv)[2]*.x, name="Clicks/hour")) + 
  labs(y="Sightings/hour", x="Week", fill="") + 
  theme_bw() + 
  theme(legend.position="top")

Update - start both axes at zero

To start both axes at zero, you need to change have the values that are used in the linear map both start at zero. Here's an updated full example that does that:

dat <- data.frame(Week = c(1, 2, 3, 4, 5, 6, 7, 8), 
                  SPH = c(2.676, 2.660, 4.175, 2.134, 3.742, 1.395, 4.739, 2.756), 
                  CPH = c(75.35, 29.58, 20.51, 80.43, 97.94, 85.39, 168.61, 142.19))

library(tidyr) 
library(dplyr)

## Find minimum and maximum values for each variable
tmp <- dat %>% 
  summarise(across(c("SPH", "CPH"), ~list(min = min(.x), max=max(.x)))) %>% 
  unnest(everything())


## set lower bound of each to zero
tmp$SPH[1] <- 0
tmp$CPH[1] <- 0

## make mapping from CPH to SPH 
m <- lm(SPH ~ CPH, data=tmp)
## make mapping from SPH to CPH
m_inv <- lm(CPH ~ SPH, data=tmp)


## transform CPH so it's on the same scale as SPH. 
## to do this, you need to use the coefficients from model m above
dat <- dat %>% 
  mutate(CPH = coef(m)[1] + coef(m)[2]*CPH) %>%
  ## pivot the data so all plotting values are in a single column
  pivot_longer(c("SPH", "CPH"), 
               names_to="var", values_to="vals") %>% 
  mutate(var = factor(var, levels=c("SPH", "CPH"), 
                      labels=c("Sightings", "Clicks")))


ggplot(dat, aes(x=as.factor(Week), y=vals, fill=var)) + 
  geom_bar(position="dodge", stat="identity") + 
  ## use model m_inv from above to identify the transformation from the tick values of SPH 
  ## to the appropriate tick values of CPH
  scale_y_continuous(sec.axis=sec_axis(trans = ~coef(m_inv)[1] + coef(m_inv)[2]*.x, name="Clicks/hour")) + 
  labs(y="Sightings/hour", x="Week", fill="") + 
  theme_bw() + 
  theme(legend.position="top")

Wow, awesome! It it possible to easily edit the legend and remove the "variable" and just name "Clicks" and "Sightings"? Or do I need to change all the CPHs and SPHs in the code in order to get that? — Kingpin96, Jan 18 '22 at 18:29
@Kingpin96 I edited the answer to produce the result you wanted. — DaveArmstrong, Jan 18 '22 at 19:22
Perfect, thanks! I just realised that the right Y axis starts at -50, but the bottom of the bars have to be on the 0 just like on the left Y axis. Any idea where it comes from? — Kingpin96, Jan 20 '22 at 08:41
@Kingpin96 - the problem came from the linear mapping from one variable to the other. By making both minimum values used in the map zero, that solves the problem. I updated the answer to add that. — DaveArmstrong, Jan 20 '22 at 12:35

Creating a grouped barplot with two Y axes in R

1 Answers1

Update - start both axes at zero

Linked