How to sum two csv files with the same number of columns with the same number of rows?

[OpenFoam-User]

Hello

Suppose I have two csv file a.csv and b.csv that have exactly the same number of columns and the columns in both files have identical names. The number of rows in each file is the same.

Example:

$ cat a.csv
x, u, v, w
0.42, 0.52, 2.74, 0.15
3.74, 1.47, 9.61, 4.54
...
...

$ cat b.csv
x, u, v, w
0.30, 0.14, 7.02, 0.10
1.20, 3.00, 4.50, 1.27
...
...

I want to use miller to create a new csv file that have the same columns x, u, v, w but each value is the sum from the file a.csv and b.csv

Is it possible to achieve that using miller?

P.S: That's a minimal example. In practice I have about 360 csv files ( Not just two file a.csv and b.csv).

Thank you for your help.

[aborruso]

One way to do it:

• add a column with row id

mlr -I --csv cat -n a.csv b.csv

• then sum the fields grouping by row id

mlr --csv stats1 -a sum -g n -f x,u,v,w a.csv b.csv >out.csv

[OpenFoam-User]

Thank you for the suggestion, I started using miller just to avoid writing Python scripts. Anyway I managed to solve this this:

find . -type f -name '*.csv' -exec paste -d ',' {} + | mlr --csv clean-whitespace | mlr --csv put -f compute_average.dsl 

the DSL script:

$avg_x = $x;
$avg_u = $u;
$avg_v = $v;
$avg_w = $w;

for ( i = 2; i <= 360; i += 1 ) {
     $avg_x += $["x_".i];
     $avg_u += $["u_".i];
     $avg_v += $["v_".i];
     $avg_w += $["w_".i];
}

$avg_x /= 360.0;
$avg_u /= 360.0;
$avg_v /= 360.0;
$avg_w /= 360.0;

It works just fine. However, for large paths or many files, find command splits the arguments to paste command (due to the ARG_MAX imposed by linux kernel). So I have added used csplit command to split the output of paste and paste them again as columns before feeding them to miller.

In my tests, it works just fine (tested it with 3600 columns (not 360)) but it takes about 110 seconds to clean the whitespace (I know this could be done before, but I am just testing the scalability).

[aborruso]

@OpenFoam-User I have seen a related question of yours in qsv repo.

If you want the average of all fields in sample.txt, you can run

mlr --csv stats1 -a mean --fr '[a-z]' sample.txt

to get (I use below the xtab format for more readability)

x_mean   0.000000000000000044408920985006264
u_mean   5.002976612535
v_mean   -0.019865015783999993
w_mean   -0.000000000000000000000023235617205000107
x_2_mean 0.000000000000000044408920985006264
u_2_mean 5.00149209564
v_2_mean -0.0309067876134
w_2_mean 0.00000000000000000000018986114606899984
x_3_mean 0.000000000000000044408920985006264
u_3_mean 5.001749525595001
v_3_mean -0.03267468354135
w_3_mean 0.0000000000000000000002732197365555
x_4_mean 0.000000000000000044408920985006264
u_4_mean 5.00291567764
v_4_mean -0.03738831503970004
w_4_mean 0.00000000000000000000019563529018600015

To restore the original heading, with repeated field names you can do several ways, which I won't add, because I guess you know how.

[OpenFoam-User]

Hi,
I am not averaging the fields by rows. I am averaging the columns to get another column. Just like you do in numpy, suppose x1 is an array and x2 is an array the average I am looking for is (x1 + x2)/2 not x1.mean() or x2.mean().

[aborruso]

Hi @OpenFoam-User , sorry I didn't understand.

If you want stats by field, the verb is merge-fields. Running

cp sample.txt s.txt
for letter in x u v w; do
    mlr -I --csv merge-fields -a mean -r "^$letter" -o "$letter" s.txt
done

You get the below result. Is that what you want?

+---------------+--------------------+------------------------+-------------------------------------------+
| x_mean        | u_mean             | v_mean                 | w_mean                                    |
+---------------+--------------------+------------------------+-------------------------------------------+
| -3            | 9.44069664125      | -0.2352999882          | -0.000000000000000000005881840624500001   |
| -2.684210526  | 9.50254667525      | -0.2510976645          | -0.000000000000000000002304013223         |
| -2.368421053  | 9.51973616275      | -0.26370021815         | -0.00000000000000000000023483448692499998 |
| -2.052631579  | 8.6653580825       | -0.22341393717500002   | -0.0000000000000000000000420240421725     |
| -1.736842105  | 6.05930869025      | -0.10562411026750002   | -0.000000000000000000017359474640000002   |
| -1.421052632  | 3.6892838545       | -0.11762588592499999   | -0.0000000000000000000083190516435        |
| -1.105263158  | 2.06319222475      | -0.1884810984          | 0.000000000000000000018764003132499998    |
| -0.7894736842 | 1.19689255225      | -0.22443858607499997   | 0.00000000000000000002100529507           |
| -0.4736842105 | 0.8545023461       | -0.20399065154999999   | -0.000000000000000000016916397707499998   |
| -0.1578947368 | 0.77521426675      | -0.14321183294999998   | 0.00000000000000000000953148272075        |
| 0.1578947368  | 0.8124239400250001 | -0.07296024836         | 0.00000000000000000002719582025           |
| 0.4736842105  | 0.945130962925     | -0.0008525158872500001 | 0.00000000000000000000302078612425        |
| 0.7894736842  | 1.2292873625       | 0.06123258282499999    | -0.0000000000000000000081564344065        |
| 1.105263158   | 1.816084334        | 0.1006384882725        | -0.0000000000000000000027659138919999998  |
| 1.421052632   | 2.9574786177500005 | 0.099311316265         | 0.0000000000000000000007233641899999999   |
| 1.736842105   | 4.8556077365       | 0.090438445335         | -0.0000000000000000000003085912634        |
| 2.052631579   | 7.39626251575      | 0.166815004875         | -0.000000000000000000000147859521225      |
| 2.368421053   | 9.27927208525      | 0.298427682475         | -0.00000000000000000000321666197675       |
| 2.684210526   | 9.52589895825      | 0.3098934364           | 0.0000000000000000000054752611830000004   |
| 3             | 9.46149154775      | 0.29976577109999997    | -0.000000000000000000016885512465000003   |
+---------------+--------------------+------------------------+-------------------------------------------+

[OpenFoam-User]

That's exactly what I was looking for! Thank you
I avoid using for loop or bash features, I prefer using commands so I can chain them using pipes:
I have updated your command to remove the for loop.

mlr.exe  --csv --opprint --barred merge-fields -a mean -r "^x" -o "x" then merge-fields -a mean -r "^u" -o "u" then merge-fields -a mean -r "^v" -o "v" then merge-fields -a mean -r "^w" -o w sample.txt