Merge files using a common column (awk)
Clash Royale CLAN TAG#URR8PPP up vote
0
down vote
favorite
I have two text files:
file 1:
#CHROM POS ID REF ALT
1 61442 rs2531261 A G
1 924629 rs28622096 A G,T
1 974662 rs2465135 G T
1 995481 rs9442393 T G
file 2
#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 987909 rs34235844 G GTG GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
I would like to combine the files based on column 3 (ID). My output file should like:
output:
#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
I want to use awk.
awk text
edited Feb 1 at 20:04
Zanna
48.3k13120229
asked Feb 1 at 20:02
June
1
add a comment |Â
up vote
0
down vote
favorite
I have two text files:
file 1:
#CHROM POS ID REF ALT
1 61442 rs2531261 A G
1 924629 rs28622096 A G,T
1 974662 rs2465135 G T
1 995481 rs9442393 T G
file 2
#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 987909 rs34235844 G GTG GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
I would like to combine the files based on column 3 (ID). My output file should like:
output:
#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
I want to use awk.
awk text
edited Feb 1 at 20:04
Zanna
48.3k13120229
asked Feb 1 at 20:02
June
1
What information is infile 1that is not already infile 2? It's not clear from your question.
– waltinator
Feb 1 at 20:12
add a comment |Â
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have two text files:
file 1:
#CHROM POS ID REF ALT
1 61442 rs2531261 A G
1 924629 rs28622096 A G,T
1 974662 rs2465135 G T
1 995481 rs9442393 T G
file 2
#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 987909 rs34235844 G GTG GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
I would like to combine the files based on column 3 (ID). My output file should like:
output:
#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
I want to use awk.
awk text
edited Feb 1 at 20:04
Zanna
48.3k13120229
asked Feb 1 at 20:02
June
1
I have two text files:
file 1:
#CHROM POS ID REF ALT
1 61442 rs2531261 A G
1 924629 rs28622096 A G,T
1 974662 rs2465135 G T
1 995481 rs9442393 T G
file 2
#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 987909 rs34235844 G GTG GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
I would like to combine the files based on column 3 (ID). My output file should like:
output:
#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
I want to use awk.
awk text
awk text
edited Feb 1 at 20:04
Zanna
48.3k13120229
asked Feb 1 at 20:02
June
1
edited Feb 1 at 20:04
Zanna
48.3k13120229
asked Feb 1 at 20:02
June
1
edited Feb 1 at 20:04
Zanna
48.3k13120229
edited Feb 1 at 20:04
Zanna
48.3k13120229
edited Feb 1 at 20:04
Zanna
48.3k13120229
48.3k13120229
asked Feb 1 at 20:02
June
1
asked Feb 1 at 20:02
June
1
asked Feb 1 at 20:02
June
1
1
What information is infile 1that is not already infile 2? It's not clear from your question.
– waltinator
Feb 1 at 20:12
add a comment |Â
What information is infile 1that is not already infile 2? It's not clear from your question.
– waltinator
Feb 1 at 20:12
What information is in
file 1 that is not already in file 2? It's not clear from your question.– waltinator
Feb 1 at 20:12
What information is in
file 1 that is not already in file 2? It's not clear from your question.– waltinator
Feb 1 at 20:12
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
2
down vote
It looks like you want to filter file2 based on the IDs in file 1 (rather than merge the files):
$ awk 'NR==FNR a[$3] = 1; next $3 in a' file1 file2
#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
answered Feb 1 at 20:25
steeldriver
63.5k1199167
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
It looks like you want to filter file2 based on the IDs in file 1 (rather than merge the files):
$ awk 'NR==FNR a[$3] = 1; next $3 in a' file1 file2
#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
answered Feb 1 at 20:25
steeldriver
63.5k1199167
add a comment |Â
up vote
2
down vote
It looks like you want to filter file2 based on the IDs in file 1 (rather than merge the files):
$ awk 'NR==FNR a[$3] = 1; next $3 in a' file1 file2
#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
answered Feb 1 at 20:25
steeldriver
63.5k1199167
add a comment |Â
up vote
2
down vote
up vote
2
down vote
It looks like you want to filter file2 based on the IDs in file 1 (rather than merge the files):
$ awk 'NR==FNR a[$3] = 1; next $3 in a' file1 file2
#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
answered Feb 1 at 20:25
steeldriver
63.5k1199167
It looks like you want to filter file2 based on the IDs in file 1 (rather than merge the files):
$ awk 'NR==FNR a[$3] = 1; next $3 in a' file1 file2
#CHROM POS ID REF ALT FORMAT
1 61442 rs2531261 A G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 924629 rs28622096 A G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 974662 rs2465135 G T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1 995481 rs9442393 T G GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
answered Feb 1 at 20:25
steeldriver
63.5k1199167
answered Feb 1 at 20:25
steeldriver
63.5k1199167
answered Feb 1 at 20:25
steeldriver
63.5k1199167
answered Feb 1 at 20:25
steeldriver
63.5k1199167
63.5k1199167
add a comment |Â
add a comment |Â
Â
draft saved
draft discarded
Â
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
var $window = $(window),
onScroll = function(e)
var $elem = $('.new-login-left'),
docViewTop = $window.scrollTop(),
docViewBottom = docViewTop + $window.height(),
elemTop = $elem.offset().top,
elemBottom = elemTop + $elem.height();
if ((docViewTop elemBottom))
StackExchange.using('gps', function() StackExchange.gps.track('embedded_signup_form.view', location: 'question_page' ); );
$window.unbind('scroll', onScroll);
;
$window.on('scroll', onScroll);
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2faskubuntu.com%2fquestions%2f1002176%2fmerge-files-using-a-common-column-awk%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
var $window = $(window),
onScroll = function(e)
var $elem = $('.new-login-left'),
docViewTop = $window.scrollTop(),
docViewBottom = docViewTop + $window.height(),
elemTop = $elem.offset().top,
elemBottom = elemTop + $elem.height();
if ((docViewTop elemBottom))
StackExchange.using('gps', function() StackExchange.gps.track('embedded_signup_form.view', location: 'question_page' ); );
$window.unbind('scroll', onScroll);
;
$window.on('scroll', onScroll);
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
var $window = $(window),
onScroll = function(e)
var $elem = $('.new-login-left'),
docViewTop = $window.scrollTop(),
docViewBottom = docViewTop + $window.height(),
elemTop = $elem.offset().top,
elemBottom = elemTop + $elem.height();
if ((docViewTop elemBottom))
StackExchange.using('gps', function() StackExchange.gps.track('embedded_signup_form.view', location: 'question_page' ); );
$window.unbind('scroll', onScroll);
;
$window.on('scroll', onScroll);
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
var $window = $(window),
onScroll = function(e)
var $elem = $('.new-login-left'),
docViewTop = $window.scrollTop(),
docViewBottom = docViewTop + $window.height(),
elemTop = $elem.offset().top,
elemBottom = elemTop + $elem.height();
if ((docViewTop elemBottom))
StackExchange.using('gps', function() StackExchange.gps.track('embedded_signup_form.view', location: 'question_page' ); );
$window.unbind('scroll', onScroll);
;
$window.on('scroll', onScroll);
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
What information is in
file 1that is not already infile 2? It's not clear from your question.– waltinator
Feb 1 at 20:12