pivote - pivot linq c#

Transformación de datos en múltiples columnas

Question

Recibo datos de una fuente de datos que necesito pivotar antes de poder enviar la información a la interfaz de usuario para su visualización. I am new to concept of pivoting & I am not sure how to go about it.

Hay dos partes del problema:

1. formando el encabezado
2. Girando los datos para que coincida con el encabezado

Cosas para tener en cuenta:

3. Tengo ciertas columnas que no quiero pivotar. Yo los llamo static columns.

4. Necesito pivotar ciertas columnas para formar información de encabezado de varios niveles. Yo los llamo dynamic columns

5. Algunas columnas deben pivotarse y contener valores reales. Los llamé value columns .

6. NO existe un límite en el número de dynamic, static and value columns uno pueda tener.

7. Se supone que, cuando lleguen los datos, primero tendremos datos para columnas estáticas, luego columnas dinámicas y luego columnas de valor.

   Ver la imagen adjunta para más información.

Datos ficticios:

class Program
{
static void Main(string[] args)
{
var _staticColumnCount = 2; //Columns that should not be pivoted
var _dynamicColumnCount = 2; // Columns which needs to be pivoted to form header
var _valueColumnCount = 1; //Columns that represent Actual value
var valueColumnIndex = 4; //Assuming index starts with 0;
List<List<string>> headerInfo = new List<List<string>>();
headerInfo.Add(new List<string> {"Product Three", "Item Ten"});
headerInfo.Add(new List<string> {"Product Two", "Item Five"});
headerInfo.Add(new List<string> {"Product Two", "Item Seven"});
headerInfo.Add(new List<string> {"Product Two", "Item Nine"});
headerInfo.Add(new List<string> {"Product One", "Item One"});
headerInfo.Add(new List<string> {"Product One", "Item Two"});
headerInfo.Add(new List<string> {"Product One", "Item Four"});
headerInfo.Add(new List<string> {"Product One", "Item Six"});
headerInfo.Add(new List<string> {"Product One", "Item Eight"});
headerInfo.Add(new List<string> {"Product One", "Item Eleven"});
List<List<string>> data = new List<List<string>>();
data.Add(new List<string> {"Global", "Europe", "Product One", "Item One", "579984.59"});
data.Add(new List<string> {"Global", "North America", "Product One", "Item Two", "314586.73"});
data.Add(new List<string> {"Global", "Asia", "Product One", "Item One", "62735.13"});
data.Add(new List<string> {"Global", "Asia", "Product Two", "Item Five", "12619234.69"});
data.Add(new List<string> {"Global", "North America", "Product Two", "Item Five", "8953713.39"});
data.Add(new List<string> {"Global", "Europe", "Product One", "Item Two", "124267.4"});
data.Add(new List<string> {"Global", "Asia", "Product One", "Item Four", "482338.49"});
data.Add(new List<string> {"Global", "North America", "Product One", "Item Four", "809185.13"});
data.Add(new List<string> {"Global", "Europe", "Product One", "Item Four", "233101"});
data.Add(new List<string> {"Global", "Asia", "Product One", "Item Two", "120561.65"});
data.Add(new List<string> {"Global", "North America", "Product One", "Item Six", "1517359.37"});
data.Add(new List<string> {"Global", "Europe", "Product One", "Item Six", "382590.45"});
data.Add(new List<string> {"Global", "North America", "Product One", "Item Eight", "661835.64"});
data.Add(new List<string> {"Global", "Europe", "Product Three", "Item Three", "0"});
data.Add(new List<string> {"Global", "Europe", "Product One", "Item Eight", "0"});
data.Add(new List<string> {"Global", "Europe", "Product Two", "Item Five", "3478145.38"});
data.Add(new List<string> {"Global", "Asia", "Product One", "Item Six", "0"});
data.Add(new List<string> {"Global", "North America", "Product Two", "Item Seven", "4247059.97"});
data.Add(new List<string> {"Global", "Asia", "Product Two", "Item Seven", "2163718.01"});
data.Add(new List<string> {"Global", "Europe", "Product Two", "Item Seven", "2158782.48"});
data.Add(new List<string> {"Global", "North America", "Product Two", "Item Nine", "72634.46"});
data.Add(new List<string> {"Global", "Europe", "Product Two", "Item Nine", "127500"});
data.Add(new List<string> {"Global", "North America", "Product One", "Item One", "110964.44"});
data.Add(new List<string> {"Global", "Asia", "Product Three", "Item Ten", "2064.99"});
data.Add(new List<string> {"Global", "Europe", "Product One", "Item Eleven", "0"});
data.Add(new List<string> {"Global", "Asia", "Product Two", "Item Nine", "1250"});
}
}

