Data cleaning and preprocessing

Part 1, from Lecture 11

What the columns mean: attribute types and data quality

Central question: what do the columns mean, and which comparisons and summaries are meaningful? By the end of this part you should be able to classify an attribute by what its values represent, justify the summaries that suit it, and judge whether data are suitable by first stating the use they must serve.

1.1From SQL to pandas

Module 1 stored data in a database and queried it with SQL. From here on we analyse tables in Python with pandas. Three tools are involved:

• SQLite is the database engine: it stores tables in a single file (here demo.db) and runs SQL queries. Python's built-in module sqlite3 lets Python talk to it.
• pandas is a Python library for inspecting, cleaning, transforming and analysing tabular data. By convention it is imported as pd.
• A DataFrame is pandas' table: two-dimensional, held in memory in your Python session, with labelled rows (the index) and labelled columns. Each row is a record (one object, such as a patient) and each column is an attribute. The name df is just a conventional variable name, not a keyword.

Suppose a SQLite table Data has one column Attr, declared INTEGER, holding 1, NULL and 3. Loading the result of a query into pandas looks like this:

import sqlite3
import pandas as pd

conn = sqlite3.connect("demo.db")       # an open link to the database file
sql = "SELECT Attr FROM Data"            # the query, as a string
df = pd.read_sql_query(sql, conn)        # run it; the result becomes a DataFrame

print(df)
print(df.dtypes)          # how each column is stored
print(df.isna().sum())    # how many entries pandas recognises as missing, per column

   Attr
0   1.0
1   NaN
2   3.0
Attr    float64
dtype: object
Attr    1
dtype: int64

Four things in that output need explaining.

• The 0, 1, 2 on the left are the row index that pandas assigns. They are labels, not data.
• SQL NULL became NaN (“not a number”), which is pandas' default marker for a missing number.
• The column is float64, not an integer type, so 1 and 3 print as 1.0 and 3.0. The reason is below.
• isna() produces a True/False table marking each recognised missing entry; .sum() counts the Trues per column (True counts as 1). Here: one.

df2 = pd.read_sql_query(sql, conn, dtype_backend="numpy_nullable")
print(df2); print(df2.dtypes)

   Attr
0     1
1  <NA>
2     3
Attr    Int64
dtype: object

Finally, df is a copy held in Python. Changing it does not change the database.

1.2Metadata: why the digits are not enough

Look again at the patient file. Each “field” is a column. What must an analyst know before using these columns?

The digits themselves cannot tell you:

• whether a column identifies patients, records a category, or measures a quantity (is 012 a code or the number twelve?);
• what unit a measurement is in (is 33.5 kilograms, a percentage, a temperature?);
• whether a value such as 0 is a real measurement or a code, for example for “not measured”.

1.3Meaning versus storage

Employee ID and age can both be stored as integers, and software will happily average either. Would both averages tell you something?

Average age describes the employees. Average ID does not: IDs are labels, and if the company reassigned every ID, the “average ID” would change while nothing about any employee changed. A calculation is only meaningful if its result depends on the property, not on the arbitrary way the property was written down.

Five terms keep these layers apart. Read the table top to bottom: it goes from the thing in the world to the bits in the computer.

The attribute and measurement scale explain what the values mean. The stored type explains how software holds them. A stored type determines which operations software can run; meaning determines which results you can interpret.

1.4Attribute types

Attribute types classify what the values represent, so that we can decide which comparisons and summaries are meaningful. The classification (from Han, Pei and Tong) is:

Nominal

A nominal attribute's values are names of categories or states, with no meaningful order. Examples: hair colour (black, brown, blond, red, auburn, grey, white); marital status (single, married, divorced, widowed). Numbers can serve as nominal labels: a customer ID or a zip code. Subtracting two customer IDs or adding two zip codes measures nothing, because the digits only name.

• Meaningful comparison: same category or different.
• Meaningful summaries: the count (frequency) of each category, and the mode, the most frequent category.

An average hair colour or an average zip code describes nothing.

