3

I have 2 files. File1 is:

chr19   4124051 4124250 1
chrX    154458151   154458200   2
chr22   37019451    37019600    3
chr15   74995401    74995550    4
chr12   128823901   128824100   5

and File2 is:

chr19   4124051 4124250 1   CUP
chr15   74995401    74995550    4   CUP
chr12   128823901   128824100   5   CUP
chr12   122752651   122752950   8   CUP
chr13   113297001   113297350   9   CUP

and I would like to have a File3 like this:

chr19   4124051 4124250 1 CUP
chrX    154458151   154458200   2
chr22   37019451    37019600    3
chr15   74995401    74995550    4 CUP
chr12   128823901   128824100   5 CUP

I'd like to merge the two files according to column 4 of File1, and adding values of column 5 from File2 to the last column of File1 whenever there is a match.

I tired this:

awk 'FNR==NR{a[$4]=$5;next} {print $1,$2,$3,$4,a[$4]}' File2 File1 > file3

but it didn't work, it creates an extra row under each row.

And I tried join command as well:

join -1 4 -2 4 -o'1.1,1.2,1.3,1.4,2.5' File1 File2

and it creates an empty file.

Any suggestions?

  • 2
    Your awk command should work perfectly. Are these files coming from Windows by any chance? What operating system are you using? – terdon Jun 7 '17 at 14:57
  • I am using a Mac, and using the bash terminal . The files came from Excel actually, I saved them in Windows Formatted text. I don't know if it's a problem of formatting actually .. – Chiara Jun 8 '17 at 10:39
  • It is. If the file came from excel, the problem is precisely that. You need to remove the Windows-style line endings. – terdon Jun 8 '17 at 10:41
  • sorry for my ignorance eheh but I have no idea how to do it :( do I have to save it in another format then? – Chiara Jun 8 '17 at 10:43
  • 1
    See my answer below. Just run the sed commands first to remove the \r and then your other approaches should work as expected. By the way: please don't use Excel for bioinformatics analyses! Every time you do, you make a bioinformatician cry :P – terdon Jun 8 '17 at 10:43
2

Your files were created in Windows so they have Windows style line-endings (\r\n). Remove the \r and everything should work as you expect:

sed -i 's/\r//' File1
sed -i 's/\r//' File2
awk 'FNR==NR{a[$4]=$5;next} {print $1,$2,$3,$4,a[$4]}' File2 File1 > file3
1

join + sort solution:

join -j4 -a1 -o1.1,1.2,1.3,1.4,2.5 <(sort -k4 File1) <(sort -k4 File2) | column -t

The output:

chr19  4124051    4124250    1  CUP
chrX   154458151  154458200  2
chr22  37019451   37019600   3
chr15  74995401   74995550   4  CUP
chr12  128823901  128824100  5  CUP

  • -j4 - join by the 4th column

  • -a1 - print unpairable lines from the 1st file

  • sort -k4 File1 - sort the file via 4th column/key (-k4)

  • Thanks! but it doesn't work either .. It finds only a match in the last row, while the matching fields are the 80% of the list.. Could be a problem of the input file? Not correctly formatted or tabulated .. it comes from excel originally. – Chiara Jun 8 '17 at 10:42
  • @Chiara, yes, I think there are issues with whitespaces in your file. Try to apply sed -i 's/ *$|\r//' file to your file beforehand – RomanPerekhrest Jun 8 '17 at 10:47
0
perl -F'\s+' -lane '
   s/\r$//; my $i = join $;, @F[0,3];
   @ARGV and $h{$i} = $F[4],next;
   print exists $h{$i} ? s/$/ $h{$i}/r : $_;
' File2 File1

Gives:

chr19   4124051 4124250 1
chrX    154458151   154458200   2
chr22   37019451    37019600    3
chr15   74995401    74995550    4 CUP
chr12   128823901   128824100   5 CUP

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.