Seqanswers Leaderboard Ad

Collapse

Announcement

Collapse
No announcement yet.
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Comparing data from two columns

    Hello everybody,
    I am analysing variant calls from two different pipelines. What i need to do for my downstream analysis is combine all the variant calls into a single file, column.

    eg:

    file 1:
    Chr Position Chr_position gene_id
    1 1234 1_1234 ABCD
    1 12345 1_12345 ABCD
    1 123456 1_123456 ZXYW

    file2:
    Chr Position Chr_position protein_id
    1 1234 1_1234 ABCD1
    2 1234 2_1234 EFGH2
    2 123456 2_1234566 EFGH2
    1 12345 1_12345 ABCD1
    1 123456 1_123456 ABCD1

    What i would like to do is the following:

    file 3:
    Chr Position Chr_position gene_id protein_id
    1 1234 1_1234 ABCD ABCD1
    1 12345 1_12345 ABCD ABCD1
    1 123456 1_123456 ZXYW ABCD1
    2 1234 2_1234 EFGH2
    2 123456 2_1234566 EFGH2

    The number of rows in both the files are different, so using the Match option in excel is not going to work out. using Mysql is an option, but I am not that well versed in SQL. is there a way i could do this using AWK or any other scripting language? I am at a loss working on this! Any inputs would be mightily appreciated. Thanks!

  • #2
    >sort
    >join

    cat file1 |sort >file1.sort
    cat file2 |sort >file2.sort

    [palle@fe1 ~]$ join -v 1 -j 3 file1.sort file2.sort # All lines from file 1 found a match
    [palle@fe1 ~]$ join -v 2 -j 3 file1.sort file2.sort # Lines missing a match
    2_1234 2 1234 EFGH2
    2_1234566 2 123456 EFGH2
    [palle@fe1 ~]$ join -j 3 file1.sort file2.sort # Matched lines
    1_1234 1 1234 ABCD 1 1234 ABCD1
    1_12345 1 12345 ABCD 1 12345 ABCD1
    1_123456 1 123456 ZXYW 1 123456 ABCD1
    [palle@fe1 ~]$

    Comment


    • #3
      many thanks! I will try this out and post the results.

      Originally posted by pallevillesen View Post
      >sort
      >join

      cat file1 |sort >file1.sort
      cat file2 |sort >file2.sort

      [palle@fe1 ~]$ join -v 1 -j 3 file1.sort file2.sort # All lines from file 1 found a match
      [palle@fe1 ~]$ join -v 2 -j 3 file1.sort file2.sort # Lines missing a match
      2_1234 2 1234 EFGH2
      2_1234566 2 123456 EFGH2
      [palle@fe1 ~]$ join -j 3 file1.sort file2.sort # Matched lines
      1_1234 1 1234 ABCD 1 1234 ABCD1
      1_12345 1 12345 ABCD 1 12345 ABCD1
      1_123456 1 123456 ZXYW 1 123456 ABCD1
      [palle@fe1 ~]$

      Comment


      • #4
        Dude, Many many thanks! this is working out absolutely fine now. Looks like i should explore more of the Unix environment tools! This has streamlined my analysis and cut down a lot of time. Thanks a loT!

        Comment

        Latest Articles

        Collapse

        • seqadmin
          Strategies for Sequencing Challenging Samples
          by seqadmin


          Despite advancements in sequencing platforms and related sample preparation technologies, certain sample types continue to present significant challenges that can compromise sequencing results. Pedro Echave, Senior Manager of the Global Business Segment at Revvity, explained that the success of a sequencing experiment ultimately depends on the amount and integrity of the nucleic acid template (RNA or DNA) obtained from a sample. “The better the quality of the nucleic acid isolated...
          03-22-2024, 06:39 AM
        • seqadmin
          Techniques and Challenges in Conservation Genomics
          by seqadmin



          The field of conservation genomics centers on applying genomics technologies in support of conservation efforts and the preservation of biodiversity. This article features interviews with two researchers who showcase their innovative work and highlight the current state and future of conservation genomics.

          Avian Conservation
          Matthew DeSaix, a recent doctoral graduate from Kristen Ruegg’s lab at The University of Colorado, shared that most of his research...
          03-08-2024, 10:41 AM

        ad_right_rmr

        Collapse

        News

        Collapse

        Topics Statistics Last Post
        Started by seqadmin, 03-27-2024, 06:37 PM
        0 responses
        12 views
        0 likes
        Last Post seqadmin  
        Started by seqadmin, 03-27-2024, 06:07 PM
        0 responses
        11 views
        0 likes
        Last Post seqadmin  
        Started by seqadmin, 03-22-2024, 10:03 AM
        0 responses
        52 views
        0 likes
        Last Post seqadmin  
        Started by seqadmin, 03-21-2024, 07:32 AM
        0 responses
        68 views
        0 likes
        Last Post seqadmin  
        Working...
        X