---
title: "pandas"
subtitle: "Lecture 08"
author: "Dr. Colin Rundel"
footer: "Sta 523 - Fall 2026"
format:
  revealjs:
    theme: slides.scss
    transition: fade
    slide-number: true
    self-contained: true
execute:
  echo: true
  warning: true
engine: knitr
---


```{r setup}
#| message: false
#| warning: false
#| include: false

options(width = 80)

if (!file.exists("data/flights.parquet")) {
  nanoparquet::write_parquet(nycflights13::flights, "data/flights.parquet")
}
if (!file.exists("data/flights.csv")) {
  readr::write_csv(nycflights13::flights, "data/flights.csv")
}
```

```{python py_setup}
#| include: false
import numpy as np
import pandas as pd

pd.set_option("display.width", 80)
pd.set_option("display.max_rows", 8)
pd.set_option("display.min_rows", 4)
```

# NumPy dtypes

## dtype

Every NumPy array stores elements of a single type, its `dtype`. As with R's atomic vectors the type is inferred at creation and mixed values are coerced, but NumPy has many more types and each has an explicit size - `int8` to `int64`, `uint8` to `uint64`, `float16` to `float64`, `bool`, `complex128`, etc.

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
np.array([1, 2, 3]).dtype
np.array([1.5, 2]).dtype
np.array([True, False]).dtype
np.array(["a", "bcd"]).dtype
```
:::

::: {.column width='50%' .fragment}
```{python}
np.array([1, 2.5, True]).dtype
np.array([1, 2, 3], dtype="float32")
np.array([1.7, 2.2]).astype("int64")
```
:::
::::

::: {.aside}
`<U3` is a fixed-width unicode string of at most 3 characters - see the [docs](https://numpy.org/doc/stable/user/basics.types.html) for the full list of dtypes.
:::


## dtypes are fixed

The dtype is set when the array is created and does not change. Where an R vector is promoted to fit what is assigned into it, a NumPy array converts the value to its own dtype. 

:::: {.columns .xsmall}
::: {.column width='50%'}
```{r}
x = c(1L, 2L, 3L)
typeof(x)
x[1] = 2.9
x
typeof(x)
```
:::

::: {.column width='50%' .fragment}
```{python}
x = np.array([1, 2, 3])
x.dtype
x[0] = 2.9
x
```
```{python}
y = np.array([100, 200], dtype="uint8")
y + y
```
:::
::::

::: {.aside}
Note that integer arithmetic that exceeds the range of the type overflows without a warning or error.
:::


# pandas

## pandas

Python has no built-in tabular data structure. pandas (2008) is an established choice - it grew out of NumPy and borrows heavily from R.

::: {.small}
```{python}
import pandas as pd
pd.__version__
```
:::

. . .

Three classes do the heavy lifting:

* `Series` - a 1d typed array with an index (a column)

* `Index` - the labels attached to rows and columns

* `DataFrame` - a collection of `Series` sharing an index (a table)




::: {.aside}
pandas 3.0 (released early 2026) changed some long standing defaults - strings have their own dtype and copy-on-write is always on. Older tutorials and answers will show different behavior.
:::


## Series

A `Series` is a 1d array with a dtype together with an *index* of labels - the closest thing in R is a named vector. The difference is that a Series always has an index (integer positions by default) and the index participates in lookup, alignment, and joins rather than being decoration/annotation.

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
s = pd.Series([4, 2, 1, 3])
```
```{python}
s
s.index
```
:::

::: {.column width='50%' .fragment}
```{python}
pd.Series(
  [4, 2, 1, 3],
  index=["a", "b", "c", "d"]
)
```
```{python}
pd.Series({"a": 4, "b": 2}).index
```
:::
::::



## dtypes

pandas dtypes come from NumPy (`int64`, `float64`, `bool`, `datetime64`) plus extension dtypes (`str`, `category`, `Int64`, ...).

