Leaked columns and labels that aren't labels

On non-financial tables the most dangerous data-quality problems are not missing values or outliers. They are columns that quietly contain the answer, and labels that do not mean what their name says. Both make a model look excellent and both are found by the same two checks.

The example is a public sample of 6,433 New York taxi trips, one row per trip, with fare, tip, tolls, total, payment and pickup/dropoff details. The question is “predict the tip”.

import pandas as pd, numpy as np

df = pd.read_csv("taxis.csv")
print(df.shape)
print(list(df.columns))

Check 1: is any column an arithmetic function of the target?

For each numeric column, look at its correlation with the target, and then look for an identity. A residual that takes a handful of discrete values is a formula, not a coincidence.

num = df.select_dtypes("number").columns.drop("tip")
print(df[num].corrwith(df["tip"]).round(3).sort_values(ascending=False).to_string())

resid = df["total"] - df["fare"] - df["tip"] - df["tolls"]
print("\ntotal - fare - tip - tolls, most common values:")
print(resid.round(2).value_counts().head(5).to_string())

total is fare plus tip plus tolls plus a small set of fixed surcharges. Any model given total as a feature can recover the tip by subtraction, and will. It is not a feature; it is the target wearing a hat. The taxi case study shows the R² jump from 0.45 to 0.91 when it is included.

The general form of the check: target against sum of some columns minus sum of others, for the combinations the column names suggest. Column names like total, net, gross, balance, amount_after are the usual suspects.

Check 2: does the target’s distribution collapse in some category?

Group the target by each categorical column. A category in which the target is always zero (or always one, or always missing) is a category in which the target was not recorded, and those rows are not examples of “zero”; they are examples of “unknown”.

print(df.groupby("payment")["tip"].agg(["count", "mean", lambda s: (s == 0).mean()]).rename(columns={"<lambda_0>": "share_zero"}).round(3).to_string())
print("\npayment missing:", df["payment"].isna().sum())

Every cash trip has a tip of exactly zero. Cash tips are not captured by the meter; they are unrecorded, not absent. A model trained on all rows learns that cash means no tip, which is true of the data and false of the world, and its predictions for card trips get worse for it. The honest study restricts to card payments and says so.

Check 3: what is a row, and is it repeated?

print("exact duplicate rows:", df.duplicated().sum())
print("duplicate (pickup, dropoff, distance, fare):", df.duplicated(["pickup", "dropoff", "distance", "fare"]).sum())
df["pickup"] = pd.to_datetime(df["pickup"])
df["dropoff"] = pd.to_datetime(df["dropoff"])
dur = (df["dropoff"] - df["pickup"]).dt.total_seconds() / 60
print("trips with non-positive duration:", (dur <= 0).sum(), "| zero distance:", (df["distance"] == 0).sum(), "| zero passengers:", (df["passengers"] == 0).sum())

No duplicates here, and a handful of impossible trips: zero minutes, zero distance, zero passengers. Small enough to note and keep or drop with one line; see first-pass interrogation.

The one-minute routine

1. corrwith(target) on numeric columns; anything above 0.6 gets a subtraction test.
2. groupby(each categorical)[target].agg(mean, share_zero); any share_zero of 1.0 is a recording artefact.
3. duplicated() on the natural key; describe() on durations and counts for impossible values.

Then write down the decisions: which columns are excluded as leaks, which rows are excluded as unrecorded, and what “one row” means. Those three lines are the data slide for a non-financial study.