多列数据转换

我正在从数据源接收数据，在将信息发送到 UI 进行显示之前，我需要先进行数据透视。I am new to concept of pivoting & I am not sure how to go about it.

问题有两个部分：

1. 形成标题
2. 旋转数据以匹配标题

要记住的事情：

3. 我有一些我不想旋转的列。我称他们为static columns.

4. 我需要旋转某些列以形成多级标题信息。我称他们为dynamic columns

5. 某些包含实际值的列需要进行透视。我打电话给他们value columns.

6. 有NO limit就数量而言dynamic, static and value columns一个可以拥有。

7. 假设，当数据到来时，我们将首先获得静态列的数据，然后是动态列的数据，然后是值列的数据。

   请参阅附图了解更多信息。

虚拟数据：

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"});


        }
    }

你叫什么static columns通常被称为行组, dynamic columns - 列组 and value columns - 价值总量或简单values.

为了实现这一目标，我建议使用以下简单的数据结构：

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; }
}

The Columns成员PivotData将代表你所说的header，而Row成员 - 列表PivotDataRow对象与Data成员包含行组价值观和Values - the values对于相应的Columns index (PivotDataRow.Values总会有相同的Count as PivotData.Columns.Count).

上述数据结构可序列化/反序列化为 JSON（使用 Newtonsoft.Json 测试），可用于以所需格式填充 UI。

用于表示行组值、列组值和聚合值的核心数据结构是这样的：

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
}

基本上它代表了一个范围（切片）string具有相等和顺序比较语义的列表。前者允许在数据透视转换期间使用基于哈希的高效 LINQ 运算符，而后者允许可选排序。此外，此数据结构允许高效转换，无需分配新列表，同时在从 JSON 反序列化时保存实际列表。

（平等比较是通过实施提供的IEquatable<PivotValues>界面 -GetHashCode and Equals方法。通过这样做，它可以治疗两个PivotValues类实例根据指定范围内的值相等List<string>输入的元素List<List<string>>。类似地，排序是通过实现IComparable<PivotValues>界面 -CompareTo方法））

转换本身非常简单：

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 };
}

首先，使用简单的 LINQ 确定列（标题）Distinct操作员。然后通过按行列对源集进行分组来确定行。每行分组内的值是通过外部连接确定的Columns与分组内容。

由于我们的数据结构实现，LINQ 转换非常高效（对于空间和时间）。列和行的顺序是可选的，如果不需要，只需将其删除即可。

使用虚拟数据进行示例测试：

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