Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Multiply values from two data frames based on shared identifier

Tags:

dataframe

r

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) 
like image 786
PCK1992 Avatar asked Sep 11 '25 09:09

PCK1992


1 Answers

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

Demo

like image 88
Tim Biegeleisen Avatar answered Sep 13 '25 23:09

Tim Biegeleisen