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)

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

Rows: 1-100 | Columns: 7

Handling Missing Values

Let's take care of our missing values.

In [12]:

sm_consumption_weather_region.count_percent()

Out[12]:

	count	percent
"dateUTC"	1188432.0	100.0
"meterID"	1188432.0	100.0
"humidity"	1188432.0	100.0
"temperature"	1188432.0	100.0
"residenceType"	1188432.0	100.0
"region"	1188432.0	100.0
"value"	1188412.0	99.998

Rows: 1-7 | Columns: 3

The variable 'value' has a few missing values that we can drop.

In [13]:

sm_consumption_weather_region["value"].dropna()
sm_consumption_weather_region.count()

20 elements were filtered.

Out[13]:

	count
"dateUTC"	1188412.0
"meterID"	1188412.0
"value"	1188412.0
"humidity"	1188412.0
"temperature"	1188412.0
"residenceType"	1188412.0
"region"	1188412.0

Rows: 1-7 | Columns: 2

Interpolation & Aggregations

Since power outages seem relatively common in each area, and the "value" represents the electricity consumed during 30 minute intervals (in kWh), it'd be a good idea to interpolate and aggregate the data to get a monthly average in electricity consumption per region.

Let's save our new dataset in the Vertica database.

In [15]:

vp.drop("sm_consumption_weather_region", method = "table")
sm_consumption_weather_region.to_db("sm_consumption_weather_region",
                                    relation_type = "table")
sm_consumption_weather_region_clean = vp.vDataFrame("sm_consumption_weather_region")

To get an equally-sliced dataset, we can then interpolate to fill any gaps. This operation is essential for creating correct time series models.

In [16]:

sm_consumption_weather_region_clean = sm_consumption_weather_region_clean.interpolate(
                                                                        ts = "dateUTC",
                                                                        rule = "30 minutes",
                                                                        method = {"value": "linear",
                                                                                  "humidity": "linear",
                                                                                  "temperature": "linear",
                                                                                  "residenceType": "ffill",
                                                                                  "region": "ffill"},
                                                                        by = ["meterID"])
display(sm_consumption_weather_region_clean)

	📅 dateUTC Datetime	123 meterID Integer	123 value Float	123 humidity Float	123 temperature Float	123 residenceType Integer	123 region Integer
1	2014-01-01 11:00:00	0	0.029	87.0	37.4	1	4
2	2014-01-01 11:30:00	0	0.0740909090909091	87.0	37.7272727272727	1	4
3	2014-01-01 12:00:00	0	0.119181818181818	87.0	38.0545454545455	1	4
4	2014-01-01 12:30:00	0	0.164272727272727	87.0	38.3818181818182	1	4
5	2014-01-01 13:00:00	0	0.209363636363636	87.0	38.7090909090909	1	4
6	2014-01-01 13:30:00	0	0.254454545454545	87.0	39.0363636363636	1	4
7	2014-01-01 14:00:00	0	0.27752380952381	86.7976190476191	39.2214285714286	1	4
8	2014-01-01 14:30:00	0	0.278571428571429	86.3928571428571	39.2642857142857	1	4
9	2014-01-01 15:00:00	0	0.279619047619048	85.9880952380952	39.3071428571429	1	4
10	2014-01-01 15:30:00	0	0.280666666666667	85.5833333333333	39.35	1	4
11	2014-01-01 16:00:00	0	0.281714285714286	85.1785714285714	39.3928571428571	1	4
12	2014-01-01 16:30:00	0	0.282761904761905	84.7738095238095	39.4357142857143	1	4
13	2014-01-01 17:00:00	0	0.283809523809524	84.3690476190476	39.4785714285714	1	4
14	2014-01-01 17:30:00	0	0.284857142857143	83.9642857142857	39.5214285714286	1	4
15	2014-01-01 18:00:00	0	0.285904761904762	83.5595238095238	39.5642857142857	1	4
16	2014-01-01 18:30:00	0	0.286952380952381	83.1547619047619	39.6071428571429	1	4
17	2014-01-01 19:00:00	0	0.288	82.75	39.65	1	4
18	2014-01-01 19:30:00	0	0.289047619047619	82.3452380952381	39.6928571428571	1	4
19	2014-01-01 20:00:00	0	0.290095238095238	81.9404761904762	39.7357142857143	1	4
20	2014-01-01 20:30:00	0	0.291142857142857	81.5357142857143	39.7785714285714	1	4
21	2014-01-01 21:00:00	0	0.292190476190476	81.1309523809524	39.8214285714286	1	4
22	2014-01-01 21:30:00	0	0.293238095238095	80.7261904761905	39.8642857142857	1	4
23	2014-01-01 22:00:00	0	0.294285714285714	80.3214285714286	39.9071428571429	1	4
24	2014-01-01 22:30:00	0	0.295333333333333	79.9166666666667	39.95	1	4
25	2014-01-01 23:00:00	0	0.296380952380952	79.5119047619048	39.9928571428571	1	4
26	2014-01-01 23:30:00	0	0.297428571428571	79.1071428571429	40.0357142857143	1	4
27	2014-01-02 00:00:00	0	0.298476190476191	78.7023809523809	40.0785714285714	1	4
28	2014-01-02 00:30:00	0	0.29952380952381	78.2976190476191	40.1214285714286	1	4
29	2014-01-02 01:00:00	0	0.300571428571429	77.8928571428571	40.1642857142857	1	4
30	2014-01-02 01:30:00	0	0.301619047619048	77.4880952380952	40.2071428571429	1	4
31	2014-01-02 02:00:00	0	0.302666666666667	77.0833333333333	40.25	1	4
32	2014-01-02 02:30:00	0	0.303714285714286	76.6785714285714	40.2928571428571	1	4
33	2014-01-02 03:00:00	0	0.304761904761905	76.2738095238095	40.3357142857143	1	4
34	2014-01-02 03:30:00	0	0.305809523809524	75.8690476190476	40.3785714285714	1	4
35	2014-01-02 04:00:00	0	0.306857142857143	75.4642857142857	40.4214285714286	1	4
36	2014-01-02 04:30:00	0	0.307904761904762	75.0595238095238	40.4642857142857	1	4
37	2014-01-02 05:00:00	0	0.308952380952381	74.6547619047619	40.5071428571429	1	4
38	2014-01-02 05:30:00	0	0.31	74.25	40.55	1	4
39	2014-01-02 06:00:00	0	0.311047619047619	73.8452380952381	40.5928571428571	1	4
40	2014-01-02 06:30:00	0	0.312095238095238	73.4404761904762	40.6357142857143	1	4
41	2014-01-02 07:00:00	0	0.313142857142857	73.0357142857143	40.6785714285714	1	4
42	2014-01-02 07:30:00	0	0.314190476190476	72.6309523809524	40.7214285714286	1	4
43	2014-01-02 08:00:00	0	0.315238095238095	72.2261904761905	40.7642857142857	1	4
44	2014-01-02 08:30:00	0	0.316285714285714	71.8214285714286	40.8071428571429	1	4
45	2014-01-02 09:00:00	0	0.317333333333333	71.4166666666667	40.85	1	4
46	2014-01-02 09:30:00	0	0.318380952380952	71.0119047619048	40.8928571428571	1	4
47	2014-01-02 10:00:00	0	0.319428571428571	70.6071428571429	40.9357142857143	1	4
48	2014-01-02 10:30:00	0	0.32047619047619	70.2023809523809	40.9785714285714	1	4
49	2014-01-02 11:00:00	0	0.313	72.5	40.1	1	4
50	2014-01-02 11:30:00	0	0.3103	75.1	39.38	1	4
51	2014-01-02 12:00:00	0	0.3209	75.3	39.74	1	4
52	2014-01-02 12:30:00	0	0.3315	75.5	40.1	1	4
53	2014-01-02 13:00:00	0	0.3421	75.7	40.46	1	4
54	2014-01-02 13:30:00	0	0.3527	75.9	40.82	1	4
55	2014-01-02 14:00:00	0	0.323285714285714	76.0	41.0	1	4
56	2014-01-02 14:30:00	0	0.253857142857143	76.0	41.0	1	4
57	2014-01-02 15:00:00	0	0.184428571428571	76.0	41.0	1	4
58	2014-01-02 15:30:00	0	0.115	76.0	41.0	1	4
59	2014-01-02 16:00:00	0	0.113705882352941	75.6764705882353	40.8941176470588	1	4
60	2014-01-02 16:30:00	0	0.112411764705882	75.3529411764706	40.7882352941176	1	4
61	2014-01-02 17:00:00	0	0.111117647058824	75.0294117647059	40.6823529411765	1	4
62	2014-01-02 17:30:00	0	0.109823529411765	74.7058823529412	40.5764705882353	1	4
63	2014-01-02 18:00:00	0	0.108529411764706	74.3823529411765	40.4705882352941	1	4
64	2014-01-02 18:30:00	0	0.107235294117647	74.0588235294118	40.3647058823529	1	4
65	2014-01-02 19:00:00	0	0.105941176470588	73.7352941176471	40.2588235294118	1	4
66	2014-01-02 19:30:00	0	0.104647058823529	73.4117647058823	40.1529411764706	1	4
67	2014-01-02 20:00:00	0	0.103352941176471	73.0882352941177	40.0470588235294	1	4
68	2014-01-02 20:30:00	0	0.102058823529412	72.7647058823529	39.9411764705882	1	4
69	2014-01-02 21:00:00	0	0.100764705882353	72.4411764705882	39.8352941176471	1	4
70	2014-01-02 21:30:00	0	0.0994705882352941	72.1176470588235	39.7294117647059	1	4
71	2014-01-02 22:00:00	0	0.0981764705882353	71.7941176470588	39.6235294117647	1	4
72	2014-01-02 22:30:00	0	0.0968823529411765	71.4705882352941	39.5176470588235	1	4
73	2014-01-02 23:00:00	0	0.0955882352941176	71.1470588235294	39.4117647058824	1	4
74	2014-01-02 23:30:00	0	0.0942941176470588	70.8235294117647	39.3058823529412	1	4
75	2014-01-03 00:00:00	0	0.093	70.5	39.2	1	4
76	2014-01-03 00:30:00	0	0.0917058823529412	70.1764705882353	39.0941176470588	1	4
77	2014-01-03 01:00:00	0	0.0904117647058824	69.8529411764706	38.9882352941176	1	4
78	2014-01-03 01:30:00	0	0.0891176470588235	69.5294117647059	38.8823529411765	1	4
79	2014-01-03 02:00:00	0	0.0878235294117647	69.2058823529412	38.7764705882353	1	4
80	2014-01-03 02:30:00	0	0.0865294117647059	68.8823529411765	38.6705882352941	1	4
81	2014-01-03 03:00:00	0	0.0852352941176471	68.5588235294118	38.5647058823529	1	4
82	2014-01-03 03:30:00	0	0.0839411764705882	68.2352941176471	38.4588235294118	1	4
83	2014-01-03 04:00:00	0	0.0826470588235294	67.9117647058823	38.3529411764706	1	4
84	2014-01-03 04:30:00	0	0.0813529411764706	67.5882352941177	38.2470588235294	1	4
85	2014-01-03 05:00:00	0	0.0800588235294118	67.2647058823529	38.1411764705882	1	4
86	2014-01-03 05:30:00	0	0.0787647058823529	66.9411764705882	38.0352941176471	1	4
87	2014-01-03 06:00:00	0	0.0774705882352941	66.6176470588235	37.9294117647059	1	4
88	2014-01-03 06:30:00	0	0.0761764705882353	66.2941176470588	37.8235294117647	1	4
89	2014-01-03 07:00:00	0	0.0748823529411765	65.9705882352941	37.7176470588235	1	4
90	2014-01-03 07:30:00	0	0.0735882352941176	65.6470588235294	37.6117647058824	1	4
91	2014-01-03 08:00:00	0	0.0722941176470588	65.3235294117647	37.5058823529412	1	4
92	2014-01-03 08:30:00	0	0.071	65.0	37.4	1	4
93	2014-01-03 09:00:00	0	0.0742101910828025	65.4458598726115	37.2853503184713	1	4
94	2014-01-03 09:30:00	0	0.0774203821656051	65.8917197452229	37.1707006369427	1	4
95	2014-01-03 10:00:00	0	0.0806305732484076	66.3375796178344	37.056050955414	1	4
96	2014-01-03 10:30:00	0	0.0838407643312102	66.7834394904459	36.9414012738853	1	4
97	2014-01-03 11:00:00	0	0.0870509554140127	67.2292993630573	36.8267515923567	1	4
98	2014-01-03 11:30:00	0	0.0902611464968153	67.6751592356688	36.712101910828	1	4
99	2014-01-03 12:00:00	0	0.0934713375796178	68.1210191082803	36.5974522292994	1	4
100	2014-01-03 12:30:00	0	0.0966815286624204	68.5668789808917	36.4828025477707	1	4