For mixtures such as numbers and strings, pandas falls back to `object` rather than coercing everything as R does. An object Series still supports elementwise operations, but storing arbitrary Python objects usually costs more memory and computation.

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
pd.Series([1, 2, 3]).dtype
pd.Series([1.5, 2]).dtype
pd.Series([True, False]).dtype
pd.Series(["a", "b"]).dtype
```
:::

::: {.column width='50%' .fragment}
```{python}
pd.Series([1, "a", True])
pd.Series([1, "a", True]).dtype
pd.Series(["1", "2"]).astype("int64")
```
:::
::::


## Labels vs positions

::: {.msmall}
A pandas index can hold integers, strings, etc. A scalar `s[key]` looks up a label, even when the key is an integer. Use `.iloc` for positions and `.loc` for labels.
:::


::: {.xsmall}
```{python}
t = pd.Series({"a": 4, "b": 2, "c": 1, "d": 3})
u = pd.Series([4, 2, 1, 3], index=[3, 2, 1, 0])
```
:::

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
#| error: true
t["b"]
t[1]
```
```{python}
t.iloc[1]
```
:::

::: {.column width='50%' .fragment}
```{python}
#| error: true
u["b"]
u[1]
u.iloc[1]
```
:::
::::

## Slices: labels vs positions

`.loc` label slices include both endpoints; `.iloc` position slices exclude the stop. Integer slices in `s[...]` are positional, unlike scalar integer lookups.

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
t.loc["b":"c"]
t.iloc[1:3]
```
:::

::: {.column width='50%' .fragment}
```{python}
u[1:3]
u.loc[3:1]
```
:::
::::

## Index alignment

Arithmetic between Series aligns on index rather than position. The result spans the union of both indexes with `NaN` for labels missing on one side. Assigning a Series to a column in a DataFrame aligns the same way.

::: {.xsmall}
```{python}
m = pd.Series([1, 2, 3], index=["a", "b", "c"])
n = pd.Series([10, 20, 30], index=["c", "b", "a"])

```
:::

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
m + n
m + n[["a", "c"]]
```
:::

::: {.column width='50%' .fragment}
```{python}
d = pd.DataFrame(
  {"x": [1, 2, 3]}, 
  index=["a", "b", "c"]
)
d["y"] = n[["a", "c"]]
d
```
:::
::::


## DataFrame

A `DataFrame` is a dict-like collection of Series sharing a row index, (keys are the column indexes). 

::: {.xsmall}
```{python}
penguins = pd.read_csv("data/penguins.csv"); penguins
```
:::

. . .

::: {.xsmall}
```{python}
penguins.index
penguins.columns
```
:::

## Missing values

R has typed `NA` values each atomic vector type. NumPy has no universal missing-value sentinel across dtypes: floating point arrays can use `NaN` and datetimes can use `NaT`, but integers and booleans have no equivalent. With pandas' default interface, missing integers become `float64`, while the boolean example below becomes `object` holding `None`.

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
pd.Series([1, 2, None])
pd.Series([True, False, None])
```
:::

::: {.column width='50%' .fragment}
```{python}
pd.Series(["a", None])
pd.Series(
  [pd.Timestamp("2026-09-15"), None]
)
```
:::
::::

::: {.aside}
The `str` dtype uses `NaN` for missing strings as well, which is why `None` came back as `NaN` above.
:::


## Detecting missing values

As we've seen previously, `NaN` is not equal to anything, including itself. So `==` cannot find missing values - `isna()` (and `notna()`) recognize `NaN`, `None`, `NaT`, and `pd.NA` alike. `dropna()` and `fillna()` can be used to remove or replace.

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
s = pd.Series([1, 2, None])
s == np.nan
s.isna()
```
:::

::: {.column width='50%' .fragment}
```{python}
s.isna().sum()
s.dropna()
s.fillna(0)
```
:::
::::


## Missing values in aggregations

Reductions skip missing values by default (`skipna=True`), the opposite of R's `na.rm = FALSE` default..

