Seqanswers Leaderboard Ad

Collapse

Announcement

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

  • Using AWK to perform VLOOKUP-like command

    Hi all,

    I have a set of files with genotypic data, each divided into 3 columns of data, including: MARKER, ID, GENOTYPE.

    I would like to use AWK (without changing the order/sorting of the files) to perform a VLOOKUP-like command in order to join the data within the files into a single file as follows:

    File1:

    BIEC2-99962 HOR_233 G_G
    BIEC2-9997 HOR_233 A_G
    BIEC2-999748 HOR_233 C_C
    BIEC2-999848 HOR_233 G_G
    BIEC2-99989 HOR_233 A_A

    File2:

    BIEC2-9997 HOR_250 A_A
    BIEC2-999748 HOR_250 C_C
    BIEC2-99989 HOR_250 A_C

    File3:

    BIEC2-9997 HOR_615 A_G
    BIEC2-999748 HOR_615 A_C
    BIEC2-999848 HOR_615 A_G
    BIEC2-99989 HOR_615 A_C

    Expected result:

    BIEC2-99962 G_G NA NA
    BIEC2-9997 A_G A_A A_G
    BIEC2-999748 C_C C_C A_C
    BIEC2-999848 G_G NA A_G
    BIEC2-99989 A_A A_C A_C

    I would appreciate any help on this.

    Thanks!
    Last edited by sagi.polani; 02-19-2015, 12:25 AM.

  • #2
    Cross-posted on biostars.

    Comment


    • #3
      You could probably modify this script for your input sets:

      Code:
      #!/usr/bin/env python
      
      input_one = '''
      BIEC2-99962 HOR_233 G_G
      BIEC2-9997 HOR_233 A_G
      BIEC2-999748 HOR_233 C_C
      BIEC2-999848 HOR_233 G_G
      BIEC2-99989 HOR_233 A_A
      '''.strip().split()
      
      input_two = '''
      BIEC2-9997 HOR_250 A_A
      BIEC2-999748 HOR_250 C_C
      BIEC2-99989 HOR_250 A_C
      '''.strip().split()
      
      input_three = '''
      BIEC2-9997 HOR_615 A_G
      BIEC2-999748 HOR_615 A_C
      BIEC2-999848 HOR_615 A_G
      BIEC2-99989 HOR_615 A_C
      '''.strip().split()
      
      one_list = input_one[::3]
      one_dict = dict(zip(one_list, input_one[2::3]))
      two_dict = dict(zip(input_two[::3], input_two[2::3]))
      three_dict = dict(zip(input_three[::3], input_three[2::3]))
      
      print '\n'.join([' '.join([k, one_dict[k], two_dict.get(k, 'NA'), three_dict.get(k, 'NA')]) for k in one_list])
      The output looks like:

      Code:
      $ ./join_test.py
      BIEC2-99962 G_G NA NA
      BIEC2-9997 A_G A_A A_G
      BIEC2-999748 C_C C_C A_C
      BIEC2-999848 G_G NA A_G
      BIEC2-99989 A_A A_C A_C
      This seems to match your expected output.

      If you want to understand how the script works, use some print statements for each variable before the list comprehension, and then break the list comprehension down into smaller pieces.

      Ultimately, you would replace lists input_one, input_two and input_three with the results from reading in your input files with open() and readlines() methods.

      Remember to strip() and split() so that each element of the list is separated from the others, regardless of whether the delimiter is a space or newline — use print to investigate one of the sample input lists, if this requirement isn't clear.

      I'd second that awk is not really the ideal tool for this job, and I use it a great deal.

      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
      53 views
      0 likes
      Last Post seqadmin  
      Started by seqadmin, 03-21-2024, 07:32 AM
      0 responses
      69 views
      0 likes
      Last Post seqadmin  
      Working...
      X