Module 2: pandas Basics

class: center, middle, inverse, title-slide

.title[
# Module 2: pandas Basics
]
.subtitle[
## The dplyr → pandas translation
]

---

# Course Map

<table>
<tr><th>#</th><th>Module</th><th>Status</th></tr>
<tr><td>1</td><td><a href="../module-01/slides.html">Python for R Users</a></td><td>✓ done</td></tr>
<tr><td><b>2</b></td><td><b>pandas basics</b> <i>(you are here)</i></td><td>← current</td></tr>
<tr><td>3</td><td><a href="../module-03/slides.html">Joins, merges, group-by recipes</a></td><td>✓ done</td></tr>
<tr><td>4</td><td><a href="../module-04/slides.html">Regression and A/B tests with statsmodels</a></td><td>✓ done</td></tr>
<tr><td>5</td><td><a href="../module-05/slides.html">End-to-end interview scenario</a></td><td>✓ done</td></tr>
<tr><td>6</td><td><a href="../module-06/slides.html">Reading and Reviewing Someone Else's Python</a></td><td>✓ done</td></tr>
<tr><td>7</td><td><a href="../module-07/slides.html">Loading, Running SQL and Checking a Reproduction</a></td><td>✓ done</td></tr>
</table>

---

# Loading Data

```python
import pandas as pd
import numpy as np

rides = pd.read_csv("data/rides.csv", parse_dates=["pickup_at"])

rides.head()
rides.shape          # (30000, 9) — like dim() in R
rides.columns        # Index(['ride_id', 'rider_id', ...])
rides.dtypes         # column types
```

`parse_dates=` is essential. Without it, date columns are strings and `.dt.year` errors out.

---

# The dplyr → pandas Cheat Sheet

| What | dplyr | pandas |
|---|---|---|
| Filter | `filter(df, fare > 10)` | `df[df["fare"] > 10]` |
| Select | `select(df, city, fare)` | `df[["city", "fare"]]` |
| Mutate | `mutate(df, rate = fare/dist)` | `df["rate"] = df["fare"]/df["dist"]` |
| Sort | `arrange(df, desc(fare))` | `df.sort_values("fare", ascending=False)` |
| Summarise | `summarise(df, avg = mean(fare))` | `df["fare"].mean()` |
| Group + summarise | `group_by(df, city) %>% summarise(...)` | `df.groupby("city")["fare"].mean()` |
| Distinct | `distinct(df, id)` | `df.drop_duplicates("id")` |

In dplyr, verbs are free functions in a pipe. In pandas, **everything is a method on the DataFrame** — you chain with dots instead of `|>`.

---

# Filter

```python
# Single condition
rides[rides["fare_usd"] > 20]

# Multiple — use & and |, NOT and/or
rides[(rides["fare_usd"] > 20) & (rides["distance_mi"] < 5)]

# isin (matches dplyr's %in%)
rides[rides["city"].isin(["SF", "Chicago"])]

# Negation
rides[~rides["city"].isin(["SF"])]

# Query method (string-based, often cleaner)
rides.query("fare_usd > 20 and distance_mi < 5")
```

**The parentheses around each `&` condition are required.** Forgetting them is the most common pandas mistake.

---

# Mutate (assign)

```python
# Direct (mutates in place)
rides["fare_per_mile"] = rides["fare_usd"] / rides["distance_mi"]

# .assign() — closer to dplyr style, returns a new DataFrame
rides = rides.assign(
    fare_per_mile = rides["fare_usd"] / rides["distance_mi"],
    is_long_trip  = rides["distance_mi"] > 10,
    log_fare      = np.log(rides["fare_usd"])
)

# Conditional with np.where
rides["tier"] = np.where(rides["fare_usd"] > 30, "premium", "standard")
```

`assign()` is the dplyr-est way. Direct assignment is shorter but mutates in place.

---

# Group By + Summarise

```python
# One group, one stat — returns a Series
rides.groupby("city")["fare_usd"].mean()

# Multiple stats with named output (the dplyr-est syntax)
rides.groupby("city").agg(
    avg_fare    = ("fare_usd", "mean"),
    n_rides     = ("ride_id", "count"),
    p90_fare    = ("fare_usd", lambda x: x.quantile(0.9))
)

# To get a DataFrame back instead of grouped index
rides.groupby("city", as_index=False)["fare_usd"].mean()
```

