Loop over the rows and bring the column name in R

Question

This is my df:

    JU_FINANCIAMENTO PRE CRE MERITO_AUTOMATICO
1                0   1   0                 0
2                0   0   1                 0
3                0   1   0                 0
4                1   0   0                 0
5                0   1   0                 0
6                0   0   0                 1

I need to create a new column with the name of the column where the number 1 is presented, as follows

    JU_FINANCIAMENTO PRE CRE MERITO_AUTOMATICO       NEW_COLUMN
1                0   1   0                 0                PRE
2                0   0   1                 0                CRE
3                0   1   1                 0                PRE
4                1   0   0                 0   JU_FINANCIAMENTO
5                0   1   0                 0                PRE
6                0   0   0                 1                 NA

What I tried so far:

for (i in names(df)) {
  df <- df %>%
    mutate(NEW_COLUMN = ifelse(i == 1, paste(i), "NAN"))
}

But i got this: Error: Can't bind data because some arguments have the same name

The last row for `MERITO_AUTOMATICO` is 1. So, shouldn't that column name be there for `NEW_COLUMN` — akrun, Jul 10 '20 at 21:28

score 2 · Accepted Answer · answered Jul 10 '20 at 21:23

We can use max.col from base R to create the column name column. It will get the index of column for each row. As we specify the ties.method as 'first', returns only the first max value

df$NEW_COLUMN <- names(df)[max.col(df, "first")]

In case there are only 0s, can change the value to NA

df$NEW_COLUMN[!rowSums(df[1:4] ==0)] <- NA

score 1 · Answer 2 · answered Jul 10 '20 at 21:33

You can try this base R solution:

#Data
df <- structure(list(JU_FINANCIAMENTO = c(0L, 0L, 0L, 1L, 0L, 0L), 
    PRE = c(1L, 0L, 1L, 0L, 1L, 0L), CRE = c(0L, 1L, 0L, 0L, 
    0L, 0L), MERITO_AUTOMATICO = c(0L, 0L, 0L, 0L, 0L, 1L)), row.names = c("1", 
"2", "3", "4", "5", "6"), class = "data.frame")

#Code
df$Index <- apply(df,1,function(x) names(x)[which(x==1)])

  JU_FINANCIAMENTO PRE CRE MERITO_AUTOMATICO             Index
1                0   1   0                 0               PRE
2                0   0   1                 0               CRE
3                0   1   0                 0               PRE
4                1   0   0                 0  JU_FINANCIAMENTO
5                0   1   0                 0               PRE
6                0   0   0                 1 MERITO_AUTOMATICO

Loop over the rows and bring the column name in R

2 Answers2