Multiplying columns from different dataframes with different lengths and two conditions in R

1
votes

I have two data frames with different lengths. I want to mutate the columns in data frame df with the multiplication of CAP * currency and Go * currency from df_cur. This should be done with the conditions that country and Year must be the same for the two data fames respectively. More specifically,

#df#

Country Sector Year Cap Go Exposion
AUS A 2000 100 200 0.2
AUS B 2000 150 200 0.3
AUS C 2000 160 160 0.25
AUS A 2001 110 200 0.25
AUS B 2001 140 190 0.4
AUS C 2001 165 155 0.2
BEL A 2000 50 150 0.1
BEL B 2000 70 160 0.15
BEL C 2000 100 200 0.2
BEL A 2001 55 160 0.15
BEL B 2001 65 140 0.1
BEL C 2001 110 190 0.3

#df_cur#

country year currency
AUS 2000 0.58
AUS 2001 0.60
BEL 2000 0.92
BEL 2001 0.95

So, I want to transform df like:

#df#

Country Sector Year Cap Go Exposion
AUS A 2000 100*0.58 200*0.58 0.2
AUS B 2000 150*0.58 300*0.58 0.3
AUS C 2000 160*0.58 160*0.58 0.25
AUS A 2001 110*0.6 200*0.6 0.25
AUS B 2001 140*0.6 190*0.6 0.4
AUS C 2001 165*0.6 155*0.6 0.2
BEL A 2000 50*0.92 150*0.92 0.1
BEL B 2000 70*0.92 160*0.92 0.15
BEL C 2000 100*0.92 200*0.92 0.2
BEL A 2001 55*0.95 160*0.95 0.15
BEL B 2001 65*0.95 140*0.95 0.1
BEL C 2001 110*0.95 190*0.95 0.3

I reviewed many answers from Multiplying columns of different size of 2 data frames but nothing worked for me.

My code sample:

Country<-c("AUS","AUS","AUS","AUS","AUS","AUS", "BEL", "BEL", "BEL", "BEL", "BEL", "BEL")
Sector<-c("A","B","C","A","B","C","A","B","C","A","B","C")
Year<-c("2000", "2000", "2000", "2001", "2001", "2001", "2000", "2000", "2000", "2001", "2001", "2001")
Cap<-c(100,150,160,110,140,165,50,70,100,55,65,110)
Go<-c(200,200,160,200,190,155,150,160,200,160,140,190)
Exposion<-c(0.2,0.3,0.25,0.25,0.4,0.2,0.1,0.15,0.2,0.15,0.1,0.3)
df<-data.frame(Country,Sector,Year,Cap,Go,Exposion)

country<-c("AUS","AUS", "BEL", "BEL")
Year<-c("200","2001","2000","2001")
currency<-c(0.58, 0.6, 0.92, 0.95)
df_cur<-data.frame(country,Year,currency)

Thank you very much for your time!

2
rdata-manipulationmultiplicationdata-handling

2 Answers

1
votes

Welcome Panagiotis! The easiest is to first combine the two data.frames. Then in the second step you can create new columns with mutate():

library(dplyr)

df %>%
  left_join(., df_cur) %>%
  mutate(cap2 = Cap * currency) %>%
  mutate(go2 = Go * currency)
0
votes

Using data.table

library(data.table)
setDT(df)[df_cur, c("Cap", "Go") := 
     .(Cap * currency, Go * currency), on = .(Country = country, Year)]

-output

df
#    Country Sector Year    Cap    Go Exposion
# 1:     AUS      A 2000 100.00 200.0     0.20
# 2:     AUS      B 2000 150.00 200.0     0.30
# 3:     AUS      C 2000 160.00 160.0     0.25
# 4:     AUS      A 2001  66.00 120.0     0.25
# 5:     AUS      B 2001  84.00 114.0     0.40
# 6:     AUS      C 2001  99.00  93.0     0.20
# 7:     BEL      A 2000  46.00 138.0     0.10
# 8:     BEL      B 2000  64.40 147.2     0.15
# 9:     BEL      C 2000  92.00 184.0     0.20
#10:     BEL      A 2001  52.25 152.0     0.15
#11:     BEL      B 2001  61.75 133.0     0.10
#12:     BEL      C 2001 104.50 180.5     0.30