如何用R高效提取数千个ID的连续记录起止日期？

Got it, dealing with thousands of IDs using loops is definitely going to drag down performance—vectorized operations are the way to go here. Let me show you two efficient solutions using dplyr (from the tidyverse) and data.table, both way faster than looping through each ID one by one.

First, let's replicate your sample data to test with:

library(tidyverse)

dat <- tibble(
  id = rep(1, 10),
  date = as.Date(c(
    "2017-11-30", "2017-12-01", "2017-12-02", "2017-12-03",
    "2017-12-05", "2017-12-06", "2017-12-07", "2017-12-08",
    "2017-12-09", "2017-12-10"
  ))
)

Solution 1: Using dplyr (Tidyverse) #

This approach leverages grouped operations and vectorized calculations to avoid loops entirely:

# Step 1: Identify consecutive date segments per ID
consecutive_segments <- dat %>%
  arrange(id, date) %>%  # Ensure data is sorted by ID and date (critical!)
  group_by(id) %>%
  mutate(
    # Calculate days between current date and previous date (first date gets 0)
    day_gap = as.integer(date - lag(date, default = first(date))),
    # Assign a unique ID to each consecutive segment (increment when gap > 1 day)
    segment_id = cumsum(day_gap != 1)
  ) %>%
  # Step 2: Extract start/end dates for each segment
  group_by(id, segment_id) %>%
  summarize(
    start_date = min(date),
    end_date = max(date),
    .groups = "drop"  # Clean up grouping
  )

# Step 3: Convert to your desired long format (start/end as separate rows)
final_output <- consecutive_segments %>%
  pivot_longer(
    cols = c(start_date, end_date),
    names_to = "name",
    values_to = "event_start"
  ) %>%
  mutate(
    name = case_when(
      name == "start_date" ~ "START_RECORDS_BY_SENSORS",
      name == "end_date" ~ "END_RECORDS_BY_SENSORS"
    )
  ) %>%
  arrange(id, event_start)

print(final_output)

How this works: #

• We first sort the data to ensure dates are in order (this is non-negotiable for consecutive checks)
• lag(date) grabs the previous date in the group, and we calculate the gap between dates
• cumsum(day_gap != 1) creates a running total that increments every time there's a gap larger than 1 day—this gives us a unique identifier for each consecutive segment
• Grouping by id and segment_id lets us easily pull the first (start) and last (end) date of each segment
• Finally, pivot_longer reshapes the data to match the output format you had from your loop

Solution 2: Using data.table (Even Faster for Large Datasets) #

If you're working with truly massive datasets (tens of thousands of IDs + millions of rows), data.table will outperform dplyr thanks to its optimized C backend:

library(data.table)

# Convert to data.table
setDT(dat)

# Step 1: Identify consecutive segments
dat[, day_gap := as.integer(date - shift(date, fill = first(date))), by = id]
dat[, segment_id := cumsum(day_gap != 1), by = id]

# Step 2: Extract start/end dates
consecutive_segments_dt <- dat[, .(start_date = min(date), end_date = max(date)), by = .(id, segment_id)]

# Step 3: Reshape to long format
final_output_dt <- melt(consecutive_segments_dt,
                       id.vars = c("id", "segment_id"),
                       measure.vars = c("start_date", "end_date"),
                       variable.name = "name",
                       value.name = "event_start")[,
                         name := fcase(
                           name == "start_date", "START_RECORDS_BY_SENSORS",
                           name == "end_date", "END_RECORDS_BY_SENSORS"
                         )][order(id, event_start)]

print(final_output_dt)

Why this is better than your loop: #

Your original loop does a lot of inefficient operations: subsetting the data for each ID, repeatedly binding rows to NEWDAT, and nested diff() calls that are easy to mess up. Both solutions above use vectorized operations—they process entire groups at once instead of one ID at a time—this is what makes them so much faster for large datasets.

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