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' dataset.

In [8]:

import verticapy as vp
sm_consumption = vp.read_csv("data/sm_consumption.csv")
display(sm_consumption)

	123 meterID Int	📅 dateUTC Timestamp	123 value Numeric(10,6)
1	0	2014-01-01 11:00:00	0.029
2	0	2014-01-01 13:45:00	0.277
3	0	2014-01-02 10:45:00	0.321
4	0	2014-01-02 11:15:00	0.305
5	0	2014-01-02 13:45:00	0.358
6	0	2014-01-02 15:30:00	0.115
7	0	2014-01-03 08:30:00	0.071
8	0	2014-01-04 23:45:00	0.323
9	0	2014-01-06 01:15:00	0.085
10	0	2014-01-06 21:45:00	0.713
11	0	2014-01-07 00:15:00	0.242
12	0	2014-01-07 23:00:00	0.09
13	0	2014-01-09 05:30:00	0.115
14	0	2014-01-09 15:00:00	0.928
15	0	2014-01-09 18:15:00	1.09
16	0	2014-01-10 10:00:00	0.07
17	0	2014-01-10 14:30:00	10.832
18	0	2014-01-10 15:30:00	3.288
19	0	2014-01-10 18:00:00	0.42
20	0	2014-01-11 10:45:00	1.045
21	0	2014-01-12 07:45:00	0.091
22	0	2014-01-13 19:45:00	0.249
23	0	2014-01-13 20:15:00	0.34
24	0	2014-01-14 13:00:00	0.099
25	0	2014-01-14 14:00:00	0.2
26	0	2014-01-14 14:45:00	0.865
27	0	2014-01-14 22:00:00	0.716
28	0	2014-01-15 03:45:00	0.05
29	0	2014-01-15 11:15:00	0.052
30	0	2014-01-15 11:30:00	0.038
31	0	2014-01-15 23:15:00	0.239
32	0	2014-01-16 19:30:00	1.422
33	0	2014-01-17 15:00:00	0.291
34	0	2014-01-18 00:30:00	0.828
35	0	2014-01-19 03:00:00	0.017
36	0	2014-01-19 05:30:00	0.024
37	0	2014-01-19 06:15:00	0.048
38	0	2014-01-19 15:30:00	13.3
39	0	2014-01-20 19:30:00	0.59
40	0	2014-01-21 12:30:00	0.327
41	0	2014-01-21 13:30:00	0.108
42	0	2014-01-22 01:30:00	0.083
43	0	2014-01-22 04:30:00	0.044
44	0	2014-01-23 21:00:00	0.226
45	0	2014-01-24 02:00:00	0.125
46	0	2014-01-24 12:15:00	0.168
47	0	2014-01-27 22:45:00	0.495
48	0	2014-01-28 06:15:00	0.056
49	0	2014-01-28 11:30:00	0.113
50	0	2014-01-28 19:00:00	1.566
51	0	2014-01-28 19:45:00	1.108
52	0	2014-01-29 05:00:00	0.064
53	0	2014-01-29 07:45:00	0.145
54	0	2014-01-29 13:00:00	1.719
55	0	2014-01-30 06:15:00	0.059
56	0	2014-01-30 17:45:00	0.575
57	0	2014-01-30 18:45:00	1.129
58	0	2014-02-01 03:30:00	0.057
59	0	2014-02-01 03:45:00	0.059
60	0	2014-02-02 08:45:00	0.186
61	0	2014-02-04 03:45:00	0.045
62	0	2014-02-04 12:00:00	0.187
63	0	2014-02-04 16:00:00	0.313
64	0	2014-02-04 18:45:00	0.912
65	0	2014-02-05 06:45:00	0.018
66	0	2014-02-05 17:15:00	0.112
67	0	2014-02-06 15:15:00	10.146
68	0	2014-02-07 11:00:00	0.868
69	0	2014-02-07 17:30:00	3.812
70	0	2014-02-07 22:15:00	1.262
71	0	2014-02-08 03:45:00	0.043
72	0	2014-02-08 06:45:00	0.106
73	0	2014-02-09 04:15:00	0.449
74	0	2014-02-09 08:30:00	0.007
75	0	2014-02-11 02:30:00	0.087
76	0	2014-02-11 05:30:00	0.039
77	0	2014-02-11 19:00:00	0.094
78	0	2014-02-12 02:30:00	0.102
79	0	2014-02-13 02:45:00	0.097
80	0	2014-02-13 09:30:00	0.161
81	0	2014-02-13 19:00:00	1.147
82	0	2014-02-14 00:15:00	0.084
83	0	2014-02-14 02:00:00	0.174
84	0	2014-02-14 11:15:00	0.05
85	0	2014-02-14 13:45:00	0.033
86	0	2014-02-15 02:00:00	0.181
87	0	2014-02-15 13:45:00	0.031
88	0	2014-02-15 15:00:00	0.483
89	0	2014-02-15 20:15:00	0.368
90	0	2014-02-16 00:00:00	0.195
91	0	2014-02-17 02:45:00	0.094
92	0	2014-02-17 12:45:00	0.049
93	0	2014-02-17 15:15:00	0.008
94	0	2014-02-18 05:00:00	0.041
95	0	2014-02-18 20:15:00	1.202
96	0	2014-02-19 00:15:00	0.148
97	0	2014-02-19 07:00:00	0.095
98	0	2014-02-20 00:15:00	0.039
99	0	2014-02-20 07:30:00	0.213
100	0	2014-02-20 19:00:00	1.208

