VerticaPy

Python API for Vertica Data Science at Scale

The Virtual DataFrame

The Virtual DataFrame is the main object and star of the library and acts as the perfect transition between small and Big Data. The principle is quite simple: As Vertica is a powerful columnar massive parallel processing (MPP) database with many built-in functions, we want it to do as much of the computation work as possible.

Indeed, columnar orientation allows for high compression, and its structure inherently avoids unncessary parsing when retrieving data. MPP allows to parallelize our computations accross the different nodes.

The best way to take advantage of your data is by simply keeping it in your Vertica database, rather than within the limitations of working memory. VerticaPy pushes all computation to your Vertica database before aggregating the final result, so you can get the best of both worlds: Vertica's power and Python's flexibility.



With Python, it's easy to add abstractions, and the vDataFrame acts as the primary abstraction layer. Simple but powerful, it'll help any user through the data science life cycle.

Creating the vDataFrame

There are two main ways to create a vDataFrame.

The first is to create one directly using an existing relation.

In [1]:
from verticapy.datasets import load_titanic
load_titanic() # Loading the titanic dataset in Vertica

import verticapy as vp
vp.vDataFrame("public.titanic")
Out[1]:
123
pclass
Int
123
survived
Int
Abc
Varchar(164)
Abc
sex
Varchar(20)
123
age
Numeric(6,3)
123
sibsp
Int
123
parch
Int
Abc
ticket
Varchar(36)
123
fare
Numeric(10,5)
Abc
cabin
Varchar(30)
Abc
embarked
Varchar(20)
Abc
boat
Varchar(100)
123
body
Int
Abc
home.dest
Varchar(100)
110female2.012113781151.55C22 C26S[null][null]Montreal, PQ / Chesterville, ON
210male30.012113781151.55C22 C26S[null]135Montreal, PQ / Chesterville, ON
310female25.012113781151.55C22 C26S[null][null]Montreal, PQ / Chesterville, ON
410male39.0001120500.0A36S[null][null]Belfast, NI
510male71.000PC 1760949.5042[null]C[null]22Montevideo, Uruguay
610male47.010PC 17757227.525C62 C64C[null]124New York, NY
710male[null]00PC 1731825.925[null]S[null][null]New York, NY
810male24.001PC 17558247.5208B58 B60C[null][null]Montreal, PQ
910male36.0001305075.2417C6CA[null]Winnipeg, MN
1010male25.0001390526.0[null]C[null]148San Francisco, CA
1110male45.00011378435.5TS[null][null]Trenton, NJ
1210male42.00011048926.55D22S[null][null]London / Winnipeg, MB
1310male41.00011305430.5A21S[null][null]Pomeroy, WA
1410male48.000PC 1759150.4958B10C[null]208Omaha, NE
1510male[null]0011237939.6[null]C[null][null]Philadelphia, PA
1610male45.00011305026.55B38S[null][null]Washington, DC
1710male[null]0011379831.0[null]S[null][null][null]
1810male33.0006955.0B51 B53 B55S[null][null]New York, NY
1910male28.00011305947.1[null]S[null][null]Montevideo, Uruguay
2010male17.00011305947.1[null]S[null][null]Montevideo, Uruguay
2110male49.0001992426.0[null]S[null][null]Ascot, Berkshire / Rochester, NY
2210male36.0101987778.85C46S[null]172Little Onn Hall, Staffs
2310male46.010W.E.P. 573461.175E31S[null][null]Amenia, ND
2410male[null]001120510.0[null]S[null][null]Liverpool, England / Belfast
2510male27.01013508136.7792C89C[null][null]Los Angeles, CA
2610male[null]0011046552.0A14S[null][null]Stoughton, MA
2710male47.000572725.5875E58S[null][null]Victoria, BC
2810male37.011PC 1775683.1583E52C[null][null]Lakewood, NJ
2910male[null]0011379126.55[null]S[null][null]Roachdale, IN
3010male70.011WE/P 573571.0B22S[null]269Milwaukee, WI
3110male39.010PC 1759971.2833C85C[null][null]New York, NY
3210male31.010F.C. 1275052.0B71S[null][null]Montreal, PQ
3310male50.010PC 17761106.425C86C[null]62Deephaven, MN / Cedar Rapids, IA
3410male39.000PC 1758029.7A18C[null]133Philadelphia, PA
3510female36.000PC 1753131.6792A29C[null][null]New York, NY
3610male[null]00PC 17483221.7792C95S[null][null][null]
3710male30.00011305127.75C111C[null][null]New York, NY
3810male19.03219950263.0C23 C25 C27S[null][null]Winnipeg, MB
3910male64.01419950263.0C23 C25 C27S[null][null]Winnipeg, MB
4010male[null]0011377826.55D34S[null][null]Westcliff-on-Sea, Essex
4110male[null]001120580.0B102S[null][null][null]
4210male37.01011380353.1C123S[null][null]Scituate, MA
4310male47.00011132038.5E63S[null]275St Anne's-on-Sea, Lancashire
4410male24.000PC 1759379.2B86C[null][null][null]
4510male71.000PC 1775434.6542A5C[null][null]New York, NY
4610male38.001PC 17582153.4625C91S[null]147Winnipeg, MB
4710male46.000PC 1759379.2B82 B84C[null][null]New York, NY
4810male[null]0011379642.4[null]S[null][null][null]
4910male45.0103697383.475C83S[null][null]New York, NY
5010male40.0001120590.0B94S[null]110[null]
5110male55.0111274993.5B69S[null]307Montreal, PQ
5210male42.00011303842.5B11S[null][null]London / Middlesex
5310male[null]001746351.8625E46S[null][null]Brighton, MA
5410male55.00068050.0C39S[null][null]London / Birmingham
5510male42.01011378952.0[null]S[null]38New York, NY
5610male[null]00PC 1760030.6958[null]C14[null]New York, NY
5710female50.000PC 1759528.7125C49C[null][null]Paris, France New York, NY
5810male46.00069426.0[null]S[null]80Bennington, VT
5910male50.00011304426.0E60S[null][null]London
6010male32.500113503211.5C132C[null]45[null]
6110male58.0001177129.7B37C[null]258Buffalo, NY
6210male41.0101746451.8625D21S[null][null]Southington / Noank, CT
6310male[null]0011302826.55C124S[null][null]Portland, OR
6410male[null]00PC 1761227.7208[null]C[null][null]Chicago, IL
6510male29.00011350130.0D6S[null]126Springfield, MA
6610male30.00011380145.5[null]S[null][null]London / New York, NY
6710male30.00011046926.0C106S[null][null]Brockton, MA
6810male19.01011377353.1D30S[null][null]New York, NY
6910male46.0001305075.2417C6C[null]292Vancouver, BC
7010male54.0001746351.8625E46S[null]175Dorchester, MA
7110male28.010PC 1760482.1708[null]C[null][null]New York, NY
7210male65.0001350926.55E38S[null]249East Bridgewater, MA
7310male44.0201992890.0C78Q[null]230Fond du Lac, WI
7410male55.00011378730.5C30S[null][null]Montreal, PQ
7510male47.00011379642.4[null]S[null][null]Washington, DC
7610male37.001PC 1759629.7C118C[null][null]Brooklyn, NY
7710male58.00235273113.275D48C[null]122Lexington, MA
7810male64.00069326.0[null]S[null]263Isle of Wight, England
7910male65.00111350961.9792B30C[null]234Providence, RI
8010male28.500PC 1756227.7208D43C[null]189?Havana, Cuba
8110male[null]001120520.0[null]S[null][null]Belfast
8210male45.50011304328.5C124S[null]166Surbiton Hill, Surrey
8310male23.0001274993.5B24S[null][null]Montreal, PQ
8410male29.01011377666.6C2S[null][null]Isleworth, England
8510male18.010PC 17758108.9C65C[null][null]Madrid, Spain
8610male47.00011046552.0C110S[null]207Worcester, MA
8710male38.000199720.0[null]S[null][null]Rotterdam, Netherlands
8810male22.000PC 17760135.6333[null]C[null]232[null]
8910male[null]00PC 17757227.525[null]C[null][null][null]
9010male31.000PC 1759050.4958A24S[null][null]Trenton, NJ
9110male[null]0011376750.0A32S[null][null]Seattle, WA
9210male36.0001304940.125A10C[null][null]Winnipeg, MB
9310male55.010PC 1760359.4[null]C[null][null]New York, NY
9410male33.00011379026.55[null]S[null]109London
9510male61.013PC 17608262.375B57 B59 B63 B66C[null][null]Haverford, PA / Cooperstown, NY
9610male50.0101350755.9E44S[null][null]Duluth, MN
9710male56.00011379226.55[null]S[null][null]New York, NY
9810male56.0001776430.6958A7C[null][null]St James, Long Island, NY
9910male24.0101369560.0C31S[null][null]Huntington, WV
10010male[null]0011305626.0A19S[null][null]Streatham, Surrey
Rows: 1-100 | Columns: 14

