Question: Help with awk loop
0
gravatar for Francisco Muñoz
11 months ago by
Francisco Muñoz 10 wrote:

Hi everyone! I'm new to awk and i'm having troubles with a script i thought would be easier. I have a variable number of columns with numbers (e.g. 4.45), and some of the "cells" have missing values (--). I have to calculate the mean of each row, adding a new column with it. I tried something like:

awk 'BEGIN {FS=";";OFS=";";SUM=0;AVG=0;MSS=0};
NR==1 {print $0,"Average","Missing"};
NR>1 {for (i=4;i<=NF;i++) {if ($i!="--") SUM+=$i};print $0,$SUM; SUM=0}' $FILE

In that example i'm not calculating the mean but just the sum as a test, but the results are senseless. I also have to add a "Missing" column where i count the missing values of each row. What am i doing wrong? Thanks in advance!

awk • 407 views
ADD COMMENTlink modified 11 months ago • written 11 months ago by Francisco Muñoz 10
1

Thank you all! The error was in the use of $ as kloetzl stated

ADD REPLYlink written 11 months ago by Francisco Muñoz 10
4
gravatar for kloetzl
11 months ago by
kloetzl1.1k
European Union
kloetzl1.1k wrote:

Don't prefix any variables in awk with $ unless you want the value of the column.

BEGIN{
    FS=";";
    OFS=";";
    print "Average;Missing"
    }
NR>1{
    sum=0;
    c=0;
    for(i=1;i<=NF;i++)
        if($i != "--") {
            sum += $i;
            c++; 
        }
    print sum/c, NF-c;
}
ADD COMMENTlink modified 11 months ago • written 11 months ago by kloetzl1.1k
4
gravatar for Kevin Blighe
11 months ago by
Kevin Blighe65k
Kevin Blighe65k wrote:

Solution already posted, but here is my own:

cat test
a;b;c;d;e;f;g;h
9;8;7;0;--;--;9
5;5;345;3;6;65;8
--;677;888;66;77;--;4
1;2;4;6;8;--;0;6

awk 'BEGIN {FS=";";OFS=";";SUM=0;MSS=0} {
  if (NR==1) {
    print "Sum\tAverage\tMissing"
  } else {
    for (i=1;i<=NF;i++) {
      if ($i!="--") {SUM+=$(i)} else {MSS+=1}
  } print SUM"\t"SUM/(NF-MSS)"\t"MSS; SUM=0; MSS=0}}' test

Sum     Average Missing
33      6.6     2
437     62.4286 0
1712    342.4   2
27      3.85714 1
ADD COMMENTlink written 11 months ago by Kevin Blighe65k
3
gravatar for Lisa Ha
11 months ago by
Lisa Ha90
Germany
Lisa Ha90 wrote:

Try this:

awk 'BEGIN {FS=OFS=";"};NR==1{print $0,"sum","miss"}; NR>1{sum=miss=0;for (i=1;i<=NF;i++) if($i=="NA"){miss+=1} else if ($i>0){sum+=$i} ; print $0,sum,miss;}' file

Input file:

var1;var2;var3
1;2;3
4;NA;6
7;8;NA

Output:

var1;var2;var3;sum;miss
1;2;3;6;0
4;NA;6;10;1
7;8;NA;15;1
ADD COMMENTlink written 11 months ago by Lisa Ha90
Please log in to add an answer.

Help
Access

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.
Powered by Biostar version 2.3.0
Traffic: 1669 users visited in the last hour