VerticaPy

Python API for Vertica Data Science at Scale

Quick Start

First, install the API using the pip command.

root@ubuntu:~$ pip3 install verticapy

Use the 'all' option to install the extra dependencies.

root@ubuntu:~$ pip3 install verticapy[all]

You can then choose which type of connection you want to use.

  • vertica_python (Native Python Client)
  • pyodbc (ODBC)
  • jaydebeapi (JDBC)

These modules create a Database Cursor, which you can use to communicate with your Vertica database.

For example, use the following command to install the vertica_python module.

root@ubuntu:~$ pip3 install vertica_python

If you already have a database DSN, you can use it to create database cursor.

In [1]:
from verticapy import *
cur = vertica_conn("VerticaDSN").cursor()

The DSN (Data Source Name) will be used to create a native connection.

Otherwise, you can set up a permanent auto-connection.

In [ ]:
from verticapy.connect import *
new_auto_connection({"host": "10.211.55.14", 
                     "port": "5433", 
                     "database": "testdb", 
                     "password": "XxX", 
                     "user": "dbadmin"},
                    name = "VerticaDSN")
change_auto_connection("VerticaDSN")

If you don't make a permanent auto-connection, you must pass a connection cursor as a parameter each time you create a new object. For the remaining examples, we assume that you have created a permanent auto-connection.

After creating a connection, you can start importing your own files (click here to download the iris dataset).

In [1]:
from verticapy import read_csv
iris = read_csv("iris.csv")
iris
Out[1]:
123
Id
Int
123
SepalLengthCm
Numeric(5,2)
123
SepalWidthCm
Numeric(5,2)
123
PetalLengthCm
Numeric(5,2)
123
PetalWidthCm
Numeric(5,2)
Abc
Species
Varchar(30)
115.13.51.40.2Iris-setosa
224.93.01.40.2Iris-setosa
334.73.21.30.2Iris-setosa
444.63.11.50.2Iris-setosa
555.03.61.40.2Iris-setosa
665.43.91.70.4Iris-setosa
774.63.41.40.3Iris-setosa
885.03.41.50.2Iris-setosa
994.42.91.40.2Iris-setosa
10104.93.11.50.1Iris-setosa
11115.43.71.50.2Iris-setosa
12124.83.41.60.2Iris-setosa
13134.83.01.40.1Iris-setosa
14144.33.01.10.1Iris-setosa
15155.84.01.20.2Iris-setosa
16165.74.41.50.4Iris-setosa
17175.43.91.30.4Iris-setosa
18185.13.51.40.3Iris-setosa
19195.73.81.70.3Iris-setosa
20205.13.81.50.3Iris-setosa
21215.43.41.70.2Iris-setosa
22225.13.71.50.4Iris-setosa
23234.63.61.00.2Iris-setosa
24245.13.31.70.5Iris-setosa
25254.83.41.90.2Iris-setosa
26265.03.01.60.2Iris-setosa
27275.03.41.60.4Iris-setosa
28285.23.51.50.2Iris-setosa
29295.23.41.40.2Iris-setosa
30304.73.21.60.2Iris-setosa
31314.83.11.60.2Iris-setosa
32325.43.41.50.4Iris-setosa
33335.24.11.50.1Iris-setosa
34345.54.21.40.2Iris-setosa
35354.93.11.50.1Iris-setosa
36365.03.21.20.2Iris-setosa
37375.53.51.30.2Iris-setosa
38384.93.11.50.1Iris-setosa
39394.43.01.30.2Iris-setosa
40405.13.41.50.2Iris-setosa
41415.03.51.30.3Iris-setosa
42424.52.31.30.3Iris-setosa
43434.43.21.30.2Iris-setosa
44445.03.51.60.6Iris-setosa
45455.13.81.90.4Iris-setosa
46464.83.01.40.3Iris-setosa
47475.13.81.60.2Iris-setosa
48484.63.21.40.2Iris-setosa
49495.33.71.50.2Iris-setosa
50505.03.31.40.2Iris-setosa
51517.03.24.71.4Iris-versicolor
52526.43.24.51.5Iris-versicolor
53536.93.14.91.5Iris-versicolor
54545.52.34.01.3Iris-versicolor
55556.52.84.61.5Iris-versicolor
56565.72.84.51.3Iris-versicolor
57576.33.34.71.6Iris-versicolor
58584.92.43.31.0Iris-versicolor
59596.62.94.61.3Iris-versicolor
60605.22.73.91.4Iris-versicolor
61615.02.03.51.0Iris-versicolor
62625.93.04.21.5Iris-versicolor
63636.02.24.01.0Iris-versicolor
64646.12.94.71.4Iris-versicolor
65655.62.93.61.3Iris-versicolor
66666.73.14.41.4Iris-versicolor
67675.63.04.51.5Iris-versicolor
68685.82.74.11.0Iris-versicolor
69696.22.24.51.5Iris-versicolor
70705.62.53.91.1Iris-versicolor
71715.93.24.81.8Iris-versicolor
72726.12.84.01.3Iris-versicolor
73736.32.54.91.5Iris-versicolor
74746.12.84.71.2Iris-versicolor
75756.42.94.31.3Iris-versicolor
76766.63.04.41.4Iris-versicolor
77776.82.84.81.4Iris-versicolor
78786.73.05.01.7Iris-versicolor
79796.02.94.51.5Iris-versicolor
80805.72.63.51.0Iris-versicolor
81815.52.43.81.1Iris-versicolor
82825.52.43.71.0Iris-versicolor
83835.82.73.91.2Iris-versicolor
84846.02.75.11.6Iris-versicolor
85855.43.04.51.5Iris-versicolor
86866.03.44.51.6Iris-versicolor
87876.73.14.71.5Iris-versicolor
88886.32.34.41.3Iris-versicolor
89895.63.04.11.3Iris-versicolor
90905.52.54.01.3Iris-versicolor
91915.52.64.41.2Iris-versicolor
92926.13.04.61.4Iris-versicolor
93935.82.64.01.2Iris-versicolor
94945.02.33.31.0Iris-versicolor
95955.62.74.21.3Iris-versicolor
96965.73.04.21.2Iris-versicolor
97975.72.94.21.3Iris-versicolor
98986.22.94.31.3Iris-versicolor
99995.12.53.01.1Iris-versicolor
1001005.72.84.11.3Iris-versicolor
Rows: 1-100 | Columns: 6

