Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Calculate average for repeated ID within a data

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Calculate average for repeated ID within a data
# 1  
Old 10-30-2013
Calculate average for repeated ID within a data
I have an awk script that gives the following output:

Code:
Average end-to-end transmission delay 2.7 to 5.7 is 0.635392 seconds
Average end-to-end transmission delay 2.1 to 5.1 is 0.66272 seconds
Average end-to-end transmission delay 2.1 to 5.1 is 0.691712 seconds
Average end-to-end transmission delay 2.6 to 5.6 is 0.716631 seconds
Average end-to-end transmission delay 2.8 to 5.8 is 0.740759 seconds
Average end-to-end transmission delay 2.9 to 5.9 is 0.764887 seconds
Average end-to-end transmission delay 2.2 to 5.2 is 0.792215 seconds
Average end-to-end transmission delay 2.2 to 5.2 is 0.821207 seconds
Average end-to-end transmission delay 2.3 to 5.3 is 0.850199 seconds
Average end-to-end transmission delay 2.3 to 5.3 is 0.880855 seconds
Average end-to-end transmission delay 2.4 to 5.4 is 0.910199 seconds
Average end-to-end transmission delay 2.4 to 5.4 is 0.941207 seconds
Average end-to-end transmission delay 2.7 to 5.7 is 0.966617 seconds
Average end-to-end transmission delay 2.0 to 5.0 is 0.990745 seconds
Average end-to-end transmission delay 2.8 to 5.8 is 1.01487 seconds
Average end-to-end transmission delay 2.5 to 5.5 is 1.0422 seconds
Average end-to-end transmission delay 2.1 to 5.1 is 1.06953 seconds
Average end-to-end transmission delay 2.1 to 5.1 is 1.09852 seconds
Average end-to-end transmission delay 2.1 to 5.1 is 1.12751 seconds
Average end-to-end transmission delay 2.1 to 5.1 is 1.15817 seconds
Average end-to-end transmission delay 2.6 to 5.6 is 1.19043 seconds
Average end-to-end transmission delay 2.6 to 5.6 is 1.22435 seconds
Average end-to-end transmission delay 2.2 to 5.2 is 1.25167 seconds
Average end-to-end transmission delay 2.2 to 5.2 is 1.28067 seconds
Average end-to-end transmission delay 2.2 to 5.2 is 1.30966 seconds
Average end-to-end transmission delay 2.2 to 5.2 is 1.34031 seconds
Average end-to-end transmission delay 2.3 to 5.3 is 1.37097 seconds
Average end-to-end transmission delay 2.3 to 5.3 is 1.40329 seconds
Average end-to-end transmission delay 2.3 to 5.3 is 1.43561 seconds
Average end-to-end transmission delay 2.3 to 6.3 is 3.8106 seconds
Average end-to-end transmission delay 2.7 to 6.7 is 3.83473 seconds
Average end-to-end transmission delay 2.4 to 6.4 is 3.85886 seconds
Average end-to-end transmission delay 2.8 to 6.8 is 3.88299 seconds
Average end-to-end transmission delay 2.9 to 6.9 is 3.90712 seconds
Average end-to-end transmission delay 2.5 to 6.5 is 3.93124 seconds
Average end-to-end transmission delay 2.6 to 6.6 is 3.95537 seconds
Average end-to-end transmission delay 2.7 to 6.7 is 3.9795 seconds
Average end-to-end transmission delay 2.0 to 6.0 is 4.00683 seconds
Average end-to-end transmission delay 2.0 to 6.0 is 4.03582 seconds
Average end-to-end transmission delay 2.8 to 6.8 is 4.05995 seconds
Average end-to-end transmission delay 2.1 to 6.1 is 4.08728 seconds
Average end-to-end transmission delay 2.1 to 6.1 is 4.11627 seconds
Average end-to-end transmission delay 2.2 to 6.2 is 4.1451 seconds
Average end-to-end transmission delay 2.2 to 6.2 is 4.1756 seconds
Average end-to-end transmission delay 2.9 to 6.9 is 4.20469 seconds
Average end-to-end transmission delay 2.3 to 6.3 is 4.23222 seconds
Average end-to-end transmission delay 2.3 to 6.3 is 4.26141 seconds
Average end-to-end transmission delay 2.4 to 6.4 is 4.28874 seconds
Average end-to-end transmission delay 2.4 to 6.4 is 4.31773 seconds
Average end-to-end transmission delay 2.5 to 6.5 is 4.34506 seconds
Average end-to-end transmission delay 2.5 to 6.5 is 4.37405 seconds
Average end-to-end transmission delay 2.6 to 6.6 is 4.40288 seconds
Average end-to-end transmission delay 2.6 to 6.6 is 4.43337 seconds
Average end-to-end transmission delay 2.7 to 6.7 is 4.46386 seconds
Average end-to-end transmission delay 2.7 to 6.7 is 4.49602 seconds
Average end-to-end transmission delay 2.0 to 6.0 is 4.52335 seconds
Average end-to-end transmission delay 2.0 to 6.0 is 4.55234 seconds
Average end-to-end transmission delay 2.0 to 6.0 is 4.58133 seconds
Average end-to-end transmission delay 2.0 to 6.0 is 4.61199 seconds

