tidyr extract regular expression

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?

alistaire · Accepted Answer · 2016-05-13T17:01:50.383

3

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.

edited May 13 '16 at 17:01

answered May 13 '16 at 16:53

alistaire

38,696
4
60
94

1

Exactly what I needed! As a note I had to make the `sep` argument of `separate` a regular expression because my column titles actually contained multiple underscores (e.g. `stat_1_min, stat_2_min`). The regex I used was `(_)(?!.*_)` – reidjax May 13 '16 at 17:15

score 2 · Answer 2 · answered May 14 '16 at 03:29

This can be done easily with melt from data.table as it can take multiple patterns in the measure

library(data.table)
nm1 <- unique(substr(names(df)[-1], 1, 1))
melt(setDT(df), measure = patterns("min$", "mean$", "max$"),
       value.name= c("min", "mean", "max"))[, variable := nm1[variable]][]
#   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

tidyr extract regular expression

2 Answers2