Get the last non zero column name of each row in R and create a separate column

Question

I have the following dataset

ID  Jan Feb March April May Jun
ABC 0   1    0      0   2   1
DEF 1   2    1      0   0   0
GHI 0   10   0      1   0   0
JKI 0   0    2      0   0   0
MNO 3   0    0      0   0   0

I would like to get something like this

ID  Jan Feb March   April   May Jun LastMonth
ABC 0   1    0      0       1   1   Jun
DEF 1   1    1      0       0   0   March
GHI 0   1    0      1       0   0   April
JKI 0   0    1      0       0   0   March
MNO 1   0    0      0       0   0   Jan

I had written the following piece of code to get the index of last nonzero element of each row but this itself is having too many issues.

  df$last <- apply(df, 1, function(x) tail(x[x > 0.00001],1))

I get the new column as

character(0)
character(0)
character(0)
2
character(0)

I am not sure why I am getting this column instead of the actual number

Answer 1

We can use max.col to get the column index of the max value of the logical matrix (df1[-1] != 0) for each row and use that to get the column names

df1$LastMonth <- names(df1)[-1][max.col(df1[-1] != 0, 'last')]
df1$LastMonth
#[1] "Jun"   "March" "April" "March" "Jan"  

data

df1 <- structure(list(ID = c("ABC", "DEF", "GHI", "JKI", "MNO"), Jan = c(0L, 
1L, 0L, 0L, 3L), Feb = c(1L, 2L, 10L, 0L, 0L), March = c(0L, 
1L, 0L, 2L, 0L), April = c(0L, 0L, 1L, 0L, 0L), May = c(2L, 0L, 
0L, 0L, 0L), Jun = c(1L, 0L, 0L, 0L, 0L)), .Names = c("ID", "Jan", 
"Feb", "March", "April", "May", "Jun"), class = "data.frame", 
row.names = c(NA, -5L))

Answer 2

You can do the following in one line:

# get name of most recent month
df$Last_Month <- apply(df[-1], 1, function(x) last(colnames(df[-1])[x==1]))