Duplicates, types, and names

Duplicates

There are two different questions: are there entirely repeated rows, and are there repeated identifiers? The duplicated() compares all columns by default, and the subset restricts the comparison:

import numpy as np
import pandas as pd

pd.set_option("display.width", 170)
pd.set_option("display.max_columns", 20)
df = pd.read_csv("dados/funcionarios_15.csv")

print(int(df.duplicated().sum()), int(df.duplicated(subset=["id_funcionario"]).sum()))
colunas = ["id_funcionario", "nome", "departamento", "experiencia", "data_admissao"]
print(df.loc[df.duplicated("id_funcionario", keep=False), colunas])

0 1
    id_funcionario       nome departamento  experiencia data_admissao
0              101  Ana Souza   Engenharia            1    2021-01-10
14             101  Ana Souza           RH            7    2023-06-24

No row is entirely repeated (0), but one identifier appears twice (1). The same employee was registered twice, and with updated fields in the second: the department changed, and the experience as well. That's why the comparison of entire rows found nothing.

The drop_duplicates needs to know which to keep, and the choice matters:

primeiro = df.drop_duplicates(subset=["id_funcionario"], keep="first")
ultimo = df.drop_duplicates(subset=["id_funcionario"], keep="last")
print(len(primeiro), len(ultimo))
print(primeiro.loc[primeiro["id_funcionario"] == 101, "departamento"].item())
print(ultimo.loc[ultimo["id_funcionario"] == 101, "departamento"].item())

14 14
Engenharia
RH

The keep="first" keeps the first registration (Engineering), and the keep="last" keeps the most recent (HR). If the second record is an update, the right thing is to keep the newest, not the first that appeared. The original course keeps the first, and I follow this in the next chapters for the numbers to match the course, but in real data I sort by date first before removing, and keep the last.

Types

The column salario is str, because two rows have R$ 4650 and R$ 5550. There are two ways to convert it to a number. The to_numeric(errors="coerce") converts what it can and turns the rest into NaN, and this destroys the two salaries written with R$:

direto = pd.to_numeric(df["salario"], errors="coerce")
print(int(df["salario"].isna().sum()), int(direto.isna().sum()))

sem_simbolo = df["salario"].str.replace("R$ ", "", regex=False)
certo = pd.to_numeric(sem_simbolo)
print(int(certo.isna().sum()), certo.dtype)

2 4
2 float64

The truly absent were 2. With the coerce directly, they turned into 4: we lost 2 salaries that existed, just written in a different way. First remove the symbol and then converting (without coerce) preserves everything. And without the coerce, a value that really isn't a number for execution with a clear message:

try:
    pd.to_numeric(pd.Series(["10", "abc"]))
except ValueError as erro:
    print("ValueError:", erro)

ValueError: Unable to parse string "abc" at position 1

The errors="coerce" is useful, but it's a decision to discard data. I use it after checking how many values it will transform into NaN, and not as the first attempt.

The `astype(int)` and the missing value

Converting a column with absents to integer fails because the NaN is not an integer. The nullable type Int64 solves:

try:
    df["idade"].astype(int)
except ValueError as erro:
    print(type(erro).__name__)
print(df["idade"].astype("Int64").tolist()[4:8])

IntCastingNaNError
[28, 29, <NA>, 31]

Dates

A date stored as text is useless: it doesn't sort by time, doesn't subtract, doesn't extract the year. The to_datetime transforms it into a real date type, and the accessor .dt provides the pieces:

datas = pd.to_datetime(df["data_admissao"])
print(datas.dtype, datas.dt.year.tolist()[:5])
print(datas.min().date(), datas.max().date())

datetime64[us] [2021, 2022, 2023, 2024, 2021]
2021-01-10 2024-08-17

Column names

The rename returns a new table and leaves the original intact (no need for inplace). To clean all the names at once, chain the text methods. In Portuguese, removing accents avoids a lot of headaches with column names, and the normalize does this:

renomeada = df.rename(columns={"nome": "nome_completo"})
print(renomeada.columns.tolist()[:3], df.columns.tolist()[:3])

sujas = pd.Series([" Nome Completo ", "Salário (R$)", "Data de Admissão"])
limpas = (
    sujas.str.strip()
    .str.lower()
    .str.normalize("NFKD")
    .str.encode("ascii", errors="ignore")
    .str.decode("ascii")
    .str.replace(r"[^a-z0-9]+", "_", regex=True)
    .str.strip("_")
)
print(limpas.tolist())

['id_funcionario', 'nome_completo', 'departamento'] ['id_funcionario', 'nome', 'departamento']
['nome_completo', 'salario_r', 'data_de_admissao']

The normalize("NFKD") separates each accented letter into letter plus accent, and the encode("ascii", errors="ignore") discards everything that is not ASCII (the loose accents), so that Salário becomes Salario. I chose this path on purpose, instead of a regular expression that deletes the accents: with the pyarrow installed, the type str from Pandas 3 uses the Arrow regular expression engine, which accepts \p{M} but rejects \u0300, and the module re of Python does the opposite. The same pattern may work on one machine and fail on another. Chapter 17 revisits this.

Inconsistent text

The departamento has engenharia and MARKETING next to Engenharia and Marketing. The common solution is strip().str.title(), but the title() ruins acronyms and prepositions:

print(df["departamento"].str.strip().str.title().dropna().unique().tolist())

['Engenharia', 'Marketing', 'Ciência De Dados', 'Rh', 'Finanças']

Notice in Rh (the acronym lost its form) and in Ciência De Dados (the De gained uppercase). In columns of known categories, the safest path is a dictionary with the correctnames, applied with the map. And it brings a bonus: what are not is in the dictionary becomes NaN, which allows to detect unexpected values:

CANONICO = {
    "engenharia": "Engenharia",
    "marketing": "Marketing",
    "finanças": "Finanças",
    "ciência de dados": "Ciência de Dados",
    "rh": "RH",
}
limpo = df["departamento"].str.strip().str.lower().map(CANONICO)
inesperados = df.loc[limpo.isna() & df["departamento"].notna(), "departamento"]
print(limpo.value_counts(dropna=False).to_dict())
print(len(inesperados))

{'Marketing': 4, 'Engenharia': 3, 'RH': 3, 'Ciência de Dados': 2, 'Finanças': 2, nan: 1}
0

Now there are 5 departments (plus 1 missing), and no unexpected value: what was not in the dictionary would be shown in inesperados. If tomorrow it arrives Tecnologia in a new file, it becomes NaN and you are notified, instead of silently gaining a sixth department.