vDataFrame.groupby¶

In [ ]:

vDataFrame.groupby(columns: Union[list, str],
                   expr: Union[list, str] = [],
                   rollup: Union[bool, list] = False,
                   having: str = "",)

Aggregates the vDataFrame by grouping the elements.

Parameters¶

Name	Type	Optional	Description
columns	list / str	❌	List of the vColumns used to group the elements or a customized expression. If rollup is set to True, this can be a list of tuples.
expr	list	✓	List of the different aggregations in pure SQL. Aliases can be used. For example, 'SUM(column)' or 'AVG(column) AS my_new_alias' are correct whereas 'AVG' is incorrect. Aliases are recommended to keep the track of the features and to prevent ambiguous names. For example, the MODE function does not exist, but can be replicated by using the 'analytic' method and then grouping the result.
rollup	bool / list of bools	✓	If set to True, the rollup operator is used. If set to a list of bools, the rollup operator is used on the matching indexes and the length of 'rollup' must match the length of 'columns.' For example, for columns = ['col1', ('col2', 'col3'), 'col4'] and rollup = [False, True, True], the rollup operator is used on the set ('col2', 'col3') and on 'col4'.
having	str	✓	Expression used to filter the result.

Returns¶

vDataFrame : object result of the grouping.

Example¶

In [1]:

from verticapy.datasets import load_market
market = load_market()
display(market)

	Abc Form Varchar(32)	Abc Name Varchar(32)	123 Price Float
1	Beefsteak,	Tomatoes	3.15921213872
2	Boiled	Spinach	3.8265942203
3	Boiled	Spinach	3.83371571139
4	Canned	Artichoke	3.3174627313
5	Canned	Artichoke	3.38561047493
6	Canned	Asparagus	2.5802971683
7	Canned	Asparagus	2.7200086787
8	Canned	Beets	0.9427311158
9	Canned	Beets	1.01727546324
10	Canned	Black beans	0.945681045
11	Canned	Black beans	0.980579717755
12	Canned	Blackeye peas	0.910440813422
13	Canned	Blackeye peas	0.9310398825
14	Canned	Carrots	0.9231914223
15	Canned	Carrots	1.06049032785
16	Canned	Collard greens	0.8534190293
17	Canned	Collard greens	0.902593822572
18	Canned	Corn	0.8537281609
19	Canned	Corn	0.903905336945
20	Canned	Great northern beans	0.8733322508
21	Canned	Great northern beans	0.923915876218
22	Canned	Green beans	0.824040727
23	Canned	Green beans	0.826939082717
24	Canned	Green peas	0.9870138533
25	Canned	Green peas	1.01306918204
26	Canned	Kale	1.0686461381
27	Canned	Lima beans	1.3282126206
28	Canned	Lima beans	1.3835692655
29	Canned	Mixed Vegetables	1.10743317098
30	Canned	Mixed Vegetables	1.1153572418
31	Canned	Mixed Vegetables	1.1300407331
32	Canned	Mixed Vegetables	1.32442847693
33	Canned	Mustard greens	0.8227393425
34	Canned	Mustard greens	0.976791582056
35	Canned	Navy beans	0.9462506636
36	Canned	Navy beans	0.970423315186
37	Canned	Pinto beans	0.8013951325
38	Canned	Pinto beans	0.866189704098
39	Canned	Potatoes	0.9031082713
40	Canned	Potatoes	0.965992955381
41	Canned	Pumpkin	1.35228040942
42	Canned	Pumpkin	1.3781292617
43	Canned	Red Kidney Beans	0.861913772
44	Canned	Red Kidney Beans	0.901679904473
45	Canned	Spinach	1.1345923224
46	Canned	Spinach	1.1471519614
47	Canned	Tomatoes	0.912876896
48	Canned	Tomatoes	0.924836358886
49	Canned	Turnip greens	0.7843472032
50	Canned	Turnip greens	0.965253514158
51	Canned,	Olives	4.18683171458
52	Canned,	Olives	5.0861220216
53	Canned, packed in syrup or water	Cherries	3.51874337733
54	Canned, packed in syrup or water	Cherries	3.7259182628
55	Cooked whole	Carrots	0.741539992379
56	Cooked whole	Carrots	0.7737514155
57	Dried	Apricots	7.3309645323
58	Dried	Apricots	7.73397208999
59	Dried	Black beans	1.4038762924
60	Dried	Black beans	1.48978420263
61	Dried	Blackeye peas	1.58990085218
62	Dried	Blackeye peas	1.613022434
63	Dried	Cranberries	4.6894038279
64	Dried	Cranberries	4.78674137766
65	Dried	Dates	4.79135143479
66	Dried	Dates	5.5117046698
67	Dried	Figs	5.7483176391
68	Dried	Figs	6.1253708776
69	Dried	Great northern beans	1.53402874163
70	Dried	Great northern beans	1.5891818482
71	Dried	Lentils	1.3850358051
72	Dried	Lentils	1.5614348422
73	Dried	Lima beans	1.73065564313
74	Dried	Lima beans	2.2195369395
75	Dried	Mangoes	8.50464930168
76	Dried	Mangoes	10.1637125484
77	Dried	Navy beans	1.4787507743
78	Dried	Navy beans	1.50577973442
79	Dried	Papaya	4.56591253937
80	Dried	Papaya	5.2722599372
81	Dried	Pineapple	5.49708634567
82	Dried	Pineapple	5.8687608122
83	Dried	Pinto beans	1.085109083
84	Dried	Pinto beans	1.20380752717
85	Dried	Red Kidney Beans	1.67188888931
86	Dried	Red Kidney Beans	1.6865123081
87	Dried (Prunes)	Plums	4.03634516164
88	Dried (Prunes)	Plums	4.7339261696
89	Florets	Broccoli	2.3624557989
90	Florets	Broccoli	2.56847143403
91	Florets	Cauliflower	3.1279734694
92	Florets	Cauliflower	3.27064843515
93	Fresh	Acorn squash	1.1193087167
94	Fresh	Acorn squash	1.1722478842
95	Fresh	Apples	1.56751539145
96	Fresh	Apples	1.6155336441
97	Fresh	Apricots	3.0400719671
98	Fresh	Apricots	3.087137817
99	Fresh	Artichoke	2.21305047929
100	Fresh	Artichoke	2.3637333814

