Feedback

×

• Feedback
• Was this page helpful?*
  • Yes
  • No
• Do you have any additional feedback?
  • Yes
  • No
• Additional Feedback

Features Engineering

While preparing our data, we need to think constantly about the most suitable features we can use to achieve our overall goals.

Features engineering makes use of many techniques - too many to go over in this short lesson. We’ll focus on the most popular ones.

Customized Features Engineering

To build a customized feature, you can use the eval() method of the vDataFrame. Let’s look at an example with the well-known titanic dataset.

import verticapy as vp
from verticapy.datasets import load_titanic

titanic = load_titanic()
titanic.head(100)

	123 pclass Int 100%	...	123 body Int 9%	Abc Varchar(100) 57%
1	1	...	22
2	1	...	[null]
3	1	...	[null]
4	1	...	[null]
5	1	...	[null]
6	1	...	[null]
7	1	...	[null]
8	1	...	62
9	1	...	[null]
10	1	...	[null]
11	1	...	[null]
12	1	...	[null]
13	1	...	110
14	1	...	[null]
15	1	...	[null]
16	1	...	38
17	1	...	[null]
18	1	...	126
19	1	...	292
20	1	...	175
21	1	...	[null]
22	1	...	122
23	1	...	166
24	1	...	[null]
25	1	...	207
26	1	...	[null]
27	1	...	232
28	1	...	[null]
29	1	...	[null]
30	1	...	[null]
31	1	...	[null]
32	1	...	[null]
33	1	...	46
34	1	...	169
35	1	...	[null]
36	1	...	[null]
37	1	...	[null]
38	1	...	[null]
39	1	...	[null]
40	1	...	[null]
41	1	...	[null]
42	1	...	[null]
43	1	...	[null]
44	1	...	[null]
45	1	...	[null]
46	1	...	[null]
47	1	...	[null]
48	1	...	[null]
49	1	...	[null]
50	1	...	[null]
51	1	...	[null]
52	1	...	[null]
53	1	...	[null]
54	1	...	[null]
55	1	...	[null]
56	1	...	[null]
57	1	...	[null]
58	1	...	[null]
59	1	...	[null]
60	1	...	[null]
61	1	...	[null]
62	1	...	[null]
63	1	...	[null]
64	1	...	[null]
65	1	...	[null]
66	1	...	[null]
67	1	...	[null]
68	1	...	[null]
69	1	...	[null]
70	1	...	[null]
71	1	...	[null]
72	1	...	[null]
73	1	...	[null]
74	2	...	[null]
75	2	...	[null]
76	2	...	[null]
77	2	...	[null]
78	2	...	[null]
79	2	...	[null]
80	2	...	[null]
81	2	...	[null]
82	2	...	[null]
83	2	...	236
84	2	...	[null]
85	2	...	[null]
86	2	...	[null]
87	2	...	155
88	2	...	[null]
89	2	...	75
90	2	...	35
91	2	...	[null]
92	2	...	[null]
93	2	...	[null]
94	2	...	[null]
95	2	...	[null]
96	2	...	[null]
97	2	...	[null]
98	2	...	165
99	2	...	[null]
100	2	...	[null]

The feature parch corresponds to the number of parents and children on-board. The feature sibsp corresponds to the number of siblings and spouses on-board. We can create the feature family_size which is equal to parch + sibsp + 1.

titanic["family_size"] = titanic["parch"] + titanic["sibsp"] + 1
titanic.select(["parch", "sibsp", "family_size"])

	123 parch Integer 100%	...	123 sibsp Integer 100%	123 family_size Integer 100%
1	0	...	0	1
2	0	...	0	1
3	0	...	0	1
4	0	...	0	1
5	0	...	1	2
6	1	...	1	3
7	0	...	1	2
8	0	...	1	2
9	0	...	0	1
10	0	...	1	2
11	0	...	0	1
12	0	...	1	2
13	0	...	0	1
14	0	...	0	1
15	0	...	0	1
16	0	...	1	2
17	0	...	0	1
18	0	...	0	1
19	0	...	0	1
20	0	...	0	1

When using the eval() method, you can enter any SQL expression and VerticaPy will evaluate it!

Regular Expressions

To compute features using regular expressions, we’ll use the regexp() method.

help(vp.vDataFrame.regexp)
Help on function regexp in module verticapy.core.vdataframe._text:

