[datatable-help] Help summarizing monthly data sequences

[Bacou, Melanie]

Hi,
I hope this is an acceptable data.table problem (also posted to 
StackOverflow 
http://stackoverflow.com/questions/38890034/using-data-table-to-summarize-monthly-sequences-count-specific-events).

I have a 3-column table with:
- `id` geographic location IDs (303,453 locations)
- `month` month over 24 years 1990-2014
- `spei` a climatic index that varies between -7 and 7.

I need to count the occurrence of droughts at each location over the 
entire 1990-2014 period. A drought event is defined as "a period in 
which the SPEI is continuously negative and the SPEI reaches a value of 
-1.0 or less. Drought starts when the SPEI first falls below zero and 
ends with the first positive SPEI value following a value of -1.0 or less".

I know this should be feasible using shift() and rolling joins but would 
very welcome some pointers!

# Sample table structure
 > dt <- data.table(
       id = rep(1:303453, each=24*12),
       month = rep(seq(as.Date("1990-01-01"), as.Date("2014-12-31"), 
"month"), 303453),
       spei = runif(303453*24*12, -7, 7))

Hoping some of you are more used to working with time series.
Many thanks, --Mel.


-- 
Melanie BACOU
International Food Policy Research Institute
Snr. Program Manager, Spatial Data and Analytics
Work   +1(202)862-5699
E-mail m.bacou at cgiar.org
Visit  www.harvestchoice.org