Rows: 1-100 | Columns: 3

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

In [9]:

interpolate = sm_consumption.interpolate(ts = "dateUTC",
                                         rule = "30 minutes",
                                         method = {"value": "linear"},
                                         by = ["meterID"])
display(interpolate)

	📅 dateUTC Datetime	123 meterID Integer	123 value Float
1	2014-01-01 11:00:00	0	0.029
2	2014-01-01 11:30:00	0	0.0740909090909091
3	2014-01-01 12:00:00	0	0.119181818181818
4	2014-01-01 12:30:00	0	0.164272727272727
5	2014-01-01 13:00:00	0	0.209363636363636
6	2014-01-01 13:30:00	0	0.254454545454545
7	2014-01-01 14:00:00	0	0.27752380952381
8	2014-01-01 14:30:00	0	0.278571428571429
9	2014-01-01 15:00:00	0	0.279619047619048
10	2014-01-01 15:30:00	0	0.280666666666667
11	2014-01-01 16:00:00	0	0.281714285714286
12	2014-01-01 16:30:00	0	0.282761904761905
13	2014-01-01 17:00:00	0	0.283809523809524
14	2014-01-01 17:30:00	0	0.284857142857143
15	2014-01-01 18:00:00	0	0.285904761904762
16	2014-01-01 18:30:00	0	0.286952380952381
17	2014-01-01 19:00:00	0	0.288
18	2014-01-01 19:30:00	0	0.289047619047619
19	2014-01-01 20:00:00	0	0.290095238095238
20	2014-01-01 20:30:00	0	0.291142857142857
21	2014-01-01 21:00:00	0	0.292190476190476
22	2014-01-01 21:30:00	0	0.293238095238095
23	2014-01-01 22:00:00	0	0.294285714285714
24	2014-01-01 22:30:00	0	0.295333333333333
25	2014-01-01 23:00:00	0	0.296380952380952
26	2014-01-01 23:30:00	0	0.297428571428571
27	2014-01-02 00:00:00	0	0.298476190476191
28	2014-01-02 00:30:00	0	0.29952380952381
29	2014-01-02 01:00:00	0	0.300571428571429
30	2014-01-02 01:30:00	0	0.301619047619048
31	2014-01-02 02:00:00	0	0.302666666666667
32	2014-01-02 02:30:00	0	0.303714285714286
33	2014-01-02 03:00:00	0	0.304761904761905
34	2014-01-02 03:30:00	0	0.305809523809524
35	2014-01-02 04:00:00	0	0.306857142857143
36	2014-01-02 04:30:00	0	0.307904761904762
37	2014-01-02 05:00:00	0	0.308952380952381
38	2014-01-02 05:30:00	0	0.31
39	2014-01-02 06:00:00	0	0.311047619047619
40	2014-01-02 06:30:00	0	0.312095238095238
41	2014-01-02 07:00:00	0	0.313142857142857
42	2014-01-02 07:30:00	0	0.314190476190476
43	2014-01-02 08:00:00	0	0.315238095238095
44	2014-01-02 08:30:00	0	0.316285714285714
45	2014-01-02 09:00:00	0	0.317333333333333
46	2014-01-02 09:30:00	0	0.318380952380952
47	2014-01-02 10:00:00	0	0.319428571428571
48	2014-01-02 10:30:00	0	0.32047619047619
49	2014-01-02 11:00:00	0	0.313
50	2014-01-02 11:30:00	0	0.3103
51	2014-01-02 12:00:00	0	0.3209
52	2014-01-02 12:30:00	0	0.3315
53	2014-01-02 13:00:00	0	0.3421
54	2014-01-02 13:30:00	0	0.3527
55	2014-01-02 14:00:00	0	0.323285714285714
56	2014-01-02 14:30:00	0	0.253857142857143
57	2014-01-02 15:00:00	0	0.184428571428571
58	2014-01-02 15:30:00	0	0.115
59	2014-01-02 16:00:00	0	0.113705882352941
60	2014-01-02 16:30:00	0	0.112411764705882
61	2014-01-02 17:00:00	0	0.111117647058824
62	2014-01-02 17:30:00	0	0.109823529411765
63	2014-01-02 18:00:00	0	0.108529411764706
64	2014-01-02 18:30:00	0	0.107235294117647
65	2014-01-02 19:00:00	0	0.105941176470588
66	2014-01-02 19:30:00	0	0.104647058823529
67	2014-01-02 20:00:00	0	0.103352941176471
68	2014-01-02 20:30:00	0	0.102058823529412
69	2014-01-02 21:00:00	0	0.100764705882353
70	2014-01-02 21:30:00	0	0.0994705882352941
71	2014-01-02 22:00:00	0	0.0981764705882353
72	2014-01-02 22:30:00	0	0.0968823529411765
73	2014-01-02 23:00:00	0	0.0955882352941176
74	2014-01-02 23:30:00	0	0.0942941176470588
75	2014-01-03 00:00:00	0	0.093
76	2014-01-03 00:30:00	0	0.0917058823529412
77	2014-01-03 01:00:00	0	0.0904117647058824
78	2014-01-03 01:30:00	0	0.0891176470588235
79	2014-01-03 02:00:00	0	0.0878235294117647
80	2014-01-03 02:30:00	0	0.0865294117647059
81	2014-01-03 03:00:00	0	0.0852352941176471
82	2014-01-03 03:30:00	0	0.0839411764705882
83	2014-01-03 04:00:00	0	0.0826470588235294
84	2014-01-03 04:30:00	0	0.0813529411764706
85	2014-01-03 05:00:00	0	0.0800588235294118
86	2014-01-03 05:30:00	0	0.0787647058823529
87	2014-01-03 06:00:00	0	0.0774705882352941
88	2014-01-03 06:30:00	0	0.0761764705882353
89	2014-01-03 07:00:00	0	0.0748823529411765
90	2014-01-03 07:30:00	0	0.0735882352941176
91	2014-01-03 08:00:00	0	0.0722941176470588
92	2014-01-03 08:30:00	0	0.071
93	2014-01-03 09:00:00	0	0.0742101910828025
94	2014-01-03 09:30:00	0	0.0774203821656051
95	2014-01-03 10:00:00	0	0.0806305732484076
96	2014-01-03 10:30:00	0	0.0838407643312102
97	2014-01-03 11:00:00	0	0.0870509554140127
98	2014-01-03 11:30:00	0	0.0902611464968153
99	2014-01-03 12:00:00	0	0.0934713375796178
100	2014-01-03 12:30:00	0	0.0966815286624204

