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

How to format code: http://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks Also, what are "input 1" and "ouput table 1"? — Tim Schmelter, Apr 16 '12 at 12:07

Mahmoud Gamal · Answer 1 · 2012-04-16T18:40:39.113

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

Thanks alot but does not solve my problem i need the date(year) values arranged in the column just like pivot (column field) eg Module ProfileType 2012 2013 2014 2015 etc module has the module values ;profile type has "xrate" and "yrate" and 2012 will contain values for xrate and yrate for 2012,2013 will contain values for xrate and yrate for 2013, etc and the years are dynamic — Ugochinyere.cyrine, Apr 16 '12 at 13:46
@Ugochinyere.cyrine, see my edit, and please edit your question with these details and make it more clear. — Mahmoud Gamal, Apr 16 '12 at 15:41
@Ugochinyere.cyrine, Please try this answer and don't hesitate to ask for more explanations and details if you wan, instead of re-asking the same question two more times with the sam uncleared details, therefore you havn't got any useful answer so far. That's how things work here in Stackoverflow. — Mahmoud Gamal, Apr 17 '12 at 10:57

data Transformation using linq

1 Answers1