Descriptive Statistics¶

The easiest way to understand data is to aggregate it. An aggregation is a number or a category which summarizes the data. VerticaPy lets you compute all well-known aggregation in a single line.

The aggregate() method is the best way to compute multiple aggregations on multiple columns at the same time.

import verticapy as vp

help(vp.vDataFrame.agg)
Help on function aggregate in module verticapy.core.vdataframe._aggregate:

aggregate(self, func: Annotated[Union[str, list[str], ForwardRef('StringSQL'), list['StringSQL']], ''], columns: Optional[Annotated[Union[str, list[str]], 'STRING representing one column or a list of columns']] = None, ncols_block: int = 20, processes: int = 1) -> verticapy.core.tablesample.base.TableSample

Aggregates the vDataFrame using the input functions.

Parameters
----------
func: SQLExpression
    List of the different aggregations:

     - aad:
        average absolute deviation.
     - approx_median:
        approximate median.
     - approx_q%:
        approximate q quantile (ex: approx_50%
        for the approximate median).
     - approx_unique:
        approximative cardinality.
     - count:
        number of non-missing elements.
     - cvar:
        conditional value at risk.
     - dtype:
        virtual column type.
     - iqr:
        interquartile range.
     - kurtosis:
        kurtosis.
     - jb:
        Jarque-Bera index.
     - mad:
        median absolute deviation.
     - max:
        maximum.
     - mean:
        average.
     - median:
        median.
     - min:
        minimum.
     - mode:
        most occurent element.
     - percent:
        percent of non-missing elements.
     - q%:
        q quantile (ex: 50% for the median)
        Use the ``approx_q%`` (approximate quantile)
        aggregation to get better performance.
     - prod:
        product.
     - range:
        difference between the max and the min.
     - sem:
        standard error of the mean.
     - skewness:
        skewness.
     - sum:
        sum.
     - std:
        standard deviation.
     - topk:
        kth most occurent element (ex: top1 for the mode)
     - topk_percent:
        kth most occurent element density.
     - unique:
        cardinality (count distinct).
     - var:
        variance.

    Other aggregations will work if supported by your database
    version.

columns: SQLColumns, optional
    List of  the vDataColumn's names. If empty,  depending on the
    aggregations, all or only numerical vDataColumns are used.
ncols_block: int, optional
    Number  of columns  used per query.  Setting  this  parameter
    divides  what  would otherwise be  one large query into  many
    smaller  queries  called "blocks", whose size is determine by
    the size of ncols_block.
processes: int, optional
    Number  of child processes  to  create. Setting  this  with  the
    ncols_block  parameter lets you parallelize a  single query into
    many smaller  queries, where each child process creates  its own
    connection to the database and sends one query. This can improve
    query performance, but consumes  more resources. If processes is
    set to 1, the queries are sent iteratively from a single process.

Returns
-------
TableSample
    result.

This is a tremendously useful function for understanding your data. Let’s use the churn dataset

vdf = vp.read_csv("churn.csv")
vdf.agg(func = ["min", "10%", "median", "90%", "max", "kurtosis", "unique"])

	...	kurtosis	unique
"SeniorCitizen"	...	1.36259589579391	2
"Partner"	...	-1.9959534211947	2
"Dependents"	...	-1.2343780571695	2
"tenure"	...	-1.38737163597169	73
"PhoneService"	...	5.43890755508706	2
"PaperlessBilling"	...	-1.85960618560884	2
"MonthlyCharges"	...	-1.25725969454951	1585
"TotalCharges"	...	-0.231798760869362	6530
"Churn"	...	-0.870211342331981	2

Some methods, like describe(), are abstractions of the aggregate() method; they simplify the call to computing specific aggregations.

vdf.describe()

	...	approx_75%	max
"SeniorCitizen"	...	0.0	1.0
"Partner"	...	1.0	1.0
"Dependents"	...	1.0	1.0
"tenure"	...	55.0	72.0
"PhoneService"	...	1.0	1.0
"PaperlessBilling"	...	1.0	1.0
"MonthlyCharges"	...	89.85	118.75
"TotalCharges"	...	3799.47	8684.8
"Churn"	...	1.0	1.0