Rows: 1-100 | Columns: 7

Let's aggregate the data to figure out the monthly energy consumption for each smart meter. We can then save the result in the Vertica database.

In [18]:

import verticapy.stats as st
sm_consumption_month = sm_consumption_weather_region_clean.groupby(
                    columns = ["meterID",
                               "region", 
                               "residenceType",
                               "MONTH(dateUTC) AS month",
                               "DATE_TRUNC('MONTH', dateUTC::date) AS date_month"],
                    expr = [st.sum(sm_consumption_weather_region["value"])._as("value"),
                            st.avg(sm_consumption_weather_region["temperature"])._as("avg_temperature"),
                            st.avg(sm_consumption_weather_region["humidity"])._as("avg_humidity")]).filter(
                                                                                    "date_month < '2015-09-01'")
vp.drop("sm_consumption_month", method = "table")
sm_consumption_month.to_db("sm_consumption_month",
                           relation_type = "table",
                           inplace = True)

Out[18]:

	123 meterID Int	123 region Int	123 residenceType Int	123 month Int	📅 date_month Timestamp	123 value Float	123 avg_temperature Float	123 avg_humidity Float
1	0	4	1	1	2014-01-01 00:00:00	1094.46976779318	40.119591413743	85.3862434750278
2	0	4	1	1	2015-01-01 00:00:00	461.098914329082	35.869996173874	91.9545988846073
3	0	4	1	2	2014-02-01 00:00:00	859.31162254347	41.1467965437012	87.3618817946322
4	0	4	1	2	2015-02-01 00:00:00	519.575404676496	36.678474318856	91.6393096571925
5	0	4	1	3	2014-03-01 00:00:00	385.589366557516	44.6128611427443	83.7845727673415
6	0	4	1	3	2015-03-01 00:00:00	402.457339761135	40.4876517285163	85.0905843339206
7	0	4	1	4	2014-04-01 00:00:00	392.35356218693	47.7098006324147	84.8403939698354
8	0	4	1	4	2015-04-01 00:00:00	293.31733758381	45.076406559308	80.6242208952574
9	0	4	1	5	2014-05-01 00:00:00	294.370028345716	51.3385783584912	83.9470163357567
10	0	4	1	5	2015-05-01 00:00:00	429.379563316476	47.9887478433461	83.8925132071737
11	0	4	1	6	2014-06-01 00:00:00	416.720396781268	56.2820908129162	80.0076357451401
12	0	4	1	6	2015-06-01 00:00:00	375.482099254297	57.8323936579065	80.5494105121803
13	0	4	1	7	2014-07-01 00:00:00	517.72659830194	58.2372942418422	82.5882473588889
14	0	4	1	7	2015-07-01 00:00:00	608.348732008156	60.8429870367751	78.4408499785012
15	0	4	1	8	2014-08-01 00:00:00	351.445887089358	60.445880559731	80.7871798502449
16	0	4	1	8	2015-08-01 00:00:00	663.947376452767	57.1943705007309	82.1793258688277
17	0	4	1	9	2014-09-01 00:00:00	540.261174161572	56.0029222042167	81.0310786590966
18	0	4	1	10	2014-10-01 00:00:00	387.217433071438	51.9100363545349	89.3897158191042
19	0	4	1	11	2014-11-01 00:00:00	654.683162814267	45.5062283911201	87.4771741861059
20	0	4	1	12	2014-12-01 00:00:00	467.379542027498	38.6945513222086	91.3160798419345
21	1	4	1	1	2014-01-01 00:00:00	582.930645498649	39.1203337581871	86.2281047089881
22	1	4	1	1	2015-01-01 00:00:00	733.173393121194	35.9825558400398	92.9931707977183
23	1	4	1	2	2014-02-01 00:00:00	328.54410112299	40.740630740952	88.6208589271437
24	1	4	1	2	2015-02-01 00:00:00	664.908073132265	36.107796743387	92.0228801611064
25	1	4	1	3	2014-03-01 00:00:00	750.141792536086	44.8419856422442	81.4965569252717
26	1	4	1	3	2015-03-01 00:00:00	747.696838463836	42.3819388480604	77.8892704917467
27	1	4	1	4	2014-04-01 00:00:00	303.626659810666	48.4319817204848	83.1294834473743
28	1	4	1	4	2015-04-01 00:00:00	333.364034859568	46.5222313993991	79.1965773965361
29	1	4	1	5	2014-05-01 00:00:00	571.635392258685	51.004360050995	81.7882238039068
30	1	4	1	5	2015-05-01 00:00:00	384.703755977335	50.7648217158462	78.4548447843953
31	1	4	1	6	2014-06-01 00:00:00	923.50266665197	58.1899828876613	77.4562080205899
32	1	4	1	6	2015-06-01 00:00:00	497.588239654778	58.0582355051984	80.2845965071549
33	1	4	1	7	2014-07-01 00:00:00	922.011508871944	58.8581826007247	83.6376663297095
34	1	4	1	7	2015-07-01 00:00:00	764.122138113839	59.7641731628079	79.953929990897
35	1	4	1	8	2014-08-01 00:00:00	433.437973518908	59.9149831870364	81.4356536158466
36	1	4	1	8	2015-08-01 00:00:00	296.335827223888	56.5667165639184	83.0268955395646
37	1	4	1	9	2014-09-01 00:00:00	605.074585796616	55.3237520701335	84.3041796784501
38	1	4	1	10	2014-10-01 00:00:00	438.547793700157	52.7157314638124	88.2888190729199
39	1	4	1	11	2014-11-01 00:00:00	565.165186604843	46.0061965452299	88.1022602608813
40	1	4	1	12	2014-12-01 00:00:00	598.063668190386	39.7721692849925	89.9018765295354
41	2	[null]	[null]	1	2014-01-01 00:00:00	[null]	[null]	[null]
42	2	4	1	1	2014-01-01 00:00:00	482.385194415	39.9039779162692	84.9491344603949
43	2	4	1	1	2015-01-01 00:00:00	849.901361174881	35.9205668118582	94.0992998737685
44	2	4	1	2	2014-02-01 00:00:00	379.595207613914	41.7625415101088	86.0871432645725
45	2	4	1	2	2015-02-01 00:00:00	657.707129834481	37.27154977376	90.4227418530055
46	2	4	1	3	2014-03-01 00:00:00	599.68447979385	44.792189195216	80.8285802271696
47	2	4	1	3	2015-03-01 00:00:00	581.380821088508	41.0998868518086	82.2095048876506
48	2	4	1	4	2014-04-01 00:00:00	470.588861292747	48.8083103078897	85.081830737512
49	2	4	1	4	2015-04-01 00:00:00	489.657988070767	47.1560235769365	77.0781891119151
50	2	4	1	5	2014-05-01 00:00:00	471.781622418939	51.1973446120168	84.3597425444918
51	2	4	1	5	2015-05-01 00:00:00	757.210491720693	50.5256095991876	78.7060994156184
52	2	4	1	6	2014-06-01 00:00:00	327.683339844742	55.9892605973656	82.169875343245
53	2	4	1	6	2015-06-01 00:00:00	381.08542291468	57.3275542948393	80.3458444814076
54	2	4	1	7	2014-07-01 00:00:00	473.421270263075	58.5948017216789	83.3348880025485
55	2	4	1	7	2015-07-01 00:00:00	285.32799526757	60.5313451847624	79.4181890210453
56	2	4	1	8	2014-08-01 00:00:00	341.386077443064	59.0586914882815	84.0634813017688
57	2	4	1	8	2015-08-01 00:00:00	320.780488313278	57.5687383243221	81.8807510916972
58	2	4	1	9	2014-09-01 00:00:00	257.285824722601	53.8704096061919	84.3442042309829
59	2	4	1	10	2014-10-01 00:00:00	434.151749742704	52.7687529570786	86.9737490248736
60	2	4	1	11	2014-11-01 00:00:00	500.065643439472	44.5910948698587	87.2311202524699
61	2	4	1	12	2014-12-01 00:00:00	721.398564589889	38.2969758676522	90.2032059995848
62	3	2	3	1	2014-01-01 00:00:00	442.99480528961	40.6046656689185	83.9113654361121
63	3	2	3	1	2015-01-01 00:00:00	971.737924806592	35.6551527545116	92.535877588346
64	3	2	3	2	2014-02-01 00:00:00	525.158746795731	40.4426979459742	88.9358369295487
65	3	2	3	2	2015-02-01 00:00:00	382.678128060429	35.54281941268	92.6688186376604
66	3	2	3	3	2014-03-01 00:00:00	318.442327079303	43.5059208447236	86.1035963115818
67	3	2	3	3	2015-03-01 00:00:00	491.438896053892	40.5464324996594	84.1240029140989
68	3	2	3	4	2014-04-01 00:00:00	565.050198893912	47.522038079698	85.9595189590546
69	3	2	3	4	2015-04-01 00:00:00	285.705378454427	46.1069185008593	78.6967913955865
70	3	2	3	5	2014-05-01 00:00:00	345.643714542942	51.460314664719	80.5660289985841
71	3	2	3	5	2015-05-01 00:00:00	434.309296272865	49.7488697535965	79.7188663290957
72	3	2	3	6	2014-06-01 00:00:00	311.656918158736	55.6055845502194	82.0744127371383
73	3	2	3	6	2015-06-01 00:00:00	375.757147429354	57.2342024626334	82.44084305558
74	3	2	3	7	2014-07-01 00:00:00	340.699347999806	59.4750738500173	80.5535345355014
75	3	2	3	7	2015-07-01 00:00:00	250.660102439874	60.1340129394979	81.2173259905666
76	3	2	3	8	2014-08-01 00:00:00	423.700154333498	60.2822176246415	77.9895358313569
77	3	2	3	8	2015-08-01 00:00:00	345.549313927961	57.3530591666175	80.0326119273861
78	3	2	3	9	2014-09-01 00:00:00	368.35656422304	55.0981256436896	83.2219195158294
79	3	2	3	10	2014-10-01 00:00:00	419.688038772183	52.9113727942871	87.0375524785344
80	3	2	3	11	2014-11-01 00:00:00	650.709053040711	45.0930851317603	88.229242580969
81	3	2	3	12	2014-12-01 00:00:00	590.575509218171	38.2958040514279	89.2197799286013
82	4	[null]	[null]	1	2014-01-01 00:00:00	[null]	[null]	[null]
83	4	4	1	1	2014-01-01 00:00:00	490.953253447234	40.0484140961615	87.6757310001256
84	4	4	1	1	2015-01-01 00:00:00	529.470431111594	36.0919536712077	93.5570243176922
85	4	4	1	2	2014-02-01 00:00:00	466.95719234148	41.1487864918148	87.9046866890208
86	4	4	1	2	2015-02-01 00:00:00	463.277408470973	36.1340770671507	90.5861334174828
87	4	4	1	3	2014-03-01 00:00:00	233.392436614671	43.5980854138578	85.9600088369246
88	4	4	1	3	2015-03-01 00:00:00	434.873190060048	40.955478652692	82.517165280247
89	4	4	1	4	2014-04-01 00:00:00	578.127585298524	47.5648730787814	85.4902693533969
90	4	4	1	4	2015-04-01 00:00:00	549.916414970295	46.0762366380595	81.0147476646827
91	4	4	1	5	2014-05-01 00:00:00	383.532471664706	52.2187648018763	80.6567902387458
92	4	4	1	5	2015-05-01 00:00:00	389.753323992759	51.6890144921101	76.7850317191888
93	4	4	1	6	2014-06-01 00:00:00	387.785194780185	57.585684455242	76.4935770006317
94	4	4	1	6	2015-06-01 00:00:00	482.108257434238	57.4503125631102	80.8217663185125
95	4	4	1	7	2014-07-01 00:00:00	360.345725469969	57.6763352373087	87.0106795700421
96	4	4	1	7	2015-07-01 00:00:00	395.686490352548	60.2653453988963	81.6996901183243
97	4	4	1	8	2014-08-01 00:00:00	405.073759509101	60.3791581947677	80.0265350900232
98	4	4	1	8	2015-08-01 00:00:00	659.941610976061	56.8122803603284	82.112426029538
99	4	4	1	9	2014-09-01 00:00:00	269.21473595903	55.8019980282461	81.6661321023242
100	4	4	1	10	2014-10-01 00:00:00	427.230302747789	52.7956093220194	88.2320107845067

