C Smith C Smith - 1 year ago 79
C# Question

Averaging with Linq while ignoring 0s cleanly

I have a linq statement that averages the rows in a DataTable and displays them on a chart, grouped by date and time of day.

There are 1 big problem: there are many 0 values that are returned, due to particular times of day simply not having anything going on. These are skewing my averages something awful

Different times of day may have 0s in different columns, so I can't just delete each row with a 0 in the columns (cleanly), as I would end up with no rows left in the datatable, or at least I can't think of a clean way to do it in any case.

This is what I have:

var results = from row2 in fiveDayDataTable.AsEnumerable()
group row2 by ((DateTime)row2["TheDate"]).TimeOfDay
into g
select new
Time = g.Key,
AvgItem1 = g.Average(x => (int)x["Item1"]),
AvgItem2 = g.Average(x => (int)x["Item2"]),
AvgItem3 = g.Average(x => (int)x["Item3"]),
AvgItem4 = g.Average(x => (int)x["Item4"]),
AvgItem5 = g.Average(x => (int)x["Item5"]),

I don't know if this is possible, so I figured I would ask- is there a way to do the average without the 0s?

Thank you!

Answer Source

Sure you can filter out the zeros:

AvgItem1 = g.Select(x => (int)x["Item1"]).Where(x => x != 0).Average(),
AvgItem2 = g.Select(x => (int)x["Item2"]).Where(x => x != 0).Average(),
AvgItem3 = g.Select(x => (int)x["Item3"]).Where(x => x != 0).Average(),
AvgItem4 = g.Select(x => (int)x["Item4"]).Where(x => x != 0).Average(),
AvgItem5 = g.Select(x => (int)x["Item5"]).Where(x => x != 0).Average(),

If your result set (after the Where) might be empty, you might need to call DefaultIfEmpty.

AvgItem1 = g.Select(x => (int)x["Item1"]).Where(x => x != 0).DefaultIfEmpty(0).Average(),

This will return a non-empty result set so your Average will be able to work with it.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download