Reading CSV file with dynamically changing columns

This topic contains 3 replies, has 2 voices, and was last updated by Profile photo of Ashwini Korlahalli Ashwini Korlahalli 2 years, 2 months ago.

  • Author
    Posts
  • #19326
    Profile photo of Ashwini Korlahalli
    Ashwini Korlahalli
    Participant

    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

  • #19328
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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.

  • #19329
    Profile photo of Ashwini Korlahalli
    Ashwini Korlahalli
    Participant

    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

  • #19335
    Profile photo of Ashwini Korlahalli
    Ashwini Korlahalli
    Participant

    It works fine. I appreciate your help.

    Thanks
    Ashwini

You must be logged in to reply to this topic.