Work (keep, drop, merge etc..) with data with the format %tcDDmonCCYY_HH:MM:SS

[Valentin FB]

Hello everybody,

I'm quite new on Stata and I'm struggling with how to work with data which has %tcDDmonCCYY_HH:MM:SS format.


Below is part of my data that I have: "t" is the date (in %tcDDmonCCYY_HH:MM:SS format), "kwh" is the hourly electricity consumption of a households, and "ni" is the ID of the household.


What I try to do is:


1) To be able to reduce my sample by using tools as "drop" or "keep".

I have already tried some things:

drop if t > mdy(1,1,2016) or
drop if t >= td(01may2013) but it was not working, I think I cannot use these with this format.

Which command should I use ?



2) I also would like to be able to put together these hourly data into weekly data for each of the household.
At the end, I would like to have the electricity consumption per week, per household.
Which command could I use ?


I thank you for your help, and don't hesitate if further details/explanation are needed.

Valentin

* Example generated by -dataex-. For more info, type help dataex
clear
input long ni double(t kwh)
13103610 1.6925796e+12               .553
13103610 1.6925832e+12               .267
13103610 1.6925868e+12 .29400000000000004
13103610 1.6925904e+12 .29300000000000004
13103610  1.692594e+12               .262
13103610 1.6925976e+12 .28900000000000003
13103610 1.6926012e+12               .344
13103610 1.6926048e+12               .343
13103610 1.6926084e+12               1.33
13103610  1.692612e+12               .805
13103610 1.6926156e+12               .818
13103610 1.6926192e+12  .6990000000000001
13103610 1.6926228e+12               .326
13103610 1.6926264e+12               .372
13103610   1.69263e+12               .323
13103610 1.6926336e+12 .29200000000000004
13103610 1.6926372e+12 .29700000000000004
13103610 1.6926408e+12  .7810000000000001
13103610 1.6926444e+12  .8620000000000001
13103610  1.692648e+12  .6020000000000001
13103610 1.6926516e+12  .6150000000000001
13103610 1.6926552e+12               .612
13103610 1.6926588e+12  .5980000000000001
13103610 1.6926624e+12               .651
13103610  1.692666e+12 .41600000000000004
13103610 1.6926696e+12               .251
13103610 1.6926732e+12               .259
13103610 1.6926768e+12                .26
13103610 1.6926804e+12               .276
13103610  1.692684e+12               .273
13103610 1.6926876e+12               .322
13103610 1.6926912e+12              1.006
13103610 1.6926948e+12 1.0050000000000001
13103610 1.6926984e+12 .29400000000000004
13103610  1.692702e+12               .504
13103610 1.6927056e+12 1.7469999999999999
13103610 1.6927092e+12 .39299999999999996
13103610 1.6927128e+12               .259
13103610 1.6927164e+12 .28400000000000003
13103610   1.69272e+12 .30000000000000004
13103610 1.6927236e+12 .41300000000000003
13103610 1.6927272e+12              1.163
13103610 1.6927308e+12 1.0100000000000002
13103610 1.6927344e+12               .429
13103610  1.692738e+12  .5970000000000001
13103610 1.6927416e+12  .5790000000000001
13103610 1.6927452e+12               .643
13103610 1.6927488e+12               .635
13103610 1.6927524e+12               .473
13103610  1.692756e+12               .252
13103610 1.6927596e+12               .249
13103610 1.6927632e+12               .276
13103610 1.6927668e+12                .23
13103610 1.6927704e+12               .246
13103610  1.692774e+12               .287
13103610 1.6927776e+12               .263
13103610 1.6927812e+12               .251
13103610 1.6927848e+12               .279
13103610 1.6927884e+12 2.5020000000000002
13103610  1.692792e+12 2.0370000000000004
13103610 1.6927956e+12 .41300000000000003
13103610 1.6927992e+12               .381
13103610 1.6928028e+12               .404
13103610 1.6928064e+12               .372
13103610   1.69281e+12                 .4
13103610 1.6928136e+12               .308
13103610 1.6928172e+12 .41700000000000004
13103610 1.6928208e+12 .35400000000000004
13103610 1.6928244e+12 .41500000000000004
13103610  1.692828e+12  .6910000000000001
13103610 1.6928316e+12               .639
13103610 1.6928352e+12               .622
13103610 1.6928388e+12  .6320000000000001
13103610 1.6928424e+12                .56
13103610  1.692846e+12               .361
13103610 1.6928496e+12 .30000000000000004
13103610 1.6928532e+12               .293
13103610 1.6928568e+12               .301
13103610 1.6928604e+12               .374
13103610  1.692864e+12               .384
13103610 1.6928676e+12              1.009
13103610 1.6928712e+12 1.5350000000000001
13103610 1.6928748e+12  .8500000000000001
13103610 1.6928784e+12 1.4020000000000001
13103610  1.692882e+12 .40800000000000003
13103610 1.6928856e+12               .307
13103610 1.6928892e+12  .5810000000000001
13103610 1.6928928e+12               .652
13103610 1.6928964e+12  .6090000000000001
13103610    1.6929e+12               .864
13103610 1.6929036e+12              1.246
13103610 1.6929072e+12 .47000000000000003
13103610 1.6929108e+12               .301
13103610 1.6929144e+12               .281
13103610  1.692918e+12               .308
13103610 1.6929216e+12               .444
13103610 1.6929252e+12  .6060000000000001
13103610 1.6929288e+12 .30700000000000005
13103610 1.6929324e+12 .29600000000000004
13103610  1.692936e+12 .29300000000000004
end
format %tc_DDmonCCYY_HH:MM:SS t

