R - How to use data from two dataframe to calculate new variables in R?-CodePudding

i'm kind of newby in R and i'm trying real hard to explain as clearly as possible the problem i'm facing. And my english is not that good, sorry in advance.

I have two dataframes, one with thousands of lines, which are polygon numbers, and 13 variables.

The other dataframe is a weigthing grid for each variables, that I'm trying to apply on the first dataframe. Here is a picture of the second dataframe : weighting grid

I'm trying to calculate new variables for each polygons (lines in my first dataframe), using the values of the second dataframe. For example, my first variable created would be FON_ECOL = param1 x 1 param2 x 0.5 param3 x 1 All the numbers (1, 0.5, 1 and 2) are in the weighting grid.

Here are codes for what the dataframes looks like :

df1 <- data.frame(IDMH=c('MH1','MH2','MH3'), param1 =c(0.5,1,0.005), param2=c(0,0.1,2), param3=c(4,5,6)) 
IDMH param1 param2 param3
MH1  0.5    0      4     
MH2  1      0.1    5
MH3  0.005  2      6

df2 <- data.frame(Parameters=c('param1','param2','param3'), FON_ECOL =c(1,0.5,1), BIOD=c(0,0,1), MEN=c(1,1,0)) 
Parameters FON_ECOL BIOD MEN
param1     1        0    1     
param2     0.5      0    1  
param3     1        1    0

Thanks a lot for your help, every insight will be usefull ! Let me know if I can explain more, or add .csv

CodePudding user response：

Here's a dplyr/tidyr approach. It combines df1 with the summary columns. Those summary columns are calculated by reshaping df1 longer, joining that to df2, and then summing the weighted values for the FON_ECOL:MEN columns within each IDMH group.

library(dplyr); library(tidyr)
left_join(
  df1, 
  df1 %>%
    pivot_longer(-IDMH, names_to = "Parameters") %>%
    left_join(df2) %>%
    group_by(IDMH) %>%
    summarize(across(FON_ECOL:MEN, ~sum(.x * value))))


  IDMH param1 param2 param3 FON_ECOL BIOD   MEN
1  MH1  0.500    0.0      4    4.500    4 0.500
2  MH2  1.000    0.1      5    6.050    5 1.100
3  MH3  0.005    2.0      6    7.005    6 2.005