如何为data.table添加前一时间分组对应id的状态列

To get the status of the same id from the immediate previous time group, we can use a self join approach in data.table (or dplyr), which ensures we only match rows where the id exists in the prior time group (returning NA if it doesn't).

Step-by-Step Explanation & Code #

First, let's confirm the sample data generation:

set.seed(10)
library(data.table)
df <- data.table(time = sample(1:3, 20, T), status = sample(letters[1:15], 20, T))[
  order(time)
][, id := 1:.N, by = time]

Using data.table (Efficient for Large Datasets)

We create a copy of the data where we increment time by 1, then join this back to the original data on time and id to pull in the prior status:

# Create a table with time shifted forward by 1, holding the prior status
prev_df <- df[, .(time = time + 1, id, prev_status = status)]

# Join to original data to populate prev_status
df[prev_df, on = .(time, id), prev_status := i.prev_status]

Result Preview

After running the code, your data will look like this (truncated):

time status id prev_status
 1:    1      j  1         <NA>
 2:    1      g  2         <NA>
 3:    2      n  1           j
 4:    2      j  2           g
 5:    3      b  1           n
 6:    3      k  2           j
 7:    3      h  7         <NA>  # id 7 doesn't exist in time 2, so NA

Alternative Using dplyr

If you prefer dplyr, you can use left_join to achieve the same result:

library(dplyr)
df <- df %>%
  left_join(
    df %>% mutate(time = time + 1, prev_status = status),
    by = c("time", "id")
  ) %>%
  select(time, status, id, prev_status)  # Reorder columns to match original

Key Note on Edge Cases #

The self join approach correctly handles cases where an id is missing from the immediate previous time group (returning NA). A simpler lag(status) grouped by id would fill in the last available status even if there's a gap in time groups, which may not align with your requirement of strictly getting the prior time group's status.

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