Reading CSV file with dynamically changing columns

PowerShell Help
1

We generate a CSV file which changes columns dynamically. So we need to process this CSV file and based on the column values and not row values e.g.

 Col1, Col2, .......................ColN

R1 29.4,12,13…
R2 30,12,13…
…
…
.
RN 29.8,12,16…

So my final result should display based on Columns like

Col1 Avg = 29.4 + 30 +…+ 29.8 / RN
Col2 Avg = 12 + 12 +…+ 12 / RN
Col3 Avg = 13 + 13 +…+ 16 / RN
ColN Avg = …+…/ RN

We searched online and it resulted in only getting values to be stored in hash table row wise which does not help at all in this case. Please see attached csv for real data that we will be using.
Thanks in advance for the help

Ashwini

2

Just to be clear, each row has the same number of columns, correct? I’d probably use a combination of Import-Csv and Measure-Object for this. For example:

$data = Import-Csv -Path .\Log9192014153241.csv

# We skip the first column that contains date / time, because it doesn't make sense to average that.
$properties = $data |
              Get-Member -MemberType NoteProperty |
              Select-Object -Skip 1 -ExpandProperty Name

$averages = @{}

foreach ($property in $properties)
{
    # I see in your sample that you may have some rows that don't contain numeric data.  Those
    # will cause Measure-Object -Average to fail, so we filter those out with Where-Object first.

    $stats = $data |
             Where-Object { $null -ne ($_.$property -as [double])  } |
             Measure-Object -Average -Property $property

    $averages[$property] = $stats.Average
}

This builds a hashtable which maps column names to average values, and you can output that however you like.

3

Thanks for you reply. Yes each row has fixed columns and also the first column is always date so we would have to exclude it.
I will try this and let you know.

Ashwini

4

It works fine. I appreciate your help.

Thanks
Ashwini