[Solved] sum up third and second columns by 0 difference


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting [Solved] sum up third and second columns by 0 difference
# 1  
Old 09-16-2012
[Solved] sum up third and second columns by 0 difference

Hi Friends,

I have the following file

Code:
chr1 1 2 
chr1 2 3
chr1 3 4
chr1 4 5
chr1 5 6
chr1 19 20
chr1 20 21
chr1 21 22

I want to compare the third column of record 1 to second column of next record and if the difference is zero, consider its third column and match it to next record and so on. Finally, my output would be

Code:
chr1 1 6
chr1 19 22

Thanks in advance.

Last edited by jacobs.smith; 09-16-2012 at 01:05 PM.. Reason: forgot code tags and title
# 2  
Old 09-16-2012
Try this..

Code:
awk '{if(! s){a=$1FS$2;s=$3}else{if (($2-s) > 0){k=s;p=$2;s=$3}else {s=$3}}}END{print a,k"\n"$1,p,s}' file

This User Gave Thanks to pamu For This Post:
# 3  
Old 09-16-2012
Thanks for your time pamu. A small change.

I would like to match the chr (first column) column before calculating the difference.

For ex, for the same above input

Code:
chr1 1 2 
chr1 2 3
chr1 3 4
chr1 4 5
chr2 5 6
chr1 19 20
chr1 20 21
chr1 21 22

should yield me

Code:
chr1 1 5
chr2 5 6
chr1 19 22

# 4  
Old 09-16-2012
awk -f t.awk b

where t.awk:

Code:
BEGIN {
 a_min=9999999;
 a_max=-1;
 lr="";
}
{
 if (lr != $1 && length(lr)>0) {
  print lr " " a_min " " a_max;
  a_min=9999999;
  a_max=-1;
 }
 if ($2 < a_min) a_min=$2;
 if ($3 > a_max) a_max=$3;
 lr=$1;
}
END {
  print lr " " a_min " " a_max;
}


Last edited by rdrtx1; 09-16-2012 at 11:25 PM..
This User Gave Thanks to rdrtx1 For This Post:
# 5  
Old 09-16-2012
Perfect! Thanks to both of you.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to calculate difference of split and sum the difference

In the awk I am trying to subtract the difference $3-$2 of each matching $4 before the first _ (underscore) and print that value in $13. I think the awk will do that, but added comments. What I am not sure off is how to add a line or lines that will add sum each matching $13 value and put it in... (2 Replies)
Discussion started by: cmccabe
2 Replies

2. UNIX for Beginners Questions & Answers

Group by columns and add sum in new columns

Dear Experts, I have input file which is comma separated, has 4 columns like below, BRAND,COUNTRY,MODEL,COUNT NIKE,USA,DUMMY,5 NIKE,USA,ORIGINAL,10 PUMA,FRANCE,DUMMY,20 PUMA,FRANCE,ORIGINAL,15 ADIDAS,ITALY,DUMMY,50 ADIDAS,ITALY,ORIGINAL,50 SPIKE,CHINA,DUMMY,1O And expected output add... (2 Replies)
Discussion started by: ricky1991
2 Replies

3. Shell Programming and Scripting

Evaluate 2 columns, add sum IF two columns satisfy the condition

HI All, I'm embedding SQL query in Script which gives following output: Assignee Group Total ABC Group1 17 PQR Group2 5 PQR Group3 6 XYZ Group1 10 XYZ Group3 5 I have saved the above output in a file. How do i sum up the contents of this output so as to get following output: ... (4 Replies)
Discussion started by: Khushbu
4 Replies

4. Shell Programming and Scripting

Get the SUM of TWO columns SEPARATELY by doing GROUP BY on other columns

My File looks like: "|" -> Field separator A|B|C|100|1000 D|E|F|1|2 G|H|I|0|7 D|E|F|1|2 A|B|C|10|10000 G|H|I|0|7 A|B|C|1|100 D|E|F|1|2 I need to do a SUM on Col. 5 and Col.6 by grouping on Col 1,2 & 3 My expected output is: A|B|C|111|11100 (2 Replies)
Discussion started by: machomaddy
2 Replies

5. UNIX for Dummies Questions & Answers

[Solved] Sum operation

I have file input w34 AG1 2 184 w35 AG1 6 552 w35 BG1 12 0 w35 CD1 7 0 w36 CG1 4 0 my output should be w34 AG1 2 184 0.991 w35 AG1 6 552 0.991 w35 BG1 12 0 1.000 w35 CD1 7 0... (3 Replies)
Discussion started by: radius
3 Replies

6. Shell Programming and Scripting

Evaluate 2 columns, add sum IF two columns match on two rows

Hi all, I know this sounds suspiciously like a homework course; but, it is not. My goal is to take a file, and match my "ID" column to the "Date" column, if those conditions are true, add the total number of minutes worked and place it in this file, while not printing the original rows that I... (6 Replies)
Discussion started by: mtucker6784
6 Replies

7. UNIX for Dummies Questions & Answers

[Solved] Remove Duplicates and Sum in a spreadsheet

Hi all, I have a problem hoping someone can help me. I have this spreadsheet : PC.333 PC.444 PC.555 PC.666 PC.777 0 0 ... (2 Replies)
Discussion started by: Giorgio C
2 Replies

8. Shell Programming and Scripting

[SOLVED] Awk one line to sum up a function

I need help with debugging an error in my awk script. I have a shell script with variable named U_new_i and want to pass it to awk for use in a summation. The original file have the following content. cat test.txt -2445.7132000000 -2444.9349000000 -2444.3295000000 -2443.1814000000 ... (0 Replies)
Discussion started by: Quantum_Dot
0 Replies

9. Shell Programming and Scripting

Difference between cksum and sum in unix

i get 2 different values when i give cksum and sum command. Are they 2 different algorithms? can someone explain how this is different (2 Replies)
Discussion started by: nidhink
2 Replies

10. Shell Programming and Scripting

Sum of three columns - in 4N columns file

Hi All, happy new year. I have a file with 4xN columns like 0.0000e+00 0.0000e+00 7.199E+07 7.123E+07 6.976E+07 6.482E+07 5.256E+07 2.523E+07 0.0000e+00 0.0000e+00 8.641E+07 8.550E+07 8.373E+07 7.780E+07 6.309E+07 3.028E+07... (8 Replies)
Discussion started by: f_o_555
8 Replies
Login or Register to Ask a Question