Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Averaging each row with null values

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Averaging each row with null values
# 8  
Old 03-22-2012
Small tweek to Ygor's original awk which prevents issues when full rows of non-values are present:

Code:
awk '/[0-9]/ {a=b=0;for(i=1;i<=NF;i++)if($i+0==$i){a+=$i;b++};print a/b; next;} {print "NaN"; }' file1

Keeping with balajesuri's solution, the mean for a 'bad' row is NaN.

I also confirmed that balajesuri's perl is doing the right thing.
Last edited by agama; 03-22-2012 at 10:34 AM.. Reason: spelling correction
This User Gave Thanks to agama For This Post:
ida1215
# 9  
Old 03-22-2012
thanks agama, for the other option. Smilie
# 10  
Old 03-24-2012
Hi I'm sorry again, I have come across another problem with the computation of the mean for my data. On top of having entire rows equal null values (NaN), the data has also entire rows with valid values equal to 0 and so I'm having fatal error on division by zero. How to revise the code such that if entire row has values of NaN and zero, it will print NaN and 0, respectively.

Many many thanks.
# 11  
Old 03-24-2012
Small conditional before a/b in the print statement:

Code:
awk '/[0-9]/ {a=b=0;for(i=1;i<=NF;i++)if($i+0==$i){a+=$i;b++};print  b== 0 ? 0 :  a/b; next;} {print "NaN"; }' file1

This User Gave Thanks to agama For This Post:
ida1215
# 12  
Old 03-24-2012
NaN (="Not a Number") can be a special value POSIX Floating Point Problems - The GNU Awk User's Guide , so this adaptation may be necessary:
Code:
awk '{a=b=0; for(i=1;i<=NF;i++)if($i!="NaN"){a+=$i;b++}; print (b==0)?$1:a/b}' infile

See also:
Quote:
Historical implementations of awk did not support floating-point infinities and NaNs in numeric strings; e.g., "-INF" and "NaN" . However, implementations that use the atof() or strtod() functions to do the conversion picked up support for these values if they used a ISO/IEC 9899:1999 standard version of the function instead of a ISO/IEC 9899:1990 standard version. Due to an oversight, the 2001 through 2004 editions of this standard did not allow support for infinities and NaNs, but in this revision support is allowed (but not required). This is a silent change to the behavior of awk programs; for example, in the POSIX locale the expression:

("-INF" + 0 < 0)

formerly had the value 0 because "-INF" converted to 0, but now it may have the value 0 or 1.
http://pubs.opengroup.org/onlinepubs...l#tag_20_06_18
Last edited by Scrutinizer; 03-24-2012 at 02:16 PM..
These 3 Users Gave Thanks to Scrutinizer For This Post:
agama ctsgnb ida1215
# 13  
Old 03-24-2012
Thank you very much for all the help on this.
@ agama: what does the / [0-9] / do in the awk expression?
@ scrutinizer: thanks for the useful information of awk capabilities.
# 14  
Old 03-24-2012
[0-9] -> Is a character class which refers to any single character which is a digit in range 0 - 9.
So, /[0-9]/ is a pattern which means, if the input text which is being read contains a number, then match is a success.
These 2 Users Gave Thanks to balajesuri For This Post:
agama ida1215
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

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

2. Shell Programming and Scripting

Check null values column

hi, I had a small question.I had a file from which i need to extract data. I have written the below script to check if the file exists and if it exists extract requierd columns from the file. IFILE=/home/home01/Report_1.csv OFILE=/home/home01/name.csv.out1 if #Checks if file exists... (1 Reply)
Discussion started by: Vivekit82
1 Replies

3. Shell Programming and Scripting

File values alwaya null

Hi All , below is my shell program. !/bin/sh set -x #---------------------------------------------------------------------------------------- # Program : weekly_remove_icd_file.sh # Author : # Date : 04/06/2013 # Purpose : Execute the script to... (3 Replies)
Discussion started by: krupasindhu18
3 Replies

4. Shell Programming and Scripting

How to use sort with null values?

Hello everyone I am doing a join command. Obviously, before I need two files sorted first. ( Both files have headers and have about 2 million lines each one ) The problem is, one of the files has null values in the key to sort (which is the first filed ). For example I have the original... (4 Replies)
Discussion started by: viktor1985
4 Replies

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

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

7. Shell Programming and Scripting

sorting null values

Hi I have a file with the values abc res set kls lmn ops i want to sort this file with the null values at the bottom of the file OUTPUT should look like this abc kls lmn ops (6 Replies)
Discussion started by: vickyhere
6 Replies

8. UNIX for Advanced & Expert Users

How to Compare Null values??

Hi, Can someone help me comparing Null values. Scenario is as follows: I have a variable which "cache_prd" which can have either some integer or nothing(Null) if it is integer I have to again do some comparision but these comparisons give me this error:( "line 32: [: 95: unary operator... (3 Replies)
Discussion started by: Yagami
3 Replies

9. Shell Programming and Scripting

How to insert data befor some field in a row of data depending up on values in row

Hi I need to do some thing like "find and insert before that " in a file which contains many records. This will be clear with the following example. The original data record should be some thing like this 60119827 RTMS_LOCATION_CDR INSTANT_POSITION_QUERY 1236574686123083rtmssrv7 ... (8 Replies)
Discussion started by: aemunathan
8 Replies

10. 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
Login or Register to Ask a Question