Count occurrences of an event by date with C#

user_14378281 0
Viewed 138

I am facing an issue with counting the number of occurrences by date in C#. Should I use Linq to filter it? Please advise. Thank you.

Date Player ID
1/1/2001 23
1/1/2001 29
1/1/2001 24
3/1/2001 22
3/1/2001 23

My preferred output should be

Date No. Of Players
1/1/2001 3
2/1/2001 0
3/1/2001 2

This is my current code, how can I do it within the select:

 var convertTable = dataPageTable.AsEnumerable();
 Records = new List<List<ContentOutputModel>>(convertTable.Select(dr =>
            {
                var playerId = dr.GetColumn<long>("PlayerID").ToString();
                var dateInt = dr.GetColumn<int>("Date").ToString();
                var dateStr = dateInt.Substring(6, 2) + "/" + dateInt.Substring(4, 2) + "/" + dateInt.Substring(0, 4);
                var output = new List<ContentOutputModel>(new ContentOutputModel[] {
                    new ContentOutputModel() { Text = dateStr },
                    new ContentOutputModel() { Text = playerId },
                });
                return output;
            }));
find-occurrences filtering linq c#
4 Answers

Here's the cleanest that I could come up with:

List<Player> players = new List<Player>()
{
    new Player() { Date = new DateTime(2021, 1, 1), ID = 23 },
    new Player() { Date = new DateTime(2021, 1, 1), ID = 29 },
    new Player() { Date = new DateTime(2021, 1, 1), ID = 24 },
    new Player() { Date = new DateTime(2021, 1, 3), ID = 22 },
    new Player() { Date = new DateTime(2021, 1, 3), ID = 23 }
};

var first = players.Min(p => p.Date);
var last = players.Max(p => p.Date);

var days = last.Subtract(first).Days + 1;

var lookup = players.ToLookup(p => p.Date);

var output =
    from n in Enumerable.Range(0, days)
    let Date = first.AddDays(n)
    select new
    {
        Date,
        Count = lookup[Date].Count(),
    };

That gives me:

output

user_2597690

You can achieve by Group() via System.Linq.

  1. Order players by Date and get startDate and endDate.
  2. Generate an array with dates from startDate to endDate.

3.1 With group to count player(s) by Date.

3.2 Left join result from (2) with the result (3.1) to get Date and Count.

using System.Linq;
using System.Collections.Generic;

List<Player> players = new List<Player>
{
    new Player{Date = new DateTime(2021, 1, 1), ID = 23}, 
    new Player{Date = new DateTime(2021, 1, 1), ID = 29}, 
    new Player{Date = new DateTime(2021, 1, 1), ID = 24}, 
    new Player{Date = new DateTime(2021, 1, 3), ID = 22}, 
    new Player{Date = new DateTime(2021, 1, 3), ID = 23}
};
        
var startDate = players.OrderBy(x => x.Date)
    .First()
    .Date;
        
var endDate = players.OrderBy(x => x.Date)
    .Last()
    .Date;
        
var dates = Enumerable.Range(0, 1 + endDate.Subtract(startDate).Days)
    .Select(offset => startDate.AddDays(offset))
    .ToArray(); 
        
var result = (from a in dates
    join b in 
    (
        from p in players
        group p by p.Date into g
        select new { Date = g.Key, Count = g.Count() }
    ) on a.Date equals b.Date into ab
    from b in ab.DefaultIfEmpty()
    select new { Date = a.Date, Count = b != null ? b.Count : 0 }
);

Sample program

Output

Date: 1/1/2021, Count: 3

Date: 2/1/2021, Count: 0

Date: 3/1/2021, Count: 2

user_80176900

You can use linq to do this as long as it can enumerate through a list or some other IEnumerable. Try this:

var playersSorted = yourlist.GroupBy(x => x.Date)
            .Where(g => g.Any())
            .Select(y => new {Date = y.Key, Count = y.Count()}).ToList();
user_149288930 
var playersgroup = from e in players 
group e by Date into g 
select new { Date= g.Key, NoOfPlayers = g.Count() };
user_26260260
Related