0
$\begingroup$

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
$\endgroup$
2
  • 1
    $\begingroup$ 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. $\endgroup$ – terdon Oct 3 '19 at 11:26
  • $\begingroup$ 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. $\endgroup$ – Ram RS Oct 3 '19 at 16:48
1
$\begingroup$

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.

$\endgroup$
3
$\begingroup$

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

$\endgroup$
1
  • 2
    $\begingroup$ 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. $\endgroup$ – swbarnes2 Oct 3 '19 at 17:51
1
$\begingroup$

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
$\endgroup$
1
$\begingroup$

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)
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.