The time is calculated from (i.e. 2.X ) to (y.z ) where x,y,z could be any number. I have a cases where the same 2.x to the same y.z is repeated with different time value. I need to calculate average time . for example from the above sample we have :
Code:
Average end-to-end transmission delay 2.1 to 5.1 is 1.06953 seconds
Average end-to-end transmission delay 2.1 to 5.1 is 1.09852 seconds
Average end-to-end transmission delay 2.1 to 5.1 is 1.12751 seconds
Average end-to-end transmission delay 2.1 to 5.1 is 1.15817 seconds

I want the output to be instead :
Average end-to-end transmission delay 2.1 to 5.1 is 1.113433 seconds which is (1.06953+1.09852 +1.12751 +1.15817 )/4.

I want to do that for all of other values.
# 2  
Old 10-30-2013
try

Code:
$ awk '{A[$5,$7]+=$9;B[$5,$7]++}END{for(i in B){split(i,P,SUBSEP);print "Average end-to-end transmission delay "P[1],"to",P[2],"is",A[i]/B[i],"seconds"}}' file

Average end-to-end transmission delay 2.7 to 6.7 is 4.19353 seconds
Average end-to-end transmission delay 2.6 to 5.6 is 1.0438 seconds
Average end-to-end transmission delay 2.6 to 6.6 is 4.26387 seconds
Average end-to-end transmission delay 2.5 to 5.5 is 1.0422 seconds
Average end-to-end transmission delay 2.5 to 6.5 is 4.21678 seconds
Average end-to-end transmission delay 2.4 to 5.4 is 0.925703 seconds
Average end-to-end transmission delay 2.4 to 6.4 is 4.15511 seconds
Average end-to-end transmission delay 2.3 to 5.3 is 1.18818 seconds
Average end-to-end transmission delay 2.3 to 6.3 is 4.10141 seconds
Average end-to-end transmission delay 2.2 to 5.2 is 1.13262 seconds
Average end-to-end transmission delay 2.2 to 6.2 is 4.16035 seconds
Average end-to-end transmission delay 2.1 to 5.1 is 0.968027 seconds
Average end-to-end transmission delay 2.1 to 6.1 is 4.10177 seconds
Average end-to-end transmission delay 2.0 to 5.0 is 0.990745 seconds
Average end-to-end transmission delay 2.0 to 6.0 is 4.38528 seconds
Average end-to-end transmission delay 2.9 to 5.9 is 0.764887 seconds
Average end-to-end transmission delay 2.9 to 6.9 is 4.05591 seconds
Average end-to-end transmission delay 2.8 to 5.8 is 0.877814 seconds
Average end-to-end transmission delay 2.8 to 6.8 is 3.97147 seconds
Average end-to-end transmission delay 2.7 to 5.7 is 0.801004 seconds

# 3  
Old 10-30-2013
Yes it works. Thank you ...

One last modifications..How about if I want to calculate the first hit. for example instead of calculating the average, I want to filtered out the repeated occurrences. For example;

Code:
Average end-to-end transmission delay 2.1 to 5.1 is 1.06953 seconds
Average end-to-end transmission delay 2.1 to 5.1 is 1.09852 seconds
Average end-to-end transmission delay 2.1 to 5.1 is 1.12751 seconds
Average end-to-end transmission delay 2.1 to 5.1 is 1.15817 seconds

The output would be only the first hit;
Code:
Average end-to-end transmission delay 2.1 to 5.1 is 1.06953 seconds

and ignore the rest repeated occurrences.
# 4  
Old 10-30-2013
In that case try:
Code:
awk '!A[$5,$7]++' file

