ArmenHeat ArmenHeat - 3 years ago 58
C# Question

Grouping object by date and sum-ing value in C#

I have a list of objects where each object has a string and a int. Each string is a date in the yyyy-M-d format.

The list can contain dates 30, 90 or 365 days from the first date

So a list of items (of 30 days) would be:

2017-7-25 10
2017-7-24 3
2017-7-23 7
2017-7-22 4
2017-7-21 2
2017-7-20 4
..
2017-6-27 5
2017-6-26 8


I want to group these dates by 5 days such that:

2017-7-21 30
2017-7-16 (Sum of values from 7-16 till 7-20)


and so on.

I cant figure out the lambda for this.

var grouped = from x in list
group x by DateTime.Parse(x.date)/5
select new { date = x.????????, count = x.Sum()}

Answer Source

If you have declared a class like the following:

internal class DayNumber
{
    public string Day { get; set; }
    public int Number { get; set; }
}

and you have defined a list like the following one:

var list = new List<DayNumber>
{
    new DayNumber {Day = "2017-7-25", Number = 10},
    new DayNumber {Day = "2017-7-24", Number = 3},
    new DayNumber {Day = "2017-7-23", Number = 7},
    new DayNumber {Day = "2017-7-22", Number = 4},
    new DayNumber {Day = "2017-7-21", Number = 2},
    new DayNumber {Day = "2017-7-20", Number = 4},
    new DayNumber {Day = "2017-7-19", Number = 5},
    new DayNumber {Day = "2017-7-18", Number = 8},
    new DayNumber {Day = "2017-7-17", Number = 2},
    new DayNumber {Day = "2017-7-16", Number = 3}
};

then you could try something like this:

var grouped = list.Select(item => new 
                  {
                      Parsed = DateTime.ParseExact(item.Day, "yyyy-M-dd", CultureInfo.InvariantCulture), 
                      Number = item.Number
                  })
                  .OrderBy(item => item.Parsed)
                  .Select((item, index) => new 
                  {
                      Index = index, 
                      Item = item
                  })
                  .GroupBy(item => item.Index / 5)
                  .Select(gr => new 
                  {
                      Date = gr.First().Item.Parsed, 
                      Count = gr.Sum(x => x.Item.Number)
                  })
                  .ToList();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download