Nested groupings with data.table

Question

In the `data.table` below, I have information on the composition of teams participating to projects. The variable `id` tells the team id while the variable `event` gives the project number. The variable `freqrel` describes the composition of the teams (you can see that freqrel adds up to 1 within every team). ``` structure(list(id = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 4L, 4L, 4L, 5L, 5L, 5L), event = c("127b", "127b", "127b", "127b", "127b", "127b", "127b", "127b", "127b", "125t", "125t", "125t", "125t", "125t", "125t"), membr = c("engineer", "mathematician", "physicist", "mathematician", "physicist", "surgeon", "dentist", "mathematician", "programmer", "physicist", "sociologist", "surgeon", "musician", "sociologist", "surgeon"), freqrel = c(0.4, 0.4, 0.2, 0.166666666666667, 0.5, 0.333333333333333, 0.333333333333333, 0.5, 0.166666666666667, 0.75, 0.125, 0.125, 0.444444444444444, 0.444444444444444, 0.111111111111111)), .Names = c("id", "event", "membr", "freqrel"), row.names = c(NA,…

Accepted Answer

Here's an easy way:

```
setkey(dt, id, membr)
ans <- dt[, .SD[CJ(unique(id), unique(membr))], by=list(event)]
```

Then, you can just replace the `NA` with 0's as follows:

```
ans[is.na(freqrel), freqrel := 0.0]
```

Some explanation: Your problem boils down to this - for every `event`, you want all possible combinations of `id, membr` so that you can then perform a join on this all-combination within that grouping using `.SD`.

So, first we group by `event`, and within that, we first get all combinations of `id, membr` with the help of `CJ` (which will have a key set to all columns by default). However, to perform a join we need to have the key set for `.SD`. Therefore, we set the `key` for `dt` to `id, membr` upfront. Thus, we perform a join within each group and that gives you the intended result. Hope this helps a bit.

Nested groupings with data.table

Solution

Problem