我需要一些幫助來決議。CSV 像附件中一樣，然后根據 message_subject 列中特定關鍵字的頻率生成摘要報告，例如“自動回復： ”
源輸入.CSV：
預期的結果是：
Sender_Address, Frequency, Date (Ascending sorted)
[email protected], 3, 2022-09-27, 2022-09-28
[email protected], 2, 2022-10-04, 2022-09-30
[email protected], 1, 2022-10-06
如何做到這一點？
uj5u.com熱心網友回復：
用于Group-Object按發件人地址對行進行分組，然后用于Sort-Object -Unique獲取所有唯一日期：
Import-Csv path\to\file.csv |Group-Object sender_address |ForEach-Object {
# Parse timestamps, grab unique dates
$uniqueDates = $_.Group |ForEach-Object { [datetime]::Parse($_.origin_timestamp_utc).Date.ToString('yyyy-MM-dd') } |Sort-Object -Unique
# Create a new object to hold the frequency info
[pscustomobject]@{
Sender_address = $_.Name
Frequency = $_.Count
Dates = $uniqueDates -join ', '
}
} |Export-Csv path\to\result.csv -NoTypeInformation
uj5u.com熱心網友回復：
在這里，通過使用group-object按 Sender_Address 分組，然后使用Select-object計算屬性來計算關鍵字“自動回復”并收集相關日期（排序）：
#Load csv
$csv = import-csv [path]
#Calculate output
$csv | Group-Object -Property 'sender_address' | Select-Object Name,@{name='keyWordCount';expression={($_.group.'message_subject' -match 'Auto Reply').count}},@{name='origin_timestamp_utc';expression={(($_.group | ?{$_.'message_subject' -match 'Auto Reply'}).'origin_timestamp_utc' | Sort-Object) -join ","}}
#Output
Name keyWordCount origin_timestamp_utc
---- ------------ --------------------
[email protected] 3 2022-09-27T19:38:02.2054022Z,2022-09-28T19:38:03.0335704Z,2022-09-28T19:38:03.06298…
[email protected] 2 2022-09-30T19:38:02.8562799Z,2022-10-04T02:33:07.6877938Z
[email protected] 1 2022-10-06T23:30:12.3284393Z
uj5u.com熱心網友回復：
這是我對這個問題的看法：
# Create sample data
$csv = ConvertFrom-Csv @'
origin_timestamp_utc,sender_address,message_subject,directionality,connector_id,delivery_priority
2022-09-27T19:38:02.1826859Z,[email protected],Customer Vehicle Conditions report for 20220927,Originating,,Normal
2022-09-27T19:38:02.2054022Z,[email protected],Auto Reply: Vehicle Reports are due now.,Originating,,Normal
2022-09-28T19:38:03.0335704Z,[email protected],Auto Reply: Late in the office.,Originating,,Normal
2022-09-28T19:38:03.0629852Z,[email protected],Auto Reply: Sick not well :-(,Originating,,Normal
2022-10-05T19:38:02.1819402Z,[email protected],Customer Vehicle Conditions report for 20221005,Originating,,Normal
2022-10-05T19:38:02.1903258Z,[email protected],Customer Vehicle Conditions report for 20221005,Originating,,Normal
2022-10-06T19:38:02.2743619Z,[email protected],Customer Vehicle Conditions report for 20221006,Originating,,Normal
2022-10-06T19:38:02.2821327Z,[email protected],Customer Vehicle Conditions report for 20221006,Originating,,Normal
2022-09-29T19:38:02.1361255Z,[email protected],Customer Vehicle Conditions report for 20220929,Originating,,Normal
2022-09-29T19:38:02.1453714Z,[email protected],Customer Vehicle Conditions report for 20220929,Originating,,Normal
2022-09-30T19:38:02.8562799Z,[email protected],Auto Reply: Out of office today?!??!,Originating,,Normal
2022-09-30T19:38:02.8979890Z,[email protected],Customer Vehicle Conditions report for 20220930,Originating,,Normal
2022-10-03T19:38:02.5937670Z,[email protected],Customer Vehicle Conditions report for 20221003,Originating,,Normal
2022-10-04T02:33:07.6877938Z,[email protected],Auto Reply: Not in the Office ?,Originating,,Normal
2022-10-04T19:38:02.6117533Z,[email protected],Customer Vehicle Conditions report for 20221004,Originating,,Normal
2022-10-01T19:38:03.3718620Z,[email protected],Customer Vehicle Conditions report for 20221001,Originating,,Normal
2022-10-01T19:38:04.8114745Z,[email protected],Customer Vehicle Conditions report for 20221001,Originating,,Normal
2022-10-02T19:38:02.2517865Z,[email protected],Customer Vehicle Conditions report for 20221002,Originating,,Normal
2022-10-02T19:38:02.2855767Z,[email protected],Customer Vehicle Conditions report for 20221002,Originating,,Normal
2022-10-03T19:38:02.5937670Z,[email protected],Customer Vehicle Conditions report for 20221003,Originating,,Normal
2022-10-04T19:38:02.6383853Z,[email protected],Customer Vehicle Conditions report for 20221004,Originating,,Normal
2022-10-06T23:30:12.3284393Z,[email protected],Auto Reply: Where are you !!!!,Originating,,Normal
'@
$keyWord = 'Auto Reply:*'
$csv | Group-Object sender_address | ForEach-Object {
# From current group, get all date/times where the message_subject matches the keyword
$dates = $_.Group.Where{ $_.message_subject -like $keyWord }.ForEach{ $_.origin_timestamp_utc }
# Remove the time part and get unique dates
$uniqueDates = $dates -replace 'T. ' | Sort-Object -Unique
# Output
[PSCustomObject]@{
Sender_Address = $_.Name
Frequency = $dates.Count
'Date (Ascending sorted)' = $uniqueDates -join ', '
}
}
輸出：
Sender_Address Frequency Date (Ascending sorted)
-------------- --------- -----------------------
[email protected] 3 2022-09-27, 2022-09-28
[email protected] 2 2022-09-30, 2022-10-04
[email protected] 1 2022-10-06
與預期輸出相比Date，第 2 行的列略有不同。我已經解釋了要單獨應用于每一行的“升序排序”。我不確定 OP 是否真的要對整個列進行排序（可能按最新日期）。
uj5u.com熱心網友回復：
這解決了您的問題，包括對日期進行升序排序：
# gets all messages starting with "Auto Reply"
$messages = Import-Csv <your file> | Where-Object message_subject -match "Auto Reply.*"
# changes timestamp format to date only
$messages | ForEach-Object { $_.origin_timestamp_utc = [datetime]::Parse($_.origin_timestamp_utc).Date.ToString('yyyy-MM-dd') }
#groups by sender_adress
$messages | Group-Object sender_address | ForEach-Object {
$sortedDates = ""
# sorts dates so the date with the least amount of messages comes first (you can change this with Sort-Object -descending)
$_.group | Group-Object origin_timestamp_utc | Sort-Object count | ForEach-Object {
$sortedDates = $_.Name ","
}
[pscustomobject]@{
Sender_Address = $_.Name
Frequency = $_.Count
"Date (Ascending Sorted)" = $sortedDates.TrimEnd(",") #remove last ","
}
}
添加Export-Csv以匯出它
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/513262.html
標籤：电源外壳