Inspect rows, columns, and the size of a Pandas DataFrame.

A DataFrame stores data in rows and columns. head() returns the first five rows unless you give it a different number.

shape returns a pair: the number of rows, then the number of columns. The index labels are not counted as a data column.

Meet a DataFrame

Choose columns and distinguish a Series from a DataFrame.

Pass a list of column names to select several columns. The result keeps them in the order you specify.

searches["query"] returns a Series. searches[["query"]] returns a one-column DataFrame. Use the output type requested by the exercise.

Select columns

Select by index label or row position without confusing the two.

loc selects index labels. The workspace provides indexed_searches, created with searches.set_index("search_id"). Search IDs become index labels instead of a data column.

iloc counts positions from zero. Its slice excludes the stop position. On this sorted index, loc[10:20] includes both endpoint labels, while iloc[0:2] selects positions 0 and 1.

Select rows with loc and iloc

Inspect column types and numeric summaries, then rename columns without changing their values.

`searches.dtypes` lists each column's data type. `searches.info()` prints column types and non-missing counts; it returns None, so use it for inspection rather than saving it as your answer.

`describe()` returns count, mean, sample standard deviation, minimum, quartiles, and maximum for numeric columns. Missing values are excluded from these statistics.

Pass a dictionary of old and new names to `rename(columns=...)`. Assign the returned DataFrame to keep the renamed result without changing the source table.

Inspect and name your data

Use Boolean conditions to keep matching rows and select the columns to return.

Comparing a column with a value creates a Boolean Series: one True or False for each row. Pass that condition to `.loc[condition, columns]` to keep matching rows and the requested columns.

`>` and `<` exclude the boundary; `>=` and `<=` include it. Use `==` to test equality and `!=` to test inequality. A single `=` assigns a value instead of comparing it.

Filter rows

Combine row-level conditions with AND, OR, and NOT using Pandas Boolean operators.

Use `&` when both conditions must be true. Put each comparison in parentheses; Python's `and` does not combine two Pandas Boolean Series.

Use `|` when either condition can match. Group an OR expression before combining it with another condition, so the extra condition applies to both alternatives.

Use `~` to reverse a Boolean condition. Parentheses make clear which condition is being reversed; `~` is not Python's scalar `not` operator.

Combine conditions

Match several values with isin and control numeric range boundaries with between.

`.isin([...])` checks whether each value belongs to a list. Use `~` before the condition to exclude those values.

`.between(low, high, inclusive="both")` includes both endpoints. Use `"neither"` to exclude both, `"left"` to include only the lower endpoint, or `"right"` to include only the upper endpoint.

Match lists and ranges

Sort ascending or descending, resolve ties, and take a reproducible top-N result.

`sort_values()` sorts ascending by default. Set `ascending=False` to put larger values first, then call `head(n)` to keep the first n rows.

When values tie, add another sort column. The `ascending` list follows the order of those columns; a unique search ID makes the final order predictable.

Sort and take the top rows

Identify missing values, keep known observations, and choose when filling or dropping is appropriate.

Use `.isna()` to identify missing values and `.notna()` to identify present values. Do not compare with `None` or NaN using `==`; zero and an empty string are not missing values.

`dropna(subset=[...])` removes rows missing values in the specified columns. Without `subset`, an unrelated missing value can remove a row you intended to keep.

`fillna()` replaces missing values; a column-name dictionary limits which columns change. A display label can use "Unknown", but never fill anonymous user IDs with a shared ID or turn an unknown measurement into zero without justification.

Handle missing values

Parse numeric text, identify failed conversions, and preserve missing values with nullable types.

`pd.to_numeric()` parses numeric text. With `errors="coerce"`, text that cannot be parsed becomes missing instead of stopping execution; a missing value remains missing.

Use `.astype("Int64")` for nullable integers. The capital I matters: ordinary `int64` cannot hold missing values. Convert only whole-number measurements to this integer type.

A missing converted value can come from bad text or an already-missing input. Compare the original column's `.notna()` mask with the converted column's `.isna()` mask to isolate non-missing text that failed.

Convert data types

Use column-wise arithmetic to derive measurements while preserving the originals.

Pandas arithmetic operates on every row of a Series. Use `.assign(new_name=expression)` to return a DataFrame with a derived column while keeping the source columns.

