Pandas Masking, map, apply, and groupby

This lesson moved further into Pandas. The focus was on filtering rows by conditions, creating new values, and aggregating data by groups. The previous lesson covered the basic tools such as DataFrame, Series, loc, iloc, and merge. This time, those tools showed up in more practical preprocessing and analysis examples.

The confusing part was that Pandas often provides several ways to produce the same result. Masking can be written with square brackets, loc, or isin. A percentage column can be created with map, astype, vectorized arithmetic, or apply. So I tried to organize the lesson around how each method works, instead of memorizing every syntax variation.

Filtering rows with masks

In Pandas, masking means creating a True or False condition and selecting the rows where the condition is True. The example started with a small DataFrame.

import pandas as pd

table2 = {
    "date": ["2021-12-06", "2021-12-07", "2021-12-08", "2021-12-09"],
    "price": [1000, 3000, 2000, 1000],
    "purchased": [False, True, True, True],
    "product": ["gum", "snack", "beverage", "gum"],
}

df2 = pd.DataFrame(table2)
df2

To select rows where the product is neither gum nor snack, I can write two conditions and connect them with &.

df2[(df2["product"] != "gum") & (df2["product"] != "snack")]

The same condition can be written with isin.

df2[~df2["product"].isin(["gum", "snack"])]

isin checks whether each value exists in the given list. By itself, it returns True for rows where the product is gum or snack. Adding ~ flips the boolean result, so only rows where the product is neither gum nor snack remain.

mask = df2["product"].isin(["gum", "snack"])

print(mask)
print(~mask)

The key is that isin returns True when the value matches any item in the list. Flipping that result with ~ leaves rows that match none of the listed values.

Assigning values with loc and a condition

Masking is not only for selecting rows. It can also be used when assigning values to a specific part of a DataFrame.

condition1 = (df2["product"] == "gum") & (df2["purchased"] == 0)

df2.loc[condition1, "target"] = True
df2.loc[~condition1, "target"] = False

df2

This code writes True into the target column for rows where condition1 is True, and False for the remaining rows. If the target column does not exist yet, Pandas creates it during the assignment.

The part I had to pin down was the structure inside loc. loc is not a normal function call. It is an indexer, and the shape to remember is df.loc[row selector, column selector].

df.loc[row_selector, column_selector]

The first position selects rows, and the second position selects columns. If a boolean Series is passed in the first position, Pandas selects the rows where the value is True. If a column name is passed in the second position, Pandas selects that column.

# All rows that satisfy the condition
df2.loc[condition1]

# One column from rows that satisfy the condition
df2.loc[condition1, "target"]

Python does not have Java- or C++-style function overloading. loc accepts many shapes because one indexer interprets the type of the value it receives. A string is treated as a label, a list as multiple labels, and a boolean Series as a row-selection mask.

So instead of memorizing a function signature, I need to keep the df.loc[rows, columns] frame in mind.

Creating columns with map, vectorized operations, and apply

There were several ways to calculate a percentage column.

df_tips["percentage"] = df_tips["percentage"].map(lambda x: f"{x:.2f}%")

map applies a function to each element of a Series. This example takes each already-calculated percentage value, formats it to two decimal places, and appends a percent sign.

The same result can also be built with vectorized column operations.

df_tips["percentage"] = (
    round((df_tips["tip"] / df_tips["total_bill"]) * 100, 2)
    .astype(str)
    + "%"
)

This calculates the entire tip column and total_bill column at once. In Pandas and NumPy-style code, vectorized operations are usually the first option to consider because the code moves at the column level, and the internal work is handled at the array level.

When a calculation needs to read multiple columns from the same row, apply can be used with axis=1.

df_tips["percentage"] = df_tips.apply(
    lambda x: f"{x.tip / x.total_bill * 100:.2f}%",
    axis=1,
)

With axis=1, apply passes each row into the function. That allows the function to read multiple values from the same row, such as x.tip and x.total_bill.

df_tips["percentage"] = df_tips.apply(
    lambda x: str(round(x.tip / x.total_bill * 100, 2)) + "%",
    axis=1,
)

This also produces a similar result, but the f-string version makes the decimal format explicit.

The distinction I want to keep is this:

Method	Unit of work	Use case
map	One element in a Series	Mapping or formatting one column
Vectorized operation	Whole columns	Calculating between columns
apply with axis=1	One row at a time	Reading several columns from the same row

The result can look the same, but the unit of work is different. Once that is clear, the different Pandas options become easier to choose between.

groupby and agg

groupby splits data by a column and applies the same aggregation to each group. For example, to see the average price by product, I can group by product and calculate the mean of price.

df2.groupby("product")["price"].mean()

agg is useful when several aggregations should be calculated at once.

df2.groupby("product")["price"].agg(["count", "sum", "mean"])

Different aggregations can also be applied to different columns.

df2.groupby("product").agg({
    "price": ["count", "sum", "mean"],
    "purchased": "sum",
})

Here, purchased contains True and False values. In Python, True can be treated like 1 and False like 0 in numeric operations, so sum can be used to count the number of True rows.

apply can also be used after groupby. In that case, each group is passed to a custom function as a DataFrame slice.

def price_range(group):
    return group["price"].max() - group["price"].min()


df2.groupby("product").apply(price_range)

For simple counts, sums, and averages, agg is easier to read. When the logic inside each group needs several steps, apply can be used.

describe and pivot_table

describe is useful for quickly checking basic statistics of numeric columns.

df2.describe()

It shows count, mean, standard deviation, minimum, quartiles, and maximum. At the beginning of EDA, it gives a quick view of the value ranges and whether unusually large or small values exist.

pivot_table defines row keys, value columns, and an aggregation function, then reshapes the result into a table.

pd.pivot_table(
    df2,
    index="product",
    values="price",
    aggfunc="mean",
)

It overlaps with groupby in that both summarize data by groups, but pivot_table focuses on arranging the result into a table shape. It is the Pandas version of building a pivot table in a spreadsheet.

Seaborn and pairplot

The lesson also touched on Seaborn. If Matplotlib is the basic plotting tool, Seaborn provides shorter ways to draw statistical visualizations.

pairplot draws pairwise plots for numeric variables in a dataset.

import seaborn as sns

sns.pairplot(df_tips)

As the number of variables grows, it becomes hard to read every relationship from tables. pairplot is useful for scanning relationships between variables, although the number of plots can grow quickly. For EDA, it is better to decide the question first and then choose the graph that answers it.

Hotel booking EDA assignment

At the end of the lesson, we got an EDA assignment using a Kaggle hotel booking dataset. The goal is to analyze hotel reservation records and find clues that could help reduce the cancellation rate.

I am still working on the assignment. For now, I am checking missing values, splitting the data by cancellation status, and looking at how variables such as lead time, room type, customer type, and booking channel relate to the cancellation rate. I am using averages and standard deviations first. Tomorrow, I plan to continue the EDA with correlation analysis and visualizations.