R How to find and reorder columns in one table according to column order in another table (tables contain diff number of columns)

Question

I have read the following threads that seem to be similar (but still different) questions with mine:

my questions are different because in thread 1, the two tables have same length, and i tried using method indicated in thread 2, but seems like they also have to be same length.

for illustration purpose, i'll create two tables as following:

table1 = data.frame(rbind(c(rep(c(TRUE,FALSE), 3)), c(rep(TRUE, 4), rep(FALSE, 2))))
dim(table1)
setnames(table1, letters[1:6])
table1
  a     b    c     d     e     f
1 TRUE FALSE TRUE FALSE  TRUE FALSE
2 TRUE  TRUE TRUE  TRUE FALSE FALSE

table2 = data.frame(rbind(c(rep(c(TRUE,FALSE), 2)), c(rep(TRUE, 3), rep(FALSE, 1))))
dim(table2)
setnames(table2, letters[7:4])
table2
  g     f    e     d
1 TRUE FALSE TRUE FALSE
2 TRUE  TRUE TRUE FALSE

the desired return would be:

  f     e      d
1 FALSE TRUE  FALSE
2 FALSE FALSE TRUE

the return table will fit the following criteria:

contains ONLY the letters exist in BOTH tables
order of the return table is consistent with the order in table 2

Basically, I want to clean my table1 to contain only column names that also exist in table2, and i want the table1 columns to be reordered in order same as the columns in table 2.

Let me know if I need to clarify anything else! Thank you!

what about f? `table1[intersect(names(table2), names(table1))]` ? what packages are you using, where does `setnames` come from — rawr, Apr 08 '16 at 22:18
@FascinatingFingers good catch! my bad! will fix my errow now — alwaysaskingquestions, Apr 08 '16 at 23:15
@rawr hi, intersect does not ORDER my output in the order i wanted right? — alwaysaskingquestions, Apr 08 '16 at 23:16
@rawr yes i DID try it! it was not working as i expected. as intersect seem to require the two items passed in to have same length — alwaysaskingquestions, Apr 09 '16 at 05:55
@alwaysaskingquestions but it doesnt.. even the examples are exclusively with two items that are _not_ the same length — rawr, Apr 09 '16 at 13:00
hi @rawr thank you very much for pointing it out... i did try using intersect, but it doesnt sort my vector in the way i wanted :/ can you point out where i can indicate what order the output should take? — alwaysaskingquestions, Apr 10 '16 at 00:05
@rawr He is using `setnames` from a package named `data.table`. — Sowmya S. Manian, Apr 10 '16 at 19:43

score 2 · Answer 1 · answered Apr 11 '16 at 03:07

Thanks to Sowmya for sharing ideas with me. Per request, below is my version of the answer based on what I've learned from Sowmya's answer and rawr's comments.

table1 = data.frame(rbind(c(rep(c(TRUE,FALSE), 3)), c(rep(TRUE, 4), rep(FALSE, 2))))
dim(table1)
setnames(table1, letters[1:6])

table2 = data.frame(rbind(c(rep(c(TRUE,FALSE), 2)), c(rep(TRUE, 3), rep(FALSE, 1))))
dim(table2)
setnames(table2, letters[7:4])

# below are my answer
col2keep = intersect(colnames(table2), colnames(table1))
table1_reduce = table1[, (match(col2keep, colnames(table1)))]

and table1_reduce will contain my desired output.

Sowmya S. Manian · Accepted Answer · 2016-04-09T19:20:12.410

0

Install and load dplyr package for select function. Other functions used match, order, is.na, which and operator %in%.

  install.packages("plyr")
  install.packages("dplyr")
  library(plyr)
  library(dplyr)

  table1 = data.frame(rbind(c(rep(c(TRUE,FALSE), 3)), c(rep(TRUE, 4), rep(FALSE, 2))))
  dim(table1)
  names(table1) <- letters[1:6]

  table2 = data.frame(rbind(c(rep(c(TRUE,FALSE), 2)), c(rep(TRUE, 3), rep(FALSE, 1))))
  dim(table2)
  names(table2) <- letters[7:4]

  target <- as.vector(names(table2)) 
  t <- select(table1, which(names(table1) %in% target))
  gg <- match (target,names(t)) 
  gg <- gg[!is.na(gg)]
  Result <- t[,order(gg)]
  Result

edited Apr 09 '16 at 19:20

answered Apr 09 '16 at 17:26

Sowmya S. Manian

3,723
3
18
30

ah thank you Sowmya! I used a variation of your method, but the basic idea/steps are the same. thank you! – alwaysaskingquestions Apr 10 '16 at 00:19
Could you please paste your version here, so that I can also learn please. Thank you. – Sowmya S. Manian Apr 10 '16 at 05:07
Hi Sowmya, absolutely. I will type up an answer in a few minutes – alwaysaskingquestions Apr 11 '16 at 02:57

R How to find and reorder columns in one table according to column order in another table (tables contain diff number of columns)

2 Answers2