Calculate average for rows in a text file


 
Thread Tools Search this Thread
Special Forums UNIX Desktop Questions & Answers Calculate average for rows in a text file
# 1  
Old 07-11-2012
Calculate average for rows in a text file

Dear Gurus,

I have tab-delimited text files with matrix containing values. The first column is a identifier and other columns have the corresponding values. I would like to calculate the average value (total number/number of entries) for all entries from 2nd column to the last column in row wise.

For example,
Code:
file 1
Id per1 per2 per3 
12 34.56 -0.54 12.5 
13 0.15 -0.06 0.05 
14 0.67 0.12 0.09 
15 0.05 0.06 -0.07

Please note some values are negative numbers. I want to have a result file
Code:
ID avg
12 15.50
13 0.14 
14 0.88
15 0.04

Can somebody suggest a script to deal with this? Thanks a lot indeed.
Moderator's Comments:
Mod Comment code tags please

Last edited by jim mcnamara; 07-11-2012 at 12:26 PM..
# 2  
Old 07-11-2012
Code:
awk 'NR==1{next}
       {printf("%s\t", $1  
        printf("%.2f\n", ($2 + $3 + $4)/3 }'  file1 > newfile

This User Gave Thanks to jim mcnamara For This Post:
# 3  
Old 07-11-2012
thanks a lot for the script Jim. It works if I have three columns to calculate. How can do it if the file has more than 100 columns? Is there a way to mention the range like column 1 to column 150 for example?
# 4  
Old 07-11-2012
Best to ask for what you need on the first go Smilie
Code:
awk 'NR==1 { next }
        { T=0
           for(N=2; N<=NF; N++) T+=$N;
           T/=(NF-1)
           print $1, T }' file > outfile

That will calculate all columns from the second column to the last. You could also have FIRST=2, LAST=150, and for(N=FIRST; N<=LAST; N++), then divide by (FIRST-LAST)
This User Gave Thanks to Corona688 For This Post:
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 time of one column

Hello dears, I have a log file with records like below and want to get a average of one column based on the search of one specific keyword. 2015-02-07 08:15:28 10.102.51.100 10.112.55.101 "kevin.c" POST ... (2 Replies)
Discussion started by: Newman
2 Replies

3. Shell Programming and Scripting

How to calculate average of two columns and copy into another file?

Hi, I need help with the awk command. I have a folder with aprox 500 files each one with two columns and I want to print in a new file, the average of column 1 and average of column 2 and the name of each file. Input files are: File-1: 100 99 20 99 50 99 50 99 File-2: 200 85... (3 Replies)
Discussion started by: Lokaps
3 Replies

4. Shell Programming and Scripting

Calculate average of rows between two specific patterns

Hi, I have a file like this: variableStep chrom=chrX span=1 92328 0 92329 0 92330 0 92331 0 92332 0 92333 0 ................ ................ ................ variableStep chrom=chrX span=1 45649610 -0.00386 45649611 1.56 45649612 -2.23 45649613 ... (2 Replies)
Discussion started by: Ranajit_Das
2 Replies

5. Shell Programming and Scripting

Calculate average from CSV file using PERL script

Hi All I have this csv file and I need to calculate the average of FPS. FPS:27.7420, Interval:1314184238772 FPS:25.9798, Interval:1314184242646 FPS:27.4772, Interval:1314184246311 FPS:26.1623, Interval:1314184250159 FPS:26.4515, Interval:1314184253972 FPS:31.5896, Interval:1314184257163... (24 Replies)
Discussion started by: sayachop
24 Replies

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

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

8. Shell Programming and Scripting

Calculate average of each of position of contents in a huge file

My input: >AAA_100 10 20 50 60 10 100 15 10 >AAA_100 20 20 50 60 20 100 15 10 >AAA_100 10 20 50 60 40 100 15 10 >AAA_100 40 20 50 60 10 100 15 10 . . . My Output 20 20 50 60 20 100 15 10 If I have a long list of file. I want to calculate average of each position inside the contents... (7 Replies)
Discussion started by: patrick87
7 Replies

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

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