Python and Pandas for Data Analysis Questions

Direct answer

merge()'s how= parameter controls which rows survive when two DataFrames are joined on a key: inner keeps only keys present on both sides, left keeps every row from the left DataFrame regardless of a match, right keeps every row from the right DataFrame regardless of a match, and outer keeps every key from either side, filling with NaN wherever the other side has no match. indicator=True adds a _merge column showing whether each result row came from "left_only", "right_only", or "both", which is the fastest way to see exactly which rows failed to match on either side.

The four join types, demonstrated

import pandas as pd

left = pd.DataFrame({
    "id": [1, 2, 2, 3],
    "left_val": ["A", "B", "C", "D"],
})
right = pd.DataFrame({
    "id": [2, 2, 4],
    "right_val": ["X", "Y", "Z"],
})

for how in ["inner", "left", "right", "outer"]:
    result = pd.merge(left, right, on="id", how=how, indicator=True)
    print(f"--- {how} ({len(result)} rows) ---")
    print(result)

Verified row counts on pandas 3.0.3: inner produces 4 rows, left produces 6, right produces 5, outer produces 7. Walking through why:

• inner (4 rows): only id=2 exists on both sides, and since it appears twice on the left and twice on the right, every left/right pairing for that key is produced, 2 x 2 = 4 rows, none of which has a NaN in either left_val or right_val.
• left (6 rows): the same 4 matched id=2 rows, plus id=1 and id=3 (present only on the left) each appearing once with right_val as NaN and _merge == "left_only".
• right (5 rows): the same 4 matched id=2 rows, plus id=4 (present only on the right) with left_val as NaN and _merge == "right_only".
• outer (7 rows): the union, the 4 matched rows, plus id=1, id=3 as "left_only", plus id=4 as "right_only".

Why merges can unintentionally explode: duplicate keys

If either side has a key that appears more than once, merge() produces every combination of matching rows for that key, a Cartesian product on the duplicated portion, not a simple row-for-row pairing. In the example above, id=2 appears twice on the left (left_val = B and C) and twice on the right (right_val = X and Y), so the inner join alone produces 2 x 2 = 4 rows just for id=2, four combinations (B-X, B-Y, C-X, C-Y) where you might have expected two. On real data, where a key that should be unique (an order id, a user id) accidentally has duplicates, often from an upstream bug or an unintended many-to-many relationship, this silently multiplies row counts and downstream aggregates (sums, counts) become inflated in a way that is easy to miss unless you are specifically checking for it, the merge itself does not raise an error.

Detecting and preventing key-duplication explosions

# check for duplicate keys before merging
left["id"].duplicated().any()      # True here, id=2 appears twice
right["id"].duplicated().any()     # True here, id=2 appears twice

# see exactly which keys are duplicated and how many times
left.groupby("id").size().loc[lambda s: s > 1]

# make pandas itself raise if the join isn't the cardinality you expect
pd.merge(left, right, on="id", how="left", validate="many_to_one")
# raises MergeError here, because id=2 is NOT unique on the right side

validate= ("one_to_one", "one_to_many", "many_to_one", "many_to_many") is the most reliable check because it does not depend on remembering to look, it fails loudly at merge time if your assumption about key uniqueness on either side turns out to be wrong.

Anti-join: rows that exist on only one side

indicator=True combined with how='outer' also gives you the anti-join pattern directly: filter the merged result down to only the rows that failed to match, using the same _merge column already shown above.

merged = pd.merge(left, right, on="id", how="outer", indicator=True)
left_only  = merged[merged["_merge"] == "left_only"]   # rows in `left` with no match in `right`
right_only = merged[merged["_merge"] == "right_only"]  # rows in `right` with no match in `left`

Verified on the same fixture as above: left_only returns id=1 and id=3 (present only on the left), right_only returns id=4 (present only on the right). This is the standard way to answer "which rows in one table have no counterpart in the other," for example finding orders with no matching payment record, or customers with no matching order, without pulling in every matched row you don't care about.

Trade-offs and pitfalls

