Unix/Linux Go Back    


Shell Programming and Scripting BSD, Linux, and UNIX shell scripting — Post awk, bash, csh, ksh, perl, php, python, sed, sh, shell scripts, and other shell scripting languages questions here.

Average select rows

Shell Programming and Scripting


Reply    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 6 Days Ago   -   Original Discussion by ncwxpanther
ncwxpanther's Unix or Linux Image
ncwxpanther ncwxpanther is offline
Registered User
 
Join Date: Aug 2011
Last Activity: 8 December 2017, 11:24 AM EST
Posts: 134
Thanks: 43
Thanked 3 Times in 3 Posts
Average select rows

I have no idea how to even get started with this script.

I need to average field 3 for each of the unique identifiers found in field 1. However, I only want to average these rows when field 2 is equal to 1506 - 2000 (note that i replaced the values field 2 for security reasons, but the real data are sequential).


Code:
001001 1500 62.6283
001001 1501 65.3417
001001 1502 65.1475
001001 1503 63.805
001001 1504 63.9375
001001 1505 64.1858
001001 1506 62.29
001001 1507 64.5242
001001 1508 62.8933
001001 1509 64.0667
001001 1510 63.6867
.....

001003 1995 66.7417
001003 1996 66.1092
001003 1997 66.5117
001003 1998 68.6833
001003 1999 67.715
001003 2000 67.5867
001003 2001 66.9783
001003 2002 67.3217
001003 2003 66.7042
001003 2004 67.1367
001003 2005 67.3717
.....

I need the results appended to each respective row.


Code:
001001  1500   62.6283	63.49
001001  1501   65.3417	63.49
001001  1502   65.1475	63.49
001001  1503   63.805	63.49
001001  1504   63.9375	63.49
001001  1505   64.1858	63.49
001001  1506   62.29    63.49
001001  1507   64.5242	63.49
001001  1508   62.8933	63.49
001001  1509   64.0667	63.49
001001  1510   63.6867	63.49
.....			
	
001003  1995	66.7417	67.22
001003  1996	66.1092	67.22
001003  1997	66.5117	67.22
001003  1998	68.6833	67.22
001003  1999	67.715	67.22
001003  2000	67.5867	67.22
001003  2001	66.9783	67.22
001003  2002	67.3217	67.22
001003  2003	66.7042	67.22
001003  2004	67.1367	67.22
001003  2005	67.3717	67.22
.....

Any help is appreciated.
Sponsored Links
    #2  
Old Unix and Linux 6 Days Ago   -   Original Discussion by ncwxpanther
RudiC's Unix or Linux Image
RudiC RudiC is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 13 December 2017, 4:17 PM EST
Location: Aachen, Germany
Posts: 11,797
Thanks: 339
Thanked 3,659 Times in 3,357 Posts
Try

Code:
awk 'NR == FNR {if ($2 >=1506 && $2 <=2000) {SUM[$1] += $3; CNT[$1]++}; next} 
{print $0, SUM[$1]/CNT[$1]}' OFS="\t" OFMT="%.2f" file file

Sponsored Links
    #3  
Old Unix and Linux 5 Days Ago   -   Original Discussion by ncwxpanther
ncwxpanther's Unix or Linux Image
ncwxpanther ncwxpanther is offline
Registered User
 
Join Date: Aug 2011
Last Activity: 8 December 2017, 11:24 AM EST
Posts: 134
Thanks: 43
Thanked 3 Times in 3 Posts
Quote:
Originally Posted by RudiC View Post
Try

Code:
awk 'NR == FNR {if ($2 >=1506 && $2 <=2000) {SUM[$1] += $3; CNT[$1]++}; next} 
{print $0, SUM[$1]/CNT[$1]}' OFS="\t" OFMT="%.2f" file file

I have played around with this but could not get anything to output.
    #4  
Old Unix and Linux 5 Days Ago   -   Original Discussion by ncwxpanther
RudiC's Unix or Linux Image
RudiC RudiC is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 13 December 2017, 4:17 PM EST
Location: Aachen, Germany
Posts: 11,797
Thanks: 339
Thanked 3,659 Times in 3,357 Posts
Difficult to believe as what I got was EXACTLY what you posted as desired output. It's not in vain that posters in here are requested to show at least their OS and shell versions, if not the tools used versions.
Sponsored Links
    #5  
Old Unix and Linux 5 Days Ago   -   Original Discussion by ncwxpanther
ncwxpanther's Unix or Linux Image
ncwxpanther ncwxpanther is offline
Registered User
 
Join Date: Aug 2011
Last Activity: 8 December 2017, 11:24 AM EST
Posts: 134
Thanks: 43
Thanked 3 Times in 3 Posts
Quote:
Originally Posted by RudiC View Post
Difficult to believe as what I got was EXACTLY what you posted as desired output. It's not in vain that posters in here are requested to show at least their OS and shell versions, if not the tools used versions.
My apologies for the lack of information.

GNU Awk 3.1.7
Linux CentOS release 6.9 (Final)

Would the format of the input cause the issue? There is a single space between each field.
Sponsored Links
    #6  
Old Unix and Linux 5 Days Ago   -   Original Discussion by ncwxpanther
RudiC's Unix or Linux Image
RudiC RudiC is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 13 December 2017, 4:17 PM EST
Location: Aachen, Germany
Posts: 11,797
Thanks: 339
Thanked 3,659 Times in 3,357 Posts
I don't think so, as the FS default ((multiple) whitespace) is used.
Did you supply the input file TWICE? It needs two passes.

Last edited by RudiC; 5 Days Ago at 12:34 PM.. Reason: typo
Sponsored Links
    #7  
Old Unix and Linux 5 Days Ago   -   Original Discussion by ncwxpanther
ncwxpanther's Unix or Linux Image
ncwxpanther ncwxpanther is offline
Registered User
 
Join Date: Aug 2011
Last Activity: 8 December 2017, 11:24 AM EST
Posts: 134
Thanks: 43
Thanked 3 Times in 3 Posts
Oh I see. Thats the first time I have had to enter the input file twice. At first glance it appears to work as you said. Thanks for the help with this one!
Sponsored Links
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Average across rows with a condition jacobs.smith Shell Programming and Scripting 5 09-18-2014 04:18 AM
Average values of duplicate rows Sanchari Shell Programming and Scripting 6 08-23-2014 04:46 AM
average of rows with same value in the first column paolo.kunder Shell Programming and Scripting 8 08-29-2012 06:28 AM
Average calculation based on number of rows Gery Shell Programming and Scripting 10 06-14-2010 01:14 AM
Calculating the Number of Rows and Average pk_eee UNIX for Dummies Questions & Answers 6 12-11-2008 03:34 PM



All times are GMT -4. The time now is 10:33 PM.