Time Series set when gap in panel data

[Leslie Edwards]

I am unsure of the best time variable to use in a time series declaration (tsset) for panel data of personal air pollution exposure monitoring with 64 monitoring sessions that should have been 48 hours in duration, and 21 of the sessions ended early when the pollution monitor ran out of batteries. I would still like to reflect the 48 hour period in my tsset for each panel because I will examine ambient air pollution during the time that the personal monitoring was missing and have added faux endpoint observations for the 21 sessions that ended early. The data has been collapsed by 1 minute and some monitoring sessions including data reported at 30 second frequency while other monitoring sessions reported a pollution level at 2 minute frequency. Thank you in advance for your thoughts on this!

I tried creating a unique value for minutes time ("minute") but it does not reflect the gap in time.

gen minute=_n

Also created variable obs_tot by ID_Sess to capture the number of observations in each monitoring session.

bysort ID_Sess (seq): gen s_seq = _n
bysort ID_Sess: generate int obs_tot = _N

Adding a chunk of my data and note the gap in time between the second to last and the last observation listed

* Example generated by -dataex-. For more info, type help dataex
clear
input float(ID_Sess year doy hr min minute s_seq) int obs_tot
1 2019 278 18  3  431 431 441
1 2019 278 18  5  432 432 441
1 2019 278 18  7  433 433 441
1 2019 278 18  9  434 434 441
1 2019 278 18 11  435 435 441
1 2019 278 18 13  436 436 441
1 2019 278 18 15  437 437 441
1 2019 278 18 17  438 438 441
1 2019 278 18 19  439 439 441
1 2019 278 18 21  440 440 441
1 2019 279 17  2 1440 441 441
end

[Andrew Musau]

Specify the appropriate gap, e.g., -delta(30000)- for 30 seconds, -delta(60000)- for a minute, and so on.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(ID_Sess year doy hr min minute s_seq) int obs_tot
1 2019 278 18  3  431 431 441
1 2019 278 18  5  432 432 441
1 2019 278 18  7  433 433 441
1 2019 278 18  9  434 434 441
1 2019 278 18 11  435 435 441
1 2019 278 18 13  436 436 441
1 2019 278 18 15  437 437 441
1 2019 278 18 17  438 438 441
1 2019 278 18 19  439 439 441
1 2019 278 18 21  440 440 441
1 2019 279 17  2 1440 441 441
end

gen date = td(1jan2019)+ doy -1
format date %td
gen double time= mdyhms(month(date), day(date), year, hr, min, 0)
format time %tc
tsset time, delta(120000)

Res.:

. tsset time, delta(120000)
        time variable:  time, 05oct2019 18:03:00 to 06oct2019 17:02:00
                        but with a gap
                delta:  2 minutes


. l time, sep(0)

     +--------------------+
     |               time |
     |--------------------|
  1. | 05oct2019 18:03:00 |
  2. | 05oct2019 18:05:00 |
  3. | 05oct2019 18:07:00 |
  4. | 05oct2019 18:09:00 |
  5. | 05oct2019 18:11:00 |
  6. | 05oct2019 18:13:00 |
  7. | 05oct2019 18:15:00 |
  8. | 05oct2019 18:17:00 |
  9. | 05oct2019 18:19:00 |
 10. | 05oct2019 18:21:00 |
 11. | 06oct2019 17:02:00 |
     +--------------------+

.

[Leslie Edwards]

Andrew - thank you for your advice. But I have a question about the delta value (values?) for tsset - I have different delta levels in different monitoring sessions (e.g. 2 minutes in session 1 included in the original example text, 30 seconds in sessions 2 included below). I think we are only allowed to have 1 delta level in tsset. Thanks again to Andrew or others who have advice on this.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(ID_Sess year doy hr min minute s_seq)
2 2020  56  6 14 1701 1260
2 2020  56  6 15 1702 1261
2 2020  56  6 16 1703 1262
2 2020  56  6 17 1704 1263
2 2020  56  6 18 1705 1264
2 2020  56  6 19 1706 1265
2 2020  56  6 20 1707 1266
2 2020  56  6 21 1708 1267
2 2020  56  6 22 1709 1268
2 2020  57  9 15 1710 1269
3 2019 319 17 15 1711    1
end

