CODEWITHSUNDEEP
shellperlunixawktext-processing
Pradyumna Sagar
Pradyumna Sagar

Reputation: 403

How to calculate the mean of row from csv file from nth column?

This may look like a duplicate but I could not solve the issue I'm having.

I'm trying to find the average of each column from a CSV/TSV file the data looks like below:

input.tsv

ID  source  random  text val1 val2 val3 val4 val330
1   atttt   eeeee   test 0.9  0.5  0.2  0.54 0.89
2   afdg    adfgrg  tf   0.6  0.23 0.5  0.4  0.29

output.tsv

ID  source  random  text Avg
1   atttt   eeeee   test 0.606
2   afdg    adfgrg  tf   0.404

or at least 

ID  Avg
1   0.606
2   0.404

I tried a suggestion from here 

awk 'NR==1{next}
{printf("%s\t", $1
printf("%.2f\n", ($5 + $6 + $7)/3}' input.tsv

which threw error.

and

awk '{ s = 4; for (i = 5; i <= NF; i++) s += $i; print $1, (NF > 1) ? s / (NF - 1) : 0; }' input.tsv

the below code also threw a syntax error

for i in `cat input.tsv` do; VALUES=`echo $i | tr '\t' '\t'`;COUNT=0;SUM=0;typeset -i j;IFS=' ';for j in $VALUES; do;SUM=`expr $SUM + $j`;COUNT=`expr $COUNT + 1`;done;AVG=`expr $SUM / $COUNT`;echo $AVG;done

help me resolve the issue to calculate the average of the row

Upvotes: 1

Views: 717

Answers (5)

stack0114106
stack0114106

Reputation: 8711

Using Perl one-liner

> perl -lane '{ $s=0;foreach(@F[4..8]){$s+=$_} $F[4]=$s==0?"Avg":$s/5;print "$F[0]\t$F[1]\t$F[2]\t$F[3]\t$F[4]" } ' input.tsv 
ID      source  random  text    Avg
1       atttt   eeeee   test    0.606
2       afdg    adfgrg  tf      0.404
>

Upvotes: 1

oliv
oliv

Reputation: 13259

You could use this awk script:

 awk 'NR>1{
        for(i=5;i<=NF;i++)
          sum+=$i
      }
      {
        print $1,$2,$3,$4,(NF>4&&sum!=""?sum/(NF-4):(NR==1?"Avg":""))
        sum=0
      }' file | column -t

The first block gets the sum of all ids starting from the 5th element.

The second block, prints the header line and the average value.

column -t displays the result in column.

Upvotes: 2

Ed Morton
Ed Morton

Reputation: 203985

$ cat tst.awk
NR == 1 { avg = "Avg" }
NR > 1 {
    sum = cnt = 0
    for (i=5; i<=NF; i++) {
        sum += $i
        cnt++
    }
    avg = (cnt ? sum / cnt : 0)
}
{ print $1, $2, $3, $4, avg }

$ awk -f tst.awk file
ID source random text Avg
1 atttt eeeee test 0.606
2 afdg adfgrg tf 0.404

Upvotes: 1

kvantour
kvantour

Reputation: 26491

This would be working as expected:

awk 'BEGIN{OFS="\t"}
     (NR==1){ print $1,$2,$3,$4,"Avg:"; next }
     { s=0; for(i=5;i<=NF;++i) s+=$i }
     { print $1,$2,$3,$4, (NF>4 ? s/(NF-4) : s) }' input.tsv

or just for the fun of it, if you want to make the for-loop obfuscated:

awk 'BEGIN{OFS="\t"}
     (NR==1){ print $1,$2,$3,$4,"Avg:"; next }
     { for(s=!(i=5);i<=NF;s+=$(i++)) {} }
     { print $1,$2,$3,$4, (NF>4 ? s/(NF-4) : s) }' input.tsv

Upvotes: 1

NeronLeVelu
NeronLeVelu

Reputation: 10039

From you code reference:

awk 'NR==1{next}
   {
   # missing the last ). This print the 1st column
   #printf("%s\t", $1
    printf("%s\t", $1 )

   # missing the last ) and average of 3 colum only
   #printf("%.2f\n", ($5 + $6 + $7)/3
    printf("%.2f\n", ($5 + $6 + $7 + $8 + $9) / 5 )
   }' input.tsv

Your second code is not easy work with , lot of subshell (backtic) and shell loop but most of all, i think it is made for working with integer value and for full line of value (not 5- > 9). Forget it unless you don't want awk in this case.

for fun

awk 'NR==1{
        # Header
        print $0 OFS "Avg"
        Count = NF - 5
        next
        }
        {
        # print each element of the line + sum after col 4
        for( i=Avg=0;i<=NF;i++) {
           if( i >=5 ) Avg+= $i
           printf( "%s ", $i)
           }
        # print average
         printf( "%.2f\n", Avg/Count )
        }
   ' input.tsv

Assuming here that it is always counting on the full stack of value, we can change the Count by (NF - 4) if less value are on the line and empty are not counting

Upvotes: 2

Related Questions