Create index multiplying number of days over a threshold by amount over threshold by group in R

Question

I am working with a moose telemetry dataset in R consisting of multiple moose, multiple gps locations per moose per day, and temperature data acquired per day. I want to create an index of thermal stress by taking the number of days a moose experiences temperatures above -5 and multiple by the number of degrees above that threshold. Each time the temperature goes below -5 the count of days above the threshold resets. I am not interested in creating an index below -5, so data points with temperatures below -5 can have an index of NA.

Each individual moose may not have locations for the same days as other moose, so I need to repeat this process per each individual moose.

Here is a sample dataset below, I have columns similar to Moose, Date, and Temperature, and am looking to create a column like TempIndex.

As you can see on 2013-01-01 Moose 1 (M1) and Moose 2 (M2) experienced a temperature of 2C, which is 7C above -5 and was the second day above the -5 threshold, creating an index of 14 (aka 2*7).

Moose <- c('M1','M1','M1','M1',
           'M1','M1','M1','M1',
           'M1','M1','M1','M1',
           'M2','M2','M2','M2',
           'M2','M2','M2','M2',
           'M2','M2','M2','M2')

Date <- as.Date(c('2012-12-30','2012-12-31','2012-12-31','2013-01-01',
                  '2013-01-01','2013-01-01','2013-01-02','2013-01-02',
                  '2013-01-03','2013-01-03','2013-01-04','2013-01-04',
                  '2012-12-30','2012-12-30','2012-12-31','2012-12-31',
                  '2013-01-01','2013-01-01','2013-01-02','2013-01-02',
                  '2013-01-02','2013-01-03','2013-01-04','2013-01-04'))

Temperature <- c(-6,-4,-4, 2,
                  2, 2,-8,-8,
                  1, 1,-9,-9,
                 -6,-6,-4,-4,
                  2, 2,-8,-8,
                 -8, 1,-9,-9)

TempIndex <- c(NA, 1, 1,14,
               14,14,NA,NA,
                6, 6,NA,NA,
               NA,NA, 1, 1,
               14,14,NA,NA,
               NA, 6,NA,NA)

dat <- data.frame(Moose,Date,Temperature,TempIndex)


dat

   Moose    Date    Temperature TempIndex
1     M1 2012-12-30          -6        NA
2     M1 2012-12-31          -4         1
3     M1 2012-12-31          -4         1
4     M1 2013-01-01           2        14
5     M1 2013-01-01           2        14
6     M1 2013-01-01           2        14
7     M1 2013-01-02          -8        NA
8     M1 2013-01-02          -8        NA
9     M1 2013-01-03           1         6
10    M1 2013-01-03           1         6
11    M1 2013-01-04          -9        NA
12    M1 2013-01-04          -9        NA
13    M2 2012-12-30          -6        NA
14    M2 2012-12-30          -6        NA
15    M2 2013-12-31          -4         1
16    M2 2013-12-31          -4         1
17    M2 2013-01-01           2        14
18    M2 2013-01-01           2        14
19    M2 2013-01-02          -8        NA
20    M2 2013-01-02          -8        NA
21    M2 2013-01-02          -8        NA
22    M2 2013-01-03           1         6
23    M2 2013-01-04          -9        NA
24    M2 2013-01-04          -9        NA

Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Nov 10 '21 at 07:11

Ben · Accepted Answer · 2021-11-10T20:45:02.023

You can use tidyverse and try the following.

After grouping by Moose, you can create groups where the temperature transitions from below -5 to above -5 degrees. When this happens, it will start a new group number, which can also be used in your grouping.

Then, you can calculate the temp index based on your formula. This assumes your dates are in chronological order (if not, you can use arrange).

The results seem to match (though I think the date for moose M2 is not what was intended going from '2012-12-30' to '2013-12-31'.

library(tidyverse)

dat %>%
  group_by(Moose) %>%
  group_by(Group = cumsum(Temperature > -5 & lag(Temperature <= -5, default = 0)), .add = TRUE) %>%
  mutate(NewTempIndex = ifelse(Temperature > -5, (Temperature + 5) * (Date - first(Date) + 1), NA_integer_))

Output

   Moose Date       Temperature TempIndex Group NewTempIndex
   <chr> <date>           <dbl>     <dbl> <int>        <dbl>
 1 M1    2012-12-30          -6        NA     0           NA
 2 M1    2012-12-31          -4         1     1            1
 3 M1    2012-12-31          -4         1     1            1
 4 M1    2013-01-01           2        14     1           14
 5 M1    2013-01-01           2        14     1           14
 6 M1    2013-01-01           2        14     1           14
 7 M1    2013-01-02          -8        NA     1           NA
 8 M1    2013-01-02          -8        NA     1           NA
 9 M1    2013-01-03           1         6     2            6
10 M1    2013-01-03           1         6     2            6
# … with 14 more rows

Thank you! The code mostly works with original dataset, but I'm having issues creating the groups, which is resulting in the cumulative sum only resetting per individual and not per stress period within individual. When I run the code I don't get any errors but instead all values under the group column are NA. I think this is because some individuals I have start during a period of thermal stress (meaning already above -5). Is there a way to start the group count within a period of thermal stress? (Lets assume all individuals start on the same day). — Kelly.M, Nov 10 '21 at 19:11
@Kelly.M Try using adding a `default` for `lag` - such as: `lag(Temperature <= -5, default = 0)` - let me know if this works (if so, will update answer). — Ben, Nov 10 '21 at 20:37
Since I'm new my reputation is too small to upvote haha. Once I have a better reputation I will upvote :) — Kelly.M, Nov 12 '21 at 16:50

Create index multiplying number of days over a threshold by amount over threshold by group in R

1 Answers1