Answer 1

Aquí está la forma de LINQ para hacer esto:

var working =
data
.Select(d => new
{
Region_L1 = d[0],
Region_L2 = d[1],
Product_L1 = d[2],
Product_L2 = d[3],
Value = double.Parse(d[4]),
});
var output =
working
.GroupBy(x => new { x.Region_L1, x.Region_L2 }, x => new { x.Product_L1, x.Product_L2, x.Value })
.Select(x => new { x.Key, Lookup = x.ToLookup(y => new { y.Product_L1, y.Product_L2 }, y => y.Value) })
.Select(x => new
{
x.Key.Region_L1,
x.Key.Region_L2,
P_One_One = x.Lookup[new { Product_L1 = "Product One", Product_L2 = "Item One" }].Sum(),
P_One_Two = x.Lookup[new { Product_L1 = "Product One", Product_L2 = "Item Two" }].Sum(),
P_One_Four = x.Lookup[new { Product_L1 = "Product One", Product_L2 = "Item Four" }].Sum(),
P_One_Six = x.Lookup[new { Product_L1 = "Product One", Product_L2 = "Item Six" }].Sum(),
P_One_Eight = x.Lookup[new { Product_L1 = "Product One", Product_L2 = "Item Eight" }].Sum(),
P_One_Eleven = x.Lookup[new { Product_L1 = "Product One", Product_L2 = "Item Eleven" }].Sum(),
P_Two_Five = x.Lookup[new { Product_L1 = "Product Two", Product_L2 = "Item Five" }].Sum(),
P_Two_Seven = x.Lookup[new { Product_L1 = "Product Two", Product_L2 = "Item Seven" }].Sum(),
P_Two_Nine = x.Lookup[new { Product_L1 = "Product Two", Product_L2 = "Item Nine" }].Sum(),
P_Three_Three = x.Lookup[new { Product_L1 = "Product Three", Product_L2 = "Item Three" }].Sum(),
P_Three_Ten = x.Lookup[new { Product_L1 = "Product Three", Product_L2 = "Item Ten" }].Sum(),
});

Eso da:

Tenga en cuenta que LINQ necesita nombres de campos específicos para las columnas de salida.

Si no se conoce el número de columnas, pero tiene una útil List<List<string>> headerInfo List<List<string>> , puede hacer esto:

var output =
working
.GroupBy(x => new { x.Region_L1, x.Region_L2 }, x => new { x.Product_L1, x.Product_L2, x.Value })
.Select(x => new { x.Key, Lookup = x.ToLookup(y => new { y.Product_L1, y.Product_L2 }, y => y.Value) })
.Select(x => new
{
x.Key.Region_L1,
x.Key.Region_L2,
Headers =
headerInfo
.Select(y => new { Product_L1 = y[0], Product_L2 = y[1] })
.Select(y => new { y.Product_L1, y.Product_L2, Value = x.Lookup[y].Sum() })
.ToArray(),
});

Eso da:

Answer 2

Como podemos predeterminar el tamaño del resultado, podemos definirlo como una matriz multidimensional.

Vamos por un enfoque funcional y tratamos el resultado como un acumulador , así que simplemente escribiremos un reductor (para el método de Aggregate Linq).

Se basará en un diccionario para mapear la transformación de línea horizontal a vertical y otro diccionario de estado para construir sus filas (por supuesto, los diccionarios necesitan un comparador estándar).