Rows: 1-100 | Columns: 3

In [2]:

market.groupby(columns = ["Form", "Name"],
               expr = ["AVG(Price) AS avg_price",
                       "STDDEV(Price) AS std"])

Out[2]:

	Abc Form Varchar(32)	Abc Name Varchar(32)	123 avg_price Float	123 std Float
1	Beefsteak,	Tomatoes	3.15921213872	nan
2	Boiled	Spinach	3.830154965845	0.0050356546423244
3	Canned	Artichoke	3.351536603115	0.048187731643313
4	Canned	Asparagus	2.6501529235	0.0987909564136379
5	Canned	Beets	0.98000328952	0.0527108135739495
6	Canned	Black beans	0.9631303813775	0.0246770881594642
7	Canned	Blackeye peas	0.920740347961	0.0145657414311878
8	Canned	Carrots	0.991840875075	0.0970849871638956
9	Canned	Collard greens	0.878006425936	0.0347718297860748
10	Canned	Corn	0.8788167489225	0.0354806214422065
11	Canned	Great northern beans	0.898624063509	0.0357680245500685
12	Canned	Green beans	0.8254899048585	0.00204944698175326
13	Canned	Green peas	1.00004151767	0.0184238996381032
14	Canned	Kale	1.0686461381	nan
15	Canned	Lima beans	1.35589094305	0.0391430589925351
16	Canned	Mixed Vegetables	1.1693149057025	0.103832331507163
17	Canned	Mustard greens	0.899765462278	0.108931383247022
18	Canned	Navy beans	0.958336989393	0.0170926458557113
19	Canned	Pinto beans	0.833792418299	0.0458166809610275
20	Canned	Potatoes	0.9345506133405	0.0444661865464487
21	Canned	Pumpkin	1.36520483556	0.0182778987330547
22	Canned	Red Kidney Beans	0.8817968382365	0.0281189019332177
23	Canned	Spinach	1.1408721419	0.00888100590615068
24	Canned	Tomatoes	0.918856627443	0.00845661730602896
25	Canned	Turnip greens	0.874800358679	0.127920079237844
26	Canned,	Olives	4.63647686809	0.635894274349174
27	Canned, packed in syrup or water	Cherries	3.622330820065	0.146494766407389
28	Cooked whole	Carrots	0.7576457039395	0.0227769157205302
29	Dried	Apricots	7.532468311145	0.284969376912029
30	Dried	Black beans	1.446830247515	0.0607460658812006
31	Dried	Blackeye peas	1.60146164309	0.0163494272967093
32	Dried	Cranberries	4.73807260278	0.0688280414993992
33	Dried	Dates	5.151528052295	0.509366657325232
34	Dried	Figs	5.93684425835	0.266616901811729
35	Dried	Great northern beans	1.561605294915	0.0389991356591416
36	Dried	Lentils	1.47323532365	0.124732955328185
37	Dried	Lima beans	1.975096291315	0.345691279858497
38	Dried	Mangoes	9.33418092504	1.17313487217308
39	Dried	Navy beans	1.49226525436	0.0191123609892753
40	Dried	Papaya	4.919086238285	0.499463034879057
41	Dried	Pineapple	5.682923578935	0.262813535677252
42	Dried	Pinto beans	1.144458305085	0.0839324747888937
43	Dried	Red Kidney Beans	1.679200598705	0.0103403185904881
44	Dried (Prunes)	Plums	4.38513566562	0.493264261155465
45	Florets	Broccoli	2.465463616465	0.145675052630875
46	Florets	Cauliflower	3.199310952275	0.10088643578742
47	Fresh	Acorn squash	1.14577830045	0.0374336443296234
48	Fresh	Apples	1.591524517775	0.033954032069563
49	Fresh	Apricots	3.06360489205	0.0332805816266319
50	Fresh	Artichoke	2.288391930345	0.106548901890849
51	Fresh	Asparagus	3.14455233272	0.0974983724584246
52	Fresh	Avocado	2.23070922879	0.00730462912737973
53	Fresh	Bananas	0.5582003536655	0.0124211237951375
54	Fresh	Blackberries	5.718189793175	0.0799291686612887
55	Fresh	Blueberries	4.562865030315	0.242900597172589
56	Fresh	Brussels sprouts	2.862823175555	0.140388703214564
57	Fresh	Butternut squash	1.268373863485	0.0334279917035078
58	Fresh	Cantaloupe	0.528333724053	0.0106632438743567
59	Fresh	Cherries	3.403028933095	0.26864517135055
60	Fresh	Collard greens	2.630153140225	0.000968403844114992
61	Fresh	Corn	2.977775549065	0.406094804455345
62	Fresh	Grapefruit	0.95379963399	0.0791925552121489
63	Fresh	Grapes	2.16485030425	0.100441537458926
64	Fresh	Green beans	2.133124356205	0.00968438768679218
65	Fresh	Green peppers	1.4526667429	0.0598270480860252
66	Fresh	Honeydew melon	0.811323709865	0.0207429836981466
67	Fresh	Kale	2.8422960573	0.049489387502778
68	Fresh	Kiwi	2.113005543435	0.0966220905848999
69	Fresh	Lettuce, Iceberg	1.151147487615	0.0875284236578059
70	Fresh	Mangoes	1.350540374165	0.0382163961426793
71	Fresh	Mustard greens	2.624251782445	0.0778054568543142
72	Fresh	Nectarines	1.8211978872	0.0849226821149126
73	Fresh	Okra	3.51718827745	0.429406103192065
74	Fresh	Onions	1.042744484175	0.00655817614593457
75	Fresh	Oranges	1.06614716527	0.0438044661062775
76	Fresh	Papaya	1.29577168415	0.00316770410008883
77	Fresh	Peaches	1.634559217475	0.061337785719176
78	Fresh	Pears	1.4895833073	0.0396102875056685
79	Fresh	Pineapple	0.640228246668	0.0177714329240488
80	Fresh	Plums	1.907707425905	0.113549212412115
81	Fresh	Pomegranate	2.12601021818	0.0672885552026009
82	Fresh	Potatoes	0.5840279522645	0.0278715165231906
83	Fresh	Radish	1.386866580055	0.106402359228897
84	Fresh	Raspberries	6.92675656173	0.0693731674410974
85	Fresh	Red peppers	2.29930513481	0.0302152343301621
86	Fresh	Strawberries	2.433629063505	0.105812279539221
87	Fresh	Summer squash	1.639761267255	0.000401969338397509
88	Fresh	Sweet potatoes	0.9866529454995	0.0958220140200086
89	Fresh	Tangerines	1.429179024965	0.0724319849529066
90	Fresh	Turnip greens	2.490080924435	0.0259255090642032
91	Fresh	Watermelon	0.3250254072615	0.0118604831471543
92	Fresh green cabbage	Cabbage	0.601539811679	0.0315813933837907
93	Fresh red cabbage	Cabbage	1.040287378205	0.0228570141526365
94	Fresh, consumed with peel	Cucumbers	1.27575847412	0.0285287803154144
95	Fresh, peeled	Cucumbers	1.27575847412	0.0285287803154144
96	Frozen	Apples	0.5241668305185	0.0193762602523861
97	Frozen	Artichoke	5.99487180028	0.320385171670741
98	Frozen	Asparagus	5.961553003735	0.147407849193659
99	Frozen	Berries, mixed	3.52357772168	0.160319404842618
100	Frozen	Blackberries	3.54025586193	0.214613990004942

