Search code examples
rdataframeweightedweighted-average

How to reference multiple dataframe columns to calculate a new column of weighted averages in R

I am currently calculating the weighted average column for my dataframe through manual referencing of each column name. Is there a way to shorten the code by multiplying sets of arrays eg: df[,c(A,B,C)] and df[,c(PerA,PerB,PerC)] to obtain the weighted average, like the SUMPRODUCT in Excel? Especially if I have multiple input columns to calculate the weighted average column

df$WtAvg = df$A*dfPerA + df$B*df$PerB + df$C*df$PerC

enter image description here

Solution

  • Without transforming your dataframe and assuming that first half of the dataframe is the size and the second half is the weight, you can use weighted.mean function in apply function:

    df$WtAvg = apply(df,1,function(x){weighted.mean(x[1:(ncol(df)/2)],
                                                x[(ncol(df)/2+1):ncol(df)])})

    And you get the following output:

    > df
  A B C PerA PerB PerC WtAvg
1 1 2 3  0.1  0.2  0.7   2.6
2 4 5 6  0.5  0.3  0.2   4.7
3 7 8 9  0.6  0.1  0.3   7.7