[Andrew Musau]

What do you want to do with these data?

I have different delta levels in different monitoring sessions (e.g. 2 minutes in session 1 included in the original example text, 30 seconds in sessions 2 included below).

It's the same as saying I have monthly, quarterly and annual data. Is there something that explains why some observations were collected in 30-second intervals and some in 2-minute intervals?

[Andrew Musau]

On my question:

Is there something that explains why some observations were collected in 30-second intervals and some in 2-minute intervals?

I see that you explained that it is the session in #3. You can create a consecutive time variable for tsset reflecting the order of readings and have a separate time variable capturing the time since initial recording in the session.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(ID_Sess year doy hr min minute s_seq) int obs_tot
1 2019 278 18  3  431 431 441
1 2019 278 18  5  432 432 441
1 2019 278 18  7  433 433 441
1 2019 278 18  9  434 434 441
1 2019 278 18 11  435 435 441
1 2019 278 18 13  436 436 441
1 2019 278 18 15  437 437 441
1 2019 278 18 17  438 438 441
1 2019 278 18 19  439 439 441
1 2019 278 18 21  440 440 441
1 2019 279 17  2 1440 441 441
end

gen date = td(1jan2019)+ doy -1
format date %td
gen double time= mdyhms(month(date), day(date), year, hr, min, 0)
format time %tc
sort ID_Sess time
gen t= _n
bys ID_Sess (time): gen elapsedtime= (time-time[1])/60000
tsset t
list, sepby(ID_Sess)

Res.:

. list ID_Sess ID_Sess time t elapsedtime , sepby(ID_Sess)

     +--------------------------------------------------------+
     | ID_Sess   ID_Sess                 time    t   elapse~e |
     |--------------------------------------------------------|
  1. |       1         1   05oct2019 18:03:00    1          0 |
  2. |       1         1   05oct2019 18:05:00    2          2 |
  3. |       1         1   05oct2019 18:07:00    3          4 |
  4. |       1         1   05oct2019 18:09:00    4          6 |
  5. |       1         1   05oct2019 18:11:00    5          8 |
  6. |       1         1   05oct2019 18:13:00    6         10 |
  7. |       1         1   05oct2019 18:15:00    7         12 |
  8. |       1         1   05oct2019 18:17:00    8         14 |
  9. |       1         1   05oct2019 18:19:00    9         16 |
 10. |       1         1   05oct2019 18:21:00   10         18 |
 11. |       1         1   06oct2019 17:02:00   11       1379 |
     +--------------------------------------------------------+

"elapsed time" is in minutes. You can thus enter "elapsed time" as an independent variable in the model to investigate any elapsed time effects and session indicators to identify whether the sessions differ.

[Leslie Edwards]

Thanks for the advice. I need to fill in the gaps in the panel data and will merge ambient data with this file so that I have the corresponding Fixed site PM2.5 data when the machine was not working. I used the standard tsfill but did not see any added blanks or observation rows in the data. Any advice on this would be appreciated.

tsfill

[Leslie Edwards]

Also, I should add that my goal with this analysis is to build a table that includes descriptive statistics for personal air pollution monitoring by location (home, car, workplace, etc) and the location variable (variable name "timeactivity") and corresponding outdoor pollution recorded at the same time ("FixedPM") was not shown in the data chunk above. The table will also include descriptive stats for the outbreak pollution during the periods of time where personal monitoring was not available. I am including my code to create the table, although I still need to add the elements for the periods of missing time, when only the outdoor FixedPM will be included.

collapse (mean) PM25  FixedPM  (sum) obs_min, by(IDnum pre_post timeactivity Mit_Cat) 
gen obs_hrs  = obs_min/60
gen pm25_min = PM25 * obs_min
gen pm25_hrs = PM25 * obs_hrs

