R keep and order variables based on variable list form other table

user_3754296 0
Viewed 207

This suppose to be an easy task and trivial, but I couldn't get it work. I would like to create a new table use the same order as another table. Suppose I have current data with same 5 variables from past data but with different order. I would like the current data the same structure. I tried to use the list of colnames from old data and use R data.table to arrange the variable, however as you can see from the example, the new data becases either a list/character or data.table with empty data. Any suggestions? Thanks!

> str(new)
Classes ‘data.table’ and 'data.frame':  2 obs. of  7 variables:
 $ id: int  1 2
 $ x1: int  0 1
 $ x4: int  1 0
 $ x5: int  1 0
 $ x2: int  0 0
 $ x3: int  1 0
 $ x6: int  0 1
 - attr(*, ".internal.selfref")=<externalptr> 

> str(old)
Classes ‘data.table’ and 'data.frame':  2 obs. of  6 variables:
 $ id: int  1 2
 $ x1: int  1 0
 $ x2: int  1 0
 $ x3: int  1 0
 $ x4: int  0 0
 $ x5: int  1 0
 - attr(*, ".internal.selfref")=<externalptr> 

 namelist <- list(names(old))
newlist <- new[,c(namelist)] # using data.table to order the variables
 newlist <- new[,unlist(namelist)] # newlist is character instead of data.table

newlist <- new[,c(namelist)] # data.table but not expected data.
 class(newlist)
[1] "data.table" "data.frame"
> str(newlist)
Classes ‘data.table’ and 'data.frame':  6 obs. of  1 variable:
 $ V1: chr  "id" "x1" "x2" "x3" ...
 - attr(*, ".internal.selfref")=<externalptr>
data.table r
3 Answers

It is easier to use setcolorder by concatenating the unique column in 'new' along with the intersecting column names in that order

nm1 <- setdiff(names(new), names(old))
nm2 <- intersect(names(new), names(old))
setcolorder(new,  gtools::mixedsort(c(nm1, nm2)))
new
#   id x1 x2 x3 x4 x5 x6
#1:  1  0  0  1  1  1  0
#2:  2  1  0  0  0  0  1

Or another option is

setcolorder(new, names(rbindlist(list(old, new), fill = TRUE)))

or with union

setcolorder(new, setdiff(union(names(old), names(new)), 
                 setdiff(names(old), names(new))))

data

new <- data.table(id = 1:2, x1 = 0:1, x4 = 1:0, x5 = 1:0, x2 = 0, x3 = 1:0, x6 = 0:1) 
old <- data.table(id = 1:2, x1 = 1:0, x2 = 1:0, x3 = 1:0, x4 = 0, x5 = 1:0)
user_37322710

You can use column names of old and subset the new dataframe to get the data in the same order as old.

library(data.table)

cols <- names(old)
new[, ..cols]
user_39629140

I combined and tweaked solution from @akrun and @Ronak Shah and solved the problem. Thanks both of you!! The codes are as following:

new <- data.table(id = 1:2, x1 = 0:1, x4 = 1:0, x5 = 1:0, x2 = 0, x3 = 1:0, x6 = 0:1) 
old <- data.table(id = 1:2, x1 = 1:0, x2 = 1:0, x3 = 1:0, x4 = 0, x5 = 1:0)

oldord <- names(old) # find old variable list
newvar <- setdiff(names(new), names(old)) #find additional variables
neword <- cbind(new[,..oldord],new[,..newvar]) #subset and combine old and new variables
neword 
> neword
   id x1 x2 x3 x4 x5 x6
1:  1  0  0  1  1  1  0
2:  2  1  0  0  0  0  1
user_37542960
Related