# 5  
Old 10-30-2013
Thanks .. It works
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Calculate the average per block.

My old school way is a one liner. And will search for average from SAR, to get the data receive rate. But, I dont think it is practical or accurate,. Because it calculates to off peak hours. I am planning to change it. My cron runs every 30 mins. When my cron runs, and my time is 14:47pm,, it will... (1 Reply)
Discussion started by: invinzin21
1 Replies

2. Shell Programming and Scripting

Calculate average, azimut and distance

Gents, Please i will to get the distance and azimut from 2 coordinates: Usig excel formula i get the correct values, but i will like to do it using awk. Example A 35089.0 50345.016 9 75 1 2101774 77 70 79 483911.6 2380106.9 137.4 1 1 6 1 A 35089.0 50345.01620 75... (8 Replies)
Discussion started by: jiam912
8 Replies

3. Shell Programming and Scripting

Calculate Average AWK

I want to calculate the average line by line of some files with several lines on them, the files are identical, just want to average the 3rd columns of those files.:wall: Example file: File 1 001 0.046 0.667267 001 0.047 0.672028 001 0.048 0.656025 001 0.049 ... (2 Replies)
Discussion started by: AriasFco
2 Replies

4. UNIX for Dummies Questions & Answers

Average for repeated elements in a column

I have a file that looks like this 452 025_E3 8 025_E3 82 025_F5 135 025_F5 5 025_F5 23 025_G2 38 025_G2 71 025_G2 9 026_A12 81 026_A12 10 026_A12 some of the elements in column2 are repeated. I want an output file that will extract the... (1 Reply)
Discussion started by: FelipeAd
1 Replies

5. Shell Programming and Scripting

AWK novice - calculate the average

Hi, I have the following data in a file for example: P1 XXXXXXX.1 YYYYYYY.1 ZZZ.1 P1 XXXXXXX.2 YYYYYYY.2 ZZZ.2 P1 XXXXXXX.3 YYYYYYY.3 ZZZ.3 P1 XXXXXXX.4 YYYYYYY.4 ZZZ.4 P1 XXXXXXX.5 YYYYYYY.5 ZZZ.5 P1 XXXXXXX.6 YYYYYYY.6 ZZZ.6 P1 XXXXXXX.7 YYYYYYY.7 ZZZ.7 P1 XXXXXXX.8 YYYYYYY.8 ZZZ.8 P2... (6 Replies)
Discussion started by: alex2005
6 Replies

6. Shell Programming and Scripting

Calculate average time using a script

Hello, I'm hoping to get some help on calculating an average time from a list of times (hour:minute:second). Here's what my list looks like right now, it will grow (I can get the full date or change the formatting of this as well): 07:55:31 09:42:00 08:09:02 09:15:23 09:27:45 09:49:26... (4 Replies)
Discussion started by: jaredhanks
4 Replies

7. Shell Programming and Scripting

calculate the average of time series data using AWK

Hi, I have two time series data (below) merged into a file. t1 and t2 are in unit of second I want to calculate the average of V1 every second and count how many times "1" in V2 is occur within a second Input File: t1 V1 t2 V2 10.000000... (5 Replies)
Discussion started by: nica
5 Replies

8. Programming

calculate average

I have a file which is 2 3 4 5 6 6 so i am writing program in C to calculate mean.. #include<stdio.h> #include<string.h> #include <math.h> double CALL mean(int n , double x) main (int argc, char **argv) { char Buf,SEQ; int i; double result = 0; FILE *fp; (3 Replies)
Discussion started by: cdfd123
3 Replies

9. HP-UX

calculate average of multiple line data

I have a question as below and i need to write a shell or perl script for this query:My Input file looks like below RNo Marks 12 50 15 70 18 80 12 40 13 55 18 88 13 75... (4 Replies)
Discussion started by: smacherla
4 Replies

10. UNIX for Dummies Questions & Answers

calculate average of column 2

Hi I have fakebook.csv as following: F1(current date) F2(popularity) F3(name of book) F4(release date of book) 2006-06-21,6860,"Harry Potter",2006-12-31 2006-06-22,,"Harry Potter",2006-12-31 2006-06-23,7120,"Harry Potter",2006-12-31 2006-06-24,,"Harry Potter",2006-12-31... (0 Replies)
Discussion started by: onthetopo
0 Replies
Login or Register to Ask a Question