How to use the awk command to combine columns from one file to another matching by ID?
1
0
Entering edit mode
4 months ago
hkj7 • 0

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.

awk • 225 views
ADD COMMENT
0
Entering edit mode
4 months ago

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 )
ADD COMMENT
0
Entering edit mode

Please could you explain what this means for my understanding?

ADD REPLY

Login before adding your answer.

Traffic: 1805 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6