Select which column I receive the results from in data.table join

I would like to select the column I want to receive the results from by passing it in the join.

My data take the following format:

tab1 = data.table(z = c(1,2,3,4),
                  x = c("A", "B", "C", "D"),
                  y = c("E", "F", "G", "H"))

tab2 = data.table(z = c(1,2,3,4),
                  column = c("x", "x", "y", "y"))

I have tried the following solutions and they do not work:

# solutions that dont work
tab2[, val := tab1[tab2, on = .(z), get(i.column)]]
tab2[, val := tab1[tab2, on = .(z), column, with=F]]

I am trying to join tab2 to tab1 and return the results from either column x or y depending on which is evaluated from the column field.

The results should look like:

| z | column | val |
--------------------
| 1 | x      | A   |
| 2 | x      | B   |
| 3 | y      | G   |
| 4 | y      | H   |

Many thanks

CodePudding user response：

We need the by argument

tab2[tab1, val := get(column), on = .(z), by = .EACHI]

-output

> tab2
   z column val
1: 1      x   A
2: 2      x   B
3: 3      y   G
4: 4      y   H

CodePudding user response：

Here is a "SQL-style" answer which requires to reshape tab2 from wide to long format. Then, the columns names become data items.

tab2[, val := melt(tab1, id.var = "z")[tab2, on = .(z, variable = column), value]]
tab2

   z column val
1: 1      x   A
2: 2      x   B
3: 3      y   G
4: 4      y   H