Home Search Forums Register Forum Rules Man Pages Unix Commands Linux Commands FAQ Members Search Today's Posts Mark Forums Read

 UNIX for Dummies Questions & Answers This forum is closed for new posts. Please post beginner questions to learn unix and learn linux in this forum UNIX for Beginners Questions & Answers

# Find the average based on similar names in the first column

## UNIX for Dummies Questions & Answers

#1
12-04-2012
 FelipeAd Registered User Join Date: Jan 2011 Last Activity: 4 May 2015, 5:02 AM EDT Posts: 53 Thanks: 6 Thanked 0 Times in 0 Posts
Find the average based on similar names in the first column

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)```

#2
12-04-2012
 Scott   Administrator Join Date: Jun 2009 Last Activity: 30 January 2017, 10:27 AM EST Location: Zürich Posts: 8,601 Thanks: 344 Thanked 1,093 Times in 949 Posts
What have you tried? Where are you stuck?
#3
12-04-2012
 FelipeAd Registered User Join Date: Jan 2011 Last Activity: 4 May 2015, 5:02 AM EDT Posts: 53 Thanks: 6 Thanked 0 Times in 0 Posts
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
#4
12-04-2012
 bakunin   Bughunter Extraordinaire Join Date: May 2005 Last Activity: 24 February 2017, 4:28 AM EST Location: In the leftmost byte of /dev/kmem Posts: 5,355 Thanks: 98 Thanked 1,462 Times in 1,089 Posts
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
#5
12-04-2012
 FelipeAd Registered User Join Date: Jan 2011 Last Activity: 4 May 2015, 5:02 AM EDT Posts: 53 Thanks: 6 Thanked 0 Times in 0 Posts
Sorry bakunin, I am not sure i understood what you said.
I found from a previous thread

HTML Code:
`http://www.unix.com/shell-programming-scripting/121566-averaging-multiple-columns.html`
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`