vlookup from 1 file to another

user_6109198 0
Viewed 57

Tried working on a file to do a vlookup using bash script but i can't get it working. Any idea what am i missing?

1st file

NAS88_01 iSC0   360
NAS88_01 iSC1   30016.8
NAS88_01 iSC101 210
NAS88_01 iSC102 210
NAS88_01 iSC103 510
NAS88_01 iSC104 510
NAS88_01 iSC105 510
NAS88_01 iSC106 2102.4
NAS88_01 iSC107 2102.4

2nd file

iSC0    ilehost1
iSC1    ilehost99
iSC101  ilehost00
iSC102  ilehost21
iSC103  ilehost22
iSC104  ilehost87
iSC105  ilehost11
iSC106  ilehost24
iSC107  ilehost65

Desired Output

NAS88_01 ilehost1  360
NAS88_01 ilehost99 30016.8
NAS88_01 ilehost00 210
NAS88_01 ilehost21 210
NAS88_01 ilehost22 510
NAS88_01 ilehost87 510
NAS88_01 ilehost11 510
NAS88_01 ilehost24 2102.4
NAS88_01 ilehost65 2102.4

Tried this but not working.

FNR==NR {
  a[$0]=1  
  next     
}
$1 in a {  
  print $1 "," $4
}

awk -f vlookup.awk FILE2 FILE1
vlookup sh awk
2 Answers

Based on your shown shown samples only, could you please try following.

awk 'FNR==NR{a[$1]=$2;next} {print $1,($2 in a?a[$2]:""),$3}' Input_file2  Input_file1

Issues in OP's attempts: Which is fixed in above command.

  • Wrong key for array is being assigned while reading Input_file2, it should NOT be $0 it should be $1(first field) as per shown samples.
  • That's why $1 is not working while checking condition when 2nd Input_file named Input_file1 is being read because key in previous read is not matching it.
user_58665800

Assuming the files are sorted on the second and first columns respectively like your samples are, it's a trivial join:

$ join -12 -21 -o 1.1,2.2,1.3 file1.txt file2.txt
NAS88_01 ilehost1 360
NAS88_01 ilehost99 30016.8
NAS88_01 ilehost00 210
NAS88_01 ilehost21 210
NAS88_01 ilehost22 510
NAS88_01 ilehost87 510
NAS88_01 ilehost11 510
NAS88_01 ilehost24 2102.4
NAS88_01 ilehost65 2102.4

For any value that's missing in file 2, example iSC108, how can i integrate the "N/A" on the script?

join -12 -21 -o 1.1,2.2,1.3 -a1 -e 'N/A' file1.txt file2.txt
user_99521960
Related