Esto supone que tiene un header_info validado , así que, en primer lugar, tuve que corregir la primera entrada que era una duplicación de la última.

Comparado con los otros, la siguiente solución es bastante eficiente (solo toma 1 milisegundo en mi computadora portátil, más de 4 veces más rápido que la respuesta aceptada).

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
namespace pivot
{
class Program
{
static void Main(string[] args)
{
var _staticColumnCount = 2; //Columns that should not be pivoted
var _dynamicColumnCount = 2; // Columns which needs to be pivoted to form header
var _valueColumnCount = 1; //Columns that represent Actual value
var valueColumnIndex = 4; //Assuming index starts with 0;
List<List<string>> headerInfo = new List<List<string>>();
headerInfo.Add(new List<string> { "Product Three", "Item Three" });
headerInfo.Add(new List<string> { "Product Two", "Item Five" });
headerInfo.Add(new List<string> { "Product Two", "Item Seven" });
headerInfo.Add(new List<string> { "Product Two", "Item Nine" });
headerInfo.Add(new List<string> { "Product One", "Item One" });
headerInfo.Add(new List<string> { "Product One", "Item Two" });
headerInfo.Add(new List<string> { "Product One", "Item Four" });
headerInfo.Add(new List<string> { "Product One", "Item Six" });
headerInfo.Add(new List<string> { "Product One", "Item Eight" });
headerInfo.Add(new List<string> { "Product One", "Item Eleven" });
headerInfo.Add(new List<string> { "Product Three", "Item Ten" });
List<List<string>> data = new List<List<string>>();
data.Add(new List<string> { "Global", "Europe", "Product One", "Item One", "579984.59" });
data.Add(new List<string> { "Global", "North America", "Product One", "Item Two", "314586.73" });
data.Add(new List<string> { "Global", "Asia", "Product One", "Item One", "62735.13" });
data.Add(new List<string> { "Global", "Asia", "Product Two", "Item Five", "12619234.69" });
data.Add(new List<string> { "Global", "North America", "Product Two", "Item Five", "8953713.39" });
data.Add(new List<string> { "Global", "Europe", "Product One", "Item Two", "124267.4" });
data.Add(new List<string> { "Global", "Asia", "Product One", "Item Four", "482338.49" });
data.Add(new List<string> { "Global", "North America", "Product One", "Item Four", "809185.13" });
data.Add(new List<string> { "Global", "Europe", "Product One", "Item Four", "233101" });
data.Add(new List<string> { "Global", "Asia", "Product One", "Item Two", "120561.65" });
data.Add(new List<string> { "Global", "North America", "Product One", "Item Six", "1517359.37" });
data.Add(new List<string> { "Global", "Europe", "Product One", "Item Six", "382590.45" });
data.Add(new List<string> { "Global", "North America", "Product One", "Item Eight", "661835.64" });
data.Add(new List<string> { "Global", "Europe", "Product Three", "Item Three", "0" });
data.Add(new List<string> { "Global", "Europe", "Product One", "Item Eight", "0" });
data.Add(new List<string> { "Global", "Europe", "Product Two", "Item Five", "3478145.38" });
data.Add(new List<string> { "Global", "Asia", "Product One", "Item Six", "0" });
data.Add(new List<string> { "Global", "North America", "Product Two", "Item Seven", "4247059.97" });
data.Add(new List<string> { "Global", "Asia", "Product Two", "Item Seven", "2163718.01" });
data.Add(new List<string> { "Global", "Europe", "Product Two", "Item Seven", "2158782.48" });
data.Add(new List<string> { "Global", "North America", "Product Two", "Item Nine", "72634.46" });
data.Add(new List<string> { "Global", "Europe", "Product Two", "Item Nine", "127500" });
data.Add(new List<string> { "Global", "North America", "Product One", "Item One", "110964.44" });
data.Add(new List<string> { "Global", "Asia", "Product Three", "Item Ten", "2064.99" });
data.Add(new List<string> { "Global", "Europe", "Product One", "Item Eleven", "0" });
data.Add(new List<string> { "Global", "Asia", "Product Two", "Item Nine", "1250" });
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
Reducer reducer = new Reducer();
reducer.headerCount = headerInfo.Count;
reducer.headerCount = headerInfo.Count;
var resultCount = (int)Math.Ceiling((double)data.Count / (double)reducer.headerCount);
ValueArray[,] results = new ValueArray[resultCount, _staticColumnCount + reducer.headerCount];
reducer.headerDict = new Dictionary<IEnumerable<string>, int>(new MyComparer());
reducer.skipCols = _staticColumnCount;
reducer.headerKeys = _dynamicColumnCount;
reducer.rowDict = new Dictionary<IEnumerable<string>, int>(new MyComparer());
reducer.currentLine = 0;
reducer.valueCount = _valueColumnCount;
for (int i = 0; i < reducer.headerCount; i++)
{
reducer.headerDict.Add(headerInfo[i], i);
}
results = data.Aggregate(results, reducer.reduce);
stopwatch.Stop();
Console.WriteLine("millisecs: " + stopwatch.ElapsedMilliseconds);
for (int i = 0; i < resultCount; i++)
{
var curr_header = new string[reducer.headerCount];
IEnumerable<string> curr_key = null;
for (int j = 0; j < reducer.headerCount; j++)
{
curr_header[j] = "[" +
String.Join(",", (results[i, reducer.skipCols + j]?.values) ?? new string[0])
+ "]";
curr_key = curr_key ?? (results[i, reducer.skipCols + j]?.row_keys);
}
Console.WriteLine(String.Join(",", curr_key)
+ ": " + String.Join(",", curr_header)
);
}
Console.ReadKey();
// if you want to compare it to the accepted answer
stopwatch.Reset();
stopwatch.Start();
var pivotData = data.ToPivot(2, 2, 1); // with all needed classes/methods
stopwatch.Stop();
Console.WriteLine("millisecs: " + stopwatch.ElapsedMilliseconds);
Console.ReadKey();
}
internal class ValueArray
{
internal IEnumerable<string> row_keys;
internal string[] values;
}
internal class Reducer
{
internal int headerCount;
internal int skipCols;
internal int headerKeys;
internal int valueCount;
internal Dictionary<IEnumerable<string>, int> headerDict;
internal Dictionary<IEnumerable<string>, int> rowDict;
internal int currentLine;
internal ValueArray[,] reduce(ValueArray[,] results, List<string> line)
{
var header_col = headerDict[line.Skip(skipCols).Take(headerKeys)];
var row_keys = line.Take(skipCols);
var curr_values = new string[valueCount];
for (int i = 0; i < valueCount; i++)
{
curr_values[i] = line[skipCols + headerKeys + i];
}
if (rowDict.ContainsKey(row_keys))
{
results[rowDict[row_keys], skipCols + header_col] = new ValueArray();
results[rowDict[row_keys], skipCols + header_col].row_keys = row_keys;
results[rowDict[row_keys], skipCols + header_col].values = curr_values;
}
else
{
rowDict.Add(row_keys, currentLine);
results[currentLine, skipCols + header_col] = new ValueArray();
results[currentLine, skipCols + header_col].row_keys = row_keys;
results[currentLine, skipCols + header_col].values = curr_values;
currentLine++;
}
return results;
}
}
public class MyComparer : IEqualityComparer<IEnumerable<string>>
{
public bool Equals(IEnumerable<string> x, IEnumerable<string> y)
{
return x.SequenceEqual(y);
}
public int GetHashCode(IEnumerable<string> obj)
{
return obj.First().GetHashCode();
}
}
}
}

