--- title: "0.5.3 pandas: groupby, merge, resample and rolling" description: "Selection with loc and iloc, filters, groupby, merge, pivot, resample, and rolling and expanding windows with SINT's prices and trades." url: "https://pythonparatrading.com/en/python-trading-course/level-0/pandas-groupby-merge-resample-rolling/" lang: "en" image: "https://pythonparatrading.com/og/en/python-trading-course/level-0/pandas-groupby-merge-resample-rolling.png" --- # 0.5.3 · Selection, filtering, groupby, merge, pivot, resample and moving windows > Almost all daily research with pandas comes down to eight operations: select, filter, group, combine, pivot, change frequency and compute over windows. This lesson walks through them with SINT until it reaches its rolling volatility and its drawdown, without using information from the future. - Course: [Level 0: Python from scratch and a data mindset](https://pythonparatrading.com/en/python-trading-course/level-0/) - Module: 0.5 · Numerical computing and data - Duration: 55 min - Prerequisites: [0.5.2 · pandas 3.0: Series, DataFrame, time indexes, Copy-on-Write, the str type and pd.col()](https://pythonparatrading.com/en/python-trading-course/level-0/pandas-3-series-and-dataframe/) ## By the end of this lesson - Select rows and columns by label with `loc` and by position with `iloc`, and filter sessions with combined conditions. - Compute monthly returns with `resample` and summarize them by year and by weekday with `groupby` and `pivot`. - Combine the trade log with the prices through `merge` and check the relationship between the tables with `validate`. - Compute rolling volatility with `rolling` and drawdown with `expanding`/`cummax`, and explain why they use no future data. ## 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: | Selector | Selects by | Example | Slice `a:b` | |---|---|---|---| | `loc` | Label: dates, column names | `precios.loc["2023-01-02", "cierre"]` | Includes `b` | | `iloc` | Integer position, from 0 | `precios.iloc[-1, 3]` | Excludes `b`, like Python | Both accept `[rows, columns]`, and each part can be a label (or position), a list, a slice or a `bool` mask: ```python 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]] ``` ```output 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. ```python 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()) ``` ```output 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: ```python 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) ``` ```output 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: ```python raises cierre.resample("M").last() ``` ```output 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`: ```mermaid flowchart TB A[Returns] --> B[Split by year] B --> C[Apply agg] C --> D[Table by year] ``` ```python 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) ``` ```output 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: ```python 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 ``` ```output 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: ```python 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) ``` ```output 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: ```python 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) ``` ```output 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. | Argument | Values | Purpose | |---|---|---| | `how` | `"inner"`, `"left"`, `"right"`, `"outer"` | Which unmatched rows are kept | | `on` / `left_on` / `right_on` | Columns | Keys on each side | | `left_index` / `right_index` | `True` | Use the index as the key | | `validate` | `"one_to_one"`, `"many_to_one"`… | Error if the relationship is not the expected one | | `indicator` | `True` | `_merge` column with the origin of each row | 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: ```python 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"] ``` ```output (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: $$ \sigma_t = \sqrt{252} \cdot \operatorname{std}(r_{t-20}, \dots, r_t) $$ ```python 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()}") ``` ```output 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. $$ \text{DD}_t = \frac{P_t}{\max_{s \le t} P_s} - 1 $$ ```python 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%}") ``` ```output 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. **Solution.** ```python semanal = precios.resample("W-FRI").agg({ "apertura": "first", "maximo": "max", "minimo": "min", "cierre": "last", "volumen": "sum", }) print(len(semanal)) semanal.head(3) ``` ```output 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 ``` The week's open is that of the first day; the close, that of the last; the high and the low, the extremes of the week; the volume, the sum. `"W-FRI"` ends the weeks on Friday and labels them with that date. 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. **Solution.** ```python 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) ``` ```output 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 ``` The `name=(column, function)` syntax of `agg` names each column of the result. The percentage of winners is the mean of a `bool` mask. With three or four trades per symbol outside SINT, none of these figures says anything about the quality of the rules that generated them. 3. How many sessions in each year had a 21-session volatility above 25%? Use `vol_21` and `groupby`. **Solution.** ```python alta = vol_21 > 0.25 alta.groupby(alta.index.year).sum() ``` ```output fecha 2021 0 2022 133 2023 0 2024 0 2025 31 Name: cierre, dtype: int64 ``` Summing a mask counts the `True` values. Most of the high-volatility sessions are in 2022; the 2025 spike is shorter. The initial `NaN`s count as `False` in the comparison (lesson 0.5.1), so they add nothing to the count. ## Self-assessment 1. `precios` has a `DatetimeIndex`. What does `precios.iloc[-1, 3]` return? - A. The row with label −1 and the column with label 3 - B. An error, because the index holds dates - C. The value of the last row and the fourth column, by position - D. The last three rows *Answer C: The value of the last row and the fourth column, by position.* `iloc` uses positions, starting at 0, like Python lists: −1 is the last row and 3 the fourth column. `loc` would use labels (dates and column names). 2. Which of these expressions filters the sessions with a drop greater than 3% and volume above the median? - A. `p[r < -0.03 and p["volumen"] > med]` - B. `p[(r < -0.03) & (p["volumen"] > med)]` - C. `p[r < -0.03 & p["volumen"] > med]` - D. `p[(r < -0.03) or (p["volumen"] > med)]` *Answer B: `p[(r < -0.03) & (p["volumen"] > med)]`.* pandas masks use `&`, `|` and `~`, not `and`/`or`, and each condition goes in parentheses because `&` has higher precedence than `<` and `>`. 3. At date t, which returns does `r.rolling(21).std()` use? - A. The 21 sessions centered on t - B. The 21 sessions after t - C. All sessions up to t - D. The 21 sessions ending at t, including t *Answer D: The 21 sessions ending at t, including t.* By default the window is right-aligned: it ends at the current row. With `center=True` it would use future sessions, and `expanding()` would use all the previous ones. 4. When combining trades with prices by date, what is `validate="many_to_one"` in `merge` for? - A. To raise an error if a date appears more than once in the price table, instead of silently duplicating trades - B. To remove duplicate trades - C. To make the merge faster - D. To keep only the common dates *Answer A: To raise an error if a date appears more than once in the price table, instead of silently duplicating trades.* If the right-hand table had two rows with the same date, every trade on that day would be duplicated and the total result would change without warning. `validate` turns that data error into an exception. ## Key points - `loc` selects by label (dates and names) with both ends included; `iloc`, by position. Masks are combined with `&`, `|` and `~`, each condition in parentheses. - `resample("ME")` changes frequency by date; `groupby` groups by any key (year, weekday, symbol) and applies an aggregation to each group. - `merge` combines tables by columns and `join` by index. `validate` and `indicator` detect duplicates and unmatched rows before they distort a result. - `rolling(n)` uses the n rows ending at the current one and `expanding()` all the previous ones: neither looks into the future. `center=True` does, and is not used for signals. - Rolling volatility: `r.rolling(21).std() * np.sqrt(252)`. Drawdown: `cierre / cierre.cummax() - 1`. ## Resources ### Research - [Volatility estimators](https://pythonparatrading.com/en/volatility-estimators-for-trading/) · Research-0002 · 4 min ### Documentation - [pandas: indexing and selecting data (user guide)](https://pandas.pydata.org/docs/user_guide/indexing.html) · pandas.pydata.org - [pandas: group by, split-apply-combine (user guide)](https://pandas.pydata.org/docs/user_guide/groupby.html) · pandas.pydata.org - [pandas: merge, join and concat (user guide)](https://pandas.pydata.org/docs/user_guide/merging.html) · pandas.pydata.org - [pandas: windowing operations (user guide)](https://pandas.pydata.org/docs/user_guide/window.html) · pandas.pydata.org ### Data - [Level 0 data: SINT daily bars (CSV)](https://pythonparatrading.com/curso/datos/nivel-0/sint_diario.csv) · Download · sint_diario.csv Previous lesson: [0.5.2 · pandas 3.0: Series, DataFrame, time indexes, Copy-on-Write, the str type and pd.col()](https://pythonparatrading.com/en/python-trading-course/level-0/pandas-3-series-and-dataframe/) Next lesson: [0.5.4 · Polars: lazy versus eager API, expressions, performance and when to pick it over pandas](https://pythonparatrading.com/en/python-trading-course/level-0/polars-lazy-api-and-expressions/)