*Need to update the code below to include stats for FixedPM for the missing data periods and duration of monitoring (# hours) when data was missing
version 16: table pre_post timeactivity, c(count ID mean PM25 mean FixedPM sum obs_hrs sum pm25_hrs) col format(%6.2f)

Thanks in advance for any thoughts on this.

[Andrew Musau]

Thanks for the advice. I need to fill in the gaps in the panel data and will merge ambient data with this file so that I have the corresponding Fixed site PM2.5 data when the machine was not working. I used the standard tsfill but did not see any added blanks or observation rows in the data. Any advice on this would be appreciated.

tsfill

With different data frequencies, you probably need to create the additional observations separately and then append. Here is an example assuming session 1 is in 2-minute intervals, session 2 is in 30-second intervals and session 3 is in 1-minute intervals. For #4, I am not very clear with what is required, perhaps give an example after adding the extra data and include all relevant variables, explaining what is required.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(ID_Sess year doy hr min)
1 2019 278 18   3
1 2019 278 18   5
1 2019 278 18   7
1 2019 278 18   9
1 2019 278 18  21
2 2019 279 18   3
2 2019 279 18 3.5
2 2019 279 18   4
2 2019 279 18 4.5
2 2019 279 18   5
2 2019 279 18  11
3 2019 281 18   3
3 2019 281 18   5
3 2019 281 18   6
3 2019 281 18   7
3 2019 281 18   8
3 2019 281 17  15
end

gen date = td(1jan2019)+ doy -1
format date %td
gen double time= mdyhms(month(date), day(date), year, hr, int(min), (min-int(min))*60)
format date %tc

tempfile holding
forval i=2/3{
    frame put * if ID_Sess==`i', into(session`i')
}
keep if ID_Sess==1
tsset time, delta(120000)
tsfill
frame session2{
    tsset time, delta(30000)
    tsfill
    save `holding', replace
}
append using `holding'
frame session3{
    tsset time, delta(60000)
    tsfill
    save `holding', replace
}
append using `holding'
replace ID_Sess= ID_Sess[_n-1] if missing(ID_Sess) & !missing(ID_Sess[_n-1])
forval i=2/3{
    frame drop session`i'
}
format time %tc

Res.:

. l, sepby(ID_Sess)

     +---------------------------------------------------------------------------+
     | ID_Sess   year   doy   hr   min                 date                 time |
     |---------------------------------------------------------------------------|
  1. |       1   2019   278   18     3   01jan1960 00:00:21   05oct2019 18:03:00 |
  2. |       1   2019   278   18     5   01jan1960 00:00:21   05oct2019 18:05:00 |
  3. |       1   2019   278   18     7   01jan1960 00:00:21   05oct2019 18:07:00 |
  4. |       1   2019   278   18     9   01jan1960 00:00:21   05oct2019 18:09:00 |
  5. |       1      .     .    .     .                    .   05oct2019 18:11:00 |
  6. |       1      .     .    .     .                    .   05oct2019 18:13:00 |
  7. |       1      .     .    .     .                    .   05oct2019 18:15:00 |
  8. |       1      .     .    .     .                    .   05oct2019 18:17:00 |
  9. |       1      .     .    .     .                    .   05oct2019 18:19:00 |
 10. |       1   2019   278   18    21   01jan1960 00:00:21   05oct2019 18:21:00 |
     |---------------------------------------------------------------------------|
 11. |       2   2019   279   18     3   01jan1960 00:00:21   06oct2019 18:03:00 |
 12. |       2   2019   279   18   3.5   01jan1960 00:00:21   06oct2019 18:03:30 |
 13. |       2   2019   279   18     4   01jan1960 00:00:21   06oct2019 18:04:00 |
 14. |       2   2019   279   18   4.5   01jan1960 00:00:21   06oct2019 18:04:30 |
 15. |       2   2019   279   18     5   01jan1960 00:00:21   06oct2019 18:05:00 |
 16. |       2      .     .    .     .                    .   06oct2019 18:05:30 |
 17. |       2      .     .    .     .                    .   06oct2019 18:06:00 |
 18. |       2      .     .    .     .                    .   06oct2019 18:06:30 |
 19. |       2      .     .    .     .                    .   06oct2019 18:07:00 |
 20. |       2      .     .    .     .                    .   06oct2019 18:07:30 |
 21. |       2      .     .    .     .                    .   06oct2019 18:08:00 |
 22. |       2      .     .    .     .                    .   06oct2019 18:08:30 |
 23. |       2      .     .    .     .                    .   06oct2019 18:09:00 |
 24. |       2      .     .    .     .                    .   06oct2019 18:09:30 |
 25. |       2      .     .    .     .                    .   06oct2019 18:10:00 |
 26. |       2      .     .    .     .                    .   06oct2019 18:10:30 |
 27. |       2   2019   279   18    11   01jan1960 00:00:21   06oct2019 18:11:00 |
     |---------------------------------------------------------------------------|
 28. |       3   2019   281   17    15   01jan1960 00:00:21   08oct2019 17:15:00 |
 29. |       3      .     .    .     .                    .   08oct2019 17:16:00 |
 30. |       3      .     .    .     .                    .   08oct2019 17:17:00 |
 31. |       3      .     .    .     .                    .   08oct2019 17:18:00 |
 32. |       3      .     .    .     .                    .   08oct2019 17:19:00 |
 33. |       3      .     .    .     .                    .   08oct2019 17:20:00 |
 34. |       3      .     .    .     .                    .   08oct2019 17:21:00 |
 35. |       3      .     .    .     .                    .   08oct2019 17:22:00 |
 36. |       3      .     .    .     .                    .   08oct2019 17:23:00 |
 37. |       3      .     .    .     .                    .   08oct2019 17:24:00 |
 38. |       3      .     .    .     .                    .   08oct2019 17:25:00 |
 39. |       3      .     .    .     .                    .   08oct2019 17:26:00 |
 40. |       3      .     .    .     .                    .   08oct2019 17:27:00 |
 41. |       3      .     .    .     .                    .   08oct2019 17:28:00 |
 42. |       3      .     .    .     .                    .   08oct2019 17:29:00 |
 43. |       3      .     .    .     .                    .   08oct2019 17:30:00 |
 44. |       3      .     .    .     .                    .   08oct2019 17:31:00 |
 45. |       3      .     .    .     .                    .   08oct2019 17:32:00 |
 46. |       3      .     .    .     .                    .   08oct2019 17:33:00 |
 47. |       3      .     .    .     .                    .   08oct2019 17:34:00 |
 48. |       3      .     .    .     .                    .   08oct2019 17:35:00 |
 49. |       3      .     .    .     .                    .   08oct2019 17:36:00 |
 50. |       3      .     .    .     .                    .   08oct2019 17:37:00 |
 51. |       3      .     .    .     .                    .   08oct2019 17:38:00 |
 52. |       3      .     .    .     .                    .   08oct2019 17:39:00 |
 53. |       3      .     .    .     .                    .   08oct2019 17:40:00 |
 54. |       3      .     .    .     .                    .   08oct2019 17:41:00 |
 55. |       3      .     .    .     .                    .   08oct2019 17:42:00 |
 56. |       3      .     .    .     .                    .   08oct2019 17:43:00 |
 57. |       3      .     .    .     .                    .   08oct2019 17:44:00 |
 58. |       3      .     .    .     .                    .   08oct2019 17:45:00 |
 59. |       3      .     .    .     .                    .   08oct2019 17:46:00 |
 60. |       3      .     .    .     .                    .   08oct2019 17:47:00 |
 61. |       3      .     .    .     .                    .   08oct2019 17:48:00 |
 62. |       3      .     .    .     .                    .   08oct2019 17:49:00 |
 63. |       3      .     .    .     .                    .   08oct2019 17:50:00 |
 64. |       3      .     .    .     .                    .   08oct2019 17:51:00 |
 65. |       3      .     .    .     .                    .   08oct2019 17:52:00 |
 66. |       3      .     .    .     .                    .   08oct2019 17:53:00 |
 67. |       3      .     .    .     .                    .   08oct2019 17:54:00 |
 68. |       3      .     .    .     .                    .   08oct2019 17:55:00 |
 69. |       3      .     .    .     .                    .   08oct2019 17:56:00 |
 70. |       3      .     .    .     .                    .   08oct2019 17:57:00 |
 71. |       3      .     .    .     .                    .   08oct2019 17:58:00 |
 72. |       3      .     .    .     .                    .   08oct2019 17:59:00 |
 73. |       3      .     .    .     .                    .   08oct2019 18:00:00 |
 74. |       3      .     .    .     .                    .   08oct2019 18:01:00 |
 75. |       3      .     .    .     .                    .   08oct2019 18:02:00 |
 76. |       3   2019   281   18     3   01jan1960 00:00:21   08oct2019 18:03:00 |
 77. |       3      .     .    .     .                    .   08oct2019 18:04:00 |
 78. |       3   2019   281   18     5   01jan1960 00:00:21   08oct2019 18:05:00 |
 79. |       3   2019   281   18     6   01jan1960 00:00:21   08oct2019 18:06:00 |
 80. |       3   2019   281   18     7   01jan1960 00:00:21   08oct2019 18:07:00 |
 81. |       3   2019   281   18     8   01jan1960 00:00:21   08oct2019 18:08:00 |
     +---------------------------------------------------------------------------+

.

[Leslie Edwards]

Thank you. The database is appended with the proper endpoints for the 21 monitoring sessions that ended before the 48 hours of planned personal air pollution monitoring. I've added another example of the gap in data between the last observation of air pollution (PM25) recorded and the endpoint in the 48 hour monitoring period that I added to the database using append. The endpoint I added is the reading when elapsed time=2880 and the 2 variables PM25 and elapsed time both are missing (.). When I did the tsfill command I expected to see more "filled" rows between the observation with time=2602, the last observation recorded before the monitor turned off, and the endpoint with time=2880.

Maybe the problem is that I used the unique variable t for the time set which does not take into account the gap in time that you can see from the elapsedtime variable. So I would need to create a new time variable that reflected the gap in time but always contained unique variables. Thanks for any advice that you have on this.

gen date = td(1jan2019)+ doy -1
format date %td
gen double time= mdyhms(month(date), day(date), year, hr, min, 0)
format time %tc
sort ID_Sess time
gen t= _n
bys ID_Sess (time): gen elapsedtime= (time-time[1])/60000
tsset t

dataex ID_Sess year doy hr min minute s_seq obs_tot in 431/441

* Example generated by -dataex-. For more info, type help dataex
clear
input float(ID_Sess timeactivity PM25 FixedPM year doy hr min date t elapsedtime)
63 4    2 47 2019 307 22 54 21856 128970 2598
63 4    3 47 2019 307 22 56 21856 128971 2600
63 4    2 47 2019 307 22 58 21856 128972 2602
63 4    .  . 2019 308  3 36 21857 128973 2880
64 4 36.5 44 2020  31 17  1 21580 128974    0
64 4   35 44 2020  31 17  2 21580 128975    1
64 4 34.5 44 2020  31 17  3 21580 128976    2
64 4 35.5 44 2020  31 17  4 21580 128977    3
64 4 34.5 44 2020  31 17  5 21580 128978    4
64 4 35.5 44 2020  31 17  6 21580 128979    5
64 4 35.5 44 2020  31 17  7 21580 128980    6
end
format %td date
label values timeactivity tactive
label def tactive 4 "Home", modify

[Leslie Edwards]

Update: I used the option to set time series for panel data (xtset) using the variable "elapsedtime" as the time variable and tsfill correctly added gaps for the missing data in the series.

xtset ID_Sess elapsedtime
tsfill

* Example generated by -dataex-. For more info, type help dataex
clear
input float(ID_Sess timeactivity PM25 FixedPM year doy hr min date t elapsedtime) int obs_min
1 4 6 10 2019 278 1 34 21827 247 512 2
1 . .  .    .   . .  .     .   . 513 .
1 4 5 10 2019 278 1 36 21827 248 514 2
1 . .  .    .   . .  .     .   . 515 .
1 4 4 10 2019 278 1 38 21827 249 516 2
1 . .  .    .   . .  .     .   . 517 .
1 . .  .    .   . .  .     .   . 518 .
1 . .  .    .   . .  .     .   . 519 .
1 . .  .    .   . .  .     .   . 520 .
1 . .  .    .   . .  .     .   . 521 .
1 . .  .    .   . .  .     .   . 522 .
end
format %td date
label values timeactivity tactive
label def tactive 4 "Home", modify

Note the filled gaps in observations after the monitor stopped recording (elapsedtime 517 to 522) which I wanted, however, I also have filled gaps during each recorded observation (time 513 and 515) which I did not want since these gaps just reflect the monitor being set to sample once every 2 minutes. I want to delete the gaps at time 513 and 515 which are expected and would not be considered periods of missing data. I think it makes sense to create a new variable that reflects when to keep data. Any thoughts on that? Thank you!

[Andrew Musau]

Update: I used the option to set time series for panel data (xtset) using the variable "elapsedtime" as the time variable and tsfill correctly added gaps for the missing data in the series.

As I said in #8, xtset will not give you the correct expansion in the presence of data with different frequencies. For example, in #10, the readings are in intervals of 2 minutes whereas your generated observations are in intervals of 1 minute. I gave you one possibility in the same post. Did you have problems implementing it? If you are happy with your solution:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(ID_Sess timeactivity PM25 FixedPM year doy hr min date t elapsedtime) int obs_min
1 4 6 10 2019 278 1 34 21827 247 512 2
1 . .  .    .   . .  .     .   . 513 .
1 4 5 10 2019 278 1 36 21827 248 514 2
1 . .  .    .   . .  .     .   . 515 .
1 4 4 10 2019 278 1 38 21827 249 516 2
1 . .  .    .   . .  .     .   . 517 .
1 . .  .    .   . .  .     .   . 518 .
1 . .  .    .   . .  .     .   . 519 .
1 . .  .    .   . .  .     .   . 520 .
1 . .  .    .   . .  .     .   . 521 .
1 . .  .    .   . .  .     .   . 522 .
end
format %td date
label values timeactivity tactive
label def tactive 4 "Home", modify

gsort ID_Sess -t
by ID_Sess: drop if elapsedtime<elapsedtime[1] & missing(t)

Res.:

. sort elapsed time

. l, sepby(ID)

     +----------------------------------------------------------------------------------------------------+
     | ID_Sess   timeac~y   PM25   FixedPM   year   doy   hr   min        date     t   elapse~e   obs_min |
     |----------------------------------------------------------------------------------------------------|
  1. |       1       Home      6        10   2019   278    1    34   05oct2019   247        512         2 |
  2. |       1       Home      5        10   2019   278    1    36   05oct2019   248        514         2 |
  3. |       1       Home      4        10   2019   278    1    38   05oct2019   249        516         2 |
  4. |       1          .      .         .      .     .    .     .           .     .        517         . |
  5. |       1          .      .         .      .     .    .     .           .     .        518         . |
  6. |       1          .      .         .      .     .    .     .           .     .        519         . |
  7. |       1          .      .         .      .     .    .     .           .     .        520         . |
  8. |       1          .      .         .      .     .    .     .           .     .        521         . |
  9. |       1          .      .         .      .     .    .     .           .     .        522         . |
     +----------------------------------------------------------------------------------------------------+

[Leslie Edwards]

Thanks Andrew. I tried the code that you shared in message #8 and received an error message. I think I will need to adjust the code a bit and need to do some research as I am not familiar with using the commands tempfile, forval, and frame. Will keep working on this and share updates.

tsset time, delta(120000)
time values with period less than delta() found
r(451);

[Andrew Musau]

You will get the error if you are trying to tsset data with different frequencies. What the code in #8 is doing is placing the observations from each session into different data frames, so that each data frame has data with the same frequency. For more on data frames, see

help frames

-delta(120000)- applies to data with 2-minute frequencies, -delta(60000)- one minute and -delta(30000)- 30 seconds. Now, as long as each session does not combine data with different frequencies, this should work. An alternative is to xtset all data specifying the highest frequency (i.e., -delta(30000)-) and then drop observations in-between after expansion as in #10.

[Leslie Edwards]

Update: I found another solution that appears to work to resolve the gaps in data for monitoring sessions with sampling once per 2 minutes instead of the usual once per 1 minute. I will use the "replace" command to added the PM2.5 value for the prior row to the gap rows introduced with tsfill.

replace PM25= PM25[_n-1] if PM25==. & PM25[_n-1]!=. & PM25[_n+1]!=.

And will repeat the replace code process for all 13 variables that I need for the analysis.