Binary

A binary attribute is nominal with exactly two states, often coded 0 and 1. Examples: smoker (1 = smokes, 0 = does not); a medical test (1 = positive, 0 = negative). The codes are labels, not quantities.

Meaningful summaries are the count of each state and the proportion in each state. There is one useful coincidence: the mean of a 0/1 column equals the proportion of 1s (five smokers among 20 patients: the mean of the column is 5/20 = 0.25, the share who smoke). That is the only meaningful reading of such a mean.

Ordinal

An ordinal attribute has values with a meaningful order, but the size of the difference between successive values is unknown. Drink size small < medium < large: large is bigger than medium, but not by a known amount. Customer satisfaction coded 1 (very dissatisfied) to 5 (very satisfied): the codes record order; they do not establish that the step from 1 to 2 equals the step from 4 to 5.

• Meaningful comparisons: same or different; before or after in the order.
• Meaningful summaries: counts per level, the mode, and the median (the middle value once the values are put in order).

Numeric: interval-scaled

A numeric attribute is a measurable quantity, recorded as an integer or real number. It is interval-scaled or ratio-scaled.

An interval-scaled attribute is measured in equal-size units, so differences are meaningful, but its zero is an arbitrary reference point. Temperature in °C: 20 °C is five degrees warmer than 15 °C, but 0 °C does not mean “no temperature”, so 10 °C is not “twice as warm” as 5 °C. Calendar year: 2012 and 2020 are eight years apart, but year 0 is not the beginning of time.

Meaningful summaries: the mode, the median and the mean. Equal units are what make averaging meaningful.

Numeric: ratio-scaled

A ratio-scaled attribute is numeric with an inherent zero: zero means none of the quantity. Both differences and ratios are meaningful. Examples: elapsed time (0 s means no time has passed, so 10 s is twice as long as 5 s); counts such as years of experience or the number of words in a document; weight, height, money; temperature in kelvin (0 K is where thermal motion ceases in the classical picture, so a ratio of kelvin readings compares absolute temperatures, though not how warm conditions feel).

Meaningful summaries: everything allowed for interval attributes, plus ratio statements such as “twice as long” or “100 times as much money”.

1.5Why the rules are what they are

The lists of “meaningful summaries” above can look like arbitrary rules to memorise. They are not. They all follow from one test.

1.6Discrete and continuous

This is a separate distinction that applies on top of the types above; it is not a fifth type.

• A discrete attribute has a finite or countably infinite set of possible values. Finite: hair colour, smoker, drink size. Countably infinite (you can list them 1, 2, 3, … forever): customer ID, zip code, number of words in a document.
• A continuous attribute is not discrete: its values can lie anywhere in a continuum, such as height or weight. In practice a computer stores them with finitely many digits, but the quantity itself is continuous.

Nominal and binary attributes are always discrete; numeric ones can be either. How you record something matters: time since birth is continuous, but age recorded in completed years is discrete.

1.7Types at a glance

Read the comparisons first: a median needs order; a mean needs equal units. The type decides which summaries are legitimate; the question being asked, and how the values are spread, decide which legitimate summary is most useful (Part 2).

1.8Data quality: fit for which use?

Knowing what a column means does not guarantee that its values can answer your question. Two situations:

• A manager wants to compare sales of items advertised as on sale with those that were not, but advertising status was never recorded.
• Sales staff need to interpret company records but do not understand the accounting codes.

Neither task can be completed: data must contain the required information and be understandable to the people using them.

Six dimensions make those requirements concrete:

Reading: Han, Pei and Tong, Data Mining: Concepts and Techniques §2.1, §2.4. Tan, Steinbach, Karpatne and Kumar, Introduction to Data Mining ch. 2, §2.1.1, §2.2. Badia, SQL for Data Science §1.1, §1.3, §6.2.

Part 2, from Lecture 12

Profiling a table, describing it, and flagging what looks unusual

