averaging specific column of multiple files


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting averaging specific column of multiple files
# 8  
Old 12-28-2011
Hi Robin, sorry for the confusion. its for the earlier suggestions for my other query. Back to the averaging stuff. what i have are files to which I have to do an average of column 3 depending on the month part of the filename as I have earlier pointed out. Basically I just need the mean of column 3 of files with the same month index.
Code:
sample filenames: 20000105.u, 20000112.u and so on
for files with the same numeric values in blue; do
average of col.3 
done

say all files have 3 columns by 5 rows: 
the mean i wanted would be: (col3row1 in file1 + col3row1 in file2)/2 to (col3row5 in file1 + col3row5 in file2)/2

Sorry very poor in explaining. thanks.
# 9  
Old 12-28-2011
So, do you provide the month for which averaging need to be done?
Please see my previous post.

--ahamed
# 10  
Old 12-28-2011
hi ahamed, the averaging will be based on the month index in the filename.
# 11  
Old 12-28-2011
Something like this ?

Code:
ls *.u | cut -c1-6 | sort -u >list
while read a
do
nawk '{A[NR]=$1 FS $2;B[FNR]+=$3;++N[FNR]
}END{
i=1;do{ print A[i];}while(++i in A)
j=1;do{ print B[j]/N[j];}while(++j in B)
}' ${a}* >"$a.out"
done <list

# 12  
Old 12-28-2011
what I'm thinking is to loop on the filenames and if the filenames have the same 5th and 6th characters say, 01 it will take the average of the 3rd columns of these corresponding files. i.e.
Code:
(20000105.u,20000102.u,20000119.u,20000126.u) 

Each of these files contain say 3 cols and 3 rows for instance:
file with filename: 20000105.u contains
lon lat value 
123.0 12.0 0.5
123.1 12.0 1.4
123.2 12.0 1.5

file with filename: 20000112.u contains
lon lat value 
123.0 12.0 0.6
123.1 12.0 1.8
123.2 12.0 1.9

file with filename: 20000119.u contains
lon lat value 
123.0 12.0 0.8
123.1 12.0 1.8
123.2 12.0 2.0

file with filename: 20000126.u contains
lon lat value 
123.0 12.0 1.0
123.1 12.0 2.4
123.2 12.0 185

Expected output:
lon lat value 
 123.0 12.0 average of values from 4files for row1
 123.1 12.0 average of values from 4files for row2
 123.2 12.0 average of values from 4files for row3

# 13  
Old 12-28-2011
Code:
awk '
NR==1{x=$0;next}
FNR>1{A[FNR]=$1 FS $2;B[FNR]+=$3;N[FNR]++;c=N[FNR]
}END{
print x;i=2;do {print A[i] "average of value from " c " files for row " i "=" B[i]/N[i] }while(++i in B)}' 200001*.u

so maybe something like :

Code:
ls *.u | cut -c1-6 | sort -u >list
while read a
do
awk 'NR==1{x=$0;next}
FNR>1{A[FNR]=$1 FS $2;B[FNR]+=$3;N[FNR]++;c=N[FNR]
}END{print x; i=2;do {print A[i] "average of value from " c " files for row " i "=" B[i]/N[i] }while(++i in B)}
' ${a}* >"$a.out"
done <list


Last edited by ctsgnb; 12-28-2011 at 12:45 PM..
This User Gave Thanks to ctsgnb For This Post:
# 14  
Old 12-28-2011
@ctsgnb: hi again, the second version of the code works only for files with 200001 files. how to i extend it for 02-12, corresponding to months of february to december.thanks much
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 do I extract specific column in multiple csv files?

file1: Name,Threshold,Curr Samples,Curr Error%,Curr ART GETHome,100,21601,0.00%,47 GETregistry,100,21592,0.00%,13 GEThomeLayout,100,30466,0.00%,17 file2: Name,Threshold,Curr Samples,Curr Error%,Curr ART GETHome,100,21601,0.00%,33 GETregistry,100,21592,0.00%,22... (6 Replies)
Discussion started by: Raghuram717
6 Replies

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

3. Shell Programming and Scripting

Sum values of specific column in multiple files, considering ranges defined in another file

I have a file (let say file B) like this: File B: A1 3 5 A1 7 9 A2 2 5 A3 1 3 The first column defines a filename and the other two define a range in that specific file. In the same directory, I have also three more files (File A1, A2 and A3). Here is 10 sample lines... (3 Replies)
Discussion started by: Bastami
3 Replies

4. Shell Programming and Scripting

How to print multiple specific column after a specific word?

Hello.... Pls help me (and sorry my english) :) So I have a file (test.txt) with 1 long line.... for example: isgc jsfh udgf osff 8462 error iwzr 653 idchisfb isfbisfb sihfjfeb isfhsi gcz eifh How to print after the "error" word the 2nd 4th 5th and 7th word?? output well be: 653 isfbisfb... (2 Replies)
Discussion started by: marvinandco
2 Replies

5. Shell Programming and Scripting

Converting Single Column into Multiple rows, but with strings to specific tab column

Dear fellows, I need your help. I'm trying to write a script to convert a single column into multiple rows. But it need to recognize the beginning of the string and set it to its specific Column number. Each Line (loop) begins with digit (RANGE). At this moment it's kind of working, but it... (6 Replies)
Discussion started by: AK47
6 Replies

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

7. Shell Programming and Scripting

Averaging 3 files with multiple rows

Hi, I am trying to average the values from 3 files with the same format. They are very large files so I will describe the file and show some it of. Basically the file has 83 columns (with nearly 7000 rows). The first three columns are the same for each file while the remaining 80 are values that... (1 Reply)
Discussion started by: kylle345
1 Replies

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

9. Shell Programming and Scripting

Averaging multiple columns

Hello, I am trying to average multiple columns simultaneously while skipping the first column. I am using this awk line to average one column awk '{sum+=$3} END { print "Average = ",sum/NR}' But I want to be able to do it for multiple columns while skipping the first column. There... (4 Replies)
Discussion started by: gisele_l
4 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