Looking to optimize binning of observations in one dataframe with logical tests on another dataframe?

user_13982965 0
Viewed 29

I am a self taught coder looking for some input on an algorithm I have for binning observations. I have 2 data frames, df1 and df2, with a set of observations. I want to associate the events in one time series to another time series based off when they occurred. I have tried a couple different approaches and ended up with the below function.

bin_observations <- function(df1, df2) {
  df1$beat.bin <- "NA"
  df1$beat.bin <- as.integer(df1$beat.bin)
  for (h in 1:nrow(df1)) {
    for(g in 1:nrow(df2)) {
      if (is.na(df1$beat.bin[h] == TRUE) & df1$SECONDS[h] <= df2$SECONDS[g]) {
          df1$beat.bin[h] <- df2$beat[g-1]
      }
    }
  }
  return(df1)
}

The key for me to get the function to work as written was introducing a logical test to see if the field I was writing to had been written to yet. Before this I was doing a bunch of checks on the values of each dataframe against each other to find the time bin. That was a mess of conditional logic.

My question is, what would be a better approach to doing something like this? Also if anyone could point me towards some good resources that I can use to improve my code, I would greatly appreciate it.

Here is the data for the example and an example output.

df.1 <- data.frame( SECONDS = c(0.043, 0.169, 0.260, 0.384, 0.448, 0.869, 1.038, 1.560, 1.584, 1.644, 1.780, 2.772, 2.839, 3.080, 3.616))

df.2 <- data.frame( SECONDS = c(0.000, 0.510, 1.018, 1.518, 2.023, 2.531, 3.038, 3.546, 4.051, 4.569, 5.080, 5.595),
                   beat = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))


----------


df.1 <- bin_observations(df1 = df.1, df2 = df.2 )


----------


> df.1

   SECONDS beat.bin
1    0.043        1
2    0.169        1
3    0.260        1
4    0.384        1
5    0.448        1
6    0.869        2
7    1.038        3
8    1.560        4
9    1.584        4
10   1.644        4
11   1.780        4
12   2.772        6
13   2.839        6
14   3.080        7
15   3.616        8

Thank you for any advice or insight.

r optimization
1 Answers

One approach in base R would be to use findInterval

data.frame(df.1,
           bin = findInterval(df.1$SECONDS, df.2$SECONDS))

#output
   SECONDS bin
1    0.043   1
2    0.169   1
3    0.260   1
4    0.384   1
5    0.448   1
6    0.869   2
7    1.038   3
8    1.560   4
9    1.584   4
10   1.644   4
11   1.780   4
12   2.772   6
13   2.839   6
14   3.080   7
15   3.616   8

In the case the beat column is encoded differently:

df.2 <- data.frame(SECONDS = c(0.000,
                               0.510,
                               1.018,
                               1.518,
                               2.023,
                               2.531,
                               3.038,
                               3.546,
                               4.051,
                               4.569,
                               5.080, 
                               5.595),
                    beat = c("one",
                             "two",
                             "three",
                             "four",
                             "five",
                             "six",
                             "seven",
                             "eight",
                             "nine",
                             "ten",
                             "eleven",
                             "twelve"))

data.frame(df.1,
           bin = df.2$beat[findInterval(df.1$SECONDS, df.2$SECONDS)])
#output
   SECONDS   bin
1    0.043   one
2    0.169   one
3    0.260   one
4    0.384   one
5    0.448   one
6    0.869   two
7    1.038 three
8    1.560  four
9    1.584  four
10   1.644  four
11   1.780  four
12   2.772   six
13   2.839   six
14   3.080 seven
15   3.616 eight

data:

dput(df.1)
structure(list(SECONDS = c(0.043, 0.169, 0.26, 0.384, 0.448, 
0.869, 1.038, 1.56, 1.584, 1.644, 1.78, 2.772, 2.839, 3.08, 3.616
), bin = c(1L, 1L, 1L, 1L, 1L, 2L, 3L, 4L, 4L, 4L, 4L, 6L, 6L, 
7L, 8L)), class = "data.frame", row.names = c(NA, -15L))
dput(df.2)
structure(list(SECONDS = c(0, 0.51, 1.018, 1.518, 2.023, 2.531, 
3.038, 3.546, 4.051, 4.569, 5.08, 5.595), beat = c(1, 2, 3, 4, 
5, 6, 7, 8, 9, 10, 11, 12)), class = "data.frame", row.names = c(NA, 
-12L))
user_85730680
Related