Answers.Body: <p>In your ATAC data frame, you have <code>chr1   16103  16348</code>. But in your merged data frame, you have <code>chr1   16104  16348</code>. Is it possible that you <code>a</code> is 1-based whereas your ATAC data frame is 0-based? This can happen in bioinformatics and can be annoying. This might be why you have a lot of NA in your merged data frame. You should subtract 1 from the start position of your <code>a</code> data frame while keeping the end position unchanged. Then try to merge again.</p>
  
  <p>You should always be aware of your data format to know whether it is 1-based or 0-based.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-30T15:10:21.260Z
Answers.Text: i will definitely  do that
Answers.UserDisplayName: kcm
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-30T13:56:14.843Z
Answers.LastActivityDate: 2020-03-30T13:56:14.843Z
Answers.UserDisplayName: Phoenix Mu
Body: <p>I have a simple task which seems very complicated now 
  Two data-frame I have One contains the Chromosome coordinates of distal enhancer elements and other one rlog values of accessibility which I want to map.</p>
  
  <p>The distal coordinates <a href="https://drive.google.com/file/d/1LWJIZuLv2Uatt24KtmmTRZCBhpjBV-FN/view?usp=sharing" rel="nofollow noreferrer">data</a></p>
  
  <pre><code>dput(head(a))
  structure(list(Chr = c("chr6", "chr11", "chr20", "chr20", "chr10", 
  "chrX"), Start = c(154986726, 70314780, 15895696, 40706992, 29132014, 
  129525939), End = c(154987062, 70315059, 15895987, 40707264, 
  29132266, 129526265)), class = c("tbl_df", "tbl", "data.frame"
  ), row.names = c(NA, -6L))
  </code></pre>
  
  <p>The Rlog_ATAC_location <a href="https://drive.google.com/file/d/1SA4dm-q40F9tnpGHg_RLZt9QagCAOe92/view?usp=sharing" rel="nofollow noreferrer">data</a></p>
  
  <pre><code>Chr    Start    End  CMP1  CMP2  CMP3  CMP4  CMP5  CMP6  GMP1  GMP2  GMP3  GMP4  GMP5  GMP6  HSC1  HSC2  HSC3  HSC4  HSC5  HSC6
    &lt;chr&gt;  &lt;dbl&gt;  &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt;
   chr1   16103  16348 2.70  2.99  2.87  1.88   2.06  1.94 2.63   2.37  2.61  1.81  1.31  1.21 1.89  1.67  1.95  1.09  2.17  0.861
   chr1   96493  96796 0.976 0.325 0.943 0.216  1.33  1.77 1.66   1.33  1.47  2.39  1.76  2.37 0.939 0.198 1.37  0.439 0.868 0.756
   chr1  271114 271432 1.95  2.62  2.04  2.29   2.49  2.86 2.12   2.89  2.30  2.25  3.16  3.50 0.695 1.03  0.491 0.785 1.15  1.00 
   chr1  273026 273333 0.740 1.88  1.48  1.71   2.50  2.88 2.81   2.66  2.89  2.07  3.02  3.61 1.24  1.40  0.984 1.35  0.651 1.53 
   chr1  274265 274599 0.954 1.59  2.06  0.227  2.05  1.19 0.906  1.60  1.62  1.99  2.05  2.47 0.920 0.211 1.06  0.426 1.99  0.529
   chr1  402478 402771 2.98  3.26  3.01  3.07   2.51  2.76 3.29   2.65  2.34  3.03  3.00  2.99 2.54  3.04  2.88  2.96  2.94  2.68 
  </code></pre>
  
  <p>I tried inner join ,merge etc but i can map the chromosome cooridnates but the expression values comes empty</p>
  
  <pre><code>tmp &lt;- merge(a,Rlog_ATAC_location, by=c("Chr","Start" ,"End"), all.x=TRUE)
  
  Chr  Start    End CMP1 CMP2 CMP3 CMP4 CMP5 CMP6 GMP1 GMP2 GMP3 GMP4 GMP5 GMP6 HSC1 HSC2 HSC3 HSC4 HSC5 HSC6 Mono1 Mono2 Mono3
   chr1  16104  16348   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA
   chr1  96494  96796   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA
   chr1 271115 271432   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA
   chr1 273027 273333   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA
   chr1 274266 274599   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA
   chr1 402479 402771   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA
  </code></pre>
  
  <p>Tried left join as well i get same result.</p>
  
  <p>I want to map the coordinates as well as the samples columns which contains the expression</p>
  
  <p>Any suggestion or help would be really appreciated </p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-03-29T19:54:41.033Z
Comments.Text: Do your two data frames have the same coordinations? For instance, does `chr1   16103  16348` exist in another data frame as well? If not, then you cannot use `left_join` or similar functions. If this is the case, maybe a more precise description of your problem is to annotate distal elements with your ATAC location?
Comments.UserDisplayName: Phoenix Mu
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-03-30T04:57:06.533Z
Comments.Text: I have updated my question with the data actul data Im using if i do left join I can map all the coordinates but the values are not coming "
  
  Do your two data frames have the same coordinations?" yes one is subset of others
