jsmith jsmith - 6 days ago 5
C# Question

Calculating Weighted Average with LINQ

My goal is to get a weighted average from one table, based on another tables primary key.

Example Data:

Table1

Key WEIGHTED_AVERAGE

0200 0


Table2

ForeignKey Length Value
0200 105 52
0200 105 60
0200 105 54
0200 105 -1
0200 47 55


I need to get a weighted average based on the length of a segment and I need to ignore values of -1. I know how to do this in SQL, but my goal is to do this in LINQ. It looks something like this in SQL:

SELECT Sum(t2.Value*t2.Length)/Sum(t2.Length) AS WEIGHTED_AVERAGE
FROM Table1 t1, Table2 t2
WHERE t2.Value <> -1
AND t2.ForeignKey = t1.Key;


I am still pretty new to LINQ, and having a hard time figuring out how I would translate this. The result weighted average should come out to roughly 55.3. Thank you.

Answer

I do this enough that I created an extension method for LINQ.

public static double WeightedAverage<T>(this IEnumerable<T> records, Func<T, double> value, Func<T, double> weight)
{
    double weightedValueSum = records.Sum(x => value(x) * weight(x));
    double weightSum = records.Sum(x => weight(x));

    if (weightSum != 0)
        return weightedValueSum / weightSum;
    else
        throw new DivideByZeroException("Your message here");
}

After you get your subset of data the call looks like this.

double weightedAverage = records.WeightedAverage(x => x.Value, x => x.Length);

This has become extremely handy because I can get a weighted average of any group of data based on another field within the same record.

Update

I now check for dividing by zero and throw a more detailed exception instead of returning 0. Allows user to catch the exception and handle as needed.