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. I have used the command:

awk 'NR == FNR{a[$2] = $0; next} $1 in a {print a[$1], $0}' file 1 file 2

But my output is wrong I just get the second file printed out with no file 1 attached. Essentially I want file 1 and file 2 joined together matching by ID.

1
  • With your code you should at least get the merged lines with a doubled ID entry. What exactly do you see?
    – FelixJN
    Jan 19, 2022 at 15:29

1 Answer 1

0
awk '#set field separators for in- and output to single space
     BEGIN {FS=OFS=" "}
     #save header line from file 1
     NR==1 {head1=$0 ; next}
     #for other lines in file1: save line into array with ID as key
     NR==FNR  {line[$2]=$0}
     #get header line of file 2, remove doubled ID entry and print
     FNR==1 {$1="" ; printf "%s%s\n",head1,$0 ; next}
     #for other lines in file 2: if ID matches any key in array line
     #print the array entry and the line from file2 with the ID removed
     NR!=FNR && $1 in line  {a=$1 ; $1="" ; printf "%s%s\n",line[a],$0}
     ' file1 file2 | sort -n

Check this for an introduction to awk.

9
  • I get the following result when running your command: FID IID Country Smoker Cancer_Type Age pc1 pc2 pc3 pc4 pc5 pc6 pc7 pc8 pc9 pc10 pc11 pc12 pc13 pc14 pc15 -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
    – HKJ3
    Jan 19, 2022 at 20:34
  • I don't get the contents of file 1 in the output. E.g. RQ34365-4 1 2 1 70 (the RQ id and the following values for the Country smoker, cancer type...
    – HKJ3
    Jan 19, 2022 at 20:37
  • @HKJ3 What is the output of head -2 file1 | tail -1 | cat -A?
    – FelixJN
    Jan 19, 2022 at 21:21
  • 1 RQ34365-4 1 2 1 70^M$
    – HKJ3
    Jan 19, 2022 at 21:56
  • There you go. MS-DOS line feeds. Convert your files with dos2unix or use <(tr -d '\r' <file1) <(tr -d '\r' file2) for the input files. If need be reconvert the result via unix2dos to use in MS DOS environments. Please make sure to adjust your question with this information.
    – FelixJN
    Jan 20, 2022 at 11:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .