{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Descriptive Statistics" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "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.\n", "\n", "The 'agg' method is the best way to compute multiple aggregations on multiple columns at the same time." ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Help on function aggregate in module verticapy.core.vdataframe._aggregate:\n", "\n", "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\n", " Aggregates the vDataFrame using the input functions.\n", " \n", " Parameters\n", " ----------\n", " func: SQLExpression\n", " | List of the different aggregations:\n", " \n", " | **aad**: average absolute deviation\n", " | **approx_median**: approximate median\n", " | **approx_q%**: approximate q quantile\n", " (ex: approx_50% for the\n", " approximate median)\n", " | **approx_unique**: approximative cardinality\n", " | **count**: number of non-missing elements\n", " | **cvar**: conditional value at risk\n", " | **dtype**: virtual column type\n", " | **iqr**: interquartile range\n", " | **kurtosis**: kurtosis\n", " | **jb**: Jarque-Bera index\n", " | **mad**: median absolute deviation\n", " | **max**: maximum\n", " | **mean**: average\n", " | **median**: median\n", " | **min**: minimum\n", " | **mode**: most occurent element\n", " | **percent**: percent of non-missing elements\n", " | **q%**: q quantile (ex: 50% for the median)\n", " Use the ``approx_q%`` (approximate quantile)\n", " aggregation to get better performance.\n", " | **prod**: product\n", " | **range**: difference between the max and the min\n", " | **sem**: standard error of the mean\n", " | **skewness**: skewness\n", " | **sum**: sum\n", " | **std**: standard deviation\n", " | **topk**: kth most occurent element (ex: top1 for the mode)\n", " | **topk_percent**: kth most occurent element density\n", " | **unique**: cardinality (count distinct)\n", " | **var**: variance\n", " | Other aggregations will work if supported by your database\n", " version.\n", " columns: SQLColumns, optional\n", " List of the vDataColumn's names. If empty, depending on the\n", " aggregations, all or only numerical vDataColumns are used.\n", " ncols_block: int, optional\n", " Number of columns used per query. Setting this parameter\n", " divides what would otherwise be one large query into many\n", " smaller queries called \"blocks\", whose size is determine by\n", " the size of ncols_block.\n", " processes: int, optional\n", " Number of child processes to create. Setting this with the\n", " ncols_block parameter lets you parallelize a single query into\n", " many smaller queries, where each child process creates its own\n", " connection to the database and sends one query. This can improve\n", " query performance, but consumes more resources. If processes is\n", " set to 1, the queries are sent iteratively from a single process.\n", " \n", " Returns\n", " -------\n", " TableSample\n", " result.\n", " \n", " Examples\n", " --------\n", " For this example, we will use the following dataset:\n", " \n", " .. code-block:: python\n", " \n", " import verticapy as vp\n", " \n", " data = vp.vDataFrame({\n", " \"x\": [1, 2, 4, 9, 10, 15, 20, 22],\n", " \"y\": [1, 2, 1, 2, 1, 1, 2, 1],\n", " \"z\": [10, 12, 2, 1, 9, 8, 1, 3],\n", " })\n", " \n", " With the ``aggregate`` method, you have the flexibility to select specific\n", " aggregates and the columns you wish to include in the query. This\n", " allows for more precise control over the aggregation process and helps\n", " tailor the results to your specific needs.\n", " \n", " .. code-block:: python\n", " \n", " data.aggregate(\n", " func = [\"min\", \"approx_10%\", \"approx_50%\", \"approx_90%\", \"max\"],\n", " columns = [\"x\", \"y\", \"z\"],\n", " )\n", " \n", " .. ipython:: python\n", " :suppress:\n", " \n", " import verticapy as vp\n", " data = vp.vDataFrame({\n", " \"x\": [1, 2, 4, 9, 10, 15, 20, 22],\n", " \"y\": [1, 2, 1, 2, 1, 1, 2, 1],\n", " \"z\": [10, 12, 2, 1, 9, 8, 1, 3],\n", " })\n", " result = data.aggregate(\n", " func = [\"min\", \"approx_10%\", \"approx_50%\", \"approx_90%\", \"max\"],\n", " columns = [\"x\", \"y\", \"z\"],\n", " )\n", " html_file = open(\"figures/core_vDataFrame_vDFAgg_aggregate_table.html\", \"w\")\n", " html_file.write(result._repr_html_())\n", " html_file.close()\n", " \n", " .. raw:: html\n", " :file: SPHINX_DIRECTORY/figures/core_vDataFrame_vDFAgg_aggregate_table.html\n", " \n", " .. note:: All the calculations are pushed to the database.\n", " \n", " .. hint::\n", " \n", " When the vDataFrame includes a large number of columns and many aggregates\n", " need to be computed, it can be resource-intensive for the database. To\n", " address this, you can use the ``ncols_block`` parameter to control the number\n", " of blocks of aggregates to use and the ``processes`` parameter to manage\n", " the number of processes. These blocks consist of specific columns, and\n", " their aggregates are calculated first (or in parallel), then the subsequent\n", " ones, and the results are combined at the end.\n", " \n", " .. seealso::\n", " | :py:mod:`verticapy.vDataColumn.aggregate` :\n", " Aggregations for a specific column.\n", " | :py:mod:`verticapy.vDataColumn.describe` :\n", " Summarizes the information within the column.\n", " | :py:mod:`verticapy.vDataFrame.describe` :\n", " Summarizes the information for specific columns.\n", "\n" ] } ], "source": [ "import verticapy as vp\n", "help(vp.vDataFrame.agg)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This is a tremendously useful function for understanding your data. Let's use the churn dataset." ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
min
10%
median
90%
max
kurtosis
unique
"SeniorCitizen"000.0111.362595895793912
"Partner"000.011-1.99595342119472
"Dependents"000.011-1.23437805716952
"tenure"0229.06972-1.3873716359716973
"PhoneService"011.0115.438907555087062
"PaperlessBilling"001.011-1.859606185608842
"MonthlyCharges"18.2520.0570.35102.6118.75-1.257259694549511585
"TotalCharges"18.884.61397.4755976.648684.8-0.2317987608693626530
"Churn"000.011-0.8702113423319812
Rows: 1-9 | Columns: 8
" ], "text/plain": [ "None min 10% median 90% max \\\\\n", "\"SeniorCitizen\" 0 0 0.0 1 1 \\\\\n", "\"Partner\" 0 0 0.0 1 1 \\\\\n", "\"Dependents\" 0 0 0.0 1 1 \\\\\n", "\"tenure\" 0 2 29.0 69 72 \\\\\n", "\"PhoneService\" 0 1 1.0 1 1 \\\\\n", "\"PaperlessBilling\" 0 0 1.0 1 1 \\\\\n", "\"MonthlyCharges\" 18.25 20.05 70.35 102.6 118.75 \\\\\n", "\"TotalCharges\" 18.8 84.6 1397.475 5976.64 8684.8 \\\\\n", "\"Churn\" 0 0 0.0 1 1 \\\\\n", "None kurtosis unique \n", "\"SeniorCitizen\" 1.36259589579391 2 \n", "\"Partner\" -1.9959534211947 2 \n", "\"Dependents\" -1.2343780571695 2 \n", "\"tenure\" -1.38737163597169 73 \n", "\"PhoneService\" 5.43890755508706 2 \n", "\"PaperlessBilling\" -1.85960618560884 2 \n", "\"MonthlyCharges\" -1.25725969454951 1585 \n", "\"TotalCharges\" -0.231798760869362 6530 \n", "\"Churn\" -0.870211342331981 2 \n", "Rows: 1-9 | Columns: 8" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "vdf = vp.read_csv(\"data/churn.csv\")\n", "vdf.agg(func = [\"min\", \"10%\", \"median\", \"90%\", \"max\", \"kurtosis\", \"unique\"])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Some methods, like 'describe', are abstractions of the 'agg' method; they simplify the call to computing specific aggregations." ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
count
mean
std
min
approx_25%
approx_50%
approx_75%
max
"SeniorCitizen"70430.1621468124378820.3686116056100130.00.00.00.01.0
"Partner"70430.4830327985233560.4997475107199860.00.00.01.01.0
"Dependents"70430.2995882436461730.4581101675100150.00.00.01.01.0
"tenure"704332.371148658242324.55948102309440.09.029.055.072.0
"PhoneService"70430.9031662643759750.2957522317836350.01.01.01.01.0
"PaperlessBilling"70430.5922192247621750.4914569240494070.00.01.01.01.0
"MonthlyCharges"704364.76169246059930.090047097678518.2535.570.321428571428689.85118.75
"TotalCharges"70322283.300440841882266.7713618831518.8402.6833333333331397.4753798.23758684.8
"Churn"70430.2653698707936940.4415613051219470.00.00.01.01.0
Rows: 1-9 | Columns: 9
" ], "text/plain": [ "None count mean std min \\\\\n", "\"SeniorCitizen\" 7043 0.162146812437882 0.368611605610013 0.0 \\\\\n", "\"Partner\" 7043 0.483032798523356 0.499747510719986 0.0 \\\\\n", "\"Dependents\" 7043 0.299588243646173 0.458110167510015 0.0 \\\\\n", "\"tenure\" 7043 32.3711486582423 24.5594810230944 0.0 \\\\\n", "\"PhoneService\" 7043 0.903166264375975 0.295752231783635 0.0 \\\\\n", "\"PaperlessBilling\" 7043 0.592219224762175 0.491456924049407 0.0 \\\\\n", "\"MonthlyCharges\" 7043 64.761692460599 30.0900470976785 18.25 \\\\\n", "\"TotalCharges\" 7032 2283.30044084188 2266.77136188315 18.8 \\\\\n", "\"Churn\" 7043 0.265369870793694 0.441561305121947 0.0 \\\\\n", "None approx_25% approx_50% approx_75% max \n", "\"SeniorCitizen\" 0.0 0.0 0.0 1.0 \n", "\"Partner\" 0.0 0.0 1.0 1.0 \n", "\"Dependents\" 0.0 0.0 1.0 1.0 \n", "\"tenure\" 9.0 29.0 55.0 72.0 \n", "\"PhoneService\" 1.0 1.0 1.0 1.0 \n", "\"PaperlessBilling\" 0.0 1.0 1.0 1.0 \n", "\"MonthlyCharges\" 35.5 70.3214285714286 89.85 118.75 \n", "\"TotalCharges\" 402.683333333333 1397.475 3798.2375 8684.8 \n", "\"Churn\" 0.0 0.0 1.0 1.0 \n", "Rows: 1-9 | Columns: 9" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "vdf.describe()" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
123
"SeniorCitizen"
Int
100%
010
"Partner"
Boolean
100%
010
"Dependents"
Boolean
100%
123
"tenure"
Int
100%
010
"PhoneService"
Boolean
100%
010
"PaperlessBilling"
Boolean
100%
123
"MonthlyCharges"
Numeric(8,3)
100%
123
"TotalCharges"
Numeric(9,3)
99%
010
"Churn"
Boolean
100%
Abc
"customerID"
Varchar(20)
100%
Abc
"gender"
Varchar(20)
100%
Abc
"MultipleLines"
Varchar(100)
100%
Abc
"InternetService"
Varchar(22)
100%
Abc
"OnlineSecurity"
Varchar(38)
100%
Abc
"OnlineBackup"
Varchar(38)
100%
Abc
"DeviceProtection"
Varchar(38)
100%
Abc
"TechSupport"
Varchar(38)
100%
Abc
"StreamingTV"
Varchar(38)
100%
Abc
"StreamingMovies"
Varchar(38)
100%
Abc
"Contract"
Varchar(28)
100%
Abc
"PaymentMethod"
Varchar(50)
100%
dtypeintbooleanbooleanintbooleanbooleannumeric(8,3)numeric(9,3)booleanvarchar(20)varchar(20)varchar(100)varchar(22)varchar(38)varchar(38)varchar(38)varchar(38)varchar(38)varchar(38)varchar(28)varchar(50)
percent100.0100.0100.0100.0100.0100.0100.099.844100.0100.0100.0100.0100.0100.0100.0100.0100.0100.0100.0100.0100.0
count704370437043704370437043704370327043704370437043704370437043704370437043704370437043
top0
❌
❌
1
✅
✅
20.05[null]
❌
0002-ORFBOMaleNoFiber opticNoNoNoNoNoNoMonth-to-monthElectronic check
top_percent83.78551.69770.0418.70490.31759.2220.8660.15673.4630.01450.47648.13343.95949.66643.84543.94449.31139.89839.54355.01933.579
avg0.1621468124378820.4830327985233560.29958824364617332.37114865824230.9031662643759750.59221922476217564.7616924605992283.300440841880.26536987079369410.04.990487008377113.77750958398416.300014198494965.970041175635386.028255004969476.027261110322315.973590799375276.067726820956986.0712764446968611.301150078091718.5702115575749
stddev0.3686116056100130.4997475107199860.45811016751001524.55948102309440.2957522317836350.49145692404940730.09004709767852266.771361883150.4415613051219470.01.00002574729434.030386526393034.178818085360946.866548314487536.836814337332836.837327235013846.864753089095936.816296591249626.814437236183972.985058424526545.04042209794139
min00000018.2518.8010422222222812
approx_25%00091035.5402.683333333333010423222222816
approx_50%000291170.32142857142861397.4750104333333331416
approx_75%011551189.853798.237511063113333331423
max1117211118.758684.8110616111919191919191425
range1117211100.58666.0102149171717171717613
empty[null][null][null][null][null][null][null][null][null]000000000000
Rows: 1-14 | Columns: 22
" ], "text/plain": [ "None \"SeniorCitizen\" \"Partner\" \"Dependents\" \\\\\n", "dtype int boolean boolean \\\\\n", "percent 100.0 100.0 100.0 \\\\\n", "count 7043 7043 7043 \\\\\n", "top 0 False False \\\\\n", "top_percent 83.785 51.697 70.041 \\\\\n", "avg 0.162146812437882 0.483032798523356 0.299588243646173 \\\\\n", "stddev 0.368611605610013 0.499747510719986 0.458110167510015 \\\\\n", "min 0 0 0 \\\\\n", "approx_25% 0 0 0 \\\\\n", "approx_50% 0 0 0 \\\\\n", "approx_75% 0 1 1 \\\\\n", "max 1 1 1 \\\\\n", "range 1 1 1 \\\\\n", "empty None None None \\\\\n", "None \"tenure\" \"PhoneService\" \"PaperlessBilling\" \\\\\n", "dtype int boolean boolean \\\\\n", "percent 100.0 100.0 100.0 \\\\\n", "count 7043 7043 7043 \\\\\n", "top 1 True True \\\\\n", "top_percent 8.704 90.317 59.222 \\\\\n", "avg 32.3711486582423 0.903166264375975 0.592219224762175 \\\\\n", "stddev 24.5594810230944 0.295752231783635 0.491456924049407 \\\\\n", "min 0 0 0 \\\\\n", "approx_25% 9 1 0 \\\\\n", "approx_50% 29 1 1 \\\\\n", "approx_75% 55 1 1 \\\\\n", "max 72 1 1 \\\\\n", "range 72 1 1 \\\\\n", "empty None None None \\\\\n", "None \"MonthlyCharges\" \"TotalCharges\" \"Churn\" \\\\\n", "dtype numeric(8,3) numeric(9,3) boolean \\\\\n", "percent 100.0 99.844 100.0 \\\\\n", "count 7043 7032 7043 \\\\\n", "top 20.05 None False \\\\\n", "top_percent 0.866 0.156 73.463 \\\\\n", "avg 64.761692460599 2283.30044084188 0.265369870793694 \\\\\n", "stddev 30.0900470976785 2266.77136188315 0.441561305121947 \\\\\n", "min 18.25 18.8 0 \\\\\n", "approx_25% 35.5 402.683333333333 0 \\\\\n", "approx_50% 70.3214285714286 1397.475 0 \\\\\n", "approx_75% 89.85 3798.2375 1 \\\\\n", "max 118.75 8684.8 1 \\\\\n", "range 100.5 8666.0 1 \\\\\n", "empty None None None \\\\\n", "None \"customerID\" \"gender\" \"MultipleLines\" \"InternetService\" \\\\\n", "dtype varchar(20) varchar(20) varchar(100) varchar(22) \\\\\n", "percent 100.0 100.0 100.0 100.0 \\\\\n", "count 7043 7043 7043 7043 \\\\\n", "top 0002-ORFBO Male No Fiber optic \\\\\n", "top_percent 0.014 50.476 48.133 43.959 \\\\\n", "avg 10.0 4.99048700837711 3.7775095839841 6.30001419849496 \\\\\n", "stddev 0.0 1.0000257472943 4.03038652639303 4.17881808536094 \\\\\n", "min 10 4 2 2 \\\\\n", "approx_25% 10 4 2 3 \\\\\n", "approx_50% 10 4 3 3 \\\\\n", "approx_75% 10 6 3 11 \\\\\n", "max 10 6 16 11 \\\\\n", "range 0 2 14 9 \\\\\n", "empty 0 0 0 0 \\\\\n", "None \"OnlineSecurity\" \"OnlineBackup\" \"DeviceProtection\" \\\\\n", "dtype varchar(38) varchar(38) varchar(38) \\\\\n", "percent 100.0 100.0 100.0 \\\\\n", "count 7043 7043 7043 \\\\\n", "top No No No \\\\\n", "top_percent 49.666 43.845 43.944 \\\\\n", "avg 5.97004117563538 6.02825500496947 6.02726111032231 \\\\\n", "stddev 6.86654831448753 6.83681433733283 6.83732723501384 \\\\\n", "min 2 2 2 \\\\\n", "approx_25% 2 2 2 \\\\\n", "approx_50% 3 3 3 \\\\\n", "approx_75% 3 3 3 \\\\\n", "max 19 19 19 \\\\\n", "range 17 17 17 \\\\\n", "empty 0 0 0 \\\\\n", "None \"TechSupport\" \"StreamingTV\" \"StreamingMovies\" \\\\\n", "dtype varchar(38) varchar(38) varchar(38) \\\\\n", "percent 100.0 100.0 100.0 \\\\\n", "count 7043 7043 7043 \\\\\n", "top No No No \\\\\n", "top_percent 49.311 39.898 39.543 \\\\\n", "avg 5.97359079937527 6.06772682095698 6.07127644469686 \\\\\n", "stddev 6.86475308909593 6.81629659124962 6.81443723618397 \\\\\n", "min 2 2 2 \\\\\n", "approx_25% 2 2 2 \\\\\n", "approx_50% 3 3 3 \\\\\n", "approx_75% 3 3 3 \\\\\n", "max 19 19 19 \\\\\n", "range 17 17 17 \\\\\n", "empty 0 0 0 \\\\\n", "None \"Contract\" \"PaymentMethod\" \n", "dtype varchar(28) varchar(50) \n", "percent 100.0 100.0 \n", "count 7043 7043 \n", "top Month-to-month Electronic check \n", "top_percent 55.019 33.579 \n", "avg 11.3011500780917 18.5702115575749 \n", "stddev 2.98505842452654 5.04042209794139 \n", "min 8 12 \n", "approx_25% 8 16 \n", "approx_50% 14 16 \n", "approx_75% 14 23 \n", "max 14 25 \n", "range 6 13 \n", "empty 0 0 \n", "Rows: 1-14 | Columns: 22" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "vdf.describe(method = \"all\")" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "f8c70e29a31346b3b6a9162bfe154f57", "version_major": 2, "version_minor": 0 }, "text/plain": [ " 0%| | 0/2 [00:00
dtype
count
top
top_percent
"customerID"varchar(20)70430002-ORFBO0.014"gender"varchar(20)7043Male50.476"SeniorCitizen"int7043083.785"Partner"boolean7043051.697"Dependents"boolean7043070.041"tenure"int704318.704"PhoneService"boolean7043190.317"MultipleLines"varchar(100)7043No48.133"InternetService"varchar(22)7043Fiber optic43.959"OnlineSecurity"varchar(38)7043No49.666"OnlineBackup"varchar(38)7043No43.845"DeviceProtection"varchar(38)7043No43.944"TechSupport"varchar(38)7043No49.311"StreamingTV"varchar(38)7043No39.898"StreamingMovies"varchar(38)7043No39.543"Contract"varchar(28)7043Month-to-month55.019"PaperlessBilling"boolean7043159.222"PaymentMethod"varchar(50)7043Electronic check33.579"MonthlyCharges"numeric(8,3)704320.050.866"TotalCharges"numeric(9,3)7032[null]0.156"Churn"boolean7043073.463
Rows: 1-21 | Columns: 5
" ], "text/plain": [ "None dtype count top top_percent \n", "\"customerID\" varchar(20) 7043 0002-ORFBO 0.014 \n", "\"gender\" varchar(20) 7043 Male 50.476 \n", "\"SeniorCitizen\" int 7043 0 83.785 \n", "\"Partner\" boolean 7043 0 51.697 \n", "\"Dependents\" boolean 7043 0 70.041 \n", "\"tenure\" int 7043 1 8.704 \n", "\"PhoneService\" boolean 7043 1 90.317 \n", "\"MultipleLines\" varchar(100) 7043 No 48.133 \n", "\"InternetService\" varchar(22) 7043 Fiber optic 43.959 \n", "\"OnlineSecurity\" varchar(38) 7043 No 49.666 \n", "\"OnlineBackup\" varchar(38) 7043 No 43.845 \n", "\"DeviceProtection\" varchar(38) 7043 No 43.944 \n", "\"TechSupport\" varchar(38) 7043 No 49.311 \n", "\"StreamingTV\" varchar(38) 7043 No 39.898 \n", "\"StreamingMovies\" varchar(38) 7043 No 39.543 \n", "\"Contract\" varchar(28) 7043 Month-to-month 55.019 \n", "\"PaperlessBilling\" boolean 7043 1 59.222 \n", "\"PaymentMethod\" varchar(50) 7043 Electronic check 33.579 \n", "\"MonthlyCharges\" numeric(8,3) 7043 20.05 0.866 \n", "\"TotalCharges\" numeric(9,3) 7032 None 0.156 \n", "\"Churn\" boolean 7043 0 73.463 \n", "Rows: 1-21 | Columns: 5" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "vdf.describe(method = \"categorical\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Multi-column aggregations can also be called with many built-in methods. For example, you can compute the 'avg' of all the numerical columns in just one line." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
avg
"SeniorCitizen"0.162146812437882
"Partner"0.483032798523356
"Dependents"0.299588243646173
"tenure"32.3711486582423
"PhoneService"0.903166264375975
"PaperlessBilling"0.592219224762175
"MonthlyCharges"64.761692460599
"TotalCharges"2283.30044084188
"Churn"0.265369870793694
Rows: 1-9 | Columns: 2
" ], "text/plain": [ "None avg \n", "\"SeniorCitizen\" 0.162146812437882 \n", "\"Partner\" 0.483032798523356 \n", "\"Dependents\" 0.299588243646173 \n", "\"tenure\" 32.3711486582423 \n", "\"PhoneService\" 0.903166264375975 \n", "\"PaperlessBilling\" 0.592219224762175 \n", "\"MonthlyCharges\" 64.761692460599 \n", "\"TotalCharges\" 2283.30044084188 \n", "\"Churn\" 0.265369870793694 \n", "Rows: 1-9 | Columns: 2" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "vdf.avg()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Or just the 'median' of a specific column." ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "29.0" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "vdf[\"tenure\"].median()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The approximate median is automatically computed. Set the parameter 'approx' to False to get the exact median." ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "29.0" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "vdf[\"tenure\"].median(approx=False)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can also use the 'groupby' method to compute customized aggregations." ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
Abc
gender
Varchar(20)
Abc
Contract
Varchar(28)
123
Churn
Float(22)
1FemaleTwo year0.0260355029585799
2FemaleMonth-to-month0.437402597402597
3FemaleOne year0.104456824512535
4MaleTwo year0.0305882352941176
5MaleMonth-to-month0.416923076923077
6MaleOne year0.120529801324503
Rows: 1-6 | Columns: 3
" ], "text/plain": [ "None gender Contract Churn \n", "1 Female Two year 0.0260355029585799 \n", "2 Female Month-to-month 0.437402597402597 \n", "3 Female One year 0.104456824512535 \n", "4 Male Two year 0.0305882352941176 \n", "5 Male Month-to-month 0.416923076923077 \n", "6 Male One year 0.120529801324503 \n", "Rows: 1-6 | Columns: 3" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# SQL way\n", "vdf.groupby([\"gender\",\n", " \"Contract\"],\n", " [\"AVG(DECODE(Churn, 'Yes', 1, 0)) AS Churn\"])" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
Abc
gender
Varchar(20)
Abc
Contract
Varchar(28)
123
min_tenure
Integer
123
max_tenure
Integer
1MaleOne year072
2FemaleOne year172
3FemaleTwo year072
4FemaleMonth-to-month171
5MaleTwo year072
6MaleMonth-to-month172
Rows: 1-6 | Columns: 4
" ], "text/plain": [ "None gender Contract min_tenure max_tenure \n", "1 Male Two year 0 72 \n", "2 Male Month-to-month 1 72 \n", "3 Female Two year 0 72 \n", "4 Female Month-to-month 1 71 \n", "5 Female One year 1 72 \n", "6 Male One year 0 72 \n", "Rows: 1-6 | Columns: 4" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Pythonic way\n", "import verticapy.stats as st\n", "vdf.groupby([\"gender\", \"Contract\"],\n", " [st.min(vdf[\"tenure\"])._as(\"min_tenure\"),\n", " st.max(vdf[\"tenure\"])._as(\"max_tenure\")])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Computing many aggregations at the same time can be resource intensive. You can use the parameters 'ncols_block' and 'processes' to manage the ressources.\n", "\n", "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 vDataFrame.agg documentation." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.10.12" } }, "nbformat": 4, "nbformat_minor": 4 }