Using dplyr lag function to find difference in numerical row values

General
1

Hello! I've included a reproducible example of my dataset. I need to create either a new column or a new dataframe entirely that has the difference in time between start/stop behaviors. For example, in the first two rows, I would like to find the difference in the time values between groom_START and groom_STOP (in this case, that would be 2265-2263 = 2.002). I need to do this for every behavior_start event. I have tried using the lag function like so, but I keep getting a non-numerical error: \

data 1 < - [,1] <- as.numeric(data[,1])

data1 %>% mutate(duration = ifelse(
status == "STOP",
TIME - lag(TIME,1), NA)

dput(newz [1:70, ])
structure(list(TIME = c("2263.280", "2265.282", "2277.031", "2294.033",
"2302.033", "2439.999", "2442.500", "2451.249", "2459.249", "2462.000",
"2465.501", "2516.583", "2521.582", "2525.330", "2534.332", "2600.668",
"2609.916", "2610.917", "2616.167", "2617.668", "2621.417", "2627.917",
"2631.167", "2659.221", "2665.471", "2669.245", "2669.974", "2674.722",
"2682.973", "2684.721", "2685.722", "2734.047", "2825.381", "2863.427",
"2867.428", "2868.426", "2868.926", "2876.427", "2880.178", "2896.924",
"2906.674", "2922.176", "2924.427", "2926.676", "2928.178", "2929.177",
"2932.428", "2933.928", "2936.176", "2936.928", "2944.427", "2950.174",
"2952.427", "2953.677", "2955.427", "2986.299", "2986.300", "2991.301",
"3000.801", "3001.302", "3002.552", "3004.300", "3009.798", "3010.051",
"3019.048", "3021.801", "3128.551", "3208.021", "3212.774", "3216.523"
), behavior = c("groom", "groom", "eat", "eat", "eat", "eat",
"eat", "eat", "eat", "eat", "eat", "eat", "groom", "groom", "eat",
"eat", "groom", "groom", "groom", "groom", "eat", "eat", "eat",
"eat", "groom", "groom", "drink", "drink", "groom", "groom",
"eat", "eat", "eat", "eat", "groom", "groom", "drink", "drink",
"eat", "eat", "eat", "eat", "groom", "groom", "groom", "groom",
"groom", "groom", "groom", "groom", "groom", "groom", "groom",
"groom", "eat", "eat", "groom", "groom", "groom", "groom", "groom",
"groom", "groom", "groom", "eat", "eat", "eat", "eat", "groom",
"groom"), modifier = c("", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", ""), extra = c("",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", ""), status = c("START", "STOP", "START", "STOP",
"START", "STOP", "START", "STOP", "START", "STOP", "START", "STOP",
"START", "STOP", "START", "STOP", "START", "STOP", "START", "STOP",
"START", "STOP", "START", "STOP", "START", "STOP", "START", "STOP",
"START", "STOP", "START", "STOP", "START", "STOP", "START", "STOP",
"START", "STOP", "START", "STOP", "START", "STOP", "START", "STOP",
"START", "STOP", "START", "STOP", "START", "STOP", "START", "STOP",
"START", "STOP", "START", "STOP", "START", "STOP", "START", "STOP",
"START", "STOP", "START", "STOP", "START", "STOP", "START", "STOP",
"START", "STOP"), mouse = c("B1238", "B1238", "B1238", "B1238",
"B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
"B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
"B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
"B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
"B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
"B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
"B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
"B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
"B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
"B1238", "B1238", "B1238"), condition = c("ABA", "ABA", "ABA",
"ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA",
"ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA",
"ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA",
"ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA",
"ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA",
"ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA",
"ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA",
"ABA", "ABA", "ABA", "ABA"), day = c("Day1", "Day1", "Day1",
"Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1",
"Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1",
"Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1",
"Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1",
"Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1",
"Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1",
"Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1",
"Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1",
"Day1", "Day1", "Day1"), trial = c("Trial1", "Trial1", "Trial1",
"Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
"Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
"Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
"Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
"Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
"Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
"Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
"Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
"Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
"Trial1", "Trial1", "Trial1", "Trial1"), trial2 = c("Trial3",
"Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
"Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
"Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
"Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
"Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
"Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
"Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
"Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
"Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
"Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3"),
real_beh = c("groom _ START", "groom _ STOP", "eat _ START",
"eat _ STOP", "eat _ START", "eat _ STOP", "eat _ START",
"eat _ STOP", "eat _ START", "eat _ STOP", "eat _ START",
"eat _ STOP", "groom _ START", "groom _ STOP", "eat _ START",
"eat _ STOP", "groom _ START", "groom _ STOP", "groom _ START",
"groom _ STOP", "eat _ START", "eat _ STOP", "eat _ START",
"eat _ STOP", "groom _ START", "groom _ STOP", "drink _ START",
"drink _ STOP", "groom _ START", "groom _ STOP", "eat _ START",
"eat _ STOP", "eat _ START", "eat _ STOP", "groom _ START",
"groom _ STOP", "drink _ START", "drink _ STOP", "eat _ START",
"eat _ STOP", "eat _ START", "eat _ STOP", "groom _ START",
"groom _ STOP", "groom _ START", "groom _ STOP", "groom _ START",
"groom _ STOP", "groom _ START", "groom _ STOP", "groom _ START",
"groom _ STOP", "groom _ START", "groom _ STOP", "eat _ START",
"eat _ STOP", "groom _ START", "groom _ STOP", "groom _ START",
"groom _ STOP", "groom _ START", "groom _ STOP", "groom _ START",
"groom _ STOP", "eat _ START", "eat _ STOP", "eat _ START",
"eat _ STOP", "groom _ START", "groom _ STOP")), row.names = c(NA,
70L), class = "data.frame")

2 
suppressPackageStartupMessages({
  library(dplyr)
})

DF <- data.frame(
  TIME = c(
    "2263.280", "2265.282", "2277.031", "2294.033",
    "2302.033", "2439.999", "2442.500", "2451.249", "2459.249", "2462.000",
    "2465.501", "2516.583", "2521.582", "2525.330", "2534.332", "2600.668",
    "2609.916", "2610.917", "2616.167", "2617.668", "2621.417", "2627.917",
    "2631.167", "2659.221", "2665.471", "2669.245", "2669.974", "2674.722",
    "2682.973", "2684.721", "2685.722", "2734.047", "2825.381", "2863.427",
    "2867.428", "2868.426", "2868.926", "2876.427", "2880.178", "2896.924",
    "2906.674", "2922.176", "2924.427", "2926.676", "2928.178", "2929.177",
    "2932.428", "2933.928", "2936.176", "2936.928", "2944.427", "2950.174",
    "2952.427", "2953.677", "2955.427", "2986.299", "2986.300", "2991.301",
    "3000.801", "3001.302", "3002.552", "3004.300", "3009.798", "3010.051",
    "3019.048", "3021.801", "3128.551", "3208.021", "3212.774", "3216.523"
  ), 
  behavior = c(
    "groom", "groom", "eat", "eat", "eat", "eat",
    "eat", "eat", "eat", "eat", "eat", "eat", "groom", "groom", "eat",
    "eat", "groom", "groom", "groom", "groom", "eat", "eat", "eat",
    "eat", "groom", "groom", "drink", "drink", "groom", "groom",
    "eat", "eat", "eat", "eat", "groom", "groom", "drink", "drink",
    "eat", "eat", "eat", "eat", "groom", "groom", "groom", "groom",
    "groom", "groom", "groom", "groom", "groom", "groom", "groom",
    "groom", "eat", "eat", "groom", "groom", "groom", "groom", "groom",
    "groom", "groom", "groom", "eat", "eat", "eat", "eat", "groom",
    "groom"
  ), 
  modifier = c(
    "", "", "", "", "", "", "", "", "", "",
    "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
    "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
    "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
    "", "", "", "", "", "", "", "", "", "", "", ""
  ), 
  extra = c(
    "",
    "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
    "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
    "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
    "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
    "", "", "", "", ""
  ), 
  status = c(
    "START", "STOP", "START", "STOP",
    "START", "STOP", "START", "STOP", "START", "STOP", "START", "STOP",
    "START", "STOP", "START", "STOP", "START", "STOP", "START", "STOP",
    "START", "STOP", "START", "STOP", "START", "STOP", "START", "STOP",
    "START", "STOP", "START", "STOP", "START", "STOP", "START", "STOP",
    "START", "STOP", "START", "STOP", "START", "STOP", "START", "STOP",
    "START", "STOP", "START", "STOP", "START", "STOP", "START", "STOP",
    "START", "STOP", "START", "STOP", "START", "STOP", "START", "STOP",
    "START", "STOP", "START", "STOP", "START", "STOP", "START", "STOP",
    "START", "STOP"
  ), 
  mouse = c(
    "B1238", "B1238", "B1238", "B1238",
    "B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
    "B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
    "B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
    "B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
    "B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
    "B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
    "B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
    "B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
    "B1238", "B1238", "B1238", "B1238", "B1238", "B1238", "B1238",
    "B1238", "B1238", "B1238"
  ), 
  condition = c(
    "ABA", "ABA", "ABA",
    "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA",
    "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA",
    "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA",
    "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA",
    "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA",
    "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA",
    "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA", "ABA",
    "ABA", "ABA", "ABA", "ABA"
  ), 
  day = c(
    "Day1", "Day1", "Day1",
    "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1",
    "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1",
    "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1",
    "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1",
    "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1",
    "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1",
    "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1",
    "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1", "Day1",
    "Day1", "Day1", "Day1"
  ), 
  trial = c(
    "Trial1", "Trial1", "Trial1",
    "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
    "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
    "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
    "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
    "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
    "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
    "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
    "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
    "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1", "Trial1",
    "Trial1", "Trial1", "Trial1", "Trial1"
  ), 
  trial2 = c(
    "Trial3",
    "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
    "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
    "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
    "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
    "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
    "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
    "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
    "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
    "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3",
    "Trial3", "Trial3", "Trial3", "Trial3", "Trial3", "Trial3"
  ),
  real_beh = c(
    "groom _ START", "groom _ STOP", "eat _ START",
    "eat _ STOP", "eat _ START", "eat _ STOP", "eat _ START",
    "eat _ STOP", "eat _ START", "eat _ STOP", "eat _ START",
    "eat _ STOP", "groom _ START", "groom _ STOP", "eat _ START",
    "eat _ STOP", "groom _ START", "groom _ STOP", "groom _ START",
    "groom _ STOP", "eat _ START", "eat _ STOP", "eat _ START",
    "eat _ STOP", "groom _ START", "groom _ STOP", "drink _ START",
    "drink _ STOP", "groom _ START", "groom _ STOP", "eat _ START",
    "eat _ STOP", "eat _ START", "eat _ STOP", "groom _ START",
    "groom _ STOP", "drink _ START", "drink _ STOP", "eat _ START",
    "eat _ STOP", "eat _ START", "eat _ STOP", "groom _ START",
    "groom _ STOP", "groom _ START", "groom _ STOP", "groom _ START",
    "groom _ STOP", "groom _ START", "groom _ STOP", "groom _ START",
    "groom _ STOP", "groom _ START", "groom _ STOP", "eat _ START",
    "eat _ STOP", "groom _ START", "groom _ STOP", "groom _ START",
    "groom _ STOP", "groom _ START", "groom _ STOP", "groom _ START",
    "groom _ STOP", "eat _ START", "eat _ STOP", "eat _ START",
    "eat _ STOP", "groom _ START", "groom _ STOP"
  ))

DF <- DF[,c(1,5)]
DF[,1] <- as.numeric(DF[,1])

DF %>% mutate(duration = ifelse(
  status == "STOP",
  TIME - lag(TIME,1),
  NA))
#>        TIME status duration
#> 1  2263.280  START       NA
#> 2  2265.282   STOP    2.002
#> 3  2277.031  START       NA
#> 4  2294.033   STOP   17.002
#> 5  2302.033  START       NA
#> 6  2439.999   STOP  137.966
#> 7  2442.500  START       NA
#> 8  2451.249   STOP    8.749
#> 9  2459.249  START       NA
#> 10 2462.000   STOP    2.751
#> 11 2465.501  START       NA
#> 12 2516.583   STOP   51.082
#> 13 2521.582  START       NA
#> 14 2525.330   STOP    3.748
#> 15 2534.332  START       NA
#> 16 2600.668   STOP   66.336
#> 17 2609.916  START       NA
#> 18 2610.917   STOP    1.001
#> 19 2616.167  START       NA
#> 20 2617.668   STOP    1.501
#> 21 2621.417  START       NA
#> 22 2627.917   STOP    6.500
#> 23 2631.167  START       NA
#> 24 2659.221   STOP   28.054
#> 25 2665.471  START       NA
#> 26 2669.245   STOP    3.774
#> 27 2669.974  START       NA
#> 28 2674.722   STOP    4.748
#> 29 2682.973  START       NA
#> 30 2684.721   STOP    1.748
#> 31 2685.722  START       NA
#> 32 2734.047   STOP   48.325
#> 33 2825.381  START       NA
#> 34 2863.427   STOP   38.046
#> 35 2867.428  START       NA
#> 36 2868.426   STOP    0.998
#> 37 2868.926  START       NA
#> 38 2876.427   STOP    7.501
#> 39 2880.178  START       NA
#> 40 2896.924   STOP   16.746
#> 41 2906.674  START       NA
#> 42 2922.176   STOP   15.502
#> 43 2924.427  START       NA
#> 44 2926.676   STOP    2.249
#> 45 2928.178  START       NA
#> 46 2929.177   STOP    0.999
#> 47 2932.428  START       NA
#> 48 2933.928   STOP    1.500
#> 49 2936.176  START       NA
#> 50 2936.928   STOP    0.752
#> 51 2944.427  START       NA
#> 52 2950.174   STOP    5.747
#> 53 2952.427  START       NA
#> 54 2953.677   STOP    1.250
#> 55 2955.427  START       NA
#> 56 2986.299   STOP   30.872
#> 57 2986.300  START       NA
#> 58 2991.301   STOP    5.001
#> 59 3000.801  START       NA
#> 60 3001.302   STOP    0.501
#> 61 3002.552  START       NA
#> 62 3004.300   STOP    1.748
#> 63 3009.798  START       NA
#> 64 3010.051   STOP    0.253
#> 65 3019.048  START       NA
#> 66 3021.801   STOP    2.753
#> 67 3128.551  START       NA
#> 68 3208.021   STOP   79.470
#> 69 3212.774  START       NA
#> 70 3216.523   STOP    3.749
1 Like
3

Hey! Thank you for your help and for your help in the past! If I wanted to use this function for the 11th column instead of the 5th column, I'm assuming I would just replace the 5 with 11 in the first line? The reason I ask is because I would ultimately like to get the average of the time difference for the specific types of events (aka, groom_start, eat_start) and make a graph out of that.

I have revised it to this, however, the duration column is purely NA's
newz <- newz[,c(1,11)]
newz[,1] <- as.numeric(newz[,1])

newtime <- newz %>% mutate(duration = ifelse(
real_beh == "_ STOP",
TIME - lag(TIME,1),
NA))

4

Hi

I simplified to make what was happening in lag clearer.

For real_beh

status == "STOP"

needs to be adjusted to

stringr::str_extra(real_beh,"Start")

or

DF %>% select(TIME,real_beh) %>% [mutate part]
5

Thank you for your help -- Despite my adjustments and your suggestions, the duration column still shows only NA's for some reason, but seemed to work just fine in the original code when using the 5th (status) column

6

My bad

DF %>% mutate(duration = ifelse(
  stringr::str_detect(real_beh,"STOP"),
  TIME - lag(TIME,1),
  NA))
1 Like
8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.