::: {.xsmall}
```{python}
mass = penguins["body_mass_g"]
```
:::

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
mass.mean()
mass.mean(skipna=False)
mass.count()
penguins.dropna().shape
```
:::

::: {.column width='50%' .fragment}
```{python}
penguins.isna().sum()
```
:::
::::


## Nullable dtypes

::: {.medium}
The extension dtypes `Int64`, `Float64`, `boolean`, and `string` use `pd.NA` to represent missing values without changing dtype. Only the numeric names here are capitalized. These types are opt in, via `dtype=`, `dtype_backend=` when reading, or `convert_dtypes()`. Arithmetic and comparisons use three valued logic - `pd.NA == 1` is `<NA>` rather than `False`.
:::

::: {.xsmall}
```{python}
t = pd.Series([1, 2, None], dtype="Int64")
```
:::

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
t
t > 1
```
:::

::: {.column width='50%' .fragment}
```{python}
pd.NA == 1
pd.NA | True
pd.NA & True
```
```{python}
#| error: true
bool(pd.NA)
```
:::
::::

::: {.aside}
So a pandas column can hold `NaN`, `None`, `NaT`, or `pd.NA` depending on its dtype - `isna()` treats all of them as missing, which is why it should be used over `==`.
:::


## Subsetting with `[]`

`df[...]` is overloaded on the type of its argument - a string picks a column, a list of strings a set of columns, and a slice or boolean Series picks rows. There is no two argument `df[i, j]` form.

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
penguins["species"]
```
:::

::: {.column width='50%' .fragment}
```{python}
penguins[["species", "island"]]
```
:::
::::

::: {.fragment .xsmall}
```{python}
penguins[penguins["body_mass_g"] > 6000]
```
:::

::: {.aside}
Note the inversion from R - `df["x"]` returns a Series (R's `df[["x"]]`) and `df[["x"]]` a DataFrame (R's `df["x"]`).
:::


## `.loc` and `.iloc`

Two dimensional subsetting like R's `df[i, j]` goes through `.loc` (labels) and `.iloc` (positions), each taking `[rows, cols]`. Label slices include the stop; position slices exclude it. Both accept boolean arrays, but an indexed boolean Series belongs with `.loc`, not `.iloc`.

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
penguins.loc[0:2, "species":"island"]
```
:::

::: {.column width='50%' .fragment}
```{python}
penguins.iloc[0:2, 0:2]
```
:::
::::

::: {.fragment .xsmall}
```{python}
penguins.loc[penguins["species"] == "Gentoo", ["species", "body_mass_g"]]
```
:::


## Combining conditions

Use `&` for elementwise AND, `|` for OR, and `~` for NOT. Parenthesize comparisons; Python's `and` / `or` do not combine Series. `.isin()` tests membership in a collection.

::: {.xsmall}
```{python}
mask = (
  penguins["species"].isin(["Adelie", "Gentoo"])
  & (penguins["body_mass_g"] > 5000)
)
penguins.loc[mask, ["species", "body_mass_g"]].head(3)
```
:::

::: {.fragment .xsmall}
```{python}
penguins.loc[
  (penguins["species"] == "Gentoo")
  | (penguins["body_mass_g"] > 5000),
  ["species", "body_mass_g"]
].head(3)
```
:::


## Rows are Series too

Selecting a single row returns a Series, and a Series has one dtype - so a heterogeneous row is upcast to `object`. R keeps a single row as a data frame for exactly this reason.

::: {.xsmall}
```{python}
cols = ["species", "year"]
```
:::

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
penguins.loc[0, cols]
```
:::

::: {.column width='50%' .fragment}
```{python}
r = penguins.loc[[0], cols]
r
r.dtypes
```
:::
::::


## Mutation

DataFrames are mutable - `df["z"] = ...` changes the object in place, and any other name bound to the same object sees the change (reference semantics). This is unlike R's copy-on-modify, where the same code would leave `d` untouched. `copy()` makes an independent data frame, and methods such as `assign()` return a new one.

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
d = pd.DataFrame({"x": [1, 2, 3]})
d2 = d
d2["y"] = d2["x"] * 2
d
```
:::

