CODEWITHSUNDEEP
rdatedata.tablecumsum
BgnR
BgnR

Reputation: 319

R: Calculations over consecutive days?

This question builds upon this question:

R: Calculate moving maximum slope by week accounting for factors

My question:

The code pasted below calculates maximum slope over a 7-day period using length(HDD). I would like to be more discriminate in that, I only want MaxSlope calculated for consecutive 7-day periods.

For example, a gap exists in the data from 2004-12-26 to 2004-12-30. Considering only this portion of data I have copied here, the MaxSlope should only be calculated for 2004-12-23 and 2004-12-24. All other dates should have "NA" inserted. This dataset will grow to several million records, hence efficiency is important.

NOTE: I subset my data.frame to provide only the columns important here. The by statement in the MaxSlope code is important as it is applied to the entire data.frame.

I have no idea where to begin with consecutive date calculations. Any ideas?

Thank you!

Code I used to arrive at Maximum Slope Calculation:

RawByDayALL <- data.table(RawByDayALL)
RawByDayALL[, MaxSlope := if(length(HDD)<7) {rep(NA_real_, length(HDD))} else {filter(HDD, c(1,1,1,1,1,1,0)/7)}, by=list(WinterID, SiteID, SubstrateConcat)]
RawByDayALL[is.na(MaxSlope), MaxSlope := -99L]

Structure of my data: 

> dput(RawByDayALL[650:660])
structure(list(WinterID = structure(c(6L, 6L, 6L, 6L, 6L, 6L, 
6L, 6L, 6L, 6L, 6L), .Label = c("2002", "2002_2003", "2003", 
"2003_2004", "2004", "2004_2005", "2005", "2005_2006", "2006", 
"2006_2007", "2007", "2007_2008", "2008"), class = "factor"), 
    Date = structure(c(12771, 12772, 12773, 12774, 12775, 12776, 
    12777, 12778, 12782, 12783, 12784), class = "Date"), SiteID = structure(c(1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "NW_SB", class = "factor"), 
    SubstrateConcat = structure(c(2L, 2L, 2L, 2L, 2L, 2L, 2L, 
    2L, 2L, 2L, 2L), .Label = c("B_A", "B_B"), class = "factor"), 
    HDD = c(17.3533333333333, 35.1066666666667, 82.6266666666667, 
    51.68, 36.22, 39.6066666666667, 38.0533333333333, 47.8333333333333, 
    4.18, 9.66, 1.5), MaxSlope = c(30.4104761904762, 33.3885714285714, 
    37.5133333333333, 40.4704761904762, 42.2885714285714, 31.0819047619048, 
    25.0790476190476, 20.1190476190476, 14.6019047619048, 9.19428571428571, 
    2.6552380952381)), .Names = c("WinterID", "Date", "SiteID", 
"SubstrateConcat", "HDD", "MaxSlope"), class = c("data.table", 
"data.frame"), row.names = c(NA, -11L), .internal.selfref = <pointer: 0x0000000000100788>)

What a portion of the data look like:

    WinterID        Date    SiteID  SubstrateConcat   HDD           MaxSlope  
650 2004_2005   2004-12-19  NW_SB   B_B               17.35333333   30.41047619
651 2004_2005   2004-12-20  NW_SB   B_B               35.10666667   33.38857143
652 2004_2005   2004-12-21  NW_SB   B_B               82.62666667   37.51333333
653 2004_2005   2004-12-22  NW_SB   B_B               51.68000000   40.47047619
654 2004_2005   2004-12-23  NW_SB   B_B               36.22000000   42.28857143
655 2004_2005   2004-12-24  NW_SB   B_B               39.60666667   31.08190476
656 2004_2005   2004-12-25  NW_SB   B_B               38.05333333   25.07904762
657 2004_2005   2004-12-26  NW_SB   B_B               47.83333333   20.11904762
658 2004_2005   2004-12-30  NW_SB   B_B               4.18000000    14.60190476
659 2004_2005   2004-12-31  NW_SB   B_B               9.66000000    9.19428571
660 2004_2005   2005-01-01  NW_SB   B_B               1.50000000    2.65523810

EDITED to include answer provided by @eddi. Thank you for the simple fix!

    RawByDayALL <- data.table(RawByDayALL)
    RawByDayALL[, MaxSlope := if(length(HDD)<7) {rep(NA_real_, length(HDD))} else {filter(HDD, c(1,1,1,1,1,1,0)/7)}, by=list(WinterID, SiteID, SubstrateConcat, cumsum(diff(c(Date[1], as.IDate(Date))) > 1))]
    RawByDayALL[is.na(MaxSlope), MaxSlope := -99L]

Upvotes: 1

Views: 933

Answers (1)

eddi
eddi

Reputation: 49448

This will give you the consecutive day grouping that you need:

dt[, cumsum(diff(c(Date[1], as.IDate(Date))) > 1)]

And this is how you can put it in your by in addition to your other columns:

dt[, your_calculation,
     by = list(various_columns, cumsum(diff(c(Date[1], as.IDate(Date))) > 1))]

Upvotes: 4

Related Questions