I opened a dataset. What now?

Every table looks new for about ten minutes. After that it is one of a small number of shapes, and each shape has a known set of traps, a natural baseline, and a validation design that answers the question actually being asked. This page is the index of those shapes. Read the first four questions in order; then find your symptom in the last table.

One screen of triage

The brief defines the columns. Don’t research theory before your first number: the first 30 minutes are rows, key, missing, wrong.

Before anything else, one block that answers most of the questions below on any table. Here it runs on a hotel-booking file; swap the file name and the column names.

import pandas as pd, numpy as np

df = pd.read_csv("hotel_bookings.csv")
print(df.shape)
print("nulls:", df.isna().sum()[lambda s: s > 0].to_dict())
print("exact duplicate rows:", df.duplicated().sum(), "(%.1f%%)" % (100 * df.duplicated().mean()))
obj = df.select_dtypes("object").nunique().sort_values(ascending=False)
print("categorical cardinality:", obj.head(6).to_dict())
print("numeric columns that look like ids or codes:", [c for c in df.select_dtypes("number") if df[c].nunique() == len(df)])
print("target: is_canceled ->", df["is_canceled"].value_counts(normalize=True).round(3).to_dict())
print("arrival years:", df["arrival_date_year"].value_counts().sort_index().to_dict())

(119390, 32)
nulls: {'children': 4, 'country': 488, 'agent': 16340, 'company': 112593}
exact duplicate rows: 31994 (26.8%)
categorical cardinality: {'reservation_status_date': 926, 'country': 177, 'arrival_date_month': 12, 'assigned_room_type': 12, 'reserved_room_type': 10, 'market_segment': 8}
numeric columns that look like ids or codes: []
target: is_canceled -> {0: 0.63, 1: 0.37}
arrival years: {2015: 21996, 2016: 56707, 2017: 40687}

Six lines and the table has already declared itself: a binary target with a 37% positive rate (not rare, but not balanced), 27% exact duplicate rows, two high-cardinality columns with most values missing, and a time axis across three years. Each of those is a fork in the road below.

If it’s a panel (entity × time)

When one row is one entity at one time, the block above misses the checks that matter most: the key, each entity’s span, and the spacing in time. Here it runs on a daily equity file with one row per ticker per day. ticker and date are placeholders for the file’s own entity and time columns.

df = pd.read_csv("all_stocks_5yr.csv").rename(columns={"Name": "ticker"})
df["date"] = pd.to_datetime(df["date"])
df = df.sort_values(["ticker", "date"]).reset_index(drop=True)   # sort before any shift/diff/pct_change
print("duplicate keys:", df.duplicated(["date", "ticker"]).sum(),
      "| exact duplicate rows:", df.duplicated().sum())
span = df.groupby("ticker")["date"].agg(["min", "max", "count"])  # who starts late, who ends early
print(span.sort_values("count").head(10))
gap = df.groupby("ticker")["date"].diff()
print((gap.dt.total_seconds() / 3600).value_counts().head(10))   # gaps in hours; don't use .dt.days on sub-daily data
print(df.groupby("date")["ticker"].nunique().describe())          # entities per timestamp over time

duplicate keys: 0 | exact duplicate rows: 0
              min        max  count
ticker                             
APTV   2017-12-05 2018-02-07     44
DWDP   2017-09-01 2018-02-07    109
BHF    2017-07-17 2018-02-07    143
BHGE   2017-07-03 2018-02-07    152
DXC    2017-04-03 2018-02-07    215
HLT    2017-01-04 2018-02-07    276
EVHC   2016-12-02 2018-02-07    297
FTV    2016-07-01 2018-02-07    404
UA     2016-04-07 2018-02-07    464
WLTW   2016-01-05 2018-02-07    528
date
24.0     484808
72.0     111568
96.0      16754
48.0       5398
144.0         3
408.0         3
120.0         1
Name: count, dtype: int64
count    1259.000000
mean      491.691819
std         8.281534
min       472.000000
25%       485.000000
50%       492.000000
75%       498.000000
max       505.000000
Name: ticker, dtype: float64

• Duplicate keys greater than exact duplicate rows means some copies disagree. Look at them, pick a rule, and check that the rule doesn’t change the result.
• Entities that start late but none that end early means the universe is probably only today’s survivors. Say so on the last slide.
• One gap size that recurs at the same timestamp across every entity is one market-wide event, such as an outage or a holiday, not many separate problems.

Then a few more lines, before anything is plotted or ranked:

