How can I keep a time interval around an event?

[Andrea Lupi]

Good evening,

I have a .dta file with this variables: Date, Time, event, ....other variables....

The variable event assumes value=1 for the whole duration of the event otherwise assumes value =.
The .dta file could have more than 1 event.

I want to keep the event and a temporal window of 10 minutes before and 10 minutes after the event.

In the picture there is an example of the data, I want to keep the event (when the variable event=1), and the observations 10 minutes before the start of the event (line 17467) and 10 minutes after the end of the event (line 17471) and drop the other observations.

Thanks to all

[Robert Picard]

I would think that this is something that can be done using rangestat (from SSC).

[Nick Cox]

Please see FAQ Advice #12 on screenshots (please don't) and #8 on cross-posting (you should tell us about it).

https://stackoverflow.com/questions/...round-an-event

[Andrea Lupi]

I'm really sorry, I didn't read the FAQ.

This is an example of my Dataset.

input str11 datel str15 timel int a double b int c double time float(hours minutes event)
"23-FEB-2006" "10:14:57.837759"     . 45.04 2 36897837 10 14 .
"23-FEB-2006" "10:14:57.990093"   100     . . 36897990 10 14 .
"23-FEB-2006" "10:14:57.993023"   100     . . 36897993 10 14 .
"23-FEB-2006" "10:14:57.993023"  1800     . . 36897993 10 14 .
"23-FEB-2006" "10:14:58.133639"     . 45.04 1 36898133 10 14 .
"23-FEB-2006" "10:15:01.773054"     . 45.04 1 36901773 10 15 .
"23-FEB-2006" "10:15:01.776960"     . 45.04 1 36901776 10 15 .
"23-FEB-2006" "10:15:02.776896"     . 45.04 3 36902776 10 15 .
"23-FEB-2006" "10:15:07.482650"     . 45.04 5 36907482 10 15 .
"23-FEB-2006" "10:15:07.885944"     . 45.04 3 36907885 10 15 .
"23-FEB-2006" "10:15:09.550877"     . 45.04 7 36909550 10 15 .
"23-FEB-2006" "10:15:22.151906"   100     . . 36922151 10 15 1
"23-FEB-2006" "10:15:22.155812"   100     . . 36922155 10 15 1
"23-FEB-2006" "10:15:22.155812"  1200     . . 36922155 10 15 1
"23-FEB-2006" "10:15:22.155812"   300     . . 36922155 10 15 1
"23-FEB-2006" "10:15:22.155812"   100     . . 36922155 10 15 1
"23-FEB-2006" "10:15:22.642109"   200     . . 36922642 10 15 .
"23-FEB-2006" "10:15:22.832527"   100     . . 36922832 10 15 .
"23-FEB-2006" "10:15:22.990720"     . 45.04 3 36922990 10 15 .
"23-FEB-2006" "10:15:23.311988"     . 45.04 1 36923311 10 15 .
"23-FEB-2006" "10:15:23.319800"     . 45.05 3 36923319 10 15 .
"23-FEB-2006" "10:15:23.331518"     .  45.1 1 36923331 10 15 .
"23-FEB-2006" "10:15:23.335424"     . 45.11 1 36923335 10 15 .
"23-FEB-2006" "10:15:23.335424"     . 45.11 2 36923335 10 15 .
"23-FEB-2006" "10:15:23.336401"     .  45.1 1 36923336 10 15 .
"23-FEB-2006" "10:15:23.336401"     .  45.1 1 36923336 10 15 .
"23-FEB-2006" "10:15:23.336401"     .  45.1 1 36923336 10 15 .
"23-FEB-2006" "10:15:23.336401"     .  45.1 1 36923336 10 15 .
"23-FEB-2006" "10:15:23.336401"     .  45.1 1 36923336 10 15 .

[Robert Picard]

Thanks for the dataex example. Unfortunately, there's virtually no time difference in the sample so all observations would be selected. The first step is to convert the timestamp to Stata clock time. Then, it's just a matter of counting, for each observation, how many events fall within a +/- 10 minute window. In the following example, I use a 1 minute window to show what happens using the data you posted.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str11 datel str15 timel int a double b int c double time float(hours minutes event)
"23-FEB-2006" "10:14:57.837759"    . 45.04 2 36897837 10 14 .
"23-FEB-2006" "10:14:57.990093"  100     . . 36897990 10 14 .
"23-FEB-2006" "10:14:57.993023"  100     . . 36897993 10 14 .
"23-FEB-2006" "10:14:57.993023" 1800     . . 36897993 10 14 .
"23-FEB-2006" "10:14:58.133639"    . 45.04 1 36898133 10 14 .
"23-FEB-2006" "10:15:01.773054"    . 45.04 1 36901773 10 15 .
"23-FEB-2006" "10:15:01.776960"    . 45.04 1 36901776 10 15 .
"23-FEB-2006" "10:15:02.776896"    . 45.04 3 36902776 10 15 .
"23-FEB-2006" "10:15:07.482650"    . 45.04 5 36907482 10 15 .
"23-FEB-2006" "10:15:07.885944"    . 45.04 3 36907885 10 15 .
"23-FEB-2006" "10:15:09.550877"    . 45.04 7 36909550 10 15 .
"23-FEB-2006" "10:15:22.151906"  100     . . 36922151 10 15 1
"23-FEB-2006" "10:15:22.155812"  100     . . 36922155 10 15 1
"23-FEB-2006" "10:15:22.155812" 1200     . . 36922155 10 15 1
"23-FEB-2006" "10:15:22.155812"  300     . . 36922155 10 15 1
"23-FEB-2006" "10:15:22.155812"  100     . . 36922155 10 15 1
"23-FEB-2006" "10:15:22.642109"  200     . . 36922642 10 15 .
"23-FEB-2006" "10:15:22.832527"  100     . . 36922832 10 15 .
"23-FEB-2006" "10:15:22.990720"    . 45.04 3 36922990 10 15 .
"23-FEB-2006" "10:15:23.311988"    . 45.04 1 36923311 10 15 .
"23-FEB-2006" "10:15:23.319800"    . 45.05 3 36923319 10 15 .
"23-FEB-2006" "10:15:23.331518"    .  45.1 1 36923331 10 15 .
"23-FEB-2006" "10:15:23.335424"    . 45.11 1 36923335 10 15 .
"23-FEB-2006" "10:15:23.335424"    . 45.11 2 36923335 10 15 .
"23-FEB-2006" "10:15:23.336401"    .  45.1 1 36923336 10 15 .
"23-FEB-2006" "10:15:23.336401"    .  45.1 1 36923336 10 15 .
"23-FEB-2006" "10:15:23.336401"    .  45.1 1 36923336 10 15 .
"23-FEB-2006" "10:15:23.336401"    .  45.1 1 36923336 10 15 .
"23-FEB-2006" "10:15:23.336401"    .  45.1 1 36923336 10 15 .
end

