.bat file to average 2 columns in .csv file, and print results to the file name.

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Message
Author
md73code
Posts: 16
Joined: 29 Jul 2015 15:29

.bat file to average 2 columns in .csv file, and print results to the file name.

#1 Post by md73code » 14 Sep 2017 16:11

Hi everyone, I'm running a test which outputs numerous .csv files (around 12). I need to average out the data in column G (7) and H (8). I'd then like to print the averages to a file with the title of column G (7) CMAA and column H (8) TAA. I'd prefer to print the title with average at the end of the respective .csv file name.

ex

Original .csv file output from my benchmark:
TestResult-Run1
TestResult-Run2
TestResult-Run3
etc..

What I would like for my .bat file to do:
TestResult-Run1_CMAA-3.46_TAA-4.67.csv
TestResult-Run2_CMAA-3.89_TAA-3.49.csv
TestResult-Run3_CMAA-3.45_TAA-4.21.csv
etc..

Is this possible with a .bat script?

Squashman
Expert
Posts: 4465
Joined: 23 Dec 2011 13:59

Re: .bat file to average 2 columns in .csv file, and print results to the file name.

#2 Post by Squashman » 14 Sep 2017 20:57

So are you expecting to do floating point math?

md73code
Posts: 16
Joined: 29 Jul 2015 15:29

Re: .bat file to average 2 columns in .csv file, and print results to the file name.

#3 Post by md73code » 18 Sep 2017 12:43

Not sure. I guess I need to calculate the column average like from this post:
https://unix.stackexchange.com/question ... ion-in-csv

and then write the results at the end of the particular .csv file name.

Squashman
Expert
Posts: 4465
Joined: 23 Dec 2011 13:59

Re: .bat file to average 2 columns in .csv file, and print results to the file name.

#4 Post by Squashman » 18 Sep 2017 13:11

Manveer Dhillon wrote:Not sure. I guess I need to calculate the column average like from this post:
https://unix.stackexchange.com/question ... ion-in-csv

and then write the results at the end of the particular .csv file name.

We do not do Unix/Linux shells scripting here. This site is dedicated to Windows Batch files (.bat .cmd).

md73code
Posts: 16
Joined: 29 Jul 2015 15:29

Re: .bat file to average 2 columns in .csv file, and print results to the file name.

#5 Post by md73code » 18 Sep 2017 14:56

Hi, yes I would rather do this in .bat format since I am already using a batch script to run my test 12x times with different benchmark settings. Post processing the .csv files is a little tedious.

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

Re: .bat file to average 2 columns in .csv file, and print results to the file name.

#6 Post by aGerman » 18 Sep 2017 15:26

Manveer Dhillon wrote:Post processing the .csv files is a little tedious.

And it is tedious in Batch, too. Batch doesn't support floating point calculations. Thus, you need some kind of complicated workarounds or 3rd party tools.
Recently CirothUngol posted a script that performs floating point calculations. See
viewtopic.php?f=3&t=8139
I used his script in the following code. Both math.cmd and this script are saved in the same directory along with the csv files.

Code: Select all

@echo off &setlocal

set "CMAA_list=" &set "TAA_list="
>"list.txt" (
  for %%i in (*.csv) do (
    set "file=%%i"
    setlocal EnableDelayedExpansion
    for /f "usebackq skip=1 tokens=7,8 delims=," %%j in ("!file!") do (
      set "CMAA_list=!CMAA_list!,%%~j"
      set "TAA_list=!TAA_list!,%%~k"
    )
    call math "/d2 (CMAA=avg(!CMAA_list:~1!))>(TAA=avg(!TAA_list:~1!))"
    echo !file:~,-4!_CMAA-!CMAA!_TAA-!TAA!.csv
    endlocal
  )
)


Because you didn't provide an example of the csv content in your files I'll post how my test files look like:

Code: Select all

foo,foo,foo,foo,foo,foo,CMMA,TAA,foo,foo
bar,bar,bar,bar,bar,bar,3.24,2.53,bar,bar
bar,bar,bar,bar,bar,bar,2.67,3.19,bar,bar
bar,bar,bar,bar,bar,bar,3.33,3.41,bar,bar
bar,bar,bar,bar,bar,bar,2.89,2.85,bar,bar

I assumed that
- there is a head line
- the value separators are commas (which could be also semicolons depending on your local settings)
- there are no empty values (that would lead to consecutive commas and thus, to wrong tokens)

Steffen
Last edited by aGerman on 19 Sep 2017 03:14, edited 1 time in total.
Reason: Updated the script according to CirothUngol's suggestion

CirothUngol
Posts: 46
Joined: 13 Sep 2017 18:37

Re: .bat file to average 2 columns in .csv file, and print results to the file name.

#7 Post by CirothUngol » 19 Sep 2017 01:33

aGerman wrote:Because you didn't provide an example of the csv content...
This would be most helpful.

call math "/d2 (CMAA=avg(!CMAA_list:~1!)) > (TAA=avg(!TAA_list:~1!))"
Change the operator in the middle to anything other than assignment and this should work, comparison is probably fastest.

Aacini
Expert
Posts: 1885
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:

Re: .bat file to average 2 columns in .csv file, and print results to the file name.

#8 Post by Aacini » 19 Sep 2017 08:31

It is very difficult to try to write a program that should process a certain data when there are not examples of such data! The only solution is to guess the data, so these are my input files:

TestResult-Run1.csv:

Code: Select all

foo,foo,foo,foo,foo,foo,CMMA,TAA,foo,foo
bar,bar,bar,bar,bar,bar,3.34,4.63,bar,bar
bar,bar,bar,bar,bar,bar,3.77,4.49,bar,bar
bar,bar,bar,bar,bar,bar,3.43,4.71,bar,bar
bar,bar,bar,bar,bar,bar,3.30,4.85,bar,bar

TestResult-Run2.csv:

Code: Select all

foo,foo,foo,foo,foo,foo,CMMA,TAA,foo,foo
bar,bar,bar,bar,bar,bar,3.54,3.53,bar,bar
bar,bar,bar,bar,bar,bar,4.27,3.29,bar,bar
bar,bar,bar,bar,bar,bar,3.85,3.71,bar,bar
bar,bar,bar,bar,bar,bar,3.93,3.45,bar,bar

This is the Batch file:

Code: Select all

@echo off
setlocal EnableDelayedExpansion

for /F "delims=" %%f in ('dir /B /A-D *.csv') do (
   set "col7="
   set /A n=sum7=sum8=0
   for /F "usebackq tokens=7,8 delims=," %%a in ("%%f") do (
      if not defined col7 (
         set "col7=%%a"
         set "col8=%%b"
      ) else (
         set "val7=%%a" & set "val8=%%b"
         set /A "sum7+=!val7:.=!, sum8+=!val8:.=!, n+=1"
      )
   )
   set /A "avg7=sum7/n, avg8=sum8/n"
   ECHO ren "%%f" "%%~Nf_!col7!-!avg7:~0,-2!.!avg7:~-2!_!col8!-!avg8:~0,-2!.!avg8:~-2!.csv"
)

... and this is the output:

Code: Select all

ren "TestResult-Run1.csv" "TestResult-Run1_CMMA-3.46_TAA-4.67.csv"
ren "TestResult-Run2.csv" "TestResult-Run2_CMMA-3.89_TAA-3.49.csv"

Of course, if the real data have a different format that my guess data, this program will fail...

Antonio

md73code
Posts: 16
Joined: 29 Jul 2015 15:29

Re: .bat file to average 2 columns in .csv file, and print results to the file name.

