如何在指定国家与年份交叉的单元格填充NA值

现有如下宽格式的R数据集（包含国家和各年份的数值）：

N_COUNTRIES <- 10
YEARS <- 2012:2020
N_YEARS <- length(YEARS)

# simulate x data
countries <- LETTERS[1:N_COUNTRIES]
mat_x <- matrix(runif(N_COUNTRIES*N_YEARS, 0, 100), nrow = N_COUNTRIES)
colnames(mat_x) <- YEARS
df_x <- bind_cols(country = countries, mat_x)

df_x

输出结果：

# A tibble: 10 × 10
   country `2012` `2013` `2014` `2015` `2016` `2017` `2018` `2019` `2020`
   <chr>    <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
 1 A        22.8    33.2  63.7    3.21   66.1  64.9   0.872  75.0   23.3 
 2 B         7.55   98.2  21.0   54.2    59.8  18.0  57.1    68.9   87.0 
 3 C        41.3    34.3  27.0    4.04   96.3  73.2  40.5    77.8   83.0 
 4 D        20.7    24.5  56.8   35.6    31.3  84.6  45.1    14.0   36.2 
 5 E        76.1    80.2   4.94  35.7    11.6   3.99 71.1    64.7    7.70
 6 F        87.4    26.8  48.0   45.2    82.4  95.3  60.0    36.1    4.80
 7 G        46.4    52.1   4.33   5.98   97.3  67.6  90.3    97.2    4.21
 8 H         4.19   21.4   8.53  55.4    45.8  31.5   9.26   95.9   51.7 
 9 I        46.8    95.2   9.50  35.1    15.9  84.9  44.4     8.26  77.1 
10 J        25.1    77.5  15.6   74.2    51.3  52.8  37.5    11.1    7.60

需要根据以下指定的国家-年份组合，将对应单元格填充为NA值：

Count = c("A","A","C","F","F","I")
Years = c("2013","2016","2014","2018","2015","2017")
Fill  = rep("NA", 6)

df_y = data.frame(Country = Count, Year = Years, Fill = Fill)

df_y

输出结果：

Country Year Fill
1       A 2013   NA
2       A 2016   NA
3       C 2014   NA
4       F 2018   NA
5       F 2015   NA
6       I 2017   NA

方法一：使用tidyverse工具（推荐） #

通过宽转长-标记NA-长转宽的流程实现，适合处理结构化数据：

library(tidyverse)

# 1. 将宽格式数据转为长格式，每行对应一个国家-年份的数值
df_x_long <- df_x %>%
  pivot_longer(cols = -country, names_to = "Year", values_to = "value")

# 2. 合并需要设NA的规则表，标记需要替换的行并设置NA
df_x_long_na <- df_x_long %>%
  left_join(df_y %>% select(Country, Year), by = c("country" = "Country", "Year")) %>%
  mutate(value = ifelse(!is.na(Country), NA, value)) %>%
  select(-Country)

# 3. 将数据转回宽格式，得到最终结果
df_x_updated <- df_x_long_na %>%
  pivot_wider(names_from = "Year", values_from = "value")

# 查看更新后的数据
df_x_updated

方法二：基础R循环实现 #

直接遍历指定的国家-年份组合，定位单元格并赋值NA，适合小数据集：

# 遍历每个需要设NA的条目
for(i in 1:nrow(df_y)){
  target_country <- df_y$Country[i]
  target_year <- df_y$Year[i]
  
  # 定位目标行和列的索引
  row_pos <- which(df_x$country == target_country)
  col_pos <- which(colnames(df_x) == target_year)
  
  # 将对应单元格设为NA
  df_x[row_pos, col_pos] <- NA
}

# 查看更新后的数据
df_x

内容的提问来源于stack exchange，提问作者Saïd Maanan