Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    collapse observation based on time

    I have a dataset is in time frame of 1 second, which i have to change it for a 5 seconds.

    e.g., in data set 0,1,2,3,4,5,6,7,8,9,10
    i have to change like 5,10,15,20,25

    Here i attached the file of dataex

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str34 trip_id float(date_only hour minute second) byte(drowsy_alert drowsy_state distraction_alert) float(ear mar yaw pitch roll ect xaccel yaccel zaccel speed) double(vehicle_heading vehicle_roll vehicle_pitch)
"0AzKU1_1667067600000_1667070000000" 22947 18 20  0 0 0 .  .3022537  .04664534  -22.33495   10.661908  -1.1843072         2   7.5     -7.5      3.5  47.16667   18691825 -393221   -2471
"0AzKU1_1667067600000_1667070000000" 22947 18 20  1 0 0 .  .3141343  .05020795  -6.899253     9.83355   -.1554014         2     0        5      9.5        48 18695274.5 -381608   -6592
"0AzKU1_1667067600000_1667070000000" 22947 18 20  2 0 0 .  .3288067  .04960649  -6.744576   8.8245735   -.8004304         2    15        6      -17        48   18689179 -388472    9372
"0AzKU1_1667067600000_1667070000000" 22947 18 20  3 0 0 . .32170725  .04852519  -8.407268   8.7683735  -1.6400268         2     6        8      -33        48 18678772.5 -363670   -5919
"0AzKU1_1667067600000_1667070000000" 22947 18 20  4 0 0 .  .3322021  .04240392 -3.5984244    9.965551   -2.264006         2 -29.5        5      -43        48 18682028.5 -392138   -8867
"0AzKU1_1667067600000_1667070000000" 22947 18 20  5 0 0 .  .3387012  .03853254  -3.244071    5.436527  -1.0186857         2 -26.5     16.5    -26.5        48   18699971 -404553  -11624
"0AzKU1_1667067600000_1667070000000" 22947 18 20  6 0 0 .   .329486  .04491525  -8.749819  -1.2305436    .5329398         2  -7.5       24      -14        48 18706512.5 -380591   -8098
"0AzKU1_1667067600000_1667070000000" 22947 18 20  7 0 0 .  .3757701  .08215675   9.634439   -1.370746    1.937833         2   5.5     16.5       -5        48 18674899.5 -383259   -1182
"0AzKU1_1667067600000_1667070000000" 22947 18 20  8 0 0 .  .3270063  .02768945   7.814692  -3.0757866    2.771564         2    15      -14        0  48.14286   18644942 -372347   -6170
"0AzKU1_1667067600000_1667070000000" 22947 18 20  9 0 0 .   .334933  .05042565  -9.694034   -1.686967   -.4650979         2    53     -5.5     35.5  48.84615 18657328.5 -419480   -3293
"0AzKU1_1667067600000_1667070000000" 22947 18 20 10 0 0 . .33013394 .032470908 -1.3694987  -1.8622993   -1.198426         2    71     25.5     59.5        48   18665142 -405118   -2361
"0AzKU1_1667067600000_1667070000000" 22947 18 20 11 0 0 . .33019575  .01798955  -1.592174   -4.008063   -.0392199         2    25       14       29        48   18674272 -392736    8020
"0AzKU1_1667067600000_1667070000000" 22947 18 20 12 0 0 .  .3416967 .029767485  -2.899269   -3.092655   -.8941393         2     1       -6       -3        48 18681657.5 -372494   14418
"0AzKU1_1667067600000_1667070000000" 22947 18 20 13 0 0 .  .3229493  .03644327 -1.1221027  -1.2657988  -1.2452044         2 -16.5       -8     -3.5        48   18680059 -348079    3938
"0AzKU1_1667067600000_1667070000000" 22947 18 20 14 0 0 .  .3317762  .03221677   -22.7151    .4665689   -2.887745       2.7   -59       -3       21        48   18657484 -359962    7785
"0AzKU1_1667067600000_1667070000000" 22947 18 20 15 0 0 .  .3086619  .08674324 -10.320287    .9748285   -3.112873         3   -42       .5       10        48 18637564.5 -376166   15525
"0AzKU1_1667067600000_1667070000000" 22947 18 20 16 0 0 . .31945235  .10331665   4.844316    .4528357   -2.556811         3   7.5      7.5        6        48   18632223 -385759   23390
"0AzKU1_1667067600000_1667070000000" 22947 18 20 17 0 0 .  .3241128  .04790377  2.2810981    -.290036  -1.6232772         3    12      1.5     24.5        48   18643308 -399702    4275
"0AzKU1_1667067600000_1667070000000" 22947 18 20 18 0 0 .  .3217847 .062722996  1.0690427    .6401941  -1.4200033         3    30     -1.5       11        48 18664237.5 -374767   25036
"0AzKU1_1667067600000_1667070000000" 22947 18 20 19 0 0 .  .3214265 .035662286   2.653966   -.3251729   -.8151694         3    52 2.666667 2.666667        48   18667526 -387912    9338
"0AzKU1_1667067600000_1667070000000" 22947 18 20 20 0 0 .  .3294363  .03282902   3.247939  -1.0641366   -2.858025         3  18.5       -1       23        48   18653388 -364239    9740
"0AzKU1_1667067600000_1667070000000" 22947 18 20 21 0 0 .  .3258928  .04120803   .3403527  -1.2979633  -2.0147753         3   4.5     -6.5     12.5  47.78571   18663812 -368746   -1774
"0AzKU1_1667067600000_1667070000000" 22947 18 20 22 0 0 .  .3427276  .07688826    5.60529    -.669441  -2.9149454         3   -26       10       -4        47   18696964 -347283    6774
"0AzKU1_1667067600000_1667070000000" 22947 18 20 23 0 0 .  .3316146  .09921147   8.102524    -.339475  -3.7714124         3   -60       30       -2        47   18719363 -392849    9421
"0AzKU1_1667067600000_1667070000000" 22947 18 20 24 0 0 .  .3177319  .08293419   8.374801   -.6115467   -3.915895         3 -44.5      -.5    -22.5        47 18699413.5 -402065  -10540
"0AzKU1_1667067600000_1667070000000" 22947 18 20 25 0 0 .  .3156898   .0424481   5.280636  -1.1720426  -4.4963675         3 -42.5     16.5   -123.5        47   18661095 -392773  -17550
"0AzKU1_1667067600000_1667070000000" 22947 18 20 26 0 0 .  .3167501 .067182854  12.098933  -1.4932454   -3.139841         3   8.5     42.5    -78.5        47 18630376.5 -328487   10189
"0AzKU1_1667067600000_1667070000000" 22947 18 20 27 0 0 .   .279004   .0474862 -22.659904   -.5402075   -6.331226       4.1  16.5     26.5     10.5        47 18635210.5 -376996    3021
"0AzKU1_1667067600000_1667070000000" 22947 18 20 28 0 0 .  .3102519  .04259655   3.408795  -.04194668  -2.1451972         5   -14     -5.5    -56.5        47   18664472 -346123    6075
"0AzKU1_1667067600000_1667070000000" 22947 18 20 29 0 0 .  .3148743  .07530516  4.2066655    .7068723  -1.3889853         5 -14.5      -25    -25.5        47   18677203 -356914  -10730
"0AzKU1_1667067600000_1667070000000" 22947 18 20 30 0 0 .  .3278304  .03484796   16.14913   -2.443574  -2.1376007         5 -34.5    -13.5        5        47 18666070.5 -352063    -234
"0AzKU1_1667067600000_1667070000000" 22947 18 20 31 0 0 .  .3751161  .06069422   28.71232   -9.071636    3.416233         5   -23       -6      -11        47 18655290.5 -369514  -15621
"0AzKU1_1667067600000_1667070000000" 22947 18 20 32 0 0 .  .3513102  .04284284   24.76533   -5.210635   .15582228         5   -17     -3.5    -17.5        47 18633519.5 -346822   -9502
"0AzKU1_1667067600000_1667070000000" 22947 18 20 33 0 0 .  .3241508  .04246193  19.257784  -1.5505214  -2.9965866         5  15.5     17.5      -50        47 18629225.5 -373646  -19021
"0AzKU1_1667067600000_1667070000000" 22947 18 20 34 0 0 .  .3347431 .018032318  11.560295  -2.7494934   -.9659829         5  21.5       25    -52.5        47 18659585.5 -358781  -21608
"0AzKU1_1667067600000_1667070000000" 22947 18 20 35 0 0 .  .3057313 .032527454 -.03994904   -3.489416   -.2480453         5 -39.5     -3.5     27.5        47 18666604.5 -381306  -38291
"0AzKU1_1667067600000_1667070000000" 22947 18 20 36 0 0 .  .2926452  .05440405  -21.59878   -2.982669   -5.702406       4.8 -33.5      5.5     51.5        47   18652699 -373748  -51558
"0AzKU1_1667067600000_1667070000000" 22947 18 20 37 0 0 . .29822645 .063912675   3.058085  -.14722517   -.9932708         4 -26.5       12     10.5        47   18645300 -403200  -45120
"0AzKU1_1667067600000_1667070000000" 22947 18 20 38 0 0 .  .3045507  .02101958   7.962337   -.8537662  -2.2392154         4    -7      -30      6.5        47   18629200 -404649  -12698
"0AzKU1_1667067600000_1667070000000" 22947 18 20 39 0 0 .  .3041966  .04173747   7.171206    .5327539   -3.579891         4     1      -38    -24.5        47   18645414 -410653  -58470
"0AzKU1_1667067600000_1667070000000" 22947 18 20 40 0 0 . .28161597  .02411564   3.267035  -1.5797496  -1.7986704 4.5555553     0      -34       62  46.84615 18713281.5 -392016  -44788
"0AzKU1_1667067600000_1667070000000" 22947 18 20 41 0 0 .  .3100269 .032919977  13.358917  -1.6282394  -1.8423996         5  12.5        3       74        46   18709882 -532050  -29861
"0AzKU1_1667067600000_1667070000000" 22947 18 20 42 0 0 .  .3334334  .06721634  24.057585  -4.2080116    .4915709         5 -41.5     38.5    -14.5  45.85714 18702130.5 -412314    4833
"0AzKU1_1667067600000_1667070000000" 22947 18 20 43 0 0 .   .325004  .03803258     9.5192  -1.4609203   -3.687742         5   -84     22.5    -59.5        45 18675201.5 -524718   77443
"0AzKU1_1667067600000_1667070000000" 22947 18 20 44 0 0 . .29310387 .034627765   8.222899   -1.464956   -2.384296       4.9 -86.5      5.5    -65.5  44.86364   18674067 -516918  119981
"0AzKU1_1667067600000_1667070000000" 22947 18 20 45 0 0 . .29714707  .06450214  20.778816  -9.0288515    5.361134         5   -74       27    -34.5  43.73077   18726115 -596953  123610
"0AzKU1_1667067600000_1667070000000" 22947 18 20 46 0 0 . .28630388  .07882614  3.8078165   -2.926129   -.3798111         5 -12.5       32      -20  42.86666   18803516 -577608  130987
"0AzKU1_1667067600000_1667070000000" 22947 18 20 47 0 0 .  .2730455 .037028197   7.079381   -4.196313    1.710713       5.2 -14.5      -67      -31  41.88889 18793655.5 -421966  152437
"0AzKU1_1667067600000_1667070000000" 22947 18 20 48 0 0 . .27385712  .05060664   10.93863    1.264601   -.5652282         6   -29    -46.5      -70        41 18776336.5 -538713  133826
"0AzKU1_1667067600000_1667070000000" 22947 18 20 49 0 0 .  .3092004  .03251319   6.405811   -1.222207  -1.4327965         6     3      -.5      -28  40.96154 18736021.5 -487559  163109
"0AzKU1_1667067600000_1667070000000" 22947 18 20 50 0 0 . .28805047  .07020306  -.8904774   -.4988047  .022270147         6 -48.5    -27.5       -4        40   18678216 -414584  119276
"0AzKU1_1667067600000_1667070000000" 22947 18 20 51 0 0 .   .329832 .024696257 .065571874  -2.2066922 -.003047648         6   -21      -40     61.5  39.78571   18617907 -406083  127369
"0AzKU1_1667067600000_1667070000000" 22947 18 20 52 0 0 . .28879267 .032248653   5.024931  -1.0826249    .4500586         6   -30    -19.5     80.5        39   18621253 -377710  143853
"0AzKU1_1667067600000_1667070000000" 22947 18 20 53 0 0 . .29723462  .08830312    3.38074   -.3083909   -.9278186         6   -82     27.5     20.5  38.78571   18647756 -455636  101799
"0AzKU1_1667067600000_1667070000000" 22947 18 20 54 0 0 .  .3128353   .0879565  4.4837418 -.015830278  -1.6022317         6     2      -15       16        38   18600042 -334756   80191
"0AzKU1_1667067600000_1667070000000" 22947 18 20 55 0 0 .  .3265799  .04420157   6.375978  -1.0171748   -3.511862         6    28      -36      -42 37.785713   18567223 -393073   98365
"0AzKU1_1667067600000_1667070000000" 22947 18 20 56 0 0 .  .3661811 .034553103   24.30518   -7.747689    .7760123         6    56      -32       26        37 18623533.5 -506692   80604
"0AzKU1_1667067600000_1667070000000" 22947 18 20 57 0 0 . .29316187 .032486893   9.349307   -.6083313   -6.610742         6    27    -28.5       53        37   18626835 -601959   85518
"0AzKU1_1667067600000_1667070000000" 22947 18 20 58 0 0 .   .324474 .026377114   4.982203  -1.4592004   -2.776484         6 -31.5      2.5      7.5        37   18653491 -508770   85017
"0AzKU1_1667067600000_1667070000000" 22947 18 20 59 0 0 .  .3307063  .04848357  .25293502  -1.1532998    .4564366         6  21.5       33     60.5 36.916668   18709305 -448431   55890
"0AzKU1_1667067600000_1667070000000" 22947 18 21  0 0 0 .   .327994  .02513575  -.5066689   -3.842685  -1.4435145         6 -31.5      -15      118 36.153847 18711518.5 -244698  120233
"0AzKU1_1667067600000_1667070000000" 22947 18 21  1 0 0 .  .3146737 .022318697   3.192631   -2.949082  -.26348996         6  22.5     10.5      104        37   18719424 -313446   41200
"0AzKU1_1667067600000_1667070000000" 22947 18 21  2 0 0 . .37468535  .03457256  24.197615   -8.472035    2.901878         6   121     25.5       74 37.142857 18647654.5 -197726  -75455
"0AzKU1_1667067600000_1667070000000" 22947 18 21  3 0 0 .  .3111919  .03336682   9.919469   -.8500754  -.56055254         6  77.5      -22     57.5        38   18628988 -457438  -54814
"0AzKU1_1667067600000_1667070000000" 22947 18 21  4 0 0 .  .3277394  .02479614   4.304323  -1.9536177  -2.5141056         6    51       23       46        38 18574737.5 -410129  -93258
"0AzKU1_1667067600000_1667070000000" 22947 18 21  5 0 0 .  .3270197 .064273514    7.87141   -.5975673   -1.946827         6  20.5     58.5     64.5        38   18596179 -427164  -27662
"0AzKU1_1667067600000_1667070000000" 22947 18 21  6 0 0 .  .3107996  .08361742   7.144328   .02371025  -2.1510544         6 -33.5     48.5       49        38   18658534 -483112  -27458
"0AzKU1_1667067600000_1667070000000" 22947 18 21  7 0 0 . .28774378  .06543818  -3.158018   .51291543   -1.993671         6   -89     -112     33.5        38   18684541 -416207  -41195
"0AzKU1_1667067600000_1667070000000" 22947 18 21  8 0 0 .  .3208535  .05808659  -9.290446  -.27205724  -3.6984444         6 -65.5   -104.5      -79        38   18674101 -342045  -77991
"0AzKU1_1667067600000_1667070000000" 22947 18 21  9 0 0 .  .3276805  .01983392   1.904321   -4.105439   -2.918402         6  16.5       28   -118.5        38   18687563 -380187  -36682
"0AzKU1_1667067600000_1667070000000" 22947 18 21 10 0 0 .  .3235767  .03103039  4.1304555   -3.184789   -.1797498         6  22.5      5.5       32        38 18691332.5 -462351  -92107
"0AzKU1_1667067600000_1667070000000" 22947 18 21 11 0 0 . .31841105 .017078497   3.850692   -3.307374   -3.181428         6   -20      -25       17        38   18633378 -395540  -59548
"0AzKU1_1667067600000_1667070000000" 22947 18 21 12 0 0 .  .3269878 .029588474   3.389606   -3.662106  -2.1291966         6  67.5       16    -19.5  38.21429   18615774 -396011  -45519
"0AzKU1_1667067600000_1667070000000" 22947 18 21 13 0 0 .  .2817583 .035313386 -1.0515712   -2.545081   1.0982317         6    93     13.5     40.5  39.21429 18668784.5 -338139  -76010
"0AzKU1_1667067600000_1667070000000" 22947 18 21 14 0 0 .  .3110451  .04678983   4.096642   -.2885777   -.3991674       5.3   -67     57.5    -39.5  40.21429   18701800 -396895 -174971
"0AzKU1_1667067600000_1667070000000" 22947 18 21 15 0 0 .  .3490636 .025387155  15.921147   -4.369901   2.3340836         5   -31       -2      -39        41 18702814.5 -173976 -112667
"0AzKU1_1667067600000_1667070000000" 22947 18 21 16 0 0 .  .2800335  .05986784   9.192592   -.6559453   -1.761507         5  69.5    -83.5     34.5  41.14286 18696679.5 -315704 -138490
"0AzKU1_1667067600000_1667070000000" 22947 18 21 17 0 0 .  .2923764  .03539529    7.84799   -.8635947  -2.2005236         5    11     15.5     32.5        42   18684843 -409549 -164482
"0AzKU1_1667067600000_1667070000000" 22947 18 21 18 0 0 . .29604355 .020586684   2.397139  -2.8071055   -1.244795         5   1.5       60     50.5  42.21429 18682184.5 -429515 -185806
"0AzKU1_1667067600000_1667070000000" 22947 18 21 19 0 0 . .27479333  .02574398  -5.596757   -6.654218  -1.3868735         5    10     36.5      5.5        43 18657080.5 -390217 -175132
"0AzKU1_1667067600000_1667070000000" 22947 18 21 20 0 0 . .18842523  .04095558  -38.83236   -11.74353  -3.7284186         6  21.5    -13.5      -18  43.23077 18619759.5 -407505 -126883
"0AzKU1_1667067600000_1667070000000" 22947 18 21 21 0 0 . .29733413  .03871763   6.647009   .20498426  -1.2752123         6  46.5      -14      8.5        44 18621390.5 -428355 -135551
"0AzKU1_1667067600000_1667070000000" 22947 18 21 22 0 0 . .32645905  .05733686   18.90567  -1.7432475    2.158265         6    30       -4      3.5  43.78571 18657900.5 -392720 -133734
"0AzKU1_1667067600000_1667070000000" 22947 18 21 23 0 0 . .28574666 .033321857  2.4505196     .391463   -.6309679         6 -40.5      9.5      -22  42.14286   18713930 -355520 -170747
"0AzKU1_1667067600000_1667070000000" 22947 18 21 24 0 0 .  .3737589  .05536446  18.951015   -5.783566   2.8627806         6 -91.5      -21    -84.5        39 18711226.5 -274903 -144800
"0AzKU1_1667067600000_1667070000000" 22947 18 21 25 0 0 . .25451517 .025573606   5.067579   -.8637982    -.793193         6   -21     -138   -115.5        39 18668324.5 -251340 -141330
"0AzKU1_1667067600000_1667070000000" 22947 18 21 26 0 0 . .28618294  .02027366   5.598778  -1.8073745   -3.698249         6   -67     -161      -93  39.14286   18672666 -297869 -140858
"0AzKU1_1667067600000_1667070000000" 22947 18 21 27 0 0 .   .330493 .023197416  .29714116   -4.928351   -5.384166       4.9 -70.5      -14    -47.5        40 18710813.5 -463581 -148172
"0AzKU1_1667067600000_1667070000000" 22947 18 21 28 0 0 . .28765947  .02331556  -4.103369   -6.560649   -3.447554         4    11       25       39  40.14286   18683340 -392983  -80213
"0AzKU1_1667067600000_1667070000000" 22947 18 21 29 0 0 .  .3003268  .01400077  -2.502214   -5.320691  -1.8223573         4    -6    -42.5     29.5  40.91667 18588024.5 -247089  -80585
"0AzKU1_1667067600000_1667070000000" 22947 18 21 30 0 0 . .30852705  .06044824    6.91312    -3.15848   -7.997088         4   8.5     28.5     25.5        40   18515638 -568601  -37810
"0AzKU1_1667067600000_1667070000000" 22947 18 21 31 0 0 .  .3015135  .04779182  -5.187423   -.1995354   -6.165501         4 -20.5       74       46        40   18598031 -554296  -23547
"0AzKU1_1667067600000_1667070000000" 22947 18 21 32 0 0 .  .3211881 .012252466  1.2692525  -3.5926306   -2.848105         4   -64       64      -42  39.84615   18621963 -589521  -20968
"0AzKU1_1667067600000_1667070000000" 22947 18 21 33 0 0 .  .2997783  .01928812  -1.548823  -2.0823328  -1.2957785         4   -37       27      -60        39   18656870 -478866  -45915
"0AzKU1_1667067600000_1667070000000" 22947 18 21 34 0 0 . .33962935  .08515368   11.82941    -.868632   -5.973417         4   -25      -22      -65        39 18625525.5 -403722  -76332
"0AzKU1_1667067600000_1667070000000" 22947 18 21 35 0 0 .  .3415732  .04346741  13.416984   -1.422998   -4.990396         4    14       -6     -5.5        39 18659476.5 -390653  -36428
"0AzKU1_1667067600000_1667070000000" 22947 18 21 36 0 0 .  .3223907  .02837064   5.590825    -.645407  -1.6538143         4  57.5      -24     21.5  39.14286   18694659 -357766  -23988
"0AzKU1_1667067600000_1667070000000" 22947 18 21 37 0 0 .  .3277752   .0280991  4.1458435    .1332528  -2.0757952         4    27    -25.5       -3        40 18691515.5 -390983  -22202
"0AzKU1_1667067600000_1667070000000" 22947 18 21 38 0 0 . .32170185    .042993 -17.854486   -.6480579   -6.236247       4.6     4       -2       44  40.14286 18682660.5 -380022  -32547
"0AzKU1_1667067600000_1667070000000" 22947 18 21 39 0 0 . .29550424   .0524175  -6.121655   -.8458835  -1.0837632         5   9.5      6.5       28        41 18677544.5 -389867  -23359
end
format %td date_only
    Tags: None
  • #2
    I guess I might have sounded confusing. To be clear I want to collapse data by 5 seconds
    For example, the first 5 seconds have to be collapsed into a single observation where I want the mean of the variables: ear mar yaw pitch roll ect xaccel yaccel zaccel speed vehicle_heading vehicle_roll vehicle_pitch.

    Comment

    • #3
      I assume, though you do not say so, that you want to do this separately for each trip_id (i.e. you want to start counting 5-second intervals from 1 at the beginning of each trip_id). I have also included in the result a variable that gives the timestamp for the beginning of each interval.
      Code:
      //  CREATE A CLOCK VARIABLE
gen double timestamp = dhms(date_only, hour, minute, second), before(date_only)
format timestamp %tc

//  DIVIDE THE DATA SET INTO FIVE-SECOND INTERVALS
by trip_id (timestamp), sort: gen double interval5 = ///
    floor((timestamp-timestamp[1])/msofseconds(5)) + 1, after(timestamp)
format timestamp %tc

//  COLLAPSE TO FIVE-SECOND INTERVAL DATA
collapse (mean) ear mar yaw pitch roll ect xaccel yaccel zaccel speed ///
    vehicle_heading vehicle_roll vehicle_pitch (min) timestamp, ///
    by(trip_id interval)

      Comment

      Previous Next
      Working...
      X