# Merging based on Chromosome coordinates

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

• 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? Mar 29, 2020 at 19:54
• 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
Mar 30, 2020 at 4:57
• 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 Mar 30, 2020 at 13:05
• i think i have messed up somewhere i checked its not matching
– kcm
Mar 30, 2020 at 15:09

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.