<p>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 <code>external_gene_name</code> attribute). In reality, multiple ENSG IDs will map to the same HGNC symbol so the merge will produce multiple rows per <code>external_gene_name</code> 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 <code>elements_not_in</code> dataset) to the initial individual datasets.</p>

CC BY-SA 4.0
2023-09-07T14:23:39.597Z
2023-09-07T14:23:39.597Z
Ram RS
<p>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.</p>
<p>Here's a small subset of <code>df</code>:</p>
<div class="s-table-container">
<table class="s-table">
<thead>
<tr>
<th>V1</th>
<th>external_gene_name</th>
<th>V3</th>
</tr>
</thead>
<tbody>
<tr>
<td>M0085</td>
<td>A1BG</td>
<td>1</td>
</tr>
<tr>
<td>M0085</td>
<td>SNX8</td>
<td>1</td>
</tr>
</tbody>
</table>
</div>
<p>and here's a small subset of <code>ensembl_ids</code>:</p>
<div class="s-table-container">
<table class="s-table">
<thead>
<tr>
<th>ensembl_gene_id</th>
<th>external_gene_name</th>
</tr>
</thead>
<tbody>
<tr>
<td>ENSG00000288263</td>
<td>GRK1</td>
</tr>
<tr>
<td>ENSG00000291428</td>
<td>BRSK2</td>
</tr>
</tbody>
</table>
</div>
<p>I also included a line to see the number of entries in <code>external_gene_name</code> which do not have corresponding ensemble names. Here is my code:</p>
<pre><code>merged_df &lt;- merge(df, ensembl_ids, by = &quot;external_gene_name&quot;)
elements_not_in &lt;- df$external_gene_name[!(df$external_gene_name %in% ensembl_ids$external_gene_name)]
</code></pre>
<p>But what's confusing me is that when I add the number of rows in <code>merged_df</code> with that of <code>elements_not_in</code>, 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.</p>

<r><ensembl>
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?
# 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?

(Asked by: username_0 on 2023-09-07)

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.

**Tags:** r, ensembl

---

## 1 answers

### Answer by username_1 on 2023-09-07. Score: 4

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.


---

