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
        Quality Control Essentials for Next-Generation Sequencing Workflows
        by seqadmin




        Like all molecular biology applications, next-generation sequencing (NGS) workflows require diligent quality control (QC) measures to ensure accurate and reproducible results. Proper QC begins at nucleic acid extraction and continues all the way through to data analysis. This article outlines the key QC steps in an NGS workflow, along with the commonly used tools and techniques.

        Nucleic Acid Quality Control
        Preparing for NGS starts with isolating the...
        02-10-2025, 01:58 PM
      • seqadmin
        An Introduction to the Technologies Transforming Precision Medicine
        by seqadmin


        In recent years, precision medicine has become a major focus for researchers and healthcare professionals. This approach offers personalized treatment and wellness plans by utilizing insights from each person's unique biology and lifestyle to deliver more effective care. Its advancement relies on innovative technologies that enable a deeper understanding of individual variability. In a joint documentary with our colleagues at Biocompare, we examined the foundational principles of precision...
        01-27-2025, 07:46 AM

      ad_right_rmr

      Collapse

      News

      Collapse

      Topics Statistics Last Post
      Started by seqadmin, 02-07-2025, 09:30 AM
      0 responses
      68 views
      0 likes
      Last Post seqadmin  
      Started by seqadmin, 02-05-2025, 10:34 AM
      0 responses
      107 views
      0 likes
      Last Post seqadmin  
      Started by seqadmin, 02-03-2025, 09:07 AM
      0 responses
      83 views
      0 likes
      Last Post seqadmin  
      Started by seqadmin, 01-31-2025, 08:31 AM
      0 responses
      47 views
      0 likes
      Last Post seqadmin  
      Working...
      X