Overview
Empty cells, wrong dtypes, and vectorized string fixes.
On this page7 sections
The table we keep using
A null in order_total means the value is unknown. A null in status might mean the record is incomplete.
Real-world data is messy. Columns that should contain numbers sometimes contain text. Rows have missing values. Text fields have inconsistent capitalization or extra whitespace. Cleaning means fixing these problems so your data is reliable for analysis.
Pandas provides tools for each cleaning task. dropna() removes rows with missing values. fillna() replaces missing values with a specified default. astype() converts a column from one data type to another. The .str accessor provides vectorized string operations like .str.lower() and .str.strip() that work on entire columns at once.
The word 'vectorized' means the operation applies to every value in the column simultaneously, without you writing a loop. Instead of processing one cell at a time, pandas handles the entire column in one instruction. This is both faster and easier to read.
What we need from it
Suppose your company exports order data from a payment system. Some orders have no total because the payment failed. Some status values are 'Paid', others are 'paid' or ' PAID '. If you count paid orders without cleaning, you will undercount because 'Paid' and 'paid' look different to a computer. If you compute an average total without handling missing values, you might get an error or a misleading result.
Every data pipeline starts with cleaning. If you skip this step, every calculation downstream inherits the errors. Dashboards show wrong numbers. Reports go to executives with inflated or deflated metrics. Cleaning is not optional.
Trace it step by step
Start by profiling: count missing values per column, check data types, and look at a few suspicious rows. This tells you what needs fixing before you write any cleaning code.
Same table, next cut
Run the example below in this tab. Read the input, follow the code, then check the output matches what you expect.
profile = (
df_orders_raw.isna()
.sum()
.rename("null_rows")
.to_frame()
.assign(dtype=df_orders_raw.dtypes.astype(str))
)
result = profiledropna: removing rows with missing values
dropna() removes rows that contain any missing value. That is often too aggressive because it throws away useful data just because one optional field is empty. Use the subset parameter to drop rows only when a specific required column is missing.
Always record row counts before and after dropping. If you drop 40% of your data, something might be wrong with the source, not just a few bad rows.
before = len(df_orders)
clean = df_orders.dropna(subset=["order_total"]).copy()
print({"before": before, "after": len(clean), "dropped": before - len(clean)})
result = clean.head(10)Pandas way vs SQL way: dropna is like WHERE col IS NOT NULL. fillna is like COALESCE(col, default).
| Situation | Recommended action | Risk |
|---|---|---|
| Required total is missing | dropna(subset=['order_total']) | Check how many rows you lose |
| Optional discount is missing | fillna(0) if contract says missing means zero | Confirm the source definition |
| Status is missing | fillna('unknown') | Do not merge 'unknown' with 'pending' |
| Sensor reading is missing | Leave as NaN or bounded forward fill | Never fill across devices |
SQL connection
In SQL, you handle nulls with IS NOT NULL in WHERE, COALESCE() for defaults, and CASE WHEN for conditional replacement. In pandas, the equivalents are dropna(), fillna(), and np.where() or Series.where().
fillna: replacing missing values
fillna(0) replaces every NaN with zero. This is correct only when 'missing' truly means 'none' or 'zero'. For example, a missing discount on an order might genuinely mean no discount was applied. But a missing sensor temperature does not mean the temperature was zero.
For text columns, fillna('unknown') makes the missing status explicit. For numeric columns, consider whether filling with the mean, median, or a business-specific default makes more sense than zero.
Zero is a real measurement
Filling every numeric null with zero invents data points and distorts averages. A missing temperature and a temperature of zero degrees are completely different things. Make the business rule explicit for each column.
astype: converting data types
Sometimes a column that should be numeric is stored as text (dtype 'object'). astype('float64') converts the values, but it raises an error if any value cannot be parsed (like the text 'N/A'). Use pd.to_numeric with errors='coerce' first to turn unparseable values into NaN, then clean or drop them.
clean = df_orders_raw.copy()
clean["order_total"] = pd.to_numeric(clean["order_total"], errors="coerce")
bad_totals = clean["order_total"].isna().sum()
clean = clean.dropna(subset=["order_total"]).copy()
clean["order_total"] = clean["order_total"].astype("float64")
print("unparseable or missing totals:", bad_totals)
result = clean.head(10)Vectorized string operations with .str
The .str accessor lets you apply string operations to an entire column at once. .str.strip() removes leading and trailing whitespace. .str.lower() converts to lowercase. .str.contains() checks for a substring. These are vectorized: they process the whole column without a Python loop.
Prefer clean['order_status'].str.strip().str.lower() over apply(lambda x: x.strip().lower()). The .str version is clearer about how it handles NaN values (it leaves them as NaN) and typically runs faster.
Profile first, then normalize text, parse types, validate counts, and publish.
clean = df_orders.copy()
clean["order_status"] = clean["order_status"].str.strip().str.lower()
print(clean["order_status"].value_counts())
result = clean.head(10)Avoid row-by-row processing
apply(axis=1) creates a Python object for each row and processes them one at a time. This is slow on large DataFrames. Most cleaning operations can be expressed with .str methods, arithmetic, fillna, or np.select, all of which work on entire columns.
Use apply only when no columnar operation can express your rule. Even then, benchmark it and document why it is necessary.
Most SQL cleaning functions have direct pandas equivalents.
| Cleaning task | Pandas way | SQL equivalent |
|---|---|---|
| Remove rows with nulls | df.dropna(subset=['col']) | WHERE col IS NOT NULL |
| Replace nulls | df['col'].fillna(default) | COALESCE(col, default) |
| Convert types | pd.to_numeric(df['col'], errors='coerce') | CAST(col AS NUMERIC) |
| Lowercase text | df['col'].str.lower() | LOWER(col) |
| Trim whitespace | df['col'].str.strip() | TRIM(col) |
Copy before editing a filtered frame
After dropna or filtering, call .copy() before modifying values. Without .copy(), you might be editing a view of the original data, which triggers SettingWithCopyWarning and can cause subtle bugs.
Copy-paste without reading the output
Run Sample first. If the numbers or row count look wrong, stop and re-read the previous section before changing code.
Common beginner questions
What is the difference between NaN and None?
In pandas, both represent missing data. NaN (Not a Number) is the standard missing-value marker for numeric columns. None is Python's built-in null. Pandas converts None to NaN in numeric columns. For practical purposes, treat them the same and use isna() to detect either.
Should I drop rows or fill missing values?
It depends on the column. If a required field like order_id is missing, drop the row. If an optional field like discount is missing and the business rule says 'no discount means zero,' fill with 0. There is no universal answer.
Why does astype fail on some columns?
astype('float') fails if any value in the column cannot be converted (like the string 'N/A'). Use pd.to_numeric(errors='coerce') first to turn bad values into NaN, handle those NaNs, then convert.
What comes next
With clean data in hand, the next lesson teaches filtering: selecting specific rows based on conditions. You will learn to build boolean masks, combine multiple conditions, and use isin for membership checks.
Practice
Start from df_orders. Remove rows missing order_total, cast that column to float, and lowercase order_status with the .str accessor. Assign the full cleaned DataFrame to result.
The check expects the transformation itself, not just a printed preview. Use vectorized operations, not a Python loop.
Practicals · load into the editor
After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.
Practice this
Same ideas as interview drills. These challenges open in the studio with a dataset and tests already set up.