Compare one column in one file, against another column and if they match, append column

Question

I would like to compare one column from one file against another column in another file. If the columns match, I would like to append the first column from the first file, as the first column of the second file.

File1

FID IID
456 2
123 1
789 3
112 4

File2

IID column1 column2 column3... column464
1 Value11 Value12 etc etc ... etc
7 Value71 Value72 etc etc ... etc
2 Value21 Value22 etc etc ... etc
6 Value61 Value62 etc etc ... etc
3 Value31 Value32 etc etc ... etc

Desired output

FID IID column1 column2 column3... column464
123 1 Value11 Value12 etc etc ... etc
456 2 Value21 Value22 etc etc ... etc
789 3 Value31 Value32 etc etc ... etc

Answer 1

Using awk:

awk 'NR==FNR { id[$2]=$1; next } ($1 in id){ print id[$1], $0}' file1 file2

within first action block we are reading the file1 into an associated array called id where they keys are the column $2 and their value is column $1; about the NR==FNR which is an always true condition when processing the first input file which are awk's internal environment variables and NR (Number of Records) update for every input as same to FNR (File Number of Record) but FNR reset for the each next input file.

then within next action block we are checking if that first column $1 of the file2 exist in the id array then print its corresponding value id[$1] followed by the entire line $0 of the file2.

Answer 2

For a limited number of fields, try

join -12 -21 -o1.1,0,2.2,2.3,2.4  <(sort -nk2,2 file1) <(sort -n file2) 2>/dev/null
FID IID column1 column2 column3...
123 1 Value11 Value12 etc
456 2 Value21 Value22 etc
789 3 Value31 Value32 etc

but with your alluded number of fields, the format might become somewhat cumbersome.