Feedback

×

• Feedback
• Was this page helpful?*
  • Yes
  • No
• Do you have any additional feedback?
  • Yes
  • No
• Additional Feedback

The Magic Methods of the vDataFrame

VerticaPy 0.3.2 introduces the Magic methods, which offer some additional flexibility for mathematical operations in the vDataFrame. These methods let you handle many operations in a pandas-like or Pythonic style.

from verticapy.datasets import load_titanic

titanic = load_titanic()
titanic.head(100)

	123 pclass Int 100%	...	123 body Int 9%	Abc Varchar(100) 57%
1	1	...	22
2	1	...	[null]
3	1	...	[null]
4	1	...	[null]
5	1	...	[null]
6	1	...	[null]
7	1	...	[null]
8	1	...	62
9	1	...	[null]
10	1	...	[null]
11	1	...	[null]
12	1	...	[null]
13	1	...	110
14	1	...	[null]
15	1	...	[null]
16	1	...	38
17	1	...	[null]
18	1	...	126
19	1	...	292
20	1	...	175
21	1	...	[null]
22	1	...	122
23	1	...	166
24	1	...	[null]
25	1	...	207
26	1	...	[null]
27	1	...	232
28	1	...	[null]
29	1	...	[null]
30	1	...	[null]
31	1	...	[null]
32	1	...	[null]
33	1	...	46
34	1	...	169
35	1	...	[null]
36	1	...	[null]
37	1	...	[null]
38	1	...	[null]
39	1	...	[null]
40	1	...	[null]
41	1	...	[null]
42	1	...	[null]
43	1	...	[null]
44	1	...	[null]
45	1	...	[null]
46	1	...	[null]
47	1	...	[null]
48	1	...	[null]
49	1	...	[null]
50	1	...	[null]
51	1	...	[null]
52	1	...	[null]
53	1	...	[null]
54	1	...	[null]
55	1	...	[null]
56	1	...	[null]
57	1	...	[null]
58	1	...	[null]
59	1	...	[null]
60	1	...	[null]
61	1	...	[null]
62	1	...	[null]
63	1	...	[null]
64	1	...	[null]
65	1	...	[null]
66	1	...	[null]
67	1	...	[null]
68	1	...	[null]
69	1	...	[null]
70	1	...	[null]
71	1	...	[null]
72	1	...	[null]
73	1	...	[null]
74	2	...	[null]
75	2	...	[null]
76	2	...	[null]
77	2	...	[null]
78	2	...	[null]
79	2	...	[null]
80	2	...	[null]
81	2	...	[null]
82	2	...	[null]
83	2	...	236
84	2	...	[null]
85	2	...	[null]
86	2	...	[null]
87	2	...	155
88	2	...	[null]
89	2	...	75
90	2	...	35
91	2	...	[null]
92	2	...	[null]
93	2	...	[null]
94	2	...	[null]
95	2	...	[null]
96	2	...	[null]
97	2	...	[null]
98	2	...	165
99	2	...	[null]
100	2	...	[null]

Feature Engineering, pandas-style

You can create new features with in a pandas style.

titanic["family_size"] = titanic["parch"] + titanic["sibsp"] + 1
titanic[["sibsp", "parch", "family_size"]]

	123 sibsp Integer 100%	...	123 parch Integer 100%	123 family_size Integer 100%
1	0	...	0	1
2	0	...	0	1
3	0	...	0	1
4	0	...	0	1
5	1	...	0	2
6	1	...	1	3
7	1	...	0	2
8	1	...	0	2
9	0	...	0	1
10	1	...	0	2
11	0	...	0	1
12	1	...	0	2
13	0	...	0	1
14	0	...	0	1
15	0	...	0	1
16	1	...	0	2
17	0	...	0	1
18	0	...	0	1
19	0	...	0	1
20	0	...	0	1

You can also create features from various mathematical functions.

import verticapy.sql.functions as fun

titanic["ln_fare"] = fun.ln(titanic["fare"])
titanic[["fare", "ln_fare"]]

	123 fare Numeric(12) 99%	123 ln_fare Float(22) 99%