If you don't have data on hand, you can load some well-known datasets. We start by creating a vDataFrame using a dataset. In this example, we use the 'titanic' dataset.

In [3]:
from verticapy.datasets import load_titanic
vdf = load_titanic()

We can now explore our imported dataset through the vDataFrame.

In [4]:
vdf
Out[4]:
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

You filter your data with a pandas-like syntax.

In [5]:
vdf[["name", "age"]][(vdf["age"] > 30) & (vdf["age"] < 60)]
Out[5]:
Abc
Varchar(164)
123
age
Numeric(6,3)
139.0
247.0
336.0
445.0
542.0
641.0
748.0
845.0
933.0
1049.0
1136.0
1246.0
1347.0
1437.0
1539.0
1631.0
1750.0
1839.0
1936.0
2037.0
2147.0
2238.0
2346.0
2445.0
2540.0
2655.0
2742.0
2855.0
2942.0
3050.0
3146.0
3250.0
3332.5
3458.0
3541.0
3646.0
3754.0
3844.0
3955.0
4047.0
4137.0
4258.0
4345.5
4447.0
4538.0
4631.0
4736.0
4855.0
4933.0
5050.0
5156.0
5256.0
5357.0
5452.0
5549.0
5640.0
5747.0
5854.0
5957.0
6050.0
6151.0
6248.0
6353.0
6450.0
6532.0
6637.0
6747.0
6842.0
6935.0
7040.0
7158.0
7245.0
7344.0
7459.0
7541.0
7642.0
7753.0
7836.0
7958.0
8036.0
8136.0
8247.0
8333.0
8436.0
8545.0
8639.0
8755.0
8836.0
8938.0
9051.0
9133.0
9231.0
9353.0
9454.0
9548.0
9638.0
9754.0
9850.0
9943.0
10048.0
Rows: 1-100 | Columns: 2

With the exception of aggregations, everything is happening in the database and nothing is loaded in memory. You can view the generated SQL code using the set_option function.

In [6]:
set_option("sql_on", True)
vdf.describe()

