Awk adding corresponding rows in different columns

Question

I have a file called presidents.txt. The content inside is as follows...

First  Last  Age  Years
Geogre W     57   8.0
John   A     61   4.0
Thomas J     57   8.0
James  M     57   8.0
Andrew J     61   8.0

What I need is a program that will add the age and years for each president. For example, George W would be 57+8.0, John A would be 61+4.0, and so on. After adding the numbers for each president, the program would then output the name of the president with the highest number. For example, output here should be "Andrew J at 69". So far I can do this for each president, one at a time, but I would like this to be done in a loop using awk. The command I've been using so far has been

awk 'NR==2 {print $3+$4}' presidents.txt

This will add the numbers 61 and 4.0, resulting in an output of 65, which I suppose is a start. I'm sure I need to use a loop and arrays but have no idea where to start as I do not have much experience with Unix or coding in general. Any help is appreicated.

Answer 1

Assuming when there are duplicate results of $3+$4 you only want the last such occurrence from the input printed:

$ cat tst.awk
NR>1 {
    curYear = $3 + $4
    if ( curYear >= maxYear ) {
        maxName = $1 OFS $2
        maxYear = curYear
    }
}
END {
    print maxName, "at", maxYear
}

$ awk -f tst.awk file
Andrew J at 69

For example, given this modified input file where 2 people (Andrew J and Sheila E) now both have a total $3+$4 value of 69:

$ cat file
First  Last  Age  Years
Geogre W     57   8.0
John   A     61   4.0
Thomas J     57   8.0
James  M     57   8.0
Andrew J     61   8.0
Sheila E     65   4.0

With the above script we get the last one output:

$ awk -f tst.awk file
Sheila E at 69

whereas maybe you wanted the first:

$ cat tst.awk
NR>1 {
    curYear = $3 + $4
    if ( curYear > maxYear ) {
        maxName = $1 OFS $2
        maxYear = curYear
    }
}
END {
    print maxName, "at", maxYear
}

$ awk -f tst.awk file
Andrew J at 69

or all of them:

$ cat tst.awk
NR>1 {
    curYear = $3 + $4
    if ( curYear >= maxYear ) {
        if ( curYear > maxYear ) {
            numNames = 0
        }
        maxNames[++numNames] = $1 OFS $2
        maxYear = curYear
    }
}
END {
    for ( nameNr=1; nameNr<=numNames; nameNr++ ) {
        print maxNames[nameNr], "at", maxYear
    }
}

$ awk -f tst.awk file
Andrew J at 69
Sheila E at 69

Answer 2

Combining awk and sort:

awk '{print $1,$2,"at",$3+$4}' file | sort -k4nr | head -1
Andrew J at 69

We create a middle file with the sum of age and years as the last field, and then sort it using as key the last, 4th field, numeric and reverse, finally print just the first line with head.

---

Here is an improvement to cover the case of more than one lines having the maximum years. We print them all:

awk '{print $1,$2,"at",$3+$4}' file | sort -k4nr | uniq -f3 --group | sed '/^$/q'

uniq is grouping the repeated lines (ignoring the first 3 fields, that means testing only the last number), separating the groups with a blank line, and sed is printing only the first group, quiting at first blank line.

Answer 3

You don't even need for any loop or array to do this job. Try:

awk '
            { name= $1 OFS $2 OFS "at" OFS }
NR>1        { age=$3+$4 }
age>ageMax  { ageMax=age; data=name; next }
age==ageMax { data= data ageMax ORS name }
END{ print data ageMax }' infile

• Record the First &Last name part with { name= $1 OFS $2 OFS "at" OFS } for future processing.

• Skip the header line and add "Age" to the "Years" with NR>1{ age=$3+$4 }.

• If "age" value was grether than "ageMax" then update the value of the "ageMax" with the "age" value and update the "data" from the "name" with age>ageMax { ageMax=age; data=name; next }; the next statement tells awk to skip proccessing the rest of the codes for the current line and awk will read the next line for processing.

• If we found equal maximum ageMax values, then keep all of them by appending to the "data" value with age==ageMax { data= data ageMax ORS name }. If you don't want all, just remove this part of the code and then it will return the very first record with highest ageMax then or replace age>ageMax with age>=ageMax to return the very last record with highest ageMax instead.

• At the END{ print data ageMax }, we print the result.