Extract rows from file_1 based on matching the first two columns from a file

Extract rows from file_1 based on matching the first two columns from a file_2

File_1

##chr pos rc allele_count allele_states deletion_sum snp_type most_variable_allele diff:1-2 diff:1-3 diff:1-4 diff:1-5 diff:1-6 diff:1-7 diff:1-8 diff:1-9 diff:1-10 diff:1-11 diff:1-12 diff:2-3
MT 227 C 2 C/A 0 pop C 0 0 0 0 0 0 0.024 0 0.022 0 0 0
MT 233 G 2 G/T 0 pop G 0 0.009 0 0.012 0 0 0 0 0 0 0 0.009
MT 245 G 2 G/A 0 pop A 0 0 0 0 0 0.055 0.224 0.072 0.026 0 0 0
MT 251 C 2 C/T 0 pop C 0.276 0.034 0.231 0.005 0.027 0.036 0.025 0.002 0.107 0.034 0.034 0.309
MT 264 G 2 G/C 0 pop G 0 0 0 0.008 0 0.003 0 0 0 0 0 0
MT 286 G 2 G/T 0 pop T 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0 0.002 0
MT 292 A 2 A/T 0 pop T 0 0 0 0 0.003 0 0 0.002 0 0 0 0
MT 293 G 2 G/T 0 pop G 0 0 0 0 0.003 0.002 0 0 0 0 0 0
MT 295 G 2 G/T 0 pop G 0 0.002 0.002 0 0.001 0.002 0.002 0.002 0.002 0.002 0.002 0.003

File_2

MT 251
MT 292
MT 295

desired_output

##chr pos rc allele_count allele_states deletion_sum snp_type most_variable_allele diff:1-2 diff:1-3 diff:1-4 diff:1-5 diff:1-6 diff:1-7 diff:1-8 diff:1-9 diff:1-10 diff:1-11 diff:1-12 diff:2-3
MT 251 C 2 C/T 0 pop C 0.276 0.034 0.231 0.005 0.027 0.036 0.025 0.002 0.107 0.034 0.034 0.309
MT 292 A 2 A/T 0 pop T 0 0 0 0 0.003 0 0 0.002 0 0 0 0
MT 295 G 2 G/T 0 pop G 0 0.002 0.002 0 0.001 0.002 0.002 0.002 0.002 0.002 0.002 0.003

It's similar to what was wanted in this post:Compare two files by first column. Keep rows if matching

I was using awk 'NR==FNRa[$0]=$0;nexta[$0]' for keeping overlap with first column, but I need the entire row when they match the first two columns (chr and pos).

asked Dec 28 '18 at 23:37

Age87

1477

add a comment |

File_1

##chr pos rc allele_count allele_states deletion_sum snp_type most_variable_allele diff:1-2 diff:1-3 diff:1-4 diff:1-5 diff:1-6 diff:1-7 diff:1-8 diff:1-9 diff:1-10 diff:1-11 diff:1-12 diff:2-3
MT 227 C 2 C/A 0 pop C 0 0 0 0 0 0 0.024 0 0.022 0 0 0
MT 233 G 2 G/T 0 pop G 0 0.009 0 0.012 0 0 0 0 0 0 0 0.009
MT 245 G 2 G/A 0 pop A 0 0 0 0 0 0.055 0.224 0.072 0.026 0 0 0
MT 251 C 2 C/T 0 pop C 0.276 0.034 0.231 0.005 0.027 0.036 0.025 0.002 0.107 0.034 0.034 0.309
MT 264 G 2 G/C 0 pop G 0 0 0 0.008 0 0.003 0 0 0 0 0 0
MT 286 G 2 G/T 0 pop T 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0 0.002 0
MT 292 A 2 A/T 0 pop T 0 0 0 0 0.003 0 0 0.002 0 0 0 0
MT 293 G 2 G/T 0 pop G 0 0 0 0 0.003 0.002 0 0 0 0 0 0
MT 295 G 2 G/T 0 pop G 0 0.002 0.002 0 0.001 0.002 0.002 0.002 0.002 0.002 0.002 0.003

File_2

MT 251
MT 292
MT 295

desired_output

##chr pos rc allele_count allele_states deletion_sum snp_type most_variable_allele diff:1-2 diff:1-3 diff:1-4 diff:1-5 diff:1-6 diff:1-7 diff:1-8 diff:1-9 diff:1-10 diff:1-11 diff:1-12 diff:2-3
MT 251 C 2 C/T 0 pop C 0.276 0.034 0.231 0.005 0.027 0.036 0.025 0.002 0.107 0.034 0.034 0.309
MT 292 A 2 A/T 0 pop T 0 0 0 0 0.003 0 0 0.002 0 0 0 0
MT 295 G 2 G/T 0 pop G 0 0.002 0.002 0 0.001 0.002 0.002 0.002 0.002 0.002 0.002 0.003

