-1

I have large csv text files with multiple columns and I would like to apply an arithmetic transformation on, say, column11 using variables in column6 and column12.

The transformation would be as follows:

column11=column11*1000.*column12./(8.314*(column6+273.15))

where .* and ./ respectively denote elementwise multiplication and division of the values in each row.

Here is a sample of what the input text files look like:

"2015-11-11 00:00:00.00",59841,0.327,3.275,1.89275,32.048,9,2435.477,2308.886,15.03365,31.2365067891251,98.7333,253
"2015-11-11 00:00:00.10",59842,0.086,3.56975,2.20325,32.10205,10,2433.668,2298.364,15.03292,31.2299567962211,98.7473,253
"2015-11-11 00:00:00.20",59843,0.26575,3.343,1.8285,32.06717,11,2433.833,2294.418,15.03119,31.2436473758837,98.72864,253
"2015-11-11 00:00:00.30",59844,-0.1915,3.28175,1.793,32.12122,12,2433.668,2280.608,15.02593,31.2410875853554,98.7333,253
"2015-11-11 00:00:00.40",59845,-0.20375,3.447,2.0135,32.08286,13,2433.833,2276.991,15.02812,31.2245602421307,98.73796,253

Ideally, the result would be saved to the same file and the process would be repeated by looping over n files.

A solution or some ideas in awk, cut/paste or just bash would be of great help.

Buzz
  • 516
  • 7
  • 21
  • 2
    What are the two dots in your mathematical expression? – Cyrus Feb 19 '17 at 15:25
  • remove the dots, replace "column" to "$" and run `awk -F, '{...}1' file > outputfile`, where `...` is your code after replacements. – karakfa Feb 19 '17 at 15:29
  • A glance at any awk man page or any googled example would answer this question. Put a tiny bit of effort in. – Ed Morton Feb 19 '17 at 15:47
  • @karakfa Thanks. It worked: a_files=(path/to/files/*.csv) for ((i=0; i<"${#a_files[@]}"; i++)); do awk -F, '{$11=$11*1000*$12/(8.314*($6+273.15))}1' "${a_files[i]}" > filenew"$i".csv done Will accept it if you move it to the answer section. – Buzz Feb 19 '17 at 16:15
  • Look up the FILENAME attribute / variable for awk and you can do it without bash at all – grail Feb 19 '17 at 17:56
  • @grail Thanks. Will keep that in mind for future reference. – Buzz Feb 19 '17 at 19:10
  • @Cyrus elementwise multiplication – Buzz Feb 19 '17 at 19:11

1 Answers1

-1

you can write the main loop without arrays

 for f in path/to/files/*.csv
 do
    awk -F, '...' "$f" > "processed_$f"
 done
karakfa
  • 66,216
  • 7
  • 41
  • 56