Subtracting the value of a specific cell to the whole column
 

Dear all,

I'm looking for a command that could tell the program to subtract the numeric value of the first cell in a column to all the other values of the subsequent cells in that column. Please note that I need the command to be performed over several files - in which the value of the first cell always varies. Here is an example for alpha.txt file:

1 345 5678
1 452 3456879
1 234 45699
1 234 45699

..

The value in $3, first line (5678) should be subtracted to all the other values in the same column:

$3

1 345 0
1 452 3451201
1 234 40021
1 234 40021

..

I'm playing around with awk but without succeeding.. I was also thinking to print that cell to anothr file and subtract it to all the others, but still: how do I select it? A double loop?

Any advice is highly appreciated.

All the best,

Emiliano

Hi,

Have a look at this: Hope this helps.

Hi druuna,

YES! It works beautifully.. I'm so grateful.
I'm using a Mac terminal now, let's hope it will work tomorrow morning on my Linux machine..

Just one quick clarification:
in this command the use of the built-in NR simply means that there is one single value to take into consideration, right? So adding a recurive option, the program would go all the way down the column?

Thanks again and have a nice evening.

Best,

E-

Yeah: basically NR > 1 would subtract each value to itself, all the way down.

Hi,

I think you already get it, but just to be sure:

The blue part is only executed once (if the record number [NR] is 1, the first line in this case) and Value is set to the content of field number 3 ($3).
The green part is executed for all the records (all lines in this case).

BTW: You're welcome :)

Hope this clears things up a bit.

I'll drop a Bash version:

$val is substracted from the $trd, and ${:=} (nice smiley btw) is "use the variable on the left if it's defined, otherwise use the value on the right AND assign it to the variable on the left (and of course $val is undefined in the first evaluation).

use:

Ok Thanks Juako!

Do realize that the shell only does integer arithmetic though. If you have floating point numbers then you have to use awk.

Just for clarification, as the OP mentioned that there are several files, NR will only ever equal 1 for the first file read (or can be manually altered but we will
assume this is not going to happen). If the idea was to subtract the third column value from the first line in each file you will need to use FNR (this would
also require some more finagling in the bash version)

I'm just using a §{file} variable, and NR seems to work accordingly to the new file:


for file in alpha.txt beta.txt;
do

awk '{ if (FNR == 1 ) { Value = $3 } { print $1, $2, $3 - Value } }' ${file}

done

Best,

E-

And the for loop is why?

Although that's true, you can solve it with a command substitution to bc which has arbitrary precision. But the resulting code would probably be less efficient than with awk or another language that doesn't need to call an external program in the loop:

This too might work: awk '(FNR==1){val=0+$3}{print $1 $2 $3-val}' <list of files> (Which is, I believe, the point of grail's question.)

Bingo :)