counting the frequency of value in a column

Question

I have data with different Projects that have Pathway A-E and Likelihood either "unlikely", "possible" or "likely". The data looks like this (but with many more projects - so more rows):

   Project   Pathway   Likelihood
1    Red        A       unlikely
2    Red        B       possible
3    Red        C       likely
4    Red        D       possible
5    Red        E       possible
6    Blue       A       possible
7    Blue       B       unlikely
8    Blue       C       possible
9    Blue       D       possible
10   Blue       E       unlikely
11   Green      A       unlikely
12   Green      B       possible
13   Green      C       likely
14   Green      D       possible
15   Green      E       possible
16   Yellow     A       unlikely
17   Yellow     B       unlikely
18   Yellow     C       possible
19   Yellow     D       possible
20   Yellow     E       likely

For each Pathway, I want to count the number of "unlikely", "likely" & "possible" from the Likelihood column, so that I can make a dataframe with each Pathway as a row and unlikely, possible and likely as columns.

Does anyone know the best way to do this?

Thanks in advance!

This is a very common data manipulation that has been asked and answered many times before. Can you share a bit more about what you've tried and why it hasn't worked? — Justin, Oct 01 '13 at 01:06

score 6 · Accepted Answer · answered Oct 01 '13 at 01:23

Assuming your data is called dat:

intm <- aggregate(Likelihood ~ Pathway, data=dat, FUN=table)
data.frame(Pathway=intm$Pathway, intm$Likelihood)

#  Pathway likely possible unlikely
#1       A      0        1        3
#2       B      0        2        2
#3       C      2        2        0
#4       D      0        4        0
#5       E      1        2        1

Or all in one line:

do.call(data.frame, aggregate(Likelihood ~ Pathway, data=dat, FUN=table))

#  Pathway Likelihood.likely Likelihood.possible Likelihood.unlikely
#1       A                 0                   1                   3
#2       B                 0                   2                   2
#3       C                 2                   2                   0
#4       D                 0                   4                   0
#5       E                 1                   2                   1

There are tons of ways of doing this. At what point should we just say: Do some searching! I'm one of the down-voters, but if I answered it I would have used `xtabs`. Better labeling in this case. — IRTFM, Oct 01 '13 at 01:24
And if you are going to use table why not: `with(dat, table(table(Pathway, Likelihood) )` — IRTFM, Oct 01 '13 at 01:32

counting the frequency of value in a column

1 Answers1

Linked