Answer 3

Lo que usted llama static columns generalmente se llama grupos de filas , dynamic columns - grupos de value columns y value columns valores - agregados de valores o valores simples.

Para lograr el objetivo, sugeriría la siguiente estructura de datos simple:

public class PivotData
{
public IReadOnlyList<PivotValues> Columns { get; set; }
public IReadOnlyList<PivotDataRow> Rows { get; set; }
}
public class PivotDataRow
{
public PivotValues Data { get; set; }
public IReadOnlyList<PivotValues> Values { get; set; }
}

El miembro Columns de PivotData representará lo que usted llama encabezado , mientras que el miembro Row - una lista de objetos PivotDataRow con el miembro Data contiene los valores del grupo de filas y los Values - los valores para el índice Columns correspondiente ( PivotDataRow.Values siempre tendrá el mismo Count como PivotData.Columns.Count ).

La estructura de datos anterior es serializable / deserializable a JSON (probado con Newtosoft.Json) y se puede usar para completar la interfaz de usuario con el formato deseado.

La estructura de datos central utilizada para representar los valores de grupo de filas, los valores de grupo de columnas y los valores agregados es la siguiente:

public class PivotValues : IReadOnlyList<string>, IEquatable<PivotValues>, IComparable<PivotValues>
{
readonly IReadOnlyList<string> source;
readonly int offset, count;
public PivotValues(IReadOnlyList<string> source) : this(source, 0, source.Count) { }
public PivotValues(IReadOnlyList<string> source, int offset, int count)
{
this.source = source;
this.offset = offset;
this.count = count;
}
public string this[int index] => source[offset + index];
public int Count => count;
public IEnumerator<string> GetEnumerator()
{
for (int i = 0; i < count; i++)
yield return this[i];
}
IEnumerator IEnumerable.GetEnumerator() => GetEnumerator();
public override int GetHashCode()
{
unchecked
{
var comparer = EqualityComparer<string>.Default;
int hash = 17;
for (int i = 0; i < count; i++)
hash = hash * 31 + comparer.GetHashCode(this[i]);
return hash;
}
}
public override bool Equals(object obj) => Equals(obj as PivotValues);
public bool Equals(PivotValues other)
{
if (this == other) return true;
if (other == null) return false;
var comparer = EqualityComparer<string>.Default;
for (int i = 0; i < count; i++)
if (!comparer.Equals(this[i], other[i])) return false;
return true;
}
public int CompareTo(PivotValues other)
{
if (this == other) return 0;
if (other == null) return 1;
var comparer = Comparer<string>.Default;
for (int i = 0; i < count; i++)
{
var compare = comparer.Compare(this[i], other[i]);
if (compare != 0) return compare;
}
return 0;
}
public override string ToString() => string.Join(", ", this); // For debugging
}

