Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Panel dataset: Grouping observations by those that came before an event

    Hi again all. Sorry I keep sliding in here with super-specific panel data questions, and I swear I read/play around with as much as possible before posting!

    So what I'm trying to do is group observations based on whether they came before a specific event on a given day (defined as 9am-8:59:59 in this case) by ID. In the example below, 'day' represents that interval (9am-8:59), obsdatetime is the date-time at which each observation occurred, and then eventdatetime is the time at which the event occurred. What I'm trying to do is generate a variable that assigns a 'period' identifier (e.g., 1, 2, 3) to observations by whether it: (a) came before the next event on a given 'day', or (2) if it never occurred that day, just the whole day. While this is easy for most days because an event never happened, some days two and three events happened, so I'm hoping to split those 'days' into multiple periods. Some events also happened at the same time, so these can just be included in the same period. And observations that come after an event can just be missing for this period identifier.

    So far, I managed to tag each observation with the datetime of the next upcoming eventdatetime if there was one, or missing if there wasn't for a given day or if an observation came after the last event. I did that by:

    Code:
    gen double eventdatetime1=eventdatetime
    format eventdatetime1%tcNN/DD/CCYY_HH:MM:SS
    gsort id day -obsdatetime
    by id day: replace eventdatetime1 = eventdatetime1[_n-1] if missing(eventdatetime1)
    So now I have this:



    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input double id float day double(obsdatetime eventdatetime eventdatetime1) float prd
    119  1 1787159832000             . 1.7871678e+12 .
    119  1 1787159956000             . 1.7871678e+12 .
    119  1 1.7871678e+12 1.7871678e+12 1.7871678e+12 .
    119  1 1.7871714e+12 1.7871714e+12 1.7871714e+12 .
    119  1 1787177959000             .             . .
    119  2 1787217769000             .             . .
    119  2 1787226397000             .             . .
    119  2 1787234426000             .             . .
    119  2 1787243424000             .             . .
    119  2 1787253795000             .             . .
    119  2 1787262198000             .             . .
    119  3 1787303693000             .             . .
    119  3 1787303841000             .             . .
    119  3 1787321256000             .             . .
    119  3 1787330805000             .             . .
    119  3 1787339597000             .             . .
    119  3 1787349824000             .             . .
    119  4 1787389766000             .             . .
    119  4 1787396971000             .             . .
    119  4 1787406181000             .             . .
    119  4 1787424176000             .             . .
    119  5 1787475686000             . 1.7875314e+12 .
    119  5 1787483042000             . 1.7875314e+12 .
    119  5 1787491465000             . 1.7875314e+12 .
    119  5 1787501112000             . 1.7875314e+12 .
    119  5 1787510888000             . 1.7875314e+12 .
    119  5 1787519871000             . 1.7875314e+12 .
    119  5 1787527948000             . 1.7875314e+12 .
    119  5 1.7875314e+12 1.7875314e+12 1.7875314e+12 .
    119  6 1787568915000             .             . .
    119  6 1787579242000             .             . .
    119  6 1787587287000             .             . .
    119  6 1787597094000             .             . .
    119  6 1787614259000             .             . .
    119  7 1787649519000             .             . .
    119  7 1787649610000             .             . .
    119  7 1787659453000             .             . .
    119  7 1787667094000             .             . .
    119  7 1787676985000             .             . .
    119  7 1787686590000             .             . .
    119  7 1787696143000             .             . .
    119  8 1787734853000             .             . .
    119  8 1787746780000             .             . .
    119  8 1787755227000             .             . .
    119  8 1787764532000             .             . .
    119  8 1787784143000             .             . .
    119  9 1787824901000             .             . .
    119  9 1787825617000             .             . .
    119  9 1787834391000             .             . .
    119  9 1787844798000             .             . .
    119  9 1787852852000             .             . .
    119  9 1787864144000             .             . .
    119  9 1787870962000             .             . .
    119 10 1787907704000             . 1.7879688e+12 .
    119 10 1787909229000             . 1.7879688e+12 .
    119 10 1787919159000             . 1.7879688e+12 .
    119 10 1787927995000             . 1.7879688e+12 .
    119 10 1787936517000             . 1.7879688e+12 .
    119 10 1787946781000             . 1.7879688e+12 .
    119 10 1787954588000             . 1.7879688e+12 .
    119 10 1.7879688e+12 1.7879688e+12 1.7879688e+12 .
    119 11 1787997443000             .             . .
    119 11 1788005989000             .             . .
    119 11 1788015805000             .             . .
    119 11 1788024247000             .             . .
    119 11 1788033675000             .             . .
    119 11 1788042563000             .             . .
    119 12 1788080534000             .             . .
    119 12 1788086743000             .             . .
    119 12 1788095057000             .             . .
    119 12 1788104028000             .             . .
    119 12 1788114686000             .             . .
    119 12 1788132825000             .             . .
    119 13 1788167968000             .             . .
    119 13 1788168185000             .             . .
    119 13 1788176805000             .             . .
    119 13 1788185648000             .             . .
    119 13 1788193744000             .             . .
    119 13 1788204063000             .             . .
    119 13 1788212818000             .             . .
    119 14 1788258757000             .             . .
    119 14 1788259339000             .             . .
    119 14 1788267816000             .             . .
    119 14 1788284663000             .             . .
    119 14 1788303682000             .             . .
    119 15 1788348652000             .             . .
    119 15 1788349822000             .             . .
    119 15 1788355849000             .             . .
    119 15 1788366820000             .             . .
    119 15 1788368462000             .             . .
    119 15 1788374117000             .             . .
    119 15 1.7883841e+12             .             . .
    119 15 1788391929000             .             . .
    119 16 1788433750000             .             . .
    119 16 1788436381000             .             . .
    119 16 1788444253000             .             . .
    119 16 1788453923000             .             . .
    119 16 1788461807000             .             . .
    119 16 1788472989000             .             . .
    119 16 1788479894000             .             . .
    end
    format %tcNN/DD/CCYY_HH:MM:SS obsdatetime
    format %tcNN/DD/CCYY_HH:MM:SS eventdatetime
    format %tcNN/DD/CCYY_HH:MM:SS eventdatetime1

    What I'm trying to achieve is this:

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input double id float day double(obsdatetime eventdatetime eventdatetime1) float prd
    119  1 1787159832000             . 1.7871678e+12  1
    119  1 1787159956000             . 1.7871678e+12  1
    119  1 1.7871678e+12 1.7871678e+12 1.7871678e+12  1
    119  1 1.7871714e+12 1.7871714e+12 1.7871714e+12  2
    119  1 1787177959000             .             .  .
    119  2 1787217769000             .             .  3
    119  2 1787226397000             .             .  3
    119  2 1787234426000             .             .  3
    119  2 1787243424000             .             .  3
    119  2 1787253795000             .             .  3
    119  2 1787262198000             .             .  3
    119  3 1787303693000             .             .  4
    119  3 1787303841000             .             .  4
    119  3 1787321256000             .             .  4
    119  3 1787330805000             .             .  4
    119  3 1787339597000             .             .  4
    119  3 1787349824000             .             .  4
    119  4 1787389766000             .             .  5
    119  4 1787396971000             .             .  5
    119  4 1787406181000             .             .  5
    119  4 1787424176000             .             .  5
    119  5 1787475686000             . 1.7875314e+12  6
    119  5 1787483042000             . 1.7875314e+12  6
    119  5 1787491465000             . 1.7875314e+12  6
    119  5 1787501112000             . 1.7875314e+12  6
    119  5 1787510888000             . 1.7875314e+12  6
    119  5 1787519871000             . 1.7875314e+12  6
    119  5 1787527948000             . 1.7875314e+12  6
    119  5 1.7875314e+12 1.7875314e+12 1.7875314e+12  6
    119  6 1787568915000             .             .  7
    119  6 1787579242000             .             .  7
    119  6 1787587287000             .             .  7
    119  6 1787597094000             .             .  7
    119  6 1787614259000             .             .  7
    119  7 1787649519000             .             .  8
    119  7 1787649610000             .             .  8
    119  7 1787659453000             .             .  8
    119  7 1787667094000             .             .  8
    119  7 1787676985000             .             .  8
    119  7 1787686590000             .             .  8
    119  7 1787696143000             .             .  8
    119  8 1787734853000             .             .  9
    119  8 1787746780000             .             .  9
    119  8 1787755227000             .             .  9
    119  8 1787764532000             .             .  9
    119  8 1787784143000             .             .  9
    119  9 1787824901000             .             . 10
    119  9 1787825617000             .             . 10
    119  9 1787834391000             .             . 10
    119  9 1787844798000             .             . 10
    119  9 1787852852000             .             . 10
    119  9 1787864144000             .             . 10
    119  9 1787870962000             .             . 10
    119 10 1787907704000             . 1.7879688e+12 11
    119 10 1787909229000             . 1.7879688e+12 11
    119 10 1787919159000             . 1.7879688e+12 11
    119 10 1787927995000             . 1.7879688e+12 11
    119 10 1787936517000             . 1.7879688e+12 11
    119 10 1787946781000             . 1.7879688e+12 11
    119 10 1787954588000             . 1.7879688e+12 11
    119 10 1.7879688e+12 1.7879688e+12 1.7879688e+12 11
    119 11 1787997443000             .             . 12
    119 11 1788005989000             .             . 12
    119 11 1788015805000             .             . 12
    119 11 1788024247000             .             . 12
    119 11 1788033675000             .             . 12
    119 11 1788042563000             .             . 12
    119 12 1788080534000             .             . 13
    119 12 1788086743000             .             . 13
    119 12 1788095057000             .             . 13
    119 12 1788104028000             .             . 13
    119 12 1788114686000             .             . 13
    119 12 1788132825000             .             . 13
    119 13 1788167968000             .             . 14
    119 13 1788168185000             .             . 14
    119 13 1788176805000             .             . 14
    119 13 1788185648000             .             . 14
    119 13 1788193744000             .             . 14
    119 13 1788204063000             .             . 14
    119 13 1788212818000             .             . 14
    end
    format %tcNN/DD/CCYY_HH:MM:SS obsdatetime
    format %tcNN/DD/CCYY_HH:MM:SS eventdatetime
    format %tcNN/DD/CCYY_HH:MM:SS eventdatetime1
    Any ideas?


  • #2
    Code:
    bys id day (obsdatetime): gen group= 1 if _n==1
    bys id day (obsdatetime): replace group= sum(eventdatetime!= ///
    eventdatetime[_n-1]) if !missing(eventdatetime) & _n>1
    bys id day (obsdatetime): replace group= group[_n-1] if missing(group) & _n>1
    egen wanted= group(id day group)
    Res.:

    Code:
    . l id day obsdatetime eventdatetime wanted, sepby(day)
    
         +----------------------------------------------------------------+
         |  id   day           obsdatetime         eventdatetime   wanted |
         |----------------------------------------------------------------|
      1. | 119     1   08/18/2016 17:17:12                     .        1 |
      2. | 119     1   08/18/2016 17:19:16                     .        1 |
      3. | 119     1   08/18/2016 19:30:00   08/18/2016 19:30:00        1 |
      4. | 119     1   08/18/2016 20:30:00   08/18/2016 20:30:00        2 |
      5. | 119     1   08/18/2016 22:19:19                     .        2 |
         |----------------------------------------------------------------|
      6. | 119     2   08/19/2016 09:22:49                     .        3 |
      7. | 119     2   08/19/2016 11:46:37                     .        3 |
      8. | 119     2   08/19/2016 14:00:26                     .        3 |
      9. | 119     2   08/19/2016 16:30:24                     .        3 |
     10. | 119     2   08/19/2016 19:23:15                     .        3 |
     11. | 119     2   08/19/2016 21:43:18                     .        3 |
         |----------------------------------------------------------------|
     12. | 119     3   08/20/2016 09:14:53                     .        4 |
     13. | 119     3   08/20/2016 09:17:21                     .        4 |
     14. | 119     3   08/20/2016 14:07:36                     .        4 |
     15. | 119     3   08/20/2016 16:46:45                     .        4 |
     16. | 119     3   08/20/2016 19:13:17                     .        4 |
     17. | 119     3   08/20/2016 22:03:44                     .        4 |
         |----------------------------------------------------------------|
     18. | 119     4   08/21/2016 09:09:26                     .        5 |
     19. | 119     4   08/21/2016 11:09:31                     .        5 |
     20. | 119     4   08/21/2016 13:43:01                     .        5 |
     21. | 119     4   08/21/2016 18:42:56                     .        5 |
         |----------------------------------------------------------------|
     22. | 119     5   08/22/2016 09:01:26                     .        6 |
     23. | 119     5   08/22/2016 11:04:02                     .        6 |
     24. | 119     5   08/22/2016 13:24:25                     .        6 |
     25. | 119     5   08/22/2016 16:05:12                     .        6 |
     26. | 119     5   08/22/2016 18:48:08                     .        6 |
     27. | 119     5   08/22/2016 21:17:51                     .        6 |
     28. | 119     5   08/22/2016 23:32:28                     .        6 |
     29. | 119     5   08/23/2016 00:30:00   08/23/2016 00:30:00        6 |
         |----------------------------------------------------------------|
     30. | 119     6   08/23/2016 10:55:15                     .        7 |
     31. | 119     6   08/23/2016 13:47:22                     .        7 |
     32. | 119     6   08/23/2016 16:01:27                     .        7 |
     33. | 119     6   08/23/2016 18:44:54                     .        7 |
     34. | 119     6   08/23/2016 23:30:59                     .        7 |
         |----------------------------------------------------------------|
     35. | 119     7   08/24/2016 09:18:39                     .        8 |
     36. | 119     7   08/24/2016 09:20:10                     .        8 |
     37. | 119     7   08/24/2016 12:04:13                     .        8 |
     38. | 119     7   08/24/2016 14:11:34                     .        8 |
     39. | 119     7   08/24/2016 16:56:25                     .        8 |
     40. | 119     7   08/24/2016 19:36:30                     .        8 |
     41. | 119     7   08/24/2016 22:15:43                     .        8 |
         |----------------------------------------------------------------|
     42. | 119     8   08/25/2016 09:00:53                     .        9 |
     43. | 119     8   08/25/2016 12:19:40                     .        9 |
     44. | 119     8   08/25/2016 14:40:27                     .        9 |
     45. | 119     8   08/25/2016 17:15:32                     .        9 |
     46. | 119     8   08/25/2016 22:42:23                     .        9 |
         |----------------------------------------------------------------|
     47. | 119     9   08/26/2016 10:01:41                     .       10 |
     48. | 119     9   08/26/2016 10:13:37                     .       10 |
     49. | 119     9   08/26/2016 12:39:51                     .       10 |
     50. | 119     9   08/26/2016 15:33:18                     .       10 |
     51. | 119     9   08/26/2016 17:47:32                     .       10 |
     52. | 119     9   08/26/2016 20:55:44                     .       10 |
     53. | 119     9   08/26/2016 22:49:22                     .       10 |
         |----------------------------------------------------------------|
     54. | 119    10   08/27/2016 09:01:44                     .       11 |
     55. | 119    10   08/27/2016 09:27:09                     .       11 |
     56. | 119    10   08/27/2016 12:12:39                     .       11 |
     57. | 119    10   08/27/2016 14:39:55                     .       11 |
     58. | 119    10   08/27/2016 17:01:57                     .       11 |
     59. | 119    10   08/27/2016 19:53:01                     .       11 |
     60. | 119    10   08/27/2016 22:03:08                     .       11 |
     61. | 119    10   08/28/2016 02:00:00   08/28/2016 02:00:00       11 |
         |----------------------------------------------------------------|
     62. | 119    11   08/28/2016 09:57:23                     .       12 |
     63. | 119    11   08/28/2016 12:19:49                     .       12 |
     64. | 119    11   08/28/2016 15:03:25                     .       12 |
     65. | 119    11   08/28/2016 17:24:07                     .       12 |
     66. | 119    11   08/28/2016 20:01:15                     .       12 |
     67. | 119    11   08/28/2016 22:29:23                     .       12 |
         |----------------------------------------------------------------|
     68. | 119    12   08/29/2016 09:02:14                     .       13 |
     69. | 119    12   08/29/2016 10:45:43                     .       13 |
     70. | 119    12   08/29/2016 13:04:17                     .       13 |
     71. | 119    12   08/29/2016 15:33:48                     .       13 |
     72. | 119    12   08/29/2016 18:31:26                     .       13 |
     73. | 119    12   08/29/2016 23:33:45                     .       13 |
         |----------------------------------------------------------------|
     74. | 119    13   08/30/2016 09:19:28                     .       14 |
     75. | 119    13   08/30/2016 09:23:05                     .       14 |
     76. | 119    13   08/30/2016 11:46:45                     .       14 |
     77. | 119    13   08/30/2016 14:14:08                     .       14 |
     78. | 119    13   08/30/2016 16:29:04                     .       14 |
     79. | 119    13   08/30/2016 19:21:03                     .       14 |
     80. | 119    13   08/30/2016 21:46:58                     .       14 |
         +----------------------------------------------------------------+

    Observations after the final event in a day are grouped with that event. To ungroup them

    Code:
    bys id day: egen max_eventdatetime= max(eventdatetime)
    bys id day (obsdatetime): replace wanted=. if obsdatetime > ///
    max_eventdatetime & missing(eventdatetime)
    Last edited by Andrew Musau; 15 May 2020, 10:16.

    Comment


    • #3
      Thanks for your response, Andrew! I'm not sure it's working like I'd hoped, though. First, it seems to group datetimes that are equal to the event time as 'new periods' on some days, rather than including them in the same period as the others (could this be because, as on day==20, there are two observations w/ the same time?). Also, the second 'replace group' command seems to replace all values of group with '1,' so the 'wanted' variable doesn't group by ID. So here's the result I get. Am I missing something?

      Code:
      * Example generated by -dataex-. To install: ssc install dataex
      clear
      input double id float day double(obsdatetime eventdatetime eventdatetime1) float(group wanted)
      119 19 1788685661000             .             . 1 287
      119 19 1788688168000             .             . 1 287
      119 19 1788696698000             .             . 1 287
      119 19 1788707180000             .             . 1 287
      119 19 1788714536000             .             . 1 287
      119 19 1788734174000             .             . 1 287
      119 20 1788775257000             .  1.788777e+12 1 288
      119 20  1.788777e+12  1.788777e+12  1.788777e+12 1 288
      119 20 1788777428000             . 1.7888274e+12 1 288
      119 20 1788787840000             . 1.7888274e+12 1 288
      119 20 1788797056000             . 1.7888274e+12 1 288
      119 20 1788813941000             . 1.7888274e+12 1 288
      119 20 1788823749000             . 1.7888274e+12 1 288
      119 20 1.7888274e+12 1.7888274e+12 1.7888274e+12 2 289
      119 20 1.7888274e+12 1.7888274e+12 1.7888274e+12 2 289
      119 21 1788865497000             .             . 1 290
      119 21 1788874381000             .             . 1 290
      119 21 1788884303000             .             . 1 290
      119 21 1788893505000             .             . 1 290
      119 21 1788901136000             .             . 1 290
      119 21 1788910143000             .             . 1 290
      119 22 1788948268000             .  1.788975e+12 1 291
      119 22 1788958072000             .  1.788975e+12 1 291
      119 22 1788968019000             .  1.788975e+12 1 291
      119 22  1.788975e+12  1.788975e+12  1.788975e+12 1 291
      119 22 1788976630000             .             . 1 291
      119 22 1788984903000             .             . 1 291
      119 22 1788995520000             .             . 1 291
      119 23 1789032210000             .             . 1 292
      119 23 1789041739000             .             . 1 292
      119 23 1789060914000             .             . 1 292
      119 23 1789069277000             .             . 1 292
      119 23 1789078349000             .             . 1 292
      119 24 1789120368000             . 1.7891784e+12 1 293
      119 24 1789122820000             . 1.7891784e+12 1 293
      119 24 1789130741000             . 1.7891784e+12 1 293
      119 24 1789148624000             . 1.7891784e+12 1 293
      119 24 1789158939000             . 1.7891784e+12 1 293
      119 24 1789167203000             . 1.7891784e+12 1 293
      119 24 1.7891784e+12 1.7891784e+12 1.7891784e+12 1 293
      119 25 1789208924000             .             . 1 294
      119 25 1789217529000             .             . 1 294
      119 25 1789227171000             .             . 1 294
      119 25 1789236069000             .             . 1 294
      119 25 1789244676000             .             . 1 294
      119 25 1789254290000             .             . 1 294
      end
      format %tcNN/DD/CCYY_HH:MM:SS obsdatetime
      format %tcNN/DD/CCYY_HH:MM:SS eventdatetime
      format %tcNN/DD/CCYY_HH:MM:SS eventdatetime1

      Comment


      • #4
        Thanks for your response, Andrew! I'm not sure it's working like I'd hoped, though. First, it seems to group datetimes that are equal to the event time as 'new periods' on some days, rather than including them in the same period as the others (could this be because, as on day==20, there are two observations w/ the same time?).
        This is what I get running the code in #2 on your data example in #3.

        Code:
        . list id day obsdatetime eventdatetime group wanted if day==20, sep(10)
        
             +------------------------------------------------------------------------+
             |  id   day           obsdatetime         eventdatetime   group   wanted |
             |------------------------------------------------------------------------|
          7. | 119    20   09/06/2016 10:00:57                     .       1        2 |
          8. | 119    20   09/06/2016 10:30:00   09/06/2016 10:30:00       1        2 |
          9. | 119    20   09/06/2016 10:37:08                     .       1        2 |
         10. | 119    20   09/06/2016 13:30:40                     .       1        2 |
         11. | 119    20   09/06/2016 16:04:16                     .       1        2 |
         12. | 119    20   09/06/2016 20:45:41                     .       1        2 |
         13. | 119    20   09/06/2016 23:29:09                     .       1        2 |
         14. | 119    20   09/07/2016 00:30:00   09/07/2016 00:30:00       2        3 |
         15. | 119    20   09/07/2016 00:30:00   09/07/2016 00:30:00       2        3 |
             +------------------------------------------------------------------------+
        Clearly, an event that happened on 09/06/2016 10:30:00 did not take place at the same time as one that happened on 09/07/2016 00:30:00. Therefore, the code is correct as far as I see. Do you want to consider time of day without regard to date?

        Also, the second 'replace group' command seems to replace all values of group with '1,' so the 'wanted' variable doesn't group by ID. So here's the result I get. Am I missing something?
        If this is the case, it does not happen in the data example that you present in #3. The combined code and output from the data example in #3 is shown below.

        Code:
        drop group wanted
        bys id day (obsdatetime): gen group= 1 if _n==1
        bys id day (obsdatetime): replace group= sum(eventdatetime!= ///
        eventdatetime[_n-1]) if !missing(eventdatetime) & _n>1
        bys id day (obsdatetime): replace group= group[_n-1] if missing(group) & _n>1
        egen wanted= group(id day group)
        bys id day: egen max_eventdatetime= max(eventdatetime)
        bys id day (obsdatetime): replace wanted=. if obsdatetime > ///
        max_eventdatetime & missing(eventdatetime)

        Res.:

        Code:
        . l id day obsdatetime eventdatetime group wanted, sepby(day)
        
             +------------------------------------------------------------------------+
             |  id   day           obsdatetime         eventdatetime   group   wanted |
             |------------------------------------------------------------------------|
          1. | 119    19   09/05/2016 09:07:41                     .       1        1 |
          2. | 119    19   09/05/2016 09:49:28                     .       1        1 |
          3. | 119    19   09/05/2016 12:11:38                     .       1        1 |
          4. | 119    19   09/05/2016 15:06:20                     .       1        1 |
          5. | 119    19   09/05/2016 17:08:56                     .       1        1 |
          6. | 119    19   09/05/2016 22:36:14                     .       1        1 |
             |------------------------------------------------------------------------|
          7. | 119    20   09/06/2016 10:00:57                     .       1        2 |
          8. | 119    20   09/06/2016 10:30:00   09/06/2016 10:30:00       1        2 |
          9. | 119    20   09/06/2016 10:37:08                     .       1        2 |
         10. | 119    20   09/06/2016 13:30:40                     .       1        2 |
         11. | 119    20   09/06/2016 16:04:16                     .       1        2 |
         12. | 119    20   09/06/2016 20:45:41                     .       1        2 |
         13. | 119    20   09/06/2016 23:29:09                     .       1        2 |
         14. | 119    20   09/07/2016 00:30:00   09/07/2016 00:30:00       2        3 |
         15. | 119    20   09/07/2016 00:30:00   09/07/2016 00:30:00       2        3 |
             |------------------------------------------------------------------------|
         16. | 119    21   09/07/2016 11:04:57                     .       1        4 |
         17. | 119    21   09/07/2016 13:33:01                     .       1        4 |
         18. | 119    21   09/07/2016 16:18:23                     .       1        4 |
         19. | 119    21   09/07/2016 18:51:45                     .       1        4 |
         20. | 119    21   09/07/2016 20:58:56                     .       1        4 |
         21. | 119    21   09/07/2016 23:29:03                     .       1        4 |
             |------------------------------------------------------------------------|
         22. | 119    22   09/08/2016 10:04:28                     .       1        5 |
         23. | 119    22   09/08/2016 12:47:52                     .       1        5 |
         24. | 119    22   09/08/2016 15:33:39                     .       1        5 |
         25. | 119    22   09/08/2016 17:30:00   09/08/2016 17:30:00       1        5 |
         26. | 119    22   09/08/2016 17:57:10                     .       1        . |
         27. | 119    22   09/08/2016 20:15:03                     .       1        . |
         28. | 119    22   09/08/2016 23:12:00                     .       1        . |
             |------------------------------------------------------------------------|
         29. | 119    23   09/09/2016 09:23:30                     .       1        6 |
         30. | 119    23   09/09/2016 12:02:19                     .       1        6 |
         31. | 119    23   09/09/2016 17:21:54                     .       1        6 |
         32. | 119    23   09/09/2016 19:41:17                     .       1        6 |
         33. | 119    23   09/09/2016 22:12:29                     .       1        6 |
             |------------------------------------------------------------------------|
         34. | 119    24   09/10/2016 09:52:48                     .       1        7 |
         35. | 119    24   09/10/2016 10:33:40                     .       1        7 |
         36. | 119    24   09/10/2016 12:45:41                     .       1        7 |
         37. | 119    24   09/10/2016 17:43:44                     .       1        7 |
         38. | 119    24   09/10/2016 20:35:39                     .       1        7 |
         39. | 119    24   09/10/2016 22:53:23                     .       1        7 |
         40. | 119    24   09/11/2016 02:00:00   09/11/2016 02:00:00       1        7 |
             |------------------------------------------------------------------------|
         41. | 119    25   09/11/2016 10:28:44                     .       1        8 |
         42. | 119    25   09/11/2016 12:52:09                     .       1        8 |
         43. | 119    25   09/11/2016 15:32:51                     .       1        8 |
         44. | 119    25   09/11/2016 18:01:09                     .       1        8 |
         45. | 119    25   09/11/2016 20:24:36                     .       1        8 |
         46. | 119    25   09/11/2016 23:04:50                     .       1        8 |
             +------------------------------------------------------------------------+
        
        .
        EDIT: I can spot a problem with the code. For observations between two events where event date is missing, we need to fill in missing values for the variable group from bottom to top and not from top to bottom. So add the following to the code. This is the one aspect that the data example in #3 differs from that in #1.

        Code:
        drop group wanted
        bys id day (obsdatetime): gen group= 1 if _n==1
        bys id day (obsdatetime): replace group= sum(eventdatetime!= ///
        eventdatetime[_n-1]) if !missing(eventdatetime) & _n>1
        gsort id day -obsdatetime
        by id day: replace group= group[_n-1] if missing(group) & _n>1
        bys id day (obsdatetime): replace group= group[_n-1] if missing(group) & _n>1
        egen wanted= group(id day group)
        bys id day: egen max_eventdatetime= max(eventdatetime)
        bys id day (obsdatetime): replace wanted=. if obsdatetime > ///
        max_eventdatetime & missing(eventdatetime)
        Res.:

        Code:
        . l id day obsdatetime eventdatetime group wanted, sepby(day)
        
             +------------------------------------------------------------------------+
             |  id   day           obsdatetime         eventdatetime   group   wanted |
             |------------------------------------------------------------------------|
          1. | 119    19   09/05/2016 09:07:41                     .       1        1 |
          2. | 119    19   09/05/2016 09:49:28                     .       1        1 |
          3. | 119    19   09/05/2016 12:11:38                     .       1        1 |
          4. | 119    19   09/05/2016 15:06:20                     .       1        1 |
          5. | 119    19   09/05/2016 17:08:56                     .       1        1 |
          6. | 119    19   09/05/2016 22:36:14                     .       1        1 |
             |------------------------------------------------------------------------|
          7. | 119    20   09/06/2016 10:00:57                     .       1        2 |
          8. | 119    20   09/06/2016 10:30:00   09/06/2016 10:30:00       1        2 |
          9. | 119    20   09/06/2016 10:37:08                     .       2        3 |
         10. | 119    20   09/06/2016 13:30:40                     .       2        3 |
         11. | 119    20   09/06/2016 16:04:16                     .       2        3 |
         12. | 119    20   09/06/2016 20:45:41                     .       2        3 |
         13. | 119    20   09/06/2016 23:29:09                     .       2        3 |
         14. | 119    20   09/07/2016 00:30:00   09/07/2016 00:30:00       2        3 |
         15. | 119    20   09/07/2016 00:30:00   09/07/2016 00:30:00       2        3 |
             |------------------------------------------------------------------------|
         16. | 119    21   09/07/2016 11:04:57                     .       1        4 |
         17. | 119    21   09/07/2016 13:33:01                     .       1        4 |
         18. | 119    21   09/07/2016 16:18:23                     .       1        4 |
         19. | 119    21   09/07/2016 18:51:45                     .       1        4 |
         20. | 119    21   09/07/2016 20:58:56                     .       1        4 |
         21. | 119    21   09/07/2016 23:29:03                     .       1        4 |
             |------------------------------------------------------------------------|
         22. | 119    22   09/08/2016 10:04:28                     .       1        5 |
         23. | 119    22   09/08/2016 12:47:52                     .       1        5 |
         24. | 119    22   09/08/2016 15:33:39                     .       1        5 |
         25. | 119    22   09/08/2016 17:30:00   09/08/2016 17:30:00       1        5 |
         26. | 119    22   09/08/2016 17:57:10                     .       1        . |
         27. | 119    22   09/08/2016 20:15:03                     .       1        . |
         28. | 119    22   09/08/2016 23:12:00                     .       1        . |
             |------------------------------------------------------------------------|
         29. | 119    23   09/09/2016 09:23:30                     .       1        6 |
         30. | 119    23   09/09/2016 12:02:19                     .       1        6 |
         31. | 119    23   09/09/2016 17:21:54                     .       1        6 |
         32. | 119    23   09/09/2016 19:41:17                     .       1        6 |
         33. | 119    23   09/09/2016 22:12:29                     .       1        6 |
             |------------------------------------------------------------------------|
         34. | 119    24   09/10/2016 09:52:48                     .       1        7 |
         35. | 119    24   09/10/2016 10:33:40                     .       1        7 |
         36. | 119    24   09/10/2016 12:45:41                     .       1        7 |
         37. | 119    24   09/10/2016 17:43:44                     .       1        7 |
         38. | 119    24   09/10/2016 20:35:39                     .       1        7 |
         39. | 119    24   09/10/2016 22:53:23                     .       1        7 |
         40. | 119    24   09/11/2016 02:00:00   09/11/2016 02:00:00       1        7 |
             |------------------------------------------------------------------------|
         41. | 119    25   09/11/2016 10:28:44                     .       1        8 |
         42. | 119    25   09/11/2016 12:52:09                     .       1        8 |
         43. | 119    25   09/11/2016 15:32:51                     .       1        8 |
         44. | 119    25   09/11/2016 18:01:09                     .       1        8 |
         45. | 119    25   09/11/2016 20:24:36                     .       1        8 |
         46. | 119    25   09/11/2016 23:04:50                     .       1        8 |
             +------------------------------------------------------------------------+
        Last edited by Andrew Musau; 18 May 2020, 06:22.

        Comment


        • #5
          Nice! I think this step got it done! The only thing that was still off was numbering 'wanted' within ID. It was probably tricky to see what that was doing because my example only gave you one ID number (119) to work with. That's because this is such a long dataset that to include >1 value of ID means having to include hundreds (sometimes thousands) of observations in dataex. But, below, you can see how 'wanted' was numbering all such periods in the dataset overall. So, I just added these two lines at the end of your code to number within IDs.

          Code:
          by id wanted (obsdatetime ), sort: gen wanted1 = _n ==1 if wanted!=. 
          by id: replace wanted1 = sum(wanted1) if wanted!=.
          So here's that same example I sent last time:
          Code:
          * Example generated by -dataex-. To install: ssc install dataex
          clear
          input double id float day double(obsdatetime eventdatetime eventdatetime1) float(wanted wanted1)
          119 19 1788685661000             .             . 287 20
          119 19 1788688168000             .             . 287 20
          119 19 1788696698000             .             . 287 20
          119 19 1788707180000             .             . 287 20
          119 19 1788714536000             .             . 287 20
          119 19 1788734174000             .             . 287 20
          119 20 1788775257000             .  1.788777e+12 288 21
          119 20  1.788777e+12  1.788777e+12  1.788777e+12 288 21
          119 20 1788777428000             . 1.7888274e+12 289 22
          119 20 1788787840000             . 1.7888274e+12 289 22
          119 20 1788797056000             . 1.7888274e+12 289 22
          119 20 1788813941000             . 1.7888274e+12 289 22
          119 20 1788823749000             . 1.7888274e+12 289 22
          119 20 1.7888274e+12 1.7888274e+12 1.7888274e+12 289 22
          119 20 1.7888274e+12 1.7888274e+12 1.7888274e+12 289 22
          119 21 1788865497000             .             . 290 23
          119 21 1788874381000             .             . 290 23
          119 21 1788884303000             .             . 290 23
          119 21 1788893505000             .             . 290 23
          119 21 1788901136000             .             . 290 23
          119 21 1788910143000             .             . 290 23
          119 22 1788948268000             .  1.788975e+12 291 24
          119 22 1788958072000             .  1.788975e+12 291 24
          119 22 1788968019000             .  1.788975e+12 291 24
          119 22  1.788975e+12  1.788975e+12  1.788975e+12 291 24
          119 22 1788976630000             .             .   .  .
          119 22 1788984903000             .             .   .  .
          119 22 1788995520000             .             .   .  .
          119 23 1789032210000             .             . 292 25
          119 23 1789041739000             .             . 292 25
          119 23 1789060914000             .             . 292 25
          119 23 1789069277000             .             . 292 25
          119 23 1789078349000             .             . 292 25
          119 24 1789120368000             . 1.7891784e+12 293 26
          119 24 1789122820000             . 1.7891784e+12 293 26
          119 24 1789130741000             . 1.7891784e+12 293 26
          119 24 1789148624000             . 1.7891784e+12 293 26
          119 24 1789158939000             . 1.7891784e+12 293 26
          119 24 1789167203000             . 1.7891784e+12 293 26
          119 24 1.7891784e+12 1.7891784e+12 1.7891784e+12 293 26
          119 25 1789208924000             .             . 294 27
          119 25 1789217529000             .             . 294 27
          119 25 1789227171000             .             . 294 27
          119 25 1789236069000             .             . 294 27
          119 25 1789244676000             .             . 294 27
          119 25 1789254290000             .             . 294 27
          end
          format %tcNN/DD/CCYY_HH:MM:SS obsdatetime
          format %tcNN/DD/CCYY_HH:MM:SS eventdatetime
          format %tcNN/DD/CCYY_HH:MM:SS eventdatetime1
          But here's another from further down the dataset for more:

          Code:
          * Example generated by -dataex-. To install: ssc install dataex
          clear
          input double id float day double(obsdatetime eventdatetime eventdatetime1) float(wanted wanted1)
          134  9 1801561794000             . 1801612860000 368  9
          134  9 1801564378000             . 1801612860000 368  9
          134  9 1801574277000             . 1801612860000 368  9
          134  9 1801591621000             . 1801612860000 368  9
          134  9 1801601438000             . 1801612860000 368  9
          134  9 1801612860000 1801612860000 1801612860000 368  9
          134 10 1801655490000             .             . 369 10
          134 10 1801663921000             .             . 369 10
          134 10 1801682752000             .             . 369 10
          134 10 1801691854000             .             . 369 10
          134 10 1801699055000             .             . 369 10
          134 11 1801739445000             .             . 370 11
          134 11 1801740236000             .             . 370 11
          134 11 1801748940000             .             . 370 11
          134 11 1801768021000             .             . 370 11
          134 11 1801775679000             .             . 370 11
          134 12 1801821251000             . 1.8018792e+12 371 12
          134 12 1801827704000             . 1.8018792e+12 371 12
          134 12 1801835633000             . 1.8018792e+12 371 12
          134 12 1801849855000             . 1.8018792e+12 371 12
          134 12 1801854679000             . 1.8018792e+12 371 12
          134 12 1801862835000             . 1.8018792e+12 371 12
          134 12 1.8018792e+12 1.8018792e+12 1.8018792e+12 371 12
          134 13 1.8019116e+12 1.8019116e+12 1.8019116e+12 372 13
          134 13 1801919204000             .             .   .  .
          134 13 1801927660000             .             .   .  .
          134 13 1801936909000             .             .   .  .
          134 13 1801945872000             .             .   .  .
          134 14 1801994866000             .             . 373 14
          134 14 1802009816000             .             . 373 14
          134 14 1802028552000             .             . 373 14
          134 14 1802037377000             .             . 373 14
          end
          format %tcNN/DD/CCYY_HH:MM:SS obsdatetime
          format %tcNN/DD/CCYY_HH:MM:SS eventdatetime
          format %tcNN/DD/CCYY_HH:MM:SS eventdatetime1
          Anyway, thanks again so very much for your help in figuring this out, Andrew!

          Comment

          Working...
          X