Rows: 1-100 | Columns: 8

Understanding the Data & Detecting Outliers

Looking at three different smart meters, we can see a clear decrease in energy consumption during the summer followed by a sharp increase in the winter.

In [19]:

for i in [10, 12, 14]:
    sm_consumption_month[sm_consumption_month["meterID"] == i]["value"].plot(ts = "date_month")

This behavior seems to be seasonal, but we don't have enough data to prove this.

Let's find outliers in the distribution by computing the ZSCORE per meterID.

In [20]:

std = st.std(sm_consumption_month["value"])._over(by = [sm_consumption_month["meterID"]])
avg = st.avg(sm_consumption_month["value"])._over(by = [sm_consumption_month["meterID"]])
sm_consumption_month["value_zscore"] = (sm_consumption_month["value"] - avg) / std
sm_consumption_month.search("value_zscore > 4")

Out[20]:

	123 meterID Integer	123 region Integer	123 residenceType Integer	123 month Integer	📅 date_month Datetime	123 value Float	123 avg_temperature Float	123 avg_humidity Float	123 value_zscore Float
1	364	2	1	12	2014-12-01 00:00:00	1852.04580636529	39.2305333549752	89.9652523028262	4.00855200430863
2	399	4	1	10	2014-10-01 00:00:00	1589.23770463456	52.7068558110441	88.7730360914782	4.07298404322647
3	809	4	1	11	2014-11-01 00:00:00	1300.55731708241	45.5709955755231	86.4715802984117	4.0151606376986
4	951	4	1	4	2015-04-01 00:00:00	2128.45466198574	48.16211775914	73.6313461768108	4.01829822269677

