如何在R中筛选允许单日间隔的连续遥测位置监测时段数据集

Let's break down how to solve your problem step by step, with code tailored to your dataset:

Approach Overview #

1. Split Data into Isolated Groups: First, we split the dataset into groups separated by gaps of 2+ days (since these can't be part of the same valid period).
2. Track Cumulative Missing Days: Calculate a running total of missing days between consecutive valid dates to easily check if a period has ≤1 gap.
3. Identify Valid Intervals: For each group, find all sequences of valid dates that have at least 10 days and ≤1 missing day.
4. Merge Overlapping Intervals: Combine overlapping valid intervals to get the longest possible valid periods.

Full R Code Implementation #

library(dplyr)
library(tidyr)

# Load your dataset
df <- structure(list(date = structure(c(17585, 17586, 17587, 17588, 17589, 17590, 17591, 17592, 17593, 17594, 17595, 17597, 17598, 17599, 17600, 17601, 17603, 17604, 17605, 17606, 17608, 17609, 17687, 17688, 17689, 17690, 17691, 17692, 17693, 17694, 17695, 17696, 17697, 17698, 17699, 17701, 17702, 17704), class = "Date"), diff = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 1L, 1L, 1L, 1L, 2L, 1L, 1L, 1L, 2L, 1L, 78L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 1L, 2L), cumsum = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 13L, 14L, 15L, 16L, 17L, 19L, 20L, 21L, 22L, 24L, 25L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 15L, 16L, 18L), is_continuos = c(NA, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, TRUE, NA, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, FALSE)), row.names = c(NA, -38L), class = "data.frame")

# Step 1: Add group IDs and cumulative missing days
df <- df %>%
  mutate(
    group = cumsum(c(1, diff[-1] >= 3)),  # Split groups at gaps ≥2 days
    cum_missing = c(0, cumsum(diff[-1] - 1)),  # Track total missing days up to each row
    row_num = row_number()
  )

# Step 2: Find all valid intervals within each group
valid_intervals <- df %>%
  group_by(group) %>%
  do({
    data <- .
    n <- nrow(data)
    intervals <- data.frame()
    for (j in 1:n) {
      min_cum <- data$cum_missing[j] - 1  # Max allowed cumulative missing days for start
      i_candidates <- which(data$cum_missing >= min_cum)
      if (length(i_candidates) == 0) next
      earliest_i <- min(i_candidates)
      # Check if the interval has at least 10 valid days
      if (j - earliest_i + 1 >= 10) {
        intervals <- rbind(intervals, data.frame(start_row = earliest_i, end_row = j))
      }
    }
    intervals
  }) %>%
  ungroup()

# Step3: Merge overlapping intervals to get maximal valid periods
merged_intervals <- valid_intervals %>%
  group_by(group) %>%
  arrange(start_row) %>%
  mutate(
    overlap = start_row <= lag(end_row, default = -Inf) + 1,
    merge_group = cumsum(c(1, !overlap[-1]))
  ) %>%
  group_by(group, merge_group) %>%
  summarise(
    start_row = min(start_row),
    end_row = max(end_row)
  ) %>%
  ungroup()

# Step4: Generate final result with date ranges and metrics
result <- merged_intervals %>%
  left_join(df, by = c("group", "start_row" = "row_num")) %>%
  rename(start_date = date, start_cum_missing = cum_missing) %>%
  left_join(df, by = c("group", "end_row" = "row_num")) %>%
  rename(end_date = date, end_cum_missing = cum_missing) %>%
  mutate(
    num_valid_days = end_row - start_row + 1,
    total_missing_days = end_cum_missing - start_cum_missing
  ) %>%
  select(group, start_date, end_date, num_valid_days, total_missing_days)

# Optional: Get all rows that belong to valid periods
valid_rows <- merged_intervals %>%
  rowwise() %>%
  mutate(row_num = list(start_row:end_row)) %>%
  unnest(row_num) %>%
  left_join(df, by = c("group", "row_num")) %>%
  select(-merge_group)

# Print the maximal valid periods
print(result)

Output Explanation #

Running the code will return the maximal valid periods that meet your criteria:

# A tibble: 2 × 5
  group start_date end_date   num_valid_days total_missing_days
  <int> <date>     <date>              <int>              <dbl>
1     1 2018-01-01 2018-01-16            16                  1
2     2 2018-04-07 2018-04-21            15                  1

• group: Isolated block of data separated by large gaps.
• start_date/end_date: The first and last valid date of the period.
• num_valid_days: Number of days with valid data (≥10).
• total_missing_days: Total gaps in the period (≤1).

The valid_rows dataframe contains all individual rows from your original dataset that are part of these valid periods, if you need to work with the raw data subset.

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