Window & Rank

Some questions are about a row’s position within its group: which gene is highest in each sample, the rank of a value, a running total. These are window operations. They keep one row per record but compute across the group. dplyr does it with group_by plus a window function like dense_rank or cumsum; Polars uses .over(...) and .cum_sum().

Every code block runs in a container in the companion repository, and the two languages agree.

Rank within a group

Rank the genes by expression inside each sample. dense_rank gives ties the same rank with no gaps. Within S01 the housekeeping genes lead, as expected.

R

ranked <- expr |>
  group_by(sample_id) |>
  mutate(rank = dense_rank(desc(counts))) |>
  ungroup()

ranked |> filter(sample_id == "S01") |> arrange(rank)

# A tibble: 6 × 3
  gene  counts  rank
  <chr>  <int> <int>
1 GAPDH   3939     1
2 ACTB    3117     2
3 TP53     393     3
4 EGFR     294     4
5 MYC      247     5
6 BRCA1    195     6

Python

ranked = expr.with_columns(
    pl.col("counts").rank(method="dense", descending=True).over("sample_id")
    .cast(pl.Int64).alias("rank")
)

ranked.filter(pl.col("sample_id") == "S01").sort("rank")

shape: (6, 3)
┌───────┬────────┬──────┐
│ gene  ┆ counts ┆ rank │
│ ---   ┆ ---    ┆ ---  │
│ str   ┆ i64    ┆ i64  │
╞═══════╪════════╪══════╡
│ GAPDH ┆ 3939   ┆ 1    │
│ ACTB  ┆ 3117   ┆ 2    │
│ TP53  ┆ 393    ┆ 3    │
│ EGFR  ┆ 294    ┆ 4    │
│ MYC   ┆ 247    ┆ 5    │
│ BRCA1 ┆ 195    ┆ 6    │
└───────┴────────┴──────┘

Top per group

The most common use of a within-group rank is “give me the top one”. dplyr has slice_max; in Polars, sort then take the first of each group.

R

expr |>
  group_by(sample_id) |>
  slice_max(counts, n = 1, with_ties = FALSE) |>
  ungroup()

# A tibble: 8 × 3
  sample_id gene  counts
  <chr>     <chr>  <int>
1 S01       GAPDH   3939
2 S02       ACTB    4331
3 S03       ACTB    4398
4 S04       ACTB    4024
5 S05       GAPDH   4376
6 S06       ACTB    4199
7 S07       GAPDH   3959
8 S08       GAPDH   4312

Python

(
    expr.sort("counts", descending=True)
    .group_by("sample_id", maintain_order=True)
    .first()
    .sort("sample_id")
)

shape: (8, 3)
┌───────────┬───────┬────────┐
│ sample_id ┆ gene  ┆ counts │
│ ---       ┆ ---   ┆ ---    │
│ str       ┆ str   ┆ i64    │
╞═══════════╪═══════╪════════╡
│ S01       ┆ GAPDH ┆ 3939   │
│ S02       ┆ ACTB  ┆ 4331   │
│ S03       ┆ ACTB  ┆ 4398   │
│ S04       ┆ ACTB  ┆ 4024   │
│ S05       ┆ GAPDH ┆ 4376   │
│ S06       ┆ ACTB  ┆ 4199   │
│ S07       ┆ GAPDH ┆ 3959   │
│ S08       ┆ GAPDH ┆ 4312   │
└───────────┴───────┴────────┘

The winner is always a housekeeping gene, GAPDH or ACTB, which is the sanity check you want from this kind of summary.

Running total

A cumulative sum walks down the rows accumulating as it goes. Here, MYC counts adding up across the samples.

R

expr |>
  filter(gene == "MYC") |>
  arrange(sample_id) |>
  mutate(cumulative = cumsum(counts))

  sample_id counts cumulative
1       S01    247        247
2       S02    243        490
3       S03    228        718
4       S04    266        984
5       S05    810       1794
6       S06    531       2325
7       S07    854       3179
8       S08    834       4013

Python

(
    expr.filter(pl.col("gene") == "MYC")
    .sort("sample_id")
    .with_columns(pl.col("counts").cum_sum().alias("cumulative"))
)

shape: (8, 3)
┌───────────┬────────┬────────────┐
│ sample_id ┆ counts ┆ cumulative │
│ ---       ┆ ---    ┆ ---        │
│ str       ┆ i64    ┆ i64        │
╞═══════════╪════════╪════════════╡
│ S01       ┆ 247    ┆ 247        │
│ S02       ┆ 243    ┆ 490        │
│ S03       ┆ 228    ┆ 718        │
│ S04       ┆ 266    ┆ 984        │
│ S05       ┆ 810    ┆ 1794       │
│ S06       ┆ 531    ┆ 2325       │
│ S07       ┆ 854    ┆ 3179       │
│ S08       ┆ 834    ┆ 4013       │
└───────────┴────────┴────────────┘

The final cumulative value, 4013, is MYC’s total across all samples: a running total ends at the grand total.