Merging two data frames with unique keys
I want to select rows of data1 if the value of the first column exists in data2:
data1<-data.table(a=c(1,2,3,1),b=c(1,4,6,3))
data2<-data.table(a=c(1,3,1,5,6))
Desired output:
a b
1 1
3 6
1 3
The merge doesn't work as the keys are not unique, any other idea?
+3
Soheil
source
to share
2 answers
Here's a no joins approach:
> unique(data1[a %in% data2[,a],])
a b
1: 1 1
2: 3 6
3: 1 3
+3
nrussell
source
to share
We can create a sequence column ("ind") in each dataset for column "a" and "join" after setting the key column as "a" and "ind"
data1[, ind:=1:.N, a]
data2[, ind:= 1:.N, a]
setkey(data1, a, ind)[data2, nomatch=0][, ind:=NULL]
# a b
#1: 1 1
#2: 3 6
#3: 1 3
+4
akrun
source
to share