Rows: 1-100 | Columns: 3

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

In [10]:

print(interpolate.current_relation())

(
   SELECT
     "dateUTC",
     "meterID",
     "value" 
   FROM
 (
   SELECT
     slice_time AS "dateUTC",
     "meterID",
     TS_FIRST_VALUE("value", 'linear') AS "value" 
   FROM
 "v_temp_schema"."_verticapy_tmp_sm_consumption_dbadmin_186831_8687362587_" TIMESERIES slice_time AS '30 minutes' OVER (PARTITION BY "meterID" ORDER BY "dateUTC"::timestamp)) interpolate) 
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'.

In [12]:

sm_weather = vp.read_csv("data/sm_weather.csv")
display(sm_weather)

	📅 dateUTC Timestamp	123 temperature Numeric(10,6)	123 humidity Numeric(11,6)
1	2014-01-01 00:00:00	38.0	95.0
2	2014-01-01 00:30:00	37.4	93.0
3	2014-01-01 01:00:00	37.4	100.0
4	2014-01-01 01:30:00	37.4	100.0
5	2014-01-01 02:00:00	39.2	93.0
6	2014-01-01 02:30:00	39.2	93.0
7	2014-01-01 03:00:00	39.2	93.0
8	2014-01-01 03:30:00	39.2	93.0
9	2014-01-01 04:00:00	39.2	93.0
10	2014-01-01 04:30:00	39.2	93.0
11	2014-01-01 05:00:00	39.2	93.0
12	2014-01-01 05:30:00	39.2	87.0
13	2014-01-01 06:00:00	38.0	89.0
14	2014-01-01 06:30:00	37.4	93.0
15	2014-01-01 07:00:00	37.4	93.0
16	2014-01-01 07:30:00	37.4	93.0
17	2014-01-01 08:00:00	37.4	93.0
18	2014-01-01 08:30:00	37.4	87.0
19	2014-01-01 09:00:00	37.4	87.0
20	2014-01-01 09:30:00	37.4	87.0
21	2014-01-01 10:00:00	37.4	93.0
22	2014-01-01 10:30:00	37.4	93.0
23	2014-01-01 11:00:00	37.4	87.0
24	2014-01-01 11:30:00	37.4	93.0
25	2014-01-01 12:00:00	38.0	85.0
26	2014-01-01 12:30:00	39.2	87.0
27	2014-01-01 13:00:00	39.2	87.0
28	2014-01-01 13:30:00	39.2	87.0
29	2014-01-01 14:00:00	39.2	87.0
30	2014-01-01 14:30:00	39.2	87.0
31	2014-01-01 15:00:00	39.2	87.0
32	2014-01-01 15:30:00	39.2	87.0
33	2014-01-01 16:00:00	37.4	93.0
34	2014-01-01 16:30:00	39.2	87.0
35	2014-01-01 17:00:00	39.2	87.0
36	2014-01-01 17:30:00	37.4	87.0
37	2014-01-01 18:00:00	38.0	81.0
38	2014-01-01 18:30:00	37.4	87.0
39	2014-01-01 19:00:00	35.6	87.0
40	2014-01-01 19:30:00	37.4	93.0
41	2014-01-01 20:00:00	39.2	87.0
42	2014-01-01 20:30:00	39.2	87.0
43	2014-01-01 21:00:00	39.2	87.0
44	2014-01-01 21:30:00	41.0	81.0
45	2014-01-01 22:00:00	39.2	87.0
46	2014-01-01 22:30:00	39.2	87.0
47	2014-01-01 23:00:00	39.2	87.0
48	2014-01-01 23:30:00	39.2	81.0
49	2014-01-02 00:00:00	38.0	76.0
50	2014-01-02 00:30:00	37.4	81.0
51	2014-01-02 01:00:00	37.4	81.0
52	2014-01-02 01:30:00	37.4	81.0
53	2014-01-02 02:00:00	37.4	81.0
54	2014-01-02 02:30:00	37.4	81.0
55	2014-01-02 03:00:00	37.4	81.0
56	2014-01-02 03:30:00	37.4	81.0
57	2014-01-02 04:00:00	37.4	81.0
58	2014-01-02 04:30:00	35.6	87.0
59	2014-01-02 05:00:00	35.6	93.0
60	2014-01-02 05:30:00	37.4	81.0
61	2014-01-02 06:00:00	37.0	75.0
62	2014-01-02 06:30:00	37.4	81.0
63	2014-01-02 07:00:00	37.4	87.0
64	2014-01-02 07:30:00	37.4	81.0
65	2014-01-02 08:00:00	35.6	81.0
66	2014-01-02 08:30:00	35.6	81.0
67	2014-01-02 09:00:00	37.4	75.0
68	2014-01-02 09:30:00	37.4	81.0
69	2014-01-02 10:00:00	39.2	75.0
70	2014-01-02 10:30:00	41.0	70.0
71	2014-01-02 11:00:00	39.2	75.0
72	2014-01-02 11:30:00	41.0	70.0
73	2014-01-02 12:00:00	41.0	61.0
74	2014-01-02 12:30:00	41.0	70.0
75	2014-01-02 13:00:00	41.0	76.0
76	2014-01-02 13:30:00	41.0	76.0
77	2014-01-02 14:00:00	41.0	76.0
78	2014-01-02 14:30:00	41.0	76.0
79	2014-01-02 15:00:00	41.0	76.0
80	2014-01-02 15:30:00	41.0	76.0
81	2014-01-02 16:00:00	41.0	76.0
82	2014-01-02 16:30:00	41.0	76.0
83	2014-01-02 17:00:00	41.0	76.0
84	2014-01-02 17:30:00	39.2	81.0
85	2014-01-02 18:00:00	39.0	70.0
86	2014-01-02 18:30:00	37.4	81.0
87	2014-01-02 19:00:00	39.2	75.0
88	2014-01-02 19:30:00	37.4	81.0
89	2014-01-02 20:00:00	37.4	81.0
90	2014-01-02 20:30:00	39.2	75.0
91	2014-01-02 21:00:00	39.2	65.0
92	2014-01-02 21:30:00	39.2	65.0
93	2014-01-02 22:00:00	39.2	65.0
94	2014-01-02 22:30:00	39.2	61.0
95	2014-01-02 23:00:00	39.2	61.0
96	2014-01-02 23:30:00	39.2	65.0
97	2014-01-03 00:00:00	39.0	48.0
98	2014-01-03 00:30:00	39.2	61.0
99	2014-01-03 01:00:00	39.2	65.0
100	2014-01-03 01:30:00	37.4	70.0

