PLEASE HELP: Wrangling 15-min interval smart electricity meter data

[Kevin Chadwick]

Hi

Hoping there's a relatively easy fix for this, because I'm new to this kind and size of data set. Also first-time poster, here, so apologies for any transgressions/lack of clarity.

I have smart meter data that provides household electricity consumption for each 15-min period over two to three years.

Working with a sample of just one household's meter data, I'm trying to extract time of day elements (both hh and mm together) from Stata datetime variable.

This is so I can assign a common 'label' to each 15-minute period (e.g. 00:00-00:15) every time it appears in the data set (once per day). Ultimately this will need to be uniquely associated with each individual household (var id in dataex below).

I want to use these 'labels' to generate average/mean values of electricity use for each of these 96 daily 15-minute time intervals.

This will enable me to create average electricity use profiles over a 24-hour period for each household annually/monthly/seasonally/weekday vs weekend etc.

I've attached a two-day sample of my data set (N= 192 obs = 2 x 96 15-min intervals) with variables id (id=1 for all obs as all come from the same household meter in the sample), time, and kwhconsumption.

Happy to provide more information/clarification, if needed.

I'll be grateful for any kind of advice/information/leads anyone can provide.

Kevin

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id double time float kwhconsumption
1 1.8039456e+12  .227
1 1.8039465e+12   .23
1 1.8039474e+12  .193
1 1.8039483e+12  .212
1 1.8039492e+12  .241
1 1.8039501e+12  .188
1  1.803951e+12  .345
1 1.8039519e+12  .519
1 1.8039528e+12  .221
1 1.8039537e+12  .199
1 1.8039546e+12  .225
1 1.8039555e+12  .207
1 1.8039564e+12  .186
1 1.8039573e+12  .219
1 1.8039582e+12  .202
1 1.8039591e+12  .368
1   1.80396e+12  .738
1 1.8039609e+12  .689
1 1.8039618e+12  .513
1 1.8039627e+12  .468
1 1.8039636e+12  .364
1 1.8039645e+12  .347
1 1.8039654e+12  .347
1 1.8039663e+12  .362
1 1.8039672e+12  .366
1 1.8039681e+12  .663
1  1.803969e+12  .736
1 1.8039699e+12  .536
1 1.8039708e+12  .522
1 1.8039717e+12  .429
1 1.8039726e+12  .474
1 1.8039735e+12  .246
1 1.8039744e+12  .134
1 1.8039753e+12  .084
1 1.8039762e+12  .129
1 1.8039771e+12  .121
1  1.803978e+12  .113
1 1.8039789e+12  .128
1 1.8039798e+12  .136
1 1.8039807e+12  .097
1 1.8039816e+12   .26
1 1.8039825e+12  .437
1 1.8039834e+12  .131
1 1.8039843e+12  .398
1 1.8039852e+12  .453
1 1.8039861e+12  .113
1  1.803987e+12  .124
1 1.8039879e+12  .134
1 1.8039888e+12   .16
1 1.8039897e+12  .192
1 1.8039906e+12  .149
1 1.8039915e+12  .128
1 1.8039924e+12  .148
1 1.8039933e+12   .13
1 1.8039942e+12  .149
1 1.8039951e+12  .212
1  1.803996e+12  .968
1 1.8039969e+12  .888
1 1.8039978e+12  .902
1 1.8039987e+12  .892
1 1.8039996e+12  .894
1 1.8040005e+12  .861
1 1.8040014e+12  .894
1 1.8040023e+12   .87
1 1.8040032e+12  .261
1 1.8040041e+12  .113
1  1.804005e+12  .131
1 1.8040059e+12  .086
1 1.8040068e+12  .143
1 1.8040077e+12  .113
1 1.8040086e+12  .105
1 1.8040095e+12  .688
1 1.8040104e+12  .835
1 1.8040113e+12  .851
1 1.8040122e+12  .793
1 1.8040131e+12  .599
1  1.804014e+12  .568
1 1.8040149e+12    .7
1 1.8040158e+12   .92
1 1.8040167e+12  .758
1 1.8040176e+12  .558
1 1.8040185e+12  .539
1 1.8040194e+12  .557
1 1.8040203e+12 1.105
1 1.8040212e+12 1.265
1 1.8040221e+12 1.019
1  1.804023e+12  .494
1 1.8040239e+12  .308
1 1.8040248e+12  .322
1 1.8040257e+12  .306
1 1.8040266e+12  .298
1 1.8040275e+12  .285
1 1.8040284e+12  .261
1 1.8040293e+12  .257
1 1.8040302e+12   .23
1 1.8040311e+12  .248
1  1.804032e+12  .255
1 1.8040329e+12  .208
1 1.8040338e+12  .235
1 1.8040347e+12  .244
1 1.8040356e+12  .215
1 1.8040365e+12  .233
1 1.8040374e+12  .209
1 1.8040383e+12  .224
1 1.8040392e+12  .202
1 1.8040401e+12  .224
1  1.804041e+12   .22
1 1.8040419e+12  .191
1 1.8040428e+12  .231
1 1.8040437e+12  .196
1 1.8040446e+12  .209
1 1.8040455e+12  .515
1 1.8040464e+12   1.3
1 1.8040473e+12  .954
1 1.8040482e+12  .916
1 1.8040491e+12  .872
1   1.80405e+12  .658
1 1.8040509e+12  .498
1 1.8040518e+12  .405
1 1.8040527e+12  .389
1 1.8040536e+12  .324
1 1.8040545e+12  .379
1 1.8040554e+12  .367
1 1.8040563e+12  .714
1 1.8040572e+12  .522
1 1.8040581e+12  .568
1  1.804059e+12  .832
1 1.8040599e+12  .398
1 1.8040608e+12  .127
1 1.8040617e+12  .119
1 1.8040626e+12  .139
1 1.8040635e+12  .087
1 1.8040644e+12  .143
1 1.8040653e+12  .123
1 1.8040662e+12  .104
1 1.8040671e+12  .137
1  1.804068e+12   .12
1 1.8040689e+12  .112
1 1.8040698e+12  .138
1 1.8040707e+12   .12
1 1.8040716e+12  .123
1 1.8040725e+12  .115
1 1.8040734e+12  .159
1 1.8040743e+12  .082
1 1.8040752e+12  .153
1 1.8040761e+12  .121
1  1.804077e+12   .12
1 1.8040779e+12  .119
1 1.8040788e+12  .142
1 1.8040797e+12  .115
1 1.8040806e+12  .118
1 1.8040815e+12  .852
1 1.8040824e+12  .955
1 1.8040833e+12 1.247
1 1.8040842e+12  .946
1 1.8040851e+12  .862
1  1.804086e+12  .877
1 1.8040869e+12  .871
1 1.8040878e+12  .863
1 1.8040887e+12  .872
1 1.8040896e+12  .836
1 1.8040905e+12  .873
1 1.8040914e+12  .254
1 1.8040923e+12   .17
1 1.8040932e+12  .123
1 1.8040941e+12  .164
1  1.804095e+12  .132
1 1.8040959e+12  .153
1 1.8040968e+12  .131
1 1.8040977e+12  .125
1 1.8040986e+12  .163
1 1.8040995e+12  .131
1 1.8041004e+12  .161
1 1.8041013e+12  .146
1 1.8041022e+12   .16
1 1.8041031e+12  .126
1  1.804104e+12  .185
1 1.8041049e+12  .689
1 1.8041058e+12  .879
1 1.8041067e+12  .831
1 1.8041076e+12  .881
1 1.8041085e+12  .821
1 1.8041094e+12  .862
1 1.8041103e+12  .595
1 1.8041112e+12  .574
1 1.8041121e+12  .566
1  1.804113e+12  .516
1 1.8041139e+12  .559
1 1.8041148e+12  .546
1 1.8041157e+12  .954
1 1.8041166e+12 1.037
1 1.8041175e+12  .355
end
format %tcDD/NN/CCYY_HH:MM time