Rows: 1-100 | Columns: 4

In [2]:

# group by with one rollup
market.groupby(columns = ["Form", "Name"],
               expr = ["AVG(Price) AS avg_price",
                       "STDDEV(Price) AS std"],
               rollup = [True, False])

Out[2]:

	Abc Form Varchar(32)	Abc Name Varchar(32)	123 avg_price Float	123 std Float
1	Fresh	Grapes	2.16485030425	0.100441537458926
2	[null]	Olives	4.63647686809	0.635894274349174
3	Raw	Spinach	3.830154965845	0.0050356546423244
4	Fresh	Watermelon	0.3250254072615	0.0118604831471543
5	Fresh	Pineapple	0.640228246668	0.0177714329240488
6	[null]	Kale	2.108602528004	0.760880973352267
7	[null]	Papaya	3.1074289612175	2.11170402880126
8	Raw whole	Carrots	0.7576457039395	0.0227769157205302
9	Frozen	Apples	0.5241668305185	0.0193762602523861
10	Fresh	Cherries	3.403028933095	0.26864517135055
11	[null]	Lettuce, Iceberg	1.151147487615	0.0875284236578059
12	Frozen	Pineapple	0.6055659651125	0.0209496044750691
13	[null]	Lettuce, Romaine	2.15669058806	0.596766685004083
14	Fresh	Tangerines	1.429179024965	0.0724319849529066
15	[null]	Berries, mixed	3.52357772168	0.160319404842618
16	Packed in syrup, syrup discarded	Apricots	1.70366667044	0.214428912851844
17	Canned	Kale	1.0686461381	nan
18	Dried	Dates	5.151528052295	0.509366657325232
19	Fresh	Okra	3.51718827745	0.429406103192065
20	[null]	Butternut squash	1.268373863485	0.0334279917035078
21	Fresh	Acorn squash	1.14577830045	0.0374336443296234
22	Fresh	Apples	1.591524517775	0.033954032069563
23	Fresh	Red peppers	2.29930513481	0.0302152343301621
24	[null]	Sweet potatoes	0.9866529454995	0.0958220140200086
25	[null]	Turnip greens	1.63047105434967	0.729972478153256
26	[null]	Mustard greens	1.67652526910267	0.785415190661448
27	Canned	Artichoke	3.351536603115	0.048187731643313
28	Fresh	Nectarines	1.8211978872	0.0849226821149126
29	[null]	Artichoke	3.87826677791333	1.71386396435448
30	Packed in syrup or water	Pineapple	1.24344553067	0.162630450717828
31	Packed in syrup or water	Peaches	1.546235219665	0.0566428888827602
32	Canned	Carrots	0.991840875075	0.0970849871638956
33	Canned, packed in syrup or water	Cherries	3.622330820065	0.146494766407389
34	Ready to drink	Grapes	0.879555994869	0.0480536917097328
35	[null]	Brussels sprouts	2.4759089546975	0.455840736086586
36	Canned	Mustard greens	0.899765462278	0.108931383247022
37	Canned	Black beans	0.9631303813775	0.0246770881594642
38	Boiled	Spinach	3.830154965845	0.0050356546423244
39	Florets	Cauliflower	3.199310952275	0.10088643578742
40	Fresh	Pears	1.4895833073	0.0396102875056685
41	[null]	Mushrooms	3.6480619267825	0.201796598770831
42	Fresh, consumed with peel	Cucumbers	1.27575847412	0.0285287803154144
43	Fresh	Pomegranate	2.12601021818	0.0672885552026009
44	Dried	Lima beans	1.975096291315	0.345691279858497
45	[null]	Cucumbers	1.27575847412	0.0232936515855741
46	[null]	Navy beans	1.2253011218765	0.308618876618397
47	Canned	Potatoes	0.9345506133405	0.0444661865464487
48	Roma,	Tomatoes	1.24341736034	nan
49	Ready to drink	Apples	0.6792101204525	0.0679919835754149
50	Fresh	Radish	1.386866580055	0.106402359228897
51	[null]	Blueberries	4.060226482135	0.601181853297374
52	Dried	Navy beans	1.49226525436	0.0191123609892753
53	Frozen	Grapes	0.728197301698	0.0115558279448051
54	Canned	Beets	0.98000328952	0.0527108135739495
55	[null]	Pumpkin	1.36520483556	0.0182778987330547
56	Packed in syrup or water	Pears	1.600862219225	0.0413195778945488
57	Fresh green cabbage	Cabbage	0.601539811679	0.0315813933837907
58	Fresh	Blueberries	4.562865030315	0.242900597172589
59	[null]	Cauliflower	2.07371408459667	0.891157734515417
60	Florets	Broccoli	2.465463616465	0.145675052630875
61	[null]	Tangerines	1.429179024965	0.0724319849529066
62	Canned	Collard greens	0.878006425936	0.0347718297860748
63	Frozen	Green beans	1.68490348667	0.0220737713811638
64	Frozen	Corn	1.60863370153	0.0147631602577653
65	Frozen	Berries, mixed	3.52357772168	0.160319404842618
66	Frozen	Blueberries	3.557587933955	0.121145043767303
67	[null]	Green peppers	1.4526667429	0.0598270480860252
68	Raisins	Grapes	3.538290033495	0.0528567963134104
69	Fresh red cabbage	Cabbage	1.040287378205	0.0228570141526365
70	Fresh	Onions	1.042744484175	0.00655817614593457
71	[null]	Asparagus	3.91875275331833	1.60028272657361
72	Heads	Cauliflower	1.322891466615	0.133050075162805
73	[null]	Lima beans	1.732813669775	0.334917082981769
74	[null]	Blackberries	4.6292228275525	1.26436335040961
75	[null]	Red peppers	2.29930513481	0.0302152343301621
76	[null]	Cabbage	0.950230875646333	0.283166125287468
77	[null]	Bananas	0.5582003536655	0.0124211237951375
78	[null]	Apples	0.931633822915333	0.517021631041942
79	Canned	Pumpkin	1.36520483556	0.0182778987330547
80	Frozen	Turnip greens	1.526531879935	0.0756442566923829
81	Dried	Mangoes	9.33418092504	1.17313487217308
82	Frozen	Kale	1.89488719366	0.252665889924294
83	[null]	Collard greens	1.68150015598867	0.792548502206632
84	Fresh	Raspberries	6.92675656173	0.0693731674410974
85	[null]	Carrots	1.0756138602228	0.324753169649653
86	[null]	Watermelon	0.3250254072615	0.0118604831471543
87	Frozen	Artichoke	5.99487180028	0.320385171670741
88	Dried	Black beans	1.446830247515	0.0607460658812006
89	Fresh	Lettuce, Iceberg	1.151147487615	0.0875284236578059
90	[null]	Tomatoes	2.038903745427	1.10866992766456
91	Dried	Cranberries	4.73807260278	0.0688280414993992
92	Trimmed bunches	Celery	1.10324987289	0.0147135928643669
93	Fresh	Butternut squash	1.268373863485	0.0334279917035078
94	[null]	Okra	2.54088589968	1.15427620815233
95	Ready to drink	Pineapple	0.9407189526075	0.0439491232715298
96	Canned	Lima beans	1.35589094305	0.0391430589925351
97	Canned	Navy beans	0.958336989393	0.0170926458557113
98	[null]	Green peas	1.32602219999	0.376596935810242
99	Raw baby	Carrots	1.44238764128	0.00717063902494663
100	Frozen	Grapefruit	0.674839678618	nan

