Replace NA in rows using coalesce-like function

user_7999936 0
Viewed 230

I'm trying to see if I can use dplyr's coalesce or something like it to combine rows in the same way coalesce combines columns.

I found some other similar posts but none that answer my question.

Here's a toy sample:

df <- read.table(sep = "|", header = F, stringsAsFactors = F, text = 
"a|NA|NA|d|NA
NA|b|NA|NA|e
a|NA|c|NA|e
1|2|3|4|5
1|2|3|4|5
1|2|3|4|5
1|2|3|4|5")

> df
    V1   V2   V3   V4   V5
1    a <NA> <NA>    d <NA>
2 <NA>    b <NA> <NA>    e
3    a <NA>    c <NA>    e
4    1    2    3    4    5
5    1    2    3    4    5
6    1    2    3    4    5
7    1    2    3    4    5

I would like to combine rows 1, 2 and 3 into a single row that I can use as headers. The output should look like this:

  V1 V2 V3 V4 V5
1  a  b  c  d  e
2  1  2  3  4  5
3  1  2  3  4  5
4  1  2  3  4  5
5  1  2  3  4  5

I was hoping something like this would work:

df %>% 
  rowwise() %>% 
  coalesce(1,2,3) %>% 
  slice(-c(1:2))

This worked but it feels clunky:

df %>% 
  slice(1:3) %>% 
  fill(everything(), .direction = "down") %>%
  slice(3) %>% 
  bind_rows(df %>% 
              slice(-c(1:3)))

I'd like something more straightforward and elegant.

dplyr r
5 Answers

Something like this maybe?

df %>% slice_tail(n = 4) %>% 
  set_names(
    df %>% slice_head(n = 3) %>% map_chr(lift_dv(coalesce))
  )
#>   a b c d e
#> 1 1 2 3 4 5
#> 2 1 2 3 4 5
#> 3 1 2 3 4 5
#> 4 1 2 3 4 5

Created on 2021-07-14 by the reprex package (v1.0.0)

Explanation

The first three rows should be used to figure out the column names. We can extract them with slice_head().

df %>% slice_head(n = 3)
#>     V1   V2   V3   V4   V5
#> 1    a <NA> <NA>    d <NA>
#> 2 <NA>    b <NA> <NA>    e
#> 3    a <NA>    c <NA>    e

Now we need to figure out a way of applying coalesce on each of the columns in the slice. We could use a summarise and across, but since we’re not looking for a dataframe as the output, i’d prefer to use map. If we just map an identity function to the slice we can see how map works with a datframe as the input.

df %>% slice_head(n = 3) %>% map(~.)
#> $V1
#> [1] "a" NA  "a"
#> 
#> $V2
#> [1] NA  "b" NA 
#> 
#> $V3
#> [1] NA  NA  "c"
#> 
#> $V4
#> [1] "d" NA  NA 
#> 
#> $V5
#> [1] NA  "e" "e"

As you can see, a dataframe is actually just a kind of list with vectors of equal length. The map functions are therefore very useful if we want to apply something to every single column in a dataframe.

Now we need to figure out how we can use coalesce on these vectors. If we manually test the first case ($V1), we see that there is a minor issue.

coalesce(c("a", NA, "a"))
#> [1] "a" NA  "a"

Nothing happens?! This is because coalesce takes its arguments as dynamic dots

coalesce("a", NA, "a")
#> [1] "a"

This means we need to figure out a way of unpacking the vector. For this we can either use !!! to unpack the vectors (you can read about it here) or alternatively use the composition helpers from purrr which can convert functions to take list, dots, or vectors. Coalesce takes dots, we want it to take vectors, which means that we should use lift_dv.

x <- c("a", NA, "a")
coalesce(!!!x)
#> [1] "a"
lift_dv(coalesce)(x)
#> [1] "a"
user_77787870

This doesn't look elegant but maybe an alternative -

library(dplyr)

df %>% 
  slice(1:3) %>% 
  summarise(across(.fns = ~na.omit(.)[1])) %>%
  bind_rows(df %>% slice(-(1:3)))

#  V1 V2 V3 V4 V5
#1  a  b  c  d  e
#2  1  2  3  4  5
#3  1  2  3  4  5
#4  1  2  3  4  5
#5  1  2  3  4  5
user_39629140

Here is another solution using built-in functions and the native pipe operator:

df[1:3, ] |> 
  sapply(function(x) na.omit(x)[1]) |> 
  rbind(df[-(1:3), ])

#   V1 V2 V3 V4 V5
# 1  a  b  c  d  e
# 4  1  2  3  4  5
# 5  1  2  3  4  5
# 6  1  2  3  4  5
# 7  1  2  3  4  5
user_108488980

You can also use the following solution:

library(dplyr)
library(janitor)

df %>%
  mutate(across(everything(), ~ replace(.x, is.na(.x), unique(.x[!is.na(.x)[1:3]])[1]))) %>%
  row_to_names(row_number = 1) %>%
  slice(-c(1:2))

  a b c d e
1 1 2 3 4 5
2 1 2 3 4 5
3 1 2 3 4 5
4 1 2 3 4 5
user_143145200

We could use invoke with coalescse

library(dplyr)
library(purrr)
library(janitor)
df %>% 
    summarise(across(everything(), ~ c(invoke(coalesce, .[1:3]), .[-(1:3)]))) %>% 
    row_to_names(1) %>%
    as_tibble %>%
    type.convert(as.is = TRUE)
# A tibble: 4 x 5
      a     b     c     d     e
  <int> <int> <int> <int> <int>
1     1     2     3     4     5
2     1     2     3     4     5
3     1     2     3     4     5
4     1     2     3     4     5

Or another option with fill

library(tidyr)
library(janitor)
df %>% 
    fill(everything(), .direction = 'downup') %>%
    row_to_names(1) %>%
    filter(across(everything(), ~ cur_column() != .))
  a b c d e
1 1 2 3 4 5
2 1 2 3 4 5
3 1 2 3 4 5
4 1 2 3 4 5
user_37322710
Related