Answers.Body: <p>I'd recommend using R and either <code>merge</code> or one of the <code>join</code>s. Assuming your data is not too large, <code>data.frame</code> + <code>read.delim</code> should suffice to hold the two datasets. If not, try <code>data.table</code> + <code>fread</code>..</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-03T17:51:15.400Z
Answers.Text: I'd agree with this.  R merge will work even if things are out of order, or some things are missing from one file or another.
Answers.UserDisplayName: swbarnes2
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-03T16:50:23.510Z
Answers.LastActivityDate: 2019-10-04T15:29:14.577Z
Answers.LastEditDate: 2019-10-04T15:29:14.577Z
Answers.UserDisplayName: Ram RS
Answers.Body: <p>It's not awk: but this is a fairly simple python/pandas script (assuming tab-delimited).</p>
  
  <p>file1</p>
  
  <pre><code>CHROM   POS ID  REF ALT QUAL    FILTER  INFO
  </code></pre>
  
  <p>file2</p>
  
  <pre><code>CHROM POS ID REF ALT FATHER MOTHER DAUGHTER1 DAUGHTER2 DAUGHTER3 SON1 SON2
  </code></pre>
  
  <pre class="lang-py prettyprint-override"><code>#!/usr/bin/python
  import pandas as pd
  
  file1 = pd.read_csv('file1.txt', sep='\t')
  file2 = pd.read_csv('file2.txt', sep='\t')
  
  # you can compare file1.shape vs. file2.shape to ensure they match
  # you can also use fileN.sort_values(by=['POS', 'CHROM'], axis=1) to ensure sort matches
  
  file3 = pd.concat([file1, file2[['FATHER', 'MOTHER', 'DAUGHTER1', 'DAUGHTER2', 'DAUGHTER3', 'SON1', 'SON2']]], axis=1)
  
  file3.to_csv('merged_file.txt', sep='\t', index=False)
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-03T17:10:43.737Z
Answers.LastActivityDate: 2019-10-03T17:17:52.217Z
Answers.LastEditDate: 2019-10-03T17:17:52.217Z
Answers.UserDisplayName: rroutsong
Answers.Body: <p>I did something like  below,</p>
  
  <pre><code>awk 'FNR==NR{a[<span class="math-container">$1$</span>2]=<span class="math-container">$8 FS $</span>12;next}{ print <span class="math-container">$0, a[$</span>1 $2]}' file1.vcf file2.vcf &gt; Final.vcf
  </code></pre>
  
  <p>But then the output looks like</p>
  
  <pre><code>CHROM   POS ID  REF ALT QUAL    FILTER  FATHER MOTHER DAUGHTER1 DAUGHTER2 DAUGHTER3 SON1 SON2 INFO
  </code></pre>
  
  <p>is it possible to arrange the output to look like </p>
  
  <pre><code>CHROM   POS ID  REF ALT QUAL    FILTER  INFO FATHER MOTHER DAUGHTER1 DAUGHTER2 DAUGHTER3 SON1 SON2
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-03T02:43:05.083Z
Answers.LastActivityDate: 2019-10-03T05:54:29.673Z
Answers.LastEditDate: 2019-10-03T05:54:29.673Z
Answers.UserDisplayName: user11766958
Answers.Body: <p>If you just need to merge column together, you can use <code>paste</code> command:</p>
  
  <pre><code>paste file1 &lt;(cut -d\  -f6- file2)
  </code></pre>
  
  <p>In this case the whole <code>file1</code> is taken and only part of <code>file2</code> starting from column 6.</p>
  
  <p>Assuming your data are sorted, another alternative is to use <code>join</code>:</p>
  
  <pre><code>join -o '1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8 2.6 2.7 2.8 2.9 2.10 2.11 2.12' file1 file2
  </code></pre>
  
  <p>The <code>-o</code> option allows to select column from either <code>file1</code> or <code>file2</code>.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-03T07:16:46.483Z
