Average across multiple columns - awk Post: 302986146

Sponsored Content

Top Forums Shell Programming and Scripting Average across multiple columns - awk Post 302986146 by Don Cragun on Sunday 20th of November 2016 11:05:04 PM

11-21-2016

Registered User

You didn't answer the question: "Will all lines for a given group be adjacent (as in your example), or will data for various groups be intermixed?" so I tried to write code that would work either way. Note that the order of the groups in the output from this is random. It uses two dimensional arrays to keep track of the sums and counts for each column and group. (If all lines for a group are adjacent, you could still use one-dimensional arrays and dump the data for each group when a new group is found.)

Given that both entries in the 3rd column for group ru are -9, I don't understand why you believe the output for that column for that group should be 2; the following code produces 0 in this case:

Code:

gawk '
BEGIN {	FS = OFS = ","
}
NR > 1 {for(i = 1; i <= 4; i++)
		if($i > 0) {
			s[i, $5] += $i
			++count[i, $5]
			group[$5]
		}
}
END {	for(g in group) {
		for(i = 1; i <= 4; i++)
			printf("%g%s", count[i, g] ? s[i, g] / count[i, g] : 0,
			    OFS)
		print g
	}
}' input.csv > average.csv

Using awk on macOS 10.12.1, I get the output:

Code:

0.005,25,0,3.6,ru
0.013,50,9,3.395,rd

in average.csv with the sample input.csv data you provided in post #3.

Don Cragun

View Public Profile for Don Cragun

Find all posts by Don Cragun

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

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

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

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?

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

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

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

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

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

LEARN ABOUT X11R4

xkbkeysymentry

XkbKeySymEntry(3)						   XKB FUNCTIONS						 XkbKeySymEntry(3)

NAME

       XkbKeySymEntry -  Returns the keysym corresponding to shift level shift and group grp from the two-dimensional array of keysyms for the key
       corresponding to keycode

SYNOPSIS

       KeySym XkbKeySymEntry macro ( xkb, keycode, shift, grp )
	     XkbDescPtr xkb;
	     KeyCode keycode;
	     int shift;
	     int grp;

ARGUMENTS

       - xkb  Xkb description of interest

       - keycode
	      keycode of interest

       - shift
	      shift level of interest

       - grp  group of interest

DESCRIPTION

       The key width and number of groups associated with a key are used to form a small two-dimensional array of KeySyms for a  key.  This  array
       may be different sizes for different keys. The array for a single key is stored as a linear list, in row-major order. The arrays for all of
       the keys are stored in the syms field of the client map. There is one row for each group associated with a key  and  one  column  for  each
       level. The index corresponding to a given group and shift level is computed as:

	    idx = group_index * key_width + shift_level

       The offset field of the key_sym_map entry for a key is used to access the beginning of the array.

       XkbKeySymEntry  returns	the  keysym corresponding to shift level shift and group grp from the two-dimensional array of keysyms for the key
       corresponding to keycode.

X Version 11							   libX11 1.2.1 						 XkbKeySymEntry(3)

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

AWK subtraction in multiple columns

Discussion started by: awkward

2. Shell Programming and Scripting

Generating multiple new columns with awk

Discussion started by: reformatplink

3. Shell Programming and Scripting

Awk if-else syntax with multiple columns

Discussion started by: giannicello

4. Shell Programming and Scripting

Awk match multiple columns in multiple lines in single file

Discussion started by: jacobs.smith