Loading...

Time Series Interpolation, Slices, and Joins

One of the major problems with working with time series models is cleaning the data. Most time series models need to have equally sliced data, and most tools don’t offer an easy way to do this.

Not only that, but missing values can distort predictions. You can fill these gaps with various interpolation methods. Luckily, VerticaPy can easily slice and interpolate time series data. We’ll demonstrate these functions with the Smart Meter datasets.

import verticapy as vp

sm_consumption = vp.read_csv(
    "sm_consumption.csv",
    dtype = {
        "meterID": "Integer",
        "dateUTC": "Timestamp(6)",
        "value": "Float(22)",
    }
)
sm_weather = vp.read_csv(
    "sm_weather.csv",
    dtype = {
        "dateUTC": "Timestamp(6)",
        "temperature": "Float(22)",
        "humidity": "Float(22)",
    }
)
sm_meters = vp.read_csv("sm_meters.csv")

Note

You can let Vertica automatically decide the data type, or you can manually force the data type on any column as seen above.

sm_consumption.head(100)
123
meterID
Int
100%
...
📅
dateUTC
Timestamp(6)
100%
123
value
Float
99%
10...2014-01-02 10:45:000.321
20...2014-01-02 11:15:000.305
30...2014-01-13 20:15:000.34
40...2014-01-18 00:30:000.828
50...2014-01-20 19:30:000.59
60...2014-01-21 12:30:000.327
70...2014-01-24 12:15:000.168
80...2014-01-27 22:45:000.495
90...2014-01-28 06:15:000.056
100...2014-01-28 19:00:001.566
110...2014-01-29 13:00:001.719
120...2014-02-04 03:45:000.045
130...2014-02-04 18:45:000.912
140...2014-02-05 06:45:000.018
150...2014-02-07 11:00:000.868
160...2014-02-07 22:15:001.262
170...2014-02-09 08:30:000.007
180...2014-02-11 19:00:000.094
190...2014-02-12 02:30:000.102
200...2014-02-13 02:45:000.097
210...2014-02-14 13:45:000.033
220...2014-02-15 02:00:000.181
230...2014-02-15 15:00:000.483
240...2014-02-16 00:00:000.195
250...2014-02-17 02:45:000.094
260...2014-02-19 07:00:000.095
270...2014-02-20 19:00:001.208
280...2014-02-23 14:45:000.75
290...2014-02-25 21:30:000.267
300...2014-03-07 15:15:000.415
310...2014-03-08 00:45:000.353
320...2014-03-12 22:30:000.511
330...2014-03-14 20:15:000.124
340...2014-03-16 06:45:000.42
350...2014-03-18 11:15:000.026
360...2014-03-20 19:00:000.239
370...2014-03-26 20:45:000.293
380...2014-04-01 01:00:000.167
390...2014-04-06 21:00:000.253
400...2014-04-11 16:45:000.22
410...2014-04-12 15:30:000.709
420...2014-04-13 09:15:000.192
430...2014-04-16 17:30:000.527
440...2014-04-21 09:45:000.133
450...2014-04-24 20:30:000.244
460...2014-04-26 03:00:000.047
470...2014-04-29 15:15:000.062
480...2014-05-05 07:30:000.182
490...2014-05-06 15:45:000.067
500...2014-05-06 18:45:000.192
510...2014-05-08 12:30:000.054
520...2014-05-14 00:15:000.577
530...2014-05-14 04:15:000.112
540...2014-05-16 16:00:000.064
550...2014-05-17 05:00:000.096
560...2014-05-18 09:30:000.065
570...2014-05-18 23:15:000.604
580...2014-05-19 08:30:000.134
590...2014-05-19 22:30:000.112
600...2014-05-28 01:00:000.284
610...2014-05-30 04:00:000.153
620...2014-06-02 18:15:000.558
630...2014-06-04 03:15:000.139
640...2014-06-06 02:30:000.085
650...2014-06-07 06:30:000.074
660...2014-06-11 08:00:000.092
670...2014-06-12 02:15:000.017
680...2014-06-14 14:00:000.016
690...2014-06-15 18:15:000.194
700...2014-06-16 18:30:000.78
710...2014-06-21 02:45:000.054
720...2014-06-24 05:30:000.048
730...2014-06-24 21:45:000.286
740...2014-06-25 08:00:000.618
750...2014-06-27 14:30:000.243
760...2014-07-02 22:30:000.617
770...2014-07-02 23:15:000.14
780...2014-07-03 13:15:000.976
790...2014-07-04 11:30:000.133
800...2014-07-06 07:00:000.037
810...2014-07-08 10:00:000.014
820...2014-07-10 12:45:000.163
830...2014-07-11 03:45:000.044
840...2014-07-15 04:30:000.068
850...2014-07-16 10:15:000.026
860...2014-07-20 11:45:001.227
870...2014-07-25 11:00:000.038
880...2014-07-25 11:45:000.05
890...2014-07-26 04:15:000.096
900...2014-07-27 10:00:000.157
910...2014-07-29 17:30:000.729
920...2014-07-30 04:15:000.437
930...2014-07-31 02:15:000.068
940...2014-07-31 12:30:002.76
950...2014-08-03 05:00:000.088
960...2014-08-03 23:30:000.748
970...2014-08-04 15:30:000.074
980...2014-08-05 13:15:000.339
990...2014-08-09 06:00:000.026
1000...2014-08-13 08:30:000.043
sm_weather.head(100)
📅
dateUTC
Timestamp(6)
100%
...
123
temperature
Float
100%
123
humidity
Float
100%
12014-01-01 01:30:00...37.4100.0
22014-01-01 02:00:00...39.293.0
32014-01-01 05:30:00...39.287.0
42014-01-01 08:30:00...37.487.0
52014-01-01 10:00:00...37.493.0
62014-01-01 11:30:00...37.493.0
72014-01-01 13:00:00...39.287.0
82014-01-01 15:30:00...39.287.0
92014-01-01 17:00:00...39.287.0
102014-01-01 19:30:00...37.493.0
112014-01-01 20:00:00...39.287.0
122014-01-01 22:30:00...39.287.0
132014-01-01 23:00:00...39.287.0
142014-01-01 23:30:00...39.281.0
152014-01-02 00:00:00...38.076.0
162014-01-02 02:30:00...37.481.0
172014-01-02 03:00:00...37.481.0
182014-01-02 04:00:00...37.481.0
192014-01-02 05:00:00...35.693.0
202014-01-02 05:30:00...37.481.0
212014-01-02 07:30:00...37.481.0
222014-01-02 09:00:00...37.475.0
232014-01-02 09:30:00...37.481.0
242014-01-02 12:30:00...41.070.0
252014-01-02 13:30:00...41.076.0
262014-01-02 14:00:00...41.076.0
272014-01-02 15:00:00...41.076.0
282014-01-02 18:00:00...39.070.0
292014-01-02 18:30:00...37.481.0
302014-01-02 20:00:00...37.481.0
312014-01-02 21:00:00...39.265.0
322014-01-02 23:30:00...39.265.0
332014-01-03 00:00:00...39.048.0
342014-01-03 04:00:00...37.470.0
352014-01-03 05:00:00...37.470.0
362014-01-03 06:00:00...38.050.0
372014-01-03 10:30:00...39.261.0
382014-01-03 11:30:00...39.261.0
392014-01-03 12:00:00...39.048.0
402014-01-03 17:00:00...35.670.0
412014-01-03 22:00:00...33.881.0
422014-01-04 01:30:00...33.881.0
432014-01-04 04:30:00...35.675.0
442014-01-04 12:30:00...35.6100.0
452014-01-04 16:00:00...33.893.0
462014-01-04 16:30:00...33.893.0
472014-01-04 17:30:00...32.0100.0
482014-01-04 18:30:00...32.0100.0
492014-01-04 22:30:00...28.4100.0
502014-01-05 00:00:00...38.083.0
512014-01-05 07:30:00...33.8100.0
522014-01-05 10:30:00...39.281.0
532014-01-05 11:00:00...39.281.0
542014-01-05 12:00:00...41.062.0
552014-01-05 16:30:00...33.887.0
562014-01-05 17:00:00...33.887.0
572014-01-05 19:30:00...32.080.0
582014-01-05 21:00:00...30.280.0
592014-01-05 22:00:00...30.286.0
602014-01-05 22:30:00...32.080.0
612014-01-05 23:30:00...33.875.0
622014-01-06 00:30:00...33.875.0
632014-01-06 02:00:00...32.075.0
642014-01-06 02:30:00...32.080.0
652014-01-06 03:00:00...32.080.0
662014-01-06 04:00:00...33.870.0
672014-01-06 04:30:00...33.865.0
682014-01-06 08:30:00...28.486.0
692014-01-06 11:30:00...32.075.0
702014-01-06 15:00:00...35.660.0
712014-01-06 16:00:00...32.069.0
722014-01-06 16:30:00...30.275.0
732014-01-06 19:30:00...28.486.0
742014-01-06 22:00:00...28.480.0
752014-01-06 22:30:00...26.686.0
762014-01-07 01:00:00...28.480.0
772014-01-07 02:30:00...28.480.0
782014-01-07 08:00:00...28.486.0
792014-01-07 09:30:00...30.280.0
802014-01-07 11:30:00...33.875.0
812014-01-07 13:00:00...35.675.0
822014-01-07 13:30:00...37.465.0
832014-01-07 14:00:00...37.465.0
842014-01-07 15:00:00...37.465.0
852014-01-07 15:30:00...35.675.0
862014-01-07 17:30:00...32.080.0
872014-01-07 18:00:00...30.085.0
882014-01-07 18:30:00...32.080.0
892014-01-07 20:30:00...30.286.0
902014-01-07 21:00:00...32.080.0
912014-01-07 21:30:00...28.493.0
922014-01-07 22:30:00...30.286.0
932014-01-08 03:30:00...35.681.0
942014-01-08 05:00:00...35.693.0
952014-01-08 06:00:00...33.090.0
962014-01-08 06:30:00...32.093.0
972014-01-08 08:30:00...32.093.0
982014-01-08 09:00:00...33.887.0
992014-01-08 10:30:00...37.475.0
1002014-01-08 12:30:00...41.070.0

