I don’t have much experience with LINQ and I’m having difficulties using LINQ to extract the average, mode and median for a column of a ListView based on distinct values in a different column.
For example, consider this data:
ReportID TAT Col3 Col4 etc... -------- --- ---- ---- ------- 80424 9 1 7 ... 80424 9 2 3 ... 80424 9 2 2 ... 80423 5 1 5 ... 80425 4 1 5 ... 80423 7 1 4 ... 82541 7 1 5 ...
To extract the count of distinct reports that have a TAT (turnaround time) greater than 5 days, I use the following:
int countDistinctReports = (from ListViewItem itm in lvList.Items
where itm.SubItems[1].Text) > 5
select itm.SubItems[0].Text
).Distinct().Count();
The value of countDistinctReports is 3.
To extract the minimum TAT for these reports I use this:
string minVal = (from ListViewItem itm in lvList.Items
where itm.SubItems[1].Text) > 5
select itm.SubItems[1].Text
).Min();
The value of minVal is 7.
However, I don’t know how to write LINQ expressions to calculate the average, mode and median TAT of distinct reports that have their TAT greater than 5. In the above example, the average should be (9+7+7)/3 = 7.67. The mode should be 7.
Can someone please help? Many thanks.
Answer
Here’s the problem reproduced and solved, only using linq 1-liners:
using System;
using System.Linq;
namespace StackOverflow_StatisticsLinq
{
class Program
{
static void Main(string[] args)
{
var reports = new int[][]
{
new int[] {80424, 9, 1, 7},
new int[] {80424, 9, 2, 3},
new int[] {80424, 9, 2, 2},
new int[] {80423, 5, 1, 5},
new int[] {80425, 4, 1, 5},
new int[] {80423, 7, 1, 4},
new int[] {80541, 7, 1, 5}
};
var reportsTat5Plus = (
from int[] r in reports
where r[1] > 5
orderby r[1] descending
select r)
.GroupBy(r => r[0])
.Select(r => r.First()); // ensure ids are distinct
var average = reportsTat5Plus.Select(r => r[1]).Average();
var mode = reportsTat5Plus.GroupBy(v => v)
.OrderByDescending(g => g.Count())
.First()
.Select(r => r[1])
.First();
var median = reportsTat5Plus.Count() % 2 == 1
? reportsTat5Plus.Skip(reportsTat5Plus.Count() / 2 + 1).Select(r => r[1]).First()
: reportsTat5Plus.Skip(reportsTat5Plus.Count() / 2 - 1).Take(2).Select(r => r[1]).Average();
Console.WriteLine($"Average: {average}");
Console.WriteLine($"mode: {mode}");
Console.WriteLine($"median: {median}");
Console.ReadKey();
}
}
}