CODEWITHSUNDEEP
rdataframematching
Fjellbekken
Fjellbekken

Reputation: 25

R: Combining dataset and lookup-table to extract value to new colume

I want to combine to dataframes, df1 with 15.000 obs and df2 consisting of 2.3 mill. I'm trying to match values, if df1$col1 == df2$c1, AND df1$col2 == df2$c2, then insert value from df2$dummy, to df1$col3. If no match in both, do nothing. All are 8 digits, except df2$dummy, which is a dummy of 0 or 1.

df1  col1       col2      col3
1    25382701   65352617  -
2    22363658   45363783  -
3    20019696   23274747  -

df2  c1         c2        dummy
1    17472802   65548585  1
2    20383829   24747473  0
3    20019696   23274747  0 
4    01382947   21930283  1
5    22123425   65382920  0

In the example the only match is row 3, and the value 0 from the dummy column should be inserted in col3 row3. I've tried to make a look-up table, a function of for and if, but not found a solution when requiring matches in two dataframes. (No need to say I guess, but I'm new to R and programming..)

Upvotes: 2

Views: 190

Answers (1)

akrun
akrun

Reputation: 887291

We can use a join in data.table

library(data.table)
df1$col3 <- NULL
setDT(df1)[df2, col3 := i.dummy, on = .(col1 = c1, col2 = c2)]
df1
#       col1     col2 col3
#1: 25382701 65352617   NA
#2: 22363658 45363783   NA
#3: 20019696 23274747    0

data

df1 <- structure(list(col1 = c(25382701L, 22363658L, 20019696L), col2 = c(65352617L, 
45363783L, 23274747L), col3 = c("-", "-", "-")), class = "data.frame", row.names = c("1", 
"2", "3"))

df2 <- structure(list(c1 = c(17472802L, 20383829L, 20019696L, 1382947L, 
22123425L), c2 = c(65548585L, 24747473L, 23274747L, 21930283L, 
65382920L), dummy = c(1L, 0L, 0L, 1L, 0L)), class = "data.frame",
row.names = c("1", 
"2", "3", "4", "5"))

Upvotes: 2

Related Questions