时间戳

首页 > 解决方案 > 将数组数据重新排列到表中

powershell - 将数组数据重新排列到表中

问题描述

我正在编写一个脚本以从大型 CSV 文件中获取一些值。

下面是一些示例数据。我使用附加的脚本按 ID1 和 ID2（这些 ID 相同，但我需要它们）、Service 和 sum 值对数据进行分组。

我得到这样的输出：

在此处输入图像描述

但我想要一个类似 Excel 表格的输出 - ID1 和 Id2 在左列，服务在上排，每个 ID 的总和，服务在下表中。像这样的东西：

在此处输入图像描述

我想将其导出为 CSV，以便可以在 Excel 中打开它。可以做到吗？如何？

$data = @(
   [pscustomobject]@{Id1='1';Id2=51213412;Service='Service1';Propertyx=1;Price='5'}
   [pscustomobject]@{Id1='1';Id2=51213412;Service='Service2';Propertyx=1;Price='4'}
   [pscustomobject]@{Id1='1';Id2=51213412;Service='Service4';Propertyx=2;Price='4'}
   [pscustomobject]@{Id1='1';Id2=51213412;Service='Service4';Propertyx=2;Price='1'}
   [pscustomobject]@{Id1='1';Id2=51213412;Service='Service2';Propertyx=2;Price='3'}
   [pscustomobject]@{Id1='2';Id2=11112314;Service='Service1';Propertyx=1;Price='17'}
   [pscustomobject]@{Id1='2';Id2=11112314;Service='Service2';Propertyx=1;Price='13'}
   [pscustomobject]@{Id1='2';Id2=11112314;Service='Service3';Propertyx=1;Price='7'}
   [pscustomobject]@{Id1='2';Id2=11112314;Service='Service1';Propertyx=1;Price='2'}
   [pscustomobject]@{Id1='3';Id2=12512521;Service='Service1';Propertyx=1;Price='3'}
   [pscustomobject]@{Id1='2';Id2=11112314;Service='Service2';Propertyx=1;Price='11'}
   [pscustomobject]@{Id1='4';Id2=42112521;Service='Service1';Propertyx=1;Price='7'}
   [pscustomobject]@{Id1='2';Id2=11112314;Service='Service3';Propertyx=1;Price='5'}
   [pscustomobject]@{Id1='3';Id2=12512521;Service='Service2';Propertyx=1;Price='4'}
   [pscustomobject]@{Id1='4';Id2=42112521;Service='Service2';Propertyx=1;Price='12'}
   [pscustomobject]@{Id1='1';Id2=51213412;Service='Service3';Propertyx=1;Price='8'}
   [pscustomobject]@{Id1='4';Id2=42112521;Service='Service1';Propertyx=1;Price='7'}
   [pscustomobject]@{Id1='3';Id2=12512521;Service='Service5';Propertyx=1;Price='7'}
   [pscustomobject]@{Id1='4';Id2=42112521;Service='Service3';Propertyx=1;Price='7'}
   [pscustomobject]@{Id1='3';Id2=12512521;Service='Service1';Propertyx=1;Price='3'}
   [pscustomobject]@{Id1='2';Id2=11112314;Service='Service2';Propertyx=1;Price='11'}
   [pscustomobject]@{Id1='4';Id2=42112521;Service='Service1';Propertyx=1;Price='7'}
   [pscustomobject]@{Id1='2';Id2=11112314;Service='Service3';Propertyx=1;Price='5'}
   [pscustomobject]@{Id1='3';Id2=12512521;Service='Service2';Propertyx=1;Price='4'}
   [pscustomobject]@{Id1='3';Id2=12512521;Service='Service4';Propertyx=1;Price='12'}
   [pscustomobject]@{Id1='1';Id2=51213412;Service='Service5';Propertyx=1;Price='8'}
   [pscustomobject]@{Id1='4';Id2=42112521;Service='Service1';Propertyx=1;Price='7'}
   [pscustomobject]@{Id1='3';Id2=12512521;Service='Service5';Propertyx=1;Price='7'}
   [pscustomobject]@{Id1='5';Id2=53252352;Service='Service1';Propertyx=1;Price='7'})


   $Grouping = $data | group Id1, Id2, Service
   $grouping| foreach {
    $b= $_.name -split ', '
    [pscustomobject] @{
         Id1 = $b[0]; Id2 = $b[1];Service = $b[2]
        'Sum Value' = ($_.group | measure Price -sum).sum
    }
}

标签： powershell

解决方案

如果我们从您的$data对象创建开始，您可以执行以下操作：

# Determine new columns based on unique Service values
$newcols = ('Id1','Id2')+($data | Select -Expand Service -Unique | Sort)

# Initially group on Id1,Id2
$data | group Id1,Id2 | Foreach-Object {
    # Create new object with required columns
    # Use index [0] since currently all objects have the same data for the new columns
    $obj = $_.Group[0] | Select $newcols
    # Grouping on Service
    $_.Group | group Service | Foreach-Object {
        # Extracting Service name
        $service = $_.Group[0].Service
        # Summing Price for target Service
        $sum = ($_.Group.Price | Measure -Sum).Sum
        # Update target Service name property on new object
        $obj.$service = $sum 
    }
    # Output new object when all Service names have been processed
    $obj
}

如果将代码通过管道传输到 Format-Table，则输出：

Id1      Id2 Service1 Service2 Service3 Service4 Service5
---      --- -------- -------- -------- -------- --------
1   51213412        5        7        8        5        8
2   11112314       19       35       17
3   12512521        6        8                12       14
4   42112521       28       12        7
5   53252352        7

推荐阅读