How do I use LINQ to get the Average Value over a Date Range

Question

I am trying to work out if the following can be done in a LINQ to Objects statement.

I have a dictionary with the key as a DateTime (keys are values that are on multiple days) and a double value. I have too much data to plot on a graph so would like to the average value of each 5 minutes.

Sample Input

01/01/2012 23:53    5
01/01/2012 23:54    2
01/01/2012 23:55    1
01/01/2012 23:56    3
01/01/2012 23:57    4
01/01/2012 23:58    5
01/01/2012 23:59    6
02/01/2012 00:00    2
02/01/2012 00:01    4
02/01/2012 00:02    5

Expected Output

01/01/2012 23:55    3
02/01/2012 00:00    4.4

`key as a DateTime (spanning multiple days)` Huh? How can a `DateTime` span multiple days? — Tim Schmelter, Oct 29 '13 at 08:09
Sorry each value is only a single date time however the key is not a set of times from a single day. — John, Oct 29 '13 at 08:11
Try saying Jon Skeet three times in front of a mirror and maybe he'll appear and give an answer. — BenM, Oct 29 '13 at 08:17

score 7 · Accepted Answer · edited May 23 '17 at 11:58

Using this helper method:

static DateTime RoundToNearestInterval(DateTime dt, TimeSpan d)
{
   int f=0;
   double m = (double)(dt.Ticks % d.Ticks) / d.Ticks;
   if (m >= 0.5)
       f=1;            
   return new DateTime(((dt.Ticks/ d.Ticks)+f) * d.Ticks);
}

it's as simple as

var result = from kvp in data
             let key = RoundToNearestInterval(kvp.Key, TimeSpan.FromMinutes(5))
             group kvp by key into g
             select new { g.Key, Value = g.Average(x => x.Value) };

or

var result = data.GroupBy(kvp => RoundToNearestInterval(kvp.Key, TimeSpan.FromMinutes(5)), kvp => kvp.Value)
                 .Select(g => new { g.Key, Value = g.Average() });

LINQPad example:

void Main()
{
    var tmp = new Dictionary<string, int>
    {
        {"01/01/2012 23:53", 5},
        {"01/01/2012 23:54", 2},
        {"01/01/2012 23:55", 1},
        {"01/01/2012 23:56", 3},
        {"01/01/2012 23:57", 4},
        {"01/01/2012 23:58", 5},
        {"01/01/2012 23:59", 6},
        {"02/01/2012 00:00", 2},
        {"02/01/2012 00:01", 4},
        {"02/01/2012 00:02", 5}
    };
    var data = tmp.ToDictionary(d => DateTime.Parse(d.Key), d=>d.Value);

    var result = from kvp in data
                 let key = RoundToNearestInterval(kvp.Key, TimeSpan.FromMinutes(5))
                 group kvp by key into g
                 select new {g.Key, Value = g.Average (x => x.Value) };

    result.ToDictionary(r => r.Key, v => v.Value).Dump();
}

enter image description here

King King · Answer 2 · 2013-10-29T08:43:22.317

Looks like your dictionary contains the ordered elements so we can do something like this:

var firstDate = yourDict.First().Key;
var output = yourDict.GroupBy(e=> (int)(e.Key - firstDate).TotalMinutes / 5)
                     .ToDictionary(g => g.First().Key
                                       .AddMinutes(g.Average(e=>(e.Key - g.First().Key).TotalMinutes)),
                                   g => g.Average(e=>e.Value));

NOTE: The input data of the OP uses a different cutlure than en-US, the month goes after the day. That's the noticeable point to take some test. otherwise the test won't be correct.

score 0 · Answer 3 · answered Oct 29 '13 at 08:29

Here's a LINQ query that will do what you want, you can test this in LINQPad:

