CODEWITHSUNDEEP
rdataframe
Peter
Peter

Reputation: 355

R Multiplying columns of two data frames

I have two data frames one with quantities and one with prices

Quantities <- data.frame(region=c("US","US","EU","China","EU"),a = 1:5, b = 5:9, c=8:12)
prices_frame <- data.frame(region=c("US","EU","China"),a = c(10,20,30), b = c(10,20,30), c=c(1000,2000,100))

Quantities

  region a b  c
1     US 1 5  8
2     US 2 6  9
3     EU 3 7 10
4  China 4 8 11
5     EU 5 9 12

Prices

  region  a  b    c
1     US 10 10 1000
2     EU 20 20 2000
3  China 30 30  100

Is there a way that I can quickly multiply the quantities with the prices auf the matching region without having to loop through the entire quantity frame? Best Alex

Upvotes: 0

Views: 871

Answers (2)

akrun
akrun

Reputation: 886938

We can use a join in data.table

library(data.table)
nm1 <- names(Quantities)[-1]
setDT(Quantities)[prices_frame,  (nm1) := Map(`*`, mget(nm1), 
              mget(paste0("i.", nm1))) , on = "region"]
Quantities
#   region   a   b     c
#1:     US  10  50  8000
#2:     US  20  60  9000
#3:     EU  60 140 20000
#4:  China 120 240  1100
#5:     EU 100 180 24000

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 388807

We can use match

Assuming your columns a, b, c are in same order in both the dataframes

Quantities[-1] * prices_frame[match(Quantities$region, prices_frame$region), -1]


#    a   b     c
#1  10  50  8000
#2  20  60  9000
#3  60 140 20000
#4 120 240  1100
#5 100 180 24000

To get the dataframe with the same number of columns, 

new_df <- cbind(Quantities[1], 
  Quantities[-1] * prices_frame[match(Quantities$region, prices_frame$region), -1])

#  region   a   b     c
#1     US  10  50  8000
#2     US  20  60  9000
#3     EU  60 140 20000
#4  China 120 240  1100
#5     EU 100 180 24000

Upvotes: 3

Related Questions