Lecture 11 was about what columns mean. This part is about what is in them. By the end you should be able to read a first profile of a table and record possible problems without repairing anything; describe the centre and spread of a numeric attribute with the mean, median, quartiles and interquartile range; and flag potential outliers with the 1.5 × IQR rule while explaining what a flag does not establish.

2.1Data profiling

Back to the patient file. We are now told that field 1 holds identification numbers. So field 1 identifies patients; it does not measure anything about them. Which summaries of field 1 are meaningful? A mean of field 1 describes nothing. The meaningful checks are how many patients there are and whether any identifier repeats. What a column identifies decides which checks make sense.

A profile answers six questions. The first five are answered from the data; the sixth needs the metadata.

Here is the profile of the three printed rows. The file is read from text; dtype={"field1": str} tells pandas to keep field 1 as text, for a reason explained just after.

import io
import pandas as pd

csv = """field1,field2,field3,field4,field5
012,232,33.5,0,10.7
020,121,16.9,2,210.1
027,165,24.0,0,427.6"""
df = pd.read_csv(io.StringIO(csv), dtype={"field1": str})

print(df.dtypes)
print(df.isna().sum())
print(df["field4"].value_counts())
print(df.describe().loc[["count", "min", "max"]])

field1        str
field2      int64
field3    float64
field4      int64
field5    float64
dtype: object

field1    0
field2    0
field3    0
field4    0
field5    0
dtype: int64

field4
0    2
2    1
Name: count, dtype: int64

       field2  field3  field4  field5
count     3.0     3.0     3.0     3.0
min     121.0    16.9     0.0    10.7
max     232.0    33.5     2.0   427.6

Reading this output:

• Field 1 is stored as text (pandas 3 prints str; older versions print object), so describe(), which summarises numeric columns, leaves it out. That is correct for an identifier.
• No entries are recognised as missing. That does not show that every measurement was recorded: pandas recognises only its own markers, and field 4's zeros are ordinary integers to it.
• Field 4 has minimum 0, and the value 0 occurs twice. The documentation (received later) says field 4's valid values are 1 to 10 and 0 is the missing-value code. So this is the profile raising a question, not answering it.

Rules to record in words: the field-4 rule (valid 1–10; 0 = missing), and “field 1 values must be distinct”, because it is an identifier.

bad = pd.read_csv(io.StringIO(csv))     # no dtype given
print(bad["field1"].tolist(), bad["field1"].dtype)
print(df["field1"].is_unique)           # the identifier check, on the text version

[12, 20, 27] int64
True

2.2What a profile can and cannot establish

A profile establishes how columns are stored, which entries pandas recognises as missing, and each column's range and value counts. It cannot establish what a value means, whether it is correct, or what to change. Those need metadata and knowledge of how the data were collected. A profile raises questions; it does not choose repairs.

The questions it raises fall into four kinds of suspected problem:

2.3Centre: the mean and the median

The profile's count, min and max describe size and range. Descriptive statistics go further: they help us grasp what the values are like and spot unusual ones. We switch to the twelve salaries to ask three questions: what is a typical salary? how much do salaries vary? which ones deserve a closer look?

The two respond to values differently. The mean depends on the magnitude of every observation. The median depends only on the middle ordered value(s): make 110 into 1,000 and the median stays 54 while the mean jumps to about 132. The gap between mean (58) and median (54) is a first clue that something is pulling the mean.

2.4Quartiles and the five-number summary

The median splits the ordered data in half. Quartiles split it in quarters: Q1, the median and Q3 are the 25th, 50th and 75th percentiles (a pth percentile is a value with about p% of the data at or below it). With twelve values, four groups of three show where they fall:

But where exactly between them? There are several conventions. The lecture uses pandas' default, called linear interpolation, which gives Q1 = 49.25, median = 54, Q3 = 64.75. Here is how it works, because the slides only stated the numbers:

1. Number the sorted values from position 0 to N − 1 (here 0 to 11).
2. The p-quantile sits at position (N − 1) × p.
3. If that position is fractional, go that fraction of the way from the value below to the value above.