Básicamente, representa un rango (porción) de una lista de string con igualdad y semántica de comparación de órdenes. El primero permite utilizar los operadores LINQ basados ​​en hash eficientes durante la transformación de pivote mientras que el último permite la clasificación opcional. Además, esta estructura de datos permite una transformación eficiente sin asignar nuevas listas, al mismo tiempo que contiene las listas reales cuando se deserializa desde JSON.

(la comparación de igualdad se proporciona implementando la IEquatable<PivotValues> - Métodos GetHashCode e Equals . Al hacerlo, permite tratar dos instancias de clase PivotValues como iguales en función de los valores en el rango especificado dentro de los elementos List<string> de la List<List<string>> entrada List<List<string>> . Similar, el orden se proporciona implementando la interfaz IComparable<PivotValues> - Método CompareTo )

La transformación en sí es bastante simple:

public static PivotData ToPivot(this List<List<string>> data, int rowDataCount, int columnDataCount, int valueDataCount)
{
int rowDataStart = 0;
int columnDataStart = rowDataStart + rowDataCount;
int valueDataStart = columnDataStart + columnDataCount;
var columns = data
.Select(r => new PivotValues(r, columnDataStart, columnDataCount))
.Distinct()
.OrderBy(c => c) // Optional
.ToList();
var emptyValues = new PivotValues(new string[valueDataCount]); // For missing (row, column) intersection
var rows = data
.GroupBy(r => new PivotValues(r, rowDataStart, rowDataCount))
.Select(rg => new PivotDataRow
{
Data = rg.Key,
Values = columns.GroupJoin(rg,
c => c,
r => new PivotValues(r, columnDataStart, columnDataCount),
(c, vg) => vg.Any() ? new PivotValues(vg.First(), valueDataStart, valueDataCount) : emptyValues
).ToList()
})
.OrderBy(r => r.Data) // Optional
.ToList();
return new PivotData { Columns = columns, Rows = rows };
}