print("bar size:", df.groupby("ticker")["date"].diff().median())      # hourly? daily? label everything with this
df["ret"] = df.groupby("ticker")["close"].pct_change()
print(df["ret"].quantile([0.001, 0.01, 0.5, 0.99, 0.999]).round(4).to_dict())   # look at the tails before plotting
flat = (df["open"] == df["high"]) & (df["high"] == df["low"]) & (df["low"] == df["close"])
print("flat bars:", flat.sum(), "| of which zero volume:", (flat & (df["volume"] == 0)).sum())

bar size: 1 days 00:00:00
{0.001: -0.0904, 0.01: -0.0429, 0.5: 0.0006, 0.99: 0.0421, 0.999: 0.0917}
flat bars: 1 | of which zero volume: 0

• Bar size first. Call it an hourly return if the bars are hourly. Mislabelling the unit makes every later number sound wrong (“daily std 0.5”).
• Tails before a histogram. If the 0.1% and 99.9% quantiles are sane but the max is absurd, a few bad rows are driving it. A +99x return followed by −0.99 on the next bar is one bad print, not two events. Fix the row, then plot.
• Flat zero-volume bars are stale, not impossible. Nothing traded, so the price just carried over. They produce a return of 0 that looks like information in a cross-sectional rank. Drop them or mark them, and say which.
• A row-count filter needs a reason. groupby("ticker").size() followed by a threshold removes late listings along with broken tickers. Look at the span table before filtering by count.

Last, check that each bar agrees with itself:

bad = (df["close"] > df["high"]) | (df["close"] < df["low"]) | (df["high"] < df["low"])
print("close outside [low, high]:", bad.sum())
df = df[~bad]

close outside [low, high]: 9

• A close outside its own bar’s range is a bad print, not a price. Drop the rows and say how many. One bad print can make the file’s largest return; here a close at half the real price gives −49% then +101%.

When a count doesn’t add up, print the rows

When a check returns a count that doesn’t make sense, print the rows behind it before guessing why.

odd = df[df["volume"] == 0]                          # whatever set looks odd
print(df.merge(odd[["date", "ticker"]]))            # every row sharing those keys
print(odd["date"].value_counts().head())            # do they share a timestamp?

        date  open  high  low  close  volume ticker       ret
0 2016-01-12   NaN   NaN  NaN  88.55       0    DHR  0.001131
1 2016-07-01   NaN   NaN  NaN  49.54       0    FTV       NaN
2 2016-01-12   NaN   NaN  NaN  52.43       0      O  0.003061
3 2016-04-07   NaN   NaN  NaN  41.56       0     UA       NaN
date
2016-01-12    2
2016-07-01    1
2016-04-07    1
Name: count, dtype: int64

On this file it prints 4 zero-volume rows whose open, high and low are all NaN, so printing them explains them at once: only a close was recorded.

• Two rows back for one key means it’s a duplicate, and the copies may disagree. That is not a new problem.
• Many rows on one timestamp means one market-wide event, not many separate faults.
• Neither means you look at the columns of those rows until one of them explains it.

1. What is one row?

One row is…	Which means	Go to
one point in time (a day, an hour) for one thing	a time series; the neighbours of a row are correlated with it and the level may drift	choosing a split, load forecasting
one entity at one time (a stock on a date, a store in a week, a patient at a visit)	a panel: both a time axis and repeated entities; two ways to leak	twelve sites, one sky, single series or panel
one event or transaction (a booking, a call, a trip, a sale)	usually time-stamped; the entity behind it may repeat; post-event columns are likely	prediction boundaries, hotel cancellations
one entity, once (a house, a customer snapshot)	static tabular data; a random split may be fine unless the entity has near-duplicates	missing values that mean something
repeated measurements of the same entity	the entity explains much of the variance; a random split measures recognition, not prediction	a model that learns who, not how ill

If you cannot say what one row is in a sentence, stop and find out. Half of the later mistakes are a wrong answer to this question. See first-pass dataset interrogation.

2. Is there a timestamp?

If yes, answer these before building a feature:

• What would be known at the moment of prediction? Every column is either known before, known at, or observed after that moment. Sort them. → prediction boundaries
• Is this forecasting or merely time-stamped? Forecasting means the target is the series’ own future; the baseline is then “the last value” or “the same time last cycle”, and a model has to beat that. → load forecasting, traffic against a seasonal baseline
• Should the split be chronological? Almost always. A shuffled split on drifting data is the single most common source of a number that is too good. → the random split that looked great, chronological splits with an embargo
• Are there repeated cycles? Hour of day, day of week, month. Lags at the cycle length (24, 168, 12) are the features that carry them, and “same time last cycle” is the baseline.
• Could a rolling calculation see the future? Centred windows, full-sample normalisation, targets that reach past the split. → forward targets and look-ahead leakage
• Does the level move? Plot the monthly mean of the target. If it trends, a model without level-carrying features will be wrong in one direction for the whole test period.

