C Smith - 3 months ago 30

C# Question

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

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.