So far we have worked with convenient data: the table returned by generate_orders_ml has no gaps, no duplicates, no dates, no free-text columns, and all its columns are ready to go into fit. NovaMarket's real orders.csv looks nothing like that: in 02-03 we saw that it had empty amounts, mistyped postcodes and orders duplicated by a faulty integration. There we learned to diagnose the quality and bias of the data; in this lesson we will learn to transform them so that an algorithm can use them: fill in or drop missing values, handle outliers, turn categories into numbers without misleading the model, scale, extract information from dates, create new features with business knowledge, select the useful ones, avoid data leakage and, above all, chain all of that into a pipeline that is fitted only on the training data. It matters because most of a model's quality is decided here: a mediocre algorithm with good features usually beats a sophisticated algorithm with badly prepared data, and because the mistakes of this phase (especially data leakage) produce models that look excellent in the lab and fail in production.

1. Why preparation takes 60-80 % of the time
2. NovaMarket's "dirty" orders: the function dirty_orders
3. Missing values, duplicates and outliers
4. Encoding categorical variables
5. Scaling numeric variables
6. Dates and feature engineering
7. Feature selection
8. Data leakage
9. Split before transforming: Pipeline and ColumnTransformer
10. Summary table: which technique for each type of column
11. Common Mistakes and Tips
12. Exercises
13. Conclusion

Surveys of data professionals have agreed for years: between 60 % and 80 % of the time of a machine learning project goes on obtaining, cleaning and transforming data, and only a small fraction on training models. The reasons are structural:

