我有一个下面的数据框,我想根据国家和年份列删除重复项,并保留第3列到最后一列的非NA值。如果(country, year)范围内的所有行都是NA,则该行的值也应为NA。
```python df.drop_duplicates(subset=['country', 'year'], keep='last', inplace=True) df = df.groupby(['country', 'year']).apply(lambda x: x.iloc[:, 2:].dropna(how='all')) ```
```python df.drop_duplicates(subset=['country', 'year'], keep='last', inplace=True) df = df.groupby(['country', 'year']).apply(lambda x: x.iloc[:, 2:].dropna(how='all')) ```
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 across,
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")