CODEWITHSUNDEEP
rdataframeappend
lolo
lolo

Reputation: 646

Append column names as a row value

suppose I have the following data frame. The idea is to be able to extract the names of the columns that contain a "1" as a value, and append them in a new data frame with the same "id".

db<-data.frame(id=c(1,2,3,4,5,6),col1=c(0,0,0,1,0,1),col2=c(0,0,1,1,0,0),col3=c(0,0,0,0,0,1),col4=c(1,0,0,0,0,0))

  id col1 col2 col3 col4
1  1    0    0    0    1
2  2    0    0    0    0
3  3    0    1    0    0
4  4    1    1    0    0
5  5    0    0    0    0
6  6    1    0    1    0

db2<-data.frame(id=c(1,2,3,4,5,6),cols=c("col4","-","col2","col1,col2","-","col1,col4"))

  id      cols
1  1      col4
2  2         -
3  3      col2
4  4 col1,col2
5  5         -
6  6 col1,col3

Upvotes: 3

Views: 584

Answers (2)

akrun
akrun

Reputation: 886938

We can loop through the rows with apply, subset the names where the column values are 1, paste it together to create the 'cols' column in the new data.frame 'db2'. If needed, change the blanks ("") to -

db2 <- data.frame(db[1], cols = apply(db[-1], 1, function(x)
       paste(names(x)[x==1], collapse=",")), stringsAsFactors = FALSE)
db2$cols[db2$cols== ""] <- "-"
db2
#  id      cols
#1  1      col4
#2  2         -
#3  3      col2
#4  4 col1,col2
#5  5         -
#6  6 col1,col3

Or we can vectorize it in base R

m1 <- (NA^!db[-1]) * col(db[-1])
m1[] <- colnames(m1)[m1]
v1 <- do.call(paste, c(as.data.frame(m1), sep=","))
db2 <- data.frame(db[1], cols =  gsub(",?NA,?", "", v1))

Upvotes: 1

IceCreamToucan
IceCreamToucan

Reputation: 28675

Another option using data.table

library(data.table)
setDT(db)

db[, .(.(names(row <- unlist(.SD))[row == 1])), by = id]

#    id        V1
# 1:  1      col4
# 2:  2          
# 3:  3      col2
# 4:  4 col1,col2
# 5:  5          
# 6:  6 col1,col3

Or if you want to add it as a new column

db[, cols := .(.(names(row <- unlist(.SD))[row == 1])), by = id]


#    id col1 col2 col3 col4      cols
# 1:  1    0    0    0    1      col4
# 2:  2    0    0    0    0          
# 3:  3    0    1    0    0      col2
# 4:  4    1    1    0    0 col1,col2
# 5:  5    0    0    0    0          
# 6:  6    1    0    1    0 col1,col3

note the code above creates a column of lists, not a character-vector column as in akrun's answer. If you want a character vector column, replace

.(.(names(row <- unlist(.SD))[row == 1]))

with

paste(names(row <- unlist(.SD))[row == 1], collapse = ',')

Upvotes: 1

Related Questions