[Jorrit Gosens]

You could do:

gen hour = hh(time)
gen minute = mm(time)
egen something = group(hour minute)

See also https://www.stata.com/manuals13/ddatetime.pdf

With multiple ids and multiple days of data per id, using his code would give the same value for 'something' for each occurrence of e.g., 00:00

[Nick Cox]

Thanks for the data example. This technique may help:

gen period = 4 * hh(time) + mm(time)/15  
forval p = 0/95 { 
   local hour : di %02.0f floor(`p'/4) 
   local min : di %02.0f 15 * mod(`p', 4) 
   label def period `p' "`hour':`min'", modify 
}

label val period period 

tab period 
 
egen double total = total(kwh), by(period)

line total period, xla(0(8)88 96 "00:00", valuelabel) sort scheme(s1color)

Alternatively,

gen wanted = mod(time, 24*60*60000)
format wanted %tcHH:MM
line total wanted, xla(0(`=2 * 60 * 60000')`=22 * 60 * 60000' `=24 * 60 * 60000' "00:00") sort scheme(s1color)

[Kevin Chadwick]

Hi Jorrit

Thanks very much. This does exactly what you said it will, creating a sequence of 1-96 for each 15-min period each day.

I may get back to you if I can't work out how it works by myself.

[Kevin Chadwick]

Hi Nick

Thank you very much. Both techniques work brilliantly (as I'm sure you already know!)

The only change I've made is to generate a mean kwhconsumption variable by period instead of the total - same chart shape, of course.

There's a lot in the code for me to research and learn from, so I'll get onto that so I can save future questions for new challenges I will no doubt encounter with this data set.

What a great resource you and the forum are! What a great way to learn!