0

So guys have two files, both of them are tab-separated text files, trying to merge both the files based on two columns. The two files are unsorted and doesn't contain a header. Another thing is that Final.tsv is large containing about 2 million rows.

  **`final.tsv`**

        ClinVarVariant:208014   OCRL:exon 6-12 del  HP:0000028  Cryptorchidism  MONDO:0010645   oculocerebrorenal syndrome 
    ClinVarVariant:208014   OCRL:exon 6-12 del  HP:0000083  Renal insufficiency MONDO:0010645   oculocerebrorenal syndrome
    ClinVarVariant:208014   OCRL:exon 6-12 del  HP:0000091  Abnormal renal tubule morphology    MONDO:0010645   oculocerebrorenal syndrome
    ClinVarVariant:208014   OCRL:exon 6-12 del  HP:0000093  Proteinuria MONDO:0010645   oculocerebrorenal syndrome
    ClinVarVariant:208014   OCRL:exon 6-12 del  HP:0000121  Nephrocalcinosis    MONDO:0010645   oculocerebrorenal syndrome
    ClinVarVariant:208014   OCRL:exon 6-12 del  HP:0000164  Abnormality of the dentition    MONDO:0010645   oculocerebrorenal syndrome
    ClinVarVariant:208014   OCRL:exon 6-12 del  HP:0000189  Narrow palate   MONDO:0010645   oculocerebrorenal syndrome
    ClinVarVariant:208014   OCRL:exon 6-12 del  HP:0000194  Open mouth  MONDO:0010645   oculocerebrorenal syndrome
    ClinVarVariant:208014   OCRL:exon 6-12 del  HP:0000219  Thin upper lip vermilion    MONDO:0010645   oculocerebrorenal syndrome



**om.tsv**
    309000  LOWE OCULOCEREBRORENAL SYNDROME HP:0000028  OMIM:309000 XLR
    309000  LOWE OCULOCEREBRORENAL SYNDROME HP:0000083  OMIM:309000 XLR
    309000  LOWE OCULOCEREBRORENAL SYNDROME HP:0000093  OMIM:309000 XLR
    309000  LOWE OCULOCEREBRORENAL SYNDROME HP:0000501  OMIM:309000 XLR
    309000  LOWE OCULOCEREBRORENAL SYNDROME HP:0000505  OMIM:309000 XLR

So the task here is match the 6th and 3rd column of the final.tsv file with the 2nd and 3rd column of the om.tsv file. On matching of the two columns, the two files should be merged and saved in a match file. If there is not matched the entire line should be printed into another mismatch file. Also note that i need a case-insensitive approach where the match is based on keywords.

Acc to the above eg oculocerebrorenal syndrome should match LOWE OCULOCEREBRORENAL SYNDROME.

 Output
      ClinVarVariant:208014 OCRL:exon 6-12 del  HP:0000028  Cryptorchidism  MONDO:0010645   oculocerebrorenal syndrome  309000  LOWE OCULOCEREBRORENAL SYNDROME HP:0000028  OMIM:309000 XLR

A lot of different approaches like awk, join and even a few pandas approaches were tried to solve this complexity. Any suggestions! Thanks in advance! :)

  • Can you clarify the many-to-many relations? Can you give examples - two input files and two output files? – Tomasz Oct 21 at 11:38
  • The major aim is the matching of the two columns. The disease name is formatted differently in both the files. Matching that column is where the complexity lies. @Tomasz – Thanujay Suthahar Oct 21 at 12:19
  • I'd use perl, which is very good at matching and processing text. – wurtel Oct 21 at 12:30
  • Simple matching of the columns is a trivial complexity. Joint the lines, then do two comparisons of columns case-insensitive. Then print the line columns to one file or the other. Without two columns in one case. – Tomasz Oct 21 at 12:43
  • That sounds good! @Tomasz It would be much appreciated if you could give me a sample code to build on! :) – Thanujay Suthahar Oct 21 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.