CODEWITHSUNDEEP
c#arrayslistlinqdatetime
scgough
scgough

Reputation: 5252

Order date list for comparison

I previously asked this question here: List of Dates ordered in a certain way

I thought the proposed solution was fine until the year ticked over on my date list and encountered an issue.

My date list (in this string based format - this is how the data comes to me from the source API) 

201711
201712
201801
201811
201812
201901

I want to present my data in a bar chart to show 3 months worth of year on year comparison in month order. This would mean I order the list as so

201711
201811
201712
201812
201801
201901

So I can then see year-on-year bars for November, December and Jan in that order.

I've tried the solution at the bottom of the question but it places the order like so (which is not what I want):

201801
201901
201711
201811
201712
201812

For clarity, next month it will need to move forward to be this date list:

the first month I want will always be 2 months before the current one

201712
201812
201801
201901
201802
201902

var rowsInOrder = dateList.OrderBy(n => DateTime.ParseExact(n, "yyyyMM", CultureInfo.CurrentCulture).Month).ThenBy(n=> DateTime.ParseExact(n, "yyyyMM", CultureInfo.CurrentCulture).Year);

Upvotes: 2

Views: 219

Answers (4)

Harald Coppoolse
Harald Coppoolse

Reputation: 30474

So you are stuck with a sequence of objects, where every object has a Date property of type string in the format yyyyMM. and you want to extract some data from it.

Your date format is language independent. It doesn't matter whether your computer is a British one, or a Chinese one. The Date property will always be in format yyyyMM.

This makes it fairly easy to convert it into a DateTime format, which makes it easy to access the Year and Month.

const string dateTimeFormat = "yyyyMM";
CultureInfo provider = CultureInfo.InvariantCulture;
var dateList = ...      // your original list of items with the string Date property

var itemsWithYearMonth = dateList.Select(item => new
{
    DateTime = DateTime.ParseExact(item, dateTimeFormat, provider)
    ... // select other items you need for your bar chart
});

Now, given a StartYear/Month, a NrOfYears and a NrOfMonths you want to group dateTimeItems into groups of same month.

For example, starting at 2018-11, I want groups with four months, for three consecutive years (yeah, yeah, I know in your original request it was only 3 months, 2 years, but why limit yourself to this, let's make your code re-usable):

group 1: 2018-11, 2019-11, 2020-11, 2021-11
group 2: 2018-12, 2019-12, 2020-12, 2021-12
group 3: 2019-01, 2020-01, 2021-01, 2022-01

Bonus-points: we'll pass the year boundary!

So input:

var itemsWithYearMonth = ... // see above
int startYear = ...
int startMonth = ...
int nrOfMonths = ...    
int nrOfYears = ...

We will make groups of items with same months. We don't want all Months of the year, we only want some months. If we want the 3 months starting at month 11, we need to keep the groups with Months 11, 12, 1.

var desiredMonths = Enumerable.Range(startMonth, nrOfMonths)   // example: 11, 12, 13
    .Select(monthNr => 1 + ((monthNr-1) % 12));                // 11, 12, 1

From your input, we don't want all months, we only want the year/month larger than the starting month.

DateTime startMonth = new DateTime(startYear, startMonth, 1);

The easiest way is to keep only the input source of items with date equal or larger than startMonth and take only the first NumberOfYears items of each group. This way you get the correct number of items if you pass the year boundary like I did in my example.

var result = itemsWithYearMonth
    // keep only the items newer than startMonth
    .Where(item => item.DateTime >= startMonth)

    // group by same month:
    .GroupBy(item => item.DateTime.Month,
    (month, itemsWithThisMonth) => new
    {
        Month = month,        // in my example: 11, 12, 1, ...

        // in every group: take the first nrOfYears items:
        Items = itemsWithThisMonth
                // order by ascending year
                .OrderBy(itemWithThisMonth => itemWithThisMonth.Year)
                // no need to order by Month, all Months are equal in this group
                .Take(nrOfYears)
                .ToList(),
     })
     // keep only the desired months:
     .Select(group => desiredMonth.Contains(group.Month));

So now you have groups:

group of month 11, with data of years 2018, 2019, 2020, 2021
group of month 12, with data of years 2018, 2019, 2020, 2021
group of month 01, with data of years 2019, 2020, 2021, 2022

Upvotes: 0

cokceken
cokceken

Reputation: 2076

I could manage to achieve your goal using GroupBy to group months, 

var rowsInOrder = new List<string>();
foreach (var grouping in dates.GroupBy(s =>
        DateTime.ParseExact(s, "yyyyMM", CultureInfo.CurrentCulture).Month))
{
    rowsInOrder.AddRange(grouping.OrderBy(s => s));
};

You can also order months with same logic:

var rowsInOrder = new List<string>();
foreach (var grouping in dates
    .OrderBy(s => DateTime.ParseExact(s, "yyyyMM", CultureInfo.CurrentCulture).Month).GroupBy(s =>
        DateTime.ParseExact(s, "yyyyMM", CultureInfo.CurrentCulture).Month))
{
    rowsInOrder.AddRange(grouping.OrderBy(s => s));
}

Upvotes: 2

Enigmativity
Enigmativity

Reputation: 117124

It seems to me that this is sufficient:

var rowsInOrder = dates.OrderBy(x => x).GroupBy(x => x.Substring(4)).SelectMany(x => x);

There's simply no need to muck around with parsing dates. It's a simple string sort and group this way.

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460208

You can use this Lookup approach that first determines the month-groups:

var monthLookup = dateList
    .Select(s => new{String = s, Date = DateTime.ParseExact(s, "yyyyMM", CultureInfo.CurrentCulture)})
    .OrderBy(x => x.Date)  // not necessary in your sample data but i assume it's desired
    .ToLookup(x=> x.Date.Month);
var rowsInOrder = monthLookup.SelectMany(x => x).Select(x => x.String);

Upvotes: 7

Related Questions