Get list of rownames as values after aggregating a dataframe

Question

Consider a data frame like this:

    type    Var1    Var2
1    A    hey      hey
2    A    hey      hey
3    B    hello    hey
4    B    hello    hey
5    C    hii      hello
6    C    hii      hello

I used melt/cast to get the below result

type    hey    hello    hii
A        4      0       0
B        2      2       0
C        0      2       2

Refer to my code below:

library(reshape)
melted <- melt(data, id='type')
count <- function(z) { 
  length(na.omit(z))
}
casted_data <- cast(melted, type~value, count)

Here the output generated is not what I want, I do not want to count/sum. Instead, I want to get a list of the original row names.

Below is my desired output:

type    hey    hello    hii
A       1,2      0       0
B       3,4    3,4       0
C        0     5,6      5,6

Where each value of hey, hello, hii is a list of unique row names from original data frame.

Can anyone help?

Answer 1

You can use reshape2 library:

library(reshape2)
data <- read.table(text="type    Var1    Var2
1    A    hey      hey
2    A    hey      hey
3    B    hello    hey
4    B    hello    hey
5    C    hii      hello
6    C    hii      hello")

data$id <- rownames(data)

melted <- melt(data, id=c('type','id'),measure.vars=c("Var1","Var2"))

fun <- function(z) paste(unique(z),collapse=",") 

dcast(melted,type~value,fun,value.var="id",fill="0")

#  type hey hello hii
#1    A 1,2    0   0
#2    B 3,4  3,4   0
#3    C   0  5,6  5,6