Rows: 1-4 | Columns: 9

Four smart meters are outliers in energy consumption. We'll need to investigate to get more information.

In [21]:

for i in [364, 399, 809, 951]:
    sm_consumption_month[sm_consumption_month["meterID"] == i]["value"].plot(ts = "date_month")

Data Encoding & Bivariate Analysis

Since most of our data is categorical, let's encode them with One-hot encoding. We can then examine the correlations between the various categories.

In [22]:

sm_consumption_month = sm_consumption_month.one_hot_encode(["region", "residenceType", "month"], 
                                                           drop_first = False,
                                                           max_cardinality = 20)
display(sm_consumption_month)

	123 meterID Int	123 region Int	123 residenceType Int	123 month Int	📅 date_month Timestamp	123 value Float	123 avg_temperature Float	123 avg_humidity Float	123 value_zscore Float	123 region_0 Bool	123 region_1 Bool	123 region_2 Bool	123 region_3 Bool	123 region_4 Bool	123 residenceType_1 Bool	123 residenceType_2 Bool	123 residenceType_3 Bool	123 month_1 Bool	123 month_2 Bool	123 month_3 Bool	123 month_4 Bool	123 month_5 Bool	123 month_6 Bool	123 month_7 Bool	123 month_8 Bool	123 month_9 Bool	123 month_10 Bool	123 month_11 Bool	123 month_12 Bool
