R pivot_longer and ggplot errorbar with two name/key columns

Question

Let's assume we have the following artifical data:

  df <- data.frame(Year = c(2015,2016,2017,2018),
               GPP_mean = c(1700,1800,1750,1850),
               Reco_mean = c(-1700,-1800,-1750,-1850),
               GPP_min = c(1600,1700,1650,1750),
               GPP_max = c(1800,1900,1850,1950),
               Reco_min = c(-1600,-1700,-1650,-1750),
               Reco_max = c(-1800,-1900,-1850,-1950))

I'd like to plot bars for each mean value and use the min/max columns for the errorbar. This is what I've achieved so far:

df %>%
    pivot_longer(cols = -Year,
                 names_to = c("variable", "stats"),
                 names_sep = "_") 

Which gives us:

    # A tibble: 24 x 4
    Year variable stats value
   <dbl> <chr>    <chr> <dbl>
 1  2015 GPP      mean   1700
 2  2015 Reco     mean  -1700
 3  2015 GPP      min    1600
 4  2015 GPP      max    1800
 5  2015 Reco     min   -1600
 6  2015 Reco     max   -1800
 7  2016 GPP      mean   1800
 8  2016 Reco     mean  -1800
 9  2016 GPP      min    1700
10  2016 GPP      max    1900
# … with 14 more rows

So far, so good (I guess?). From here on, I have no clue of how I can tell ggplot to plot the mean values as the bars and use min/max for the errorbars. Any help appreciated, thanks.

Answer 1

additional solution using tidyverse

library(tidyverse)
out <- df %>% 
  pivot_longer(-Year, names_sep = "_", names_to = c("index", ".value"))

ggplot(out, aes(Year, mean, fill = index)) +
  geom_col() +
  geom_errorbar(aes(ymin = min, ymax = max), width = 0.5)