Creating Panel Data with non-integer time variable

[Nicole Moiseyev]

Hi all,

I'm new to Stata so please forgive me if I sound perplexed/am not getting my problem across well. I am trying to edit a .csv file in such a way that I can apply it to other identically formatted files in the future. I have a time variable "Time" that is in seconds, but not integers – it is very precise to fractions of seconds. I also have another variable called "Section", which has values of 1, 2, and 3. I want to split my data up and possibly turn it into panel data by minute (i.e., 1-60 seconds, 60-120 seconds, etc). The problem is that I don't have constant time intervals for my observations. I want to create panel data so that I can add an observation to the end of each minute panel with the exact seconds – for the first panel, I want to add an observation with Time=60 and all other variables identical to the observation above it, and the same for all other minutes.

I guess to sum up what I want to see in my data: an observation at exactly every minute (60 seconds, 120 s, 180, etc) with all other variables matching the observation immediately before that time.

Any help would be much appreciated. Thanks in advance!

Here's what the sheet kind of looks like after I played around with it a little:


Minute Section StartStop Time
1 1 start 39.6
1 1 stop 58.2
1 1 start 59.9
2 1 stop 73.5
2 1 start 74.7
2 1 stop 86.3
2 1 start 88
2 1 stop 95
2 1 start 95.5
2 1 stop 102.7
3 1 start 169.7
3 1 stop 178.8
3 1 start 179.4
4 1 stop 186.9
4 1 start 189.8
4 1 stop 214.3
4 1 start 221.9
5 1 stop 274.9
5 1 start 276
5 1 stop 286.6
5 1 start 286.9
1 stop 300.2
1 2 start .3
1 2 stop 2.2
1 2 start 36.2
1 2 stop 38.5
1 2 start 39.3
1 2 stop 39.7
1 2 start 58.2
1 2 stop 59.8
2 2 start 73.4
2 2 stop 74.6
2 2 start 86.3
2 2 stop 88
2 2 start 95
2 2 stop 95.4
2 2 start 102.7
2 2 stop 105.7
2 2 start 113.6
2 2 stop 114.8
3 2 start 144.2
3 2 stop 145
3 2 start 156.4
3 2 stop 157.7
3 2 start 168.3
3 2 stop 169.7
3 2 start 178.8
3 2 stop 179.3
4 2 start 186.8
4 2 stop 189.8
4 2 start 214.3
4 2 stop 221.9
5 2 start 274.8
5 2 stop 276
5 2 start 286.6
5 2 stop 286.9
1 3 start 2.1
1 3 stop 36.2
1 3 start 38.5
1 3 stop 39.3
2 3 start 105.7
2 3 stop 113.5
2 3 start 114.8
3 3 stop 144.1
3 3 start 145
3 3 stop 156.4
3 3 start 157.7
3 3 stop 168.2

Nicole

[Phil Bromiley]

Welcome to Statalist.

You didn't get a quick answer. That you provide data is great. However, it might be that you're asking a bunch of questions that make it hard to figure out what you're doing. I wondered about tsfill and insob but I'm not sure either do what you want to do.

This is very slow and clumsy, but you could add a bunch of observations at the end, then do a loop over all your original observations and write the values you need into one of the empty observations you've added at the end.

local origN=_N
local ob=`origN' + 1
insobs 100

forvalues i=1/`origN' {
if minute[`i']==minute[`i'-1]+1 {
local ob=`ob' + 1
replace minute=minute[`i'] in `ob'/`ob'
replace time=60 in `ob'/`ob'
}
}

You can add the other variables in the loop. Then sort as you want and delete excess missing observations. But you'll also have trouble with observations like 1 stop 300.2 - I can't tell if this is missing or what is missing. You may need some condition for missing data.

Phil

[Phil Bromiley]

After writing this, I saw Clyde's more elegant answer. His is probably the way to go. Please try to not double post questions.