Rows: 1-100 | Columns: 3

Since the data was ingested at regular intervals of 30 minutes, we can join this dataset using 'dateUTC' as the key.

In [13]:

interpolate.join(sm_weather,
                 how = "left",
                 on = {"dateUTC": "dateUTC"},
                 expr2 = ["temperature", "humidity"])

Out[13]:

	📅 dateUTC Datetime	123 meterID Integer	123 value Float	123 temperature Numeric(10,6)	123 humidity Numeric(11,6)
1	2014-01-01 11:00:00	0	0.029	37.4	87.0
2	2014-01-01 11:30:00	0	0.0740909090909091	37.4	93.0
3	2014-01-01 12:00:00	0	0.119181818181818	38.0	85.0
4	2014-01-01 12:30:00	0	0.164272727272727	39.2	87.0
5	2014-01-01 13:00:00	0	0.209363636363636	39.2	87.0
6	2014-01-01 13:30:00	0	0.254454545454545	39.2	87.0
7	2014-01-01 14:00:00	0	0.27752380952381	39.2	87.0
8	2014-01-01 14:30:00	0	0.278571428571429	39.2	87.0
9	2014-01-01 15:00:00	0	0.279619047619048	39.2	87.0
10	2014-01-01 15:30:00	0	0.280666666666667	39.2	87.0
11	2014-01-01 16:00:00	0	0.281714285714286	37.4	93.0
12	2014-01-01 16:30:00	0	0.282761904761905	39.2	87.0
13	2014-01-01 17:00:00	0	0.283809523809524	39.2	87.0
14	2014-01-01 17:30:00	0	0.284857142857143	37.4	87.0
15	2014-01-01 18:00:00	0	0.285904761904762	38.0	81.0
16	2014-01-01 18:30:00	0	0.286952380952381	37.4	87.0
17	2014-01-01 19:00:00	0	0.288	35.6	87.0
18	2014-01-01 19:30:00	0	0.289047619047619	37.4	93.0
19	2014-01-01 20:00:00	0	0.290095238095238	39.2	87.0
20	2014-01-01 20:30:00	0	0.291142857142857	39.2	87.0
21	2014-01-01 21:00:00	0	0.292190476190476	39.2	87.0
22	2014-01-01 21:30:00	0	0.293238095238095	41.0	81.0
23	2014-01-01 22:00:00	0	0.294285714285714	39.2	87.0
24	2014-01-01 22:30:00	0	0.295333333333333	39.2	87.0
25	2014-01-01 23:00:00	0	0.296380952380952	39.2	87.0
26	2014-01-01 23:30:00	0	0.297428571428571	39.2	81.0
27	2014-01-02 00:00:00	0	0.298476190476191	38.0	76.0
28	2014-01-02 00:30:00	0	0.29952380952381	37.4	81.0
29	2014-01-02 01:00:00	0	0.300571428571429	37.4	81.0
30	2014-01-02 01:30:00	0	0.301619047619048	37.4	81.0
31	2014-01-02 02:00:00	0	0.302666666666667	37.4	81.0
32	2014-01-02 02:30:00	0	0.303714285714286	37.4	81.0
33	2014-01-02 03:00:00	0	0.304761904761905	37.4	81.0
34	2014-01-02 03:30:00	0	0.305809523809524	37.4	81.0
35	2014-01-02 04:00:00	0	0.306857142857143	37.4	81.0
36	2014-01-02 04:30:00	0	0.307904761904762	35.6	87.0
37	2014-01-02 05:00:00	0	0.308952380952381	35.6	93.0
38	2014-01-02 05:30:00	0	0.31	37.4	81.0
39	2014-01-02 06:00:00	0	0.311047619047619	37.0	75.0
40	2014-01-02 06:30:00	0	0.312095238095238	37.4	81.0
41	2014-01-02 07:00:00	0	0.313142857142857	37.4	87.0
42	2014-01-02 07:30:00	0	0.314190476190476	37.4	81.0
43	2014-01-02 08:00:00	0	0.315238095238095	35.6	81.0
44	2014-01-02 08:30:00	0	0.316285714285714	35.6	81.0
45	2014-01-02 09:00:00	0	0.317333333333333	37.4	75.0
46	2014-01-02 09:30:00	0	0.318380952380952	37.4	81.0
47	2014-01-02 10:00:00	0	0.319428571428571	39.2	75.0
48	2014-01-02 10:30:00	0	0.32047619047619	41.0	70.0
49	2014-01-02 11:00:00	0	0.313	39.2	75.0
50	2014-01-02 11:30:00	0	0.3103	41.0	70.0
51	2014-01-02 12:00:00	0	0.3209	41.0	61.0
52	2014-01-02 12:30:00	0	0.3315	41.0	70.0
53	2014-01-02 13:00:00	0	0.3421	41.0	76.0
54	2014-01-02 13:30:00	0	0.3527	41.0	76.0
55	2014-01-02 14:00:00	0	0.323285714285714	41.0	76.0
56	2014-01-02 14:30:00	0	0.253857142857143	41.0	76.0
57	2014-01-02 15:00:00	0	0.184428571428571	41.0	76.0
58	2014-01-02 15:30:00	0	0.115	41.0	76.0
59	2014-01-02 16:00:00	0	0.113705882352941	41.0	76.0
60	2014-01-02 16:30:00	0	0.112411764705882	41.0	76.0
61	2014-01-02 17:00:00	0	0.111117647058824	41.0	76.0
62	2014-01-02 17:30:00	0	0.109823529411765	39.2	81.0
63	2014-01-02 18:00:00	0	0.108529411764706	39.0	70.0
64	2014-01-02 18:30:00	0	0.107235294117647	37.4	81.0
65	2014-01-02 19:00:00	0	0.105941176470588	39.2	75.0
66	2014-01-02 19:30:00	0	0.104647058823529	37.4	81.0
67	2014-01-02 20:00:00	0	0.103352941176471	37.4	81.0
68	2014-01-02 20:30:00	0	0.102058823529412	39.2	75.0
69	2014-01-02 21:00:00	0	0.100764705882353	39.2	65.0
70	2014-01-02 21:30:00	0	0.0994705882352941	39.2	65.0
71	2014-01-02 22:00:00	0	0.0981764705882353	39.2	65.0
72	2014-01-02 22:30:00	0	0.0968823529411765	39.2	61.0
73	2014-01-02 23:00:00	0	0.0955882352941176	39.2	61.0
74	2014-01-02 23:30:00	0	0.0942941176470588	39.2	65.0
75	2014-01-03 00:00:00	0	0.093	39.0	48.0
76	2014-01-03 00:30:00	0	0.0917058823529412	39.2	61.0
77	2014-01-03 01:00:00	0	0.0904117647058824	39.2	65.0
78	2014-01-03 01:30:00	0	0.0891176470588235	37.4	70.0
79	2014-01-03 02:00:00	0	0.0878235294117647	37.4	70.0
80	2014-01-03 02:30:00	0	0.0865294117647059	37.4	70.0
81	2014-01-03 03:00:00	0	0.0852352941176471	37.4	70.0
82	2014-01-03 03:30:00	0	0.0839411764705882	37.4	70.0
83	2014-01-03 04:00:00	0	0.0826470588235294	37.4	70.0
84	2014-01-03 04:30:00	0	0.0813529411764706	37.4	70.0
85	2014-01-03 05:00:00	0	0.0800588235294118	37.4	70.0
86	2014-01-03 05:30:00	0	0.0787647058823529	39.2	61.0
87	2014-01-03 06:00:00	0	0.0774705882352941	38.0	50.0
88	2014-01-03 06:30:00	0	0.0761764705882353	37.4	65.0
89	2014-01-03 07:00:00	0	0.0748823529411765	39.2	61.0
90	2014-01-03 07:30:00	0	0.0735882352941176	37.4	65.0
91	2014-01-03 08:00:00	0	0.0722941176470588	37.4	65.0
92	2014-01-03 08:30:00	0	0.071	37.4	65.0
93	2014-01-03 09:00:00	0	0.0742101910828025	37.4	65.0
94	2014-01-03 09:30:00	0	0.0774203821656051	37.4	65.0
95	2014-01-03 10:00:00	0	0.0806305732484076	37.4	65.0
96	2014-01-03 10:30:00	0	0.0838407643312102	39.2	61.0
97	2014-01-03 11:00:00	0	0.0870509554140127	39.2	56.0
98	2014-01-03 11:30:00	0	0.0902611464968153	39.2	61.0
99	2014-01-03 12:00:00	0	0.0934713375796178	39.0	48.0
100	2014-01-03 12:30:00	0	0.0966815286624204	39.2	61.0

