CODEWITHSUNDEEP
r
SQLnRprobs
SQLnRprobs

Reputation: 77

populate Data Frame based on lookup data frame in R

How does one go about switching a data frame based on column names between to tables with a lookup table in between.

Orig

A B C
1 2 3
2 2 2
4 5 6 

Ret

D E
7 8
8 9
2 4


lookup <- data.frame(Orig=c('A','B','C'),Ret=c('D','D','E'))
  Orig Ret
1  A  D
2  B  D
3  C  E

So that the final data frame would be

A B C

7 7 8
8 8 9
2 2 4

Upvotes: 2

Views: 295

Answers (2)

lmo
lmo

Reputation: 38490

I believe that the following will work as well. 

OrigN <- Orig
OrigN[, as.character(lookup$Orig)] <- Ret[, as.character(lookup$Ret)]

This method applies a column shuffle to Orig (actually a copy OrigN following @Akrun) and then fills these columns with the appropriately ordered columns of Ret using the lookup.

Upvotes: 1

akrun
akrun

Reputation: 886938

We can match the 'Orig' column in 'lookup' with the column names of 'Orig' to find the numeric index (although, it is in the same order, it could be different in other cases), get the corresponding 'Ret' elements based on that. We use that to subset the 'Ret' dataset and assign the output back to the original dataset. Here I made a copy of "Orig".

OrigN <- Orig
OrigN[] <- Ret[as.character(lookup$Ret[match(as.character(lookup$Orig), 
           colnames(Orig))])]
OrigN
#  A B C
#1 7 7 8
#2 8 8 9
#3 2 2 4

NOTE: as.character was used as the columns in 'lookup' were factor class.

Upvotes: 2

Related Questions