Linq Aggregate and Group by Periods of Time

LINQ aggregate and group by periods of time

You could round the time stamp to the next boundary (i.e. down to the closest 5 minute boundary in the past) and use that as your grouping:

var groups = series.GroupBy(x =>
{
    var stamp = x.timestamp;
    stamp = stamp.AddMinutes(-(stamp.Minute % 5));
    stamp = stamp.AddMilliseconds(-stamp.Millisecond - 1000 * stamp.Second);
    return stamp;
})
.Select(g => new { TimeStamp = g.Key, Value = g.Average(s => s.value) })
.ToList();

Above achieves that by using a modified time stamp in the grouping, which sets the minutes to the previous 5 minute boundary and removes the seconds and milliseconds. The same approach of course can be used for other time periods, i.e. hours and days.

Edit:

Based on this made up sample input:

var series = new List<Sample>();
series.Add(new Sample() { timestamp = DateTime.Now.AddMinutes(3) });
series.Add(new Sample() { timestamp = DateTime.Now.AddMinutes(4) });
series.Add(new Sample() { timestamp = DateTime.Now.AddMinutes(5) });
series.Add(new Sample() { timestamp = DateTime.Now.AddMinutes(6) });
series.Add(new Sample() { timestamp = DateTime.Now.AddMinutes(7) });
series.Add(new Sample() { timestamp = DateTime.Now.AddMinutes(15) });

3 groups were produced for me, one with grouping timestamp 3:05, one with 3:10 and one with 3:20 pm (your results may vary based on current time).

Aggregate values at hourly interval using LINQ C#

You can shift your Timestamp to get the effect. Instead of

items.GroupBy(u => new { u.ID, u.TimeStamp.Date, u.TimeStamp.Hour })

You shift the time that used for grouping by to be 15 minutes in the past, so everything at the first 15 minutes of the hour go to the earlier hour.

items.GroupBy(u => new { u.ID, u.TimeStamp.AddMinutes(-15).Date, u.TimeStamp.AddMinutes(-15).Hour })

LINQ: Group by Date Range within 5 minutes

You can try something like this:

var groupedItems = items
    .GroupBy(i => i.ItemId, (k, g) => g
        .GroupBy(i => (long)new TimeSpan(i.Date.Ticks - g.Min(e => e.Date).Ticks).TotalMinutes / 5))
    .SelectMany(g => g);

Group by date range , count and sort within each group LINQ

Introduce array, which contains ranges you want to group by. Here is two ranges - today (zero days) and 14 days (two weeks):

var today = DateTime.Today;
var ranges = new List<int?> { 0, 14 };

Now group your items by range it falls into. If there is no appropriate range (all dates more than two weeks) then default null range value will be used:

var defaultGroups = 
      from h in ListHeader
      let daysFromToday = (int)(today - h.EntryDate).TotalDays
      group h by ranges.FirstOrDefault(range => daysFromToday <= range) into g
      orderby g.Min(x => x.EntryDate)
      select g;

UPDATE: Adding custom ranges for grouping:

var ranges = new List<int?>();
ranges.Add(0); // today
ranges.Add(7*2); // two weeks
ranges.Add(DateTime.Today.Day); // within current month
ranges.Add(DateTime.Today.DayOfYear); // within current year
ranges.Sort();

Linq: Group by date intervals (5, 10, 15, etc.. years)

Review the following Unit tests used to demonstrate how to group by periods/intervals.

[TestClass]
public class GroupByDateIntervalsTests {
    [TestMethod]
    public void Group_By_5_year_Intervals_Max_30() {
        var employees = GenerateRandomDates(DateTime.Now, 5, 40, 50).Select((d, i) => new { id = i, StartDate = d });

        var now = DateTime.Today;
        var period = 5;
        var maxPeriod = 30;
        var groups = from employee in employees
                     let interval = DateTime.MinValue.AddDays((now - employee.StartDate).TotalDays).Year / period
                     group employee by Math.Min(interval * period, maxPeriod) into g
                     orderby g.Key
                     select new {
                         period = g.Key,
                         employees = g.Select(e => e.id).ToArray()
                     };

        var result = groups.ToList();
    }

    [TestMethod]
    public void Group_By_Random_Interval_Max_30() {
        var employees = GenerateRandomDates(DateTime.Now, 5, 40, 50).Select((d, i) => new { id = i, StartDate = d });

        var now = DateTime.Today;
        var periods = new[] { 5, 10, 20, 30 };
        var groups = employees
            .GroupBy(employee => {
                var period = DateTime.MinValue.AddDays((now - employee.StartDate).TotalDays).Year;
                var interval = periods.Where(p => (period / p) > 0).Max();
                return Math.Min(interval, periods.Max());
            })
            .Select(g => new {
                period = g.Key,
                employees = g.Select(e => e.id).ToArray()
            });

        var result = groups.ToList();
    }

