Recipe Group By - dathere/qsv GitHub Wiki

Recipe: Group By

Tier: Intermediate Commands used: sqlp, pivotp, sort, sortcheck, sample, fill, enum, luau, partition, foreach Anchor dataset: Boston 311 (resources/test/boston311-100.csv)

Problem

You want to run an operation per group — number rows within each department, keep the latest two records per department, count or tally per group, sample from each group, or run any qsv command on each group and stitch the results back together ("split → apply → combine").

qsv has no single --groupby flag shared by every command (#1764). It doesn't need one for most tasks: sqlp window functions, pivotp's group-by mode and a few command-specific options cover them. This page maps each common "per-group X" to its recipe.

Data

cp ~/GitHub/qsv/resources/test/boston311-100.csv .   # 100 rows, 29 columns
qsv frequency -s department --limit 0 boston311-100.csv

The examples group by department (7 groups). In sqlp, _t_1 refers to the first input file.

Quick map

Per-group task Recipe
Count rows per group pivotp --index <keys> (group-by mode)
Number rows within each group (enum per group) sqlp + row_number() over (partition by …)
Group size / running total on every row sqlp + count(*) over (partition by …)
Frequency per group sqlp + group by <key>, <col>
Top/first/last N per group (slice per group) sqlp + qualify row_number() over (…) <= N
Sort within groups / check sort within groups sort -s <keys>,<col> / sortcheck -s <keys>,<col>
Reverse within groups enum + sqlp … order by <key>, rownum desc
Sample N per group sample --stratified <col> N (or --cluster)
Fill empty cells from previous row in the same group fill --groupby <keys>
Stateful per-group logic luau map with a Lua table keyed by group
Any qsv command per group partition + foreach --unify

Solution

1. Count per group — pivotp group-by mode

When <on-cols> is omitted, pivotp does a group-by aggregation. With no --values it produces a count column.

qsv pivotp --index department boston311-100.csv
qsv pivotp --index department,source boston311-100.csv      # multi-column key

Pass --values <cols> --agg <sum|mean|median|…> for other aggregations. See pivotp help.

2. Number rows within each group (enum per group)

qsv sqlp boston311-100.csv "
  select department, case_enquiry_id, open_dt,
         row_number() over (partition by department order by open_dt) as seq
  from _t_1
  order by department, seq"

3. Group size and running totals on every row

# group size repeated on each row
qsv sqlp boston311-100.csv "
  select department, case_enquiry_id,
         count(*) over (partition by department) as dept_n
  from _t_1"

# running count within each group
qsv sqlp boston311-100.csv "
  select department, open_dt,
         count(*) over (partition by department order by open_dt
                        rows between unbounded preceding and current row) as running
  from _t_1
  order by department, open_dt"

4. Frequency per group

qsv sqlp boston311-100.csv "
  select department, case_status, count(*) as n
  from _t_1
  group by department, case_status
  order by department, n desc"

To get frequency's own output format (percentages, ranks, Other) per group, use the generic recipe in step 9.

5. Top / first / last N per group (slice per group)

QUALIFY filters on a window function without a subquery:

# latest 2 requests per department
qsv sqlp boston311-100.csv "
  select department, case_enquiry_id, open_dt
  from _t_1
  qualify row_number() over (partition by department order by open_dt desc) <= 2
  order by department, open_dt desc"

Use = 1 for first-per-group, or between 3 and 5 for a middle slice.

6. Sort, sortcheck and reverse within groups

Group-then-sort is just a sort whose first key is the group:

qsv sort -s department,open_dt boston311-100.csv > by_dept.csv
qsv sortcheck -s department,open_dt by_dept.csv && echo "sorted within groups"

To reverse the original row order within each group, number the rows first:

qsv enum -c rownum boston311-100.csv |
  qsv sqlp - "select * from _t_1 order by department, rownum desc"

7. Sample N per group

qsv sample --stratified department 2 --seed 42 boston311-100.csv

Groups with fewer than N rows are returned whole. --stratified takes a single column. For a multi-column stratum, build a composite key first (see Variations). --cluster <col> samples whole groups instead of rows within them.

8. Stateful per-group logic — luau

A Lua table keyed by the group value persists across rows:

qsv luau map dept_seq '
  counts = counts or {}
  counts[department] = (counts[department] or 0) + 1
  return counts[department]
' boston311-100.csv

The same pattern works for per-group running sums, "changed since previous row in this group" flags, and so on. See Scripting (Luau / Python).

9. Any qsv command per group — partition + foreach

This is the general split → apply → combine. partition writes one file per group. foreach runs a command per file. --unify stitches the outputs under one header, and --new-column records which group each row came from.

rm -rf by_dept
qsv partition department by_dept boston311-100.csv
# build the file list right away, before any command writes caches into by_dept/
(echo file; ls by_dept/*.csv) > dept_files.csv

# per-department stats
qsv foreach file --unify --new-column dept_file --dry-run false \
  "qsv stats -s 'latitude,longitude' {}" dept_files.csv |
  qsv select dept_file,field,mean,stddev

# per-department frequency, in frequency's own format
qsv foreach file --unify --new-column dept_file --dry-run false \
  "qsv frequency -s case_status {}" dept_files.csv

Important

  • foreach defaults to --dry-run true (it only prints the commands). Pass --dry-run false to execute.
  • foreach tokenizes the command itself rather than through a shell. An unquoted argument containing characters other than letters, digits and _ . + / - gets split, so -s latitude,longitude becomes -s latitude longitude. Quote such arguments inside the command string: -s 'latitude,longitude'.
  • --unify assumes every child prints CSV with a header row and the same columns. Commands that print a bare value (e.g. count) or change the header per group (e.g. transpose) don't combine cleanly. Use pivotp for counts.
  • stats writes *.stats.csv cache files next to its input, which is why the file list is built first.

Variations

Multi-column group key for single-column commands (partition, sample --stratified): derive a composite column first.

qsv sqlp boston311-100.csv "select *, department || '|' || source as dept_source from _t_1" > keyed.csv
qsv sample --stratified dept_source 1 --seed 42 keyed.csv

Many groups: partition --limit <n> caps the number of simultaneously open files. For very large inputs see Larger-than-RAM CSV.

Performance notes

  • sqlp and pivotp run on Polars and are multithreaded. Prefer them over partition + foreach, which starts one process per group.
  • pivotp's default --agg smart picks an aggregation from the stats cache, computing streaming stats if needed. A prior stats --everything or moarstats run gives it more signals.

See also

qsv — GitHub · Releases · Discussions · qsv pro · Try it online · Benchmarks · datHere · DeepWiki · Dual-licensed MIT / Unlicense

Edit this page: Contributing to the Wiki

⚠️ **GitHub.com Fallback** ⚠️