Bash: Finding average of entries from multiple columns after reading a CSV text file

user_10165436 0
Viewed 184

I am trying read a CSV text file and find average of weekly hours (columns 3 through 7) spent by all user-ids (column 2) ending with an even number (2,4,6,...).

The input sample is as below:

Computer ID,User ID,M,T,W,T,F
Computer1,User3,5,7,3,5,2
Computer2,User5,8,8,8,8,8
Computer3,User4,0,8,0,8,4
Computer4,User1,5,4,5,5,8
Computer5,User2,9,8,10,0,0
Computer6,User7,4,7,8,2,5
Computer7,User6,8,8,8,0,0
Computer8,User9,5,2,0,6,8
Computer9,User8,2,5,7,3,6
Computer10,User10,8,9,9,9,10

I have written the following script:

awk -F, '$2~/[24680]$/{for(i=3;i<=7;i++){a+=$i};printf "%s\t%.2g\n",$2,a/5;a=0}' user-list.txt > superuser.txt

The output of this script is:

User4   4
User2   5.4
User6   4.8
User8   4.6
User10  9

However, I want to change the script to only print one average for all user-Ids ending with an even number.

The desired output for this would be as below (which is technically the average of all hours for the IDs ending with even numbers):

5.56

Any help would be appreciated.

TIA

awk
4 Answers

Trying to fix OP's attempt here and adding logic to get average of averages at last of the file's reading. Written on mobile so couldn't test it should work in case I got the thought correct by OP's description.

awk -F, '
$2~/[24680]$/{
  count++
  for(i=3;i<=7;i++){
    sum+=$i
  }
  tot+=sum/5
  sum=0
}
END{
  print "Average of averages is: " (count?tot/count:"NaN")
}
' user-list.txt > superuser.txt
user_58665800

You may try:

awk -F, '$2 ~ /[02468]$/ {
   for(i=3; i<=7; i++) {
      s += $i
      ++n
   }
}
END {
   if (n)
      printf "%.2f\n", s/n
}' cust.csv

5.56
user_5482250 
awk -F, 'NR == 1 { next } { match($2,/[[:digit:]]+/);num=substr($2,RSTART,RLENGTH);if(num%2==0) { av+=($3+$4+$5+$6+$7)/5 } } END { printf "%.2f\n",av/5}' user-list.txt

Ignore the first header like. Pick the number out of the userid with awk's match function. Set the num variable to this number. Check to see if the number is even with num%2. If it is average, set the variable av to av plus the average. At the end, print the average to 2 decimal places.

user_78404400

Print the daily average, for all even numbered user IDs:

#!/bin/sh

awk -F , '
(NR>1) &&
($2 ~ /[02468]$/) {
        hours += ($3 + $4 + $5 + $6 + $7)
        (users++)
}

END {
        print (hours/users/5)
}' \
"$1"

Usage example:

$ script user-list
5.56

One way to get evenness or oddness of an integer is to use modulus (%), as in N % 2. For even values of N, this sum evaluates to zero, and for odd values, it evaluates to 1.

However in this case, a string operation would be required to extract the number any way, so may as well just use a single string match, to get odd or even.

Also, IMO, for 5 fields, which are not going to change (days of the week), it's more succinct to just add them directly, instead of a loop. (NR>1) skips the titles line too, in case there's a conflict.

Finally, you can of of course swap /[02468]$/ for /[13579]$/ to get the same data, for odd numbered users.

user_139196680
Related