R语言实现SNR_kHz低于40kHz后恢复时长统计的自动化函数开发

业务规则说明 #

• 待统计变量为SNR_kHz，取值范围为-17~77kHz，已生成关联分类变量SNRLevel，包含「Low SNR」「Avg SNR」「High SNR」三个水平，「Low SNR」对应SNR_kHz低于40的情况
• 数据集包含数值、日期时间、分类三类数据，原始观测约3000条，有效匹配观测约300条，已完成数据分箱与无效NA值剔除
• 双通道判定规则：数据包含1、2两个麦克风通道的SNR_kHz记录，只要任意一个通道SNR_kHz低于40kHz即触发计时，两个通道视为统一实体

功能需求 #

• 触发计时：SNR_kHz低于40kHz时，记录对应时间（binned列，时区为America/Chicago）、相对湿度（Humidmean列）、温度（Tempmean列）
• 停止计时：SNR_kHz回到40kHz以上时，再次记录对应时间、湿度、温度，用difftime计算小时为单位的恢复耗时
• 输出指标：所有恢复事件的平均耗时、恢复发生时的平均湿度与平均温度，用于分析站点SNR恢复规律

原有实现问题 #

你之前尝试的循环逻辑存在多处问题，示例代码如下：

repeat{
    if(example$SNRLevel=="Low SNR")
      start<-print(example$binned)
      print(example$Humidmean)
    
    if(example$SNRLevel!="Low SNR")
      {
      break
      end<-print(example$binned)
      }
      print(difftime(start-end, tz="America/Chicago", units="hours"))
  }

核心问题：未按时间顺序逐行遍历判断、break语句放在end赋值前导致结束时间无法捕获、未处理双通道同时间点的判定逻辑、未处理连续NA区间的事件切割

实现代码 #

使用dplyr向量式运算替代循环，逻辑更简洁不易出错：

library(dplyr)
library(lubridate)

# 第一步：按时间分组，判定每个时间点是否为低SNR状态（任意通道符合条件即判定）
snr_status <- example %>%
  ungroup() %>%
  filter(!is.na(SNRLevel)) %>% # 剔除无效NA行
  group_by(binned) %>%
  summarise(
    is_low = any(SNRLevel == "Low SNR"),
    Humidmean = first(Humidmean),
    Tempmean = first(Tempmean),
    .groups = "drop"
  ) %>%
  arrange(binned) # 按时间升序排序

# 第二步：识别状态切换事件，切割恢复区间
snr_events <- snr_status %>%
  mutate(
    # 状态变化标记：当前状态与上一状态不同时为1
    status_change = is_low != lag(is_low, default = first(is_low)),
    # 事件编号：累计状态变化次数作为事件ID
    event_id = cumsum(status_change)
  ) %>%
  group_by(event_id) %>%
  summarise(
    event_type = ifelse(first(is_low), "low_snr_event", "normal_event"),
    start_time = first(binned),
    end_time = last(binned),
    start_humid = first(Humidmean),
    start_temp = first(Tempmean),
    end_humid = last(Humidmean),
    end_temp = last(Tempmean),
    .groups = "drop"
  ) %>%
  # 匹配低SNR事件与后续恢复事件，计算恢复耗时
  filter(event_type == "low_snr_event") %>%
  mutate(
    # 取下一个正常事件的开始时间作为恢复完成时间
    recover_time = lead(start_time),
    recover_humid = lead(start_humid),
    recover_temp = lead(start_temp),
    # 计算恢复耗时（小时）
    recover_duration_h = as.numeric(difftime(recover_time, end_time, units = "hours"))
  ) %>%
  filter(!is.na(recover_duration_h)) # 剔除未完成恢复的截断事件

# 第三步：计算最终统计指标
stats_result <- snr_events %>%
  summarise(
    平均恢复耗时_小时 = mean(recover_duration_h, na.rm = T),
    平均低SNR起始湿度 = mean(start_humid, na.rm = T),
    平均恢复完成湿度 = mean(recover_humid, na.rm = T),
    平均低SNR起始温度 = mean(start_temp, na.rm = T),
    平均恢复完成温度 = mean(recover_temp, na.rm = T)
  )

# 输出结果
print(stats_result)

样例数据集 #

你提供的样例数据集dput输出如下：