1	49.5042	3.90205751446006
2	35.5	3.56953269648137
3	31.0	3.43398720448515
4	47.1	3.85227300102237
5	136.7792	4.91836794684454
6	83.1583	4.42074602026042
7	52.0	3.95124371858143
8	106.425	4.66744051171422
9	31.6792	3.45566031410212
10	53.1	3.97217692824789
11	79.2	4.37197629882038
12	83.475	4.42454718582972
13	0.0	-inf
14	42.5	3.74950407593037
15	51.8625	3.94859598565899
16	52.0	3.95124371858143
17	30.6958	3.42412583741652
18	30.0	3.40119738166216
19	75.2417	4.32070559854228
20	51.8625	3.94859598565899

titanic["x"] = 1 - fun.exp(-titanic["fare"])
titanic[["fare", "x"]]

	123 fare Numeric(12) 99%	123 x Float(22) 99%
1	49.5042	1.0
2	35.5	1.0
3	31.0	0.999999999999966
4	47.1	1.0
5	136.7792	1.0
6	83.1583	1.0
7	52.0	1.0
8	106.425	1.0
9	31.6792	0.999999999999983
10	53.1	1.0
11	79.2	1.0
12	83.475	1.0
13	0.0	0.0
14	42.5	1.0
15	51.8625	1.0
16	52.0	1.0
17	30.6958	0.999999999999953
18	30.0	0.999999999999906
19	75.2417	1.0
20	51.8625	1.0

Conditional Operators

You can now filter your data with conditional operators like and (&), or (|), equals (==), not equals (!=), and more!

Equal Operator (==)

# Identifies the passengers who came alone

single_family = titanic[titanic["family_size"] == 1]
single_family.head(100)

	123 pclass Integer 100%	...	123 ln_fare Float(22) 99%	123 x Float(22) 99%
