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

<p>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:</p>

<p>File1:</p>

<pre>
<code>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</code></pre>

<p>File2:</p>

<pre>
<code>BIEC2-9997 HOR_250 A_A
BIEC2-999748 HOR_250 C_C
BIEC2-99989 HOR_250 A_C</code></pre>

<p>File3:</p>

<pre>
<code>BIEC2-9997 HOR_615 A_G
BIEC2-999748 HOR_615 A_C
BIEC2-999848 HOR_615 A_G
BIEC2-99989 HOR_615 A_C</code></pre>

<p>Expected result:</p>

<pre>
<code>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</code></pre>

<p>I would appreciate any help on this.</p>

<p>Thanks!</p>

I strongly recommend you against using awk for complex join operations. I would be able to tell you a million stories, about times when I trusted the output of a awk command, only to discover that it didn't run correctly because of some minor mistake or some exception.

This is how I would do it using R and the dplyr package:

```
> library(dplyr)
> bio1 = read.table('bio1')
> bio2 = read.table('bio2')
> bio3 = read.table('bio3')
> bio1 %>%
    left_join(
        bio2 %>% # join with the second file (only the first and third column)
            select(V1, V3), 
        by='V1') %>%     
    left_join(
        bio3 %>% 
            select(V1, V3), 
        by='V1') %>%
    mutate_each(
       funs(ifelse(is.na(.), 'NA', as.character(.))), starts_with('V3'))   # transform all NAs to the string NA
```