LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 11-10-2011, 02:40 PM   #1
udiubu
Member
 
Registered: Oct 2011
Posts: 54

Rep: Reputation: Disabled
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
 
Old 11-10-2011, 02:48 PM   #2
druuna
LQ Veteran
 
Registered: Sep 2003
Posts: 10,532
Blog Entries: 7

Rep: Reputation: 2374Reputation: 2374Reputation: 2374Reputation: 2374Reputation: 2374Reputation: 2374Reputation: 2374Reputation: 2374Reputation: 2374Reputation: 2374Reputation: 2374
Hi,

Have a look at this:
Code:
awk '{ if (NR == 1 ) { Value = $3 } { print $1, $2, $3 - Value } }' infile
Hope this helps.
 
1 members found this post helpful.
Old 11-10-2011, 03:01 PM   #3
udiubu
Member
 
Registered: Oct 2011
Posts: 54

Original Poster
Rep: Reputation: Disabled
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-
 
Old 11-10-2011, 03:03 PM   #4
udiubu
Member
 
Registered: Oct 2011
Posts: 54

Original Poster
Rep: Reputation: Disabled
Yeah: basically NR > 1 would subtract each value to itself, all the way down.
 
Old 11-10-2011, 03:09 PM   #5
druuna
LQ Veteran
 
Registered: Sep 2003
Posts: 10,532
Blog Entries: 7

Rep: Reputation: 2374Reputation: 2374Reputation: 2374Reputation: 2374Reputation: 2374Reputation: 2374Reputation: 2374Reputation: 2374Reputation: 2374Reputation: 2374Reputation: 2374
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.
 
Old 11-10-2011, 04:43 PM   #6
Juako
Member
 
Registered: Mar 2010
Posts: 202

Rep: Reputation: 84
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

Last edited by Juako; 11-10-2011 at 04:44 PM.
 
Old 11-10-2011, 05:22 PM   #7
udiubu
Member
 
Registered: Oct 2011
Posts: 54

Original Poster
Rep: Reputation: Disabled
Ok Thanks Juako!
 
Old 11-10-2011, 08:03 PM   #8
David the H.
Bash Guru
 
Registered: Jun 2004
Location: Osaka, Japan
Distribution: Debian sid + kde 3.5 & 4.4
Posts: 6,823

Rep: Reputation: 1947Reputation: 1947Reputation: 1947Reputation: 1947Reputation: 1947Reputation: 1947Reputation: 1947Reputation: 1947Reputation: 1947Reputation: 1947Reputation: 1947
Do realize that the shell only does integer arithmetic though. If you have floating point numbers then you have to use awk.
 
Old 11-10-2011, 08:29 PM   #9
grail
Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 7,517

Rep: Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896
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)
 
Old 11-11-2011, 01:24 AM   #10
udiubu
Member
 
Registered: Oct 2011
Posts: 54

Original Poster
Rep: Reputation: Disabled
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-
 
Old 11-11-2011, 05:26 AM   #11
grail
Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 7,517

Rep: Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896
And the for loop is why?
 
Old 11-11-2011, 07:10 AM   #12
Juako
Member
 
Registered: Mar 2010
Posts: 202

Rep: Reputation: 84
Quote:
Originally Posted by David the H. View Post
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
 
Old 11-11-2011, 10:44 AM   #13
PTrenholme
Senior Member
 
Registered: Dec 2004
Location: Olympia, WA, USA
Distribution: Fedora, (K)Ubuntu
Posts: 4,150

Rep: Reputation: 330Reputation: 330Reputation: 330Reputation: 330
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.)
 
Old 11-11-2011, 10:50 AM   #14
grail
Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 7,517

Rep: Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896
Bingo
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Sum numbers in a column in a specific block Alkass Programming 3 07-15-2011 01:12 PM
[SOLVED] Change to capital first letter of every word over specific column cgcamal Programming 10 05-02-2010 07:22 AM
[SOLVED] Replace pattern in specific lines and column with AWK cgcamal Programming 10 04-26-2010 01:11 AM
[SOLVED] GtkCellRendererToggle toggles when clicking on other cell renderers in same column grumpybuffalo Programming 1 08-15-2009 07:16 PM
A macro for solve insert/delete merged cell/column kstan Linux - General 0 11-02-2008 11:01 PM


All times are GMT -5. The time now is 07:58 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration