CODEWITHSUNDEEP
c#linqdatatableaverage
C Smith
C Smith

Reputation: 808

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!

Upvotes: 3

Views: 2094

Answers (3)

p.s.w.g
p.s.w.g

Reputation: 149020

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.

Upvotes: 8

Douglas
Douglas

Reputation: 54887

Since you have a lot of repetition, you could consider refactoring your average logic into a separate method or anonymous function:

Func<IEnumerable<YourClass>, string, double> avg = 
    (g, name) => g.Select(x => (int)x[name]).Where(x => x != 0).Average();

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

Upvotes: 1

Servy
Servy

Reputation: 203830

Add a Where to each query just before the Average in which you ensure that the item is not equal to zero.

Upvotes: 0

Related Questions