Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Average in awk based on time

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Average in awk based on time
# 1  
Old 07-30-2008
Average in awk based on time
Hi

I am looking for an awk script which can compute the average of the last column based on the date and time. The file looks:

Code:
site1,"2000-01-01 00:00:00", "2000-01-01 00:59:00",0.013
site2,"2000-02-01 01:00:00", "2000-02-01 01:59:00",0.035
site1,"2000-02-01 02:00:00", "2000-02-01 02:59:00",0.034
site3,"2000-03-01 03:00:00", "2000-03-01 03:59:00",0.021
site4,"2000-02-01 02:00:00", "2000-02-01 02:59:00",-999
site1,"2000-01-01 01:00:00", "2000-01-01 01:59:00",0.012
site5,"2000-05-01 03:00:00", "2000-05-01 03:59:00",0.034
........
site5,"2000-12-31 23:00:00", "2000-12-31 23:59:00",0.034

There are 5 sites totally, and each site has hourly data listed in column 4 (12 months totally). I need to compute the daily average of column 4 for each site for each day. Note: some site has -999 value, which shouldn't be included for calculating the daily average. I greatly appreciate your help.
Last edited by Don Cragun; 04-17-2016 at 12:32 AM.. Reason: Add CODE an ICODE tags.
# 2  
Old 07-30-2008
Try this:

Code:
awk -F, '$4>0{a[$1]+=$4;b[$1]++}END{for i in a){print "Average for " i " = " a[i]/b[i]}}' file

Regards
# 3  
Old 07-30-2008
get error
Thank you so much for quick response. But should I define "i" somewhere first? I got error message as below:

Code:
awk: $4>0{a[$1]+=$4;b[$1]++}END{for i in a}{print "Average for " i " = " a[i]/b[i]}
awk:                                ^ syntax error

Last edited by Don Cragun; 04-17-2016 at 12:33 AM.. Reason: Add CODE tags.
# 4  
Old 07-30-2008
Sorry, a typo, I forgot a parenthesis:

Code:
awk -F, '$4>0{a[$1]+=$4;b[$1]++}END{for(i in a){print "Average for " i " = " a[i]/b[i]}}' file

If you get errors now use nawk, gawk or /usr/xpg4/bin/awk on Solaris.

Regards
# 5  
Old 07-30-2008
Sorry, I guess I didn't make my question clear. I want to get the DAILY average output for each day in each month for each site, instead of one average output for each site. The format should look like below for site1:
Code:
site1,"2000-01-01 00:00:00", "2000-01-01 00:59:00",*
site1,"2000-01-02 00:00:00", "2000-01-02 00:59:00",*
site1,"2000-01-03 00:00:00", "2000-01-03 00:59:00",*
....
site1,"2000-12-31 00:00:00", "2000-12-31 00:59:00",*

Thank you very much.
Last edited by Don Cragun; 04-17-2016 at 12:34 AM.. Reason: Add CODE tags.
# 6  
Old 07-30-2008
I should read the question betterSmilie:
Code:
awk -F, '
  $4>0{
    split($2,s," ")
    e=s[1]
    a[e]+=$4
    b[e]++
  }
END{
  for(i in a){
    print "Average for " i " = " a[i]/b[i]
  }
}' file

Regards
Last edited by Franklin52; 07-30-2008 at 05:14 PM.. Reason: one more typo
# 7  
Old 07-30-2008
There is a " sign before the date, to remove it you can use the sub function:

Code:
awk -F, '
  $4>0{
    split($2,s," ")
    sub("\"',"",s[1])
    e=s[1]
    a[e]+=$4
    b[e]++
  }
END{
  for(i in a){
    print "Average for " i " = " a[i]/b[i]
  }
}' file

I don't see so it clear anymore, I must go to bed now..Smilie

Regards
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Emergency UNIX and Linux Support

Average columns based on header name

Hi Friends, I have files with columns like this. This sample input below is partial. Please check below for main file link. Each file will have only two rows. ... (8 Replies)
Discussion started by: jacobs.smith
8 Replies

2. Shell Programming and Scripting

Awk: time intervals based on epoch time

I have a list of epoch times delimited by "-" as follows: 1335078000 - 1335176700 1335340800 - 1335527400 1335771300 - 1335945600 1336201200 - 1336218000 The corresponding dates are: 20120422 1000 - 20120423 1325 20120425 1100 - 20120427 1450 20120430 1035 - 20120502 1100 ... (3 Replies)
Discussion started by: alex2005
3 Replies

3. Shell Programming and Scripting

KSH or AWK for fetching the first and the last line based on time.

Dears could anybody please help me with the below task as I am a newbie to programming. I have a text file with 4 columns (priv_ip,time,pub_ip). eg: 10.160.101.160,0708044510,22.203.195.151 10.160.101.160,0708044645,22.203.195.151 10.160.101.160,0708050410,22.203.195.151... (5 Replies)
Discussion started by: BrownBob
5 Replies

4. Shell Programming and Scripting

awk based script to find the average of all the columns in a data file

Hi All, I need the modification for the below mentioned code (found in one more post https://www.unix.com/shell-programming-scripting/27161-script-generate-average-values.html) to find the average values for all the columns(but for a specific rows) and print the averages side by side. I have... (4 Replies)
Discussion started by: ks_reddy
4 Replies

5. Shell Programming and Scripting

AWK: how to get average based on certain column

Hi, I'm new to shell programming, can anyone help me on this? I want to do following operations - 1. Average salary for each country 2. Total salary for each city and data that looks like - salary country city 10000 zzz BN 25000 zzz BN 30000 zzz BN 10000 yyy ZN 15000 yyy ZN ... (3 Replies)
Discussion started by: shell123
3 Replies

6. Shell Programming and Scripting

Average calculation based on number of rows

Dear users, I need your support, I have a file like this: 272134.548 6680572.715 272134.545 6680572.711 272134.546 6680572.713 272134.548 6680572.706 272134.545 6680572.721 272134.543 6680572.710 272134.544 6680572.715 272134.543 6680572.705 272134.540 6680572.720 272134.544... (10 Replies)
Discussion started by: Gery
10 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. Shell Programming and Scripting

Remove lines, Sorted with Time based columns using AWK & SORT

Hi having a file as follows MediaErr.log 84 Server1 Policy1 Schedule1 master1 05/08/2008 02:12:16 84 Server1 Policy1 Schedule1 master1 05/08/2008 02:22:47 84 Server1 Policy1 Schedule1 master1 05/08/2008 03:41:26 84 Server1 Policy1 ... (1 Reply)
Discussion started by: karthikn7974
1 Replies

9. UNIX for Dummies Questions & Answers

Average Time

Hi Guys, I am using a command $ runprt_req PPGUS_ROYXN1102 Output: **************** Start Date and Time End Date and Time*** PPGUS_ROYXN1102 01/15/2008 02:20:08 01/15/2008 04:54:50 PPGUS_ROYXN1102 01/12/2008 02:03:57 01/12/2008 04:22:10... (1 Reply)
Discussion started by: sambond
1 Replies

10. Shell Programming and Scripting

average transaction time

Hi all, I have large daily log file(s) that hold the times for requests and responses on different system requests. What I want to do is work out average transaction times for the day (one log = one day). The problem I'm having is figuring out how to skip rows, i've sorted the output by uniq... (2 Replies)
Discussion started by: nhatch
2 Replies
Login or Register to Ask a Question