Sum column values based in common identifier in 1st column.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sum column values based in common identifier in 1st column.
# 8  
Old 10-22-2014
Same idea, small variation:
Code:
awk '
  function pr() {
    for(i=1; i<=NF; i++) $i=T[i]
    print
  }

  $1!=p {
    n=$0
    p=$1
    if(NR>1) pr()
    split(n,T)
    next
  }
 
  {
    for(i=2; i<=NF; i++) T[i]+=$i
  }

  END{
    pr()
  }
' OFS='\t' file

# 9  
Old 10-24-2014
Hello,

One more solution on same. This solution will take care of sequences of first column, lets say the first column values are not in a sequence as follows then following may help.

Input file is as follows.
Code:
cat get_sum.txt 
K00001 1 1 4 3 5
K00001 2 4 4 3 3
K00001 8 9 1 5 2
K00006 7 2 3 2 8
K00006 3 4 6 6 3
K00006 5 1 9 7 7
K00008 1 1 2 1 1
K00011 14 18 18 12 15
K00001 2 3 4 5 6
K00008 5 4 3 2 1

Code is as follows.
Code:
awk  '{for(i=1;i<=NF;i++){if(i==1){Q=$1} else {A[Q OFS i]+=$i}}} END{for(y in A){W=y;gsub(/[[:space:]].*/,X,W);while(p<=NF){D[W]=D[W] OFS A[W OFS p];p++};while(k<=NF){delete A[W OFS k];k++};p="";k=""} { for(t in D){print t OFS D[t]}}}' get_sum.txt

EDIT: Adding a non single liner approach for same.
Code:
awk  '
{for(i=1;i<=NF;i++)
	{if(i==1)
		{Q=$1} 
	else 
		{A[Q OFS i]+=$i}}
	} 
END{
for(y in A)
{W=y;
	gsub(/[[:space:]].*/,X,W);
	while(p<=NF){
			D[W]=D[W] OFS A[W OFS p];p++
		    }
	while(k<=NF){
			delete A[W OFS k];k++};p="";k=""
		    } 
{for(t in D)
	{print t OFS D[t]}
}
}' get_sum.txt

Output will be as follows.
Code:
K00001    13 17 13 16 16                            
K00011    14 18 18 12 15                            
K00006    15 7 18 15 18                            
K00008    6 5 5 3 2

Thanks,
R. Singh

Last edited by RavinderSingh13; 10-24-2014 at 05:08 PM.. Reason: Added a non single liner approach in solution
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Sum of a column as new column based on header in a script

Hello, I am trying to store sum of a column as a new column inside a file but have to find the column names dynamically I/p c1,c2,c3,c4,c5 10,20,30,40,50 20,30,40,50,60 If i want to find sum only column c1, c3 and output it as c6,c7 O/p c1,c2,c3,c4,c5,c6,c7 10,20,30,40,50,30,70... (6 Replies)
Discussion started by: mkathi
6 Replies

2. UNIX for Beginners Questions & Answers

Sum the values in the column using date column

I have a file which need to be summed up using date column. I/P: 2017/01/01 a 10 2017/01/01 b 20 2017/01/01 c 40 2017/01/01 a 60 2017/01/01 b 50 2017/01/01 c 40 2017/01/01 a 20 2017/01/01 b 30 2017/01/01 c 40 2017/02/01 a 10 2017/02/01 b 20 2017/02/01 c 30 2017/02/01 a 10... (6 Replies)
Discussion started by: Booo
6 Replies

3. UNIX for Dummies Questions & Answers

Match sum of values in each column with the corresponding column value present in trailer record

Hi All, I have a requirement where I need to find sum of values from column D through O present in a CSV file and check whether the sum of each Individual column matches with the value present for that corresponding column present in the trailer record. For example, let's assume for column D... (9 Replies)
Discussion started by: tpk
9 Replies

4. Shell Programming and Scripting

awk to sum a column based on duplicate strings in another column and show split totals

Hi, I have a similar input format- A_1 2 B_0 4 A_1 1 B_2 5 A_4 1 and looking to print in this output format with headers. can you suggest in awk?awk because i am doing some pattern matching from parent file to print column 1 of my input using awk already.Thanks! letter number_of_letters... (5 Replies)
Discussion started by: prashob123
5 Replies

5. Shell Programming and Scripting

Sum Of Column Based On Column Condition

I have a following inputfile MT,AP,CDM,TTML,MUM,GS,SUCC,3 MT,AP,CDM,TTSL,AP,GS,FAIL,9 MT,AP,CDM,RCom,MAH,GS,SUCC,3 MT,AP,CDM,RTL,HP,GS,SUCC,1 MT,AP,CDM,Uni,UPE,GS,SUCC,2 MT,AP,CDM,Uni,MUM,GS,SUCC,2 TTSL,AP,GS,MT,MAH,CDM,SUCC,20 TTML,AP,GS,MT,MAH,CDM,FAIL,10... (2 Replies)
Discussion started by: siramitsharma
2 Replies

6. Shell Programming and Scripting

common entries between files based on 1st column

Hi, I am trying to get the common entries from 2 files based on 1st field.. However when I try to do in perl I am getting blank output.. How can I do this in awk? open(BUFF1, "my_genes"); open(BUFF3, "rawcounts"); #open(WRBUFF,">result_rawcounts"); while($line =<BUFF1>) { ... (3 Replies)
Discussion started by: Diya123
3 Replies

7. Shell Programming and Scripting

How to averaging column based on first column values

Hello I have file that consist of 2 columns of millions of entries timestamp and throughput I want to find the average (throughput ) for each equal timestamp before change it to proper format e.g : i want to average 2 coloumnd fot all 1308154800 values in column 1 and then print... (4 Replies)
Discussion started by: aadel
4 Replies

8. Shell Programming and Scripting

print unique values of a column and sum up the corresponding values in next column

Hi All, I have a file which is having 3 columns as (string string integer) a b 1 x y 2 p k 5 y y 4 ..... ..... Question: I want get the unique value of column 2 in a sorted way(on column 2) and the sum of the 3rd column of the corresponding rows. e.g the above file should return the... (6 Replies)
Discussion started by: amigarus
6 Replies

9. Shell Programming and Scripting

merging column from two files based on identifier

Hi, I have two files consisting of two columns. So I want to merge column 2 if column 1 is the same. So heres an example of what I mean. FILE1 driver 444 car 333 hat 222 FILE2 driver 333 car 666 hat 999 So I want to merge the column 2's together so... (4 Replies)
Discussion started by: phil_heath
4 Replies

10. UNIX for Dummies Questions & Answers

How do I sum one column based on another column?

Hi, I am new to this forum and new to awk. I have a file that contains 2 columns. Heres an example of what it looks like: 10 + 20 + 40 + 50 - 70 - So the file is tab-delimited. What I want to do is add 10 to column 1 whenever column 2 is + and substract 10 from column 1... (1 Reply)
Discussion started by: phil_heath
1 Replies
Login or Register to Ask a Question