**The `name = (column, function)` syntax** in `.agg()` is the closest pandas gets to `summarise(avg_fare = mean(fare_usd), ...)`.

---

# Date / Time Operations

Once parsed, every datetime column has a `.dt` accessor:

```python
rides["pickup_at"].dt.year
rides["pickup_at"].dt.month
rides["pickup_at"].dt.day_name()
rides["pickup_at"].dt.hour
rides["pickup_at"].dt.dayofweek      # 0 = Monday
```

Filter on dates:

```python
rides[rides["pickup_at"] >= "2025-12-01"]
```

Resample to a frequency:

```python
rides.set_index("pickup_at").resample("D")["fare_usd"].sum()    # daily
rides.set_index("pickup_at").resample("W")["fare_usd"].mean()   # weekly
```

---

# Pivot

```python
# Long → wide
hourly = (
    rides
    .assign(hour = rides["pickup_at"].dt.hour)
    .groupby(["city", "hour"])
    .size()
    .reset_index(name="n_rides")
)

wide = hourly.pivot(index="city", columns="hour", values="n_rides")
```

```python
# Wide → long
long = wide.reset_index().melt(
    id_vars="city", var_name="hour", value_name="n_rides"
)
```

These are `tidyr::pivot_wider` and `pivot_longer`.

---

# Common Traps

**1. Operator precedence in boolean filters.** Always parenthesize each condition before `&` / `|`.

**2. `SettingWithCopyWarning`.** Use `.loc[row_selector, col_selector]` to modify a subset, never chained `[]` indexing.

**3. NaN handling.** `df["col"] == np.nan` is always False. Use `df["col"].isna()`.

**4. The default index sticks around.** If you filter or group, the row index can become non-contiguous. Use `df.reset_index(drop=True)` for a fresh integer index.

---
name: wild-table

# In the Wild: Filter, Stable Sort, and a Summary Row

