R语言DataFrame筛选与统计汇总技术求助

原始数据构造 #

ID<-c("KMT1","KMT1","KMT1","KMT2","KMT2","KMT2","KMT3","KMT3","KMT3")

Date<-c("01-03-2015","01-03-2015","01-03-2015",
        "04-06-2014","04-06-2014","04-06-2014",
        "07-01-2019","07-01-2019","07-01-2019")
TimeUTC<-c("10:22:05","10:22:05","10:22:05",
           "10:25:05","10:25:05","10:25:05",
           "10:23:05","10:23:05","10:23:05")

V1<-c(0.01,0.003,0.04,0.03,0.02,0.05,0.03,0.1,0.02)
V2<-c(0.02,0.002,0.02,0.003,0.002,0.09,0.01,0.05,0.023)
V3<-c(0.04,0.008,0.06,0.09,0.004,0.05,0.01,0.003,0.04)
V4<-c(0.08,0.009,0.08,0.09,0.004,0.05,0.05,0.03,0.1)

Flag1<-c(0,0,0,1,0,1,0,0,1)
Flag2<-c(0,0,0,0,0,1,1,0,0)
Flag3<-c(0,0,0,0,0,0,1,1,0)

df1<-data.frame(ID,Date,TimeUTC,V1,V2,V3,V4,Flag1,Flag2,Flag3)

数据预览：

df1
#>     ID       Date  TimeUTC    V1    V2    V3    V4 Flag1 Flag2 Flag3
#> 1 KMT1 01-03-2015 10:22:05 0.010 0.020 0.040 0.080     0     0     0
#> 2 KMT1 01-03-2015 10:22:05 0.003 0.002 0.008 0.009     0     0     0
#> 3 KMT1 01-03-2015 10:22:05 0.040 0.020 0.060 0.080     0     0     0
#> 4 KMT2 04-06-2014 10:25:05 0.030 0.003 0.090 0.090     1     0     0
#> 5 KMT2 04-06-2014 10:25:05 0.020 0.002 0.004 0.004     0     0     0
#> 6 KMT2 04-06-2014 10:25:05 0.050 0.090 0.050 0.050     1     1     0
#> 7 KMT3 07-01-2019 10:23:05 0.030 0.010 0.010 0.050     0     1     1
#> 8 KMT3 07-01-2019 10:23:05 0.100 0.050 0.003 0.030     0     0     1
#> 9 KMT3 07-01-2019 10:23:05 0.020 0.023 0.040 0.100     1     0     0

核心需求 #

• 按ID统计每个分组下激活Flag（值为1）的数量及类型；
• 按规则移除数据：
  • 若Flag1或Flag2激活，移除整个ID组；
  • 仅Flag3激活（且Flag1/Flag2未激活），移除该条记录；
• 筛选后计算每个ID的V1-V4中位数，同时保留Date和TimeUTC列，需输出两种结果：
  • (A) 移除Flag3激活条目 + 移除含Flag1/Flag2的ID组
  • (B) 仅保留无任何Flag激活的ID组

解决方案（dplyr版） #

先加载依赖包：

library(dplyr)

步骤1：统计每个ID的Flag激活情况 #

先明确每个ID下各Flag的激活次数，方便后续筛选：

flag_summary <- df1 %>%
  group_by(ID) %>%
  summarise(
    Flag1_count = sum(Flag1),
    Flag2_count = sum(Flag2),
    Flag3_count = sum(Flag3),
    .groups = "drop"
  )

flag_summary
#> # A tibble: 3 × 4
#>   ID    Flag1_count Flag2_count Flag3_count
#>   <chr>       <dbl>       <dbl>       <dbl>
#> 1 KMT1            0           0           0
#> 2 KMT2            2           1           0
#> 3 KMT3            1           1           2

情况A：按规则筛选后计算中位数 #

result_A <- df1 %>%
  # 先过滤掉包含Flag1/Flag2的ID
  filter(!ID %in% flag_summary$ID[flag_summary$Flag1_count > 0 | flag_summary$Flag2_count > 0]) %>%
  # 再移除当前ID组内Flag3激活的条目
  filter(Flag3 == 0) %>%
  # 按ID+Date+TimeUTC分组计算中位数（同一ID下Date/TimeUTC值一致，直接保留）
  group_by(ID, Date, TimeUTC) %>%
  summarise(
    V1.med = median(V1),
    V2.med = median(V2),
    V3.med = median(V3),
    V4.med = median(V4),
    .groups = "drop"
  )

result_A
#> # A tibble: 2 × 7
#>   ID    Date       TimeUTC  V1.med V2.med V3.med V4.med
#>   <chr> <chr>      <chr>     <dbl>  <dbl>  <dbl>  <dbl>
#> 1 KMT1  01-03-2015 10:22:05  0.01   0.02   0.04   0.08
#> 2 KMT2  04-06-2014 10:25:05  0.025  0.025  0.047  0.047

情况B：仅保留无任何Flag激活的ID组 #

result_B <- df1 %>%
  # 筛选出所有Flag都未激活的ID
  filter(ID %in% flag_summary$ID[flag_summary$Flag1_count == 0 & flag_summary$Flag2_count == 0 & flag_summary$Flag3_count == 0]) %>%
  group_by(ID, Date, TimeUTC) %>%
  summarise(
    V1.med = median(V1),
    V2.med = median(V2),
    V3.med = median(V3),
    V4.med = median(V4),
    .groups = "drop"
  )

result_B
#> # A tibble: 1 × 7
#>   ID    Date       TimeUTC  V1.med V2.med V3.med V4.med
#>   <chr> <chr>      <chr>     <dbl>  <dbl>  <dbl>  <dbl>
#> 1 KMT1  01-03-2015 10:22:05    0.01   0.02   0.04   0.08

解决方案（data.table版） #

如果习惯用data.table，同样可以实现，且能完整保留Date/TimeUTC列：

library(data.table)
setDT(df1)

# 统计Flag激活情况
flag_summary_dt <- df1[, .(
  Flag1_count = sum(Flag1),
  Flag2_count = sum(Flag2),
  Flag3_count = sum(Flag3)
), by = ID]

# 情况A
result_A_dt <- df1[
  !ID %in% flag_summary_dt[Flag1_count > 0 | Flag2_count > 0, ID] & Flag3 == 0,
  .(
    V1.med = median(V1),
    V2.med = median(V2),
    V3.med = median(V3),
    V4.med = median(V4)
  ), by = .(ID, Date, TimeUTC)
]

# 情况B
result_B_dt <- df1[
  ID %in% flag_summary_dt[Flag1_count == 0 & Flag2_count == 0 & Flag3_count == 0, ID],
  .(
    V1.med = median(V1),
    V2.med = median(V2),
    V3.med = median(V3),
    V4.med = median(V4)
  ), by = .(ID, Date, TimeUTC)
]

内容的提问来源于stack exchange，提问作者Peter.2055