0

In R, I have two datatables With the same Field Names Key and Value. 

Table A
+----------+-------+
| Key      | Value |
+----------+-------+
| 20170910 | 10.11 |
+----------+-------+
| 20170911 | 12.21 |
+----------+-------+
| 20170915 | 15.51 |
+----------+-------+
| 20170916 | 33.01 |
+----------+-------+
| 20170912 | 82.2  |
+----------+-------+
| 20170913 | 11.22 |
+----------+-------+
| 20170914 | 2.33  |
+----------+-------+

Table B
+----------+-------+
| Key      | Value |
+----------+-------+
| 20170912 | 3.5   |
+----------+-------+
| 20170913 | 13.2  |
+----------+-------+
| 20170914 | 31.1  |
+----------+-------+
| 20170916 | 33.01 |
+----------+-------+
| 20170910 | 33.11 |
+----------+-------+
| 20170911 | 9.21  |
+----------+-------+
| 20170915 | 52.1  |
+----------+-------+

What I need to do is multiply Value elements of the Table A with the Value elements of the Table B if TableB.Key == TableA.Key. Is there a better way of doing this in datatables rather than checking iteratively

If done iteratively it'd be like this

for(i in 1 : dim(TableB)[1])
{
    mult <- TableA[Key == TableB[i]$Key]
    TableB[i, Value := mult * Value]
}
zx8754
  • 52,746
  • 12
  • 114
  • 209
BAdhi
  • 420
  • 7
  • 19
  • Merge then multiply? – zx8754 Oct 09 '17 at 12:41
  • 3
    What is `mult` supposed to be? It is a data.table in your attempt, e.g., all the column returned. Are you just trying to do `TableB[TableA, on = .(Key), Value := Value * i.Value]`? – David Arenburg Oct 09 '17 at 13:00
  • 3
    `TableA[TabelB, on = "Key", new_val := x.Value * i.Value][]` should do it – Jaap Oct 09 '17 at 13:00
  • @DavidArenburg `mult` is the `Value` of the `TableA` that matches to the `Key` of the `TableB` for each iteration. I know my method could not be the best since im new to R – BAdhi Oct 09 '17 at 14:12
  • @DavidArenburg Can you tell what each of the element in the bracket does? Because normally as far as I know the first element in the bracket filters elements row-wise and the second element selects the column and the third does some grouping. But in this case it seems to act differently – BAdhi Oct 09 '17 at 14:43
  • 1
    You can also pass data.tables to ith element in order to join with them. The is equivalent to `merge` in most cases. See the answers in the dupe question. – David Arenburg Oct 09 '17 at 14:53

0 Answers0