CODEWITHSUNDEEP
r
NewUsr_stat
NewUsr_stat

Reputation: 2583

occurrence counting in a data.frame

I have a data frame like this:

 G1  G2  G3
  a   b   f
  b   c   a
  c   d   b

and a reference list: L: a b c d e f

I would like to have the following output:

a: 2, G1,G3   (that is "a" occurs two times in G1 and in G3 column)
b: 3, G1,G2,G3
c: 2, G1,G2

and so on.. Can anyone help me? Sorry to bother you but I'm new in R Thanks in advance.

Eleonora

Upvotes: 1

Views: 5800

Answers (3)

Tyler Rinker
Tyler Rinker

Reputation: 109874

Both methods here work well already. Just thought I'd give a little different method that I used for a similar problem. 

#Vinux's data:
A=data.frame( G1=c("a","b","c"),  G2=c("b","c","d"),  G3=c("f","a","b"))

B <- data.frame(x=rep(colnames(A), sapply(A, length)),        #means of reshaping data
    y=c(apply(A, 2, as.character)))
C <- split(B$x, B$y)                                          #column names by value
D <- data.frame(letter=names(C), occurance=sapply(C, length)) #get occurrences
D$column <- sapply(C, as.character)                           #force vector of vectors
D[, c(1, 3, 2), ]                                             #order it

Which yields:

  letter     column occurance
a      a     G1, G3         2
b      b G1, G2, G3         3
c      c     G1, G2         2
d      d         G2         1
f      f         G3         1

Upvotes: 1

digEmAll
digEmAll

Reputation: 57210

You could do something like this:

r <- reshape(d,varying=list(colnames(d)),direction='long',
             v.names='value',times=colnames(d),timevar='colname')

res <- by(r,r$value,FUN=function(x){unique(x$colname)})



> res

r$value: a
[1] "G1" "G3"
------------------------------------------------------------ 
r$value: b
[1] "G1" "G2" "G3"
------------------------------------------------------------ 
r$value: c
[1] "G1" "G2"
------------------------------------------------------------ 
r$value: d
[1] "G2"
------------------------------------------------------------ 
r$value: f
[1] "G3"

Basically res is a list containing for each letter, the vector of column names where the letter appears e.g.:

res$a : 'G1', 'G3'
res$b : 'G1', 'G2', 'G3'
...

If you want only the number of occurrences, you can do something like this:

> lapply(res,length)
$a
[1] 2

$b
[1] 3

$c
[1] 2

$d
[1] 1

$f
[1] 1

EDIT :
To write res on a file, one way could be the following:

# prepare the table (data.frame) to be written
dF <- do.call(rbind.data.frame, lapply(res,function(x){list(Occur=length(x),Columns=paste(x,collapse=' '))}))
dF <- cbind(Letter=row.names(dF),dF)
# write the table to file
write.table(dF,row.names=FALSE,sep=',',file='myfile.csv')

File text:

"Letter","Occur","Columns"
"a",2,"G1 G3"
"b",3,"G1 G2 G3"
"c",2,"G1 G2"
"d",1,"G2"
"f",1,"G3"

Upvotes: 3

vinux
vinux

Reputation: 965

I tried this in another way. 

A=data.frame( G1=c("a","b","c"),  G2=c("b","c","d"),  G3=c("f","a","b"))

B= melt(as.matrix(A))
B$X2 =as.character(B$X2)
B1=table(as.matrix(A))
D=aggregate(X2~value , B, FUN=c)
D$cnt=B1[D$value]
D

Output is 

  value         X2 cnt
1     a     G1, G3   2
2     b G1, G2, G3   3
3     c     G1, G2   2
4     d         G2   1
5     f         G3   1

Upvotes: 1

Related Questions