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
        Exploring the Dynamics of the Tumor Microenvironment
        by seqadmin




        The complexity of cancer is clearly demonstrated in the diverse ecosystem of the tumor microenvironment (TME). The TME is made up of numerous cell types and its development begins with the changes that happen during oncogenesis. “Genomic mutations, copy number changes, epigenetic alterations, and alternative gene expression occur to varying degrees within the affected tumor cells,” explained Andrea O’Hara, Ph.D., Strategic Technical Specialist at Azenta. “As...
        07-08-2024, 03:19 PM
      • seqadmin
        Exploring Human Diversity Through Large-Scale Omics
        by seqadmin


        In 2003, researchers from the Human Genome Project (HGP) announced the most comprehensive genome to date1. Although the genome wasn’t fully completed until nearly 20 years later2, numerous large-scale projects, such as the International HapMap Project and 1000 Genomes Project, continued the HGP's work, capturing extensive variation and genomic diversity within humans. Recently, newer initiatives have significantly increased in scale and expanded beyond genomics, offering a more detailed...
        06-25-2024, 06:43 AM

      ad_right_rmr

      Collapse

      News

      Collapse

      Topics Statistics Last Post
      Started by seqadmin, 07-10-2024, 07:30 AM
      0 responses
      24 views
      0 likes
      Last Post seqadmin  
      Started by seqadmin, 07-03-2024, 09:45 AM
      0 responses
      201 views
      0 likes
      Last Post seqadmin  
      Started by seqadmin, 07-03-2024, 08:54 AM
      0 responses
      210 views
      0 likes
      Last Post seqadmin  
      Started by seqadmin, 07-02-2024, 03:00 PM
      0 responses
      192 views
      0 likes
      Last Post seqadmin  
      Working...
      X