1	0	4	1	1	2014-01-01 00:00:00	1094.46976779318	40.119591413743	85.3862434750278	3.00202923664348	0	0	0	0	1	1	0	0	1	0	0	0	0	0	0	0	0	0	0	0
2	0	4	1	1	2015-01-01 00:00:00	461.098914329082	35.869996173874	91.9545988846073	-0.22772416132508	0	0	0	0	1	1	0	0	1	0	0	0	0	0	0	0	0	0	0	0
3	0	4	1	2	2014-02-01 00:00:00	859.31162254347	41.1467965437012	87.3618817946322	1.80288529983888	0	0	0	0	1	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0
4	0	4	1	2	2015-02-01 00:00:00	519.575404676496	36.678474318856	91.6393096571925	0.0704655049152898	0	0	0	0	1	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0
5	0	4	1	3	2014-03-01 00:00:00	385.589366557516	44.6128611427443	83.7845727673415	-0.612770642640802	0	0	0	0	1	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0
6	0	4	1	3	2015-03-01 00:00:00	402.457339761135	40.4876517285163	85.0905843339206	-0.526755642939972	0	0	0	0	1	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0
7	0	4	1	4	2014-04-01 00:00:00	392.35356218693	47.7098006324147	84.8403939698354	-0.57827792214516	0	0	0	0	1	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0
8	0	4	1	4	2015-04-01 00:00:00	293.31733758381	45.076406559308	80.6242208952574	-1.083294187394	0	0	0	0	1	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0
9	0	4	1	5	2014-05-01 00:00:00	294.370028345716	51.3385783584912	83.9470163357567	-1.07792619240863	0	0	0	0	1	1	0	0	0	0	0	0	1	0	0	0	0	0	0	0
10	0	4	1	5	2015-05-01 00:00:00	429.379563316476	47.9887478433461	83.8925132071737	-0.389470918622196	0	0	0	0	1	1	0	0	0	0	0	0	1	0	0	0	0	0	0	0
11	0	4	1	6	2014-06-01 00:00:00	416.720396781268	56.2820908129162	80.0076357451401	-0.454023914562756	0	0	0	0	1	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0
12	0	4	1	6	2015-06-01 00:00:00	375.482099254297	57.8323936579065	80.5494105121803	-0.664310717050938	0	0	0	0	1	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0
13	0	4	1	7	2014-07-01 00:00:00	517.72659830194	58.2372942418422	82.5882473588889	0.0610378707926432	0	0	0	0	1	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0
14	0	4	1	7	2015-07-01 00:00:00	608.348732008156	60.8429870367751	78.4408499785012	0.523148090528864	0	0	0	0	1	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0
15	0	4	1	8	2014-08-01 00:00:00	351.445887089358	60.445880559731	80.7871798502449	-0.786878778857722	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0
16	0	4	1	8	2015-08-01 00:00:00	663.947376452767	57.1943705007309	82.1793258688277	0.806662732598842	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0
17	0	4	1	9	2014-09-01 00:00:00	540.261174161572	56.0029222042167	81.0310786590966	0.175948625773495	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	1	0	0	0
18	0	4	1	10	2014-10-01 00:00:00	387.217433071438	51.9100363545349	89.3897158191042	-0.604468629173181	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0
19	0	4	1	11	2014-11-01 00:00:00	654.683162814267	45.5062283911201	87.4771741861059	0.75942164893545	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0
20	0	4	1	12	2014-12-01 00:00:00	467.379542027498	38.6945513222086	91.3160798419345	-0.195697302906525	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	1
21	1	4	1	1	2014-01-01 00:00:00	582.930645498649	39.1203337581871	86.2281047089881	0.0548245114853174	0	0	0	0	1	1	0	0	1	0	0	0	0	0	0	0	0	0	0	0
22	1	4	1	1	2015-01-01 00:00:00	733.173393121194	35.9825558400398	92.9931707977183	0.824497262186302	0	0	0	0	1	1	0	0	1	0	0	0	0	0	0	0	0	0	0	0
23	1	4	1	2	2014-02-01 00:00:00	328.54410112299	40.740630740952	88.6208589271437	-1.24836246059288	0	0	0	0	1	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0
24	1	4	1	2	2015-02-01 00:00:00	664.908073132265	36.107796743387	92.0228801611064	0.474783499327259	0	0	0	0	1	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0
25	1	4	1	3	2014-03-01 00:00:00	750.141792536086	44.8419856422442	81.4965569252717	0.911424018115072	0	0	0	0	1	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0
26	1	4	1	3	2015-03-01 00:00:00	747.696838463836	42.3819388480604	77.8892704917467	0.898898857627101	0	0	0	0	1	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0
27	1	4	1	4	2014-04-01 00:00:00	303.626659810666	48.4319817204848	83.1294834473743	-1.37601105527559	0	0	0	0	1	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0
28	1	4	1	4	2015-04-01 00:00:00	333.364034859568	46.5222313993991	79.1965773965361	-1.22367060880014	0	0	0	0	1	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0
29	1	4	1	5	2014-05-01 00:00:00	571.635392258685	51.004360050995	81.7882238039068	-0.0030395037076764	0	0	0	0	1	1	0	0	0	0	0	0	1	0	0	0	0	0	0	0
30	1	4	1	5	2015-05-01 00:00:00	384.703755977335	50.7648217158462	78.4548447843953	-0.96066434062269	0	0	0	0	1	1	0	0	0	0	0	0	1	0	0	0	0	0	0	0
31	1	4	1	6	2014-06-01 00:00:00	923.50266665197	58.1899828876613	77.4562080205899	1.79952772332632	0	0	0	0	1	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0
32	1	4	1	6	2015-06-01 00:00:00	497.588239654778	58.0582355051984	80.2845965071549	-0.382372792855224	0	0	0	0	1	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0
33	1	4	1	7	2014-07-01 00:00:00	922.011508871944	58.8581826007247	83.6376663297095	1.79188872890933	0	0	0	0	1	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0
34	1	4	1	7	2015-07-01 00:00:00	764.122138113839	59.7641731628079	79.953929990897	0.983043388812507	0	0	0	0	1	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0
35	1	4	1	8	2014-08-01 00:00:00	433.437973518908	59.9149831870364	81.4356536158466	-0.711005705766532	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0
36	1	4	1	8	2015-08-01 00:00:00	296.335827223888	56.5667165639184	83.0268955395646	-1.41336097905403	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0
37	1	4	1	9	2014-09-01 00:00:00	605.074585796616	55.3237520701335	84.3041796784501	0.168264845279752	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	1	0	0	0
38	1	4	1	10	2014-10-01 00:00:00	438.547793700157	52.7157314638124	88.2888190729199	-0.684828805937927	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0
39	1	4	1	11	2014-11-01 00:00:00	565.165186604843	46.0061965452299	88.1022602608813	-0.0361854695662386	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0
40	1	4	1	12	2014-12-01 00:00:00	598.063668190386	39.7721692849925	89.9018765295354	0.132348887109964	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	1
41	2	[null]	[null]	1	2014-01-01 00:00:00	[null]	[null]	[null]	[null]	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
42	2	4	1	1	2014-01-01 00:00:00	482.385194415	39.9039779162692	84.9491344603949	-0.0413592525534165	0	0	0	0	1	1	0	0	1	0	0	0	0	0	0	0	0	0	0	0
43	2	4	1	1	2015-01-01 00:00:00	849.901361174881	35.9205668118582	94.0992998737685	2.21426992785248	0	0	0	0	1	1	0	0	1	0	0	0	0	0	0	0	0	0	0	0
44	2	4	1	2	2014-02-01 00:00:00	379.595207613914	41.7625415101088	86.0871432645725	-0.672232434884875	0	0	0	0	1	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0
45	2	4	1	2	2015-02-01 00:00:00	657.707129834481	37.27154977376	90.4227418530055	1.03467850922427	0	0	0	0	1	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0
46	2	4	1	3	2014-03-01 00:00:00	599.68447979385	44.792189195216	80.8285802271696	0.678564698749006	0	0	0	0	1	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0
47	2	4	1	3	2015-03-01 00:00:00	581.380821088508	41.0998868518086	82.2095048876506	0.566226057845256	0	0	0	0	1	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0
48	2	4	1	4	2014-04-01 00:00:00	470.588861292747	48.8083103078897	85.081830737512	-0.113759205586572	0	0	0	0	1	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0
49	2	4	1	4	2015-04-01 00:00:00	489.657988070767	47.1560235769365	77.0781891119151	0.00327749291998388	0	0	0	0	1	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0
50	2	4	1	5	2014-05-01 00:00:00	471.781622418939	51.1973446120168	84.3597425444918	-0.106438638371225	0	0	0	0	1	1	0	0	0	0	0	0	1	0	0	0	0	0	0	0
51	2	4	1	5	2015-05-01 00:00:00	757.210491720693	50.5256095991876	78.7060994156184	1.64538004270448	0	0	0	0	1	1	0	0	0	0	0	0	1	0	0	0	0	0	0	0
52	2	4	1	6	2014-06-01 00:00:00	327.683339844742	55.9892605973656	82.169875343245	-0.990841340669473	0	0	0	0	1	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0
53	2	4	1	6	2015-06-01 00:00:00	381.08542291468	57.3275542948393	80.3458444814076	-0.663086243719655	0	0	0	0	1	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0
54	2	4	1	7	2014-07-01 00:00:00	473.421270263075	58.5948017216789	83.3348880025485	-0.0963753054968396	0	0	0	0	1	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0
55	2	4	1	7	2015-07-01 00:00:00	285.32799526757	60.5313451847624	79.4181890210453	-1.25079711898721	0	0	0	0	1	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0
56	2	4	1	8	2014-08-01 00:00:00	341.386077443064	59.0586914882815	84.0634813017688	-0.906740836872007	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0
57	2	4	1	8	2015-08-01 00:00:00	320.780488313278	57.5687383243221	81.8807510916972	-1.03320756765658	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0
58	2	4	1	9	2014-09-01 00:00:00	257.285824722601	53.8704096061919	84.3442042309829	-1.42290584204232	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	1	0	0	0
59	2	4	1	10	2014-10-01 00:00:00	434.151749742704	52.7687529570786	86.9737490248736	-0.337391849523244	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0
60	2	4	1	11	2014-11-01 00:00:00	500.065643439472	44.5910948698587	87.2311202524699	0.0671544411267175	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0
61	2	4	1	12	2014-12-01 00:00:00	721.398564589889	38.2969758676522	90.2032059995848	1.42558446594122	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	1
62	3	2	3	1	2014-01-01 00:00:00	442.99480528961	40.6046656689185	83.9113654361121	0.00593259852715639	0	0	1	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0
63	3	2	3	1	2015-01-01 00:00:00	971.737924806592	35.6551527545116	92.535877588346	3.24234744481631	0	0	1	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0
64	3	2	3	2	2014-02-01 00:00:00	525.158746795731	40.4426979459742	88.9358369295487	0.508854698701174	0	0	1	0	0	0	0	1	0	1	0	0	0	0	0	0	0	0	0	0
65	3	2	3	2	2015-02-01 00:00:00	382.678128060429	35.54281941268	92.6688186376604	-0.363263297282671	0	0	1	0	0	0	0	1	0	1	0	0	0	0	0	0	0	0	0	0
66	3	2	3	3	2014-03-01 00:00:00	318.442327079303	43.5059208447236	86.1035963115818	-0.756447988080173	0	0	1	0	0	0	0	1	0	0	1	0	0	0	0	0	0	0	0	0
67	3	2	3	3	2015-03-01 00:00:00	491.438896053892	40.5464324996594	84.1240029140989	0.302456881827006	0	0	1	0	0	0	0	1	0	0	1	0	0	0	0	0	0	0	0	0
68	3	2	3	4	2014-04-01 00:00:00	565.050198893912	47.522038079698	85.9595189590546	0.753028633160173	0	0	1	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0
69	3	2	3	4	2015-04-01 00:00:00	285.705378454427	46.1069185008593	78.6967913955865	-0.956829501599522	0	0	1	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0
70	3	2	3	5	2014-05-01 00:00:00	345.643714542942	51.460314664719	80.5660289985841	-0.589949416321158	0	0	1	0	0	0	0	1	0	0	0	0	1	0	0	0	0	0	0	0
71	3	2	3	5	2015-05-01 00:00:00	434.309296272865	49.7488697535965	79.7188663290957	-0.0472310442547154	0	0	1	0	0	0	0	1	0	0	0	0	1	0	0	0	0	0	0	0
72	3	2	3	6	2014-06-01 00:00:00	311.656918158736	55.6055845502194	82.0744127371383	-0.797981196472041	0	0	1	0	0	0	0	1	0	0	0	0	0	1	0	0	0	0	0	0
73	3	2	3	6	2015-06-01 00:00:00	375.757147429354	57.2342024626334	82.44084305558	-0.405626334527961	0	0	1	0	0	0	0	1	0	0	0	0	0	1	0	0	0	0	0	0
74	3	2	3	7	2014-07-01 00:00:00	340.699347999806	59.4750738500173	80.5535345355014	-0.620213680185845	0	0	1	0	0	0	0	1	0	0	0	0	0	0	1	0	0	0	0	0
75	3	2	3	7	2015-07-01 00:00:00	250.660102439874	60.1340129394979	81.2173259905666	-1.17134019195028	0	0	1	0	0	0	0	1	0	0	0	0	0	0	1	0	0	0	0	0
76	3	2	3	8	2014-08-01 00:00:00	423.700154333498	60.2822176246415	77.9895358313569	-0.112169164887513	0	0	1	0	0	0	0	1	0	0	0	0	0	0	0	1	0	0	0	0
77	3	2	3	8	2015-08-01 00:00:00	345.549313927961	57.3530591666175	80.0326119273861	-0.590527238595423	0	0	1	0	0	0	0	1	0	0	0	0	0	0	0	1	0	0	0	0
78	3	2	3	9	2014-09-01 00:00:00	368.35656422304	55.0981256436896	83.2219195158294	-0.450924999372992	0	0	1	0	0	0	0	1	0	0	0	0	0	0	0	0	1	0	0	0
79	3	2	3	10	2014-10-01 00:00:00	419.688038772183	52.9113727942871	87.0375524785344	-0.136727158908371	0	0	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0	1	0	0
80	3	2	3	11	2014-11-01 00:00:00	650.709053040711	45.0930851317603	88.229242580969	1.27734294959056	0	0	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	1	0
81	3	2	3	12	2014-12-01 00:00:00	590.575509218171	38.2958040514279	89.2197799286013	0.909268005816287	0	0	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	1
82	4	[null]	[null]	1	2014-01-01 00:00:00	[null]	[null]	[null]	[null]	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
83	4	4	1	1	2014-01-01 00:00:00	490.953253447234	40.0484140961615	87.6757310001256	0.524106070436419	0	0	0	0	1	1	0	0	1	0	0	0	0	0	0	0	0	0	0	0
84	4	4	1	1	2015-01-01 00:00:00	529.470431111594	36.0919536712077	93.5570243176922	0.899495176350039	0	0	0	0	1	1	0	0	1	0	0	0	0	0	0	0	0	0	0	0
85	4	4	1	2	2014-02-01 00:00:00	466.95719234148	41.1487864918148	87.9046866890208	0.290240027539082	0	0	0	0	1	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0
86	4	4	1	2	2015-02-01 00:00:00	463.277408470973	36.1340770671507	90.5861334174828	0.254376787788181	0	0	0	0	1	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0
87	4	4	1	3	2014-03-01 00:00:00	233.392436614671	43.5980854138578	85.9600088369246	-1.98608628040708	0	0	0	0	1	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0
88	4	4	1	3	2015-03-01 00:00:00	434.873190060048	40.955478652692	82.517165280247	-0.022451235447758	0	0	0	0	1	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0
89	4	4	1	4	2014-04-01 00:00:00	578.127585298524	47.5648730787814	85.4902693533969	1.37370867573199	0	0	0	0	1	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0
90	4	4	1	4	2015-04-01 00:00:00	549.916414970295	46.0762366380595	81.0147476646827	1.0987621025803	0	0	0	0	1	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0
91	4	4	1	5	2014-05-01 00:00:00	383.532471664706	52.2187648018763	80.6567902387458	-0.522818799845478	0	0	0	0	1	1	0	0	0	0	0	0	1	0	0	0	0	0	0	0
92	4	4	1	5	2015-05-01 00:00:00	389.753323992759	51.6890144921101	76.7850317191888	-0.462190261228294	0	0	0	0	1	1	0	0	0	0	0	0	1	0	0	0	0	0	0	0
93	4	4	1	6	2014-06-01 00:00:00	387.785194780185	57.585684455242	76.4935770006317	-0.481371683913744	0	0	0	0	1	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0
94	4	4	1	6	2015-06-01 00:00:00	482.108257434238	57.4503125631102	80.8217663185125	0.437902580293363	0	0	0	0	1	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0
95	4	4	1	7	2014-07-01 00:00:00	360.345725469969	57.6763352373087	87.0106795700421	-0.748797245071543	0	0	0	0	1	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0
96	4	4	1	7	2015-07-01 00:00:00	395.686490352548	60.2653453988963	81.6996901183243	-0.404365515229353	0	0	0	0	1	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0
97	4	4	1	8	2014-08-01 00:00:00	405.073759509101	60.3791581947677	80.0265350900232	-0.312877021281114	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0
98	4	4	1	8	2015-08-01 00:00:00	659.941610976061	56.8122803603284	82.112426029538	2.17106964069964	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0
99	4	4	1	9	2014-09-01 00:00:00	269.21473595903	55.8019980282461	81.6661321023242	-1.63696150704416	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	1	0	0	0
100	4	4	1	10	2014-10-01 00:00:00	427.230302747789	52.7956093220194	88.2320107845067	-0.0969389525839496	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0

