<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>

CC BY-SA 4.0
2019-10-03T17:51:15.400Z
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.
swbarnes2
CC BY-SA 4.0
2019-10-03T16:50:23.510Z
2019-10-04T15:29:14.577Z
2019-10-04T15:29:14.577Z
Ram RS
<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>

CC BY-SA 4.0
2019-10-03T17:10:43.737Z
2019-10-03T17:17:52.217Z
2019-10-03T17:17:52.217Z
rroutsong
<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>

CC BY-SA 4.0
2019-10-03T02:43:05.083Z
2019-10-03T05:54:29.673Z
2019-10-03T05:54:29.673Z
user11766958
<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>

CC BY-SA 4.0
2019-10-03T07:16:46.483Z
2019-10-03T07:16:46.483Z
oliv
<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>

CC BY-SA 4.0
2019-10-03T11:26:29.513Z
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.
terdon
CC BY-SA 4.0
2019-10-03T16:48:50.283Z
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_.
Ram RS
<awk>
Merging two files together
# 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

#!/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


---

