Kiwi - 4 months ago 23

R Question

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.