Shell Programming and Scripting

Hi All,

I got stuck up with shell script where i use awk. The scenario which i am working on is as below.

I have a file text.txt with contents

Code:

COL1 COL2 COL3 COL4
1 A 500 400
1 B 500 400
1 A 500 200
2 A 290 300
2 B 290 280
3 C 100 100

I could able to sum col 3 and col4 based on col1 using associative array as below.

Code:

awk '{a[$1]+=$3;b[$1]+=$4;c[$1]++} END {for (i in c) {print i,a[i]/c[i],b[i]/c[i]}}' Test.txt

But, all i need is to get the average on col3 based on col1 and col4 based on col2. I need the result like the below.

Code:

COL1 COL2 COL3
1 500 500
2 290 290
3 100 100

Still i tried this..

Code:

awk '{a[$1]+=$3;b[$1]+=$4;c[$1]++;d[$2]++} END {for (i in c) {print i,a[i]/c[i],b[i]/d[i]}}' Test.txt

But it seems to be not working.. Please advice me !!

Within your sample result, COL3 is not the average on col4 based on col2 but the sum of col4 based on col1.

From your requirement, I don't understand how you can mix in the same output value of col1, average based on col1 and average base on col2.


Jean-Pierre.

Thanks Jean-Pierre for the reply.

To understand the requirement, All the columns in the test.txt are independent and no relation between. in other words, it is source file.

the output derived from the file should be

TGT_COL1 TGT_COL2 TGT_COL3
1 500 500
2 290 290
3 100 100

ie: TGT_COL2 = sum of SRC_COL2 / unique count of SRC_COL1
TGT_COL3= sum of SRC_COL4 / unique count of SRC_COL2


PS : I have updated the test.txt in the subject post.

Thanks

Sorry, I still do not understand your request.
In your example, the column TGT_COL3 not seem to be an average but rather a sum.

Can you explain in details how you get your output.

Jean-Pierre.