1	1	...	3.90205751446006	1.0
2	1	...	3.56953269648137	1.0
3	1	...	3.43398720448515	0.999999999999966
4	1	...	3.85227300102237	1.0
5	1	...	3.45566031410212	0.999999999999983
6	1	...	4.37197629882038	1.0
7	1	...	-inf	0.0
8	1	...	3.74950407593037	1.0
9	1	...	3.94859598565899	1.0
10	1	...	3.42412583741652	0.999999999999953
11	1	...	3.40119738166216	0.999999999999906
12	1	...	4.32070559854228	1.0
13	1	...	3.94859598565899	1.0
14	1	...	3.74714836223791	1.0
15	1	...	3.3499040872746	0.999999999999581
16	1	...	3.95124371858143	1.0
17	1	...	-inf	0.0
18	1	...	4.90995492057888	1.0
19	1	...	3.92189016450581	1.0
20	1	...	3.42412583741652	0.999999999999953
21	1	...	3.4757109858218	0.999999999999991
22	1	...	4.33456415124619	1.0
23	1	...	3.27902974768795	0.999999999997052
24	1	...	3.32218303393412	0.999999999999086
25	1	...	4.90156419904189	1.0
26	1	...	3.31221315078453	0.999999999998797
27	1	...	4.70847829690348	1.0
28	1	...	5.35422499848633	1.0
29	1	...	4.34028233996554	1.0
30	1	...	4.42074602026042	1.0
31	1	...	4.69043002993891	1.0
32	1	...	3.41772668361337	0.999999999999943
33	1	...	4.46014441393783	1.0
34	1	...	4.37197629882038	1.0
35	1	...	6.23896738717366	1.0
36	2	...	2.35137525716348	0.99997246355065
37	2	...	3.25809653802148	0.999999999994891
38	2	...	4.29728540621879	1.0
39	2	...	4.29728540621879	1.0
40	2	...	3.25809653802148	0.999999999994891
41	2	...	2.56494935746154	0.999997739670593
42	2	...	2.56494935746154	0.999997739670593
43	2	...	2.56494935746154	0.999997739670593
44	2	...	2.56494935746154	0.999997739670593
45	2	...	2.56494935746154	0.999997739670593
46	2	...	2.56494935746154	0.999997739670593
47	2	...	4.29728540621879	1.0
48	2	...	2.51365606307399	0.999995670246734
49	2	...	2.35137525716348	0.99997246355065
50	2	...	2.5552874465498	0.999997438711229
51	2	...	2.74593669124235	0.999999828588716
52	2	...	2.77258872223978	0.999999887464825
53	2	...	2.56494935746154	0.999997739670593
54	2	...	2.35137525716348	0.99997246355065
55	2	...	2.56494935746154	0.999997739670593
56	2	...	2.35137525716348	0.99997246355065
57	2	...	2.71109888248023	0.999999707792012
58	2	...	2.56494935746154	0.999997739670593
59	2	...	2.56494935746154	0.999997739670593
60	2	...	2.484906649788	0.999993855787647
61	2	...	2.35137525716348	0.99997246355065
62	2	...	2.56494935746154	0.999997739670593
63	2	...	2.51365606307399	0.999995670246734
64	2	...	3.04452243772342	0.999999999241744
65	2	...	3.04452243772342	0.999999999241744
66	2	...	2.75684036527164	0.999999855501975
67	3	...	2.08567209143047	0.999680898077519
68	3	...	3.1146258034944	0.999999999834988
69	3	...	2.0509134580653	0.999579892530644
70	3	...	2.08567209143047	0.999680898077519
71	3	...	2.0509134580653	0.999579892530644
72	3	...	1.97812837997935	0.999274899288251
73	3	...	2.04769284336526	0.999569257459424
74	3	...	2.06104842056294	0.999611881557209
75	3	...	2.04769284336526	0.999569257459424
76	3	...	2.04553567965004	0.999562003658341
77	3	...	2.08567209143047	0.999680898077519
78	3	...	1.95302761682418	0.999132591042693
79	3	...	2.04553567965004	0.999562003658341
80	3	...	2.03198299052013	0.99951395045752
81	3	...	2.08567209143047	0.999680898077519
82	3	...	2.08567209143047	0.999680898077519
83	3	...	1.97812837997935	0.999274899288251
84	3	...	2.04769284336526	0.999569257459424
85	3	...	2.04769284336526	0.999569257459424
86	3	...	2.06633097252974	0.99962769606213
87	3	...	2.0509134580653	0.999579892530644
88	3	...	2.04769284336526	0.999569257459424
89	3	...	2.06422637587336	0.999621464235705
90	3	...	2.04769284336526	0.999569257459424
91	3	...	2.04769284336526	0.999569257459424
92	3	...	2.06051353179432	0.999610248031747
93	3	...	1.95302761682418	0.999132591042693
94	3	...	2.12375742545435	0.999766540046286
95	3	...	2.0509134580653	0.999579892530644
96	3	...	2.18041745901984	0.999856618263724
97	3	...	2.06104842056294	0.999611881557209
98	3	...	2.02154756326093	0.999473889872884
99	3	...	2.93319175507909	0.999999993070651
100	3	...	2.07002231976334	0.99963841015017

And Operator (&)

# Identifies the passengers who came alone and
# who are between 15 and 24 years old...
# ...with comparison operators
single_family[(titanic["age"] >= 15) & (titanic["age"] <= 24)]

	123 pclass Integer 100%	...	123 ln_fare Float(22) 100%	123 x Float(22) 100%
1	1	...	3.85227300102237	1.0
2	1	...	4.37197629882038	1.0
3	1	...	4.90995492057888	1.0
4	1	...	4.42074602026042	1.0
5	2	...	2.35137525716348	0.99997246355065
6	2	...	4.29728540621879	1.0
7	2	...	4.29728540621879	1.0
8	2	...	2.56494935746154	0.999997739670593
9	2	...	2.56494935746154	0.999997739670593
10	2	...	4.29728540621879	1.0
11	2	...	2.35137525716348	0.99997246355065
12	2	...	2.56494935746154	0.999997739670593
13	2	...	2.484906649788	0.999993855787647
14	2	...	2.35137525716348	0.99997246355065
15	3	...	2.08567209143047	0.999680898077519
16	3	...	2.04769284336526	0.999569257459424
17	3	...	2.06104842056294	0.999611881557209
18	3	...	2.04553567965004	0.999562003658341
19	3	...	1.95302761682418	0.999132591042693
20	3	...	2.08567209143047	0.999680898077519