Our first dataset has a lot of gaps, so let’s slice and interpolate the energy consumption every 30 minutes.

interpolate = sm_consumption.interpolate(
    ts = "dateUTC",
    rule = "30 minutes",
    method = {"value": "linear"},
    by = ["meterID"],
)
interpolate.head(100)
📅
dateUTC
Timestamp(29)
100%
...
123
meterID
Integer
100%
123
value
Float(22)
99%
12014-01-01 03:00:00...10.081
22014-01-01 03:30:00...10.0977719298245614
32014-01-01 04:00:00...10.114543859649123
42014-01-01 04:30:00...10.131315789473684
52014-01-01 05:00:00...10.148087719298246
62014-01-01 05:30:00...10.164859649122807
72014-01-01 06:00:00...10.181631578947368
82014-01-01 06:30:00...10.19840350877193
92014-01-01 07:00:00...10.215175438596491
102014-01-01 07:30:00...10.231947368421053
112014-01-01 08:00:00...10.248719298245614
122014-01-01 08:30:00...10.265491228070175
132014-01-01 09:00:00...10.282263157894737
142014-01-01 09:30:00...10.299035087719298
152014-01-01 10:00:00...10.31580701754386
162014-01-01 10:30:00...10.332578947368421
172014-01-01 11:00:00...10.349350877192982
182014-01-01 11:30:00...10.366122807017544
192014-01-01 12:00:00...10.382894736842105
202014-01-01 12:30:00...10.399666666666667
212014-01-01 13:00:00...10.416438596491228
222014-01-01 13:30:00...10.433210526315789
232014-01-01 14:00:00...10.449982456140351
242014-01-01 14:30:00...10.466754385964912
252014-01-01 15:00:00...10.483526315789474
262014-01-01 15:30:00...10.500298245614035
272014-01-01 16:00:00...10.517070175438597
282014-01-01 16:30:00...10.533842105263158
292014-01-01 17:00:00...10.550614035087719
302014-01-01 17:30:00...10.536
312014-01-01 18:00:00...10.49
322014-01-01 18:30:00...10.444
332014-01-01 19:00:00...10.398
342014-01-01 19:30:00...10.364969696969697
352014-01-01 20:00:00...10.344909090909091
362014-01-01 20:30:00...10.324848484848485
372014-01-01 21:00:00...10.304787878787879
382014-01-01 21:30:00...10.284727272727273
392014-01-01 22:00:00...10.264666666666667
402014-01-01 22:30:00...10.244606060606061
412014-01-01 23:00:00...10.224545454545455
422014-01-01 23:30:00...10.204484848484848
432014-01-02 00:00:00...10.184424242424242
442014-01-02 00:30:00...10.164363636363636
452014-01-02 01:00:00...10.14430303030303
462014-01-02 01:30:00...10.124242424242424
472014-01-02 02:00:00...10.104181818181818
482014-01-02 02:30:00...10.0841212121212121
492014-01-02 03:00:00...10.0640606060606061
502014-01-02 03:30:00...10.044
512014-01-02 04:00:00...10.044
522014-01-02 04:30:00...10.044
532014-01-02 05:00:00...10.044
542014-01-02 05:30:00...10.044
552014-01-02 06:00:00...10.0456
562014-01-02 06:30:00...10.0472
572014-01-02 07:00:00...10.0504285714285714
582014-01-02 07:30:00...10.0552857142857143
592014-01-02 08:00:00...10.0601428571428571
602014-01-02 08:30:00...10.065
612014-01-02 09:00:00...10.0698571428571429
622014-01-02 09:30:00...10.0747142857142857
632014-01-02 10:00:00...10.0795714285714286
642014-01-02 10:30:00...10.0818082191780822
652014-01-02 11:00:00...10.0814246575342466
662014-01-02 11:30:00...10.081041095890411
672014-01-02 12:00:00...10.0806575342465753
682014-01-02 12:30:00...10.0802739726027397
692014-01-02 13:00:00...10.0798904109589041
702014-01-02 13:30:00...10.0795068493150685
712014-01-02 14:00:00...10.0791232876712329
722014-01-02 14:30:00...10.0787397260273973
732014-01-02 15:00:00...10.0783561643835616
742014-01-02 15:30:00...10.077972602739726
752014-01-02 16:00:00...10.0775890410958904
762014-01-02 16:30:00...10.0772054794520548
772014-01-02 17:00:00...10.0768219178082192
782014-01-02 17:30:00...10.0764383561643836
792014-01-02 18:00:00...10.0760547945205479
802014-01-02 18:30:00...10.0756712328767123
812014-01-02 19:00:00...10.0752876712328767
822014-01-02 19:30:00...10.0749041095890411
832014-01-02 20:00:00...10.0745205479452055
842014-01-02 20:30:00...10.0741369863013699
852014-01-02 21:00:00...10.0737534246575342
862014-01-02 21:30:00...10.0733698630136986
872014-01-02 22:00:00...10.072986301369863
882014-01-02 22:30:00...10.0726027397260274
892014-01-02 23:00:00...10.0722191780821918
902014-01-02 23:30:00...10.0718356164383562
912014-01-03 00:00:00...10.0714520547945205
922014-01-03 00:30:00...10.0710684931506849
932014-01-03 01:00:00...10.0706849315068493
942014-01-03 01:30:00...10.0703013698630137
952014-01-03 02:00:00...10.0699178082191781
962014-01-03 02:30:00...10.0695342465753425
972014-01-03 03:00:00...10.0691506849315068
982014-01-03 03:30:00...10.0687671232876712
992014-01-03 04:00:00...10.0683835616438356
1002014-01-03 04:30:00...10.068

