Average each numeric column Post: 303030510

Sponsored Content

Top Forums Shell Programming and Scripting Average each numeric column Post 303030510 by RudiC on Tuesday 12th of February 2019 07:26:14 AM

02-12-2019

Registered User

Quote:

Originally Posted by Geneanalyst

Never mind, the following AWK code works:

Code:

 awk -F '\t' '{ for(i=1;i<=NF;i++) total[i]+=$i ; } END { for(i=1;i<=NF;i++) printf "%f ",total[i]/NR ;}'

Header can easily be added with:

Code:

printf '%s\n' '0r header' x | ex file

Moderator's Comments:

Added code tags. Issued reminder to user.

And, you should mind as well, as the results you get from your line are plainly wrong:

Code:

7.600000    0.000000    0.000000    0.132000    0.126000    0.072000    0.492000    0.138000    0.132000    0.154000    0.018000    0.012000    0.000000    0.000000    0.082000    0.082000    0.046000   0.244000    0.084000    0.086000    0.088000    0.018000    0.008000

Try

Code:

awk 'NR==1 {print; next} {for (i=1; i<=NF; i++) sum[i]+=$i} END {for (i=1; i<=NF; i++) printf "%.2f\t",sum[i]/(NR-1); print ""}' file
CHR    RS_ID    ALLELE    POP1    POP2    POP3    POP4    POP5    POP6    POP7    POP8    POP9    SUBJECT_A1    SUBJECT_A2    POP10    POP11    POP12    POP13    POP14    POP15    POP16    POP17    POP18
9.50    0.00    0.00    0.17    0.16    0.09    0.61    0.17    0.17    0.19    0.02    0.02    0.00    0.00    0.10    0.10    0.06    0.30    0.10    0.11    0.11    0.02    0.01

instead.

This User Gave Thanks to RudiC For This Post:

RudiC

View Public Profile for RudiC

Find all posts by RudiC

10 More Discussions You Might Find Interesting

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

2. UNIX for Dummies Questions & Answers

average of a column in a table

Hello, Is there a quick way to compute the average of a column data in a numerical tab delimeted file? Thanks, Gussi

3. Shell Programming and Scripting

How to check if a column is having a numeric value or not in a file?

Hi, I want to know, how we find out if a column is having a numeric value or not. For Example if we have a csv file as ASDF,QWER,GHJK,123,FGHY,9876 GHTY,NVHR,WOPI,623,HFBS,5386 we need to find out if the 4th and 6th column has muneric value or not. Thanks in advance Keerthan

4. Shell Programming and Scripting

average of rows with same value in the first column

Dear All, I have this file tab delimited A 1 12 22 B 3 34 33 C 55 9 32 A 12 81 71 D 11 1 66 E 455 4 2 B 89 4 3 I would like to make the average every column where the first column is the same, for example, A 6,5 46,5 46,5 B 46,0 19,0 18,0 C 55,0 9,0 32,0 D 11,0 1,0 66,0...

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

6. Shell Programming and Scripting

Remove certain column with numeric value

I have file1.txt LBP298W2,300,-18,-115,-12,-105 LBP298W2,300,-18,-115,LBP298W3,300 LBP298W3,300,-18,-115,-12,-105---------- Post updated at 03:35 AM ---------- Previous update was at 03:34 AM ---------- i want to remove every line with non numeric value in column 5 expected result ...

7. Shell Programming and Scripting

Get column average using ID

I have a file that looks like this: id window BV 1 1 0.5 1 2 0.2 1 3 0.1 2 1 0.5 2 2 0.1 2 3 0.2 3 1 0.4 3 2 0.6 3 3 0.8 Using awk, how would I get the average BV for window 1? Output like this: window avgBV 1 0.47 2 0.23

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

9. UNIX for Advanced & Expert Users

Sort by second column numeric values

From googling and reading man pages I figured out this sorts the first column by numeric values. sort -g -k 1,1 Why does the -n option not work? The man pages were a bit confusing. And what if I want to sort the second column numerically? I haven't been able to figure that out. The file...

10. UNIX for Beginners Questions & Answers

Replace a numeric values in a certain column

Hi All, I am trying to replace a certain value from one place in a file . In the below file at position 35 I will have 8 I need to modify all 8 in that position to 7 I tried awk '{gsub("8","7",$35)}1' infile > outfile ----> not working sed -i 's/8/7'g' infile --- it is replacing all...

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

calculate average of column 2

Discussion started by: onthetopo

2. UNIX for Dummies Questions & Answers

average of a column in a table

Discussion started by: Gussifinknottle

3. Shell Programming and Scripting

How to check if a column is having a numeric value or not in a file?

Discussion started by: keerthan

4. Shell Programming and Scripting

average of rows with same value in the first column

Discussion started by: paolo.kunder

5. Shell Programming and Scripting

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

Discussion started by: jackken007

6. Shell Programming and Scripting

Remove certain column with numeric value

Discussion started by: radius

7. Shell Programming and Scripting

Get column average using ID

Discussion started by: jwbucha

8. Shell Programming and Scripting

Check first column - average second column based on a condition

Discussion started by: jacobs.smith

9. UNIX for Advanced & Expert Users

Sort by second column numeric values

Discussion started by: cokedude

10. UNIX for Beginners Questions & Answers

Replace a numeric values in a certain column

Discussion started by: arunkumar_mca