Average of a column in multiple files


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Average of a column in multiple files
# 1  
Old 09-04-2012
Average of a column in multiple files

I have several sequential files with name stat.1000, stat.1001....to stat.1020 with a format like this

Code:
0.01 1 3822 4.97379915032e-14 4.96982253992e-09 0
0.01 3822 1 4.97379915032e-14 4.96982253992e-09 0
0.01 2 502 0.00993165137406 993.165137406 0
0.01 502 2 0.00993165137406 993.165137406 0
0.01 4 33 0.00189645523539 189.645523539 0
0.01 33 4 0.00189645523539 189.645523539 0
0.01 4 548 0.00357382134942 357.382134942 0
0.01 548 4 0.00357382134942 357.382134942 0
0.01 4 1225 0.00088154447822 88.154447822 0
0.01 1225 4 0.00088154447822 88.154447822 0
0.01 4 868 0.00295939726649 295.939726649 0
0.01 868 4 0.00295939726649 295.939726649 0

I know I can find the average of the 9th column by using

Code:
awk '{sum+=$9} END {print sum/NR}'

But I would like to find the average of the 9th column in each file and put them in a new file (in that order). Can someone pls help with this problem?

Last edited by Scrutinizer; 09-21-2012 at 11:28 AM.. Reason: code tags instead of quote tags
# 2  
Old 09-04-2012
Try something like this..

Code:
while read line
do
awk '{sum+=$9} END {print sum/NR}' $line >> file_new
done<file_list

# 3  
Old 09-04-2012
Code:
awk 'END { print sum / fnr }
NR > 1 && FNR == 1 {
  print sum/fnr
  sum = x
  }
{ 
  sum += $9 
  fnr = FNR
  }' > new_file file1 file2 ... filen

You may need to add 0 divisor handler.

With GNU awk >= 4 you could use the ENDFILE pattern:

Code:
{ sum += $9 }
ENDFILE { 
  print sum/FNR 
  sum = x
  }

This User Gave Thanks to radoulov For This Post:
# 4  
Old 09-21-2012
Thanks radoulov,

I tried your solution but it doesnt seem to catch it. What I actually want to write the average of the 9th column in each of my stat files to a new file as a single column. I have 250 files in all (in the same folder) numbered stat.1000, stat 1001, stat.1002 ....stat.1250

Could you please help with this?
# 5  
Old 09-21-2012
Code:
awk 'END { print sum / fnr > fn }
NR > 1 && FNR == 1 {
  print sum/fnr > fn
  close(fn)
  sum = x
  }
{ 
  sum += $5 
  fnr = FNR
  fn = "avg_" FILENAME
  }' stat.*

With GNU awk in most cases you don't need to bother with closing the output files.
# 6  
Old 09-21-2012
Quote:
Originally Posted by radoulov
With GNU awk in most cases you don't need to bother with closing the output files.
That's system-dependent, not awk-dependent. If you install gawk on a foreign system, that won't increase the system's maximum-open-files limit.
# 7  
Old 09-21-2012
Quote:
Originally Posted by Corona688
That's system-dependent, not awk-dependent. If you install gawk on a foreign system, that won't increase the system's maximum-open-files limit.
Sorry for not being clear. Some awk implementations have their own (rather low) open files limits:

Code:
bash-2.03$ uname -s
SunOS
bash-2.03$ ulimit -n
1024
bash-2.03$ awk '{ for (i = 0; ++i <= 1000;) print > i }' /etc/issue
awk: too many output files 10
 record number 1
bash-2.03$ nawk '{ for (i = 0; ++i <= 1000;) print > i }' /etc/issue
nawk: 21 makes too many open files
 input record number 1, file /etc/issue
 source line number 1
bash-2.03$ /usr/xpg4/bin/awk '{ for (i = 0; ++i <= 1000;) print > i }' /etc/issue
/usr/xpg4/bin/awk: line 0 (NR=1): output file "253": Too many open files

This User Gave Thanks to radoulov For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to copy a column of multiple files and paste into new excel file (next to column)?