Comments.UserDisplayName: kcm
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-03-30T13:05:26.347Z
Comments.Text: in the Example you've given us, there is no overlap in Start positions between the two data frames, so the behaviour of something like join is to return NA. You should go back and check with `any(a$Start  %in%  Rlog$Start)` if there is any overlap between the to data frames
Comments.UserDisplayName: user438383
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-03-30T15:09:49.660Z
Comments.Text: i think i have messed up somewhere i checked its not matching
Comments.UserDisplayName: kcm
Tags: <r>
Title: Merging based on Chromosome coordinates
ThreadText: # Merging based on Chromosome coordinates
  
  (Asked by: username_0 on 2020-03-29)
  
  I have a simple task which seems very complicated now
  Two data-frame I have One contains the Chromosome coordinates of distal enhancer elements and other one rlog values of accessibility which I want to map.
  
  The distal coordinates [data](https://drive.google.com/file/d/1LWJIZuLv2Uatt24KtmmTRZCBhpjBV-FN/view?usp=sharing)
  
  ```
  dput(head(a))
  structure(list(Chr = c("chr6", "chr11", "chr20", "chr20", "chr10", 
  "chrX"), Start = c(154986726, 70314780, 15895696, 40706992, 29132014, 
  129525939), End = c(154987062, 70315059, 15895987, 40707264, 
  29132266, 129526265)), class = c("tbl_df", "tbl", "data.frame"
  ), row.names = c(NA, -6L))
  
  ```
  
  The Rlog\_ATAC\_location [data](https://drive.google.com/file/d/1SA4dm-q40F9tnpGHg_RLZt9QagCAOe92/view?usp=sharing)
  
  ```
  Chr    Start    End  CMP1  CMP2  CMP3  CMP4  CMP5  CMP6  GMP1  GMP2  GMP3  GMP4  GMP5  GMP6  HSC1  HSC2  HSC3  HSC4  HSC5  HSC6
    <chr>  <dbl>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
   chr1   16103  16348 2.70  2.99  2.87  1.88   2.06  1.94 2.63   2.37  2.61  1.81  1.31  1.21 1.89  1.67  1.95  1.09  2.17  0.861
   chr1   96493  96796 0.976 0.325 0.943 0.216  1.33  1.77 1.66   1.33  1.47  2.39  1.76  2.37 0.939 0.198 1.37  0.439 0.868 0.756
   chr1  271114 271432 1.95  2.62  2.04  2.29   2.49  2.86 2.12   2.89  2.30  2.25  3.16  3.50 0.695 1.03  0.491 0.785 1.15  1.00 
   chr1  273026 273333 0.740 1.88  1.48  1.71   2.50  2.88 2.81   2.66  2.89  2.07  3.02  3.61 1.24  1.40  0.984 1.35  0.651 1.53 
   chr1  274265 274599 0.954 1.59  2.06  0.227  2.05  1.19 0.906  1.60  1.62  1.99  2.05  2.47 0.920 0.211 1.06  0.426 1.99  0.529
   chr1  402478 402771 2.98  3.26  3.01  3.07   2.51  2.76 3.29   2.65  2.34  3.03  3.00  2.99 2.54  3.04  2.88  2.96  2.94  2.68 
  
  ```
  
  I tried inner join ,merge etc but i can map the chromosome cooridnates but the expression values comes empty
  
  ```
  tmp <- merge(a,Rlog_ATAC_location, by=c("Chr","Start" ,"End"), all.x=TRUE)
  
  Chr  Start    End CMP1 CMP2 CMP3 CMP4 CMP5 CMP6 GMP1 GMP2 GMP3 GMP4 GMP5 GMP6 HSC1 HSC2 HSC3 HSC4 HSC5 HSC6 Mono1 Mono2 Mono3
   chr1  16104  16348   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA
   chr1  96494  96796   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA
   chr1 271115 271432   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA
   chr1 273027 273333   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA
   chr1 274266 274599   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA
   chr1 402479 402771   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA
  
  ```
  
  Tried left join as well i get same result.
  
  I want to map the coordinates as well as the samples columns which contains the expression
  
  Any suggestion or help would be really appreciated
  
  **Tags:** r
  
  > **Comments:**
  > **username_1**: Do your two data frames have the same coordinations? For instance, does `chr1   16103  16348` exist in another data frame as well? If not, then you cannot use `left_join` or similar functions. If this is the case, maybe a more precise description of your problem is to annotate distal elements with your ATAC location?  
  > **username_0**: I have updated my question with the data actul data Im using if i do left join I can map all the coordinates but the values are not coming "  Do your two data frames have the same coordinations?" yes one is subset of others  
  > **username_2**: in the Example you've given us, there is no overlap in Start positions between the two data frames, so the behaviour of something like join is to return NA. You should go back and check with `any(a$Start  %in%  Rlog$Start)` if there is any overlap between the to data frames  
  > **username_0**: i think i have messed up somewhere i checked its not matching  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2020-03-30. Score: 2 | Accepted answer
  
  In your ATAC data frame, you have `chr1 16103 16348`. But in your merged data frame, you have `chr1 16104 16348`. Is it possible that you `a` is 1-based whereas your ATAC data frame is 0-based? This can happen in bioinformatics and can be annoying. This might be why you have a lot of NA in your merged data frame. You should subtract 1 from the start position of your `a` data frame while keeping the end position unchanged. Then try to merge again.
  
  You should always be aware of your data format to know whether it is 1-based or 0-based.
  
  > **Comments:**
  > **username_0**: i will definitely  do that  
  
  ---
  
  