--- title: "0.5.2 pandas 3.0: Series, DataFrame and Copy-on-Write" description: "pandas 3.0 Series and DataFrame with SINT prices: time indexes, Copy-on-Write, the PyArrow-backed str type and pd.col() expressions." url: "https://pythonparatrading.com/en/python-trading-course/level-0/pandas-3-series-and-dataframe/" lang: "en" image: "https://pythonparatrading.com/og/en/python-trading-course/level-0/pandas-3-series-and-dataframe.png" --- # 0.5.2 · pandas 3.0: Series, DataFrame, time indexes, Copy-on-Write, the str type and pd.col() > pandas is the working table of research in Python: prices with dates, operations with text columns, results aligned by index. Version 3.0 changes three things that break old code —Copy-on-Write, the `str` type and date resolution— and adds `pd.col()`. This lesson teaches them with the course data. - 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: 45 min - Prerequisites: [0.5.1 · NumPy 2: arrays, broadcasting, vectorization, linear algebra and random generators](https://pythonparatrading.com/en/python-trading-course/level-0/numpy-arrays-and-vectorization/) ## By the end of this lesson - Read a price CSV into a `DataFrame` with a `DatetimeIndex` and select sessions, months and date ranges. - Explain why two `Series` align on their index and how `NaN`s appear when the calendars do not match. - Modify a `DataFrame` in pandas 3.0 without chained assignment, with `.loc` in a single step. - Detect text columns with the `str` type and write derived columns with `pd.col()` in `assign` and `loc`. ## Series and DataFrame Lesson 0.5.1 worked with NumPy arrays: numbers with no name and no date. Research needs more: knowing which session each close belongs to, which column is the volume and which trade belongs to which asset. pandas adds that on top of NumPy with two structures: - **`Series`**: a one-dimensional array with an **index** (the row labels) and a name. The closes of SINT are a `Series` indexed by date. - **`DataFrame`**: several columns that share the same index, each with its own type. SINT's daily bars (open, high, low, close and volume) are a `DataFrame`. The course versions are those in `curso-code/uv.lock`: pandas 3.0.6, from the 3.0 series released in January 2026. Everything that follows works with it, and the differences from pandas 2 are pointed out where they appear. ```python import numpy as np import pandas as pd precios = pd.read_csv("sint_diario.csv", parse_dates=["fecha"], index_col="fecha") print(pd.__version__, type(precios).__name__, precios.shape) print(precios.dtypes) precios.head(3) ``` ```output 3.0.6 DataFrame (1303, 5) apertura float64 maximo float64 minimo float64 cierre float64 volumen int64 dtype: object apertura maximo minimo cierre volumen fecha 2021-01-04 100.02 100.64 99.07 99.14 2322609 2021-01-05 99.32 99.79 98.63 99.25 1517954 2021-01-06 99.10 99.24 97.37 97.38 3245658 ``` `parse_dates` converts the text column into dates and `index_col` uses it as the index. Without those two options, the dates would be text and the index the row numbers 0, 1, 2…: everything that comes afterwards would stop working. Each column has its type: prices are `float64` and volume is `int64`, because it is a whole number of shares. (The column names are Spanish: `apertura` open, `maximo` high, `minimo` low, `cierre` close, `volumen` volume, `fecha` date.) A column of a `DataFrame` is a `Series`. NumPy's vectorized operations still work, and pandas adds named methods for the most common ones in finance, such as `pct_change` (the simple return from lesson 0.5.1): ```python cierre = precios["cierre"] print(type(cierre).__name__, cierre.name, cierre.dtype) r = cierre.pct_change() print(r.head(3)) print(f"Daily mean: {r.mean():.4%} · sessions: {r.count()}") ``` ```output Series cierre float64 fecha 2021-01-04 NaN 2021-01-05 0.001110 2021-01-06 -0.018841 Name: cierre, dtype: float64 Daily mean: 0.0342% · sessions: 1302 ``` The first return is `NaN`: the first day has no previous close. pandas does not remove it and, unlike `np.mean`, `mean()` and `count()` ignore `NaN`s by default, which is why there are 1,302 returns from 1,303 closes. It is convenient and dangerous at the same time: a gap in the data does not break the mean, so it does not warn you either. ### Index alignment The fundamental difference between pandas and NumPy is **alignment**: when two `Series` operate together, pandas pairs the values by their label, not by their position. With two assets that trade on different dates —a market closes for a holiday, a provider is missing a day—the result has the union of the dates and `NaN` where one of the two is missing. An example with SINT and `RVAR`, the equity asset in `cartera_sint.csv`, over two stretches that only overlap on two days: ```python cartera = pd.read_csv("cartera_sint.csv", parse_dates=["fecha"], index_col="fecha") sint = cierre["2024-01-02":"2024-01-05"] rvar = cartera["RVAR"]["2024-01-04":"2024-01-09"] (sint / rvar).round(4) ``` ```output fecha 2024-01-02 NaN 2024-01-03 NaN 2024-01-04 0.7574 2024-01-05 0.7577 2024-01-08 NaN 2024-01-09 NaN dtype: float64 ``` With NumPy arrays, dividing two four-element vectors would have paired SINT's January 2 with `RVAR`'s January 4 without any error: a ratio between prices from different days. pandas prevents it: it only divides what corresponds to the same date and marks the rest as missing. What to do with those missing values (drop them, carry forward the last price) is a decision that lesson 0.5.3 covers with `merge`, `join` and `reindex`. ## Time indexes A date index, `DatetimeIndex`, lets you select by dates written as text. Partial selection —just the year, or the year and month— returns all the sessions in that period, and ranges with `loc` include **both ends** (unlike Python's positional slices): ```python print(precios.index.dtype, precios.index.min().date(), precios.index.max().date()) print(len(precios.loc["2024"]), "sessions in 2024") print(precios.loc["2022-03", "cierre"].iloc[[0, -1]]) precios.loc["2022-06-01":"2022-06-07", ["cierre", "volumen"]] ``` ```output datetime64[us] 2021-01-04 2025-12-31 262 sessions in 2024 fecha 2022-03-01 117.20 2022-03-31 111.28 Name: cierre, dtype: float64 cierre volumen fecha 2022-06-01 108.39 1463683 2022-06-02 105.80 1832370 2022-06-03 104.44 1709652 2022-06-06 102.97 2301634 2022-06-07 102.23 1637924 ``` The index is `datetime64[us]`: dates with **microsecond** resolution. It is a pandas 3.0 change: when reading dates from text, the default resolution goes from nanosecond to microsecond (nanosecond is only used if the text needs it). For selecting or comparing dates nothing changes; where it bites is when converting dates into numbers, a common step when sending timestamps to a broker's API, which usually expects them in milliseconds since 1970: ```python tres = precios.index[:3] print(tres.astype("int64").tolist()) print(tres.as_unit("ns").astype("int64").tolist()) print(tres.as_unit("ms").astype("int64").tolist()) ``` ```output [1609718400000000, 1609804800000000, 1609891200000000] [1609718400000000000, 1609804800000000000, 1609891200000000000] [1609718400000, 1609804800000, 1609891200000] ``` The same January 4, 2021 becomes a number a thousand times smaller than in pandas 2. Old code that divided by `10**6` to go from nanoseconds to milliseconds now produces dates in 1970 without any error. The rule: before converting to integer, set the unit with `as_unit`. The index knows which weekday each session falls on, how many there are per year and whether there are gaps. A quick look at the data calendar before computing anything avoids surprises: ```python print(precios.index.year.value_counts().sort_index().to_dict()) print((precios.index.dayofweek >= 5).sum(), "weekend sessions") precios.loc["2025-12-23":"2025-12-29", "cierre"] ``` ```output {2021: 260, 2022: 260, 2023: 260, 2024: 262, 2025: 261} 0 weekend sessions fecha 2025-12-23 139.43 2025-12-24 139.36 2025-12-25 139.32 2025-12-26 139.23 2025-12-29 139.56 Name: cierre, dtype: float64 ``` There are no weekends, but December 25, 2025 has a close. It is not a reading error: SINT is a fictitious asset that trades every weekday, **without holidays**, as the data README says. A real market closes on Christmas, and each exchange's calendar (lesson 0.4.4) is the reference for knowing which sessions should exist. Checking the calendar is the first step of any data cleaning: a holiday with a price, or a missing session, shifts all the surrounding returns. ## Copy-on-Write: each object owns itself In pandas 2, `precios["cierre"]` could return a **view**: an object that shared memory with the `DataFrame`, so that changing it also changed the original. Other operations returned a **copy**. Which of the two depended on internal details, and the `SettingWithCopyWarning` tried, without much success, to flag the doubtful cases. pandas 3.0 removes the ambiguity with **Copy-on-Write** (CoW): the result of any indexing or method behaves like a copy. Internally it copies nothing as long as nobody writes (which is why it is fast); the real copy is made at the moment of the first write: ```mermaid flowchart LR A[precios] --> B[cierre] B -->|read| C[Shared memory] A -->|read| C B -->|write| D[Copy of cierre] ``` In practice, there is only one way to change an object: write on that object directly. ```python df = precios.copy() col = df["cierre"] col.iloc[0] = 0.0 # written to the extracted Series print(col.iloc[0], df["cierre"].iloc[0]) ``` ```output 0.0 99.14 ``` `col` changes and `df` does not. In pandas 2 without CoW, this same cell would have put a zero in the first close of `df`: a price of zero that nobody wrote on purpose and that later shows up as a 100% drop in a backtest. The other side is **chained assignment**: two indexing steps in a row and an assignment at the end. It is the most common mistake when building a signal column, and in pandas 3.0 it never works: ```python import warnings df["senal"] = 0 with warnings.catch_warnings(record=True) as avisos: warnings.simplefilter("always") df["senal"][df["cierre"] > 120] = 1 # chained print([a.category.__name__ for a in avisos]) print("With chained assignment:", df["senal"].sum()) df.loc[df["cierre"] > 120, "senal"] = 1 # a single step print("With .loc:", df["senal"].sum()) ``` ```output ['ChainedAssignmentError'] With chained assignment: 0 With .loc: 523 ``` `df["senal"]` returns an intermediate object that behaves like a copy; the assignment is made on it and is lost. pandas detects it and emits the `ChainedAssignmentError` warning (in the cell it is captured with `warnings` to show only its name; in a notebook the full message appears). The `.loc` version states rows and column in a single operation on `df`, and it is the only one that changes the `DataFrame`: 523 sessions with a close above 120. | Pattern | pandas 2 | pandas 3.0 | |---|---|---| | `df["c"][mascara] = v` | Changed `df` (sometimes with a warning) | Changes nothing; `ChainedAssignmentError` | | `s = df["c"]; s.iloc[0] = v` | Could change `df` | Only changes `s` | | `df.loc[mascara, "c"] = v` | Changes `df` | Changes `df` | | `sub = df[mascara].copy()` | `.copy()` to silence the warning | `.copy()` unnecessary | ## The str type and PyArrow Up to pandas 2, a text column had the `object` type: an array of pointers to Python strings, each in its own place in memory, just like a list. pandas 3.0 infers by default a text type of its own, **`str`**, stored with PyArrow when it is installed (in the course project it is, because Parquet uses it). The fictitious trade log has two text columns: ```python ops = pd.read_csv("operaciones_sint.csv", parse_dates=["fecha_entrada", "fecha_salida"]) print(ops.dtypes) print(ops["lado"].dtype.storage, ops["lado"].dtype.na_value) ops.iloc[:3, :6] ``` ```output id int64 fecha_entrada datetime64[us] fecha_salida datetime64[us] simbolo str lado str cantidad int64 precio_entrada float64 precio_salida float64 comision float64 dtype: object pyarrow nan id fecha_entrada fecha_salida simbolo lado cantidad 0 1 2021-03-04 2021-04-26 ORO corto 200 1 2 2021-09-02 2021-10-12 SINT largo 180 2 3 2021-10-27 2021-12-08 SINT corto 10 ``` `simbolo` (symbol) and `lado` (side, with the values `largo` for long and `corto` for short) are `str`, with `pyarrow` storage and `NaN` as the missing value (the same as in the numeric columns). The entry and exit dates, like the price index, are `datetime64[us]`. The change is intentional and breaks a very widespread pattern: finding text columns by comparing their type with `object`. ```python lado = ops["lado"] print(lado.dtype == object) print(lado.dtype == "str") print(pd.api.types.is_string_dtype(lado)) print(ops.select_dtypes(include="str").columns.tolist()) ``` ```output False True True ['simbolo', 'lado'] ``` `dtype == object` returns `False` without an error: code that cleaned text columns this way simply stops finding them. The two robust forms are `pd.api.types.is_string_dtype`, which accepts the old and the new type, and `select_dtypes(include="str")`. `select_dtypes(include="object")` still includes text columns for compatibility, but with a warning that it will stop doing so. The `str` type is also stricter: it only accepts strings or missing values. A number in the `lado` column was possible with `object` and was a latent bug; now it is rejected on write: ```python raises ops.loc[0, "lado"] = 1 ``` ```output TypeError: Invalid value '1' for dtype 'str'. Value should be a string or missing value, got 'int' instead. ``` Text operations are still in the `.str` accessor (`ops["simbolo"].str.upper()`, `.str.startswith("l")`) and comparisons (`ops["lado"] == "largo"`) work the same way. With PyArrow, in addition, the column takes less memory and those operations are usually faster than with `object`: in `ops`, `lado` takes 520 bytes as `str` and 2,160 as `object` (`memory_usage(deep=True)`). In tables with millions of orders, the difference shows. ## Expressions with pd.col() To create derived columns, `assign` used to accept values or functions that receive the `DataFrame` itself (`lambda d: d["cierre"] / ...`). pandas 3.0 adds **`pd.col("name")`**: a reference to a column that can be combined with operators and with `Series` methods, and that pandas evaluates on the `DataFrame` where it is used. It is the same idea as Polars expressions (lesson 0.5.4): ```python barras = precios.assign( r=pd.col("cierre").pct_change(), rango=(pd.col("maximo") - pd.col("minimo")) / pd.col("cierre"), ) print(pd.col("cierre").pct_change()) barras[["cierre", "r", "rango"]].head(3).round(4) ``` ```output col('cierre').pct_change() cierre r rango fecha 2021-01-04 99.14 NaN 0.0158 2021-01-05 99.25 0.0011 0.0117 2021-01-06 97.38 -0.0188 0.0192 ``` Printed, `pd.col("cierre").pct_change()` is neither a number nor a column: it is an **expression** waiting to be evaluated. That is why it can be stored in a variable and reused. In the trade log, the sign of each trade (1 for `largo`, −1 for `corto`) is used to compute its result net of commissions: $$ \text{pnl} = \text{sign} \cdot (P_{\text{exit}} - P_{\text{entry}}) \cdot \text{quantity} - \text{commissions} $$ ```python signo = pd.col("lado").map({"largo": 1, "corto": -1}) ops = ops.assign( pnl=signo * (pd.col("precio_salida") - pd.col("precio_entrada")) * pd.col("cantidad") - pd.col("comision"), ) print(f"Total result: {ops['pnl'].sum():,.2f}") ops.loc[pd.col("pnl") < -1000, ["id", "simbolo", "lado", "pnl"]] ``` ```output Total result: 1,149.19 id simbolo lado pnl 0 1 ORO corto -3123.56 7 8 SINT largo -1288.04 14 15 SINT largo -1058.60 ``` `pd.col` works anywhere that accepts a function of the `DataFrame`: `assign`, `loc` and bracket selection. In `loc`, the expression `pd.col("pnl") < -1000` returns the mask of the trades with a loss greater than 1,000 in the asset's currency: three of the forty, one in `ORO` and two in SINT. The log as a whole makes 1,149.19 net of commissions. The advantage over `df["pnl"] < -1000` shows up when chaining methods: the expression refers to the `DataFrame` of the step in which it is evaluated, not to a variable that may have become stale. ## Exercises 1. This code tries to mark with a 1 in `alerta` the SINT sessions with a volume greater than twice the median, but the column stays at zero. Explain why and fix it. ```python skip v = precios.copy() v["alerta"] = 0 v[v["volumen"] > 2 * v["volumen"].median()]["alerta"] = 1 ``` **Solution.** ```python v = precios.copy() v["alerta"] = 0 v.loc[v["volumen"] > 2 * v["volumen"].median(), "alerta"] = 1 print(v["alerta"].sum(), "sessions with abnormal volume") ``` ```output 51 sessions with abnormal volume ``` `v[mascara]` returns a new `DataFrame` that behaves like a copy; `["alerta"] = 1` modifies that one and is lost. With `.loc[mascara, "alerta"]`, rows and column are stated in a single operation on `v`, and 51 sessions are marked. 2. With `pd.col()`, add to `precios` a column `hueco` with the opening *gap* (today's open divided by yesterday's close, minus one) and select with `loc` the 2022 sessions that opened more than 1% below the previous close. **Solution.** ```python gaps = precios.assign( hueco=pd.col("apertura") / pd.col("cierre").shift(1) - 1, ) bajistas = gaps.loc["2022"].loc[pd.col("hueco") < -0.01] print(len(bajistas), "bearish gaps of more than 1%") bajistas[["apertura", "cierre", "hueco"]].round(4) ``` ```output 3 bearish gaps of more than 1% apertura cierre hueco fecha 2022-08-22 109.40 108.59 -0.0127 2022-08-29 111.41 115.26 -0.0108 2022-09-02 113.75 117.11 -0.0109 ``` `shift(1)` moves the closes forward one session, so each row compares its open with the previous close: information available at the moment of the open, with no look-ahead bias. Three sessions come out, all between late August and early September. The first `loc` selects the year and the second filters with the expression, which is evaluated on the already trimmed `DataFrame`. 3. Write a function `columnas_texto(df)` that returns the names of the text columns of a `DataFrame` and that works the same with `str` columns and with pandas 2 `object` columns. Test it with `ops` and with `ops.astype({"lado": object})`. **Solution.** ```python def columnas_texto(df: pd.DataFrame) -> list[str]: return [c for c in df.columns if pd.api.types.is_string_dtype(df[c])] print(columnas_texto(ops)) print(columnas_texto(ops.astype({"lado": object}))) ``` ```output ['simbolo', 'lado'] ['simbolo', 'lado'] ``` `is_string_dtype` recognizes the `str` type and also an `object` column whose values are strings, so the function gives the same result with both types. Comparing with `object` would only find the second one. ## Self-assessment 1. In pandas 3.0, `df[df["cierre"] > 120]["senal"] = 1`. What happens to `df`? - A. `senal` is set to 1 on the days with a close above 120 - B. It raises an exception and the program stops - C. Nothing: the assignment is made on an intermediate object that behaves like a copy, and pandas warns with `ChainedAssignmentError` - D. All rows are set to 1 *Answer C: Nothing: the assignment is made on an intermediate object that behaves like a copy, and pandas warns with `ChainedAssignmentError`.* With Copy-on-Write, the result of any indexing behaves like a copy. Chained assignment never reaches the original; the correct form is `df.loc[df["cierre"] > 120, "senal"] = 1`. 2. A `lado` column with “largo” (long) and “corto” (short) is read with pandas 3.0 and PyArrow installed. What does `df["lado"].dtype == object` return? - A. `False`: the column has the `str` type - B. `True`, as in pandas 2 - C. A type error - D. It depends on the content of the first row *Answer A: `False`: the column has the `str` type.* pandas 3.0 infers the `str` type for text. Checks against `object` stop finding those columns; the robust option is `pd.api.types.is_string_dtype`. 3. You add the closes of two assets with different calendars: `a + b`. What happens to the dates that are only in one of the two? - A. They are added by position, ignoring the dates - B. They appear in the result with `NaN` - C. They are silently dropped - D. They are filled with the last known price *Answer B: They appear in the result with `NaN`.* pandas aligns by index: the result has the union of the dates, and where one of the two operands is missing the value is `NaN`. Filling or dropping is an explicit decision. 4. In pandas 3.0, `pd.read_csv(..., parse_dates=["fecha"])` creates `datetime64[us]` dates. What does `index.astype("int64")` return? - A. Nanoseconds since 1970, as in pandas 2 - B. Seconds since 1970 - C. Days since 1970 - D. Microseconds since 1970: numbers 1,000 times smaller than in pandas 2 *Answer D: Microseconds since 1970: numbers 1,000 times smaller than in pandas 2.* The default resolution when parsing text becomes the microsecond. To get another unit you have to request it with `as_unit("ns")` or `as_unit("ms")` before converting to integer. ## Key points - A `Series` is a column with an index; a `DataFrame`, several columns sharing an index. Every operation between them aligns by index, not by position. - With a `DatetimeIndex`, `loc["2022-03"]` selects a month and `loc["2022-06-01":"2022-06-07"]` a range with both ends included. - Copy-on-Write: any indexing behaves like a copy. To change a `DataFrame`, assign on it in a single step with `.loc`; chained assignment does nothing. - Text has the `str` type (PyArrow underneath): `dtype == object` checks fail silently and the column only accepts strings or `NaN`. - `pd.col("cierre")` builds an expression that `assign` and `loc` evaluate on the `DataFrame` itself, without a `lambda`. ## Resources ### Documentation - [pandas 3.0.0: What's new](https://pandas.pydata.org/docs/whatsnew/v3.0.0.html) · pandas.pydata.org - [pandas: Copy-on-Write (user guide)](https://pandas.pydata.org/docs/user_guide/copy_on_write.html) · pandas.pydata.org - [pandas: migrating to the pandas 3.0 string type](https://pandas.pydata.org/docs/user_guide/migration-3-strings.html) · pandas.pydata.org - [pandas: time series (user guide)](https://pandas.pydata.org/docs/user_guide/timeseries.html) · pandas.pydata.org ### Books - [Wes McKinney, Python for Data Analysis, 3rd ed. (O'Reilly, 2022; open access edition)](https://wesmckinney.com/book/) · wesmckinney.com ### Data - [Level 0 data: fictitious trades (CSV)](https://pythonparatrading.com/curso/datos/nivel-0/operaciones_sint.csv) · Download · operaciones_sint.csv Previous lesson: [0.5.1 · NumPy 2: arrays, broadcasting, vectorization, linear algebra and random generators](https://pythonparatrading.com/en/python-trading-course/level-0/numpy-arrays-and-vectorization/) Next lesson: [0.5.3 · Selection, filtering, groupby, merge, pivot, resample and moving windows](https://pythonparatrading.com/en/python-trading-course/level-0/pandas-groupby-merge-resample-rolling/)