How to fill NA with last non-missing value from previous columns?-CodePudding

My df contains a column (V5) with all missing values:

> df
# A tibble: 7 × 5
     V1    V2    V3    V4 V5   
  <dbl> <dbl> <dbl> <dbl> <lgl>
1  1.19  2.45  0.83  0.87 NA   
2  1.13  0.79  0.68  5.43 NA   
3  1.18  1.09  1.04 NA    NA   
4  1.11  1.1   4.24 NA    NA   
5  1.16  1.13 NA    NA    NA   
6  1.18 NA    NA    NA    NA   
7  1.44 NA     9.17 NA    NA

And I want to fill column V5 with the nearest non-missing value from the preceding columns:

> df1
# A tibble: 7 × 5
     V1    V2    V3    V4    V5
  <dbl> <dbl> <dbl> <dbl> <dbl>
1  1.19  2.45  0.83  0.87  0.87
2  1.13  0.79  0.68  5.43  5.43
3  1.18  1.09  1.04 NA     1.04
4  1.11  1.1   4.24 NA     4.24
5  1.16  1.13 NA    NA     1.13
6  1.18 NA    NA    NA     1.18
7  1.44 NA     9.17 NA     9.17

There are similar posts, but none is helping with this case. So any clue will be greatly appreciated.

Here is the dput:

structure(list(V1 = c(1.19, 1.13, 1.18, 1.11, 1.16, 1.18, 1.44
), V2 = c(2.45, 0.79, 1.09, 1.1, 1.13, NA, NA), V3 = c(0.83, 
0.68, 1.04, 4.24, NA, NA, 9.17), V4 = c(0.87, 5.43, NA, NA, NA, 
NA, NA), V5 = c(NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_)), row.names = c(NA, 
-7L), class = c("tbl_df", "tbl", "data.frame"))

CodePudding user response：

You could use

library(dplyr)
df %>% 
  mutate(V5 = coalesce(V4, V3, V2, V1))

This returns

# A tibble: 7 x 5
     V1    V2    V3    V4    V5
  <dbl> <dbl> <dbl> <dbl> <dbl>
1  1.19  2.45  0.83  0.87  0.87
2  1.13  0.79  0.68  5.43  5.43
3  1.18  1.09  1.04 NA     1.04
4  1.11  1.1   4.24 NA     4.24
5  1.16  1.13 NA    NA     1.13
6  1.18 NA    NA    NA     1.18
7  1.44 NA     9.17 NA     9.17

Or more general from https://github.com/tidyverse/funs/issues/54#issuecomment-892377998

df %>% 
  mutate(V5 = do.call(coalesce, rev(across(-V5))))

or https://github.com/tidyverse/funs/issues/54#issuecomment-1096449488

df %>% 
  mutate(V5 = coalesce(!!!rev(select(., -V5))))

CodePudding user response：

You can also try this, however, the other solution is more elegant and certainly most recommended:

library(dplyr)

df %>%
  rowwise() %>%
  mutate(V5 = last(c_across(V1:V4)[!is.na(c_across(V1:V4))]))

# A tibble: 7 x 5
# Rowwise: 
     V1    V2    V3    V4    V5
  <dbl> <dbl> <dbl> <dbl> <dbl>
1  1.19  2.45  0.83  0.87  0.87
2  1.13  0.79  0.68  5.43  5.43
3  1.18  1.09  1.04 NA     1.04
4  1.11  1.1   4.24 NA     4.24
5  1.16  1.13 NA    NA     1.13
6  1.18 NA    NA    NA     1.18
7  1.44 NA     9.17 NA     9.17