Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to perform averaging of values for particular timestamp using awk or anythoing else??
# 1  
Old 09-30-2013
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 average the values (Hourly) and print it in another csv file. similarly for 17:00:00 to 17:55:00 and so on.. I need 24 values for 24 hours.
Last edited by Saidul; 09-30-2013 at 03:02 PM.. Reason: Wrong data
# 2  
Old 09-30-2013
16:00:00 to 16:00:30, or to 16:30:00? You only have 1 value every 5 minutes in your example, so 30 second averages would be a bit odd.

Assuming it's half-hourly, you could do something like:
Code:
$ awk '/^..:([012].|30)/{split($1,ti,":"); sum[ti[1]]+=$2;cnt[ti[1]]++} END {for (i in sum) {printf ("%d:xx:xx - %f\n", i, sum[i]/cnt[i])}}' FS="," RS="[ \n]" file
16:xx:xx - 84.320000
17:xx:xx - 98.605714

(Note - I changed the last value to 183.33, just to make them different)

EDIT: To process every timestamp just remove the regex (assuming you have no other lines in the file - otherwise you'll need something like /^..:..:../).
Last edited by CarloM; 09-30-2013 at 03:07 PM.. Reason: changed requirements
This User Gave Thanks to CarloM For This Post:
Saidul
# 3  
Old 09-30-2013
Hi CarloM.
Thanks for your reply.
That was typo there. Script is suppose to take hourly averages from 16:00:00 to 16:55:00 and so on for the remaining timestamps.


I ran the script you have posted. It is running fine but the manual answers are not similar to the ones obtained by the script.

Can you please explain tha above.
Many thanks in advance.
# 4  
Old 09-30-2013
What awk are you using? I get the same results manually & by script from your test data using GNU awk on cygwin.
# 5  
Old 09-30-2013
Not sure what you need explained, so here's it all:
Code:
awk     '/^..:[0-5][05]/        {split($1,ti,":")               # if the record starts with any two chars:00 - 55 in 05 increments
                                                                #     split rec into ti
                                 SUM[ti[1]]+=$2                 # sum second field into the SUM array indexed by the first ti element (= hour)
                                 CNT[ti[1]]++                   # keep number of summands
                                }
         END                    {for (i in SUM) {               # after file has been processed
                                    printf ("%d:00:00;%f\n",    # print the index i which is the hour
                                            i, SUM[i]/CNT[i])   # and the computed avg value
                                   }
                                }
        ' FS="," RS="[ \n]" file                                # field separator: comma; record sep: space or <newline>

Please be aware that CarloM's original proposal did compute just the avg for 00 - 30, not 00 - 55 as your edited spec required. That's why the the regex has to be modified, if you don't drop it entirely as suggested.
These 2 Users Gave Thanks to RudiC For This Post:
CarloM Saidul
# 6  
Old 10-01-2013
Thank you so much CarloM and RudiC.
The above was really helpful.

---------- Post updated at 01:55 AM ---------- Previous update was at 01:19 AM ----------
Last edited by Saidul; 10-01-2013 at 09:27 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Averaging each row with null values

Hi all, I want to compute for the average of a file with null values (NaN) for each row. any help on how to do it. the sample file looks like this. 1.4 1.2 1.5 NaN 1.6 1.3 1.1 NaN 1.3 NaN 2.4 1.3 1.5 NaN 1.5 NaN 1.2 NaN 1.4 NaN I need to do a row-wise averaging such that it will sum only... (14 Replies)
Discussion started by: ida1215
14 Replies

2. 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

3. 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

4. Shell Programming and Scripting

How to averaging column based on first column values

Hello I have file that consist of 2 columns of millions of entries timestamp and throughput I want to find the average (throughput ) for each equal timestamp before change it to proper format e.g : i want to average 2 coloumnd fot all 1308154800 values in column 1 and then print... (4 Replies)
Discussion started by: aadel
4 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

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... (3 Replies)
Discussion started by: itisthus
3 Replies
Login or Register to Ask a Question