Search code examples
rdata.tablecoalesce

R First Non-NA Value From Cols

df <- data.frame(ID=c(1,2,3,4,5,6),
                CO=c(-6,4,2,3,0,2),
                CATFOX=c(1,NA,NA,3,0,NA),
                DOGFOX=c(NA,NA,5,1,2,NA),
                RABFOX=c(NA,3,NA,5,3,NA),
                D=c(0,4,5,6,1,2),
                WANT=c(1,3,5,3,0,NA))

I have a dataframe and i wish to make column WANT take the first value of 'CATFOX' 'DOGFOX' 'RABFOX' that is not NA. Is there a data.table solution? I tried this but it did not produce the desired outcome:

df$WANT=do.call(coalesce, data[grepl('FOX',names(data))])

Solution

  • We can use a vectorized option in base R

    i1 <- endsWith(names(df), 'FOX')
df$WANT2 <-  df[i1][cbind(seq_len(nrow(df)), max.col(!is.na(df[i1]), 'first'))]
df$WAN2
#[1]  1  3  5  3  0 NA