R: Find position of last value meeting a certain criteria for every row of a dataframe

Question

I have a dataframe that looks something like this:

M0 M1  M2  M3  M4  M5  M6  M7  M8
1   1   0   0   0   NA  NA  NA  NA
2   2   2   2   2   2   2   0   0
2   2   2   2   2   0   NA  NA  NA
2   2   2   2   2   0   0   0   0

What I would like to know is the position (column) of the last value larger than 0 in each row.

The desired output would be a vector containing these indexed positions

here: (M1, M6, M4, M4)

could you give the output desired ? I am not sure to understand what you mean. — denis, Nov 21 '17 at 15:01
I added the outcome I would envision for the provided example — Lukas, Nov 21 '17 at 15:06
Related: [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, Nov 21 '17 at 15:29

Pierre Lapointe · Answer 1 · 2017-11-21T15:28:30.230

2

Here's what you could do using apply. Basically, the function inside the apply looks for data>0 rowwise, finds the last one using tail(x,1) and finds the corresponding column names.

df <- read.table(text="M0 M1  M2  M3  M4  M5  M6  M7  M8
1   1   0   0   0   NA  NA  NA  NA
                 2   2   2   2   2   2   2   0   0
                 2   2   2   2   2   0   NA  NA  NA
                 2   2   2   2   2   0   0   0   0",header=TRUE, stringsAsFactors=FALSE)

names(df)[apply(df, 1, function(x) tail(which(x > 0), 1))] 

[1] "M1" "M6" "M4" "M4"

edited Nov 21 '17 at 15:28

answered Nov 21 '17 at 15:16

Pierre Lapointe

16,017
2
43
56

Thank you. This works, but returns a NA for row that only consist of zero's. Since Henrik's solution simply returns the last column then, it fits better for my purposes. But I didn't specify this in my questions, so no way for you to know. Thanks! – Lukas Nov 21 '17 at 15:36

score 2 · Accepted Answer · answered Nov 21 '17 at 15:27

2

You may use max.col

names(df)[max.col(!is.na(df) & df > 0, ties.method = "last")]
# [1] "M1" "M6" "M4" "M4"

answered Nov 21 '17 at 15:27

Henrik

65,555
14
143
159

Uwe · Answer 3 · 2017-11-21T15:20:56.027

One solution is to reshape from wide to long format and to pick the rightmost, i.e., last column > 0 by row id:

library(data.table)
melt(setDT(DF)[, rn := .I], id.vars = "rn", na.rm = TRUE)[
  value > 0, last(variable), by = rn]

      rn     V1
1:     1     M1
2:     2     M6
3:     3     M4
4:     4     M4

If you only want the vector:

melt(setDT(DF)[, rn := .I], id.vars = "rn", na.rm = TRUE)[
  value > 0, last(variable), by = rn][, V1]

[1] M1 M6 M4 M4
Levels: M0 M1 M2 M3 M4 M5 M6 M7 M8

R: Find position of last value meeting a certain criteria for every row of a dataframe

3 Answers3