Pandas Essentials for Machine Learning Data (Lesson 32)

Before any model can learn, someone has to load a table, look at it, reshape it, and hand it over as numbers. That someone is usually pandas. Many practitioners say most of their time goes into data handling, not model code, and that matches common experience. Today we make that part fast and safe.

• Models eat numbers
• Pandas handles tables

In lesson 31 we met NumPy arrays, shapes, and broadcasting. Arrays are fast, but they have no column names and every entry shares one type. Real data has names like size and city, mixed types, and dates. Pandas wraps NumPy arrays with labels, and that is the whole idea.

• Arrays: fast, unnamed
• Pandas: labeled columns

Here is our plan. We build Series and DataFrames, inspect them, select and filter rows, create columns, group, combine tables, reshape, and use text and date tools. Then we cover speed and memory, three classic bugs, and finally convert to a design matrix. We skip plotting, missing values, and splitting, which come in the next lessons.

• Build, select, group, merge
• Bugs and design matrix

A Series is a one-dimensional array with an index of labels. Take prices of two hundred, three hundred, and two hundred forty, labeled by house id. The index is not decoration. It is how pandas decides which values belong together, and that will matter later when we hit the alignment bug.

• Values plus an index
• Labels drive alignment

A DataFrame is a table whose columns are Series sharing one index. Meet our toy table. It is invented, and clearly labeled as such, so you can check every result by hand. Six houses, with columns id, city, size in square feet, bedrooms, price in thousands, and listing date.

• Six invented houses
• Small enough to check by hand

In practice you load data with read_csv, passing a file path. Useful options include usecols to load only needed columns, dtype to force types, and parse_dates to read date columns properly. Choosing these at load time saves effort and memory later. The result is a DataFrame ready to inspect.

• usecols, dtype, parse_dates
• Choose types at load time

After loading, always look. The head method shows the first rows. The info method lists columns, counts, dtypes, and memory. The describe method gives count, mean, standard deviation, minimum, quartiles, and maximum for numeric columns. For our sizes, the mean is 1233.3, which you can verify: seven thousand four hundred divided by six.

• Look before you model
• Mean of sizes: 7400 / 6

Dtypes deserve attention. Integers, floats, booleans, strings stored as object, and datetimes behave differently. A price column read as text will silently refuse arithmetic. Check dtypes right after loading, and convert with astype when a column has the wrong type.

• Wrong dtype breaks math
• Fix with astype

Two selectors do most of the work. The iloc selector uses integer positions, like array indexing. The loc selector uses labels. If our index is the default zero to five, iloc zero returns the first row, while loc with label zero also does, but the two diverge once the index changes.

• iloc: position
• loc: label

A key difference is slicing. With iloc, the end of a slice is excluded, like Python lists. With loc, the end label is included. So iloc zero to three returns three rows, while loc zero to three on a default index returns four. Forgetting this is a classic off-by-one error.

• iloc excludes the end
• loc includes the end

Boolean filtering is the workhorse. The expression size greater than one thousand yields a Series of true and false, one per row. Passing it back as a mask keeps only true rows. In our table, sizes 1500, 1200, and 2000 pass, so we get houses 2, 4, and 5.

• Condition gives True/False
• Mask keeps True rows

Combine conditions with ampersand for and, and a vertical bar for or, and wrap each condition in parentheses. Python's words and, or, not do not work on whole columns. For example, size above one thousand and city equal to Boston keeps only house 4. The isin method is handy for membership checks.

• Use & and | with parentheses
• Not the words and, or

Creating a column is just assignment. Dividing price by size gives price per square foot, in thousands. Houses 1 and 2 give 0.2 and 0.2, houses 3 and 4 give 0.3 each, and houses 5 and 6 give 0.2 and 0.2. The operation runs on the whole column at once, with no loop.

• price_k / size_sqft
• Whole column at once

Sorting uses sort_values with a column name, ascending or descending. Sorting by price descending puts house 5 at four hundred first, then house 4 at three sixty. Notice the index labels travel with their rows, so each row keeps its identity. That stability is part of why labels are useful.

• sort_values by column
• Index travels with rows

Groupby follows split, apply, combine. Split rows by city, compute a statistic in each group, and combine results. The mean price for Austin is two hundred and three hundred averaged, which is 250. Boston gives 240 and 360, so 300. Chicago gives 400 and 180, so 290.

• Austin 250, Boston 300
• Chicago 290

The agg method computes several statistics at once, with different functions per column. For each city we can take mean size, max price, and a count. Austin has mean size 1250, Boston 1000, and Chicago 1450. Named aggregation lets you choose clear output column names, which keeps downstream code readable.

• Many stats per group
• Named aggregation

Real data lives in several tables. Suppose a second small invented table maps each city to its state: Austin to Texas, Boston to Massachusetts, Chicago to Illinois. A merge joins the two on the shared city key, matching rows with equal keys. Each house gains a state column.

• Match rows by city
• Adds state column

The how option decides what happens to unmatched keys. Inner keeps only matches. Left keeps every row from the left table, filling gaps. Right and outer work similarly. For model data, left joins are common because you want to keep every example, but you must check the row count afterward.

• inner, left, right, outer
• Left keeps all left rows

The concat function stacks tables. Along rows, it appends tables with the same columns, like adding next month's data. Along columns, it places tables side by side, aligned on the index. Concat does not match on a key the way merge does, so choose merge for lookups and concat for stacking.

