在R中筛选具备至少3年非连续有效ESG类数据的企业面板数据

需求说明 #

• 拥有2010-2020年9000+家企业的面板数据
• 需保留在指定6个变量（refin_e、refin_esg、ESG_spg、E_spg、env_msci_hist、esg_msci_hist）上至少有3年非连续非NA值的企业
• 若某企业移除NA后有效年份不足3年，则直接剔除该企业
• 已实现连续3年有效数据的筛选，现需非连续场景的R解决方案

现有连续筛选代码 #

panel_data1bis <- panel_data1 %>%
  group_by(ISIN) %>%
  filter(any(with(rle(if_all(c("refin_e", "refin_esg", "ESG_spg", "E_spg",
                               "env_msci_hist", "esg_msci_hist"), complete.cases)),
                  values & lengths >2))) %>%
  ungroup

测试数据子集 #

structure(list(ISIN = c("AEA000201011", "AEA000201011", "AEA000201011", 
"AEA000201011", "AEA000201011", "AEA000201011", "AEA000201011", 
"AEA000201011", "AEA000201011", "AEA000201011", "AEA000201011", 
"AEA000801018", "AEA000801018", "AEA000801018", "AEA000801018", 
"AEA000801018", "AEA000801018", "AEA000801018", "AEA000801018", 
"AEA000801018", "AEA000801018", "AEA000801018", "AEA001501013", 
"AEA001501013", "AEA001501013"), year = c(2010L, 2011L, 2012L, 
2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 2010L, 
2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 
2020L, 2010L, 2011L, 2012L), full_company_name = c("Abu Dhabi Commercial Bank PJSC", 
"Abu Dhabi Commercial Bank PJSC", "Abu Dhabi Commercial Bank PJSC", 
"Abu Dhabi Commercial Bank PJSC", "Abu Dhabi Commercial Bank PJSC", 
"Abu Dhabi Commercial Bank PJSC", "Abu Dhabi Commercial Bank PJSC", 
"Abu Dhabi Commercial Bank PJSC", "Abu Dhabi Commercial Bank PJSC", 
"Abu Dhabi Commercial Bank PJSC", "Abu Dhabi Commercial Bank PJSC", 
"Abu Dhabi Islamic Bank PJSC", "Abu Dhabi Islamic Bank PJSC", 
"Abu Dhabi Islamic Bank PJSC", "Abu Dhabi Islamic Bank PJSC", 
"Abu Dhabi Islamic Bank PJSC", "Abu Dhabi Islamic Bank PJSC", 
"Abu Dhabi Islamic Bank PJSC", "Abu Dhabi Islamic Bank PJSC", 
"Abu Dhabi Islamic Bank PJSC", "Abu Dhabi Islamic Bank PJSC", 
"Abu Dhabi Islamic Bank PJSC", "Arabtec Holding PJSC", "Arabtec Holding PJSC", 
"Arabtec Holding PJSC"), refin_esg = c(NA, NA, NA, NA, 48.74, 
54.32, 63.81, 67.64, 71.74, 68.17, 63.8, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, 30.89, NA, NA, NA, NA), refin_e = c(NA, NA, NA, 
NA, 23.21, 53.02, 60.18, 18.64, 50.25, 52.21, 46.69, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, 20.52, NA, NA, NA, NA), esg_msci_hist = c(NA, 
NA, NA, NA, NA, 70, 84, 73, 74, 63, 71, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA), env_msci_hist = c(NA, NA, NA, 
NA, NA, 29, 17, 20, 19, 14, 26, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA), ESG_spg = c(NA, NA, NA, NA, NA, 35, 
36, 37, 26, 20, 15, NA, NA, NA, NA, NA, NA, NA, NA, NA, 8, 6, 
NA, NA, NA), E_spg = c(NA, NA, NA, NA, NA, 18, 26, 26, 15, 12, 
2, NA, NA, NA, NA, NA, NA, NA, NA, NA, 6, 0, NA, NA, NA)), row.names = c(NA, 
-25L), class = c("tbl_df", "tbl", "data.frame"))

预期输出示例 #

ISIN         year full_…¹ refin…² refin_e esg_m…³ env_m…⁴
   <chr>       <int> <chr>     <dbl>   <dbl>   <dbl>   <dbl>
 1 AEA0002010…  2015 Abu Dh…    54.3    53.0      70      29
 2 AEA0002010…  2016 Abu Dh…    63.8    60.2      84      17
 3 AEA0002010…  2017 Abu Dh…    67.6    18.6      73      20
 4 AEA0002010…  2018 Abu Dh…    71.7    50.2      74      19
 5 AEA0002010…  2019 Abu Dh…    68.2    52.2      63      14

非连续场景解决方案 #

核心思路：按企业分组后，统计每个企业在指定6个变量上完全无NA的年份总数，只要总数≥3就保留该企业（可选择保留所有观测或仅保留有效观测）。

方案1：保留符合条件企业的所有观测 #

panel_data_filtered <- panel_data1 %>%
  group_by(ISIN) %>%
  # 标记每行是否在6个变量上均无NA
  mutate(is_valid = if_all(c("refin_e", "refin_esg", "ESG_spg", "E_spg",
                             "env_msci_hist", "esg_msci_hist"), complete.cases)) %>%
  # 统计该企业的有效年份总数
  mutate(total_valid = sum(is_valid)) %>%
  # 筛选有效年份≥3的企业
  filter(total_valid >= 3) %>%
  ungroup()

方案2：仅保留符合条件企业的有效观测（匹配预期输出） #

panel_data_filtered <- panel_data1 %>%
  group_by(ISIN) %>%
  mutate(is_valid = if_all(c("refin_e", "refin_esg", "ESG_spg", "E_spg",
                             "env_msci_hist", "esg_msci_hist"), complete.cases)) %>%
  mutate(total_valid = sum(is_valid)) %>%
  # 同时满足：企业有效年份≥3，且当前行是有效观测
  filter(total_valid >= 3 & is_valid) %>%
  select(-is_valid, -total_valid) %>% # 移除临时变量
  ungroup()

代码说明 #

• if_all(..., complete.cases)：检查指定6个变量在当前行是否均无NA，返回逻辑值
• sum(is_valid)：统计该企业的有效年份总数
• 若需保留企业所有观测（包括NA行），仅用filter(total_valid >=3)；若需仅保留有效行，则添加& is_valid

验证结果 #

用测试数据运行方案2，会得到与预期输出一致的结果：仅保留AEA000201011企业的有效年份（2015-2019年），另外两家企业因有效年份不足3年被完全剔除。

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