Group the DataTable by weeks

Question

I want to group my DataTable by weeks and display it to the DataGridView.

Here is my code:

var week = dt.AsEnumerable().GroupBy(row => Convert.ToDateTime(row["Date"]))
              .Select(g => new
              {
                  Date = g.Key.ToString(),
              }).ToList();
dataGridView1.DataSource = week;

Do you want to convert the dates into weekly buckets? Like "1W", "2W", "3W" and so on? — Skyuppercut, Apr 30 '18 at 07:35
refer https://stackoverflow.com/questions/8561782/how-to-group-dates-by-week and change the logic to handle datatable — Sangram Nandkhile, Apr 30 '18 at 07:39
Which date belongs to which week, do you already have that data or do you plan to add it by code. Once you have Week wise division, then it would be a breeze to group by week — Mrinal Kamboj, Apr 30 '18 at 08:44
I have already the data and it is in days, so i need to group it by week — San, Apr 30 '18 at 08:55
And when you say "by week", do you mean by ISO Standard Week Number, other Week number, every seven days, etc. ? Do you want to count the [first full week](https://learn.microsoft.com/en-us/dotnet/api/system.globalization.calendarweekrule?view=netframework-4.7) or other? — NetMage, Apr 30 '18 at 16:00

Skyuppercut · Answer 1 · 2018-04-30T09:02:21.270

0

You can use something like this:

 private static string[] TimeBucket = { "0D", "1D", "1W", "2W", "1M", "2M", "3M", "6M", "9M", "1Y", "2Y", "3Y", "5Y", "10Y", "15Y", "20Y", "25Y", ">=30Y" };

        private static int[] TimeIntervals = { 0, 1, 7, 2 * 7, 30, 2 * 30, 3 * 30, 6 * 30, 9 * 30, 365, 2 * 365, 3 * 365, 5 * 365, 3650, 15 * 365, 20 * 365, 25 * 365, 30 * 365 };


        private static string ComputeMaturity(DateTime? mat, DateTime? refDate)
        {
            if (!mat.HasValue)
                return "Void";

            int nbd = (mat - refDate).Value.Days;

            for (int pos = 0; pos < TimeBucket.Length; pos++)
                if (nbd < TimeIntervals[pos])
                    return TimeBucket[pos];
            return TimeBucket[(TimeBucket).Length - 1];
        }

During groupby,

var week = dt.AsEnumerable().GroupBy(row => ComputeMaturity(Convert.ToDateTime(row["Date"]),DateTime.Now))
              .Select(g => new
              {
                  Date = (g.Key),
              }).ToList();
dataGridView1.DataSource = week;

edited Apr 30 '18 at 09:02

answered Apr 30 '18 at 07:39

Skyuppercut

332
3
14

You can define your own timebuckets using this answer and group your dates into those buckets – Skyuppercut Apr 30 '18 at 07:40
Thank you sir Skyuppercut ! i will try this – San Apr 30 '18 at 07:45
Sir @Skyuppercut, i tried it but my datatable didnt group. – San Apr 30 '18 at 07:57
It only shows the time buckets,not grouped . Please sir help me – San Apr 30 '18 at 08:08
it will work now. I grouped by first and then converted to buckets. You should convert and then groupby. – Skyuppercut Apr 30 '18 at 09:02

score 0 · Accepted Answer · answered Apr 30 '18 at 16:10

You can use the System.Globalization namespace to find the week of year for a DateTime, using the rules you would like:

using System.Globalization;

DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo;

var week = dt.AsEnumerable().GroupBy(row => 
                  dfi.Calendar.GetWeekOfYear(Convert.ToDateTime(row["Date"]), dfi.CalendarWeekRule, 
                                             dfi.FirstDayOfWeek))
              .Select(g => new
              {
                  Date = g.First()["Date"].ToString(),
              }).ToList();

I couldn't figure out how your Select related to grouping by weeks without more information, so I just chose the first Date of each group.

Group the DataTable by weeks

2 Answers2