Powershell csv行列转置与操作

Question

我是Powershell的新手。我尝试处理/转置行列对一个中等大小的基于csv的记录(大约10000行)。最初的CSV由大约10000行组成，3列为("Time","Id","IOT")，如下所示：

"Time","Id","IOT" 
"00:03:56","23","26" 
"00:03:56","24","0" 
"00:03:56","25","0" 
"00:03:56","26","1" 
"00:03:56","27","0" 
"00:03:56","28","0" 
"00:03:56","29","0" 
"00:03:56","30","1953" 
"00:03:56","31","22" 
"00:03:56","32","39" 
"00:03:56","33","8" 
"00:03:56","34","5" 
"00:03:56","35","269" 
"00:03:56","36","5" 
"00:03:56","37","0" 
"00:03:56","38","0" 
"00:03:56","39","0" 
"00:03:56","40","1251" 
"00:03:56","41","103" 
"00:03:56","42","0" 
"00:03:56","43","0" 
"00:03:56","44","0" 
"00:03:56","45","0" 
"00:03:56","46","38" 
"00:03:56","47","14" 
"00:03:56","48","0" 
"00:03:56","49","0" 
"00:03:56","2013","0" 
"00:03:56","2378","0" 
"00:03:56","2380","32" 
"00:03:56","2758","0" 
"00:03:56","3127","0" 
"00:03:56","3128","0" 
"00:09:16","23","22" 
"00:09:16","24","0" 
"00:09:16","25","0" 
"00:09:16","26","2" 
"00:09:16","27","0" 
"00:09:16","28","0" 
"00:09:16","29","21" 
"00:09:16","30","48" 
"00:09:16","31","0" 
"00:09:16","32","4" 
"00:09:16","33","4" 
"00:09:16","34","7" 
"00:09:16","35","382" 
"00:09:16","36","12" 
"00:09:16","37","0" 
"00:09:16","38","0" 
"00:09:16","39","0" 
"00:09:16","40","1882" 
"00:09:16","41","42" 
"00:09:16","42","0" 
"00:09:16","43","3" 
"00:09:16","44","0" 
"00:09:16","45","0" 
"00:09:16","46","24" 
"00:09:16","47","22" 
"00:09:16","48","0" 
"00:09:16","49","0" 
"00:09:16","2013","0" 
"00:09:16","2378","0" 
"00:09:16","2380","19" 
"00:09:16","2758","0" 
"00:09:16","3127","0" 
"00:09:16","3128","0" 
... 
... 
... 

我尝试使用从https://gallery.technet.microsoft.com/scriptcenter/Powershell-Script-to-7c8368be下载的powershell脚本中的代码进行转置。

基本上，我的powershell代码如下：

$b = @() 
    foreach ($Time in $a.Time | Select -Unique) { 
        $Props = [ordered]@{ Time = $time } 
        foreach ($Id in $a.Id | Select -Unique){ 
            $IOT = ($a.where({ $_.Id -eq $Id -and $_.time -eq $time })).IOT 
            $Props += @{ $Id = $IOT } 
        } 
        $b += New-Object -TypeName PSObject -Property $Props 
    } 
$b | FT -AutoSize 
$b | Out-GridView 

上面的代码可以给出我预期的结果，即所有的"Id"值都将成为列标题，而所有的"Time"值将成为唯一的行和"IOT"值，作为"Id" x "Time"的交集，如下所示：

"Time","23","24","25","26","27","28","29","30","31","32","33","34","35","36","37","38","39","40","41","42","43","44","45","46","47","48","49","2013","2378","2380","2758","3127","3128" 
"00:03:56","26","0","0","1","0","0","0","1953","22","39","8","5","269","5","0","0","0","1251","103","0","0","0","0","38","14","0","0","0","0","32","0","0","0" 
"00:09:16","22","0","0","2","0","0","21","48","0","4","4","7","382","12","0","0","0","1882","42","0","3","0","0","24","22","0","0","0","0","19","0","0","0" 

虽然它只涉及几百行，结果很快就出来了，但现在当处理整个csv文件有10000行时，上面的脚本“继续执行”，看起来很长时间(几个小时)都无法完成，也不能说出任何结果。那么，如果来自堆栈溢出的一些powershell专家能够帮助评估上面的代码，并可能帮助修改以加快结果，那么可能会这样吗？

非常感谢你的建议

Answer 1

10000条记录很多，但我认为仅仅建议streamreader*并手动解析CSV是不够的。不过，最不利于你的是下面这句话：

$b += New-Object -TypeName PSObject -Property $Props 

PowerShell在这里所做的是创建一个新的数组并将该元素附加到其中。这是一个非常内存密集型的操作，您正在重复1000次。在这种情况下，更好的做法是利用管道对您有利。

$data = Import-Csv -Path "D:\temp\data.csv"
$headers = $data.ID  | Sort-Object {[int]$_}  -Unique

$data | Group-Object Time | ForEach-Object{
    $props = [ordered]@{Time = $_.Name}
    foreach($header in $headers){
        $props."$header" = ($_.Group | Where-Object{$_.ID -eq $header}).IOT
    }
    [pscustomobject]$props
} |  export-csv d:\temp\testing.csv -NoTypeInformation

作为一个对象，$data将是内存中的整个文件。需要获取所有将是列标题的$headers。

将数据按每个Time分组。然后在每次对象内部，我们得到每个ID的值。如果ID在这段时间内不存在，那么条目将显示为null。

这不是最好的方法，但应该比你的快。我在一分钟内完成了10000项记录(平均51秒，3次传球)。如果我可以的话会给你看的。

我只是用我自己的数据运行了你的代码一次，花费了13分钟。我认为可以肯定地说，我的表现更快。

虚拟数据是用这个逻辑FYI生成的。

1..100 | %{
 $time = get-date -Format "hh:mm:ss"
 sleep -Seconds 1
    1..100 | % {

        [pscustomobject][ordered]@{
            time = $time 
            id = $_
            iot = Get-Random -Minimum 0 -Maximum 7
        } 
    }
} | Export-Csv d:\temp\data.csv -notypeinformation

对于您的流读取器来说，*不是一个很好的例子。只是指出这是更好的方式来读取大文件。只需要逐行解析字符串。