    public List<DateTime> GenerateRandomDates(DateTime rootDate, int minAgeAtRootDate, int maxAgeAtRootDate, int count) {
        Contract.Assert(minAgeAtRootDate <= maxAgeAtRootDate, "invalid age range. Minimum age cannot be higher than maximum age");
        var minDate = rootDate.Date.AddYears(-maxAgeAtRootDate);
        var maxDate = rootDate.Date.AddYears(-minAgeAtRootDate);
        var range = (maxDate - minDate).Days;
        if (range == 0) {
            range = 364;
        }
        var random = new Random();
        var dates = Enumerable
            .Range(1, count)
            .Select(i => minDate.AddDays(random.Next(range)))
            .ToList();
        return dates;
    }
}

Group datetime by an interval of minutes

I think you are looking for SelectMany which will unwind one level of grouping:

var ans = myList
            .GroupBy(c => c.caller, (caller, cg) => new { Key = caller, MinDateTime = cg.Min(c => c.date), Calls = cg })
            .SelectMany(cg => cg.Calls.GroupBy(c => (int)(c.date - cg.MinDateTime).TotalMinutes / 30))
            .OrderBy(cg => cg.Min(c => c.date))
            .ToList();

Note: The GroupBy return selects the Min as a minor efficiency improvement so you don't constantly re-find the minimum DateTime for each group per call.

Note 2: The (int) conversion creates the buckets - otherwise, .TotalMinutes returns a double and the division by 30 just gives you a (unique) fractional answer and you get no grouping into buckets.

By modifying the initial code (again for minor efficiency), you can reformat the answer to match your textual result:

var ans = myList
            .GroupBy(c => c.caller, (caller, cg) => new { Key = caller, MinDateTime = cg.Min(c => c.date), Calls = cg })
            .SelectMany(cg => cg.Calls.GroupBy(c => (int)(c.date - cg.MinDateTime).TotalMinutes / 30), (bucket, cg) => new { FirstCall = cg.MinBy(c => c.date), Calls = cg })
            .OrderBy(fcc => fcc.FirstCall.date)
            .ToList();

var ans2 = ans.Select(fcc => new { Caller = fcc.FirstCall.caller, FirstCallDateTime = fcc.FirstCall.date, LastCallDateTime = fcc.Calls.Max(c => c.date), Count = fcc.Calls.Count() })
              .ToList();

linq group by timespan

This is what I ended up with:

var result = (from logs in db.logs
         where logs.AccountNumber == 1450
         where logs.StartDateTime > new DateTime(2013,1,1) && logs.StartDateTime < new DateTime(2013,1,31)
         group logs by logs.AccountNumber into g
         select new {
             AvgTime = (g.Sum(h=>h.ElapsedHours) * 60) + g.Sum(h=>h.ElapsedMinutes) / g.Count(),
             TotalTime = (g.Sum(h=>h.ElapsedHours) * 60) + g.Sum(h=>h.ElapsedMinutes)
         }).ToList()
         .Select(x=> new {
            AvgTime = TimeSpan.FromMinutes(x.AvgTime),
            TotalTime = TimeSpan.FromMinutes(x.TotalTime)
         });

Grouping by Time ranges in Linq

I've done this myself for exactly the same situation you describe.

For speed, modified the database's datapoints table to include an integer-based time column, SecondsSince2000, and then worked with that value in my LINQ to SQL query. SecondsSince2000 is a computed column defined as:

datediff(second, dateadd(month,1200,0), DataPointTimeColumn) PERSISTED

Where DataPointTimeColumn is the name of the column that stores the datapoint's time. The magic function call dateadd(month,1200,0) returns 2000-01-01 at midnight, so the column stores the number of seconds since that time.

The LINQ to SQL query is then made much simpler, and faster:

int timeSlotInSeconds = 60;

var wattList = 
    (from t in _table
     where t.Id == id
           && t.Time >= startTime
           && t.Time <= endTime
     group t by t.SecondsSince2000 - (t.SecondsSince2000 % timeSlotInSeconds)
     into g
     orderby g.Key 
     select g.Average(a => a.Value))).ToList();

If you can't modify your database, you can still do this:

var baseTime = new DateTime(2000, 1, 1);

var wattList = 
    (from t in _table
     where t.Id == id
           && t.Time >= startTime
           && t.Time <= endTime
     let secondsSince2000 = (int)(t.Time- baseTime).TotalSeconds
     group t by secondsSince2000 - (secondsSince2000 % timeSlotInSeconds)
     into g
     orderby g.Key 
     select g.Average(a => a.Value))).ToList();

The query will be quite a bit slower.

---

Related Topics