How can I count occurences of different integer values (in a particular column) for rows with different factor values(another column)?

Question

I have a dataframe which looks something like this:

My_Data = data.frame(name = rep(LETTERS[1:10],3), number = sample(0:3,30, replace=TRUE)

    name number
1     A      3
2     B      3
3     C      0
4     D      3
5     E      2
6     F      2
7     G      2
8     H      2
9     I      1
10    J      3
11    A      1
12    B      2
13    C      0
14    D      1
15    E      3
16    F      0
17    G      2
18    H      2
19    I      2
20    J      2
21    A      0
22    B      1
23    C      3
24    D      0
25    E      2
26    F      0
27    G      1
28    H      1
29    I      3
30    J      0

Now I would like to get a dataframe which has columns for each of the possible values in the number column and the count of the occurences for each of the number values with respect to each value in the name column

    name number_0  number_1   number_2  number_3
1     A      1         1        0         1
2     B      0         1        1         1
3     C      2         0        0         1
4     D      1         1        0         1
5     E      0         0        2         1
6     F      2         0        1         0
7     G      0         1        2         0
8     H      0         1        2         0
9     I      0         1        1         1  
10    J      1         0        1         1

How can I do that? Thanks!

Edit: I am not looking for a conversion to the wide format. I am looking for a way to count occurences for each of the possible values.

Possible duplicate of [Reshape three column data frame to matrix ("long" to "wide" format)](https://stackoverflow.com/questions/9617348/reshape-three-column-data-frame-to-matrix-long-to-wide-format) — jogo, Dec 12 '17 at 15:31
Not really. My problem here was to count occurences of each value rather than convert it from long to wide format — ag14, Dec 12 '17 at 15:33
But the answer of R18 to your question is structural the same as the answer https://stackoverflow.com/a/5890831/5414452 to the question https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format During reshaping to 'wide' you can do some operations with the data (e.g. sum or count). BTW: To make your data reproducable please use `set.seed()`. — jogo, Dec 12 '17 at 18:34

score 6 · Answer 1 · answered Dec 12 '17 at 13:08

6

You can also use xtabs() function.

xtabs(~My_Data$name + My_Data$number)

answered Dec 12 '17 at 13:08

R18

1,476
1
8
17

1

`as.data.frame.matrix(xtabs(~My_Data$name + My_Data$number))` – Adela Dec 12 '17 at 13:26
@Adela Yes, if you want to manipulate it them as a data.frame or as a matrix. – R18 Dec 12 '17 at 13:32

score 5 · Answer 2 · answered Dec 12 '17 at 13:06

We could get the count and then spread to 'wide' format

library(dplyr)
library(tidyr)
My_Data %>% 
     count(name, number) %>% 
     mutate(number = paste('number', number, sep='_')) %>% 
     spread(number, n, fill = 0)
# A tibble: 10 x 5
#    name number_0 number_1 number_2 number_3
# * <chr>    <dbl>    <dbl>    <dbl>    <dbl>
# 1     A        1        1        0        1
# 2     B        0        1        1        1
# 3     C        2        0        0        1
# 4     D        1        1        0        1
# 5     E        0        0        2        1
# 6     F        2        0        1        0
# 7     G        0        1        2        0
# 8     H        0        1        2        0
# 9     I        0        1        1        1
#10     J        1        0        1        1

score 4 · Accepted Answer · answered Dec 12 '17 at 13:28

4

Try also:

table(My_Data)

or, if you need a data.frame:

as.data.frame.matrix(table(My_Data))

answered Dec 12 '17 at 13:28

nicola

24,005
3
35
56

How can I count occurences of different integer values (in a particular column) for rows with different factor values(another column)?

3 Answers3