我正在从web服务中解析JSON来获取我的任务(使用TimeFlip)。现在,我得到了每个任务的发生时间和持续时间,因此数据如下所示:
(taskname, start, durationinSec)
TaskA,"6/5/2018 12:16:36 PM",312
TaskB,"6/5/2018 12:30:36 PM",200
TaskA,"6/6/2018 08:00:00 AM",150
TaskA,"6/6/2018 03:00:00 PM",150
(etc etc)
我想生成一个汇总报告,按天显示哪些任务有多少时间。
虽然数据将跨越几周,但我只是想做一份每周报告,这样我就可以很容易地将其转录到我们的time应用程序中(因为他们不会给我API密钥)。所以我会先做一些像where {$_.start -gt (? {$_.start -gt (get-date -Hour 0 -Minute 00 -Second 00).adddays(-7)}
这样的事情。
6/5/2018 6/6/2018
TaskA 312 300
TaskB 200
我该怎么做呢?我假设是group-object,但不清楚您将如何进行轴心或分组。
发布于 2018-06-10 21:14:00
用谷歌搜索PowerShell
和Pivot
,我发现这个gist.github.com有一种更通用的方法来创建PivotTable。
要转置(交换x,y),只需更改变量$rotate
、$keep
它还有一个额外的好处,那就是计算行合计
## Q:\Test\2018\06\09\PivotTable.ps1
## Source https://gist.github.com/andyoakley/1651859
# #############################################################################
# Rotates a vertical set similar to an Excel PivotTable
# #############################################################################
$OutputFile = "MyPivot.csv"
$data = @'
taskname,start,duration
TaskA,"6/5/2018 12:16:36 PM",312
TaskB,"6/5/2018 12:30:36 PM",200
TaskA,"6/6/2018 08:00:00 AM",150
TaskA,"6/6/2018 03:00:00 PM",150
'@ | ConvertFrom-Csv |Select-Object taskname, duration, @{n='start';e={($_.start -split ' ')[0]}}
# Fields of interest
$rotate = "taskname" # Bits along the top
$keep = "start" # Those along the side
$value = "duration" # What to total
#-------------------- No need to change anything below ------------------------
# Creatre variable to store the output
$rows = @()
# Find the unique "Rotate" [top row of the pivot] values and sort ascending
$pivots = $data | select -unique $rotate | foreach { $_.$rotate} | Sort-Object
# Step through the original data...
# for each of the "Keep" [left hand side] find the Sum of the "Value" for each "Rotate"
$data |
group $keep |
foreach {
$group = $_.Group
# Create the data row and name it as per the "Keep"
$row = new-object psobject
$row | add-member NoteProperty $keep $_.Name
# Cycle through the unique "Rotate" values and get the sum
foreach ($pivot in $pivots) {
$row | add-member NoteProperty $pivot ($group | where { $_.$rotate -eq $pivot } | measure -sum $value).Sum
}
# Add the total to the row
$row | add-member NoteProperty Total ($group | measure -sum $value).Sum
# Add the row to the collection
$rows += $row
}
# Do something with the pivot rows
$rows | Format-Table
$rows | Export-Csv $OutputFile -NoTypeInformation
示例输出:
start TaskA TaskB Total
----- ----- ----- -----
6/5/2018 312 200 512
6/6/2018 300 300
或x/y交换
taskname 6/5/2018 6/6/2018 Total
-------- -------- -------- -----
TaskA 312 300 612
TaskB 200 200
https://stackoverflow.com/questions/50766209
复制相似问题