Weighted Average with LINQ

Question

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    PCR
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.PCR*t2.LENGTH)/Sum(t2.LENGTH) AS WEIGHTED_AVERAGE
FROM Table1 t1, Table2 t2
WHERE t2.PCR <> -1
AND t2.ForeinKey = 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 1

If you're certain that for each foreign key in Table2 there is a corresponding record in Table1, then you can avoid the join just making a group by.

In that case, the LINQ query is like this:

IEnumerable<int> wheighted_averages =
    from record in Table2
    where record.PCR != -1
    group record by record.ForeignKey into bucket
    select bucket.Sum(record => record.PCR * record.Length) / 
        bucket.Sum(record => record.Length);

UPDATE

This is how you can get the wheighted_average for a specific foreign_key.

IEnumerable<Record> records =
    (from record in Table2
    where record.ForeignKey == foreign_key
    where record.PCR != -1
    select record).ToList();
int wheighted_average = records.Sum(record => record.PCR * record.Length) /
    records.Sum(record => record.Length);

The ToList method called when fetching the records, is to avoid executing the query twice while aggregating the records in the two separate Sum operations.

Answer 2

(Answering jsmith's comment to the answer above)

If you don't wish to cycle through some collection, you can try the following:

var filteredList = Table2.Where(x => x.PCR != -1)
 .Join(Table1, x => x.ForeignKey, y => y.Key, (x, y) => new { x.PCR, x.Length });

int weightedAvg = filteredList.Sum(x => x.PCR * x.Length) 
    / filteredList.Sum(x => x.Length);

Answer 3

I do this so much on a day to day basis that I created a generic 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(record => value(record) * weight(record));
        double weightSum = records.Sum(record => weight(record));

        return weightedValueSum / weightSum;
    }

After you get your subset of data from the join the call would look like this.

double weightedAverage = records.WeightedAverage(record => record.PCR, record => record.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.