Calculation of distribution using data.table

Question

A quite straightforward question here.

I did do the search on all relevant posts on stackoverflow and google but failed to find the answer. Reference is made to Find which interval row in a data frame that each element of a vector belongs in and Split a vector into chunks in R

Data:

Time Price Volume Amount Flag 1: 2016-01-04 09:05:06 105.0 9500 993700 1 2: 2016-01-04 09:20:00 104.1 23500 2446350 0 3: 2016-01-04 09:30:00 104.1 18500 1924550 1 4: 2016-01-04 09:30:01 103.9 12500 1300550 0 5: 2016-01-04 09:30:02 104.1 16118 1675233 1 6: 2016-01-04 09:30:05 104.0 13000 1352200 0 7: 2016-01-04 09:30:06 104.1 2500 260100 1 8: 2016-01-04 09:30:07 104.1 1500 156150 1 9: 2016-01-04 09:30:08 104.3 500 52150 1 10: 2016-01-04 09:30:10 104.0 1000 104000 0 11: 2016-01-04 09:30:11 103.9 1000 103900 0 12: 2016-01-04 09:30:15 104.0 3500 364450 1 13: 2016-01-04 09:30:17 104.3 2000 208450 1 14: 2016-01-04 09:30:19 104.3 1500 156450 1 15: 2016-01-04 09:30:20 104.4 500 52200 1 16: 2016-01-04 09:30:21 104.4 1500 156600 1 17: 2016-01-04 09:30:22 104.4 1000 104400 1 18: 2016-01-04 09:30:24 104.4 1500 156600 1 19: 2016-01-04 09:30:25 104.0 2000 208000 0 20: 2016-01-04 09:30:27 104.1 3500 364350 1

Similar to preparation work for Histogram or Hist object, I would like to build distribution of Volume according to different levels of Price.

To be specific:

1. Divide the range of Price into N pieces/bins (Say, N = 5)
2. Sum up Volume for different bins

I tried split function, and several other such as cut_number function in ggplot2 package. And I think findInterval might be helpful and the code should be like this:

library(data.table)
dt[, sum(Volume), by = findInterval(Price,cut_number(Price, 5))] # Do not work
# I think the key should be in `by` part. 
dt[, sum(Volume), by = some functions here]

Reproducible data

dt <- data.table(structure(list(Time
  = structure(c(1451898306, 1451899200,
  1451899800,1451899801, 1451899802,
  1451899805, 1451899806, 1451923195,
  1451923196,1451923200), class =
  c("POSIXct", "POSIXt"), tzone =
  "GMT"),Price = c(105, 104.1,
  104.1, 103.9, 104.1, 104, 104.1, 103,102.9, 102.9),
  Volume = c(9500L, 23500L, 18500L,
  12500L,16118L, 13000L, 2500L, 4000L, 2000L, 1000L),
  Amount = c(993700L,2446350L,
  1924550L, 1300550L, 1675233L, 1352200L, 260100L,412000L, 206016L, 102880L),
  Flag = c(1L, 0L, 1L, 0L, 1L,0L,
  1L, 1L, 0L, 1L)), .Names = c("Time",
  "Price", "Volume","Amount",
  "Flag"), class = c("data.table",
  "data.frame"), row.names = c(NA,-10L)))

Desired Output (For illustration ONLY):

Price Range    Sum
102.3 - 102.5 300000
.
. (Total N bins, thus N rows)
.
105.0 - 105.3  500000

And also I tried several other combinations, all failed.

Any suggestions are welcome! Thanks a lot.

Answer 1

Assuming that N refers to number of pieces per bin and not number of rows. There is probably a shorter way without creating an index. But here is one where you group them first and then sum

setorder(dt, Price)
dt[,GROUP:=ceiling(seq_along(Price)/5)][, 
    list(PriceRange=paste(range(Price), collapse=" - "), 
         Volume=sum(Volume)), 
    by="GROUP"]

---

EDIT after OP's comments

If you want bands of equal width, you can use this:

dt[, sum(Volume), by=cut(Price, 5)]

If you want to show all bands, you can use this

dt[,Band:=cut(Price, 5)]
dt[dt[, list(Band=levels(Band))], on="Band"][, sum(Volume, na.rm=TRUE), by="Band"]

HTH