LinuxQuestions.org
Latest LQ Deal: Linux Power User Bundle
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 01-17-2013, 09:45 AM   #1
jv61
LQ Newbie
 
Registered: May 2012
Posts: 24

Rep: Reputation: Disabled
How to sum only specific column values in a row using awk?


I have a file which looks like this

Code:
Ref1  BC1:10   BC2:25   BC3:2   BC4:5   BC5:2  PL:2  PL:2  ML:3 ML:2
Ref2  BC2:10   BC6:25   BC9:2   PL:2    PL:1   ML:3  ML:3
Ref3  BC5:2    PL:2     PL:20   ML:30   MLb:3
Ref4  BC7:10   BC8:24   BC3:20  BC4:3   BC5:3  PL:2  PL:20
I want to sum the values associated with "BC" tag and print the number of fields that has BC tag ignoring PL and ML tag values.

I tried using the following command to sum only the values associated with BC tag, but didn't give me the expected results.
Code:
awk 'FS=":"{sum=0;nf=0;for(i=1;i<=NF;i++)if($i~/BC/)nf++;{sum+=$i}; print sum,nf,$0}' File
My result file should look something like this

Code:
44  5 Ref1  BC1:10   BC2:25   BC3:2   BC4:5   BC5:2  PL:2  PL:2 ML:3 ML:2
37  3 Ref2  BC2:10   BC6:25   BC9:2   PL:2    PL:1   ML:3  ML:3
 2  1 Ref3  BC5:2    PL:2     PL:20   ML:30   MLb:3
60  4 Ref4  BC7:10   BC8:24   BC3:20  BC4:3   BC5:3  PL:2  PL:20
But at the moment using the above command my results look like the one given below. The number of fields with BC tag is counted correctly but the sum value associated with BC tag is incorrect.

Code:
 

0 5 Ref1  BC1:10   BC2:25   BC3:2   BC4:5   BC5:2  PL:2  PL:2  ML:3 ML:2
0 3 Ref2  BC2:10   BC6:25   BC9:2   PL:2    PL:1   ML:3  ML:3
0 1 Ref3  BC5:2    PL:2     PL:20   ML:30   MLb:3
0 5 Ref4  BC7:10   BC8:24   BC3:20  BC4:3   BC5:3  PL:2  PL:20
Any ideas of how to get the sum value for only specific fields in a row?

Thanks in advance

Last edited by jv61; 01-17-2013 at 10:22 AM.
 
Old 01-17-2013, 10:14 AM   #2
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,496

Rep: Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867
Maybe something like:
Code:
awk -F'[ :]*' '{for(i=2;i< NF;i+=2)if($i ~ /BC/){cnt++;sum+=$(i+1)}$0=sum" "cnt" "$0;sum=cnt=0}1' file
 
2 members found this post helpful.
Old 01-17-2013, 10:57 AM   #3
colucix
LQ Guru
 
Registered: Sep 2003
Location: Bologna
Distribution: CentOS 6.5 OpenSuSE 12.3
Posts: 10,509

Rep: Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978
Another one:
Code:
BEGIN {

  RS = "[[:space:]]+"
  FS = ":"
  
}

/^Ref/ {

  if ( n )
    printf "%4d %3d %s\n", sum, n, line
  
  n = 0
  sum = 0
  line = sprintf("%-6s",$0)
  
}

! /^Ref/ {

  line = sprintf("%s %-8s",line,$0)
  
}
  
/BC/ {

  n++
  sum += $2
  
}

END {

  printf "%4d %3d %s\n", sum, n, line
  
}
 
1 members found this post helpful.
Old 01-17-2013, 11:41 AM   #4
jv61
LQ Newbie
 
Registered: May 2012
Posts: 24

Original Poster
Rep: Reputation: Disabled
Thanks for the answers, problem solved
 
  


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] how to subtract selective values in multi row multi column (awk or sed ?) nricardo Linux - Newbie 2 10-20-2012 09:55 AM
[SOLVED] Script to sum values across columns if they have the same row title kmkocot Programming 7 08-05-2012 07:19 PM
sum up values from each columns (awk) lcvs Linux - Newbie 10 06-20-2012 04:16 AM
[SOLVED] Sum numbers in a column in a specific block Alkass Programming 3 07-15-2011 01:12 PM


All times are GMT -5. The time now is 01:13 AM.

Main Menu
Advertisement
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
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration