Page 1 of 2

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

Posted: 14 Sep 2017 16:11
by md73code
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?

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

Posted: 14 Sep 2017 20:57
by Squashman
So are you expecting to do floating point math?

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

Posted: 18 Sep 2017 12:43
by md73code
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.

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

Posted: 18 Sep 2017 13:11
by Squashman
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).

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

Posted: 18 Sep 2017 14:56
by md73code
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.

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

Posted: 18 Sep 2017 15:26
by aGerman
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

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

Posted: 19 Sep 2017 01:33
by CirothUngol
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.

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

Posted: 19 Sep 2017 08:31
by Aacini
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

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

Posted: 19 Sep 2017 11:06
by md73code
    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

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

    Posted: 19 Sep 2017 12:10
    by aGerman
    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

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

    Posted: 19 Sep 2017 15:55
    by md73code
    I've attached the .csv file as .zip.

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

    Posted: 19 Sep 2017 16:17
    by aGerman
    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

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

    Posted: 20 Sep 2017 15:37
    by Aacini
    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

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

    Posted: 21 Sep 2017 09:33
    by md73code
    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?

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

    Posted: 21 Sep 2017 09:52
    by Squashman
    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?