I have two data frames:
Number 1:
>df1
ID NUM QUANT STR IDENT
1 30 30 10 1111
1 30 50 100 2222
2 60 60 20 1111
3 15 15 10 3333
3 20 10 10 1111
4 60 30 20 2222
5 30 20 15 3333
6 45 60 10 2222
Number 2:
>df2
IDENT FACTOR
1111 1.0
2222 0.5
3333 2.0
I want to calculate a new value based on values in two data frames and append the calculated value to a new column called RESULT
in data frame 1. The shared identifier in both data frames is IDENT
.
The formula used to calculate the value:
STR*QUANT/NUM*FACTOR=RESULT
The final data frame should look like this:
ID NUM QUANT STR IDENT RESULT
1 30 30 10 1111 10.0000
1 30 50 100 2222 83.3333
2 60 60 20 1111 20.0000
3 15 15 10 3333 20.0000
3 20 10 10 1111 5.0000
4 60 30 20 2222 5.0000
5 30 20 15 3333 20.0000
6 45 60 10 2222 6.6667
This is the code for reproducing the data frames:
ID = c(1, 1, 2, 3, 3, 4, 5, 6)
NUM = c(30, 30, 60, 15, 20, 60, 30, 45)
QUANT = c(30, 50, 60, 15, 10, 30, 20, 60)
STR = c(10,100,20,10,10,20,15,10)
IDENT = c(1111,2222,1111,3333,1111,2222,3333,2222)
df1 = data.frame(ID, NUM, QUANT, STR, IDENT)
IDENT = c(1111, 2222, 3333)
FACTOR = c(1, 0.5, 2)
df2 = data.frame(IDENT, FACTOR)
IDENT = c(1111,2222,1111,3333,1111,2222,3333,2222)
RESULT = c(10,83.3333,20,20,5,5,20,6.6667)
df3 = data.frame(ID, NUM, QUANT, STR, IDENT, RESULT)
Inspired by the comment by @A5C1D2H2I1M1N2O1R2T1 above:
output <- within(merge(df1, df2), { RESULT = STR*QUANT/NUM*FACTOR })
output <- output[, !(names(output) %in% c("FACTOR"))]
output
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With