How to find last column with value (for each row), with some rows with all NA as values?-CodePudding

I was having the same problem as How to find last column with value (for each row) in R?, except I have rows with no value (entire row of NA). The sample provided in said post did not have an entire row of NAs.

I was wondering how I should modify the following? I do not want to remove those rows with all NAs because they will be useful in later analysis.

df %>%
  rowwise %>% 
  mutate(m = {tmp <- c_across(starts_with('m'))
               tail(na.omit(tmp), 1)}) %>%
  ungroup

Thanks a lot in advance!

CodePudding user response：

If all the elements in the rows are empty, then a general solution would be to create condition to return NA for those rows

library(dplyr)
df %>%
  rowwise %>% 
  mutate(m = {tmp <- c_across(starts_with('m'))
               if(all(is.na(tmp))) NA_character_ else 
                   tail(na.omit(tmp), 1)}) %>%
  ungroup

-output

# A tibble: 4 × 5
     id m_1   m_2   m_3   m    
  <dbl> <chr> <chr> <chr> <chr>
1     1 a     e     i     i    
2     2 b     <NA>  <NA>  b    
3     3 <NA>  <NA>  <NA>  <NA> 
4     4 d     h     l     l

If the OP wants to return only the last single non-NA element, we may also add an index [1] to extract, which automatically return NA when there are no elements

df %>%
  rowwise %>% 
  mutate(m = {tmp <- c_across(starts_with('m'))
               tail(na.omit(tmp), 1)[1]}) %>%
  ungroup
# A tibble: 4 × 5
     id m_1   m_2   m_3   m    
  <dbl> <chr> <chr> <chr> <chr>
1     1 a     e     i     i    
2     2 b     <NA>  <NA>  b    
3     3 <NA>  <NA>  <NA>  <NA> 
4     4 d     h     l     l

data

df <- structure(list(id = c(1, 2, 3, 4), m_1 = c("a", "b", NA, "d"), 
    m_2 = c("e", NA, NA, "h"), m_3 = c("i", NA, NA, "l")), row.names = c(NA, 
-4L), class = "data.frame")

CodePudding user response：

Using data from @akrun (many thanks) we could do maybe this way:

library(dplyr)
library(tidyr)
library(stringr)

df %>% 
  mutate(across(starts_with("m"), ~case_when(!is.na(.) ~ cur_column()), .names = 'new_{col}')) %>%
  unite(New_Col, starts_with('new'), na.rm = TRUE, sep = ', ') %>% 
  mutate(New_Col = str_extract(New_Col, '\\b[^,] $'))

  id  m_1  m_2  m_3 New_Col
1  1    a    e    i     m_3
2  2    b <NA> <NA>     m_1
3  3 <NA> <NA> <NA>    <NA>
4  4    d    h    l     m_3

CodePudding user response：

library(tidyverse)
df <- data.frame(id = c(1, 2, 3, 4), m_1 = c("a", NA, "c", "d"), m_2 = c("e", NA, "g", "h"), m_3 = c("i", NA, NA, "l"))


df %>%
  rowwise() %>%
  mutate(
    nms = list(str_subset(names(df), "^m")),
    m = c_across(starts_with("m")) %>%
      {
        ifelse(test = all(is.na(.)),
          yes = NA,
          no = nms[which(. == tail(na.omit(.), 1))]
        )
      }
  ) %>%
  select(-nms)
#> # A tibble: 4 × 5
#> # Rowwise: 
#>      id m_1   m_2   m_3   m    
#>   <dbl> <chr> <chr> <chr> <chr>
#> 1     1 a     e     i     m_3  
#> 2     2 <NA>  <NA>  <NA>  <NA> 
#> 3     3 c     g     <NA>  m_2  
#> 4     4 d     h     l     m_3




# only the value no the column name
df %>%
  rowwise() %>%
  mutate(
    m = c_across(starts_with("m")) %>%
      {
        ifelse(test = all(is.na(.)),
          yes = NA,
          no = tail(na.omit(.), 1)
        )
      }
  )
#> # A tibble: 4 × 5
#> # Rowwise: 
#>      id m_1   m_2   m_3   m    
#>   <dbl> <chr> <chr> <chr> <chr>
#> 1     1 a     e     i     i    
#> 2     2 <NA>  <NA>  <NA>  <NA> 
#> 3     3 c     g     <NA>  g    
#> 4     4 d     h     l     l

^{Created on 2022-01-01 by the reprex package (v2.0.1)}