Conditional Weighted Average

1
votes

I'm looking to build out a conditional weighted average formula.

Loan Amount Loan Term Credit Score
71100 75 636
29190 75 803
16865.89 72 569
22930.86 75 556
11663.19 72 531

In R, how would you get the loan term's weighted average by loan amount for credit scores between 500-600?

The output should equal 73.34 and in excel would look like:

=SUMPRODUCT(1*(C2:C6>500),1*(C2:C6<600),A2:A6,B2:B6)/SUMPRODUCT(1*(C2:C6>500),1*(C2:C6<600),A2:A6)
1
rdataframeweighted-average
with(subset(df1, Credit_Score > 500, Credit_Score < 600), sum(Loan_Amount * Loan_Term) / sum(Loan_Amount)) – M--

1 Answers

2
votes

Using dplyr you can do this:

library(dplyr)

df1 %>% 
  filter(Credit_Score < 600 & Credit_Score > 500) %>% 
  summarise(Weighted_Average = sum(Loan_Amount * Loan_Term) / sum(Loan_Amount))
#>   Weighted_Average
#> 1         73.33682

Data:

read.table(text="Loan_Amount    Loan_Term   Credit_Score
71100   75  636
29190   75  803
16865.89    72  569
22930.86    75  556
11663.19    72  531", header=T) -> df1