Real code from the BSZ reproduction ([`fhoces/opa-prop40`](https://github.com/fhoces/opa-prop40), `bsz-analysis/py/`, see module 1). Two table builders:

```python
# figures.py: keep the rows with a positive total, largest first
d = d[d["total"].notna() & (d["total"] > 0)]
# R: d[order(d$total, decreasing = TRUE), ]  (stable for ties)
d = d.sort_values("total", ascending=False, kind="stable")

# tables.py: a per-year panel, then one average row appended under it
panel["ca_inctax_per_wealth"] = panel["ca_inctax_b"] / panel["wealth_b"]
avg_row = {"year": "2019-2025 average",
           "wealth_b": _rmean(panel["wealth_b"]),
           "ca_inctax_b": _rmean(panel["ca_inctax_b"])}
pd.concat([panel, pd.DataFrame([avg_row])], ignore_index=True)
```

- `.notna()`, never `!= np.nan` (trap 3). Each condition in parentheses (trap 1).
- **`kind="stable"`.** pandas' default sort may reorder tied rows; R's `order()` never does. When a parity test compares row by row, the order within ties is part of the answer.
- `pd.DataFrame([avg_row])` is a one-row frame from a dict; `concat` with `ignore_index=True` is `bind_rows(panel, tibble(...))`. The `year` column holds `"2019"` and a label, so the panel stores it as text from the start.

<a href="https://fhoces.github.io/opa-prop40/bsz-analysis/site/repro.html#a.-short-run-growth-table-1-panel-a-and-figure-1" class="nav-btn">report: Figure A1 (figures.py) →</a>
<a href="https://fhoces.github.io/opa-prop40/bsz-analysis/site/repro.html#a.-california-income-tax-table-2-figure-3-and-table-3" class="nav-btn-br">report: Table 2 (tables.py) →</a>

---
name: wild-numpy

# In the Wild: numpy for R's Vector Idioms

`compute_pareto.py` reproduces a spreadsheet column by column. Its R-to-numpy dictionary:

```python
next_C = np.append(C[1:], 0.0)                   # R: c(x[-1], 0)   shift up, pad with 0
wealth_in_bracket = C - next_C
with np.errstate(divide="ignore", invalid="ignore"):
    avg = wealth_in_bracket / actual_density     # 0/0 is NaN, silently, as in R
anchor = np.flatnonzero(A == anchor_threshold)   # R: which(A == 4.5)
total = np.nansum(d["wealth_in_bracket"])        # R: sum(x, na.rm = TRUE)
# R: seq(0, max_rate, by = rate_step)
n = int(max_rate / rate_step + 1e-10)
rates = np.minimum(0 + np.arange(n + 1) * rate_step, max_rate)
```

- **No `np.arange` with a float step.** `np.arange(1, 1.3, 0.1)` returns four elements, `1.3` included. Build the grid as an integer range times the step, as R's `seq()` does, or use `np.linspace(0, 0.2, 201)`.
- **Summation order.** `tables.py` has a `_rsum()` that adds left to right in a loop, because numpy's `sum()` adds pairwise and the two differ in the last digits (`356347.01` against `356347.00999999914` on this course's fares). Irrelevant for analysis, visible to a parity test at `1e-9`.
- NaN is the missing value throughout (`NA = np.nan`); `np.nansum` and `np.nanmean` are the `na.rm = TRUE` family.

<a href="https://fhoces.github.io/opa-prop40/bsz-analysis/site/repro.html#row-2-adding-the-billionaires-forbes-misses" class="nav-btn">report: the missing billionaires →</a>
<a href="https://fhoces.github.io/opa-prop40/bsz-analysis/site/repro.html#b.-a-permanent-annual-wealth-tax-the-laffer-curve" class="nav-btn-br">report: the Laffer curve →</a>

---
name: wild-grid

# In the Wild: The Explorer's Grid as a DataFrame

Exercise Q7 rebuilds the repo [explorer](https://fhoces.github.io/opa-prop40/bsz-analysis/site/explorer/)'s grid with its own function (`score_tab5_cell()`, module 1) and the loop of `build_site_grid()` in `site_exports.py`:

.small[
```python
DIALS = {"avoidance": [0, 0.05, 0.10, 0.15, 0.20, 0.30],
         "mobility_share": [0, 0.25, 0.50, 0.75, 1],
         "avoidance_small": [0.10, 0.20, 0.30, 0.50],
         "sell_share": [0, 1 / 3, 2 / 3, 1],
         "pareto": [False, True], "leavers": [False, True]}
rows = []
for values in itertools.product(*DIALS.values()):         # every combination
    setting = dict(zip(DIALS, values))                    # {"avoidance": 0, ...}
    rows.append({**setting, **score_tab5_cell(INP, **setting)})
grid = pd.DataFrame(rows)                                 # 1,920 rows: 6 x 5 x 4 x 4 x 2 x 2
```
]

- `itertools.product` is R's `expand.grid()`, except that it varies the **last** dial fastest. R's `build_site_grid()` reverses the order to match.
- A list of dicts into one `DataFrame` is R's `bind_rows()` over a list of rows.
- The rebuilt grid matches the repo's published `site/data/grid.csv` cell for cell.

<a href="https://fhoces.github.io/opa-prop40/bsz-analysis/site/repro.html#how-far-the-guesses-move-the-result" class="nav-btn">report: how far the guesses move the result →</a>

---
name: wild-grid-query

# In the Wild: Querying the Grid (Exercise Q7)

.small[
```python
grid = grid.assign(
    income_tax_loss_pv = grid["annual_ca_inctax_loss"] * PV_5Y_3PCT,
    main_estimate = lambda d: d["wealth_tax_revenue"] + d["extra_ca_inctax_sales"]
                              + d["income_tax_loss_pv"])
grid.groupby("avoidance")["main_estimate"].agg(["min", "median", "max"])
grid.nsmallest(5, "main_estimate")
# others_at_base: True where the other four dials sit at their base values
grid[others_at_base].pivot(index="avoidance", columns="mobility_share", values="main_estimate")
(grid["main_estimate"] < 90).mean()                      # 0.195
```
]

.small[
| pandas | dplyr |
|---|---|
| `assign(..., main_estimate = lambda d: ...)`: the `lambda` sees a column made in the same call | `mutate()`, which always does |
| `.agg(["min", "median", "max"])` | `summarise(min = min(x), median = median(x), max = max(x))` |
| `nsmallest(5, "main_estimate")`: the four lowest tie, because `avoidance_small` only matters when `pareto` is True | `slice_min(main_estimate, n = 5)` |
| `pivot(index=, columns=, values=)` | `pivot_wider(names_from =, values_from =)` |
| `(x < 90).mean()`: the mean of True/False is a share | `mean(x < 90)` |
]

<a href="https://fhoces.github.io/opa-prop40/bsz-analysis/site/repro.html#how-far-the-guesses-move-the-result" class="nav-btn">report: how far the guesses move the result →</a>

---
class: inverse, center, middle

# Interview Questions

---

# Q1. Keep only weekday rides between 7-9am. Compute mean fare by city.

*Hint: `.dt.dayofweek`, `.dt.hour` for filtering, then `.groupby().mean()`.*

```python
morning_weekday = rides[
    (rides["pickup_at"].dt.dayofweek < 5) &
    (rides["pickup_at"].dt.hour >= 7) &
    (rides["pickup_at"].dt.hour < 9)
]
morning_weekday.groupby("city")["fare_usd"].mean().round(2)
```

Notice the parentheses around each `&` condition — required.

---

# Q2. Find the top 5 most expensive rides (by fare per mile) in each city.

*Hint: `.assign()` to create fare_per_mile, `.sort_values()`, `.groupby().head(5)`.*

```python
(rides
 .assign(fare_per_mile = rides["fare_usd"] / rides["distance_mi"])
 .sort_values(["city", "fare_per_mile"], ascending=[True, False])
 .groupby("city")
 .head(5)
)
```

`groupby().head(N)` = "top-N per group". Like `slice_max(n=5, by=city)` in dplyr.

---

# Q3. What share of rides per city are during peak hours (7-9am or 5-7pm)?

*Hint: create an `is_peak` column with `.between()`, then `.agg()` with count and sum.*

.small[
```python
rides["is_peak"] = (
    (rides["pickup_at"].dt.hour.between(7, 8)) |
    (rides["pickup_at"].dt.hour.between(17, 18))
).astype(int)

(rides.groupby("city")
 .agg(n_rides=("ride_id","count"), n_peak=("is_peak","sum"))
 .assign(peak_share = lambda d: (d["n_peak"] / d["n_rides"]).round(3)))
```
]

---

# Q4. Which are the top 3 cities by ride volume in the last 30 days of 2025?

*Hint: filter on date, `.groupby().size()`, `.sort_values().head(3)`.*

```python
recent = rides[rides["pickup_at"] >= "2025-12-02"]
recent.groupby("city").size().sort_values(ascending=False).head(3)
```

`.size()` = pandas equivalent of `dplyr::n()` inside a `summarise`.

---

# Q5. Pivot the hourly ride counts (city × hour → wide table with hours as columns).

*Hint: `.groupby().size()` to get the long table, then `.pivot()`.*

```python
long = (
    rides
    .assign(hour = rides["pickup_at"].dt.hour)
    .groupby(["city", "hour"])
    .size()
    .reset_index(name="n_rides")
)
wide = long.pivot(index="city", columns="hour", values="n_rides")
```

`pivot()` = long → wide. `melt()` = wide → long.

---
class: inverse

# The Key Takeaways

<br>

### 1. The dplyr 5 verbs all have direct pandas equivalents. The hardest part is remembering the method-call style instead of the pipe.

<br>

### 2. `.agg(name = (col, func))` is the cleanest summarise. Memorize it.

<br>

### 3. The two pandas-specific traps: parenthesize boolean conditions, and use `.loc` for any "modify a subset" operation.

---

# Course Map

<table>
<tr><th>#</th><th>Module</th><th>Status</th></tr>
<tr><td>1</td><td><a href="../module-01/slides.html">Python for R Users</a></td><td>✓ done</td></tr>
<tr><td>2</td><td>pandas basics <i>(just finished)</i></td><td>✓ done</td></tr>
<tr><td><b>3</b></td><td><b><a href="../module-03/slides.html">Joins, merges, group-by recipes</a></b></td><td>next</td></tr>
<tr><td>4</td><td><a href="../module-04/slides.html">Regression and A/B tests with statsmodels</a></td><td>✓ done</td></tr>
<tr><td>5</td><td><a href="../module-05/slides.html">End-to-end interview scenario</a></td><td>✓ done</td></tr>
<tr><td>6</td><td><a href="../module-06/slides.html">Reading and Reviewing Someone Else's Python</a></td><td>✓ done</td></tr>
<tr><td>7</td><td><a href="../module-07/slides.html">Loading, Running SQL and Checking a Reproduction</a></td><td>✓ done</td></tr>
</table>