-3

I'm having trouble stacking 10 columns in R into two columns of 5 where each column relates. Basically I have something like:

Name1, ID1, Name2, ID2, Name3, ID3, Name4, ID4, Name5, ID5

And I need to stack them in to a Name and ID table where the values in each Name column still match its ID counterpart. What would be the best way to approach this?

Thanks!

A5C1D2H2I1M1N2O1R2T1
  • 177,446
  • 27
  • 370
  • 450
leroy332
  • 1
  • 1
    what have you tried so far ... ? Can you show some code and a [reproducible example](http://tinyurl.com/reproducible-000) please? – Ben Bolker Nov 24 '15 at 17:19

2 Answers2

4

I would recommend melt from the "data.table" package.

Here's some sample data. (This is something you should share.)

mydf <- data.frame(
  matrix(1:20, ncol = 10, dimnames = list(NULL, paste0(c("Name", "ID"), 
                                                       rep(1:5, each = 2)))))

mydf
##   Name1 ID1 Name2 ID2 Name3 ID3 Name4 ID4 Name5 ID5
## 1     1   3     5   7     9  11    13  15    17  19
## 2     2   4     6   8    10  12    14  16    18  20

Here's the reshaping:

library(data.table)
melt(as.data.table(mydf), measure = patterns("Name", "ID"), 
     value.name = c("Name", "ID"))
##     variable Name ID
##  1:        1    1  3
##  2:        1    2  4
##  3:        2    5  7
##  4:        2    6  8
##  5:        3    9 11
##  6:        3   10 12
##  7:        4   13 15
##  8:        4   14 16
##  9:        5   17 19
## 10:        5   18 20
A5C1D2H2I1M1N2O1R2T1
  • 177,446
  • 27
  • 370
  • 450
  • 1
    Can also use `setDT(mydf)` instead of coercing with `as.data.table`. Maybe worth noting: the reshape2 `melt` doesn't have `patterns` and so can't substitute here. – Frank Nov 24 '15 at 17:41
  • 3
    @Frank, I'm aware of `setDT` but I've found enough people to be surprised by their data changing from a `data.frame` to a `data.table` that I generally don't use it in answers. – A5C1D2H2I1M1N2O1R2T1 Nov 24 '15 at 17:42
0

You can do this with reshaping

library(dplyr)
library(tidyr)
library(rex)

variable_regex =
  rex(capture("Name" %>% 
                or ("ID") ),
      capture(digits) )

mydf %>%
  mutate(row_ID = 1:n()) %>%
  gather(variable, value, -row_ID) %>%
  extract(variable,
           c("new_variable", "column_ID"),
           variable_regex) %>%
  spread(new_variable, value)
bramtayl
  • 3,764
  • 2
  • 9
  • 17