Is there a function for creating a index based presence of a value in columns?

Question

The following is my dataframe and on that I just wanted to add a new column with values present in max BSTN(n)

CARDID BSTN BSTN2 BSTN3 BSTN4 BSTN5

 5786  150     0     0     0     0

 9737  150     0     0     0     0

45924  150   151   154     0     0

66405  150     0     0     0     0

91720  150     0     0  4233     0

96172  150     0     0     0  3000  

I know that for the first row the result of the new column would be 1 as only BSTN 1 has a value. But I dont know how to perform that, can someone guide me on this?

Answer 1

In R programming, you can simply use max.col() function having the argument ties.method = "last" to get the column number of last non-zero value.

df$NTF <- max.col(df[-1] != 0, ties.method = "last")

df

#  CARDID BSTN BSTN2 BSTN3 BSTN4 BSTN5 NTF

#1   5786  150     0     0     0     0   1

#2   9737  150     0     0     0     0   1

#3  45924  150   151   154     0     0   3

#4  66405  150     0     0     0     0   1

#5  91720  150     0     0  4233     0   4

#6  96172  150     0     0     0  3000   5

But if you are having other columns before and/or after "BSTN" columns, then you can there use the grep() function to select only those columns that you want to select.

cols <- grep('^BSTN', names(df))

df$NTF <- max.col(df[cols] != 0, ties.method = "last")

 And you can also use apply row-wise as follows :

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

#[1] 1 1 3 1 4 5

data

df <- structure(list(CARDID = c(5786L, 9737L, 45924L, 66405L, 91720L, 

96172L), BSTN = c(150L, 150L, 150L, 150L, 150L, 150L), BSTN2 = c(0L, 

0L, 151L, 0L, 0L, 0L), BSTN3 = c(0L, 0L, 154L, 0L, 0L, 0L), BSTN4 = c(0L, 

0L, 0L, 0L, 4233L, 0L), BSTN5 = c(0L, 0L, 0L, 0L, 0L, 3000L)), 

class = "data.frame", row.names = c(NA, -6L))