void Main()
{
    var points = new[]
    {
        new { dt = new DateTime(2012, 1, 1, 23, 53, 00), value = 5 },
        new { dt = new DateTime(2012, 1, 1, 23, 54, 00), value = 2 },
        new { dt = new DateTime(2012, 1, 1, 23, 55, 00), value = 1 },
        new { dt = new DateTime(2012, 1, 1, 23, 56, 00), value = 3 },
        new { dt = new DateTime(2012, 1, 1, 23, 57, 00), value = 4 },
        new { dt = new DateTime(2012, 1, 1, 23, 58, 00), value = 5 },
        new { dt = new DateTime(2012, 1, 1, 23, 59, 00), value = 6 },
        new { dt = new DateTime(2012, 1, 2, 00, 00, 00), value = 2 },
        new { dt = new DateTime(2012, 1, 2, 00, 01, 00), value = 4 },
        new { dt = new DateTime(2012, 1, 2, 00, 01, 00), value = 5 }
    };

    var interval = TimeSpan.FromMinutes(5);
    var averageByInterval =
        from point in points
        let intervalStart = new DateTime(((int)((point.dt.Ticks + interval.Ticks / 2) / interval.Ticks)) * interval.Ticks)
        group point.value by intervalStart into g
        select new { g.Key, average = g.Average() };
    averageByInterval.Dump();
}

Output:

output

wdavo · Answer 4 · 2013-10-29T09:06:42.927

  var data = new Dictionary<DateTime, double>();

  data.Add(new DateTime(2012, 1, 1, 23, 53, 0), 5);
  data.Add(new DateTime(2012, 1, 1, 23, 54, 0), 2);
  data.Add(new DateTime(2012, 1, 1, 23, 55, 0), 1);
  data.Add(new DateTime(2012, 1, 1, 23, 56, 0), 3);
  data.Add(new DateTime(2012, 1, 1, 23, 57, 0), 4);
  data.Add(new DateTime(2012, 1, 1, 23, 58, 0), 5);
  data.Add(new DateTime(2012, 1, 1, 23, 59, 0), 6);
  data.Add(new DateTime(2012, 1, 2, 0, 0, 0), 2);
  data.Add(new DateTime(2012, 1, 2, 0, 1, 0), 4);
  data.Add(new DateTime(2012, 1, 2, 0, 2, 0), 5);

  var result = data.GroupBy(kvp =>
  {
    var dt = kvp.Key;
    var nearest5 = (int)Math.Round(dt.Minute / 5.0) * 5;
    //Add the minutes after inital date creation to deal with minutes=60
    return new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, 0, 0).AddMinutes(nearest5);
  })
  .Select(g =>
  {
    return new KeyValuePair<DateTime, double>(g.Key, g.Average(row => row.Value));
  });

  foreach (var r in result)
  {
    Console.WriteLine(r.Key + " " + r.Value);
    //  1/01/2012 11:55:00 PM 3
    //  2/01/2012 12:00:00 AM 4.4

  }

This will work, but why not just simplify to `data.GroupBy(dt => Math.Round(new TimeSpan(dt.Key.Ticks).TotalMinutes / 5.0) * 5) .Select(g => new { Key = new DateTime().AddMinutes(g.Key), Value = g.Average(row => row.Value)})`? — sloth, Oct 29 '13 at 08:45
there was a problem with how it was working before anyway, so it has been slightly changed anyway — wdavo, Oct 29 '13 at 08:52

Enigmativity · Answer 5 · 2013-10-29T08:43:42.110

0

Try this:

var results = 
    data
        .GroupBy(
            x => (x.Key.Ticks / TimeSpan.TicksPerMinute + 2) / 5,
            x => x.Value)
        .Select(x => new
        {
            Key = new DateTime(x.Key * TimeSpan.TicksPerMinute * 5),
            Value = x.Average()
        });

edited Oct 29 '13 at 08:43

answered Oct 29 '13 at 08:36

Enigmativity

113,464
11
89
172

Doesn't match with the expected output. The output should be `{{01/01/2012 23:55, 3}, {02/01/2012 00:00, 4.4}}`. – sloth Oct 29 '13 at 08:38
The rounding to the nearest 5 mins threw me. I've fixed my answer. – Enigmativity Oct 29 '13 at 08:43

How do I use LINQ to get the Average Value over a Date Range

5 Answers5