Merge files using a common column (awk)

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP








up vote
0
down vote

favorite












I have two text files:



file 1:



#CHROM POS ID REF ALT
1 61442 rs2531261 A G
1 924629 rs28622096 A G,T
1 974662 rs2465135 G T
1 995481 rs9442393 T G


file 2



#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 987909 rs34235844 G GTG GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD


I would like to combine the files based on column 3 (ID). My output file should like:



output:



#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD


I want to use awk.






awk text







share|improve this question























  • What information is in file 1 that is not already in file 2? It's not clear from your question.
    – waltinator
    Feb 1 at 20:12














up vote
0
down vote

favorite












I have two text files:



file 1:



#CHROM POS ID REF ALT
1 61442 rs2531261 A G
1 924629 rs28622096 A G,T
1 974662 rs2465135 G T
1 995481 rs9442393 T G


file 2



#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 987909 rs34235844 G GTG GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD


I would like to combine the files based on column 3 (ID). My output file should like:



output:



#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD


I want to use awk.






awk text







share|improve this question























  • What information is in file 1 that is not already in file 2? It's not clear from your question.
    – waltinator
    Feb 1 at 20:12












up vote
0
down vote

favorite









up vote
0
down vote

favorite











I have two text files:



file 1:



#CHROM POS ID REF ALT
1 61442 rs2531261 A G
1 924629 rs28622096 A G,T
1 974662 rs2465135 G T
1 995481 rs9442393 T G


file 2



#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 987909 rs34235844 G GTG GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD


I would like to combine the files based on column 3 (ID). My output file should like:



output:



#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD


I want to use awk.






awk text







share|improve this question















I have two text files:



file 1:



#CHROM POS ID REF ALT
1 61442 rs2531261 A G
1 924629 rs28622096 A G,T
1 974662 rs2465135 G T
1 995481 rs9442393 T G


file 2



#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 987909 rs34235844 G GTG GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD


I would like to combine the files based on column 3 (ID). My output file should like:



output:



#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD


I want to use awk.






awk text




awk text






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Feb 1 at 20:04









Zanna

48.3k13120229




48.3k13120229










asked Feb 1 at 20:02









June

1




1











  • What information is in file 1 that is not already in file 2? It's not clear from your question.
    – waltinator
    Feb 1 at 20:12
















  • What information is in file 1 that is not already in file 2? It's not clear from your question.
    – waltinator
    Feb 1 at 20:12















What information is in file 1 that is not already in file 2? It's not clear from your question.
– waltinator
Feb 1 at 20:12




What information is in file 1 that is not already in file 2? It's not clear from your question.
– waltinator
Feb 1 at 20:12










1 Answer
1






active

oldest

votes

















up vote
2
down vote













It looks like you want to filter file2 based on the IDs in file 1 (rather than merge the files):



$ awk 'NR==FNR a[$3] = 1; next $3 in a' file1 file2
#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD





share|improve this answer




















    Your Answer







    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "89"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    convertImagesToLinks: true,
    noModals: false,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













     

    draft saved


    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2faskubuntu.com%2fquestions%2f1002176%2fmerge-files-using-a-common-column-awk%23new-answer', 'question_page');

    );

    Post as a guest

















    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    2
    down vote













    It looks like you want to filter file2 based on the IDs in file 1 (rather than merge the files):



    $ awk 'NR==FNR a[$3] = 1; next $3 in a' file1 file2
    #CHROM POS ID REF ALT FORMAT
    1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
    1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
    1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
    1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD





    share|improve this answer
























      up vote
      2
      down vote













      It looks like you want to filter file2 based on the IDs in file 1 (rather than merge the files):



      $ awk 'NR==FNR a[$3] = 1; next $3 in a' file1 file2
      #CHROM POS ID REF ALT FORMAT
      1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
      1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
      1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
      1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD





      share|improve this answer






















        up vote
        2
        down vote










        up vote
        2
        down vote









        It looks like you want to filter file2 based on the IDs in file 1 (rather than merge the files):



        $ awk 'NR==FNR a[$3] = 1; next $3 in a' file1 file2
        #CHROM POS ID REF ALT FORMAT
        1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
        1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
        1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
        1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD





        share|improve this answer












        It looks like you want to filter file2 based on the IDs in file 1 (rather than merge the files):



        $ awk 'NR==FNR a[$3] = 1; next $3 in a' file1 file2
        #CHROM POS ID REF ALT FORMAT
        1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
        1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
        1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
        1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Feb 1 at 20:25









        steeldriver

        63.5k1199167




        63.5k1199167



























             

            draft saved


            draft discarded















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2faskubuntu.com%2fquestions%2f1002176%2fmerge-files-using-a-common-column-awk%23new-answer', 'question_page');

            );

            Post as a guest
































































            -

            Popular posts from this blog

            Which professions warranted travel in Medieval times?

            Image
            Clash Royale CLAN TAG #URR8PPP up vote 54 down vote favorite 15 I'm trying to come up with characters whose backgrounds allow them to have homes, histories, and skills, but will still enable or encourage them to travel in a typical medieval setting. I've never really liked "Adventurer" as a job, and would like my story to have a kind of unlikely-hero vibe. besides the common "Traveling merchant", what kinds of artisans or professionals in fantasy would have a reason to move around the world (or at least visit other settlements)? First time posting, so let me know what to improve. Thanks! EDIT: When I say traveling, I was thinking someone who spends multiple days/weeks/months away from their place of origin as they go elsewhere for their business, or to gather materials for it. medieval travel share | improve this question edited Aug 1 at 15:16 asked Aug 1 at 15:07 Julia Witham 279 2 7 11 Please ...
            Read more

            How do so many people here on Academia.SE, and in general, afford lavish higher education programs?

            Image
            Clash Royale CLAN TAG #URR8PPP up vote 45 down vote favorite 7 I graduated last year with a BS in Computer Science. I do not come from a particularly wealthy family, (very low middle class), and was only able to afford my education via school loans/scholarships and working low-wage jobs, with no support from my family or anyone else. I realize some scholarship/loan programs extend into grad school. But I don't think those cover all costs. Now I'm lucky enough to have a decent-paying salary in industry, along with benefits and living on my own, and I'm thinking one day (sooner rather than later) I'd like to try my hand at research or continue my degree, but these things cost money, in addition to my regular living expenses. I read posts here often about academic people being in positions of choosing between these crazy research grad programs, some in Japan, or Ireland (while they currently live in the U.S./U.K.) then moving around between countries/universit...
            Read more

            Trouble downloading packages list due to a “Hash sum mismatch” error

            Image
            Clash Royale CLAN TAG #URR8PPP .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0; up vote 331 down vote favorite 117 When I check for updates, I get a "Failed To Download Repository Information" error. This is what comes up under details: W: Failed to fetch gzip:/var/lib/apt/lists/partial/us.archive.ubuntu.com_ubuntu_dists_natty_main_source_Sources Hash Sum mismatch, E: Some index files failed to download. They have been ignored, or old ones used instead. apt share | improve this question edited Nov 12 '14 at 23:37 Braiam 49.5k 19 130 210 asked May 9 '11 at 20:55 Rob 5,300 10 26 40 add a comment  |  up vote 331 down vote favorite 117 When I check for updates, I get a "Failed To Download Repository Information" error. This is what comes up under details: W: Failed to fetch gzip:/var/lib/apt/lists/partial/us.archive.ubuntu.com_ubuntu_dists_nat...
            Read more