::: {.column width='50%' .fragment}
```{python}
d3 = d.copy()
d3["z"] = 1
d
```
:::
::::

::: {.aside}
pandas 3.0 enforces copy-on-write: assigning through a subset does not update its source. Binding two names to the same DataFrame still creates an alias. This protection does not recursively copy mutable Python objects stored inside object columns.
:::


## Updating selected rows

Use a single `.loc[rows, column] = value` assignment on the frame you want to change. Chained assignment such as `df["x"][mask] = value` cannot update the original frame under copy-on-write.

::: {.xsmall}
```{python}
updated = penguins.copy()
updated["size"] = "other"
updated.loc[updated["body_mass_g"] > 5000, "size"] = "large"
updated.loc[updated["size"] == "large", ["body_mass_g", "size"]].head(3)
```
:::


## Referring to columns

dplyr's `filter(df, x > 1)` works because R captures the unevaluated expression. Python evaluates arguments eagerly and a bare `x` is a `NameError`, so pandas offers several workarounds, none of which are quite as convenient.

::: {.xsmall}
```{python}
pg = penguins[["species", "island", "body_mass_g"]]
```
:::

:::: {.columns}
::: {.column width='50%'}
Explicit - reuse the object,

::: {.xsmall}
```{python}
pg[pg["body_mass_g"] > 6000]
```
:::
Callable - a function / lambda

::: {.xsmall}
```{python}
pg[lambda d: d["body_mass_g"] > 6000]
```
:::
:::

::: {.column width='50%' .fragment}
String - DSL parsed by pandas,

::: {.xsmall}
```{python}
pg.query("body_mass_g > 6000")
```
:::
Expression - `pd.col()`,

::: {.xsmall}
```{python}
pg[pd.col("body_mass_g") > 6000]
```
:::
:::
::::

::: {.aside}
`pd.col()` is borrowed from polars (next lecture) and is new in pd 3.0.
:::


## Method chaining

There is no pipe operator, but since nearly every method returns a new DataFrame the equivalent is method chaining. Callables and `pd.col()` are what let a step refer to columns of the intermediate result. Subsetting with `[]` can be a step in the chain like any other.

::: {.xsmall}
```{python}
(penguins
  .query("species == 'Gentoo'")
  .assign(kg = pd.col("body_mass_g") / 1000)
  .sort_values("kg", ascending=False)
  [["species", "island", "kg"]]
  .head(3)
)
```
:::

::: {.aside}
`.pipe(f, ...)` calls `f(df, ...)` as a step in the chain, for anything that is not already a method.
:::


## groupby

Split-apply-combine works the same way - `groupby()` returns a `DataFrameGroupBy` which is then used by subsequent aggregation calls. By default the group keys become the *index* of the result (`as_index=False` keeps them as columns, like `.by`). Named aggregations use `(column, function)` pairs.

::: {.xsmall}
```{python}
g = penguins.groupby("species")
type(g)
```
:::

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}

g["body_mass_g"].mean()
```
:::

::: {.column width='50%' .fragment}
```{python}
(penguins
  .groupby("species", as_index=False)
  .agg(
    n = ("species", "size"),
    mass = ("body_mass_g", "mean")
  )
)
```
:::
::::


## Grouped transforms

`transform()` returns a result the same length as its input, broadcasting each group's value back over that group's rows - the equivalent of `mutate()` with `.by`. Since the result shares the frame's index it can be used directly in arithmetic or assignment.

::: {.xsmall}
```{python}
avg = g["body_mass_g"].transform("mean")
avg.head(3)
```
:::

. . .

::: {.xsmall}
```{python}
penguins.assign(rel = pd.col("body_mass_g") / avg)[["species", "rel"]].head(3)
```
:::

::: {.aside}
`g.filter(f)` keeps whole groups for which `f(group)` is true, the analog of `filter(n() > 10, .by = g)`.
:::


## MultiIndex

Group by two keys and the result has a hierarchical row `MultiIndex`; aggregate a column two ways with the dictionary/list syntax and the columns get one too. The tidyverse usually keeps group keys and summaries as ordinary columns.

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
(penguins
  .groupby(["species", "island"])
  ["body_mass_g"].mean()
)
```
:::

