Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-04-28T11:11:04.520Z
Answers.LastActivityDate: 2020-04-28T11:11:04.520Z
Answers.UserDisplayName: user3479780
Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-04-28T14:01:44.110Z
Answers.LastActivityDate: 2020-04-28T14:01:44.110Z
Answers.UserDisplayName: Alex Reynolds
Body: <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>
  
Tags: <r>
Title: Intersecting a data frame based on the multiple columns
ThreadText: # 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"))
  
  ```
  
  
  ---
  
  