S Wellman S Wellman - 2 months ago 8
R Question

R - Match row to row and row to column to pull element between two dataframes

I have two data frames "df1" and "df2".

drugs<-c("Drug1", "Drug1", "Drug1", "Drug2", "Drug2", "Drug2")
level<-c("1", "2", "3", "1", "2", "3")
df1<-data.frame(drugs, level)

standards<-c("Drug1", "Drug2")
std1<-c("100", "100")
std2<-c("200", "200")
std3<-c("300", "300")
df2<-data.frame(standards, std1, std2, std3)


I want to cross-reference the drug name in df1 with the drug name in df2, then match the level (1, 2, or 3) with the column in df2 (std1 with level 1, std2 with level 2, etc) and pull the amount (100, 200, or 300) from df2 and add it as a new column in df1 named "Amount".

I haven't been able to determine how I can cross reference based on rows of one dataframe with the row and column of another data frame to pull the associated element from that second data frame.

Any suggestions?

Answer

Here's One possible way of first reshaping df2 to a long format, stripping the std and joinig back to df1 while creating Amount in place (using data.table)

library(data.table)
tmp <- melt(setDT(df2), 1)[, level := sub("std", "", variable, fixed = TRUE)]
setDT(df1)[tmp, Amount := value, on = c(drugs = "standards", "level")]
df1
#    drugs level Amount
# 1: Drug1     1    100
# 2: Drug1     2    200
# 3: Drug1     3    300
# 4: Drug2     1    100
# 5: Drug2     2    200
# 6: Drug2     3    300