I have the following dataset and I want to transform it into a transactional format.
sample_data<-data.frame(id=c(452,125,288,496,785,328,712,647),a=c(5,8,7,9,0,0,4,0),b=c(0,7,8,9,3,6,0,0),c=c(7,8,9,0,0,0,0,7),d=c(8,7,5,0,0,0,0,7))
sample_data
sample_data
id a b c d
452 5 0 7 8
125 8 7 8 7
288 7 8 9 5
496 9 9 0 0
785 0 3 0 0
328 0 6 0 0
712 4 0 0 0
647 0 0 7 7
The desired output is as follows:
id item
452 a c d
125 a b c d
288 a b c d
496 a b
785 b
328 b
712 a
647 c d
How can I achieve this in R?
Is there an easier way of doing this?
We can use apply to loop over the rows, get the names of the data where the value of numeric columns are not 0, and paste them together, then cbind with the first column of the data
cbind(sample_data[1], item = apply(sample_data[-1], 1,
function(x) paste(names(x)[x != 0], collapse = ' ')))
-output
# id item
#1 452 a c d
#2 125 a b c d
#3 288 a b c d
#4 496 a b
#5 785 b
#6 328 b
#7 712 a
#8 647 c d