Primero, las columnas (encabezados) se determinan con el operador LINQ Distinct simple. Luego, las filas se determinan agrupando el conjunto fuente por columnas de fila. Los valores dentro de cada agrupación de filas están determinados por la unión externa de las Columns con el contenido de agrupación.

Debido a nuestra implementación de la estructura de datos, la transformación LINQ es bastante eficiente (tanto para el espacio como para el tiempo). El orden de columnas y filas es opcional, simplemente quítelo si no lo necesita.

Prueba de muestra con sus datos ficticios:

var pivotData = data.ToPivot(2, 2, 1);
var json = JsonConvert.SerializeObject(pivotData);
var pivotData2 = JsonConvert.DeserializeObject<PivotData>(json);

Answer 4

Puede usar la biblioteca NReco PivotData para crear tablas dinámicas por cualquier número de columnas de la siguiente manera (no olvide instalar el paquete nuget "NReco.PivotData"):

// rows in dataset are represented as ''arrays''
// lets define ''field name'' -> ''field index'' mapping
var fieldToIndex = new Dictionary<string,int>() {
{"Region L1", 0},
{"Region L2", 1},
{"Product L1", 2},
{"Product L2", 3},
{"Val", 4}
};
// create multidimensional dataset
var pvtData = new PivotData(
// group by 4 dimensions
new[]{"Region L1", "Region L2", "Product L1", "Product L2"},
// value (use CompositeAggregatorFactory for multiple values)
new SumAggregatorFactory("Val") );
pvtData.ProcessData(data, (row, field) => ((IList)row)[fieldToIndex[field]] );
// create pivot table data model by the grouped data
var pvtTbl = new PivotTable(
// dimensions for rows
new[] {"Region L1", "Region L2"},
// dimensions for columns
new[] {"Product L1", "Product L2"},
pvtData);
// now you can iterate through ''pvtTbl.RowKeys'' and ''pvtTbl.ColumnKeys''
// to get row/column header labels and use ''pvtTbl.GetValue()''
// or ''pvtTbl[]'' to pivot table get values
// you can easily render pivot table to HTML (or Excel, CSV) with
// components from PivotData Toolkit (NReco.PivotData.Extensions assembly):
var htmlResult = new StringWriter();
var pvtHtmlWr = new PivotTableHtmlWriter(htmlResult);
pvtHtmlWr.Write(pvtTbl);
var pvtTblHtml = htmlResult.ToString();

Por defecto, las filas / columnas de la tabla dinámica están ordenadas por encabezados (AZ). Puede cambiar el orden que necesite .

La biblioteca PivotData OLAP (PivotData, clases de tabla dinámica) se puede utilizar de forma gratuita en proyectos de implementación única. Los componentes avanzados (como PivotTableHtmlWriter) requieren una clave de licencia comercial.

Answer 5

Una versión un poco "simplificada":