We can also create one using a customized relation.

In [2]:
vp.vDataFrame(sql = "SELECT pclass, AVG(survived) AS survived FROM titanic GROUP BY 1")
Out[2]:
123
pclass
Integer
123
survived
Float
110.612179487179487
220.416988416988417
330.227752639517345
Rows: 1-3 | Columns: 2

In-memory vs. In-database Loading and Processing

First, let's load the expedia dataset in Vertica.

In [4]:
vp.read_csv("data/expedia.csv", schema = "public", parse_nrows = 2000)
Out[4]:
📅
date_time
Timestamp
123
site_name
Int
123
posa_continent
Int
123
user_location_country
Int
123
user_location_region
Int
123
user_location_city
Int
123
orig_destination_distance
Numeric(11,5)
123
user_id
Int
123
is_mobile
Int
123
is_package
Int
123
channel
Int
📅
srch_ci
Date
📅
srch_co
Date
123
srch_adults_cnt
Int
123
srch_children_cnt
Int
123
srch_rm_cnt
Int
123
srch_destination_id
Int
123
srch_destination_type_id
Int
123
is_booking
Int
123
cnt
Int
123
hotel_continent
Int
123
hotel_country
Int
123
hotel_market
Int
123
hotel_cluster
Int
12013-01-07 00:00:022423505703[null]4618990092013-03-142013-03-1521166930125021241
22013-01-07 00:00:062366174211775713.6206137960092013-01-192013-01-2610188211036173058
32013-01-07 00:00:0611320515514703795.729811285750092013-01-192013-01-2210125064601250123091
42013-01-07 00:00:093716976141949[null]10804760192013-05-292013-06-05201763530125067510
52013-01-07 00:00:173716976141949[null]10188950002013-09-082013-09-102012721560225064559
62013-01-07 00:00:173716976141949[null]10804760192013-05-292013-06-05201763530125067510
72013-01-07 00:00:182366462187672696.50037837251092013-07-292013-08-04331885510225021348
82013-01-07 00:00:233716976141949[null]10804760192013-05-292013-06-05201763530125067510
92013-01-07 00:00:2823662947976511.01211979680092013-01-162013-01-18101581130225035051
102013-01-07 00:00:28236635647794948.18615933750092013-05-092013-05-10402276330267019012
112013-01-07 00:00:2924234919105[null]11748190092013-04-252013-04-2820114908101388103275
122013-01-07 00:00:3324235053819[null]2335340012013-01-082013-01-091112672960161051248
132013-01-07 00:00:3923119027731[null]5190860092013-01-092013-01-111011510303399104382
142013-01-07 00:00:42231631213476[null]1767090092013-01-192013-01-20201235076026701959
152013-01-07 00:00:4824234919105[null]11748190092013-04-252013-04-2820114908103388103296
162013-01-07 00:00:502366442197445315.414111735040092013-01-202013-01-261118739111614442
172013-01-07 00:00:5124235053819[null]2335340012013-01-082013-01-091112672960261051261
182013-01-07 00:00:552366459438051768.1618369470192013-04-132013-04-183211225760245115095
192013-01-07 00:00:592323484924[null]10465580092013-02-152013-02-17101824410425064368
202013-01-07 00:00:59236617416634382.62038864360012013-02-152013-02-20201825010125062845
212013-01-07 00:01:102423505703[null]2077690012013-04-292013-05-012011856910231828326
222013-01-07 00:01:182321564651733149.14119015731092013-01-172013-01-20201221196024812858
232013-01-07 00:01:34236635647794948.18615933750092013-05-092013-05-10201276330367019012
242013-01-07 00:01:36236617467352404.024411035720032013-02-112013-02-13211251696022506744
252013-01-07 00:01:4523662947976509.484811979680092013-01-162013-01-18101581130425035076
262013-01-07 00:01:5324234919105[null]11748190092013-04-252013-04-282011490810338810325
272013-01-07 00:02:022366462147032605.09716143220012013-01-082013-01-152011309430125021241
282013-01-07 00:02:0234320515541977155.8357257530052013-01-112013-01-122018288111219839910
292013-01-07 00:02:202321564651733149.23439015731092013-01-172013-01-20201221196014812836
302013-01-07 00:02:2134320513513892118.08717996630052013-01-082013-01-1031125946611219837073
312013-01-07 00:02:2411320515514703796.328111285750092013-01-192013-01-221012506460125012304
322013-01-07 00:02:281314634718105555.79969703960092013-10-092013-10-162018279102250123070
332013-01-07 00:02:331132051551229154.31127568700002013-01-082013-01-0921126385601219811317
342013-01-07 00:02:362366348479971692.39118567620122013-03-242013-04-0160311634101435161640
352013-01-07 00:02:432366462147032605.40936143220012013-01-082013-01-152011309430125021240
362013-01-07 00:02:432426634631371384.018211620591022013-01-112013-01-1431114985101250124199
372013-01-07 00:02:45236635622202145.544611504960032013-01-182013-01-202011227160125066391
382013-01-07 00:02:462423505703[null]50610032013-01-212013-01-22111874610161052910
392013-01-07 00:02:592426634631371386.405511620591022013-01-112013-01-1431114985101250124191
402013-01-07 00:03:09236617442881379.3402807370092013-03-202013-03-2320147429301250123018
412013-01-07 00:03:19231631213476[null]1767090092013-01-192013-01-20201235076016701949
422013-01-07 00:03:232423505703[null]486357000[null][null]201804330125067683
432013-01-07 00:03:27171133251503[null]2286870192013-02-182013-02-2322288211016173082
442013-01-07 00:03:332321564651733149.24959015731092013-01-172013-01-20201221196014812867
452013-01-07 00:03:3523704714566[null]5980760092013-01-122013-01-13613822010231824646
462013-01-07 00:03:372426634631371383.601411620591022013-01-112013-01-1431114985101250124194
472013-01-07 00:03:412366174211775713.6394137960092013-01-192013-01-2610188211016173067
482013-01-07 00:03:521132051551229154.2167568700002013-01-082013-01-0921118111012198113189
492013-01-07 00:03:5911320515514703796.308211285750092013-01-192013-01-221012506460225012305
502013-01-07 00:04:02236635647794948.18615933750092013-05-092013-05-10201276331267019012
512013-01-07 00:04:142366348488621093.72183680840032013-03-042013-03-084014404530225070198
522013-01-07 00:04:172423505703[null]4863570002013-01-242013-01-28101804330425067683
532013-01-07 00:04:24231116714716[null]10027370192013-01-172013-01-2220146810134815364
542013-01-07 00:04:251314634718105557.0739703960092013-10-092013-10-162018279101250123068
552013-01-07 00:04:29236617442881379.3402807370092013-03-202013-03-2340247429301250123018
562013-01-07 00:04:312366348488621093.72183680840032013-03-012013-03-054014404530125070198
572013-01-07 00:04:3211320515553078989.65255646640192013-03-172013-03-21421825010525062845
582013-01-07 00:04:332335031800[null]1920040092013-02-242013-02-281112223860267726
592013-01-07 00:04:33231034538784[null]7533941192013-02-092013-02-17231826810125068231
602013-01-07 00:04:372366467161591510.87141034490192013-03-012013-03-0520187911114811065
612013-01-07 00:04:402426634631371390.732611620591022013-01-112013-01-1431114985101250124140
622013-01-07 00:04:422366348488621093.72183680840032013-03-022013-03-064014404530125070198
632013-01-07 00:04:4434320515514703995.19543328531092013-03-242013-03-282211220660325062879
642013-01-07 00:04:512366462187672688.26257837251092013-07-292013-08-0433188551012502136
652013-01-07 00:04:553716976141949[null]9761180132013-02-152013-02-15211101410139216892
662013-01-07 00:04:582366348488621093.72183680840032013-03-042013-03-064014404530325070198
672013-01-07 00:05:042335031800[null]1920040092013-02-242013-02-282112223860167726
682013-01-07 00:05:063716976141949[null]10804760192013-05-292013-06-05201763530125067583
692013-01-07 00:05:133716976141949[null]10804760192013-05-292013-06-05201763530125067555
702013-01-07 00:05:311711332046168[null]1245610032013-03-092013-03-1342182531026701948
712013-01-07 00:05:322319820854488[null]4253400092013-02-102013-02-1111146810134815364
722013-01-07 00:05:3424235022013[null]4872650002013-05-232013-05-2741166930125021296
732013-01-07 00:05:393716976141949[null]10188950002013-09-082013-09-102012721560325064591
742013-01-07 00:05:422366348488621093.72183680840032013-03-042013-03-074014404530525070198