CODEWITHSUNDEEP
c#asp.netasp.net-mvclinqgroup-by
Jadin Stojadin
Jadin Stojadin

Reputation: 225

Grouping data between ranges using LINQ in C#

I have made a following code to create me a range between two numbers, and data is separated in 7 columns: 

  private List<double> GetRangeForElements(double minPrice, double maxPrice)
        {
            double determineRange = Math.Round(maxPrice / 7.00d, 3);
            var ranges = new List<double>();
            ranges.Insert(0, Math.Round(minPrice, 3));
            ranges.Insert(1, determineRange);
            for (int i = 2; i < 8; i++)
            {
                ranges.Insert(i, Math.Round(determineRange * i, 3));
            }
            return ranges;
        }

Now I have list of ranges when I call the method:

var ranges = GetRangeForElements(1,1500);

On the other side now I have the data (a list) that contains following data (properties):

public double TransactionPrice {get;set;}

public int SaleAmount {get;set;}

Input data would be:

Transaction price    Sale amount

114.5                   4
331.5                   6
169.59                  8
695.99                  14
1222.56                 5

Generated range for between 1 and 1500 is:

1
214.28
428.57
642.85
857.14
1071.43
1285.71
1500.00

And the desired output would be:

Range                    Sales
(1 - 214.28)             12
(214.28 - 428.57)        6
(428.57 - 642.85)        0
(642.85 - 857.14)        14
(857.14 - 1071.43)       0 
(1071.43 - 1285.71)      5    
(1285.71 - 1500)         0

I've tried something like this:

var priceGroups = _groupedItems.GroupBy(x => ranges.FirstOrDefault(r => r > x.TransactionPrice))
   .Select(g => new { Price = g.Key, Sales = g.Sum(x=>x.Sales) })
   .ToList();

But this doesn't gives me what I want, the results I receive are completely messed up (I was able to verify the data and results manually)...

Can someone help me out?

P.S. guys, the ranges that have no sales should simply have value set to 0...

@blinkenknight here's a pic of what I'm saying, min price is = 2.45 , max price = 2.45

and the output of the 2nd method you posted is:

enter image description here

Upvotes: 3

Views: 1459

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726489

Since GetRangeForElements returns a List<double>, you cannot group by it. However, you can group by range index, and then use that index to get the range back:

var rangePairs = ranges.Select((r,i) => new {Range = r, Index = i}).ToList();
var priceGroups = _groupedItems
    .GroupBy(x => rangePairs.FirstOrDefault(r => r.Range >= x.TransactionPrice)?.Index ?? -1)
    .Select(g => new { Price = g.Key >= 0 ? rangePairs[g.Key].Range : g.Max(x => x.TransactionPrice), Sales = g.Sum(x=>x.Sales) })
    .ToList();

Assuming that _groupedItems is a list, you could also start with ranges, and produce the results directly:

var priceGroups = ranges.Select(r => new {
    Price = r
,   Sales = _groupedItems.Where(x=>ranges.FirstOrDefault(y=>y >= x.TransactionPrice) == r).Sum(x => x.Sales)
});

Note: Good chances are, your GetRangeForElements has an error: it assumes that minPrice is relatively small in comparison to maxPrice / 7.00d. To see this problem, consider what would happen if you pass minPrice=630 and maxPrice=700: you will get 630, 100, 200, 300, ... instead of 630, 640, 650, .... To fix this problem, compute (maxPrice - minPrice) / 7.00d and use it as a step starting at minPrice:

 private List<double> GetRangeForElements(double minPrice, double maxPrice) {
     double step = (maxPrice - minPrice) / 7.0;
     return Enumerable.Range(0, 8).Select(i => minPrice + i*step).ToList();
 }

Upvotes: 3

Related Questions