Creating discrete time variable for panel data

[Anton Ivanov]

Hello forum participants,

I am working with auction data (see dataex for three exemplar id's below). Each auction generally has a duration of 7 days (approximately 10,080 minutes with some auctions running a little over depending on the time of the last bid). During the time of the auction, participants may either place a bid (bid = 1) or leave a comment (bid = 0). Given that the timing of the bids and comments is very heterogeneous, I would like to create a discrete time variable that can be uniform for each auction regardless of the date and starting hour. For example, each auction starts at time period = 1 with a step of 6 hours. This way, with each auction running for 7 days, for each id time periods 1-2-3-4 represent the first day, 5-6-7-8 represent the second day, ..., and 25-26-27-28 represent the seventh day. What would be the correct way of coding it given the exemplar data below?

* Example generated by -dataex-. For more info, type help dataex
clear
input float id double dts byte bid long bid_amount float increment
1 1909742581000 1  1997     .
1 1909742582000 0     .     .
1 1909742640000 0     .     .
1 1909742760000 0     .     .
1 1909742940000 0     .     .
1  1.909743e+12 0     .     .
1 1909743480000 0     .     .
1 1909743720000 0     .     .
1 1.9097439e+12 0     .     .
1 1909744560000 0     .     .
1 1.9097454e+12 0     .     .
1 1909745880000 0     .     .
1 1.9097463e+12 0     .     .
1 1909746660000 1  2097   100
1 1909746720000 0     .     .
1 1909747080000 0     .     .
1 1909747140000 0     .     .
1 1.9097472e+12 0     .     .
1 1909747321000 1  3333  1236
1 1909747322000 0     .     .
1 1909748040000 0     .     .
1 1909748160000 0     .     .
1 1909748280000 0     .     .
1 1909748520000 0     .     .
1 1909749360000 1 17760 14427
1 1909749541000 0     .     .
1 1909749542000 0     .     .
1 1.9097496e+12 0     .     .
1 1.9097514e+12 0     .     .
1 1909751760000 0     .     .
1 1909753020000 0     .     .
1 1909753080000 0     .     .
1 1909756620000 0     .     .
1 1909756920000 0     .     .
1 1909757040000 0     .     .
1 1.9097571e+12 0     .     .
1 1909757520000 0     .     .
1 1909762680000 0     .     .
1 1909764180000 0     .     .
1 1909768560000 0     .     .
1 1909768680000 0     .     .
1 1909769220000 0     .     .
1 1.9097709e+12 0     .     .
1 1909772220000 0     .     .
1 1909773360000 0     .     .
1 1909784220000 0     .     .
1 1909788180000 0     .     .
1 1909789920000 0     .     .
1 1909805520000 1 25000  7240
1 1909816020000 0     .     .
1 1909823580000 0     .     .
1 1.9098264e+12 0     .     .
1 1909826640000 0     .     .
1 1909830120000 0     .     .
1 1909837140000 0     .     .
1 1.9098399e+12 0     .     .
1 1909842480000 0     .     .
1 1909843140000 0     .     .
1 1909850160000 0     .     .
1 1909858560000 0     .     .
1 1909859880000 0     .     .
1 1909864260000 0     .     .
1 1909911420000 0     .     .
1 1.9099161e+12 0     .     .
1 1909916820000 0     .     .
1 1909918560000 0     .     .
1 1909919580000 0     .     .
1 1909925160000 0     .     .
1 1909928520000 0     .     .
1 1909957560000 0     .     .
1 1909984920000 0     .     .
1 1909994820000 0     .     .
1 1909999320000 0     .     .
1 1910001720000 0     .     .
1 1910002980000 0     .     .
1 1910003220000 0     .     .
1 1910014620000 0     .     .
1 1910028240000 0     .     .
1 1910086560000 0     .     .
1 1910114460000 0     .     .
1 1910235540000 0     .     .
1 1.9102632e+12 0     .     .
1 1910263440000 0     .     .
1 1.9102683e+12 0     .     .
1 1910269681000 0     .     .
1 1910269682000 0     .     .
1 1910269980000 0     .     .
1 1910270640000 0     .     .
1 1910273280000 0     .     .
1 1910280360000 0     .     .
1 1910336280000 0     .     .
1 1.9103364e+12 0     .     .
1 1.9103379e+12 0     .     .
1 1910341680000 0     .     .
1 1910342460000 0     .     .
1 1910343060000 0     .     .
1 1.9103463e+12 0     .     .
1 1910346720000 0     .     .
1 1.9103535e+12 0     .     .
1 1910353860000 0     .     .
1 1910354760000 0     .     .
1 1.9103553e+12 0     .     .
1 1910355420000 0     .     .
1 1910355720000 0     .     .
1 1910355840000 0     .     .
1 1.9103562e+12 0     .     .
1 1.9103565e+12 0     .     .
1 1.9103568e+12 0     .     .
1 1910357280000 0     .     .
1 1.9103577e+12 0     .     .
1 1910357940000 0     .     .
1 1910358180000 0     .     .
1 1910358420000 0     .     .
1 1910358541000 0     .     .
1 1910358542000 0     .     .
1 1910358601000 0     .     .
1 1910358602000 0     .     .
1 1910358603000 1     .     .
2 1727231760000 0     .     .
2 1727232780000 0     .     .
2 1727237040000 0     .     .
2 1727241060000 0     .     .
2 1727246280000 0     .     .
2 1727249040000 0     .     .
2 1727249460000 0     .     .
2 1727250060000 0     .     .
2 1727250360000 0     .     .
2 1727250420000 0     .     .
2 1727251260000 0     .     .
2 1727251560000 0     .     .
2 1727251860000 0     .     .
2 1727251920000 0     .     .
2 1727252460000 0     .     .
2  1.727253e+12 0     .     .
2 1727253240000 0     .     .
2 1727253360000 0     .     .
2 1727254020000 0     .     .
2 1727254260000 0     .     .
2 1.7272551e+12 0     .     .
2 1.7272557e+12 0     .     .
2  1.727256e+12 0     .     .
2 1727257020000 0     .     .
2 1727257920000 0     .     .
2 1.7272584e+12 0     .     .
2 1727258760000 0     .     .
2 1727260620000 0     .     .
2 1.7272608e+12 0     .     .
2 1727261580000 0     .     .
2 1727261760000 0     .     .
2 1727261940000 0     .     .
2 1727262960000 0     .     .
2 1727263081000 0     .     .
2 1727263082000 0     .     .
2 1727263140000 0     .     .
2 1.7272635e+12 0     .     .
2 1.7272641e+12 0     .     .
2 1727264220000 0     .     .
2 1727265360000 1  2000     .
2 1727265420000 0     .     .
2 1727266560000 0     .     .
2 1727267520000 0     .     .
2 1727267640000 0     .     .
2 1.7272689e+12 0     .     .
2 1727269140000 0     .     .
2 1.7272695e+12 0     .     .
2 1727271180000 0     .     .
2 1.7272716e+12 0     .     .
2 1727271660000 0     .     .
2 1727273220000 0     .     .
2 1727273880000 0     .     .
2 1727274240000 0     .     .
2 1727277660000 1  6199  4199
2 1727278980000 0     .     .
2 1727279520000 0     .     .
2 1.7272818e+12 0     .     .
2 1727282040000 0     .     .
2 1727282460000 0     .     .
2 1727282580000 0     .     .
2 1727282880000 0     .     .
2 1.7272833e+12 0     .     .
2 1727284440000 0     .     .
2 1727284620000 0     .     .
2 1727285940000 0     .     .
2 1727287260000 0     .     .
2 1.7272884e+12 0     .     .
2 1727288760000 0     .     .
2 1727289840000 0     .     .
2 1727290260000 0     .     .
2 1727292540000 0     .     .
2 1.7272944e+12 0     .     .
2 1.7272959e+12 0     .     .
2 1727296620000 0     .     .
2 1727296740000 0     .     .
2 1727296980000 0     .     .
2 1727298540000 0     .     .
2 1727302201000 0     .     .
2 1727302202000 0     .     .
2 1727307360000 0     .     .
2 1727316120000 0     .     .
2 1727317440000 0     .     .
2 1.7273217e+12 0     .     .
2 1727338560000 0     .     .
2 1.7273427e+12 0     .     .
2 1727345040000 0     .     .
2 1727345460000 0     .     .
2 1727350860000 0     .     .
2 1727353380000 0     .     .
2 1.7273547e+12 0     .     .
2  1.727358e+12 0     .     .
2 1727359140000 0     .     .
2 1727359740000 0     .     .
2 1727359921000 0     .     .
2 1727359922000 0     .     .
2 1727360040000 0     .     .
2 1727366520000 0     .     .
2 1727367420000 0     .     .
2   1.72737e+12 0     .     .
2 1727373360000 0     .     .
2 1727374020000 0     .     .
2 1727374140000 0     .     .
2 1727374620000 0     .     .
2 1727384520000 0     .     .
2 1.7273874e+12 0     .     .
2 1727387940000 0     .     .
2 1727388060000 0     .     .
2 1727389620000 0     .     .
2 1727397840000 0     .     .
2 1.7273985e+12 0     .     .
2 1727399760000 0     .     .
2 1727427840000 0     .     .
2 1727427960000 0     .     .
2 1727434380000 0     .     .
2 1727435280000 0     .     .
2 1.7274429e+12 0     .     .
2 1727452140000 0     .     .
2 1.7274534e+12 0     .     .
2 1727516760000 0     .     .
2 1727520240000 0     .     .
2 1727526360000 0     .     .
2 1727553120000 0     .     .
2 1727554020000 0     .     .
2 1727555040000 0     .     .
2 1727563440000 0     .     .
2 1727569080000 0     .     .
2 1727571540000 0     .     .
2 1727584680000 0     .     .
2 1727589180000 0     .     .
2 1727604120000 0     .     .
2 1.7276151e+12 0     .     .
2 1727618160000 0     .     .
2 1727629920000 0     .     .
2 1727683320000 0     .     .
2 1727713140000 0     .     .
2 1.7277132e+12 1  6356   157
2 1727716560000 1  6500   144
2 1.7277177e+12 1  6700   200
2 1727723220000 0     .     .
2 1727723460000 0     .     .
2 1.7277258e+12 0     .     .
2 1.7277261e+12 0     .     .
2 1.7277264e+12 0     .     .
2 1727740020000 0     .     .
2 1727741880000 1  6800   100
2 1727745240000 0     .     .
2 1727768460000 0     .     .
2  1.727772e+12 0     .     .
2 1727775780000 0     .     .
2  1.727778e+12 0     .     .
2 1727778840000 0     .     .
2 1727779080000 0     .     .
2 1727779440000 0     .     .
2 1727780460000 1  6900   100
2 1727782740000 0     .     .
2 1727786520000 0     .     .
2 1727786760000 0     .     .
2 1727788860000 1  7000   100
2 1727790060000 0     .     .
2 1727791020000 0     .     .
2 1.7277921e+12 0     .     .
2 1727794440000 1  7100   100
2 1727794680000 0     .     .
2 1.7277954e+12 1  7200   100
2  1.727796e+12 0     .     .
2 1727798880000 0     .     .
2  1.727799e+12 0     .     .
2 1727800080000 0     .     .
2 1727800140000 1  7300   100
2 1727800920000 0     .     .
2 1727801521000 1  7400   100
2 1727801522000 0     .     .
2 1.7278017e+12 1  7500   100
2 1727801940000 1  7700   200
2  1.727802e+12 0     .     .
2 1727802060000 1  7800   100
2 1727802180000 1  7900   100
2 1727802420000 0     .     .
2 1.7278038e+12 0     .     .
3 1729057260000 0     .     .
3  1.729065e+12 0     .     .
3 1729065420000 0     .     .
3 1729066620000 0     .     .
3 1729066680000 0     .     .
3 1.7290671e+12 0     .     .
3 1729068421000 0     .     .
3 1729068422000 0     .     .
3 1729068660000 0     .     .
3 1729068840000 0     .     .
3 1729069320000 0     .     .
3 1729069740000 0     .     .
3 1729072741000 0     .     .
3 1729072742000 0     .     .
3 1729072860000 0     .     .
3 1729074960000 0     .     .
3 1729075080000 0     .     .
3 1.7290755e+12 1     1     .
3 1729075920000 0     .     .
3 1729076760000 1  2500  2499
3 1729077120000 0     .     .
3 1729077540000 0     .     .
3 1729078980000 0     .     .
3 1729079220000 0     .     .
3 1729079640000 0     .     .
3 1.7290809e+12 0     .     .
3 1729082340000 0     .     .
3 1729082580000 0     .     .
3 1729083660000 0     .     .
3 1.7290857e+12 0     .     .
3 1729087140000 0     .     .
3 1729087260000 0     .     .
3 1729088280000 0     .     .
3 1729089240000 0     .     .
3 1729097340000 0     .     .
3 1729098060000 0     .     .
3 1729100280000 0     .     .
3 1729100940000 0     .     .
3 1.7291025e+12 0     .     .
3 1.7291046e+12 0     .     .
3 1.7291076e+12 0     .     .
3 1.7291085e+12 0     .     .
3 1729109520000 1  2600   100
3   1.72911e+12 0     .     .
3 1729110060000 0     .     .
3 1729117620000 0     .     .
3 1729120080000 0     .     .
3 1729121521000 0     .     .
3 1729121522000 0     .     .
3 1729122301000 0     .     .
3 1729122302000 0     .     .
3 1.7291229e+12 0     .     .
3 1729124040000 0     .     .
3 1729128480000 0     .     .
3 1729130460000 0     .     .
3 1729176540000 0     .     .
3 1729176660000 0     .     .
3 1729185780000 0     .     .
3 1729187520000 0     .     .
3 1.7291922e+12 0     .     .
3 1729196460000 0     .     .
3    1.7292e+12 0     .     .
3 1729204740000 0     .     .
3 1729205580000 0     .     .
3 1729272420000 0     .     .
3 1729336680000 0     .     .
3 1.7293401e+12 0     .     .
3 1.7294253e+12 0     .     .
3 1.7294256e+12 0     .     .
3 1729425960000 0     .     .
3 1729456260000 0     .     .
3 1729539660000 0     .     .
3 1729543140000 0     .     .
3 1729596780000 1  2700   100
3 1729607640000 0     .     .
3 1.7296149e+12 1  2800   100
3 1729617720000 1  2900   100
3 1729618560000 1  3200   300
3 1729618920000 1  3500   300
3 1729618981000 1  3700   200
3 1729618982000 1  4000   300
3 1729618983000 0     .     .
3 1729619041000 1  4500   500
3 1729619042000 1  4600   100
3 1729619580000 0     .     .
3 1.7296197e+12 0     .     .
end
format %tc dts

[Clyde Schechter]

by id (dts), sort: gen int period = 1 + floor((dts-dts[1])/msofhours(6))

[Anton Ivanov]

Clyde Schechter Thank you very much for offering a solution. Please let me ask a follow-up question in the same thread. Once I got the period variable, what would be the correct way of calculating the following two measurements:

- For each id, average of comment_likes up to given time period (e.g., mean of period 1, then mean of periods 1-2, then mean of periods 1-2-3, etc.)
- For each id, incremental difference between the average of comment_likes at a given time period and average of all previous time periods?

Note, there are missing time periods (since there were no bids or comments) and comment likes (as the row may be a bid or comment received no likes).

* Example generated by -dataex-. For more info, type help dataex
clear
input float id int(period comment_likes) byte bid
1  1  . 1
1  1  5 0
1  1  2 0
1  1  7 0
1  1  1 0
1  1  5 0
1  1  2 0
1  1  3 0
1  1  2 0
1  1  3 0
1  1  1 0
1  1  3 0
1  1  3 0
1  1  . 1
1  1  2 0
1  1  6 0
1  1  1 0
1  1  0 0
1  1  . 1
1  1  1 0
1  1  1 0
1  1  1 0
1  1  6 0
1  1  1 0
1  1  . 1
1  1  4 0
1  1  4 0
1  1  1 0
1  1 13 0
1  1  1 0
1  1  1 0
1  1 25 0
1  1  3 0
1  1  9 0
1  1  0 0
1  1  0 0
1  1  2 0
1  1  2 0
1  1  4 0
1  2  3 0
1  2  3 0
1  2  5 0
1  2  1 0
1  2  0 0
1  2  3 0
1  2  0 0
1  3  7 0
1  3  4 0
1  3  . 1
1  4  0 0
1  4  2 0
1  4  3 0
1  4  4 0
1  5  2 0
1  5  4 0
1  5  4 0
1  5  9 0
1  5  1 0
1  5  2 0
1  6  2 0
1  6  2 0
1  6  6 0
1  8  2 0
1  9  3 0
1  9  3 0
1  9  0 0
1  9  0 0
1  9  4 0
1  9  1 0
1 10  2 0
1 12  1 0
1 12  2 0
1 12  4 0
1 12  2 0
1 13  0 0
1 13  3 0
1 13  3 0
1 14  1 0
1 16  3 0
1 18  0 0
1 23  1 0
1 25  2 0
1 25  1 0
1 25  3 0
1 25  0 0
1 25  1 0
1 25  9 0
1 25  3 0
1 25  1 0
1 25  3 0
1 28  2 0
1 28  3 0
1 28  3 0
1 28  2 0
1 28  2 0
1 28  1 0
1 28  1 0
1 28  1 0
1 29  3 0
1 29  2 0
1 29  1 0
1 29  1 0
1 29  6 0
1 29  3 0
1 29  8 0
1 29  4 0
1 29  0 0
1 29  4 0
1 29  1 0
1 29  8 0
1 29 11 0
1 29  4 0
1 29  0 0
1 29  2 0
1 29  4 0
1 29 10 0
1 29  0 0
1 29  . 1
4  1  4 0
4  1  3 0
4  1  1 0
4  1  3 0
4  1  0 0
4  1  4 0
4  1  1 0
4  1  1 0
4  1  . 1
4  1  0 0
4  1  4 0
4  1  2 0
4  1  1 0
4  1  2 0
4  1  2 0
4  2  0 0
4  2  . 1
4  2  0 0
4  2  0 0
4  3  0 0
4  3  0 0
4  4  . 1
4  4  0 0
4  5  0 0
4  5  0 0
4  6  0 0
4  8  0 0
4  9  . 1
4  9  0 0
4  9  1 0
4 12  0 0
4 12  0 0
4 13  0 0
4 14  . 1
4 14  2 0
4 17  1 0
4 17  0 0
4 24  0 0
4 24  0 0
4 24  3 0
4 24  0 0
4 25  0 0
4 25  1 0
4 25  . 1
4 25  0 0
4 25  0 0
4 25  0 0
4 26  . 1
4 26  . 1
4 27  . 1
4 28  0 0
4 28  . 1
4 28  . 1
4 28  0 0
4 28  0 0
4 28  2 0
4 28  0 0
4 28  2 0
4 29  . 1
4 29  0 0
4 29  0 0
4 29  0 0
4 29  0 0
4 29  . 1
4 29  0 0
4 29  1 0
4 29  0 0
4 29  1 0
4 29  . 1
end

[Clyde Schechter]

For each id, incremental difference between the average of comment_likes at a given time period and average of all previous time periods?

In light of what you said you wanted in the previous line, I assume you really meant to ask for the difference between the average of comment_likes at a given time period and the average of all time periods up to given time period. ("All previous periods" would exclude the given time period.)

by id period, sort: egen mean_this_period = mean(comment_likes)
rangestat (mean) mean_up_to_this_period = comment_likes, by(id) ///
    interval(period . 0)
gen difference = mean_this_period - mean_up_to_this_period

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer. It is available from SSC.

[Anton Ivanov]

Clyde Schechter Thank you, Clyde! That worked. -rangestat- seems to be a very handy program. Merry Christmas!