<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>

CC BY-SA 4.0
2020-03-30T15:10:21.260Z
i will definitely  do that
kcm
CC BY-SA 4.0
2020-03-30T13:56:14.843Z
2020-03-30T13:56:14.843Z
Phoenix Mu
<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>

CC BY-SA 4.0
2020-03-29T19:54:41.033Z
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?
Phoenix Mu
CC BY-SA 4.0
2020-03-30T04:57:06.533Z
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
kcm
CC BY-SA 4.0
2020-03-30T13:05:26.347Z
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
user438383
CC BY-SA 4.0
2020-03-30T15:09:49.660Z
i think i have messed up somewhere i checked its not matching
kcm
<r>
Merging based on Chromosome coordinates
# 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
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
Chr    Start    End  CMP1  CMP2  CMP3  CMP4  CMP5  CMP6  GMP1  GMP2  GMP3  GMP4  GMP5  GMP6  HSC1  HSC2  HSC3  HSC4  HSC5  HSC6
                        
 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  

---

