Using AWK to check conditions in multiple columns to output average, min, max, and total occurrences from a dataset containing age, race, and sex

user_13861474 0
Viewed 131

I am using PuTTy for school to learn UNIX/Linux and have a file 2.asr which is a large data set containing the age, sex, and race of multiple individuals in their own columns, for example:

19 Male White 
23 Female White
23 Male White
45 Female Other
54 Male Asian
24 Male Other
34 Female Asian
23 Male Hispanic
45 Female Hispanic
38 Female White

I would like to find the average age, max age, min age, and total occurrences of unique demographics such as Male White or Female Hispanic.

I've tried using awk code as follows:

$ awk '$2 == "Male" && $3 == "Hispanic" {sum+=$1; n++} 
    (NR==1) {min=$1;max=$1+0}; 
    (NR>=2) {if(min>$1) min=$1; if(max<$1) max=$1} 
    END {if (n>0) 
      print $2 " " $3 " Average Age: " sum/n ", Max: " max ", Min: " min ", Total: " n
    }' 2.asr

However, regardless of what sex and race I input, the output is always "Male White" and the max and min values are those of the entire dataset rather than the unique demographic conditions I've set. It does seem however that the average age and total occurrences of each demographic are outputted properly and change accordingly. I've tried using $2 and $3 at the start of the command in an if statement and utilizing BEGIN at the start also but I keep getting syntax errors at the end where I have my print function. Is there a better way to approach this with if statements ate the start of the command or is my syntax off somewhere? Thanks to whoever wishes to assist!

multiple-columns conditional-statements awk linux
4 Answers

do it wholesale

$ awk         '{k=$2 FS $3} 
     !(k in c) {max[k]=min[k]=$1} 
               {sum[k]+=$1; c[k]++} 
     max[k]<$1 {max[k]=$1} 
     min[k]>$1 {min[k]=$1} 
     END       {for(k in c) print k,max[k],min[k],sum[k]/c[k]}' file | sort | column -t

Female  Asian     34  34  34
Female  Hispanic  45  45  45
Female  Other     45  45  45
Female  White     38  23  30.5
Male    Asian     54  54  54
Male    Hispanic  23  23  23
Male    Other     24  24  24
Male    White     23  19  21

add the header

user_14358690

If this is for a class, it might not be an option, but GNU datamash is a useful tool intended just for this sort of statistics:

$ datamash -Ws -g2,3 mean 1 min 1 max 1 count 1 < input.txt
GroupBy(field-2)        GroupBy(field-3)        mean(field-1)   min(field-1)    max(field-1)    count(field-1)
Female  Asian   34      34      34      1
Female  Hispanic        45      45      45      1
Female  Other   45      45      45      1
Female  White   30.5    23      38      2
Male    Asian   54      54      54      1
Male    Hispanic        23      23      23      1
Male    Other   24      24      24      1
Male    White   21      19      23      2
user_99521960

This will let you process all of your demographics at once while avoiding the need to store all of your input in memory at once (sort uses demand paging to handle that if necessary) which may matter since you said your input is a large data set :

$ cat tst.sh
#!/usr/bin/env bash

sort -k2 -k1,1n file |
awk '
    BEGIN { OFS="\t" }
    { curr = $2 FS $3 }
    curr != prev {
        prt()
        min = $1
        sum = cnt = 0
        prev = curr
    }
    {
        max = $1
        sum += $1
        cnt++
    }
    END { prt() }

    function prt() {
        if (cnt) {
            print prev, sum/cnt, max, min, cnt
        }
    }
'

.

$ ./tst.sh
Female Asian    34      34      34      1
Female Hispanic 45      45      45      1
Female Other    45      45      45      1
Female White    30.5    38      23      2
Male Asian      54      54      54      1
Male Hispanic   23      23      23      1
Male Other      24      24      24      1
Male White      21      19      23      2

To only find one group, say Female Asian, just change sort -k2 -k1,1n file | to grep 'Female Asian' file |sort -k2 -k1,1n | or tweak the awk script to test for those values or even just pipe the output to grep if you don't care much about efficiency:

$ ./tst.sh | grep 'Female Asian'
Female Asian    34      34      34      1
user_17450010

@rockytimmy, your code contained a few logical bugs. Here is a minimal rewrite and yet keeping to your "original requirements":

awk -v Sex="Female" -v Race="White" '
   BEGIN {max=0; min=999; n=0; sum=0 }
   $2 == Sex && $3 == Race {
     print;
     sum+=$1;
     n++;
     if ($1 < min) {min = $1};
     if ($1 > max) {max = $1}
   }
   END { print Sex " " Race " Average Age: " sum/n ", Max: " max ", Min: " min ", Total: " n
   }' 2.asr

NOTE: All matching entries are also printed out for verification. Running the above awk script using the sample data you provided prints:

23 Female White
38 Female White
Female White Average Age: 30.5, Max: 38, Min: 23, Total: 2
user_137799360
Related