CODEWITHSUNDEEP
c#linqpivot
Arnab
Arnab

Reputation: 2354

How to create two dim array after pivot in C#

I'm receiving a Json doc from 3rd party which when deserialized, I have a dynamic object list in following format

period       key          val
"2013-3"     0            5
"2013-3"     1            6
"2013-3"     2            5  
"2013-3"     3            6
"2013-3"     4            15
"2013-3"     5            25
"2013-3"     ">5"         55
"2013-3"    "mykey1"      25
"2013-3"    "mykey2"       6
"2013-4"     0            15
"2013-4"     1            16
"2013-4"     2            25  
"2013-4"     3            36
 and so on...

First, I need to remove mykey1 and mykey2 from this data which I should be able to do using a where clause in LinQ 

.Where(w => (string)w.key != "mykey1" && (string)w.key != "mykey2")

Once done, I need to pivot data with columns as key (ordered 0 to 5 and then string ">5") and get a two dimensional array int[,] of the values. this I have no clue as to how!

So result could be something similar as below..

int[,] activities= { 
                      {5,6,5,6,15,25,55},
                      {15,16,25,36,21,22,23},
                      {31,32,33,34,31,32,33},
                      {41,42,43,44,41,42,43},
                      {11,12,13,14,11,12,13},
                      {21,22,23,24,21,22,23},
                      {31,32,33,34,31,32,33},
                      {41,42,43,44,41,42,43},
                      {11,12,13,14,11,12,13},
                      {21,22,23,24,21,22,23},
                      {31,32,33,34,31,32,33},
                      {41,42,43,44,41,42,43}
                      };

Upvotes: 1

Views: 409

Answers (2)

shA.t
shA.t

Reputation: 16968

I think you want something like this:

var result = list.GroupBy(g => g.period).Select(c => new
        {
            period = c.Key,
            s0 = c.Sum(f=> f.key.ToString() == "0" ? f.val : 0),
            s1 = c.Sum(f=> f.key.ToString() == "1" ? f.val : 0),
            s2 = c.Sum(f=> f.key.ToString() == "2" ? f.val : 0),
            s3 = c.Sum(f=> f.key.ToString() == "3" ? f.val : 0),
            s4 = c.Sum(f=> f.key.ToString() == "4" ? f.val : 0),
            s5 = c.Sum(f=> f.key.ToString() == "5" ? f.val : 0),
            s6 = c.Sum(f=> f.key.ToString() == ">5" ? f.val : 0),
        })
        .ToList();

result will be like this:

{ period = "2013-3", s0 = 5,  s1 = 6,  s2 = 5,  s3 = 6,  s4 = 15, s5 = 25, s6 = 55 }
{ period = "2013-4", s0 = 15, s1 = 16, s2 = 25, s3 = 36, s4 = 21, s5 = 22, s6 = 23 }
...

The dynamic version can be something like this:

var result = list.GroupBy(g => g.period)
             .ToDictionary(k => k.Key, l =>
                list.Where(w => w.period == l.Key)
                    .GroupBy(k => k.key.ToString())
                    .OrderBy(o => o.Key)
                    .Select(s => s.Sum(f => f.val)));

That returns a dictionary of period keys and list of summed values based on their keys ;).

Edit :

var result = list.GroupBy(g => g.period)
            .Select(l =>
                list.Where(w => w.period == l.Key && !w.key.In(new[] {"myKey1", "myKey2"}))
                    .GroupBy(k => k.key == ">5" ? "6" : k.key.ToString())
                    .OrderBy(o => o.Key)
                    .Select(s => s.Sum(f => f.val))
                    .ToArray())
            .ToArray();

Upvotes: 4

Mat
Mat

Reputation: 1500

I doubt the efficiency of this but it works with any number of items per period. Also you get a int[][] instead of int[,].

var periods = list.Select(w => w.period.ToString()).Distinct().ToList();
List<int[]> matrix = new List<int[]>(periods.Count());

foreach(string p in periods) 
{
    matrix.Add(list.Where(w => w.period.ToString().Equals(p)
                   .Select(w => (int)w.val))
                   .ToArray());
}

int[][] arrays = matrix.ToArray();

Upvotes: 1

Related Questions