s = pd.Series([30, 36, 47, 50, 52, 52, 56, 60, 63, 70, 70, 110], name="salary")
print(s.describe())

# other conventions give other quartiles for the same data
for m in ["linear", "lower", "higher", "midpoint", "nearest"]:
    print(m, s.quantile([0.25, 0.75], interpolation=m).tolist())

count     12.000000
mean      58.000000
std       20.338052
min       30.000000
25%       49.250000
50%       54.000000
75%       64.750000
max      110.000000
Name: salary, dtype: float64
linear [49.25, 64.75]
lower [47, 63]
higher [50, 70]
midpoint [48.5, 66.5]
nearest [50, 63]

So a textbook or calculator might report Q1 = 48.5 and Q3 = 66.5 for the very same list. Neither is wrong; state which convention you used, and use the same one throughout. (Notice describe() also prints a std row: 20.34. That is a standard deviation computed with a divisor of N − 1. Part 4 explains it, and why the course uses 19.47 instead.)

The minimum and maximum are observations; the quartiles may fall between observations. And because quartiles mark quarter positions, small samples and repeated values can prevent four groups of exactly equal size.

2.5Spread: the IQR and the range

In this list, the quartiles and IQR would not change if the largest salary were larger still, as long as it remained the largest. The range would. Spread describes how far apart the values are; centre describes where they are. They answer different questions, so a summary needs both.

2.6The 1.5 × IQR rule for potential outliers

For the salaries: 1.5 × 15.5 = 23.25, so the lower fence is 49.25 − 23.25 = 26.0 and the upper fence is 64.75 + 23.25 = 88.0. Only 110 lies beyond a fence.

q1, q3 = s.quantile(0.25), s.quantile(0.75)
iqr = q3 - q1
lower, upper = q1 - 1.5 * iqr, q3 + 1.5 * iqr
flag = (s < lower) | (s > upper)      # True where a value lies strictly beyond a fence

print(iqr, lower, upper)
print(s[flag])                         # the flagged rows; the Series itself is unchanged

15.5 26.0 88.0
11    110
Name: salary, dtype: int64

Three cautions. The rule is a rule of thumb: it is neither a definition of every outlier nor a statistical test. Fences are computed boundaries, not the observed min and max. And a flagged value is unusual for its attribute, which may mean an error or a correct but rare value; the data alone usually cannot tell which.

2.7Salary explorer

Everything in this part, live. Drag the largest salary and watch which summaries react. Then try making it small enough that it stops being flagged, and notice that the quartiles never moved.

Monday's practical follows exactly this pattern: profile a new table, read the rows of describe(), compute the fences in pandas and flag rows without changing the DataFrame.

Reading: Tan §2.2; Han §2.2, §2.4; Badia §1.1, §3.2, §3.3.

Part 3, from Lecture 13

Missing values and cleaning decisions

Part 2 detected and recorded problems without repairing anything. This part decides what to do about entries that are unknown, and how to record why. By the end you should be able to distinguish four kinds of recorded entry and say when converting one to a missing marker is justified; explain what retaining, deleting, filling with a constant, and filling with the mean or median each assume and change; and record a cleaning decision so that observed values, estimates and open questions stay separate.

The whole part follows four steps, which are also its summary:

3.1Field 4, revisited

Two facts about field 4 are known: the documentation says valid values are 1 to 10 and 0 is the missing code; and a data-entry error changed every 10 into 0. Part 2 ended with “detect, record and ask” because the repair needs information the file does not contain. Which information? The original forms, or a log of the entry error, showing which zeros were tens. Until that arrives, each zero may be a missing measurement and may be a 10, and the table cannot tell which.

3.2Four kinds of recorded entry

Before deciding anything, we need names for what we are looking at.

3.3Converting a marker is not estimating a value

Two operations are easily confused, and the difference is the most important idea in this part.

Field 4 shows how condition (2) can fail. The code 0 was distinguishable from the values 1 to 10 until the entry error turned real tens into 0. The documentation did not change, but the zeros are no longer distinguishable, so converting them is no longer justified: it would overwrite real tens.

