Skip to content

    Chapter 11, Basic

    New columns

    Analyzing is not just reading columns, it's creating information from them. In machine learning, this is called *feature engineering*: taking the existing columns and transforming them into the ones you need.

    The starting point: clean data

    From now on, each chapter starts with the same function, which brings together what chapter 10 taught. It returns the table with the correct types, standardized departments, and without repetitions, but still with the missing ones (filling is a decision that each analysis makes):

    basico/cap11_novas_colunas.pylinhas 10 a 29
    import numpy as np
import pandas as pd

pd.set_option("display.width", 170)
pd.set_option("display.max_columns", 20)

CANONICO = {"engenharia": "Engenharia", "marketing": "Marketing", "finanças": "Finanças",
            "ciência de dados": "Ciência de Dados", "rh": "RH"}


def carregar_limpo() -> pd.DataFrame:
    df = pd.read_csv("dados/funcionarios_15.csv")
    df["salario"] = pd.to_numeric(df["salario"].str.replace("R$ ", "", regex=False))
    df["data_admissao"] = pd.to_datetime(df["data_admissao"])
    df["departamento"] = df["departamento"].str.strip().str.lower().map(CANONICO)
    return df.drop_duplicates(subset=["id_funcionario"], keep="first").reset_index(drop=True)


df = carregar_limpo()
print(df.shape, df.dtypes["salario"], df.dtypes["data_admissao"])
    Output
    (14, 10) float64 datetime64[us]

    Create and modify columns with arithmetic

    The calculations apply to the column integer, as in NumPy, and the NaN passes through without error. The assign creates columns in a newtable, and it is the form that fits with chaining (chapter 30):

    basico/cap11_novas_colunas.pylinhas 34 a 38
    df["salario_anual"] = df["salario"] * 12
nova = df.assign(bonus=(df["salario"] * df["nota_desempenho"] * 0.02).round(2))
print(df[["nome", "salario", "salario_anual"]].head(4))
print(nova[["nome", "bonus"]].head(4))
print("bonus" in df.columns, "bonus" in nova.columns)
    Output
               nome  salario  salario_anual
0     Ana Souza   4500.0        54000.0
1    Bruno Lima   4650.0        55800.0
2  Carla Mendes   4800.0        57600.0
3   Diego Rocha      NaN            NaN
           nome  bonus
0     Ana Souza  360.0
1    Bruno Lima  465.0
2  Carla Mendes  576.0
3   Diego Rocha    NaN
False True

    Employees without salary remain without annual salary and without bonus (NaN), because a calculation with a missing value is missing. The assign are not altered the df original.

    `apply`, `lambda`, and `map`

    When the rule is too specific for a calculation, the apply runs a function for each value of a column. Here, the performance classification:

    basico/cap11_novas_colunas.pylinhas 43 a 53
    def categoria_desempenho(nota):
    if nota >= 8:
        return "Excelente"
    elif nota >= 6:
        return "Bom"
    else:
        return "Precisa melhorar"


df["categoria"] = df["nota_desempenho"].apply(categoria_desempenho)
print(df["categoria"].value_counts().to_dict())
    Output
    {'Precisa melhorar': 6, 'Bom': 4, 'Excelente': 4}

    A lambda is a small function, written in place, for one-line rules. And the map with a dictionary is the tool for replacing values:

    basico/cap11_novas_colunas.pylinhas 55 a 58
    df["faixa_salarial"] = df["salario"].apply(lambda s: "Alta" if s > 6000 else "Normal")
codigos = {"Engenharia": "ENG", "Marketing": "MKT", "Finanças": "FIN"}
df["codigo_depto"] = df["departamento"].map(codigos)
print(df[["nome", "faixa_salarial", "codigo_depto"]].head(5))
    Output
                  nome faixa_salarial codigo_depto
0        Ana Souza         Normal          ENG
1       Bruno Lima         Normal          MKT
2     Carla Mendes         Normal          ENG
3      Diego Rocha         Normal          NaN
4  Elisa Fernandes         Normal          NaN

    The rule for choosing: the map is used to replace value with value (a dictionary), and the apply, for a logic that needs a real function, with decisions. Notice two things. Those not in the dictionary (Data Science, HR, the missing) became NaN. And Diego, who has no salary, was classified as Normal: the apply received a NaN, the comparison NaN > 6000 returned false, and the missing fell into the "else" rule. The function does not know what a missing data is, and those who classify need to decide this explicitly.

    The error that doesn't give an error: the function without `return`

    A function used in apply that calculates, but does not return anything does not generate an error: the new column simply fills with None.

    basico/cap11_novas_colunas.pylinhas 63 a 71
    def sem_return(experiencia):
    if experiencia >= 5:
        "Sênior"
    else:
        "Pleno"


df["nivel_errado"] = df["experiencia"].apply(sem_return)
print(bool(df["nivel_errado"].isna().all()))
    Output
    True

    The entire column was absent, in silence. When a column created with apply comes out completely empty, look for the return forgotten.

    Ranges without `apply`: `pd.cut` and `np.select`

    The apply is flexible, but runs a Python function per row, and loses the speed of vectorization (the same lesson from NumPy). To transform numbers into ranges, the pd.cut does the same in one pass, and the np.select does the same for any list of conditions:

    basico/cap11_novas_colunas.pylinhas 76 a 88
    def nivel_por_apply(experiencia):
    return "Sênior" if experiencia >= 5 else ("Pleno" if experiencia >= 2 else "Iniciante")


por_apply = df["experiencia"].apply(nivel_por_apply)
por_cut = pd.cut(df["experiencia"], bins=[0, 2, 5, np.inf], right=False,
                 labels=["Iniciante", "Pleno", "Sênior"]).astype(str)
por_select = pd.Series(
    np.select([df["experiencia"] >= 5, df["experiencia"] >= 2], ["Sênior", "Pleno"], default="Iniciante"),
    index=df.index,
)
print(bool((por_apply == por_cut).all()), bool((por_apply == por_select).all()))
print(por_cut.value_counts().to_dict())
    Output
    True True
{'Pleno': 6, 'Sênior': 6, 'Iniciante': 2}

    The three versions give the same result. In pd.cut, the right=False makes the intervals be [0, 2), [2, 5) and [5, ∞), which is exactly "less than 2", "from 2 to 4" and "5 or more". In large data, the time difference is significant:

    basico/cap11_novas_colunas.pylinhas 90 a 103
    import time

grande = pd.Series(np.random.default_rng(0).integers(0, 20, 200_000))


def medir(funcao):
    inicio = time.perf_counter()
    funcao()
    return time.perf_counter() - inicio


t_apply = medir(lambda: grande.apply(nivel_por_apply))
t_cut = medir(lambda: pd.cut(grande, bins=[0, 2, 5, np.inf], right=False, labels=["I", "P", "S"]))
print("o cut foi pelo menos 3 vezes mais rápido:", t_apply > 3 * t_cut)
    Output
    o cut foi pelo menos 3 vezes mais rápido: True

    The order of choice

    First direct arithmetic (df["a"] * 2). Then pd.cut or np.select for ranges. Then map to replace values with a dictionary. The apply is left for logic that really needs a function. It’s the same order as NumPy: always vectorize when possible.

    Exercise 1

    Sorting performance without apply

    Rewrite categoria_desempenho with np.select (Excellent if the score is 8 or more, Good if it is 6 or more, and Needs improvement in other cases), and check that the result is the same as that of apply.