R Data.table divide values in column based on another column

Question

I have a main data.table which has 364 rows and the 3 columns:

Date        Weekday     Weight
2012-01-01  Monday      100
2013-01-02  Tuesday     200
...

and a help data.table with 7 rows 2 columns:

Weekday   Coefficient
Monday    0.91
Tuesday   0.84
Wednesday 0.99
...

Now i would like to create a 4th column in the main data.table with the "weight/Coefficient" based on the Weekday.

Weight_divided <- main[, Weight * help[Weekday==main$Weekday]$Coefficient]

The result is the following:

Date        Weekday     Weight   Weight_divided
2012-01-01  Monday      100      91
2013-01-02  Tuesday     200      168
2012-01-03  Wednesday   300      297
2012-01-04  Thursday    400      256
2012-01-05  Friday      500      399
2012-01-06  Saturday    600      410
2012-01-07  Sunday      700      680
2012-01-08  Monday      300      NA     <--
2012-01-09  Tuesday     600      NA     <--
...

I guess the issue is that the length of both data.tables is different. Is there a way how to reference in the main data.table operation that this works with a shorter data.table?

Try `setDT(main, key='Weekday')[help][, Weight_divided:=Weight/Coefficient]` — akrun, Jun 08 '15 at 14:58
BTW, I haven't tested this as the example you provided and the expected result is not reproducible — akrun, Jun 08 '15 at 15:05
I missed it was a `data.table`. And yes, looking on resulting table you should multiply instead of divide — Andriy T., Jun 08 '15 at 15:11
@nicola: yeah you were right * instead of / , sorry for that — RandomDude, Jun 08 '15 at 15:25

score 2 · Accepted Answer · answered Jun 08 '15 at 15:20

Using data.table

library(data.table)
setkey(main, Weekday)[help, Weight_Coef := Weight*Coefficient][order(Date)]
  #      Weekday       Date Weight Weight_Coef
  # 1:    Monday 2012-01-01     59       53.69
  # 2:   Tuesday 2012-01-02     45       37.80
  # 3: Wednesday 2012-01-03    141      139.59
  # 4:  Thursday 2012-01-04    104       97.76
  # 5:    Friday 2012-01-05    133      109.06
  #---                                        
  #360: Wednesday 2012-12-25    192      190.08
  #361:  Thursday 2012-12-26     79       74.26
  #362:    Friday 2012-12-27     39       31.98
  #363:  Saturday 2012-12-28    175      148.75
  #364:    Sunday 2012-12-29    134      116.58

data

set.seed(24)
main <- data.table(Weekday=rep(c('Monday', 'Tuesday', 'Wednesday',
'Thursday', 'Friday', 'Saturday', 'Sunday'), length.out=364),
Date=seq(as.Date('2012-01-01'), length.out=364, by='day'), 
Weight=sample(200, 364, replace=TRUE))

help <- data.table(Weekday=c('Monday', 'Tuesday', 'Wednesday',
'Thursday', 'Friday', 'Saturday', 'Sunday'), Coefficient=c(0.91, 0.84, 
 0.99, 0.94, 0.82, 0.85, 0.87))

setkey(main, Weekday)[help, Weight_Coef := Weight*Coefficient] works perfectly! thanks for that — RandomDude, Jun 08 '15 at 15:25
@RandomDude Glad to know that it worked. Thanks for the feedback — akrun, Jun 08 '15 at 15:26
conceptual takeaway: if you have columns you need in two separate `data.table`s, you're first thought should be to merge them somehow. merging is (in one sense) how to combine the "column space" of your separate dts. — MichaelChirico, Jun 08 '15 at 22:11

R Data.table divide values in column based on another column

1 Answers1

data

Linked