It's similar to what was wanted in this post:Compare two files by first column. Keep rows if matching

I was using awk 'NR==FNRa[$0]=$0;nexta[$0]' for keeping overlap with first column, but I need the entire row when they match the first two columns (chr and pos).

asked Dec 28 '18 at 23:37

Age87

1477

add a comment |

File_1

##chr pos rc allele_count allele_states deletion_sum snp_type most_variable_allele diff:1-2 diff:1-3 diff:1-4 diff:1-5 diff:1-6 diff:1-7 diff:1-8 diff:1-9 diff:1-10 diff:1-11 diff:1-12 diff:2-3
MT 227 C 2 C/A 0 pop C 0 0 0 0 0 0 0.024 0 0.022 0 0 0
MT 233 G 2 G/T 0 pop G 0 0.009 0 0.012 0 0 0 0 0 0 0 0.009
MT 245 G 2 G/A 0 pop A 0 0 0 0 0 0.055 0.224 0.072 0.026 0 0 0
MT 251 C 2 C/T 0 pop C 0.276 0.034 0.231 0.005 0.027 0.036 0.025 0.002 0.107 0.034 0.034 0.309
MT 264 G 2 G/C 0 pop G 0 0 0 0.008 0 0.003 0 0 0 0 0 0
MT 286 G 2 G/T 0 pop T 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0 0.002 0
MT 292 A 2 A/T 0 pop T 0 0 0 0 0.003 0 0 0.002 0 0 0 0
MT 293 G 2 G/T 0 pop G 0 0 0 0 0.003 0.002 0 0 0 0 0 0
MT 295 G 2 G/T 0 pop G 0 0.002 0.002 0 0.001 0.002 0.002 0.002 0.002 0.002 0.002 0.003

File_2

MT 251
MT 292
MT 295

desired_output

##chr pos rc allele_count allele_states deletion_sum snp_type most_variable_allele diff:1-2 diff:1-3 diff:1-4 diff:1-5 diff:1-6 diff:1-7 diff:1-8 diff:1-9 diff:1-10 diff:1-11 diff:1-12 diff:2-3
MT 251 C 2 C/T 0 pop C 0.276 0.034 0.231 0.005 0.027 0.036 0.025 0.002 0.107 0.034 0.034 0.309
MT 292 A 2 A/T 0 pop T 0 0 0 0 0.003 0 0 0.002 0 0 0 0
MT 295 G 2 G/T 0 pop G 0 0.002 0.002 0 0.001 0.002 0.002 0.002 0.002 0.002 0.002 0.003

It's similar to what was wanted in this post:Compare two files by first column. Keep rows if matching

I was using awk 'NR==FNRa[$0]=$0;nexta[$0]' for keeping overlap with first column, but I need the entire row when they match the first two columns (chr and pos).

asked Dec 28 '18 at 23:37

Age87

1477

File_1

##chr pos rc allele_count allele_states deletion_sum snp_type most_variable_allele diff:1-2 diff:1-3 diff:1-4 diff:1-5 diff:1-6 diff:1-7 diff:1-8 diff:1-9 diff:1-10 diff:1-11 diff:1-12 diff:2-3
MT 227 C 2 C/A 0 pop C 0 0 0 0 0 0 0.024 0 0.022 0 0 0
MT 233 G 2 G/T 0 pop G 0 0.009 0 0.012 0 0 0 0 0 0 0 0.009
MT 245 G 2 G/A 0 pop A 0 0 0 0 0 0.055 0.224 0.072 0.026 0 0 0
MT 251 C 2 C/T 0 pop C 0.276 0.034 0.231 0.005 0.027 0.036 0.025 0.002 0.107 0.034 0.034 0.309
MT 264 G 2 G/C 0 pop G 0 0 0 0.008 0 0.003 0 0 0 0 0 0
MT 286 G 2 G/T 0 pop T 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0 0.002 0
MT 292 A 2 A/T 0 pop T 0 0 0 0 0.003 0 0 0.002 0 0 0 0
MT 293 G 2 G/T 0 pop G 0 0 0 0 0.003 0.002 0 0 0 0 0 0
MT 295 G 2 G/T 0 pop G 0 0.002 0.002 0 0.001 0.002 0.002 0.002 0.002 0.002 0.002 0.003

