Merge 2 data frames if value is in range

Question

I struggled with this for some time and couldn't find a way to do this, so I would be incredibly grateful if you could help! I am new to programming and my code is probably inefficient, but that was the best I could come up with.

Basically, I have 2 CSV files (fixes.csv and .csv zones) that contain different variables and have different numbers of rows and columns. The first fixes.csv file contains eye movement data recorded during the experiment and looks something like this:

Order Participant Sentence Fixation StartPosition
1       1          1         1       -6.89
2       1          1         2       -5.88
3       1          1         3       -5.33
4       1          1         4       -4.09
5       1          1         5       -5.36      

It contains eye movement records made while reading a sentence. It happens that each of the 20 participants reads a set of 40 sentences of 12 words, making several fixations on different words in each sentence and sometimes returning to previously read words. The StartPosition column contains the position on the screen (in degrees of the visual angle) where the fixation started. Values ​​are usually between -8deg and 8deg.

The second zone.csv file contains offer information. Each of the 40 sentences contains 12 words, and each word forms one zone of interest. zone.csv looks something like this:

Sentence     Zone  ZoneStart   ZoneEnd
  1           1     -8.86      -7.49
  1           2     -7.49      -5.89
  1           3     -5.88      -4.51
  1           4     -4.51      -2.90

ZoneStart and ZoneEnd indicate the start and end coordinates of each zone on the screen (in degrees of visual angle). Since the words in each sentence are different, each zone has a width.

, , - , zone.csv fixations.csv. , Sentence 1 1, 1, :

Order Participant Sentence Fixation StartPosition Zone
1       1          1        1        -6.89          2
2       1          1        2        -5.88          2
3       1          1        3        -5.33          3
4       1          1        4        -4.09          3
5       1          1        5        -5.36          3   

, , - .

zones = read.csv(file.choose(), header = TRUE, sep = ",")
fixes = read.csv(file.choose(), header = TRUE, sep = ",")

fixes$SentNo = as.factor(fixes$SentNo)
zones$Sentence = as.factor(zones$Sentence)
zones$Zone = as.factor(zones$Zone)

nfix = nrow(fixes) ## number of fixations in file fixes.csv
nsent = nlevels(fixes$Sentence) ## number of sentences in data file fixes.csv
nzs = nlevels(zones1$Zone) ## number of zones per sentence from file zones.csv
nsz = nlevels(zones$Sentence) ## number of sentences in data file zones.csv

fixes$Zone = 0

for (i in c(1:nfix)){
  for (j in c(1:nzs)){
    for (k in c(1:nsent){
      for (l in c(1:nsz)){ 
        while(fixes$Sentence[k] == zones$Sentence[l]){
          ifelse(fixes$StartPosition[i] > zones$ZoneStart[j]  
          & fixes$StratPosition[i] < zones1$ZoneEnd[j], 
          fixes$Zone[i] -> zones1$Zone[j], 0)
        return(fixes$Zone)
}
}
}
}

, . .csv , ? Sentence , , , .

!

!

Answer 1

Bioconductor IRanges, , .

IRanges :

zone.ranges <- with(zones, IRanges(ZoneStart, ZoneEnd))

:

zone.ind <- findOverlaps(fixes$StartPosition, zone.ranges, select="arbitrary")

zones, :

fixes$Zone <- zones$Zone[zone.ind]

: , IRanges - . 100, .

Answer 2

sqldf:

library(sqldf)

#dummy data
fixes <- read.table(text="
Order Participant Sentence Fixation StartPosition
1       1          1         1       -6.89
2       1          1         2       -5.88
3       1          1         3       -5.33
4       1          1         4       -4.09
5       1          1         5       -5.36 
",header=TRUE)
zones <- read.table(text="
Sentence     Zone  ZoneStart   ZoneEnd
1           1     -8.86      -7.49
1           2     -7.49      -5.89
1           3     -5.88      -4.51
1           4     -4.51      -2.90
",header=TRUE)

#output merged result
res <- 
  sqldf("SELECT [Order],Participant,f.Sentence,Fixation,StartPosition,Zone
       FROM fixes f,zones z
       WHERE f.Sentence=z.Sentence AND
             f.StartPosition>=z.ZoneStart AND
             f.StartPosition<z.ZoneEnd")

Answer 3

I think the best approach is to change zonesto a friendlier format for what you do:

ZoneLookUp = lapply(split(zones, zones$Sentence), function(x) c(x$ZoneStart, x$ZoneEnd[nrow(x)]))

#$`1`
#[1] -8.86 -7.49 -5.88 -4.51 -2.90

Then you can easily view each zone:

fixes$Zone = NULL
for(i in 1:nrow(fixes))
    fixes$Zone[i] = cut(fixes$StartPosition[i], ZoneLookUp[[fixes$Sentence[i]]], labels=FALSE)

If performance is a problem, you can use the (only) slightly less simple approach, using byor data.tablewith.

Answer 4

With version v1.9.8 (according to CRAN on November 25, 2016), I data.tablewas able to perform joins without binding and combining ranges:

library(data.table)
setDT(fixes)[setDT(zones), 
             on = .(Sentence, StartPosition >= ZoneStart, StartPosition < ZoneEnd), 
             Zone := Zone][]

   Order Participant Sentence Fixation StartPosition Zone
1:     1           1        1        1         -6.89    2
2:     2           1        1        2         -5.88    3
3:     3           1        1        3         -5.33    3
4:     4           1        1        4         -4.09    4
5:     5           1        1        5         -5.36    3

Data

fixes <- readr::read_table(
  "Order Participant Sentence Fixation StartPosition
  1       1          1         1       -6.89
  2       1          1         2       -5.88
  3       1          1         3       -5.33
  4       1          1         4       -4.09
  5       1          1         5       -5.36"
)
zones <- readr::read_table(
  "Sentence     Zone  ZoneStart   ZoneEnd
  1           1     -8.86      -7.49
  1           2     -7.49      -5.89
  1           3     -5.88      -4.51
  1           4     -4.51      -2.90"
)