Identify the column name of the last occurrence of a value in R data frame

user_5012665 0
Viewed 175

I have a dataset like below with columns of 1s and 0s. I would like to add a final column that identifies the column name of the final occurrence of 0 per row.

have = data.frame(a = c(1,0,1,1,0,0,1,1,1,0),
                  b = c(1,0,1,1,1,0,1,1,0,0),
                  c = c(0,0,0,1,0,1,1,1,1,0),
                  d = c(1,0,1,1,0,0,0,1,0,1),
                  e = c(1,1,1,1,1,1,1,1,1,1))
> have
   a b c d e
1  1 1 0 1 1
2  0 0 0 0 1
3  1 1 0 1 1
4  1 1 1 1 1
5  0 1 0 0 1
6  0 0 1 0 1
7  1 1 1 0 1
8  1 1 1 1 1
9  1 0 1 0 1
10 0 0 0 1 1

I would like the output to look like this where the final column specifies the column name of the last occurring 0 and if one does not exist return NA.

> want
   a b c d e last_0
1  1 1 0 1 1      c
2  0 0 0 0 1      d
3  1 1 0 1 1      c
4  1 1 1 1 1   <NA>
5  0 1 0 0 1      d
6  0 0 1 0 1      d
7  1 1 1 0 1      d
8  1 1 1 1 1   <NA>
9  1 0 1 0 1      d
10 0 0 0 1 1      c

I've tried using max.col but it returns the last column name if a zero does not exist. Any other solutions? A dplyr solution is preferred.

> have$last_0 = names(have)[max.col(have == 0, ties.method = "last")]
> have
   a b c d e last_0
1  1 1 0 1 1      c
2  0 0 0 0 1      d
3  1 1 0 1 1      c
4  1 1 1 1 1      e
5  0 1 0 0 1      d
6  0 0 1 0 1      d
7  1 1 1 0 1      d
8  1 1 1 1 1      e
9  1 0 1 0 1      d
10 0 0 0 1 1      c
dplyr r
7 Answers

Here is an approach with purrr::pmap:

library(dplyr);library(purrr)
have %>% 
   mutate(want = pmap_chr(cur_data(), 
                          ~ tail(c(NA,names(which(c(...)==0))),1)))
   a b c d e want
1  1 1 0 1 1    c
2  0 0 0 0 1    d
3  1 1 0 1 1    c
4  1 1 1 1 1 <NA>
5  0 1 0 0 1    d
6  0 0 1 0 1    d
7  1 1 1 0 1    d
8  1 1 1 1 1 <NA>
9  1 0 1 0 1    d
10 0 0 0 1 1    c

purrr:pmap is a very useful function because it will work row wise on data and it comes in various flavors so you can control what returns. You can refer to the entire row of data with c(...).

If you wanted to apply the procedure to only a subset of columns, you might use dplyr::select:

have %>% 
    mutate(want = pmap_chr(cur_data() %>% select(a,b,c), 
                           ~ tail(c(NA,names(which(c(...)==0))),1)))
   a b c d e want
1  1 1 0 1 1    c
2  0 0 0 0 1    c
3  1 1 0 1 1    c
4  1 1 1 1 1 <NA>
5  0 1 0 0 1    c
6  0 0 1 0 1    b
7  1 1 1 0 1 <NA>
8  1 1 1 1 1 <NA>
9  1 0 1 0 1    b
10 0 0 0 1 1    c
user_130953260

We can use max.col and then replace those elements that doesn't have any 0 to NA

have$last_0 <- names(have)[(NA^!rowSums(have == 0)) * max.col(have == 0, 'last')]

-output

have
   a b c d e last_0
1  1 1 0 1 1      c
2  0 0 0 0 1      d
3  1 1 0 1 1      c
4  1 1 1 1 1   <NA>
5  0 1 0 0 1      d
6  0 0 1 0 1      d
7  1 1 1 0 1      d
8  1 1 1 1 1   <NA>
9  1 0 1 0 1      d
10 0 0 0 1 1      c
user_37322710

We can use pmax

have$last_0 <- names(have)[replace(do.call(pmax, data.frame((have == 0) * col(have))), rowSums(have) == ncol(have), NA)]

Another base R option using max.col

