Trying to pivot data using linq

Question

I have the following class structure:

 public class TaskLog
 {
    public DateTime LogDate { get; set; }
    public string Robot { get; set; }
    public string Task { get; set; }
    public Enums.ProbeDataState State { get; set; }

    public TaskLog()
    {
    }
}

I created a generic list as follows:

List<TaskLog> Logs;

My output:

Robot   Date                    State
---------------------------------------------
aaa     8/5/2013 12:00:00 AM    Task:1=fileDeltaFailed
aaa     8/5/2013 12:00:00 AM    Task:2=fileDeltaFailed
aaa     8/5/2013 12:00:00 AM    Task:4=fileDeltaFailed
bbb     8/5/2013 12:00:00 AM    Task:1=fileDeltaFailed
bbb     8/5/2013 12:00:00 AM    Task:2=fileDeltaFailed
bbb     8/5/2013 12:00:00 AM    Task:4=fileDeltaFailed

However I would like to group the tasks and state as follows:

                                Tasks
Robot   Date                    1                 2                4
aaa     8/5/2013 12:00:00 AM    fileDeltaFailed   fileDeltaFailed  fileDeltaFailed 
bbb     8/5/2013 12:00:00 AM    fileDeltaFailed   fileDeltaFailed  fileDeltaFailed

I tried using the groupby with no luck, and quite stuck.

e.g.

 var dataQuery = Logs.Where(w => w.LogDate >= start && w.LogDate <= finish).GroupBy(g => g.LogDate).Select(t => new
            {
                LogDate = t.Key,
                Details = t.OrderBy(o => o.Robot)
            }).ToList();

Sounds like you want to do something similar to http://stackoverflow.com/q/7325278/2460812 — brader24, Aug 14 '13 at 17:17
@brader24 A pivot table is radically different from just a group by. — Servy, Aug 14 '13 at 17:21
So you want to pivot the tasks? Every taks should be a column? — Tim Schmelter, Aug 14 '13 at 17:21
What is the correlation on a Robot-Task pair? Could be none, none or 1, none to many? — gunr2171, Aug 14 '13 at 17:28
I recognize that creating the output is more complex than just creating the group by. I suspect that it is part of a possible solution as Pete seemed to suggest. This is also a possible duplicate to http://stackoverflow.com/q/963491/2460812 — brader24, Aug 14 '13 at 17:46

score 5 · Accepted Answer · answered Aug 14 '13 at 18:15

If I understand the question, you want a mapping of Task to State for each Robot. You could group by Robot and select a dictionary for each group:

Logs.GroupBy(t => t.Robot).Select(g => new {
    Robot = g.Key,
    TaskStates = g.ToDictionary(t => t.Task, t => t.State)
})

This assumes that task names are unique for each robot (ToDictionary would throw an exception otherwise).

You could also add another level of grouping for dates:

Logs.GroupBy(t => t.LogDate).Select(g => new {
    Date = g.Key,
    Details = g.GroupBy(t => t.Robot).Select(g => new {
        Robot = g.Key,
        TaskStates = g.ToDictionary(t => t.Task, t => t.State)
    }).ToList()
})

Note that the Details property is essentially equivalent to my first example, the only difference being that it queries the outer grouping instead of the whole sequence. The result is a sequence of {Date, Details} where each "detail" is a list of {Robot, TaskStates}.

I haven't tested this, so let me know if there are any bugs I missed.

Your example is exactly what I need! I was able to get the desired results. — Pete Dragun, Aug 20 '13 at 15:10

King King · Answer 2 · 2013-08-15T02:56:42.707

var result = Logs.GroupBy(x=> new {x.Robot, x.Date})                     
                 .Select(g=> {
                               var a = g.ToList();
                               return
                               new {
                                 a[0].Robot,
                                 a[0].Date,
                                 Task1 = a[0].State,
                                 Task2 = a[1].State,
                                 Task4 = a[2].State
                               };
                             });

Suppose you have to define a fixed number of Tasks and to make it safer, we can do something like this:

var result = Logs.GroupBy(x=> new {x.Robot, x.Date})
                 .Select(g=> { 
                               var c = g.Count();
                               var a = g.ToList();
                               return 
                               new {
                                    a[0].Robot,
                                    a[0].Date,
                                    Task1 = a[0].State,
                                    Task2 = c > 1 ? a[1].State : "",
                                    Task4 = c > 2 ? a[2].State : ""
                                   };
                             });

score 0 · Answer 3 · answered Aug 14 '13 at 17:38

0

try this:

var newData = from w in Logs
              where w.LogDate >= start && w.LogDate <= finish
              group w by w.LogDate into g
              select new 
              {
                  LogDate = g.Key,
                  Details = g.ToList().OrderBy(o => o.Robot)
              };

The query newData contains a list of objects with LogDate and Detais (List of tasks ordered by Robot).

answered Aug 14 '13 at 17:38

Julio Borges

661
14
29

Someone posted another solution and I managed to use it before they deleted it :( – Pete Dragun Aug 14 '13 at 17:59
If you will have multiple dates per robot, you will need to group by both robot and date. This question shows how to do this: http://stackoverflow.com/q/847066/2460812 – brader24 Aug 14 '13 at 18:03
Please @PeteDragun close the question – Julio Borges Aug 14 '13 at 18:16
@JulioBorges why delete the question? – King King Aug 14 '13 at 19:03

Trying to pivot data using linq

3 Answers3

Linked