implementing Excel-vlookup-like function with awk

user_12909353 0
Viewed 713

I have a question about vlookup function implementation with awk. I have a csv file having id-score pairs like this (say 1.csv):

id,score
1,16
3,12
5,13
11,8
13,32
17,37
23,74
29,7
31,70
41,83

There are "unscored" guys. I also have a csv file including all registered guys both scored and unscored like this (say, 2.csv) (I transposed for the want of space)

id,1,3,5,7,11,13,17,19,23,29,31,37,41

I would like to generate id-score pairs according to 2nd csv file so as to include both scored and unscored guys. For unscored guys, NAN would be used instead of the digit.

In other words, final result is desired to be like this:

id,score
1,16
3,12
5,13
7,NAN
11,8
13,32
17,37
19,NAN
23,74
29,7
31,70
37,NAN
41,83

When I tried to create a new table with the following awk command, it did not work to me. Thanks in advance for any advice.

awk 'FNR==NR{a[$1]++; next} {print $0, (a[$1]) ? a[$2] : "NAN"}' 1.csv 2.csv

vlookup awk excel linux bash
4 Answers

With bash and join:

echo "id,score"
join --header -j 1 -t ',' <(sort 1.csv | grep -v '^id') <(tr ',' '\n' < 2.csv | grep -v '^id' | sort) -e "NAN" -a 2 -o 2.1,1.2 | sort -n

Output:

id,score
1,16
3,12
5,13
7,NAN
11,8
13,32
17,37
19,NAN
23,74
29,7
31,70
37,NAN
41,83

See: man join

user_37768580

With awk could you please try following, written with shown samples in GNU awk. Considering(like your shown samples) your both the Input_files have headers in their first line.

awk -v counter=2 '
FNR==1{
  next
}
FNR==NR{
  a[FNR]=$0
  b[FNR]=$1
  next
}
{
  if($0==b[counter]){
    print a[counter]
    counter++
  }
  else{
    print $0",NA"
  }
}
' FS="," 1.csv <(tr ',' '\n' < 2.csv)

Explanation: Adding detailed explanation for above.

awk -v counter=2 '                           ##Starting awk program from here and setting counter as 2.
FNR==1{                                      ##Checking condition if line is 1st then do following.
  next                                       ##next will skip all further statements from here.
}
FNR==NR{                                     ##Checking condition if FNR==NR which will be TRUE when Input_file 1.csv is being read.
  a[FNR]=$0                                  ##Creating array a with index FNR and value of current line.
  b[FNR]=$1                                  ##Creating array b with index FNR and value of 1st field of current line.
  next                                       ##next will skip all further statements from here.
}
{
  if($0==b[counter]){                        ##Checking condiiton if current line is same as array b with index counter value then do following.
    print a[counter]                         ##Printing array a with index of counter here.
    counter++                                ##Increasing count of counter by 1 each time cursor comes here.
  }
  else{                                      ##Else part of for above if condition starts here.
    print $0",NA"                            ##Printing current line and NA here.
  }
}
' FS="," 1.csv <(tr ',' '\n' < 2.csv)        ##Setting FS as , for Input_file 1.csv and sending 2.csv output by changing comma to new line to awk.
user_58665800

here is your script with fixes: set field separators; save the score value for each id; print the value from lookup, if missing NaN

$ awk 'BEGIN   {FS=OFS=","} 
       FNR==NR {a[$1]=$2; next} 
               {print $1, (($1 in a)?a[$1]:"NAN")}' file1 file2

id,score
1,16
3,12
5,13
7,NAN
11,8
13,32
17,37
19,NAN
23,74
29,7
31,70
37,NAN
41,83
user_14358690

An awk solution could be:

awk -v FS=, -v OFS=, '
    NR == 1   { print; next }
    NR == FNR { score[$1] = $2; next }
              { for (i = 2; i <= NF; ++i)
                    print $i, score[$i] == "" ? "NAN" : score[$i] }
' 1.csv 2.csv
user_138090010
Related