3

I have some data.table with an amount column like:

n = 1e5
set.seed(1)

dt <- data.table(id = 1:n, amount = pmax(0,rnorm(n, mean = 5e3, sd = 1e4)))

And a vector of breaks given like:

breaks <- as.vector( c(0, t(sapply(c(1, 2.5, 5, 7.5), function(x) x * 10^(1:4))) ) )

For each interval defined by these breaks, I want to use data.table syntax to:

  1. get counts of amount contained
  2. get counts of amount equal to or greater than the left bound (basically n * (1-cdf(amount))

For 1, this mostly works, but doesn't return rows for the empty intervals:

dt[, .N, keyby = breaks[findInterval(amount,breaks)] ] #would prefer to get 0 for empty intvl

For 2, I tried:

dt[, sum(amount >= thresh[.GRP]), keyby = breaks[findInterval(amount,breaks)]  ]

but it didn't work because sum is restricted to within the group, not beyond. So came up with a workaround, which also returns the empty intervals:

dt[, cbind(breaks, sapply(breaks, function(x) sum(amount >= x)))] # desired result

So, what's the data.table way to fix my 2. and to get the empty intervals for both?

C8H10N4O2
  • 18,312
  • 8
  • 98
  • 134
  • See some questions on `foverlaps`, just a few [1](http://stackoverflow.com/questions/25815032/finding-overlaps-between-interval-sets-efficient-overlap-joins), [2](http://stackoverflow.com/questions/28540466/how-to-identify-overlaps-in-multiple-columns), [3](http://stackoverflow.com/questions/34245295/efficient-method-for-counting-open-cases-at-time-of-each-cases-submission-in-la), [4](http://stackoverflow.com/questions/27574775/is-it-possible-to-use-the-r-data-table-funcion-foverlaps-to-find-the-intersectio) – MichaelChirico Mar 25 '16 at 14:47

1 Answers1

6

I would consider doing this:

mybreaks = c(-Inf, breaks, Inf)
dt[, g := cut(amount, mybreaks)]
dt[.(g = levels(g)), .N, on="g", by=.EACHI]


                  g     N
 1:        (-Inf,0] 30976
 2:          (0,10]    23
 3:         (10,25]    62
 4:         (25,50]    73
 5:         (50,75]    85
 6:        (75,100]    88
 7:       (100,250]   503
 8:       (250,500]   859
 9:       (500,750]   916
10:     (750,1e+03]   912
11: (1e+03,2.5e+03]  5593
12: (2.5e+03,5e+03]  9884
13: (5e+03,7.5e+03]  9767
14: (7.5e+03,1e+04]  9474
15: (1e+04,2.5e+04] 28434
16: (2.5e+04,5e+04]  2351
17: (5e+04,7.5e+04]     0
18:  (7.5e+04, Inf]     0

You can use cumsum if you want the CDF.

Frank
  • 66,179
  • 8
  • 96
  • 180
  • The syntax in the third line is new to me, but I will read up on it. Thanks for your help. – C8H10N4O2 Mar 25 '16 at 20:03
  • 2
    It's pretty new, in version 1.9.6, and hasn't been added to the vignette on merges yet. `on=` is just a way of doing `X[Y]` merges even when `X` is not keyed. @C8H10N4O2 – Frank Mar 25 '16 at 20:30