Import-CSV, measuring, exporting

This topic contains 1 reply, has 2 voices, and was last updated by  Don Jones 1 month ago.

  • Author
  • #100879


    From the following file, I need to export the entire row that contains the maximum and 95th percentile values of columns A and B respectively.

    Sample output might look like this:

    I have tried the following but do not have the knowledge necessary to complete. Any pointers appreciated!

    import-csv data.csv | Group-Object -Property 'Time' | Foreach-Object {
    $MaxA = $_.Group | Where-Object -Property 'A'  | Measure-Object -Maximum
    $MaxB = $_.Group | Where-Object -Property 'B' | Measure-Object -Maximum
  • #100885

    Don Jones

    Measure-Object needs to be given a property to measure. And I'm not certain what your Where-Object is meant to be doing – that syntax doesn't make sense to me. Measure also won't help you find percentiles; you're going to have to code something on your own, there.

    So, for maximum... the trick is to first find the max value, and then go back again and find rows containing that value.

    $maxA = Import-CSV whatever.csv |
    Measure-Object -Property A -Maximum |
    Select-Object -ExpandProperty Maximum
    $maxB = Import-CSV whatever.csv |
    Measure-Object -Property B -Maximum |
    Select-Object -ExpandProperty Maximum
    Import-CSV whatever.csv |
    Where A -eq $MaxA
    Import-CSV whatever.csv | 
    Where B -eq $MaxB

    That's if you want to use Measure-Object. It's not terribly efficient, obviously, but that's how the command works. Me, I'd probably write my own function that accepted whatever was in the CSV, accepted the property I wanted to scan (A or B), and then just ran through each until it found the max and then output that. So, in pseudo-ish-code:

    function Select-MaxValue {
     BEGIN { $output = $null; $maxvalue = 0 }
     PROCESS {
      $thisvalue = $InputObject | Select -Expand $Property # Might need to fuss with this syntax
      if ($thisvalue -gt $maxvalue) {
       $maxvalue = $thisvalue
       $output = $InputObject
     END { Write-Output $output }
    Import-CSV whatever.csv | Select-MaxValue -Property A

    I mean, something vaguely like that. Basically, you scan through the file and when you find a new maximum value you save off that line. At the end, you output whatever you've found to that point. If there's a possibility of multiple lines having the same max value, and you want them all, then you'll have to make the logic a bit more complex and use an array or something.

    If you want percentiles – same thing. You're going to have to first go find out what all the values ARE, figure out what the 95th percentile even IS, and then go back AGAIN to find lines containing that value.

You must be logged in to reply to this topic.