tidyr extract regular expression [duplicate]

Question

I have a data frame that contains some statistics for a number of variables and scenarios. The data looks like:

df <- data.frame(
  Scenario = c('base','stress','extreme'),
  x_min = c(-3,-2, -2.5),
  x_mean = c(0,0.25, 1),
  x_max = c(2, 1, 3),
  y_min = c(-1.5, -2, -3),
  y_mean = c(1, 2, 3),
  y_max = c(5, 3, 3.5),
  z_min = c(0, 1, 3),
  z_mean = c(0.25, 2, 5),
  z_max = c(2, 4, 7)
)

   Scenario x_min x_mean x_max y_min y_mean y_max z_min z_mean z_max
1     base  -3.0   0.00     2  -1.5      1   5.0     0   0.25     2
2   stress  -2.0   0.25     1  -2.0      2   3.0     1   2.00     4
3  extreme  -2.5   1.00     3  -3.0      3   3.5     3   5.00     7

I would like to use tidyr's gather and extract functions (in a similar manner to Hadley's answer to this question) to get the data in a format like:

new_df
    Scenario variable  min  mean   max
1     base        x   -3.0  0.00   2.0
2   stress        x   -2.0  0.25   1.0
3  extreme        x   -2.5  1.00   3.0
4     base        y   -1.5  1.00   5.0
5   stress        y   -2.0  2.00   3.0
6  extreme        y   -3.0  3.00   3.5
7     base        z    0.0  0.25   2.0
8   stress        z    1.0  2.00   4.0
9  extreme        z    3.0  5.00   7.0

The command I have so far looks like:

new_df <- df %>%
            gather(key, value, -Scenario) %>%
            extract(key, c("min", "mean", "max"), "regex")

It's the regex I'm struggling with. Following the answer in the question referenced above I've tried:

"_min|_mean|_max" --> idea being to capture the 3 different groups

The error I get looks like:

 Error in names(l) <- into : 
     'names' attribute [3] must be the same length as the vector [0]

What I think this error is saying is the the regular expression isn't "finding" 3 groups to sort into the c("min","mean","max") I passed it.

What regular expression would get this working? Or is there another better method?

Answer 1

All you need is

df %>% gather(var, val, -Scenario) %>% 
    separate(var, into = c('var', 'stat'), sep = '_') %>% 
    spread(stat, val)
#   Scenario var max mean  min
# 1     base   x 2.0 0.00 -3.0
# 2     base   y 5.0 1.00 -1.5
# 3     base   z 2.0 0.25  0.0
# 4  extreme   x 3.0 1.00 -2.5
# 5  extreme   y 3.5 3.00 -3.0
# 6  extreme   z 7.0 5.00  3.0
# 7   stress   x 1.0 0.25 -2.0
# 8   stress   y 3.0 2.00 -2.0
# 9   stress   z 4.0 2.00  1.0

Since your initial column names are nicely formatted with underscores separating the variable and the statistic, separate is all you need to split them into two columns. spread will rearrange from long to wide.