Skip to content

DataFrame Processing

The final part introduces some more advanced DataFrame workflows. These include combining datasets, selecting data conditionally, applying custom transformations, and carrying out grouped calculations.

These techniques are widely used in empirical finance, economics, and data science, since real datasets are rarely self-contained, they usually need to be merged, cleaned, and transformed before any serious analysis can begin.

Python Code Examples: Advanced DataFrame Processing

Before combining two datasets, it's good practice to sanity-check that they're actually compatible, for example, confirming they cover the same set of observations:

# Confirm that both datasets contain the same number of rows (i.e. the same passengers)
assert data_org.shape[0] == data_nat.shape[0]

An assert statement like this doesn't print anything if the condition is true, it simply does nothing and lets your code continue. If the condition were false, it would immediately raise an AssertionError, stopping the code before you go any further with mismatched data. This is a small but valuable habit: catching a data problem immediately, rather than discovering it several steps later in a confusing result.

# Inspect both datasets to look for a shared column we can merge on
data_org.head(5)

data_nat.head(5)

Performing the merge

# Both datasets share a common column, PassengerId, so we use it to combine them
# .merge() works similarly to a SQL "join": it lines up rows from both DataFrames
# wherever the PassengerId values match
data_new = data_org.merge(data_nat, on='PassengerId')

# Inspect the newly combined dataset
data_new.head(5)

What just happened: data_new now contains all the original Titanic columns plus the nationality information from data_nat, joined row by row using PassengerId as the shared key. This is an extremely common real-world pattern, useful whenever you have related information (customer details and their orders, stock prices and company fundamentals, and so on) spread across separate files that need to be brought together.

Grouped Calculations with .groupby()

Often, rather than summarising an entire dataset at once, you'll want a summary broken down by category, for example, the average age within each passenger class.

# One (very inefficient) way to do this manually would be to loop through every row,
# sort values into groups by hand, and calculate the average for each group yourself.
# Pandas lets us do the same thing in a single, much cleaner line.

# First, we group the data by 'Pclass'; then we ask pandas to compute the mean of 'Age'
# within each group
print(data_new.groupby(['Pclass'])['Age'].mean())

# We can group by more than one column at once
# Here, we separate the calculation further by both class AND sex
print(data_new.groupby(['Pclass', 'Sex'])['Age'].mean())

How to read .groupby(): think of it as three steps happening in sequence, split the data into groups based on the column(s) you specify, apply a calculation (like .mean()) separately within each group, then combine the results back into a single summary. This "split-apply-combine" pattern is one of the most powerful and frequently used ideas in all of pandas, and it extends naturally to other aggregations too:

# The same groupby pattern works with other summary statistics, not just .mean()
print(data_new.groupby(['Pclass'])['Fare'].median())     # median fare per class
print(data_new.groupby(['Pclass'])['Age'].count())         # number of known ages per class
print(data_new.groupby(['Pclass'])['Age'].agg(['mean', 'std', 'min', 'max']))  # several statistics at once

Preparing Data for Statistical Processing

# Drop several text-based (string) columns, since some of the following operations
# only make sense for numeric data
data = data_new.drop(['Cabin', 'Name', 'Ticket', 'Embarked', 'Nationality', 'Sex'], axis=1)

Clipping values to a fixed range

# .clip() restricts values to a chosen interval
# Any value below 'lower' is raised to 'lower'; any value above 'upper' is lowered to 'upper'
# Values already within the range are left untouched
data.clip(lower=0, upper=40).head(5)

.clip() is a common tool for handling outliers or enforcing sensible bounds, for example, capping an unrealistically high recorded age, or ensuring a probability column never technically exceeds 1 due to rounding errors elsewhere in a calculation.

Applying Custom Functions with .apply()

Not every transformation you might want has a ready-made pandas method. For anything more custom, .apply() lets you run any function of your choosing against each value in a column.

# .apply() runs the given function against every value in the column
# Here, we use a "lambda" function: a small, unnamed function defined inline,
# useful for simple, one-off transformations like this
data["SurvivedPlusOne"] = data["Survived"].apply(lambda x: x + 1)
data.head(5)

The lambda function lambda x: x + 1 is exactly equivalent to writing a small named function first:

# The lambda above is shorthand for this more explicit, named version
def add_one(x):
    return x + 1

data["SurvivedPlusOne"] = data["Survived"].apply(add_one)

When to reach for .apply(): it's a flexible, general-purpose tool, but it's worth knowing it's usually slower than pandas' own built-in vectorised methods (like .clip(), .map(), or basic arithmetic on a whole column at once). As a rule of thumb, use a built-in method when one already does what you need, and reach for .apply() (or a lambda) mainly when the transformation is genuinely custom and no ready-made method covers it.

# A slightly more realistic example: creating a new categorical column based on Age
data["AgeGroup"] = data["Age"].apply(
    lambda age: "Child" if age < 18 else "Adult" if age < 65 else "Senior"
)
data[["Age", "AgeGroup"]].head(10)

Having worked through creating DataFrames, loading real data, indexing and filtering, and now merging, grouping, and applying custom transformations, you now have the core pandas toolkit needed to prepare almost any real-world tabular dataset for further statistical or financial analysis.