如何在R中根据df$a的条件为新列df$b生成指定跨行序列？

Got it, let's work through this problem using tidyverse and lubridate—exactly what you asked for. Here's a step-by-step solution that meets your requirements:

First, we need to load the two packages you specified:

library(tidyverse)
library(lubridate)

Let's start by formatting your date vector into proper date objects and creating a sample data frame (I'll add a dummy a column where one row has the value "6" to test the logic):

# Full date vector (completed the partial one you provided)
date <- c("2/29/1940","3/31/1940","4/30/1940","5/31/1940","6/30/1940","7/31/1940","8/31/1940","9/30/1940","10/31/1940","11/30/1940","12/31/1940",
          "1/31/1941","2/28/1941", "3/31/1941","4/30/1941","5/31/1941","6/30/1941","7/31/1941","8/31/1941","9/30/1941","10/31/1941",
          "11/30/1941", "12/31/1941","1/31/1942","2/28/1942")

# Create data frame with date column and sample a column (1940-06-30 has "6")
df <- tibble(
  date = mdy(date),  # Convert character dates to lubridate date objects
  a = case_when(
    date == mdy("6/30/1940") ~ "6",
    TRUE ~ as.character(sample(1:5, length(date), replace = TRUE))  # Random values for other rows
  )
)

Now we'll identify the trigger date (where a == "6"), calculate the target time window, and assign the 1:9 sequence to matching rows:

# Find the date where column a equals "6"
trigger_date <- df %>% filter(a == "6") %>% pull(date)

# Calculate the start and end of our target window: previous year Sep to next year May
window_start <- floor_date(trigger_date, "year") - months(3)  # Sep of the year before trigger date
window_end <- ceiling_date(trigger_date, "year") + months(5) - days(1)  # Last day of May in the year after trigger date

# Generate column b: assign 1-9 to dates in the window, NA otherwise
df <- df %>%
  mutate(
    # Temporary flag to check if date is in target window
    in_window = date >= window_start & date <= window_end,
    # Assign sequence: 1 for first window row, up to 9 for the last
    b = ifelse(in_window, row_number() - min(which(in_window)) + 1, NA)
  ) %>%
  select(-in_window)  # Remove the temporary flag column

# View the rows with non-NA values in column b to verify
df %>% filter(!is.na(b))

• Date Formatting: mdy() converts your US-style date strings into proper date objects that lubridate can manipulate.
• Window Calculation:
  • floor_date(trigger_date, "year") gets the first day of the trigger date's year; subtracting 3 months gives us September 1st of the previous year.
  • ceiling_date(trigger_date, "year") gets the first day of the year after the trigger date; adding 5 months and subtracting 1 day gives us May 31st of that next year.
• Sequence Assignment: We use row_number() to count rows, then adjust it so the first row in the window starts at 1, incrementing up to 9 (since your window covers exactly 9 months: Sep, Oct, Nov, Dec, Jan, Feb, Mar, Apr, May).

If your a column has multiple rows with "6", you can adjust this logic to handle each trigger date's window (e.g., using group_by or looping), but this solution works perfectly for the single-trigger scenario you described.

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