0

How can I combine columns of a data frame in the following fashion?

data <- data.frame(user.A = c(2,4,6), 
               user.B = c(11,13,15), 
               other.A = c(102,104,106), 
               other.B = c(201,103,105), 
               id = c('001', '004', '006'))
data
  user.A user.B other.A other.B  id
1      2     11     102     201 001
2      4     13     104     103 004
3      6     15     106     105 006

# Desired output.
  user other  id
1    2   102 001
2   11   201 001
3    4   104 004
4   13   103 004
5    6   106 006
6   15   105 006

I believe this can be done with dyplr or tidyr. The bind_rows function in dplyr does something similar but does not create this desired output.

Adam
  • 897
  • 2
  • 11
  • 20

2 Answers2

2

It is much easier with melt from data.tableas it can take multiple measure patterns.

library(data.table)
melt(setDT(data), measure = patterns("^user", "^other"),
      value.name = c("user", "other"))[, variable := NULL][]
#   id user other
#1: 001    2   102
#2: 004    4   104
#3: 006    6   106
#4: 001   11   201
#5: 004   13   103
#6: 006   15   105

As the 'user', 'other' columns are numeric, we can also use gather/spread from tidyr

library(dplyr)
library(tidyr)
gather(data, var, val, -id) %>%
        separate(var, into = c("var1", "var2")) %>%
        spread(var1, val) %>% 
        select(-var2)
#  id other user
#1 001   102    2
#2 001   201   11
#3 004   104    4
#4 004   103   13
#5 006   106    6
#6 006   105   15
akrun
  • 674,427
  • 24
  • 381
  • 486
2

You can use a variation of the reshape function as follows:

new_data <- reshape(data, varying = 1:4, direction = "long")

The varying argument is used to specify which columns to pivot on.

aakash
  • 148
  • 6