2

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 ?

2 Answers 2

1

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
Sign up to request clarification or add additional context in comments.

Comments

1

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.