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")

After creating a connection, you can start experimenting with the data in your database.

In [2]:
vDataFrame("public.iris")
Out[2]:
123
SepalLengthCm
Numeric(5,2)
123
SepalWidthCm
Numeric(5,2)
123
PetalLengthCm
Numeric(5,2)
123
PetalWidthCm
Numeric(5,2)
Abc
Species
Varchar(30)
14.33.01.10.1Iris-setosa
24.42.91.40.2Iris-setosa
34.43.01.30.2Iris-setosa
44.43.21.30.2Iris-setosa
54.52.31.30.3Iris-setosa
64.63.11.50.2Iris-setosa
74.63.21.40.2Iris-setosa
84.63.41.40.3Iris-setosa
94.63.61.00.2Iris-setosa
104.73.21.30.2Iris-setosa
114.73.21.60.2Iris-setosa
124.83.01.40.1Iris-setosa
134.83.01.40.3Iris-setosa
144.83.11.60.2Iris-setosa
154.83.41.60.2Iris-setosa
164.83.41.90.2Iris-setosa
174.92.43.31.0Iris-versicolor
184.92.54.51.7Iris-virginica
194.93.01.40.2Iris-setosa
204.93.11.50.1Iris-setosa
214.93.11.50.1Iris-setosa
224.93.11.50.1Iris-setosa
235.02.03.51.0Iris-versicolor
245.02.33.31.0Iris-versicolor
255.03.01.60.2Iris-setosa
265.03.21.20.2Iris-setosa
275.03.31.40.2Iris-setosa
285.03.41.50.2Iris-setosa
295.03.41.60.4Iris-setosa
305.03.51.30.3Iris-setosa
315.03.51.60.6Iris-setosa
325.03.61.40.2Iris-setosa
335.12.53.01.1Iris-versicolor
345.13.31.70.5Iris-setosa
355.13.41.50.2Iris-setosa
365.13.51.40.2Iris-setosa
375.13.51.40.3Iris-setosa
385.13.71.50.4Iris-setosa
395.13.81.50.3Iris-setosa
405.13.81.60.2Iris-setosa
415.13.81.90.4Iris-setosa
425.22.73.91.4Iris-versicolor
435.23.41.40.2Iris-setosa
445.23.51.50.2Iris-setosa
455.24.11.50.1Iris-setosa
465.33.71.50.2Iris-setosa
475.43.04.51.5Iris-versicolor
485.43.41.50.4Iris-setosa
495.43.41.70.2Iris-setosa
505.43.71.50.2Iris-setosa
515.43.91.30.4Iris-setosa
525.43.91.70.4Iris-setosa
535.52.34.01.3Iris-versicolor
545.52.43.71.0Iris-versicolor
555.52.43.81.1Iris-versicolor
565.52.54.01.3Iris-versicolor
575.52.64.41.2Iris-versicolor
585.53.51.30.2Iris-setosa
595.54.21.40.2Iris-setosa
605.62.53.91.1Iris-versicolor
615.62.74.21.3Iris-versicolor
625.62.84.92.0Iris-virginica
635.62.93.61.3Iris-versicolor
645.63.04.11.3Iris-versicolor
655.63.04.51.5Iris-versicolor
665.72.55.02.0Iris-virginica
675.72.63.51.0Iris-versicolor
685.72.84.11.3Iris-versicolor
695.72.84.51.3Iris-versicolor
705.72.94.21.3Iris-versicolor
715.73.04.21.2Iris-versicolor
725.73.81.70.3Iris-setosa
735.74.41.50.4Iris-setosa
745.82.64.01.2Iris-versicolor
755.82.73.91.2Iris-versicolor
765.82.74.11.0Iris-versicolor
775.82.75.11.9Iris-virginica
785.82.75.11.9Iris-virginica
795.82.85.12.4Iris-virginica
805.84.01.20.2Iris-setosa
815.93.04.21.5Iris-versicolor
825.93.05.11.8Iris-virginica
835.93.24.81.8Iris-versicolor
846.02.24.01.0Iris-versicolor
856.02.25.01.5Iris-virginica
866.02.75.11.6Iris-versicolor
876.02.94.51.5Iris-versicolor
886.03.04.81.8Iris-virginica
896.03.44.51.6Iris-versicolor
906.12.65.61.4Iris-virginica
916.12.84.01.3Iris-versicolor
926.12.84.71.2Iris-versicolor
936.12.94.71.4Iris-versicolor
946.13.04.61.4Iris-versicolor
956.13.04.91.8Iris-virginica
966.22.24.51.5Iris-versicolor
976.22.84.81.8Iris-virginica
986.22.94.31.3Iris-versicolor
996.23.45.42.3Iris-virginica
1006.32.34.41.3Iris-versicolor
Rows: 1-100 | Columns: 5

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]: