4

I've recently started trying to do all of my code in the tidyverse. This has sometimes lead me to difficulties. Here is a simple task that I haven't been able to complete in the tidyverse: I need a column in a dataframe that returns the position index of the first non-na value from the left. Does anyone know how to achieve this in dplyr using mutate?

Here is the desired output.

data.frame(                           
"X1"=c(100,rep(NA,8)),
"X2"=c(NA,10,rep(NA,7)),
"X3"=c(NA,NA,1000,1000,rep(NA,5)),
"X4"=c(rep(NA,4),25,50,10,40,50),
"FirstNonNaPosition"=c(1,2,3,3,4,4,4,4,4)
)
curiositasisasinbutstillcuriou
  • 69
  • 9
  • Related canonical: [For each row return the column name of the largest value](https://stackoverflow.com/questions/17735859/for-each-row-return-the-column-name-of-the-largest-value) – Henrik May 14 '19 at 20:12

3 Answers3

4

An easier and efficient base R option would be max.col after replacing the NA elements to 0

max.col(replace(df2[1:4], is.na(df2[1:4]), 0), 'first')

Or even

df2$FirstNonNaPosition <- max.col(!is.na(df2[1:4]), "first")
df2$FirstNonNaPosition
#[1] 1 2 3 3 4 4 4 4 4

With tidyverse, a possible option is pmap

df2 %>% 
  mutate(FirstNonNaPosition = pmap_int(.[-5], ~ 
                          which.max(!is.na(c(...)))))

Or wrap the max.col

df2 %>% 
   mutate(FirstNonNaPosition = max.col(!is.na(.[-5]), 'first'))

data

df2 <- structure(list(X1 = c(100, NA, NA, NA, NA, NA, NA, NA, NA), X2 = c(NA, 
10, NA, NA, NA, NA, NA, NA, NA), X3 = c(NA, NA, 1000, 1000, NA, 
NA, NA, NA, NA), X4 = c(NA, NA, NA, NA, 25, 50, 10, 40, 50), 
    FirstNonNaPosition = c(1, 2, 3, 3, 4, 4, 4, 4, 4)), 
    class = "data.frame", row.names = c(NA, 
-9L))
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Right. I can get this done out of dplyr, but I need to incorporate it into a pipe. How would I incorporate this into a dplyr command? – curiositasisasinbutstillcuriou May 14 '19 at 19:50
  • @mcmahon.an I would use `pmap` i.e. `df2 %>% mutate(FirstNonNaPosition = pmap(.[-5], ~ which.max(!is.na(c(..))))` – akrun May 14 '19 at 19:52
  • 2
    @mcmahon.an Anything you can do outside of a "pipeline" to generate a vector you can also just put inside `mutate`. Here you can copy the code directly to do `df2 %>% mutate(newcol = max.col(!is.na(df2[1:4]), "first"))`. Optionally, replace `df2` with `.`. – IceCreamToucan May 14 '19 at 20:25
4

Also a base R possibility:

apply(df, 1, which.max)

[1] 1 2 3 3 4 4 4 4 4

The same with dplyr:

df %>%
 mutate(FirstNonNaPosition = apply(., 1, which.max))

A modification for a scenario mentioned by @Andrew:

apply(df, 1, function(x) which.max(!is.na(x)))

The same with dplyr:

df %>%
 mutate(FirstNonNaPosition = apply(., 1, function(x) which.max(!is.na(x))))
tmfmnk
  • 38,881
  • 4
  • 47
  • 67
3

You could use apply as well:

data.frame(                           
"X1"=c(100,rep(NA,8)),
"X2"=c(NA,10,rep(NA,7)),
"X3"=c(NA,NA,1000,1000,rep(NA,5)),
"X4"=c(rep(NA,4),25,50,10,40,50),
"FirstNonNaPosition"=c(1,2,3,3,4,4,4,4,4)
) %>%
  mutate(First_Non_NA_Pos = apply(., 1, function(x) which(!is.na(x))[1]))

   X1 X2   X3 X4 FirstNonNaPosition First_Non_NA_Pos
1 100 NA   NA NA                  1                1
2  NA 10   NA NA                  2                2
3  NA NA 1000 NA                  3                3
4  NA NA 1000 NA                  3                3
5  NA NA   NA 25                  4                4
6  NA NA   NA 50                  4                4
7  NA NA   NA 10                  4                4
8  NA NA   NA 40                  4                4
9  NA NA   NA 50                  4                4
Andrew
  • 5,028
  • 2
  • 11
  • 21