Average value in CSV files bash script

Question

I'm currently writing a bash script to find out a server's average memory usage per hour, that outputs to a .csv file. What will happen is, the script will run at every 10th min and after running six times in an hour, I'll have 6 different values for the hour in my .csv file and so.

What I'm trying to do is to use the script find out the average value for each hour.

#date(YYYYMMDDHHmm) total     used
201811270000        10        3
201811270010        10        4
201811270020        10        5
201811270030        10        9
201811270040        10        8
201811270050        10        2
201811270100        10        5
201811270110        10        1
201811270120        10        7
201811270130        10        6
201811270140        10        5
201811270150        10        2
201811270200        10        1

Based on the output above, does anyone know a way I can find the average of each hour? For example:

The average of hour 201811270000: 5.166666666666667
The average of hour 201811270100: 4.333333333333333

How do I go about this?

Is it possible to do so?

Answer 1

Awkward,

awk '
  function calc() {
    if (count) print "The average of hour " date ": " (sum/count);
    count=0; sum=0; date=$1;
  }
  /^#/ {next}             # throw away comment lines
  $1~/00$/ {calc()}       # full hour, time to calculate/reset variables
  END {calc()}            # end of file, ditto
  {count+=1; sum+=$3;}    # update variables at each line
' < file.txt

Pure bash would be herculean, as you'd need to implement floating point arithmetic library first. :)

Answer 2

I'd use "tr" to trim the line into smaller, space separated chunks, "cut" out the parts we need to calculate the average. In case the format gets more complex you can always enhance the getFieldAtPosition function.

I don't have a fullfledged bash here atm so I used an array to iterate over instead of reading from file input. For a way to read a file line by line you can check out this answer:

• https://stackoverflow.com/a/10929511/1177024

Short bash-only version:

    function average {
       local sum=$1
       local count=$2
       local floatingPointUnits=2

       # https://linux.die.net/man/1/dc
       echo "${floatingPointUnits}k" "$sum" "$count" /p | dc
    }

   function getFieldAtPosition {
        local line=$1
        local position=$2

        echo "$line"  | tr -s ' ' | cut -d ' ' -f $position
    }

    function parseHourFromDate {
        local date=$1
        local positionOfHour=4+2+2
        local lengthOfHour=2

        echo ${date:positionOfHour:lengthOfHour}
    }

    lines=('201811270000        10        3      ' \
        '201810270020        7        2      ' \
        '201811270100        10        3      ' \
        '201810270140        22        2      ' \
        '201811271000        33        3      ' )

    sum=0
    count=0
    declare -A HOURS
    for line in "${lines[@]}"; do
        date=`getFieldAtPosition "$line" 1`
        number=`getFieldAtPosition "$line" 2`
        hour=`parseHourFromDate "$date"`

        # new hour, reset
        if [ "$hour" != "$previousHour" ]; then
           sum=0
           count=0
        fi

        sum=$((sum+number))
        count=$((count+1))

        # save average in associative array
        HOURS[$hour]=`average $sum $count`
        previousHour=$hour
    done


    # print results
    for key in "${!HOURS[@]}"; do
        echo "Average of $key: ${HOURS[$key]}"
    done

Answer 3

Using Perl

> cat ivan.txt
201811270000        10        3
201811270010        10        4
201811270020        10        5
201811270030        10        9
201811270040        10        8
201811270050        10        2
201811270100        10        5
201811270110        10        1
201811270120        10        7
201811270130        10        6
201811270140        10        5
201811270150        10        2
201811270200        10        1
> perl -F'/\s+/'  -lane ' { $F[0]=~s/..$//g;push @{$datekv{$F[0]}},$F[2];} END { for my $x (sort keys %datekv){ $total=0;$z=0; foreach(@{$datekv{$x}}) {$total+=$_;$z++ } print $x,"\t",$total/$z }}' ivan.txt
2018112700      5.16666666666667
2018112701      4.33333333333333
2018112702      1
>

Answer 4

Using bash and bc to calculate:

PROCESS_FILE="file.txt"
PROCESSED_DATE=""

while read -r line; do
        if [[ $line =~ ^# ]]; then
                 continue;
        fi

        LINE_DATE=${line:0:10}
        if [[ $PROCESSED_DATE != *"$LINE_DATE"* ]]; then
                PROCESSED_DATE+=","+$LINE_DATE
                USED_LIST=$(grep $LINE_DATE $PROCESS_FILE | sed 's/  */,/g' | cut -d ',' -f3 | tr '\n' ' ')
                COUNT=0;
                SUM=0;
                for USED in $USED_LIST; do
                        COUNT=$(echo "$COUNT + 1" | bc -l);
                        SUM=$(echo "$SUM + $USED" | bc -l);
                done

                if [ $COUNT -ne 0 ]; then
                        AVG=$(echo "$SUM/$COUNT" | bc -l)
                fi
                echo "The average of hour $LINE_DATE: $AVG"
        fi

done < $PROCESS_FILE

Answer 5

Here is a short (a bit brute) way to do it in bash:

calc() {
awk "BEGIN { print "$*" }";
}

IFS=$'\r\n' GLOBIGNORE='*' command eval  'memory=($(<'$1'))'
for (( i = 0; i < ${#memory[@]}; i++ )); do
echo "${memory[i]}" | awk '{print $1" "$3}' >> values.txt
total=$(awk '{ (Values += $2) } END { printf "%0.0f", Values }' values.txt)
length=$(awk '{print $2}' values.txt | wc -l)
echo "The average of hour $(awk '{print $1}' values.txt | tail -n1): $(calc ${total}/${length})"
done
rm values.txt

The result of the execution is the following:

ivo@spain-nuc-03:~/Downloads/TestStackoverflow$ ./processing.sh test.csv 
The average of hour 201811270000: 3
The average of hour 201811270010: 3.5
The average of hour 201811270020: 4
The average of hour 201811270030: 5.25
The average of hour 201811270040: 5.8
The average of hour 201811270050: 5.16667
The average of hour 201811270100: 5.14286
The average of hour 201811270110: 4.625
The average of hour 201811270120: 4.88889
The average of hour 201811270130: 5
The average of hour 201811270140: 5
The average of hour 201811270150: 4.75
The average of hour 201811270200: 4.46154
ivo@spain-nuc-03:~/Downloads/TestStackoverflow$

You can later change the output to forward it to a file. There are more elegant ways of doing this for experienced bash users.

For Paul Hodges:

Awk is to point to the specific column in question as we don't know if that column has the same length as the rest of the file (Still Applies).

tr -d is necesarry as the value of the variable needs to be an integer and not a string (Only at command line):

This is a string:

ivo@spain-nuc-03:~/Downloads/ScriptsClientes/BashReports/Tools/TextProcessing$ cat values.txt | wc -l
13
ivo@spain-nuc-03:~/Downloads/ScriptsClientes/BashReports/Tools/TextProcessing$

This is an integer:

ivo@spain-nuc-03:~/Downloads/ScriptsClientes/BashReports/Tools/TextProcessing$ cat values.txt | wc -l | tr -d '\n'
13ivo@spain-nuc-03:

Addtionally just doing wc -l file returns the following(Still applies):

ivo@spain-nuc-03:~/Downloads/ScriptsClientes/BashReports/Tools/TextProcessing$ wc -l values.txt 
13 values.txt
ivo@spain-nuc-03:~/Downloads/ScriptsClientes/BashReports/Tools/TextProcessing$

Not at all suitable for the task at hand as it forces you to filter out the name of the file.

Please be sure before criticizing.