Smart Meters¶

This example uses the following datasets to predict peoples' electricity consumption. You can download the Jupyter Notebook of the study here. We'll use the following datasets:

sm_consumption

dateUTC: Date and time of the record
meterID: Smart meter ID
value: Electricity consumed during 30 minute interval (in kWh)

sm_weather

dateUTC: Date and time of the record
temperature: Temperature
humidity: Humidity

sm_meters

longitude: Longitude
latitude: Latitude
residenceType: 1 for Single-Family; 2 for Multi-Family; 3 for Appartement

We will follow the data science cycle (Data Exploration - Data Preparation - Data Modeling - Model Evaluation - Model Deployment) to solve this problem.

Initialization¶

This example uses the following version of VerticaPy:

In [1]:

import verticapy as vp
vp.__version__

Out[1]:

'0.9.0'

Create vDataFrames of the datasets:

In [3]:

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

	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

	📅 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

	123 meterID Int	123 residenceType Int	🌎 latitude Float	🌎 longitude Float
1	0	1	53.3575915882838	-6.27554509198069
2	1	1	53.3130886440828	-6.21174062227953
3	2	1	53.322863954253	-6.25885396251447
4	3	3	51.895226209818	-8.54531304761961
5	4	1	53.6511753623331	-6.69348712237822
6	5	1	53.3262233045899	-6.31623713317817
7	6	3	53.3816401612885	-6.24222877491921
8	7	3	53.4410265504271	-6.2749868845315
9	8	1	53.2883538726462	-6.31547263281076
10	9	1	53.307294775666	-6.171585396401
11	10	1	53.3043589304317	-6.27057231678352
12	11	1	52.2168162005257	-7.19089306958349
13	12	3	53.3292821225723	-6.26914988094354
14	13	1	52.2330869136812	-7.0801397379672
15	14	1	53.3121094759744	-6.1914301584486
16	15	1	52.6815374444767	-8.71513846618087
17	16	2	53.2598894923594	-8.98501096632971
18	17	1	53.3386461784187	-6.19326209020707
19	18	1	53.2930399359755	-6.29464811435931
20	19	1	53.2904090932662	-6.20219801883112
21	20	1	53.3140843911614	-6.27801544356096
22	21	2	53.3368019275034	-6.30227460284837
23	22	1	53.3606245053304	-6.25253082496436
24	23	3	53.7334769562786	-6.40192964510675
25	24	1	51.8904635261463	-8.4583653371337
26	25	1	53.2943781673251	-6.27264309456447
27	26	1	53.4252605572445	-6.24346657284835
28	27	2	53.3445341131241	-6.21965121543182
29	28	1	53.358289570405	-6.22902191315319
30	29	1	51.9025419059901	-8.45522362503022
31	30	3	53.4226787570522	-6.2597908055893
32	31	3	53.379068564415	-6.25362478692152
33	32	3	54.0078974821771	-6.36373099514602
34	33	2	53.3786128724389	-6.26875699530572
35	34	1	53.3276679292134	-6.25674968108046
36	35	1	53.3775061858458	-6.2658589998757
37	36	1	53.422378212805	-6.30085964886839
38	37	2	53.3163882602178	-6.15806917577308
39	38	3	53.37096469415	-6.27913717729599
40	39	1	53.4132892637185	-6.23844413381297
41	40	1	53.4228804497345	-6.26418479308435
42	41	1	53.353488205755	-6.30562807207814
43	42	1	53.3054854916401	-6.24275285029992
44	43	1	53.3376504362861	-6.25709734131062
45	44	1	53.3748180529522	-6.21375987196951
46	45	1	53.7635143297958	-6.25548923165312
47	46	1	53.2618719521814	-6.25472544269271
48	47	3	51.8616597388643	-8.49342918637484
49	48	3	53.2044437294531	-6.185303194865
50	49	1	53.3679368733445	-6.23992423549581
51	50	1	53.2659672891683	-6.26641539940328
52	51	1	53.3662651408726	-6.14679963936097
53	52	1	53.3393859597162	-6.30342142910585
54	53	3	51.9040058992909	-8.39981371864713
55	54	1	53.3343246471021	-6.25061672172339
56	55	2	51.8643744508624	-8.56908207755333
57	56	1	53.3530817973677	-6.30734386455951
58	57	1	53.3414541477597	-6.25186365095182
59	58	3	53.3338737360076	-6.22063504106583
60	59	1	52.2124956730345	-7.11448302907311
61	60	1	53.3363487476997	-6.31363533408071
62	61	1	53.3587038304275	-6.21556299304468
63	62	3	53.3172551197574	-6.24415298656193
64	63	1	53.2271603237907	-6.14839645868847
65	64	2	53.2890269752152	-6.23614626114708
66	65	1	53.3963744886512	-6.34171292270289
67	66	1	51.8895373160002	-8.48780907327119
68	67	1	53.344893347275	-6.23974905608669
69	68	1	53.3712600623191	-6.24875843222256
70	69	1	53.3504808496605	-6.34801942486053
71	70	3	51.9154011937038	-8.48836529664276
72	71	3	52.1952892522058	-7.11063882871
73	72	1	53.303992111683	-6.25315326364841
74	73	1	53.2753986962432	-9.07589204146141
75	74	1	51.91819680651	-8.43446154154656
76	75	1	52.6027837572115	-8.6160224579197
77	76	1	53.4035657645851	-6.25428225078493
78	77	1	53.3709894952545	-6.37512943696882
79	78	2	53.3358314852994	-6.39381681908009
80	79	1	53.2716167370656	-6.23869625794234
81	80	1	53.3591211533429	-6.21909400592063
82	81	1	53.372120236396	-6.20827879919494
83	82	1	53.3647332578122	-6.28451041581688
84	83	1	53.2632427747372	-6.26140047474471
85	84	1	51.9123757703805	-8.46795169870626
86	85	1	53.3549789863896	-6.2372798346737
87	86	3	53.2266733295124	-6.2667286257736
88	87	3	53.307434324047	-6.17627526417622
89	88	3	53.2391085904911	-6.15017286838827
90	89	3	53.9521467669519	-6.43670239554822
91	90	1	52.2406296264404	-7.09240230508834
92	91	1	53.425341733642	-6.32118808599842
93	92	3	51.8614117711696	-8.50296011084579
94	93	1	53.7029164125936	-6.42959758250948
95	94	1	53.378597106695	-6.17601148907721
96	95	3	53.3405488207761	-6.31607804590097
97	96	1	53.4212269471156	-6.21357678538708
98	97	1	53.3123208667246	-6.26734792518767
99	98	1	53.2186994970707	-9.04100949651601
100	99	1	53.3065251415844	-6.21247769416289

