Check rows for NA, but with starting column specified by value in separate column

Question

I have a table in R that looks like this:

ID    Year    Source_1999    Source_2000    Source_2001    Source_2002
 1    1999            ABC            ABC           ABC             ABC
 2    2001            ABC            BBB           XYZ              NA
 3    2000             NA            ABC           BBB             BBB
 4    2001             NA             NA            NA              NA

The table has many rows, and quite a lot of "Source_" columns - probably about 50.

I need to make a new column that states whether any of the source columns contain NA, BUT I only want to check the years that are greater or equal to the year in the "Year" column. So my new table would look like this:

ID    Year    Source_1999    Source_2000    Source_2001    Source_2002   NA_check
 1    1999            ABC            ABC           ABC             ABC   No  
 2    2001            ABC            BBB           XYZ              NA  Yes 
 3    2000             NA            ABC           BBB             BBB   No
 4    2001             NA             NA            NA              NA  Yes

(the values in the new "NA" column can be any kind of binary indicator)

I have tried going through each year in turn, and using an if loop with the function is.na(df[,start_year:finish_year]), but this doesn't seem to work, and isn't very efficient.

In the future I might want to check other columns in this manner i.e. counting particular values, or summing the row, but with the starting column specified by this Year column, so am hoping I can adapt any answers to do this.

Any help much appreciated. Thanks

Answer 1

Here is a base R option with apply, to loop through the rows, get the index of first non-NA element, subset the row elements from that element, check for NA with anyNA and create the 'No/Yes' values based on that

df1$any_NA <- apply(df1[-(1:2)], 1, function(x) 
  c("No", "Yes")[anyNA(x[pmax(which(!is.na(x))[1], 1,
               na.rm = TRUE):length(x)]) + 1])
df1$any_NA
#[1] "No"  "Yes" "No"  "Yes"

data

df1 <- structure(list(ID = 1:4, Year = c(1999L, 2001L, 2000L, 2001L), 
Source_1999 = c("ABC", "ABC", NA, NA), Source_2000 = c("ABC", 
"BBB", "ABC", NA), Source_2001 = c("ABC", "XYZ", "BBB", NA
), Source_2002 = c("ABC", NA, "BBB", NA)), class = "data.frame", row.names = c(NA, 
-4L))

Answer 2

Here are two data.table approaches:

Not necessarily the fastest:

dt[, NA_check := Reduce(`|`, lapply(paste0("Source_", 1999:2002), 
    function(x) x >= paste0("Source_", Year) & is.na(get(x))))]

Converting into a long format:

checkNA <- melt(dt, id.vars=c("ID", "Year"), variable.factor=FALSE)[,
    anyNA(value[variable >= paste0("Source_", Year)]),
    by=.(ID, Year)]
dt[checkNA , on=.(ID, Year), NA_check := V1]

data:

library(data.table)
dt <- fread("ID    Year    Source_1999    Source_2000    Source_2001    Source_2002
1    1999            ABC            ABC           ABC             ABC
2    2001            ABC            BBB           XYZ              NA
3    2000             NA            ABC           BBB             BBB
4    2001             NA             NA            NA              NA")

Answer 3

That's a nice task for gather and spread from tidyr together with group_by, mutate from dplyr and parse_number from readr:

library(tidyverse)

mydata %>% 
  gather(source, value, starts_with("Source")) %>% 
  mutate(source_year = parse_number(source)) %>% 
  group_by(ID, Year) %>% 
  mutate(any_na = anyNA(value[Year <= source_year])) %>% 
  select(-source_year) %>% 
  spread(source, value)

# A tibble: 4 x 7
# Groups:   ID, Year [4]
#      ID  Year any_na Source_1999 Source_2000 Source_2001 Source_2002
#   <int> <int> <lgl>  <chr>       <chr>       <chr>       <chr>      
# 1     1  1999 FALSE  ABC         ABC         ABC         ABC        
# 2     2  2001 TRUE   ABC         BBB         XYZ         NA         
# 3     3  2000 FALSE  NA          ABC         BBB         BBB        
# 4     4  2001 TRUE   NA          NA          NA          NA  

Step-by-step
First turn your data from wide format into a long and extract the year of the source column.

mydata <- mydata %>% 
  gather(source, value, starts_with("Source")) %>% 
  mutate(source_year = parse_number(source)) 

mydata
# A tibble: 16 x 5
#      ID  Year source      value source_year
#   <int> <int> <chr>       <chr>       <dbl>
# 1     1  1999 Source_1999 ABC          1999
# 2     2  2001 Source_1999 ABC          1999
# 3     3  2000 Source_1999 NA           1999
# 4     4  2001 Source_1999 NA           1999
# 5     1  1999 Source_2000 ABC          2000
# ...

Then group by ID and year, such that the following calculations are applied in these groups. filter the value by the source_Years whichs are greater or equal to the group year and check whether there are any NA's

mydata <- mydata %>% 
  group_by(ID, Year) %>% 
  mutate(any_na = anyNA(value[Year <= source_year])) 

mydata
# A tibble: 16 x 6
# Groups:   ID, Year [4]
# ID  Year source      value source_year any_na
# <int> <int> <chr>       <chr>       <dbl> <lgl> 
# 1     1  1999 Source_1999 ABC          1999 FALSE 
# 2     2  2001 Source_1999 ABC          1999 TRUE  
# 3     3  2000 Source_1999 NA           1999 FALSE 
# 4     4  2001 Source_1999 NA           1999 TRUE  
# 5     1  1999 Source_2000 ABC          2000 FALSE 
# ...

Finally drop the yource_year column as it's not needed anymore and transform the data from long to wide format:

mydata <- mydata %>% 
  select(-source_year) %>% 
  spread(source, value)

Data

mydata <- tibble(ID = 1:4, 
                 Year = c(1999L, 2001L, 2000L, 2001L), 
                 Source_1999 = c("ABC", "ABC", NA, NA), 
                 Source_2000 = c("ABC", "BBB", "ABC", NA), 
                 Source_2001 = c("ABC", "XYZ", "BBB", NA), 
                 Source_2002 = c("ABC", NA, "BBB", NA))