how to filter data using complex criteria?

[sudharsan reddy]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str34 trip_id float(date_only admithour admitmn second) byte(distraction_alert distraction_state drowsy_alert drowsy_state) float(ear mar yaw pitch roll ect xaccel yaccel zaccel speed) double(vehicle_heading vehicle_roll vehicle_pitch)
"URnFGI_1664431800000_1664433000000" 22917 5 30  0 0 0 0 0  .12339642  .02654846   3.2522924   5.346786  -7.561827    445.4      59.68      59.68      59.68       56 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30  1 0 0 0 0   .0977508  .05325393      2.7059  -.6044217  -1.697068    452.2         -8         -8         -8       56 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30  2 0 0 0 0  .10890697  .12550355   -2.757768   2.761439  2.0508072    456.9  -41.28571  -41.28571  -41.28571       56 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30  3 0 0 0 0   .1147201  .07063284   -6.776319  1.9004796    3.06508    462.1  -30.42857  -30.42857  -30.42857       56 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30  4 0 0 0 0  .10706218  .11241633    1.411382   4.395678  -7.654655    465.3  -6.142857  -6.142857  -6.142857       56 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30  5 0 0 0 2  .04862783 .018031677     .729813  -6.570393  -7.160207      467  12.857142  12.857142  12.857142       56 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30  6 0 0 1 3  .13999373  .03814996   1.7377788 -2.1882339  -4.546707      467          8          8          8 55.78571 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30  7 0 0 0 0  .11607492  .09353194  -.02536547   5.223692  -2.982557    465.2         54         54         54       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30  8 0 0 0 0  .11707409  .10936575   -7.977838   .7949892  2.7013454    465.7   5.714286   5.714286   5.714286       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30  9 0 0 0 0  .07758173  .06462802   -.8192918  -.7785137   1.140566    471.1  25.714285  25.714285  25.714285       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 10 0 0 0 0  .05201187  .05731596   -1.756431   3.205118   -3.52376      472          1          1          1       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 11 0 0 1 3   .1191597   .0407237 .0010871099    1.04114  -1.663454    472.7 -15.428572 -15.428572 -15.428572       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 12 0 0 1 3   .1123781  .19251773   -.6646857   5.757377 -1.7380743    472.7  -9.428572  -9.428572  -9.428572       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 13 0 0 0 0  .08806506  .11683363    .7800815   9.409576 -2.8835325    472.8 -1.3571428 -1.3571428 -1.3571428       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 14 0 0 0 0  .12702875  .04556134    .7537467   9.859732  -6.271179 472.4445  21.076923  21.076923  21.076923       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 15 0 0 0 0   .1544885  .04640368    5.530661  13.368636   -6.51475      468         22         22         22       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 16 0 0 0 0  .22463498   .0895549    18.73136   6.358907  -6.775558    460.5  -3.142857  -3.142857  -3.142857       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 17 0 0 0 0  .22189854  .08840252   10.139014  -3.428161   11.48644    451.4  11.428572  11.428572  11.428572       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 18 0 0 0 0  .12154574   .0845197  -2.2534573 -4.1433187   12.73637    446.5         11         11         11       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 19 0 0 0 0  .13962033  .11032006    -.259007   5.112194  -.8551204    444.4  24.714285  24.714285  24.714285       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 20 0 0 0 0  .10280009  .03857792    1.315789   5.417327  -5.192885    446.5  18.857143  18.857143  18.857143       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 21 0 0 0 0  .10477731  .06951819   -.6659279   6.981791  -5.446809    449.8 -23.714285 -23.714285 -23.714285       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 22 0 0 0 0  .12514341  .11253773  -.25905845   3.468549  -2.798191    453.1  15.642858  15.642858  15.642858       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 23 0 0 0 0   .1389783  .12211073    .8024364   3.939855  -2.940337      456        -22        -22        -22       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 24 0 0 0 0  .13425846  .20348495   -8.599177   4.904412   .9808686    459.3  -9.142858  -9.142858  -9.142858       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 25 0 0 0 2  .13097496  .19758755  -11.494318   3.278737  -7.520661    461.9  -7.428571  -7.428571  -7.428571       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 26 0 0 0 0  .11027313  .06793213  -13.343916    .750807   6.496198    458.4  -22.42857  -22.42857  -22.42857       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 27 0 0 0 0  .10138578  .05326097   -12.09308   1.702493  11.308733      459 -25.666666 -25.666666 -25.666666       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 28 0 0 0 0  .10354044  .06231843  -11.982944    2.62427  11.489053      446      -25.6      -25.6      -25.6       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 29 0 0 0 0  .10951202  .07831217  -3.1524146   4.471075   6.921002 443.8333      -30.8      -30.8      -30.8       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 30 0 0 0 0  .12832037  .09963004   -7.243002    3.95511   8.554109 446.6667 -32.923077 -32.923077 -32.923077       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 31 0 0 0 0  .24272837   .2383429  -18.796183  1.0636818   9.213249    449.4 -13.777778 -13.777778 -13.777778       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 32 0 0 0 0   .1865226  .06799021   -5.720202  -4.917655  10.352312  440.875        -11        -11        -11       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 33 0 0 0 0  .17392263   .0822888   -6.757066  -3.915321   6.087207    437.7  1.8571428  1.8571428  1.8571428       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 34 0 0 0 0  .16098604  .09804758   -5.952703  -1.640913  14.310016    434.5   56.85714   56.85714   56.85714       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 35 0 0 0 0  .18048488  .10144543  -2.1562638 -2.0490317   8.830972    431.9   8.642858   8.642858   8.642858       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 36 0 0 0 0   .1639606  .05994041    .0591549  -3.472554   4.352923      427  -91.64286  -91.64286  -91.64286       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 37 0 0 0 0  .17791907  .09800312    .8607917  -.7626018  2.8068695    427.6  -41.85714  -41.85714  -41.85714       55 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 38 0 0 0 0  .14515519   .0748878  -11.787642   1.505693 -1.2993003    426.9   3.857143   3.857143   3.857143 54.85714 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 39 0 0 0 0  .12205917  .03829762   -4.498424   3.949697 -4.7224736    424.7  3.4285715  3.4285715  3.4285715       54 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 40 0 0 0 0  .11536177  .05215496   -4.228537  -.4854725 -2.0391912      424  24.142857  24.142857  24.142857       54 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 41 0 0 0 0  .12918092  .05360835    -7.55979  -5.120641  -1.340725      424  -.2857143  -.2857143  -.2857143       54 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 42 0 0 0 0  .12251315  .08444925  -13.222935  -.7093179  -2.522333      424  -62.35714  -62.35714  -62.35714       54 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 43 0 0 0 0  .15101977  .11954053   -10.52162   .1764475   2.513386    421.3  1.1428572  1.1428572  1.1428572       54 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 44 0 0 0 0  .12980908  .11192584   -2.949959  2.7912705 -3.6900804    422.6  -12.11111  -12.11111  -12.11111       54 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 45 0 0 0 0  .15403973  .10105447   1.4679753   4.025689 -2.8014135    425.8 -14.419354 -14.419354 -14.419354 53.83871 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 46 0 0 0 0  .14830123  .12049705   -9.624621  3.9267035  -3.425218      430 -12.285714 -12.285714 -12.285714       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 47 0 0 0 0   .1205977   .1277374   -7.448845   1.572005   3.155074      430 -12.714286 -12.714286 -12.714286       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 48 0 0 0 2  .12129763  .06863744   -4.908096  -.7351879   3.503877    432.1    -2.5625    -2.5625    -2.5625       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 49 0 0 0 0  .14866462  .02904085  -4.6788516 -4.2435083  -4.266938    439.8  -9.153846  -9.153846  -9.153846       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 50 0 0 0 0   .1523975 .034321558   -6.161615  -6.955306  -3.746095    439.4  -70.65385  -70.65385  -70.65385       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 51 0 0 0 0  .14847322  .07376538   -3.765381 -2.0130715   2.667142    440.1  -7.714286  -7.714286  -7.714286       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 52 0 0 0 0  .13514954  .04011479   -.8379696  -3.413717 -.29360902    445.4  19.142857  19.142857  19.142857       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 53 0 0 0 0  .15308447  .02521627    .3956952  -3.101467  -2.949688      447 -13.285714 -13.285714 -13.285714       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 54 0 0 0 0  .11929153  .04259476  -13.784514 -1.0296441  -6.182702    444.5  -18.51724  -18.51724  -18.51724       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 55 0 0 0 0  .14234982   .0508466  -3.4338825  -4.226422 -2.4278965      442   67.85714   67.85714   67.85714       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 56 0 0 0 0  .12381519  .04032712   1.3430073   1.236415  -3.035326      441    96.5862    96.5862    96.5862       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 57 0 0 0 0  .07674707  .06596033   -2.916795     3.7848  -.3859877    441.6   41.85714   41.85714   41.85714       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 58 0 0 0 0 .069100924  .03785746   -2.224098 -1.7373855  1.9932425    442.9      -13.5      -13.5      -13.5       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 30 59 0 0 0 0   .0626095  .02207089  -2.7396076  -2.878593   .9423206    443.5 -31.285715 -31.285715 -31.285715       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31  0 0 0 0 0   .0923234  .13921995   -2.821775   5.008524 -1.4296007    444.3  -8.333333  -8.333333  -8.333333       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31  1 0 0 0 0  .26513106  .15275407   -40.98676 -4.2727213   .6479518 440.7143       -9.2       -9.2       -9.2       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31  2 0 0 0 0   .4353674   .1102824   -50.74295 -13.087067  -9.482792      428 -11.333333 -11.333333 -11.333333       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31  3 0 0 0 0  .14610513   .0485546    -4.33766   -2.10147   4.432676    427.7      -27.6      -27.6      -27.6       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31  4 0 0 0 0   .1776704  .07906605  -19.462376  -6.864448  1.9567325    421.7   52.51852   52.51852   52.51852       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31  5 0 0 0 0  .14081326  .09653369   -5.525327   4.114639   5.814667    416.8 -18.793104 -18.793104 -18.793104       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31  6 0 0 0 0  .13699368  .17423844  -11.263557  4.6144567  1.4925103    414.6 -2.2307692 -2.2307692 -2.2307692       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31  7 0 0 0 0  .11995017  .06834296   -2.115493  2.6887355   8.398074    412.3   25.57143   25.57143   25.57143       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31  8 0 0 0 0  .10843392  .09005112   -3.379709   6.614095  1.3354633    412.5   40.28571   40.28571   40.28571 52.78571 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31  9 0 0 0 2  .12898315  .04617303   -.7082447   2.806872    3.73977    411.4 -2.2857144 -2.2857144 -2.2857144       52 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 10 0 0 0 0  .12900414  .11933352   -3.786248   4.648853   4.348421      411  -73.42857  -73.42857  -73.42857       52 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 11 0 0 0 0  .12800984  .13402633   -11.82039   2.487257     .42364    409.1  -20.92857  -20.92857  -20.92857       52 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 12 0 0 0 0   .0959955  .14251143   .08353132   5.751774   3.238669    408.3 -24.142857 -24.142857 -24.142857       52 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 13 0 0 0 0  .08107781  .14001569   .26010227   4.780283  .28486526    409.3   8.571428   8.571428   8.571428       52 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 14 0 0 0 0    .129814  .04938783    5.419729   3.800308  4.0482078    410.1 -3.7142856 -3.7142856 -3.7142856       52 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 15 0 0 0 0  .14999142  .13494013   13.621274  11.523085   6.816957    413.9 -15.714286 -15.714286 -15.714286 52.21429 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 16 0 0 0 0    .308899  .13571303    21.37174   2.605071  1.4519987      413  30.333334  30.333334  30.333334       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 17 0 0 0 0  .23436624  .02846201   18.748241  1.4978542  -.8770841    413.1 -12.571428 -12.571428 -12.571428       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 18 0 0 0 0  .16038187  .14197291   -10.41079   2.771312   4.601961    417.5  -46.23077  -46.23077  -46.23077       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 19 0 0 0 0  .11104074  .15279166    .7547392   4.883463   6.135167    418.5  .59090906  .59090906  .59090906       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 20 0 0 0 0  .13233201  .11365164   -.7559518  2.6671035   9.088449      418      29.25      29.25      29.25       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 21 0 0 0 0  .13833909  .12594475   -6.526359   .4774391   7.387772    418.6  26.586206  26.586206  26.586206 52.72414 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 22 0 0 1 3   .1250001   .0704146   -3.922181    .404988   8.591097      419 -1.5555556 -1.5555556 -1.5555556       52 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 23 0 0 1 3  .14016595   .1008047   -7.737551  2.0833483    7.57153    418.3 -19.931034 -19.931034 -19.931034       52 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 24 0 0 0 0  .13013655  .07136775  -.04321716  1.0568165 -2.0157268    419.6        -48        -48        -48       52 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 25 0 0 0 0  .14110996   .0820423   -.6328651   1.643243  -2.284346      421   6.923077   6.923077   6.923077       52 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 26 0 0 0 0  .13714455  .05932629   -.6049158  -.2135011 -1.1530191    422.7 -17.545454 -17.545454 -17.545454 52.12121 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 27 0 0 0 0  .13173705  .08875018  -2.9799986 -.07249656   .7695053    425.8   3.153846   3.153846   3.153846       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 28 0 0 0 2  .09948588  .13780203   1.0455716    4.50528  4.4139156    435.3   43.46667   43.46667   43.46667       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 29 0 0 0 0  .13515073   .1165372   -3.237815   2.397888   7.693812    442.7    38.0625    38.0625    38.0625       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 30 0 0 0 0    .250157  .07113061   1.0830361  4.1033177   1.249555    441.5     21.875     21.875     21.875       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 31 0 0 0 0  .13894114  .07977459   -8.429343  -2.434651   .2248763    444.5  -2.758621  -2.758621  -2.758621       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 32 0 0 0 0   .1342772  .08059468  -2.3647554 -1.1246979   5.637848    449.8       -7.5       -7.5       -7.5       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 33 0 0 0 0   .1207192  .08752328   -1.244957  .21177545   4.801655    453.3  1.5172414  1.5172414  1.5172414       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 34 0 0 1 3   .1112098  .12575012   -2.402787  4.6734157   9.799796    456.5  -9.444445  -9.444445  -9.444445 53.18518 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 35 0 0 1 3  .10289557  .07343301    4.194681   4.604837  .13871053      462        -13        -13        -13       54 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 36 0 0 1 3   .1339287   .0913543    .7064995  1.8697013   5.955521      468  18.655172  18.655172  18.655172       54 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 37 0 0 0 0  .16400373  .05505309   2.5044374  -1.926487  1.0904437      469     39.125     39.125     39.125 53.83333 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 38 0 0 0 0  .13286053  .08704934    2.470409   .6239137  -.1129851    473.7   27.42857   27.42857   27.42857       53 . . .
"URnFGI_1664431800000_1664433000000" 22917 5 31 39 0 0 0 0  .14078647   .1058961   -2.062343 -2.4474356   1.263971    477.3          1          1          1 53.28571 . . .
end
format %td date_only