#9 Post by md73code » 19 Sep 2017 11:06

    Your assumptions of my .csv file are absolutely spot on. I'm sorry for not providing an example of it, I will do a better job providing examples for future posts. I will try your .bat files and update here with the results.

    Thanks for your efforts! :D

    Here is an example of the .csv file and I've also attached it:

    Code: Select all

    EYE   EDGE        DISP         PROC        DISPZ       PROCZ,     CMAA        TAA
    0     0.96             0.013     0.202     0.016     0.216     1.41     0.72
    1     0.97             0.014     0.197     0.016     0.214     1.41        0.68
    Attachments
    TestResult-Run1.zip
    TestResult-Run1 Template
    (7.21 KiB) Downloaded 337 times
    Last edited by md73code on 19 Sep 2017 15:56, edited 3 times in total.

    aGerman
    Expert
    Posts: 4654
    Joined: 22 Jan 2010 18:01
    Location: Germany

    Re: .bat file to average 2 columns in .csv file, and print results to the file name.

    #10 Post by aGerman » 19 Sep 2017 12:10

    Manveer Dhillon wrote:Here is an example of the .csv file
    Nope. That's how it might look like in Excel or Open Office. We need to know how it looks like in a text editor.

    Manveer Dhillon wrote:and I've also attached it
    Nope. You have to put it in a ZIP archive to be able to upload it.

    Steffen

    md73code
    Posts: 16
    Joined: 29 Jul 2015 15:29

    Re: .bat file to average 2 columns in .csv file, and print results to the file name.

    #11 Post by md73code » 19 Sep 2017 15:55

    I've attached the .csv file as .zip.

    aGerman
    Expert
    Posts: 4654
    Joined: 22 Jan 2010 18:01
    Location: Germany

    Re: .bat file to average 2 columns in .csv file, and print results to the file name.

    #12 Post by aGerman » 19 Sep 2017 16:17

    OK seems you have commas plus additional spaces. Also the number of values in the file does exceed some batch limits for CirothUngol's batch tool. So I'd rather suggest to use a hybrid script.

    Code: Select all

    @if (@a)==(@b) @end /*

    @echo off &setlocal

    >"list.txt" (
      for %%i in (*.csv) do (
        set "file=%%i"
        setlocal EnableDelayedExpansion
        for /f "tokens=1,2" %%j in ('cscript //nologo //e:jscript "%~fs0" "!file!"') do (
          echo !file:~,-4!_CMAA-%%j_TAA-%%k.csv
        )
        endlocal
      )
    )

    goto :eof */

    var oFile = WScript.CreateObject('Scripting.FileSystemObject').OpenTextFile(WScript.Arguments(0)), cnt = 0, sumCMAA = 0., sumTAA = 0.;
    oFile.ReadLine();
    while (!oFile.AtEndOfStream) {
      var arr = oFile.ReadLine().split(/,[ ]*/);
      ++cnt;
      sumCMAA += parseFloat(arr[6]);
      sumTAA += parseFloat(arr[7]);
    }
    WScript.Echo((sumCMAA / cnt).toFixed(2).toString() + ' ' + (sumTAA / cnt).toFixed(2).toString());

    Steffen

    Aacini
    Expert
    Posts: 1885
    Joined: 06 Dec 2011 22:15
    Location: México City, México
    Contact:

    Re: .bat file to average 2 columns in .csv file, and print results to the file name.

    #13 Post by Aacini » 20 Sep 2017 15:37

    Aacini wrote:Of course, if the real data have a different format that my guess data, this program will fail...

    Manveer Dhillon wrote:Your assumptions of my .csv file are absolutely spot on.

    Not at all! My guess files:

    • Have commas as separators
    • The integer part of all numbers is greater than 0
    • Have two decimals

    Your real data file:

    • Have commas and spaces as separators
    • The integer part of TAA column is zero
    • Have six decimals

    After an extensive modification of my code in order to fulfill all these new specifications, this is the new code:

    Code: Select all

    @echo off
    setlocal EnableDelayedExpansion

    for /F "delims=" %%f in ('dir /B /A-D *.csv') do (
       set "col7="
       set /A n=sum7=sum8=0
       for /F "usebackq tokens=7,8 delims=, " %%a in ("%%f") do (
          if not defined col7 (
             set "col7=%%a" & set "col8=%%b"
          ) else (
             set "val7=%%a" & set "val8=%%b"
             set "val7=!val7:0.=!" & set "val8=!val8:0.=!"
             set /A "sum7+=!val7:.=!, sum8+=!val8:.=!, n+=1"
          )
       )
       set /A "avg7=sum7/n, avg8=sum8/n"
       set "avg7=0!avg7!" & set "avg8=0!avg8!"
       ECHO ren "%%f" "%%~Nf_!col7!-!avg7:~-7,1!.!avg7:~-6!_!col8!-!avg8:~-7,1!.!avg8:~-6!.csv"
    )

    ... and this is the output:

    Code: Select all

    ren "TestResult-Run1.csv" "TestResult-Run1_CMAA-1.450398_TAA-0.536898.csv"

    If you consider that Batch files can only perform arithmetic operations over integer numbers, then you should realize that the form of integer/decimal parts in the numbers is an extremely important information!

    Antonio

    md73code
    Posts: 16
    Joined: 29 Jul 2015 15:29

    Re: .bat file to average 2 columns in .csv file, and print results to the file name.

    #14 Post by md73code » 21 Sep 2017 09:33

    Thank you Steffen. Your code works very well as it creates a .txt file with all the .csv runs with it's averages! Very useful, thank you very much!

    Antonio, thank you for outlining the details required from me when writing problems on the forums. I will pay closer attention to the information that is required from me in order to acquire proper support from this community. I tried your code and placed the .bat file in the same directory as my .csv files however no renaming output was being generated. I also tried running the .bat file as administrator. Is there a certain way I should be running your code?

    Squashman
    Expert
    Posts: 4465
    Joined: 23 Dec 2011 13:59

    Re: .bat file to average 2 columns in .csv file, and print results to the file name.

    #15 Post by Squashman » 21 Sep 2017 09:52

    Manveer Dhillon wrote:Antonio, thank you for outlining the details required from me when writing problems on the forums. I will pay closer attention to the information that is required from me in order to acquire proper support from this community. I tried your code and placed the .bat file in the same directory as my .csv files however no renaming output was being generated. I also tried running the .bat file as administrator. Is there a certain way I should be running your code?


    Look at this line of code:

    Code: Select all

    ECHO ren "%%f" "%%~Nf_!col7!-!avg7:~-7,1!.!avg7:~-6!_!col8!-!avg8:~-7,1!.!avg8:~-6!.csv"

    What would you do to change that line of code to make it work for your needs?

    Post Reply