data.table - Summarize a column with filters based on same column

Question

The data table structure is this:

DT <- data.table(category = c("categ A","categ A","categ A","categ B","categ B"),
                 type = c("type 1","type 1","type 3","type 2", "type 1"))
     category   type
1:  categ A     type 1
2:  categ A     type 1
3:  categ A     type 3
4:  categ B     type 2
5:  categ B     type 1

The expected output is this:

category    type1   type2   type3
categ A     2           0           1
categ B     0           1           1

Basically I need to summarize by category and create the three columns specifying a filter for each - I need to be able to specify the column names manually, not generate the based on the data.

My (hopeful) try was this:

DT_new <- DT[, list(type1=.N[type=="type 1"],
                    type2=.N[type=="type 2"],
                    type3=.N[type=="type 3"]),
               by='Date')

This produced some strange output. I know I can create three separate data tables and then merge them together, but maybe there is another way?

Where is the 'Date' column in the original dataset – akrun Oct 20 '15 at 13:44 — akrun, Oct 20 '15 at 13:44

akrun · Accepted Answer · 2015-10-20T13:57:04.850

2

We can use dcast

dcast(DT, category~type, value.var='type', length)

If we need to specify the column names manually,

DT[, list(type1= sum(type=='type 1'),
          type2= sum(type=='type 2'),
          type3 = sum(type=='type 3')), by = category]

edited Oct 20 '15 at 13:57

answered Oct 20 '15 at 13:14

akrun

874,273
37
540
662

I am looking at ` dcast.data.table` now, but I'd like to be able to specify the column names myself - I'll edit my post to clearly state that. I like the answer though, I might end up using this. – BogdanC Oct 20 '15 at 13:42
@BogdanC In the `v1.9.6`, you dont need to specify `dcast.data.table`, just `dcast` is enough. – akrun Oct 20 '15 at 13:43
@BogdanC You need something like `DT[, list(type1= sum(type=='type 1'), type2= sum(type=='type 2')), by = category]` – akrun Oct 20 '15 at 13:55

score 0 · Answer 2 · answered Oct 20 '15 at 13:30

0

Maybe it is good enough.

as.data.frame.matrix(with(DT,table(category, type)))
        type 1 type 2 type 3
categ A      2      0      1
categ B      1      1      0

answered Oct 20 '15 at 13:30

Shenglin Chen

4,504
11
11

data.table - Summarize a column with filters based on same column

2 Answers2