Compute the maximum value by group and by a time "window"

user_7065778 0
Viewed 161

For the following Panel data (Tracking the Value for unit "ID" over "Time" :

ID=c(1,1,1,1,1,2,2,2,2,2)
Time=c(1,2,3,4,5,1,2,3,4,5)
Value=c(1,9,4,8,5,2,5,9,7,6)

I would like to create a vector which is a maximum value for each "ID" over the last two days (assuming that the unit of Time is a day)

Output vector "Max_Value" would be as follows:

Max_Value=c(1,9,9,8,8,2,5,9,9,7)

To clarify, here's how Max_Value is computed for ID "1".

For ID "1", the maximum value by the "Time=1" is 1, which is a maximum of {1}.

Similarly, for ID "1", the maximum value at the "Time 2" is 9, which is a maximum of {1,9}.

Again, for ID "1", the maximum value at the "Time 3" is 9, which is a maximum of {9,4}.

For ID "1", the maximum value at the "Time 4" is 8, which is a maximum of {4,8}.

For ID "1", the maximum value at the "Time 5" is 8, which is a maximum of {8,5}.

max r
4 Answers

If you just have vectors and Time is complete and sorted, slide + ave could work well for you:

ave(Value, ID, FUN = function(x) slider::slide_dbl(x, max, .before=1))
#> [1] 1 9 9 8 8 2 5 9 9 7

Or even a full Base R solution:

Value[ave(Value, ID, FUN = function(x) c(0, -(diff(x)<0))) + seq_along(Value)]
#> [1] 1 9 9 8 8 2 5 9 9 7

Otherwise you can solve it with dplyr + slider:

library(dplyr)
data.frame(ID, Time, Value) %>% 
 group_by(ID) %>% 
 mutate(Max_Value = slider::slide_index_dbl(Value, Time, max, .before=1)) %>% 
 ungroup()

#> # A tibble: 10 x 4
#>       ID  Time Value Max_Value
#>    <dbl> <dbl> <dbl>     <dbl>
#>  1     1     1     1         1
#>  2     1     2     9         9
#>  3     1     3     4         9
#>  4     1     4     8         8
#>  5     1     5     5         8
#>  6     2     1     2         2
#>  7     2     2     5         5
#>  8     2     3     9         9
#>  9     2     4     7         9
#> 10     2     5     6         7
user_99182650

Try this:

library(data.table)
dt <- data.table(ID=c(1,1,1,1,1,2,2,2,2,2),
                 Time=c(1,2,3,4,5,1,2,3,4,5),
                 Value=c(1,9,4,8,5,2,5,9,7,6))

max_v <- function(x) max(dt[ID==x$ID & Time <= x$Time & Time > (x$Time-2) ,Value])

sapply(split(dt,1:nrow(dt)),max_v)
user_146695450

I believe you can use a rollapply() style function from zoo setting a width of 2:

library(dplyr)
library(tidyr)
library(zoo)
#Data
df <- data.frame(ID,Time,Value)
#Code
newdf <- df %>% group_by(ID) %>%
  mutate(Max=rollapply(Value,width=2,FUN=function(x) max(x, na.rm=TRUE),
                       by=1, by.column=TRUE,partial=TRUE,fill=NA, align="right"))

Output:

# A tibble: 10 x 4
# Groups:   ID [2]
      ID  Time Value   Max
   <dbl> <dbl> <dbl> <dbl>
 1     1     1     1     1
 2     1     2     9     9
 3     1     3     4     9
 4     1     4     8     8
 5     1     5     5     8
 6     2     1     2     2
 7     2     2     5     5
 8     2     3     9     9
 9     2     4     7     9
10     2     5     6     7
user_20808480

With data.table you also can try frollapply (fast rolling function). Note that fill is set to first(Value) in initial row of ID group where there is only one element available instead of two.

dt <- data.frame(ID,Time,Value)

setDT(dt)

dt[, ValueMax := frollapply(x = Value, 
                            n = 2, 
                            max, 
                            fill = first(Value), 
                            align = "right", 
                            na.rm = TRUE), 
   by = ID]

Output

    ID Time Value ValueMax
 1:  1    1     1        1
 2:  1    2     9        9
 3:  1    3     4        9
 4:  1    4     8        8
 5:  1    5     5        8
 6:  2    1     2        2
 7:  2    2     5        5
 8:  2    3     9        9
 9:  2    4     7        9
10:  2    5     6        7
user_34606700
Related