data Transformation using linq

Question

pls i need to arrage my data in datatable input 1 to ouput table 1

  Module  | date(year) |  xrate |  yrate  |  cumrate
----------+------------+--------+---------+---------
 No2_gft       2013        50      65         458
 No2_gft       2014        59      69         458
 No2_gft       2015        59      69         458

 No2_gft       2016        59      65         458

 No3_gft       2013        50      65         458
 No3_gft       2014        59      69         458
 No3_gft       2015        59      69         458

 No4_gft       2016        59      65         458
 No4_gft       2013        50      65         458
 No4_gft       2014        59      69         458
 No4_gft       2015        59      69         458
 No3_gft       2016        59      69         458

to

Module ; Valuetype ; y2013 ; 2014; 2015; 2016

No2_gft ; ; xrate; 50; 65; 458

No2_gft yrate 65 69

just like a transpose

Answer 1

It's unclear how do you want to aggregate or arrange your data, but if you want to aggregate them by year you can do this:

var query = modules.GroupBy( m => m.date)
                   .Select( g => 
                                 new 
                                 { 
                                     y = g.Key, 
                                     xrate = g.FirstOrDefault( x=> x.xrate),
                                     yrate = g.FirstOrDefault( x => x.yrate)
                                 });

This will give you a list of grouped years with the FirstOrDefault() as an aggregate function:

 Year  |   xrate  |  yrate
-------+----------+--------
 2013       50        65
 2014       59        69

Or you can group by an anonymous type:

.GroupBy( m => 
          new
          {
              m.date,
              m.xrate,
              m.yrate
          })

Instead of only m.date, since your date, xrate and yrate are the same for all rows.

Edit: Well, for pivoting this list of modules, I couldn't find any thing shorter and easier than this:

var query = Modules.GroupBy(m => m.ModuleValue)
    .Select(g => new { ModuleValue = g.Key, Values = g });
Func<Module, bool> matchy2013 = m => m.Year == 2013;
Func<Module, bool> matchy2014 = m => m.Year == 2014;
Func<Module, bool> matchy2015 = m => m.Year == 2015;
Func<Module, bool> matchy2016 = m => m.Year == 2016;

IList<NewModule> PivotedModules = new List<NewModule>();

foreach (var item in query)
{
    var xrateRow = new NewModule
    {
        ModuleValue = item.ModuleValue,
         ValueType = "xrate",
         y2013 = item.Values.Where(matchy2013).FirstOrDefault().xrate,
         y2014 = item.Values.Where(matchy2014).FirstOrDefault().xrate,
         y2015 = item.Values.Where(matchy2015).FirstOrDefault().xrate,
         y2016 = item.Values.Where(matchy2016).FirstOrDefault().xrate
     };

     var yrateRow = new NewModule
     {
         ModuleValue = item.ModuleValue,
         ValueType = "yrate",
         y2013 = item.Values.Where(matchy2013).FirstOrDefault().yrate,
         y2014 = item.Values.Where(matchy2014).FirstOrDefault().yrate,
         y2015 = item.Values.Where(matchy2015).FirstOrDefault().yrate,
         y2016 = item.Values.Where(matchy2016).FirstOrDefault().yrate
      };

      var cumrateRow = new NewModule
      {
          ModuleValue = item.ModuleValue,
          ValueType = "cumrate",
          y2013 = item.Values.Where(matchy2013).FirstOrDefault().cumrate,
          y2014 = item.Values.Where(matchy2014).FirstOrDefault().cumrate,
          y2015 = item.Values.Where(matchy2015).FirstOrDefault().cumrate,
          y2016 = item.Values.Where(matchy2016).FirstOrDefault().cumrate
       };
       PivotedModules.Add(xrateRow);
       PivotedModules.Add(yrateRow);
       PivotedModules.Add(cumrateRow);
}

Give it a tray. You should add a new class:

public class NewModule
{
    public string ModuleValue { get; set; }
    public string ValueType { get; set; }
    public int y2013 { get; set; }
    public int y2014 { get; set; }
    public int y2015 { get; set; }
    public int y2016 { get; set; }
}

The PivotedModules should contains the following data:

          Module  |  ValueType |  2013  |  2014  |  2015  | 2016
         ---------+------------+--------+--------+--------+------
         No2_gft       xrate       50       59       59      59
         No2_gft       yrate       65       69       69      65
         No2_gft      cumrate     458       458     458     458

         No3_gft       xrate       50       59       59      59
         No3_gft       yrate       65       69       69      69
         No3_gft      cumrate     458       458     458     458

         No4_gft       xrate       50       59       59      59
         No4_gft       yrate       65       69       69      65
         No4_gft      cumrate     458       458     458     458