I want to multiply several columns from a df by a column from another df.
df1 and df2 have a column called "year_quarter". I want many columns from df1 to be multiplied by a column from df2 so that year_quarter matches.
df1
year_quarter col1 col2 col3
2010Q1 5 0.34 0.45
2010Q1 4 0.45 0.34
2010Q1 6 0.63 0.86
2010Q1 2 0.75 0.45
2010Q2 3 0.78 0.27
2010Q2 5 0.43 0.38
2010Q2 2 0.34 0.74
2010Q2 1 0.87 0.35
2010Q3 5 0.34 0.45
2010Q3 8 0.54 0.42
2010Q3 9 0.23 0.45
2010Q3 3 0.74 0.34
2010Q4 2 0.72 0.78
2010Q4 7 0.62 0.91
2010Q4 2 0.74 0.10
2010Q4 6 0.73 0.09
df2
year_quarter ratio
2010Q1 0.96
2010Q2 1.34
2010Q3 1.92
2010Q4 0.74
I want to multiply col1, col2 and col3 in df1 by ratio in df2 where the year_quarter matches in both dfs. i.e. if quarter_year in df1 = 2010Q1, then col1, col2 and col3 should be multiplied by 0.96 for all instances of 2010Q1 and so on.
We can do this using match. Matching the similar columns from both the dataframes and then getting the corresponding ratio value and multiplying it to df1 excluding 1st column.
df2$ratio[match(df1$year_quarter, df2$year_quarter)] * df1[-1]
# col1 col2 col3
#1 4.80 0.3264 0.4320
#2 3.84 0.4320 0.3264
#3 5.76 0.6048 0.8256
#4 1.92 0.7200 0.4320
#5 4.02 1.0452 0.3618
#6 6.70 0.5762 0.5092
#7 2.68 0.4556 0.9916
#8 1.34 1.1658 0.4690
#9 9.60 0.6528 0.8640
#10 15.36 1.0368 0.8064
#11 17.28 0.4416 0.8640
#12 5.76 1.4208 0.6528
#13 1.48 0.5328 0.5772
#14 5.18 0.4588 0.6734
#15 1.48 0.5476 0.0740
#16 4.44 0.5402 0.0666