2

I have two TSV (tab separated value) files -- file_A.txt and file_B.txt

file_A.txt contains two columns -- A_ID, A_Name

file_B.txt contains 4 columns -- B_ID1, B_Name1, B_ID2, B_Name2

Need to go through each A_ID, if it matches B_ID1 or B_ID2, replace B_Name1 or B_Name2 with A_Name accordingly.

I am looking for a way to do it efficiently since file_B is few hundred MB.

Any suggestion?

  • 1
    How big is fileA ? – Stephen Harris Aug 31 '16 at 22:30
  • 1
    "a few hundred MB" isn't all that large. Just about anything should be able to handle that okay... – larsks Aug 31 '16 at 23:29
  • Bash is easy to hack together but very slow at processing. I've done similar things (search and replace) on similar sized files (couple thousand lines) and it works. If you're keen then you can make something in C(++) and you'll see a dramatic speed improvement of maybe 50x to 100x. – captcha Sep 1 '16 at 1:40
  • Have you actually tried any of the "standard" solutions such as awk -vOFS=\\t 'NR==FNR {a[$1]=$2; next}; $1 in a {$2=a[$1]}; $3 in a {$4=a[$3]}; {print}' file_A.txt file_B.txt – steeldriver Sep 1 '16 at 2:27
  • if possible, go for any light weight db. – Spike Sep 1 '16 at 3:52
0

Assuming you want the substitutions in file_B.txt format, as Spike suggested, a light weight db like sqlite could do this.

The following would read file_A.txt and file_B.txt into a transient in-memory database and output the results to the TSV file MODIFIED-file_B.txt

sqlite3 -separator $'\t' << EOF  > MODIFIED-file_B.txt
CREATE TABLE a ( id INTEGER PRIMARY KEY, name );
.import file_A.txt a
CREATE TABLE b (id1 INTEGER,name1 TEXT,id2 INTEGER,name2 TEXT);
.import file_B.txt b
SELECT b.id1,
       ifnull((SELECT name from a WHERE a.id=b.id1),b.name1),
       b.id2, 
       ifnull((SELECT name from a WHERE a.id=b.id2),b.name2)
FROM b;
EOF

This reads each file into a separate table then outputs the b table with your A substitutions, if available, otherwise returns the original B_name.

If memory usage is a concern you can run it with a filename e.g. sqlite3 -separator $'\t' temporary.db to create a scratch file.

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.