79674543

Date: 2025-06-21 15:14:43
Score: 0.5
Natty:
Report link

I've come up with a fairly inelegant solution to achieve the double join (thanks to r2evans for the terminology and the point in the right direction!):

# Step 1: split dt1 into apple and pear tables
apple_dt <- dt1[type == "apple"]
pear_dt <- dt1[type == "pear"]

# Step 2: merge dt2 with apple_dt, and dt2 with pear_dt 
merged_apple <- merge(dt2, apple_dt[ ,':='(type=NULL)] , by.x = "apple", by.y = "id")
names(merged_apple)[4]<-"apple.value"
merged_pear <- merge(dt2, pear_dt, by.x = "pear", by.y = "id")

# Step 3: cbind() and rename
dt3 <- cbind(merged_apple, merged_pear$value)
names(dt3)[5]<- "pear.value"

dt3

# Key: <apple>
#    apple   pear measure apple.value pear.value
#    <char> <char>   <num>       <num>      <num>
# 1:      a      d       1           1          1
# 2:      a      d       2           5          8
# 3:      b      d       1           1          1
# 4:      b      d       2           9          8
# 5:      c      f       1           4          9
# 6:      c      f       2           2          5
Reasons:
  • Blacklisted phrase (0.5): thanks
  • Long answer (-1):
  • Has code block (-0.5):
  • Self-answer (0.5):
  • Low reputation (1):
Posted by: tpotter