基于时间序列超前/滞后项创建多列及特殊面板数据处理求助

Got it, let's work through this problem together. Your data has a twist on standard panel data: each ID has multiple disconnected time series segments (like ID A has two blocks: 2001-2003 and 2006-2009). We can't just calculate lags/leads across the entire ID group—we need to first isolate each continuous time block within an ID, then compute the leads and lags only within those blocks.

Step 1: Reproduce Your Data #

First, let's confirm the full dataset we're working with:

set.seed(100)
mydf <- data.frame(
  ID = c(rep('A',7), rep('B',3)),
  year = c(2001:2003, 2006:2009, 2001:2003),
  x = rnorm(10),
  y = rnorm(10)
)

# View the complete data
print(mydf)

Step 2: Identify Continuous Time Segments per ID #

The critical first step is to tag each continuous time block within an ID. We'll do this by checking when the year gap between consecutive rows is greater than 1—this signals the start of a new segment.

Using dplyr (Tidyverse):

library(dplyr)

# Add a segment identifier for each continuous time block
mydf_with_segments <- mydf %>%
  group_by(ID) %>%
  # Start with segment 1, increment when year gap exceeds 1
  mutate(segment = cumsum(c(1, diff(year) > 1))) %>%
  ungroup()

Using data.table:

library(data.table)
setDT(mydf)

# Add segment identifier
mydf[, segment := cumsum(c(1, diff(year) > 1)), by = ID]

Now ID A will have two segments (1 for 2001-2003, 2 for 2006-2009), and ID B will have one segment (1 for 2001-2003).

Step 3: Calculate Leads and Lags Within Segments #

Now we can safely compute lags/leads within each ID-segment group, ensuring we don't pull values from unrelated time blocks.

Using dplyr:

final_df <- mydf_with_segments %>%
  group_by(ID, segment) %>%
  mutate(
    # Example lag columns for x: lag 1 and lag 2 periods
    lag_x1 = lag(x, n = 1),
    lag_x2 = lag(x, n = 2),
    # Example lead columns for y: lead 1 and lead 2 periods
    lead_y1 = lead(y, n = 1),
    lead_y2 = lead(y, n = 2)
  ) %>%
  ungroup()

Using data.table:

mydf[, `:=`(
  lag_x1 = shift(x, n = 1),
  lag_x2 = shift(x, n = 2),
  lead_y1 = shift(y, n = 1, type = "lead"),
  lead_y2 = shift(y, n = 2, type = "lead")
), by = .(ID, segment)]

Why This Works #

By grouping on both ID and segment, we guarantee that lags/leads only reference rows within the same continuous time block. For example, the 2006 row in ID A won't pull a lag value from 2003—instead, it starts fresh with NA for its first lag, which is the correct behavior for a new time segment.

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