Rows: 1-100 | Columns: 4

In [3]:

# group by with all rollups
market.groupby(columns = ["Form", "Name"],
               expr = ["AVG(Price) AS avg_price",
                       "STDDEV(Price) AS std"],
               rollup = True)

Out[3]:

	Abc Form Varchar(32)	Abc Name Varchar(32)	123 avg_price Float	123 std Float
1	Frozen	Carrots	1.42854937688	0.0452030054447557
2	Fresh	Peaches	1.634559217475	0.061337785719176
3	Sticks	[null]	2.1695111646	0.0501455588369294
4	Dried	Figs	5.93684425835	0.266616901811729
5	Fresh	Tangerines	1.429179024965	0.0724319849529066
6	Canned	Kale	1.0686461381	nan
7	Dried (Prunes)	Plums	4.38513566562	0.493264261155465
8	Fresh	Raspberries	6.92675656173	0.0693731674410974
9	Frozen	Blueberries	3.557587933955	0.121145043767303
10	Juice, ready to drink	Grapefruit	0.8494909232	nan
11	Florets	Cauliflower	3.199310952275	0.10088643578742
12	Fresh, consumed with peel	[null]	1.27575847412	0.0285287803154144
13	Heads	Broccoli	1.777607017835	0.200125518705313
14	Fresh	Potatoes	0.5840279522645	0.0278715165231906
15	Fresh	Pomegranate	2.12601021818	0.0672885552026009
16	Beefsteak,	[null]	3.15921213872	nan
17	Canned	Green beans	0.8254899048585	0.00204944698175326
18	Fresh	Red peppers	2.29930513481	0.0302152343301621
19	Frozen	Green beans	1.68490348667	0.0220737713811638
20	Frozen	Grapes	0.728197301698	0.0115558279448051
21	Whole	[null]	3.480119338735	0.0963560092729786
22	Frozen	Raspberries	4.606165354625	0.221608231237791
23	Heads	Cauliflower	1.322891466615	0.133050075162805
24	Fresh	[null]	2.0580829673886	1.3097843628429
25	Fresh	Bananas	0.5582003536655	0.0124211237951375
26	Hearts	Lettuce, Romaine	2.653314584035	0.140980565011309
27	Frozen	[null]	2.19253958499914	1.4220758283626
28	Canned,	[null]	4.63647686809	0.635894274349174
29	Grape and cherry,	[null]	3.386044798935	0.137298351321261
30	Fresh	Pears	1.4895833073	0.0396102875056685
31	Fresh	Honeydew melon	0.811323709865	0.0207429836981466
32	Fresh green cabbage	[null]	0.601539811679	0.0315813933837907
33	Canned	Carrots	0.991840875075	0.0970849871638956
34	Frozen	Kale	1.89488719366	0.252665889924294
35	Fresh	Sweet potatoes	0.9866529454995	0.0958220140200086
36	Dried	Great northern beans	1.561605294915	0.0389991356591416
37	Canned, packed in syrup or water	Cherries	3.622330820065	0.146494766407389
38	Canned	Artichoke	3.351536603115	0.048187731643313
39	Raisins	Grapes	3.538290033495	0.0528567963134104
40	Fresh	Blueberries	4.562865030315	0.242900597172589
41	Canned	Black beans	0.9631303813775	0.0246770881594642
42	Frozen french fries	Potatoes	1.19722809107	0.0289791581345278
43	Dried (Prunes)	[null]	4.38513566562	0.493264261155465
44	Sauerkraut	[null]	1.208865437055	0.0788702025476506
45	Fresh	Green peppers	1.4526667429	0.0598270480860252
46	Canned	Pinto beans	0.833792418299	0.0458166809610275
47	Ready to drink	Apples	0.6792101204525	0.0679919835754149
48	Fresh	Papaya	1.29577168415	0.00316770410008883
49	Full Heads	Lettuce, Romaine	1.660066592085	0.248946451608926
50	Roma,	[null]	1.24341736034	nan
51	Dried	Papaya	4.919086238285	0.499463034879057
52	Juice, ready to drink	Pomegranate	2.976529524805	0.0477218128757649
53	Juice, ready to drink	[null]	1.880649400672	1.02261447851835
54	[null]	[null]	nan	nan
55	Fresh, peeled	Cucumbers	1.27575847412	0.0285287803154144
56	Fresh	Turnip greens	2.490080924435	0.0259255090642032
57	Frozen	Lima beans	1.86745377496	0.0448718757678454
58	Fresh	Apricots	3.06360489205	0.0332805816266319
59	Frozen	Turnip greens	1.526531879935	0.0756442566923829
60	Grape and cherry,	Tomatoes	3.386044798935	0.137298351321261
61	Frozen french fries	[null]	1.19722809107	0.0289791581345278
62	Raw	Spinach	3.830154965845	0.0050356546423244
63	Packed in juice	Fruit cocktail	1.449519300285	0.0574655341542805
64	Canned,	Olives	4.63647686809	0.635894274349174
65	Canned	Green peas	1.00004151767	0.0184238996381032
66	Canned	Tomatoes	0.918856627443	0.00845661730602896
67	Ready to drink	Pineapple	0.9407189526075	0.0439491232715298
68	Sliced	Mushrooms	3.81600451483	0.00789112051889268
69	Canned	Navy beans	0.958336989393	0.0170926458557113
70	Canned	Blackeye peas	0.920740347961	0.0145657414311878
71	Frozen	Strawberries	2.49470111866	0.165304332740037
72	Raw baby	Carrots	1.44238764128	0.00717063902494663
73	Fresh	Summer squash	1.639761267255	0.000401969338397509
74	Fresh	Kale	2.8422960573	0.049489387502778
75	Dried	Lima beans	1.975096291315	0.345691279858497
76	Dried	Red Kidney Beans	1.679200598705	0.0103403185904881
77	Fresh	Brussels sprouts	2.862823175555	0.140388703214564
78	Dried	Blackeye peas	1.60146164309	0.0163494272967093
79	Frozen	Collard greens	1.536340901805	0.0788916229899441
80	Hearts	[null]	2.653314584035	0.140980565011309
81	Canned	[null]	1.1649022634076	0.595638890885002
82	Packed in syrup or water	Pineapple	1.24344553067	0.162630450717828
83	Dried	Apricots	7.532468311145	0.284969376912029
84	Packed in syrup or water	Peaches	1.546235219665	0.0566428888827602
85	Ready to drink	Grapes	0.879555994869	0.0480536917097328
86	Packed in juice	[null]	1.592047339472	0.276362085872738
87	Packed in syrup or water	[null]	1.41017352542375	0.188283136462026
88	Packed in juice	Peaches	1.922325988955	0.0597938250345314
89	Fresh red cabbage	Cabbage	1.040287378205	0.0228570141526365
90	Fresh	Green beans	2.133124356205	0.00968438768679218
91	Frozen	Spinach	1.83156678291	0.100908896376676
92	Fresh	Okra	3.51718827745	0.429406103192065
93	Fresh	Mustard greens	2.624251782445	0.0778054568543142
94	Fresh	Plums	1.907707425905	0.113549212412115
95	Frozen	Pineapple	0.6055659651125	0.0209496044750691
96	Canned	Pumpkin	1.36520483556	0.0182778987330547
97	Fresh	Pineapple	0.640228246668	0.0177714329240488
98	Packed in syrup, syrup discarded	[null]	1.70366667044	0.214428912851844
99	Raw whole	[null]	0.7576457039395	0.0227769157205302
100	Fresh	Lettuce, Iceberg	1.151147487615	0.0875284236578059