`.round(n)` rounds to n decimal places; it does not format a fixed number of displayed digits. Round after the calculation. Pandas rounds exact halfway values to the nearest even value.

Calculate new columns

Set values with loc and choose between where and mask without chained assignment.

Create a copy when you want a separate working table. Use a single `.loc[condition, column] = value` assignment to update matching rows; avoid chained assignments such as `df["flag"][condition] = value`.

`where(condition, other)` keeps values where the condition is True and replaces the rest. `mask(condition, other)` does the reverse: it replaces values where the condition is True.

Apply display caps and labels to a derived column, not the raw measurement. Assign the result of `where()` or `mask()` back to a column; calling either method alone does not update the DataFrame.

Update values safely

Distinguish all rows, recorded values, distinct users, and frequency counts.

`len(df)` counts rows. A column's `.count()` counts non-missing values, including repeats. `.nunique()` counts distinct non-missing values by default. Missing user IDs do not identify distinct people.

`.value_counts(dropna=False)` includes a missing-value bucket. It returns a Series; `.rename_axis("label").reset_index(name="rows")` turns its index into a column. Sort explicitly when you need a fixed output order.

`pd.DataFrame({"metric": [value]})` creates a one-row DataFrame. Put each scalar inside a list, and add dictionary entries in the required column order.

Count rows and unique values

Reduce search rows to counts, totals, and averages for each device.

`.groupby("group", as_index=False)` keeps the grouping key as a column. `.size()` counts all rows in each group, including rows with missing measurements. Rename the resulting size column to describe the count.

Select the numeric column after `.groupby()` and apply `.sum()` or `.mean()`. These skip missing measurements. A mean uses recorded measurements as its denominator, not every row.

Grouping drops rows with missing keys by default. Use `dropna=False` to retain them. Sort the output explicitly; `na_position="last"` places missing keys last. `.sum()` returns zero for an all-missing group by default; `.mean()` returns missing.

Group and aggregate

Name several aggregations and summarize each country-device pair.

Named `.agg()` entries use `output_name=("source_column", "operation")`. Combine size, count, nunique, mean, or median in one report. Size counts all rows; count and nunique exclude missing values.

Pass a list of columns to group by each distinct combination. `dropna=False` keeps combinations with missing keys. A country-device report has one row per pair, not one row per country.

A median is the middle recorded value after sorting; with an even number of values, it averages the middle two. Distinct users are counted within each group. Do not add group-level distinct counts to get a global total: a user can appear in several groups.

Build multi-metric reports

Use the right denominator for a rate and filter after grouping.

For a complete True/False column, `.mean()` is the fraction of rows that are True: True counts as 1 and False as 0. The result is a proportion from 0 to 1, not a percentage. In this dataset, every search has a recorded result_count.

Sum the zero-result flag for the numerator and count all searches for the denominator. Divide those counts within each group. Filtering to zero-result searches before counting would remove the denominator's other searches.

First aggregate all search rows. Then filter the report by its search count. An inclusive minimum uses `>=`; exactly the minimum qualifies. Keep missing device keys with `dropna=False`.

Calculate rates and filter groups

Normalize query whitespace and capitalization without overwriting the original text.

Use `.str.strip()` to remove leading and trailing whitespace from every string in a Series. It leaves spaces inside the string unchanged. Create a new column so the original query stays available.

Chain `.str.lower()` to lowercase text. `.str.replace(r"\s+", " ", regex=True)` collapses each run of whitespace into one space: `\s` means whitespace, and `+` means one or more. Use `regex=False` when replacing literal text.

These string methods preserve missing values. A whitespace-only string becomes `""`, not missing. Avoid `.astype(str)` here: it can turn missing values into literal text such as "None" or "<NA>". Cleaning text does not remove duplicate search events.

Clean query text

Match literal text safely and extract a defined URL pattern.

`.str.contains(text, regex=False, na=False)` produces a Boolean mask. `regex=False` treats punctuation literally; `na=False` makes missing input a non-match. Add `case=False` for case-insensitive matching.

`.str.extract(pattern, expand=False)` returns a Series for one capture group. In `r"^https://([^/]+)"`, `^` anchors the start; `[^/]+` matches one or more non-slash characters; parentheses capture that text. Non-matches stay missing.

