如何用dplyr等工具判断ID中'd'后是否出现'c'并提取对应日期

Hey there! Great question—dplyr is absolutely the right tool for this kind of grouped sequential analysis, and we can avoid messy deduplication/lead hacks with clean, straightforward logic. Let's walk through this step by step.

First, let's set up some sample data to work with #

I'll create a tibble that mirrors the structure you're likely working with:

library(dplyr)

df <- tibble(
  ID = rep(c(1, 2, 3), each = 4),
  value = c("a", "d", "c", "b", "d", "a", "a", "b", "c", "d", "a", "c"),
  date = seq.Date(as.Date("2024-01-01"), as.Date("2024-01-12"), by = "day")
)

The dplyr solution #

We'll group by ID, sort records by date (critical for "before/after" logic), then add our two new columns in one pass:

result <- df %>%
  group_by(ID) %>%
  arrange(date, .by_group = TRUE) %>% # Ensure records are ordered chronologically per ID
  mutate(
    # Track if we've already seen a "d" in this ID's sequence
    has_seen_d = cumsum(value == "d") > 0,
    # Flag rows where "c" appears after a "d"
    is_c_post_d = has_seen_d & value == "c",
    # Final check: does this ID have ANY "c" after "d"?
    has_c_after_d = any(is_c_post_d),
    # Extract the first date where "c" appears after "d" (NA if none)
    first_c_post_d_date = if (has_c_after_d) date[which(is_c_post_d)[1]] else NA_Date_
  ) %>%
  ungroup()

What this does: #

• group_by(ID): Isolates each ID's records for independent analysis
• arrange(date, .by_group = TRUE): Guarantees we're working with chronological order (this is non-negotiable for "after" checks)
• has_seen_d: Uses cumulative sum to create a running flag—once a "d" is hit, all subsequent rows in the ID will be TRUE
• is_c_post_d: Pinpoints exactly which rows are "c" values that come after a "d"
• has_c_after_d: A single TRUE/FALSE per ID indicating if the condition is met
• first_c_post_d_date: Grabs the earliest date where the "c-after-d" condition occurs, or returns a proper date-type NA if no such row exists

Want a condensed summary per ID? #

If you don't need to keep all original rows and just want one row per ID with the results, add this step:

summary_result <- result %>%
  distinct(ID, has_c_after_d, first_c_post_d_date)

Key notes: #

• Make sure your date column is a proper Date type (not a character string) so sorting works correctly
• This logic captures the first "d" followed by the first "c" after it—if you need to handle multiple "d"s or all "c"s post-"d", we can tweak it, but this covers the core requirement
• Using NA_Date_ instead of plain NA ensures your date column stays consistent in type, avoiding errors down the line

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