Sadek Almuganahi Sadek Almuganahi - 9 months ago 33
R Question

Find out how many times each number appears in a database using r

So I have a database which has an id and several fields such as:

ID Name1 Name2 Name3
1 2 3 6
1 1 2 7
1 3 4 7
1 2 1 3
2 5 2 1
2 8 3 7
2 6 2 3
5 2 1 4
5 5 8 6
5 3 2 4


I would like to know what is the best way to get an overall count on how many times each number appears in the database regardless of column. and how many times does each number appear within a unique id.

Answer Source

Using the function gather from the tidyr package to change the data frame to long format and then using function ddply from the plyr package to summarize based on ID and Value:

df <- data.frame(ID = c(rep(1, 4), rep(2,3), rep(5,3)),
             Name1 = c(2,1,3,2,5,8,6,2,5,3),
             Name2 = c(3,2,4,1,2,3,2,1,8,2),
             Name3 = c(6,7,7,3,1,7,3,4,6,4))

library(tidyr)
library(plyr)
df<-gather(df, key="ID")
ddply(df, .(value), summarize, count=length(value)) #overall count
ddply(df, .(ID,value), summarize, count=length(value)) #count by ID