Filtering by different ranges by group in data.table

Question

I have some data in a table and I would like to do a non-equi join (I think is the right terminology) and filter it by different ranges for different groups. In the below example I would like to filter group "a" so that it only returns values between 1 and 20 (inclusive) and group "b" so it only returns values between 80 and 100 (inclusive). My reading suggests that inrange should be the item to use. I get how to use it in the generic case, but I am not sure how to get it to run with different ranges by group. (example code adapted from ?inrange)

create sample data

set.seed(1234)
Y = data.table(a=sample(1:100,100), val=runif(100), group=c(rep("a",50),rep("b",50)))
range = data.table(group=c("a","b"),start = c(1,80), end = c(20,100))

Try to filter

Y[inrange(a, range$start, range$end),,by=group]

This obviously does not work and instead applies those ranges to the entire dataset and throws the error message Ignoring by= because j= is not supplied. I think it is clear to me that this is not working because I haven't created the 'join' between the range table and Y, but I am not seeing how to make the two tables communicate grouping via inrange.

Note: In reality values in a will be posixct datetimes, but for the sake of simplicity I am not using that here.

Answer 1

Perhaps:

Y[range, K := TRUE, on = .(group, a >= start, a <= end)][!is.na(K),]
#         a        val  group      K
#     <int>      <num> <char> <lgcl>
#  1:     9 0.60189755      a   TRUE
#  2:     5 0.99874081      a   TRUE
#  3:    16 0.55512663      a   TRUE
#  4:     4 0.42944396      a   TRUE
#  5:    14 0.43101637      a   TRUE
#  6:     3 0.47880269      a   TRUE
#  7:     2 0.02220682      a   TRUE
#  8:     6 0.63891131      a   TRUE
#  9:     8 0.83470266      a   TRUE
# 10:    17 0.98304402      a   TRUE
# 11:    98 0.76785547      b   TRUE
# 12:    94 0.30766574      b   TRUE
# 13:    88 0.25814665      b   TRUE
# 14:    89 0.49954639      b   TRUE
# 15:    83 0.50892062      b   TRUE
# 16:    95 0.49443856      b   TRUE
# 17:    97 0.56695890      b   TRUE
# 18:    87 0.98970989      b   TRUE
# 19:    82 0.53190509      b   TRUE
# 20:   100 0.59662376      b   TRUE
#         a        val  group      K

There are other ways to do this, but they involve renaming or loss of information. For instance,

• left-join range and Y, we lose a:

  Y[range, on = .(group, a >= start, a <= end)]
  #         a        val  group   a.1
  #     <int>      <num> <char> <int>
  #  1:     1 0.60189755      a    20
  #  2:     1 0.99874081      a    20
  #  3:     1 0.55512663      a    20
  # ...
  # 18:    80 0.98970989      b   100
  # 19:    80 0.53190509      b   100
  # 20:    80 0.59662376      b   100
  #         a        val  group   a.1
  

  The fix is to copy Y$a into a new variable and join on it instead:

  Y[,a1 := a][range, on = .(group, a1 >= start, a1 <= end)]
  #         a        val  group    a1  a1.1
  #     <int>      <num> <char> <int> <int>
  #  1:     9 0.60189755      a     1    20
  #  2:     5 0.99874081      a     1    20
  #  3:    16 0.55512663      a     1    20
  # ...
  # 18:    87 0.98970989      b    80   100
  # 19:    82 0.53190509      b    80   100
  # 20:   100 0.59662376      b    80   100
  #         a        val  group    a1  a1.1
  

• left-join Y and range, we get a duplicated into start and end but no clear indicator to filter on:

  range[Y, on = .(group, start <= a, end >= a)]
  #       group start   end        val
  #      <char> <int> <int>      <num>
  #   1:      a    28    28 0.85026492
  #   2:      a    80    80 0.23466126
  #   3:      a    22    22 0.98816745
  # ...
  #  98:      b    82    82 0.53190509
  #  99:      b   100   100 0.59662376
  # 100:      b    30    30 0.26388647
  #       group start   end        val
  

  A remedy would be to copy in another field that would give us the indicator of merge that we need to be able to filter. But even with that we have to rename to regain a's data:

  range[, K := TRUE][Y, on = .(group, start <= a, end >= a)][ !is.na(K), ]
  #      group start   end      K        val
  #     <char> <int> <int> <lgcl>      <num>
  #  1:      a     9     9   TRUE 0.60189755
  #  2:      a     5     5   TRUE 0.99874081
  #  3:      a    16    16   TRUE 0.55512663
  # ...
  # 18:      b    87    87   TRUE 0.98970989
  # 19:      b    82    82   TRUE 0.53190509
  # 20:      b   100   100   TRUE 0.59662376
  #      group start   end      K        val