Rows: 1-100 | Columns: 5

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.

In [14]:

sm_consumption.join(sm_weather,
                    how = "left",
                    on_interpolate = {"dateUTC": "dateUTC"},
                    expr2 = ["temperature", "humidity"])

Out[14]:

	123 meterID Integer	📅 dateUTC Datetime	123 value Numeric(10,6)	123 temperature Numeric(10,6)	123 humidity Numeric(11,6)
1	913	2014-01-01 00:00:00	0.097	38.0	95.0
2	895	2014-01-01 00:00:00	0.184	38.0	95.0
3	795	2014-01-01 00:00:00	0.093	38.0	95.0
4	780	2014-01-01 00:00:00	0.116	38.0	95.0
5	747	2014-01-01 00:00:00	0.559	38.0	95.0
6	660	2014-01-01 00:00:00	0.836	38.0	95.0
7	640	2014-01-01 00:00:00	1.796	38.0	95.0
8	605	2014-01-01 00:00:00	0.038	38.0	95.0
9	484	2014-01-01 00:00:00	0.211	38.0	95.0
10	474	2014-01-01 00:00:00	0.313	38.0	95.0
11	457	2014-01-01 00:00:00	0.022	38.0	95.0
12	371	2014-01-01 00:00:00	0.815	38.0	95.0
13	355	2014-01-01 00:00:00	0.448	38.0	95.0
14	348	2014-01-01 00:00:00	0.071	38.0	95.0
15	307	2014-01-01 00:00:00	1.423	38.0	95.0
16	188	2014-01-01 00:00:00	0.152	38.0	95.0
17	181	2014-01-01 00:00:00	0.138	38.0	95.0
18	179	2014-01-01 00:00:00	0.033	38.0	95.0
19	129	2014-01-01 00:00:00	0.054	38.0	95.0
20	116	2014-01-01 00:00:00	0.232	38.0	95.0
21	986	2014-01-01 00:15:00	0.301	38.0	95.0
22	848	2014-01-01 00:15:00	0.186	38.0	95.0
23	829	2014-01-01 00:15:00	0.145	38.0	95.0
24	718	2014-01-01 00:15:00	0.091	38.0	95.0
25	637	2014-01-01 00:15:00	0.319	38.0	95.0
26	625	2014-01-01 00:15:00	0.141	38.0	95.0
27	619	2014-01-01 00:15:00	0.103	38.0	95.0
28	583	2014-01-01 00:15:00	4.081	38.0	95.0
29	444	2014-01-01 00:15:00	0.131	38.0	95.0
30	221	2014-01-01 00:15:00	0.399	38.0	95.0
31	67	2014-01-01 00:15:00	0.157	38.0	95.0
32	52	2014-01-01 00:15:00	0.157	38.0	95.0
33	933	2014-01-01 00:30:00	0.061	37.4	93.0
34	930	2014-01-01 00:30:00	0.202	37.4	93.0
35	904	2014-01-01 00:30:00	0.159	37.4	93.0
36	829	2014-01-01 00:30:00	0.082	37.4	93.0
37	796	2014-01-01 00:30:00	0.237	37.4	93.0
38	663	2014-01-01 00:30:00	0.08	37.4	93.0
39	628	2014-01-01 00:30:00	1.246	37.4	93.0
40	626	2014-01-01 00:30:00	0.106	37.4	93.0
41	618	2014-01-01 00:30:00	0.067	37.4	93.0
42	506	2014-01-01 00:30:00	0.108	37.4	93.0
43	497	2014-01-01 00:30:00	0.144	37.4	93.0
44	460	2014-01-01 00:30:00	0.094	37.4	93.0
45	376	2014-01-01 00:30:00	0.129	37.4	93.0
46	332	2014-01-01 00:30:00	0.044	37.4	93.0
47	308	2014-01-01 00:30:00	0.045	37.4	93.0
48	286	2014-01-01 00:30:00	0.209	37.4	93.0
49	277	2014-01-01 00:30:00	0.287	37.4	93.0
50	264	2014-01-01 00:30:00	0.162	37.4	93.0
51	138	2014-01-01 00:30:00	2.276	37.4	93.0
52	90	2014-01-01 00:30:00	0.183	37.4	93.0
53	977	2014-01-01 00:45:00	0.176	37.4	93.0
54	937	2014-01-01 00:45:00	0.201	37.4	93.0
55	927	2014-01-01 00:45:00	0.551	37.4	93.0
56	867	2014-01-01 00:45:00	0.844	37.4	93.0
57	762	2014-01-01 00:45:00	0.13	37.4	93.0
58	528	2014-01-01 00:45:00	0.067	37.4	93.0
59	447	2014-01-01 00:45:00	0.599	37.4	93.0
60	375	2014-01-01 00:45:00	0.519	37.4	93.0
61	297	2014-01-01 00:45:00	0.609	37.4	93.0
62	250	2014-01-01 00:45:00	0.428	37.4	93.0
63	235	2014-01-01 00:45:00	0.06	37.4	93.0
64	980	2014-01-01 01:00:00	0.16	37.4	100.0
65	797	2014-01-01 01:00:00	0.114	37.4	100.0
66	783	2014-01-01 01:00:00	0.025	37.4	100.0
67	709	2014-01-01 01:00:00	0.058	37.4	100.0
68	697	2014-01-01 01:00:00	0.184	37.4	100.0
69	636	2014-01-01 01:00:00	0.139	37.4	100.0
70	548	2014-01-01 01:00:00	0.309	37.4	100.0
71	374	2014-01-01 01:00:00	0.104	37.4	100.0
72	358	2014-01-01 01:00:00	0.471	37.4	100.0
73	304	2014-01-01 01:00:00	0.109	37.4	100.0
74	291	2014-01-01 01:00:00	0.412	37.4	100.0
75	263	2014-01-01 01:00:00	0.172	37.4	100.0
76	256	2014-01-01 01:00:00	0.124	37.4	100.0
77	123	2014-01-01 01:00:00	0.309	37.4	100.0
78	79	2014-01-01 01:00:00	0.243	37.4	100.0
79	75	2014-01-01 01:00:00	0.274	37.4	100.0
80	61	2014-01-01 01:00:00	0.1	37.4	100.0
81	35	2014-01-01 01:00:00	0.134	37.4	100.0
82	950	2014-01-01 01:15:00	0.127	37.4	100.0
83	844	2014-01-01 01:15:00	0.539	37.4	100.0
84	832	2014-01-01 01:15:00	0.358	37.4	100.0
85	807	2014-01-01 01:15:00	0.095	37.4	100.0
86	656	2014-01-01 01:15:00	0.104	37.4	100.0
87	598	2014-01-01 01:15:00	0.18	37.4	100.0
88	588	2014-01-01 01:15:00	0.064	37.4	100.0
89	570	2014-01-01 01:15:00	0.018	37.4	100.0
90	476	2014-01-01 01:15:00	0.299	37.4	100.0
91	467	2014-01-01 01:15:00	0.292	37.4	100.0
92	386	2014-01-01 01:15:00	0.156	37.4	100.0
93	306	2014-01-01 01:15:00	0.16	37.4	100.0
94	290	2014-01-01 01:15:00	0.092	37.4	100.0
95	193	2014-01-01 01:15:00	0.101	37.4	100.0
96	134	2014-01-01 01:15:00	0.429	37.4	100.0
97	123	2014-01-01 01:15:00	0.446	37.4	100.0
98	119	2014-01-01 01:15:00	0.268	37.4	100.0
99	89	2014-01-01 01:15:00	0.107	37.4	100.0
100	2	2014-01-01 01:15:00	0.037	37.4	100.0

Rows: 1-100 | Columns: 5

In [15]:

print(sm_consumption.join(sm_weather,
                          how = "left",
                          on_interpolate = {"dateUTC": "dateUTC"},
                          expr2 = ["temperature", "humidity"]).current_relation())

(
   SELECT
     "meterID",
     "dateUTC",
     "value",
     "temperature",
     "humidity" 
   FROM
 (
   SELECT
     x.*,
     y.temperature,
     y.humidity 
   FROM
 "v_temp_schema"."_verticapy_tmp_sm_consumption_dbadmin_186831_8687362587_" AS x LEFT JOIN "v_temp_schema"."_verticapy_tmp_sm_weather_dbadmin_186831_3560437028_" AS y  ON x."dateUTC" INTERPOLATE PREVIOUS VALUE y."dateUTC") 
VERTICAPY_SUBTABLE) 
VERTICAPY_SUBTABLE

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