# Data Preprocessing Steps: How to Clean Business Data > Learn the data preprocessing steps that turn messy business records into reliable insights, with examples for SMEs. See how ELECTE automates them. Source: https://www.electe.net/post/data-preprocessing-steps Site guide: https://www.electe.net/llms.txt You pulled a sales export that looks finished. Orders, costs, and dates fill the sheet. Then a forecast lands on the finance lead's desk and the average order value has jumped, even though nothing in the catalog changed. That jump is often a preprocessing error, not a change in buying. Filling blank prices while duplicate invoices are still in the file, or scaling the whole extract before you hold any rows back, teaches the model facts it shouldn't see yet. Most write-ups list **data preprocessing steps** as separate techniques you can apply in any order. The order is the part that inflates results. What follows is the sequence I recommend when an analyst owns a live business file. You'll get a running order, the thresholds I treat as starting points rather than laws, and a way to lock each fitted setting so the next data load follows the same path. ## Seven Data Preprocessing Steps in the Right Order A sales export can look complete and still hide duplicate invoice rows, blank unit costs, and dates stored in two formats. Clean those problems in the wrong order and every later average inherits them. **Any step that learns a number from the data must run after the split.** A mean, a median, or a scaling range calculated on the full file lets validation rows influence training. The score then looks stronger than the model will be on next month's orders. StepWhat You DoReady When1. Profile and cleanCheck row counts, data types and null rates; drop exact duplicates; standardize dates and labelsClean base table with one label per category2. Split the dataShuffle the rows, then divide them into training and validation setsTwo sets exist and no learned settings have been calculated yet3. Handle missing valuesDrop rows, drop the feature, or impute using training data onlyEvery field is complete or its gaps are flagged4. Encode and engineer featuresConvert categories to numbers; build ratios, time parts and rolling averagesA model-ready feature table with documented columns5. Treat outliersVerify extreme values, then cap, transform or flag themExtreme values are explained, not guessed6. Scale numeric featuresFit a scaler on training data and apply it to validation dataNumeric columns sit on comparable ranges7. Lock the pipelineSave every fitted transformation so new data follows the same pathA repeatable run that works on each new data load Say a retailer holds 12,000 order lines, including 900 duplicated invoice rows. Imputing missing prices before you drop those duplicates counts the same invoice twice and pulls the average off the price list. > **Practical rule:** Finish the row you are on before you calculate anything the next row needs. Map the file you have open onto those seven rows. The first row you haven't finished is the only one worth touching today. ## Profile and Clean Raw Business Data Profiling is a one-hour pass a manager can finish before anyone opens a model. Keep the raw file untouched and write every change into a cleaning log so a finance lead can see why a total moved. 1. **Count rows and columns, then confirm each type.** A cost stored as text won't sum, and it won't fail loudly. Convert it before you trust a total. 2. **Calculate the null rate on every column.** A column above 30% blank goes to the missing-value rules rather than a quick fill. I would drop a column outright only when blanks pass 50% and the field isn't required for the decision. 3. **Remove exact duplicates, not lookalikes.** Match on invoice number, date, and amount together. A second order with a different line ID is a real sale. Deleting it because the customer and the day match is the slip that understates revenue. 4. **Normalize formats in one pass.** One date pattern, one currency per column, and no stray spaces in codes. Mixed formats split one product into two series and break any later join. 5. **Standardize category labels.** `UK`, `U.K.`, and `United Kingdom` are one market. Four spellings of one product line in a three-year e-commerce export do the same damage. Pick one label and map the rest. Say that export also stores order dates as `2024-03-01` in one year and `01/03/2024` in another. Until both become one pattern, a monthly chart double-counts March or drops it. ### What the log and the privacy cut protect The cleaning log is a short table: column, rule, rows changed, person, date. When finance asks why last March shrank, you point at the duplicate rule instead of rebuilding the file from memory. Before the file reaches any analysis platform, strip customer names, emails, and payment details. You don't need them to forecast demand, and leaving them in the extract creates a privacy problem you can't undo with a model. If you want a second opinion on what "good" looks like for commercial extracts, the [sales data quality insights from Pipecorn](https://pipecorn.com/blog/data-quality-standards) are a useful checklist for identifiers, completeness, and consistency. Use them to decide which columns are worth keeping, then come back to the null-rate cut above. ## Split Data Before Any Learning Happens The split is a fence. Nothing that estimates a statistic belongs on the full file. For supervised text classification, [Google's text-classification guide](https://developers.google.com/machine-learning/guides/text-classification/step-3) recommends shuffling before the split, because keeping the original order can bias both sets. After the shuffle, a typical split puts **80%** of examples in training and **20%** in validation. The same transformations then apply to both sets, but only after they are learned on training rows. ### Why a sorted sales file lies Exports often arrive sorted by date, region, or customer. Split that file from top to bottom and you train on 2023 while you validate on late 2024. The score then tracks the calendar, a promotion calendar, or a single region's mix. It doesn't tell you whether the model understood the orders. Shuffle when each row is an independent case: a closed order, a churned account, a classified ticket. Don't shuffle when the question is a forecast. > Keep time order for forecasting. Train on earlier months and test on later months. A shuffle would let the model see the future and call that skill. ### Text fields need a frequency cut Support tickets and product descriptions explode into thousands of tokens. In the same Google workflow, you discard tokens that appear fewer than **two** times, keep the top **20,000** token features, and rank what remains with an F-test-based importance score. A token that shows up once is usually a typo or a one-off SKU, not a pattern worth a column. After the split, fit every mapping on training data only, then apply that frozen mapping to validation. Recalculating a vocabulary or a category list on the held-out rows is another way to leak the answer into the prep. ## Fill Missing Values Without Skewing Results How often a field is blank, and whether the decision needs that field, picks the action. The bands below are a working rule of thumb, not a law. A regulated risk model may refuse to drop rows that a retail forecast can spare. Share MissingFeature ImportanceActionUnder 5%AnyDrop those rows if enough data remains5% to 30%LowDrop the feature5% to 30%HighImpute from training rows onlyOver 30%HighImpute, add a missing-flag column, and investigate why the field is blankOver 30%LowDrop the feature A handful of blank costs on an otherwise complete order file is the first row. Deleting those lines hurts less than inventing a price. A rarely used survey field in the middle band isn't worth a filled-in guess, so the column goes. Tenure, which you use for churn, stays, and you estimate it. [Google's clustering guidance on preparing data](https://developers.google.com/machine-learning/clustering/prepare-data) names three practical options: remove examples that contain missing values, remove the feature, or predict the missing values from other data. Rare gaps can justify deleting rows. Frequent gaps usually mean you drop the feature or impute it. ### Predict the gap instead of pasting a constant For a numeric field, train a regression on rows where the value exists, then estimate the blanks. Missing customer age, for example, can be estimated from income and tenure rather than replaced with one company-wide average. That uses relationships you already trust. Order value is skewed. A few large wholesale tickets pull the **mean** up, so a mean fill makes ordinary baskets look richer than they are. The **median** sits with the typical order and moves less when one bulk invoice lands. Compare summary statistics before and after the fill. A large shift means the method is rewriting the business, not patching a hole. On categories, label the blank `Unknown`. The gap is often the signal: a checkout that never captured region, or a product with no supplier code. Turning that into a real level keeps the row and shows the model that the field was empty on purpose. Frequent blanks aren't rare in operations. One 2025 study of incomplete water-consumption data describes a building dataset of **33,888** hourly records from February 2019 through December 2023, with missing values on **20%** of the hours, which sits in the band where a blanket row drop would throw away too much history ([the study](https://arxiv.org/html/2506.08882v1)). Fit the imputer on training rows only. [ELECTE's guide to missing data](https://www.electe.net/post/missing-data-imputation) walks through imputation choices in more detail if you want a second pass on method, not on order. ## Encode Categories and Engineer Useful Features Two jobs sit here. Turn labels into numbers the model can use, and build columns that answer a business question the raw fields don't. ### Encoding without a fake ranking **One-hot encoding** fits categories with few values, such as five sales regions. Each region becomes its own yes-or-no column, and no region is "larger" than another. **Ordinal encoding** fits a real order, such as sizes S, M, and L. The numbers match a ranking the business already uses. Don't assign 1, 2, 3 to unordered names like channel or city. The model will treat a higher code as more of something. That false ranking shows up later as a confident, wrong coefficient. When a field has hundreds of levels, one-hot creates a very wide table. Group rare levels into `Other` before you encode, and learn that grouping on training rows only. ### Columns that answer a business question Build a short list tied to one decision, not a dump of every ratio you can spell. - **Margin percentage:** `(price − cost) / price` tells you whether a promotion still clears cost, which revenue alone can't. - **Days since last purchase:** a churn review needs recency, not another copy of the customer ID. - **Day of week and month:** demand planning cares about Friday spikes and seasonal months hidden inside a full timestamp. - **Rolling 7-day average:** daily units jump around. A week average shows whether demand is rising. - **Stock-to-sales ratio:** an inventory manager sees a shortage forming earlier than a raw on-hand count allows. I would start with ten to fifteen features tied to that one decision. Drop a pair when the columns correlate above 0.95, because the second column repeats the first. Write the business meaning next to the column name in the data dictionary. An encoding map or a rolling average learned on the full file, including rows you meant to hold out, repeats the leak you already fenced off at the split. ## Scale Numbers and Tame Outliers An extreme value isn't automatically a mistake. A 40-unit order from a B2B account is a real sale. Flag it so it doesn't dominate a mean, and leave it in the history. A unit price of 100,000 that was typed instead of 1,000 is an entry error. Fix the digit, don't "treat" it as demand. ### Check the extreme before you delete it Two checks work well together. The **1.5 × interquartile range** rule marks points far from the middle of the column. A domain check asks whether the value can exist: negative stock, a date in the next decade, a discount above the selling price. Verify first. Then cap at the 1st and 99th percentiles, apply a log transform to skewed revenue, or add a flag and keep the raw number for reporting. > A bulk order you delete is revenue you will never forecast again. A typo you keep becomes a price the model thinks customers will pay. Rerun the summary after capping. The center of the column should barely move. If the median jumps, the cap is doing too much work and you should look at the source rows again. ### Which scaler matches the profile Scaling stops a column in large units, such as annual revenue, from drowning a column in small units, such as units per order. [Scikit-learn's preprocessing documentation](https://scikit-learn.org/stable/modules/preprocessing.html) notes that **StandardScaler** centers each feature on the training-set mean and scales by variance, and that this can perform poorly when many outliers are present. **RobustScaler** is the drop-in that uses more resistant estimates of center and range. Data ProfileScalerWhy It FitsRoughly symmetric, few extreme valuesStandardScalerCenters on the mean and scales by varianceHeavy outliers, such as occasional bulk ordersRobustScalerUses median and interquartile range, which resist extremesValues need a fixed range, such as ratings from 1 to 5MinMaxScalerMaps values to 0-1 without changing their shape Fit the scaler on training data only, then apply that fit to validation. Refitting on the held-out set lets extreme validation rows set the scale. For a closer look at spotting extremes before you choose a treatment, see [ELECTE’s guide to outliers](https://www.electe.net/post/outlier-statistica). ## Automate the Pipeline and Check Its Outputs A cleaned file stored in one notebook can drift between runs. A new file may get a different imputation value, category encoding, or scale, making a dashboard change even when the underlying business has not. A pipeline keeps the fitted imputer, encoder, and scaler together, then applies the same transformations to new files. Fit those steps using the training partition only. Apply the fitted settings to validation and test data without recalculating them. [IBM's note on data leakage](https://www.ibm.com/think/topics/data-leakage-machine-learning) explains why fitting preprocessing steps across training and test data can leak information into the evaluation. ### Checks after every run A successful job only confirms that the process ran. Check whether the output still matches the source and the rules you set: - **Row counts:** reconcile the loaded file with the export, accounting for logged duplicates. - **Null rate per column:** investigate a sudden increase, which may mean a source field stopped arriving. - **Value ranges:** check that prices, quantities, and dates remain within accepted bounds. - **New categories:** route an unseen region or SKU to a defined bucket rather than dropping it without notice. For [practical examples of data validation](https://www.electe.net/post/data-validation-techniques), review how these checks apply to business files. Treat automated outputs as estimates, and review the first few cycles manually before using them to trigger alerts. **ELECTE**, an AI-powered data analytics platform for growing companies, connects to accounting software or an uploaded file and runs data analysis and forecasting without requiring an analyst. Customers save 10+ hours per week by automating financial analysis. That help depends on using fitted steps you can defend in a review, rather than recalculating them on every upload. This week, take one dataset through the seven-row table and mark which steps are still manual. Automate a repeated step, but keep judgment calls, such as a duplicate versus a real reorder or a typo versus a bulk order, under review until the rules are stable. --- ELECTE connects your accounting data or a file upload, prepares it, and turns it into forecasts and reports you can review with your team. If you want those data preprocessing steps applied the same way on every load, visit [ELECTE](https://www.electe.net) and see how the platform runs the path on your own numbers.