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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to calculate average of two columns and copy into another file?
# 1  
Old 11-05-2013
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:
Code:
100 99
20 99
50 99
50 99

File-2:
Code:
200 85
50 85
100 85

Output file should contain:
Code:
namefile-1 55 99
namefile-2 116 85
namefile-3 avcol1 avcol2
...

I know how to do the average of column 1 for each file but I don't know how to do the rest Smilie

This is what I use:
Code:
 (ls -r | while read filename; do awk '{sum+=$1} END {print sum/NR >> "newfile.tsv"}' $filename ; done)

Any ideas?

Thanks

Moderator's Comments:
Mod Comment Next time, Please use code tags for code and data

Last edited by vbe; 11-05-2013 at 10:30 AM..
# 2  
Old 11-05-2013
Code:
[user@host ~]$ ls -l file*
-rw-r--r-- 1 user group 25 Nov  5 19:59 file1
-rw-r--r-- 1 user group 20 Nov  5 19:59 file2
[user@host ~]$ cat test.sh
#! /bin/bash

for f in file*
do
    s1=0
    s2=0
    c=0
    while read c1 c2
    do
        s1=$(( s1 + c1 ))
        s2=$(( s2 + c2 ))
        (( c++ ))
    done < $f
    echo "Name: $f --> $(( s1 / c )) $(( s2 / c ))"
done
[user@host ~]$ ./test.sh
Name: file1 --> 55 99
Name: file2 --> 116 85
[user@host ~]$

# 3  
Old 11-05-2013
Please use code tags as required by forum rules!

Try
Code:
awk    'FNR==1 && fn        {print fn, sum1/cnt, sum2/cnt; cnt=sum1=sum2=0}
     END             {print fn, sum1/cnt, sum2/cnt}
                {sum1+=$1;sum2+=$2;cnt++;fn=FILENAME}
    ' file1 file2
file1 55 99
file2 116.667 85

# 4  
Old 11-05-2013
Code:
ls | while read file
do
  printf "%s" "$file"
  awk '{for (i=1;i<=NF;i++) {a[i]+=$i; c++}} END {for (i=1;i in a;i++) printf " %.f",a[i]/c}' "$file"
  echo ""
done > ../newfile

NB put newfile to another directory to ensure that it is not considered by the ls.
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

Match first two columns and calculate percent of average in third column

I have the need to match the first two columns and when they match, calculate the percent of average for the third columns. The following awk script does not give me the expected results. awk 'NR==FNR {T=$3; next} $1,$2 in T {P=T/$3*100; printf "%s %s %.0f\n", $1, $2, (P>=0)?P:-P}' diff.file... (1 Reply)
Discussion started by: ncwxpanther
1 Replies

3. Shell Programming and Scripting

awk based script to find the average of all the columns in a data file

Hi All, I need the modification for the below mentioned code (found in one more post https://www.unix.com/shell-programming-scripting/27161-script-generate-average-values.html) to find the average values for all the columns(but for a specific rows) and print the averages side by side. I have... (4 Replies)
Discussion started by: ks_reddy
4 Replies

4. UNIX Desktop Questions & Answers

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... (3 Replies)
Discussion started by: Unilearn
3 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 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

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

9. UNIX for Dummies Questions & Answers

Use awk to calculate average of column 3

Suppose I have 500 files in a directory and I need to Use awk to calculate average of column 3 for each of the file, how would I do that? (6 Replies)
Discussion started by: grossgermany
6 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