新增数据后连续数字累计计数缺口结果不一致的问题求助

Let's break down why your existing code causes old SeqCount values to change, then fix the logic to keep historical values stable even when new months are added.

Problem with the Original Code #

The key issue is how you determine LastValue:

mutate(LastValue = if_else(Month == last(Month), 1, 0))

The last(Month) function returns the final month in the entire UniqueID group. When you add data for month 15, the last(Month) value for each group updates to 15—so rows that were previously the "last" (like month 14) now fail the Month == last(Month) check. This changes LastValue, FinalTally, and ultimately SeqCount for those older rows.

Fixed Code Logic #

We need to redefine "last record" to mean the last record as of the current row's month, not the final record in the entire dataset. Here's how to do that:

library(dplyr)

# Define your target final month (easily adjustable later)
TARGET_MONTH <- 15

data2 <- data %>%
  group_by(UniqueID) %>%
  mutate(
    # Calculate gap count (original logic, unchanged—safe because it only uses prior months)
    Skip = if_else(Month - lag(Month, default = first(Month) - 1) - 1 > 0, 1, 0),
    CountSkip = cumsum(Skip),
    
    # Compute the maximum month observed up to the current row (cumulative max)
    cum_max_month = cummax(Month),
    
    # Check if this row was the last record as of its month
    is_last_at_time = Month == cum_max_month,
    
    # Calculate FinalTally based on historical state, not the full dataset
    FinalTally = if_else(is_last_at_time & Month != TARGET_MONTH, 1, 0),
    
    # Compute stable SeqCount
    SeqCount = CountSkip + FinalTally
  ) %>%
  ungroup() %>%
  as.data.frame()

Why This Works #

1. cummax(Month): This calculates the highest month value for each UniqueID up to the current row. Adding new months later won't change this value for existing rows—since it only looks at data up to that point.
2. is_last_at_time: This checks if the current row's month was the most recent one available when that row was added. For example, a row for month 14 will always show is_last_at_time = TRUE (since up to month 14, it was the latest), even after you add month 15.
3. Stable SeqCount: Since both CountSkip (depends only on prior months) and FinalTally (depends on historical state) are unchanged when new data is added, your old SeqCount values stay consistent for regression model stability.

Example Behavior #

• Before adding month 15: For a UniqueID with data up to 14, the month 14 row will have is_last_at_time = TRUE and FinalTally = 1 (if TARGET_MONTH = 15).
• After adding month 15: The month 14 row still has is_last_at_time = TRUE (since up to month 14, it was the latest), so FinalTally remains 1, and SeqCount stays the same. The new month 15 row will have is_last_at_time = TRUE but FinalTally = 0 (since it matches TARGET_MONTH).

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