regexp(self, column: str, pattern: str, method: Literal['count', 'ilike', 'instr', 'like', 'not_ilike', 'not_like', 'replace', 'substr'] = 'substr', position: int = 1, occurrence: int = 1, replacement: Optional[str] = None, return_position: int = 0, name: Optional[str] = None) -> 'vDataFrame'

Computes a new vDataColumn based on regular expressions.

Parameters
----------
column: str
    Input vDataColumn  used  to compute the  regular
    expression.
pattern: str
    The regular expression.
method: str, optional
    Method used to compute the regular  expressions.

         - count:
            Returns the number of times a
            regular expression matches each
            element of the input vDataColumn.
         - ilike:
            Returns  True if  the  vDataColumn
            element  contains a match for  the
            regular expression.
         - instr:
            Returns  the  starting  or  ending
            position in  a vDataColumn element
            where a regular expression matches.
         - like:
            Returns  True  if the  vDataColumn
            element    matches   the   regular
            expression.
         - not_ilike :
            Returns  True  if the  vDataColumn
            element  does  not match the  case
            -insensitive  regular   expression.
         - not_like:
            Returns  True if  the  vDataColumn
            element  does not contain a  match
            for the regular expression.
         - replace:
            Replaces   all  occurrences  of  a
            substring  that  match  a  regular
            expression  with another substring.
         - substr:
            Returns the substring that matches
            a  regular   expression  within  a
            vDataColumn.

position: int, optional
    The number of characters from the start of the string
    where the function should start searching for matches.
occurrence: int, optional
    Controls  which occurrence of a pattern match in  the
    string to return.
replacement: str, optional
    The string to replace matched substrings.
return_position: int, optional
    Sets the position within the string to return.
name: str, optional
    New feature name. If empty, a name is generated.

Returns
-------
vDataFrame
    self

Consider the following example: notice that passenger names include their title.

titanic["name"]

	Abc name Varchar(164)
1	Artagaveytia, Mr. Ramon
2	Blackwell, Mr. Stephen Weart
3	Cairns, Mr. Alexander
4	Carrau, Mr. Jose Pedro
5	Clark, Mr. Walter Miller
6	Compton, Mr. Alexander Taylor Jr
7	Davidson, Mr. Thornton
8	Douglas, Mr. Walter Donald
9	Evans, Miss. Edith Corse
10	Futrelle, Mr. Jacques Heath
11	Giglio, Mr. Victor
12	Harris, Mr. Henry Birkhardt
13	Harrison, Mr. William
14	Head, Mr. Christopher
15	Hilliard, Mr. Herbert Henry
16	Holverson, Mr. Alexander Oskar
17	Hoyt, Mr. William Fisher
18	Long, Mr. Milton Clyde
19	McCaffry, Mr. Thomas Francis
20	McCarthy, Mr. Timothy J

Let’s extract the title using regular expressions.

titanic.regexp(
    column = "name",
    name = "title",
    pattern = " ([A-Za-z])+\.",
    method = "substr",
)
titanic.select(["name", "title"])

	Abc name Varchar(164) 100%	Abc title Varchar(164) 100%
1	Artagaveytia, Mr. Ramon	Mr.
2	Blackwell, Mr. Stephen Weart	Mr.
3	Cairns, Mr. Alexander	Mr.
4	Carrau, Mr. Jose Pedro	Mr.
5	Clark, Mr. Walter Miller	Mr.
6	Compton, Mr. Alexander Taylor Jr	Mr.
7	Davidson, Mr. Thornton	Mr.
8	Douglas, Mr. Walter Donald	Mr.
9	Evans, Miss. Edith Corse	Miss.
10	Futrelle, Mr. Jacques Heath	Mr.
11	Giglio, Mr. Victor	Mr.
12	Harris, Mr. Henry Birkhardt	Mr.
13	Harrison, Mr. William	Mr.
14	Head, Mr. Christopher	Mr.
15	Hilliard, Mr. Herbert Henry	Mr.
16	Holverson, Mr. Alexander Oskar	Mr.
17	Hoyt, Mr. William Fisher	Mr.
18	Long, Mr. Milton Clyde	Mr.
19	McCaffry, Mr. Thomas Francis	Mr.
20	McCarthy, Mr. Timothy J	Mr.

Advanced Analytical Functions

The analytic() method contains the many advanced analytical functions in VerticaPy.

help(vp.vDataFrame.analytic)
Help on function analytic in module verticapy.core.vdataframe._math:

