Selection, filtering, groupby, merge, pivot, resample and moving windows.

Selecting: loc and iloc

Lesson 0.5.2 introduced Series and DataFrame. To work with them there are two selectors, and confusing them is one of the most common mistakes:

Both accept [rows, columns], and each part can be a label (or position), a list, a slice or a bool mask:

import numpy as np
import pandas as pd

precios = pd.read_csv("sint_diario.csv", parse_dates=["fecha"],
                      index_col="fecha")
cierre = precios["cierre"]
r = cierre.pct_change()

print(precios.loc["2023-01-02", "cierre"])
print(precios.iloc[-1, 3], precios.columns[3])
precios.iloc[-3:, [3, 4]]

105.44
142.22 cierre
            cierre  volumen
fecha
2025-12-29  139.56   801992
2025-12-30  139.98  1510567
2025-12-31  142.22  2459297

The last row has position −1 and the cierre column is the fourth (position 3). iloc is useful for "the last n sessions" or "the first row"; for everything else, loc with dates and names is more readable and does not break if the order of the columns changes.

Filtering sessions

A bool mask with the same index selects the rows where it is True, as in NumPy (lesson 0.5.1). Conditions are combined with & (and), | (or) and ~ (not), each one in parentheses: & has higher precedence than <, and Python's and/or do not work with masks.

volumen_alto = precios["volumen"] > precios["volumen"].median()
caidas = precios[(r < -0.03) & volumen_alto]
print(len(caidas), "sessions")
print(caidas.index.year.value_counts().sort_index().to_dict())

ops = pd.read_csv("operaciones_sint.csv",
                  parse_dates=["fecha_entrada", "fecha_salida"])
otras = ops[~ops["simbolo"].isin(["SINT"])]
print(otras["simbolo"].value_counts().to_dict())

10 sessions
{2021: 1, 2022: 8, 2025: 1}
{'RVAR': 4, 'ORO': 3, 'TECH': 3}

Of the ten sessions with a drop of more than 3% and volume above the median, most are in 2022, the year of SINT's decline. isin checks membership in a list and ~ negates it: ten trades in the log are for other assets of the fictitious portfolio.

Changing frequency: resample

resample groups a date index into periods (weeks, months, years) and applies an aggregation to each one. For monthly returns you take the last close of each month and compute its change:

cierre_mes = cierre.resample("ME").last()
r_mes = cierre_mes.pct_change()
print(len(r_mes), r_mes.index[0].date(), r_mes.index[-1].date())
r_mes.head(3).round(4)

60 2021-01-31 2025-12-31
fecha
2021-01-31       NaN
2021-02-28    0.0173
2021-03-31    0.0576
Freq: ME, Name: cierre, dtype: float64

"ME" means month end: each month's label is its last calendar day, even if the last session was earlier. The January 2021 return is NaN because there is no previous month: the data starts on January 4. The old "M" alias is no longer accepted in pandas 3.0:

cierre.resample("M").last()

ValueError: Invalid frequency: M. Failed to parse with error message: ValueError("'M' is no longer supported for offsets. Please use 'ME' instead.")

Compounding each month's daily returns with (1 + r).resample("ME").prod() - 1 gives the same result. What does not give the same result is the mean of the month's closes: it is a different, smoother series that does not correspond to any price you could have traded at.

The period label matters too. The March 2022 value carries the date of March 31 and uses that day's close: it is not known until the month ends. If this series is later joined with daily data, the monthly value can only be used from the next session onward. A signal computed "at the start of the month" with the value of the month that has just begun lets in four weeks of future information, and the backtest rewards it misleadingly. resample has the label and closed parameters to choose which end of the period names each group; with price data, the safe choice is to keep the defaults and shift with shift(1) whatever will be used as a signal.

Grouping: groupby

groupby follows the split, apply, combine pattern: it separates the rows into groups according to a key, applies a function to each group and joins the results into a table with one row per group. The key can be a column, an attribute of the index or any aligned Series:

flowchart TB
  A[Returns] --> B[Split by year]
  B --> C[Apply agg]
  C --> D[Table by year]

por_ano = r.groupby(r.index.year).agg(["mean", "std", "count"])
por_ano["anual"] = (1 + r).groupby(r.index.year).prod() - 1
por_ano["vol_anual"] = por_ano["std"] * np.sqrt(252)
por_ano[["count", "anual", "vol_anual"]].round(4)

       count   anual  vol_anual
fecha
2021     259  0.1520     0.1599
2022     260 -0.0850     0.2483
2023     260  0.1421     0.1495
2024     262  0.1432     0.1499
2025     261  0.0424     0.1792

