LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (http://www.linuxquestions.org/questions/programming-9/)
-   -   Subtracting the value of a specific cell to the whole column (http://www.linuxquestions.org/questions/programming-9/subtracting-the-value-of-a-specific-cell-to-the-whole-column-912881/)

udiubu 11-10-2011 03:40 PM

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

druuna 11-10-2011 03:48 PM

Hi,

Have a look at this:
Code:

awk '{ if (NR == 1 ) { Value = $3 } { print $1, $2, $3 - Value } }' infile
Hope this helps.

udiubu 11-10-2011 04:01 PM

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-

udiubu 11-10-2011 04:03 PM

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

druuna 11-10-2011 04:09 PM

Hi,

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

Code:

awk '{ if (NR == 1 ) { Value = $3 } { print $1, $2, $3 - Value } }' infile
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.

Juako 11-10-2011 05:43 PM

I'll drop a Bash version:

Code:

#!/bin/bash
while read fst snd trd; do
    echo $fst $snd $(( trd-${val:=$trd} ))
done

$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:
Code:

./script < inputfile

udiubu 11-10-2011 06:22 PM

Ok Thanks Juako!

David the H. 11-10-2011 09:03 PM

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

grail 11-10-2011 09:29 PM

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)

udiubu 11-11-2011 02:24 AM

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-

grail 11-11-2011 06:26 AM

And the for loop is why?

Juako 11-11-2011 08:10 AM

Quote:

Originally Posted by David the H. (Post 4521151)
Do realize that the shell only does integer arithmetic though. If you have floating point numbers then you have to use awk.

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:

Code:

#!/bin/bash
while read fst snd trd; do
    [[ ! "$val" ]] && val=$trd
    echo $fst $snd $( bc <<< "$trd-$val" )
done


PTrenholme 11-11-2011 11:44 AM

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.)

grail 11-11-2011 11:50 AM

Bingo :)


All times are GMT -5. The time now is 06:48 PM.