2019-07-12
r compare two data.tables by row
stackoverflow
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?

1

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
``````
r compare two data.tables by row
Related Questions
Related Documents