Create New Column That Takes Column Name Based on Value

Question

I have a table with binary values that I want to summarize into one column. Here is an example:

df <- data.frame("User" = c("User A", "User B", "User C"), "quality 1" = c(0,0,1), "quality 2" = c(1,0,0), "quality 3" = c(0,1,0))

I'd like to run function that generates a dataframe like this:

summary <- data.frame("User" = c("User A", "User B", "User C"), "qualityNumber" = c("quality.2", "quality.3", "quality.1") )

For each row, the new variable ("qualityNumber") is assigned the column name from the original df that contains 1.

I have tried using dplyr and which(), but I can't figure it out.

My attempt:

summary = df %>%
mutate(
    qualityNumber= 
      colnames(df[which(2:4 == 1)])
    )

Duck · Accepted Answer · 2020-08-04T14:54:52.387

1

You can try adding to the same df:

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

    User quality.1 quality.2 quality.3 qualityNumber
1 User A         0         1         0     quality.2
2 User B         0         0         1     quality.3
3 User C         1         0         0     quality.1

Or selecting the columns after the task:

df[,c(1,5)]

    User qualityNumber
1 User A     quality.2
2 User B     quality.3
3 User C     quality.1

edited Aug 04 '20 at 14:54

answered Aug 04 '20 at 14:49

Duck

39,058
13
42
84

1

This is perfect; thank you! – joeOrtigara Aug 04 '20 at 15:10

score 0 · Answer 2 · answered Aug 04 '20 at 16:13

Another method:

df$qualityNumber <- names(df)[max.col(df == 1, ties.method = "first")]

The result:

> df
    User quality.1 quality.2 quality.3 qualityNumber
1 User A         0         1         0     quality.2
2 User B         0         0         1     quality.3
3 User C         1         0         0     quality.1

Create New Column That Takes Column Name Based on Value

2 Answers2