根据几列删除重复项并保留数字值(如果有)，如果 R 中没有数字则保留 NA

Question

我在下面有一个数据框，我想删除基于国家和年份列的重复项，并将第 3 列的非 NA 值保留到最后一列。如果 (country, year) 中的所有行都是 NA，则该行的值也应该是 NA。

   iso2c country year DT.ODA.ODAT.GN.ZS NY.GDP.MKTP.CD DT.ODA.ODAT.GD.ZS DT.ODA.ALLD.GD.ZS DT.ODA.ODAT.XP.ZS DT.ODA.ALLD.XP.ZS NY.GNP.MKTP.CD
1    AGO  Angola 1985                NA             NA          1.329899          1.329899                NA                NA             NA
2     AO  Angola 1985          1.352825     7558613008                NA                NA                NA                NA     6688963211
3    AGO  Angola 1986                NA             NA          2.049293          2.049293                NA                NA             NA
4     AO  Angola 1986          1.947237     7076793823                NA                NA                NA                NA     6688963211
5    AGO  Angola 1987                NA             NA          1.820775          1.820775                NA                NA             NA
6     AO  Angola 1987          2.009728     8089279285                NA                NA                NA                NA     6688963211
7    AGO  Angola 1988                NA             NA          1.968970          1.968970                NA                NA             NA
8     AO  Angola 1988          2.347598     8775116269                NA                NA                NA                NA     6688963211
9    AGO  Angola 1989                NA             NA          1.799623          1.799623                NA                NA             NA
10    AO  Angola 1989          1.665031    10207922517                NA                NA                NA                NA    10033444816

我尝试过使用 summarize accross，

df %>%
  select(-iso2c) %>%
  group_by(country, year) %>%
  summarise(across(, ~ sum(.x, na.rm = TRUE)))

   country  year DT.ODA.ODAT.GN.ZS NY.GDP.MKTP.CD DT.ODA.ODAT.GD.ZS DT.ODA.ALLD.GD.ZS DT.ODA.ODAT.XP.ZS DT.ODA.ALLD.XP.ZS NY.GNP.MKTP.CD
   <chr>   <int>             <dbl>          <dbl>             <dbl>             <dbl>             <dbl>             <dbl>          <dbl>
 1 Angola   1985              1.35    7558613008.              1.33              1.33                 0                 0    6688963211.
 2 Angola   1986              1.95    7076793823.              2.05              2.05                 0                 0    6688963211.
 3 Angola   1987              2.01    8089279285.              1.82              1.82                 0                 0    6688963211.
 4 Angola   1988              2.35    8775116269.              1.97              1.97                 0                 0    6688963211.
 5 Angola   1989              1.67   10207922517.              1.80              1.80                 0                 0   10033444816.
 6 Angola   1990              2.65   11236275843.              2.59              2.59                 0                 0   10033444816.
 7 Angola   1991              0                0               2.27              2.27                 0                 0             0 
 8 Angola   1992              0                0               5.95              5.95                 0                 0             0 
 9 Angola   1993              0                0               5.48              5.48                 0                 0             0 
10 Angola   1994             30.1     3390500000              11.0              11.0                  0                 0    1484500000 

但随后它为所有行均为 NA 的组返回 0，这是有问题的，因为我可能有实际上为 0 的观察结果，然后我可能无法区分真实的 0 和由 NA 创建的 0。

可重现的数据集如下

df <- structure(list(iso2c = c("AGO", "AO", "AGO", "AO", "AGO", "AO", 
"AGO", "AO", "AGO", "AO", "AGO", "AO", "AGO", "AO", "AGO", "AO", 
"AGO", "AO", "AGO", "AO"), country = c("Angola", "Angola", "Angola", 
"Angola", "Angola", "Angola", "Angola", "Angola", "Angola", "Angola", 
"Angola", "Angola", "Angola", "Angola", "Angola", "Angola", "Angola", 
"Angola", "Angola", "Angola"), year = c(1985L, 1985L, 1986L, 
1986L, 1987L, 1987L, 1988L, 1988L, 1989L, 1989L, 1990L, 1990L, 
1991L, 1991L, 1992L, 1992L, 1993L, 1993L, 1994L, 1994L), DT.ODA.ODAT.GN.ZS = c(NA, 
1.35282546806335, NA, 1.9472375, NA, 2.00972839050293, NA, 2.34759848175049, 
NA, 1.66503130900065, NA, 2.64884089050293, NA, NA, NA, NA, NA, 
NA, NA, 30.1158644206278), NY.GDP.MKTP.CD = c(NA, 7558613007.90635, 
NA, 7076793822.60201, NA, 8089279284.72241, NA, 8775116269.16722, 
NA, 10207922517.1839, NA, 11236275842.7358, NA, NA, NA, NA, NA, 
NA, NA, 3390500000), DT.ODA.ODAT.GD.ZS = c(1.32989861920417, 
NA, 2.04929343541605, NA, 1.82077454909391, NA, 1.9689704235723, 
NA, 1.79962315882805, NA, 2.59030206019162, NA, 2.27231226407093, 
NA, 5.94508667614588, NA, 5.47506427358001, NA, 11.0127233451064, 
NA), DT.ODA.ALLD.GD.ZS = c(1.32989861920417, NA, 2.04929343541605, 
NA, 1.82077454909391, NA, 1.9689704235723, NA, 1.79962315882805, 
NA, 2.59030206019162, NA, 2.27231226407093, NA, 5.94508667614588, 
NA, 5.47506427358001, NA, 11.0127233451064, NA), DT.ODA.ODAT.XP.ZS = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), DT.ODA.ALLD.XP.ZS = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), NY.GNP.MKTP.CD = c(NA, 
6688963210.70234, NA, 6688963210.70234, NA, 6688963210.70234, 
NA, 6688963210.70234, NA, 10033444816.0535, NA, 10033444816.0535, 
NA, NA, NA, NA, NA, NA, NA, 1484500000)), row.names = c(NA, 20L
), class = "data.frame")

Answer 1

一种可能的解决方案，使用 all(is.na(.x)) 检测分组列中的所有 元素何时为 NA :

df %>%
  select(-iso2c) %>%
  group_by(country, year) %>%
  summarise(across(everything(),
    ~ if_else(all(is.na(.x)), NA_real_, sum(.x, na.rm = TRUE))))