Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-09-07T14:23:39.597Z
Answers.LastActivityDate: 2023-09-07T14:23:39.597Z
Answers.UserDisplayName: Ram RS
Body: <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>
  
Tags: <r><ensembl>
Title: 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?
ThreadText: # 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.
  
  
  ---
  
  