Handbooks / Pandas & Data Analysis / Chapter 2

Cleaning Data

7 pages · ~4 min✓ Reviewed

Builds on DataFrames and Indexing. Next up: GroupBy and Aggregation.

Pandas & Data AnalysisPage 1

Data Cleaning Mental Model

1. What Cleaning Means

  • Every column has the right dtype
  • No hidden NaN, no duplicate rows
  • Consistent labels and text, done before any analysis

2. How It Works

  • Most cleaning methods return a new object
  • The original stays untouched, so assign the result
python
df.drop_duplicates()         # lost
df = df.drop_duplicates()    # kept

no assignment, no cleaning

  1. Inspectinfo, describe, isna().sum()
  2. Fix dtypes
  3. Handle missing
  4. Clean text, names
  5. Drop duplicates
  • df.info() shows dtype and non-null count per column
  • A text dtype (str in pandas 3, object before) on a numeric-looking column = dirty data

3. Vectorized vs Row-by-Row

StyleRuns inSpeed
df['a'] * 2, s.str.lower()compiled code, whole columnfast
df.apply(f, axis=1)Python, once per rowslowest
Remember

Row-by-row Python is the slow exception. Reach for a vectorized operation first.

Pandas & Data AnalysisPage 2

Missing Values: isna, dropna, fillna

1. Detecting Missing

  • NaN, None and NaT all count as missing
  • isna() gives a boolean mask; notna() is its inverse
  • df.isna().sum() counts missing per column
  • df.isna().mean() gives the fraction missing

2. dropna()

ArgumentEffect
axis=0drops rows
how='any'default: one NaN drops the row
how='all'drops only all-NaN rows
subset=[cols]checks only those columns
thresh=nkeeps rows with n or more non-null

3. fillna()

  • fillna(value) takes a scalar or a per-column dict
  • Standard numeric fill is the median
  • ffill() / bfill() fill from neighbours
python
df['a'] = df['a'].fillna(df['a'].median())
df = df.fillna({'a': 0, 'b': 'n/a'})
df['a'] = df['a'].ffill()

4. Mistakes

  • df[df.a == np.nan] matches nothing: NaN == NaN is False, so use isna()
  • df['a'].fillna(x, inplace=True) never changes df under pandas 3 Copy-on-Write
Assign back

Write df['a'] = df['a'].fillna(x). The result is a new object.

Pandas & Data AnalysisPage 3

Dtypes, Conversion and Duplicates

1. Reading dtypes

  • df.dtypes lists the type of every column
  • Mixed values in a column → object
  • Int column with NaN → turns float

2. Converting types

CallOn bad value
astype('int64')raises
astype('datetime64[ns]')raises
astype('category')never raises, every value a category
astype('Int64')nullable int, allows NaN (capital I)
to_numeric / to_datetimedefault errors='raise'
same, errors='coerce'NaN / NaT
  • Use coerce to turn unparseable values into NaN/NaT, then inspect them
python
df['age'] = pd.to_numeric(df['age'], errors='coerce')
df['joined'] = pd.to_datetime(df['joined'], errors='coerce')

assign the result back

3. Duplicates

  • duplicated() marks repeat rows; keep='first' by default, so the first copy is False
  • drop_duplicates(subset=[cols], keep='first'|'last'|False) removes them
  • keep=False drops every copy
python
df.duplicated().sum()
df = df.drop_duplicates(subset=['email'], keep='last')

count first, then drop

Near-duplicates survive

With no subset, all columns are compared, so 'Ann' and ' ann ' stay. Clean text first, then dedupe.

Pandas & Data AnalysisPage 4

String Methods and Renaming

1. The .str Accessor

  • .str applies string methods to a whole Series
  • strip() lower() upper() title() len()
  • replace('old','new', regex=False) = literal; regex=True = patterns
  • contains('x', na=False) = boolean mask
  • split(',', expand=True) = separate columns
python
s.str.strip().str.title()
s.str.replace(r'\s+', ' ', regex=True)
df[s.str.contains('x', na=False)]
s.str.split(',', expand=True)

2. Renaming

FormRenamesResult
rename(columns={'old':'new'})chosen columnscopy
df.columns = [...]all; count must matchin place
rename(columns=str.lower)every namecopy

3. Mistakes

  • .str on a non-string column raises AttributeError
  • Missing values return NaN, no error
Fix

Convert first: astype('string')

4. Interview Point

  • Does rename change df? No, it returns a copy; assign it back.
  • Why does .str fail? The dtype is not string; use astype('string').
Pandas & Data AnalysisPage 5

Apply vs Vectorized Operations

1. Speed Ladder

PatternPython callsSpeed
df['a'] * 2, s.str.lower(), np.wherenone, compiled column-widefastest
map(dict)fast lookupfast
Series.apply(func)once per elementslow
df.apply(func, axis=1)once per rowslowest

2. Choosing and Replacing

  • apply only when no vectorized form exists, e.g. multi-step custom logic
  • map(dict) recodes values by lookup
  • Replace row-wise if/else with np.where or a boolean mask
python
df['tier'] = np.where(df.score >= 50, 'pass', 'fail')
df['city'] = df['code'].map({'H': 'Hyd', 'B': 'Blr'})

vectorized if/else and dict recode

3. Mistakes

Slow apply

df.apply(f, axis=1) on a large frame is often 10 to 100 times slower than the vectorized form.

  • Default axis=0 passes whole columns, not rows
  • Why is axis=1 slowest? One Python call per row
  • What replaces row-wise if/else? np.where or a mask
Pandas & Data AnalysisPage 6

Pandas Cleaning Cheat Sheet

1. Missing and Types

  1. isna().sum()count
  2. dropna(subset=, thresh=)drop
  3. fillna(value | median)fill
  4. ffill()neighbour
  • info() / dtypes: a text dtype on a numeric-looking column means dirty data
CallBad value
Strictastype('Int64'|'int64')raises
Lenientto_numeric / to_datetime(errors='coerce')NaN / NaT

2. Duplicates and Text

  • duplicated().sum() counts repeat rows
  • drop_duplicates(subset=[...], keep='first'|'last'|False); False drops every copy
  • .str.strip().str.lower() first, then dedupe
  • .str.replace(..., regex=False) is a literal replace
  • rename(columns={...}) or columns=str.lower

3. Speed Order

OrderToolUse for
1Vectorized ops, np.wherewhole columns
2map(dict)lookups
3apply(axis=1)last resort
python
df['tier'] = np.where(df.score >= 50, 'pass', 'fail')

row-wise if/else, vectorized

Assign it back

Cleaning methods return a new object: df['a'] = df['a'].fillna(x), never inplace=True on a slice.

Pandas & Data AnalysisPage 7

Check yourself

df['age'].fillna(0, inplace=True) runs without error in pandas 3. Does df change?
  • No: Copy-on-Write makes df['age'] a temporary copy.
  • Assign back: df['age'] = df['age'].fillna(0).
What does this return?
  • 1.0, NaN, 3.0, dtype float64.
  • 'x' becomes NaN instead of raising; NaN forces float.
s = pd.Series(['1', 'x', '3'])
pd.to_numeric(s, errors='coerce')
Rows 'Ann' and 'ann ' (trailing space): does drop_duplicates() merge them?
  • No: it compares exact values, so both survive.
  • Run .str.strip().str.lower() first, then dedupe.