_           _                 
 | |_ ___  __| |____  ___ _   _ 
 | __/ _ \/ _` |_  / / _ \ | | |
 | ||  __/ (_| |/ / |  __/ |_| |
  \__\___|\__,_/___(_)___|\__,_|

Awk script to combine RoboSumo team list with individual Track TT rankings

I saved the following awk script as teams.awk:

FNR==NR {
    a[$2]=$1;
    next;
}

{
    print $1 "," $2 "," a[$2] "," $3 "," a[$3] "," $4 "," a[$4];
}

Each team's members are listed in a row in the file teams.csv, together with a couple of trailing metadata fields, as shown in the following example:

TeamABC,dave,andrew,betty,CONFIRMED,CQ-507
Terminator,john,kathy,bruce,CONFIRMED,CQ-509
ToughBot,joe,mary,ellen,CONFIRMED,CQ-503

The user's individual rankings are stored in ranking.csv:

1,mary
2,betty
3,andrew
4,bruce
5,paul
6,dave
7,kathy
8,ellen
9,joe
10,john

To run the awk script:

awk -F',' -f teams.awk ranking.csv teams.csv

That produces the following output:

TeamABC,dave,6,andrew,3,betty,2
Terminator,john,10,kathy,7,bruce,4
ToughBot,joe,9,mary,1,ellen,8