Why does representation correction matter at all, if nothing is learned? Because software treats a code as a number until told otherwise:

import numpy as np
pt = pd.DataFrame({"patient": ["A", "B", "C", "D", "E"],
                   "weight":  [61.2, -1, 70.5, -1, 82.0]})   # documentation: -1 = not recorded
print(pt["weight"].mean())                          # the codes are averaged as weights

pt["weight"] = pt["weight"].replace(-1, np.nan)     # representation correction
print(pt["weight"].mean(), pt["weight"].isna().sum())

42.339999999999996
71.23333333333333 2

Before the correction the “mean weight” of 42.3 kg was nonsense; afterwards pandas skips the two unknowns and averages the three real weights (and isna() can finally count them). You can also do this at load time with pd.read_csv(..., na_values={"weight": [-1]}), scoped to the one column where the code is documented. (Note the trailing digits in 42.339999999999996: that is ordinary floating-point rounding, not an error.)

For field 4, the defensible move is the opposite: change nothing in the source, and label the ambiguity in a working copy:

work = df.copy()                                   # the source stays as received
work["field4_status"] = np.where(work["field4"] == 0,
                                 "unresolved: missing or 10", "recorded")
print(work[["field1", "field4", "field4_status"]])

  field1  field4              field4_status
0    012       0  unresolved: missing or 10
1    020       2                   recorded
2    027       0  unresolved: missing or 10

3.4How values become unknown: one sensor, three situations

Now suppose the missing entries are genuinely missing. Why they are missing changes what we may do. An air-temperature sensor takes readings at scheduled times, and its record has gaps. The target is fixed:

For each situation, ask: is the mean of the recorded temperatures expected to be too high, too low, neither in particular, or does it depend?

Situation 1: the battery runs out

Readings stop until the battery is replaced. Assumption: whether a scheduled reading is recorded is unrelated to the temperature at that time. Answer: neither in particular. The recorded mean is a fair guide on average: not guaranteed to equal the full-period mean in this particular period, and based on fewer readings, but not expected to err in any one direction. (One long gap in a single season would break the assumption — a month-long outage in winter loses mostly cold readings — which is exactly why the assumption is written down.)

Situation 2: no replacement when it rains

The person who replaces batteries does not do it when it rains, so batteries are more likely to be dead on rainy days. Assumption: whether a reading exists depends on rain, not on temperature itself. Rain makes a gap more likely, not certain. Answer: it depends. Recorded readings over-represent dry times. That matters only if rainy and dry times differ in temperature — a relation the table may or may not show.

Situation 3: the sensor fails below zero

Assumption: these failures are the only cause of gaps. Answer: too high. Every recorded value is at or above zero and every missing one is below zero. The missing values all come from one part of the range, so the recorded values are not an impartial guide to them, and no recorded value says how far below zero the missing ones were.

3.5Four treatments of missing values

A company's customer table has no recorded income for some customers. Four common ways to treat those gaps:

Other methods predict each missing value from the customer's other attributes (for example from age and job). They are often better estimates, but they are still estimates.

In pandas (incomes in thousands of dollars, three missing):

inc = pd.Series([42, 48, np.nan, 55, 56, np.nan, 58, 61, np.nan, 70], name="income")

def summary(x):
    return f"n={x.count()}  mean={x.mean():.2f}  median={x.median():.2f}  sd={x.std(ddof=0):.2f}"

print("recorded   ", summary(inc))                        # retain: pandas skips NaN
print("mean fill  ", summary(inc.fillna(inc.mean())))
print("median fill", summary(inc.fillna(inc.median())))
print(inc.fillna("Unknown").dtype)                          # constant label

# deleting rows: use the whole table so you see what else goes
# customers.dropna(subset=["income"])  drops every column of those 3 customers

recorded    n=7  mean=55.71  median=56.00  sd=8.33
mean fill   n=10  mean=55.71  median=55.71  sd=6.97
median fill n=10  mean=55.80  median=56.00  sd=6.97
object