VerticaPy achieves this with its close integration with Vertica; by leveraging Vertica’s comutational power and the TIMESERIES clause, slicing and interpolation is easy.

print(interpolate.current_relation())
(
   SELECT
     slice_time AS "dateUTC",
     "meterID",
     TS_FIRST_VALUE("value", 'linear') AS "value" 
   FROM
 "v_temp_schema"."_verticapy_tmp_sm_consumption_v_mldb_9ea03f2297bd11efa8720242ac120002_" TIMESERIES slice_time AS '30 minutes' OVER (PARTITION BY "meterID"  ORDER BY "dateUTC"::timestamp)) 
VERTICAPY_SUBTABLE

Having sliced data on regular interval of time can often make it easier to join your the data with other datasets. For example, let’s join the weather dataset with the smart_meters_consumption dataset on dateUTC.

interpolate.join(
    sm_weather,
    how = "left",
    on = {"dateUTC": "dateUTC"},
    expr2 = ["temperature", "humidity"],
)
📅
dateUTC
Timestamp(29)
100%
...
123
temperature
Float(22)
99%
123
humidity
Float(22)
99%
12014-01-01 02:30:00...39.293.0
22014-01-01 03:00:00...39.293.0
32014-01-01 03:30:00...39.293.0
42014-01-01 04:00:00...39.293.0
52014-01-01 04:30:00...39.293.0
62014-01-01 05:00:00...39.293.0
72014-01-01 05:30:00...39.287.0
82014-01-01 06:00:00...38.089.0
92014-01-01 06:30:00...37.493.0
102014-01-01 07:00:00...37.493.0
112014-01-01 07:30:00...37.493.0
122014-01-01 08:00:00...37.493.0
132014-01-01 08:30:00...37.487.0
142014-01-01 09:00:00...37.487.0
152014-01-01 09:30:00...37.487.0
162014-01-01 10:00:00...37.493.0
172014-01-01 10:30:00...37.493.0
182014-01-01 11:00:00...37.487.0
192014-01-01 11:30:00...37.493.0
202014-01-01 12:00:00...38.085.0