Computes the descriptive statistics of all the numerical columns using SUMMARIZE_NUMCOL.

  SELECT
    SUMMARIZE_NUMCOL("pclass", "survived", "age", "sibsp", "parch", "fare", "body") OVER ()  
  FROM
"public"."titanic"

Computes the different aggregations.

  SELECT
    COUNT(DISTINCT "age"),
    COUNT(DISTINCT "body"),
    COUNT(DISTINCT "fare"),
    COUNT(DISTINCT "parch"),
    COUNT(DISTINCT "pclass"),
    COUNT(DISTINCT "sibsp"),
    COUNT(DISTINCT "survived")  
  FROM
"public"."titanic" LIMIT 1
Out[6]:
count
mean
std
min
25%
50%
75%
max
unique
"age"99730.152457372116314.43530462991590.3321.028.039.080.096.0
"body"118164.1440677966196.57602075578081.079.25160.5257.5328.0118.0
"fare"123333.96379367396652.64607298312930.07.895814.454231.3875512.3292277.0
"parch"12340.3784440842787680.8686047077903930.00.00.00.09.08.0
"pclass"12342.284440842787680.8424856361902921.01.03.03.03.03.0
"sibsp"12340.5040518638573741.041117272416290.00.00.01.08.07.0
"survived"12340.3646677471636960.4815320186412880.00.00.01.01.02.0
Rows: 1-7 | Columns: 10

VerticaPy is smart enough to not recompute the same aggregation twice. Each virtual column has its own catalog which will be updated as the user modifies it.

In [7]:
vdf["age"].catalog
Out[7]:
{'25%': 21,
 '50%': 28,
 '75%': 39,
 'avg': 30.1524573721163,
 'biserial': {},
 'count': 997,
 'cov': {},
 'cramer': {},
 'kendall': {},
 'max': 80,
 'min': 0.33,
 'pearson': {},
 'regr_avgx': {},
 'regr_avgy': {},
 'regr_count': {},
 'regr_intercept': {},
 'regr_r2': {},
 'regr_slope': {},
 'regr_sxx': {},
 'regr_sxy': {},
 'regr_syy': {},
 'spearman': {},
 'std': 14.4353046299159,
 'unique': 96}

Draw the dataset Correlation Matrix.

In [8]:
vdf.corr(method = "spearman")

Computes the spearman Corr Matrix.

  SELECT
    CORR_MATRIX("pclass", "survived", "age", "sibsp", "parch", "fare", "body") OVER ()  
  FROM
(
  SELECT
    RANK() OVER (ORDER BY "pclass") AS "pclass",
    RANK() OVER (ORDER BY "survived") AS "survived",
    RANK() OVER (ORDER BY "age") AS "age",
    RANK() OVER (ORDER BY "sibsp") AS "sibsp",
    RANK() OVER (ORDER BY "parch") AS "parch",
    RANK() OVER (ORDER BY "fare") AS "fare",
    RANK() OVER (ORDER BY "body") AS "body"  
  FROM
"public"."titanic") spearman_table
Out[8]:
"pclass"
"survived"
"age"
"sibsp"
"parch"
"fare"
"body"
"pclass"1.0-0.335634606444129-0.167006853613121-0.0691851178284916-0.0429882472107741-0.7225221647915550.0306003583215355
"survived"-0.3356346064441291.0-0.09097639691725840.09343035183924840.1716914555791240.3224096131114810.211060721441923
"age"-0.167006853613121-0.09097639691725841.0-0.158699476099267-0.2353446209476840.00451935857538285-0.0206617211295797
"sibsp"-0.06918511782849160.0934303518392484-0.1586994760992671.00.4365249859077550.4439429239672830.0332791733089436
"parch"-0.04298824721077410.171691455579124-0.2353446209476840.4365249859077551.00.4023733695150660.0606076641278389
"fare"-0.7225221647915550.3224096131114810.004519358575382850.4439429239672830.4023733695150661.0-0.000856180961294435
"body"0.03060035832153550.211060721441923-0.02066172112957970.03327917330894360.0606076641278389-0.0008561809612944351.0
Rows: 1-7 | Columns: 8

Use statistical tests.

In [10]:
from verticapy.stats import jarque_bera
jarque_bera(vdf, "age")
Out[10]: