Two possible approaches:
1: 'wide'-approach
With the dplyr and purrr packages:
library(dplyr) library(purrr) df12 <- left_join(df1, df2, by = 'id') cbind(id=df12[,1], map2_df(df12[,2:4], df12[,5:7], `/`))
With the data.table package (method borrowed from here):
library(data.table) # convert to 'data.tables' setDT(df1) setDT(df2) # creates two vectors of matching columnnames xcols = names(df1)[-1] icols = paste0("i.", xcols) # join and do the calculation df1[df2, on = 'id', Map('/', mget(xcols), mget(icols)), by = .EACHI]
which both give:
id val.1 val.2 val.3 1: 1 0.9600000 0.9750000 0.9306122 2: 1 0.8960000 0.9250000 0.9387755 3: 1 0.9520000 1.0000000 0.9795918 4: 2 0.9892857 0.9574468 0.9074733 5: 2 1.0392857 1.0283688 0.9430605 6: 2 1.0142857 1.0000000 1.0142349
2: 'long'-approach
Another option is to reshape your dataframes into long format, then merge/join them and do the calculation.
With the data.table-package:
library(data.table) dt1 <- melt(setDT(df1), id = 1) dt2 <- melt(setDT(df2), id = 1) dt1[dt2, on = c('id','variable'), value := value/i.value][]
With the dplyr and tidyr packages:
library(dplyr) library(tidyr) df1 %>% gather(variable, value, -id) %>% left_join(., df2 %>% gather(variable, value, -id), by = c('id','variable')) %>% mutate(value = value.x/value.y) %>% select(id, variable, value)
which both give:
id variable value 1: 1 val.1 0.9600000 2: 1 val.1 0.8960000 3: 1 val.1 0.9520000 4: 2 val.1 0.9892857 5: 2 val.1 1.0392857 6: 2 val.1 1.0142857 7: 1 val.2 0.9750000 8: 1 val.2 0.9250000 9: 1 val.2 1.0000000 10: 2 val.2 0.9574468 11: 2 val.2 1.0283688 12: 2 val.2 1.0000000 13: 1 val.3 0.9306122 14: 1 val.3 0.9387755 15: 1 val.3 0.9795918 16: 2 val.3 0.9074733 17: 2 val.3 0.9430605 18: 2 val.3 1.0142349
Used data:
df1 <- structure(list(id = c(1, 1, 1, 2, 2, 2), val.1 = c(240, 224, 238, 277, 291, 284), val.2 = c(234, 222, 240, 270, 290, 282), val.3 = c(228, 230, 240, 255, 265, 285)), .Names = c("id", "val.1", "val.2", "val.3"), class = "data.frame", row.names = c(NA, -6L)) df2 <- structure(list(id = c(1, 2), val.1 = c(250, 280), val.2 = c(240, 282), val.3 = c(245, 281)), .Names = c("id", "val.1", "val.2", "val.3"), class = "data.frame", row.names = c(NA, -2L))
