2

I have two very large text files with space-delimited fields:

File1

527858  51  2   27.92464882 8.63E-07
570289  82  2   30.12532071 2.87E-07
571034  90  2   29.26089611 4.43E-07
571033  90  2   28.56723908 6.26E-07
452403  104 2   28.27577506 7.24E-07
351390  100 2   28.16226794 7.67E-07
527858  50  2   27.92464882 8.63E-07

File2

527858  rs435           
570289  rs564           
571034  rs654           
571033  rs345           
452403  rs665           
351390  rs787           
527858  rs435           

output:

rs435   51  2   27.92464882 8.63E-07
rs564   82  2   30.12532071 2.87E-07
rs654   90  2   29.26089611 4.43E-07
rs345   90  2   28.56723908 6.26E-07
rs665   104 2   28.27577506 7.24E-07
rs787   100 2   28.16226794 7.67E-07
rs435   50  2   27.92464882 8.63E-07

Compare the first column of file1 and file2 and replace the first column of file1 with names in 2nd column of file2.

0

Here's the same basic idea as the awk in Archemar's answer, implemented in Perl:

$ perl -lane '$#F>1?print"$l{$F[0]} @F[1..$#F]":($l{$F[0]}=$F[1])' file2 file1
rs435 51 2 27.92464882 8.63E-07
rs564 82 2 30.12532071 2.87E-07
rs654 90 2 29.26089611 4.43E-07
rs345 90 2 28.56723908 6.26E-07
rs665 104 2 28.27577506 7.24E-07
rs787 100 2 28.16226794 7.67E-07
rs435 50 2 27.92464882 8.63E-07

Explanation

  • -lane: the -l adds a newline to each print call and removes trailing newlines from each line of input. The -a makes perl act like awk: it will automatically split each input line into the array @F. So, the 1st field will be $F[0], the second $F[1] etc. The -n tells perl to read its input file(s) line by line and apply the script given with -e to each of them.

  • $#F>1? ... : ... : this is a C-style conditional operator. The general format is condition ? foo : bar which means "if condition is true, do foo and if it isn't, do bar. The $#F is the number of array indices in the array @F. Since arrays begin at 0, a value of 1 means an array with two elements. So, this will execute the first block (print ..., see below) if there are more than 2 elements in the array, which will only be true for file1.

  • ($l{$F[0]}=$F[1]) : this is executed for each line of file2, for each line with less than 3 fields. It populates the hash %l, whose keys are the numerical first fields of file2 and whose values are the associated rsIDs.
  • print"$l{$F[0]} @F[1..$#F]" : print the rsID saved in the hash %l for this first field ($l{$F[0]}), a space, and then the rest of the fields in this line ($F[1..$#F]).

Personally, I would probably use the awk solution or, at worst, the perl one I provided since they don't need the files to be sorted. However, since you tagged with join, here's how to do it using that tool:

$ join -o 2.2 1.2 1.3 1.4 1.5 <(sort file1) <(sort file2)
rs787 100 2 28.16226794 7.67E-07
rs665 104 2 28.27577506 7.24E-07
rs435 50 2 27.92464882 8.63E-07
rs435 50 2 27.92464882 8.63E-07
rs435 51 2 27.92464882 8.63E-07
rs435 51 2 27.92464882 8.63E-07
rs564 82 2 30.12532071 2.87E-07
rs345 90 2 28.56723908 6.26E-07
rs654 90 2 29.26089611 4.43E-07
  • thankuu soo much.. This is really helpful.. what if i have to do the same but change the 2nd column of file1. 527858 rs435 2 27.92464882 8.63E-07 – H.K Mar 14 '16 at 18:14
  • @H.K I would use awk instead: awk 'FNR==NR { F2[$1]=$2 ; next } $1 in F2 {$2 = F2[$2] ; print } ' File2 File1 – terdon Mar 15 '16 at 10:58
  • think there is some problem with the output..it is not howing me the 1st column now. It did change the 100 with relavant vale.. but rs column is not printed.. – H.K Mar 15 '16 at 11:30
  • @H.K please stop trying to edit my answer to leave me a message. That's what the comments are for. Also, please don't continue this discussion here. You might want to take the tour to see how the site works. We're a Q&A site, not a discussion forum, so if you have a new question, please post it separately. Show what you tried and how it failed and link back to this question for reference. Then, feel free to leave me a comment (not an edit) here linking me to the new question. – terdon Mar 15 '16 at 11:33
2

I would try

awk 'FNR==NR { F2[$1]=$2 ; next } $1 in F2 {$1 = F2[$1] ; print } ' File2 File1

where

  • FNR==NR { F2[$1]=$2 ; next } store values from File1
  • $1 = F2[$1] replace key
  • rs435 51 2 27.92464882 8.63E-07 rs564 82 2 30.12532071 2.87E-07 rs654 90 2 29.26089611 4.43E-07 rs345 90 2 28.56723908 6.26E-07 rs665 104 2 28.27577506 7.24E-07 rs787 100 2 28.16226794 7.67E-07 rs435 50 2 27.92464882 8.63E-07 i am getting the output in this format.. the rs# is in the separate line – H.K Mar 14 '16 at 17:27
  • @H.K please edit your question to add extra information. It is hard to read and easy to miss in the comments. Also, comments can be deleted with no warning. – terdon Mar 14 '16 at 17:30

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.