Kiwi Kiwi - 1 month ago 8
R Question

Need to delete cells which contains "0" and retaining other cells in R

My data frame contains "0" values in the cell. I need to call out the values(letters/non-zero`s) and their corresponding ticket number. Data frame(m) looks like: (Please note: column name is similar to its values)

a1 b1 c1 d1 e1 f1 g1 h1 i1 j1 k1 l1
TKT1 a1 b1 0 d1 0 0 0 h1 0 0 k1 0
TKT2 0 b1 0 0 e1 0 g1 h1 0 j1 k1 0
TKT3 a1 0 0 d1 e1 0 g1 h1 i1 0 k1 l1


**Code for generating dataset:**
#sample data
m <- matrix(sample(0:1, 12*3, replace=T), ncol=12)
colnames(m) <- c("a1", "b1", "c1", "d1", "e1", "f1", "g1", "h1", "i1", "j1", "k1", "l1")
rownames(m) <- c("TKT1","TKT2","TKT3")

#replacement
ones <- which(m==1, arr.ind=T)
m[ones]<-colnames(m)[ones[,2]]
m <- as.data.frame(m)


My desired output format is:

Tickets Values
TKT1 a1
TKT1 b1
TKT1 d1
TKT1 h1
TKT1 k1
TKT2 b1
TKT2 e1
TKT2 g1
TKT2 h1
TKT2 j1
TKT2 k1
TKT3 a1
TKT3 d1
TKT3 e1
TKT3 g1
TKT3 h1
TKT3 i1
TKT3 k1
TKT3 l1


One approach which came to my mind is to delete cells in my data frame if they contain 0 and then shift all the data leftwards. I am not sure how to proceed.

Thanks for your help!

Answer

Though there is no set.seed for random generation of your data.frame, the result will be slightly different:

library(dplyr); library(reshape2)
m %>% add_rownames('Tickets') %>% melt(id.var="Tickets") %>% filter(value!=0) %>% select(-variable) %>% arrange(Tickets)

This gives the expected result.