How to convert first column in df as a header and keep original header as sub-header

Question

I do have this type of data:

id <- c('id_1', 'id_1', 'id_1', 'id_1', 'id_2', 'id_2', 'id_2', 'id_2')
var1 <- round(runif(n = 8, min = 0, max = 10), 4)
var2 <-  round(runif(n = 8, min = 0, max = 10), 4)
var3  <- round(runif(n = 8, min = 0, max = 10), 4)
var4 <- round(runif(n = 8, min = 0, max = 10), 4)
tbl <- tibble(id, var1, var2, var3, var4)

# A tibble: 8 × 5
  id     var1  var2   var3  var4
  <chr> <dbl> <dbl>  <dbl> <dbl>
1 id_1  1.62  6.36  9.69    2.44
2 id_1  6.10  8.49  8.37    7.75
3 id_1  1.28  7.58  7.03    9.43
4 id_1  5.71  4.94  0.0167 10.0 
5 id_2  7.75  0.836 9.31    2.36
6 id_2  1.38  2.35  2.94    6.01
7 id_2  0.560 6.63  3.51    4.04
8 id_2  1.9   9.29  0.230   1.83

And I would like to create a new tibble using the 'id' as a header and var1:var2 as a sub-header, something like this:

        id1                          id2 
------------------------------------------------------------------------
     var1  var2   var3  var4          var1  var2   var3  var4
------------------------------------------------------------------------
     1.62  6.36  9.69    2.44        7.75  0.836 9.31    2.36
     6.10  8.49  8.37    7.75        1.38  2.35  2.94    6.01
     1.28  7.58  7.03    9.43        0.560 6.63  3.51    4.04
     5.71  4.94  0.0167  10.0        1.9   9.29  0.230   1.83
   

So far, I've been playing with pivot longer but I couldn't manage to get my desired output. I've also played with group_by(sample_id) and then transpose, but still not success. Does anyone could suggest a hint? I really appreciate.

Answer 1

library(dplyr)
library(tidyr)

set.seed(123)
id <- c('id_1', 'id_1', 'id_1', 'id_1', 'id_2', 'id_2', 'id_2', 'id_2')
var1 <- round(runif(n = 8, min = 0, max = 10), 4)
var2 <-  round(runif(n = 8, min = 0, max = 10), 4)
var3  <- round(runif(n = 8, min = 0, max = 10), 4)
var4 <- round(runif(n = 8, min = 0, max = 10), 4)
tbl <- tibble(id, var1, var2, var3, var4)

tbl |> 
  pivot_longer(-id) |> 
  mutate(id = paste(id, name, sep = "_"))|> 
  select(-name) |>
  group_by(id) |> 
  mutate(rowid = row_number()) |> 
  pivot_wider(names_from = id, values_from = value) |> 
  select(-rowid)


#> # A tibble: 4 × 9
#>   rowid id_1_var1 id_1_var2 id_1_var3 id_1_var4 id_2_var1 id_2_var2 id_2_var3
#>   <int>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>
#> 1     1      2.88      5.51     2.46       6.56     9.40       6.78      8.90
#> 2     2      7.88      4.57     0.421      7.09     0.456      5.73      6.93
#> 3     3      4.09      9.57     3.28       5.44     5.28       1.03      6.41
#> 4     4      8.83      4.53     9.54       5.94     8.92       9.00      9.94
#> # ℹ 1 more variable: id_2_var4 <dbl>

^{Created on 2023-06-26 with reprex v2.0.2}

To present the data including grouped headings use a table presentation package, for example kableExtra (there are several others which can do this too)

library(kableExtra) kbl(tbl1, col.names = rep(paste0("var_", 1:4), 2), digits = 2) |> add_header_above(c("id_1" = 4, "id_2" = 4)) |> column_spec(1:8, width = "15mm")