help sum columns by break in first column with awk or sed or something.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting help sum columns by break in first column with awk or sed or something.
# 1  
Old 01-31-2009
help sum columns by break in first column with awk or sed or something.

I have some data that is something like this?

item: onhand counted location
ITEM0001 1 0 a1
ITEM0001 0 1 a2
ITEM0002 5 0 b5
ITEM0002 0 6 c1

I want to sum up the on hand and counded lines by item number for an output like:
item: onhand counted location difference
ITEM0001 1 1 a1,a2 0
ITEM0002 5 6 b5,c1 1


I would then filter this through awk with an if $2 <> $3 print $0 as I only want those lines with some difference between the onhand and counted numbers

Unfortunately, I dont have access to the program that creates the original data so I cant manipulate that and am stuck with multiple lines of output per item number.


I would very much appreciate any help. I dont know how to handle multiple input lines like this...

I prefer awk and sed and similar 'standard' utilities. if this must be done in perl or something then I will have much more learning to understand the help!

Thanks
# 2  
Old 01-31-2009
This is almost a FAQ. Search around here for more threads on "column sum". Meanwhile, it's easy enough to use awk to filter and then add:
Code:
awk '$2 != $3 { sum+=$2; } END { print sum }'  input.txt


Last edited by otheus; 01-31-2009 at 08:15 PM.. Reason: corrected syntax from <> to !=
# 3  
Old 01-31-2009
Quote:
Originally Posted by otheus
This is almost a FAQ. Search around here for more threads on "column sum". Meanwhile, it's easy enough to use awk to filter and then add:
Code:
awk '$2 <> $3 { sum+=$2; } END { print sum }'  input.txt

what's '<>' ? Smilie
Code:
awk '$2 != $3 { sum+=$2; } END { print sum }'  input.txt

# 4  
Old 01-31-2009
MySQL

Quote:
Originally Posted by vgersh99
what's '<>' ? Smilie
Um SQL ? Smilie The poster originally had that and I copied him. Doh. I corrected my post.
Thanks vgersh
# 5  
Old 02-01-2009
actual data

here is some actual data since I dont understand how this works.
Quote:
MIL 000067 1 .030 X 1.125 CONTACT TIP EACH 0 2 072-08 A
MIL 000067 1 .030 X 1.125 CONTACT TIP EACH 131 0 053-09 H088D4
MIL 000068 1 .035 X 1.125 CONTACT TIP EA 0 10 072-07 A
MIL 000068 1 .035 X 1.125 CONTACT TIP EA 327 0 053-10 H088D5
MIL 000069 1 .045 X 1.125 CONTACT TIP EA 0 6 072-09 A
MIL 000069 1 .045 X 1.125 CONTACT TIP EA 176 0 053-11 H088D6
this is the actual data I am working with. lets call it file 'data'. the numbers line up perfectly on the command line.

here is my script
Quote:
gawk '
{ FIELDWIDTHS = " 58 10 11 8 " }

$2 != $3
{ sum+=$2 }
{ print sum }' $1
I set fieldwidths as there are random spaces that mess up the process.

so $1 = all the detail up to the first number, $2 is on number and $3 is the other. $4 is the junk on the end.

I run this like
./script data

my output
Quote:
0
MIL 000067 1 .030 X 1.125 CONTACT TIP EACH 0 2 072-08 A
0
MIL 000067 1 .030 X 1.125 CONTACT TIP EACH 131 0 053-09 H088D4
131
MIL 000068 1 .035 X 1.125 CONTACT TIP EA 0 10 072-07 A
131
MIL 000068 1 .035 X 1.125 CONTACT TIP EA 327 0 053-10 H088D5
458
MIL 000069 1 .045 X 1.125 CONTACT TIP EA 0 6 072-09 A
458
MIL 000069 1 .045 X 1.125 CONTACT TIP EA 176 0 053-11 H088D6
634
here is the output. I see that it is adding up column $2 and printing it after the line but it does not reset the number on part number changes. I took the END out as I wanted the numbers after each part number.

How do I have the sum reset after each change in part number? (change in $1)

also, what is the appropriate way to sum both $2 and $3?

thanks!
# 6  
Old 02-01-2009
if that wont work.......?

