Using R to produce Excel style combo graphs - for categorical data on x axis

Question

I need to use R to create graphs with a primary and a secondary axis. I've sometimes been able to use plotrix::twoord() for this, but it doesn't seem to like categorical data on the x-axis, which is a big problem because that's usually what I'm working with.

I'd like to be able to produce something like this Excel "combo graph" (this stuff is very easy in Excel of course):

Is there a way to do this in R? Same data set here:

df <- data.frame(star_sign = c("Aries", "Taurus", "Gemini", "Cancer", "Leo", "Virgo", "Libra", "Scorpio", "Sagittarius", "Capricorn", "Aquarius", "Pisces"),
                 y1 = c(50, 70, 60, 40, 45, 78, 42, 22, 28, 49, 50, 31),
                 y2 = c(1.1, 1.2, 1.4, 1.3, 1.8, 1.6, 1.4, 1.3, 1.2, 1.1, 1.5, 1.3))

df$y3 <- 0.95 * df$y2
df$y4 <- 1.05 * df$y2

I know some practitioners object to showing two variables on the same graph with split axes, but for my particular application it's extremely useful.

Thanks!

Update:

I'm trying now to make this more flexible by wrapping the code in a function, and then pass to the function the names of the fields I want to use in creating the plot. I had thought I could use the [[]] notation to do this. It works in the dplyr mutate command but doesn't seem to work inside the ggplot() stuff. So my code now looks like:

my_graph_2 <- function (df, lvls_name, exposure_name, min_name, mean_name, max_name, scale_fac)
{

  df %>% mutate(star_sign = as.numeric(factor(star_sign, levels = df[[lvls_name]]))) %>%
    ggplot(aes(star_sign)) +
    geom_line(aes(y = mean), color = "red") +
    geom_line(aes(y = min), color = "blue") + 
    geom_line(aes(y = max), color = "green") + 
    geom_col(aes(y = scale_fac * exposure + b), width = 0.5, fill = "blue") + 
    scale_y_continuous("Linear Predictor", sec.axis = sec_axis(~ ./scale_fac , name = "Exposure")) +
    scale_x_continuous("Star Sign", breaks = c(1:row_count), labels = df$lvls) +
    theme_bw()
}

df <- data.frame(star_sign = c("Aries", "Taurus", "Gemini", "Cancer", "Leo", "Virgo", "Libra", "Scorpio", "Sagittarius", "Capricorn", "Aquarius", "Pisces"),
                 exposure = c(50, 70, 60, 40, 45, 78, 42, 22, 28, 49, 50, 31),
                 mean = c(1.1, 1.2, 1.4, 1.3, 1.8, 1.6, 1.4, 1.3, 1.2, 1.1, 1.5, 1.3))

df$min <- 0.95 * df$mean
df$max <- 1.05 * df$mean

df$lvls = c("Aries", "Taurus", "Gemini", "Cancer", "Leo", "Virgo", "Libra", "Scorpio", "Sagittarius", "Capricorn", "Aquarius", "Pisces")

my_graph_2(df, "lvls", "exposure", "mean", "min", "max", 1/150)

and the reference to df[[lvls_name]] works fine. However if I attempt to change

geom_line(aes(y = mean), color = "red") +

to

geom_line(aes(y = df[[mean_name]]), color = "red") +

I see the error

Error in .subset2(x, i, exact = exact) : recursive indexing failed at level 2

Is there a way to fix this problem pls? Thanks.

Does this answer your question? [Plot line and bar graph (with secondary axis for line graph) using ggplot](https://stackoverflow.com/questions/53922846/plot-line-and-bar-graph-with-secondary-axis-for-line-graph-using-ggplot) — UseR10085, Jan 27 '20 at 12:30

Zhiqiang Wang · Accepted Answer · 2020-01-27T13:41:05.187

It is a little tricky. You could try something like this.

library(tidyverse)
lvls = c("Aries", "Taurus", "Gemini", "Cancer", "Leo", "Virgo", "Libra", 
         "Scorpio", "Sagittarius", "Capricorn", "Aquarius", "Pisces")
df %>% 
  mutate(
    star_sign =as.numeric(factor(star_sign, 
                          levels = lvls)))%>% 
  ggplot(aes(star_sign))+ 
  geom_line(aes(y = y2), color = "red") + 
  geom_line(aes(y = y3), color = "grey") + 
  geom_line(aes(y = y4), color = "yellow") + 
  geom_col(aes(y=y1/150), width = 0.5, fill = "blue") + 
  scale_y_continuous("Y title for lines", 
                     sec.axis = sec_axis(~ .*150, name = "Y Title for bar"))+  
  scale_x_continuous("X title", breaks = c(1:12), labels = lvls) + 
  theme_bw()

df <- data.frame(star_sign = c("Aries", "Taurus", "Gemini", "Cancer", "Leo", "Virgo", "Libra", 
                               "Scorpio", "Sagittarius", "Capricorn", "Aquarius", "Pisces"),
                 y1 = c(50, 70, 60, 40, 45, 78, 42, 22, 28, 49, 50, 31),
                 y2 = c(1.1, 1.2, 1.4, 1.3, 1.8, 1.6, 1.4, 1.3, 1.2, 1.1, 1.5, 1.3))

df$y3 <- 0.95 * df$y2
df$y4 <- 1.05 * df$y2

Thank you. It works nicely. I'm trying to wrap it in a more flexible function call now...and having some problems passing field names to the function. Can you help pls? — Alan, Jan 27 '20 at 15:25
Actually I think I've fixed it...using aes_string instead of aes — Alan, Jan 27 '20 at 17:43

Using R to produce Excel style combo graphs - for categorical data on x axis

1 Answers1