How to use the awk command to combine columns from one file to another matching by ID?

Question

[hkj7]

I have a file that looks like this:

FID IID Country Smoker Cancer_Type Age
1 RQ34365-4 1 2 1 70 
2 RQ22067-0 1 3 1 58
3 RQ22101-7 1 1 1 61
4 RQ14754-1 2 3 1 70

And another file with 16 columns.

Id pc1 pc2 pc3 pc4 pc5 pc6 pc7 pc8 pc9 pc10 pc11 pc12 pc13 pc14 pc15
RQ22067-0 -0.0731995 -0.0180998 -0.598532 0.0465712 0.152631 1.3425 -0.716615 -1.15831 -0.477422 0.429214 -0.5249 -0.793306 0.274061 0.608845 0.0224554
RQ34365-4 -1.39583 -0.450994 0.156784 2.28138 -0.259947 2.83107 0.335012 0.632872 1.03957 -0.53202 -0.162737 -0.739506 -0.040795 0.249346 0.279228
RQ34616-4 -0.960775 -0.580039 -0.00959004 2.28675 -0.295607 2.43853 -0.102007 1.01575 -0.083289 1.0861 -1.07338 1.2819 -0.132876 -0.303037 0.9752
RQ34720-1 -1.32007 -0.852952 -0.0532576 2.52405 -0.189117 3.07359 1.31524 0.637381 -1.36214 -0.0246524 0.708741 0.502428 -0.437373 -0.192966 0.331765
RQ56001-9 0.13766 -0.3691 0.420061 -0.490546 0.655668 0.547926 -0.614815 0.62115 0.783559 -0.163262 -0.660511 -1.08647 -0.668259 -0.331539 -0.444824
RQ30197-8 -1.50017 -0.225558 -0.140212 2.02165 0.770034 0.158586 -0.445182 -0.0443478 0.655487 0.972675 -0.24107 -0.560063 -0.194244 0.842883 0.749828
RQ14799-8 -0.956607 -0.686249 -0.478327 1.68038 -0.0311278 2.64806 -0.0842574 0.360613 -0.361503 -0.717515 0.227098 -0.179404 0.147733 0.907197 -0.401291
RQ14754-1 -0.226723 -0.480497 -0.604539 0.494973 -0.0712862 -0.0122033 1.24771 -0.274619 -0.173038 0.969016 -0.252396 -0.143416 -0.639724 0.307468 -1.22722
RQ22101-7 -0.47601 0.0133572 -0.689546 0.945925 1.51096 -0.526306 -1.00718 -0.0973459 -0.0701914 -0.710037 -0.9271 -0.953768 1.22585 0.303631 0.625667

I want to add the second file onto the first -> matched exactly by IID in the first file and Id in the second file. The desired output will look like this:

FID IID Country Smoker Cancer_Type Age pc1 pc2 pc3 pc4 pc5 pc6 pc7 pc8 pc9 pc10 pc11 pc12 pc13 pc14 pc15
1 RQ34365-4 1 2 1 70 -1.39583 -0.450994 0.156784 2.28138 -0.259947 2.83107 0.335012 0.632872 1.03957 -0.53202 -0.162737 -0.739506 -0.040795 0.249346 0.279228
2 RQ22067-0 1 3 1 58 -0.0731995 -0.0180998 -0.598532 0.0465712 0.152631 1.3425 -0.716615 -1.15831 -0.477422 0.429214 -0.5249 -0.793306 0.274061 0.608845 0.0224554
3 RQ22101-7 1 1 1 61 -0.47601 0.0133572 -0.689546 0.945925 1.51096 -0.526306 -1.00718 -0.0973459 -0.0701914 -0.710037 -0.9271 -0.953768 1.22585 0.303631 0.625667
4 RQ14754-1 2 3 1 70 -0.226723 -0.480497 -0.604539 0.494973 -0.0712862 -0.0122033 1.24771 -0.274619 -0.173038 0.969016 -0.252396 -0.143416 -0.639724 0.307468 -1.22722

I am new to awk and don't really know how I would do this. Any help would be greatly appreciated.

Answer 1

[Pierre Lindenbaum]

assuming tab as delimiter.

join -t '\t' -1 2 -2 1 \
    <(sort -t $'\t' -k2,2 file1.txt ) \
    <(sort -t $'\t' -k1,1 file2.txt )

[hkj7]

Please could you explain what this means for my understanding?

[Pierre Lindenbaum]

https://linux.die.net/man/1/join

https://www.geeksforgeeks.org/join-command-linux/