I need to match the external gene names of a table I have called (which has 1,000,000 entries) with their respective ensemble gene IDs.

Here's a small subset of df:

V1 external_gene_name V3
M0085 A1BG 1
M0085 SNX8 1

and here's a small subset of ensembl_ids:

ensembl_gene_id external_gene_name
ENSG00000288263 GRK1
ENSG00000291428 BRSK2

I also included a line to see the number of entries in external_gene_name which do not have corresponding ensemble names. Here is my code:

merged_df <- merge(df, ensembl_ids, by = "external_gene_name")
elements_not_in <- df$external_gene_name[!(df$external_gene_name %in% ensembl_ids$external_gene_name)]

But what's confusing me is that when I add the number of rows in merged_df with that of elements_not_in, the total exceeds 1,000,000. Shouldn't the total be 1,000,000? I'm wondering if there's a flaw in my code.


1 Answer 1


From a brief glance, the gap is not in your code but in your expectations. You're operating based on an assumption of 1:1 mapping between ENSG IDs and HGNC symbols (your external_gene_name attribute). In reality, multiple ENSG IDs will map to the same HGNC symbol so the merge will produce multiple rows per external_gene_name in the merge product. This is why you're seeing a total greater than the sum of number of unique entries when you compare the merge product + the anti-join result (i.e., the elements_not_in dataset) to the initial individual datasets.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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