• indicator=True is cheap and gives immediate visibility into match rates, but it adds a categorical _merge column to the result that you need to drop before downstream code that is not expecting it (result.drop(columns="_merge")), or filter on before further processing (result[result["_merge"] == "both"] to keep only matched rows explicitly).
• outer merges are the easiest to reason about for "did I lose anything," since nothing from either side disappears, but they are also the easiest to accidentally ship with unintended NaNs propagating into downstream numeric operations if you forget that unmatched rows exist at all.
• Deduplicating with drop_duplicates(subset=["id"]) before merging is a real fix for the explosion problem, but only when duplicates are genuinely redundant; if the duplicate rows carry different, meaningful information (as left_val = B and C do for id=2 here), dropping one silently discards real data rather than fixing a bug, decide which case you are in before reaching for drop_duplicates as the fix.
• validate= catches the problem at merge time, which is strictly better than discovering it downstream via an inflated sum() or count(), but it requires you to actually know and state the cardinality you expect, which is itself a modeling decision worth making explicit in code rather than assuming.

Direct answer

Missing values are not one problem: they are a symptom, and the right handling depends on the mechanism behind each field's missingness, not on a single blanket rule for the dataset. I would look at income, last_login, and plan_type separately, because a numeric field missing due to a survey skip, a timestamp missing because the event never happened, and a category missing because of an integration gap all call for different treatments.

Structured elaboration

The common thread: decide whether the fact that a value is missing carries information before deciding how to fill it in. Missingness that is itself predictive (an MNAR pattern, missing-not-at-random) should stay visible to the model as a flag; missingness that looks closer to random can be imputed more mechanically without much risk.

Worked example

Concretely, before finalizing any of the three choices above, I would run this check: compare the target churn rate for rows where last_login is missing against rows where it is present.

• If the two groups have similar churn rates, the missingness in last_login is probably closer to random and treating it lightly (e.g. simple imputation) carries lower risk.
• If missing-last_login rows churn dramatically more (or less) than the rest, that gap is signal, and collapsing it into an imputed "typical" login date would erase the single most predictive fact in the dataset. In that case the never_logged_in flag is not an optional nicety, it is likely one of the strongest features in the model.

I would run the same comparison for income and plan_type rather than assuming the table above holds for every dataset; the mechanism has to be checked, not guessed.

Trade-offs and pitfalls

• Imputing on the full dataset before splitting leaks information from validation/test rows into the training-time imputation statistic (e.g. a median computed across all rows). Fit any imputer on the training split only, then apply it unchanged to validation and test.
• Dropping rows is the easiest option and the most dangerous: if missingness correlates with the outcome (which is exactly the case that matters most for churn), dropping those rows biases the remaining dataset toward the "well-behaved" users and the model will underperform precisely on the segment it most needs to catch.
• A missingness flag with no real fill value change can still help even for fields you do impute: keeping income_missing as a separate column after filling income with the median lets a model use "the value" and "whether the value was actually observed" as two independent signals, instead of collapsing them into one filled number that looks identical to genuinely observed data.
• Validate by segment, not just in aggregate: after choosing a treatment, compare model calibration and error rates across the missing-vs-present subgroups specifically, since an averaged validation metric can look fine even while the model is quietly wrong on exactly the rows that had missing data.

Direct answer

Use pivot_table when you need a wide layout, one column per category, and possibly an aggregation, and use melt to go the other direction, back to the original long ("tidy") format. Plain pivot (without _table) only works when the index/columns pair is already unique per value; the moment two rows can land in the same cell, you need pivot_table with an explicit aggfunc.

Approach

import pandas as pd

sales = pd.DataFrame({
    'date': ['2024-01-01', '2024-01-01', '2024-01-02', '2024-01-02'],
    'store_id': [1, 1, 2, 2],
    'product': ['A', 'B', 'A', 'B'],
    'units_sold': [10, 5, 3, 8],
})

