Geek Answers Handbook

Using multiple reference values ​​in a lookup table

Using data.table in R, I have two data tables. One of them is my actual dataset, and the other is the lookup table.

Dataset:

dt <- data.table(Name = c("John", "Bob", "Sue", "Trish"), Ref = c("a", "A0", "BA", "c"))

**Name**    **Ref**  
John        a  
Bob         A0  
Sue         BA  
Trish       c

Search Table:

lookup <- data.table(Ref1 = c("a", "b", "c"), Ref2 = c("A0", "AA", "BA"), Value = 1:3)

**Ref1** **Ref2** **Value**
a        A0       1
b        aa       2
c        BA       3

As you can see, there are two different links for each value in the lookup table. I would like to either translate the Ref2 values ​​into Ref1 and then do a search or do all the queries directly.

My approach so far has been to split dt, so each split contains each formatting style, then it looks through the values ​​and finally rbind.

dt1 <- dt[dt[, nchar(Ref) == 2,],]
dt2 <- dt[dt[, nchar(Ref) == 1,],]

setnames(lookup, c("Ref1", "Ref", "Value"))
setkey(lookup, Ref)

dt1Merged <- merge(dt1, lookup, by = "Ref")[,c(1:2, 4), with = F]

setnames(lookup, c("Ref", "Ref2", "Value"))
setkey(lookup, Ref)

dt2Merged <- merge(dt2, lookup, by = "Ref")[,c(1:2, 4), with = F]

result <- rbind(dt1Merged, dt2Merged)

. ? , , data.table, .

+4
1

, , , data.tables

newlookup=melt(lookup,id.vars="Value")
setnames(newlookup, c("Value", "oldRef", "Ref"))
merge(dt,newlookup,by="Ref")
+7

More articles:


All Articles