Add rows using name columns

Question

Is it possible to create two rows using the name of the columns? I need to separate DX from SX and create new rows, after the separation I like to maintain the information DX or SX by adding a column. Some columns it is in common, in this case X. Instead, Num is the key

a = read.table(text="
Num X STDX ABDX XBDX STSX ABSX XBSX 
12   3   9    5   3   11   3   7   
13   35  24   1   7   18   2   8   
14   35  24   1   7   18   2   8   
15   10  1    5   16 -10   5   3   ",h=T)

b= read.table(text="Num X   ST   AB   XB DX/SX
12   3   9    5   3  DX 
12   3   11   3   7  SX 
13   35  24   1   7  DX
13   35  18   2   8  SX
14   35  24   1   7  DX
14   35   18  2   8  SX
15   10  1    5   16 DX
15  10 -10   5   3   SX",h=T)

My idea was separate the data and after join, but it is heavy. I have tried this code:

c <- sapply(c("DX", "SX",""),
       function(x) a[endsWith(names(a),x)],
       simplify = FALSE)

But the problem is x and Num, because I would like to have in the same DB with DX and SX.

Answer 1

There are more elegant and compact approaches for sure, but here's an example how you could achieve this by simple renaming and row binding:

a = read.table(text="
Num X STDX ABDX XBDX STSX ABSX XBSX 
12   3   9    5   3   11   3   7   
13   35  24   1   7   18   2   8   
14   35  24   1   7   18   2   8   
15   10  1    5   16 -10   5   3   ",h=T)

library(dplyr)
library(stringr)

dx <- a %>% 
  select(1,2,ends_with("DX")) %>% 
  rename_with(~ str_remove(.x, "DX$"), .cols = -c(1:2)) %>% 
  mutate(`DX/SX` = "DX" )
dx
#>   Num  X ST AB XB DX/SX
#> 1  12  3  9  5  3    DX
#> 2  13 35 24  1  7    DX
#> 3  14 35 24  1  7    DX
#> 4  15 10  1  5 16    DX

sx <- a %>% 
  select(1,2,ends_with("SX")) %>% 
  rename_with(~ str_remove(.x, "SX$"), .cols = -c(1:2)) %>% 
  mutate(`DX/SX` = "SX" )
sx
#>   Num  X  ST AB XB DX/SX
#> 1  12  3  11  3  7    SX
#> 2  13 35  18  2  8    SX
#> 3  14 35  18  2  8    SX
#> 4  15 10 -10  5  3    SX

bind_rows(dx,sx) %>% 
  arrange(Num)
#>   Num  X  ST AB XB DX/SX
#> 1  12  3   9  5  3    DX
#> 2  12  3  11  3  7    SX
#> 3  13 35  24  1  7    DX
#> 4  13 35  18  2  8    SX
#> 5  14 35  24  1  7    DX
#> 6  14 35  18  2  8    SX
#> 7  15 10   1  5 16    DX
#> 8  15 10 -10  5  3    SX

^{Created on 2022-10-12 with reprex v2.0.2}