# 1) Pivot to wide format, summing units_sold, filling missing combos with 0
wide = sales.pivot_table(
    index='store_id',
    columns='product',
    values='units_sold',
    aggfunc='sum',
    fill_value=0,
).reset_index()
print('wide:')
print(wide)

# 2) Melt back to long format
long = pd.melt(
    wide,
    id_vars=['store_id'],
    var_name='product',
    value_name='units_sold',
)
print('long:')
print(long)

Output:

wide:
product  store_id   A  B
0               1  10  5
1               2   3  8

long:
   store_id product  units_sold
0         1       A          10
1         2       A           3
2         1       B           5
3         2       B           8

Key points

• pivot_table aggregates; plain pivot does not, and raises if it finds duplicate (index, columns) pairs instead of silently picking one.
• melt(id_vars=..., var_name=..., value_name=...) reverses the shape: id_vars stays fixed per row, and the former column headers become values in one var_name column.
• Columns that were dropped by the pivot (here, date) are not restored by the round trip; you would need to carry them alongside or re-join them from the original data.

Complexity

pivot_table is O(n) to scan the input rows into buckets, plus O(r x c) to materialize the wide grid, where r is the number of distinct index values and c is the number of distinct columns values; a high-cardinality columns key can blow this up even when n itself is modest. melt is O(rows_wide x value_columns), since it stacks every value column back into its own set of rows.

Edge cases

• A missing store/product combination: pivot_table fills it with fill_value=0 here rather than leaving NaN (not-a-number); without fill_value, it would be NaN.
• Duplicate (store_id, product) pairs in the input: pivot_table aggregates them via aggfunc (sum, here); plain pivot() raises ValueError: Index contains duplicate entries, cannot reshape.
• Melting a fill_value=0-filled wide table introduces rows for combinations that never existed in the original long data, all carrying value 0, so filter those out (long[long['units_sold'] != 0]) if you need to recover exactly the original rows.
• Empty input: pivot_table on an empty frame returns an empty result with the requested index/columns structure, not an error.

Trade-offs and pitfalls

Aggregating away duplicate combinations with pivot_table is convenient but lossy: if two rows in sales legitimately represent different dates for the same store/product, summing them into one wide cell discards the per-date detail permanently. If that detail matters, keep date in the index (e.g. index=['store_id', 'date']) rather than aggregating over it.

Direct answer

Write each stage (read, filter, impute, feature creation, write) as a small pure function that takes a DataFrame and returns one, and compose them with .pipe() so the pipeline reads top-to-bottom as its own table of contents. Each function can then be unit-tested in complete isolation with a two- or three-row DataFrame, with no need to run the rest of the pipeline or touch the filesystem.

Approach

import pandas as pd

def read_csv(path):
    return pd.read_csv(path, parse_dates=["event_time"])

def filter_rows(df):
    return df[df["event_time"].notna() & (df["revenue"] > 0)]

def impute(df):
    df["country"] = df["country"].fillna("unknown")
    df["revenue"] = df["revenue"].fillna(0)
    return df

def create_features(df):
    # cast the period to str: parquet's partition_cols can't hash a raw Period dtype column
    df["month"] = df["event_time"].dt.to_period("M").astype(str)
    df["rev_per_item"] = df["revenue"] / df["items"].replace(0, 1)
    return df

def run_pipeline(input_path, output_dir):
    result = (
        read_csv(input_path)   # the chain starts from the function that produces the first DataFrame,
        .pipe(filter_rows)     # not from an undefined variable piped through read_csv
        .pipe(impute)
        .pipe(create_features)
    )
    result.to_parquet(output_dir, partition_cols=["month", "country"], engine="pyarrow")
    return result

Key points:

