Skip to content

Pandas, Essential Tools

Essential Pandas Tools

The previous sections covered the core pandas workflow: creating and inspecting DataFrames, loading data, filtering, merging, and grouping. Before moving on, it's worth rounding out your toolkit with a handful of everyday tools that come up in almost every real analysis, but haven't been introduced yet.

Handling Missing Data Properly

Earlier, we used np.isnan() inside a loop to check for missing ages. In practice, pandas provides much more convenient, vectorised tools for this exact problem.

# .isna() (or the identical .isnull()) flags every missing value as True
print(data['Age'].isna().sum())     # counts how many ages are missing in total

# .notna() is simply the opposite: flags every NON-missing value as True
print(data['Age'].notna().sum())

# .dropna() removes rows containing missing values
# By default, this checks ALL columns; use subset= to check only specific ones
data_complete_age = data.dropna(subset=['Age'])
print(data_complete_age.shape)

# .fillna() replaces missing values with something more useful than leaving them blank
# A common approach is filling with the column's own mean or median
data['Age'] = data['Age'].fillna(data['Age'].median())
print(data['Age'].isna().sum())     # should now be 0

Why this matters: missing data is the rule, not the exception, in real datasets. Deciding whether to drop, fill, or flag missing values (as we did earlier with the "European" nationality trick) is one of the most consequential judgement calls in any analysis, since it can meaningfully change your results.

.loc vs .iloc

Earlier, we used .loc to select rows and columns by their labels. Pandas also provides .iloc, which selects by integer position instead, this distinction trips up almost every beginner at least once.

# .loc selects by LABEL (the row/column name)
print(data.loc[3, 'Age'])           # the value in the row labelled 3, column 'Age'

# .iloc selects by POSITION (like a list index), regardless of the actual labels
print(data.iloc[3, 4])               # the value in the 4th row, 5th column (0-indexed)

# The difference becomes obvious once row labels no longer match their position,
# for example after filtering or sorting
filtered = data[data['Pclass'] == 1]
print(filtered.loc[1])               # looks for the row LABELLED 1 (may or may not exist after filtering)
print(filtered.iloc[1])              # always returns the 2nd row of whatever remains, positionally

A simple rule of thumb: use .loc when you're thinking in terms of names or labels ("give me the row for passenger 3"), and .iloc when you're thinking in terms of position ("give me the second row, whatever it happens to be").

Sorting Data

# .sort_values() sorts rows by the values in one or more columns
data_sorted = data.sort_values('Age')                       # ascending by default
data_sorted_desc = data.sort_values('Age', ascending=False)  # descending

# Sorting by multiple columns: sorts by the first, then breaks ties using the second
data_sorted_multi = data.sort_values(['Pclass', 'Age'])

# .sort_index() sorts by the row labels instead of by values
data_sorted_index = data.sort_values('Age').sort_index()     # restores the original row order

Sorting is especially useful before inspecting extremes, for example, quickly viewing the oldest or youngest passengers with data.sort_values('Age', ascending=False).head(5).

Renaming Columns and Managing the Index

# .rename() lets you relabel specific columns without touching the others
data = data.rename(columns={'Pclass': 'PassengerClass'})

# .set_index() promotes a column to become the DataFrame's row index
data_indexed = data.set_index('PassengerId')
print(data_indexed.head())

# .reset_index() reverses this, turning the index back into an ordinary column
# and replacing it with a fresh default integer index
data_reset = data_indexed.reset_index()

Setting a meaningful column (like an ID) as the index can make later .loc lookups more natural, e.g. data_indexed.loc[123] to jump straight to passenger 123, rather than filtering for it.

Concatenating vs Merging

Section E.5 introduced .merge(), which combines datasets side by side using a shared key column. pd.concat() solves a different, equally common problem: stacking datasets on top of each other, for example, combining several months of data that share the same columns.

# Imagine two DataFrames with identical columns, e.g. January and February sales
jan_sales = pd.DataFrame({'day': [1, 2, 3], 'sales': [100, 150, 120]})
feb_sales = pd.DataFrame({'day': [1, 2, 3], 'sales': [130, 140, 160]})

# pd.concat() stacks them vertically by default, adding rows
all_sales = pd.concat([jan_sales, feb_sales], ignore_index=True)
print(all_sales)

The distinction that matters: use .merge() when you're combining different information about the same observations (like Titanic passenger data and their nationalities), and use pd.concat() when you're combining the same kind of information from different sources or time periods (like sales data from different months).

Working with Text Columns Using .str

Pandas provides a whole family of string methods, accessed via .str, that apply a text operation to every value in a column at once, similar in spirit to the string methods covered earlier in this course.

# Applying string methods across an entire column
names = pd.Series(["Braund, Mr. Owen Harris", "Cumings, Mrs. John Bradley"])

print(names.str.upper())                   # uppercase every entry
print(names.str.contains("Mrs"))            # Boolean: does each entry contain "Mrs"?
print(names.str.split(",").str[0])          # split on comma, keep the first part (the surname)

This is especially useful for cleaning messy real-world text data, extracting titles from names, standardising capitalisation, or flagging rows that contain a particular keyword.

A Brief Introduction to Dates

Financial and time-series data almost always involves dates, and pandas has dedicated support for working with them properly, rather than treating them as plain text.

# pd.to_datetime() converts a text column into a proper datetime type
sales_dates = pd.Series(["2024-01-01", "2024-01-02", "2024-01-03"])
sales_dates = pd.to_datetime(sales_dates)

# Once converted, you can extract useful components directly
print(sales_dates.dt.year)
print(sales_dates.dt.month)
print(sales_dates.dt.day_name())    # e.g. "Monday", "Tuesday", ...

Working with a proper datetime type (rather than plain strings) is what unlocks pandas' more advanced time-series tools later on, such as resampling data by week or month.

Finding and Removing Duplicate Rows

# .duplicated() flags rows that are exact repeats of an earlier row
print(data.duplicated().sum())        # counts how many duplicate rows exist

# .drop_duplicates() removes them, keeping the first occurrence by default
data_unique = data.drop_duplicates()

Duplicate rows are a surprisingly common data quality issue, for example, from a file being accidentally exported twice, and checking for them is a quick, worthwhile habit early in any analysis.

Saving Your Work Back Out

After all this cleaning, filtering, and transforming, you'll usually want to save the result:

# .to_csv() writes a DataFrame back out to a CSV file
# index=False avoids writing pandas' own row index as an extra column in the file
data.to_csv('cleaned_titanic_data.csv', index=False)

With this rounding out the core pandas toolkit, missing data, precise indexing, sorting, combining datasets in two different ways, text and date handling, duplicates, and exporting, you now have a genuinely practical foundation for working with real, messy, tabular datasets in Python.