Between Operator (_between)

# ...with the 'between' function
single_family[titanic["age"]._between(15, 24)]

	123 pclass Integer 100%	...	123 ln_fare Float(22) 100%	123 x Float(22) 100%
1	1	...	3.85227300102237	1.0
2	1	...	4.37197629882038	1.0
3	1	...	4.90995492057888	1.0
4	1	...	4.42074602026042	1.0
5	2	...	2.35137525716348	0.99997246355065
6	2	...	4.29728540621879	1.0
7	2	...	4.29728540621879	1.0
8	2	...	2.56494935746154	0.999997739670593
9	2	...	2.56494935746154	0.999997739670593
10	2	...	4.29728540621879	1.0
11	2	...	2.35137525716348	0.99997246355065
12	2	...	2.56494935746154	0.999997739670593
13	2	...	2.484906649788	0.999993855787647
14	2	...	2.35137525716348	0.99997246355065
15	3	...	2.08567209143047	0.999680898077519
16	3	...	2.04769284336526	0.999569257459424
17	3	...	2.06104842056294	0.999611881557209
18	3	...	2.04553567965004	0.999562003658341
19	3	...	1.95302761682418	0.999132591042693
20	3	...	2.08567209143047	0.999680898077519

In Operator (_in)

# Identifies the passengers who came alone in 1st and 3rd class...

# ...with the 'in' method
single_family[titanic["pclass"]._in(1, 3)]

	123 pclass Integer 100%	...	123 ln_fare Float(22) 99%	123 x Float(22) 99%
1	1	...	3.90205751446006	1.0
2	1	...	3.56953269648137	1.0
3	1	...	3.43398720448515	0.999999999999966
4	1	...	3.85227300102237	1.0
5	1	...	3.45566031410212	0.999999999999983
6	1	...	4.37197629882038	1.0
7	1	...	-inf	0.0
8	1	...	3.74950407593037	1.0
9	1	...	3.94859598565899	1.0
10	1	...	3.42412583741652	0.999999999999953
11	1	...	3.40119738166216	0.999999999999906
12	1	...	4.32070559854228	1.0
13	1	...	3.94859598565899	1.0
14	1	...	3.74714836223791	1.0
15	1	...	3.3499040872746	0.999999999999581
16	1	...	3.95124371858143	1.0
17	1	...	-inf	0.0
18	1	...	4.90995492057888	1.0
19	1	...	3.92189016450581	1.0
20	1	...	3.42412583741652	0.999999999999953

Not In Operator (_not_in)

# ...with the 'not_in' method
single_family[titanic["pclass"]._not_in(2)]

	123 pclass Integer 100%	...	123 ln_fare Float(22) 99%	123 x Float(22) 99%
1	1	...	3.90205751446006	1.0
2	1	...	3.56953269648137	1.0
3	1	...	3.43398720448515	0.999999999999966
4	1	...	3.85227300102237	1.0
5	1	...	3.45566031410212	0.999999999999983
6	1	...	4.37197629882038	1.0
7	1	...	-inf	0.0
8	1	...	3.74950407593037	1.0
9	1	...	3.94859598565899	1.0
10	1	...	3.42412583741652	0.999999999999953
11	1	...	3.40119738166216	0.999999999999906
12	1	...	4.32070559854228	1.0
13	1	...	3.94859598565899	1.0
14	1	...	3.74714836223791	1.0
15	1	...	3.3499040872746	0.999999999999581
16	1	...	3.95124371858143	1.0
17	1	...	-inf	0.0
18	1	...	4.90995492057888	1.0
19	1	...	3.92189016450581	1.0
20	1	...	3.42412583741652	0.999999999999953

Or Operator (|)