Rows: 1-100 of 20462 | Columns: 29

Let's compute the Pearson correlation matrix.

In [23]:

sm_consumption_month.corr()

Out[23]:

	"meterID"	"region"	"residenceType"	"month"	"value"	"avg_temperature"	"avg_humidity"	"value_zscore"	"region_0"	"region_1"	"region_2"	"region_3"	"region_4"	"residenceType_1"	"residenceType_2"	"residenceType_3"	"month_1"	"month_2"	"month_3"	"month_4"	"month_5"	"month_6"	"month_7"	"month_8"	"month_9"	"month_10"	"month_11"	"month_12"
"meterID"	1.0	-0.0383154119269016	0.0980146907774023	-0.000958239223703914	0.000429585019171308	-0.000388408483331499	-0.00036832764708373	-6.06712073807718e-18	0.013651309137	0.0466265869557577	0.00328987359897663	-0.013700995176311	-0.0265760464510187	-0.102365768266218	0.0467577567557462	0.0816737879786041	0.00182472281671094	-0.000222116232717449	-0.000222116232717449	-0.000222116232717449	-0.000222116232717449	-0.000222116232717449	-0.000222116232717449	-0.000222116232717449	-0.000152971646153471	-0.000152971646153471	-0.000152971646153471	-0.000152971646153471
"region"	-0.0383154119269016	1.0	0.0166975561248509	0.0	0.00391838085849298	0.000771742974758161	-0.00217981408380471	-3.74313875208588e-18	-0.564373811092644	-0.539485038229266	-0.474356114920355	-0.0850796152695257	0.907172644025799	-0.0310062198052858	0.0574944611713134	0.000324658415336287	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
"residenceType"	0.0980146907774023	0.0166975561248509	1.0	0.0	-0.00058821325675922	-0.000214800019030768	0.00183889966163093	-6.57284327067527e-18	-0.00435561722873074	0.00168175653142086	-0.00460956649743866	-0.0623601449658743	0.0350513980015978	-0.964066970378623	0.0987873344174206	0.960040414014256	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
"month"	-0.000958239223703914	0.0	0.0	1.0	-0.0631050387695357	0.469805916060245	-0.0101794307863282	-0.0673472424493425	0.00589382858218876	0.0079672351655185	0.0119564967832839	0.00735660635197768	0.0532908661867017	0.0410984240967133	0.00902473393159095	0.021276792503283	-0.525142347609341	-0.365619082725405	-0.263885316907362	-0.162151551089319	-0.0604177852712763	0.0413159805467665	0.143049746364809	0.244783512182852	0.238646756195648	0.308710890798427	0.378775025401207	0.448839160003987
"value"	0.000429585019171308	0.00391838085849298	-0.00058821325675922	-0.0631050387695357	1.0	-0.286002119828714	0.226840366290784	0.934346221805581	-0.00416924243520411	-0.00382241121967591	0.00259294684655743	-0.00184644046736284	0.00268770872382402	0.00102728747461828	-0.00178196776030956	-7.98952664875218e-05	0.219480342881377	0.0633433193874819	0.0503407640346705	-0.0661901237431247	-0.091208055644822	-0.152884648437902	-0.0821649426287596	-0.0682407716347868	-0.0450040710314486	0.00266779894718576	0.0476690731851529	0.170203430554323
"avg_temperature"	-0.000388408483331499	0.000771742974758161	-0.000214800019030768	0.469805916060245	-0.286002119828714	1.0	-0.658481080773544	-0.309447511959112	-0.000725793447764298	-0.000488976526300037	-7.42642531323949e-05	0.000236330353627353	0.000489612481223581	-3.04846404635027e-05	0.000868657788855458	-0.000456483170841854	-0.453302104104287	-0.4197274849156	-0.251537709844356	-0.0708195835461551	0.0916363105725905	0.36125777145111	0.43586627878446	0.395541204991271	0.167177748226652	0.101654334296474	-0.0997624596518854	-0.291460186920634
"avg_humidity"	-0.00036832764708373	-0.00217981408380471	0.00183889966163093	-0.0101794307863282	0.226840366290784	-0.658481080773544	1.0	0.247414540577416	0.00287317106640849	0.000539563796871106	-0.000411911971726602	0.00123123327393878	-0.00174317956967894	-0.00141710572205898	-0.00115078613818213	0.00214015121562913	0.368442701267311	0.392388381005602	-0.120244449684894	-0.190363603253431	-0.302626151421451	-0.357900077169892	-0.146593598441957	-0.186084432600167	0.00946546949018451	0.211806388946452	0.208523703849475	0.317615062275282
"value_zscore"	-6.06712073807718e-18	-3.74313875208588e-18	-6.57284327067527e-18	-0.0673472424493425	0.934346221805581	-0.309447511959112	0.247414540577416	1.0	4.12522668562028e-19	-2.16549570827437e-18	3.44914590748451e-19	7.29594933790264e-18	-3.08905986829715e-18	4.5174568540405e-18	2.04486013789287e-18	-6.02067785713798e-18	0.236941988113497	0.0678026461029818	0.0557949986970486	-0.073370313163134	-0.0974217129848312	-0.168049217626795	-0.0884954028425124	-0.0739300152307806	-0.0443552630664107	0.00406229109713327	0.0508597540286246	0.183143185651282
"region_0"	0.013651309137	-0.564373811092644	-0.00435561722873074	0.00589382858218876	-0.00416924243520411	-0.000725793447764298	0.00287317106640849	4.12522668562028e-19	1.0	-0.0436458180521819	-0.0654996460256671	-0.0403006934839292	-0.291936085870083	0.0167180597630204	-0.0274854061804049	0.00643135281586266	-0.0112232970908171	0.00136616720394519	0.00136616720394519	0.00136616720394519	0.00136616720394519	0.00136616720394519	0.00136616720394519	0.00136616720394519	0.000940880563079907	0.000940880563079907	0.000940880563079907	0.000940880563079907
"region_1"	0.0466265869557577	-0.539485038229266	0.00168175653142086	0.0079672351655185	-0.00382241121967591	-0.000488976526300037	0.000539563796871106	-2.16549570827437e-18	-0.0436458180521819	1.0	-0.0885419512745503	-0.0544781881322893	-0.394637105065615	0.0190485475998429	-0.0501706576893738	0.0197775757586913	-0.0151715724351475	0.00184677501855816	0.00184677501855816	0.00184677501855816	0.00184677501855816	0.00184677501855816	0.00184677501855816	0.00184677501855816	0.00127187559057567	0.00127187559057567	0.00127187559057567	0.00127187559057567
"region_2"	0.00328987359897663	-0.474356114920355	-0.00460956649743866	0.0119564967832839	0.00259294684655743	-7.42642531323949e-05	-0.000411911971726602	3.44914590748451e-19	-0.0654996460256671	-0.0885419512745503	1.0	-0.0817558748588118	-0.592235220783074	0.0167811428537608	-0.00528401899528803	0.00306688529055391	-0.0227681062814963	0.00277147079383375	0.00277147079383375	0.00277147079383375	0.00277147079383375	0.00277147079383375	0.00277147079383375	0.00277147079383375	0.00190871438981376	0.00190871438981376	0.00190871438981376	0.00190871438981376
"region_3"	-0.013700995176311	-0.0850796152695257	-0.0623601449658743	0.00735660635197768	-0.00184644046736284	0.000236330353627353	0.00123123327393878	7.29594933790264e-18	-0.0403006934839292	-0.0544781881322893	-0.0817558748588118	1.0	-0.364391131118677	0.0663413595304377	-0.00802323641415758	-0.0552576313558247	-0.0140087852093207	0.00170523356596748	0.00170523356596748	0.00170523356596748	0.00170523356596748	0.00170523356596748	0.00170523356596748	0.00170523356596748	0.0011743958668434	0.0011743958668434	0.0011743958668434	0.0011743958668434
"region_4"	-0.0265760464510187	0.907172644025799	0.0350513980015978	0.0532908661867017	0.00268770872382402	0.000489612481223581	-0.00174317956967894	-3.08905986829715e-18	-0.291936085870083	-0.394637105065615	-0.592235220783074	-0.364391131118677	1.0	0.00323694008182913	0.0580602516959156	0.0443629121886827	-0.101478896968228	0.012352621498718	0.012352621498718	0.012352621498718	0.012352621498718	0.012352621498718	0.012352621498718	0.012352621498718	0.00850726136424882	0.00850726136424882	0.00850726136424882	0.00850726136424882
"residenceType_1"	-0.102365768266218	-0.0310062198052858	-0.964066970378623	0.0410984240967133	0.00102728747461828	-3.04846404635027e-05	-0.00141710572205898	4.5174568540405e-18	0.0167180597630204	0.0190485475998429	0.0167811428537608	0.0663413595304377	0.00323694008182913	1.0	-0.3447442864841	-0.812772177641524	-0.0782614928767598	0.00952645947397227	0.00952645947397227	0.00952645947397227	0.00952645947397227	0.00952645947397227	0.00952645947397227	0.00952645947397227	0.0065608810752776	0.0065608810752776	0.0065608810752776	0.0065608810752776
"residenceType_2"	0.0467577567557462	0.0574944611713134	0.0987873344174206	0.00902473393159095	-0.00178196776030956	0.000868657788855458	-0.00115078613818213	2.04486013789287e-18	-0.0274854061804049	-0.0501706576893738	-0.00528401899528803	-0.00802323641415758	0.0580602516959156	-0.3447442864841	1.0	-0.178475277615356	-0.0171853097977628	0.00209189923828634	0.00209189923828634	0.00209189923828634	0.00209189923828634	0.00209189923828634	0.00209189923828634	0.00209189923828634	0.0014406928577567	0.0014406928577567	0.0014406928577567	0.0014406928577567
"residenceType_3"	0.0816737879786041	0.000324658415336287	0.960040414014256	0.021276792503283	-7.98952664875218e-05	-0.000456483170841854	0.00214015121562913	-6.02067785713798e-18	0.00643135281586266	0.0197775757586913	0.00306688529055391	-0.0552576313558247	0.0443629121886827	-0.812772177641524	-0.178475277615356	1.0	-0.0405162383116568	0.00493188013831537	0.00493188013831537	0.00493188013831537	0.00493188013831537	0.00493188013831537	0.00493188013831537	0.00493188013831537	0.00339659021837193	0.00339659021837193	0.00339659021837193	0.00339659021837193
"month_1"	0.00182472281671094	0.0	0.0	-0.525142347609341	0.219480342881377	-0.453302104104287	0.368442701267311	0.236941988113497	-0.0112232970908171	-0.0151715724351475	-0.0227681062814963	-0.0140087852093207	-0.101478896968228	-0.0782614928767598	-0.0171853097977628	-0.0405162383116568	1.0	-0.121726012676168	-0.121726012676168	-0.121726012676168	-0.121726012676168	-0.121726012676168	-0.121726012676168	-0.121726012676168	-0.0838328127168386	-0.0838328127168386	-0.0838328127168386	-0.0838328127168386
"month_2"	-0.000222116232717449	0.0	0.0	-0.365619082725405	0.0633433193874819	-0.4197274849156	0.392388381005602	0.0678026461029818	0.00136616720394519	0.00184677501855816	0.00277147079383375	0.00170523356596748	0.012352621498718	0.00952645947397227	0.00209189923828634	0.00493188013831537	-0.121726012676168	1.0	-0.108330625067707	-0.108330625067707	-0.108330625067707	-0.108330625067707	-0.108330625067707	-0.108330625067707	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543
"month_3"	-0.000222116232717449	0.0	0.0	-0.263885316907362	0.0503407640346705	-0.251537709844356	-0.120244449684894	0.0557949986970486	0.00136616720394519	0.00184677501855816	0.00277147079383375	0.00170523356596748	0.012352621498718	0.00952645947397227	0.00209189923828634	0.00493188013831537	-0.121726012676168	-0.108330625067707	1.0	-0.108330625067707	-0.108330625067707	-0.108330625067707	-0.108330625067707	-0.108330625067707	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543
"month_4"	-0.000222116232717449	0.0	0.0	-0.162151551089319	-0.0661901237431247	-0.0708195835461551	-0.190363603253431	-0.073370313163134	0.00136616720394519	0.00184677501855816	0.00277147079383375	0.00170523356596748	0.012352621498718	0.00952645947397227	0.00209189923828634	0.00493188013831537	-0.121726012676168	-0.108330625067707	-0.108330625067707	1.0	-0.108330625067707	-0.108330625067707	-0.108330625067707	-0.108330625067707	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543
"month_5"	-0.000222116232717449	0.0	0.0	-0.0604177852712763	-0.091208055644822	0.0916363105725905	-0.302626151421451	-0.0974217129848312	0.00136616720394519	0.00184677501855816	0.00277147079383375	0.00170523356596748	0.012352621498718	0.00952645947397227	0.00209189923828634	0.00493188013831537	-0.121726012676168	-0.108330625067707	-0.108330625067707	-0.108330625067707	1.0	-0.108330625067707	-0.108330625067707	-0.108330625067707	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543
"month_6"	-0.000222116232717449	0.0	0.0	0.0413159805467665	-0.152884648437902	0.36125777145111	-0.357900077169892	-0.168049217626795	0.00136616720394519	0.00184677501855816	0.00277147079383375	0.00170523356596748	0.012352621498718	0.00952645947397227	0.00209189923828634	0.00493188013831537	-0.121726012676168	-0.108330625067707	-0.108330625067707	-0.108330625067707	-0.108330625067707	1.0	-0.108330625067707	-0.108330625067707	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543
"month_7"	-0.000222116232717449	0.0	0.0	0.143049746364809	-0.0821649426287596	0.43586627878446	-0.146593598441957	-0.0884954028425124	0.00136616720394519	0.00184677501855816	0.00277147079383375	0.00170523356596748	0.012352621498718	0.00952645947397227	0.00209189923828634	0.00493188013831537	-0.121726012676168	-0.108330625067707	-0.108330625067707	-0.108330625067707	-0.108330625067707	-0.108330625067707	1.0	-0.108330625067707	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543
"month_8"	-0.000222116232717449	0.0	0.0	0.244783512182852	-0.0682407716347868	0.395541204991271	-0.186084432600167	-0.0739300152307806	0.00136616720394519	0.00184677501855816	0.00277147079383375	0.00170523356596748	0.012352621498718	0.00952645947397227	0.00209189923828634	0.00493188013831537	-0.121726012676168	-0.108330625067707	-0.108330625067707	-0.108330625067707	-0.108330625067707	-0.108330625067707	-0.108330625067707	1.0	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543
"month_9"	-0.000152971646153471	0.0	0.0	0.238646756195648	-0.0450040710314486	0.167177748226652	0.00946546949018451	-0.0443552630664107	0.000940880563079907	0.00127187559057567	0.00190871438981376	0.0011743958668434	0.00850726136424882	0.0065608810752776	0.0014406928577567	0.00339659021837193	-0.0838328127168386	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	1.0	-0.0513821806597472	-0.0513821806597472	-0.0513821806597472
"month_10"	-0.000152971646153471	0.0	0.0	0.308710890798427	0.00266779894718576	0.101654334296474	0.211806388946452	0.00406229109713327	0.000940880563079907	0.00127187559057567	0.00190871438981376	0.0011743958668434	0.00850726136424882	0.0065608810752776	0.0014406928577567	0.00339659021837193	-0.0838328127168386	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0513821806597472	1.0	-0.0513821806597472	-0.0513821806597472
"month_11"	-0.000152971646153471	0.0	0.0	0.378775025401207	0.0476690731851529	-0.0997624596518854	0.208523703849475	0.0508597540286246	0.000940880563079907	0.00127187559057567	0.00190871438981376	0.0011743958668434	0.00850726136424882	0.0065608810752776	0.0014406928577567	0.00339659021837193	-0.0838328127168386	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0513821806597472	-0.0513821806597472	1.0	-0.0513821806597472
"month_12"	-0.000152971646153471	0.0	0.0	0.448839160003987	0.170203430554323	-0.291460186920634	0.317615062275282	0.183143185651282	0.000940880563079907	0.00127187559057567	0.00190871438981376	0.0011743958668434	0.00850726136424882	0.0065608810752776	0.0014406928577567	0.00339659021837193	-0.0838328127168386	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0746073974094543	-0.0513821806597472	-0.0513821806597472	-0.0513821806597472	1.0