Look at the last column of numbers: both fills shrank the spread, from 8.33 to 6.97. Ten values now look less variable than seven did, because three of them were placed exactly at the centre. And the “Unknown” fill silently turned a numeric column into a text (object) column.

3.6Filling with the mean or the median

Suppose each missing income is filled with the mean, or the median, of the recorded incomes. Where do the filled entries land? All at one point: the chosen centre. Consequences:

• Every filled entry is the same number, so the filled column shows an artificial concentration at that value.
• One repeated number cannot show how uncertain each missing income is. Each filled entry is an estimate, not an observation. As the code showed, the spread is understated.
• The smallest and largest entries do not change.

The two fills keep different summaries:

• Mean filling: the column's mean stays equal to the recorded mean. (Adding values exactly at the mean cannot move the balance point.)
• Median filling: the column's median stays equal to the recorded median; the mean generally moves (55.71 → 55.80 above).

Choosing between mean and median is the question of which centre to use, from Part 2. Neither choice says anything about why the incomes are missing. If high earners are the ones who decline to report, both fills are biased low. Try it:

3.7Recording a decision: the cleaning decision log

Whatever you choose, someone else must be able to check it, and undo it if needed. One log entry has six fields:

Two words are used carefully from here on:

A perfectly written log entry can describe an indefensible action. Writing an assumption down does not make it true.

Field 4 under alternative interpretations

The three printed rows hold 0, 2, 0 in field 4. If both zeros were really tens, what is the mean of the three measurements? Because we do not know, report the mean of the available measurements under each interpretation:

Note that n changes with the interpretation. These are not three “possible true means”: wherever a zero is a missing code, that measurement remains unknown.

A worked entry: field 4 of the patient file

The shaded row is not a seventh field. It is the judgement that separates a recorded action from a defensible one. Here the defensible action is a non-action plus a question.

Reading: Han §2.4; Badia §3.3; Tan §2.2.

Part 4, from Lecture 14

Scaling numeric attributes: min–max normalization and z-score standardization

After deciding what to do with unknown entries, perfectly usable numeric attributes may still be recorded in very different units and ranges. By the end of this part you should be able to explain the standard deviation as the root-mean-square distance from the mean, compute it with a stated divisor, and recognise when a reported value used N − 1; compute and interpret a min–max value and a z-score; and record what a scaling step used, and say what rescaling does not change.

4.1Why measurement scale matters

Some calculations combine attributes by their numerical magnitudes. The commonest is the distance between two objects. With two attributes, the straight-line (Euclidean) distance between person p and person q is

A difference of a few thousand dollars is numerically far larger than a difference of a few years, so income will dominate this sum purely because of the unit it is recorded in. Change metres to centimetres and the same quantity becomes 100 times “bigger”, without anything real changing. Methods later in the course, such as nearest-neighbour and clustering methods, use exactly this kind of calculation.

Two qualifications matter. Each attribute can still be described and summarised in its own unit; there is nothing wrong with the raw values. And rescaling does not decide how important an attribute should be; that is a choice for the analysis. Rescaling only prevents accidental dominance caused by units.

4.2The standard deviation: spread about the mean

Before standardizing with a z-score, we need a way to describe how spread out values are around the mean. Part 2's IQR measured the spread of the middle half around the median; now we want a spread that goes with the mean.

Deviations from the mean

The salaries have mean x̄ = 58. Each value's signed distance from the mean, x_i − x̄, is its deviation. Negative deviations lie below the mean, positive ones above.

The deviations add up to zero. This is true of any list, because the mean is the balance point: positive and negative deviations cancel exactly. (Proof in one line: Σ(x_i − x̄) = Σx_i − N·x̄ = N·x̄ − N·x̄ = 0.) So the raw sum of deviations cannot measure spread.

Squaring prevents the cancellation

Square each deviation. Every square is at least 0, so deviations on both sides now add up instead of cancelling.

