Author Posts

May 19, 2018 at 2:01 pm

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:

https://gist.github.com/oatmeal10/445af7b6c3717e2b9a923328bc5031e3

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
#$95A
#$95B
}


May 19, 2018 at 2:17 pm

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 {
 [CmdletBinding()]
 Param(
  [Parameter(ValueFromPipeline=$True)][object]$InputObject,
  [string]$Property
 )
 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.