question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked Paul-5034 edited

C# Confusion about LINQ Grouping

This is my data on which a LINQ query executed. i have bit confusion about LINQ grouping used there. here is sample code.

 List<Data> _data = new List<Data> 
 { 
     new Data
     {
         Section = "Consensus Model",
         Lineitem = "Net Revenue",
         BrokerCode = "ZB",
         BrokerName = "B Securities",
         Period = "2012 FYA",
         PeriodValue = ""
     },
     new Data
     {
         Section = "Consensus Model",
         Lineitem = "Net Revenue",
         BrokerCode = "ZB",
         BrokerName = "B. Riley Securities",
         Period = "2013 FYA",
         PeriodValue = ""
     },
     new Data
     {
         Section = "Consensus Model",
         Lineitem = "Net Revenue",
         BrokerCode = "ZB",
         BrokerName = "B. Riley Securities",
         Period = "1Q 2014A",
         PeriodValue = "204.45"
     },
     new Data
     {
         Section = "Consensus Model",
         Lineitem = "Net Revenue",
         BrokerCode = "ZB",
         BrokerName = "B. Riley Securities",
         Period = "2Q 2014A",
         PeriodValue = "205.00"
     },
     new Data
     {
         Section = "Consensus Model",
         Lineitem = "Net Revenue",
         BrokerCode = "TU",
         BrokerName = "Cantor Fitzgerald & Co",
         Period = "2012 FYA",
         PeriodValue = "101.33"
     },
     new Data
     {
         Section = "Consensus Model",
         Lineitem = "Net Revenue",
         BrokerCode = "TU",
         BrokerName = "Cantor Fitzgerald & Co",
         Period = "2013 FYA",
         PeriodValue = ""
     },
     new Data
     {
         Section = "Consensus Model",
         Lineitem = "Net Revenue",
         BrokerCode = "TU",
         BrokerName = "Cantor Fitzgerald & Co",
         Period = "1Q 2014A",
         PeriodValue = "204.45"
     },
     new Data
     {
         Section = "Consensus Model",
         Lineitem = "Net Revenue",
         BrokerCode = "TU",
         BrokerName = "Cantor Fitzgerald & Co",
         Period = "2Q 2014A",
         PeriodValue = "201.00"
     },
     new Data
     {
         Section = "Consensus Model",
         Lineitem = "Cost of Goods Sold",
         BrokerCode = "ZB",
         BrokerName = "B. Riley Securities",
         Period = "2012 FYA",
         PeriodValue = ""
     },
     new Data
     {
         Section = "Consensus Model",
         Lineitem = "Cost of Goods Sold",
         BrokerCode = "ZB",
         BrokerName = "B. Riley Securities",
         Period = "2013 FYA",
         PeriodValue = ""
     },
     new Data
     {
         Section = "Consensus Model",
         Lineitem = "Cost of Goods Sold",
         BrokerCode = "ZB",
         BrokerName = "B. Riley Securities",
         Period = "1Q 2014A",
         PeriodValue = "204.45"
     },
     new Data
     {
         Section = "Consensus Model",
         Lineitem = "Cost of Goods Sold",
         BrokerCode = "ZB",
         BrokerName = "B. Riley Securities",
         Period = "2Q 2014A",
         PeriodValue = "201.00"
     },
    
     new Data
     {
         Section = "Consensus Model",
         Lineitem = "Cost of Goods Sold",
         BrokerCode = "TU",
         BrokerName = "Cantor Fitzgerald & Co",
         Period = "2012 FYA",
         PeriodValue = "101.33"
     },
     new Data
     {
         Section = "Consensus Model",
         Lineitem = "Cost of Goods Sold",
         BrokerCode = "TU",
         BrokerName = "Cantor Fitzgerald & Co",
         Period = "2013 FYA",
         PeriodValue = "222.30"
     },
     new Data
     {
         Section = "Consensus Model",
         Lineitem = "Cost of Goods Sold",
         BrokerCode = "TU",
         BrokerName = "Cantor Fitzgerald & Co",
         Period = "1Q 2014A",
         PeriodValue = "784.45"
     },
     new Data
     {
         Section = "Consensus Model",
         Lineitem = "Cost of Goods Sold",
         BrokerCode = "TU",
         BrokerName = "Cantor Fitzgerald & Co",
         Period = "2Q 2014A",
         PeriodValue = "555.00"
     },
 };
    
 var periods = _data.Select(y => y.Period).Distinct().OrderBy(y => y).ToArray();
    
 var results =
     _data
         .GroupBy(
             x => new { x.Section, x.Lineitem, x.BrokerCode, x.BrokerName },
             x => new { x.Period, x.PeriodValue })
         .Select(x => new 
         {
             x.Key,
             Lookup = x.ToLookup(y => y.Period, y => y.PeriodValue),         
         })
         .Select(x => new
         {
             x.Key.Section,
             x.Key.Lineitem,
             x.Key.BrokerCode,
             x.Key.BrokerName,
             Map = periods.ToDictionary(y => y, y => String.Join("|", x.Lookup[y])),
         })
         .ToArray();
        
 var dt = new DataTable();
 dt.Columns.Add("Section");
 dt.Columns.Add("Lineitem");
 dt.Columns.Add("BrokerCode");
 dt.Columns.Add("BrokerName");
 foreach (var period in periods)
 {
     dt.Columns.Add(period);
 }
    
 foreach (var result in results)
 {
     string[] key_values = new string[]
     {
         result.Section,
         result.Lineitem,
         result.BrokerCode,
         result.BrokerName,
     };
        
     string[] period_values = periods.Select(p => result.Map[p]).ToArray()
        
     dt.Rows.Add(key_values.Concat(period_values).ToArray());
 }

i have to group on few fields and those are Section, LineItem, BrokerCode and Period but a guy does the grouping like this way

 var results =
     _data
         .GroupBy(
             x => new { x.Section, x.Lineitem, x.BrokerCode, x.BrokerName },
             x => new { x.Period, x.PeriodValue })
         .Select(x => new 
         {
             x.Key,
             Lookup = x.ToLookup(y => y.Period, y => y.PeriodValue),         
         })
         .Select(x => new
         {
             x.Key.Section,
             x.Key.Lineitem,
             x.Key.BrokerCode,
             x.Key.BrokerName,
             Map = periods.ToDictionary(y => y, y => String.Join("|", x.Lookup[y])),
         })
         .ToArray();

So my question is what kind of grouping is it.... see the below grouping code.

         .GroupBy(
             x => new { x.Section, x.Lineitem, x.BrokerCode, x.BrokerName },
             x => new { x.Period, x.PeriodValue })

Is it two set of grouping ? one set is { x.Section, x.Lineitem, x.BrokerCode, x.BrokerName }
and another set is { x.Period, x.PeriodValue } ?

please guide me how the above grouping will be working ?

Desired output

Section LineItem Broker Code Broker Name 2012 FYA 2013 FYA 1Q 2014A 2Q 2014A
Consensus Model Net Revenue ZB B Securities 204.45 205.00
Consensus Model Net Revenue TU Cantor Fitzgerald 101.33 204.45 201.00
Consensus Model Cost of Goods Sold ZB B Securities 204.45 205.00
Consensus Model Cost of Goods Sold TU Cantor Fitzgerald 101.33 204.45 201.00

Thanks





dotnet-csharp
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@TZacks-2728,

Is it two set of grouping ?

This is just one grouping. The code x => new { x.Period, x.PeriodValue }) will get the correspond data based on the code x => new { x.Section, x.Lineitem, x.BrokerCode, x.BrokerName }.

please guide me how the above grouping will be working ?

What is your desired grouping? Could you describe it in more detail?








0 Votes 0 ·

I want to group on Section, Lineitem, Brokercode and Period.

thanks

0 Votes 0 ·

Is your goal to group by/pivot your table on { Section, ListItem, BrokerCode, BrokerName } ?

then group by Period, then for all distinct Periods you want to push it to a new column in the results and work out some sort of aggregation on PeriodValue?

So for example you used these in your example:

 new Data
 {
     Section = "Consensus Model",
     Lineitem = "Net Revenue",
     BrokerCode = "TU",
     BrokerName = "Cantor Fitzgerald & Co",
     Period = "2012 FYA",
     PeriodValue = "101.33"
 },
 new Data
 {
     Section = "Consensus Model",
     Lineitem = "Net Revenue",
     BrokerCode = "TU",
     BrokerName = "Cantor Fitzgerald & Co",
     Period = "1Q 2014A",
     PeriodValue = "204.45"
 },

If you group on { Section, ListItem, BrokerCode, BrokerName } then this will result in one group. Within this group you have two periods (2012 FYA, 1Q 2014A) so you could do a nested GroupBy on Period. If you imagine that the second row above was duplicated, then you would have two rows in the nested group with the value 204.45
so you need to consider how you want to aggregate these PeriodValue properties (for example: average, median, top.)

0 Votes 0 ·
Show more comments

@JackJJun-MSFT Sir still not clear this kind of grouping
.GroupBy(
x => new { x.Section, x.Lineitem, x.BrokerCode, x.BrokerName },
x => new { x.Period, x.PeriodValue })

if possible can you provide small example code where this kind of grouping will be performed.

they can't not write this kind of grouping like this way x => new { x.Section, x.Lineitem, x.BrokerCode, x.BrokerName,x.Period, x.PeriodValue }
as a result we do not have to use two new block for grouping

Sir please provide sample code and use two new block for grouping which i can understand why it is being used and how it is working.

thanks


0 Votes 0 ·

0 Answers