For the salaries, dividing by N = 12: variance = 4,550 / 12 ≈ 379.17 (thousand dollars)², and σ = √379.17 ≈ 19.47 thousand dollars.

Properties

• σ describes spread about the mean; it is the natural spread measure when the mean is the chosen centre (the IQR pairs with the median).
• σ = 0 only when all values are equal; otherwise σ > 0.
• Squaring gives large deviations extra weight: a deviation twice as large contributes four times as much to the variance.
• σ is in the attribute's unit (thousand dollars); the variance is in the squared unit, (thousand dollars)², which is why we usually report σ.
• σ is not a limit on individual deviations: 110 lies 52 above the mean and 30 lies 28 below it, both more than 19.47 away.

4.3Two conventions: dividing by N or by N − 1

Dividing the sum of squared deviations by N describes the spread of the values at hand. A second convention divides by N − 1. It is used when the values are a sample and the aim is to estimate the spread of a larger population.

• This course uses N for descriptive standard deviations and for scaling. In pandas this is written ddof=0 (“delta degrees of freedom”: the divisor is N − ddof).
• pandas' s.std() and the std row of describe() divide by N − 1 (ddof=1) by default.
• Neither is an error. A report states which divisor it used.

s = df["salary"]          # the twelve salaries, thousand dollars
s.std(ddof=0)             # divide by N
s.std()                   # divide by N - 1 (pandas default)

np.float64(19.47220240924654)
np.float64(20.338052110179177)

Both are in thousand dollars; 4,550 / 11 = 413.64 and √413.64 ≈ 20.34. The wrapper np.float64(...) only says that pandas returned a NumPy number.

4.4Min–max normalization

Read it in two steps. Subtracting min measures how far v lies above the recorded minimum. Dividing by max − min expresses that distance as a fraction of the recorded range. Therefore:

• the recorded minimum maps to 0 and the recorded maximum to 1;
• a value halfway between them maps to 0.5;
• the result has no unit, because the unit cancels in the ratio.

So v′ = 0.25 means the observation lies one quarter of the way from the recorded minimum to the recorded maximum.

Scenario 1 (hypothetical)

An attribute income has a stated minimum of $12,000 and maximum of $98,000. Where does $73,600 map?

$73,600 lies about 71.6% of the way from the recorded minimum to the recorded maximum. The number 0.716 is a position within the range: not a probability, and not “71.6% of the income”. The scenario states only a minimum and a maximum; it gives no list of incomes.

The twelve salaries

With min = 30 and max = 110 (range 80), v′ = (v − 30) / 80. Salary 52 maps to 22/80 = 0.275.

Notice two things. First, 70 maps to 0.5: it is the midpoint of the recorded range, not the median (the median, 54, maps to 0.3). Second, eleven of the twelve values lie in [0, 0.5]: the single value 110 sets the maximum, and the other eleven span only 40 of the range of 80.

4.5Z-score standardization

The numerator is the signed distance from the mean; dividing by σ expresses it in units of typical spread. So z > 0 means above the mean, z < 0 below, z = 0 is the mean; |z| counts standard deviations (z = 2 means two standard deviations above); and z has no unit.

Scenario 2 (hypothetical, separate from Scenario 1)

Stated mean $54,000, stated standard deviation $16,000. Then $38,000, $54,000 and $70,000 have z = −1, 0, +1. Since $73,600 is above $70,000, expect its z-score to exceed 1:

$73,600 lies 1.225 standard deviations above the stated mean: a standardized distance, not a percentage and not an amount of money.

The twelve salaries

With mean 58 and σ = 19.47: z = (v − 58) / 19.47.

The raw value 70 tells you a salary; its z-score, 0.62, adds context: 70 lies about 0.62 standard deviations above this list's mean. Likewise 110 lies 2.67 SDs above and 30 lies 1.44 below.

