Stack/Melt Sets of Columns

Question

I'm trying to take a single table with 10 columns and merge/union/stack into 2 columns. The current layout is like this

ID_1 | Name_1 | ID_2 | Name_2 | ID_3 | Name_3

I'm trying to get this into the format with column headers "ID" and "Name"

ID   | Name  
ID_1 | Name_1  
ID_2 | Name_2  
ID_3 | Name_3

try `reshape(df,matrix(1:10,2),dir="long")` or even`type.convert(unstack(transform(stack(df),ind=sub("_.*","",ind))))` — Onyambu, Jun 05 '20 at 14:40
@Onyambu the ```type.convert``` code seems to work for stacking the IDs but doesn't stack the names or keep the name columns. Any suggestions? — Joseph Wooster, Jun 05 '20 at 14:47
try the `reshape` function. I am not quite sure what you mean since there is no example given — Onyambu, Jun 05 '20 at 14:53

score 0 · Accepted Answer · answered Jun 05 '20 at 14:52

sample data

df <- data.frame( ID_1 = 1:10, Name_1 = LETTERS[1:10], 
ID_2 = 11:20, Name_2 = LETTERS[11:20])

   ID_1 Name_1 ID_2 Name_2
1     1      A   11      K
2     2      B   12      L
3     3      C   13      M
4     4      D   14      N
5     5      E   15      O
6     6      F   16      P
7     7      G   17      Q
8     8      H   18      R
9     9      I   19      S
10   10      J   20      T

code

library( data.table )
#groups of how many columns?
numcols = 2
#set df as a data.table
data.table::setDT(df)
#split every two columns of df into list
L <- split.default( df, rep ( 1: (ncol(df)/numcols), each = numcols) )
#rowbind together
data.table::rbindlist(L, use.names = FALSE )

   ID_1 Name_1
1:    1      A
2:    2      B
3:    3      C
4:    4      D
5:    5      E
6:    6      F
7:    7      G
8:    8      H
9:    9      I
10:   10      J
11:   11      K
12:   12      L
13:   13      M
14:   14      N
15:   15      O
16:   16      P
17:   17      Q
18:   18      R
19:   19      S
20:   20      T

score 0 · Answer 2 · answered Jun 05 '20 at 15:43

A tidyverse solution. Reshape your data twice to make it long (reshaping by names and then id). Use separate to find the "suffix` of the names and ids, then filter on the suffixes matching.

library(tidyverse)
df %>%
    pivot_longer(cols = starts_with("Name"), names_to = "name_names", values_to = "name") %>%
    pivot_longer(cols = starts_with("ID"), names_to = "id_names", values_to = "id") %>%
    separate(col = name_names, into = c("name_names", "suffix_names")) %>%
    separate(col = id_names, into = c("id_names", "suffix_id")) %>%
    filter(suffix_names == suffix_id) %>%
    select(name, id)

# A tibble: 20 x 2
   name     id
   <fct> <int>
 1 A         1
 2 K        11
 3 B         2
 4 L        12
 5 C         3
 6 M        13
 7 D         4
 8 N        14
 9 E         5
10 O        15
11 F         6
12 P        16
13 G         7
14 Q        17
15 H         8
16 R        18
17 I         9
18 S        19
19 J        10
20 T        20

Stack/Melt Sets of Columns

2 Answers2