Text Processing – Compare 1st Column of 1st File and 2nd Column of 2nd File

awkgreprtext processing

File1: Excel file (.xls)

UN          ID    St      M1    M2       SE    DOF  PV        PA            FC
17127159    0   -5.9    297.3   765.7   0.22    4   0.003   0.00389231  2.57536
17127163    2   -3.87   189.914 492.307 0.3548  4   0.0179  0.01795     2.59226
17127167    4   -3.8908 339.136 855.276 0.3429  4   0.0176  0.017       2.52192
17127171    6   -3.922  390.44  986.365 0.340   4   0.0172179   0.01721 2.52627
17127175    8   -4.715  536.072 1210.65 0.2492  4   0.00920158  0.00920 2.258

File2: Text file (.txt)

UNIT_ID   UN      TID        X       E       GG7     J     O
0      17127159 16657436 353.568 335.295 221.717 815.654 684.85
1      17127161 16657436 11.0842 7.01459 7.33511 11.2121 12.6268
2      17127163 16657450 221.647 226.774 136.274 431.32  392.533
3      17127165 16657452 5.02182 3.41172 4.12834 6.90306 4.91183

If 1st column of 1st file matches with 2nd column of 2nd file extract the matched rows of 2nd file from column 3 to column 9 and save them in the first file.

Can anyone help me ?

Output should be saved in a new file

output:

UN        ID   St  M1    M2    SE   DOF PV    PA    FC    TID     X  E  GG7  J O
17127159  0   -5.9  297.3   765.7   0.22    4   0.003   0.00389231  2.57536  16657436 353.568 335.295 221.717 815.654 684.85

Best Answer

An awk solution:

$ awk 'NR==FNR{a[$2]=$3"\t"$4"\t"$5"\t"$6"\t"$7"\t"$8"\t"$9; next} 
              {
                if($1 in a){
                    print $0,a[$1]
                }
               }' file2 file1
UN          ID    St      M1    M2       SE    DOF  PV        PA            FC TID  X   E   GG7 J   O   
17127159    0   -5.9    297.3   765.7   0.22    4   0.003   0.00389231  2.57536 16657436    353.568 335.295 221.717 815.654 684.85  
17127163    2   -3.87   189.914 492.307 0.3548  4   0.0179  0.01795     2.59226 16657450    221.647 226.774 136.274 431.32  392.533

Explanation

Awk splits each input line into fields (at whitespace, by default), making the 1st field $1 the 2nd $2 etc. The special variable NR is the current input line number and FNR is the current line number of the file being read. Therefore, when processing multiple files, the two are equal only while the first file is being read.

  • NR==FNR{a[$2]=$3"\t"$4"\t"$5"\t"$6"\t"$7"\t"$8"\t"$9; next} : if we're reading the first file, save fields 3 through 9 (joined by tabs) as the value in the array a whose key is the 2nd field. Then, skip to the next line.

  • The next ensures that the rest of the script will not be run for the first file (file2) but only the second (file1).

  • if($1 in a){ print $0,a[$1] } : we're now in the second file (file1). If the first field exists as a key in the a array (if($1 in a)), then print the current line $0 and the value stored in a for $1: fields 3 through 9 from file2.

Related Question