Sum of two columns in a file


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sum of two columns in a file
# 1  
Sum of two columns in a file

Hi,

I need to do a sum of two columns in a file where delimiter is |^

input

Code:
1|^2|^3|^4|^50|^2|^3|^100
2|^3|^4|^6|^100|^7|^2|^50
3|^4|^2|^3|^50|^6|^3|^50
4|^2|^5|^7|^25|^2|^8|^25

Output required:

Code:
 Sum of 2 columns 5 & 8 which is 450


Last edited by Scott; 08-13-2012 at 05:38 AM.. Reason: Code tags
# 2  
Try
Code:
awk 'BEGIN {FS="\|\^"} {sum+=$5+$8} END {print sum}' infile

# 3  
Hi RudiC,

Tried as suggested..

following is the issue
Code:
[root@host-6-113 ~]# awk 'BEGIN {FS="\|\^"} {sum+=$5+$8} END {print sum}' infile
awk: warning: escape sequence `\|' treated as plain `|'
awk: warning: escape sequence `\^' treated as plain `^'
0

---------- Post updated at 11:24 PM ---------- Previous update was at 10:50 PM ----------

Rudic,

tweaked the code you've provided.. It is giving me the desired result. Thanks !!

Code:
[root@host-6-113 ~]# awk 'BEGIN {FS="[|^]"} {sum+=$9+$15} END {print sum}' infile
450

But it looks like it is considering the delimiter |^ as two strings. Please suggest alternatives if any..
# 4  
Try
Code:
awk -F'[.|.][.^.]' ' {sum+=$5+$8} END {print sum}' infile

The '[!^]' is rejected by some versions of awk, but when it is accepted it is saying the field separator is a "|" or a "^". You want it to be a "|" immediately followed by a "^".
# 5  
Given awk's ability to evaluate a number up to the first non-digit, the carrot (^) can be used as the simple field separator:


Code:
awk -F "^" '{s += $5 + $8;} END { print s; }' input-file

# 6  
Quote:
Originally Posted by Jram
... following is the issue
Code:
[root@host-6-113 ~]# awk 'BEGIN {FS="\|\^"} {sum+=$5+$8} END {print sum}' infile
awk: warning: escape sequence `\|' treated as plain `|'
awk: warning: escape sequence `\^' treated as plain `^'
0

---------- Post updated at 11:24 PM ---------- Previous update was at 10:50 PM ----------

Code:
[root@host-6-113 ~]# awk 'BEGIN {FS="[|^]"} {sum+=$9+$15} END {print sum}' infile
450

There does not seem to be an issue for me - awk gave warnings but treated the FS as requested. The 0 result I guess was due to 0 input, sum (0)=0; in your second post you suddenly summed columns (9 & 15), totally different to your first example (5 & 8).
# 7  
According to POSIX, -F "|^" or any other mechanism that sets FS to the two characters <vertical-line><circumflex> produces undefined results because <vertical-line> is special in an extended regular expression (ERE) except when it appears in a bracket expression. Also in an ERE ^ is an anchor (even when it is not the first character in the ERE) except when it appears in a bracket expression, in a collating symbol ([.^.]), or equivalence class ([=^=]). So, any of the solutions here that set FS to a string starting with "|" is not portable. And any of the solution here that set FS to a string ending with "^" is not portable. Setting FS to "[|^]" is valid, but makes both <vertical-line> and <circumflex> field separators, not the two character string "|^". (This is why the solutions that set FS to "[|^]" had to sum fields 9 and 15 instead of fields 5 and 8. Note that this solution won't work if there are any <vertical-line> or <circumflex> characters in the input that aren't adjacent pairs and it that order).

Since "^" is an anchor in an ERE, setting FS to "^" produces undefined results. (It is anchoring the empty expression following the <circumflex> to the beginning of the string. But since this isn't a replacement [where an empty expression is replaced by the previously matched expression], the standards don't specify what this means and the results are not portable.)

I'm sure the solutions given all work on one or more implementations. Several of them fail with syntax errors when using Mac OS X's awk.

To portably search for "|^" as a field delimiter and sum the numeric values in the 5th and 8th columns, I think you need something like:
Code:
awk -F "[|][[.^.]]" ' {sum+=$5+$8}
END {print "Sum of 2 columns 5 & 8 which is",sum}' input

which should work on any system that meets the POSIX awk utility requirements.
This User Gave Thanks to Don Cragun For This Post:
Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #829
Difficulty: Medium
The JSON standard supports object references.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Need Optimization shell/awk script to aggreagte (sum) for all the columns of Huge data file

Optimization shell/awk script to aggregate (sum) for all the columns of Huge data file File delimiter "|" Need to have Sum of all columns, with column number : aggregation (summation) for each column File not having the header Like below - Column 1 "Total Column 2 : "Total ... ...... (2 Replies)
Discussion started by: kartikirans
2 Replies

2. UNIX for Beginners Questions & Answers

Copy columns from one file into another and get sum of column values and row count

I have a file abc.csv, from which I need column 24(PurchaseOrder_TotalCost) to get the sum_of_amounts with date and row count into another file say output.csv abc.csv- UTF-8,,,,,,,,,,,,,,,,,,,,,,,,, ... (6 Replies)
Discussion started by: Tahir_M
6 Replies

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

4. Shell Programming and Scripting

Sum of Columns

HI Guys, I gave Input file F.Txt ID H1 H2 H3 H4 H5 A 5 6 7 8 9 B 4 65 4 4 7 C 4 4 4 4 4 D 4 4 4 4 4 Output :- ID H1 H2 H3 H4 H5 Total 17 79 19 20 24 Sum of Each Columns (8 Replies)
Discussion started by: pareshkp
8 Replies

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

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

7. Shell Programming and Scripting

Sum numeric columns contained in a plain text file

Hi everyone, Here are the contents of a plain text file created by a SQL query: SUM(T.TRNQTY) COUNT(D.TRNSEQ) ---------------- ---------------- 1380 46 1393 59 2680 134 740 37 ... (5 Replies)
Discussion started by: gacanepa
5 Replies

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

9. UNIX for Dummies Questions & Answers

Sum of all columns in all files in one output file

If I have say 4 files like this: File1: 1 3 4 7 7 0 5 7 5 9 1 2 7 4 8 File2: 1 4 6 2 5 7 1 2 3 6 0 3 0 3 8 File3: (5 Replies)
Discussion started by: cosmologist
5 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

Featured Tech Videos