question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked VitaliyFedorchenko-7544 answered

C# How to convert my List<T> to Pivot datatable

I am curious to know how to convert my List<T> to Pivot datatable.

this is my sample data populated in List.

     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"
         },
     };

Data grouping should be like Section, Lineitem, BrokerCode & Period sample periods are like 2012 FYA or 2013 FYA...2Q 2014A

Desired output would be like below example

 +-----------------+--------------------+-------------+-------------------+----------+----------+-----------+----------+
 |     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 |
 +-----------------+--------------------+-------------+-------------------+----------+----------+-----------+----------+

ToPivotTable() function sample code

     public static DataTable ToPivotTable<T, TColumn, TRow, TData>(
         this IEnumerable<T> source,
         Func<T, TColumn> columnSelector,
         Expression<Func<T, TRow>> rowSelector,
         Func<IEnumerable<T>, TData> dataSelector)
             {
                 DataTable table = new DataTable();
                 var rowName = ((MemberExpression)rowSelector.Body).Member.Name;
                 table.Columns.Add(new DataColumn(rowName));
                 var columns = source.Select(columnSelector).Distinct();
         
                 foreach (var column in columns)
                     table.Columns.Add(new DataColumn(column.ToString()));
         
                 var rows = source.GroupBy(rowSelector.Compile())
                                  .Select(rowGroup => new
                                  {
                                      Key = rowGroup.Key,
                                      Values = columns.GroupJoin(
                                          rowGroup,
                                          c => c,
                                          r => columnSelector(r),
                                          (c, columnGroup) => dataSelector(columnGroup))
                                  });
         
                 foreach (var row in rows)
                 {
                     var dataRow = table.NewRow();
                     var items = row.Values.Cast<object>().ToList();
                     items.Insert(0, row.Key);
                     dataRow.ItemArray = items.ToArray();
                     table.Rows.Add(dataRow);
                 }
         
                 return table;
             }

Please guide me how to customize the code in the function ToPivotTable()

As a result i can use that function to get desired output.

if ToPivotTable() function code customization not possible then also tell me how to group data using LINQ to display data as i mention in the post.

Thanks









dotnet-csharp
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.

TimonYang-MSFT avatar image
0 Votes"
TimonYang-MSFT answered TimonYang-MSFT commented

Please try this code:

        List<Data> datas = GetDatas();
         var query =
                 from c in datas
                 group c by new{c.Section,c.Lineitem,c.BrokerCode} into gcs
                 select new
                 {
                     Section = gcs.First().Section,
                     Lineitem = gcs.First().Lineitem,
                     BrokerCode = gcs.First().BrokerCode,
                     BrokerName = gcs.First().BrokerName,
                     Period = gcs.Select(d =>d.Period),
                     PeriodValue = gcs.Select(d =>d.PeriodValue),
                 };
         DataTable dataTable = new DataTable();

         dataTable.Columns.Add("Section", typeof(string));
         dataTable.Columns.Add("Lineitem", typeof(string));
         dataTable.Columns.Add("BrokerCode", typeof(string));
         dataTable.Columns.Add("BrokerName", typeof(string));
         //int columnNum = query.First().Period.Count();
         foreach (var item in query.First().Period)
         {
             dataTable.Columns.Add(item, typeof(string));
         }
         foreach (var item in query)
         {
             DataRow dataRow = dataTable.NewRow();
             dataRow["Section"] = item.Section;
             dataRow["Lineitem"] = item.Lineitem;
             dataRow["BrokerCode"] = item.BrokerCode;
             dataRow["BrokerName"] = item.BrokerName;
             int nums = 4;
             foreach (var PeriodValue in item.PeriodValue)
             {
                 dataRow[nums++] = PeriodValue;
             }
             dataTable.Rows.Add(dataRow);
         }

133344-capture.png


If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


capture.png (18.2 KiB)
1.txt (8.3 KiB)
· 5
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.

Here you hard code the period which is not good for my scenario because i do not know how many periods will be there. period is not fixed.

                          FYA2012 = gcs.Where(d => d.Period == "2012 FYA").First().PeriodValue,
                          FYA2013 = gcs.Where(d => d.Period == "2013 FYA").First().PeriodValue,
                          FYA2014Q1 = gcs.Where(d => d.Period == "1Q 2014A").First().PeriodValue,
                          FYA2014Q2 = gcs.Where(d => d.Period == "2Q 2014A").First().PeriodValue,

Sir please discuss a solution where periods will not be hard coded.

again

Sir i was expecting to achieve this output using function ToPivotTable() but ToPivotTable() does not accept multiple fields name on which group will be performed. if you please change the code inside ToPivotTable() function.

thanks

0 Votes 0 ·

@TZacks-2728
Are the first four columns fixed, right?
I wrote a new piece of code based on this, and then revised my answer, please see if it can work for you now.

0 Votes 0 ·

@TZacks-2728
May I know if you have a chance to check my new code?
I think it should be closer to your needs now, if you still have any questions, please let me know.

0 Votes 0 ·

Yes sir i will try but in grouping one field is missing which is period.

group c by new{c.Section,c.Lineitem,c.BrokerCode,c.Period} into gcs

a records is uniquely identified by Section, Li, BrokerCode & Period.

please alter the code and tell me right output is coming?

Thanks

0 Votes 0 ·

@TZacks-2728
We cannot add Period to the grouping condition.
I use three conditional groupings based on the current test data. Only in this way can it be divided into four groups of data that meet the requirements. Adding or removing any of them will lead to success.
In fact, this is not good. I still think that if we can add a new field ID to form a unique identifier, it would be better to use it for grouping.

0 Votes 0 ·
VitaliyFedorchenko-7544 avatar image
0 Votes"
VitaliyFedorchenko-7544 answered

A bit outdated answer - however it might be useful to know about this open source library that can pivot data dynamically: https://github.com/nreco/pivotdata

For example, for your list it is enough to use this code snippet:

 var pvtData = new PivotData(new [] {"Section","Lineitem", "BrokerCode", "BrokerName", "Period"}, new SumAggregatorFactory("PeriodValue") );
 pvtData.ProcessData( _data , new ObjectMember().GetValue );

In particular, to get pivoted result as DataTable: https://www.nrecosite.com/pivotdata/pivot-datatable.aspx

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.