* convert to Stata clock time (milliseconds since 01jan1960)
gen s = datel + " " + substr(timel,1,12)
gen double eventtime = clock(s, "DMYhms")
format %tcDDmonCCYY_HH:MM:SS.sss eventtime

* count events within 1 second of time stamp for current obs
rangestat (count) event, interval(eventtime -1000 1000)

list eventtime event event_count, sepby(event_count)

and the results:

. list eventtime event event_count, sepby(event_count)

     +-------------------------------------------+
     |              eventtime   event   event_~t |
     |-------------------------------------------|
  1. | 23feb2006 10:14:57.837       .          0 |
  2. | 23feb2006 10:14:57.990       .          0 |
  3. | 23feb2006 10:14:57.993       .          0 |
  4. | 23feb2006 10:14:57.993       .          0 |
  5. | 23feb2006 10:14:58.133       .          0 |
  6. | 23feb2006 10:15:01.773       .          0 |
  7. | 23feb2006 10:15:01.776       .          0 |
  8. | 23feb2006 10:15:02.776       .          0 |
  9. | 23feb2006 10:15:07.482       .          0 |
 10. | 23feb2006 10:15:07.885       .          0 |
 11. | 23feb2006 10:15:09.550       .          0 |
     |-------------------------------------------|
 12. | 23feb2006 10:15:22.151       1          5 |
 13. | 23feb2006 10:15:22.155       1          5 |
 14. | 23feb2006 10:15:22.155       1          5 |
 15. | 23feb2006 10:15:22.155       1          5 |
 16. | 23feb2006 10:15:22.155       1          5 |
 17. | 23feb2006 10:15:22.642       .          5 |
 18. | 23feb2006 10:15:22.832       .          5 |
 19. | 23feb2006 10:15:22.990       .          5 |
     |-------------------------------------------|
 20. | 23feb2006 10:15:23.311       .          0 |
 21. | 23feb2006 10:15:23.319       .          0 |
 22. | 23feb2006 10:15:23.331       .          0 |
 23. | 23feb2006 10:15:23.335       .          0 |
 24. | 23feb2006 10:15:23.335       .          0 |
 25. | 23feb2006 10:15:23.336       .          0 |
 26. | 23feb2006 10:15:23.336       .          0 |
 27. | 23feb2006 10:15:23.336       .          0 |
 28. | 23feb2006 10:15:23.336       .          0 |
 29. | 23feb2006 10:15:23.336       .          0 |
     +-------------------------------------------+

.

You can then drop any observation where the count is 0.

[William Lisowski]

Where Robert wrote in his text "I use a 1 minute window" he meant to write, as his code makes clear, "I use a 1 second window" or "I use a 1000 millisecond window".

I notice the original data already included a variable "time" which consists only of the clock time, and not the date, and if we display the value with a %tc format

. display %tc 36897837
01jan1960 10:14:57

we see that Stata treats the time as a time on January 1 1960, as Robert's code suggests it will. So one lesson is that, although perhaps the time variable has a format assigned that only displays the clock time, that doesn't affect Stata's interpretation of the value. As everywhere in Stata, how a value is displayed does not necessarily reflect how Stata interprets the value.

If every observation were to have a date of January 1, we can see two problems.

• An event close to midnight will not keep the observations in the day on the other side of midnight, because one side will be 23:00 hours on January 1 and the other side will be 0:00 hours on January 1.
• An event will find the observations in every day in the dataset, not just those close to the day and time of the event

Perhaps neither of these is an issue in this particular data, but it is worth pointing out. Robert's code to convert date1 and time1 to a Stata Internal Format clock value was as crucial to the solution as the use of rangestat was.

Before working with dates and times, any Stata user should thoroughly review the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF. After that, the help datetime documentation will usually be enough to point the way. All Stata manuals are included as PDFs in the Stata installation (since version 11) and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.

[Andrea Lupi]

Many Thanks Robert, exactly what I was looking for. Sorry for the bad example of the dataset, there are no observations that fall out a +/- 10 minute window.

Thanks for the helpful clarification, William.