File_2

MT 251
MT 292
MT 295

desired_output

##chr pos rc allele_count allele_states deletion_sum snp_type most_variable_allele diff:1-2 diff:1-3 diff:1-4 diff:1-5 diff:1-6 diff:1-7 diff:1-8 diff:1-9 diff:1-10 diff:1-11 diff:1-12 diff:2-3
MT 251 C 2 C/T 0 pop C 0.276 0.034 0.231 0.005 0.027 0.036 0.025 0.002 0.107 0.034 0.034 0.309
MT 292 A 2 A/T 0 pop T 0 0 0 0 0.003 0 0 0.002 0 0 0 0
MT 295 G 2 G/T 0 pop G 0 0.002 0.002 0 0.001 0.002 0.002 0.002 0.002 0.002 0.002 0.003

It's similar to what was wanted in this post:Compare two files by first column. Keep rows if matching

I was using awk 'NR==FNRa[$0]=$0;nexta[$0]' for keeping overlap with first column, but I need the entire row when they match the first two columns (chr and pos).

awk grep

asked Dec 28 '18 at 23:37

Age87

1477

asked Dec 28 '18 at 23:37

Age87

1477

asked Dec 28 '18 at 23:37

Age87

1477

asked Dec 28 '18 at 23:37

Age87

1477

asked Dec 28 '18 at 23:37

Age87

1477

add a comment |

1 Answer
1

active

oldest

votes

You should just test whether the first two columns are keys of the array, instead of testing the entire line with $0.

awk 'NR==FNR a[$1" "$2] = 1; next
 FNR == 1 && FNR != NR print # print header
 $1" "$2 in a' File_2 File_1

edited Dec 29 '18 at 0:00

answered Dec 28 '18 at 23:44

Barmar

6,9931223

Thanks! the column headers weren't added to the output, but that's trivial to do.
– Age87
Dec 28 '18 at 23:58

add a comment |

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "106"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f491395%2fextract-rows-from-file-1-based-on-matching-the-first-two-columns-from-a-file-2%23new-answer', 'question_page');

);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

You should just test whether the first two columns are keys of the array, instead of testing the entire line with $0.

awk 'NR==FNR a[$1" "$2] = 1; next
 FNR == 1 && FNR != NR print # print header
 $1" "$2 in a' File_2 File_1

edited Dec 29 '18 at 0:00

answered Dec 28 '18 at 23:44

Barmar

6,9931223

Thanks! the column headers weren't added to the output, but that's trivial to do.
– Age87
Dec 28 '18 at 23:58

add a comment |

You should just test whether the first two columns are keys of the array, instead of testing the entire line with $0.

awk 'NR==FNR a[$1" "$2] = 1; next
 FNR == 1 && FNR != NR print # print header
 $1" "$2 in a' File_2 File_1

edited Dec 29 '18 at 0:00

answered Dec 28 '18 at 23:44

Barmar

6,9931223

Thanks! the column headers weren't added to the output, but that's trivial to do.
– Age87
Dec 28 '18 at 23:58

add a comment |

You should just test whether the first two columns are keys of the array, instead of testing the entire line with $0.

awk 'NR==FNR a[$1" "$2] = 1; next
 FNR == 1 && FNR != NR print # print header
 $1" "$2 in a' File_2 File_1

edited Dec 29 '18 at 0:00

answered Dec 28 '18 at 23:44

Barmar

6,9931223

You should just test whether the first two columns are keys of the array, instead of testing the entire line with $0.

awk 'NR==FNR a[$1" "$2] = 1; next
 FNR == 1 && FNR != NR print # print header
 $1" "$2 in a' File_2 File_1

edited Dec 29 '18 at 0:00

answered Dec 28 '18 at 23:44

Barmar

6,9931223

edited Dec 29 '18 at 0:00

answered Dec 28 '18 at 23:44

Barmar

6,9931223

answered Dec 28 '18 at 23:44

Barmar

6,9931223

answered Dec 28 '18 at 23:44

Barmar

6,9931223

Thanks! the column headers weren't added to the output, but that's trivial to do.
– Age87
Dec 28 '18 at 23:58

add a comment |

Thanks! the column headers weren't added to the output, but that's trivial to do.
– Age87
Dec 28 '18 at 23:58

Thanks! the column headers weren't added to the output, but that's trivial to do.
– Age87
Dec 28 '18 at 23:58

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Unix & Linux Stack Exchange!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

搜尋此網誌

mjhjmtu