I have to select a particular variable in the drowsy alert"1". how to select the data before and after the drowsy alert variable "1" for 5 seconds and figure out the mean of the other variables? which is got selected?

[George Ford]

This may get you started.

g meanaround = cond(drowsy_alert==1, (ear[_n-1]+ear[_n+1])/2,.)

[sudharsan reddy]

This may get you started.

g meanaround = cond(drowsy_alert==1, (ear[_n-1]+ear[_n+1])/2,.)

Dear George ford
thank you for your great help. but with this code, I found it for one second. if I added some more observations I got an error (equation [_n-1] not found).
this is the code I used to do for whole 5 observation
g ear_mean_5sec = cond(drowsy_alert==1, ( ear[_n-1][_n-2][_n-3][_n-4][_n-5]+ear[_n+1][_n+2][_n+3][_n+4][_n+5])/10,.)

[George Ford]

What is the nature of the data? Is this patient data? Can it be xtset?

[sudharsan reddy]

yeah its xtset data.

[sudharsan reddy]

What is the nature of the data? Is this patient data? Can it be xtset?

Its a numerical and continuous data. its related to truck drivers data. and its a panel data

[Hemanshu Kumar]

You might want something like this, where I use the community-contributed command rangestat (available by typing net install rangestat.pkg)

gen double admit_time = mdyhms(month(date_only),day(date_only),year(date_only),admithour,admitmn,second)
format %tc admit_time
rangestat (mean) ear-speed if drowsy_alert == 1, interval(admit_time -5000 5000) by(trip_id)

where I use the interval +/-5000 because admit_time is measured in milliseconds. This code computes the means of all variables from ear to speed, but you can easily change that.

[George Ford]

I think you've got events that don't have 5 observations on either side, so Stata is giving an error.

Try this and see if gives you what you want. I like this better.

I'm not sure what the admitmn variable is, but I use it since the second variable is repeating for the same trip_id.

First thing:

ssc install asrol

Second thing:

asrol ear mar yaw , stat(mean) window(second -5 5) by(admitmn)

What the asrol command does is is create a rolling mean of ear mar yaw (or anything else you add). With this, the mean you want is the value when drowsy_event==1. Note this will include the value at the time of the event. You could adjust by subtracting the value at drowsy_event==1 multiplied by 1/11. Or it may be OK to keep it as is (your call).

You can use the -minimum- option to force a missing if there isn't 5 obs on either side.