Contains selects matching rows; extract keeps the row positions and returns captured values. This small regex fits the course URLs. It is not a general URL parser for credentials, ports, or other URL formats.

Find patterns in text

Identify repeated event IDs without deleting distinct searches that share query text.

`.duplicated(subset=["id"])` marks repeated keys after their first occurrence. Use `keep=False` to flag every row in a repeated-key group. Matching query text alone does not mean two rows represent the same search.

`.drop_duplicates(subset=["id"], keep="first")` keeps the first occurrence; `keep="last"` keeps the last in current row order. Neither option checks timestamps. Without subset, Pandas compares all columns, so conflicting versions may both remain.

Recognize duplicate records

Read two CSV extracts, append their rows, and export without an index column.

Import `StringIO` from `io`, then use `pd.read_csv(StringIO(csv_text))`. StringIO makes the supplied text readable like a file. read_csv uses its first line as column names and infers types; it is not a reliable schema validator.

next_day

`pd.concat([first, second], ignore_index=True)` stacks rows in the supplied order and resets the index. Columns align by name, not position. Mismatched schemas can create extra columns and missing cells, so check them before combining.

`combined.to_csv(index=False)` returns CSV text when no path is supplied. The header is included; `index=False` omits index labels. Pandas quotes commas and quotation marks as needed. Save this returned string as `result` for the export task.

Load and combine daily files

Attach page metadata to result impressions without multiplying or dropping rows.

details

Use `.merge(details, on="page_id", how="left")` to attach lookup columns. Every left row is retained; a repeated left key receives the same lookup detail on each row. Select only the lookup columns you need.

`validate="many_to_one"` allows repeated keys on the left but requires unique keys on the right. It raises an error if the lookup would multiply rows. Do not silently discard conflicting lookup rows to hide that error.

A left merge fills unmatched details with missing values. Here, `page_id` is the lookup key, while (`search_id`, `position`) identifies a result impression. Preserve that grain: do not deduplicate result rows by page_id. Sort the result explicitly when an output order is required.

Add page details with merge

Compare inner and left merges and identify searches without clicks.

events

An inner merge keeps matching keys. When one search has several clicks, its search fields repeat once per click. `validate="one_to_many"` checks that the left search key is unique while allowing multiple right-side clicks.

A left merge keeps unmatched left rows, with missing right-side values. `indicator=True` adds `_merge`: `both` means matched, and `left_only` means unmatched. Select left_only rows to find searches without clicks.

other

When both inputs have a non-key column with the same name, `suffixes=("_search", "_click")` distinguishes the two sources. Select only needed columns when overlaps are unnecessary. Match indicators are categorical values; compare them to their text labels.

Keep matches or keep every search

Match a click to its exact result impression using search ID and position together.

results

Position 1 occurs in many searches, and each search has several positions. Match clicks to results with `on=["search_id", "position"]`. Using only one key can attach the wrong page or multiply rows.

Clicks can repeat the same result key, so clicks-to-results is many-to-one. In reverse, results-to-clicks is one-to-many. Validation applies to the complete key list. These dataset keys are non-missing; Pandas can match missing keys to each other, unlike SQL NULL joins.

First attach page_id to each click using the result key pair. Then attach domain from pages using page_id. Each lookup should preserve one row per click; keep the joins left-sided when unmatched clicks must remain.

Join on multiple keys

Count clicked searches once and retain unclicked searches in the CTR denominator.

Search CTR is searches with at least one click divided by all searches. Several clicks on one search still count as one clicked search. Deduplicate click search IDs before merging, or aggregate clicks to one row per search.

markers

Left-merge the unique clicked-search markers into all searches. Fill missing markers with 0 and keep matched markers as 1. `.fillna(0).astype("Int64")` produces integer flags; do not fill unrelated missing fields.

Within each device, count all search rows and sum clicked flags, then divide clicked_searches by searches. Keep the rate as a proportion from 0 to 1. A device with searches but no clicks has rate 0. Do not average device rates to obtain overall CTR; use the total numerator divided by the total denominator.

Measure click-through rate correctly

Move unique country-device pairs into a wide table without aggregating again.