Rows: 1-100 | Columns: 4

In [4]:

# group by with having clause
market.groupby(columns = ["Form", "Name"],
               expr = ["AVG(Price) AS avg_price",
                       "STDDEV(Price) AS std"],
               having = "AVG(Price) > 2")

Out[4]:

	Abc Form Varchar(32)	Abc Name Varchar(32)	123 avg_price Float	123 std Float
1	Beefsteak,	Tomatoes	3.15921213872	nan
2	Boiled	Spinach	3.830154965845	0.0050356546423244
3	Canned	Artichoke	3.351536603115	0.048187731643313
4	Canned	Asparagus	2.6501529235	0.0987909564136379
5	Canned,	Olives	4.63647686809	0.635894274349174
6	Canned, packed in syrup or water	Cherries	3.622330820065	0.146494766407389
7	Dried	Apricots	7.532468311145	0.284969376912029
8	Dried	Cranberries	4.73807260278	0.0688280414993992
9	Dried	Dates	5.151528052295	0.509366657325232
10	Dried	Figs	5.93684425835	0.266616901811729
11	Dried	Mangoes	9.33418092504	1.17313487217308
12	Dried	Papaya	4.919086238285	0.499463034879057
13	Dried	Pineapple	5.682923578935	0.262813535677252
14	Dried (Prunes)	Plums	4.38513566562	0.493264261155465
15	Florets	Broccoli	2.465463616465	0.145675052630875
16	Florets	Cauliflower	3.199310952275	0.10088643578742
17	Fresh	Apricots	3.06360489205	0.0332805816266319
18	Fresh	Artichoke	2.288391930345	0.106548901890849
19	Fresh	Asparagus	3.14455233272	0.0974983724584246
20	Fresh	Avocado	2.23070922879	0.00730462912737973
21	Fresh	Blackberries	5.718189793175	0.0799291686612887
22	Fresh	Blueberries	4.562865030315	0.242900597172589
23	Fresh	Brussels sprouts	2.862823175555	0.140388703214564
24	Fresh	Cherries	3.403028933095	0.26864517135055
25	Fresh	Collard greens	2.630153140225	0.000968403844114992
26	Fresh	Corn	2.977775549065	0.406094804455345
27	Fresh	Grapes	2.16485030425	0.100441537458926
28	Fresh	Green beans	2.133124356205	0.00968438768679218
29	Fresh	Kale	2.8422960573	0.049489387502778
30	Fresh	Kiwi	2.113005543435	0.0966220905848999
31	Fresh	Mustard greens	2.624251782445	0.0778054568543142
32	Fresh	Okra	3.51718827745	0.429406103192065
33	Fresh	Pomegranate	2.12601021818	0.0672885552026009
34	Fresh	Raspberries	6.92675656173	0.0693731674410974
35	Fresh	Red peppers	2.29930513481	0.0302152343301621
36	Fresh	Strawberries	2.433629063505	0.105812279539221
37	Fresh	Turnip greens	2.490080924435	0.0259255090642032
38	Frozen	Artichoke	5.99487180028	0.320385171670741
39	Frozen	Asparagus	5.961553003735	0.147407849193659
40	Frozen	Berries, mixed	3.52357772168	0.160319404842618
41	Frozen	Blackberries	3.54025586193	0.214613990004942
42	Frozen	Blueberries	3.557587933955	0.121145043767303
43	Frozen	Brussels sprouts	2.08899473384	0.0696626501832463
44	Frozen	Peaches	3.04085898263	0.206788714039254
45	Frozen	Raspberries	4.606165354625	0.221608231237791
46	Frozen	Strawberries	2.49470111866	0.165304332740037
47	Grape and cherry,	Tomatoes	3.386044798935	0.137298351321261
48	Hearts	Lettuce, Romaine	2.653314584035	0.140980565011309
49	Juice, ready to drink	Pomegranate	2.976529524805	0.0477218128757649
50	Large round,	Tomatoes	2.0066199543	nan
51	Raisins	Grapes	3.538290033495	0.0528567963134104
52	Raw	Spinach	3.830154965845	0.0050356546423244
53	Sliced	Mushrooms	3.81600451483	0.00789112051889268
54	Sticks	Celery	2.1695111646	0.0501455588369294
55	Whole	Mushrooms	3.480119338735	0.0963560092729786

