# pandas From Zero > Learn the Python data-analysis workhorse: the DataFrame mental model, loading and inspecting data, selecting and filtering, cleaning messy data, transforming with vectorized operations, the split-apply-combine power of groupby, joining datasets, time series, reshaping and pivoting, and plotting. The tool every Python data person reaches for, explained idea-first. --- # pandas From Zero If you do anything with data in Python - analysis, cleaning, reporting, feeding a machine-learning model - you'll do it through **pandas**. It's the library that turns a messy CSV into something you can query, reshape, and summarize with a few lines instead of a hundred. The mental model that makes it click is simple: pandas gives you a **DataFrame**, which is a spreadsheet or a SQL table that lives in memory and that you manipulate with code. Once you think of it that way - rows, columns, filters, group-bys, joins - the whole library lines up with things you already understand from Excel and SQL. We build that model first the whole way, and we lean on one habit that separates people who fight pandas from people who fly with it: **think in whole columns, not in loops.** pandas operations work on entire columns at once (vectorized), which is both faster and clearer than looping row by row. By the end you'll load real data, clean it, filter it, group and aggregate it, join datasets, and chart the result. > 📝 This assumes you know **Python** - lists, dicts, functions ([Python From Zero](/guides/python-from-zero)). > No data-science background needed. It pairs naturally with the data work in > [What Data Engineering Is](/guides/what-is-data-engineering) and > [Spreadsheets to SQL to Pipelines](/guides/spreadsheets-to-sql-to-pipelines). > > 💡 The best way to learn pandas is hands-on: open a Jupyter notebook (or any Python REPL) and type these > examples as you read - each one shows its output so you can check yourself. ## How to read this Read in order - it works one small sales dataset from raw CSV to a finished chart, adding one pandas skill per phase. Phases carry difficulty badges. ## The phases **Part 1 - The basics (🟢 Basic)** 1. **[What pandas Is & the DataFrame](01-what-pandas-is.md)** 🟢 - Series vs DataFrame, and the "spreadsheet/table in memory" mental model. 2. **[Loading & Inspecting Data](02-loading-and-inspecting-data.md)** 🟢 - `read_csv` and friends, then `head`/`info`/`describe` to know what you've got. 3. **[Selecting & Filtering](03-selecting-and-filtering.md)** 🟢 - columns, `loc`/`iloc`, and boolean masks to slice the data you want. **Part 2 - Real data work (🟡 Intermediate → 🔴)** 4. **[Cleaning Data](04-cleaning-data.md)** 🟡 - missing values, types, duplicates, and messy strings. 5. **[Transforming Data](05-transforming-data.md)** 🟡 - new columns, `apply`/`map`, and why you vectorize instead of loop. 6. **[GroupBy & Aggregation](06-groupby-and-aggregation.md)** 🔴 - split-apply-combine, the single most powerful pandas idea. 7. **[Joining & Combining](07-joining-and-combining.md)** 🟡 - `merge` (the SQL joins), `concat`, and stitching datasets together. 8. **[Time Series & Dates](08-time-series.md)** 🟡 - parsing dates, date indexing, and resampling. 9. **[Reshaping & Pivoting](09-reshaping-and-pivoting.md)** 🟡 - `pivot_table`, `melt`, and wide-vs-long data. **Part 3 - Output & beyond (🟢)** 10. **[Plotting & Where to Go Next](10-plotting-and-where-next.md)** 🟢 - quick charts, performance habits, and what to learn next. > The throughline: a DataFrame is a table you compute on, and almost everything is a column operation. > Hold those two ideas and pandas stops being a grab-bag of methods and becomes a coherent tool. --- # What pandas Is & the DataFrame If you've ever stared at a spreadsheet and thought "I wish I could do this with code instead of dragging formulas around," pandas is the answer. It's the library Python people reach for the moment data is involved - and before you write a single line of it, there's one idea that makes everything else fall into place. Get this idea, and pandas stops being a wall of unfamiliar methods and becomes a tool you already half-understand. That idea is this: **a DataFrame is a spreadsheet - or a SQL table - that lives in your computer's memory, and you poke at it with code instead of a mouse.** Rows and columns. Filters. Group-bys. Joins. You know these from Excel and SQL ([Spreadsheets to SQL to Pipelines](/guides/spreadsheets-to-sql-to-pipelines)) already. pandas gives you the same shapes with a programmer's superpower: repeatable, scriptable, version-controllable operations on data of any size. ## What pandas actually is 📝 **pandas** - a Python library for working with **tabular data** (rows and columns). It's built on top of **NumPy** (Python's fast numerical array library), and it's the standard, default tool for data analysis, cleaning, and prep in Python. If a data scientist or analyst is writing Python, they're using pandas. The reason it won is the mental model. A spreadsheet is a grid you edit by hand. A SQL table is a grid you query. pandas is a grid that *lives in a variable* - you load it once, then transform it with code, see the result, transform it again. Fast feedback, no manual steps, every operation written down. By universal convention, you import it under the name `pd`: ```python import pandas as pd ``` *What just happened:* You pulled in the library and gave it the short alias `pd`. Every pandas tutorial, Stack Overflow answer, and codebase on Earth writes `pd.something`. Follow the convention - `import pandas as pd` is as standard as it gets, and writing it any other way only makes your code harder for others (and future you) to read. > 💡 pandas isn't available in this guide's in-browser runtime, so the code blocks here aren't runnable. To > follow along for real, type these into a Jupyter notebook or a Python REPL where pandas is installed > (`pip install pandas`). Each example shows its output so you can check yourself either way. ## The Series - one labeled column Before the table, the column. The building block of pandas is the **Series**. 📝 **Series** - a single column of data: a one-dimensional array of values, each paired with a **label** (its index). Think of it as one column lifted out of a spreadsheet, carrying its row labels with it. Let's make one from the `units` figures in our running sales dataset: ```python import pandas as pd units = pd.Series([10, 4, 7, 12]) print(units) ``` ```console 0 10 1 4 2 7 3 12 dtype: int64 ``` *What just happened:* You handed pandas a plain Python list and got back a Series. Notice there are **two** columns in that output, not one. On the right are your values (`10, 4, 7, 12`). On the left - the `0, 1, 2, 3` - is the **index**: the label attached to each value. You didn't ask for it; pandas added a default numbered index automatically. The `dtype: int64` at the bottom tells you pandas figured out these are 64-bit integers (a NumPy type - that's the NumPy foundation showing through). A Series is always "values + their labels," and that left-hand index is the part that makes pandas different from a bare list. ## The DataFrame - a table of Series Now the main event. Stack several Series side by side, sharing one set of row labels, and you have a **DataFrame**. 📝 **DataFrame** - a table with rows and columns. Mechanically, it's a dict of Series that all share the same index. Each **column** is a Series; the **index** labels the rows they have in common. This is the object you'll spend 95% of your pandas life working with. Here's our sales dataset - five columns: `date`, `product`, `region`, `units`, `price` - built from a dict where each key is a column name and each value is that column's data: ```python import pandas as pd sales = pd.DataFrame({ "date": ["2024-01-05", "2024-01-05", "2024-01-06", "2024-01-06", "2024-01-07"], "product": ["Widget", "Gadget", "Widget", "Gadget", "Widget"], "region": ["North", "South", "North", "West", "South"], "units": [10, 4, 7, 12, 5], "price": [9.99, 19.99, 9.99, 19.99, 9.99], }) print(sales) ``` ```console date product region units price 0 2024-01-05 Widget North 10 9.99 1 2024-01-05 Gadget South 4 19.99 2 2024-01-06 Widget North 7 9.99 3 2024-01-06 Gadget West 12 19.99 4 2024-01-07 Widget South 5 9.99 ``` *What just happened:* You passed a dict of equal-length lists, and pandas assembled them into a table. Each dict key became a **column header**; each list became that column's values. Down the left edge is the same default index (`0`–`4`) you saw on the Series - it labels the rows, and every column shares it. That's the "dict of Series sharing one index" definition made concrete. Pull out any single column and you get a Series right back: ```python print(sales["product"]) ``` ```console 0 Widget 1 Gadget 2 Widget 3 Gadget 4 Widget Name: product, dtype: object ``` *What just happened:* Indexing the DataFrame with a column name (`sales["product"]`) handed you that one column as a Series - values plus the shared index, with `Name: product` noting which column it came from. `dtype: object` is how pandas labels columns of text/strings. A DataFrame really is just its columns; ask for one and a Series falls out. ## The index - labels, not row numbers You've now seen that `0, 1, 2, ...` running down the left side three times. It's worth slowing down on, because it's a pandas signature that trips up newcomers. 📝 **Index** - the set of labels for the rows of a Series or DataFrame. By default it's a range `0, 1, 2, … n-1`, but it doesn't have to be numbers, and it doesn't have to be sequential. It's how pandas finds rows and **aligns** data across operations. The default index is just pandas being helpful when you didn't supply labels. But you can promote any column to *be* the index - useful when a column is a natural identifier for each row, like the date: ```python by_date = sales.set_index("date") print(by_date) ``` ```console product region units price date 2024-01-05 Widget North 10 9.99 2024-01-05 Gadget South 4 19.99 2024-01-06 Widget North 7 9.99 2024-01-06 Gadget West 12 19.99 2024-01-07 Widget South 5 9.99 ``` *What just happened:* `set_index("date")` moved the `date` column out of the body and made it the row labels. Now rows are identified by their date instead of by `0`–`4`. `date` is no longer a regular column you'd select - it's the index, sitting in that left-hand position. (`set_index` returns a *new* DataFrame; your original `sales` is untouched.) ⚠️ **Gotcha - the index is a label, not a row number.** It looks like a counter, but it isn't one. When you filter or sort a DataFrame, rows keep their original index labels - so after filtering you might see an index like `0, 2, 4`, with gaps. More importantly, pandas uses the index to **align** data: when you combine two Series or DataFrames, it matches them up *by index label*, not by position. Treat the index as each row's identity, and the surprises you'll hit later (mismatched merges, `NaN`s appearing out of nowhere) start making sense. ## Why columns, not loops Here's the habit that separates people who fight pandas from people who fly with it. Coming from regular Python ([Python From Zero](/guides/python-from-zero)), your instinct for "compute revenue for every row" is a loop: ```python # The Python instinct - DON'T do this in pandas revenue = [] for i in range(len(sales)): revenue.append(sales["units"][i] * sales["price"][i]) ``` That works, but it's slow, verbose, and not how pandas thinks. pandas operates on **whole columns at once** - a style called **vectorization**, powered by the NumPy underneath. You write the operation as if the columns were single values, and pandas applies it to every row in one fast, C-level sweep: ```python sales["revenue"] = sales["units"] * sales["price"] print(sales) ``` ```console date product region units price revenue 0 2024-01-05 Widget North 10 9.99 99.90 1 2024-01-05 Gadget South 4 19.99 79.96 2 2024-01-06 Widget North 7 9.99 69.93 3 2024-01-06 Gadget West 12 19.99 239.88 4 2024-01-07 Widget South 5 9.99 49.95 ``` *What just happened:* `sales["units"] * sales["price"]` multiplied the two columns element-by-element - row 0's units times row 0's price, row 1's times row 1's, all the way down - and produced a new Series of results in one expression. Assigning it to `sales["revenue"]` added that result as a brand-new column. No loop, no index bookkeeping, no `append`. One line that reads like the math you actually mean, and it runs far faster than the loop because the work happens in NumPy's compiled core, not in Python. 💡 **Key point.** Think in columns, not rows. Almost everything in pandas is a whole-column (vectorized) operation: arithmetic, comparisons, string methods, filters. Whenever you catch yourself about to write `for i in range(len(df))`, stop - there's almost always a column expression that's shorter, clearer, and much faster. This single habit runs through the entire rest of this guide. ## Recap 1. **pandas** is Python's standard library for tabular data, built on **NumPy**. The mental model: a **DataFrame is a spreadsheet / SQL table that lives in memory** and that you manipulate with code. 2. Import it the one true way: `import pandas as pd`. 3. A **Series** is one labeled column - values paired with an **index**. A **DataFrame** is a dict of Series sharing one index: a table whose every column is a Series. 4. Every Series and DataFrame has an **index** labeling its rows (default `0…n-1`). ⚠️ The index is a *label*, not a row number - operations align data by index, and you can set any column as the index. 5. Build a DataFrame from a dict of equal-length lists (one key per column); select a column with `df["name"]` and you get a Series back. 6. **Think in columns, not loops.** Vectorized operations like `df["price"] * df["units"]` compute a whole new column in one fast, readable step - the core habit for the rest of this guide. ## Quick check Test yourself on the two ideas that everything else builds on - what a DataFrame *is*, and the column-first habit: ```quiz [ { "q": "What is the best mental model for a pandas DataFrame?", "choices": [ "A spreadsheet or SQL table that lives in memory and that you manipulate with code", "A faster replacement for Python's print() function", "A file format for saving data to disk", "A type of for-loop that runs over rows one at a time" ], "answer": 0, "explain": "A DataFrame is a table of rows and columns held in memory - like a spreadsheet or SQL table - that you transform with code instead of a mouse or a query window." }, { "q": "How does a Series relate to a DataFrame?", "choices": [ "A Series is one labeled column; a DataFrame is several Series sharing one index", "A Series is a DataFrame with no index", "They are completely unrelated objects", "A DataFrame is a single Series with extra colors" ], "answer": 0, "explain": "A Series is a single column (values plus an index). A DataFrame is a dict of Series that all share the same row index - so each column of a DataFrame is itself a Series." }, { "q": "Why prefer `sales[\"units\"] * sales[\"price\"]` over a Python for-loop over the rows?", "choices": [ "It is vectorized - pandas computes the whole column at once in NumPy's fast core, and reads more clearly", "Loops are not allowed anywhere in pandas", "It rounds the numbers automatically to save memory", "It is the only way to create a new column" ], "answer": 0, "explain": "Vectorized column operations apply to every row in one compiled NumPy sweep - faster than a Python loop and far easier to read. Thinking in whole columns instead of row loops is the central pandas habit." } ] ``` --- # Loading & Inspecting Data In Phase 1 we hand-built a tiny DataFrame to learn what one *is*. Real work starts differently - with a file somebody emailed you, or a query result, or an export from a tool - and the very first question is always the same: *what is actually in here?* Here's the mental model for this whole phase: **loading data and trusting data are two separate steps, and the gap between them is where bugs are born.** pandas will happily load almost anything and guess what it means. Sometimes it guesses wrong - a date read as text, a number read as a string, a blank cell read as the literal word "NA". So the rhythm we build here is: load the data, then *interrogate* it with a handful of inspection commands before you compute anything. Inspect first, analyze second. That habit will save you more grief than any clever one-liner. We'll work the same small sales dataset the whole guide uses: each row is a sale, with a date, a product, a region, a unit count, and a price. ## Reading real data - `read_csv` and friends 📝 The overwhelming majority of analysis begins with `pd.read_csv("something.csv")`. CSV (comma-separated values) is the lingua franca of data: every tool can export it, and pandas reads it in one line. pandas also has a matching reader for nearly every other format you'll meet: - `pd.read_csv(...)` - comma-separated (and tab/pipe/anything-separated) text files. - `pd.read_excel(...)` - `.xlsx` / `.xls` spreadsheets (needs the `openpyxl` package). - `pd.read_json(...)` - JSON data. - `pd.read_sql(query, connection)` - the result of a SQL query, straight into a DataFrame. - `pd.read_parquet(...)` - Parquet, a fast compressed columnar format common in data pipelines. They all return the same thing: a DataFrame. Learn the inspection habits once and they apply no matter how the data arrived. Say we have a file `sales.csv` that looks like this: ```text date,product,region,units,price 2026-01-03,Widget,North,12,9.99 2026-01-03,Gadget,South,5,19.50 2026-01-04,Widget,East,8,9.99 2026-01-05,Gadget,North,3,19.50 2026-01-05,Doohickey,West,20,4.25 ``` Loading it is one line: ```python import pandas as pd df = pd.read_csv("sales.csv") ``` *What just happened:* `read_csv` opened the file, used the first line as column names (`date`, `product`, `region`, `units`, `price`), split every following line on commas, and built a DataFrame with one row per sale. You now have a table in memory called `df`. That worked because the file was clean. Real CSVs rarely are, so `read_csv` has arguments for the common messes. The ones you'll reach for constantly: ```python df = pd.read_csv( "sales.csv", sep=",", # the delimiter; use "\t" for tab-separated, "|" for pipe header=0, # which row holds the column names (0 = first row; None = no header) parse_dates=["date"], # read these columns as real dates, not text dtype={"units": "int64"}, # force a column's type instead of letting pandas guess na_values=["", "NA", "n/a"], # treat these strings as missing (NaN) usecols=["date", "product", "units", "price"], # load only the columns you need nrows=1000, # load only the first N rows (great for peeking at a huge file) ) ``` *What just happened:* each argument steers one part of the read. `sep` picks the delimiter; `header` says where the names live; `parse_dates` turns the `date` column into actual datetime values (so you can do date math later); `dtype` pins a column's type so pandas can't guess wrong; `na_values` tells pandas which oddball strings mean "missing"; `usecols` and `nrows` keep memory down by loading less. You rarely need all of these at once - reach for them when the defaults misread your file. ⚠️ **`read_csv` guesses column types, and guesses can be wrong.** A CSV is just text - there are no types in the file itself, so pandas infers them from the values it sees. A ZIP code like `02134` can come in as the number `2134` (leading zero gone); a column with one stray letter in it gets read as text instead of numbers. Never assume the types are right. *Verify them* - which is exactly what the rest of this phase is about. ## First look: `head`, `tail`, `sample` The instant you load data, look at some of it. Three methods show you rows: - `df.head(n)` - the first `n` rows (default 5). - `df.tail(n)` - the last `n` rows. - `df.sample(n)` - `n` random rows. ```python df.head() ``` ```console date product region units price 0 2026-01-03 Widget North 12 9.99 1 2026-01-03 Gadget South 5 19.50 2 2026-01-04 Widget East 8 9.99 3 2026-01-05 Gadget North 3 19.50 4 2026-01-05 Doohickey West 20 4.25 ``` *What just happened:* `head()` showed the top 5 rows plus the header and that leftmost unlabeled column - the **index** (0, 1, 2, …), pandas' built-in row labels. In two seconds you can confirm the columns landed in the right places, the values look sane, and nothing shifted by one. 💡 `head` answers "did this load like I expect?"; `tail` is great for spotting a junk summary row at the bottom of a spreadsheet export; `sample` guards against being fooled by data that happens to be sorted (the first 5 rows of a date-sorted file all look like January). ## Shape & structure: `info`, `dtypes`, `shape`, `columns` `head` shows you *values*. Next you need the *structure*: how big is this, what are the columns, what type is each, and where are the holes? 📝 **`df.info()` is the single best first command on any new DataFrame.** In one printout it tells you the number of rows, every column name, how many non-null (non-missing) values each column has, and each column's type. It's your data's vital signs. ```python df.info() ``` ```console RangeIndex: 5 entries, 0 to 4 Data columns (total 5 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 date 5 non-null object 1 product 5 non-null object 2 region 5 non-null object 3 units 5 non-null int64 4 price 5 non-null float64 dtypes: float64(1), int64(1), object(3) memory usage: 332.0 bytes ``` *What just happened:* `info()` reported 5 rows (`RangeIndex: 5 entries`), 5 columns, and for each column the **non-null count** and the **dtype**. Two things jump out: every column is `5 non-null`, so there's no missing data - good. But notice `date` is type **`object`**, not a date. We loaded this without `parse_dates`, so pandas read the dates as plain text. That's a bug waiting to happen, and `info` caught it in one line. The companions to `info` give you each piece on its own: ```python print(df.shape) # (rows, columns) print(df.columns) # the column names print(df.dtypes) # each column's type ``` ```console (5, 5) Index(['date', 'product', 'region', 'units', 'price'], dtype='object') date object product object region object units int64 price float64 dtype: object ``` *What just happened:* `shape` returned the tuple `(5, 5)` - 5 rows, 5 columns (rows always come first). `columns` listed the names so you can copy them exactly. `dtypes` gave the per-column types: `units` is `int64` (integers), `price` is `float64` (decimals), and the text columns are `object`. ⚠️ **A dtype of `object` almost always means "Python strings" - or, worse, mixed junk.** That's expected for genuine text like `product` and `region`. But if a column you *expect* to be numeric shows up as `object`, that's a red flag: pandas hit something non-numeric (a stray `"unknown"`, a `"$9.99"` with a dollar sign, a number with a thousands comma) and gave up, storing the whole column as text. You can't average a text column. Spotting an `object` where a number belongs - right here, before you compute - is the difference between a clean analysis and a confusing crash later. ## Summary stats: `describe`, `value_counts`, `nunique` Now that you trust the structure, get a feel for the *distribution* of values. Three methods do the heavy lifting. `df.describe()` gives a numeric summary of every number column at once: ```python df.describe() ``` ```console units price count 5.000000 5.000000 mean 9.600000 12.646000 std 6.580274 6.605779 min 3.000000 4.250000 25% 5.000000 9.990000 50% 8.000000 9.990000 75% 12.000000 19.500000 max 20.000000 19.500000 ``` *What just happened:* `describe()` computed, for each numeric column, the **count** (non-missing values), **mean** (average), **std** (standard deviation, a spread measure), the **min** and **max**, and the **quartiles** - `25%`, `50%` (the median), and `75%`. At a glance: units run 3 to 20 averaging ~9.6; prices run \$4.25 to \$19.50. This is your fastest way to catch impossible values - a negative price, a `max` of 9999, an age of 200 - that signal a data problem. `describe()` only looks at numbers. For text/category columns, you want frequencies - that's `value_counts()`: ```python df["region"].value_counts() ``` ```console region North 2 South 1 East 1 West 1 Name: count, dtype: int64 ``` *What just happened:* `value_counts()` counted how many rows had each distinct value in the `region` column and sorted by frequency. North appears twice; the rest once each. This is how you check categories: are the regions spelled consistently (no `"north"` *and* `"North"`)? Is one value swamping the rest? Are there surprise categories you didn't expect? Add `normalize=True` to get proportions instead of counts. And `nunique()` answers "how many distinct values?" without listing them: ```python print(df["product"].nunique()) # 3 distinct products ``` ```console 3 ``` *What just happened:* `nunique()` returned the count of unique products (Widget, Gadget, Doohickey) - `3`. It's the quick way to gauge a column's cardinality: a few distinct values means a category to group by later; thousands means it's more like an ID. ## The inspect-first discipline 💡 Pull these together into a habit you run on *every* dataset, in this order, before you analyze anything: 1. **`info()`** - types and missing values. Is anything the wrong type? Are there holes? 2. **`head()`** (and `sample()`) - the shape of the actual values. Did it load right? Do the numbers look plausible? 3. **`describe()` and `value_counts()`** - distributions. Any impossible numbers? Any messy or surprising categories? This three-step sweep takes under a minute and catches the exact problems that quietly wreck an analysis: dates read as text, numbers trapped as strings, missing values you didn't know were there, categories spelled three different ways, a stray summary row at the bottom. Every one of those produces *wrong answers that look right* - the most dangerous kind of bug - if you skip straight to computing. Inspect first, analyze second. Now that you can load data and know exactly what's in it, the next phase is about reaching for the parts you want: selecting columns, slicing rows with `loc` and `iloc`, and filtering with boolean masks. ## Recap 1. **Analysis starts with loading.** `pd.read_csv` is the workhorse; `read_excel`, `read_json`, `read_sql`, and `read_parquet` cover other sources. All return a DataFrame. 2. **`read_csv` has arguments for messy files** - `sep`, `header`, `parse_dates`, `dtype`, `na_values`, `usecols`, `nrows` - because the defaults can't read your mind. 3. ⚠️ **pandas guesses types, and guesses can be wrong.** A column of `object` where you expected numbers means something non-numeric snuck in. Always verify. 4. **`head`/`tail`/`sample`** show you real rows; **`info`** is the best single command - rows, columns, types, and null counts in one shot. 5. **`describe`** summarizes numeric columns (count/mean/std/min/quartiles/max); **`value_counts`** counts category frequencies; **`nunique`** counts distinct values. 6. 💡 **Inspect before you analyze:** `info` → `head` → `describe`/`value_counts`. One minute of looking prevents wrong-but-plausible answers later. ## Quick check Run the inspect-first sweep in your head and pick the best answer: ```quiz [ { "q": "You load a CSV and `df.info()` shows the `price` column has dtype `object`. What's the most likely explanation?", "choices": [ "Some values in the column aren't pure numbers (e.g. a '$' sign or the text 'N/A'), so pandas stored the whole column as text", "The column is fine - `object` is pandas' normal type for decimal numbers", "pandas ran out of memory and downgraded the column", "The file was saved in the wrong encoding" ], "answer": 0, "explain": "A CSV has no types, so pandas infers them. If even one value in a column isn't numeric, pandas falls back to `object` (text) for the entire column. You can't do math on it until you clean and convert it - which is exactly why inspecting types early matters." }, { "q": "Which single command gives you row count, column names, each column's type, AND how many non-missing values each column has, all at once?", "choices": [ "df.info()", "df.head()", "df.describe()", "df.shape" ], "answer": 0, "explain": "`df.info()` is the best first command on any new DataFrame: it reports the number of rows, every column with its non-null count, and each column's dtype in one printout. `head` shows values, `describe` shows numeric stats, and `shape` only gives the (rows, columns) tuple." }, { "q": "You want to check whether the `region` column is spelled consistently and see how many sales fall in each region. Which method fits best?", "choices": [ "df['region'].value_counts() - it counts each distinct value", "df.describe() - it summarizes every column", "df['region'].nunique() - it returns just the number of distinct values", "df.head() - it shows the first five rows" ], "answer": 0, "explain": "`value_counts()` lists each distinct value with its frequency, so you immediately see both the counts per region and any inconsistencies (like 'North' vs 'north' showing up as separate entries). `nunique` would only tell you how many distinct values exist, not what they are or how often they appear." } ] ``` --- # Selecting & Filtering Loading a file gives you the whole table. Real work almost never wants the whole table - it wants *these columns* and *the rows where something is true*, and this phase is about carving out exactly that slice. Here is the mental model to carry through everything below. Picture your sales DataFrame as a spreadsheet: | | date | product | region | units | price | |---|---|---|---|---|---| | **0** | 2026-01-03 | Widget | West | 120 | 9.99 | | **1** | 2026-01-03 | Gadget | East | 45 | 19.99 | | **2** | 2026-01-04 | Widget | North | 80 | 9.99 | | **3** | 2026-01-04 | Gizmo | West | 200 | 4.50 | | **4** | 2026-01-05 | Gadget | West | 60 | 19.99 | Two questions answer almost everything you'll ever do: 1. **Which columns?** Grab them by name. 2. **Which rows?** Build a *mask* - a column of True/False - and keep the True ones. Selecting columns is the easy half. The row half - boolean masks - is the real engine of pandas, and the thing worth getting fluent in. Let's take them in order. ## Selecting columns To pull one column, index the DataFrame with its name: ```python df["price"] ``` ```console 0 9.99 1 19.99 2 9.99 3 4.50 4 19.99 Name: price, dtype: float64 ``` *What just happened:* `df["price"]` handed back a single column - and a single column is a **Series**, the 1-D pandas object from phase 1. Notice the output: it has the index on the left, the values on the right, and a `Name`/`dtype` footer. That's a Series, not a table. To pull several columns, pass a **list** of names: ```python df[["product", "price"]] ``` ```console product price 0 Widget 9.99 1 Gadget 19.99 2 Widget 9.99 3 Gizmo 4.50 4 Gadget 19.99 ``` *What just happened:* the inner `[...]` is a Python list of column names, and asking for a list of columns gives you back a **DataFrame** - a 2-D table, with column headers across the top. > ⚠️ This is the single most common beginner trip-up: **single brackets vs double brackets.** `df["price"]` (one name) is a Series; `df[["price"]]` (a list with one name) is a one-column DataFrame. Same data, different *type* - and the type changes what methods work and what your downstream code expects. When something later complains that a Series has no such method, or a DataFrame showed up where you wanted a column, check your brackets first. ## `loc` vs `iloc` Selecting whole columns is fine, but often you want a specific *cell* or a rectangular block - particular rows *and* particular columns. pandas gives you two indexers for that, and the difference between them is the thing everyone has to internalize once and never forgets again. > 📝 **`loc` selects by LABEL** - the actual index values and column *names*. **`iloc` selects by POSITION** - integer offsets, counting from 0, exactly like list slicing. Label vs position. That's the whole distinction. Here's `loc`, working by name: ```python df.loc[0, "price"] # row labelled 0, column named "price" ``` ```console 9.99 ``` ```python df.loc[:, ["product", "units"]] # all rows, just these two columns ``` ```console product units 0 Widget 120 1 Gadget 45 2 Widget 80 3 Gizmo 200 4 Gadget 60 ``` *What just happened:* `loc` reads the labels you give it. `0` is the row's index label, `"price"` is the column's name. The `:` means "every row," and the list picks columns by name. Because our index happens to be 0,1,2,… the row label `0` looks like a position - but it isn't. If the index were dates or product codes, you'd pass *those* to `loc`. Now `iloc`, working by position: ```python df.iloc[0, 3] # first row, fourth column (0-based) -> units ``` ```console 120 ``` ```python df.iloc[:5] # first five rows, like list slicing ``` ```console date product region units price 0 2026-01-03 Widget West 120 9.99 1 2026-01-03 Gadget East 45 19.99 2 2026-01-04 Widget North 80 9.99 3 2026-01-04 Gizmo West 200 4.50 4 2026-01-05 Gadget West 60 19.99 ``` *What just happened:* `iloc` ignores names entirely and counts. `[0, 3]` is "row index 0, column index 3" - and since columns go `date`(0), `product`(1), `region`(2), `units`(3), that cell is `120`. `df.iloc[:5]` slices the first five rows by position, and just like Python slices, the end is *exclusive*. > 💡 One quirk worth knowing: `loc` slicing is *inclusive* of its endpoint (`df.loc[0:2]` gives rows 0, 1, **and** 2), because labels aren't necessarily contiguous numbers. `iloc` slicing is *exclusive* like normal Python. When a slice returns one more or one fewer row than you expected, this is usually why. ## Boolean filtering (masks) - the workhorse Now the important half: keeping rows where a condition holds. This is where you'll spend most of your pandas life, so go slow here. > 📝 A comparison on a column doesn't return one True/False - it returns a whole **Series of True/False**, one per row. That Series is called a **mask**. When you index the DataFrame *with* a mask, pandas keeps every row where the mask is True and drops the rest. Look at the mask by itself first: ```python df["units"] > 100 ``` ```console 0 True 1 False 2 False 3 True 4 False Name: units, dtype: bool ``` *What just happened:* `df["units"] > 100` compared every value in the `units` column against 100 *at once* (vectorized - no loop) and gave back a boolean Series. Rows 0 and 3 cleared the bar; the rest didn't. This Series *is* the mask. Now hand that mask back to the DataFrame: ```python df[df["units"] > 100] ``` ```console date product region units price 0 2026-01-03 Widget West 120 9.99 3 2026-01-04 Gizmo West 200 4.50 ``` *What just happened:* `df[ mask ]` kept only the rows where the mask was True - the two high-volume sales. The original index labels (0 and 3) come along for the ride, which is your proof these are the same rows from the source table, not renumbered copies. That two-step - *build a mask, index with it* - is **the** way to filter rows in pandas. Everything in the next section is just building fancier masks. ## Combining conditions Real filters usually have more than one clause: West region *and* more than 50 units. You combine masks with `&` (and), `|` (or), and `~` (not) - and there are two rules you must follow or pandas bites you. ```python df[(df["region"] == "West") & (df["units"] > 50)] ``` ```console date product region units price 0 2026-01-03 Widget West 120 9.99 3 2026-01-04 Gizmo West 200 4.50 4 2026-01-05 Gadget West 60 19.99 ``` *What just happened:* two masks - "region is West" and "units over 50" - combined with `&`, which does an element-by-element AND. A row survives only if it's True in *both*. Each condition is wrapped in its own parentheses, and that's not optional. > ⚠️ **Use `&` / `|` / `~`, never Python's `and` / `or` / `not`.** The word `and` tries to collapse a whole Series into a single True/False and throws; the symbols operate element-wise, which is what you want. **And wrap every condition in parentheses.** `&` binds tighter than `>` in Python, so without parens, `df["region"] == "West" & df["units"] > 50` is parsed as `"West" & df["units"]` first - nonsense - and you get this classic error: ```console ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all(). ``` *What just happened:* pandas couldn't reduce a whole boolean Series to one yes/no, which is exactly what writing `and` (or forgetting parens) asks it to do. When you see "truth value of a Series is ambiguous," it's nearly always a missing pair of parentheses or a stray `and`/`or`. Add the parens, swap to symbols. Two more mask-builders you'll reach for constantly: ```python df[~(df["region"] == "East")] # ~ negates: everything NOT East df[df["region"].isin(["West", "North"])] # membership: region in a set df[df["units"].between(50, 150)] # range: 50 <= units <= 150 (inclusive) ``` *What just happened:* `~` flips a mask (keep the rows the condition is False for). `.isin([...])` builds a mask that's True wherever the value is in your list - far cleaner than chaining `==` with `|`. `.between(a, b)` is shorthand for `(col >= a) & (col <= b)`, inclusive on both ends. Each still returns a mask, so each still goes inside `df[ ... ]`. ## `query()` & the assignment gotcha Once filters get long, all those `df["..."]` repetitions get noisy. `query()` lets you write the condition as a string, referring to columns by bare name: ```python df.query("region == 'West' and units > 50") ``` ```console date product region units price 0 2026-01-03 Widget West 120 9.99 3 2026-01-04 Gizmo West 200 4.50 4 2026-01-05 Gadget West 60 19.99 ``` *What just happened:* same result as the `&` version above, but easier to read. Inside the `query()` string you *do* write `and`/`or` (it's a mini-language pandas parses, not raw Python), and columns are referenced by name without `df[...]`. Use whichever style is clearer for the filter at hand; for long multi-clause filters, `query()` usually wins. Now the gotcha that catches everyone eventually. Filtering gives you a *view or a copy* of the original - pandas itself isn't always sure which - and **writing to a filtered slice may silently fail to update the original** while warning you about it: ```python high = df[df["units"] > 100] high["price"] = 0.0 # triggers a warning, may not write back to df ``` ```console SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer, col_indexer] = value instead ``` *What just happened:* you filtered into `high`, then tried to assign into it. pandas couldn't guarantee `high` was a real view onto `df`, so it warned that your write might land on a throwaway copy and vanish. This is the infamous `SettingWithCopyWarning`, and it means "your edit might not do what you think." There are two clean ways out, depending on intent: ```python df.loc[df["units"] > 100, "price"] = 0.0 # edit the ORIGINAL, in place subset = df[df["units"] > 100].copy() # an INDEPENDENT subset to edit freely ``` *What just happened:* if you mean to change the original table, do the masking and the assignment in **one `.loc` step** - pandas knows that targets `df` directly, no ambiguity, no warning. If you instead want a separate working table, call `.copy()` to make the break explicit; now editing `subset` can't surprise you because it's genuinely its own object. > 💡 Step back and notice the shape of this whole phase: boolean masks are the heart of pandas. Selecting columns, `loc`/`iloc`, `query()` - all useful - but you will *build masks* constantly, every day you touch pandas. Get comfortable reading `df[(...) & (...)]` at a glance and most of the rest follows. Next phase puts these to work on data that's actually messy. ## Recap - **One column is a Series, a list of columns is a DataFrame.** `df["price"]` (single brackets) → Series; `df[["product", "price"]]` (double brackets) → DataFrame. Wrong type downstream usually traces back to your brackets. - **`loc` is by label, `iloc` is by position.** `df.loc[0, "price"]` uses index/column *names*; `df.iloc[0, 3]` counts from 0. `loc` slices are inclusive of the endpoint; `iloc` slices are exclusive. - **A comparison builds a mask** - a boolean Series - and `df[mask]` keeps the True rows. This two-step (build a mask, index with it) is the core way to filter. - **Combine conditions with `&` `|` `~`, not `and` `or` `not`, and parenthesize each clause.** Forgetting either gives "The truth value of a Series is ambiguous." `.isin([...])` and `.between(a, b)` build common masks cleanly. - **`query("...")` reads cleaner for complex filters** and lets you use bare column names and `and`/`or` inside the string. - **Editing a filtered slice triggers `SettingWithCopyWarning`** and may not write back. Use `df.loc[mask, "col"] = value` to edit the original, or `.copy()` to take an independent subset. ## Quick check ```quiz [ { "q": "What type does df[[\"product\", \"price\"]] return?", "choices": ["A Series", "A DataFrame", "A Python list"], "answer": 1, "explain": "Passing a list of column names returns a DataFrame. A single name in single brackets, df[\"price\"], would return a Series." }, { "q": "Which selects the cell at the first row and fourth column by position?", "choices": ["df.loc[0, 3]", "df.iloc[0, 3]", "df[0][3]"], "answer": 1, "explain": "iloc selects by integer position (0-based). loc selects by label, so df.loc[0, 3] would look for a column literally named 3." }, { "q": "Why does df[df[\"region\"] == \"West\" & df[\"units\"] > 50] raise 'truth value of a Series is ambiguous'?", "choices": ["You must use 'and' instead of '&'", "Each condition needs its own parentheses because & binds tighter than the comparisons", "isin() is required for multiple conditions"], "answer": 1, "explain": "& has higher precedence than == and >, so without parentheses pandas tries to combine the wrong pieces. Wrap each condition: (df[\"region\"] == \"West\") & (df[\"units\"] > 50)." } ] ``` --- # Cleaning Data Here's the part nobody warns you about when you start: the analysis is the easy bit; the data is the hard bit. Real sales exports come with blank cells where someone forgot to enter a price, a `units` column that loaded as text because one row had "N/A" in it, the same order pasted in twice, and a `region` field where the same place shows up as `"North"`, `"north"`, and `"North "` with a trailing space. None of that is exotic - it's Tuesday. Cleaning this up is most of the job, and the people who are good at data are mostly people who are patient and systematic about cleaning. The mental model to hold the whole way through: **cleaning is a loop, not a step.** You inspect the data (the `head`/`info`/`describe` habit from Phase 2), you spot a problem, you fix that one thing, and then you inspect *again* to confirm the fix worked and didn't create a new mess. Dirty data doesn't announce itself with an error - it sits there quietly and corrupts every total, average, and chart you build on top of it. This is the same fear that drives whole data teams: a pipeline can run green and still produce wrong numbers ([Data Quality & Observability](/guides/data-quality-and-observability) is the grown-up version of this chapter). You're learning the hand-tool version: how to find the dirt and decide, column by column, what to do about it. We'll work a messier cousin of our running sales dataset - the same five columns, but with the kinds of problems a real CSV hands you: ```python import pandas as pd import numpy as np sales = pd.DataFrame({ "date": ["2024-01-05", "2024-01-05", "2024-01-06", "2024-01-06", "2024-01-06", "2024-01-07"], "product": ["Widget", "Gadget", "Widget", "Gadget", "Gadget", "Widget"], "region": ["North", "south", "North ", "WEST", "WEST", None], "units": ["10", "4", "7", "12", "12", "5"], "price": ["9.99", "19.99", None, "19.99", "19.99", "9.99"], }) print(sales) ``` ```console date product region units price 0 2024-01-05 Widget North 10 9.99 1 2024-01-05 Gadget south 4 19.99 2 2024-01-06 Widget North 7 None 3 2024-01-06 Gadget WEST 12 19.99 4 2024-01-06 Gadget WEST 12 19.99 5 2024-01-07 Widget None 5 9.99 ``` *What just happened:* We built a DataFrame that looks fine at a glance but is quietly broken in four ways. Look closely: `region` has casing chaos (`"south"`, `"North "`, `"WEST"`) and a `None` in row 5; `price` has a `None` in row 2; `units` and `price` are full of *quoted* numbers - they're strings, not numbers (a CSV often loads them this way when even one cell is non-numeric); and rows 3 and 4 are byte-for-byte identical (a duplicate). One DataFrame, every common kind of dirt. Let's clean it. ## Missing values: finding the holes 📝 **NaN** - pandas marks a missing value as `NaN` ("Not a Number"), a special float that means "there's nothing here." When you load a CSV, blank cells, `None`, and `NA` all become `NaN`. It is *not* the same as `0` or an empty string `""` - it specifically means **absent**, and pandas treats it specially (most math skips it rather than crashing). You can't fix holes you can't see. The first move is always to count them. `isna()` returns a same-shaped DataFrame of `True`/`False` (`True` = missing), and chaining `.sum()` counts the `True`s per column: ```python print(sales.isna().sum()) ``` ```console date 0 product 0 region 1 price 1 units 0 dtype: int64 ``` *What just happened:* `sales.isna()` turned every cell into "is this missing?" and `.sum()` added up the `True`s column by column (`True` counts as 1). The verdict: `region` has 1 missing value, `price` has 1, everything else is clean. ⚠️ Notice `units` shows `0` missing even though it's a mess - that's because its problem is *type*, not absence. Every value is present; they're just strings. `isna().sum()` is the single most useful first command on any new dataset - run it before you do anything else, so you know exactly where the holes are. ## Handling missing: drop vs fill Once you've found the holes, you have two straightforward choices, and they pull in opposite directions. ⚠️ **The judgment call.** Dropping rows **loses real data**. Filling them **invents data that wasn't there.** There is no free option - every missing value forces you to pick which kind of wrong you can live with, and the right answer changes column by column. Don't reach for one reflexively; decide on purpose. **Option A - drop.** `dropna()` removes any row that has a missing value in *any* column: ```python print(sales.dropna()) ``` ```console date product region units price 0 2024-01-05 Widget North 10 9.99 1 2024-01-05 Gadget south 4 19.99 3 2024-01-06 Gadget WEST 12 19.99 4 2024-01-06 Gadget WEST 12 19.99 ``` *What just happened:* `dropna()` threw out rows 2 and 5 - the ones with a missing `price` and a missing `region` - and handed back the survivors. Notice the index now reads `0, 1, 3, 4`: the dropped labels are gone, leaving gaps (exactly the "index is a label, not a row number" point from Phase 1). You can narrow it with `subset=` to only drop on specific columns (`sales.dropna(subset=["price"])` drops only rows missing a price) or `axis=1` to drop whole *columns* that have holes. Like everything else, `dropna()` returns a new DataFrame - your original is untouched unless you reassign it. **Option B - fill.** `fillna()` plugs the holes with a value of your choosing: ```python filled = sales.copy() filled["region"] = filled["region"].fillna("Unknown") print(filled["region"]) ``` ```console 0 North 1 south 2 North 3 WEST 4 WEST 5 Unknown ``` *What just happened:* `fillna("Unknown")` replaced the `None` in row 5 with the string `"Unknown"`. For a categorical column like `region`, a constant placeholder is clear - it says "we don't know" out loud instead of silently dropping a sale. For a *numeric* column you'd often fill with a computed value, like the column's own mean, or carry the previous value forward with `method="ffill"` (forward-fill): ```python print(filled["price"].fillna(method="ffill")) ``` ```console 0 9.99 1 19.99 2 19.99 3 19.99 4 19.99 5 9.99 ``` *What just happened:* `ffill` walked down the column and copied the last seen value into each hole - row 2's missing price became `19.99`, the value from row 1. (That only makes sense for `price` here because it's still a string column; we fix that next, and you'd normally fill numbers *after* converting.) 💡 Forward-fill is great for ordered data like time series where "same as the last reading" is a reasonable guess, and dangerous for unordered data where it just smears arbitrary neighbors around. Choose the fill that matches what the column *means*. ## Fixing types: numbers that are secretly strings Look back at `units` and `price`: they hold things like `"10"` and `"9.99"` - quoted, because they're text. ⚠️ **Why this matters.** A number stored as `object` (pandas-speak for "string/mixed") breaks everything you'd want to do with it. Sorting goes alphabetical (`"10"` sorts *before* `"4"`). Math either errors or, worse, silently concatenates (`"10" + "4"` becomes `"104"`, not `14`). Sums are nonsense. **Fix types early**, right after you've handled missing values, before any calculation touches the column. `df.info()` (from Phase 2) or `df.dtypes` shows you the current types so you know what needs fixing. The blunt tool is `astype()`, which converts a column to a type you name: ```python clean = sales.dropna().copy() clean["units"] = clean["units"].astype(int) print(clean["units"]) ``` ```console 0 10 1 4 3 12 4 12 Name: units, dtype: int64 ``` *What just happened:* `astype(int)` converted the `units` column from strings to real 64-bit integers - note the `dtype: int64` at the bottom, where it used to be `object`. Now `units` will sort numerically and do arithmetic correctly. ⚠️ But `astype(int)` is brittle: it throws an error the instant it hits a value it can't convert (a stray `"N/A"`, a blank), and it can't run on a column that still has `NaN` in it. That's why we dropped missing rows first. For real-world data you usually want the *robust* converters, which handle the junk gracefully: ```python clean["price"] = pd.to_numeric(clean["price"], errors="coerce") clean["date"] = pd.to_datetime(clean["date"]) print(clean.dtypes) ``` ```console date datetime64[ns] product object region object units int64 price float64 dtype: object ``` *What just happened:* `pd.to_numeric(..., errors="coerce")` turned `price` into real floats, and the `errors="coerce"` part is the magic word: instead of crashing on anything unconvertible, it quietly turns that value into `NaN` - so one bad cell doesn't blow up your whole conversion (you'd then handle those new `NaN`s with `fillna`/`dropna`). `pd.to_datetime` parsed the `date` strings into a proper `datetime64` type, which unlocks all the date math you'll meet in Phase 8 (sorting by date, filtering by month, resampling). Strings in, real typed columns out. ## Duplicates and renaming Remember rows 3 and 4 were identical. Duplicated rows double-count revenue, inflate totals, and skew every average - and they sneak in constantly (a re-run export, a double-paste, a bad join). `duplicated()` flags them; `drop_duplicates()` removes them: ```python print("dupes flagged:") print(sales.duplicated()) deduped = sales.drop_duplicates() print(deduped) ``` ```console dupes flagged: 0 False 1 False 2 False 3 False 4 True 5 False dtype: bool date product region units price 0 2024-01-05 Widget North 10 9.99 1 2024-01-05 Gadget south 4 19.99 2 2024-01-06 Widget North 7 None 3 2024-01-06 Gadget WEST 12 19.99 5 2024-01-07 Widget None 5 9.99 ``` *What just happened:* `duplicated()` walked the rows and marked the *second* occurrence of an identical row as `True` - row 4 is the repeat of row 3, so it's the one flagged (the first copy is kept by default). `drop_duplicates()` then dropped it, leaving four unique rows plus the still-clean ones. You can dedupe on a *subset* of columns when "same" means same key rather than same everything: `drop_duplicates(subset=["date", "product"])` keeps one row per date-product pair regardless of the other columns. Think about what "the same record" actually means for your data before you dedupe. While we're tidying structure, column names often need standardizing too - inconsistent casing, spaces, names you'd rather not type. `rename` fixes specific ones via a `{old: new}` mapping: ```python renamed = deduped.rename(columns={"units": "quantity", "price": "unit_price"}) print(renamed.columns.tolist()) ``` ```console ['date', 'product', 'region', 'quantity', 'unit_price'] ``` *What just happened:* `rename(columns={...})` swapped `units`→`quantity` and `price`→`unit_price`, leaving the untouched columns alone. For a bulk cleanup you'd often run a transform over *all* names at once - e.g. `df.columns = df.columns.str.lower().str.replace(" ", "_")` to force every header to lowercase snake_case - so your code never has to guess whether it's `Region`, `region`, or `REGION`. ## String cleaning with `.str` That brings us to the messiest column: `region`, with `"North "`, `"south"`, and `"WEST"` all meaning the same handful of places. To a computer, `"North"` and `"North "` are *different values* - they'll group separately, count separately, and split your totals in ways that are maddening to debug. 📝 **The `.str` accessor** - pandas gives every text column a `.str` attribute that vectorizes Python's string methods over the whole column at once. `series.str.lower()` lowercases every value; `series.str.strip()` trims whitespace from every value - no loop, same column-first thinking as everywhere else in pandas. Let's standardize `region` in one chain: ```python clean = sales.drop_duplicates().copy() clean["region"] = clean["region"].str.strip().str.lower() print(clean["region"]) ``` ```console 0 north 1 south 2 north 3 west 5 None ``` *What just happened:* `.str.strip()` knocked the trailing space off `"North "`, then `.str.lower()` folded `"North"` and `"WEST"` down to `"north"` and `"west"` - so the two spellings of North now collapse to one value that will group and count together. Notice the `None` in row 5 stayed `None`: `.str` methods skip missing values rather than crashing on them, which is exactly what you want (handle the `NaN` separately with `fillna`). The wider `.str` toolkit is deep: `.str.replace("old", "new")` swaps substrings, `.str.contains("dget")` returns a boolean mask you can filter with (great for "rows where product name contains X"), `.str.split`, `.str.startswith`, and more - the whole string library, applied column-wide. 💡 **Cleaning is iterative - don't skip the re-inspect.** The straightforward workflow is: inspect (Phase 2) → fix nulls → fix types → drop dupes → scrub strings → **inspect again**. After every fix, re-run `isna().sum()`, `dtypes`, and `head()` to confirm the fix landed and didn't introduce a new problem (a coerced column full of fresh `NaN`s, a rename that broke later code). Skipping this is how dirty data survives into your final chart and quietly makes every number wrong. Patient inspection is the whole skill. ## Recap 1. **Missing values** show up as `NaN` (absent, not zero). Find them first with `df.isna().sum()` to count nulls per column - your standard first command on any new dataset. 2. **Handle missing deliberately:** `dropna()` loses real rows; `fillna(value)` invents data (a constant, the mean, or `method="ffill"`). ⚠️ Every choice is a tradeoff - decide per column based on what it means. 3. **Fix types early.** Numbers/dates loaded as `object` (string) break sorting and math. Convert with `astype()`, or robustly with `pd.to_numeric(..., errors="coerce")` and `pd.to_datetime(...)`, which turn junk into `NaN` instead of crashing. 4. **Duplicates** double-count: `duplicated()` flags them, `drop_duplicates()` (optionally `subset=`) removes them. **Rename/standardize** columns with `rename(columns={...})` or a bulk `df.columns.str...` transform. 5. **The `.str` accessor** vectorizes string methods over a column: `.str.strip()`, `.str.lower()`, `.str.replace()`, `.str.contains()` - perfect for collapsing inconsistent text like `"North "`/`"WEST"`. 6. **Cleaning is a loop:** inspect → fix nulls/types/dupes/strings → re-inspect. Dirty data fails silently, so confirm every fix before you build on it. ## Quick check Lock in the three decisions every cleaning pass forces - how to find holes, how missing-value handling trades off, and why types come first: ```quiz [ { "q": "What does `df.isna().sum()` tell you?", "choices": [ "The number of missing values in each column", "The total of all numeric values in the DataFrame", "How many duplicate rows exist", "The data type of every column" ], "answer": 0, "explain": "isna() marks every cell True/False for missing, and .sum() counts the Trues per column - giving you a per-column tally of holes, the ideal first command on a new dataset." }, { "q": "What is the core tradeoff between `dropna()` and `fillna()`?", "choices": [ "dropna() loses real data, while fillna() invents data that wasn't there", "dropna() is faster but fillna() uses less memory", "There is no difference - they produce identical results", "fillna() only works on text and dropna() only works on numbers" ], "answer": 0, "explain": "Dropping rows throws away real records; filling them substitutes values that were never measured. Neither is free, so you choose per column based on what the data means." }, { "q": "Why convert a `price` column from strings to floats before doing math on it?", "choices": [ "As strings, sorting goes alphabetical and '+' concatenates instead of adding, so totals are wrong", "Strings take up more disk space than floats always", "pandas refuses to display string columns", "Floats are the only type that can be missing" ], "answer": 0, "explain": "A numeric column stored as object sorts alphabetically and makes '+' glue strings together rather than add - so sums and averages are silently wrong. Fix types early with astype or pd.to_numeric(errors='coerce')." } ] ``` --- # Transforming Data Cleaning got your data trustworthy. Now you make it *useful* - and most of that work is the same move, repeated: take the columns you have and compute new ones from them. Revenue from units and price. A "high value" flag from revenue. A size bucket from a number. A full region name from a code. Here's the mental model for the whole phase, and it's the same one from Phase 1 ([What pandas Is](01-what-pandas-is.md)) wearing a different hat: **a transformation is a column in, a column out.** You describe what each value should become, and pandas computes the result for every row at once. The whole skill is learning the right tool for each shape of transformation - and there's a clear pecking order, from blazing-fast vectorized math down to the slow-but-flexible escape hatch you reach for only when nothing else fits. We'll keep working the running sales dataset (`date`, `product`, `region`, `units`, `price`), with the `revenue` column we built in Phase 1: ```python import pandas as pd import numpy as np sales = pd.DataFrame({ "date": ["2024-01-05", "2024-01-05", "2024-01-06", "2024-01-06", "2024-01-07"], "product": ["Widget", "Gadget", "Widget", "Gadget", "Widget"], "region": ["North", "South", "North", "West", "South"], "units": [10, 4, 7, 12, 5], "price": [9.99, 19.99, 9.99, 19.99, 9.99], }) sales["revenue"] = sales["units"] * sales["price"] ``` ## Creating columns the vectorized way 📝 **Deriving a column** means computing a brand-new column from existing ones in a single column operation - no loop, no row-by-row work. You write the expression as if the columns were single values, and pandas applies it to every row in one fast sweep. You already met the headline example. It's worth seeing again, because every other tool in this phase is a variation on it: ```python sales["revenue"] = sales["units"] * sales["price"] print(sales[["product", "units", "price", "revenue"]]) ``` ```console product units price revenue 0 Widget 10 9.99 99.90 1 Gadget 4 19.99 79.96 2 Widget 7 9.99 69.93 3 Gadget 12 19.99 239.88 4 Widget 5 9.99 49.95 ``` *What just happened:* `sales["units"] * sales["price"]` multiplied the two columns element by element - row 0's units times row 0's price, and so on down - producing a new Series, which we assigned to a new column named `revenue`. This is **vectorization**: one expression, every row computed at C speed under the hood. It isn't only multiplication - `+`, `-`, `/`, `**`, comparisons (`>`, `==`), and string methods like `sales["product"].str.upper()` all work the same column-at-a-time way. When the transformation is plain arithmetic or a built-in operation, this is the tool. Reach for nothing fancier. ## Vectorized conditionals: np.where and pd.cut Plenty of derived columns aren't arithmetic - they're a *decision*. "Is this a high-value order?" "Is this order small, medium, or large?" You could imagine writing an `if`/`else` per row, but that's a loop in disguise. There are vectorized tools built exactly for this. For a **two-way choice** - pick value A where a condition is true, value B where it's false - use `np.where(condition, a, b)`: ```python sales["high_value"] = np.where(sales["revenue"] > 100, "high", "normal") print(sales[["product", "revenue", "high_value"]]) ``` ```console product revenue high_value 0 Widget 99.90 normal 1 Gadget 79.96 normal 2 Widget 69.93 normal 3 Gadget 239.88 high 4 Widget 49.95 normal ``` *What just happened:* `sales["revenue"] > 100` produced a column of `True`/`False` - a boolean mask, the same kind you filter with. `np.where` walked that mask and chose `"high"` wherever it was `True` and `"normal"` wherever it was `False`, all in one vectorized call. (If you only need the boolean itself, `sales["high_value"] = sales["revenue"] > 100` is even simpler - a bare comparison *is* a vectorized conditional.) `np.where` is what you want the moment the answer depends on two outcomes. When the decision is "**which bucket does this number fall into**" - splitting a continuous value into named ranges - `pd.cut` is the purpose-built tool: ```python sales["order_size"] = pd.cut( sales["units"], bins=[0, 5, 10, np.inf], labels=["small", "medium", "large"], ) print(sales[["product", "units", "order_size"]]) ``` ```console product units order_size 0 Widget 10 medium 1 Gadget 4 small 2 Widget 7 medium 3 Gadget 12 large 4 Widget 5 small ``` *What just happened:* `pd.cut` chopped the `units` column into the ranges set by `bins` and gave each range a label. The bins read as intervals: `(0, 5]` → `small`, `(5, 10]` → `medium`, `(10, ∞]` → `large` (by default the right edge is included, the left excluded - that's why `5` units lands in `small`). One call turned a numeric column into a tidy categorical one. ⚠️ Watch the edges: a value of exactly `0`, or one below your lowest bin, falls *outside* every interval and comes back as `NaN`. Set your bins to cover the full range you expect, and sanity-check with `value_counts()` afterward. ## map and replace: translating values A different flavor of transformation is **substitution** - swap each value for another according to a lookup. The classic case is expanding codes into readable names. `Series.map` does this with a dict: ```python region_names = {"North": "Northern", "South": "Southern", "West": "Western"} sales["region_full"] = sales["region"].map(region_names) print(sales[["region", "region_full"]]) ``` ```console region region_full 0 North Northern 1 South Southern 2 North Northern 3 West Western 4 South Southern ``` *What just happened:* `map` looked up each value of `region` in the dict and replaced it with the matching value, building a new column in one pass. ⚠️ The catch with `map`: any value **not** in your dict becomes `NaN`. That's a feature when you want to catch unexpected codes, but a footgun if you only meant to fix a couple of values and accidentally wiped the rest. For that "change a few, leave the rest alone" job, use `replace` instead: ```python sales["region"] = sales["region"].replace({"West": "Pacific"}) print(sales[["region", "region_full"]]) ``` ```console region region_full 0 North Northern 1 South Southern 2 North Northern 3 Pacific Western 4 South Southern ``` *What just happened:* `replace` swapped only the keys you listed (`"West"` → `"Pacific"`) and left every other value untouched - no surprise `NaN`s. Rule of thumb: **`map` is a full translation** (every value should be in the dict); **`replace` is a targeted edit** (a few specific swaps). ## apply: the flexible (and slower) escape hatch Sometimes the logic doesn't fit a clean vectorized expression - it's a chain of `if`s, a string-parsing routine, a call into another library. For those, pandas gives you an escape hatch. 📝 **`apply(func)`** runs a Python function once per element (on a Series) or once per row/column (on a DataFrame with `axis=1`). It's the "just run my own code on each piece" tool - maximally flexible, because the function can be *any* Python you want. Per element on a Series: ```python def label_order(rev): if rev > 200: return "big" elif rev > 80: return "medium" return "small" sales["rev_label"] = sales["revenue"].apply(label_order) print(sales[["revenue", "rev_label"]]) ``` ```console revenue rev_label 0 99.90 medium 1 79.96 small 2 69.93 small 3 239.88 big 4 49.95 small ``` *What just happened:* `apply(label_order)` called your function once for every value in `revenue` and collected the returns into a new column. Per *row* works the same with `axis=1`, where the function receives the whole row and you read columns off it: ```python sales["summary"] = sales.apply( lambda row: f"{row['product']} x{row['units']}", axis=1, ) print(sales[["product", "units", "summary"]]) ``` ```console product units summary 0 Widget 10 Widget x10 1 Gadget 4 Gadget x4 2 Widget 7 Widget x7 3 Gadget 12 Gadget x12 4 Widget 5 Widget x5 ``` *What just happened:* with `axis=1`, `apply` handed your lambda each row as a little Series, and you built a string from its fields. Readable and powerful. 💡 But here's the plain catch: **`apply` is a Python loop wearing a pandas coat.** It calls your function once per row in plain Python, so it's far slower than a vectorized operation - often 10–100× on real data. Use it when no vectorized tool fits. When one *does* fit, prefer it. The `rev_label` above, for instance, has a vectorized equivalent - `pd.cut` does the exact same bucketing without the per-row Python call: ```python sales["rev_label"] = pd.cut( sales["revenue"], bins=[0, 80, 200, np.inf], labels=["small", "medium", "big"], ) ``` *What just happened:* same three buckets, same result column - but computed in one vectorized sweep instead of five Python function calls. On five rows you'd never notice; on five million you'd feel it. Before reaching for `apply`, always ask: "is there a built-in that does this?" ## The performance lesson (the heart of this phase) This is the part to tattoo somewhere. ⚠️ **The single biggest pandas performance mistake is iterating over rows with a `for` loop or `iterrows()` instead of operating on whole columns.** It's the instinct everyone brings from regular Python, and it's the slowest thing you can do - routinely *orders of magnitude* slower than the vectorized equivalent, and longer to write besides. Here's the same task - compute revenue - done the wrong way and the right way. First, the row loop: ```python # The slow, un-pandas way - DON'T do this revenue = [] for index, row in sales.iterrows(): revenue.append(row["units"] * row["price"]) sales["revenue"] = revenue ``` *What just happened:* `iterrows()` handed you one row at a time, and you did the math row by row in pure Python, accumulating into a list. It produces the correct numbers - and it is the wrong tool. Every iteration pays Python's per-row overhead and rebuilds a Series object for the row. It's verbose, and it crawls as the data grows. Now the same result, vectorized: ```python # The pandas way - DO this sales["revenue"] = sales["units"] * sales["price"] ``` *What just happened:* one column expression replaced the entire loop. pandas multiplied the two columns in NumPy's compiled core, computing all rows in a single fast operation. Same answer, a fraction of the code, and dramatically faster - the gap only widens with more rows. Keep this **hierarchy of preference** in your head and reach down it only as far as you must: 1. **Vectorized operation** - column arithmetic, comparisons, `.str` methods. Fastest, clearest. Default here. 2. **Vectorized helpers / built-ins** - `np.where`, `pd.cut`, `map`, `replace`. Still vectorized, for conditionals and lookups. 3. **`.apply`** - when the logic genuinely doesn't vectorize. Flexible, but a Python loop underneath. 4. **An explicit `for` loop / `iterrows()`** - last resort, almost never needed for transforming data. 💡 "Think in columns" isn't a style preference or a nicety - it's about correctness *and* speed. Vectorized code is shorter, so it has fewer places to hide bugs; it aligns on the index, so it does the right thing across rows; and it runs in compiled code, so it scales. Whenever your fingers start typing `for ... in df...`, stop and ask what column operation you actually mean. There almost always is one. ## Recap 1. **A transformation is a column in, a column out.** Derive new columns from existing ones in single column operations - the Phase 1 habit, applied everywhere. 2. **Vectorized arithmetic** (`df["a"] * df["b"]`, comparisons, `.str` methods) is the default tool: fast, readable, applied to every row at once. 3. **Vectorized conditionals:** `np.where(cond, a, b)` for a two-way choice; `pd.cut(col, bins, labels)` to bucket a numeric column into named ranges. ⚠️ Mind `pd.cut`'s edges - values outside the bins become `NaN`. 4. **Translate values** with `Series.map(dict)` for a full lookup (missing keys → `NaN`) and `replace` for targeted swaps that leave everything else alone. 5. **`apply`** is the flexible escape hatch - any Python function, per element or per row (`axis=1`) - but it's a Python loop under the hood and is much slower. Prefer a vectorized equivalent when one exists. 6. **The #1 performance mistake is looping over rows (`iterrows()` / `for`) instead of using columns.** Preference order: vectorized op → `np.where`/`pd.cut`/`map` → `apply` → explicit loop (last resort). ## Quick check Lock in the pecking order - the right tool for each shape of transformation, and why looping loses: ```quiz [ { "q": "You want a new column that is \"high\" where revenue > 100 and \"normal\" otherwise. What's the idiomatic tool?", "choices": [ "np.where(sales[\"revenue\"] > 100, \"high\", \"normal\")", "A for-loop over sales.iterrows() with an if/else", "sales[\"revenue\"].map({100: \"high\"})", "There is no way to do a two-way choice in pandas" ], "answer": 0, "explain": "np.where(condition, a, b) is the vectorized two-way conditional: it picks the first value where the mask is True and the second where it's False, for every row at once." }, { "q": "Why is `df.apply(func, axis=1)` slower than a vectorized column expression?", "choices": [ "apply runs your Python function once per row - it's a Python loop under the hood, not a compiled column sweep", "apply secretly sorts the DataFrame first", "apply copies the entire DataFrame to disk before running", "It isn't slower; apply and vectorization are identical in speed" ], "answer": 0, "explain": "apply calls your Python function per row/element, paying Python's per-row overhead each time. Vectorized operations run in NumPy's compiled core over the whole column, so they're often 10–100x faster." }, { "q": "What is the #1 pandas performance mistake when transforming data?", "choices": [ "Iterating over rows with a for-loop or iterrows() instead of operating on whole columns", "Importing pandas as pd instead of its full name", "Adding too many columns to a DataFrame", "Using np.where instead of pd.cut" ], "answer": 0, "explain": "Row-by-row iteration is the slowest, most un-pandas approach - often orders of magnitude slower than the vectorized equivalent. The preference order is: vectorized op > np.where/pd.cut/map > apply > explicit loop (last resort)." } ] ``` --- # GroupBy & Aggregation If you take one idea from this whole guide, make it this one. Every phase so far has been about *shaping* a table - selecting it, filtering it, deriving new columns. This phase is about *summarizing* it: turning ten thousand rows of raw sales into "revenue per region," "average order size per product," "how many orders each region placed." That move - collapse many rows into one number per group - is the engine room of data analysis, and pandas has one beautiful pattern for it. The pattern has a name worth memorizing because it explains *everything* that follows. ## Split-apply-combine: the mental model 📝 **Split-apply-combine** is the shape of nearly every summary you'll ever write. Three steps: 1. **Split** - break the rows into groups by some key (e.g. all the `North` rows together, all the `South` rows together). 2. **Apply** - run a function on each group independently (e.g. `sum` the revenue inside each group). 3. **Combine** - stitch the per-group results back into one new table, one row per group. ```mermaid flowchart TD A[Full table: all rows] --> B{Split by region} B --> N[North rows] B --> S[South rows] B --> W[West rows] N --> N2[sum revenue] S --> S2[sum revenue] W --> W2[sum revenue] N2 --> C[Combine: one row per region] S2 --> C W2 --> C ``` 💡 If you've written SQL, you already know this in your bones: `SELECT region, SUM(revenue) FROM sales GROUP BY region` is *exactly* split-apply-combine. `GROUP BY region` is the split, `SUM(revenue)` is the apply, and the result set is the combine. (Same idea drives why GROUP BY can be slow on big tables in SQL - see [Why Is My Query Slow?](/guides/why-is-my-query-slow).) pandas `groupby` is the same pattern in Python, and once you see it that way it stops being three separate methods and becomes one thought: *group, then aggregate.* We'll keep working the running sales dataset, now with a few more rows so the groups have something to chew on: ```python import pandas as pd sales = pd.DataFrame({ "date": ["2024-01-05", "2024-01-05", "2024-01-06", "2024-01-06", "2024-01-07", "2024-01-07"], "product": ["Widget", "Gadget", "Widget", "Gadget", "Widget", "Gadget"], "region": ["North", "South", "North", "West", "South", "North"], "units": [10, 4, 7, 12, 5, 8], "price": [9.99, 19.99, 9.99, 19.99, 9.99, 19.99], }) sales["revenue"] = sales["units"] * sales["price"] ``` ## Basic groupby The simplest summary: total revenue per region. You name the grouping key, pick the column you care about, and call an aggregation: ```python print(sales.groupby("region")["revenue"].sum()) ``` ```console region North 259.65 South 119.91 West 239.88 Name: revenue, dtype: float64 ``` *What just happened:* `groupby("region")` did the **split** - it bucketed the rows by their `region` value. `["revenue"]` picked the column to work on. `.sum()` was the **apply** (sum within each bucket), and the returned Series is the **combine** - one row per region, the region values now sitting in the index. Three steps, one line. The aggregation on the end is interchangeable. Swap `.sum()` for whatever question you're asking: ```python print(sales.groupby("region")["units"].mean()) print(sales.groupby("region")["revenue"].count()) print(sales.groupby("region").size()) ``` ```console region North 8.333333 South 4.500000 West 12.000000 Name: units, dtype: float64 region North 3 South 2 West 1 Name: revenue, dtype: int64 region North 3 South 2 West 1 dtype: int64 ``` *What just happened:* `.mean()` averaged `units` inside each region, `.count()` tallied how many non-null `revenue` rows each region had, and `.size()` counted rows per group regardless of any column. (Subtle but real: `.count()` skips nulls in the chosen column; `.size()` counts every row in the group, nulls included. When you just want "how many rows landed here," `.size()` is the accurate one.) ⚠️ One thing to internalize early: `sales.groupby("region")` *by itself* doesn't compute anything. It's **lazy** - it hands you a `DataFrameGroupBy` object that's holding the split, waiting. Nothing runs until you attach an aggregation. Print it and you'll see ``, not data. The work happens at `.sum()`, `.mean()`, `.agg(...)` - the apply step. ## Grouping by multiple keys Real questions are rarely one-dimensional. "Total units per region" is fine, but "total units per region *and* product" is where analysis gets interesting. Pass a **list** of keys: ```python print(sales.groupby(["region", "product"])["units"].sum()) ``` ```console region product North Gadget 8 Widget 17 South Gadget 4 Widget 5 West Gadget 12 Name: units, dtype: int64 ``` *What just happened:* grouping by two keys split the rows into every observed `(region, product)` combination, then summed `units` in each. The result has a **MultiIndex** (a hierarchical index): the outer level is `region`, the inner is `product`. Notice North only shows the combos that actually *exist* in the data - there's no `(West, Widget)` row because no such rows were in the table. That MultiIndex is powerful but awkward to work with downstream - it's not plain columns. When you want a normal flat table (every key back as its own column), call `.reset_index()`: ```python result = sales.groupby(["region", "product"])["units"].sum().reset_index() print(result) ``` ```console region product units 0 North Gadget 8 1 North Widget 17 2 South Gadget 4 3 South Widget 5 4 West Gadget 12 ``` *What just happened:* `.reset_index()` lifted `region` and `product` out of the index and back into ordinary columns, giving you a tidy DataFrame with a clean 0-based index - the shape you want for saving to CSV, merging, or plotting. Memorize the chain `groupby(...).agg(...).reset_index()`; you'll type it constantly. ## Multiple and named aggregations with `agg` So far each summary computed one statistic. But you usually want several at once - total revenue, *and* average units, *and* order count, per region, in a single pass. That's the job of `.agg()`, and the modern way to call it is **named aggregation**: ```python summary = sales.groupby("region").agg( total_rev=("revenue", "sum"), avg_units=("units", "mean"), orders=("revenue", "count"), ) print(summary) ``` ```console total_rev avg_units orders region North 259.65 8.333333 3 South 119.91 4.500000 2 West 239.88 12.000000 1 ``` *What just happened:* each keyword argument defines one output column: the name on the left (`total_rev`), and a `(column, function)` tuple on the right saying *which column to aggregate* and *how*. One `groupby`, three statistics, and - the real win - **clean, self-documenting column names** you chose yourself. No cryptic `("revenue", "sum")` headers to rename afterward. 💡 Named aggregation (`agg(new_name=("col", "func"))`) is the clean, current way to do multi-stat summaries. You'll see older code using `agg({"revenue": "sum", "units": "mean"})` (a dict) or `agg(["sum", "mean"])` (a list, which produces a messy MultiIndex on the columns). Both still work, but the named form reads better and gives you the column names up front - prefer it. ## Transform vs aggregate (and the pitfalls) Here's a distinction that trips up almost everyone, and getting it straight is what separates fumbling with groupby from wielding it. 📝 **`agg` collapses** each group to a single row - five North rows become one North number. The result is *smaller* than the input. But sometimes you don't want to collapse; you want to *tag every original row with a fact about its group* - for example, "what's this row's revenue compared to its region's average?" For that you need a value **per original row**, not per group. That's **`transform`**. ```python sales["region_avg_rev"] = sales.groupby("region")["revenue"].transform("mean") sales["vs_region_avg"] = sales["revenue"] - sales["region_avg_rev"] print(sales[["region", "revenue", "region_avg_rev", "vs_region_avg"]]) ``` ```console region revenue region_avg_rev vs_region_avg 0 North 99.90 86.550 13.350 1 South 79.96 59.955 20.005 2 North 69.93 86.550 -16.620 3 West 239.88 239.880 0.000 4 South 49.95 59.955 -10.005 5 North 159.84 86.550 73.290 ``` *What just happened:* `transform("mean")` computed each region's average revenue (the same numbers `agg` would give), but then **broadcast that group value back onto every row in the group** - so all three North rows got `86.55`. Because the result lines up row-for-row with the original DataFrame, you can assign it straight back as a new column and do arithmetic against it (here, each order's distance from its region's average). `agg` answers "what's the summary?"; `transform` answers "how does each row compare to its group?" Two pitfalls that bite people in production: ⚠️ **NaN keys vanish.** By default, rows whose *grouping key* is `NaN` are silently dropped from the output - they're in no group. If a region is missing and you `groupby("region").sum()`, those rows just disappear from the totals, no error, no warning. If you need them counted, pass `dropna=False`: `sales.groupby("region", dropna=False)`. Always sanity-check your row counts after a groupby. ⚠️ **Index vs columns.** `groupby` puts the keys in the *index* by default, which is why you keep reaching for `.reset_index()`. If you'd rather get plain columns straight from the call, pass `as_index=False`: `sales.groupby("region", as_index=False)["revenue"].sum()` gives you `region` and `revenue` as ordinary columns, no MultiIndex gymnastics needed. Pick whichever keeps your downstream code simple. 💡 Step back and notice how much ground one method covers. "Summarize the data," "break it down by category," "compare each row to its group," "get totals per X and Y" - those are most of the questions anyone ever asks of a dataset, and they're all `groupby` + an aggregation. This isn't a corner of pandas; it's the center of it. Master split-apply-combine and you've mastered the part of pandas that does the actual analysis. ## Recap 1. **Split-apply-combine** is the pattern behind almost every summary: split rows into groups by a key, apply a function to each group, combine the results into a new table. It's exactly SQL's `GROUP BY`. 2. **Basic groupby:** `df.groupby("key")["col"].sum()` (also `.mean()`, `.count()`, `.size()`). The keys land in the index. `.count()` skips nulls; `.size()` counts every row. 3. **`groupby` alone is lazy** - it just holds the split. Nothing computes until you attach an aggregation. 4. **Multiple keys** (`groupby(["region", "product"])`) produce a hierarchical MultiIndex result; `.reset_index()` flattens it back to plain columns. Memorize `groupby(...).agg(...).reset_index()`. 5. **Named aggregation** - `agg(total_rev=("revenue", "sum"), avg_units=("units", "mean"))` - computes several stats in one pass with clean, self-chosen column names. It's the modern, preferred form. 6. **`agg` collapses to one row per group; `transform` returns a value per original row** (broadcast the group stat back onto each row, e.g. for comparisons). ⚠️ NaN grouping keys are dropped by default (`dropna=False` keeps them); use `as_index=False` to get columns instead of an index. ## Quick check Make sure the one big idea stuck - and the agg-vs-transform line that everyone fumbles: ```quiz [ { "q": "What are the three steps of the split-apply-combine pattern, in order?", "choices": [ "Sort the rows, filter them, then merge", "Split rows into groups by a key, apply a function to each group, combine the per-group results into one table", "Combine all rows, apply a filter, then split into a sample", "Select columns, rename them, then export" ], "answer": 1, "explain": "Split (group rows by a key) → apply (run a function on each group) → combine (stitch the per-group results into a new table). It's the same idea as SQL's GROUP BY." }, { "q": "You write `sales.groupby(\"region\")` and print it. Why don't you see any totals?", "choices": [ "groupby is lazy - it only holds the split; nothing computes until you attach an aggregation like .sum()", "You must call .show() to display a groupby", "The region column has nulls, so output is suppressed", "groupby only works inside a for-loop" ], "answer": 0, "explain": "groupby returns a DataFrameGroupBy object holding the split. The apply step (and any output) happens only when you call an aggregation such as .sum(), .mean(), or .agg(...)." }, { "q": "You want to add a column showing each order's revenue minus its region's average revenue, keeping all original rows. Which tool?", "choices": [ "groupby(\"region\")[\"revenue\"].agg(\"mean\") - agg gives a value per row", "groupby(\"region\")[\"revenue\"].transform(\"mean\") - transform broadcasts the group stat back to every original row", "groupby(\"region\")[\"revenue\"].sum().reset_index()", "sales[\"revenue\"].mean() applied to the whole table" ], "answer": 1, "explain": "transform returns a result aligned row-for-row with the original DataFrame (the group's mean broadcast onto every row in it), so you can assign it back and subtract. agg collapses each group to a single row, which won't line up with the original rows." } ] ``` --- # Joining & Combining Up to now we've worked one table - the sales DataFrame, all by itself - but real analysis is almost never one table. Your sales rows know the `product` name and a `units` count - but *what category is that product? Who's the supplier?* That information lives somewhere else, in a `products` table, because repeating "Widgets are Hardware, supplied by Acme" on every single sales row would be wasteful and error-prone. So the data gets split across tables on purpose, linked by a shared value. This phase is about putting it back together. 📝 Here's the mental model: **combining means matching rows from one table to rows in another using a shared key.** If you've touched SQL, you already know this move by its real name - it's a **JOIN**, and pandas' `merge` *is* that join, just with DataFrame syntax instead of `SELECT ... JOIN ... ON`. Same picture, same join types, same gotchas. If joins have never quite clicked, the calm walkthrough in [SQL Joins, Finally Explained](/guides/sql-joins-explained) draws the picture in pure SQL terms; everything there maps one-to-one onto what we're about to do. We'll keep the running sales dataset, and introduce a second `products` table to join to it: ```python import pandas as pd sales = pd.DataFrame({ "date": ["2024-01-05", "2024-01-05", "2024-01-06", "2024-01-06", "2024-01-07"], "product": ["Widget", "Gadget", "Widget", "Gadget", "Gizmo"], "region": ["North", "South", "North", "West", "South"], "units": [10, 4, 7, 12, 5], "price": [9.99, 19.99, 9.99, 19.99, 14.99], }) sales["revenue"] = sales["units"] * sales["price"] products = pd.DataFrame({ "product": ["Widget", "Gadget", "Sprocket"], "category": ["Hardware", "Electronics", "Hardware"], "supplier": ["Acme", "Globex", "Acme"], }) ``` *What just happened:* we built two tables that share a `product` column - that shared column is the **key** that lets us tie a sales row to its product details. Notice the deliberate mismatch: `sales` has a `Gizmo` that isn't in `products`, and `products` has a `Sprocket` that never sold. Those non-matches are exactly where join types start to matter. ## merge and the join types The core function is `pd.merge`. You hand it two DataFrames, tell it the key with `on=`, and tell it *which rows to keep* with `how=`. Start with the default, `how="inner"`: ```python result = pd.merge(sales, products, on="product", how="inner") print(result[["product", "units", "category", "supplier"]]) ``` ```console product units category supplier 0 Widget 10 Hardware Acme 1 Widget 7 Hardware Acme 2 Gadget 4 Electronics Globex 3 Gadget 12 Electronics Globex ``` *What just happened:* `merge` matched each sales row to the `products` row with the same `product`, and glued the `category` and `supplier` columns on. The `how="inner"` means **keep only rows that matched on both sides** - so the `Gizmo` sale (no product entry) and the `Sprocket` product (no sale) both vanished. Inner join = the intersection. Often you don't want rows to disappear - you want every sales row preserved, matched detail where it exists and blanks where it doesn't. That's a **left** join (`how="left"`): keep all of the left table: ```python result = pd.merge(sales, products, on="product", how="left") print(result[["product", "units", "category", "supplier"]]) ``` ```console product units category supplier 0 Widget 10 Hardware Acme 1 Gadget 4 Electronics Globex 2 Widget 7 Hardware Acme 3 Gadget 12 Electronics Globex 4 Gizmo 5 NaN NaN ``` *What just happened:* every original sales row survived - including the `Gizmo` sale. But `Gizmo` has no entry in `products`, so pandas had nothing to fill `category` and `supplier` with and put `NaN` there. ⚠️ This is the thing to internalize about left/right/outer joins: **non-matches don't drop the row, they fill the borrowed columns with `NaN`.** A sudden crop of `NaN`s after a merge usually means keys that didn't line up, not missing source data. The `how=` options, in one breath: - **`inner`** - only rows that match on both sides (the default). The intersection. - **`left`** - every row from the left table; `NaN` in the right's columns where there's no match. - **`right`** - mirror image: every row from the right table; `NaN` on the left where there's no match. - **`outer`** - every row from *both* tables; `NaN` wherever either side had no match. The union. An outer join shows both lonely rows at once: ```python result = pd.merge(sales, products, on="product", how="outer") print(result[["product", "units", "category"]]) ``` ```console product units category 0 Gadget 4.0 Electronics 1 Gadget 12.0 Electronics 2 Gizmo 5.0 NaN 3 Sprocket NaN Hardware 4 Widget 7.0 Hardware 5 Widget 10.0 Hardware ``` *What just happened:* the outer join kept everything - the `Gizmo` sale with no product (`category` is `NaN`) *and* the `Sprocket` product with no sale (`units` is `NaN`). Notice `units` turned into floats (`4.0`): pandas widens an integer column to float so it can hold `NaN`, since plain ints can't represent missing. 💡 Pick the join type by asking "which rows must survive even if they don't match?" - none (inner), the left ones (left), or all of them (outer). ## Choosing the join keys `on="product"` works when both tables name the key column identically. They often don't. Say `sales` calls it `product` but `products` calls it `item` - use `left_on` and `right_on` to name each side: ```python products_alt = products.rename(columns={"product": "item"}) result = pd.merge(sales, products_alt, left_on="product", right_on="item", how="inner") print(result[["product", "item", "category"]].head(2)) ``` ```console product item category 0 Widget Widget Hardware 1 Widget Widget Hardware ``` *What just happened:* `left_on="product"` and `right_on="item"` told merge that those two differently-named columns hold the same key. The match worked exactly as before; you just get *both* key columns in the result (drop one with `.drop(columns="item")` if it bothers you). If your key sits in the **index** rather than a column, use `left_index=True` / `right_index=True` the same way. Now the trap that bites everyone at least once. ⚠️ **A merge multiplies rows when the key isn't unique on the side you're joining to.** If `products` accidentally listed `Widget` twice, every `Widget` sale would match *both* rows and your result would silently double: ```python products_dupe = pd.concat([products, products.iloc[[0]]]) # Widget appears twice print(len(sales), "sales rows ->", len(pd.merge(sales, products_dupe, on="product", how="inner")), "after merge") ``` ```console 5 sales rows -> 6 after merge ``` *What just happened:* the duplicate `Widget` in the lookup table turned a 5-row merge into a 6-row one - each of the two `Widget` sales matched two product rows... but here only one extra appeared because of how the counts fell, and on bigger data this kind of many-to-many blow-up can multiply your rows into the millions. The defense is to declare what you expect with `validate=`: ```python pd.merge(sales, products_dupe, on="product", how="inner", validate="many_to_one") ``` ```console MergeError: Merge keys are not unique in right dataset; not a one-to-many merge ``` *What just happened:* `validate="many_to_one"` asserts "many sales rows, but each product key appears once in the lookup." Because the key *wasn't* unique on the right, pandas refused the merge and told you why - far better than a silently inflated result you discover three steps later. Reach for `validate=` whenever you're joining a fact table to what's supposed to be a one-row-per-key lookup. ## concat: stacking, not matching `merge` relates tables side by side on a key. The other combining move is just **stacking** - gluing rows on top of each other, or columns next to each other, with no key matching at all. 📝 **`pd.concat([df1, df2])` stacks tables.** By default it stacks rows (one DataFrame's rows after the other's) - perfect for "I have January sales in one DataFrame and February in another, give me one table": ```python jan = sales.iloc[:2] feb = sales.iloc[2:] combined = pd.concat([jan, feb]) print(len(jan), "+", len(feb), "->", len(combined), "rows") ``` ```console 2 + 3 -> 5 rows ``` *What just happened:* `concat` lined the two frames up and stacked February's rows below January's into one 5-row table. The two frames have the **same columns**, which is what makes stacking sensible - concat aligns by column name and fills `NaN` for any column one frame lacks. Pass `axis=1` instead and concat glues columns side by side (aligning on the index) rather than stacking rows. So when do you reach for which? **merge when the tables *relate by a key*** (sales ↔ products: different shapes, joined on a shared value). **concat when the tables are *more of the same thing*** (Jan + Feb sales: same columns, appended). That distinction - relate by key vs. stack more of the same - covers the vast majority of data assembly you'll ever do. ## Suffixes and verifying the join One last practicality. When both tables have a non-key column with the **same name**, merge can't keep two columns named the same, so it disambiguates with `_x` (left) and `_y` (right) suffixes: ```python # both tables happen to have a "region" column prod_region = pd.DataFrame({"product": ["Widget", "Gadget"], "region": ["Imported", "Domestic"]}) result = pd.merge(sales, prod_region, on="product", how="inner") print(result[["product", "region_x", "region_y"]].head(2)) ``` ```console product region_x region_y 0 Widget North Imported 1 Widget North Imported ``` *What just happened:* both frames carried a `region` column, so merge kept the sales one as `region_x` and the product one as `region_y`. Cryptic. Set readable names yourself with `suffixes=`: `pd.merge(sales, prod_region, on="product", suffixes=("_sale", "_origin"))` gives you `region_sale` and `region_origin`. ⚠️ And the habit that saves you the most grief: **check the row count before and after every merge.** A left/inner merge should never *increase* your left table's row count - if it does, your key isn't unique and rows multiplied. A merge that was meant to enrich your data but changed how many rows you have is a bug, not a result: ```python before = len(sales) result = pd.merge(sales, products, on="product", how="left") print("before:", before, " after:", len(result)) ``` ```console before: 5 after: 5 ``` *What just happened:* a left join with a clean one-row-per-product lookup left the row count untouched at 5 - exactly what you want. The two-second `len()` check is the cheapest bug-catcher in pandas; make it reflex. 💡 The whole phase boils down to two verbs: **merge** to look up related data by key, **concat** to stack more of the same - and almost every "combine these datasets" task is one or the other. ## Recap 1. **Combining = matching rows across tables on a shared key.** `pd.merge` is exactly the SQL JOIN ([SQL Joins, Finally Explained](/guides/sql-joins-explained)) in DataFrame form. 2. **`how=` picks which rows survive:** `inner` (matches only), `left` (all left rows), `right` (all right), `outer` (all rows from both). ⚠️ Non-matches don't drop the row in left/right/outer - they fill the borrowed columns with `NaN`. 3. **Keys:** `on=` for an identically-named column, `left_on`/`right_on` for differently-named ones, `left_index`/`right_index` to join on the index. 4. **The multiplication trap:** if the key isn't unique on the side you join to, rows multiply (a many-to-many join can explode the row count). Guard with `validate=`. 5. **`concat` stacks** rather than matches - rows by default, columns with `axis=1`. Use it for "more of the same" (Jan + Feb sales); use merge for "relate by key." 6. **Verify:** overlapping column names get `_x`/`_y` (override with `suffixes=`), and always check the row count before/after a merge - an unexpected change is a bug. ## Quick check Make sure the join types and the row-count instinct stuck: ```quiz [ { "q": "You merge sales (5 rows) with a products lookup using how=\"left\", and a few sales have a product not in the lookup. What happens to those rows?", "choices": [ "They're kept, with NaN filled into the columns borrowed from products", "They're dropped, because there's no match", "The whole merge raises an error", "They're duplicated until a match is found" ], "answer": 0, "explain": "A left join keeps every left (sales) row. Where there's no matching product, pandas has nothing to fill the borrowed columns with, so it puts NaN there - the row stays." }, { "q": "Your products lookup accidentally lists \"Widget\" on two rows. You inner-merge sales onto it. What's the danger?", "choices": [ "Each Widget sale matches both Widget rows, multiplying your row count", "The merge silently drops all Widget rows", "pandas automatically deduplicates the lookup for you", "Nothing - duplicate keys are always fine" ], "answer": 0, "explain": "A non-unique key on the side you join to multiplies rows: every sale matches every duplicate. Declaring validate=\"many_to_one\" makes pandas refuse the merge instead of silently inflating it." }, { "q": "You have January sales and February sales in two DataFrames with identical columns, and want one combined table. Which tool fits?", "choices": [ "pd.concat([jan, feb]) - stacking more of the same", "pd.merge(jan, feb, on=\"date\") - relate by key", "Neither; you must loop and append row by row", "pd.merge with how=\"outer\" on every column" ], "answer": 0, "explain": "Same shape, same columns, just more rows of the same thing - that's concat (stacking). merge is for relating different tables by a shared key, which isn't what's happening here." } ] ``` --- # Time Series & Dates Almost every sales dataset has a `date` column, and almost every interesting question about it is a time question: how did revenue trend month over month? Which weekday sells best? What's the seven-day moving average? pandas has a whole toolkit for this, but it only works once pandas knows your dates are *dates*. Here's the mental model for the entire phase: **a date is only powerful once pandas stores it as a real datetime instead of text.** A column of date strings is just letters to pandas - it can't sort them by calendar, can't pull the month out, can't bucket by week. The moment you convert it to the `datetime64` type, three superpowers unlock at once: extract any part of the date, slice by partial dates, and roll the data up to any time period. The rest of this phase is those three powers, plus a couple of tools you'll reach for on real, messy time series. We'll keep working the running sales dataset (`date`, `product`, `region`, `units`, `price`), with the `revenue` column we built earlier - but with a longer stretch of daily rows so the time tools have something to chew on: ```python import pandas as pd import numpy as np sales = pd.DataFrame({ "date": ["2024-01-05", "2024-01-12", "2024-02-03", "2024-02-20", "2024-03-08", "2024-03-25"], "product": ["Widget", "Gadget", "Widget", "Gadget", "Widget", "Gadget"], "region": ["North", "South", "North", "West", "South", "North"], "units": [10, 4, 7, 12, 5, 9], "price": [9.99, 19.99, 9.99, 19.99, 9.99, 19.99], }) sales["revenue"] = sales["units"] * sales["price"] ``` ## Parsing dates: from text to real datetimes 📝 When you load a CSV, a date column almost always arrives as **strings** - pandas stores it with the `object` dtype, the same type it uses for plain text. It looks like a date to *you*, but to pandas `"2024-01-05"` is just six characters. `pd.to_datetime(df["date"])` converts that text into real `datetime64` values, and that conversion is the gate you have to walk through before any date math, date sorting, or resampling will work. Watch the dtype change - that's the whole point: ```python print(sales["date"].dtype) sales["date"] = pd.to_datetime(sales["date"]) print(sales["date"].dtype) ``` ```console object datetime64[ns] ``` *What just happened:* before conversion, `sales["date"]` reported `object` - pandas was holding the dates as text. After `pd.to_datetime`, the same column reports `datetime64[ns]` (the `[ns]` means nanosecond precision), so each value is now a true point on the calendar. Nothing looks different when you print the column, but everything that follows in this phase now works. ⚠️ This is not optional housekeeping - a date stored as a string lies to you in two quiet ways. It **sorts alphabetically**, not chronologically: text sorting puts `"2024-1-9"` after `"2024-1-10"` because `9` comes after `1` character by character. And you **cannot resample** it - every time tool in this phase will either error or silently treat the column as meaningless text. When in doubt, check `.dtype` and convert. A common shortcut is to parse at load time: `pd.read_csv("sales.csv", parse_dates=["date"])` hands you a real datetime column straight away. ## The .dt accessor: pulling date parts out 📝 Just as `.str` gives you string methods on a column of text, **`.dt` gives you date methods on a column of datetimes** - and like everything in pandas, they're vectorized, running over the whole column at once. `df["date"].dt.year`, `.dt.month`, `.dt.day_name()`, `.dt.quarter` each pull one piece out of every date in a single sweep. This is how you derive the columns that time analysis usually wants - a month number to group by, a weekday name to compare: ```python sales["month"] = sales["date"].dt.month sales["weekday"] = sales["date"].dt.day_name() sales["quarter"] = sales["date"].dt.quarter print(sales[["date", "month", "weekday", "quarter"]]) ``` ```console date month weekday quarter 0 2024-01-05 1 Friday 1 1 2024-01-12 1 Friday 1 2 2024-02-03 2 Saturday 1 3 2024-02-20 2 Tuesday 1 4 2024-03-08 3 Friday 1 5 2024-03-25 3 Monday 1 ``` *What just happened:* `.dt.month` pulled the month number from every date, `.dt.day_name()` produced the spelled-out weekday, and `.dt.quarter` gave the calendar quarter - each as a new column, computed for all six rows at once. None of this would work on a string column; the `.dt` accessor only exists on real datetimes (try it on text and pandas raises `AttributeError`). Now that you have a `weekday` column, you could lean on the previous phase and group by it - `sales.groupby("weekday")["revenue"].sum()` - to see which day of the week sells best. The `.dt` accessor is what turns a single date into the categories you slice your sales by. ## A DatetimeIndex: slicing by partial dates 📝 Set the date column as the DataFrame's **index** with `df.set_index("date")`, and you unlock something that feels like magic the first time you see it: **time-based slicing by partial date strings.** With a `DatetimeIndex`, `df.loc["2024-01"]` returns every row in January - you name the period and pandas figures out the range. `df.loc["2024-01":"2024-03"]` returns the whole first quarter. ```python sales_ts = sales.set_index("date").sort_index() print(sales_ts.loc["2024-02", ["product", "revenue"]]) ``` ```console product revenue date 2024-02-03 Widget 69.93 2024-02-20 Gadget 239.88 ``` *What just happened:* `set_index("date")` moved the date column into the index (we `sort_index()` too, since range selection wants the index in order), and then `loc["2024-02"]` matched every date whose year and month are February 2024 - no `>=`/`<` boolean mask, no manual date comparison. This is **partial-string date selection**, and it's one of pandas' genuine superpowers. A range works the same way: ```python print(sales_ts.loc["2024-01":"2024-02", ["product", "revenue"]]) ``` ```console product revenue date 2024-01-05 Widget 99.90 2024-01-12 Gadget 79.96 2024-02-03 Widget 69.93 2024-02-20 Gadget 239.88 ``` *What just happened:* `loc["2024-01":"2024-02"]` grabbed everything from the start of January through the end of February - and note the range is **inclusive on both ends**, unlike normal Python slicing where the stop is excluded. You wrote the months you cared about and pandas handled the calendar boundaries. ⚠️ For range slicing to behave, the index must be sorted; an unsorted DatetimeIndex can raise or return surprising results, which is why `sort_index()` is a good reflex right after `set_index`. ## Resampling: groupby over time 📝 **`resample` is groupby, but the groups are time buckets instead of category values.** Where Phase 6's `groupby("region")` made one group per region, `resample("ME")` makes one group per month-end, `"W"` per week, `"D"` per day - then you aggregate each bucket exactly like a groupby. It needs a DatetimeIndex (the one you just set), and the whole shape mirrors what you already know: `df.resample("ME")["revenue"].sum()` gives monthly revenue totals. Here we roll our scattered daily sales up into monthly revenue: ```python monthly = sales_ts.resample("ME")["revenue"].sum() print(monthly) ``` ```console date 2024-01-31 179.86 2024-02-29 309.81 2024-03-31 229.86 Freq: ME, dtype: float64 ``` *What just happened:* `resample("ME")` bucketed the rows by calendar month (`ME` = month-end, so each bucket is labeled with the last day of its month), `["revenue"]` picked the column to aggregate, and `.sum()` added up the revenue in each bucket. January's two orders summed to `179.86`, February's to `309.81`, and so on. Notice the chain reads exactly like a groupby - *split into time buckets, pick a column, aggregate* - because that's precisely what it is. Swap `"ME"` for `"W"` to get weekly totals or `"D"` for daily, and swap `.sum()` for `.mean()`, `.max()`, or `.count()` just as you would after a groupby. 💡 The one sentence to remember: **resample = "group by a time period."** Once that clicks, every frequency string (`"D"`, `"W"`, `"ME"`, `"QE"` for quarter, `"YE"` for year) is just choosing how wide your buckets are, and every aggregation you learned for groupby works unchanged. ## Rolling windows and timezones (brief) A close cousin of resampling is the **rolling window** - instead of collapsing data into fixed buckets, it slides a window of N rows along the series and aggregates within it. The classic use is smoothing a noisy daily series into a moving average so the trend shows through the wiggle: ```python daily = sales_ts.resample("D")["revenue"].sum() print(daily.rolling(7).mean().tail(3)) ``` ```console date 2024-03-23 0.0 2024-03-24 0.0 2024-03-25 142.84 ``` *What just happened:* `resample("D")` first filled in every calendar day (days with no sale become `0`), then `rolling(7).mean()` slid a seven-day window across that daily series and averaged each window - so the last value is the mean revenue over the trailing week ending March 25. Rolling averages are how you turn a spiky day-to-day series into a readable trend line. (The leading days read as their own short-window averages; with default settings a window only produces a number once it has enough rows.) One plain warning about **timezones**, because they bite people on real data. The datetimes you've made so far are *naive* - they carry no timezone, just a wall-clock reading. You can attach one with `df.index.tz_localize("UTC")` and convert between zones with `tz_convert("America/New_York")`. ⚠️ The trap is mixing the two: pandas refuses to compare or combine a naive datetime with a timezone-aware one, and will raise rather than guess. Pick one convention (storing everything in UTC is the common, sane default) and stick to it across your whole dataset. 💡 Put it all together and the toolkit is complete: with real datetimes you can **slice by date** (`loc["2024-02"]`), **extract parts** (`.dt.month`, `.dt.day_name()`), and **roll up to any period** (`resample("ME").sum()`). That trio - plus rolling windows for smoothing - is the foundation of essentially any time-based analysis you'll ever do. ## Recap 1. **A date is only powerful once it's a real datetime.** CSVs load dates as `object` (text) strings; `pd.to_datetime(df["date"])` converts them to `datetime64`, which is required before any date math, sorting, or resampling. ⚠️ A string date sorts alphabetically and can't be resampled. 2. **The `.dt` accessor** is `.str` for dates: `df["date"].dt.year`, `.dt.month`, `.dt.day_name()`, `.dt.quarter` pull date parts out, vectorized over the whole column - handy for deriving columns to group by. 3. **A DatetimeIndex** (`df.set_index("date")`) unlocks partial-string date slicing: `df.loc["2024-01"]` for a whole month, `df.loc["2024-01":"2024-03"]` for a range (inclusive on both ends). Sort the index first. 4. **`resample` is groupby over time.** `df.resample("ME")["revenue"].sum()` buckets by month-end and sums; `"W"` is weekly, `"D"` daily. Same split-pick-aggregate shape as Phase 6 - resample = "group by a time period." 5. **Rolling windows** (`rolling(7).mean()`) slide a window along the series to smooth noisy data into a moving average - collapsing nothing, just averaging locally. 6. **Timezones bite.** Naive datetimes carry no zone; `tz_localize` / `tz_convert` add and shift one, and mixing naive with timezone-aware values raises an error. Store everything in one convention (UTC is the safe default). ## Quick check Lock in the three superpowers - convert first, then extract, slice, and roll up: ```quiz [ { "q": "Your `date` column loaded from a CSV has dtype `object` and won't resample. What's the fix?", "choices": [ "Convert it with pd.to_datetime(df[\"date\"]) so it becomes datetime64", "Sort the DataFrame by the date column", "Rename the column to \"datetime\"", "Nothing - object columns resample fine" ], "answer": 0, "explain": "An object column holds dates as plain strings. pd.to_datetime converts them to real datetime64 values, which is required before resampling, the .dt accessor, or chronological sorting will work." }, { "q": "With a DatetimeIndex, what does `df.loc[\"2024-02\"]` return?", "choices": [ "Every row whose date falls in February 2024", "Only the single row dated exactly 2024-02-01", "An error - loc needs a full date string", "The 2024th and 2nd rows by position" ], "answer": 0, "explain": "Partial-string date selection: a DatetimeIndex lets you pass just \"2024-02\" and pandas matches every date in that month. \"2024-01\":\"2024-03\" works the same way for a range (inclusive on both ends)." }, { "q": "How is `df.resample(\"ME\")[\"revenue\"].sum()` related to groupby?", "choices": [ "It's groupby where the groups are time buckets (here, each month) instead of category values", "It's unrelated - resample sorts the data, it doesn't aggregate", "It only counts rows and can't sum a column", "It replaces the date index with integers" ], "answer": 0, "explain": "resample is groupby over time: \"ME\" makes one bucket per month-end, then you pick a column and aggregate exactly like a groupby. resample = \"group by a time period.\"" } ] ``` --- # Reshaping & Pivoting You've spent eight phases working data row by row - selecting, cleaning, grouping. This phase is a different superpower: changing the *shape* of the table itself without changing a single number in it. 📝 Here's the mental model that runs the whole phase: **the same data can be stored in two shapes, and neither is more "correct" than the other.** It's *long* (one row per observation, lots of rows, few columns) or it's *wide* (categories spread out across columns, like a spreadsheet report). Reshaping is just converting between them. Different tasks want different shapes - a human reading a report wants wide; a plotting library or a model usually wants long - so the skill is recognizing which shape you have, which one your task needs, and the one command that gets you there. We'll keep working the running sales dataset (`date`, `product`, `region`, `units`, `price`, `revenue`): ```python import pandas as pd sales = pd.DataFrame({ "date": ["2024-01-05", "2024-01-05", "2024-01-06", "2024-01-06", "2024-01-07", "2024-01-07"], "product": ["Widget", "Gadget", "Widget", "Gadget", "Widget", "Gadget"], "region": ["North", "South", "North", "West", "South", "North"], "units": [10, 4, 7, 12, 5, 8], "price": [9.99, 19.99, 9.99, 19.99, 9.99, 19.99], }) sales["revenue"] = sales["units"] * sales["price"] ``` *What just happened:* the same setup as ever, with `revenue` derived from `units * price`. Notice the shape it's in: one row per sale, with `region` and `product` living as *values* inside columns. That's **long format** - and it's the shape pandas (and almost every data tool) prefers to receive. ## Wide vs long: the core idea 📝 The two shapes are easiest to feel by seeing the same fact in both. **Long** is what we have above: each row is one observation, and the things you'd group by (`region`, `product`) are stored as ordinary column values. If you added a new region, you'd add *rows*, not columns. **Wide** takes one of those category columns and spreads its distinct values across the *top* as column headers. Imagine total revenue by region and product laid out as a grid: ```console product Gadget Widget region North 159.92 169.83 South 79.96 49.95 West 239.88 NaN ``` *What just happened:* nothing changed about the underlying numbers - this is the *same revenue*, reshaped. In long form, `product` was a column of values (`"Gadget"`, `"Widget"`). In wide form, those values became the column *headers*, and each cell holds the revenue where a region meets a product. A person reading a report loves this layout; a plotting library usually hates it. That tension is the whole reason reshaping exists. 💡 Quick gut-check for "which shape am I looking at?": if adding a new category (a new product) would mean adding a **row**, you're long; if it would mean adding a **column**, you're wide. ## pivot_table: long → wide summary The workhorse for going long → wide is `pivot_table`, and it's exactly the spreadsheet feature of the same name. You tell it three things: what goes down the side (`index`), what spreads across the top (`columns`), and what fills the cells (`values`, plus how to combine them with `aggfunc`). ```python report = sales.pivot_table( values="revenue", index="region", columns="product", aggfunc="sum", ) print(report) ``` ```console product Gadget Widget region North 159.92 169.83 South 79.96 49.95 West 239.88 NaN ``` *What just happened:* `pivot_table` put each distinct `region` down the side as a row, each distinct `product` across the top as a column, and filled every cell with the **sum** of `revenue` for that region-product pair. North bought both products, so it has two numbers; West only bought Gadgets, so its Widget cell is `NaN` (there were no Widget sales in West to sum). 💡 Here's the connection to lean on: this *is* a groupby from [Phase 6](06-groupby-and-aggregation.md), reshaped into a grid. `sales.groupby(["region", "product"])["revenue"].sum()` computes the exact same six numbers - `pivot_table` just lays them out as a 2-D table instead of a stacked list. Same aggregation, prettier shape. That's why it's the go-to for human-readable reports. Two options make those reports much nicer. `fill_value` replaces the gaps, and `margins=True` adds totals: ```python report = sales.pivot_table( values="revenue", index="region", columns="product", aggfunc="sum", fill_value=0, margins=True, ) print(report) ``` ```console product Gadget Widget All region North 159.92 169.83 329.75 South 79.96 49.95 129.91 West 239.88 0.00 239.88 All 479.76 219.78 699.54 ``` *What just happened:* `fill_value=0` swapped that empty West/Widget cell for a clean `0.00`, and `margins=True` added an `All` row and `All` column holding the row totals, column totals, and the grand total (`699.54`) in the corner. That's a finished report in one call - exactly what you'd hand a stakeholder. ## pivot vs pivot_table There's a near-twin called `pivot`, and the difference is worth knowing so it doesn't bite you. ⚠️ **`pivot` does no aggregation.** It assumes each index/column pair appears exactly once, and it errors out the moment two rows land in the same cell - `ValueError: Index contains duplicate entries`. Our data *has* duplicates (North/Widget shows up on two different dates), so `sales.pivot(index="region", columns="product", values="revenue")` would blow up trying to fit two revenue numbers into one cell with no instruction for how to combine them. `pivot_table` doesn't have that problem because aggregating duplicates is its whole job - that's what `aggfunc="sum"` is *for*. It takes the two North/Widget rows and sums them into one cell. 💡 Rule of thumb: **reach for `pivot_table` by default.** Use plain `pivot` only when you're certain every index/column pair is unique (already-summarized data, for example) and you want it to *fail loudly* if that assumption is wrong. For real, messy data, `pivot_table` is the safe choice. ## melt: wide → long `melt` is the inverse move - it takes a wide table and collapses those spread-out columns back into long key/value rows. This comes up constantly: someone hands you a spreadsheet-shaped report (months across the top, say) and you need it long before you can plot, filter, or feed it to a model. Picture a wide table where each region has its monthly revenue across columns: ```python wide = pd.DataFrame({ "region": ["North", "South", "West"], "Jan": [329.75, 129.91, 239.88], "Feb": [410.20, 188.40, 301.55], }) long = pd.melt( wide, id_vars="region", var_name="month", value_name="revenue", ) print(long) ``` ```console region month revenue 0 North Jan 329.75 1 South Jan 129.91 2 West Jan 239.88 3 North Feb 410.20 4 South Feb 188.40 5 West Feb 301.55 ``` *What just happened:* `melt` kept `region` fixed (that's `id_vars` - the column to anchor on) and unpivoted the two month columns (`Jan`, `Feb`) into rows. The old column *names* became values in a new `month` column (`var_name`), and the old cell *contents* became values in a new `revenue` column (`value_name`). Three wide rows became six long ones. Now each row is a single observation again - which is exactly the tidy shape that plotting and modeling tools expect. `melt` is `pivot_table` run backward. ## stack, unstack, and crosstab Two more reshaping tools round out the kit. Keep these in your back pocket. `stack` and `unstack` reshape between columns and a **MultiIndex** - the multi-level index you got from grouping by more than one key in [Phase 6](06-groupby-and-aggregation.md). `unstack` lifts an index level up into columns (long → wide); `stack` pushes columns down into the index (wide → long): ```python grouped = sales.groupby(["region", "product"])["revenue"].sum() print(grouped.unstack()) ``` ```console product Gadget Widget region North 159.92 169.83 South 79.96 49.95 West 239.88 NaN ``` *What just happened:* `groupby` gave us a Series with a two-level (`region`, `product`) MultiIndex - the same six numbers as before, stacked vertically. `unstack()` popped the inner `product` level up to become columns, producing the very same wide grid `pivot_table` gave us. That's not a coincidence: `pivot_table` is essentially `groupby` + `unstack` bundled into one friendly call. `stack()` would undo it, folding the columns back down into the index. For a pure **frequency table** - how many times each combination occurs - `pd.crosstab` is the shortcut: ```python print(pd.crosstab(sales["region"], sales["product"])) ``` ```console product Gadget Widget region North 1 1 South 1 1 West 1 0 ``` *What just happened:* `crosstab` counted the *rows* for each region/product pair - North had one Gadget sale and one Widget sale, West had one Gadget sale and zero Widget sales. It's `pivot_table` with `aggfunc="count"` wired in by default, purpose-built for "how often does each combination show up?" 💡 The takeaway for the whole phase: **pick the shape your task needs, and reshape freely to get there.** Wide for a human-readable report; long for plotting and modeling. The numbers never change - only their arrangement does - so reshaping is a cheap, reversible move you should make without hesitation the moment a tool wants the other shape. ## Recap 1. **Long vs wide is the core idea.** Long = one row per observation, categories as column *values*; wide = categories spread across column *headers*. Same data, different arrangement - neither is more correct. 2. **`pivot_table(values, index, columns, aggfunc)`** goes long → wide: it's a [Phase 6](06-groupby-and-aggregation.md) groupby reshaped into a grid. Add `fill_value` to clean gaps and `margins=True` for row/column totals. 3. **`pivot` vs `pivot_table`:** ⚠️ plain `pivot` does no aggregation and errors on duplicate index/column pairs; `pivot_table` aggregates duplicates. Default to `pivot_table` for real data. 4. **`pd.melt(df, id_vars, var_name, value_name)`** goes wide → long, unpivoting spread-out columns back into key/value rows - the shape plotting and modeling tools want. 5. **`stack`/`unstack`** reshape between columns and a MultiIndex (`unstack` = long → wide, `stack` = wide → long); `pd.crosstab` builds frequency tables in one call. 6. **Pick the shape your task needs and reshape freely** - wide for reports, long for plotting/modeling. The numbers stay put; only the layout moves. ## Quick check Make sure the two shapes - and the one command for each direction - have stuck: ```quiz [ { "q": "Your sales data is long (one row per sale) and you want a report with regions down the side and products across the top, summing revenue. Which tool fits?", "choices": [ "sales.pivot_table(values=\"revenue\", index=\"region\", columns=\"product\", aggfunc=\"sum\")", "pd.melt(sales, id_vars=\"region\")", "sales.stack()", "sales.sort_values(\"revenue\")" ], "answer": 0, "explain": "pivot_table is the long → wide summary: index goes down the side, columns spread across the top, and aggfunc combines the values in each cell. It's a groupby reshaped into a grid." }, { "q": "Why prefer pivot_table over plain pivot for real-world data?", "choices": [ "pivot_table aggregates duplicate index/column pairs; plain pivot does no aggregation and errors when a pair appears more than once", "pivot is deprecated and no longer works", "pivot can only handle numeric columns", "There is no difference - they are aliases for the same function" ], "answer": 0, "explain": "pivot assumes each index/column pair is unique and raises a ValueError on duplicates. pivot_table aggregates them (via aggfunc), so it's the safe default for messy data." }, { "q": "You receive a wide table with months as columns (Jan, Feb, ...) and need it long for plotting. Which function reshapes it?", "choices": [ "pd.melt - it unpivots the month columns into key/value rows", "pd.pivot_table - it spreads values into columns", "pd.crosstab - it builds a frequency table", "df.fillna - it fills missing values" ], "answer": 0, "explain": "melt is the wide → long move: it keeps id_vars fixed and collapses the spread-out columns into a var_name/value_name pair of long rows - the tidy shape plotting tools expect." } ] ``` --- # Plotting & Where to Go Next Stop for a second and look at what you can actually do now. You can take a raw, messy CSV of sales records and **load** it, **inspect** it to see what you've got, **select and filter** down to the rows that matter, **clean** the missing values and wrong types, **transform** it with new columns, **group and aggregate** it with the split-apply-combine pattern, **join** it against another dataset, parse and resample it over **time**, and **reshape** it between wide and long. That's not a warm-up. That is the core loop of practical data analysis - the same moves a working analyst makes every day, whatever the dataset. And the whole thing rests on two ideas you've been carrying since Phase 1: a **DataFrame is a table you compute on**, and **almost everything is a column operation**. Hold those two and pandas stops being a grab-bag of methods. This last phase isn't a pile of new methods - it's turning your analysis into a picture, getting your results back out of pandas, a clear word about where pandas stops being the right tool, and a real project to point all of this at. ## Quick charts, right off the DataFrame 📝 Here's a thing people don't realize for too long: pandas can plot for you directly. It sits on top of **matplotlib**, and every DataFrame and Series has a `.plot()` method. You don't have to leave your analysis to see it. The fastest path is one line. Say you've grouped monthly revenue into a Series indexed by month - a line chart is right there: ```python import pandas as pd # monthly_revenue is a Series: month -> total revenue monthly_revenue.plot(kind="line", title="Revenue by month") ``` Want bars instead? Change one word, or use the shortcut form: ```python # revenue per region, as a bar chart revenue_by_region.plot.bar(title="Revenue by region") ``` And when you're plotting straight from a DataFrame with several columns, tell it which column is the x-axis and which is the y: ```python # DataFrame with 'month' and 'revenue' columns sales.plot(x="month", y="revenue", kind="line") ``` 💡 These are **exploratory** charts - the kind you throw up to see the shape of your data while you're still working, not the kind you put in a board deck. That's exactly what they're for. The point is speed: a question, a `.plot()`, an answer, and you move on. When you do need something prettier or interactive - for a report or a dashboard - reach for a dedicated plotting library. **seaborn** makes statistical charts that look polished with almost no fuss, and **plotly** gives you interactive charts you can hover and zoom. If you're heading toward dashboards specifically, [BI Dashboards That Work](/guides/bi-dashboards-that-work) is the guide that picks up where these quick charts leave off. ## Getting data back out A chart is one kind of output. Often you want the *data* itself - cleaned, joined, summarized - handed off somewhere else. pandas reads from a lot of formats, and it writes to all the same ones. Every `read_*` has a matching `to_*`: ```python df.to_csv("summary.csv", index=False) # plain text, universal df.to_excel("summary.xlsx", index=False) # for the spreadsheet folks df.to_parquet("summary.parquet") # compact, typed, fast to reload df.to_sql("sales_summary", engine) # straight into a database table ``` This is one of the quietly useful things about pandas: it's the **bridge between systems**. Pull from a database, clean it up, write it to Excel for a colleague - or read three messy CSVs, stitch them together, and load the result into SQL. (`index=False` on the text formats keeps pandas from writing the row index as an extra column, which is usually what you want.) ## Performance, and when to leave pandas 💡 Remember the habit from Phase 5: **think in whole columns, not loops.** pandas operations are vectorized - they run over an entire column at once in fast, compiled code. A `for` loop over rows does the same work one Python step at a time, which can be hundreds of times slower. Ninety percent of "pandas is slow" turns out to be a loop that should have been a column operation. Reach for vectorized methods first, `apply` second, and a raw row loop almost never. But clarity matters here, so: ⚠️ **pandas has real limits.** It's single-machine and it holds everything in **memory**. That's fine for the millions-of-rows datasets most people actually have. It stops being fine when your data is bigger than your RAM, or when even vectorized pandas isn't fast enough. At that point you don't write cleverer pandas - you pick a different tool: ```mermaid flowchart TD Q{Does pandas hurt?} -->|No| P[Stay in pandas] Q -->|Need more speed| Polars[Polars: faster, similar API] Q -->|Querying big files| Duck[DuckDB / SQL on files] Q -->|Bigger than one machine| Dask[Dask / Spark: distributed] ``` *What this shows:* don't switch on a hunch. **Polars** is the easy next step - it's much faster and the API feels familiar. **DuckDB** lets you run SQL directly against CSV or Parquet files without loading them whole. **Dask** and **Spark** spread the work across many machines when one machine genuinely isn't enough. The trap is reaching for the big-data hammer too early. Distributed tools add real complexity, and most problems never need them. ⚠️ Don't leave pandas until pandas actually hurts - and when it does, you'll know, because it'll either run out of memory or run out of patience. ## Where to go next - and what to build You've got a foundation now, and it opens onto a few clear paths: - **Data visualization.** Learn **seaborn** properly. Once charts come easily, you communicate findings, not just compute them. - **Machine learning.** This is the big one, and it's closer than you think. The load → clean → transform pipeline you've been practicing *is* the data-prep stage of nearly every ML project - scikit-learn and PyTorch both take their input as the kind of clean, numeric table you now know how to build. [ML Basics for Data People](/guides/ml-basics-for-data-people) is the natural next guide. - **Data engineering.** If moving and reshaping data at scale appeals to you, that path is wide open. But reading isn't the thing that makes it stick. **Building is.** So here's the assignment: find a CSV you actually care about - your bank statement, your music listening history, a public dataset on something you're curious about - and run the **whole pipeline** end to end. Load it. Clean the mess. Group it to answer one specific question. Chart the answer. Then write up, in a couple of sentences, **one thing you learned** that you didn't know before. That single project exercises almost everything in this guide, and "I found this in my own data" is a very different feeling from finishing a tutorial. When you want the canonical reference, the **official pandas documentation** is genuinely good - start with their "**10 minutes to pandas**" page, then keep the API docs open in a tab while you work. Nobody memorizes pandas; everybody looks things up. And remember the through-line: none of this was magic. The DataFrame was a **table** all along - rows, columns, filters, group-bys, joins, the same shapes you already knew from spreadsheets and SQL. The difference now is that you can **compute on it fluently**, in code, on real data. Go find a CSV that matters to you, run it all the way through, and show someone what you found. You're ready. ## Recap 1. **You can run the full analysis loop** - load, inspect, filter, clean, transform, group, join, time-analyze, and reshape real data - and it all rests on "a DataFrame is a table you compute on, mostly with column operations." 2. **pandas plots for you** - `.plot(kind="line"/"bar")`, `series.plot.bar()`, and `df.plot(x=..., y=...)` give fast exploratory charts straight off the data; reach for seaborn or plotly when you need polished or interactive ones. 3. **Every `read_*` has a `to_*`** - `to_csv`, `to_excel`, `to_parquet`, `to_sql` - which makes pandas the bridge between files, spreadsheets, and databases. 4. **Vectorize, and know the ceiling** - think in whole columns instead of loops; but pandas is single-machine and memory-bound, so for data bigger than RAM or needing more speed, look to Polars, DuckDB, or Dask/Spark - and not before pandas actually hurts. 5. **Build one real thing** - take a CSV you care about through load → clean → group → chart and write up one finding; lean on the official docs and "10 minutes to pandas" as you go. ## Quick check Test yourself on the decisions that matter most as you leave this guide: ```quiz [ { "q": "You're mid-analysis and want a fast look at monthly revenue as a line chart. What's the quickest path?", "choices": [ "Export to Excel and chart it there", "Call .plot(kind=\"line\") directly on the Series or DataFrame", "Install plotly and build an interactive dashboard first", "Write a for loop to print each value" ], "answer": 1, "explain": "pandas sits on matplotlib, so every Series and DataFrame has .plot(). It's built for exactly this - fast exploratory charts without leaving your analysis. seaborn/plotly come in later for polished or interactive output." }, { "q": "Your dataset is comfortably bigger than your machine's RAM and pandas keeps running out of memory. What's the sensible move?", "choices": [ "Rewrite your pandas code to be more clever", "Add more for loops to process it row by row", "Reach for a tool built for it - Polars for speed, DuckDB to query files, or Dask/Spark to distribute the work", "Drop most of the data so it fits" ], "answer": 2, "explain": "pandas is single-machine and memory-bound. When data is bigger than RAM, no amount of cleverer pandas fixes that - you switch tools. But don't reach for big-data tooling until pandas genuinely hurts." }, { "q": "Why prefer a vectorized column operation over a Python for loop over rows?", "choices": [ "Loops are not allowed in pandas", "Vectorized operations run over the whole column in fast compiled code, while a row loop does it one slow Python step at a time", "Loops always give wrong answers", "There is no real difference; it's just style" ], "answer": 1, "explain": "Vectorized operations work on entire columns at once in compiled code - often hundreds of times faster and clearer than a row-by-row Python loop. Most 'pandas is slow' complaints are really a loop that should have been a column operation." } ] ```