Intersecting a data frame based on the multiple columns

Question

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

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

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