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

[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?

[Squashman]

So are you expecting to do floating point math?

[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.

[Squashman]

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]

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]

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

[CirothUngol]

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]

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]

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!

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

[aGerman]

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.

and I've also attached it

Nope. You have to put it in a ZIP archive to be able to upload it.

Steffen

[md73code]

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

[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

[Aacini]

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

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]

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]

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?