Group by Variable Integer Range Using Linq

Group by variable integer range using Linq

Parameterizing the list of range ceilings...

var ceilings = new[] { 10, 100, 500 };
var groupings = items.GroupBy(item => ceilings.First(ceiling => ceiling >= item));

Group by range using linq

var grouped = ranges.Select(r => new { 
            Price = r, 
            Count = data.Where(x => x.Price >= r).Count() });

And another option (if you have huge data, then grouping is better than enumerating all data for each price group):

var priceGroups = data.GroupBy(x => ranges.FirstOrDefault(r => r > x.Price))
                      .Select(g => new { Price = g.Key, Count = g.Count() })
                      .ToList();

var grouped = ranges.Select(r => new
{
    Price = r,
    Count = priceGroups.Where(g => g.Price > r || g.Price == 0).Sum(g => g.Count)
});

Linq GroupBy A Range of a Property

The logic for generating these groups could be interpreted as follows:

1. Sort the list
2. Go through the list one by one and compute the gap between the current item and the previous item
3. When the gap is less than 3, continue the group; otherwise start a new group

This can be coded like this:

static IEnumerable<IEnumerable<Item>> GroupUp(IEnumerable<Item> input)
{
    var sorted = input.OrderBy( item => int.Parse(item.Amount) ).GetEnumerator();
    int lastValue = int.MinValue;
    var list = new List<Item>();
    while (sorted.MoveNext())
    {
        var current = sorted.Current;
        var currentAmount = int.Parse(current.Amount);
        var gap = currentAmount - lastValue;
        if (gap < 3)
        {
            list.Add(current);
        }
        else
        {
            if (list.Count != 0) yield return list;
            list = new List<Item>();
            list.Add(current);
        }
        lastValue = currentAmount;
    }
    if (list.Count != 0) yield return list;
}

See my working example on DotNetFiddle.

Can you create a grouping based on ranges?

Well, you can certainly express it in LINQ easily:

var x = from value in values
        group value by ranges.Where(x => value >= x)
                             .DefaultIfEmpty()
                             .Last();

But I very much doubt that that will work in LINQ to SQL. Basically you've got to find a simple way of mapping a value to one of those categories.

Complete example:

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

class Test
{
    static void Main()
    {
        int[] values = {100, 110, 120, 130, 140, 150, 160, 170};
        int[] ranges = {115, 145, 180};

        var query = from value in values
                    group value by ranges.Where(x => value >= x)
                                         .DefaultIfEmpty()
                                         .Last();

        foreach (var group in query)
        {
            Console.WriteLine("{0}: {{{1}}}", group.Key, 
                              string.Join(", ", group));
        }
    }
}

Output:

0: {100, 110}
115: {120, 130, 140}
145: {150, 160, 170}

Note that this won't include any categories which don't have any values in.

Also note that this would be simpler (using First() instead of Last()) if you'd be happy to categorize slightly differently:

115: {100, 110}
145: {120, 130, 140}
180: {150, 160, 170}

In other words, if the category was defined by the first range value higher than the row value.

EDIT: Here's a version which gives the empty groups. It's pretty horrible though, IMO:

var query = from range in ranges
            join value in values
            on range equals ranges.Where(x => value >= x)
                                  .DefaultIfEmpty()
                                  .Last() into groups
            select new { Key = range, Values = groups};

foreach (var group in query)
{
    Console.WriteLine("{0}: {{{1}}}", group.Key, 
                      string.Join(", ", group.Values));
}

Group by using linq (range + count)

Try this:

var data = new[] {
 new { Id = 0, Cat = 1, Price = 2 },
 new { Id = 1, Cat = 1, Price = 10 },
 new { Id = 2, Cat = 1, Price = 30 },
 new { Id = 3, Cat = 2, Price = 50 },
 new { Id = 4, Cat = 2, Price = 120 },
 new { Id = 5, Cat = 2, Price = 200 },
 new { Id = 6, Cat = 2, Price = 1024 },
};

var ranges = new[] { 10, 50, 100, 500 };

var result = from r in ranges
        from g in data
        where g.Price >= r
        select new {g.Cat, Price=r};

var groupedData = 
        from d in result
        group d by new{d.Cat, d.Price} into g
        select new{Cat=g.Key.Cat, Price=g.Key.Price, TotalCount=g.Count()};

Group By variable ceiling value with catch-all maximum using LINQ To NHibernate

Edit:

You changed your OP to state that you could use a floor function, but you wanted to find out about a default grouping.

Mathematically a floor function is equivalent. In the case of ceiling, the lower bound for the data they used is presumably 0. In the case of floor, the logical upper bound is positive infinity (effectively it ends up being the highest value the DB supports, since integers don't support the concept of infinity). It gets you where you want to go.

If you want something that might be more applicable to other situations, you could try something like this:

items.GroupBy(item =>
    (
        floors.FirstOrDefault(floor => floor <= item)
            ?? "Default"
        )
        .ToString()
    );

It probably wouldn't work in Linq to NHibernate, as I don't think this would map well to SQL. Instead you could import the whole set into memory first (.ToList()), and then add your grouping as a Linq to Objects query.

It doesn't make a lot of sense to use it in this situation, but it might in the case of non-number-line groupings:

var groups = new HashSet<string>
{
    "Orange",
    "Green",
    "Mauve",
};

items.GroupBy(item =>
    groups.Contains(item.Color)
        ? item.Color
        : "Default"
    );

Before Edit:

You could simply reverse the logic and you'll automatically include everything below a certain value.

var floors = new[] { 250, 100, 50, 0 };
var groupings = items.GroupBy(item => floors.First(floor => floor <= item));

How it works:

• Take an item 270.
  
  The first item in the list would be the first bucket it falls under. This is because 250 <= 270.

• Take an item 99.
  
  The third item in the list would be the first bucket it falls under. 250 is not less than 99. 100 is not less than 99. But 50 is less than 99.

• An item 50 would fall into the third bucket.
  
  It is less than 250 and 100, but equal to 50.

Doesn't quite match the description in your question:

Your group description is a bit broken. You'd have to bucketize them separately for this algorithm to work. There would be a 0-50 bucket, 51-100 bucket, etc. Or a 0-49 bucket, a 50-99 bucket, etc.

A 0-50 bucket, and a 50-100 bucket couldn't exist together.

---

Related Topics