I have a dataframe with multiple columns that I want to turn into one column with 'coalesce'. Each existing column is a different source of depth data for each location (rows). I need a final "depth" column where the first non-NA value is chosen from the row.
My data is full of NA values with sparse numeric values as below:
df <- data.frame(col1=c("A","B","C","D"), col2=c(NA,NA,NA,NA), col3=c(-94.2,-95.3,NA,NA),col4=c(-91.2,NA,-90.7,-90.9)
df
col1 col2 col3 col4
1 A NA -94.2 -91.2
2 B NA -95.3 NA
3 C NA NA -90.7
4 D NA NA -90.9
I would like it to look like this:
col1 col2 col3 col4 depth
1 A NA -94.2 -91.2 -94.2
2 B NA -95.3 NA -95.3
3 C NA NA -90.7 -90.7
4 D NA NA -90.9 -90.9
I have tried to do this using this code:
df %>% mutate(depth = coalesce(2:4)
and also
df %>% coalesce(2:4)
but only receive the error:
Error in `mutate()`:
ℹ In argument: `depth = coalesce(2:4)`.
Caused by error:
! `depth` must be size 4 or 1, not 3.
or
Error in `coalesce()`:
! Can't recycle `..1` (size 4) to match `..2` (size 3).
I've checked and all the columns in the dataframe are the same length.
I would appreciate any help, I can't find any similar questions or guidance online! Thanks
You can use !!! here, which will pass the variables through:
df$depth <- coalesce(!!!df[-1]) # or coalesce(!!!df[2:4])
# or
df %>%
mutate(depth = coalesce(!!!df[-1]))
Both give:
# col1 col2 col3 col4 depth
# 1 A NA -94.2 -91.2 -94.2
# 2 B NA -95.3 NA -95.3
# 3 C NA NA -90.7 -90.7
# 4 D NA NA -90.9 -90.9