Why does the subset of rows that match a condition added by the rows that don't match a condition not add up to the total number of rows?

Question

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.

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.