Joining and grouping two data tables

Question

Suppose I've got the following two data tables : ``` dt1 <- data.table(id=1:3,val1=c("a","a","b"),key="id") # id val1 # 1: 1 a # 2: 2 a # 3: 3 b dt2 <- data.table(id=c(1:3,1:2),val2=10:14,key="id") # id val2 # 1: 1 10 # 2: 1 13 # 3: 2 11 # 4: 2 14 # 5: 3 12 ``` Let's say that `dt1` is a list of people identified by their `id`, and `dt2` a list of observations on these same people, with the correspondent `id`. Now, I'd like to compute the mean of `val2` for each group of `val1`. I've understood that I can do it the following way : ``` dt1[dt2][,mean(val2),by=val1] # val1 V1 # 1: a 12 # 2: b 12 ``` But I've also read in the FAQ (section 1.14) that it's not efficient (at least for very large data tables). So, is there a better, more efficient way to do it ? EDIT : Another related question : I just saw that the following two lines will give the same result : ``` dt1[dt2][,mean(val2),by=val1] dt2[dt1][,mean(val2),by=val1] ``` Are they equivalent or is there a difference between the two ?

Accepted Answer

In your case, it's okay to do that. What the documentation explains, iiuc, is for example in this scenario (where you are not grouping/aggregating on ALL the columns):

```
dt1 <- data.table(id=1:3,val1=c("a","a","b"),key="id")
dt2 <- data.table(id=c(1:3,1:2),val2=10:14,key="id")

dt2[, val3 := rep(5:7, c(2,1,2))]
#    id val2 val3
# 1:  1   10    5
# 2:  1   13    5
# 3:  2   11    6
# 4:  2   14    7
# 5:  3   12    7
```

Now, suppose you want to get the mean of `val2` alone for every `val1`, then there's no point in joining all columns. In this case, you can do:

```
dt1[dt2, list(val1, val2)][, mean(val2), by=val1]
#    val1 V1
# 1:    a 12
# 2:    b 12
```

Instead of doing:

```
# gives same result but performs join on all columns
dt1[dt2][, mean(val2), by=val1]
```

For your second question, I guess it's essential to understand the difference `dt1[dt2]` and `dt2[dt1]`. For this, your data is not the best example. Suppose that,

```
dt1 <- data.table(id=c(1,4,5), val1=c("a","a","b"))
dt2 <- data.table(id=c(1,2,3,6,7,8), val2=c(6,5,3,4,2,1))

setkey(dt1, "id")
setkey(dt2, "id")
```

`dt1[dt2]` takes, for every `id` in `dt2` and fetches the corresponding value of all other columns in `dt1` to perform the join:

```
dt1[dt2]
#    id val1 val2
# 1:  1    a    6
# 2:  2   NA    5
# 3:  3   NA    3
# 4:  6   NA    4
# 5:  7   NA    2
# 6:  8   NA    1
```

`dt2[dt1]` takes for every `id` in `dt1` the corresponding value from other columns of `dt2` to perform the join:

```
dt2[dt1]
#    id val2 val1
# 1:  1    6    a
# 2:  4   NA    a
# 3:  5   NA    b
```

Note that, the values in `dt1[dt2]` contains only the id of `dt2`. Similarly `dt2[dt1]` contains only those in `dt1`. In your case, since the `id`s are exactly the same (ignorning the amount of times they occur), both the join would give you identical joins (except for the order of the columns), iiuc.

Just to make this part complete, if you want a "full" join, use `merge` with `all=TRUE`. The `merge.data.table` method is implemented.

```
merge(dt1, dt2, all = TRUE)

merge(dt1, dt2, all.x = TRUE) 
# is equivalent to
dt2[dt1]

merge(dt1, dt2, all.y = TRUE)
# is equivalent to
dt1[dt2]
```

Joining and grouping two data tables

Solution

Problem