# Or operator
single_family[(titanic["pclass"] == 1) | (titanic["pclass"] == 3)]

	123 pclass Integer 100%	...	123 ln_fare Float(22) 99%	123 x Float(22) 99%
1	1	...	3.90205751446006	1.0
2	1	...	3.56953269648137	1.0
3	1	...	3.43398720448515	0.999999999999966
4	1	...	3.85227300102237	1.0
5	1	...	3.45566031410212	0.999999999999983
6	1	...	4.37197629882038	1.0
7	1	...	-inf	0.0
8	1	...	3.74950407593037	1.0
9	1	...	3.94859598565899	1.0
10	1	...	3.42412583741652	0.999999999999953
11	1	...	3.40119738166216	0.999999999999906
12	1	...	4.32070559854228	1.0
13	1	...	3.94859598565899	1.0
14	1	...	3.74714836223791	1.0
15	1	...	3.3499040872746	0.999999999999581
16	1	...	3.95124371858143	1.0
17	1	...	-inf	0.0
18	1	...	4.90995492057888	1.0
19	1	...	3.92189016450581	1.0
20	1	...	3.42412583741652	0.999999999999953

Not Equal Operator (!=)

# ...with the not equal operator
single_family[titanic["pclass"] != 2]

	123 pclass Integer 100%	...	123 ln_fare Float(22) 99%	123 x Float(22) 99%
1	1	...	-inf	0.0
2	1	...	4.32070559854228	1.0
3	1	...	3.41772668361337	0.999999999999943
4	1	...	3.92189016450581	1.0
5	1	...	3.67882911826043	1.0
6	1	...	1.6094379124341	0.993262053000915
7	1	...	3.95124371858143	1.0
8	1	...	3.24210395098741	0.999999999992282
9	1	...	3.65065824129374	1.0
10	1	...	3.91202300542815	1.0
11	1	...	3.35733256801522	0.999999999999661
12	1	...	3.39114704580865	0.999999999999874
13	1	...	3.8177123259569	1.0
14	1	...	3.25809653802148	0.999999999994891
15	1	...	3.27902974768795	0.999999999997052
16	1	...	3.91202300542815	1.0
17	1	...	3.51154543883102	0.999999999999997
18	1	...	3.27902974768795	0.999999999997052
19	1	...	5.35345638166308	1.0
20	1	...	3.27902974768795	0.999999999997052

‘Pythonic’ Invokation of Vertica Functions

You can easily apply Vertica functions to your vDataFrame. Here, we use Vertica’s COALESCE function to impute the age of the passengers in our dataset.

titanic["age"].count()

res = titanic["age"].count()

titanic["age"] = fun.coalesce(titanic["age"], titanic["age"].avg());

titanic["age"].count()
Out[3]: 1234.0

Slicing the vDataFrame

You can now slice the vDataFrame with indexing operators.

titanic[0:30]

	123 pclass Integer 100%	...	123 ln_fare Float(22) 100%	123 x Float(22) 100%
1	1	...	3.90205751446006	1.0
2	1	...	3.56953269648137	1.0
3	1	...	3.43398720448515	0.999999999999966
4	1	...	3.85227300102237	1.0
5	1	...	4.91836794684454	1.0
6	1	...	4.42074602026042	1.0
7	1	...	3.95124371858143	1.0
8	1	...	4.66744051171422	1.0
9	1	...	3.45566031410212	0.999999999999983
10	1	...	3.97217692824789	1.0
11	1	...	4.37197629882038	1.0
12	1	...	4.42454718582972	1.0
13	1	...	-inf	0.0
14	1	...	3.74950407593037	1.0
15	1	...	3.94859598565899	1.0
16	1	...	3.95124371858143	1.0
17	1	...	3.42412583741652	0.999999999999953
18	1	...	3.40119738166216	0.999999999999906
19	1	...	4.32070559854228	1.0
20	1	...	3.94859598565899	1.0

To access a single row, we just have to specify the index.

titanic[0:1]

	123 pclass Integer 100%	...	123 ln_fare Float(22) 100%	123 x Float(22) 100%
1	1	...	3.90205751446006	1.0