::: {.column width='50%' .fragment}
```{python}
(penguins
  .groupby("species")
  .agg({
    "body_mass_g": ["mean", "max"]
  })
)
```
:::
::::

::: {.aside}
`reset_index()` moves row index levels into columns, but does not flatten hierarchical columns. Use `as_index=False` plus named aggregations to avoid both. Hierarchical column lookup uses tuples, e.g. `res[("body_mass_g", "max")]`.
:::


## Exercise 1

Using pandas and `data/flights.parquet`, answer the following. Questions 1 and 2 are the core; 3 and 4 are extensions if time allows.

1. How many flights to LAX did each legacy carrier (AA, UA, DL, US) have in May from JFK, and what was their mean actual air time (`air_time`)? Report only carriers with matching records.

2. Which plane (`tailnum`) has the most flight records from each New York airport? Exclude missing tail numbers and return all ties.

3. Which five calendar dates (`year`, `month`, `day`) had the lowest mean departure delay? Skip missing delays and break ties by earliest date.

4. Which flight has the largest arrival delay as a percentage of its actual air time (`100 * arr_delay / air_time`)? Exclude missing values and nonpositive air times, and return all ties.


# Summary {visibility="uncounted"}

## R vs pandas {visibility="uncounted"}

::: {.small}
| Concept            | R / tibble                     | pandas                                    |
|:-------------------|:-------------------------------|:------------------------------------------|
| column             | atomic vector                  | `Series` (values + index)                 |
| row labels         | `row.names`, rarely used       | `Index`, central                          |
| alignment          | by position, with recycling    | by index label                            |
| mixed types        | coerced to a common type       | `object` dtype                            |
| missing values     | `NA` in any type               | `NaN` / `None` / `NaT` / `pd.NA` by dtype |
| column references  | data masking                   | strings, callables, `pd.col()`            |
| mutation           | copy-on-modify                 | in place, copy-on-write for subsets       |
| grouped result     | keys as columns                | keys as (Multi)Index                      |
:::


## Verbs {visibility="uncounted"}

::: {.small}
| dplyr                              | pandas                                   |
|:-----------------------------------|:-----------------------------------------|
| `filter(x > 1)`                    | `query("x > 1")`, `[pd.col("x") > 1]`    |
| `select(x, y)`                     | `[["x", "y"]]`, `filter(regex=)`         |
| `mutate(z = x * 2)`                | `assign(z = pd.col("x") * 2)`            |
| `arrange(desc(x))`                 | `sort_values("x", ascending=False)`      |
| `summarize(m = mean(x))`           | `agg(m = ("x", "mean"))`                 |
| `group_by(g)`                      | `groupby("g", as_index=False)`           |
| `mutate(m = mean(x), .by = g)`     | `groupby("g")["x"].transform("mean")`    |
| `across(where(is.numeric), f)`     | `select_dtypes("number").apply(f)`       |
| `rename(new = old)`                | `rename(columns={"old": "new"})`         |
:::


## Takeaways {visibility="uncounted"}

::: {.medium}
* A pandas Series is a typed array plus an index, and the index is what makes pandas different from R - lookup is by label, arithmetic and assignment align on labels, and group keys end up in the index. `.loc` and `.iloc` exist because `[]` cannot tell labels from positions.

* pandas dtypes are messier than R's - mixed data becomes `object`, and missing values are `NaN`, `None`, `NaT`, or `pd.NA` depending on the dtype, with `NaN` silently turning integer columns into floats. Use `isna()`, never `==`, and remember that aggregations skip missing values by default.

* Python has no data masking, so pandas refers to columns with strings, callables, or `pd.col()` and chains methods instead of piping. DataFrames are mutable, so aliases matter.
:::