dplyr - How to present another column on this R data.table output?

Question

I am trying to present another columns DF2$time_expected and DF$time/DF2$time_expected in the output of DF[DF$Experiment=="A", ]. Code where outputs of some commands shown

library(data.table)
library('dplyr')
ow <- options("warn")

DF <- read.csv(text= 
"Field,time,T,Experiment
Acute,0,0,A
An,9,120,A
En,15.6,2,A
Fo,9.2,2,A
Acute,8.3,1,B
An,7.7,26,B
En,12.9,1,B
Fo,0,0,B
Acute,7.5,1,C
An,7.9,43,C
En,0,0,C
Fo,5.4,1,C
Acute,8.6,2,D
An,7.8,77,D
En,0,0,D
Fo,0,0,D
Acute,0,0,E
An,7.9,60,E
En,14.3,1,E
Fo,0,0,E
Acute,8.3,4,F
An,8.2,326,F
En,14.6,4,F
Fo,7.9,3,F", 
header=TRUE, sep=",")

# http://stackoverflow.com/a/43695774/54964
DF[DF$Experiment=="A", ]
#  Field time   T Experiment
#1 Acute  0.0   0          A
#2    An  9.0 120          A
#3    En 15.6   2          A
#4    Fo  9.2   2          A
# TODO integrate here relative values of DF$time/DF2$time_expected as another column

DF2 <- read.csv(text=
"Field,time_expected
Acute,6
An,6
En,6
Fo,5", 
header=TRUE, sep=",")
#DF2[DF2$Field=="An", ]

## Now compare DF.time to DF2.time_expected by DF.time/DF2.time_expected
DF$time/DF2$time_expected
# [1] 0.000000 1.500000 2.600000 1.840000 1.383333 1.283333 2.150000 0.000000
# [9] 1.250000 1.316667 0.000000 1.080000 1.433333 1.300000 0.000000 0.000000
#[17] 0.000000 1.316667 2.383333 0.000000 1.383333 1.366667 2.433333 1.580000

Expected output where two new columns (time_expected and time/time_expected)

  Field time   T Experiment  time_expected time/time_expected
1 Acute  0.0   0          A  6              0.0
2    An  9.0 120          A  6              1.5
3    En 15.6   2          A  6              2.6
4    Fo  9.2   2          A  5              1.84

Answer 1

It's usually not a good idea to load both data.table and dplyr together.

With dplyr we can do:

library(dplyr) # No need to quote

final_df <- DF %>% 
  filter(time < 8) %>%
  full_join(DF2) %>% 
  mutate(`time/time_expected` = time / time_expected) %>% 
  select(Field, `time/time_expected`)

final_df
#> Joining, by = "Field"
#>    Field time  T Experiment time_expected time/time_expected
#> 1  Acute  0.0  0          A             6           0.000000
#> 2     An  7.7 26          B             6           1.283333
#> 3     Fo  0.0  0          B             5           0.000000
#> 4  Acute  7.5  1          C             6           1.250000
#> 5     An  7.9 43          C             6           1.316667
#> 6     En  0.0  0          C             6           0.000000
#> 7     Fo  5.4  1          C             5           1.080000
#> 8     An  7.8 77          D             6           1.300000
#> 9     En  0.0  0          D             6           0.000000
#> 10    Fo  0.0  0          D             5           0.000000
#> 11 Acute  0.0  0          E             6           0.000000
#> 12    An  7.9 60          E             6           1.316667
#> 13    Fo  0.0  0          E             5           0.000000
#> 14    Fo  7.9  3          F             5           1.580000