After standardizing, the twelve z-scores have mean 0 and standard deviation 1 (with the same N divisor). Why: subtracting x̄ moves the balance point to 0; dividing every deviation by σ divides the root-mean-square deviation by σ, giving 1. pandas' default std() of the z-scores gives 1.044 only because it uses N − 1 (√(12/11) ≈ 1.044).

4.6Two separate scenarios

The worked income examples came from two separate hypothetical scenarios. Scenario 1 supplied a minimum and maximum, enough for min–max. Scenario 2 supplied a mean and standard deviation, enough for a z-score. Neither supplied the incomes behind its parameters. So 0.716 and 1.225 are not two descriptions of one income distribution; each belongs only to its own scenario. (For the salaries, by contrast, both sets of parameters come from the same twelve values, so the two tables above do describe the same data.)

4.7Recording a scaling step

To reproduce a scaling step, another analyst must know what was scaled and with which parameters. For the salary column:

The parameters are computed once from the data they describe, recorded, and then reused for later values. They are not recomputed for each new value. pandas skips missing entries when computing min, max, mean or standard deviation, and a missing entry stays missing after scaling; that is why the record states which rows were used. As with Part 3's decision log, the purpose is reproducibility.

Going back: the inverse maps

Recorded parameters also make the transformation reversible. Solve each formula for the original value:

For Scenario 2: 54,000 + 1.225 × 16,000 = 73,600. Without the recorded parameters, this recovery is impossible.

4.8What rescaling does not do

Rescaling changes the axis used to describe the data; it does not repair or reshape the data.

• Order and relative spacing are preserved. Both transformations subtract one number and divide by another, using the same two numbers for every observation (a straight-line map). So the shape of the distribution is unchanged.
• Extreme values stay extreme. 110 stays far from the other salaries under either transformation.
• Scaling does not make data normal. It relabels the axis; it does not change the pattern. A skewed column stays exactly as skewed.
• Extreme values affect the parameters. 110 contributes about 59% of the squared deviations, inflating σ, and sets the min–max maximum, compressing the other eleven salaries into the lower half.
• Reused min–max parameters can give values outside [0, 1]. If min 30 and max 70 had been recorded earlier (from the first eleven salaries) and were later applied to 110: (110 − 30) / (70 − 30) = 2.0. Valid arithmetic: the new value lies outside the range the parameters were recorded on. Record it and examine it; do not silently clip it to 1.

A constant column

If every value in a column is the same, then σ = 0 and max − min = 0, so both formulas divide by zero. Each numerator is also 0, so every row is 0/0.

c = pd.Series([4.0, 4.0, 4.0, 4.0], name="constant")
print(((c - c.mean()) / c.std(ddof=0)).tolist())

[nan, nan, nan, nan]

4.9Scaling playground

Things to try: switch to “Salaries in dollars” and confirm that every min–max value and z-score is unchanged (both are unit-free). Switch to “Salaries without 110”, then enter 110 as the new value: min–max gives 2.0. Choose “Constant column” to see the zero-denominator guard.

4.10Checks

4.11Reference: the pandas pattern

For reference; you will practise it with worked examples before being expected to write it yourself.

work = df.copy()                                    # keep the original table
col = work["salary"]
params = {"min": col.min(), "max": col.max(),
          "mean": col.mean(), "sd": col.std(ddof=0),  # N divisor
          "n_used": int(col.count()), "n_missing": int(col.isna().sum())}

rng = params["max"] - params["min"]
if rng == 0 or params["sd"] == 0:                   # check the denominator first
    print("constant column: not scaled")
else:
    work["salary_minmax"] = (col - params["min"]) / rng
    work["salary_z"] = (col - params["mean"]) / params["sd"]

# later values reuse the SAME stored parameters
new = 120
print((new - params["mean"]) / params["sd"], (new - params["min"]) / rng)

3.184026064281192 1.125

df.copy() makes a working copy so the original is kept; params holds the numbers the record needs; the two new columns leave salary unchanged; and the guard prevents the silent NaN column.

Reading: Han §2.2, §2.5; Tan §2.3.

Part 5

The module on one page

Formulas