`.pivot(index="region", columns="device", values="rows")` moves region values into the row index and device values into column labels. Each pair must occur only once. Pivot does not aggregate duplicate pairs; it raises an error instead.

`.sort_index()` sorts row labels. `.reindex(columns=[...])` sets an explicit column order and adds missing columns filled with missing values. The country index is not a data column until you call `.reset_index()`.

For counts from a complete search log, an absent country-device pair means zero searches. `.fillna(0)` is appropriate for that report, but not automatically for missing averages. After resetting the index, `.rename_axis(columns=None)` removes the column-axis name, not its labels.

Pivot a report

Build wide counts, totals, and means directly from search events.

`.pivot_table()` can combine repeated index-column pairs. Always choose `aggfunc` explicitly: `"size"` counts rows, `"sum"` adds values, and `"mean"` averages recorded values. Its default is mean, not count.

`fill_value=0` fills empty result cells after aggregation. It suits event counts and totals when no events means zero. Leave absent mean cells missing; a missing average is not a measured value of zero.

For these tasks, first drop rows missing country or device. Order device columns with `.reindex(columns=[...])` and sort the country index. With count and total reports, `fill_value=0` in reindex also supplies a device column absent from the entire input. Do not use that fill for mean reports. For means, `dropna=False` keeps all-missing result rows and columns after those input rows with missing keys have been excluded.

Aggregate while pivoting

Unpivot device columns into labeled country-device rows.

`.melt()` keeps `id_vars` as identifiers and unfolds `value_vars` into rows. `var_name` names the column holding the former labels; `value_name` names the column holding the cells. Use `.sort_values()` for the required row order.

Explicit value_vars prevents unrelated columns from becoming measurements. Melt preserves zero and missing values; it does not remove empty-looking cells or aggregate rows.

For a complete country-device report, retain zero cells. If a task asks for observed pairs only, melt first and then keep counts greater than zero. The row index created by melt is not the country-device key.

Return to a long table

Repeat a device statistic beside each search and rank timings within device groups.

`.groupby(...).transform("mean")` returns one value per input row, aligned to its original index. It repeats each group mean beside that group’s rows instead of reducing the table to one row per group. Use `dropna=False` to keep a missing-device group.

Subtract the group mean from the original response time. A positive difference means slower than that device's average; a negative difference means faster. Means ignore missing timings. A row with a missing timing has a missing difference.

Group `.rank(method="dense", ascending=False)` puts the largest timing at rank 1 within each device. Equal timings share a rank and the next distinct timing gets the next integer. Missing timings keep a missing rank; the default rank method is average, not dense.

Add group context to rows

Parse text as UTC timestamps and filter complete days with half-open boundaries.

`pd.to_datetime(..., utc=True)` converts offset-aware text to UTC. Set the known format explicitly and use `errors="coerce"` to turn invalid text into `NaT`, the missing timestamp value. UTC-naive input would be interpreted as UTC, not automatically as local time.

The `.dt` accessor works on datetime Series. `.dt.hour` returns hour numbers; `.dt.normalize()` sets the time to midnight while retaining timezone information. Avoid formatting timestamps into strings when the next step requires time arithmetic.

Build timezone-aware boundaries with `pd.Timestamp("YYYY-MM-DD", tz="UTC")`. Compare `>= start` and `< next_day`: this includes every time in the day without accidentally including the next midnight. Missing timestamps do not match.

Parse and filter timestamps

Resample UTC events into complete daily and Monday-start weekly calendars.

`.set_index("searched_at").resample("D").size()` counts rows in each UTC day. Size counts searches even when other columns are missing. Resampling groups by time; the source rows need not already be chronological.

`pd.date_range(start, end, freq="D", tz="UTC")` includes both date endpoints. Reindex the counts to that calendar with `fill_value=0`, so leading, interior, and trailing no-activity dates remain. `.rename_axis("date").reset_index(name="searches")` turns the date index into a column.

Use `.resample("W-MON", label="left", closed="left")` for buckets from Monday 00:00 UTC up to, but not including, the next Monday. W-MON defaults differ, so specify both options. Filter to the requested reporting interval first, then reindex to its Monday labels.

Build daily and weekly reports

Align yesterday's count and compute changes without inventing growth from a zero baseline.

