Get the first non-NA element in a row

user_2489956 0
Viewed 460

I have a dataframe where every row should contains mostly "No response" values (-1). I'd like to get the first value of every row that is not -1, preferably using something tidy-friendly.

# A tibble: 3,222 x 10
   tracc1 tracc2 tracc3 tracc4 tracc5 tracc6 tracc7 tracc8 tracc9 tracc10
   <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  
 1 1      -1     -1     -1     -1     -1     7      -1     -1     -1     
 2 1      -1     -1     -1     -1     -1     -1     -1     -1     -1     
 3 1      -1     -1     -1     -1     -1     -1     -1     -1     -1     
 4 1      -1     -1     -1     -1     -1     -1     -1     -1     -1     
 5 1      -1     -1     -1     -1     -1     -1     -1     -1     -1     
 6 1      -1     -1     -1     -1     -1     -1     -1     -1     -1     
 7 1      -1     -1     -1     -1     -1     -1     -1     -1     -1     
 8 1      -1     -1     -1     -1     -1     -1     -1     -1     -1     
 9 -1     -1     3      -1     -1     -1     -1     -1     -1     -1     
10 1      -1     -1     -1     -1     -1     -1     -1     -1     -1     
# ...

I was able to use dpylr::unite to combine all the columns, but problems arise when a single row has multiple valid responses. In the example below, row one should yield 1 rather than 17.

> df %>%
    mutate_at(vars(starts_with("tracc")),
              function(t) {if_else(t < 0,"",t)}) %>%
    unite("tracc",starts_with("tracc"),sep = "", na.rm = TRUE)
# A tibble: 3,222 x 1
   tracc
   <chr>
 1 17
 2 1
 3 1
 4 1
 5 1
 6 1
 7 1
 8 1
 9 3
10 1
# ...
dplyr r
4 Answers

Try this simple code:

apply(df, 1, function(x) x[x != -1][1])

It parallelly applies to each row.

user_101078640

One dplyr option could be:

df %>%
 mutate_all(~ replace(., . == "-1", NA_integer_)) %>%
 transmute(tracc = coalesce(!!!.))

   tracc
1      1
2      1
3      1
4      1
5      1
6      1
7      1
8      1
9      3
10     1

An option since dplyr 1.0.0 could be:

df %>%
 transmute(tracc = Reduce(coalesce, across(everything(), ~ replace(., . == "-1", NA_integer_))))
user_59645570

Another way to do it with dplyr

library(dplyr)
df %>% mutate(row_num = row_number()) %>% # add column with row number
       pivot_longer(-row_num,names_to='tracc') %>% # pivot to get three columns
       mutate(tracc=as.numeric(str_replace(tracc,'tracc',''))) %>% # convert tracc to numeric
       filter(value != -1) %>% # keep only -1 values
       arrange(tracc) %>% # sort by tracc
       group_by(row_num)  %>% 
       filter(row_number()==1) # keep first -1 value by row_num
# A tibble: 10 x 3
# Groups:   row_num [10]
#   row_num tracc value
#     <int> <dbl> <int>
# 1       1     1     1
# 2       2     1     1
# 3       3     1     1
# 4       4     1     1
# 5       5     1     1
# 6       6     1     1
# 7       7     1     1
# 8       8     1     1
# 9      10     1     1
#10       9     3     3
user_33011130

We can use vectorized option with row/column indexing

df[cbind(seq_len(nrow(df)), max.col(df != -1, 'first'))]
user_37322710
Related