Rows: 1-28 | Columns: 29

There's a clear correlation between the month and energy consumption, but this isn't causal. Instead, we can think of the weather as having the direct influence on energy consumption. To accomodate for this view, we'll use the temperature as a predictor (rather than the month).

In [24]:

sm_consumption_month.corr(focus = "value")

Out[24]:

	"value"
"value"	1.0
"value_zscore"	0.934346221805581
"avg_temperature"	-0.286002119828714
"avg_humidity"	0.226840366290784
"month_1"	0.219480342881377
"month_12"	0.170203430554323
"month_6"	-0.152884648437902
"month_5"	-0.091208055644822
"month_7"	-0.0821649426287596
"month_8"	-0.0682407716347868
"month_4"	-0.0661901237431247
"month_2"	0.0633433193874819
"month"	-0.0631050387695357
"month_3"	0.0503407640346705
"month_11"	0.0476690731851529
"month_9"	-0.0450040710314486
"region_0"	-0.00416924243520411
"region"	0.00391838085849298
"region_1"	-0.00382241121967591
"region_4"	0.00268770872382402
"month_10"	0.00266779894718576
"region_2"	0.00259294684655743
"region_3"	-0.00184644046736284
"residenceType_2"	-0.00178196776030956
"residenceType_1"	0.00102728747461828
"residenceType"	-0.00058821325675922
"meterID"	0.000429585019171308
"residenceType_3"	-7.98952664875218e-05

