AWK - averaging $3 by info in $1


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting AWK - averaging $3 by info in $1
# 1  
Old 10-31-2008
AWK - averaging $3 by info in $1

Hello,

I have three columns of data of the format below:
<name> <volume> <size>
a 2 1.2
a 2 1.1
b 3 1.7
c 0.7 1.9
c 0.7 1.9
c 0.7 1.8

What I want as output is:
<name> <volume/number of sizes recorded> <average size>
a 1 1.15
a 1 1.15
....

i.e. I want get an average of column three, the size, for each name, divide the volume by the number of records represented by the name and then print name, volume scaled by number of records per name and average size.

So far I have this, which doesn't run properly:
Quote:
awk '
{
while (i <= NR) {
NR = NR + n
erup = $1
mod = 1
n = 0
while (mod > 0) {
if ($1 ~ erup) {
sum += $2
n++
i++
}
else {
print erup, sum/n
sum = 0
mod = 0
}
}
}}' test.d > avout
The challenge, as I see it, is to store information about the previous record in awk so that in looking at the next record it can decide whether to continue summing or stop, divide through to get the average, print and start over.

Any help would be greatly appreciated. Thank you,
# 2  
Old 10-31-2008
Use nawk or /usr/xpg4/bin/awk on Solaris.

Assuming you want to exclude the first line:

Code:
awk 'END {
  printf "%s %.2f %.2f\n", \
    n, v/c, s/c
  }      
!_[$1]++ && c { 
  printf "%s %.2f %.2f\n", \
    n, v/c, s/c
  c = 0    
  }
NR > 1 { 
  n = $1
  v = $2
  s += $3
  c ++
    }' infile

Given your data:

Code:
$ cat file
<name> <volume> <size>
a 2 1.2
a 2 1.1
b 3 1.7
c 0.7 1.9
c 0.7 1.9
c 0.7 1.8
$ awk 'END {
  printf "%s %.2f %.2f\n", \
    n, v/c, s/c
  }
!_[$1]++ && c {
  printf "%s %.2f %.2f\n", \
    n, v/c, s/c
  c = 0
  }
NR > 1 {
  n = $1
  v = $2
  s += $3
  c ++
    }' file
a 1.00 1.15
b 3.00 4.00
c 0.23 3.20

# 3  
Old 10-31-2008
Thank you very much for your help, it works and has saved me much time.
# 4  
Old 11-03-2008
Code:
nawk '{
_1[$1]++
_2[$1]+=$3
_l[NR]=$0
a=NR
}
END{
for(i=1;i<=a;i++){
	split(_l[i],tmp," ")
	temp=sprintf("%s",tmp[1])
	print tmp[1]," ",tmp[2]/_1[temp]," ",_2[temp]/_1[temp];
   }
}' file

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Help with awk to extract additional info

Hi I use multipath linux command to get LUNs info and find out if any failed. # multipath -ll >/tmp/mpfail # cat /tmp/mpfail multipath.conf line 109, invalid keyword: user_friendly_names multipath.conf line 153, invalid keyword: user_friendly_names multipath.conf line 193, invalid... (4 Replies)
Discussion started by: prvnrk
4 Replies

2. Shell Programming and Scripting

How to perform averaging of values for particular timestamp using awk or anythoing else??

I have a file of the form. 16:00:26,83.33 16:05:26,83.33 16:10:26,83.33 16:15:26,83.33 16:20:26,90.26 16:25:26,83.33 16:30:26,83.33 17:00:26,83.33 17:05:26,83.33 17:10:26,83.33 17:15:26,83.33 17:20:26,90.26 17:25:26,83.33 17:30:26,83.33 For the timestamp 16:00:00 to 16:55:00, I need to... (5 Replies)
Discussion started by: Saidul
5 Replies

3. Shell Programming and Scripting

Loop for row-wise averaging of multiple files using awk

Hello all, I need to compute a row-wise average of files with a single column based on the pattern of the filenames. I really appreciate any help on this. it would just be very difficult to do them manually as the rows are mounting to 100,000 lines. the filenames are as below with convention as... (2 Replies)
Discussion started by: ida1215
2 Replies

4. Shell Programming and Scripting

Averaging help in awk

Hi all, I have a data file like below, where Time is in the second column DATE TIME FRAC_DAYS_SINCE_JAN1 2011-06-25 08:03:20.000 175.33564815 2011-06-25 08:03:25.000 175.33570602... (10 Replies)
Discussion started by: gd9629
10 Replies

5. Shell Programming and Scripting

Hourly averaging using Awk

Hey all, I have a set of 5-second data as shown below. I need to find an hourly average of this data. date co2 25/06/2011 08:04:00 8.30 25/06/2011 08:04:05 8.31 25/06/2011 08:04:10 8.32 25/06/2011 08:04:15 8.33 25/06/2011 08:04:20 ... (5 Replies)
Discussion started by: gd9629
5 Replies

6. Shell Programming and Scripting

Averaging data every 30 mins using AWK

A happy Monday to you all, I have a .csv file which contains data taken every 5 seconds. I want to average these 5 second data points into 30 minute averages! date co2 25/06/2011 08:04 8.31 25/06/2011 08:04 8.32 25/06/2011 08:04 8.33... (18 Replies)
Discussion started by: gd9629
18 Replies

7. Shell Programming and Scripting

Averaging in increments using awk & head/tail

Hi, I only have a very limited understanding and experience with writing code and I was hoping I could get some help. I have a dataset of two columns (txt format, numbers in each row separated by a tab) Eg. 1 5 2 5 3 6 4 7 5 6 6 6 7 ... (5 Replies)
Discussion started by: Emred_Skye
5 Replies

8. UNIX for Dummies Questions & Answers

Averaging the rows using 'awk'

Dear all, I have the data in the following format. I want to do average of each NR= 5 (rows) for all the 3 ($1,$2, $3) columns and want to print average result in another file in the same format. I dont know how to write code for this in 'awk', can some one help me to write a code for this in... (1 Reply)
Discussion started by: arvindr
1 Replies

9. Shell Programming and Scripting

averaging column values with awk

Hello. Im just starting to learn awk so hang in there with me...I have a large text file formatted as such everything is in a single column ID001 value 1 value 2 value....n ID002 value 1 value 2 value... n I want to be able to calculate the average for values for each ID from the... (18 Replies)
Discussion started by: johnmillsbro
18 Replies

10. Shell Programming and Scripting

some info on awk please

i know this is a stupid question but...can awk do more than print? can i have it assign a value from a file list to a variable in the script? (1 Reply)
Discussion started by: MadHatter
1 Replies
Login or Register to Ask a Question

Featured Tech Videos