Subsetting data by time frame window for each row in a data frame after using group_by()

user_15384755 0
Viewed 110

I have time-series data with three columns: a value column, a group_var column (used for grouping), and a date column. For each row in the data frame, I'd like to get the mean of that row's group after further subsetting by a specific timeframe. Here's an example of the code for subsetting:

df$value[df$date >= (current_row$date - 545) & df$date <= (current_row$date - 365)]

After I get this subset I can easily apply mean(), but where I'm stuck on is how I can get this code to work with something like this:

df %>%
    group_by(group_var) %>%
      mutate(subset_mean = mean(df$value[df$date >= (current_row$date - 545) & df$date <= (current_row$date - 365)])
)

The issues I see is that I don't think I can use 'df' inside the mutate() line after I group the original 'df'. Also I'm not sure how I can create 'current_row' variable for referencing the current row to calculate the data subset.

Edit: Added example data and reproducible code

library(dplyr)
date <- c("2016-02-03", "2016-06-14", "2016-03-15", "2017-04-16","2016-01-27", "2016-01-13", "2017-04-24", "2017-06-15")
date <- date %>% as.Date(format = "%Y-%m-%d")
val <- c(10, 20, 50, 70, 30, 44, 67, 42)
group_var <- c("A", "B", "B", "A", "B", "A", "A", "B")

df <- data.frame(date, val, group_var)
 
df %>% 
  group_by(group_var)
dplyr r
4 Answers

I would suggest using slider::slide_index_dbl for this:

library(dplyr)
df %>% 
  group_by(group_var) %>%
  arrange(group_var, date) %>%   # slider 0.1.5 requires the window variable to be ascending
  mutate(subset_mean = slider::slide_index_dbl(
    val, date, mean, .before = 545, .after = -365   
    # negative ".after" means the window ends before the current date
  )) %>% 
  ungroup()

With the updated data, I get

#date <- c("2016-02-03", "2016-06-14", "2016-03-15", "2017-04-16","2016-01-27", "2016-01-13", "2017-04-24", "2017-06-15")


# A tibble: 8 x 4
  date         val group_var subset_mean
  <date>     <dbl> <chr>           <dbl>
1 2016-01-13    44 A               NaN  
2 2016-02-03    10 A               NaN  
3 2017-04-16    70 A                27  
4 2017-04-24    67 A                27  
5 2016-01-27    30 B               NaN  
6 2016-03-15    50 B               NaN  
7 2016-06-14    20 B               NaN  
8 2017-06-15    42 B                33.3
user_68518250

1) This can be done with a self join using sql:

library(sqldf)

sqldf("select a.date, a.val, a.group_var, avg(b.val) as mean
  from df a
  left join df b on a.group_var = b.group_var and
    b.date between a.date - 595 and a.date - 365
  group by a.rowid")

giving:

        date val group_var     mean
1 2016-02-03  10         A       NA
2 2016-06-14  20         B       NA
3 2016-03-15  50         B       NA
4 2017-04-16  70         A 27.00000
5 2016-01-27  30         B       NA
6 2016-01-13  44         A       NA
7 2017-04-24  67         A 27.00000
8 2017-06-15  42         B 33.33333

2) or we can use SQL window functions:

sqldf("select date, val, group_var,
  avg(val) over (partition by group_var 
                 order by date 
                 range between 595 preceding and 365 preceding) as mean
  from df"
)

giving:

        date val group_var     mean
1 2016-01-13  44         A       NA
2 2016-02-03  10         A       NA
3 2017-04-16  70         A 27.00000
4 2017-04-24  67         A 27.00000
5 2016-01-27  30         B       NA
6 2016-03-15  50         B       NA
7 2016-06-14  20         B       NA
8 2017-06-15  42         B 33.33333
user_5165480

Lubridate provides a very elegant solution...

library(tidyverse)
library(lubridate)

df = tibble(
  value = runif(100,1,100),
  group = rep(1:4,25),
  dt = as.Date(round(runif(100,1000,2000)), origin = "1970-01-01")
)

first_year <- interval(ymd("1972-01-01"), ymd("1972-12-31"))
sec_year <- interval(ymd("1973-01-01"), ymd("1973-12-31"))
furhter <- interval(ymd("1974-01-01"), ymd("1975-12-31"))

df <- df %>% 
  mutate(
    range = case_when(
      dt %within% first_year ~"1972",
      dt %within% sec_year ~"1973",
      TRUE ~"1974-1975"
    )
  )

mean_by_group_interval <- df %>% 
  group_by(
    group,
    range
  ) %>% 
  summarise(
    mean = mean(value)
  )
user_18763500

Here is a solution that utilizes the dplyr package.

library(dplyr)

date <- c("2016-02-03", "2016-06-14", "2016-03-15", "2017-04-16","2016-01-27", "2016-01-13", "2017-04-24", "2017-06-15")
date <- date %>% as.Date(format = "%Y-%m-%d")
val <- c(10, 20, 50, 70, 30, 44, 67, 42)
group_var <- c("A", "B", "B", "A", "B", "A", "A", "B")

df <- data.frame(date, val, group_var)


df %>% 
  group_by(group_var) %>%
  arrange(group_var, date) %>% 
  mutate(
    # Determine if the current date - the first date of each group is between 365 and 595 days.
    match = between(date - first(date), 365, 595),
    # Count the number of dates that are not within the range described above to be used in calculating the mean.
    count_false = sum(match == FALSE),
    # Calculate the cumulative sum for rows in each group that are not within the range described above.
    sum_match_false = ifelse(match == FALSE, cumsum(val), NA),
    # Calculate the mean.
    mean_match_true = ifelse(match == TRUE, max(sum_match_false, na.rm = TRUE) / count_false, NA)
  ) %>% 
  # Return only these variables.
  select(date, val, group_var, mean_match_true)


#>   date         val group_var mean_match_true
#>   <date>     <dbl> <chr>               <dbl>
#> 1 2016-01-13    44 A                    NA  
#> 2 2016-02-03    10 A                    NA  
#> 3 2017-04-16    70 A                    27  
#> 4 2017-04-24    67 A                    27  
#> 5 2016-01-27    30 B                    NA  
#> 6 2016-03-15    50 B                    NA  
#> 7 2016-06-14    20 B                    NA  
#> 8 2017-06-15    42 B                    33.3

Created on 2021-03-12 by the reprex package (v0.3.0)

user_133567300
Related