2022 is the only negative year (−8.5%) and the most volatile (24.8%, against 15–18% in the others). The count column is a quick check: 259 returns in 2021 because the first session has no previous one.

The same pattern with the weekday as the key is the first step of a seasonality study:

dias = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"]
semana = r.groupby(r.index.day_name()).agg(["mean", "count"])
semana = semana.reindex(dias)
semana["mean"] = (semana["mean"] * 100).round(3)
semana

            mean  count
fecha
Monday     0.045    260
Tuesday   -0.052    261
Wednesday  0.019    261
Thursday   0.051    260
Friday     0.108    260

Friday comes out with the highest mean (0.108% daily) and Tuesday negative. Before turning that into a rule, a calculation: the standard error of a mean of 260 returns with a standard deviation of 1.14% is $0.0114 / \sqrt{260} \approx 0.07\%$. The differences between days are of that order: consistent with chance. In SINT there can be no day-of-the-week effect, because the data is generated without one; on real data, the same table produces "patterns" just as convincing and almost always just as false (multiple testing, level 2).

Pivoting: a year × month table

pivot reshapes a long table (one row per month) into a wide table: one row per year, one column per month. It is the monthly returns table that accompanies any fund report:

larga = r_mes.to_frame("r").assign(
    ano=r_mes.index.year, mes=r_mes.index.month,
)
tabla = larga.pivot(index="ano", columns="mes", values="r")
(tabla * 100).round(1)

mes    1    2     3    4    5    6    7     8    9    10   11   12
ano
2021  NaN  1.7   5.8  1.5 -5.0  7.2 -1.7   3.0  2.2  0.5 -3.5  6.6
2022 -3.6  8.4  -6.8 -9.9  7.2 -0.0 -3.9  10.9 -9.5 -3.1 -5.1  9.6
2023  2.6 -1.9   2.7  0.6  3.1 -0.4 -3.1   0.5  6.2 -0.5  2.4  1.5
2024  8.2 -1.9  -2.6  0.9  1.4  1.0  2.0  -5.4  7.3 -2.9 -0.9  7.4
2025  3.6  1.9 -10.1  5.6 -0.7 -2.1 -1.3   2.0  2.7  1.0  4.6 -2.1

Read by rows, the table tells the story of 2022: five months with moves of more than 8% in one direction or the other, from the 9.9% drop in April to the 10.9% rise in August. Read by columns, it suggests temptations (does December almost always rise?) with five data points per column: too few to conclude anything. If the long table had two rows with the same year and month, pivot would raise an error; pivot_table would aggregate them with a function (the mean, by default).

Combining tables: merge and join

The trade log stores entry dates and prices, but not the volume on those days or how the market closed. merge combines two tables by common columns, like SQL's JOIN (lesson 0.5.5). For the SINT trades, the key is the entry date on the left and the price index on the right:

ops_sint = ops[ops["simbolo"] == "SINT"]
con_precio = ops_sint.merge(
    precios[["cierre", "volumen"]],
    left_on="fecha_entrada", right_index=True,
    how="left", validate="many_to_one",
)
print(len(ops_sint), len(con_precio))
iguales = (con_precio["precio_entrada"] == con_precio["cierre"]).all()
print("Entry at the day's close:", iguales)
con_precio[["id", "fecha_entrada", "lado", "cierre", "volumen"]].head(3)

30 30
Entry at the day's close: True
   id fecha_entrada   lado  cierre  volumen
1   2    2021-09-02  largo  108.64  1454984
2   3    2021-10-27  corto  112.07  1644654
3   4    2021-11-10  corto  104.30  2575784

Three checks in one cell. how="left" keeps all the trades even if their date is missing from the prices (it would show NaN); validate="many_to_one" raises an error if a date appears twice in the prices, which would silently duplicate trades; and the number of rows before and after must match: 30 and 30. The final comparison confirms what the data README says: entries were made at the closing price.

When both tables share the date index, join is shorter: it joins by index and, by default, keeps the rows of the left table. SINT and the four portfolio assets have the same calendar:

cartera = pd.read_csv("cartera_sint.csv", parse_dates=["fecha"],
                      index_col="fecha")
todos = precios[["cierre"]].rename(columns={"cierre": "SINT"})
todos = todos.join(cartera)
print(todos.shape, todos.isna().sum().sum())
todos.pct_change().corr().round(2).loc["SINT"]

(1303, 5) 0
SINT    1.00
RVAR   -0.01
RFIJ    0.00
ORO     0.00
TECH   -0.02
Name: SINT, dtype: float64