Answers.LastActivityDate: 2019-10-03T07:16:46.483Z
Answers.UserDisplayName: oliv
Body: <p>Is it possible to merge two files together using awk? I have two files with matched  Chrom and pos  column</p>
  
  <p>file1 has the columns</p>
  
  <pre><code>CHROM   POS ID  REF ALT QUAL    FILTER  INFO
  </code></pre>
  
  <p>file2 has the columns </p>
  
  <pre><code>CHROM POS ID REF ALT FATHER MOTHER DAUGHTER1 DAUGHTER2 DAUGHTER3 SON1 SON2
  </code></pre>
  
  <p>I want the final merged file to have these columns</p>
  
  <pre><code>CHROM   POS ID  REF ALT QUAL    FILTER  INFO FATHER MOTHER DAUGHTER1 DAUGHTER2 DAUGHTER3 SON1 SON2
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-10-03T11:26:29.513Z
Comments.Text: Can you show us a few more lines from each file? Do the files have other headers or is it only the fields? If they're real vcf files, they will have headers which complicate things. If they're headerless lists of variants, there are better tools than awk.
Comments.UserDisplayName: terdon
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-10-03T16:48:50.283Z
Comments.Text: Matched `CHROM` and `POS` alone might create problems if there are multi-allelic variants (or even if `ALT` is different between the two files). You might want to match all three of those columns to be sure that the variants you're merging are _identical_.
Comments.UserDisplayName: Ram RS
Tags: <awk>
Title: Merging two files together
ThreadText: # Merging two files together
  
  (Asked by: username_0 on 2019-10-03)
  
  Is it possible to merge two files together using awk? I have two files with matched Chrom and pos column
  
  file1 has the columns
  
  ```
  CHROM   POS ID  REF ALT QUAL    FILTER  INFO
  
  ```
  
  file2 has the columns
  
  ```
  CHROM POS ID REF ALT FATHER MOTHER DAUGHTER1 DAUGHTER2 DAUGHTER3 SON1 SON2
  
  ```
  
  I want the final merged file to have these columns
  
  ```
  CHROM   POS ID  REF ALT QUAL    FILTER  INFO FATHER MOTHER DAUGHTER1 DAUGHTER2 DAUGHTER3 SON1 SON2
  
  ```
  
  **Tags:** awk
  
  > **Comments:**
  > **username_1**: Can you show us a few more lines from each file? Do the files have other headers or is it only the fields? If they're real vcf files, they will have headers which complicate things. If they're headerless lists of variants, there are better tools than awk.  
  > **username_2**: Matched `CHROM` and `POS` alone might create problems if there are multi-allelic variants (or even if `ALT` is different between the two files). You might want to match all three of those columns to be sure that the variants you're merging are _identical_.  
  
  ---
  
  ## 4 answers
  
  ### Answer by username_5 on 2019-10-03. Score: 2 | Accepted answer
  
  If you just need to merge column together, you can use `paste` command:
  
  ```
  paste file1 <(cut -d\  -f6- file2)
  
  ```
  
  In this case the whole `file1` is taken and only part of `file2` starting from column 6.
  
  Assuming your data are sorted, another alternative is to use `join`:
  
  ```
  join -o '1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8 2.6 2.7 2.8 2.9 2.10 2.11 2.12' file1 file2
  
  ```
  
  The `-o` option allows to select column from either `file1` or `file2`.
  
  
  ---
  
  ### Answer by username_2 on 2019-10-03. Score: 3
  
  I'd recommend using R and either `merge` or one of the `join`s. Assuming your data is not too large, `data.frame` + `read.delim` should suffice to hold the two datasets. If not, try `data.table` + `fread`..
  
  > **Comments:**
  > **username_3**: I'd agree with this.  R merge will work even if things are out of order, or some things are missing from one file or another.  
  
  ---
  
  ### Answer by username_4 on 2019-10-03. Score: 1
  
  It's not awk: but this is a fairly simple python/pandas script (assuming tab-delimited).
  
  file1
  
  ```
  CHROM   POS ID  REF ALT QUAL    FILTER  INFO
  
  ```
  
  file2
  
  ```
  CHROM POS ID REF ALT FATHER MOTHER DAUGHTER1 DAUGHTER2 DAUGHTER3 SON1 SON2
  
  ```
  
  ```py
  #!/usr/bin/python
  import pandas as pd
  
  file1 = pd.read_csv('file1.txt', sep='\t')
  file2 = pd.read_csv('file2.txt', sep='\t')
  
  # you can compare file1.shape vs. file2.shape to ensure they match
  # you can also use fileN.sort_values(by=['POS', 'CHROM'], axis=1) to ensure sort matches
  
  file3 = pd.concat([file1, file2[['FATHER', 'MOTHER', 'DAUGHTER1', 'DAUGHTER2', 'DAUGHTER3', 'SON1', 'SON2']]], axis=1)
  
  file3.to_csv('merged_file.txt', sep='\t', index=False)
  
  ```
  
  
  ---
  
  ### Answer by username_0 on 2019-10-03. Score: 1
  
  I did something like below,
  
  ```
  awk 'FNR==NR{a[$1$2]=$8 FS $12;next}{ print $0, a[$1 $2]}' file1.vcf file2.vcf > Final.vcf
  
  ```
  
  But then the output looks like
  
  ```
  CHROM   POS ID  REF ALT QUAL    FILTER  FATHER MOTHER DAUGHTER1 DAUGHTER2 DAUGHTER3 SON1 SON2 INFO
  
  ```
  
  is it possible to arrange the output to look like
  
  ```
  CHROM   POS ID  REF ALT QUAL    FILTER  INFO FATHER MOTHER DAUGHTER1 DAUGHTER2 DAUGHTER3 SON1 SON2
  
  ```
  
  
  ---
  
  