I'd like to gather the data frame with two key columns in R

Question

I'd like it to gather the dataframe with two key columns, one would be mean and the other variance. Here is the data frame:

 est1_mean  est1_var  est2_mean  est2_var  est3_mean  est3_var
1  0.2007412 0.8962381  0.2149612 0.7264134  0.2091068 0.9239417
2  0.2046424 0.7970008  0.1621528 0.9365701  0.1715687  1.014485
3  0.2013047 0.9072657    0.17906 0.9759063  0.1998138 0.7717872
4 0.07402292 0.8749833 0.09736422  1.087518  0.1167655  1.082997

I'd tried to use the gather function and then extract from the key column the strings that contain the "var" string and then spread the key column into two:

x %>%
  gather(key, value) %>%
  extract(key, c("mean", "var"), "var") %>%
  spread(var, value)

But I get an Error: regex should define 2 groups; 0 found.`

Answer 1

You can use pivot_longer(), where .value indicates that component of the name defines the name of the column containing the cell values.

library(tidyr)

pivot_longer(df, everything(),
             names_to = c("est", ".value"),
             names_sep = "_")

# # A tibble: 12 × 3
#    est     mean   var
#    <chr>  <dbl> <dbl>
#  1 est1  0.201  0.896
#  2 est2  0.215  0.726
#  3 est3  0.209  0.924
#  4 est1  0.205  0.797
#  5 est2  0.162  0.937
#  6 est3  0.172  1.01 
#  7 est1  0.201  0.907
#  8 est2  0.179  0.976
#  9 est3  0.200  0.772
# 10 est1  0.0740 0.875
# 11 est2  0.0974 1.09 
# 12 est3  0.117  1.08

Data

df <- structure(list(est1_mean = c(0.2007412, 0.2046424, 0.2013047, 0.07402292),
est1_var = c(0.8962381, 0.7970008, 0.9072657, 0.8749833),
est2_mean = c(0.2149612, 0.1621528, 0.17906, 0.09736422), 
est2_var = c(0.7264134, 0.9365701, 0.9759063, 1.087518), 
est3_mean = c(0.2091068, 0.1715687, 0.1998138, 0.1167655), 
est3_var = c(0.9239417, 1.014485, 0.7717872, 1.082997)), class = "data.frame", row.names = c("1", "2", "3", "4"))