verticapy.vDataFrame.pivot#

vDataFrame.pivot(index: str, columns: str, values: str, aggr: str = 'sum', prefix: str | None = None) → vDataFrame#

Returns the Pivot of the vDataFrame using the input aggregation.

Parameters#

index: str: vDataColumn used to group the elements.
columns: str: The vDataColumn used to compute the different categories, which then act as the columns in the pivot table.
values: str: The vDataColumn whose values populate the new vDataFrame.
aggr: str, optional: Aggregation to use on ‘values’. To use complex aggregations, you must use braces: {}. For example, to aggregate using the aggregation: x -> MAX(x) - MIN(x), write MAX({}) - MIN({}).
prefix: str, optional: The prefix for the pivot table’s column names.

Returns#

vDataFrame: the pivot table object.

Examples#

Let’s begin by importing VerticaPy.

import verticapy as vp

Hint

By assigning an alias to verticapy, we mitigate the risk of code collisions with other libraries. This precaution is necessary because verticapy uses commonly known function names like “average” and “median”, which can potentially lead to naming conflicts. The use of an alias ensures that the functions from verticapy are used as intended without interfering with functions from other libraries.

For this example, let’s generate a dummy dataset representing sales of two items for different dates:

vdf = vp.vDataFrame(
    {
        "date": [
            "2014-01-01",
            "2014-01-02",
            "2014-01-01",
            "2014-01-02",
        ],
        "cat": ["A", "A", "B", "B"],
        "sale": [100, 120, 120, 110],
    }
)

	Abc date Varchar(10) 100%	...	Abc cat Varchar(1) 100%	123 sale Integer 100%
1	2014-01-01	...	A	100
2	2014-01-02	...	A	120
3	2014-01-01	...	B	120
4	2014-01-02	...	B	110

To better view the data, we can create a pivot table:

vdf.pivot(
    index = "date",
    columns = "cat",
    values = "sale",
    aggr = "avg",
)

	Abc date Varchar(10) 100%	...	123 A Float(22) 100%	123 B Float(22) 100%
1	2014-01-02	...	120.0	110.0
2	2014-01-01	...	100.0	120.0

Note

The inverse function of pivot is narrow. With both, you can preprocess the table either vertically or horizontally. These functions utilize pure SQL statements to perform the job.