如何用data.table计算窗口长度不等的两个月滚动均值？

Got it, let's break down how to solve this rolling mean problem with variable-length windows using data.table—this package is perfect for efficient time series operations, especially with large datasets.

Step 1: Set Up the Environment and Sample Data #

First, load the required packages and generate your sample data:

library(data.table)
library(lubridate) # Makes date manipulation way easier

# Generate sample data as specified
set.seed(24)
test <- data.table(
  x = rnorm(762),
  time = seq(as.Date("1988/03/15"), as.Date("1990/04/15"), "day")
)

Step 2: Define Your Custom Windows #

Your window rules are:

• First window: 1988/03/15 to 1988/04/30
• Subsequent windows: Start on the 1st of the month, end on the last day of the next month (e.g., 1988/04/01 to 1988/05/31, 1988/05/01 to 1988/06/30, etc.)

We'll generate these windows programmatically, and you can easily adjust them later if you need different lengths for specific months:

# 1. Generate end dates: Start with the first full month end after your data starts, then monthly ends
end_dates <- seq(
  floor_date(min(test$time) + months(1), "month") - days(1), # 1988-04-30
  max(test$time),
  by = "month"
)

# 2. Generate start dates: First window uses your data's start date; others use the 1st of the prior month to the end date
start_dates <- c(
  min(test$time), # 1988-03-15
  floor_date(end_dates[-1] - months(1), "month") # e.g., 1988-04-01, 1988-05-01, etc.
)

# 3. Package windows into a data.table for easy joining
windows <- data.table(
  window_id = seq_along(start_dates),
  start_date = start_dates,
  end_date = end_dates
)

Step 3: Calculate Rolling Means with Non-Equi Joins #

Data.table's non-equi join is the key here—it lets us efficiently match each row in test to the windows it falls into, then compute the mean per window:

# Compute mean for each window
window_rolling_means <- test[windows,
                             on = .(time >= start_date, time <= end_date),
                             .(rolling_mean = mean(x)),
                             by = .EACHI]

# View the first few results
head(window_rolling_means)

Step 4: (Optional) Attach Means to Original Data #

If you want each date in your original data to show all the rolling means of the windows it belongs to, use this variant:

# Join window means back to the original data
test_with_means <- test[windows,
                        on = .(time >= start_date, time <= end_date),
                        .(time = x.time, x = x.x, window_id = i.window_id, rolling_mean = mean(x.x)),
                        by = .EACHI]

# View sample results
head(test_with_means)

Customizing Window Lengths #

If you need to adjust the length of specific windows (e.g., make a window end on the 15th instead of the month end), just modify the windows table directly:

# Example: Change the 3rd window to end on 1988-06-15 instead of 1988-06-30
windows[window_id == 3, end_date := as.Date("1988-06-15")]

# Recompute means with the custom window
custom_window_means <- test[windows,
                            on = .(time >= start_date, time <= end_date),
                            .(rolling_mean = mean(x)),
                            by = .EACHI]

Key Notes #

• Non-equi joins in data.table are extremely efficient—they outperform loops or apply-based methods by a wide margin, even with large datasets.
• Using lubridate simplifies date arithmetic, but if you prefer base R, you can replace functions like floor_date() with base date handling (e.g., as.Date(paste0(year(date), "-", month(date), "-01"))).

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