I have two files. File1 is a csv with 60 fields:


File2 is a mapping file:


Now what I have to do is in field 2 of file 1 I have to replace the field with the field 1 of file 1 of matching field 2. Example:

Now what I have to do is replace field 2 of file1 with field 1 of the line in file2 matching field 2. Example:

In file1:


will be


where OPUM04181Check1|^/h1/h2/h3 is mapped in file2.

My constraint is that File1 and file2 don't have the same set of values; their count is different. Also, the line count is 3 million for both files.

This is what I was trying:

  • I ran a loop taking field 2 in file1
  • I was checking if that field is present in file2.
  • If present, I took the field 1 in file2.
  • I was replacing the field2 in file1 using sed s///g. But this is taking an enormous amount of time.

Also I can not sort File1.

What is a faster method?

  • There seems to be two problems. First, you are doing the complete look up 3 million times. Second, File2 is sorted by the first column (if at all) and you are looking up the second column. I think it is going to be worth the effort to first create a new temporary File2 which is sorted the way you need it to be so that you can use a binary search which is much faster (which is really going to matter when you are doing this so many times). This is all asking a lot from the shell but perhaps someone will know how. – krowe Jun 26 '14 at 12:58
  • If you're allowing scripting languages, they're going to be the simplest method of doing this (likely most efficient as well). You'll want to load columns 1 & 2 of File2 into a hash, and then fetch from the hash while iterating over File1. – Patrick Jun 26 '14 at 13:01
  • @Patrick Please can you elaborate or give an example. I have never worked using hash. and yes I am trying this in bash script. – roy_soham Jun 26 '14 at 13:04
  • hi @krowe Unfortunately File2 is not Sorted by field 1. – roy_soham Jun 26 '14 at 13:07
awk -F, -v OFS=, '
    NR==FNR {opu[$2]=$1; next} 
    $2 in opu {$2 = opu[$2]; print}
' file2 file1

This reads the "mapping" file into memory, and then replaces the 2nd field for file1. Each file is processed exactly once.

The output goes to stdout, so to replace the file "in place" do

awk ... file2 file1 > tempfile && mv tempfile file1
  • I copy-pasted your code.this is giving me output: awk: syntax error near line 1 awk: bailing out near line 1 – roy_soham Jun 26 '14 at 14:00
  • 1
    what is your OS? your version of awk? – glenn jackman Jun 26 '14 at 19:07

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.