vdf.describe(method = "all")

	...	Abc "Contract" Varchar(28) 100%	Abc "PaymentMethod" Varchar(50) 100%
dtype	...	varchar(28)	varchar(50)
percent	...	100.0	100.0
count	...	7043	7043
top	...	Month-to-month	Electronic check
top_percent	...	55.019	33.579
avg	...	11.3011500780917	18.5702115575749
stddev	...	2.98505842452654	5.04042209794139
min	...	8	12
approx_25%	...	8	16
approx_50%	...	14	16
approx_75%	...	14	23
max	...	14	25
range	...	6	13
empty	...	0	0

vdf.describe(method = "categorical")

	...	top	top_percent
"customerID"	...	0002-ORFBO	0.014
"gender"	...	Male	50.476
"SeniorCitizen"	...	0	83.785
"Partner"	...	0	51.697
"Dependents"	...	0	70.041
"tenure"	...	1	8.704
"PhoneService"	...	1	90.317
"MultipleLines"	...	No	48.133
"InternetService"	...	Fiber optic	43.959
"OnlineSecurity"	...	No	49.666
"OnlineBackup"	...	No	43.845
"DeviceProtection"	...	No	43.944
"TechSupport"	...	No	49.311
"StreamingTV"	...	No	39.898
"StreamingMovies"	...	No	39.543
"Contract"	...	Month-to-month	55.019
"PaperlessBilling"	...	1	59.222
"PaymentMethod"	...	Electronic check	33.579
"MonthlyCharges"	...	20.05	0.866
"TotalCharges"	...	[null]	0.156
"Churn"	...	0	73.463

Multi-column aggregations can also be called with many built-in methods. For example, you can compute the vDataFrameavg() of all the numerical columns in just one line.

vdf.avg()

	avg
"SeniorCitizen"	0.162146812437882
"Partner"	0.483032798523357
"Dependents"	0.299588243646173
"tenure"	32.3711486582422
"PhoneService"	0.903166264375977
"PaperlessBilling"	0.592219224762175
"MonthlyCharges"	64.7616924605991
"TotalCharges"	2283.30044084187
"Churn"	0.265369870793696

Or just the median of a specific column.

vdf["tenure"].median()
Out[3]: 29.0

The approximate median is automatically computed. Set the parameter approx to False to get the exact median.

vdf["tenure"].median(approx = False)
Out[4]: 29.0

You can also use the groupby() method to compute customized aggregations.

# SQL way
vdf.groupby(
    [
        "gender",
        "Contract",
    ],
    [
        "AVG(DECODE(Churn, 'Yes', 1, 0)) AS Churn",
    ],
)

	Abc gender Varchar(20) 100%	...	Abc Contract Varchar(28) 100%	123 Churn Float(22) 100%
1	Female	...	Month-to-month	0.437402597402597
2	Female	...	One year	0.104456824512535
3	Male	...	One year	0.120529801324503
4	Male	...	Two year	0.0305882352941176
5	Female	...	Two year	0.0260355029585799
6	Male	...	Month-to-month	0.416923076923077

# Pythonic way
import verticapy.sql.functions as fun

vdf.groupby(
    [
        "gender",
        "Contract",
    ],
    [
        fun.min(vdf["tenure"])._as("min_tenure"),
        fun.max(vdf["tenure"])._as("max_tenure"),
    ],
)

	Abc gender Varchar(20) 100%	...	Abc Contract Varchar(28) 100%	123 max_tenure Integer 100%
1	Female	...	Month-to-month	71
2	Female	...	One year	72
3	Male	...	One year	72
4	Male	...	Two year	72
5	Female	...	Two year	72
6	Male	...	Month-to-month	72

Computing many aggregations at the same time can be resource intensive. You can use the parameters ncols_block and processes to manage the ressources.

For example, the parameter ncols_block will divide the main query into smaller using a specific number of columns. The parameter processes allows you to manage the number of queries you want to send at the same time.

An entire example is available in the aggregate() documentation.