How to pivot a list in C#

Question

I have a list with following structure,

TimeStamp          Name          Value
---------------------------------------     
01/May/2018        Prop1          5
07/May/2018        Prop1          9
01/May/2018        Prop2          8
07/May/2018        Prop2          11
01/May/2018        Prop3          18
07/May/2018        Prop3          7
.....................................................
......................................................
  07/May/2018        Prop(N)      (N)Value

And I am trying to pivot/convert it to following structure,

TimeStamp         Prop1      Prop2      Prop3  ....  PropN                         Value
---------------------------------------------------------------------------------------------
01/May/2018        5           8          18           (N)th Value             31+(N)th Value  
07/May/2018        9           11          7             (N)th Value             27+(N)th Value

Edit: In reality the list is quite big and can have 100 of Names and won't be knowing there values till it's loaded in memory. So it's not possible to check for the names as mentioned by @TheGeneral, though it works if we have limited sets of values and we know them before hand.

I attempted doing this using grouping , but it is probably not the approach.

var pivotList= customersList.GroupBy(cust => new { cust.Name, cust.Timestamp, cust.Value }).Select(x => new  {
                                TimeStamp = x.Key.Timestamp,
                                Circuit = x.Key.Name,
                                NumData = x.Sum(y=>x.Key.Value)
                            });

I have pasted the actual format and not the C# lists for brevity.

what if `01/May/2018 Prop1 ` appears twice? Sum up or take the first? — fubo, Aug 23 '18 at 05:20
Possible duplicate of [Pivot data using LINQ](https://stackoverflow.com/questions/963491/pivot-data-using-linq) — Access Denied, Aug 23 '18 at 06:48

TheGeneral · Accepted Answer · 2018-08-23T05:37:23.250

7

If you know how many props you have beforehand, you could do something like this

var query = myList
    .GroupBy(c => c.TimeStamp)
    .Select(g => new {
        TimeStamp = g.Key,
        Prop1 = g.Where(x => x.Name == "Prop1").Sum(x => x.Value),
        Prop2 = g.Where(x => x.Name == "Prop2").Sum(x => x.Value),
        Prop3 = g.Where(x => x.Name == "Prop3").Sum(x => x.Value),
        Value = g.Sum(c => c.Value)
    });

If you this list of props is dynamic, you are going to have to call pivot at the server and query this manually. or use a sublist and groupby Name

edited Aug 23 '18 at 05:37

answered Aug 23 '18 at 05:25

TheGeneral

79,002
9
103
141

Thanks, It answers the question , but is there a way I can do without checking the property names manually? Because , In reality I will get a list on which I do not have any control and could be hundreds of value – Simsons Aug 23 '18 at 05:31
@Simsons yeah you could use a sublist and groupby Name, best to update your questions and uncheck your correct answer flag so other people can give it a shot – TheGeneral Aug 23 '18 at 05:36
@Simsons to me that's a new question – fubo Aug 23 '18 at 05:38
@fubo, Agree and that's why marked as an answer. But unflagging as answer as TheGeneral suggested. – Simsons Aug 23 '18 at 05:40

MistyK · Answer 2 · 2018-08-23T14:12:43.770

I'll add my 5 cents to this question. I have implemented a simple pivot table class which does exactly what you need.

 public static class PivotTable
    {
        public static PivotTable<TRow, TColumn, TValue> Create<TRow, TColumn, TValue>(Dictionary<TRow, Dictionary<TColumn, TValue>> dictionary)
        where TRow : IComparable, IEquatable<TRow>
        where TColumn : IComparable, IEquatable<TColumn>
        {
            return new PivotTable<TRow, TColumn, TValue>(dictionary);
        }
    }
    public class PivotTable<TRow, TColumn, TValue>
        where TRow : IComparable, IEquatable<TRow>
        where TColumn : IComparable, IEquatable<TColumn>
    {
        private readonly Dictionary<TRow, Dictionary<TColumn, TValue>> _dictionary;

        public PivotTable(Dictionary<TRow, Dictionary<TColumn, TValue>> dictionary)
        {
            _dictionary = dictionary;
        }

        public bool HasValue(TRow row, TColumn col)
        {
            return _dictionary.ContainsKey(row) && _dictionary[row].ContainsKey(col);
        }

        public TValue GetValue(TRow row, TColumn col)
        {
            return _dictionary[row][col];
        }

        public string Print()
        {
            var separator = " ";
            var padRight = 15;

            var rows = _dictionary.Keys;
            var columns = _dictionary.SelectMany(x => x.Value.Keys).Distinct().OrderBy(x => x).ToList();

            var sb = new StringBuilder();

            var columnsRow = new[] { "" }.ToList();

            columnsRow.AddRange(columns.Select(x => x.ToString()));

            sb.AppendLine(string.Join(separator, columnsRow.Select(x => x.PadRight(padRight))));

            foreach (var row in rows.OrderBy(x => x))
            {
                sb.Append(row.ToString().PadRight(padRight)).Append(" ");

                foreach (var col in columns.OrderBy(x => x))
                {
                    var val = HasValue(row, col) ? GetValue(row, col).ToString() : default(TValue).ToString();
                    sb.Append(val.PadRight(padRight)).Append(" ");
                }
                sb.AppendLine();
            }

            return sb.ToString();
        }
}

        public class Element
        {
            public DateTime TimeStamp { get; set; }
            public string Name { get; set; }
            public int Value { get; set; }
        }

 public static class Extensions
    {
        public static PivotTable<TRow, TColumn, TValue> ToPivot<TItem, TRow, TColumn, TValue>(
            this IEnumerable<TItem> source,
            Func<TItem, TRow> rowSelector,
            Func<TItem, TColumn> colSelector,
            Func<IEnumerable<TItem>, TValue> aggregatFunc
        )
            where TRow : IComparable, IEquatable<TRow>
            where TColumn : IComparable, IEquatable<TColumn>
        {
            var dic = source
                .GroupBy(rowSelector)
                .ToDictionary(x => x.Key, x => x.GroupBy(colSelector).ToDictionary(y => y.Key, y => aggregatFunc(y)));

            return PivotTable.Create(dic);
        }

And then you can call it on any type implementing IEnumerable. You can select how elements should be aggregated. It also supports pretty printing, getting elements by row/col.

In your case you need to do this:

 var elements = new List<Element>();
            //fill in with some data
 var pivot = elements.ToPivot(x => x.TimeStamp, x => x.Name, x => x.Sum(y => y.Value));

The core function is this.

var dic = source
                .GroupBy(rowSelector)
                .ToDictionary(x => x.Key, x => x.GroupBy(colSelector).ToDictionary(y => y.Key, y => aggregatFunc(y)));

If you don't need the rest of functionality just do this:

 var dic = elements //Dictionary<DateTime,Dictionary<string,int>>
                    .GroupBy(x=>x.TimeStamp)
                    .ToDictionary(x => x.Key, x => x.GroupBy(x=>x.Name).ToDictionary(y => y.Key, y => y=>y.Sum(z=>z.Value));

And then you can access your data by doing e.g this:

var date = new DateTime(2018,6,6);
var prop = "Prop1";

dic[date][prop] // will give you a sum for this particular date and prop

Access Denied · Answer 3 · 2018-08-23T06:59:36.390

 class Program
    {
        static void Main(string[] args)
        {
            var elements = new Element[]
            {
                new Element
                {
                    TimeStamp = new DateTime(2018,5,1),
                    Name = "Prop1",
                    Value = 5,
                },
                 new Element
                {
                    TimeStamp = new DateTime(2018,5,7),
                    Name = "Prop1",
                    Value = 9,
                },
                  new Element
                {
                    TimeStamp = new DateTime(2018,5,1),
                    Name = "Prop2",
                    Value = 8,
                },
                   new Element
                {
                    TimeStamp = new DateTime(2018,5,7),
                    Name = "Prop2",
                    Value = 11,
                },
                    new Element
                {
                    TimeStamp = new DateTime(2018,5,1),
                    Name = "Prop3",
                    Value = 18,
                },
                     new Element
                {
                    TimeStamp = new DateTime(2018,5,7),
                    Name = "Prop3",
                    Value = 18,
                },
            };            
            var pivot = from line in elements
            group line by line.TimeStamp into g
            select new { g.Key, Props=g.Select(el=>new { el.Name, el.Value }).ToArray(), Total = g.Sum(line => line.Value) };
            int propCount = pivot.Max(line => line.Props.Count());
            string[] props = pivot.SelectMany(p => p.Props, (parent, c) => c.Name).Distinct().ToArray();
            Console.Write($"Date\t");
            for (int i = 0; i < propCount; i++)
            {
                Console.Write($"{props[i]}\t");
            }
            Console.Write($"Total");
            Console.WriteLine();

            foreach (var pivotLine in pivot)
            {
                Console.Write($"{pivotLine.Key.ToShortDateString()}\t");
                for (int i = 0; i < propCount; i++)
                {
                    Console.Write($"{pivotLine.Props.FirstOrDefault(p=>p.Name==props[i])?.Value}\t");
                }
                Console.Write($"{pivotLine.Total}\t");
                Console.WriteLine();
            }
        }

        class Element
        {
            public DateTime TimeStamp { get; set; }
            public string Name { get; set; }

            public int Value;
        }

Thanks for the attempt, but it does not modify and transpose the collection. The object is not to write logic on console app and then display, but to pivot/transpose the list. — Simsons, Aug 23 '18 at 06:29
@Simsons pivot object is what you are looking for. I don't think it's possible to flatten inner list without dictionaries or dynamic objects. — Access Denied, Aug 23 '18 at 06:51

How to pivot a list in C#

3 Answers3