have$last_0 <- replace(names(have)[max.col(1 - have, "last")], rowSums(have) == ncol(have), NA)

such that

> have
   a b c d e last_0
1  1 1 0 1 1      c
2  0 0 0 0 1      d
3  1 1 0 1 1      c
4  1 1 1 1 1   <NA>
5  0 1 0 0 1      d
6  0 0 1 0 1      d
7  1 1 1 0 1      d
8  1 1 1 1 1   <NA>
9  1 0 1 0 1      d
10 0 0 0 1 1      c
user_121587570

Here is my approach:

library(dplyr)

have %>%
  purrr::pmap_dfr(\(...) tibble(...,
                                last_0 = which(c(...) == 0) %>%
                                  names %>%
                                  last))

Returns:

# A tibble: 10 x 6
       a     b     c     d     e last_0
   <dbl> <dbl> <dbl> <dbl> <dbl> <chr>
 1     1     1     0     1     1 c
 2     0     0     0     0     1 d
 3     1     1     0     1     1 c
 4     1     1     1     1     1 NA
 5     0     1     0     0     1 d
 6     0     0     1     0     1 d
 7     1     1     1     0     1 d
 8     1     1     1     1     1 NA
 9     1     0     1     0     1 d
10     0     0     0     1     1 c
user_70100560

You can also use the following solution, however it may sound a bit verbose. As most of the possible "row_wise" operations had already been suggested, I thought I would try something I had never done before:

library(dplyr)
library(tidyr)

have %>% 
  mutate(id = row_number()) %>%
  pivot_longer(-id, names_to = "Last_0", values_to = "val") %>%
  group_by(id) %>% 
  arrange(desc(val), .by_group = TRUE) %>%
  slice_tail(n = 1) %>%
  mutate(Last_0 = ifelse(val == 1, "NA", Last_0)) %>%
  select(Last_0) %>%
  bind_cols(have) %>%
  relocate(Last_0, .after = last_col())

# A tibble: 10 x 7
# Groups:   id [10]
      id     a     b     c     d     e Last_0
   <int> <dbl> <dbl> <dbl> <dbl> <dbl> <chr> 
 1     1     1     1     0     1     1 c     
 2     2     0     0     0     0     1 d     
 3     3     1     1     0     1     1 c     
 4     4     1     1     1     1     1 NA    
 5     5     0     1     0     0     1 d     
 6     6     0     0     1     0     1 d     
 7     7     1     1     1     0     1 d     
 8     8     1     1     1     1     1 NA    
 9     9     1     0     1     0     1 d     
10    10     0     0     0     1     1 c
user_143145200

Though the strategy used by dear @akrun is fantabulous, but you can simply store the value calculated by you inside a temp variable and replace it like this. This can be done in dplyr only using cur_data()

library(dplyr)

have %>% mutate(last_0 = {xx <- names(.)[max.col(cur_data() == 0, ties.method = 'last')];
                          replace(xx, rowSums(cur_data() == 0) == 0, NA)})
#>    a b c d e last_0
#> 1  1 1 0 1 1      c
#> 2  0 0 0 0 1      d
#> 3  1 1 0 1 1      c
#> 4  1 1 1 1 1   <NA>
#> 5  0 1 0 0 1      d
#> 6  0 0 1 0 1      d
#> 7  1 1 1 0 1      d
#> 8  1 1 1 1 1   <NA>
#> 9  1 0 1 0 1      d
#> 10 0 0 0 1 1      c

Created on 2021-06-10 by the reprex package (v2.0.0)

user_28848590

A minimalist base R approach (that actually uses nothing but a grep-search for the condition):

data.frame( have,
     last_0=unlist(apply( have, 1, function(x){
                sol <- grep(0,x);
                if( length(sol > 0 )){
                  colnames(have)[sol[length(sol)]]
                }else{
                  NA } } )) )

   a b c d e last_0
1  1 1 0 1 1      c
2  0 0 0 0 1      d
3  1 1 0 1 1      c
4  1 1 1 1 1   <NA>
5  0 1 0 0 1      d
6  0 0 1 0 1      d
7  1 1 1 0 1      d
8  1 1 1 1 1   <NA>
9  1 0 1 0 1      d
10 0 0 0 1 1      c
user_94620950
Related