如何使用dplyr在R中保留各列均含至少三个非NA值的分组

要实现你的需求——仅保留每个变量列都至少包含3个非NA值的ISIN分组，我们可以用dplyr包的分组筛选功能来高效处理。

步骤说明 #

1. 按ISIN分组，确保我们针对每个独立的ISIN进行检查
2. 对每个目标列（refin_esg、refin_e、mktcap）统计非NA值的数量，要求数量≥3
3. 仅保留所有列都满足条件的分组

完整代码 #

library(dplyr)

# 加载你的数据
df <- structure(list(ISIN = c("AEA000201011", "AEA000201011", "AEA000201011", "AEA000201011", "AEA000201011", "AEA000201011", "AEA000201011", "AEA000201011", "AEA000201011", "AEA000201011", "AEA000801018", "AEA000801018", "AEA000801018", "AEA000801018", "AEA000801018", "AEA000801018", "AEA000801018", "AEA000801018", "AEA000801018", "AEA000801018", "AEA001501013", "AEA001501013", "AEA001501013", "AEA001501013", "AEA001501013", "AEA001501013", "AEA001501013", "AEA001501013", "AEA001501013", "AEA001501013"), year = c(2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L), refin_esg = c(NA, NA, NA, NA, 48.74, 54.32, 63.81, 67.64, 71.74, 68.17, NA, NA, NA, NA, NA, NA, NA, NA, NA, 30.89, NA, NA, NA, NA, 13.47, 14.63, 19.68, 17.51, 13.92, 15.15), refin_e = c(NA, NA, NA, NA, 23.21, 53.02, 60.18, 18.64, 50.25, 52.21, NA, NA, NA, NA, NA, NA, NA, NA, NA, 20.52, NA, NA, NA, NA, 0, 0, 0, 0, 0, 0), mktcap = c(2723907, 4219977, 4576565, 9207428, 9954352, 9329029, 9765366, 9623839, 11548606, 15002191, 1918568, 2047331, 2047331, 3734126, 4655646, 3399201, 3277575, 3260324, 3856508, 5329892, 641477, 647176, 957332, 2452771, 3505987, 1571026, 1646012, 971968, 775941, 526823)), class = c("grouped_df", "tbl_df", "tbl", "data.frame"), row.names = c(NA, -30L), groups = structure(list( ISIN = c("AEA000201011", "AEA000801018", "AEA001501013"), .rows = structure(list(1:10, 11:20, 21:30), ptype = integer(0), class = c("vctrs_list_of", "vctrs_vctr", "list"))), row.names = c(NA, -3L), class = c("tbl_df", "tbl", "data.frame"), .drop = TRUE))

# 执行筛选
filtered_df <- df %>%
  group_by(ISIN) %>%
  filter(
    # 对每个目标列检查非NA数量≥3
    across(c(refin_esg, refin_e, mktcap), ~ sum(!is.na(.x)) >= 3)
  ) %>%
  ungroup() # 可选：如果不需要保留分组结构，可以去掉这行

# 查看筛选结果
print(filtered_df)

结果说明 #

运行代码后，会保留以下两个ISIN的所有行：

• AEA000201011：refin_esg和refin_e各有6个非NA值，mktcap全为非NA，满足条件
• AEA001501013：refin_esg有6个非NA值，refin_e的0属于有效值（非NA），共6个，mktcap全为非NA，满足条件

而AEA000801018因为refin_esg和refin_e仅各有1个非NA值，不符合要求，会被剔除。

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