Home > Net >  Fill with NAs at the intersection of a row and a column
Fill with NAs at the intersection of a row and a column

Time:08-05

I have a data set that looks like this:

> 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

And I need to fill some of its cells with NAs, the cells are indicated in the table below:

> 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
> 

Which means that the cell at the intersection of Country A and Year 2013 should be NA, and so on. How do I fill the cells at the intersection of Country and Year with NAs?

CodePudding user response:

Loop over the Count and Years variables:

for (i in seq_along(Count)) {
  df_x[df_x$country == Count[i], ][[Years[i]]] <- NA
}
  • Related