Dates and time series

Read dates directly in reading

The parse_dates converts the columns already in read_csv, and the accessor .dt delivers the pieces for each date:

import pandas as pd

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

pedidos = pd.read_csv("dados/pedidos.csv", parse_dates=["data_pedido"])
print(pedidos["data_pedido"].dtype, pedidos["data_pedido"].min().date(), pedidos["data_pedido"].max().date())

data = pedidos["data_pedido"]
print(data.dt.year.iloc[0], data.dt.month.iloc[0], data.dt.dayofweek.iloc[0], data.dt.quarter.iloc[0])
dias = {0: "seg", 1: "ter", 2: "qua", 3: "qui", 4: "sex", 5: "sáb", 6: "dom"}
print(data.dt.dayofweek.map(dias).value_counts().reindex(list(dias.values())).tolist())

datetime64[us] 2025-01-01 2025-12-31
2025 1 2 1
[126, 136, 125, 83, 109, 108, 113]

The dayofweek goes from 0 (Monday) to 6 (Sunday). I use a dictionary for the names in Portuguese, instead of the day_name(locale=...), which depends on the machine having that language installed.

Resample: from days to months

The resample is a groupby to time periods: it needs an index of dates, and you choose the frequency. Here, the amount requested per month:

por_mes = pedidos.set_index("data_pedido")["quantidade"].resample("MS").sum()
print(len(por_mes), por_mes.index[0].date(), por_mes.iloc[:3].tolist())
print(por_mes.head(3))

12 2025-01-01 [366, 286, 367]
data_pedido
2025-01-01    366
2025-02-01    286
2025-03-01    367
Freq: MS, Name: quantidade, dtype: int64

The old code used "M" for month, and this is a temporary copy, and the erases the evidence

try:
    pedidos.set_index("data_pedido")["quantidade"].resample("M").sum()
except ValueError as erro:
    print("ValueError:", str(erro)[:35])

ValueError: Invalid frequency: M. Failed to par

Variation between periods

With the monthly series, shift, diff and pct_change compares each month with the previous one, without a loop:

resumo = pd.DataFrame({"quantidade": por_mes})
resumo["mes_anterior"] = resumo["quantidade"].shift(1)
resumo["variacao"] = resumo["quantidade"].diff()
resumo["variacao_pct"] = (resumo["quantidade"].pct_change() * 100).round(1)
print(resumo.head(4))

             quantidade  mes_anterior  variacao  variacao_pct
data_pedido                                                  
2025-01-01          366           NaN       NaN           NaN
2025-02-01          286         366.0     -80.0         -21.9
2025-03-01          367         286.0      81.0          28.3
2025-04-01          317         367.0     -50.0         -13.6

The first month has no comparison (NaN), because there is no previous one.

Dates in mixed formats

In the employee file, three dates are written as dd/mm/aaaa, and the others as aaaa-mm-dd. Pandas infers the format of the first date and fails to find the first that doesn't match:

f = pd.read_csv("dados/funcionarios_100.csv")
try:
    pd.to_datetime(f["data_admissao"])
except ValueError as erro:
    print("ValueError:", str(erro).splitlines()[0])

ValueError: time data "29/09/2019" doesn't match format "%Y-%m-%d". You might want to try:

The "correction" that is most commonly found out there is format="mixed" with dayfirst=True. It does not throw an error, and that's why it seems to solve the issue. See what it actually does, comparing with the explicit: first the ISO format, then the Brazilian for the remaining ones, and NaT (the missing date) for anything that doesn't match any:

iso = pd.to_datetime(f["data_admissao"], format="%Y-%m-%d", errors="coerce")
br = pd.to_datetime(f["data_admissao"], format="%d/%m/%Y", errors="coerce")
explicito = iso.fillna(br)
print(int(iso.isna().sum()), int(explicito.isna().sum()))

tentacao = pd.to_datetime(f["data_admissao"], format="mixed", dayfirst=True)
trocadas = tentacao != explicito
print(int(trocadas.sum()), len(f))
linha = trocadas.idxmax()
print(f.loc[linha, "data_admissao"], tentacao[linha].date(), explicito[linha].date())

3 0
32 104
2019-08-07 2019-07-08 2019-08-07

The explicit conversion finds the 3 Brazilian dates and does not leave any without a date. Already the dayfirst=True swapped the day for the month in 32 out of 104 dates: the ISO date 2019-08-07 (August 7) became 2019-07-08 (July 8). This happens with any ISO date where the day is 12 or less, because the dayfirst instructs to treat the first number after the year as the day. It is the worst kind of error: the code runs, the dates remain valid, and a third of them are wrong.

The format="mixed" without the dayfirst gets it right here, but only by luck: the three Brazilian dates have a day greater than 12, so they cannot be read as a month. A date like 04/05/2020 would be read as April:

ambiguas = pd.Series(["2020-01-31", "04/05/2020"])
print(pd.to_datetime(ambiguas, format="mixed").dt.strftime("%Y-%m-%d").tolist())

['2020-01-31', '2020-04-05']

The Brazilian date is 4 of 04/05/2020 May , and Pandas read it as 5 of April. The rule I follow:never guess a date format . Declare each format you accept, convert in groups withcount errors="coerce", what remained as and investigate. A rejected date is a visible problem, and a swapped date is an invisible problem. NaT The subtraction of two dates gives a duration (

Time at home and displacements

). With aTimedeltafixed reference date , the result is reproducible: a report that useschanges every day, and this breaks any test. hoje() A date

referencia = pd.Timestamp("2026-01-01")
admissao = explicito
tempo_de_casa_anos = ((referencia - admissao).dt.days // 365)
print(tempo_de_casa_anos.describe()[["min", "max"]].astype(int).tolist())
print(pd.Timestamp("2025-01-31") + pd.offsets.MonthEnd(1), pd.Timestamp("2025-01-10") + pd.Timedelta(days=30))
print(pd.date_range("2025-01-01", periods=4, freq="W").strftime("%d/%m").tolist())

[0, 10]
2025-02-28 00:00:00 2025-02-09 00:00:00
['05/01', '12/01', '19/01', '26/01']

Time zone

without a timezone is ambiguous. The indicates in which timezone it was recorded, and the tz_localize takes it to another. The golden rule: tz_convert store in UTC and convert only to display Requests by day of the week.

registro = pd.to_datetime(pd.Series(["2025-01-10 12:00", "2025-07-10 12:00"]))
em_sp = registro.dt.tz_localize("America/Sao_Paulo")
print(em_sp.dt.tz_convert("UTC").astype(str).tolist())

['2025-01-10 15:00:00+00:00', '2025-07-10 15:00:00+00:00']