UNIX for Dummies Questions & Answers

I have a table, say this:

Code:

name1  num1 num2 num3 num4
name2  num5 num6 num7 num8
name3  num1 num3 num4 num9
name2  num8 num9 num1 num2
name2  num4 num5 num6 num4
name4  num4 num5 num7 num8
name5  num1 num3 num9 num7
name5  num6 num8 num3 num4

I want a code that will sort my data according to the first column and for ALL columns with the same name, calculate the average of each of the corresponding columns. In this case it would be:

Code:

name1  num1 num2 num3 num4
name2  avg(5,8,4) avg(6,9,5) avg(7,1,6) avg(8,2,4)
name2  avg(5,8,4) avg(6,9,5) avg(7,1,6) avg(8,2,4)
name2  avg(5,8,4) avg(6,9,5) avg(7,1,6) avg(8,2,4)
name3  num1 num3 num4 num9
name4  num4 num5 num7 num8
name5  avg(1,6) avg(3,8) avg(9,3) avg(7,4)
name5  avg(1,6) avg(3,8) avg(9,3) avg(7,4)

What have you tried? Where are you stuck?

I started by using sort command, in particular

Code:

sort -d -k1,1 file

to find those lines that are repeated but then i am not sure how to distinguish between the lines with the same name after that

Once they are sorted you can read them line by line: as they are sorted already you can rely on all the identical key values coming one after the other. The underlying algorithm is a widely used and basic one and called: single group change and it works like this:

You have to remember your last key value. If the key value you read now is identical you are within the same group, so add the other values to sums or whatever you do within your groups.

If the key you read is not identical with the previous one you have to first end your last group - calculate any averages from the sums, etc. - then start with a new group.

Two things to take into account: when you read the first line your group changes (from "" to some value) but you should suppress group end-processing at this point, because otherwise you get a "ghost-group" with an empty key and all values zero/nil. Second, your last line will have to trigger a group change too, because otherwise the last group would not be processed.

I hope this helps.

bakunin

Sorry bakunin, I am not sure i understood what you said.
I found from a previous thread

that in order to find the average (after grep for name1) for a number of columns, the correct code would be:

Code:

 grep name1 file.txt|awk '{for (i=2;i<=NF;i++) s[i]+=$i}END{for(i=2;i in s;i++) printf("%.3f%c"),s[i]/NR,((i+1) in s) ?OFS:ORS}'

but it only prints the (average) number and not 'name 1' at the beginning of the line.
Can someone tell me how to do this?

I mean now the output is

Code:

avg1 avg2 avg3 avg4

but i want it to be

Code:

name1 avg1 avg2 avg3 avg4