Sort by date before `.shift(1)`. Shift uses the previous row, not a calendar lookup. On a complete daily calendar, that row is yesterday; without the calendar, it could be several days earlier. The first row has no previous value.

`.pct_change(fill_method=None)` returns fractional change, not a percentage: 0.5 means 50% growth. A zero previous count has no defined relative growth here. Use `.where(previous > 0)` to leave those rows and the first row missing; a drop from a positive count to zero is -1.

Retain every date in the reporting calendar. Do not fill the first prior count with 0 or forward-fill gaps to create a growth rate. For this report, an absent event date has a known count of zero, while the day before the report is unavailable.

Compare with the previous period

Choose a trailing window and minimum coverage explicitly.

`.rolling(7, min_periods=1).mean()` averages up to seven rows, including the current row. With `min_periods=7`, the first six results stay missing. Keep zero-count days: removing them changes the window and its denominator.

`.rolling("7D", closed="right")` needs a sorted datetime index and covers (current time minus seven days, current time]. The left endpoint is excluded. Seven daily midnight rows and a complete calendar make this the current day plus the six preceding dates. On irregular data, seven rows need not be seven days.

Use `.sum()` for a trailing count and `.mean()` for its daily average. Set `min_periods` explicitly for both row-based and time-based windows; their defaults differ. Keep `center=False`, the default, so future rows never contribute.

Calculate rolling metrics

Combine counts, distinct users, and correctly defined rates in one device report.

One row in searches is one search. Count all rows for searches and distinct non-missing user_id values for known_users. A person may use several devices, so adding device-level unique users does not give a global unique-user total.

Deduplicate click search IDs before a one-to-one left merge into searches. Fill only absent clicked markers with 0. Then aggregate at device level, retaining devices without clicks and missing-device groups.

search_ctr is clicked searches divided by all searches. zero_result_rate is searches with result_count equal to 0 divided by all searches. Repeated clicks do not add to the first numerator. Keep both rates as unrounded proportions, not percentages.

Build the device summary

Normalize query variants, apply a volume minimum, and rank with deterministic ties.

Trim query edges, lowercase text, and collapse whitespace before grouping. Each search still counts as an event. Exclude missing queries and strings that become empty after cleaning; do not deduplicate the search rows.

Aggregate first, keep query groups with at least the required count, then sort count descending and normalized query ascending. The second key makes tied counts deterministic. Take at most the requested number after filtering.

For each normalized query, zero_result_rate is zero-result searches divided by all searches for that query. A Boolean mean computes this proportion. Do not filter to zero-result events before calculating the denominator.

Find frequently searched queries

Measure result CTR using impressions and distinct clicked result keys.

Each search_results row is an impression identified by (search_id, position). Result CTR at a position is clicked impressions divided by all impressions at that position. This differs from search CTR: one search can contribute several result impressions.

Deduplicate clicks using both search_id and position, then attach a marker to search_results with a one-to-one left merge. Repeated clicks on the same result count once; clicks on different positions within a search remain separate.

Fill absent markers with 0 and group the full impression table by position. Count rows for impressions and sum markers for clicked_results. Divide within each position and retain positions with zero clicked results.

Compare result positions

Combine weekly search volume, clicked searches, CTR, changes, and CSV export.

Deduplicate clicked search IDs and attach markers to searches before weekly grouping. Use searched_at, not clicked_at, to assign the week. Any matching click in the provided clicks table marks its search as clicked, even if the click occurs in a later week.

Report the eight Monday-start UTC weeks beginning July 6 through August 24, 2026. Keep weeks with no searches as zero counts. Their CTR is missing because the denominator is zero; the first absolute search change is missing because no earlier report week is available.

Use `.to_csv(index=False, date_format="%Y-%m-%d")` for a CSV string with UTC week-start dates such as 2026-07-06. This omits the row index and leaves missing numeric cells empty. Keep the full precision of rates; the CSV date labels refer to UTC Mondays.

index	text	trimmed
0	Blue Sky	Blue Sky
1	Red Fox	Red Fox
2	NA	NA

Clean query text

Trim the edges

Normalize case and repeated whitespace

Keep missing and empty values distinct

index	text	normalized
0	Blue Sky	blue sky
1	RED FOX	red fox
2	NA	NA