Group data by every n day using LINQ

Question

I have the following class:

public class TotalPaymentStatistics 
{ 
     public DateTime RegisteredAt { get; set; }
     public double TotalAmount { get; set; }  
}

Also I have following examplary data stored in database:

2021-01-01 | 120
2021-01-02 | 120
2021-01-03 | 120
2021-01-04 | 120
2021-01-05 | 120
2021-01-06 | 120
2021-01-07 | 120
2021-01-08 | 120
2021-01-09 | 120
2021-01-10 | 120

How to group object of this class in such a way, that when I pass argument, for example 5, (5 - being a time frame, meaning that I want to received sumarized data for every 5 days period in the whole set), I will receive following results:

2021-01-01 600
2021-01-05 600

Thanks in advance

`.GroupBy(p => p.RegisteredAt.Date).Take(nDays).Select(g => new TotalPaymentStatistics { RegisteredAt = g.Key, TotalAmount = g.Sum(i => i.TotalAmount))`? — ProgrammingLlama, Apr 02 '21 at 09:11
What if I want it to be grouped by every 5th day ? This will return me data grouped by every day — mSwierkot, Apr 02 '21 at 09:20
@PrasadTelkikar I have tried diving group key by number like this : Item.GroupBy(d => d.RegisteredAt.Date.DayOfYear / 5) — mSwierkot, Apr 02 '21 at 09:21
I recommend you edit your question to be clear about what you're asking. "when I pass argument, for example 5" definitely needs clarity. As does how you end up with 600 for both of them. — ProgrammingLlama, Apr 02 '21 at 09:21
5 - being a time frame, meaning that I want to received sumarized data for every 5 days period in the whole set — mSwierkot, Apr 02 '21 at 09:23
If 5 is the time frame then first frame is from 1 to 5. is from 6 to 10.. Can use finger index to check ^^ — Self, Apr 02 '21 at 09:29
Could be 4 lines for loop, and a single variable to store endPartitionDate. — Drag and Drop, Apr 02 '21 at 09:40

Ashutosh Kushawaha · Answer 1 · 2021-04-02T09:27:56.227

0

you can try this. suppose your dbcontext class is _context and add DaysCount as int type to model for days count, then you can write as

_context.yourTables.GroupBy(x=>x.RegisteredAt.Date).select(t=>new TotalPaymentStatistics (){
 RegisteredAt =t.Key,
 TotalAmount =t.Sum(s=>s.TotalAmount),
 DaysCount = t.Count()
}).ToList();

edited Apr 02 '21 at 09:27

answered Apr 02 '21 at 09:20

Ashutosh Kushawaha

751
8
16

But where do I specify number of days, time frame ? – mSwierkot Apr 02 '21 at 09:22
@mSwierkot I have edit in above model to put days count and also change in query to get it. – Ashutosh Kushawaha Apr 02 '21 at 09:28

eocron · Answer 2 · 2021-04-02T09:28:23.307

Universal approach would be to just use rounding to nearest. You can specify any time interval that way. For example:

Rounding DateTime objects

Then you can just:

var groupInterval = TimeSpan.FromDays(5);
var groups = input
      .GroupBy(x=> x.RegisteredAt.Floor(groupInterval))
      .Select(x=> 
          new 
          { 
               date = x.Key, 
               sum = x.Sum(y=> y.TotalAmount)
          });

You can tweak with it a little to get desired groups by specifying starting shift or using Floor/Ceiling/Round methods.

score 0 · Answer 3 · edited Apr 03 '21 at 15:18

With a naive partitioning: data is ordered, and has at least one element.

static IEnumerable<List<T>> Partition<T>(List<T> input, Func<T, DateTime> DateSelector, TimeSpan partitionSize)
{
    var partitionEnd = DateSelector(input.First()).Add(partitionSize);
    var partition = new List<T>();

    foreach (var element in input)
    {
        var dt = DateSelector(element);
        if (dt >= partitionEnd)
        {
            yield return partition;
            partition = new List<T>();
            partitionEnd = dt.Add(partitionSize);
        }
        partition.Add(element);
    }
    yield return partition;
}

You can do

var result = Partition(input, (x) => x.Date, TimeSpan.FromDays(partitionSize))
                .Select(p => new { Date = p.Min(x=> x.Date), Amount = p.Sum(x => x.Amount) });

Live demo https://dotnetfiddle.net/lpwgbI

Result:

Date	Amount
01/01/2021 00:00:00	600
01/06/2021 00:00:00	600

score 0 · Answer 4 · answered Apr 05 '21 at 15:49

<pre>
void Main()
{
    var items = new List<TotalPaymentStatistics>()
    {
        new TotalPaymentStatistics(){RegisteredAt = new DateTime(2021,1,1),TotalAmount = 120},
        new TotalPaymentStatistics(){RegisteredAt = new DateTime(2021,1,2),TotalAmount = 120},
        new TotalPaymentStatistics(){RegisteredAt = new DateTime(2021,1,3),TotalAmount = 120},
        new TotalPaymentStatistics(){RegisteredAt = new DateTime(2021,1,4),TotalAmount = 120},
        new TotalPaymentStatistics(){RegisteredAt = new DateTime(2021,1,5),TotalAmount = 120},
        new TotalPaymentStatistics(){RegisteredAt = new DateTime(2021,1,6),TotalAmount = 120},
        new TotalPaymentStatistics(){RegisteredAt = new DateTime(2021,1,7),TotalAmount = 120},
        new TotalPaymentStatistics(){RegisteredAt = new DateTime(2021,1,8),TotalAmount = 120},
        new TotalPaymentStatistics(){RegisteredAt = new DateTime(2021,1,9),TotalAmount = 120},
        new TotalPaymentStatistics(){RegisteredAt = new DateTime(2021,1,10),TotalAmount = 120},
    };
    DateTime day0 = items[0].RegisteredAt.AddDays(-1);
    var q = items
        .GroupBy(x => ((int)((x.RegisteredAt.Subtract(day0).TotalDays-1) / 5)))
        .Select(x => new {
            x.Key,
            Date = day0.AddDays(x.Key*5+1),
            Amount = x.Sum(y => y.TotalAmount)
        });
    foreach(var item in q)
    {
        Console.WriteLine($"{item.Date.ToString("yyyy-MM-dd")} {item.Amount}");
    }
}

// Define other methods and classes here
public class TotalPaymentStatistics
{
    public DateTime RegisteredAt { get; set; }
    public double TotalAmount { get; set; }
}
</pre>

which will give you :

2021-01-01 600

2021-01-06 600

Group data by every n day using LINQ

4 Answers4