automated tests - Merge duplicate results, average them and generate one CSV file

Question

I'm looking for an approach of the following situation:

I am producing data for test runs in our laboratory.

The samples are measured in duplicate, every measurement generates a .CSV-file. The output name will be something like:

• 10891234.csv
• 10891234 duplo.csv
• 10891234 a/b .csv
• 10891234 1/2 .csv

The data it contains:

component,mass%,area,calc
C6:0,<0.1,n.a.,0.0000
C7:0,<0.1,0.031,0.0076
C8:0,<0.1,0.209,0.0514
C9:0,<0.1,n.a.,0.0000
C10:0,<0.1,0.270,0.0665
C11:0,<0.1,n.a.,0.0000
C12:0,0.3,1.224,0.3010

and 40 more rows

The two files needs to be merged and averged, where the final output needs to be at least row a and b (component/mass%). Row D, calc, contains the unrounded values, every component needs to be rounded to 1 decimal, or if below 0.1 it needs to be reported as "<0.1".

The software which generates the results is able to run a program like .bat-files. I've been programming some macro's in Excel, where you would have to paste the files, and then it averages; but this still is something that can be automated.. just not sure how I approach this.

question from:https://stackoverflow.com/questions/65890455/merge-duplicate-results-average-them-and-generate-one-csv-file

Answer 1

Waitting for answers