• axis zero stacks rows
• Merge matches keys

A pivot table is groupby with a two-dimensional layout. Put cities down the side and listing months across the top, with mean price in each cell. Austin sits under January at 250, Boston under February at 300, and Chicago under March at 290. Other cells have no data, so they show missing markers. How to handle those is a later lesson.

• Rows, columns, values
• Empty cells appear as NaN

The str accessor applies string operations to a whole column. You can lower-case, strip spaces, test containment, or split text. Cleaning inconsistent city names like Austin with a trailing space is a typical use. Without it, groupby would treat Austin and Austin-space as different groups, quietly splitting your counts.

• Vectorized text methods
• Fix inconsistent labels

The dt accessor does the same for dates, once the column has a datetime dtype. From the listing date you can extract year, month, and day of week. For house 4, listed on February twentieth, the month is 2. These extracted parts often become useful features, since models cannot read raw date strings.

• Needs datetime dtype
• Extract month, weekday

Now speed. The apply method runs a Python function row by row or element by element, which is flexible but slow. Vectorized operations run in compiled code across the whole column. Computing price per square foot with a division is vectorized. Doing it with apply and a lambda gives the same numbers but takes far longer on big tables.

• Same answer, different speed
• Prefer column operations

Use apply only when no built-in operation fits. Before reaching for it, look for string methods, the where method, clip, or map with a dictionary. A good habit is to write the vectorized version first, then verify against a slow reference on a small slice. That also builds trust in your code.

• str, dt, where, clip, map
• apply is the last resort

Memory tips matter on big files. Check usage with memory_usage and deep equals true. Text columns with few distinct values, like city, shrink greatly when stored as the category dtype, because each unique string is stored once with small integer codes. Smaller numeric types, such as int32 or float32, also help, within the precision you need.

• category for repeated strings
• Downcast numeric types

Also load only what you need. Use usecols to skip columns, and read big files in chunks when they do not fit. Drop temporary columns when finished. These habits keep your laptop responsive and your pipeline cheaper to run, long before you need any special tools.

• usecols to skip columns
• Read in chunks

Mistake one: chained assignment. Writing df filtered by size, then selecting price, then assigning zero, may modify a temporary copy instead of the original. Pandas may warn with SettingWithCopy, or silently do nothing. You think you changed the data, but the original table is unchanged.

• df[mask]['price'] = 0
• May edit a copy

The fix is a single loc call that takes the row mask and the column together. Assign through loc, with the mask first and the column name second. Now pandas knows exactly which cells of the original to change. When you truly want a copy, call copy explicitly, so your intent is clear.

• df.loc[mask, col] = value
• Use copy() on purpose

Mistake two: merge row explosion. Suppose the state table accidentally lists Austin twice. Each Austin house now matches two rows. Our two Austin houses become four rows, so six rows turn into eight. Duplicated keys silently multiply rows, inflating counts and sums, and corrupting later statistics.

• Duplicate keys multiply rows
• 6 rows become 8

Defend against it. Compare row counts before and after the merge, and pass the validate option, for example many to one, so pandas raises an error when the right table has duplicate keys. Also check for duplicates with the duplicated method first. A one-line assertion here can save hours of confusion.

• validate many_to_one
• Assert row counts

Mistake three: index misalignment. Arithmetic between Series matches labels, not positions. Let the first Series have labels a, b, c with values 1, 2, 3, and the second have labels b, c, d with values 10, 20, 30. The sum gives missing for a, 12 for b, 23 for c, and missing for d.

• Labels match, not positions
• Unmatched labels give NaN

This bites after filtering or shuffling, when an index no longer runs zero to n. If you assign a Series built from another table, values land by label, which may not be the row order you expected. When you want positional behavior, use to_numpy on one side, or reset_index with drop equals true. Always check a few rows by eye.

• Filtering changes the index
• reset_index(drop=True)

Now back to machine learning. A model wants a design matrix X with one row per example and one column per feature, and a target vector y. Select size and bedrooms, then call to_numpy. The result is a NumPy array of shape six by two, exactly the kind of array we studied in lesson 31.

• X shape (6, 2)
• y shape (6,)

For linear regression from lesson 14, we usually add a column of ones for the intercept. Stack it beside X to get shape six by three. Then the normal equation, or gradient descent, works on plain arrays. Check shapes carefully: X has six rows, y has six entries, and the weight vector has three.

• Column of ones for intercept
• Check shapes: (6,3) times (3,)

Keep a few rules. Select feature columns explicitly by name, so extra columns like id never sneak in. Make sure all chosen columns are numeric, and remember that text columns like city need encoding, which we handle later. Save the column order, because a model trained on one order breaks on another.

• Select columns by name
• Numeric only, fixed order

Recap. A Series is values plus labels, and a DataFrame is a table of them. Use loc for labels and iloc for positions, filter with masks, and prefer vectorized operations over apply. Use groupby to summarize, merge to join, concat to stack, and pivot tables to reshape.

• Select, filter, group
• Merge, stack, pivot

Remember the three traps: assign through a single loc to avoid chained assignment, validate merges to stop row explosion, and watch indexes to avoid misalignment. Finish by turning columns into a NumPy design matrix. Next time, in lesson 33, we plot our data and explore it visually.

• Single loc, validate, check index
• Next: plotting and EDA