Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Column sum

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Column sum
# 1  
Old 07-25-2016
Column sum
I have a text file in below format

Code:
First Column Header |Second Column Header| 	Third Column Header|	Fourth Column Header| Fifth Column Header
DATA1|	1.4|22.80|	6.6|6.55
DATA2|	1.4|NA|	6.8|6.83
DATA3|	2.4|22.80|	4.5|	4.45
DATA4|	4.4|NA|	8.8|	8.77
DATA5|	5.4|22.80|	NA|	10.93

I need to sum 2nd,3rd,4th and 5th columns, but numbers only. Should ignore all strings like "NA" and space. Output like:

Code:
First Column Header |Second Column Header| 	Third Column Header|	Fourth Column Header| Fifth Column Header
DATA1|	1.4|22.80|	6.6|6.55
DATA2|	1.4|NA|	6.8|6.83
DATA3|	2.4|22.80|	4.5|	4.45
DATA4|	4.4|NA|	8.8|	8.77
DATA5|	5.4|22.80|	NA|	10.93
|15.00|68.40|26.7|37.53

# 2  
Old 07-26-2016
Hello ctrld,

Could you please try following and let me know if this helps.
Code:
awk -F"|" 'NR==1{print;next} {print;Q=Q?Q FS ($2+$3+$4+$5):($2+$3+$4+$5)} END{print Q}'  Input_file

Output will be as follows.
Code:
First Column Header |Second Column Header| 	Third Column Header|	Fourth Column Header| Fifth Column Header
DATA1|	1.4|22.80|	6.6|6.55
DATA2|	1.4|NA|	6.8|6.83
DATA3|	2.4|22.80|	4.5|	4.45
DATA4|	4.4|NA|	8.8|	8.77
DATA5|	5.4|22.80|	NA|	10.93
37.35|15.03|34.15|21.97|39.13

Although I am not at all sure how and in which sequence you have shown the sum of fields for each line in Input_file at very bottom of your shown expected output. My above code will give SUM of fields in sequence of lines from 2nd line to till last line, if above doesn't meet your requirements then please get back to us with complete requirement details and expected sample output(with details).

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
ctrld
# 3  
Old 07-26-2016
This may help you

Input
Code:
[akshay@localhost tmp]$ cat file
First Column Header |Second Column Header| 	Third Column Header|	Fourth Column Header| Fifth Column Header
DATA1|	1.4|22.80|	6.6|6.55
DATA2|	1.4|NA|	6.8|6.83
DATA3|	2.4|22.80|	4.5|	4.45
DATA4|	4.4|NA|	8.8|	8.77
DATA5|	5.4|22.80|	NA|	10.93

Script
Code:
[akshay@localhost tmp]$ cat test.awk
BEGIN{
        # if variable cols not defined exit with below message to user
	if(cols==""){ print "Please mention column number separated by comma" ; exit }

        # split string cols separated by comma and save columns to be added in array c
	split(cols,c,/,/)
}
FNR>1{
        # Skip header and loop through array c and sum up fields, save result in array s
	for(i in c)
		s[c[i]]+= $(c[i])
}1
END{
        # Loop through fields and print result
	for(i=1; i<=NF; i++){		
		printf("%s%s",(i==1?"":OFS),(i in s ? s[i] : "NA"))
	}
	print ""
}

How to Execute
Code:
[akshay@localhost tmp]$ awk -vFS='|' -vOFS='|' -vcols="2,3,4,5" -f test.awk file

Output
Code:
First Column Header |Second Column Header| 	Third Column Header|	Fourth Column Header| Fifth Column Header
DATA1|	1.4|22.80|	6.6|6.55
DATA2|	1.4|NA|	6.8|6.83
DATA3|	2.4|22.80|	4.5|	4.45
DATA4|	4.4|NA|	8.8|	8.77
DATA5|	5.4|22.80|	NA|	10.93
NA|15|68.4|26.7|37.53

Last edited by Akshay Hegde; 07-26-2016 at 02:08 AM.. Reason: To add comments for better reading
This User Gave Thanks to Akshay Hegde For This Post:
ctrld
# 4  
Old 07-26-2016
The following produces the output you said you want. (I don't understand why you want two digits after the decimal in the sum of field 2 since all of the input lines only have one digit after the decimal point in field 2; but the output matches what you requested.)
Code:
awk '
BEGIN {	FS = OFS = "|"
}
NR > 1 {for(i = 2; i <= 5; i++)
		s[i] += $i
}
1
END {	printf("%s%.2f%s%.2f%s%.1f%s%.2f\n", OFS, s[2], OFS, s[3], OFS, s[4],
	   OFS, s[5])
}' file

producing the output:
Code:
First Column Header |Second Column Header| 	Third Column Header|	Fourth Column Header| Fifth Column Header
DATA1|	1.4|22.80|	6.6|6.55
DATA2|	1.4|NA|	6.8|6.83
DATA3|	2.4|22.80|	4.5|	4.45
DATA4|	4.4|NA|	8.8|	8.77
DATA5|	5.4|22.80|	NA|	10.93
|15.00|68.40|26.7|37.53

As always, if you want to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk.
This User Gave Thanks to Don Cragun For This Post:
ctrld
# 5  
Old 07-26-2016
Thanks all. The first solution probably was for row sum. Second and Third perfectly worked. Third solution is more crisp and clear, I liked it the most. Thanks a ton guys.
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. Shell Programming and Scripting

awk to Sum columns when other column has duplicates and append one column value to another with Care

Hi Experts, Please bear with me, i need help I am learning AWk and stuck up in one issue. First point : I want to sum up column value for column 7, 9, 11,13 and column15 if rows in column 5 are duplicates.No action to be taken for rows where value in column 5 is unique. Second point : For... (1 Reply)
Discussion started by: as7951
1 Replies

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

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

5. Shell Programming and Scripting

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

Hi, I have a table to be imported for R as matrix or data.frame but I first need to edit it because I've got several lines with the same identifier (1st column), so I want to sum the each column (2nd -nth) of each identifier (1st column) The input is for example, after sorted: K00001 1 1 4 3... (8 Replies)
Discussion started by: sargotrons
8 Replies

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

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

8. UNIX for Dummies Questions & Answers

How to sum rows in e.g. column 1 by a category in e.g. column 2

Hi, I've shown an example of what I would like to achieve below. In the example file, I would like to sum the values in column 2 for each distinct category in column 3 (presumably making an array?) and print the sum as well as the category name and length (note:length always corresponds with... (8 Replies)
Discussion started by: auburn
8 Replies

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

10. Shell Programming and Scripting

Column Sum

Hi Friends :D Please help me to modify a small script. I have a file with columns like: xxx 200 xxx 10 yyy 150 yyy 1500 zzz 05 www 120 and so on I have to add the column 2 for a particular column 1. I am trying the script given below, it works fine but takes a lot of time... (2 Replies)
Discussion started by: vanand420
2 Replies
Login or Register to Ask a Question