<p>reformat df2 so that you have <code>chr1</code> is instead of <code>1</code>, then you basically have two bed files.  Then use <code>bedtools intersect</code>.  <a href="https://bedtools.readthedocs.io/en/latest/content/tools/intersect.html" rel="nofollow noreferrer">https://bedtools.readthedocs.io/en/latest/content/tools/intersect.html</a></p>

CC BY-SA 4.0
2020-04-28T11:11:04.520Z
2020-04-28T11:11:04.520Z
user3479780
<p>In R:</p>

<pre><code>&gt; write.table(df1, 'df1.txt', quote=F, sep="\t", row.names=F, col.names=F)
&gt; write.table(df2, 'df2.txt', quote=F, sep="\t", row.names=F, col.names=F)
</code></pre>

<p>Convert both files to sorted BED:</p>

<pre><code>$ sort-bed df1.txt &gt; df1.bed
$ awk -v FS="\t" -v OFS="\t" '{ print "chr"$1 }' df2.txt | sort-bed - &gt; df2.bed
</code></pre>

<p>Then map <code>df1</code> gene names to <code>df2</code> coordinates:</p>

<pre><code>$ bedmap --echo --echo-map-id-uniq --delim '\t' df2.bed df1.bed &gt; answer.bed
</code></pre>

<p>To bring back into R, e.g.:</p>

<pre><code>&gt; dfMap &lt;- read.table('answer.bed', sep='\t', col.names=c('Chromosome', 'Start_Position', 'End_Position', 'Genes'), colClasses=c("factor", "numeric", "numeric", "character"))
</code></pre>

CC BY-SA 4.0
2020-04-28T14:01:44.110Z
2020-04-28T14:01:44.110Z
Alex Reynolds
<p>I have 2 data frames</p>

<pre><code>&gt; head(df1)
   Chromosome Start_Position End_Position       Gene
1:      chr12       46254706     46254706       ARID2
2:      chr17        7579849      7579849        TP53
3:       chr9       21974777     21974780      CDKN2A
4:       chr1       27105930     27105930      ARID1A
5:       chr1      201982973    201982973        ELF3
6:       chr3       89480459     89480459       EPHA3
&gt; head(df2)
  Chromosome Start_Position End_Position
1          1          10583       863511
2          1       12841835     12854479
3          1       17231299     17232877
4          1      120380739    142540462
5          1      142540484    142684781
6          1      142685823    143462598
&gt; 
</code></pre>

<p>How I can intersect genes from df1 which are in the range of df2? Because both data frames have chromosome number, start and end but I don't know how to identify genes from df1 based on df2</p>

<p>I have tried</p>

<pre><code>df1[df1<span class="math-container">$Start_Position%in%df2$</span>Start_Position,]
</code></pre>

<p>But I obtain one gene</p>

<p>I also tried</p>

<pre><code>&gt; new_dataset &lt;- df1 %&gt;% right_join(df2, by=c("Chromosome","Start_Position"))
Error: Can't join on 'Start_Position' x 'Start_Position' because of incompatible types (character / integer)
&gt;
</code></pre>

<r>
Intersecting a data frame based on the multiple columns
# Intersecting a data frame based on the multiple columns

(Asked by: username_0 on 2020-04-18)

I have 2 data frames
> head(df1)
   Chromosome Start_Position End_Position       Gene
1:      chr12       46254706     46254706       ARID2
2:      chr17        7579849      7579849        TP53
3:       chr9       21974777     21974780      CDKN2A
4:       chr1       27105930     27105930      ARID1A
5:       chr1      201982973    201982973        ELF3
6:       chr3       89480459     89480459       EPHA3
> head(df2)
  Chromosome Start_Position End_Position
1          1          10583       863511
2          1       12841835     12854479
3          1       17231299     17232877
4          1      120380739    142540462
5          1      142540484    142684781
6          1      142685823    143462598
> 

How I can intersect genes from df1 which are in the range of df2? Because both data frames have chromosome number, start and end but I don't know how to identify genes from df1 based on df2
I have tried
df1[df1$Start_Position%in%df2$Start_Position,]

But I obtain one gene
I also tried
> new_dataset <- df1 %>% right_join(df2, by=c("Chromosome","Start_Position"))
Error: Can't join on 'Start_Position' x 'Start_Position' because of incompatible types (character / integer)
>

**Tags:** r

---

## 2 answers

### Answer by username_1 on 2020-04-28. Score: 1

reformat df2 so that you have `chr1` is instead of `1`, then you basically have two bed files. Then use `bedtools intersect`. <https://bedtools.readthedocs.io/en/latest/content/tools/intersect.html>


---

### Answer by username_2 on 2020-04-28. Score: 0

In R:
> write.table(df1, 'df1.txt', quote=F, sep="\t", row.names=F, col.names=F)
> write.table(df2, 'df2.txt', quote=F, sep="\t", row.names=F, col.names=F)

Convert both files to sorted BED:
$ sort-bed df1.txt > df1.bed
$ awk -v FS="\t" -v OFS="\t" '{ print "chr"$1 }' df2.txt | sort-bed - > df2.bed

Then map df1 gene names to df2 coordinates:
$ bedmap --echo --echo-map-id-uniq --delim '\t' df2.bed df1.bed > answer.bed

To bring back into R, e.g.:
> dfMap <- read.table('answer.bed', sep='\t', col.names=c('Chromosome', 'Start_Position', 'End_Position', 'Genes'), colClasses=c("factor", "numeric", "numeric", "character"))


---

