Grouping, Aggregating, and Joining

Learning Goals

• Use group_by and agg to compute per-group statistics
• Join two dataframes on a key column and check for row count changes
• Reshape dataframes with pivot and unpivot

Lesson

• group_by splits a dataframe into groups sharing the same value in one or more columns, then agg computes a summary for each group
  • Aggregation functions include mean, median, std, min, max, count, and n_unique
  • The result has one row per unique combination of the grouping columns
  • Order of rows in the output is not guaranteed; use sort if you need a specific order
• You can group by more than one column at a time
  • Example: df.group_by(["language", "year"]).agg(pl.col("lines").median()) gives median line count per language per year
  • Each additional grouping column multiplies the number of output rows
• Common aggregation mistake: forgetting that mean is sensitive to outliers
  • A few very long functions can pull the mean far above the median
  • When in doubt, report both; if they differ substantially, explain why
• A join combines two dataframes by matching rows on one or more key columns
  • An inner join keeps only rows where the key appears in both dataframes
  • A left join keeps all rows from the left dataframe and fills with null where the right has no match
  • Outer joins keep all rows from both sides
  • The danger of joining without checking: row counts can explode (many-to-many join) or silently shrink (inner join on a key that many rows do not share)
• Always check shape before and after a join
  • If the output has more rows than either input, you have a many-to-many relationship that you probably did not intend
  • If the output has far fewer rows than either input, many keys did not match, which is worth investigating before proceeding
• A pivot reshapes a dataframe from long format (one row per observation) to wide format (one row per entity with multiple value columns)
  • unpivot goes the other direction: from wide to long
  • Long format is usually better for analysis; wide format is often more readable for humans
  • Polars uses df.pivot(on="variable", index="id", values="value") and df.unpivot(on=["col_a", "col_b"], index="id")
• El Haji et al. studied tests generated by GitHub Copilot and found that the tests are syntactically correct but often lack meaningful assertions [ElHaji2024]
  • This is partly a data problem: if test outcomes are stored as unstructured text or log files, you cannot group by assertion type or aggregate pass rates across test suites
  • Structuring test result data as a proper dataframe with columns for test name, assertion type, pass/fail, and file name makes exactly this kind of group-and-aggregate analysis straightforward
  • Well-structured data enables the analysis; poorly structured data makes it impossible

"""Compare Python and JavaScript function sizes using groupby."""

import polars as pl

py = pl.read_csv("data/py_func_counts.csv").with_columns(
    pl.lit("Python").alias("language")
)
js = pl.read_csv("data/js_func_counts.csv").with_columns(
    pl.lit("JavaScript").alias("language")
)
combined = pl.concat([py, js])
combined = combined.with_columns(
    (pl.col("lines") / pl.col("functions")).alias("lines_per_func")
)
stats = combined.group_by("language").agg(
    pl.col("lines_per_func").median().alias("median"),
    pl.col("lines_per_func").mean().alias("mean"),
    pl.col("lines_per_func").std().alias("std"),
)
print(stats)

Check Understanding

result = (df
          .group_by("language")
          .agg(pl.col("lines").mean()))
# then later
py_mean = result["language" == "Python"]["lines"]

py_mean = result.filter(pl.col("language") == "Python")["lines"][0]

Exercises