• The chain has to start from a call that actually produces a DataFrame (read_csv("input.csv")), not from .pipe()-ing an undefined name through a reader function; .pipe() threads an existing DataFrame through the next step, it can't conjure the first one.
• create_features casts month to a plain string before it becomes a partition column: to_period("M") produces a pandas-specific Period dtype, and PyArrow's write_to_dataset (which to_parquet(partition_cols=...) uses under the hood) cannot hash that extension dtype as a partition key, so writing the raw Period column raises ArrowNotImplementedError: Keys of type extension<pandas.period<ArrowPeriodType>>.
• impute and create_features each receive the DataFrame that the previous .pipe() call already produced fresh (from filter_rows's boolean-indexed slice, which is itself already a new object under Copy-on-Write), so a plain df["col"] = ... inside them is an ordinary column assignment, not the chained-slice pattern that pandas 3.0 flags.

Worked example

pd.DataFrame({
    "event_time": ["2026-01-05", "2026-01-20", "2026-02-01", None, "2026-02-10"],
    "revenue": [10.0, 0.0, 25.0, 15.0, -5.0],
    "items": [2, 1, 0, 3, 1],
    "country": ["US", None, "DE", "US", "FR"],
}).to_csv("input.csv", index=False)

result = run_pipeline("input.csv", "out/")
print(result)

Output (verified against pandas 3.0.3, pyarrow):

  event_time  revenue  items country    month  rev_per_item
0 2026-01-05     10.0      2      US  2026-01           5.0
2 2026-02-01     25.0      0      DE  2026-02          25.0

Of the five input rows, three are filtered out by filter_rows: the row with revenue == 0, the row with a missing event_time, and the row with negative revenue (a data-quality problem, not something impute should paper over). The items == 0 row on 2026-02-01 survives filtering (its revenue is positive) and rev_per_item correctly falls back to dividing by 1 instead of 0 via .replace(0, 1). Writing this result with partition_cols=["month", "country"] succeeds and reading it back with pd.read_parquet("out/") reproduces the same two rows.

Complexity and edge cases

Complexity: each stage is a single O(n) pass over the rows it touches; .pipe() itself adds no algorithmic cost, it is function composition. Memory: each .pipe() call's result is a fresh DataFrame (a shallow copy for the untouched columns plus whatever the step added or filtered), so peak memory during the chain is bounded by roughly the size of the largest single intermediate, not the sum across every stage, since Python frees each intermediate once the next step has consumed it.

Edge cases: an items value of exactly 0 needs the .replace(0, 1) guard or rev_per_item divides by zero; a missing event_time is dropped by the filter rather than imputed, since a synthetic timestamp would be a worse assumption than dropping the row; an already-unknown or already-zero-filled value passing through impute a second time is a no-op, so the function is safe to call more than once (idempotent) if the pipeline is ever re-run on partially-processed data.

Trade-offs and pitfalls

Unit-testing each function in isolation is the main payoff of this shape: create_features can be tested with a two-row DataFrame asserting the exact rev_per_item value and that month is a plain string (not a Period), without needing a real CSV file or a filesystem to write parquet to; impute can be tested by asserting that a null country becomes "unknown" and a null revenue becomes 0, again with no dependency on the rest of the chain. Mocking I/O becomes trivial too, since read_csv and the final to_parquet call are the only two functions that touch the filesystem, everything in between is a pure DataFrame-to-DataFrame transformation that pytest fixtures can exercise directly. The pitfall in a .pipe() chain specifically: because each step only runs when the chain executes, not when it's defined, a bug like the undefined-starting-variable above or a missing import inside a step function stays completely invisible until someone actually runs the full pipeline end-to-end, which is exactly why testing each function independently (rather than trusting that the chain "looks right") is the point of building it this way in the first place.

Direct answer

Run a short, repeatable inspection pass over any newly loaded DataFrame: dtypes and non-null counts first (df.info()), then descriptive statistics split by numeric versus categorical columns (since describe() reports very different things for each), then percent missing per column, then a sample of unique values for whichever categorical column you actually care about, usually the one you are about to group, filter, or join on.

Implementation

import pandas as pd

def profile_df(df, cat_col=None, n_unique_sample=10):
    # 1) dtypes and non-null counts
    print("Dtypes and non-null counts:")
    print(df.info())

    # 2) descriptive stats for numeric columns
    print("\nNumeric summary:")
    print(df.select_dtypes(include="number").describe().T)

    # 3) descriptive stats for categorical/text/boolean columns
    # include 'string' alongside 'object' so this also catches columns under
    # pandas 3.0's default string dtype, not just the legacy object dtype
    cat = df.select_dtypes(include=["object", "string", "category", "bool"])
    if not cat.empty:
        print("\nCategorical summary (count, unique, top, freq):")
        print(cat.describe().T)
    else:
        print("\nNo categorical columns found.")

    # 4) percent missing per column
    missing_pct = df.isna().mean() * 100
    print("\nPercent missing per column:")
    print(missing_pct.sort_values(ascending=False))

    # 5) sample unique values for a chosen categorical column
    if cat_col is None:
        cat_col = cat.columns[0] if not cat.empty else None
    if cat_col is not None:
        uniques = df[cat_col].dropna().unique()
        print(f"\nSample unique values for '{cat_col}' (up to {n_unique_sample}):")
        print(list(uniques[:n_unique_sample]))
        print(f"Total unique: {len(uniques)}")
    else:
        print("\nNo categorical column to sample uniques from.")

Worked example (verified against pandas 3.0.3, no warnings emitted)

df = pd.DataFrame({
    "id":      [1, 2, 3, 4, 5],
    "price":   [10.5, 20.1, None, 15.0, 9.9],
    "country": ["US", "UK", "US", "CA", None],
    "active":  [True, False, True, True, False],
})
profile_df(df, cat_col="country")

Actual output (trimmed to the key sections):

Numeric summary:
       count    mean       std  min    25%    50%     75%   max
id       5.0   3.000  1.581139  1.0   2.00   3.00   4.000   5.0
price    4.0  13.875  4.733128  9.9  10.35  12.75  16.275  20.1

Categorical summary (count, unique, top, freq):
        count unique   top freq
country     4      3    US    2
active      5      2  True    3

Percent missing per column:
price      20.0
country    20.0
id          0.0
active      0.0

Sample unique values for 'country' (up to 10):
['US', 'UK', 'CA']
Total unique: 3

price correctly shows count=4 (one missing value excluded from the mean/std), country's describe() correctly reports US as the most frequent value with freq=2, and the missing-percent breakdown immediately tells you price and country are the two columns that need a decision (impute, drop, or carry forward as legitimately missing) before modeling or aggregation.

Key points

• df.info() is the fastest way to see dtypes and non-null counts together, catching an accidentally-all-object numeric column or an unexpectedly low non-null count in one glance.
• describe() reports different statistics depending on dtype: mean/std/quartiles for numeric columns, count/unique/top/freq for text, category, and boolean columns, calling it once on the whole DataFrame silently drops whichever family of columns it was not built for unless you explicitly split by select_dtypes first, as this function does.
• df.isna().mean() * 100 is a fast, vectorized way to get percent-missing per column without writing a loop, and sorting it descending immediately surfaces the columns that need attention first.
• Sampling unique values (rather than printing all of them) is deliberate: it is meant to catch typos and inconsistent category labels ("US" vs "U.S." vs "United States") at a glance, not to enumerate a high-cardinality column in full.

Complexity and edge cases

Complexity: each step here is a single O(n) or O(n * columns) pass, info(), describe(), and isna().mean() are all vectorized, so profiling scales linearly with the size of the DataFrame; nunique()/unique() on a very high-cardinality column is still O(n) but with a larger constant cost than the aggregate statistics.
Edge cases: a DataFrame with no numeric columns makes select_dtypes(include="number").describe() return an empty frame, not an error, worth guarding the print with a check if you want a clean message instead of an empty table. A very wide DataFrame (hundreds of columns) makes the printed describe().T output unwieldy, consider .sample(n=30, axis=1) on the columns or splitting the profile into a few DataFrames of columns at a time. A high-cardinality categorical column (a free-text or id-like column) will make cat.describe()'s unique/top/freq less informative (often unique close to len(df)), and printing "a sample of unique values" for it is far more useful than the full describe() summary, which is exactly why the function separates those two concerns.