3. Are there repeated entities?

Customer, product, store, machine, patient, ticker, site, user. If an identifier column has fewer unique values than rows, decide which question is being asked:

• Another observation for a known entity, where the entity’s own history is a legitimate feature and usually the best baseline; or
• A never-seen entity, where entity identity, and anything that proxies for it, must be kept out of the model, and the split must hold out whole entities.

Then run the variance decomposition: how much of the target’s variance is between entities and how much within. → a model that learns who, not how ill (entity explains 93%), twelve sites, one sky (entity explains almost nothing), choosing a split.

4. What does the target look like?

Target	First numbers to compute	Baseline	Watch for	Example
continuous, roughly symmetric	mean, sd, tails	train mean; last value if a series	outliers driving the fit	taxi tips
continuous, heavily right-skewed	median vs mean, max	median; fit on log(1 + y)	MAE and RMSE telling different stories; residual variance growing with the fitted value	house prices
a count	share of zeros, max	seasonal naive; mean by cell	interactions between calendar factors; skew	bike rentals, traffic
binary, balanced-ish	positive share	majority class; constant probability	post-event columns	hotel cancellations
binary, rare positive	positive share, count of positives	constant probability; log-loss of the base rate	accuracy that equals the base rate; threshold at 0.5 predicting nothing	bank marketing, flight delays
the series’ own future	autocorrelation at 1 and at the cycle length	persistence; seasonal naive	a model that barely beats yesterday	load forecasting
no obvious target	what varies most; what the rows cluster on	none	inventing a target that is really an identifier	extreme values
a ranking or a sign	rank correlation; hit rate against the majority	majority sign	overlapping targets inflating t-statistics	evaluating signals

Metrics are chosen by this table, not by habit. → classification metrics, logistic regression with a majority-class baseline.

5. Symptoms

Arrive with the symptom; leave with the page.

Symptom	Most likely cause	Worked example
My random split is excellent and my chronological split is bad	the level of the series moved and the shuffle let the model see it	bike rentals
My random split is excellent and holding out entities is bad	the model recognises entities; identity proxies in the features	Parkinson’s subjects
One feature makes the model absurdly accurate	it is recorded after the outcome, or it is arithmetic on the target	bank marketing: call duration, taxi tips: total
Accuracy is 90% and nobody is impressed	the positive class is 10%; accuracy is measuring the base rate	bank marketing
The model never predicts the positive class	threshold 0.5 on a rare class; the threshold is a business decision	hotel cancellations
The probabilities are right on average in test and wrong in use	the base rate changed after the training period	bank marketing, 2010, distribution shift
Performance falls apart in the last months	drift, a regime change, or a calendar column that memorised campaigns	distribution shift
The sophisticated model barely beats “same as last week”	the seasonal naive is the real competitor and it is strong	traffic volume, load forecasting
Pearson correlation is near zero but the scatter has structure	a nonlinear or non-monotone relationship, or an interaction	hour × working day, Pearson versus Spearman
A linear model is far behind a tree on a handful of features	an interaction the linear model cannot express	bike rentals, section 8
Lots of missing values	find out whether absence means “none”, “not recorded” or “not applicable”, and whether it tracks the target	house prices, missing values
A column is 90% “unknown” or −1	a sentinel; treat as a category, never as a number	bank marketing: pdays
Hundreds or thousands of categories	one-hot explodes; use frequencies or leak-safe target encoding, and handle unseen levels	flight delays: destinations and aircraft
Duplicate rows	twins across train and test inflate every score; decide what a duplicate means before dropping	hotel cancellations
A giant right tail in the target	model log(1 + y); report MAE next to RMSE; check whether a few rows drive the fit	house prices, extreme values
The best of twenty features looks significant	selection on the same sample; needs a clean hold-out	selection bias from the inside
The t-statistic is in the hundreds	a leak, almost always the wrong shift	forward targets and leakage
Everything looks fine and I do not trust it	run the checklist	debugging a model that looks too good

6. Then

Freeze the protocol (target, features, split, score, baseline) before fitting anything: pre-analysis protocol. If the time available is fixed, pace it: analysing a new dataset in a fixed block of time.