Rows: 1-55 | Columns: 4

In [12]:

# loading the amazon dataset
from verticapy.datasets import load_amazon
amazon = load_amazon()
display(amazon)

# customized SQL selection
amazon.groupby(columns = ["MONTH(date) AS month"],
               expr = ["AVG(number) AS avg_number"],)

	📅 date Date	Abc state Varchar(32)	123 number Int
1	1998-01-01	ACRE	0
2	1998-01-01	ALAGOAS	0
3	1998-01-01	AMAPÁ	0
4	1998-01-01	AMAZONAS	0
5	1998-01-01	BAHIA	0
6	1998-01-01	CEARÁ	0
7	1998-01-01	DISTRITO FEDERAL	0
8	1998-01-01	ESPÍRITO SANTO	0
9	1998-01-01	GOIÁS	0
10	1998-01-01	MARANHÃO	0
11	1998-01-01	MATO GROSSO	0
12	1998-01-01	MATO GROSSO DO SUL	0
13	1998-01-01	MINAS GERAIS	0
14	1998-01-01	PARANÁ	0
15	1998-01-01	PARAÍBA	0
16	1998-01-01	PARÁ	0
17	1998-01-01	PERNAMBUCO	0
18	1998-01-01	PIAUÍ	0
19	1998-01-01	RIO DE JANEIRO	0
20	1998-01-01	RIO GRANDE DO NORTE	0
21	1998-01-01	RIO GRANDE DO SUL	0
22	1998-01-01	RONDÔNIA	0
23	1998-01-01	RORAIMA	0
24	1998-01-01	SANTA CATARINA	0
25	1998-01-01	SERGIPE	0
26	1998-01-01	SÃO PAULO	0
27	1998-01-01	TOCANTINS	0
28	1998-02-01	ACRE	0
29	1998-02-01	ALAGOAS	0
30	1998-02-01	AMAPÁ	0
31	1998-02-01	AMAZONAS	0
32	1998-02-01	BAHIA	0
33	1998-02-01	CEARÁ	0
34	1998-02-01	DISTRITO FEDERAL	0
35	1998-02-01	ESPÍRITO SANTO	0
36	1998-02-01	GOIÁS	0
37	1998-02-01	MARANHÃO	0
38	1998-02-01	MATO GROSSO	0
39	1998-02-01	MATO GROSSO DO SUL	0
40	1998-02-01	MINAS GERAIS	0
41	1998-02-01	PARANÁ	0
42	1998-02-01	PARAÍBA	0
43	1998-02-01	PARÁ	0
44	1998-02-01	PERNAMBUCO	0
45	1998-02-01	PIAUÍ	0
46	1998-02-01	RIO DE JANEIRO	0
47	1998-02-01	RIO GRANDE DO NORTE	0
48	1998-02-01	RIO GRANDE DO SUL	0
49	1998-02-01	RONDÔNIA	0
50	1998-02-01	RORAIMA	0
51	1998-02-01	SANTA CATARINA	0
52	1998-02-01	SERGIPE	0
53	1998-02-01	SÃO PAULO	0
54	1998-02-01	TOCANTINS	0
55	1998-03-01	ACRE	0
56	1998-03-01	ALAGOAS	0
57	1998-03-01	AMAPÁ	0
58	1998-03-01	AMAZONAS	0
59	1998-03-01	BAHIA	0
60	1998-03-01	CEARÁ	0
61	1998-03-01	DISTRITO FEDERAL	0
62	1998-03-01	ESPÍRITO SANTO	0
63	1998-03-01	GOIÁS	0
64	1998-03-01	MARANHÃO	0
65	1998-03-01	MATO GROSSO	0
66	1998-03-01	MATO GROSSO DO SUL	0
67	1998-03-01	MINAS GERAIS	0
68	1998-03-01	PARANÁ	0
69	1998-03-01	PARAÍBA	0
70	1998-03-01	PARÁ	0
71	1998-03-01	PERNAMBUCO	0
72	1998-03-01	PIAUÍ	0
73	1998-03-01	RIO DE JANEIRO	0
74	1998-03-01	RIO GRANDE DO NORTE	0
75	1998-03-01	RIO GRANDE DO SUL	0
76	1998-03-01	RONDÔNIA	0
77	1998-03-01	RORAIMA	0
78	1998-03-01	SANTA CATARINA	0
79	1998-03-01	SERGIPE	0
80	1998-03-01	SÃO PAULO	0
81	1998-03-01	TOCANTINS	0
82	1998-04-01	ACRE	0
83	1998-04-01	ALAGOAS	0
84	1998-04-01	AMAPÁ	0
85	1998-04-01	AMAZONAS	0
86	1998-04-01	BAHIA	0
87	1998-04-01	CEARÁ	0
88	1998-04-01	DISTRITO FEDERAL	0
89	1998-04-01	ESPÍRITO SANTO	0
90	1998-04-01	GOIÁS	0
91	1998-04-01	MARANHÃO	0
92	1998-04-01	MATO GROSSO	0
93	1998-04-01	MATO GROSSO DO SUL	0
94	1998-04-01	MINAS GERAIS	0
95	1998-04-01	PARANÁ	0
96	1998-04-01	PARAÍBA	0
97	1998-04-01	PARÁ	0
98	1998-04-01	PERNAMBUCO	0
99	1998-04-01	PIAUÍ	0
100	1998-04-01	RIO DE JANEIRO	0

Rows: 1-100 | Columns: 3

Out[12]:

	123 month Int	123 avg_number Float
1	1	97.2033271719039
2	2	59.0685185185185
3	3	65.0333333333333
4	4	55.4759259259259
5	5	85.3388888888889
6	6	210.422222222222
7	7	417.233333333333
8	8	1466.26111111111
9	9	2132.94259259259
10	10	1243.8462962963
11	11	622.798148148148
12	12	327.300194931774

Rows: 1-12 | Columns: 2

vDataFrame.append	Merges the vDataFrame with another relation.
vDataFrame.analytic	Adds a new vcolumn to the vDataFrame by using an advanced analytical function on a specific vcolumn.
vDataFrame.join	Joins the vDataFrame with another relation.
vDataFrame.sort	Sorts the vDataFrame.

vDataFrame.groupby¶

Parameters¶

Returns¶

Example¶

See Also¶