Rows: 1-100 | Columns: 4

Data Exploration and Preparation¶

Predicting energy consumption in households is very important. Surges in electricity use could cause serious power outages. In our case, we'll be using data on general household energy consumption in Ireland to predict consumption at various times.

In order to join the different data sources, we need to assume that the weather will be approximately the same across the entirety of Ireland. We'll use the date and time as the key to join 'sm_weather' and 'sm_consumption'.

Joining different datasets with interpolation

In VerticaPy, you can interpolate joins; Vertica will find the closest timestamp to the key and join the result.

In [4]:

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

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

Rows: 1-100 | Columns: 5

Segmenting Latitude & Longitude using Clustering

The dataset 'sm_meters' is pretty important. In particular, the type of residence is probably a good predictor for electricity usage. We can create clusters of the different regions with k-means clustering based on longitude and latitude. Let's find the most suitable 'k' using an elbow curve and scatter plot.

In [5]:

sm_meters.agg(["min", "max"])

Out[5]:

	min	max
"meterID"	0.0	999.0
"residenceType"	1.0	3.0
"latitude"	51.7964600770212	54.0270361317983
"longitude"	-9.16352332036362	-6.07134572494937

Rows: 1-4 | Columns: 3

In [6]:

%matplotlib inline
from verticapy.learn.model_selection import elbow
from verticapy.datasets import load_world

world = load_world()
df = world.to_geopandas(geometry = "geometry")
df = df[df["country"].isin(["Ireland", "United Kingdom"])]
ax = df.plot(edgecolor = "black",
             color = "white",
             figsize = (10, 9))
ax = sm_meters.scatter(["longitude", "latitude"], ax = ax)

Based on the scatter plot, five seems like the optimal number of clusters. Let's verify this hypothesis using an elbow curve.

In [7]:

elbow(sm_meters, ["longitude", "latitude"], n_cluster = (3, 8))

Out[7]:

	Within-Cluster SS
3	0.9311388771341764
4	0.975099232332047
5	0.9844977831351884
6	0.993627480208735
7	0.9947041356087959

Rows: 1-5 | Columns: 2

The elbow curve seems to confirm that five is the optimal number of clusters, so let's create a k-means model with that in mind.

In [8]:

from verticapy.learn.cluster import KMeans
model = KMeans("kmeans_sm_regions", 
               n_cluster = 5,
               init = [(-6.26980, 53.38127),
                       (-9.06178, 53.25998),
                       (-8.48641, 51.90216),
                       (-7.12408, 52.24610),
                       (-8.63985, 52.65945),])
model.drop()
model.fit(sm_meters, 
          ["longitude", "latitude"])

Out[8]:


=======
centers
=======
longitude|latitude
---------+--------
-9.06178 |53.25998
-8.63985 |52.65945
-8.48641 |51.90216
-7.12408 |52.24610
-6.26980 |53.38127


=======
metrics
=======
Evaluation metrics:
     Total Sum of Squares: 1209.2077
     Within-Cluster Sum of Squares: 
         Cluster 0: 0.099754154
         Cluster 1: 0.2779225
         Cluster 2: 0.53464463
         Cluster 3: 0.2657853
         Cluster 4: 17.892423
     Total Within-Cluster Sum of Squares: 19.07053
     Between-Cluster Sum of Squares: 1190.1372
     Between-Cluster SS / Total SS: 98.42%
 Number of iterations performed: 1
 Converged: True
 Call:
kmeans('public.kmeans_sm_regions', '"public"."_verticapy_tmp_view_dbadmin_239726_1781523806_"', '"longitude", "latitude"', 5
USING PARAMETERS max_iterations=300, epsilon=0.0001, initial_centers_table='"public"."_verticapy_tmp_kmeans_init_dbadmin_239726_3848320059_"', distance_method='euclidean')

Let's add our clusters to the vDataFrame.

In [9]:

sm_meters = model.predict(sm_meters, name = "region")

Let's draw a scatter plot of the different regions.

In [10]:

ax = df.plot(edgecolor = "black",
             color = "white",
             figsize = (10, 9))
sm_meters.scatter(["longitude", "latitude"], 
                  catcol = "region",
                  max_cardinality = 10,
                  ax = ax)

Out[10]:

<AxesSubplot:xlabel='"longitude"', ylabel='"latitude"'>

Dataset Enrichment

Let's join 'sm_meters' with 'sm_consumption_weather'.

In [11]:

sm_consumption_weather_region = sm_consumption_weather.join(
                                    sm_meters,
                                    how = "natural",
                                    expr1 = ["*"],
                                    expr2 = ["residenceType", 
                                             "region"])
display(sm_consumption_weather_region)