Using table function

Question

I'm not really sure how to explain this properly, so please bear with me:

I would like to create a table from a dataframe with 3 variables, such that the table looks something like this:

        dat <- read.table(text="
   Collection Item_Name Count
1         123    Item_1    12
2         123    Item_3     3
3         123    Item_4     4
4         123    Item_7    18
5         124    Item_2     9
6         124    Item_3     1
7         124    Item_7     5
8         125    Item_3    13
9         125    Item_4     6
10        125    Item_5    11
11        125    Item_6    10
12        125    Item_7     2
        ", header=TRUE)

Convert this to:

Collection Item_1 Item_2 Item_3 Item_4 Item_5 Item_6 Item_7
       123     12      0      3      4      0      0     18
       124      0      9      1      0      0      0      5
       125      0      0     13      6     11     10      2

I think I should be able to do this with the table function, but using something like

table(data$Collection_No, data$Item_Name)

only gives me the frequency of occurrences of a particular Item_Name in Collection_No. What function should I use so that instead of frequency it shows Item_Counts instead?

Thanks, sorry for the dumb question...

Please provide the data in copyable form so that others can try it. If the input data frame is called `data` then paste the output of the R code `dput(data)` into the question. — G. Grothendieck, Nov 23 '15 at 23:44
See `?xtabs` or `?reshape` - `xtabs(Count ~ Collection + Item_Name, data=dat)` for example. This has been asked here countless times before. — thelatemail, Nov 23 '15 at 23:55

score 0 · Answer 1 · answered Nov 24 '15 at 00:02

The reshape2 package is your friend here and has a function dcast which transforms data from "long" format to "wide" format. This should do the trick for you:

dcast(dat,Collection~Item_Name,value.var = "Count", fun.aggregate = sum)

The formula Collection~Item_Name defines the rows and columns in the resulting data frame and value.var is the column to put into the cells of the resulting table. fun.aggragate tells R what operation to perform on the value.var column.

Here is the output:

      Collection Item_1 Item_2 Item_3 Item_4 Item_5 Item_6 Item_7
1        123     12      0      3      4      0      0     18
2        124      0      9      1      0      0      0      5
3        125      0      0     13      6     11     10      2

Using table function

1 Answers1