LEARNING SECTION 01
What is data transformation?
Data transformation changes the representation, structure or scale of observations so that they answer a defined analytical question. A transaction file can become a monthly sales panel; share prices can become returns; differently sized companies can become comparable through ratios. The transformation should preserve the information needed for the decision.
This guide follows data understanding and outlier management. First establish what the observations mean and resolve data-quality issues. Then decide which representation fits the model, calculation or business question. Transformation is not a substitute for correcting a misplaced decimal or understanding a corporate action.
LEARNING SECTION 02
One word, several different operations
| Operation | Question answered | Illustration |
|---|---|---|
| Cleaning | Is the observation trustworthy? | Reconcile duplicate trade records |
| Format / unit standardisation | Do observations use the same convention? | Convert ₹ lakh to ₹ crore |
| Aggregation | What happened at the required level? | Orders → store-month revenue |
| Reshaping | Which layout suits the analysis? | Long transaction table → region-by-month matrix |
| Feature scaling | Are numerical magnitudes comparable for this model? | Centre and scale borrower features |
| Mathematical transformation | Which functional form is useful? | Log revenue or Box–Cox transform |
| Encoding / engineering | How can the model use available information? | Sector indicators, lagged returns and margins |
“Standardise dates” and “standardise a feature to zero mean” mean different things. “Normalisation” is also used loosely: min–max scaling changes a range, while vector normalisation changes a row’s length. Neither automatically creates a normal distribution.
LEARNING SECTION 03
Your interactive Data Transformation Lab
Use the companion application to compare methods interactively. It opens in a new tab so this guide can remain visible alongside it.
THE MOUNTAIN PATH · PRACTICAL APPLICATION
Data Transformation for Financial Analytics
Explore the full sequence from cleaning and reshaping to scaling, transformation and feature engineering.
Launch Data Transformation Lab ↗| App module | What to explore |
|---|---|
| 1–4 · Cleaning, Standardize, Aggregate, Pivot | Compare raw observations with consistent formats and business summaries |
| 5–7 · Min-Max, Z-Score, Robust Scaling | See how alternative scales change numerical representations |
| 8–9 · Log and Box-Cox | Compare functional transformations and their input domains |
| 10–12 · Binning, Categorical Encode, Feature Engineering | Turn raw fields into interpretable analytical inputs |
| 13 · FreshMart Caselet | An integrated retail-data cleaning and transformation exercise |
| 14 · Python Case Study | Connect the workflow to a Python implementation |
LEARNING SECTION 04
Begin with consistent formats, units and keys
Before applying a statistical transform, make dates, units, identifiers and categories consistent. Otherwise the model can confuse formatting differences with economic differences. Preserve the raw field alongside the standardised value and the transformation rule.
| Raw field | Transformation | Control |
|---|---|---|
| " North ", "NORTH", "north" | Trim whitespace and map to one category | Use a documented mapping; do not merge genuinely distinct entities |
| ₹250 lakh | 250 / 100 = ₹2.50 crore | Record source unit before conversion |
| USD 1,000 at illustrative ₹83/USD | ₹83,000 | Choose a rate appropriate to valuation date and purpose |
| Text date 03/04/2026 | Parse using a declared day/month convention | Do not let locale silently choose the date |
| Security / customer identifier | Retain as stable text key | Do not average it or discard leading zeros |
One row must represent a defined entity or event. The key for a transaction table differs from the key for a company-year panel. Check duplicates and join cardinality before summarising: a many-to-many join can multiply revenue without creating any business activity.
LEARNING SECTION 05
Aggregation: preserve the meaning of stocks, flows and rates
| Variable | Usual operation for the stated question | Why |
|---|---|---|
| Revenue flow | Sum non-overlapping periods | Monthly contributions form the quarter |
| Debt outstanding | Period-end value or a defined average | A stock is measured at a point in time |
| Portfolio return | Beginning-value-weighted constituent return | Unequal positions require unequal weights |
| Price-series return through time | Compound simple returns | Wealth evolves multiplicatively |
| OHLCV bars | First open; max high; min low; last close; sum volume | Fields represent different session properties |
| Margin / yield / ratio | Recompute from totals or use justified weights | An unweighted average may answer the wrong question |
Store A earns ₹10 on ₹100 revenue; Store B earns ₹20 on ₹400. Their margins are 10% and 5%. The combined margin is (10 + 20)/(100 + 400) = 6%, rather than the simple average of 7.5%. Aggregation choices determine the economic question you answer.
LEARNING SECTION 06
Pivot and unpivot: structure without inventing observations
Long data store repeated observations in rows, such as region, month and revenue. Wide data put months or instruments in separate columns. Long format often suits joins and grouped calculations; wide format often suits comparison tables and aligned return matrices.
| Region | January revenue | February revenue |
|---|---|---|
| North | 100 | 120 |
| South | 80 | 90 |
| Total | 180 | 210 |
This matrix could come from four unique region-month records. If multiple records share a region and month, a pivot needs an explicit aggregation rule. Unpivoting reverses the layout into region-month-value rows; it does not recover information lost during aggregation.
LEARNING SECTION 07
When does feature scaling matter?
Distance-based methods can treat a ₹10,000 income difference as numerically larger than a 0.10 leverage-ratio difference simply because the units differ. Scaling sets a comparison convention; it does not decide which variable is economically important.
| Method / setting | Why consider scaling? | Qualification |
|---|---|---|
| KNN, clustering and distance-based anomaly screens | Distances depend on feature magnitudes | Choose and validate the scale and metric |
| Penalised linear models | Penalties act on coefficient magnitudes | Feature units affect the effective penalty |
| Neural networks and gradient-based optimisation | Scale can affect numerical optimisation | Architecture and optimisation choices also matter |
| Unpenalised OLS with intercept | Can improve numerical conditioning | Invertible linear rescaling alone does not change fitted values |
| Ordinary axis-aligned decision trees | Often less sensitive to positive affine rescaling | Do not assume every model or transform is unaffected |
Choose scaling using the training data and the intended model. Compare predictive performance, stability and interpretation; do not choose the method solely because the resulting chart looks more symmetric.
LEARNING SECTION 08
Min–max scaling: change the range
Inverse: x = minimum + xscaled × (maximum − minimum)
Use five synthetic company revenues, in ₹ crore: 10, 20, 30, 40, 100. Training minimum = 10 and maximum = 100. Revenue 30 maps to (30 − 10)/(100 − 10) = 0.2222.
| Revenue · ₹ crore | Min–max scaled |
|---|---|
| 10 | 0.0000 |
| 20 | 0.1111 |
| 30 | 0.2222 |
| 40 | 0.3333 |
| 100 | 1.0000 |
The fitted training sample occupies [0,1]. A later revenue of 120 maps to 1.2222 using those same bounds. This is valid extrapolation, not a calculation error. Clipping future values to [0,1] is an additional modelling decision that discards magnitude information.
LEARNING SECTION 09
Z-score scaling: centre and scale
Inverse: x = mean + z × standard deviation
For revenues 10, 20, 30, 40 and 100, mean = 40. Using sample SD, s = √(5,000/4) = 35.3553. Revenue 30 has z = (30 − 40)/35.3553 = −0.2828. The training scores have mean zero and sample variance one, apart from rounding.
Population SD for the same observations is √(5,000/5) = 31.6228, producing z = −0.3162. This article’s hand calculations, Excel recipe and scaling calculator use sample SD. scikit-learn StandardScaler uses population-style variance (ddof = 0); match the convention before comparing outputs.
LEARNING SECTION 10
Robust scaling: use median and IQR
IQR = Q3 − Q1
Using inclusive quartiles for the five revenues, median = 30, Q1 = 20, Q3 = 40 and IQR = 20. Revenue 30 maps to zero. Revenue 100 maps to (100 − 30)/20 = 3.5000.
Median and quartile estimates are less sensitive to a few extremes than mean and SD. However, robust scaling does not remove or cap an extreme observation: 100 remains the most distant observation on the new scale. If the IQR is zero, the stated formula is undefined.
LEARNING SECTION 11
Compare all three scales on one dataset
| Revenue · ₹ crore | Min–max | Sample Z-score | Median/IQR |
|---|---|---|---|
| 10 | 0.0000 | -0.8485 | -1.0000 |
| 20 | 0.1111 | -0.5657 | -0.5000 |
| 30 | 0.2222 | -0.2828 | 0.0000 |
| 40 | 0.3333 | 0.0000 | 0.5000 |
| 100 | 1.0000 | 1.6971 | 3.5000 |
All three operations are increasing affine transformations when their scale denominator is positive. They preserve observation order and do not compress the upper tail relative to the centre in a nonlinear way. Their centres and denominators differ, which matters when combining features.
LEARNING SECTION 12
Log transformations: compress positive magnitudes
The natural logarithms of 10, 20, 30, 40 and 100 are 2.3026, 2.9957, 3.4012, 3.6889 and 4.6052. A doubling adds ln(2) ≈ 0.6931 regardless of starting size. Logs therefore turn multiplicative relationships into additive ones.
A log transform can help with positive right-skewed variables such as revenue or assets. It does not guarantee normality or stationarity. Logging a share-price level is also different from calculating its log return: the return is the change in the log level.
LEARNING SECTION 13
From prices to differences, growth rates and log returns
Simple return: rₜ = Pₜ/Pₜ₋₁ − 1
Log return: gₜ = ln(Pₜ/Pₜ₋₁) = ln(1 + rₜ)
A price move from 100 to 110 gives a difference of 10, a simple return of 10% and a log return of 9.5310%. A later move from 110 back to 100 gives −9.0909% simple return. Compounding the two returns gives zero; their arithmetic sum does not.
Use dividend and corporate-action conventions consistently. Sum log returns over compatible intervals, or compound simple returns. For portfolio returns within a period, use value-weighted simple returns rather than an average of log returns.
LEARNING SECTION 14
Box–Cox and Yeo–Johnson: choose the domain carefully
y = (x^λ − 1)/λ, if λ ≠ 0
y = ln(x), if λ = 0
For x = 25 and λ = 0.5, y = (√25 − 1)/0.5 = 8. For λ = 1, y = x − 1. For λ = 0, use the log limit. The inverse for non-zero λ is x = (λy + 1)^(1/λ), on its valid domain.
Box–Cox estimates a useful power parameter under a chosen criterion; it does not guarantee a normal population or eliminate model misspecification. The conventional positive-input formula shown here must not be applied blindly to losses or negative earnings.
Yeo–Johnson extends power transformations to zero and negative observations using separate branches. It can be useful for signed features, but interpretation and training-only estimation still matter. Some software applies standardisation after a power transform by default; a raw Box–Cox output and a standardised output are not directly comparable.
LEARNING SECTION 15
Binning: turn continuous values into defined groups
Binning groups numerical observations into intervals. Equal-width bins divide a numerical range; quantile bins seek similar observation counts; business-defined bins use meaningful thresholds. The choice changes what the model can learn.
| Illustrative revenue group | Boundary rule | Examples · ₹ crore |
|---|---|---|
| Small | x < 25 | 10, 20 |
| Medium | 25 ≤ x < 50 | 30, 40 |
| Large | x ≥ 50 | 100 |
State inclusivity at the boundaries: a value exactly 25 belongs to Medium here. Quantile bins can have unequal counts when values are tied. A threshold learned from the full future sample leaks information; fit data-dependent bins on training history.
LEARNING SECTION 16
Encode categories without inventing numerical distances
| Encoding | Example | What to watch |
|---|---|---|
| One-hot indicators | North = 1 or 0; South = 1 or 0 | For OLS with an intercept, omit a reference category to avoid exact collinearity |
| Ordinal mapping | Documented ordered risk bands | A numerical code does not establish equal economic spacing |
| Frequency encoding | Share of training records in each category | Learn counts on training data; define unseen-category treatment |
| Target encoding | Category-level training outcome estimate | Needs leakage-safe cross-fitting and smoothing |
Store type and industry are usually nominal categories. Coding North = 1, South = 2 and West = 3 can imply an arbitrary order in a linear or distance-based model. Missing and unseen categories need explicit treatment; they should not silently become a familiar category.
Even if ratings are ordered, moving one grade is not necessarily an equal change in default probability. Choose a representation that fits the objective and available evidence.
LEARNING SECTION 17
Feature engineering: create financially meaningful inputs
| Feature | Definition | Timing control |
|---|---|---|
| Operating margin | Operating profit / revenue | Use a consistent scope; handle zero revenue |
| Leverage | A defined debt / assets or debt / equity measure | State numerator, denominator and reporting period |
| Lagged return | Prior period’s observed return | At date t, lag must already be known |
| Rolling volatility | SD of a defined past return window | Exclude future data and state minimum window |
| Growth | Current / comparable prior value − 1 | Check base values, seasonality and restatements |
| Calendar indicator | Month-end, weekday or event flag | Use a documented calendar, not future labels |
A ratio can reduce firm-size effects but introduces a denominator problem. A very large P/E may reflect tiny earnings, not a price-record error. A negative equity denominator can reverse the usual interpretation of debt/equity. Flags and domain definitions belong beside engineered features.
LEARNING SECTION 18
Economic transformations: inflation, currency and common bases
Real return = (1 + nominal return)/(1 + inflation) − 1
If nominal revenue is ₹120 crore and the price index is 125 with a base of 100, real revenue at base prices is 120 × 100/125 = ₹96 crore. If nominal investment return is 10% and inflation is 6%, real return is 1.10/1.06 − 1 = 3.7736%, rather than exactly 4%.
Currency conversion, inflation adjustment and statistical scaling solve different problems. Choose exchange-rate dates and price indices that fit the cash flows and question. A market-cap-weighted index and an equal-weighted index also describe different portfolios.
LEARNING SECTION 19
Differencing and seasonal adjustment are not feature scaling
Seasonal difference with period s: xₜ − xₜ₋s
Differencing changes levels into changes. A quarterly revenue series may need comparison with the same quarter a year earlier; a daily price series may need return construction. Neither Z-score scaling nor a log transform guarantees that a series becomes stationary.
A moving average can smooth noise but adds lag and changes the series. Seasonal adjustment estimates a component; it is not simply deleting calendar labels. Use plots, subject knowledge and appropriate diagnostics before selecting the time-series representation.
LEARNING SECTION 20
How does transformation change model interpretation?
| Regression form | Interpretation of β, with other inputs fixed | Qualification |
|---|---|---|
| Y = α + βX | One X-unit increase changes fitted Y by β units | Units matter |
| ln(Y) = α + βX | One X-unit increase changes fitted conditional median by exp(β) − 1 proportionally, under the usual log-error assumptions | Mean prediction requires additional treatment |
| Y = α + β ln(X) | A 1% X increase changes fitted Y by β × ln(1.01) units | Approximately 0.01β |
| ln(Y) = α + β ln(X) | β is an elasticity | A 1% change is a local approximation; exact finite effect is (1.01)^β − 1 |
Report coefficients with the transformation and units. A standardised coefficient refers to changes on the chosen scale. After an invertible transform, return predictions to the units of the business decision before evaluating economic consequences.
LEARNING SECTION 21
Inverse transformations: recover units without hiding bias
The inverse of a log prediction returns a positive value, but it does not automatically recover the expected value on the original scale. In general, E[exp(Y)] differs from exp(E[Y]).
Median(X) = exp(μ)
Mean(X) = exp(μ + σ²/2)
For a conditional log forecast μ = ln(100) and log-error variance 0.04, the back-transformed median is 100; the mean under that model is 100 × exp(0.02) = 102.0201. The correction follows the stated distributional assumptions and cannot be copied automatically to every model.
Store training means, scales, category mappings, bin edges and power parameters with the model. Predictions and later datasets must use the same fitted transforms. Retain the original unit and a clear inverse path wherever the transform is invertible.
LEARNING SECTION 22
Fit on training data; apply consistently to later data
Split the data according to the prediction setting before estimating preprocessing parameters. Fit scaling, imputation, binning and feature-selection choices inside each training fold. Apply the fitted rules unchanged to validation or test observations.
For the revenue sample, training bounds 10 and 100 map a later value 120 to 1.2222. Recomputing bounds with 120 included produces a different historical scale and uses information unavailable at training time. This can make a backtest look more stable than an implementable process.
LEARNING SECTION 23
Excel workshop: reproduce the scaling and power calculations
Create a Transform sheet. Enter 10, 20, 30, 40 and 100 in B2:B6, in ₹ crore. Enter labels for the reference statistics beside the following cells. These recipes use the complete five-row teaching sample; a prediction task would fit these cells on training rows only.
| Cell / range | Formula | Purpose |
|---|---|---|
| J2 | =MIN(B2:B6) | Training minimum |
| J3 | =MAX(B2:B6) | Training maximum |
| J4 | =AVERAGE(B2:B6) | Training mean |
| J5 | =STDEV.S(B2:B6) | Sample SD |
| J6 | =MEDIAN(B2:B6) | Median |
| J7 | =QUARTILE.INC(B2:B6,3)-QUARTILE.INC(B2:B6,1) | Inclusive IQR |
| J8 | 0.5 | Box–Cox λ assumption |
| C2; fill down | =IF($J$3=$J$2,NA(),(B2-$J$2)/($J$3-$J$2)) | Min–max |
| D2; fill down | =IF($J$5=0,NA(),(B2-$J$4)/$J$5) | Sample Z-score |
| E2; fill down | =IF($J$7=0,NA(),(B2-$J$6)/$J$7) | Median/IQR |
| F2; fill down | =IF(B2<=0,NA(),LN(B2)) | Natural log |
| G2; fill down | =IF(B2<=0,NA(),IF($J$8=0,LN(B2),(B2^$J$8-1)/$J$8)) | Raw Box–Cox |
| H2; fill down | =IF(B2<25,"Small",IF(B2<50,"Medium","Large")) | Defined revenue bins |
Use plain numerical formatting for scaled values, not percentage formatting. For a population-SD comparison, use STDEV.P instead of STDEV.S. Keep units in the headers and retain the unchanged original column.
LEARNING SECTION 24
Two quick calculators to test your understanding
Calculator 1 compares scaling and logarithms. Enter 3–100 finite values in consistent units; use a separate observation to see how the fitted sample maps a new value. Sample SD and inclusive quartiles match the article’s worked case.
Calculator 2 illustrates the raw positive-input Box–Cox formula. λ is supplied by you, not statistically estimated here. No subsequent Z-score scaling is applied.
LEARNING SECTION 25
Revision questions and answers
Does transformation repair an incorrect raw value?
No. Resolve data quality first. A log of a wrong price is still based on a wrong price.
What is min–max scaling for revenue 30 in the worked sample?
(30 − 10)/(100 − 10) = 0.2222.
Can a later min–max value exceed one?
Yes. Applying the fitted training bounds to 120 gives 1.2222. Clipping is a separate decision.
Why can Excel and StandardScaler give different Z-scores?
STDEV.S uses n − 1; StandardScaler uses population-style variance. For 30, the worked sample gives −0.2828 with sample SD and −0.3162 with population SD.
Does Z-score scaling make data normally distributed?
No. It changes centre and scale, not distribution shape.
What is the robust-scaled value of 100?
(100 − 30)/20 = 3.5 using the worked median and inclusive IQR.
Why can we not log a loss of −10 directly?
The real-valued natural logarithm requires a positive input. Select a representation appropriate to signed values rather than adding an unexplained constant.
What is Box–Cox at x = 25 and λ = 0.5?
8. The inverse (0.5 × 8 + 1)^2 returns 25.
Why is combined margin 6%, rather than 7.5%, in the store example?
Profit totals 30 and revenue totals 500. The ratio of totals incorporates the different store sizes.
What is the difference between pivoting and aggregation?
Pivoting changes layout. If keys are repeated, an aggregation rule is needed and information can be lost.
Why not code regions as 1, 2 and 3?
A linear or distance model can infer an arbitrary ordering and spacing. One-hot encoding is often more appropriate for nominal categories.
Why fit transformations inside each training fold?
The validation observations must not influence the reference statistics used to simulate an unseen prediction.
Is exp(log prediction) necessarily the original-scale mean?
No. Under a lognormal model it gives a median; recovering the mean requires the variance correction under that model.
Where should I practise the complete workflow?
Open the linked Streamlit application. Its FreshMart case and Python case study connect the individual transformation modules.
LEARNING SECTION 26
Sources and connected learning
All worked datasets are original synthetic teaching examples. The application module list was checked on 2 November 2026. Consult library documentation for exact parameter defaults when reproducing calculations in software.
- Prof. V. Ravichandran · Data Transformation for Financial Analytics Lab — 14 interactive modules.
- scikit-learn · Preprocessing Data — scaling and transformation definitions.
- StandardScaler API — variance convention and fitted parameters.
- PowerTransformer API — Box–Cox, Yeo–Johnson and default standardisation.
- scikit-learn · Common Pitfalls — consistent preprocessing and leakage.
- Hyndman and Athanasopoulos · Forecasting: Principles and Practice — transformations and adjustments.