Powershell - Group and count from CSV file

Question

In my CSV file, I have two columns with header Start_date and Status. I am trying to find out the success percentage for each Start_date

    Start_date       Status
------------------------------------------
    02-03-2022      Completed
    02-03-2022      Completed
    03-03-2022      Failed
    03-03-2022      Completed

I am looking for a final output like below which I export CSV

Start_date  Total Completed  Failed Success %
02-03-2022   2       2         0      100
03-03-2022   2       1         1       50

As a first step, I am trying to get the count of each day job using below code.

$data = Import-Csv "C:\file.csv"
$data | group {$_.Start_date} | Sort-Object {$_.Start_date} | Select-Object {$_.Status}, Count

Above code will give me output like

$_.Status Count
--------- -----
              1
              1

it is not showing the date value. what will be the correct approach for this issue ?

Santiago Squarzon · Accepted Answer · 2022-03-10 13:07:06Z

1

You can use Group-Object to group the objects by the date column, then it's just math:

$csv = @'
Start_date,Status
02-03-2022,Completed
02-03-2022,Completed
03-03-2022,Failed
03-03-2022,Completed
'@ | ConvertFrom-Csv

# This from your side should be:
# Import-Csv path/to/csv.csv | Group-Object ....

$csv | Group-Object Start_date | ForEach-Object {
    $completed, $failed = $_.Group.Status.where({ $_ -eq 'Completed' }, 'Split')
    $totalc  = $_.Group.Count
    $complc  = $completed.Count
    $failc   = $failed.Count
    $success = $complc / $totalc

    [pscustomobject]@{
        Start_Date = $_.Name
        Total      = $totalc
        Completed  = $complc
        Failed     = $failc
        Success    = $success.ToString('P0')
    }
}

answered Mar 10, 2022 at 13:07

Santiago Squarzon

65.6k5 gold badges26 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Avshalom · Accepted Answer · 2022-03-10 13:17:34Z

1

Here's another one:

$csv = Import-Csv C:\Temp\tmp.csv
$Results = @()
foreach ($group in $csv | Group Start_date)
{
    $Completed = ($group.Group | group status | ? Name -eq Completed).Count
    $Failed = ($group.Group | group status | ? Name -eq Failed).Count
    $row = "" | Select Start_date,Total,Completed,Failed,"Success %"
    $row.Start_date = $group.Name 
    $row.Total = $group.Count
    $row.Completed = $Completed
    $row.Failed = $Failed
    $row."Success %" = $Completed / 2 * 100
    $Results += $row
}

$results

Start_date Total Completed Failed Success %
---------- ----- --------- ------ ---------
02-03-2022     2         2      0       100
03-03-2022     2         1      1        50

answered Mar 10, 2022 at 13:17

Avshalom

8,9471 gold badge29 silver badges50 bronze badges

Collectives™ on Stack Overflow

Powershell - Group and count from CSV file

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related