将基于年的向量与年& R中基于月的矩阵 [英] Multiplying year based vector with a year & month based matrix in R

问题描述

我有两个数据框

df1 

Year  Farm 1  Farm 2  Farm 3
2015    1000    2000    1500
2016    500     2000    1000

df 2

Year Month  Farm 1 Farm 2 Farm 3
2015  Jan    1        1      3
2015  Feb    1        2      1
2016  Jan    2        2      2
2016  Feb    2        1      2

我想根据年份将df2中各个农场的df1的年值相乘，以便输出为...

I want to multiply the annual values in df1 across the respective farms in df2 based on the year so that the output is...

df 3 

Year    Month   Farm 1      Farm 2      Farm 3
2015    Jan     1000        2000        4500
2015    Feb     1000        4000        1500
2016    Jan     1000        4000        2000
2016    Feb     1000        2000        2000

我的年份格式正确，但是一直在dplyr中使用group_by寻找解决方案。我应该尝试其他路径吗？

I've got the years properly formatted, but have been struggling to find the solution with group_by in dplyr. Should I be trying a different path?

推荐答案

1）Base R 假设 df1 和 df2 ，合并数据框得到数据框 m 。然后通过使用相同列的乘积替换 d2 的除了前两个列以外的所有列，创建一个新的数据框 df3 df2 和 m 的相应列。

1) Base R Assuming df1 and df2 shown reproducibly in the Note at the end, merge the data frames giving data frame m. Then create a new data frame df3 by replacing all but the first two coiumns of d2 with the product of those same columns of df2 and the appropriate columns of m. No packages are used.

m <- merge(df2, df1, by = 1)
df3 <- replace(df2, -(1:2), df2[-(1:2)] * m[-(1:ncol(df2))] )

给予：

> df3
  Year Month Farm1 Farm2 Farm3
1 2015   Jan  1000  2000  4500
2 2015   Feb  1000  4000  1500
3 2016   Jan  1000  4000  2000
4 2016   Feb  1000  2000  2000

2）sqldf 如果您只有几个服务器场，那么然后将它们分别写出来是可行的：

2) sqldf If you only have a few farms so that it is feasible to write them each out then:

library(sqldf)

sqldf("select 
         Year, 
         b.Month, 
         a.Farm1 * b.Farm1 Farm1,
         a.Farm2 * b.Farm2 Farm2,
         a.Farm3 * b.Farm3 Farm3
       from df2 b left join df1 a using (Year)")

给予：

  Year Month Farm1 Farm2 Farm3
1 2015   Jan  1000  2000  4500
2 2015   Feb  1000  4000  1500
3 2016   Jan  1000  4000  2000
4 2016   Feb  1000  2000  2000

注意

Note

Lines1 <- "
Year  Farm1  Farm2  Farm3
2015    1000    2000    1500
2016    500     2000    1000"

Lines2 <- "
Year Month  Farm1 Farm2 Farm3
2015  Jan    1        1      3
2015  Feb    1        2      1
2016  Jan    2        2      2
2016  Feb    2        1      2"

df1 <- read.table(text = Lines1, header = TRUE)
df2 <- read.table(text = Lines2, header = TRUE)

这篇关于将基于年的向量与年& R中基于月的矩阵的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！