Display of values ​​between data frames R

Question

create example data:

df <- data.frame(date=c("2017-01-01","2017-01-02", "2017-01-03", "2017-01-04", "2017-01-05"), X1=c("A", "B", "C", "D", "F"), X2=c("B", "A", "D", "F", "C")) df2 <- data.frame(date=c("2017-01-01","2017-01-02", "2017-01-03", "2017-01-04", "2017-01-05"), A=c("3", "4", "2", "1", "5"), B=c("6", "2", "5", "1", "1"), C=c("1", "4", "5", "2", "3"), D=c("67", "67", "63", "61", "62"), F=c("31", "33", "35", "31", "38")) 

So, I have two data frames, and I want to match the values ​​from df2 to df by date and X1 and X2 and create new variables for them. What makes this difficult for me is that the consistent values ​​in df2 are in the code names. The end result should look like this:

 > result date X1 X2 Var1 Var2 1 2017-01-01 AB 3 6 2 2017-01-02 BA 2 4 3 2017-01-03 CD 5 63 4 2017-01-04 DF 61 31 5 2017-01-05 FC 38 3 result <- data.frame(date=c("2017-01-01","2017-01-02", "2017-01-03", "2017-01-04", "2017-01-05"), X1=c("A", "B", "C", "D", "F"), X2=c("B", "A", "D", "F", "C"), Var1=c("3", "2", "5", "61", "38"), Var2=c("6", "4", "63", "31", "3")) 

I wanted to use mapvalues, but couldn't figure it out. The second thought was that the long formatting (fusion) with df2 and the attempt then, but also failed.

Okay, here is my best attempt, it just feels like there might be a more efficient way if you need to create several (> 50) new variables for a data frame.

 df2.long <- melt(df2, id.vars = c("date")) df$Var1 <- na.omit(merge(df, df2.long, by.x = c("date", "X1"), by.y = c("date", "variable"), all.x = FALSE, all.y = TRUE))[,4] df$Var2 <- na.omit(merge(df, df2.long, by.x = c("date", "X2"), by.y = c("date", "variable"), all.x = FALSE, all.y = TRUE))[,5] 

Answer 1

Opportunity with mapply :

 df$Var1 <- mapply(function(day, col) df2[df2$date==day, as.character(col)], day=df$date, col=df$X1) df$Var2 <- mapply(function(day, col) df2[df2$date==day, as.character(col)], day=df$date, col=df$X2) df # date X1 X2 Var1 Var2 #1 2017-01-01 AB 3 6 #2 2017-01-02 BA 2 4 #3 2017-01-03 CD 5 63 #4 2017-01-04 DF 61 31 #5 2017-01-05 FC 38 3 

NB:
If you have more columns to change (not only 2, as in your example), you can use lapply to cycle through the X. columns.:

 df[, paste0("Var", 1:2)] <- lapply(df[,paste0("X", 1:2)], function(value) { mapply(function(day, col) df2[df2$date==day, as.character(col)], day=df$date, col=value)}) 

Answer 2

Using dplyr and tidyr :

 df2_m <- group_by(df2, date) %>% gather('X1', 'var', -date) left_join(df, df2_m) %>% left_join(df2_m, by = c('date', 'X2' = 'X1')) %>% rename(Var1 = var.x, Var2 = var.y) -> result 

Answer 3

Double melt> join> dcast parameter using data.table

 library(data.table) # v>=1.10.0 dcast( melt(setDT(df), 1L)[ # melt the first table by date melt(setDT(df2), 1L), # melt the second table by date on = .(date, value = variable), # join by date and the letters nomatch = 0L], # remove everything that wasn't matched date ~ variable, # convert back to long format value.var = c("value", "i.value")) # take both values columns # date value_X1 value_X2 i.value_X1 i.value_X2 # 1: 2017-01-01 AB 3 6 # 2: 2017-01-02 BA 2 4 # 3: 2017-01-03 CD 5 63 # 4: 2017-01-04 DF 61 31 # 5: 2017-01-05 FC 38 3 

Answer 4

We can use match to get the column index "df2" from the columns "X1" and "X2", cbind with a sequence of rows, use the row / column index to extract the values ​​in 'df2' and assign the output to create the columns "Var"

 df[paste0("Var", 1:2)] <- lapply(df[2:3], function(x) df2[-1][cbind(1:nrow(df2), match(x, names(df2)[-1]))]) df # date X1 X2 Var1 Var2 #1 2017-01-01 AB 3 6 #2 2017-01-02 BA 2 4 #3 2017-01-03 CD 5 63 #4 2017-01-04 DF 61 31 #5 2017-01-05 FC 38 3 

Answer 5

Use of melt and compliance:

 df2l<-melt(df2, measure=c("A","B","C","D","F")) Indices <- match(paste(df$date, df$X1), paste(df2l$date,df2l$variable)) df$Var1 <- df2l$value[Indices] Indices2 <- match(paste(df$date, df$X2), paste(df2l$date,df2l$variable)) df$Var2 <- df2l$value[Indices2]