Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Average across multiple columns - awk
Top Forums Shell Programming and Scripting Average across multiple columns - awk Post 302986139 by theflamingmoe on Sunday 20th of November 2016 07:33:30 PM
Old 11-20-2016
Average across multiple columns - awk
Hi forum members,

I'm trying to get an average of multiple columns in a csv file using awk. A small example of my input data is as follows:
Code:
cu,u3o8,au,ag
-9,20,-9,3.6
0.005,30,-9,-9
0.005,50,10,3.44
0.021,-9,8,3.35

The following code seems to do most of what I want
Code:
gawk -F"," 'NR==1;NR>1 {for (i=1;i<=4;i++){if($i>0)a[i]+=$i}}END{ for (i=1;i<=4;i++){printf a[i]/(NR-1)","};printf "\n"}' OFS=, input.csv > average.csv

However, the issue is i'm using the $i>o condition to filter out the -9 values and then dividing by NR to calculate the average, which gives the wrong answer. For the Cu column, ignoring -9 values, I should get an average of 0.0103 but instead I get 0.0077.
I tried using a counter as follows:
Code:
gawk -F"," 'NR==1;NR>1 {for (i=1;i<=4;i++){if($i>0)a[i]+=$i}; ++count[$i]}END{ for (i=1;i<=4;i++){printf a[i]/count[$i]","};printf "\n"}' OFS=, input.csv > average.csv

I can't seem to get it to work. Any help would be greatly appreciated.
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

AWK subtraction in multiple columns

AWK subtraction in multiple columns Hi there, Can not get the following: input: 34523 934 9485 3847 394 3847 3456 9384 awk 'NR==1 {for (i = 1; i <= NF; i++) {n=$i; next}; {n-=$i} END {print n}' input output: 21188 first column only,... (2 Replies)
Discussion started by: awkward
2 Replies

2. Shell Programming and Scripting

Generating multiple new columns with awk

Hi, I'm trying to reformat a file to create a new columns reflecting the previous 2 over and over. By that I mean currently each observation has two columns and I want to create a third which has a value equal to 1 minus the sum of the previous two. This is slightly complicated as 1) I... (6 Replies)
Discussion started by: reformatplink
6 Replies

3. Shell Programming and Scripting

Awk if-else syntax with multiple columns

I can't seem to get this to work. I can reformat the date field if it's the first field (and only field) in the file: However, I get a syntax error when the date field is the second field (or has any other columns following): I can use a ";" but then it puts each column on separate... (8 Replies)
Discussion started by: giannicello
8 Replies

4. Shell Programming and Scripting

Awk match multiple columns in multiple lines in single file

Hi, Input 7488 7389 chr1.fa chr1.fa 3546 9887 chr5.fa chr9.fa 7387 7898 chrX.fa chr3.fa 7488 7389 chr21.fa chr3.fa 7488 7389 chr1.fa chr1.fa 3546 9887 chr9.fa chr5.fa 7898 7387 chrX.fa chr3.fa Desired Output 7488 7389 chr1.fa chr1.fa 2 3546 9887 chr5.fa chr9.fa 2... (2 Replies)
Discussion started by: jacobs.smith
2 Replies

5. Shell Programming and Scripting

Extracting multiple columns with awk

Hi everyone!! I need to apply a simple command to extract columns from a matrix, but I need to extract contemporary from the first to the tenth columns, than from the eleventh to the twentyth and so on... how can i do that? (1 Reply)
Discussion started by: gabrysfe
1 Replies

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

7. Shell Programming and Scripting

Average across multiple columns group by

Hi experts, I want to group by average, for multiple columns starting column $7 until NF, group by ($1-$5), please help For just 7th column, I can do awk ' NR>1{ arr += $7 count += 1 } END{ for (a in arr) { print a, arr/count ... (10 Replies)
Discussion started by: ritakadm
10 Replies

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

9. Shell Programming and Scripting

Awk: is it possible to print into multiple columns?

Hi guys, I have hundreds file like this, here I only show two of them: file 1 feco4_s_BB95.log ZE_1=-1717.5206260 feco4_t_BB95.log ZE_1=-1717.5169250 feco5_s_BB95.log ZE_1=-1830.9322060... (11 Replies)
Discussion started by: liuzhencc
11 Replies

10. Shell Programming and Scripting

Comparing multiple columns using awk

Hello All; I have two files with below conditions: 1. Entries in file A is missing in file B (primary is field 1) 2. Entries in file B is missing in file A (primary is field 1) 3. Field 1 is present in both files but Field 2 is different. Example Content: File A ... (4 Replies)
Discussion started by: mystition
4 Replies

LEARN ABOUT DEBIAN

shtool-table

SHTOOL-TABLE.TMP(1)					      GNU Portable Shell Tool					       SHTOOL-TABLE.TMP(1)

NAME

       shtool-table - GNU shtool pretty-print a field-separated list

SYNOPSIS

       shtool table [-F|--field-sep sep] [-w|--width width] [-c|--columns cols] [-s|--strip strip] strsepstr...

DESCRIPTION

       This pretty-prints a list of strings as a table.

OPTIONS

       The following command line options are available.

       -F, --field-sep sep
	   Separate columns using sep. Default is ":".

       -w, --width width
	   Width of each column. Default is 15 characters.

       -c, --columns cols
	   Number of columns. Default is 3.

       -s, --strip strip
	   Strip off any characters past strip. Default is 79.

EXAMPLE

	#   shell script
	shtool table -F , -w 5 -c 4 "1,2,3,4,5,6,7,8,9,10,11,12"

HISTORY

       The GNU shtool table command was originally written by Ralf S.  Engelschall <rse@engelschall.com> in 1999 for GNU shtool.

SEE ALSO

       shtool(1), tr(1), fmt(1), sh(1), awk(1), sed(1).

18-Jul-2008							   shtool 2.0.8 					       SHTOOL-TABLE.TMP(1)
All times are GMT -4. The time now is 08:36 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy