How to filter a file based on two separate field data by using awk?

user_13987314 0
Viewed 70

Input file consists of 3 fields separated by "|" as follows:

TeamId|TeamName|TotalPlayers

TeamId consists of unique numbers. TeamName consists of several Premier league football teams and corresponding no of players in TotalPlayers field.

2 of the records are as follows:(these records belong to one of the visible test cases)

103|Manchester United|12

105|Manchester City|13

Code requirement:

I had to output the TeamName which starts with Manchester and has the most number of players. If no Team starts with Manchester, then it should not be any output. i.e. In the above test case the output should be Manchester City.

My solution:

awk 'BEGIN{FS = "|";OFS = ",";}{if($2 ~ /^Manchester/){print $2, $3}}' | sort -n -k2 | awk -F , '(NR==1){print $1}'

This provided the expected output for normal test cases but hidden test cases were failed. What changes can I make to this or any other easier way to achieve the same...

Also recommend any websites where I can practice these kind of unix coding problems by solving.

awk unix
2 Answers

I had to output the TeamName which starts with Manchester and has the most number of players. If no Team starts with Manchester, then it should not be any output. i.e. In the above test case the output should be Manchester City.

$ cat file 
TeamId|TeamName|TotalPlayers
103|Manchester United|12
105|Manchester City|13

$ awk -F'|' '$2~/^Manchester/ && $3 >max{max=$3; team=$2}END{if(team)print team}' file 
Manchester City
user_31619930

Could you please try following, written and tested with shown samples in GNU awk.

awk '
BEGIN{
  FS="|"
}
FNR>1 && $2~/^Manchester/{
  arr[$NF]=(arr[$NF]?arr[$NF] ORS:"")$2
  max=(max>$NF?max:$NF)
}
END{
  if(max!=""){
    num=split(arr[max],val,ORS)
    if(num>1){
       for(i=1;i<=num;i++){
          print val[i],max
       }
    }
    else{ print arr[max],max }
  }
}
'  Input_file

Explanation: Adding detailed explanation for above.

awk '                                      ##Starting awk program from here.
BEGIN{                                     ##Starting BEGIN section of program from here.
  FS="|"                                   ##Setting FS as | here.
}
FNR>1 && $2~/^Manchester/{                 ##Checking condition if line number is more than 1 then do following.
  arr[$NF]=(arr[$NF]?arr[$NF] ORS:"")$2    ##Creating array arr with index of last field and keep appending its value with new line in case similar max objects found to print them all.
  max=(max>$NF?max:$NF)                    ##Creating max by checking if value of 2nd field if its greater than $2 then keep it else assign its value as $2.
}
END{                                       ##Starting END block of this program from here.
  if(max!=""){                             ##Checking condition if max is NOT NULL then do following.
    num=split(arr[max],val,ORS)            ##Splitting arr[max] value into val array with delimiter of ORS here.
    if(num>1){                             ##if num(total number of elements in arr) is greater than 1 then do following.
       for(i=1;i<=num;i++){                ##Start a loop till value of num here.
          print val[i],max                 ##Printing value of val with index i and max here.
       }
    }
    else{ print arr[max],max }             ##Else printing value of arr[max] and max only 1 time.
  }
}
' Input_file                               ##Mentioning Input_file name here.
user_58665800
Related