analytic(self, func: str, columns: Optional[Annotated[Union[str, list[str]], 'STRING representing one column or a list of columns']] = None, by: Optional[Annotated[Union[str, list[str]], 'STRING representing one column or a list of columns']] = None, order_by: Union[NoneType, Annotated[Union[str, list[str]], 'STRING representing one column or a list of columns'], dict] = None, name: Optional[str] = None, offset: int = 1, x_smoothing: float = 0.5, add_count: bool = True) -> 'vDataFrame'

Adds a new vDataColumn to the vDataFrame by using an advanced
analytical function on one or two specific vDataColumns.

.. warning::

    Some analytical  functions can make the vDataFrame
    structure  more resource intensive. It is best  to
    check  the structure of  the vDataFrame with  the
    ``current_relation`` method and save it with the
    ``to_db``  method,  uisng  the  parameters
    ``inplace = True`` and ``relation_type = table``.

Parameters
----------
func: str
    Function to apply.

    - aad:
        average absolute deviation
    - beta:
        Beta Coefficient between 2 vDataColumns
    - count:
        number of non-missing elements
    - corr:
        Pearson's correlation between 2 vDataColumns
    - cov:
        covariance between 2 vDataColumns
    - dense_rank:
        dense rank
    - ema:
        exponential moving average
    - first_value:
        first non null lead
    - iqr:
        interquartile range
    - kurtosis:
        kurtosis
    - jb:
        Jarque-Bera index
    - lead:
        next element
    - lag:
        previous element
    - last_value:
        first non null lag
    - mad:
        median absolute deviation
    - max:
        maximum
    - mean:
        average
    - median :
        median
    - min:
        minimum
    - mode:
        most occurent element
    - q%:
        q quantile (ex: 50% for the median)
    - pct_change:
        ratio between the current value and the previous one
    - percent_rank :
        percent rank
    - prod:
        product
    - range:
        difference between the max and the min
    - rank:
        rank
    - row_number:
        row number
    - sem:
        standard error of the mean
    - skewness:
        skewness
    - sum:
        sum
    - std:
        standard deviation
    - unique:
        cardinality (count distinct)
    - var:
        variance

    Other analytical functions could work if they are part of your DB
    version.
columns: SQLColumns, optional
    Input vDataColumns. Must be a list of one or two elements.
by: SQLColumns, optional
    vDataColumns used in the partition.
order_by: dict / list, optional
    Either a list of the vDataColumns used to sort (in ascending order)
    the data, or a dictionary of vDataColumns and their sorting
    methods. For example, to sort by "column1" ASC and "column2" DESC,
    write: ``{"column1": "asc", "column2": "desc"}``
name: str, optional
    Name of  the new vDataColumn. If empty, a default name based on the
    other parameters is generated.
offset: int, optional
    Lead/Lag  offset  if parameter ``func`` is the function  'lead'/'lag'.
x_smoothing: float, optional
    The  smoothing parameter of the 'ema' if the  function is 'ema'. It
    must be a float in the range [0;1].
add_count: bool, optional
    If the ``func`` is set to ``mode`` and this parameter is True, a column
    with the mode number of occurences is added to the vDataFrame.

Returns
-------
vDataFrame
    self

To demonstrate some of these techniques, let’s use the amazon dataset and perform some computations.

from verticapy.datasets import load_amazon

amazon = load_amazon()
amazon.head(100)

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

For each state, let’s compute the previous number of forest fires.

amazon.analytic(
    name = "previous_number",
    func = "lag",
    columns = "number",
    by = "state",
    order_by = {"date": "asc"},
)

	📅 date Date 100%	...	Abc state Varchar(32) 100%	123 previous_number Integer 99%
1	1998-01-01	...	TOCANTINS	[null]
2	1998-02-01	...	TOCANTINS	0
3	1998-03-01	...	TOCANTINS	0
4	1998-04-01	...	TOCANTINS	0
5	1998-05-01	...	TOCANTINS	0
6	1998-06-01	...	TOCANTINS	0
7	1998-07-01	...	TOCANTINS	252
8	1998-08-01	...	TOCANTINS	640
9	1998-09-01	...	TOCANTINS	3747
10	1998-10-01	...	TOCANTINS	5149
11	1998-11-01	...	TOCANTINS	1738
12	1998-12-01	...	TOCANTINS	1
13	1999-01-01	...	TOCANTINS	9
14	1999-02-01	...	TOCANTINS	36
15	1999-03-01	...	TOCANTINS	1
16	1999-04-01	...	TOCANTINS	1
17	1999-05-01	...	TOCANTINS	9
18	1999-06-01	...	TOCANTINS	24
19	1999-07-01	...	TOCANTINS	113
20	1999-08-01	...	TOCANTINS	373

