Powershell - counting elements in csv column and add it to csv-CodePudding

I have a csv

TEST1.CSV

Users DHMS          
----- ----          
A     22/12/21 05:02:00
B     22/12/21 05:10:00
C     22/12/21 06:30:00
D     23/12/21 12:30:00
A     23/12/21 12:35:00
B     23/12/21 12:45:00
C     26/12/21 10:32:00
D     28/12/21 11:15:00
A     29/12/21 14:17:00
B     29/12/21 14:25:00

I would like to add a column with the number of unique users

Users DHMS              Count
----- ----              ---- 
A     22/12/21 05:02:00    3
B     22/12/21 05:10:00    3
C     22/12/21 06:30:00    2
D     23/12/21 12:30:00    2
A     23/12/21 12:35:00    3
B     23/12/21 12:45:00    3
C     26/12/21 10:32:00    2
D     28/12/21 11:15:00    2
A     29/12/21 14:17:00    3
B     29/12/21 14:25:00    3

I have the code for counting elements

$countusers = @{}
Import-Csv "$($path)\TEST1.csv" -DeLimiter ";" |
    Group-Object Users| Select-Object Name,Count
$countusers

But I don't find how to add the third column count

A ForEach loop but how to assign value ?

CodePudding user response：

Might not be the prettiest solution but, by using Group-Object first, then saving to a variable, you can reference those properties while iterating through your CSV object.

In this case, Select-Object, can handle the iteration of your values in your CSV object. Finally using a calculated property we can add the new column of count by comparing the current object in the pipeline (first saving it to $obj) to what the count was in $Grouped by matching the name property to the users property, then getting just the count value:

@"
Users,DHMS
A,22/12/21 05:02:00
B,22/12/21 05:10:00
C,22/12/21 06:30:00
D,23/12/21 12:30:00
A,23/12/21 12:35:00
B,23/12/21 12:45:00
C,26/12/21 10:32:00
D,28/12/21 11:15:00
A,29/12/21 14:17:00
B,29/12/21 14:25:00
"@ | ConvertFrom-Csv -OutVariable "CSV" | Group-Object -Property "users" -OutVariable "Grouped" 

$CSV | Select-Object -Property *, @{
    Name = "Count"
    Expression = {
        $obj = $_
        $Grouped | Where-Object -Property "Name" -EQ $obj.Users | Select-Object -ExpandProperty "Count"
    }
}

This will output:

Users DHMS              Count
----- ----              -----
A     22/12/21 05:02:00     3
B     22/12/21 05:10:00     3
C     22/12/21 06:30:00     2
D     23/12/21 12:30:00     2
A     23/12/21 12:35:00     3
B     23/12/21 12:45:00     3
C     26/12/21 10:32:00     2
D     28/12/21 11:15:00     2
A     29/12/21 14:17:00     3
B     29/12/21 14:25:00     3

CodePudding user response：

Here is a different approach to Abraham's helpful answer showing how this can be done without the use of Group-Object and updating the existing object instead creating a new object.

$csv = Import-Csv path\to\mycsv.csv -Delimiter ';'

$csv.foreach{
    begin { $map = @{} }
    process {
        [int] $val = $map[$_.Users]
        $map[$_.Users] =   $val
    }
}

foreach($line in $csv) {
    $line.PSObject.Properties.Add(
        [psnoteproperty]::new('Count', $map[$line.Users])
    )
}

$csv | Format-Table -AutoSize