Rows: 1-28 | Columns: 2

Global Behavior

Let's look at this globally.

In [25]:

sm_consumption_final = sm_consumption_month.groupby(
                            ["date_month"], 
                            [st.avg(sm_consumption_month["avg_temperature"])._as("avg_temperature"),
                             st.avg(sm_consumption_month["avg_humidity"])._as("avg_humidity"),
                             st.avg(sm_consumption_month["value"])._as("avg_value")])
sm_consumption_final.plot(ts = "date_month", columns = ["avg_value"])

Out[25]:

<AxesSubplot:xlabel='"date_month"', ylabel='"avg_value"'>

We expect to see a fall in energy consumption during summer and then an increase during the winter. A simple prediction could use the average value a year before.

In [26]:

sm_consumption_final["prediction"] = st.case_when(sm_consumption_final["date_month"] < '2015-01-01', 
                                                  sm_consumption_final["avg_value"],
                                                  st.lag(sm_consumption_final["avg_value"], 12)._over(
                                                                                order_by = ["date_month"]))
sm_consumption_final.plot(ts = "date_month", columns = ["prediction", "avg_value"])
sm_consumption_final.score("avg_value", "prediction", "r2")

Out[26]:

0.987990336935642

As expected, our model's score is excellent.

Let's use machine learning to understand the influence of the weather and the humidity on energy consumption.

Machine Learning¶

Let's create our model.

In [27]:

from verticapy.learn.linear_model import LinearRegression

predictors = ["avg_temperature",
              "avg_humidity"]
model = LinearRegression("sm_lr", solver = "BFGS")
model.fit(sm_consumption_final, 
          predictors,
          "avg_value")
model.report("details")

Out[27]:

	value
Dep. Variable	"avg_value"
Model	LinearRegression
No. Observations	20.0
No. Predictors	2
R-squared	0.79911369421353
Adj. R-squared	0.7754800111798276
F-statistic	33.81249096905816
Prob (F-statistic)	3.837253952408389e-07
Kurtosis	-0.0457129692472424
Skewness	0.893296166786593
Jarque-Bera (JB)	3.65140760780652

Rows: 1-11 | Columns: 2

The model seems to be good with an adjusted R2 of 77.5%, and the F-Statistic indicates that at least one of the two predictors is useful. Let's look at the residual plot.

In [28]:

sm_consumption_final = model.predict(sm_consumption_final, 
                                     name = "value_prediction")
sm_consumption_final["residual"] = sm_consumption_final["avg_value"] - sm_consumption_final["value_prediction"]
sm_consumption_final.scatter(["avg_value", "residual"])

Out[28]:

<AxesSubplot:xlabel='"avg_value"', ylabel='"residual"'>

Looking at the residual plot, we can see that the error variance varies by quite a bit. A possible suspect might be heteroscedasticity. Let's verify our hypothesis using a Breusch-Pagan test.

In [29]:

from verticapy.stats import het_breuschpagan

het_breuschpagan(sm_consumption_final, "residual", predictors)

Out[29]:

	value
Lagrange Multiplier Statistic	6.066154825831241
lm_p_value	0.04816717950866987
F Value	3.700508752254135
f_p_value	0.04632851414387972

Rows: 1-4 | Columns: 2

The p-value is 4.81% and sits around the 5% threshold, so we can't really draw any conclusions.

Let's look at the entire regression report.

In [30]:

model.report()

Out[30]:

	value
explained_variance	0.799113694213527
max_error	64.4503700445748
median_absolute_error	13.1054316247273
mean_absolute_error	20.9034976459432
mean_squared_error	734.33259460088
root_mean_squared_error	27.09857181847191
r2	0.79911369421353
r2_adj	0.7754800111798276
aic	139.47924104286193
bic	140.9664378635239

Rows: 1-10 | Columns: 2

Our model is very good; its median absolute error is around 13kWh.

With this model, we can make predictions about the energy consumption of households per region. If the usage exceeds what the model predicts, we can raise an alert and respond, for example, by regulating the electricity distributed to the region.

Conclusion¶

We've solved our problem in a Pandas-like way, all without ever loading data into memory!

VerticaPy

Python API for Vertica Data Science at Scale

About the Author

Badr Ouali
Head of Data Science

Badr Ouali works as a Lead Data Scientist for Vertica worldwide. He can embrace data projects end to end through a clear understanding of the “big picture” as well as attention to details, resulting in achieving great business outcomes – a distinctive differentiator in his role. Badr enjoys sharing knowledge and insights related to data analytics with colleagues & peers and has a sweet spot for Python. He loves helping customers finding the best value from their data and empower them to solve their use-cases.