string[][] data = {
new [] { "Global", "Europe" , "Product One" , "Item One" , "579984.59" },
new [] { "Global", "North America", "Product One" , "Item Two" , "314586.73" },
new [] { "Global", "Asia" , "Product One" , "Item One" , "62735.13" },
new [] { "Global", "Asia" , "Product Two" , "Item Five" , "12619234.69" },
new [] { "Global", "North America", "Product Two" , "Item Five" , "8953713.39" },
new [] { "Global", "Europe" , "Product One" , "Item Two" , "124267.4" },
new [] { "Global", "Asia" , "Product One" , "Item Four" , "482338.49" },
new [] { "Global", "North America", "Product One" , "Item Four" , "809185.13" },
new [] { "Global", "Europe" , "Product One" , "Item Four" , "233101" },
new [] { "Global", "Asia" , "Product One" , "Item Two" , "120561.65" },
new [] { "Global", "North America", "Product One" , "Item Six" , "1517359.37" },
new [] { "Global", "Europe" , "Product One" , "Item Six" , "382590.45" },
new [] { "Global", "North America", "Product One" , "Item Eight" , "661835.64" },
new [] { "Global", "Europe" , "Product Three", "Item Three" , "0" },
new [] { "Global", "Europe" , "Product One" , "Item Eight" , "0" },
new [] { "Global", "Europe" , "Product Two" , "Item Five" , "3478145.38" },
new [] { "Global", "Asia" , "Product One" , "Item Six" , "0" },
new [] { "Global", "North America", "Product Two" , "Item Seven" , "4247059.97" },
new [] { "Global", "Asia" , "Product Two" , "Item Seven" , "2163718.01" },
new [] { "Global", "Europe" , "Product Two" , "Item Seven" , "2158782.48" },
new [] { "Global", "North America", "Product Two" , "Item Nine" , "72634.46" },
new [] { "Global", "Europe" , "Product Two" , "Item Nine" , "127500" },
new [] { "Global", "North America", "Product One" , "Item One" , "110964.44" },
new [] { "Global", "Asia" , "Product Three", "Item Ten" , "2064.99" },
new [] { "Global", "Europe" , "Product One" , "Item Eleven", "0" },
new [] { "Global", "Asia" , "Product Two" , "Item Nine" , "1250" }
};
string[][] headerInfo = {
new [] { "Product One" , "Item One" },
new [] { "Product One" , "Item Two" },
new [] { "Product One" , "Item Four" },
new [] { "Product One" , "Item Six" },
new [] { "Product One" , "Item Eight" },
new [] { "Product One" , "Item Eleven" },
new [] { "Product Two" , "Item Five" },
new [] { "Product Two" , "Item Seven" },
new [] { "Product Two" , "Item Nine" },
new [] { "Product Three", "Item Three" },
new [] { "Product Three", "Item Ten" }
};

int[] rowHeaders = { 0, 1 }, colHeaders = { 2, 3 }; int valHeader = 4;
var pivot = data.ToLookup(r => string.Join("|", rowHeaders.Select(i => r[i])))
.Select(g => g.ToLookup(c => string.Join("|", colHeaders.Select(i => c[i])), c => c[valHeader]));
foreach (var r in pivot)
Debug.Print(string.Join(", ", headerInfo.Select(h => "[" + r[string.Join("|", h)].FirstOrDefault() + "]")));

resultados en:

[579984.59], [124267.4], [233101], [382590.45], [0], [0], [3478145.38], [2158782.48], [127500], [0], []
[110964.44], [314586.73], [809185.13], [1517359.37], [661835.64], [], [8953713.39], [4247059.97], [72634.46], [], []
[62735.13], [120561.65], [482338.49], [0], [], [], [12619234.69], [2163718.01], [1250], [], [2064.99]

Lo anterior está lejos de ser el más eficiente debido a las numerosas concatenaciones de cadenas, por lo que puede ser aproximadamente 5 veces más rápido con un comparador personalizado:

public class SequenceComparer : IEqualityComparer<IEnumerable<string>>
{
public bool Equals(IEnumerable<string> first, IEnumerable<string> second)
{
return first.SequenceEqual(second);
}
public int GetHashCode(IEnumerable<string> value)
{
return value.Aggregate(0, (a, v) => a ^ v.GetHashCode());
}
}

y entonces:

var comparer = new SequenceComparer();
var pivot = data.ToLookup(r => rowHeaders.Select(i => r[i]), comparer)
.Select(g => g.ToLookup(c => colHeaders.Select(i => c[i]), c => c[valHeader], comparer));
foreach (var r in pivot)
Debug.Print(string.Join(", ", headerInfo.Select(h => "[" + string.Join(",", r[h]) + "]")));