1,303 rows and no missing values: the calendars match. With data from two different markets that would not be the case, and the decision about what to do with the unmatched dates (lesson 0.5.2) is made here: how="inner" keeps the common sessions, how="outer" all of them (with NaN where a market is missing), and ffill() carries forward the last known price of a closed market, which is what a manager would see when valuing the portfolio that day. SINT is barely correlated with the portfolio assets: they are fictitious series generated separately.

Windows: rolling and expanding

A rolling window computes a statistic over the last n rows and moves forward one row at a time. It is the tool behind moving averages, rolling volatilities and the bands of any indicator. The annualized volatility of the last 21 days (one market month) is:

vol_21 = r.rolling(21).std() * np.sqrt(252)
print(vol_21.isna().sum(), "values without a full window")
print(f"Median:  {vol_21.median():.1%}")
print(f"Maximum: {vol_21.max():.1%} on {vol_21.idxmax().date()}")
print(f"Minimum: {vol_21.min():.1%} on {vol_21.idxmin().date()}")

21 values without a full window
Median:  15.8%
Maximum: 33.9% on 2022-09-12
Minimum: 7.9% on 2021-11-03

The window is right-aligned: the value for September 12, 2022 uses the 21 returns ending on that day, inclusive. The first 21 values are NaN: the first because there is no return and the other twenty because the window is not complete (with min_periods you can ask for a computation with fewer data points). The window size is a trade-off: with 21 sessions the estimate reacts within a few days to a regime change, but it is noisy; with 63 or 126 it is more stable and arrives late. There is no correct window, and choosing the one that gives the best result in a backtest is another form of overfitting. The figure at the top plots this series: volatility spends long periods around its 15.8% median and spikes in two episodes, in 2022 and in April 2025, to double it.

An expanding window (expanding) starts at the first row and grows up to the current one. Its most common use in trading is the running maximum of prices, which defines the drawdown: the decline from the peak reached up to that moment.

maximo = cierre.expanding().max()
print((maximo == cierre.cummax()).all())
drawdown = cierre / maximo - 1
fondo = drawdown.idxmin()
pico = cierre.loc[:fondo].idxmax()
print(f"Worst drawdown: {drawdown.min():.1%}")
print(f"From the peak on {pico.date()} to the trough on {fondo.date()}")
print(f"Sessions in drawdown: {(drawdown < 0).mean():.0%}")

True
Worst drawdown: -25.0%
From the peak on 2022-03-07 to the trough on 2022-10-20
Sessions in drawdown: 95%

expanding().max() and cummax() are the same thing; cummax is shorter. SINT's worst drawdown is 25%: from the March 2022 peak to the October trough. And 95% of the sessions are below the previous peak: for an asset, or a strategy, being in drawdown is the normal state. Lesson 0.6.1 plots this curve under the prices.

Exercises

1. Build SINT's weekly bars (open, high, low, close and volume) with resample("W-FRI") and agg, with the correct aggregation for each column. Show the first three.

semanal = precios.resample("W-FRI").agg({
    "apertura": "first", "maximo": "max", "minimo": "min",
    "cierre": "last", "volumen": "sum",
})
print(len(semanal))
semanal.head(3)

261
            apertura  maximo  minimo  cierre   volumen
fecha
2021-01-08    100.02  100.64   97.22   99.05  10647985
2021-01-15     98.98   99.70   96.96   97.75   7135061
2021-01-22     97.68   99.32   96.02   98.36   7573689

2. Compute the net result of each trade in the log (sign × price difference × quantity − commission) and, with groupby, a table by symbol with the number of trades, the total result and the percentage of winners.

signo = np.where(ops["lado"] == "largo", 1, -1)
ops["pnl"] = (signo * (ops["precio_salida"] - ops["precio_entrada"])
              * ops["cantidad"] - ops["comision"])
resumen = ops.groupby("simbolo").agg(
    operaciones=("pnl", "size"),
    total=("pnl", "sum"),
    ganadoras=("pnl", lambda s: (s > 0).mean()),
)
resumen.round(2)

         operaciones    total  ganadoras
simbolo
ORO                3 -3237.96       0.00
RVAR               4    17.09       0.50
SINT              30  4405.08       0.63
TECH               3   -35.02       0.33

3. How many sessions in each year had a 21-session volatility above 25%? Use vol_21 and groupby.

alta = vol_21 > 0.25
alta.groupby(alta.index.year).sum()

fecha
2021      0
2022    133
2023      0
2024      0
2025     31
Name: cierre, dtype: int64