Merging two files together

Question

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

Answer 1

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 2

I'd recommend using R and either merge or one of the joins. Assuming your data is not too large, data.frame + read.delim should suffice to hold the two datasets. If not, try data.table + fread..

Answer 3

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

Answer 4

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)