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


 
Thread Tools Search this Thread
Top Forums 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
# 1  
Old 12-29-2014
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
Code:
8859 0 subscriberCreate
18 0 subscriberPaymentMethodChange
1650 0 subscriberProfileUpdate
7668 0 subscriberStatusChange
13 4020100 subscriberProfileUpdate
1 4020129 subscriberStatusChange
2 4020307 subscriberCreate
8831 0 subscriberCreate
14 0 subscriberPaymentMethodChange
1570 0 subscriberProfileUpdate
7878 0 subscriberStatusChange
12 4020100 subscriberProfileUpdate
1 4020129 subscriberStatusChange


I want to sum of the 1st column wherever the 2nd and 3rd columns are equal.
I want the output just like below, Like sum up the 1st and column wherever 2nd and 3rd columns are equal, then print the sum,2nd and 3rd column
Code:
17690 0 subscriberCreate
32 0 subscriberPaymentMethodChange
3220 0 subscriberProfileUpdate
15546 0 subscriberStatusChange
25 4020100 subscriberProfileUpdate
2 4020129 subscriberStatusChange
2 4020307 subscriberCreate


Last edited by vgersh99; 12-29-2014 at 05:27 PM.. Reason: code tags, please!
# 2  
Old 12-29-2014
now that we know what you "want", what exactly have you tried so far and where exactly are you stuck?
# 3  
Old 12-29-2014
Not what you want, but what you asked for:
Code:
a_sum=0
 
while read a b c
do
 if [ "$b" = "$c" ]
 then
  (( a_sum += a ))
 fi
done <File1

You want the sum of column 1 for every set of column 2 and 3 values.
Code:
bhist=''
 
( sort +1 file1 ; echo x )| while read a b
do
 if [ "$b" != "$bhist" ]
 then
  if [ "$bhist" != "" ]
  then
   echo "$a_sum $bhist"
  fi
  a_sum=0 bhist="$b"
  continue
 fi
 (( a_sum += a ))
done

But, of course, you could accumulate a_sum in an associative array keyed on b in ksh, bash or awk, then dump it.
These 2 Users Gave Thanks to DGPickett For This Post:
# 4  
Old 12-29-2014
I feel no one understand what i want exactly...

Let me explain me again.. Lets say one file has the below content, between ""
"
8859 0 subscriberCreate
18 0 subscriberPaymentMethodChange
1650 0 subscriberProfileUpdate
7668 0 subscriberStatusChange
13 4020100 subscriberProfileUpdate
1 4020129 subscriberStatusChange
2 4020307 subscriberCreate
8831 0 subscriberCreate
14 0 subscriberPaymentMethodChange
1570 0 subscriberProfileUpdate
7878 0 subscriberStatusChange
12 4020100 subscriberProfileUpdate
1 4020129 subscriberStatusChange
"

Field 1 represents count of "field 2 and 3"..
Ex in the first line of file, 8859 is the total count of "0 subscriberCreate"...
Like that in another file "0 subscriberCreate" has the count 8831...
So i want to add the count of "0 subscriberCreate" and print like as below,
17690 0 subscriberCreate


It means that the output like wherever the field 2 and 3 same in some lines, then 1st filed value should be added and printed as i said...

If possible, Please someone provide the code to reach out my target.

Thanks all for your inputs.
# 5  
Old 12-29-2014
Try

Code:
 
 awk '{arr[$2" "$3]+=$1} END {for (i in arr) {print arr[i],i}}'  infile

Your example

Code:
 
 $ cat > tmp1
 8859 0 subscriberCreate
 18 0 subscriberPaymentMethodChange
 1650 0 subscriberProfileUpdate
 7668 0 subscriberStatusChange
 13 4020100 subscriberProfileUpdate
 1 4020129 subscriberStatusChange
 2 4020307 subscriberCreate
 8831 0 subscriberCreate
 14 0 subscriberPaymentMethodChange
 1570 0 subscriberProfileUpdate
 7878 0 subscriberStatusChange
 12 4020100 subscriberProfileUpdate
 1 4020129 subscriberStatusChange
  
 $ awk '{arr[$2" "$3]+=$1} END {for (i in arr) {print arr[i],i}}' tmp1
 32 0 subscriberPaymentMethodChange
 17690 0 subscriberCreate
 2 4020307 subscriberCreate
 15546 0 subscriberStatusChange
 3220 0 subscriberProfileUpdate
 25 4020100 subscriberProfileUpdate
 2 4020129 subscriberStatusChange

This User Gave Thanks to senhia83 For This Post:
# 6  
Old 12-29-2014
Another bash attempt...
Code:
#!/bin/bash

hist=./hist
data=./abc.data
sort -k 2 -u $data -o $hist

while read a b
do
    a=0
    while read aa bb
    do
        if [[ "$bb" == $b ]]; then
            a=$(( $a + $aa ))
        fi
    done < $data
    printf "%s %s\n" $a "$b"
done < $hist
rm $hist

# output
# --------------------
# 17690 0 subscriberCreate
# 32 0 subscriberPaymentMethodChange
# 3220 0 subscriberProfileUpdate
# 15546 0 subscriberStatusChange
# 25 4020100 subscriberProfileUpdate
# 2 4020129 subscriberStatusChange
# 2 4020307 subscriberCreate


Last edited by ongoto; 12-29-2014 at 10:00 PM..
This User Gave Thanks to ongoto For This Post:
 
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

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

4. Linux

Print the 1st column and the value in 2nd or 3rd column if that is different from the values in 1st

I have file that looks like this, DIP-17571N|refseq:NP_651151 DIP-17460N|refseq:NP_511165|uniprotkb:P45890 DIP-17571N|refseq:NP_651151 DIP-19241N|refseq:NP_524261 DIP-19241N|refseq:NP_524261 DIP-17151N|refseq:NP_524316|uniprotkb:O16797 DIP-19588N|refseq:NP_731165 ... (2 Replies)
Discussion started by: Syeda Sumayya
2 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

1st column,2nd column on first line 3rd,4th on second line ect...

I need to take one column of data and put it into the following format: 1st line,2nd line 3rd line,4th line 5th line,6th line ... Thanks! (6 Replies)
Discussion started by: batcho
6 Replies

7. Shell Programming and Scripting

split on the basis of 2nd and 3rd column

file A aa 22 48 ab 22 48 tcf 50 76 gf 50 76 h 89 100 yh 89 100 how can we split the file on the basis of common 2 and third column output like file A-1 aa 22 48 ab 22 48 file A-2 cf 50 76 gf 50 76 (3 Replies)
Discussion started by: cdfd123
3 Replies

8. Shell Programming and Scripting

grep data on 2nd line and 3rd column

How do I grep/check the on-hand value on the second line of show_prod script below? In this case it's a "3". So if it's > 0, then run_this, otherwise, quit. > ./show_prod Product Status Onhand Price shoe OK 3 1.1 (6 Replies)
Discussion started by: joker_789us
6 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

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... (6 Replies)
Discussion started by: syadnom
6 Replies
Login or Register to Ask a Question