I have data of an excel files as given below, file1 org1_1 1 1 2.5 100 org1_2 1 2 5.5 98 org1_3 1 3 7.2 88 file2 org2_1 1 1 2.5 100 org2_2 1 2 5.5 56 org2_3 1 3 7.2 70 I have multiple excel files as above shown. I have to copy column 1, column 4 and paste into a new excel file as... (26 Replies)
Discussion started by: dineshkumarsrk
26 Replies

2. Shell Programming and Scripting

Select multiple column from multiple files

Hi Friends, $ cat test1.txt emeka:1438 shelley:1439 dmeyer:1440 kurtarn:1441 abdul:1442 $ cat test2.txt 1:a 2:b 3:c 4:d $ cat test3.txt cat:dog:bat man:hot:cold (5 Replies)
Discussion started by: Jewel
5 Replies

3. Shell Programming and Scripting

Match first two columns and average third from multiple files

I have the following format of input from multiple files File 1 24.01 -81.01 1.0 24.02 -81.02 5.0 24.03 -81.03 0.0 File 2 24.01 -81.01 2.0 24.02 -81.02 -5.0 24.03 -81.03 10.0 I need to scan through the files and when the first 2 columns match I... (18 Replies)
Discussion started by: ncwxpanther
18 Replies

4. Shell Programming and Scripting

Check first column - average second column based on a condition

Hi, My input file Gene1 1 Gene1 2 Gene1 3 Gene1 0 Gene2 0 Gene2 0 Gene2 4 Gene2 8 Gene3 9 Gene3 9 Gene4 0 Condition: If the first column matches, then look in the second column. If there is a value of zero in the second column, then don't consider that record while averaging. ... (5 Replies)
Discussion started by: jacobs.smith
5 Replies

5. Shell Programming and Scripting

Calculate the average of a column based on the value of another column

Hi, I would like to calculate the average of column 'y' based on the value of column 'pos'. For example, here is file1 id pos y c 11 1 220 aa 11 4333 207 f 11 5333 112 ee 11 11116 305 e 11 11117 310 r 11 22228 781 gg 11 ... (2 Replies)
Discussion started by: jackken007
2 Replies

6. Shell Programming and Scripting

Column extraction from multiple files to multiple files

I have roughly ~30 .txt files in a directory which all have unique names. These files all contain text arranged in columns separated by whitespace (example file: [#YY MM DD hh mm WDIR WSPD GST WVHT DPD APD MWD PRES ATMP WTMP DEWP VIS TIDE #yr mo dy hr mn degT m/s m/s m sec ... (5 Replies)
Discussion started by: aozgaa
5 Replies

7. Shell Programming and Scripting

Computing average and standard deviation from multiple text files

Hello there, I found an elegant solution to computing average values from multiple text files awk '{for (i=1;i<=NF;i++){if ($i!~"n/a"){a+=$i}else{b++}}}END{for (i=1;i<=FNR;i++){for (j=1;j<=NF;j++){printf (a/(3-b))((b>0)?"~"b" ":" ")};printf "\n"}}' file1 file2 file3 I tried to modify... (2 Replies)
Discussion started by: charmmilein
2 Replies

8. Shell Programming and Scripting

Computing average values from multiple text files

Hi, first, I have searched in the forum for this, but I could not find the right answer. (There were some similar threads, but I was not sure how to adapt the ideas.) Anyway, I have a quite natural problem: Given are several text files. All files contain the same number of lines and the same... (3 Replies)
Discussion started by: rbredereck
3 Replies

9. Shell Programming and Scripting

Help in extracting multiple files and taking average at same time

Hi, I have 20 files which have respective 50 lines with different values. I would like to process each line of the 50 lines in these 20 files one at a time and do an average of 3rd field ($3) of these 20 files. This will be output to an output file. Instead of using join to generate whole... (8 Replies)
Discussion started by: ahjiefreak
8 Replies

10. Shell Programming and Scripting

Average of elements throught multiple files

Hi, I got a lot of files looking like this: 1 0.5 6 All together there are ard 1'000'000 lines in each of the ard 100 files. I want to build the average for every line, and write the result to a new file. The averaging should start at a specific line, here for example at line... (10 Replies)
Discussion started by: chillmaster
10 Replies
Login or Register to Ask a Question