If awk cant do this directly, does it make and sense to first take the part number which is the first 15 characters and dump it to a temp file, then run uniq on it. afterwards do something like:

#filter_sum.awk
/PARTNUM/ { sum+=$2}
END { print sum }
except instead of /PARTNUM/ so some sort of
for x in `cat sum.awk.tempfile`;do gawk '{ /$x/ { sum+=$2} END { print sum }'

i havent gotten it to work yet but need some opinions on whether this is the right way..
# 7  
Old 02-01-2009
Quote:
Originally Posted by syadnom
I set fieldwidths as there are random spaces that mess up the process.
I don't get this. awk (by default) separates fields by whitespace. If there are "random" spaces, this would adjust the column width, no?

But whatever, you have your fields:
Quote:
so $1 = all the detail up to the first number, $2 is on number and $3 is the other. $4 is the junk on the end.
...

How do I have the sum reset after each change in part number? (change in $1)

also, what is the appropriate way to sum both $2 and $3?
Code:
($1 != lastpartnum) { print lastpartnum, sum1,sum2; sum2=sum1=0; } 
($2 != $3) { sum1+=$2; sum2+=$3 }
{ lastpartnum=$1 }

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

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

2. Shell Programming and Scripting

Do replace operation and awk to sum multiple columns if another column has duplicate values

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... (12 Replies)
Discussion started by: as7951
12 Replies

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

4. Shell Programming and Scripting

Sum of columns using awk

Hello everyone I am a beginner in Shell scripting. Need your help to achieve desired result. I have a file (sample format below) 001g8aX0007jxLz xxxxxxxxxxxxxxx 9213974926411 CO-COMM-133 CO-L001-DLY 7769995578239 44938 1 1 ... (1 Reply)
Discussion started by: Rohit Mallah
1 Replies

5. Shell Programming and Scripting

Sum of Columns Base on First Column

Input :- Hd1;Hd2:hd3;Hd4;Hd5 X;1;2;3;4 Y;2;3;5;6 Z;3;5;6;7 X;10;11;24;16 Y;11;23;21;1 Z;10;13;14;15 X;0;1;2;0 K;0;0;0;0 K;0;0;0;0 I want Sum Data base on first column; Hd1;Hd2:hd3;Hd4;Hd5 X;11;14;29;20 Y;12;26;26;7 Z;13;18;20;22 K;0;0;0;0 (4 Replies)
Discussion started by: pareshkp
4 Replies

6. UNIX for Dummies Questions & Answers

Want the UNIX code - I want to sum of the 1st column wherever the first 2nd and 3rd columns r equal

I have the code for the below things.. File1 has the content as below 8859 0 subscriberCreate 18 0 subscriberPaymentMethodChange 1650 0 subscriberProfileUpdate 7668 0 subscriberStatusChange 13 4020100 subscriberProfileUpdate 1 4020129 subscriberStatusChange 2 4020307 subscriberCreate 8831... (5 Replies)
Discussion started by: Mahen
5 Replies

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

8. Shell Programming and Scripting

Need help with awk statement to break nth column in csv file into 3 separate columns

Hello Members, I have a csv file in the format below. Need help with awk statement to break nth column into 3 separate columns and export the changes to new file. input file --> file.csv cat file.csv|less "product/fruit/mango","location/asia/india","type/alphonso" need output in... (2 Replies)
Discussion started by: awk-admirer
2 Replies

9. Shell Programming and Scripting

sum multiple columns based on column value

i have a file - it will be in sorted order on column 1 abc 0 1 abc 2 3 abc 3 5 def 1 7 def 0 1 -------- i'd like (awk maybe?) to get the results (any ideas)??? abc 5 9 def 1 8 (2 Replies)
Discussion started by: jjoe
2 Replies

10. Shell Programming and Scripting

awk sum columns

can anyone help me how do i add the colums using awk seperated by character @. for eg i have 3@4 2@9 5@1 the result should be 10 14 i tried using { sum+= $1 } END { print sum } but it just gives the result 10. can anyone help me with this one thank you and best regards (7 Replies)
Discussion started by: phone_book
7 Replies
Login or Register to Ask a Question