Section 2.1: SQL, Reading and Shaping Data

Most data a model learns from does not start as a file you read line by line. It sits in a database, in tables of typed columns, and the first step of the work is a query that pulls out the rows and columns you need. This section covers SQL through SQLite, the database engine Python carries in its standard library: building a table and querying it, filtering and ordering rows, joining a lookup table onto them, and grouping and aggregating them.

Module 1 read a CSV file by hand and parsed every value it held. A query hands that work to the database: it filters, joins and counts on the database's side and returns only what you asked for. It also fails in quiet ways of its own, a missing value that drops out of both sides of a filter, a join that loses unmatched rows or doubles matched ones, and the rest of this module meets each of them again in pandas.

Lessons

  1. SQLite - Your First Query, Filtering and Ordering - a STRICT table built and queried, its rows filtered with WHERE, sorted and limited.
  2. Joins - keys, INNER JOIN and LEFT JOIN, and the duplicated key that multiplies rows.
  3. Aggregation - GROUP BY and CTEs - counting and averaging per group, filtering groups, naming steps with WITH, and ranking within a group.

By the end you will be able to:

  • Build a STRICT SQLite table from Python, insert rows through placeholders, and query it.
  • Filter and order rows, and account for every row a NULL drops from a filter.
  • Join a lookup table onto a table, and count the rows before and after and the keys that matched nothing or more than once, so neither can change the result unseen.
  • Aggregate per group with GROUP BY and HAVING, and rank rows within a group with ROW_NUMBER.