r compare two data.tables by row

Question

I have two data.tables that I want to compare.

But don't know why there is a warning

DT1 <- data.table(ID=c("F","A","E","B","C","D","C"),
                  num=c(59,3,108,11,22,54,241),
                  value=c(90,47,189,38,42,86,280),
                  Mark=c("Mary","Tom","Abner","Norman","Joanne",
                  "Bonnie","Trista"))

DT2 <- data.table(Mark=c("Mary","Abner","Bonnie","Trista","Norman"),
                  numA=c(48,20,88,237,20),
                  numB=c(60,326,54,268,89),
                  valueA=c(78,34,78,270,59),
                  valueB=c(90,190,90,385,75))

DToutput <- DT1[(num > DT2$numA & num < DT2$numB &
                value > DT2$valueA & value < DT2$valueB)]

My goal:

I want to find num and value based on Mark in DT1, and there is a range of numA and numB in DT2.

For example:

For row F in DT1, num = 59 and value = 90, and Mark = "Mary". So, when using by=Mary, you must also match:

num(59) > DT2$numA(48) & num(59) < DT2$numB(60) & value(90) > DT2$valueA(78) & value(90) < DT2$valueB(90)

You can see that 90 < 90 does not hold, so my result will not have row F

I got this warning:

Warning messages:
 1: In num > DT2$numA : longer object length is not a multiple of shorter object lengt
 2: In num < DT2$numB : longer object length is not a multiple of shorter object lengt
 3: In value > DT2$valueA : longer object length is not a multiple of shorter object lengt
 4: In value < DT2$valueB : longer object length is not a multiple of shorter object lengt

How can I modify it to complete what I want to do?

Thank you

Added: Multiple identical Marks may be used in DT2, but the values are not the same range. Does this affect the comparison?

chinsoon12 · Answer 1 · 2019-07-12T00:59:49.180

3

Another option using non-equi inner join:

DT2[DT1, on=.(Mark=Mark, numA<num, numB>num, valueA<value, valueB>value), nomatch=0L, 
    .(ID, num, value, Mark)]

or:

DT1[DT2, on=.(Mark, num>numA, num<numB, value>valueA, value<valueB), nomatch=0L, 
    .(ID, num=x.num, value=x.value, Mark)]

output:

   ID num value   Mark
1:  E 108   189  Abner
2:  C 241   280 Trista

edited Jul 12 '19 at 00:59

answered Jul 12 '19 at 00:53

chinsoon12

25,005
4
25
35

see also https://github.com/Rdatatable/data.table/issues/1700 to understand why we need to type the output columns explictly and https://stackoverflow.com/a/47721442/817778 – chinsoon12 Jul 12 '19 at 01:28

Andrew · Accepted Answer · 2019-07-12T12:29:45.403

Is this generally what you are looking for? I joined the datatables and filtered using between for your conditions. If this is not what you are looking for, can you post a datatable of your expected output?

library(data.table)

DT1[DT2, on = "Mark"][between(num, numA, numB, incbounds = F) & between(value, valueA, valueB, incbounds = F)]

   ID num value   Mark numA numB valueA valueB
1:  E 108   189  Abner   20  326     34    190
2:  C 241   280 Trista  237  268    270    385

EDIT: Benchmark comparison between this approach and the non-equi inner-join from @Chinsoon12 shows that the non-equi inner-join is much faster with even a little more data. It is not a perfect benchmark (just repeated the data.table), but I still think it is clear that the non-equi inner-join is much more efficient.

Unit: milliseconds
           expr      min       lq      mean    median       uq      max neval
        between 233.6378 265.4323 303.14039 301.82455 334.3225 373.2760    10
 non_equi_inner  71.6925  74.1547  96.96584  91.14375  97.6664 179.9907    10

Benchmark code:

DT1 <- data.table(sapply(DT1, rep, 1e3))[, c("num", "value") := lapply(.SD, as.integer), .SDcols = c("num", "value")]
DT2 <- data.table(sapply(DT2, rep, 1e3))[, c("numA", "numB", "valueA", "valueB") := lapply(.SD, as.integer), .SDcols = c("numA", "numB", "valueA", "valueB")]

microbenchmark::microbenchmark(
  between = {
    DT1[DT2, on = "Mark", allow.cartesian = T][between(num, numA, numB, incbounds = F) & between(value, valueA, valueB, incbounds = F)]

  },
  non_equi_inner = {
    DT1[DT2, on=.(Mark, num>numA, num<numB, value>valueA, value<valueB), nomatch=0L, 
        .(ID, num=x.num, value=x.value, Mark), allow.cartesian = T]
  },
  times = 10

)

Thanks, this is the result I want, but I applied this program to somewhere else, and something went wrong. Maybe my other file DT2 has too many repetitions (as my question adds), maybe it is original. Too much information, I added allow.cartesian=TRUE as suggested, but showed error:unused argument (incbounds = FALSE), don't know if you have any thoughts? — kolinunlt, Jul 11 '19 at 18:22
The following are partial error fragments: Check for duplicate key values in i each of which join to the same group in x over and over again. If that's ok, try by=.EACHI to run j for each group to avoid the large allocation. You are sure you wish to proceed, rerun with allow.cartesian=TRUE. — kolinunlt, Jul 11 '19 at 18:22
Two questions: 1) does this code work for the example on your machine? And 2) does the first bracket look like this: `DT1[DT2, on = "Mark", allow.cartesian = T]`? Or is the `allow.cartesian = T` somewhere else? — Andrew, Jul 11 '19 at 18:30
1. This code can be used on my DT1 and DT2 examples, but not on other materials. 2. I am modifying it to DT1[DT2, on = "Mark", allow.cartesian = T] Instead, it shows error:unused argument (incbounds = F) — kolinunlt, Jul 11 '19 at 18:34
I am doing Quit the current R session, it is normal, thank you very much for your help. — kolinunlt, Jul 11 '19 at 18:50

r compare two data.tables by row

2 Answers2

Linked