[Nick Cox]

The absolute fundamental here is that datetimes are measured in milliseconds with origin the start of 1960.

It is misleading to say

drop if t > mdy(1,1,2016)

didn't work because it is perfectly legal but it will do absolutely nothing to your data unless you have datetimes that are from the first few seconds of 1960 or earlier.

Let's check that daily date

. di mdy(1,1,2016)
20454

Hence presented as a date-time it corresponds to a time 20.454 seconds after the start of 1960.

Otherwise put, you're mixing units (days and milliseconds) which are a factor of 1000 * 60 * 24 apart, and Stata has no way to know that there should be a conversion factor.

However,

. di %15.0f clock("1 Jan 2016", "DMY")
  1767225600000

is exactly the constant you need. Naturally you should not and need not type in the integer constant. The point is that it is vastly different from 20454 and is indeed the product of 20454 and the conversion factor expressing days in milliseconds.


.

 di %15.0f 20454 * 24 * 60 * 60000
  1767225600000

I pass on your second question -

- it can't be answered without a definition of "week" --

beyond the very minute detail that kWh is the correct unit as Watt should be recognised as the person concerned.

[Clyde Schechter]

1) This is a units problem. Stata date variables are denominated in days, whereas date-time (clock) variables are denominated in milliseconds. So attempting to compare a -td()- or -mdy()- expression (days) to a clock variable (milliseconds) produces incorrect results. Use

drop if t > tc(21aug2013 00:00:00) // OR WHATEVER DATE)

instead.

2) Weeks are a very problematic unit of time because they are not commensurable with other commonly used units like years and months. Simple code can get you Stata weekly date variables from t:

gen weekly_variable = wofd(dofc(t))
format weekly_variable %tw

But Stata's weekly date system may not be suitable for what you want to do. In particular, Stata's weekly date system begins a new week on 1 January of each year. And the 52nd week is always extended all the way to 31 December, so it will contain more than 7 days. People typically prefer to think of a week as 7 consecutive days beginning with a Sunday or Monday. Which is nice, except then it is not possible to define the month or year of that week. Anyway, if Stata's conception of weeks is not suitable for your purposes, post back and explain how you would like to define a week, and code for that can be crafted.

Added: Crossed with #2.

[Nick Cox]

To add to the circle, there is much discussions of weeks in Stataland, but it is best to hear your exact definition of a week (e.g. it starts on Sunday, or Monday, or whatever) and then code should ensure.

.

 search week, sj

Search of official help files, FAQs, Examples, and Stata Journals

SJ-22-2 dm0107_1  . . .  Erratum: Stata tip 145: Numbering weeks within months
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q2/22   SJ 22(2):465--466                                (no commands)
        errata for tip on numbering weeks within months

SJ-22-1 dm0107  . . . . . . . . . Stata tip 145: Numbering weeks within months
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q1/22   SJ 22(1):224--230                                (no commands)
        tip on numbering weeks within months

SJ-19-3 dm0100  . . . . . . . . . .  Speaking Stata: The last day of the month
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q3/19   SJ 19(3):719--728                                (no commands)
        discusses three related problems about getting the last day
        of the month in a new variable

SJ-12-4 dm0065_1  . . . . . Stata tip 111: More on working with weeks, erratum
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q4/12   SJ 12(4):765                                     (no commands)
        lists previously omitted key reference

SJ-12-3 dm0065  . . . . . . . . . .  Stata tip 111: More on working with weeks
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q3/12   SJ 12(3):565--569                                (no commands)
        discusses how to convert data presented in yearly and weekly
        form to daily dates and how to aggregate such data to months
        or longer intervals

SJ-10-4 dm0052  . . . . . . . . . . . . . . . . Stata tip 68: Week assumptions
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q4/10   SJ 10(4):682--685                                (no commands)
        tip on Stata's solution for weeks and on how to set up
        your own alternatives given different definitions of the
        week

[Valentin FB]

Hello,

Thanks a lot for your answers ! It helped me a lot.
Thanks to your help I was able to do what I wanted !

Have a nice day

Valentin