# /// script # requires-python = ">=3.12,<3.15" # dependencies = [ # "marimo[sql]", # "duckdb", # "polars", # "pyarrow", # "altair", # "matplotlib", # "pandas>=3,<4", # ] # /// """Python Handbook, Chapter C: Data. """ import marimo __generated_with = "0.24.0" app = marimo.App(width="medium", sql_output="polars") @app.cell def _(): from datetime import date, datetime from io import BytesIO from pathlib import Path from urllib.request import urlopen import altair as alt import marimo as mo import polars as pl return BytesIO, Path, alt, date, datetime, mo, pl, urlopen @app.cell(hide_code=True) def _(mo): mo.md(r""" # Data Chapter C of the Python Handbook, in 11 sections, each one concept. Read this chapter and you can open a file somebody else made, work out what is wrong with it, answer a question from it, and draw the answer. Chapters A and B ran on numbers typed into the cell. Nothing could go wrong before the lesson started. This chapter runs on a real file, because the subject of the chapter **is** the file: what type each column came back as, what is missing from it, and which of your assumptions about it are wrong. Every table in this chapter is a **polars** DataFrame. Section 30 shows the other library you will be handed tables in, pandas, and how to tell the two apart. You can read this straight through, or open it when you need one thing. Every section ends with questions that have no answers written down, because those are the ones asked out loud in class. To run the code yourself, download the `.py` into your project and open it with `uv run marimo edit`. ### The data **Online Retail**, every transaction of a UK online giftware retailer between 1 December 2010 and 9 December 2011. It holds 541,909 rows in eight columns. > Chen, D. (2015). *Online Retail* [Dataset]. UCI Machine Learning Repository. > . Licensed CC BY 4.0. Sections 23 and 27 also use **Northwind**, the course warehouse, because a join needs two tables and Northwind has keys that resolve. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 20. Files and paths A path is the address of a file. Before polars can read anything you have to hand it one, and the most common failure in the first week of real data is that the address was right on your machine and wrong everywhere else. ๐Ÿ“– **Used in:** From a CSV file to a dataframe """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A path is an object with parts `Path` comes from the standard library. Build one with `/` between the pieces, and it assembles the separator your operating system uses. The parts have names. """) return @app.cell def _(Path): example_path = Path("data") / "online-retail" / "online-retail.parquet" example_path.name, example_path.stem, example_path.suffix, example_path.parent return @app.cell(hide_code=True) def _(mo): mo.md(r""" ๐Ÿ‘‰ **Try it:** build a path to `reports/2011/november.csv` the same way, then ask it for `.parent.parent`. Write the answer down before you run it. **Why a `Path` and not a string.** A string only holds the path. A `Path` also tells you its folder with `.parent`, its file name with `.name`, and whether the file is there with `.exists()`, which is the first thing to ask when a read fails. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A relative path is relative to where you started Every running program has a **current directory**, which is the default for anything that does not say otherwise. `Path("sales.csv")` means *sales.csv in the current directory*, and the current directory is where the program was launched from, which is not necessarily where your file sits. """) return @app.cell def _(Path): Path.cwd() return @app.cell(hide_code=True) def _(mo): mo.md(r""" That is the folder this notebook was started from. Start it from somewhere else and the same relative path points somewhere else, with no warning. ```python import polars as pl retail = pl.read_parquet("online-retail.parquet") ``` ``` FileNotFoundError: No such file or directory (os error 2): online-retail.parquet ``` ๐Ÿ‘‰ **Try it:** print `Path.cwd()`, then run the same line from a terminal opened in a different folder. The code did not change and the answer did. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Anchor the path to the notebook's folder The fix is to stop asking *where was I started* and start asking *where am I*. In a marimo notebook that is `mo.notebook_dir()`. In a plain `.py` script it is `Path(__file__).parent`. Either way you get the folder holding the code, and you walk up from there with `.parent` until you reach the top of the project. This notebook sits in `notebooks/handbooks/python/`, so the top of the project is three levels up. """) return @app.cell def _(mo): here = mo.notebook_dir() repo_root = here.parent.parent.parent retail_path = repo_root / "data" / "online-retail" / "online-retail.parquet" here.name, repo_root.name, retail_path.exists() return repo_root, retail_path @app.cell(hide_code=True) def _(mo): mo.md(r""" ๐Ÿ‘‰ **Try it:** print `repo_root` on its own, then `sorted(p.name for p in repo_root.iterdir())[:5]`. You are reading the project from the outside. **The trap.** `Path.cwd()` looks like it does the same job and does not. It answers a question about the terminal, and `mo.notebook_dir()` answers a question about the file. Only the second one is still true tomorrow when somebody runs your notebook from their home folder. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Ask a path whether it is there before you read it `.exists()` returns `True` or `False` and raises nothing. `.stat().st_size` gives the size in bytes. `glob` lists what a folder holds, which is how you find out that the file is called `online-retail.parquet` and not `online_retail.parquet`. """) return @app.cell def _(repo_root, retail_path): _folder = repo_root / "data" / "online-retail" _names = sorted(p.name for p in _folder.glob("*")) if _folder.exists() else [] _size = (f"{retail_path.stat().st_size / 1_000_000:.1f} MB" if retail_path.exists() else "not here") _names, _size return @app.cell(hide_code=True) def _(mo): mo.md(r""" The file is 3.6 MB and holds 541,909 rows. The same data as a CSV is about 50 MB. If you downloaded this chapter as a single `.py` rather than the whole project, the folder is empty and the size says `not here`. That is the correct answer and the reason this cell asks. Every cell that needs the data falls back to fetching it over HTTP, so the chapter runs either way. ๐Ÿ‘‰ **Try it:** point a path at a file that does not exist and call `.exists()` on it. Then call `.stat()` on the same path and read what comes back. ### โœ” Check your understanding 1. Your script reads `data/sales.csv` and works. A colleague runs it and gets `FileNotFoundError`. Name two things that could differ between your machine and theirs. 2. What question does `Path.cwd()` answer, and what question does `Path(__file__).parent` answer? 3. You are given a path and told the file might not be there. What do you call before you read it, and what does it return? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 21. Parquet, CSV and JSON Data arrives in three shapes in this course. **Parquet** keeps its types, **CSV** throws them away, and **JSON** nests. Which one you were handed decides how much work happens before the first calculation. ๐Ÿ“– **Used in:** From a CSV file to a dataframe """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `pl.read_parquet` gives the types back with the rows A parquet file stores what each column is, so a date arrives as a date and a number arrives as a number. One line, and nothing to declare. The cell below falls back to the published copy over HTTPS when the repository copy is missing, so this notebook runs after a download of the single `.py` file. """) return @app.cell def _(BytesIO, pl, retail_path, urlopen): if retail_path.exists(): retail = pl.read_parquet(retail_path) else: _url = "https://oim7510.github.io/data/online-retail/online-retail.parquet" retail = pl.read_parquet(BytesIO(urlopen(_url).read())) retail.shape return (retail,) @app.cell def _(retail): retail.schema return @app.cell(hide_code=True) def _(mo, retail): mo.md(f""" Eight columns, {retail.height:,} rows. `InvoiceDate` came back as `{retail.schema["InvoiceDate"]}`, which means the month is already available and nothing has to be parsed. Hold on to that, because the next cell takes it away. ๐Ÿ‘‰ **Try it:** run `retail.head()` and then `retail.tail(3)`. With {retail.height:,} rows, `.head()` is an instruction and not a suggestion: printing the whole frame gives you a screen of dots and a slow page. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A CSV has no types Write part of the frame out as a CSV and read it straight back. Nothing was lost on the way to the disk, and something is lost on the way back. """) return @app.cell def _(Path, pl, retail): import tempfile work_dir = Path(tempfile.mkdtemp()) france_csv_path = work_dir / "france-2011.csv" retail.filter(pl.col("Country") == "France").write_csv(france_csv_path) round(france_csv_path.stat().st_size / 1000), france_csv_path.name return france_csv_path, work_dir @app.cell(hide_code=True) def _(mo): mo.md(r""" Read it back with no arguments and polars stops: ```python pl.read_csv(france_csv_path) ``` ``` ComputeError: could not parse `C537893` as dtype `i64` at column 'InvoiceNo' (column number 1) ``` A CSV carries no types, so polars reads the first 100 rows and guesses one for each column. Every `InvoiceNo` in those rows is six digits, so it guessed a whole number. Row 179 is `C537893`, a cancelled invoice, and polars stops there. Your copy of polars may name a later invoice starting with `C`; the cause is the same. `StockCode` got through only because `POST` turns up in row 20, inside the rows polars looked at. The error lists the fixes, and the one to use is to specify the column's type. Specify it for both code columns, so the read does not depend on where the first letter happens to fall. """) return @app.cell def _(france_csv_path, pl): code_columns = {"InvoiceNo": pl.String, "StockCode": pl.String} france = pl.read_csv(france_csv_path, schema_overrides=code_columns) france.schema return code_columns, france @app.cell(hide_code=True) def _(france, mo, retail): mo.md(f""" `InvoiceDate` went out as `{retail.schema["InvoiceDate"]}` and came back as `{france.schema["InvoiceDate"]}`. It is text now. Nothing raised, the column still reads as a date on the screen, and every date operation on it will fail until somebody parses it. That is section 25. `pl.read_csv` guessed the numeric columns correctly because they look numeric. It does not guess dates unless you ask, and you would not want it to: guessing is how `03/04/2011` becomes the third of April in one file and the fourth of March in another. ๐Ÿ‘‰ **Try it:** read the file with `infer_schema_length=10000` and no overrides, and read the type polars chose for `InvoiceNo` this time. Then delete `work_dir` when you are done with it. **The trap.** Writing to a path that already holds a file **clears the old file and starts fresh**. There is no prompt and no undo. Check the path before you write, the same way you check it before you read. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `pl.read_csv` has the arguments you will actually need Four cover almost everything. All four exist because somebody handed somebody else a file that needed them. | argument | what it does | |---|---| | `separator=";"` | the file is separated by something other than a comma | | `encoding="latin1"` | the file is not UTF-8, which is what an error about invalid UTF-8 means | | `schema_overrides={"InvoiceNo": pl.String}` | stop polars guessing on a column you know about | | `try_parse_dates=True` | turn columns that look like dates into real dates on the way in | """) return @app.cell def _(code_columns, france_csv_path, pl): _typed = pl.read_csv( france_csv_path, schema_overrides=code_columns, try_parse_dates=True, ) _typed.schema["InvoiceDate"], _typed.schema["InvoiceNo"] return @app.cell(hide_code=True) def _(mo): mo.md(r""" Same file, same one function, and the date is a date. The arguments are the whole difference. ๐Ÿ‘‰ **Try it:** read the file a third time with `n_rows=10`. On a 50 MB CSV that is how you look before you commit. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The `csv` module reads a file as a list of records Python's own `csv` module reads the same file without polars. `csv.DictReader` turns each line into a dictionary, with the header's names as keys, so the file comes back as a list of records, the shape of Chapter B and of an API reply. It handles the commas and quotes inside values for you. The empty `newline` setting in `open()` is what the module asks for. """) return @app.cell def _(france_csv_path): import csv with open(france_csv_path, newline="", encoding="utf-8") as _file: france_records = list(csv.DictReader(_file)) len(france_records), france_records[0] return (france_records,) @app.cell(hide_code=True) def _(mo): mo.md(r""" Every value is a string, `Quantity` and `UnitPrice` included. The module guesses no types, so you apply `int()` and `float()` yourself. `csv.DictWriter` goes the other way, from a list of dictionaries to a file: give it `fieldnames`, call `.writeheader()`, then `.writerows()`. ๐Ÿ‘‰ **Try it:** add up `Quantity` over `france_records` with a `for` loop, then compare it with `france["Quantity"].sum()`. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Reading and writing JSON with `load` and `loads` A CSV is a rectangle. JSON is a shape: dictionaries inside lists inside dictionaries, which is what almost every API hands back. In Python it becomes exactly the dictionaries and lists of Chapter B. Four function names, and the `s` is the only difference between two pairs of them. | | reads or writes | |---|---| | `json.load(f)` | an open **file** | | `json.loads(text)` | a **string** | | `json.dump(obj, f)` | to an open **file** | | `json.dumps(obj)` | to a **string** | """) return @app.cell def _(retail, work_dir): import json summary_path = work_dir / "summary.json" _summary = { "rows": retail.height, "countries": retail["Country"].n_unique(), "first_invoice": str(retail["InvoiceDate"].min()), } with open(summary_path, "w", encoding="utf-8") as _f: json.dump(_summary, _f, indent=2) summary_path.read_text() return json, summary_path @app.cell def _(json, summary_path): with open(summary_path, encoding="utf-8") as _f: summary_back = json.load(_f) summary_back["rows"], summary_back["countries"], type(summary_back) return @app.cell(hide_code=True) def _(mo): mo.md(r""" A dictionary went out and a dictionary came back. `with open(...)` closes the file when the indented block ends, whether or not anything went wrong inside it. ```python json.load('{"rows": 541909}') ``` ``` AttributeError: 'str' object has no attribute 'read' ``` `load` wanted a file and got a string. The message never says the word JSON, which is why this one costs people 10 minutes. ๐Ÿ‘‰ **Try it:** fix that line two ways, once with `json.loads` and once by writing the string to a file first. Then add a nested list to the summary dictionary, dump it, and read the file with your eyes. ### โœ” Check your understanding 1. The same column is a real date in the parquet file and text in the CSV written from it. What did the CSV lose, and why can it not keep it? 2. You read a CSV and polars stops with `could not parse ... as dtype i64`. What did it guess, how did it guess, and which argument settles it? 3. `json.load` and `json.loads` both parse JSON. Which one takes a file, and what is the error message when you get it the wrong way round? 4. You open a file with `with open(...)` and never call `.close()`. Is the file closed? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 22. `try` / `except`, and `assert` Two different things go wrong with data, and they need opposite tools. Sometimes the code stops, and `try` / `except` decides what happens next. Sometimes the code finishes and the answer is wrong, and `assert` is how you make that one stop too. ๐Ÿ“– **Used in:** Signing off on somebody else's work """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### What `try` and `except` each do Put the line that might fail in the `try` block. Name the exception you expect in the `except` clause. If it happens, the `except` block runs and the program carries on. """) return @app.cell def _(pl, work_dir): _missing = work_dir / "not-here.csv" try: _orders = pl.read_csv(_missing) read_result = f"{_orders.height} rows" except FileNotFoundError: read_result = f"no file at {_missing.name}, using an empty result" read_result return @app.cell(hide_code=True) def _(mo): mo.md(r""" That cell ran to the end and produced a value. Without the `try` it would have stopped, and everything below it would never have run. ๐Ÿ‘‰ **Try it:** add an `else:` block, which runs only when nothing was raised, and a `finally:` block, which runs either way. Predict which of them prints when the file is missing. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Catch only the exception you expected A bare `except:` catches everything, including the typo you just made three lines up. The program then reports the failure you predicted while suffering a different one. ```python # Do not write this. try: orders = pl.read_csv(path) except: print("File not found") ``` `pl.read_csv` can raise `FileNotFoundError`, and it can also raise `ComputeError` when a value will not parse, as it did in section 21, and `PermissionError`. All three print *File not found*. So does `pl.read_csvv`. A real example from this course's own archive: a script caught a `KeyError` and printed `JSON parsing error`. The JSON parsed correctly. The assumption about which keys it contained was wrong, and the message sent everybody to look at the wrong thing. """) return @app.cell def _(pl, work_dir): try: _orders = pl.read_csv(work_dir / "also-not-here.csv") typed_result = "read it" except FileNotFoundError as _err: typed_result = f"{type(_err).__name__}: there is no file at that path" except pl.exceptions.ComputeError: typed_result = "the file is there and a value in it would not parse" typed_result return @app.cell(hide_code=True) def _(mo): mo.md(r""" `as _err` binds the exception to a name so the handler can report what actually happened. Two `except` clauses on one `try` is normal, and the first matching one runs. ๐Ÿ‘‰ **Try it:** write a `try` around `int("n/a")` and catch `ValueError`. Then run the same line without the `try` and compare what you see. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A loop that survives a bad row This is the shape that matters in analytics. You are working through a list, one item fails, and you want the other 400 anyway. Put the `try` **inside** the loop and collect the failures. """) return @app.cell def _(work_dir): _wanted = ["france-2011.csv", "summary.json", "germany-2011.csv", "italy-2011.csv"] found, failed = [], [] for _name in _wanted: try: _size = (work_dir / _name).stat().st_size found.append(_name) except FileNotFoundError: failed.append(_name) f"read {len(found)} of {len(_wanted)}, missing: {', '.join(failed)}" return @app.cell(hide_code=True) def _(mo): mo.md(r""" Two lists come out instead of one, and the second one is the report. A loop that prints an error and moves on gives you neither. ๐Ÿ‘‰ **Try it:** move the `try` to the outside of the loop, so it wraps the whole `for`. Run it and count how many files it manages to read before it gives up. **The trap.** Catching an error and continuing is a decision to produce an answer from incomplete data. That decision belongs in the memo, next to the number. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `assert` stops the code when the data is not what you believe `try` / `except` is for the failure that announces itself. `assert` is for the one that does not. You write down what you expect to be true, and Python raises `AssertionError` the moment it stops being true. Three things are worth asserting on every dataset, and all three appear again in sections 26 and 27: - **A row count.** How many rows should this have, and does it. - **A total.** Does the number you are about to report agree with a second way of computing it. - **A join cardinality.** Did a join change the row count when it had no business changing it. This is what the course's *How I know these numbers are right* section runs on. An assert that holds is a check somebody else can rerun. """) return @app.cell def _(retail): assert retail.height == 541_909, "the file is not the one this chapter was written for" assert retail["Quantity"].null_count() == 0, "Quantity has gaps" assert (retail["InvoiceDate"].dt.year() >= 2010).all(), "a date before the data starts" _is_cancelled = retail["InvoiceNo"].str.starts_with("C") _cancelled = _is_cancelled.sum() _negative = (retail["Quantity"] < 0).sum() assert (retail.filter(_is_cancelled)["Quantity"] < 0).all(), "a cancelled line with a quantity of zero or more" f"4 checks passed. {_cancelled:,} cancelled lines, {_negative:,} negative quantities." return @app.cell(hide_code=True) def _(mo): mo.md(r""" The last one is the interesting kind. It is not a fact about the file format; it is a claim about the business, that a cancellation always shows up as a negative quantity. It holds here, and 1,336 negative rows are not cancellations, which is a question worth asking. When an assert fails you get the message you wrote: ```python assert joined.height == orders.height, f"join changed the row count: {orders.height} to {joined.height}" ``` ``` AssertionError: join changed the row count: 5 to 14 ``` ๐Ÿ‘‰ **Try it:** write an assert that is false on purpose, with a message that names both numbers. Then delete the message and run it again. Compare how long each version takes to understand. **The trap.** `assert` disappears when Python is run with the `-O` flag, so it is a development check and not a production guard. In a notebook it is exactly the right tool, and in shipped code you use `if ...: raise ValueError(...)` instead. ### โœ” Check your understanding 1. A `try` block wraps a whole `for` loop. The third of 400 files is missing. How many files get read? 2. Name a failure that `except:` would catch and that you would rather have seen. 3. You join two tables and the row count goes from 5 to 14. Which of the two tools in this section catches it, and why does the other one not? 4. Give one assert you could write about a dataset before you have looked at it at all. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 23. Series and DataFrame A **DataFrame** is a table: named columns, rows, one type per column, which polars calls the column's **dtype**. A **Series** is one of those columns. Every polars operation in this chapter either produces one of the two or asks one of them a question. ๐Ÿ“– **Used in:** From a CSV file to a dataframe """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Questions to ask a frame you have never seen Shape, column names, types, and the first few rows. In that order, before anything else, every time. """) return @app.cell def _(retail): _rows, _cols = retail.shape f"{_rows:,} rows and {_cols} columns: {', '.join(retail.columns)}" return @app.cell def _(retail): retail.head(3) return @app.cell(hide_code=True) def _(mo): mo.md(r""" `retail.schema` answered the third question in section 21. A row has no name, only a position, so there is no column of labels down the left. That is the largest difference between polars and pandas, and section 30 is where it matters. ๐Ÿ‘‰ **Try it:** run `retail.describe()`. It summarizes every column in one table, and one of the numbers it reports for `Quantity` is alarming. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A column that looks numeric and is not `InvoiceNo` is six digits on most rows. On 9,288 of them it is six digits with a `C` in front, and the `C` means the invoice was cancelled. So the column is text, and any code that treats it as a number either fails or throws the cancellations away. Section 21 met it from the other side, when polars guessed a number and stopped. This was found by the file refusing to be written. Converting the column to a number for storage failed twice, once for `InvoiceNo` and once for `StockCode`, and the error named the value it choked on: ``` Could not convert 'C536379' with type str ``` """) return @app.cell def _(pl, retail): _c = retail.filter(pl.col("InvoiceNo").str.starts_with("C")) ( retail.schema["InvoiceNo"], f"{_c.height:,} cancelled lines", f"first one: {_c['InvoiceNo'][0]}, quantity {_c['Quantity'][0]}", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ๐Ÿ‘‰ **Try it:** find the pair `581483` and `C581484` in the data. One sells 80,995 units of a paper craft kit and the other takes them back 12 minutes later. Then compute `retail["Quantity"].sum()` and decide whether you would put that number in a slide. **The trap.** An agent asked to clean this file will see a column called `InvoiceNo`, call it an identifier, and **cast** it to an integer. The cast either raises or silently turns 9,291 invoice numbers into nulls, depending on how it was written. Read what it did to the column. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A column or a frame One name in brackets gives a Series. `.select()` with one or more names gives a frame, and it is the same word, doing the same job, as `SELECT` in SQL. """) return @app.cell def _(retail): _one = retail["UnitPrice"] _two = retail.select("Quantity", "UnitPrice") type(_one).__name__, type(_two).__name__, _one.shape, _two.shape return @app.cell def _(retail): _q = retail["Quantity"] f"sum {_q.sum():,} ยท mean {_q.mean():.2f} ยท min {_q.min():,} ยท max {_q.max():,}" return @app.cell(hide_code=True) def _(mo): mo.md(r""" A minimum of -80,995 and a maximum of 80,995 in a column called `Quantity`. The summary took one line and it has already told you the file has returns in it. ๐Ÿ‘‰ **Try it:** call `.describe()` on `retail["UnitPrice"]` and then count how many rows have a price of zero or less. The count is 2,517 and none of them raised anything. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Money that arrives as a decimal A parquet file can store money as a **decimal**, a type that does exact arithmetic and never rounds 0.1 + 0.2 to 0.30000000000000004. Online Retail's `UnitPrice` is a plain float and none of this touches it. The Northwind warehouse stores money as decimals, and polars reads every money column there as `Decimal`. """) return @app.cell def _(BytesIO, mo, pl, urlopen): def northwind(name): """One Northwind table. Repository copy first, published copy second.""" _here = mo.notebook_dir() _local = next( (p / "data" / "northwind" / f"{name}.parquet" for p in [_here, *_here.parents] if (p / "data" / "northwind").is_dir()), None, ) if _local is not None: return pl.read_parquet(_local) _url = f"https://oim7510.github.io/data/northwind/{name}.parquet" return pl.read_parquet(BytesIO(urlopen(_url).read())) return (northwind,) @app.cell def _(northwind): nw_lines = northwind("order-details") nw_lines.schema return (nw_lines,) @app.cell(hide_code=True) def _(mo): mo.md(r""" `UnitPrice` is `Decimal`, exact to four places. Now multiply it three ways. """) return @app.cell def _(nw_lines, pl): _three = nw_lines.head(1).select( by_quantity=pl.col("UnitPrice") * pl.col("Quantity"), by_rate=pl.col("UnitPrice") * 1.05, after_discount=pl.col("UnitPrice") * pl.col("Quantity") * (1 - pl.col("Discount")), ) _three.schema, _three.row(0) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Nothing raised. Decimal times a whole number stays a decimal. Decimal times a float becomes a float, and `14 * 1.05` comes back as `14.700000000000001`. So the line total is exact and the line total after a discount is a float, off the same column, because `Discount` is a float. It gets quieter than that. """) return @app.cell def _(nw_lines): _first = nw_lines["UnitPrice"].head(100) f"sum: {_first.sum()!r}", f"mean: {_first.mean()!r}" return @app.cell(hide_code=True) def _(mo): mo.md(r""" The same column, two aggregates. `.sum()` hands back a `Decimal` and `.mean()` hands back a float, because the mean divides. Put those two numbers in one report and they behave differently the next time somebody multiplies one of them. **The fix is one call, and you make it at read time.** Cast the money columns to `Float64` as soon as the frame arrives, and write down that you did, because you have just accepted floating point rounding on money in exchange for one type everywhere. """) return @app.cell def _(nw_lines, pl): nw_priced = nw_lines.cast({"UnitPrice": pl.Float64}).with_columns( LineTotal=pl.col("UnitPrice") * pl.col("Quantity") * (1 - pl.col("Discount")) ) _line = nw_priced.row(0, named=True) f"{_line['Quantity']} at {_line['UnitPrice']:.2f} comes to {_line['LineTotal']:.2f}" return @app.cell(hide_code=True) def _(mo): mo.md(r""" That is the Queso Cabrales line on order `10248`: 12 at 14.00, so 168.00, which you can check by hand. **The other route.** Reading the same file through DuckDB with `duckdb.sql("SELECT * FROM 'order-details.parquet'").pl()` keeps the `Decimal`. Ending the same line in `.df()` hands you a pandas frame with `float64` instead, because pandas has no decimal column. Same file, same rows, different type, and nothing anywhere reports the difference. If your numbers disagree with a classmate's in the fourth decimal place, this is the first thing to check. ๐Ÿ‘‰ **Try it:** read one Northwind money column both ways, `.pl()` and `.df()`, and print its type from each. Then multiply the first value of each by `1.05`. ### โœ” Check your understanding 1. `retail["Quantity"]` and `retail.select("Quantity")` name the same column. What is different about what comes back? 2. `InvoiceNo` holds `536365` and `C536379`. Give one thing that breaks if the column is stored as an integer. 3. A `Decimal` money column times a quantity stays exact, and times a discount rate it does not. What decides which one you get? 4. `.sum()` returns a `Decimal` and `.mean()` returns a float off the same column. Why would that be hard to notice? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 24. Selecting, filtering, sorting Three operations answer most of what gets asked of a table. Take the columns you want, keep the rows that qualify, and put them in an order that lifts the answer to the top. All three hand back a new frame and leave the original where it was. ๐Ÿ“– **Used in:** From a CSV file to a dataframe """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A comparison on a column gives a column of `True` and `False` `retail["Country"] == "France"` does not give one answer. It gives 541,909 of them, one per row, as a Series of booleans. That Series is called a **mask**, and `.sum()` counts it because `True` adds one and `False` adds nothing. """) return @app.cell def _(retail): _is_france = retail["Country"] == "France" _is_france.dtype, f"{_is_france.sum():,} rows", f"{_is_france.mean():.2%} of the file" return @app.cell(hide_code=True) def _(mo): mo.md(r""" `.filter()` keeps the rows where a condition is `True`. Write the condition with `pl.col("Country")`, which means *the `Country` column of whichever frame this runs on*, so the frame's name is written once. It is `WHERE` in SQL. """) return @app.cell def _(pl, retail): retail.filter(pl.col("Country") == "France").head(3) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ๐Ÿ‘‰ **Try it:** build the mask for Germany, count it, and take its `.mean()`. Then filter on `pl.col("Quantity") < 0` and read what share of the file that is. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Combining conditions with `&`, `|` and `~` `&` is and, `|` is or, `~` is not. The English words do something else here and say so loudly. ```python retail.filter((pl.col("Country") == "France") and (pl.col("Quantity") > 100)) ``` ``` TypeError: the truth value of an Expr is ambiguous ``` `and` wants one yes or one no, and a condition on a column holds half a million of them. `&` compares row by row, which is the question you meant. The parentheses are load-bearing. `&` binds tighter than `==` and `>`, so without them Python tries to combine `"France"` with `pl.col("Quantity")`, and you get the same message as above for a different mistake. Inside `.filter()`, conditions separated by commas are joined with and, which is the form the cleaning rule below uses. """) return @app.cell def _(pl, retail): _bulk_france = retail.filter((pl.col("Country") == "France") & (pl.col("Quantity") > 100)) _not_uk = retail.filter(~(pl.col("Country") == "United Kingdom")) ( f"{_bulk_france.height:,} French lines of more than 100 units", f"{_not_uk.height:,} lines outside the United Kingdom", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ๐Ÿ‘‰ **Try it:** write one condition for lines that are French **or** German, and one for lines that are French **and** cancelled. Predict which keeps more rows before you run either. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `.select()` and `.filter()` chain Each call hands back a frame, so the next call goes straight on the end. Rows first, then the four columns you want to read, then the first three. """) return @app.cell def _(pl, retail): _wanted = ["InvoiceNo", "Description", "Quantity", "UnitPrice"] retail.filter(pl.col("Country") == "France").select(_wanted).head(3) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ๐Ÿ‘‰ **Try it:** select a column that does not exist and read the error; it lists the columns that do. Then run `retail.filter(pl.col("Quantity") > 1000).select("Description", "Quantity")` and see how few rows come back. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Decisions that produce a frame you can add up Section 23 found three things in this file that stop a plain `SUM` from meaning anything: cancelled invoices, negative quantities, and prices at or below zero. Handling them is three conditions in one `.filter()`, and each one is a decision somebody has to defend. Write them as three lines with three comments, so the decisions are readable by the person who reviews the memo. """) return @app.cell def _(pl, retail): sales = retail.filter( ~pl.col("InvoiceNo").str.starts_with("C"), # decision 1: cancellations are out pl.col("Quantity") > 0, # decision 2: returns and empty lines are out pl.col("UnitPrice") > 0, # decision 3: free and negative prices are out ).with_columns(Revenue=pl.col("Quantity") * pl.col("UnitPrice")) ( f"{sales.height:,} rows kept, {retail.height - sales.height:,} dropped", f"revenue: {sales['Revenue'].sum():,.2f}", ) return (sales,) @app.cell(hide_code=True) def _(mo): mo.md(r""" `.with_columns()` returns a new frame with the extra column. `retail` is unchanged, because no polars method changes the frame it was called on, so there is never a copy to make first. ๐Ÿ‘‰ **Try it:** run the three conditions separately and print how many rows each one removes on its own. They overlap, so the three numbers add up to more than 11,805. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Sorting puts the answer where you can see it `.sort("Revenue", descending=True)` returns a new frame in a new order. `.head(6)` is what makes it readable on a frame this size. """) return @app.cell def _(sales): _cols = ["InvoiceNo", "StockCode", "Description", "Quantity", "UnitPrice", "Revenue"] sales.sort("Revenue", descending=True).head(6).select(_cols) return @app.cell def _(pl, sales): _kept_cancelled = sales.filter(pl.col("InvoiceNo").is_in(["581483", "541431", "556444"])) _not_products = sales.filter(~pl.col("StockCode").str.contains(r"^\d{5}")) ( f"three lines cancelled minutes later and kept anyway: {_kept_cancelled['Revenue'].sum():,.2f}", f"lines whose StockCode is not a five-digit product code: {_not_products.height:,}", f"and they carry {_not_products['Revenue'].sum():,.2f} of the total", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Read the six rows above. The top three are the biggest revenue lines in the cleaned frame and all three were cancelled within 20 minutes by an invoice starting `C`, which decision 1 dropped while keeping the sale it cancelled. `AMAZONFEE` is a marketplace fee. `A563185` is an accounting entry whose description is *Adjust bad debt* and whose invoice number starts with `A`, a prefix that appears three times in the whole file. `POST` is postage. None of the six largest lines in a column named `Revenue` is a product sale that stood, and the three cleaning rules ran without raising anything. ๐Ÿ‘‰ **Try it:** sort ascending instead and read the bottom six. Then sort by `Quantity` and by `UnitPrice` and see whether the same rows keep appearing. **The trap.** A cleaning rule is a claim about the data. Sorting on the column the rule was supposed to fix is how you test the claim, and it costs one line. ### โœ” Check your understanding 1. `retail["Country"] == "France"` gives back something with 541,909 entries. What is in it, and what does `.sum()` on it mean? 2. Two conditions go into `.filter()` once separated by a comma and once joined with `&`. Do the two keep the same rows? What would `|` keep? 3. Three conditions removed 11,805 rows. Name one row that survived all three and should not be in a revenue total. 4. Somebody hands you a cleaned frame and a revenue figure. What is the first thing you sort by, and what are you looking for? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 25. A column of dates A date column arrives as text far more often than it arrives as a date, and text does not know that November comes after October. This section parses the text, pulls parts out of the result, and groups by month. ๐Ÿ“– **Used in:** Building and querying your database ยท Building your product """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A string slice looks like a month Take the seven leading characters of `2010-12-01 08:26` and you get `2010-12`, which is the month. That works, and it works only on that one way of writing a date. This course's own archive has it. A starter script tells the student to extract a month with `order_date[:7]`, and the comment beside it reads `'2024-01-15' โ†’ '2024-01'`. The comment is correct about that string. Here is the same slice on the same French rows written the way a spreadsheet exports them. """) return @app.cell def _(code_columns, pl, retail, work_dir): excel_csv_path = work_dir / "france-excel.csv" retail.filter(pl.col("Country") == "France").write_csv( excel_csv_path, datetime_format="%m/%d/%Y %H:%M" ) excel_dates = pl.read_csv(excel_csv_path, schema_overrides=code_columns) excel_dates["InvoiceDate"].head(3).to_list(), excel_dates.schema["InvoiceDate"] return (excel_dates,) @app.cell def _(excel_dates): _sliced = excel_dates["InvoiceDate"].str.slice(0, 7) ( f"first three slices: {_sliced.head(3).to_list()}", f"{_sliced.n_unique()} distinct 'months'", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" The slice ran, returned strings, and grouped nothing. `12/01/2` is a month and a day and one digit of a year. Group by it and you get one bucket per calendar day. ๐Ÿ‘‰ **Try it:** run the same slice on the parquet frame's `InvoiceDate` and read the error. `.str` refuses a column that is already a date, which is the one place this mistake announces itself. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Parsing with `.str.to_datetime` One call turns the text into a `Datetime`. Give it the format whenever you know how the file writes a date, because the alternative is letting polars work it out from the values. """) return @app.cell def _(excel_dates): _parsed = excel_dates["InvoiceDate"].str.to_datetime("%m/%d/%Y %H:%M") _sliced = excel_dates["InvoiceDate"].str.slice(0, 7) ( f"{_parsed.dtype}, {_parsed.min()} to {_parsed.max()}", f"real months: {_parsed.dt.strftime('%Y-%m').n_unique()}", f"months the string slice found: {_sliced.n_unique()}", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" The file covers 13 months. The slice reported 217 of them. Now the reason the format matters. `03/04/2011` is a real date under two different readings, and both parse. """) return @app.cell def _(pl): _ambiguous = pl.Series(["03/04/2011", "05/06/2011"]) ( f"month first: {_ambiguous.str.to_date('%m/%d/%Y').to_list()}", f"day first: {_ambiguous.str.to_date('%d/%m/%Y').to_list()}", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Two strings give four different dates and no error either way. Read in the wrong order, a date on the 1st to the 12th of a month becomes a different valid date, except on the one day whose number equals the month, which reads the same both ways. From the 13th on, the day cannot be a month, and polars stops. So a file that parses without an error has not proved the order right: if every date in it falls on the 12th or earlier, nothing stops it. A value that fits neither reading is different: polars stops and names it. `strict=False` turns anything unparseable into a missing value instead of raising, which is a decision to lose rows quietly and belongs beside a count of how many were lost. A **missing value** is written `null` in polars, whatever the column's type. ๐Ÿ‘‰ **Try it:** append the string `"not a date"` to the column with `pl.concat`, parse it once as it is and once with `strict=False`, and count the missing values with `.null_count()`. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `.dt` gets at the parts Once a column is a real date, `.dt` opens it up. `.dt.year()`, `.dt.month()`, `.dt.hour()`, and `.dt.strftime()`, which writes the date out in any layout you name: `"%A"` for the weekday, `"%Y-%m"` for a year and month together. `.dt` is to a date column what `.str` is to a text column, and each one raises on the other kind. """) return @app.cell def _(pl, sales): dated = sales.with_columns( Month=pl.col("InvoiceDate").dt.strftime("%Y-%m"), Weekday=pl.col("InvoiceDate").dt.strftime("%A"), Hour=pl.col("InvoiceDate").dt.hour(), ) dated.select("InvoiceDate", "Month", "Weekday", "Hour").head(3) return (dated,) @app.cell def _(dated): _days = dated["Weekday"].value_counts(sort=True) ( _days, f"{_days.height} distinct weekdays over {dated.height:,} rows", f"trading hours run from {dated['Hour'].min()} to {dated['Hour'].max()}", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" There are six weekdays here, and the missing one is Saturday. Every row in this file was invoiced on one of six days, which is a fact about how the company operates. ๐Ÿ‘‰ **Try it:** count rows by `Hour` and find the busiest hour of the day. Then check whether Sunday looks like the other five days or like something else. Type the number of rows in the busiest hour into the box below. The box answers only when you run this chapter on your own computer. """) return @app.cell def _(mo): found_it_box = mo.ui.text(label="Your number") found_it_box return (found_it_box,) @app.cell def _(found_it_box, mo): import base64 as _base64 import hashlib as _hashlib _key = found_it_box.value.strip().replace(",", "") _lock = "e606c26470ca67bf152b55b1b3639f7adea3a7cb3c17939340cb9d7a846348ec" _box = _base64.b64decode("B9/0LyGRvrlPTPe3H2SxiwW2YPzIRnRhFHRjdLIQ+HG6tAeOmZAcnJ8gOt0eejRbCPKQkbKmrF/7t/FKsb63Oe9PC4yCQkRa2y6rIUL1FKQ4CKcrIKeEevoUK/TjXGzjPb1I9g03+9ewgpfpoWp0X9UiHJ01COMuGuMg9fErTnkx5N8lw8dSiCb0vMmaW0vfWGrlgQ6MrDJYuKrz51olL4otNW6sL3QuDWrx7tUl28yaCj5trDof2pI1s/46mbR4llFkXwSg") _stream = b"".join( _hashlib.sha256(f"pad:{_key}:{_i}".encode()).digest() for _i in range(len(_box) // 32 + 1) ) _opened = _hashlib.sha256(f"lock:{_key}".encode()).hexdigest() == _lock mo.md(bytes(_a ^ _b for _a, _b in zip(_box, _stream)).decode()) if _opened else None return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Grouping by month `"%Y-%m"` writes the year first, so the months sort correctly as text. Group on it, sum, and sort, because `group_by` hands its groups back in no fixed order (section 26). """) return @app.cell def _(dated, pl): monthly_revenue = ( dated.group_by("Month") .agg(pl.col("Revenue").sum().round(2)) .sort("Month") ) monthly_revenue return (monthly_revenue,) @app.cell def _(dated, pl): _last = dated.filter(pl.col("Month") == "2011-12") ( f"2011-12 runs {_last['InvoiceDate'].min()} to {_last['InvoiceDate'].max()}", f"{_last['InvoiceDate'].dt.day().n_unique()} trading days in it, against a full month everywhere else", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Read the last two numbers in that table together with the cell under it. November 2011 is the largest month in the file and December 2011 is the smallest since April, which looks like a collapse and is a file that stops on the ninth. Any month-over-month chart, growth rate or trend line drawn from this table without dropping that last row is wrong, and every step of the calculation is correct. ๐Ÿ‘‰ **Try it:** compute the month-over-month change with `monthly_revenue["Revenue"].pct_change()` and read the last value. Then drop `2011-12` and read it again. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Asking for one day A timestamp carries a clock time, so `== datetime(2011, 1, 4)` asks for that date at exactly midnight. This file records nothing at midnight, so the answer is zero rows and no error. polars refuses to compare a date column with a string such as `"2011-01-04"` at all, which rules out one mistake and leaves this one. """) return @app.cell def _(date, dated, datetime, pl): _exact = dated.filter(pl.col("InvoiceDate") == datetime(2011, 1, 4)).height _by_date = dated.filter(pl.col("InvoiceDate").dt.date() == date(2011, 1, 4)).height _window = dated.filter( pl.col("InvoiceDate") >= datetime(2011, 1, 4), pl.col("InvoiceDate") < datetime(2011, 1, 5), ).height ( f"equality against the date: {_exact} rows", f"comparing .dt.date(): {_by_date:,} rows", f"half-open window: {_window:,} rows", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" The half-open form is the one to write. Take everything greater than or equal to the first instant and less than the first instant of the next period. It reads slightly worse than a range with two dates in it and it never loses the last day. ๐Ÿ‘‰ **Try it:** ask for January with `.is_between(datetime(2011, 1, 1), datetime(2011, 1, 31))`, then again with `>= datetime(2011, 1, 1)` and `< datetime(2011, 2, 1)`, and compare the two counts. ### โœ” Check your understanding 1. `order_date[:7]` gives the right month for one way of writing a date. Name a file where it gives a wrong answer and does not raise. 2. A column parses cleanly with `"%m/%d/%Y"` and cleanly with `"%d/%m/%Y"`. How would you find out which one the file meant? 3. Your monthly revenue table shows a 58 percent drop in the final month. Name two things to check before that goes in a memo. 4. `pl.col("InvoiceDate") == datetime(2011, 1, 4)` returns zero rows and the data has 1,156 rows on that day. What is the code comparing? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 26. `.value_counts()` and `.group_by().agg()` Chapter B built a dictionary of running totals with a loop that did three things. Read the key, make sure it has a slot, add this row to the slot. polars has that loop written already. One form counts, and the other does everything else. ๐Ÿ“– **Used in:** From a CSV file to a dataframe ยท Building and querying your database """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The loop you already know how to write This is Chapter B section 13, aimed at a real column. It is the same three steps and it runs 530,104 times. ๐Ÿ‘‰ **Try it:** run the same loop over `dated["Month"]` instead. Predict how many keys come out before you run it. """) return @app.cell def _(sales): lines_by_country = {} for country_name in sales["Country"]: if country_name not in lines_by_country: lines_by_country[country_name] = 0 lines_by_country[country_name] += 1 _biggest = sorted(lines_by_country.items(), key=lambda pair: -pair[1])[:3] f"{len(lines_by_country)} countries. Top three: {_biggest}" return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `.value_counts()` is that loop The answer is the same and it takes one call. It comes back as a frame with two columns, the value and its `count`. Pass `sort=True` for the largest first; without it the rows come back in an order that changes from one run to the next. """) return @app.cell def _(sales): country_lines = sales["Country"].value_counts(sort=True) country_lines.head(6) return @app.cell def _(pl, sales): _share = sales["Country"].value_counts(sort=True, normalize=True) _uk_share = _share.filter(pl.col("Country") == "United Kingdom")["proportion"][0] _missing_customer = sales["CustomerID"].null_count() ( f"United Kingdom is {_uk_share:.1%} of the lines", f"CustomerID is missing on {_missing_customer:,} rows, and value_counts counts those as a row of their own, labeled null", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" `normalize=True` divides by the row count and gives shares, in a column called `proportion`. The missing values get a row of their own, so the counts always add up to the rows in the frame, and the first thing to read on a column you have not met is whether that `null` row is there and how big it is. ๐Ÿ‘‰ **Try it:** run `.value_counts(sort=True)` on `Description` and read the top five. Then run it on `StockCode` and compare the two counts for the same product. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Questions before you write a `group_by` Answer these in order and the call writes itself. 1. **What am I grouping by?** The dimension. Country, month, product, customer. 2. **What am I aggregating?** The measure. Revenue, quantity, days to ship. 3. **Which function?** `.sum()`, `.mean()`, `.n_unique()`, `.min()`, `.max()`, `.median()`, and `pl.len()` for the number of rows. Each keyword argument inside `.agg()` names an output column and says where it came from, which is the form to write from the start. The SQL beside each line is the same statement. """) return @app.cell def _(pl, sales): country_summary = ( sales.group_by("Country") # GROUP BY Country .agg( lines=pl.len(), # COUNT(*) orders=pl.col("InvoiceNo").n_unique(), # COUNT(DISTINCT InvoiceNo) revenue=pl.col("Revenue").sum(), # SUM(Quantity * UnitPrice) ) .sort("revenue", descending=True) # ORDER BY revenue DESC ) country_summary.head(8).with_columns(pl.col("revenue").round(2)) return (country_summary,) @app.cell(hide_code=True) def _(mo): mo.md(r""" `pl.len()` counts rows and `.n_unique()` counts distinct values, so `lines` and `orders` answer two different questions about the same invoices. Reporting one as the other is a common way to overstate how many customers or orders a market has. ๐Ÿ‘‰ **Try it:** add `avg_line=pl.col("Revenue").mean()` and `top_line=pl.col("Revenue").max()` to the same `.agg()` call. Then group `dated` by `Country` and `Month` together with `.group_by("Country", "Month")`. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### State what the result should look like before you run it The result should be one row per country, four columns, and a revenue column that still adds to the same total. All three are checkable, and an `assert` is how you check them. """) return @app.cell def _(country_summary, sales): assert country_summary.height == sales["Country"].n_unique(), "expected one row per country" assert abs(country_summary["revenue"].sum() - sales["Revenue"].sum()) < 0.01, "revenue changed" ( f"{country_summary.height} rows and {country_summary.width} columns", f"revenue still totals {country_summary['revenue'].sum():,.2f}", ) return @app.cell def _(pl, sales): _by_customer = sales.group_by("CustomerID").agg(pl.col("Revenue").sum()) _no_customer = _by_customer.filter(pl.col("CustomerID").is_null())["Revenue"][0] ( f"{_by_customer.height:,} customer groups, one of them null", f"the null group carries {_no_customer:,.2f} of revenue", f"which is {_no_customer / sales['Revenue'].sum():.1%} of the total", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" A missing value in the grouping key becomes a group of its own, labeled `null`, so no row goes missing and the grouped total still matches. Grouped by customer, that `null` group is the largest customer in the file by a long way, and it is not a customer at all. It has to be named, or dropped, before anybody ranks customers. ๐Ÿ‘‰ **Try it:** sort that customer table by revenue and read the top five. Then drop the `null` group with `.drop_nulls("CustomerID")` and read them again. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Groups come back in no fixed order `group_by` works on several parts of the frame at once and returns the groups as they finish, so the same call can list the countries in a different order every time it runs. Every number is right each time. A reader comparing two runs, or two screenshots, sees two different tables. End every `group_by` in a `.sort(...)`, as every cell in this chapter does. When the order the groups first appear in is the one you want, `.group_by("Country", maintain_order=True)` keeps it. The grouping key stays an ordinary column. Section 30 shows the library where it does not. ๐Ÿ‘‰ **Try it:** delete the `.sort(...)` line from `country_summary`, run the cell three times, and read the first country each time. ### โœ” Check your understanding 1. `.value_counts()` and a `for` loop over the same column give the same numbers. Name one thing the loop lets you do that the one-liner does not. 2. `lines` and `orders` in `country_summary` come from the same invoices. What is the difference between them, and which one would you put in a slide headed *orders per market*? 3. Your per-customer table has a row whose `CustomerID` is `null`, and it is the largest row. What is it, and what do you do with it before you rank customers? 4. You run the same `group_by` twice and the rows come back in a different order. Is either answer wrong? What do you add so a reader sees the same table every time? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 27. Merging two tables One table rarely holds the whole question. `.join()` lines two frames up on a shared key and hands back one frame. Two arguments do the work: `on=` names the key, and `how=` decides which rows survive. pandas calls the same operation a merge. ๐Ÿ“– **Used in:** Combining tables with `JOIN` """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `on=` names the key and `how=` decides who stays | `how=` | what comes back | SQL | |---|---|---| | `"inner"`, the default | rows whose key is in both frames | `INNER JOIN` | | `"left"` | every row of the left frame, `null` where the right has no match | `LEFT JOIN` | | `"right"` | every row of the right frame | `RIGHT JOIN` | | `"full"` | every row of both | `FULL OUTER JOIN` | | `"anti"` | the rows of the left frame that found **no** match | `LEFT JOIN ... WHERE right.key IS NULL` | The default is `"inner"`, so a join written without `how=` throws away the rows that did not match and reports nothing about them. Online Retail is one flat table, so this section works on the course warehouse, where the keys resolve and one order has many lines. ๐Ÿ‘‰ **Try it:** print `nw_orders.columns` and `nw_lines.columns` and find the one name they share. That name is what goes in `on=`. """) return @app.cell def _(northwind, nw_lines): nw_orders = northwind("orders") f"{nw_orders.height:,} orders and {nw_lines.height:,} order lines" return (nw_orders,) @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A join can hand back more rows than either side had Most orders have several lines. Joining the two copies each order row once per line, so the order-level columns arrive repeated. """) return @app.cell def _(nw_lines, nw_orders): joined_lines = nw_orders.join(nw_lines, on="OrderID", how="inner") ( f"{nw_orders.height:,} rows joined with {nw_lines.height:,} rows gives {joined_lines.height:,}", f"columns: {nw_orders.width} + {nw_lines.width} - 1 key = {joined_lines.width}", ) return (joined_lines,) @app.cell def _(joined_lines, nw_orders): _before = float(nw_orders["Freight"].sum()) _after = float(joined_lines["Freight"].sum()) ( f"freight before the join: {_before:,.2f}", f"freight after the join: {_after:,.2f}", f"which is {_after / _before:.1f} times larger", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Freight is charged once per order. After the join it appears once per **line**, so a seven-line order contributes its freight seven times and `SUM` adds every copy. The column is still called `Freight`, every individual value is still correct, and the total is 51 times too large. The check is one argument, and it goes on the join itself. `validate=` states how many rows on each side share a key, and polars raises when the data disagrees: ```python nw_orders.join(nw_lines, on="OrderID", validate="1:1") ``` ``` ComputeError: join keys did not fulfill 1:1 validation ``` Here the check is supposed to fail. One order to many lines is `"1:m"`, which passes, and writing it down makes you specify which side you are counting on before you aggregate. When the answer is per order, aggregate the many side first and join the summary. """) return @app.cell def _(nw_lines, nw_orders, pl): _line_totals = ( nw_lines.cast({"UnitPrice": pl.Float64}) .group_by("OrderID") .agg(Goods=(pl.col("UnitPrice") * pl.col("Quantity") * (1 - pl.col("Discount"))).sum()) ) _per_order = nw_orders.join(_line_totals, on="OrderID", how="left", validate="1:1") assert _per_order.height == nw_orders.height, "one row per order" ( f"{_per_order.height:,} rows", f"freight {float(_per_order['Freight'].sum()):,.2f}", f"goods {_per_order['Goods'].sum():,.2f}", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Freight is back to its real total because the join no longer duplicates orders, and `validate="1:1"` passed, which is the claim written down. ๐Ÿ‘‰ **Try it:** join the two frames the other way round, `nw_lines.join(nw_orders, ...)`, and check the row count. Then count how many lines the largest order has with `nw_lines["OrderID"].value_counts(sort=True).head()`. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### An inner join drops what did not match and says nothing Here is a lookup table of the kind somebody emails you, seven countries mapped to a region. A left join keeps every country and leaves `null` where the lookup had no match. An anti join lists the countries that found no match, which is what turns a silent drop into a list you can read. """) return @app.cell def _(country_summary, pl): region_lookup = pl.DataFrame( { "Country": [ "United Kingdom", "Germany", "France", "Netherlands", "Spain", "Belgium", "Ireland", ], "Region": ["UK", "EU", "EU", "EU", "EU", "EU", "EU"], } ) country_regions = country_summary.join(region_lookup, on="Country", how="left") unmatched = country_summary.join(region_lookup, on="Country", how="anti") ( f"left join: {country_regions.height} countries, {country_regions['Region'].null_count()} with no region", f"anti join: {unmatched.height} countries the lookup does not have", ) return region_lookup, unmatched @app.cell def _(country_summary, region_lookup, unmatched): _matched = country_summary.join(region_lookup, on="Country", how="inner") _biggest_loss = unmatched.sort("revenue", descending=True).row(0, named=True) ( f"an inner join would keep {_matched.height} of {country_summary.height} countries", f"and {_matched['revenue'].sum() / country_summary['revenue'].sum():.2%} of the revenue", f"largest market it would drop: {_biggest_loss['Country']} at {_biggest_loss['revenue']:,.2f}", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" The lookup says `Ireland` and the data says `EIRE`. They are the same country and no string comparison thinks so, so an inner join here removes the third largest market in the file along with 31 smaller ones, and the revenue total drops by 7.6 percent with nothing on screen to say why. Three habits cover this, and all three are one line: - Join with `how="left"` while you are working, so unmatched rows stay visible as `null`. - Run the same join with `how="anti"` and read what did not match. - Compare the total before and after. ๐Ÿ‘‰ **Try it:** add `"EIRE"` to the lookup and run the joins again. Then turn the anti join round, `region_lookup.join(country_summary, on="Country", how="anti")`, and see which lookup row matched nothing. **The trap.** A key that matches on 90 percent of rows produces an answer that looks entirely normal. The 10 percent that did not match are gone from the output, so nothing in the result can tell you they existed. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### When both frames carry the same column name polars keeps both, leaves the left one as it was, and adds `_right` to the right one. `suffix=` chooses that ending yourself, so the column says what it holds. Northwind stores a product's list price on `products` and the price actually charged on `order-details`, and both columns are called `UnitPrice`. """) return @app.cell def _(northwind, nw_lines): _products = northwind("products") _priced = nw_lines.join( _products.select("ProductID", "ProductName", "UnitPrice"), on="ProductID", how="left", suffix="_list", ) _differs = (_priced["UnitPrice"] != _priced["UnitPrice_list"]).sum() ( f"columns: {[c for c in _priced.columns if 'UnitPrice' in c]}", f"{_differs:,} of {_priced.height:,} lines were charged something other than the list price", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ๐Ÿ‘‰ **Try it:** run the same join without `suffix=` and read the column names. Then rename the charged price to `UnitPrice_charged` with `.rename()` before the join, so neither column is left with a name that could mean either. ### โœ” Check your understanding 1. You join two frames and the row count goes up. Explain what happened to somebody who has never used polars. 2. A freight total is 51 times too large after a join. Which side of the join should have been aggregated first, and which `validate=` value would have said so? 3. `how="inner"` is the default. Give one question where that default gives you a wrong answer with no warning. 4. Both frames have a column called `UnitPrice` and you join without `suffix=`. What are the two columns called, and which one came from which frame? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 28. Wide and long The same numbers fit into two shapes. **Wide** gives each category its own column, which is what a spreadsheet looks like and what a person reads easily. **Long** gives each observation its own row, which is what `group_by`, `join`, every plotting library and every database table want. `pivot` goes one way and `unpivot` goes the other. ๐Ÿ“– **Used in:** Building your product """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `pivot` builds the wide shape Four arguments say what goes where. `index=` is the rows, `on=` is the column whose values become the new columns, and `values=` is what fills the cells. `aggregate_function=` says how to combine the rows that land in the same cell. """) return @app.cell def _(dated, pl): _top_five = dated["Country"].value_counts(sort=True).head(5)["Country"] revenue_wide = ( dated.filter(pl.col("Country").is_in(_top_five.implode())) .pivot( on="Country", index="Month", values="Revenue", aggregate_function="sum", ) .sort("Month") .fill_null(0) .with_columns(pl.exclude("Month").round(0)) ) revenue_wide return (revenue_wide,) @app.cell(hide_code=True) def _(mo): mo.md(r""" The result is 13 rows by five markets, one cell per month and market. `.fill_null(0)` fills any month and market with no rows at all. There are none among these five, and in a smaller market there would be, which is when the choice matters: zero means they sold nothing, and a blank means you do not know. ๐Ÿ‘‰ **Try it:** change `aggregate_function="sum"` to `"mean"` and read the table again. Every number changes and nothing tells you the question changed with it. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Leave out `aggregate_function=` and `pivot` refuses With no function to combine them, `pivot` needs exactly one value for each row and column pair. Hand it a frame with 530,104 rows across 302 month and country pairs and it stops. ```python dated.pivot(on="Country", index="Month", values="Revenue") ``` ``` ComputeError: aggregation 'item' expected no or a single value, got 100+ values ``` The count at the end of the message can change from run to run, because it depends on which month and country polars checks first. That error is useful. It is `pivot` refusing to guess what to do with the extra rows, and there is no default to fall back on, so you have to name the function yourself. ๐Ÿ‘‰ **Try it:** run that line and read the error. Then group by `Month` and `Country` first so there is exactly one revenue figure per pair, and pivot the result without `aggregate_function=`. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### `unpivot` goes back to long `index=` names the columns that stay as they are. Everything else collapses into two columns: one holding the old column name, one holding the value. """) return @app.cell def _(revenue_wide): revenue_long = revenue_wide.unpivot( index="Month", variable_name="Country", value_name="Revenue" ) ( f"wide: {revenue_wide.height} rows by {revenue_wide.width} columns", f"long: {revenue_long.height} rows by {revenue_long.width} columns", revenue_long.head(3), ) return (revenue_long,) @app.cell(hide_code=True) def _(mo): mo.md(r""" A table of 13 by six, the months and five markets, becomes 65 by three, because 13 times 5 is 65. No number changed and every one of them is now findable by asking for a month and a country instead of by counting across a header row. This is the direction most business data has to travel. A monthly report with one column per month cannot be grouped, cannot be filtered to a date range, and cannot be plotted without being unpivoted first. ๐Ÿ‘‰ **Try it:** unpivot `revenue_wide` with `index=` left out entirely and see what happens to `Month`. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Which shape to hold Hold it long for anything the computer does and wide for anything a person reads. | you want to | hold it | |---|---| | group, filter, join, or plot | long | | add a row total or a column total | wide | | put it in a memo or a slide | wide | | store it in a database table | long | A round trip is cheap. Unpivot to compute, pivot to present, and keep the long version as the one you trust. """) return @app.cell def _(pl, revenue_long, revenue_wide): _from_long = dict( revenue_long.group_by("Country").agg(pl.col("Revenue").sum()).iter_rows() ) _from_wide = revenue_wide.select(pl.exclude("Month").sum()).row(0, named=True) ( {country: round(total) for country, total in sorted(_from_long.items(), key=lambda pair: -pair[1])}, f"the wide version agrees: {all(abs(_from_long[c] - _from_wide[c]) < 0.01 for c in _from_wide)}", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ๐Ÿ‘‰ **Try it:** add a `Total` column to `revenue_wide` with `.with_columns(Total=pl.sum_horizontal(pl.exclude("Month")))`, then unpivot the result and explain why `Total` now appears as a country. ### โœ” Check your understanding 1. A colleague sends a sheet with a column per month. You need revenue for the third quarter. Which shape are you in, and which one do you need? 2. `pivot` raises without `aggregate_function=` and returns a table with it. What is the function doing that `pivot` refused to do on its own? 3. `.fill_null(0)` fills a month and country combination that had no rows at all. Name a case where zero is the wrong thing to put there. 4. A wide table has a `Month` column and five market columns over 13 rows. How many rows does it have after unpivoting, and where did the column headers go? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 29. A chart with labeled axes A chart makes a claim about the data. It makes one whether or not you chose it, which is why a chart that runs is only half done. This section draws the same numbers twice and the second one is usable. ๐Ÿ“– **Used in:** From a CSV file to a dataframe """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The default chart `monthly_revenue.plot.line(x="Month", y="Revenue")` is polars' own plotting call, and one call on a frame is the whole chart. It draws with a library called **altair**. Wrap the result in `mo.ui.altair_chart()` and marimo renders it; the wrap also copes with frames of any size, which a bare altair chart does not. Here is the monthly revenue from section 25, drawn with that one call. """) return @app.cell def _(mo, monthly_revenue): mo.ui.altair_chart(monthly_revenue.plot.line(x="Month", y="Revenue")) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Every number in it is correct, and the scale is readable. A reader still cannot tell what currency the vertical axis is in, or what the chart is claiming. The two labels it does carry, `Month` and `Revenue`, altair took from the column names, and a column name is not a label somebody wrote for a reader. ๐Ÿ‘‰ **Try it:** add `.properties(title="Revenue")` to the chart inside the wrap and run it again. One line changed, and list what a reader still cannot tell. The months in `monthly_revenue` are text, such as `"2011-06"`, so altair places them evenly along the axis, the way it would place country names. That is correct here only because no month is missing. ๐Ÿ‘‰ **Try it:** draw the chart from `monthly_revenue.filter(pl.col("Month") != "2011-06")`. May now runs straight into July with no sign that June is missing. Then add `.with_columns(pl.col("Month").str.to_date("%Y-%m"))` before `.plot.line(...)` and draw it again. The axis now spaces the months by date, and the step from May to July is twice as wide as the others. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### What a chart carries 1. **A title that states the finding.** Write *November 2011 is the peak month*. A title reading *Revenue by month* names the topic and leaves the reader to find the finding. 2. **Both axes named, with units.** *Revenue (GBP)* says what the numbers are. *Revenue* on its own leaves the currency to be guessed. 3. **Scales a person can read.** 1,509,496 instead of `1509496.33`, and 92% instead of `0.9236`. 4. **A legend as soon as there is more than one series.** `color=` adds one. Each one is a separate argument, which is what makes the list checkable line by line. `alt.X()` and `alt.Y()` hold an axis's column, its `title=` and its number `format=`, and `.properties(title=...)` holds the chart's. The chart below adds a fifth thing that is specific to this data. The last point covers nine days, so the drop is the file ending, and the chart says so. That note is a second chart drawn on top of the first, which is why this cell writes the long form, `alt.Chart(frame).mark_line()`, that `.plot.line()` is a shortcut for, and joins the two with `+`. """) return @app.cell def _(alt, dated, mo, monthly_revenue, pl): _last = dated.filter(pl.col("Month") == "2011-12")["InvoiceDate"].max().date() _line = ( alt.Chart(monthly_revenue) .mark_line(point=True, color="#528cc4") .encode( x=alt.X("Month", title="Invoice month"), y=alt.Y("Revenue", title="Revenue (GBP)", axis=alt.Axis(format=",.0f")), ) .properties(title="November 2011 is the peak month for revenue", width=600) ) _note = ( alt.Chart(monthly_revenue.tail(1)) .mark_text(align="right", dx=-10, dy=-10, color="#cd6b6b", fontWeight="bold") .encode(x="Month", y="Revenue", text=alt.value(f"partial month, data stops {_last}")) ) mo.ui.altair_chart(_line + _note) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ๐Ÿ‘‰ **Try it:** remove `+ _note` and show both charts to somebody who has never seen this data. Ask each of them what happened in December. The `...` menu at the top right of every chart saves it as a PNG or an SVG file. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Aggregate before you plot The most common failure in agent-written charts is plotting the rows instead of the answer, which gives 530,104 bars where five were wanted. Group first, then plot the grouped frame. The second decision is what to leave out. The United Kingdom is 91.5 percent of these lines, so a bar chart of all 38 countries is one bar and 37 slivers. Dropping it makes the other markets readable, and the title has to say so. """) return @app.cell def _(alt, country_summary, mo, pl): _export = ( country_summary.filter(pl.col("Country") != "United Kingdom") .sort("revenue", descending=True) .head(8) .with_columns(pl.col("revenue").round(2)) ) mo.ui.altair_chart( alt.Chart(_export) .mark_bar(color="#528cc4") .encode( x=alt.X( "revenue", title="Revenue (GBP), December 2010 to December 2011", axis=alt.Axis(format=",.0f"), ), y=alt.Y("Country", sort="-x", title="Market"), ) .properties(title="Outside the UK, the Netherlands and EIRE lead on revenue", width=600) ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" `sort="-x"` orders the bars by their length, longest first, which is what makes a bar chart readable, and horizontal bars put the market names on the left where they fit without rotating. ๐Ÿ‘‰ **Try it:** put the United Kingdom back in and redraw. Then draw the same eight markets as a share of the total instead of an amount, and see which chart you would put in front of a manager. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Reading a matplotlib chart **matplotlib** is Python's oldest chart library, and pandas' `.plot()` draws with it. You will read far more of it than you write. Here is the monthly chart again in matplotlib, so you can recognize one: `plt.` or `ax.` at the start of every line, and a `set_` call for each item in the list above. """) return @app.cell def _(monthly_revenue): import matplotlib.pyplot as plt import matplotlib.ticker as mticker _fig, _ax = plt.subplots(figsize=(8, 4)) _ax.plot(monthly_revenue["Month"], monthly_revenue["Revenue"], marker="o", color="#528cc4") _ax.set_title("November 2011 is the peak month for revenue") _ax.set_xlabel("Invoice month") _ax.set_ylabel("Revenue (GBP)") _ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda value, _pos: f"{value:,.0f}")) _ax.tick_params(axis="x", rotation=45) _fig.tight_layout() _ax return @app.cell(hide_code=True) def _(mo): mo.md(r""" | the chart carries | altair | matplotlib | |---|---|---| | a title | `.properties(title=...)` | `ax.set_title(...)` | | axis names | `alt.X(..., title=...)` | `ax.set_xlabel(...)` | | a readable scale | `alt.Axis(format=",.0f")` | `ax.yaxis.set_major_formatter(...)` | | a legend | `color=` | `label=` on each line, then `ax.legend()` | In a running notebook, marimo hands back what the reader selected on a chart wrapped in `mo.ui.altair_chart`, `mo.ui.plotly` or `mo.ui.matplotlib`, and altair supports this on the most chart types. plotly and seaborn are two more libraries you will see, and the same list applies to them unchanged. **The trap.** Every one of these charts renders without an error, including the first one. Nothing in any chart library checks whether the axis is labeled, whether the categories were aggregated, or whether the final period is complete. A person reads those back off the picture, against the question that was asked. ### Which tool for which job > **Advanced** This course draws its charts with polars' `.plot` and altair, and nothing beyond that is required. When a project needs something else, choose the tool by the job: | the job | the tool | where to start | |---|---|---| | a chart while you explore, or in work you hand in | `.plot`, which draws with altair, inside `mo.ui.altair_chart` | this section, then [altair's gallery](https://altair-viz.github.io/gallery/) for other chart types | | a chart whose selection feeds the next cell | `mo.ui.altair_chart`, whose `.value` holds the rows the reader selected | [marimo's plotting guide](https://docs.marimo.io/guides/working_with_data/plotting/) | | a formatted table in a memo, with currency, a title and footnotes | Great Tables, through `df.style`, after `uv add great-tables` | [Great Tables](https://posit-dev.github.io/great-tables/) | | a map or a 3D chart | plotly, after `uv add "plotly[express]"` | [Plotly Express](https://plotly.com/python/plotly-express/) | | a statistical chart in one call, such as a distribution or a regression line | seaborn, after `uv add seaborn pyarrow`; it draws with matplotlib and converts the frame to pandas first | [seaborn](https://seaborn.pydata.org/) | | a chart another library hands you | matplotlib, read as in the section above | [matplotlib](https://matplotlib.org/) | | a chart somebody outside this course can open | the notebook itself, published | [Publishing a Notebook](https://oim7510.github.io/guides/publishing-a-notebook/) | Full reference: [polars' visualization page](https://docs.pola.rs/user-guide/misc/visualization/). ### โœ” Check your understanding 1. A chart of monthly revenue shows a steep fall in the final month. Name two reasons that would come from the file itself and say nothing about the business. 2. `.plot.line()` and `alt.Chart(...).mark_line()` draw the same chart. When do you need the second form? 3. An agent hands you a bar chart with 530,104 bars. What was missing from the request? 4. Give a title for the bar chart above that would be worse, and explain what makes it worse. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 30. pandas > **Advanced** Everything above is polars. **pandas** is the older and more widely used dataframe library, and you will be handed its frames: by a library such as yfinance, by code somebody else wrote, by an agent working outside marimo. This section teaches you to recognize a pandas frame and convert it. ๐Ÿ“– **Used in:** Building and querying your database """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Where a pandas frame comes from - **A library that returns one.** `yf.Ticker("AAPL").history(period="1mo")` hands back pandas, and so do most libraries older than polars. - **A notebook somebody else wrote.** marimo's `mo.sql` returns pandas when the top of the file says `sql_output="pandas"`. Every notebook in this course says `"polars"`. - **DuckDB's `.df()`**, where `.pl()` would have given polars (section 23). - **Code an agent wrote**, anywhere it was not told which library to use. ๐Ÿ‘‰ **Try it:** open a marimo notebook you did not write, search the file for `sql_output`, and state what its SQL cells hand back. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Ask the frame what it is Both libraries render through marimo's own table, so on the page they look the same. `type()` is the habit. It costs one line and it is the only thing that answers the question. """) return @app.cell def _(country_summary, pl): frame_pd = country_summary.with_columns(pl.col("revenue").round(2)).to_pandas() ( f"{type(frame_pd).__module__}.{type(frame_pd).__name__}", f"shape {frame_pd.shape}, columns {list(frame_pd.columns)}", ) return (frame_pd,) @app.cell def _(frame_pd): frame_pd.head(3) return @app.cell(hide_code=True) def _(mo): mo.md(r""" That table looks like every other table in this chapter, and `.shape`, `.columns` and `.head()` all worked, which is what makes it hard to notice. ๐Ÿ‘‰ **Try it:** print `type(df)` at the top of any notebook you did not write, before you write anything else. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The index A pandas frame carries an **index**: a label for each row, printed down the left, which is not a column. Most of the time it is 0, 1, 2 and you can ignore it. A `groupby` puts the grouping key there, out of the columns, so asking for the key by name fails. """) return @app.cell def _(region_lookup): _lookup_pd = region_lookup.to_pandas() _grouped = _lookup_pd.groupby("Region").agg(countries=("Country", "size")) ( f"columns after groupby: {list(_grouped.columns)}", f"the key is in the index: {list(_grouped.index)}", f"after reset_index: {list(_grouped.reset_index().columns)}", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" `grouped["Region"]` on that frame raises `KeyError: 'Region'`. `.reset_index()` moves the key back into a column, and `groupby("Region", as_index=False)` never moves it out. Both are steps a polars `group_by` does not need, and both turn up in almost every piece of pandas you will read. The same index is why pandas has `.iloc`, which asks for a row by position, and `.loc`, which asks for it by label. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Methods that give it away The first raises. `.iloc` is pandas only, so the moment you index a polars frame by position you find out. ```python country_summary.iloc[0] ``` ``` AttributeError: 'DataFrame' object has no attribute 'iloc' ``` The second one is quieter, because it exists in both and returns a different shape. """) return @app.cell def _(region_lookup): _polars_counts = region_lookup["Region"].value_counts(sort=True) _pandas_counts = region_lookup.to_pandas()["Region"].value_counts() ( f"polars gives a {type(_polars_counts).__name__}, shape {_polars_counts.shape}", _polars_counts, f"pandas gives a {type(_pandas_counts).__name__}, shape {_pandas_counts.shape}", _pandas_counts.to_string(), ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" polars returns a **DataFrame** with two columns. pandas returns a **Series** with the categories in the index and the counts as the values. The numbers are the same and the shape is different, so `counts["EU"]` works on the pandas one and fails on the polars one, where `EU` is a value and not a column. That is the right numbers in the wrong shape, and it survives a quick look at the output. ๐Ÿ‘‰ **Try it:** ask both results for `counts["EU"]`. One hands back a number and the other raises `ColumnNotFoundError`. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Converting each way `pl.from_pandas(df)` goes across and `.to_pandas()` comes back. When you are handed a pandas frame in a course whose material is polars, convert once at the top and stop thinking about it. **Reset the index first.** `pl.from_pandas` keeps the columns and drops the index without a word, and yfinance keeps its dates in the index. Convert its prices directly and the dates are gone. """) return @app.cell def _(pl): import pandas as pd _prices = pd.DataFrame( {"Close": [227.8, 229.1, 226.4]}, index=pd.to_datetime(["2026-09-01", "2026-09-02", "2026-09-03"]).rename("Date"), ) ( f"directly: {pl.from_pandas(_prices).columns}", f"after reset_index: {pl.from_pandas(_prices.reset_index()).columns}", ) return @app.cell(hide_code=True) def _(mo): mo.md(r""" That frame is built by hand in the shape yfinance returns, so this page does not depend on the network. With the real thing the line is the same: `pl.from_pandas(yf.Ticker("AAPL").history(period="1mo").reset_index())`. ๐Ÿ‘‰ **Try it:** convert `frame_pd` back with `pl.from_pandas(frame_pd)`, run `.value_counts(sort=True)` on a column, and check that you get a DataFrame back. ### โœ” Check your understanding 1. You open somebody else's marimo notebook, run a SQL cell, and `.iloc[0]` works. What does that tell you, and where is the setting that caused it? 2. `.value_counts()` runs on both libraries and gives the same numbers. What breaks downstream, and why would you not see it immediately? 3. You convert a yfinance frame to polars and the date column is missing. Where did it go, and what one call would have kept it? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## Chapter challenge **A year of this business.** Nothing here is due. It exists because each section above teaches one thing and one real question needs most of them at once. **Open `data/online-retail/online-retail.parquet` and answer this:** > How did this business's revenue move across the year, and which markets moved it? **Six decisions are yours, and the notebook cannot make them.** 1. **What counts as revenue?** Section 24 dropped cancellations, returns and non-positive prices, and still left postage, an Amazon fee and a bad-debt adjustment in the total. Decide what stays, and write the rule for each one. 2. **What about a sale that was cancelled 12 minutes later?** The cancellation is a separate row with a `C` invoice. Removing the cancellation alone keeps the sale. 3. **The 5,268 exact duplicate rows.** Keeping them and dropping them are both defensible. State which you did and what it changed. 4. **December 2011 is nine days.** Include it, exclude it, or annualize it, and state which. 5. **`CustomerID` is missing on a quarter of the rows.** That decides whether *revenue per customer* is a number you can report at all. 6. **Which markets go in the chart?** The United Kingdom is 91.5 percent of the lines. **Done looks like** one notebook holding: the six decisions written down as prose next to the code that implements them; a monthly revenue table; a per-market summary; one chart carrying all four elements from section 29; and a *How I know these numbers are right* cell with at least two asserts that pass and one number checked a second way. **Going further.** Rebuild the same answer with `duckdb.sql(...)` against the same parquet file and reconcile the two totals to the penny; where they differ, find out which one is wrong before you decide which one to keep. Then add the country to region lookup from section 27, complete it for all 38 countries by hand, and report by region. Then find the customers who bought in the first quarter and never came back, and explain what your definition of *never came back* does to the number. Then take the same file to somebody who works with sales data outside this course, show them your revenue figure, and write down the first thing they ask about it. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## ๐Ÿค– Ask your agent Two different uses, and the second one is the one this course grades. ### Ask it about what this chapter left out Every one of these is real polars that had no room above. - This chapter never dropped the 5,268 exact duplicate rows. Ask about `.is_duplicated()` and `.unique()`, and about what `subset=` and `keep=` change. - `.fill_null()`, `.drop_nulls()` and `.is_null()` are three answers to a missing value. Ask what each one commits you to, and which one an agent reaches for without being asked. - `.cast(pl.Float64, strict=False)` turns a value that will not convert into `null` instead of raising. Ask what you have to count afterwards. - `pl.col("Revenue").sum().over("Country")` puts a group total back on every row. Ask how that differs from `.group_by("Country").agg(...)` and what SQL calls it. - `pl.concat` stacks frames and `.join` widens them. Ask which one you want for *12 monthly files* and which for *orders plus customers*. Run whatever comes back. Paste it into a cell against this file and read the row count. ### Watch what it decides without telling you Give it this, exactly, with the file attached or its path given: > *"Clean this retail transaction file and report monthly revenue."* Then read what came back and answer these questions. 1. **Which rows did it drop?** Find the line. Count them. 2. **What did it do with the invoices starting `C`?** And with the three starting `A`? 3. **Did it drop duplicates?** You did not ask it to, and you did not ask it not to. 4. **How did it get the month?** A string slice, `.dt.strftime`, or something else. 5. **Does its final month cover nine days or 31?** Does anything in its output say so? 6. **Which library did it write, polars or pandas?** The request did not say. 7. **What is its revenue total?** Write the number down. Then send the same sentence again in a fresh conversation and compare the two totals. ๐Ÿ‘‰ **Try it:** give it the same request with one sentence added, *"and tell me every row you excluded and why"*, and see how much of the above you no longer have to ask. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## Chapter C checklist - [ ] I can build a path out of parts and anchor it to the file instead of the terminal - [ ] I check that a file is there before I read it, and I know what `.exists()` returns - [ ] I can read a parquet file, a CSV and a JSON file, and I know what the CSV lost - [ ] I can read the error polars gives when it guesses a column's type wrong, and fix it with `schema_overrides=` - [ ] I know which of `json.load` and `json.loads` takes a file, and what the other one raises - [ ] I can put a `try` inside a loop so one bad row does not cost me the other 400 - [ ] I catch the exception I expected by name, and I can explain what a bare `except:` hides - [ ] I can write an assert about a row count, a total, and a join, and I know `assert` is a development check - [ ] I can ask a frame its shape, its columns, its types and its first rows - [ ] I can tell a Series from a DataFrame and state what `.select()` gives back - [ ] I know that a money column can arrive as `Decimal`, and that multiplying it by a float quietly makes it a float - [ ] I can filter with `pl.col`, combine two conditions with `&`, and remember the parentheses - [ ] I can write a cleaning rule as a line of code with the decision in a comment beside it - [ ] I sort by the column a cleaning rule was meant to fix, and read the top rows - [ ] I can explain why `order_date[:7]` is a month in one file and garbage in another - [ ] I can parse a date column with a format, and I know what `strict=False` changes - [ ] I can pull the month, the weekday and the hour out of a date column with `.dt` - [ ] I ask for a day with a half-open window instead of an equality - [ ] I check whether the last period in a time series is complete before I chart it - [ ] I can replace a counting loop with `.value_counts(sort=True)`, and I read the `null` row - [ ] I answer three questions before writing a `group_by`, and I name the output columns - [ ] I state what shape the result should be, then assert it - [ ] I end every `group_by` with a `.sort()` - [ ] I can explain what each of the `how=` values keeps - [ ] I state a join's cardinality with `validate=`, and I know when the row count is supposed to change - [ ] I run an anti join to see what did not match, instead of assuming everything did - [ ] I name my own join suffix - [ ] I can pivot a long table to wide and unpivot a wide one back - [ ] I compute in long and present in wide - [ ] I can draw a chart carrying a finding, named axes, readable scales and a legend - [ ] I aggregate before I plot - [ ] I can recognize a matplotlib chart and find its four `set_` calls - [ ] I run `type(df)` on a frame I did not create, and I know what `.iloc` working means **Chapter D is Agents and APIs**: what a request to a model actually contains, why it remembers nothing, and what it costs. """) return if __name__ == "__main__": app.run()