Keep in mind that slicing, interpolating, and joins can be computationally expensive.

Thanks to Vertica’s built-in clauses and options, VerticaPy can perform joins based on interpolated data. In the following example, we’ll have Vertica identify the closest time series to our key and merge the two datasets.

sm_consumption.join(
    sm_weather,
    how = "left",
    on_interpolate = {"dateUTC": "dateUTC"},
    expr2 = ["temperature", "humidity"],
)
123
meterID
Integer
100%
...
123
temperature
Float(22)
100%
123
humidity
Float(22)
100%
1913...38.095.0
2895...38.095.0
3795...38.095.0
4780...38.095.0
5747...38.095.0
6660...38.095.0
7640...38.095.0
8605...38.095.0
9484...38.095.0
10474...38.095.0
11457...38.095.0
12371...38.095.0
13355...38.095.0
14348...38.095.0
15307...38.095.0
16188...38.095.0
17181...38.095.0
18179...38.095.0
19129...38.095.0
20116...38.095.0
print(
    sm_consumption.join(
        sm_weather,
        how = "left",
        on_interpolate = {"dateUTC": "dateUTC"},
        expr2 = ["temperature", "humidity"],
    ).current_relation()
)

(
   SELECT
     x.*,
     y.temperature,
     y.humidity 
   FROM
 "v_temp_schema"."_verticapy_tmp_sm_consumption_v_mldb_9ea03f2297bd11efa8720242ac120002_" AS "x" LEFT JOIN "v_temp_schema"."_verticapy_tmp_sm_weather_v_mldb_a6290d4697bd11efa8720242ac120002_" AS "y" ON x."dateUTC" INTERPOLATE PREVIOUS VALUE y."dateUTC") 
VERTICAPY_SUBTABLE

Vertica offers powerful methods for cleaning time series data, and you can leverage it all with the flexibility of Python.