How to write a script to perform some basic operations on multiple csv files

user_5778175 0
Viewed 256

I have a folder with multiple csv files inside, named progressively (starting from 00000.csv up a generic #####.csv). Each csv file has 4 columns and a variable number of rows, N.

What I'd like to do is to write a script of some kind to place inside the folder and that, when executed, reads each csv file progressively and at each step - for the i-th csv file - sum all the N values in the third column in order to obtain the value t and later all the N values in the fourth column in order to obtain the value q and then calculates the final value sqrt(t^2 + q^2) and prints it in the i-th row of a (txt, for example) file, to be generated inside the same folder all the csv files are.

I'd like to have something automated, a run-and-forget-it kind of approach, and not just a command to change every time.

Following @Ed Morton advice, I put here the code I managed to write so far:

#!/bin/bash
shopt -s nullglob
for f in *.csv
do
        cat "$f" | awk -F "," '{sum3 += $3} {sum4 += $4} {final = sqrt(sum3^2 + sum4^2)} END {print final}' > result.txt
done

Looks like it succeds somehow in doing what I need but the problem is that it only displays the correct value for the last csv file as it continously overwrites the previous one.

Suppose I have the following #####=3 csv files:

00000.csv

1.817675, 0.859327, 0.959465, 0.281827
4.264659, 3.040230, -0.787732, -0.616018
3.645565, 2.943500, -0.424509, -0.905424
0.603874, 3.858309, -0.302506, -0.953147
0.056403, 0.410131, 0.941520, 0.336956

00001.csv

1.762620, 0.775846, -0.550544, -0.834806
4.364223, 3.049563, 0.995636, 0.093324
3.675804, 2.848182, 0.302385, -0.953186
0.696330, 3.820203, 0.924550, -0.381060
0.154763, 0.428169, 0.983598, 0.180376

00002.csv

1.781079, 0.677564, 0.184586, -0.982816
4.264546, 3.057596, -0.996768, 0.080330
3.718724, 2.757861, 0.429205, -0.903207
0.733074, 3.913208, 0.367446, 0.930045
0.088634, 0.353155, -0.661285, -0.750135

What I'd like to get in the end would be the following result.txt file:

result.txt

1.895572658137904
3.262622157794096
1.761036700624096

Where, for example,

1.895572658137904 = sqrt[ (0.959465-0.787732-0.424509-0.302506+0.941520)^2 + (0.281827-0.616018-0.905424-0.953147+0.336956)^2 ]

and so on for the other values.

csv awk linux bash
2 Answers

Using GNU awk for ENDFILE and tested with your provided sample input/output:

awk -F ',' '
    { sum3 += $3; sum4 += $4 }
    ENDFILE { printf "%.15f\n", sqrt(sum3^2 + sum4^2); sum3=sum4=0 }
' *.csv
1.895572658137904
3.262622157794095
1.761036700624095

and with any awk:

awk -F ',' '
    { sum3[FILENAME] += $3; sum4[FILENAME] += $4 }
    END {
        for (i=1; i < ARGC; i++) {
            fname = ARGV[i]
            printf "%.15f\n", sqrt(sum3[fname]^2 + sum4[fname]^2)
        }
    }
' *.csv
1.895572658137904
3.262622157794095
1.761036700624095
user_17450010

OK I am not the best at this but I think this might work (I also did not understand the last part so I just threw the final number in the txt file):

for file in [here you put the path of the directory with csv files]/*.csv; do
t=0
q=0 
    for row in $file;do 
        tt=`echo "$row"|awk -F, '{print $3}'`
        tq=`echo "$row"|awk -F, '{print $4}'`
        t=`echo $((t + tt))`
        q=`echo $((q + tq))`
    done
t=`echo $((t ** 2))`
q=`echo $((q ** 2))`
##finalv is the variable for the final value  
finalv=`echo $((t + q))`
echo "$finalv" >> [here you put the path of the directory with csv files]/file.txt
done
user_120958780
Related