dimanche 31 janvier 2021

How to reference another value that meets certain conditions which can then be used in a calculation

I have to two data frames:

Test

Group.1   x
   1     25.5
   2     51
   3     51.5
   4     50
   5     51.5
   6     60 
   ...
   53    35.5

Calendar

Week   Hours  HourSpent
  1     8.5
  1     8.5
  1      0
  2     8.5
  2     8.5
  2     8.5
  2     8.5
  2     8.5
  2     6.5
  2     8.5
  3     7.0
  3     7.0
  3     8.2
  ...

What I am trying to do is to populate the 'HourSpent' column in the Calendar df by doing the following calculation: (('Hours' / 'HourSpent') * 0.79)

I want to be able to go through each line in the Calendar df and divide that rows 'Hours' value with the matching 'HourSpent' value. The 'HoursSpent' value can be decided from the 'Test' df...so if the value in the 'Week' column from the Calendar df matches any value in 'Group.1' column of the 'Test' df then I want the corresponding value in the 'x' column of the 'Test' df to be the 'HourSpent' value.

E.g.

Row 1 in the Calendar df will be 8.5 / 25.5 * 0.79...which will apply to the top 3 rows as the week number is 1. Then when we get to row 4 the calculation would change to 8.5/ 51 * 0.79 and so on...etc

Desired Output - Calendar df

Week   Hours  HourSpent
  1     8.5     0.2633
  1     8.5     0.2633
  1      0        0
  2     8.5     0.1317
  2     8.5     0.1317
  2     8.5     0.1317
  2     8.5     0.1317
  2     8.5     0.1317
  2     6.5     0.1007
  2     8.5     0.1317
  3     7.0     0.1074
  ...

Code Tried

for (i in 1:nrow(Calendar)){

 Calendar$'HourSpent' <- ifelse(Calendar$Week == Test$Group.1, 
 (Calendar$Hours/Test$x)*0.79, 
 0)

}

The problem is this only seems to work for one row then everything else is 0...is there a better solution to this issue?

Many thanks

Aucun commentaire:

Enregistrer un commentaire