如何在R数据框中按客户实现排除当前值的24小时滑动窗口统计

Here's how you can achieve this using R with dplyr, lubridate, and the slider package (optimized for time-based sliding windows):

Step-by-Step Solution #

library(dplyr)
library(lubridate)
library(slider)

# Your sample data
dt <- tibble(
  order_id = 1:10,
  customer_id = c(1, rep(2, 2), rep(3, 3), rep(4, 4)),
  amount = seq(10, 100, by = 10),
  date = c("2020-10-07 12:00", # 1st customer
           "2020-10-07 12:00", "2020-10-08 11:00", # 2nd customer
           "2020-10-07 12:00", "2020-10-08 11:00", "2020-10-08 20:00", # 3rd customer
           "2020-10-07 12:00", "2020-10-08 11:00", "2020-10-08 20:00", "2020-10-08 21:00") # 4th customer
) %>% mutate(
  date = lubridate::ymd_hm(date)
)

# Calculate rolling stats excluding current row (per customer, 24h window)
result <- dt %>%
  arrange(customer_id, date) %>% # Sort to ensure window logic works predictably
  group_by(customer_id) %>%
  mutate(
    # Count of values in last 24h (excluding current)
    amount__n__last_24h = slide_index_dbl(
      .x = amount,
      .i = date,
      .f = ~ length(.x[-length(.x)]), # Remove current value from window
      .before = days(1)
    ),
    # Mean of values in last 24h (excluding current)
    amount__mean__last_24h = slide_index_dbl(
      .x = amount,
      .i = date,
      .f = ~ if (length(.x[-length(.x)]) == 0) NA_real_ else mean(.x[-length(.x)]),
      .before = days(1)
    )
  ) %>%
  ungroup() %>%
  arrange(order_id) # Restore original order to match expected output

# View the result
print(result)

How It Works #

1. Sorting: We first sort by customer_id and date to ensure the sliding window logic behaves as expected (though slider can handle unsorted data, sorting makes the process more transparent).
2. Grouping: We group by customer_id to isolate calculations to each individual customer.
3. Sliding Window Calculations:
   • slide_index_dbl uses the date column to define a 24-hour window (days(1)) before the current row's date.
   • For each window, we exclude the current row's value using .x[-length(.x)] (removes the last element in the window, which corresponds to the current row).
   • We compute the count of valid values and their mean, returning NA when there are no historical values in the window.

Alternative (Without slider Package) #

If you can't use slider, you can achieve the same result with purrr (though it's less efficient for large datasets):

library(dplyr)
library(lubridate)
library(purrr)

result_alt <- dt %>%
  arrange(customer_id, date) %>%
  group_by(customer_id) %>%
  mutate(
    amount__n__last_24h = map_int(row_number(), ~ sum(date[1:(.x-1)] >= date[.x] - days(1))),
    amount__mean__last_24h = map_dbl(row_number(), ~ {
      window_vals <- amount[1:(.x-1)][date[1:(.x-1)] >= date[.x] - days(1)]
      if (length(window_vals) == 0) NA_real_ else mean(window_vals)
    })
  ) %>%
  ungroup() %>%
  arrange(order_id)

This uses purrr::map to iterate over each row in the group, filtering previous rows that fall within the 24-hour window, then computing the required statistics.

内容的提问来源于stack exchange，提问作者codez0mb1e