I have two dataframes, DF and DFMap. I am trying to use DFMap as a mapping table and merge the contents to complete DF (similar to VLOOKUP logic) to create DFMerge (code below). The table I want is shown as DFMerge in the code below. I tried to create using: DFmerge <- merge(DF, DFMap, by = "C_SHORT", all.x = TRUE) but not working?
C_SHORT <- c("100001", "100002","100003","100001", "100002","100003")
C_FULL <- c("Base 100001", "Base 100002","Base 100003","Base 100001", "Base 100002","Base 100003")
ACC_SHORT <- c("X", "Y", "Z","X", "Y", "Z")
ACC_FULL <- c("Base X", "Base Y", "Base Z","Base X", "Base Y", "Base Z")
Date <-c ("04/01/2022","04/01/2022","045/01/2022","05/01/2022","05/01/2022","05/01/2022")
Value <- c(60,100,50,80,65,75)
DF<-data.frame(C_SHORT, C_FULL,ACC_SHORT,ACC_FULL,Date,Value)
C_SHORT <- c("100001", "100002","100003")
COUNTRY <- c("UK", "China","Ireland")
COLOUR <- c("BLUE", "RED", "GREEN")
DFMap<-data.frame(C_SHORT, COUNTRY, COLOUR)
C_SHORT <- c("100001", "100002","100003","100001", "100002","100003")
COUNTRY1 <- c("UK", "China","Ireland","UK", "China","Ireland")
C_FULL1 <- c("Base 100001", "Base 100002","Base 100003","Base 100001", "Base 100002","Base 100003")
ACC_SHORT1 <- c("X", "Y", "Z","X", "Y", "Z")
ACC_FULL1 <- c("Base X", "Base Y", "Base Z","Base X", "Base Y", "Base Z")
Date1 <-c ("04/01/2022","04/01/2022","045/01/2022","05/01/2022","05/01/2022","05/01/2022")
COLOUR1 <- c("BLUE", "RED", "GREEN","BLUE", "RED", "GREEN")
Value1 <- c(60,100,50,80,65,75)
DFmerge<-data.frame(C_SHORT, COUNTRY1,C_FULL1,ACC_SHORT1,ACC_FULL1,Date1,COLOUR1,Value1)
DFm <- merge(DF, DFMap, by = "C_SHORT", all.x = TRUE)
If you want only to keep the row order of DF you can use match.
cbind(DF, DFMap[match(DF$C_SHORT, DFMap$C_SHORT), -1])
# C_SHORT C_FULL ACC_SHORT ACC_FULL Date Value COUNTRY COLOUR
#1 100001 Base 100001 X Base X 04/01/2022 60 UK BLUE
#2 100002 Base 100002 Y Base Y 04/01/2022 100 China RED
#3 100003 Base 100003 Z Base Z 045/01/2022 50 Ireland GREEN
#1.1 100001 Base 100001 X Base X 05/01/2022 80 UK BLUE
#2.1 100002 Base 100002 Y Base Y 05/01/2022 65 China RED
#3.1 100003 Base 100003 Z Base Z 05/01/2022 75 Ireland GREEN
If the columns need also to be as expected, this needs to be defined as those from DF and DFMap are mixed.
cbind(DF, DFMap[match(DF$C_SHORT, DFMap$C_SHORT), -1])[c(1,7,2:5,8,6)]
# C_SHORT COUNTRY C_FULL ACC_SHORT ACC_FULL Date COLOUR Value
#1 100001 UK Base 100001 X Base X 04/01/2022 BLUE 60
#2 100002 China Base 100002 Y Base Y 04/01/2022 RED 100
#3 100003 Ireland Base 100003 Z Base Z 045/01/2022 GREEN 50
#1.1 100001 UK Base 100001 X Base X 05/01/2022 BLUE 80
#2.1 100002 China Base 100002 Y Base Y 05/01/2022 RED 65
#3.1 100003 Ireland Base 100003 Z Base Z 05/01/2022 GREEN 75