Descriptive Statistics

The easiest way to understand data is to aggregate it. An aggregation is a number or a category which summarizes the data. vastorbit 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 vastorbit as vo

help(vo.VastFrame.agg)

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

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

	min	10%	median	90%	max	kurtosis	unique
"seniorcitizen"	0.0	0.0	0.0	1.0	1.0	1.3625958957939215	2.0
"tenure"	0.0	2.0	29.0	69.0	72.0	-1.3873716359717028	73.0
"monthlycharges"	18.25	20.102162826420894	70.44862291192591	102.58851812818602	118.75	-1.2572596945495114	1585.0
"totalcharges"	18.8	87.11162912308932	1400.0816226160912	5978.383331053353	8684.8	-0.23179876086936746	6530.0

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

vdf.describe()

	count	mean	std	min	approx_25%	approx_50%	approx_75%	max
"seniorcitizen"	7043.0	0.1621468124378816	0.368611605610013	0.0	0.0	0.0	0.0	1.0
"tenure"	7043.0	32.37114865824223	24.55948102309446	0.0	9.0	29.0	55.0	72.0
"monthlycharges"	7043.0	64.76169246059919	30.090047097678493	18.25	35.68514691248798	70.72470981442034	89.8382428893593	118.75
"totalcharges"	7032.0	2283.3004408418656	2266.771361883145	18.8	404.4292031578628	1411.8010114446745	3762.570648269476	8684.8

vdf.describe(method = "all")

	"seniorcitizen"	"tenure"	"monthlycharges"	"totalcharges"	"customerid"	"gender"	"partner"	"dependents"	"phoneservice"	"multiplelines"	"internetservice"	"onlinesecurity"	"onlinebackup"	"deviceprotection"	"techsupport"	"streamingtv"	"streamingmovies"	"contract"	"paperlessbilling"	"paymentmethod"	"churn"
dtype	integer	integer	double	double	varchar(50)	varchar(50)	varchar(50)	varchar(50)	varchar(50)	varchar(50)	varchar(50)	varchar(50)	varchar(50)	varchar(50)	varchar(50)	varchar(50)	varchar(50)	varchar(50)	varchar(50)	varchar(50)	varchar(50)
percent	100.0	100.0	100.0	99.844	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0
count	7043	7043	7043	7032	7043	7043	7043	7043	7043	7043	7043	7043	7043	7043	7043	7043	7043	7043	7043	7043	7043
top	0	1	20.05	[null]	8205-VSLRB	Male	No	No	Yes	No	Fiber optic	No	No	No	No	No	No	Month-to-month	Yes	Electronic check	No
top_percent	83.785	8.704	0.866	0.156	0.014	50.476	51.697	70.041	90.317	48.133	43.959	49.666	43.845	43.944	49.311	39.898	39.543	55.019	59.222	33.579	73.463
avg	0.1621468124378816	32.37114865824223	64.76169246059919	2283.3004408418656	10.0	4.990487008377112	2.4830327985233565	2.2995882436461734	2.9031662643759764	3.7775095839840978	6.300014198494959	5.970041175635383	6.028255004969473	6.027261110322306	5.973590799375266	6.067726820956978	6.071276444696863	11.301150078091723	2.592219224762175	18.570211557574897	2.265369870793696
stddev	0.368611605610013	24.55948102309446	30.090047097678493	2266.771361883145	0.0	1.0000257472943026	0.49974751071998724	0.4581101675100153	0.29575223178363474	4.030386526393035	4.178818085360937	6.866548314487532	6.836814337332832	6.837327235013845	6.864753089095935	6.816296591249624	6.814437236183974	2.985058424526545	0.4914569240494068	5.040422097941398	0.4415613051219471
min	0	0	18.25	18.8	10	4	2	2	2	2	2	2	2	2	2	2	2	8	2	12	2
approx_25%	0	9	35.68514691248798	404.4292031578628	10	4	2	2	3	2	3	2	2	2	2	2	2	8	2	15	2
approx_50%	0	29	70.72470981442034	1411.8010114446745	10	5	2	2	3	3	3	3	3	3	3	3	3	13	3	16	2
approx_75%	0	55	89.8382428893593	3762.570648269476	10	6	3	3	3	3	11	4	4	4	4	4	4	14	3	23	3
max	1	72	118.75	8684.8	10	6	3	3	3	16	11	19	19	19	19	19	19	14	3	25	3
range	1	72	100.5	8666.0	0	2	1	1	1	14	9	17	17	17	17	17	17	6	1	13	1
empty	[null]	[null]	[null]	[null]	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

vdf.describe(method = "categorical")

	dtype	count	top	top_percent
"customerid"	varchar(50)	7043	8205-VSLRB	0.014
"gender"	varchar(50)	7043	Male	50.476
"seniorcitizen"	integer	7043	0	83.785
"partner"	varchar(50)	7043	No	51.697
"dependents"	varchar(50)	7043	No	70.041
"tenure"	integer	7043	1	8.704
"phoneservice"	varchar(50)	7043	Yes	90.317
"multiplelines"	varchar(50)	7043	No	48.133
"internetservice"	varchar(50)	7043	Fiber optic	43.959
"onlinesecurity"	varchar(50)	7043	No	49.666
"onlinebackup"	varchar(50)	7043	No	43.845
"deviceprotection"	varchar(50)	7043	No	43.944
"techsupport"	varchar(50)	7043	No	49.311
"streamingtv"	varchar(50)	7043	No	39.898
"streamingmovies"	varchar(50)	7043	No	39.543
"contract"	varchar(50)	7043	Month-to-month	55.019
"paperlessbilling"	varchar(50)	7043	Yes	59.222
"paymentmethod"	varchar(50)	7043	Electronic check	33.579
"monthlycharges"	double	7043	20.05	0.866
"totalcharges"	double	7032	[null]	0.156
"churn"	varchar(50)	7043	No	73.463

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

vdf.avg()

	avg
"seniorcitizen"	0.1621468124378816
"tenure"	32.37114865824223
"monthlycharges"	64.76169246059919
"totalcharges"	2283.3004408418656

Or just the median of a specific column.

vdf["tenure"].median()

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

vdf["tenure"].median(approx = False)

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

# SQL way
vdf.groupby(
    [
        "gender",
        "Contract",
    ],
    [
        "AVG(CASE WHEN Churn = 'Yes' THEN 1 ELSE 0 END) AS Churn",
    ],
)

	Abc gender Varchar(50) 100%	Abc contract Varchar(50) 100%	123 Churn Double 100%
1	Male	Month-to-month	0.4169230769230769
2	Female	One year	0.10445682451253482
3	Female	Two year	0.02603550295857988
4	Male	One year	0.1205298013245033
5	Male	Two year	0.03058823529411765
6	Female	Month-to-month	0.4374025974025974

# Pythonic way
import vastorbit.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(50) 100%	Abc contract Varchar(50) 100%	123 min_tenure Integer 100%	123 max_tenure Integer 100%
1	Male	Month-to-month	1	72
2	Female	Month-to-month	1	71
3	Female	One year	1	72
4	Male	One year	0	72
5	Male	Two year	0	72
6	Female	Two year	0	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.