1

I have three files 2d.tsv, 3d.tsv, 4d.tsv, and they are of the same format (same number of columns with the same data type, but with different numbers of rows).

I want to select rows that are present in all files by comparing column 3 and writing three new files, 2d_new.tsv, 3d_new.tsv, 4d_new.tsv, that is, I want for the three new files to have the same number of rows and maintaining their original first and second columns according to the remaining rows. Could you please help me how to do this?

Sample Input:

2d.tsv

0.1 0.22 gene1
0.3 0.1 gene2
0.5 0.33 gene3
0.3 0.44 gene5
0.9 0.3 gene6

3d.tsv

0.4 0.5 gene2
0.6 0.1 gene3
0.1 0.99 gene4
0.01 0.44 gene5

4d.tsv

0.03 0.11 gene1
0.33 0.34 gene2
0.54 1 gene3
0.41 0.44 gene4
0.44 0.45 gene5
0.11 0.89 gene6

Desired Output:

2d_new.tsv

0.3 0.1 gene2
0.5 0.33 gene3
0.3 0.44 gene5

3d_new.tsv

0.4 0.5 gene2
0.6 0.1 gene3
0.01 0.44 gene5

4d_new.tsv

0.33 0.34 gene2
0.54 1 gene3
0.44 0.45 gene5
  • 1
    Hi, what have you tried so far ? – steve Oct 19 '19 at 15:25
  • I've tried to adapt what was being discussed in the topic link, including instances of cut and awk suggested there, but I could not adapt it to my case since I don't know how to write three different files in just a command line – Lucas Farinazzo Marques Oct 19 '19 at 15:33
2

I am sure there is some superstar who will come up with a stunning one shot awk solution but here is a sledgehammer.

Assuming that the files are all nicely sorted on the key in column 3 (if not you will need to sort them with sort -k 3 file first).

join -j 3 2d.tsv 3d.tsv | join -1 1 -2 3 - 4d.tsv > tmp
awk '{print $2, $3, $1}' tmp > 2d_new.tsv
awk '{print $4, $5, $1}' tmp > 3d new.tsv
awk '{print $6, $7, $1}' tmp > 4d_new.tsv

The last 3 lines can be combined

awk '{ print $2, $3, $1 > "2d_new.tsv"; print $4 $5, $1 > "3d_new.tsv"; print $6, $7, $1 > "4d_new.tsv" }' tmp

And if you are bent on a one liner to avoid the tmp file then just | the join to the awk

  • It worked like a glove! Thanks a lot! By the way, I've edited my question because it was supposed to be 3 columns instead of 4. – Lucas Farinazzo Marques Oct 19 '19 at 23:33
  • Answer edited to match the post,,,,, – bu5hman Oct 20 '19 at 17:20

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.