Time between consecutive events

[Melvyn Hillsdon]

I have the following data taken from a thigh worn accelerometer. For each person I want to calculate the time in seconds between the end of one sitting event and the start of the next one. I then want to create a variable that returns '1' for each upright event (the standing and stepping events between the end one sitting event and the start of the next). Thank you.

[Clyde Schechter]

I have the following data

But no data followed. And without example data, at most you will get vague, general, and probably unhelpful, advice. Please post back with example data, and use the -dataex- command to do so.

If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Melvyn Hillsdon]

I did include data in the dataex format as requested so I don't understand why it didn't display. This is my first ever post so I copied and pasted the output from Stata into this window.

[Melvyn Hillsdon]

Trying a screenshot...

[Melvyn Hillsdon]

And trying an attachment of the screenshot.

[Melvyn Hillsdon]

I've now attached the dataex output in a Word document in the hope that will work.

[Nick Cox]

Sorry, but a Word document is deprecated here. #3 #4 #5 all failed but we can't (I can't) tell you what you did wrong.

[Melvyn Hillsdon]

I've tried again to copy and paste the screenshot as a jpeg and also attach it.

[Clyde Schechter]

Well, I don't know why you had such difficulty with the posting. With the -dataex- screenshot, it was possible to at least see the organization of the data and important metadata, though not to import it to test code. So here is some untested code that I think will do what you want. But, as it is untested, it may contain typos or syntax errors. In any case, I hope it will point you in the right direction.

frame put participantid starttime if activitytype == "Sitting", into(sitting_events)
frame sitting_events {
    by participantid (starttime), sort: gen long latency = clockdiff(starttime, ///
        starttime[_n+1], "sec")
}
frlink 1:1 participantid starttime, frame(sitting_events)
frget latency, from(sitting_events)
frame drop sitting_events

gen byte standing_stepping = inlist(typegroup, 2, 3)

Added: I just noticed that you want the difference between the end of one sitting event and the start of the next, whereas the code above gives you the difference between the start of one sitting event and the start of the next. So corrected code would be:

frame put participantid starttime endtime if activitytype == "Sitting", into(sitting_events)
frame sitting_events {
    by participantid (starttime), sort: gen long latency = clockdiff(endtime, ///
        starttime[_n+1], "sec")
}
frlink 1:1 participantid starttime endtime, frame(sitting_events)
frget latency, from(sitting_events)
frame drop sitting_events

gen byte standing_stepping = inlist(typegroup, 2, 3)

[Melvyn Hillsdon]

Sorry for the hassle with the data and thank you very much for the code. It just returned one error:
invalid match variables for 1:1 match
The variables participantid starttime do not uniquely identify the observations in frame default. Perhaps you meant to specify m:1 instead of 1:1.

[Clyde Schechter]

Well, I intended to specify 1:1 because it looked like the variables participantid and starttime (and endtime) uniquely identify observations in frame default. And that is important for the code to work correctly. If you change it to m:1 then the error message will go away, but the results may be wrong. If there is more than one event occupying exactly the same timespan for the same person, what does that mean? Maybe it's OK in the context of your study, but in most situations it would mean data errors. And if it is OK in the context of your study, then identifying the "next" sitting event becomes problematic.

Now, looking a bit more closely at your data and typing in some values, I see that your time variables are not actually Stata clock variables. They are time-only variables with a false date (this looks like something imported from Excel). So probably the combination of participant id and true clock variables would provide unique identification. BUT, the code would still need some corrections because it calculated the difference only from the time part. So if a sitting event ended before midnight and the next one began after midnight, the calculation would go wrong. So I think we need to patch those variables up.

Altogether, then, try this:

foreach x in start end {
    gen double `x'_clock = cofC(Cdhms(date, hh(`x'time), mm(`x'time), ss(`x'time)))
    format `x'_clock %tc
}

isid participantid start_clock end_clock, sort // VERIFY UNIQUE IDENTIFICATION
frame put participantid start_clock end_clock if activitytype == "Sitting", into(sitting_events)
frame sitting_events {
    by participantid (start_clock), sort: gen long latency = clockdiff(end_clock, ///
        start_clock[_n+1], "sec")
}
frlink 1:1 participantid start_clock end_clock, frame(sitting_events)
frget latency, from(sitting_events)
frame drop sitting_events

[Melvyn Hillsdon]

I think I have spotted the problem. There are 64 events (rows) that do have duplicate start and end clocks but the event type is different. I've tried to paste in a screen shot but I won't know if you can see it until I press send. The data is from the British Birth Cohort 1970 so I don't have the raw acceleration data and code that generated the data. There are no duplicate sitting events. I suspect all of the events include a fraction of a second but the outputted data has been rounded.

[Clyde Schechter]

OK. As long as there are no sitting events that begin at the same time for the same person, this is doable. I have revised the code accordingly.

foreach x in start end {
    gen double `x'_clock = cofC(Cdhms(date, hh(`x'time), mm(`x'time), ss(`x'time)))
    format `x'_clock %tc
}

frame put participantid start_clock end_clock if activitytype == "Sitting", into(sitting_events)
frame sitting_events {
    isid participantid start_clock end_clock, sort // VERIFY UNIQUE IDENTIFICATION
    by participantid (start_clock end_clock), sort: gen long latency = clockdiff(end_clock, ///
        start_clock[_n+1], "sec")
}
frlink m:1 participantid start_clock end_clock, frame(sitting_events)
frget latency, from(sitting_events)
replace latency = . if activitytype != "Sitting"
frame drop sitting_events

[Melvyn Hillsdon]

Brilliant, thank you so much.