4

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?

Improve this question
6
  • 2
    Your awk command should work perfectly. Are these files coming from Windows by any chance? What operating system are you using?
    – terdon ♦
    Commented Jun 7, 2017 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
    Commented Jun 8, 2017 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 ♦
    Commented Jun 8, 2017 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
    Commented Jun 8, 2017 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 ♦
    Commented Jun 8, 2017 at 10:43

3 Answers 3

Reset to default
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
Improve this answer
0
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)

Improve this answer
2
  • 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
    Commented Jun 8, 2017 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
    Commented Jun 8, 2017 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
Improve this answer

You must log in to answer this question.

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