Moving Windows

Moving windows are powerful features. Moving windows are managed by the rolling() method in VerticaPy.

help(vp.vDataFrame.rolling)
Help on function rolling in module verticapy.core.vdataframe._rolling:

rolling(self, func: str, window: Union[list, tuple], columns: Annotated[Union[str, list[str]], 'STRING representing one column or a list of columns'], by: Optional[Annotated[Union[str, list[str]], 'STRING representing one column or a list of columns']] = None, order_by: Union[NoneType, dict, list] = None, name: Optional[str] = None) -> 'vDataFrame'

Adds a new :py:class:`~vDataColumn` to the
:py:class:`~vDataFrame` by using an advanced
analytical  window function on one or two
specific :py:class:`~vDataColumn`.

.. warning::

    Some window functions can make the
    vDataFrame structure heavier. It is
    recommended to always check the current
    structure with the ``current_relation``
    method and to save it with the ``to_db``
    method, using the parameters ``inplace
    = True`` and ``relation_type = table``.

.. warning::

    Make use of the ``order_by`` parameter to sort
    your data. Otherwise, you might encounter unexpected
    results, as Vertica does not work with indexes, and
    the data may be randomly shuffled.

Parameters
----------
func: str
    Function to use.

    - aad:
        average absolute deviation
    - beta:
        Beta Coefficient between 2 vDataColumns
    - count:
        number of non-missing elements
    - corr:
        Pearson correlation between 2 vDataColumns
    - cov:
        covariance between 2 vDataColumns
    - kurtosis:
        kurtosis
    - jb:
        Jarque-Bera index
    - max:
        maximum
    - mean:
        average
    - min:
        minimum
    - prod:
        product
    - range:
        difference between the max and the min
    - sem:
        standard error of the mean
    - skewness:
        skewness
    - sum:
        sum
    - std:
        standard deviation
    - var:
        variance

    Other window functions could work if it is part of
    the DB version you are using.

window: list | tuple
    Window Frame Range.
    If set to two integers, computes a Row Window, otherwise
    it computes a Time  Window. For example, if set  to
    ``(-5, 1)``,  the moving  windows will take 5 rows  preceding
    and one following. If set to ``('- 5 minutes', '0 minutes')``,
    the  moving window  will take all elements of the last  5
    minutes.
columns: SQLColumns
    Input :py:class:`~vDataColumn`. Must be a list of one
    or two elements.
by: SQLColumns, optional
    vDataColumns used in the partition.
order_by: dict | list, optional
    List of the vDataColumns used to sort the data using
    ascending/descending order or a dictionary of all the
    sorting methods.
    For example, to sort by "column1" ASC and "column2" DESC,
    use: ``{"column1": "asc", "column2": "desc"}``.
name: str, optional
    Name of the new :py:class:`~vDataColumn`. If empty, a
    default name is generated.

Returns
-------
vDataFrame
    self

Let’s look at forest fires for each state three months preceding two months following the examined period.

amazon.rolling(
    name = "number_3mp_2mf",
    func = "sum",
    window = ("- 3 months", "2 months"),
    columns = "number",
    by = "state",
    order_by = {"date": "asc"},
)

	📅 date Date 100%	...	123 previous_number Integer 99%	123 number_3mp_2mf Integer 100%
1	1998-01-01	...	[null]	0
2	1998-02-01	...	0	0
3	1998-03-01	...	0	0
4	1998-04-01	...	0	0
5	1998-05-01	...	0	46
6	1998-06-01	...	0	235
7	1998-07-01	...	46	946
8	1998-08-01	...	189	2937
9	1998-09-01	...	711	5477
10	1998-10-01	...	1991	5712
11	1998-11-01	...	2586	5120
12	1998-12-01	...	424	3150
13	1999-01-01	...	119	590
14	1999-02-01	...	21	176
15	1999-03-01	...	22	176
16	1999-04-01	...	4	65
17	1999-05-01	...	10	91
18	1999-06-01	...	8	188
19	1999-07-01	...	47	677
20	1999-08-01	...	123	1541

Moving windows give us infinite possibilities for creating new features.

After we’ve finished preparing our data, our next task is to create a machine learning model.