• The algorithms in scikit-learn (and in almost any library) demand a numeric matrix with no gaps: each row an instance, each column a number. Business data come with text, dates, blanks and codes.
• Real data have errors (the €99,999 amount that was really €99.99), inconsistencies (the same category written three ways) and duplicates.
• The useful information is rarely in the columns as they are: it has to be built (the amount per item, the customer's previous return rate) from business knowledge. This "feature engineering" is where Marta adds the most value.
• There are decisions the algorithm cannot make: which columns will actually exist at prediction time, what to do with a missing value, whether an outlier is an error or a valuable data point.

In 02-03 we built a quality report (percentage of nulls, duplicates, out-of-range values, bias by group) with the csv module. This lesson takes the next step: using pandas and scikit-learn to transform. The difference in approach matters: in 02-03 the aim was to know what was in the data; here it is to get them ready for fit without introducing traps.

To practise we need realistic data. We start from generate_orders_ml (04-01) and add to it, in code, the typical problems Marta found in orders.csv: identifiers, order date, two new text columns (payment method and shipping type), missing values, an impossible amount, duplicated rows and a column that only exists after the return (return_reason). Add the function to novamarket_ml.py:

Output (abridged):

The diagnosis (what we did in 02-03, now with pandas): 3,003 rows, three duplicated; 56 amounts, 153 delivery days and 100 payment methods missing; and a maximum amount of €99,999 that inflates the mean (€158.8 against a median of €107.9) and the standard deviation. Note: isna().sum() counts the nulls per column, duplicated().sum() the repeated rows and describe() gives the basic statistics.

They are almost always removed; the doubt is what "duplicate" means (the whole row identical, or the same order_id?). Here, the whole row:

Two strategies:

The basic imputation strategies: mean (symmetric numeric), median (numeric with outliers, such as amount), mode or most frequent value (categorical), constant ("unknown", 0), and advanced methods (imputing with a model from the other columns, KNNImputer/IterativeImputer). An extra tip: sometimes the absence is information (a missing payment method may indicate a specific channel); in that case a binary "was missing" column is added (SimpleImputer(add_indicator=True)).

We will not impute by hand yet: we will do it inside the pipeline of section 9, and you will soon see why that matters.

An outlier is a value far away from the rest. The most widely used criterion for detecting them is the interquartile range (IQR): the first and third quartiles (Q1, Q3) are computed, and anything below Q1 − 1.5·IQR or above Q3 + 1.5·IQR is considered an outlier:

Output:

And here comes the decision the algorithm cannot make: the criterion flags 114 orders, but only one is an error. The 113 orders between €314 and €633 are statistical outliers and real customers who, moreover, return a lot (they are precisely the ones Diego worries about); removing them would destroy the signal. The €99,999 one is impossible at NovaMarket (no product costs more than €5,000) and is a typing error. The options when facing an outlier:

• Drop the row if it is an obvious or impossible error (our case).
• Correct it if the error is known (99,999 → 99.99 if there is a way to confirm it).
• Clip (winsorise) at the limit: replace everything above a value with that value.
• Leave it and use robust models (trees) or transformations (logarithm of the amount).

(We keep the rows with a missing amount, isna(), to impute them later.)

Algorithms need numbers, and category, payment_method, shipping_type and postcode_zone are text. The temptation is to assign arbitrary integers (home=0, electronics=1, computing=2, accessories=3); it is a mistake for categories with no order: the model would read accessories (3) as "more" than home (0) and computing as "between" the two, relationships that do not exist. The correct alternatives depend on the type of category:

With OneHotEncoder(handle_unknown="ignore") the model does not fail if a new category appears in production ("crypto" as a payment method): it simply puts zeros. With OrdinalEncoder(categories=[[...]]) we fix the order ourselves; without that argument it would assign alphabetical, i.e. arbitrary, integers.

amount ranges from 6 to 633 and num_items from 1 to 5. For a decision tree it makes no difference (it asks "amount > 195?" without comparing columns), but for any algorithm based on distances (k nearest neighbours, k-means, SVM) or on gradients (logistic and linear regression with regularisation, neural networks) the large columns dominate and training suffers. Two common scalings:

Example with three orders (amount, num_items) = (16.25, 2), (31.60, 1), (25.49, 3): standardised they become (−1.30, 0), (1.13, −1.22), (0.17, 1.22); with min-max, (0, 0.5), (1, 0), (0.6, 1). Now the two columns carry the same weight. Practical rule: always scale unless you use only trees or forests; and remember that the means and deviations are computed on the training set (section 9).

A date cannot be used as it is, but it contains information: day of the week, month, whether it is a public holiday, how many days have passed since an origin. pandas does it with the .dt accessor:

For demand forecasting (04-04) we will use the same on the weekly series: the week of the year captures seasonality and the "previous week's sales" (a lag) capture inertia.

This means creating new columns that express business knowledge. Three kinds that Marta adds to the returns predictor:

For customer 1124, for example, the sequence of orders comes out as: previous orders 0, 1, 2, 3, 4, 5; previous returns 0, 0, 0, 0, 0, 1 (their fifth order was returned and it only counts from the sixth onwards); previous rate 0, ..., 0, 0.2. Note the detail of subtracting clean["returned"]: cumsum() includes the current row, and the return of the current order is not known when it has to be predicted. This care is the difference between a legitimate feature and a leak (section 8).

Basic text: for reviews.csv (case 4) the first features are the length of the review, the number of exclamation marks or the presence of keywords ("broken", "late", "perfect"). In pandas: reviews["text"].str.len(), reviews["text"].str.count("!"), reviews["text"].str.contains("broken|damaged").astype(int). Serious text representation (bags of words, embeddings) belongs to module 5.

More columns is not always better: irrelevant ones add noise, lengthen training and make overfitting easier (04-06). Two simple tools for deciding:

• Correlation with the label (numeric): clean[cols + ["returned"]].corr()["returned"]. In our data: amount 0.35, new_customer 0.30, amount_per_item 0.28, delivery_days 0.16, num_items −0.08, and practically 0 for weekday, weekend, previous_orders and previous_return_rate (the last one because, with 90 days of history, hardly any customer has previous returns; with two years of data it would be among the best). Correlation only detects linear relationships: a feature with zero correlation may be useful combined with another.
• Feature importance from a model: random forests (04-04) share out 100 % of "importance" among the columns according to how much they help to separate. Training one with the pipeline of section 9 we get: amount 0.27, amount_per_item 0.19, new_customer 0.17, delivery_days 0.08, ... and the three postcode_zone columns at around 0.01 each. This is the check we announced in 04-01: the zone contributes nothing, as befits data where it has no influence. In 02-04 we saw the opposite case, a biased history where the model learned to be suspicious of zone B; feature importance is one of the tools for detecting it.

With that information Marta can drop weekday, month and weekend (noise in these data), and consider dropping postcode_zone too, out of ethical prudence, not only statistical.

This is the most dangerous mistake of this phase, because it does not produce failures but results that are too good. There is leakage when the model uses, during training, information that will not exist at prediction time. The return_reason column is the perfect example: it is filled in when the customer returns, so it is empty in every non-returned order and filled in every returned one:

If we add a has_reason feature to the model, it gets 100 % right on test (we have checked). It is a spectacular and completely useless result: when a new order arrives, the return reason does not exist yet. Other frequent, subtler leaks:

• Columns filled in after the event: return_date, refunded_amount, final_status = "refunded".
• Aggregates computed with all the data, including the future: the customer's return rate including the current order (which is why we subtract returned in 6.2), or the mean sales of the whole year when forecasting a week of that year.
• Transformations fitted with the test set: imputing with the median of the whole dataset, scaling with the mean of the whole dataset. It is a small leak, but a leak.
• Duplicates spread between training and test: the model "has already seen" the example.

The rule for detecting it: for each column, ask yourself "will I have this value, exactly like this, at the moment the model has to decide?". And be suspicious of results that are too good.

Everything above converges on a rule and a tool. The rule: first split into training and test, and every transformation that "learns something" from the data (the median for imputing, the mean and deviation for scaling, the one-hot categories) is fitted only on the training set (fit on train) and then applied as is to the test set (transform on test). The tool: Pipeline chains steps, and ColumnTransformer applies its own chain to each group of columns. That way the complete pipeline, preprocessing + model, behaves like a single model with fit/predict, and it is impossible to get the order wrong.

Output:

Explanation:

• Each group of columns gets its treatment: the numeric ones are imputed with the median (robust to outliers) and standardised; the binary ones pass through as they are (passthrough); the nominal ones are imputed with the mode and converted to one-hot; the ordinal one gets integers in the order we fixed. The 15 input columns become 21 numeric columns (one-hot expands category into 4, postcode_zone into 3 and payment_method into 4).
• model.fit(X_train, y_train) computes medians, means, deviations and categories on X_train and trains the logistic regression; model.score(X_test, y_test) applies those same medians and scales to X_test (without recomputing them) and evaluates. The leak of section 8 (third point) is ruled out by construction.
• When the model goes into production, the whole pipeline is handed over: it receives an order as it arrives (with its text and its gaps) and returns the prediction. Nobody has to remember "first impute with 107.9, then subtract 125 and divide by 84".
• The accuracy (87.1 %) is similar to that of the tree of 04-01 and the logistic regression of 04-02, despite having more columns and dirtier data. That is normal: accuracy is a crude measure (04-05) and several of the new columns are noise in these data; the value of the pipeline is that it is now correct, reproducible and deployable, and that the business features (ratios, history) are ready for when the history is longer.

One last useful detail: model.named_steps["classifier"].coef_ gives the logistic regression's coefficients per transformed column. In these data, the largest in absolute value are new_customer (2.0), amount (1.0), delivery_days (0.6) and category_electronics (0.5): the model has rediscovered the "hidden truth" of the generator of 04-01. In 04-04 we will learn to read them properly.

Lessons 04-04, 04-05 and 04-06 will reuse exactly this preprocessing. To avoid repeating code, add two functions to novamarket_ml.py: prepare_orders(dirty), which applies the cleaning and engineering of sections 3 and 6 and returns X and y, and build_preprocessing(), which builds the ColumnTransformer of section 9 (a new instance each time, so that each pipeline fits its own):

From now on, getting the data ready will be a single line: X, y = prepare_orders(dirty_orders(generate_orders_ml(3000, 42), 42)), and any model is trained with Pipeline([("prep", build_preprocessing()), ("model", ...)]).

• Imputing or scaling before splitting. It is the most common silent leak. Split first; fit the transformations inside a Pipeline on the training data.
• Encoding categories with arbitrary integers. It invents an order that does not exist. Use one-hot for nominal ones and ordinal only when the order is real and explicit.
• Removing every outlier "because the IQR says so". The criterion detects candidates; the decision (error or signal) belongs to the business. The €400 orders are the ones Diego wants to watch, not the ones to delete.
• Using columns from the future. Before including a column, ask yourself whether it will exist with that value at prediction time. A suspiciously perfect result is almost always a leak.
• Confusing identifiers with features. customer_id is useful for computing per-customer aggregates; never as a number fed into the model.
• Not scaling before using distances or gradients. k-NN, k-means, SVM, regularised logistic regression and networks need it; trees do not.
• Forgetting handle_unknown="ignore". In production new categories will appear and the pipeline will fail when transforming.
• Doing the preparation by hand in a notebook and not saving it. If it is not in a Pipeline, it can be neither reproduced nor deployed.

Exercise 1. In the pipeline, change the imputation of the numeric columns from median to mean and add add_indicator=True to the SimpleImputer of the nominal ones (it will create a column marking whether the payment method was missing). How many columns does the transformed matrix have now? Does the accuracy change appreciably? Justify why the median was the more prudent option for amount while the 99,999 value was still there.

Exercise 2. Deliberately commit the "scale before splitting" leak: apply StandardScaler().fit_transform to the numeric columns of the complete dataset (impute first with the global median), split afterwards and train the logistic regression. Compare the accuracy with that of the correct pipeline. Is the difference noticeable? Why is this leak less serious than the return_reason one and yet still to be avoided?

Exercise 3. Prepare the demand series of 04-02 for a supervised model: starting from generate_weekly_demand(104, 42), create the columns week_of_year (date.dt.isocalendar().week), month, units_previous_week (units.shift(1)) and mean_4_weeks (rolling mean of the previous 4 weeks, units.shift(1).rolling(4).mean()). Why must shift(1) be used before the rolling mean? What happens with the first rows and what would you do with them?

Solution 1. The matrix goes from 21 to 23 columns: the missing indicator is generated before the OneHotEncoder of the same sub-pipeline, so the latter treats it as one more category and expands it into two columns (nom__missingindicator_payment_method_False/True); if you wanted a single 0/1 column you would have to take the indicator out of the one-hot. Test accuracy stays around 0.87 (the differences are one or two thousandths, within the noise). The median was more prudent because, had the 99,999 gone unnoticed, the mean of amount would have risen from €125 to €159 and every missing amount would have been filled with an inflated value; the median (€107.9) did not move a cent because of the outlier. In general, median for columns with a long tail or outliers, mean for symmetric, clean columns.

Solution 2. The accuracy stays practically the same (around 0.87). With 3,000 rows, the mean and deviation computed with 100 % of the data barely differ from those computed with 75 %, so the effect is imperceptible. It is less serious than return_reason because it does not introduce the label into the features, only a pinch of information about the test distribution. It still has to be avoided because: (a) with small datasets or with outliers in the test set it does distort; (b) it is the sign of a badly assembled workflow, in which other, more serious leaks will go unnoticed; and (c) in production there is no "test set": the scaler has to be fitted and saved in advance, which is exactly what the Pipeline guarantees.

Solution 3. shift(1) moves the series one row down, so that in the row for week t the units of week t−1 appear; without it, rolling(4).mean() would include week t itself, that is, the label to be predicted: a leak. The first rows are left with NaN (week 1 has no previous week; the first 4 have no complete rolling mean). They can be dropped (dropna(), losing 4 weeks out of 104) or imputed inside the pipeline; with a long series, dropping them is the cleanest. The week_of_year column captures seasonality and the lag ones inertia; in 04-04 we will train the linear regression with them and check that the mean error drops a lot compared with the straight line of 04-02.

In this lesson we have turned some "dirty" NovaMarket orders into the numeric matrix an algorithm needs, and along the way we have fixed the preparation techniques: duplicates (remove), missing values (impute with median/mode inside the pipeline), outliers (detect with IQR, decide with business judgement), encoding of categoricals (one-hot for nominal, ordinal with explicit order, frequency or target for many categories; never arbitrary integers), scaling (standardisation for distance- and gradient-based models), dates (day of the week, month, days since…), feature engineering (ratios, per-customer aggregates without peeking at the present, basic text), selection (correlation and importance, which confirmed that the zone contributes nothing) and data leakage (return_reason gave an illusory 100 %). All of it encapsulated in a Pipeline with ColumnTransformer that is fitted only on the training set and that is reproducible and deployable. Marta now has the function dirty_orders to practise with and a pipeline we will reuse in the next three lessons.

With the data prepared, it is time to open the box of algorithms. In the next lesson, Machine Learning Algorithms, we will see what is inside fit: how linear regression fits a straight line to NovaClean demand, how logistic regression turns a weighted sum into a return probability, how k nearest neighbours, trees, forests, support vector machines and k-means decide, with the intuition, a little maths and code for each one, and we will compare several classifiers on these same orders.