2

A is a table which stores data:

A <- data.table(Type = c(1,2,1,1,2), value1 = 1:5)

B is a reference table:

B <- data.table(Type = c(1,2), value2 = c(20,40))

For each row in A, I want to add the value corresponding to its type:

A[B, on = .(Type)]

This is the result I get:

   Type value1 value2
1:  1      1     20
2:  1      3     20
3:  1      4     20
4:  2      2     40
5:  2      5     40

The result is what I expect except the order is changed. Is there any way to do this while keeping the original order of A?

Catiger3331
  • 611
  • 1
  • 6
  • 18

1 Answers1

1

We need to do the assignment := to create the column in the same order

A[B, value2 := value2, on = .(Type)]
A
#   Type value1 value2
#1:    1      1     20
#2:    2      2     40
#3:    1      3     20
#4:    1      4     20
#5:    2      5     40
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Hi I usually do joins with dplyr but I'm trying to learn how to do this with data tables. Why is it that you need to assignment to create in the same order and how does this work if my second table has more columns than the first? – Nick Mar 21 '22 at 12:38
  • @Nick It doesn't really matter if you have multiple columns in second data or not. Here, we are doing a left join. `on` the 'Type' column and assign (`:=`) the column 'value2' to create the column (`value2` is the name of the column created in 'A'). Using `:=`, you create the column in place in A – akrun Mar 21 '22 at 14:42
  • first thanks for answering but I guess where I am getting confused is that in my situation I want all the columns in the right table to be part of the left table. I have achieved this with my left join but the problem that I have is that the order of the rows have changed. You say here in this answer to make := an assignment to keep the order but do I need to do that for all my columns in table B to be part of table A(the left table) in order to keep the columns from table B and to have the rows in the same order. – Nick Mar 22 '22 at 10:39
  • @Nick it is not clear. You could only subset the colums that needs to be created as well as the `on` column with second data. The column will be updated in `A` and it will be the same row order of A – akrun Mar 22 '22 at 14:46