structure(list(binned = structure(c(1597578360, 1597578360, 1597579560, 
1597622760, 1597623960, 1597623960, 1597625160, 1597626360, 1597627560, 
1597628760, 1597629960, 1597631160, 1597632360, 1597633560, 1597634760, 
1597635960, 1597637160, 1597638360, 1597639560, 1597640760, 1597641960, 
1597643160, 1597644360, 1597645560, 1597646760, 1597647960, 1597649160, 
1597650360, 1597651560, 1597652760, 1597653960, 1597655160, 1597656360, 
1597657560, 1597658760, 1597659960, 1597661160, 1597662360, 1597663560, 
1597664760, 1597664760), tzone = "America/Chicago", class = c("POSIXct", 
"POSIXt")), precipsum = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0), Humidmean = c(64.3, 64.3, 66, 48.8, 
53.1, 53.1, 54.05, 51.4, 55.1, 59.85, 59.6, 57.7, 56.85, 58.8, 
59, 59.2, 57.8, 59.1, 59.45, 62.2, 63.5, 65, 66, 67.6, 67.75, 
70.2, 70.9, 71.8, 74.6, 74.1, 76.5, 74.1, 77, 76.3, 78.3, 81.7, 
90.95, 100, 100, 100, 100), Pressmean = c(980.35, 980.35, 980.5, 
978.6, 978.7, 978.7, 978.6, 978.7, 978.5, 978.6, 978.6, 978.7, 
978.55, 978.6, 978.7, 978.55, 978.6, 978.7, 978.85, 978.8, 979, 
979.2, 979, 979, 979.2, 979.2, 979.1, 979.05, 979.2, 979.2, 978.75, 
978.5, 978.4, 978.15, 978.1, 977.9, 977.7, 977.8, 977.6, 977.95, 
977.95), Tempmean = c(21.6, 21.6, 21.6, 28.5, 27.9, 27.9, 27.5, 
27.5, 27.3, 26.75, 26.5, 26.4, 26.35, 26, 25.7, 25.45, 25.8, 
24.9, 24.7, 24.4, 24.3, 23.9, 23.6, 23.3, 23.3, 23.1, 23, 22.85, 
22.3, 22.5, 21.9, 22.2, 21.7, 22.05, 22, 21.3, 20.45, 19.7, 19.3, 
19.05, 19.05), F.SNRChannel = structure(c(1L, 2L, NA, NA, 1L, 
2L, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, 1L, 2L), .Label = c("Channel 1", "Channel 2"), class = "factor"), 
    SNR_40kHz = c(2, 65, NA, NA, 0, 65, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 73, 63), 
    HumidLevel = c("Avg Humid", "Avg Humid", "Avg Humid", "Low Humid", 
    "Avg Humid", "Avg Humid", "Avg Humid", "Avg Humid", "Avg Humid", 
    "Avg Humid", "Avg Humid", "Avg Humid", "Avg Humid", "Avg Humid", 
    "Avg Humid", "Avg Humid", "Avg Humid", "Avg Humid", "Avg Humid", 
    "Avg Humid", "Avg Humid", "Avg Humid", "Avg Humid", "Avg Humid", 
    "Avg Humid", "Avg Humid", "Avg Humid", "Avg Humid", "Avg Humid", 
    "Avg Humid", "Avg Humid", "Avg Humid", "Avg Humid", "Avg Humid", 
    "Avg Humid", "High Humid", "High Humid", "High Humid", "High Humid", 
    "High Humid", "High Humid"), SNRLevel = c("Low SNR", "Avg SNR", 
    NA, NA, "Low SNR", "Avg SNR", NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "High SNR", "Avg SNR"
    ), RainPresence = c("No rain", "No rain", "No rain", "No rain", 
    "No rain", "No rain", "No rain", "No rain", "No rain", "No rain", 
    "No rain", "No rain", "No rain", "No rain", "No rain", "No rain", 
    "No rain", "No rain", "No rain", "No rain", "No rain", "No rain", 
    "No rain", "No rain", "No rain", "No rain", "No rain", "No rain", 
    "No rain", "No rain", "No rain", "No rain", "No rain", "No rain", 
    "No rain", "No rain", "No rain", "No rain", "No rain", "No rain", 
    "No rain")), row.names = c(NA, -41L), groups = structure(list(
    binned = structure(c(1597578360, 1597579560, 1597622760, 
    1597623960, 1597625160, 1597626360, 1597627560, 1597628760, 
    1597629960, 1597631160, 1597632360, 1597633560, 1597634760, 
    1597635960, 1597637160, 1597638360, 1597639560, 1597640760, 
    1597641960, 1597643160, 1597644360, 1597645560, 1597646760, 
    1597647960, 1597649160, 1597650360, 1597651560, 1597652760, 
    1597653960, 1597655160, 1597656360, 1597657560, 1597658760, 
    1597659960, 1597661160, 1597662360, 1597663560, 1597664760
    ), tzone = "America/Chicago", class = c("POSIXct", "POSIXt"
    )), .rows = structure(list(1:2, 3L, 4L, 5:6, 7L, 8L, 9L, 
        10L, 11L, 12L, 13L, 14L, 15L, 16L, 17L, 18L, 19L, 20L, 
        21L, 22L, 23L, 24L, 25L, 26L, 27L, 28L, 29L, 30L, 31L, 
        32L, 33L, 34L, 35L, 36L, 37L, 38L, 39L, 40:41), ptype = integer(0), class = c("vctrs_list_of", 
    "vctrs_vctr", "list"))), row.names = c(NA, -38L), class = c("tbl_df", 
"