Conditionally filling in dataframe values using information from a separate df using R

user_6323015 0
Viewed 46

Suppose I have a table with a country-year unit, like below, that records information about a few variables (the actual dataset is very large). Some values in some of the columns are missing (not all of cols are affected). However, some of the 'missing' values in the affected columns are really zeros because only non-zero values were initially recorded.

data <- tibble::tibble(country = c(rep("USA",8), rep("MEX",8))
                       ,year = c(1990:1997, 1990:1997)
                       ,var1 = c(1:4, rep(NA, 4), c(3,3,3,3), rep(NA, 4))
                       ,var2 = c(rep(c(rep(1, 6), rep(NA, 2)), 2))
                       ,var3 = c(1:length(country))
                       ,var4 = c(length(country):1)
                       )

So, I have information regarding when those problematic variables in the data df were observed, such that anything outside these ranges should be NA and anything inside the ranges should be 0:

when_observed <- tibble::tibble(variable = c(rep("var1",6), rep("var2",7))
                       ,year = c(c(1990:1995), c(1990:1996))
                       )

I need something that will use the information regarding when the variable columns are observed (using when_observed) and fill in those values with zeros in the data df, but without altering actual missing values. It should produce the following table, but at scale (handling multiple column types beyond numerics would be great too):

goal_data <- tibble::tibble(country = c(rep("USA",8), rep("MEX",8))
                            ,year = c(1990:1997, 1990:1997)
                            ,var1 = c(1:4, 0, 0, rep(NA, 2), c(3,3,3,3), 0, 0, rep(NA, 2))
                            ,var2 = c(rep(c(rep(1, 6), 0, NA), 2))
                            ,var3 = c(1:length(country))
                            ,var4 = c(length(country):1)
                            )

Thanks for any ideas/help.

dplyr tidyverse r
1 Answers

One dplyr option could be:

data %>%
 mutate(across(var1:var4,
               ~ ifelse(is.na(.) & year %in% observed$year[which(observed$variable %in% cur_column())], 0, .)))

   country  year  var1  var2  var3  var4
   <chr>   <int> <dbl> <dbl> <int> <int>
 1 USA      1990     1     1     1    16
 2 USA      1991     2     1     2    15
 3 USA      1992     3     1     3    14
 4 USA      1993     4     1     4    13
 5 USA      1994     0     1     5    12
 6 USA      1995     0     1     6    11
 7 USA      1996    NA     0     7    10
 8 USA      1997    NA    NA     8     9
 9 MEX      1990     3     1     9     8
10 MEX      1991     3     1    10     7
11 MEX      1992     3     1    11     6
12 MEX      1993     3     1    12     5
13 MEX      1994     0     1    13     4
14 MEX      1995     0     1    14     3
15 MEX      1996    NA     0    15     2
16 MEX      1997    NA    NA    16     1
user_59645570
Related