Data Wrangling and Exploratory Analysis

Week 02 · Day 1 of 7

Loading Data That Fights Back

Encodings, dtypes, sentinel values and the missing-value list you did not know about

Week 1 read a CSV in one line and it worked. That is the exception. Real extracts arrive with the wrong encoding, dates as text, numbers with currency symbols, and a sentinel value someone chose in 1998 to mean “unknown”. Loading is where most silent errors enter.

pandas has opinions about what missing means

You met this in week 1 without knowing it. read_csv converts a list of strings to NaN automatically, and that list is longer than you would guess.

import pandas as pd
from pandas.io.parsers.readers import STR_NA_VALUES

print(sorted(STR_NA_VALUES))

['', '#N/A', '#N/A N/A', '#NA', '-1.#IND', '-1.#QNAN', '-NaN', '-nan', '1.#IND', '1.#QNAN', '<NA>', 'N/A', 'NA', 'NULL', 'NaN', 'None', 'n/a', 'nan', 'null']

'None' is on that list

A category legitimately called None: a customer with no internet service, a product with no discount, is read as a missing value. The column silently gains hundreds of gaps that were never in the file. This is a real defect that appeared while building this course's dataset, and it is why the category is spelled No internet.

Two arguments control it. keep_default_na=False switches the whole list off; na_values= adds your own sentinels.

import pandas as pd
import io as _io

raw = 'service,code\nNone,1\nDSL,-999\nFibre,2\n'

default = pd.read_csv(_io.StringIO(raw))
print('default:')
print(default)

explicit = pd.read_csv(_io.StringIO(raw), keep_default_na=False,
                       na_values=['-999'])
print('\nkeeping None as text, treating -999 as missing:')
print(explicit)

default:
  service  code
0     NaN     1
1     DSL  -999
2   Fibre     2

keeping None as text, treating -999 as missing:
  service  code
0    None   1.0
1     DSL   NaN
2   Fibre   2.0

Declare the types you expect

Letting pandas guess the dtype of every column is convenient and occasionally wrong. An identifier of digits becomes an integer and loses its leading zeros; a postcode becomes a float. Declare what matters.

import pandas as pd
import io as _io

raw = 'account,postcode\n007831,01234\n004120,00987\n'

print('guessed:')
print(pd.read_csv(_io.StringIO(raw)))

print('\ndeclared:')
print(pd.read_csv(_io.StringIO(raw), dtype={'account': str, 'postcode': str}))

guessed:
   account  postcode
0     7831      1234
1     4120       987

declared:
  account postcode
0  007831    01234
1  004120    00987

The leading zeros are gone in the first version and there is no way to get them back afterwards. Loss at load time is unrecoverable, which is why loading deserves more care than it usually gets.

Parse dates at load time

import pandas as pd

df = pd.read_csv('customers.csv', parse_dates=['signup_date'])
print(df['signup_date'].dtype)
print(df['signup_date'].min(), 'to', df['signup_date'].max())

datetime64[ns]
2019-01-01 00:00:00 to 2024-12-01 00:00:00

When the format is ambiguous

03/04/2024 is the third of April in Britain and the fourth of March in America, and pandas will pick one. If your source is not ISO format, pass the format explicitly: pd.to_datetime(col, format='%d/%m/%Y'). Guessing wrong produces dates that are valid, plausible and wrong for eleven months of the year.

Memory, and when it starts to matter

import pandas as pd

df = pd.read_csv('customers.csv')
before = df.memory_usage(deep=True).sum()

for col in ['contract', 'internet_service', 'payment_method']:
    df[col] = df[col].astype('category')

after = df.memory_usage(deep=True).sum()
print('before %6.1f KB' % (before / 1024))
print('after  %6.1f KB' % (after / 1024))
print('saved  %5.1f%%' % (100 * (1 - after / before)))

before 1322.7 KB
after   782.4 KB
saved   40.8%

A category column stores each distinct value once and an integer code per row. With three distinct values across three thousand rows the saving is large, and it grows with the row count. On a dataset that fits comfortably in memory this is housekeeping; on one that does not, it is the difference between working and not.

Day 1 takeaway

Loading is not a formality. pandas silently converts a list of strings to missing values, guesses dtypes in ways that destroy leading zeros, and picks a date format when yours is ambiguous. Declare dtype, na_values and parse_dates deliberately, and convert low-cardinality text to category.

Week 02 · Day 2 of 7

Reshaping and Joining

Long against wide, and how a careless join duplicates your target

By Pawan Rama Mali746 words

Data almost never arrives in the shape a model wants. Models want one row per observation and one column per feature. Sources give you one row per event, or one column per month, or the information split across three tables.

Long and wide

Tidy data: One row per observation, one column per variable, one table per kind of observation. scikit-learn requires it: every row is an example, every column a feature. Most reshaping work is moving from a human-readable layout to this one.

import pandas as pd

wide = pd.DataFrame({
    'customer_id': ['C1', 'C2'],
    'jan': [40.0, 55.0],
    'feb': [42.0, 55.0],
    'mar': [41.5, 60.0],
})
print('wide -- readable, not modellable')
print(wide)

long = wide.melt(id_vars='customer_id', var_name='month',
                 value_name='charge')
print('\nlong -- one row per observation')
print(long)

wide -- readable, not modellable
  customer_id   jan   feb   mar
0          C1  40.0  42.0  41.5
1          C2  55.0  55.0  60.0

long -- one row per observation
  customer_id month  charge
0          C1   jan    40.0
1          C2   jan    55.0
2          C1   feb    42.0
3          C2   feb    55.0
4          C1   mar    41.5
5          C2   mar    60.0

melt goes wide to long, pivot goes back:

import pandas as pd

long = pd.DataFrame({
    'customer_id': ['C1', 'C1', 'C1', 'C2', 'C2', 'C2'],
    'month': ['jan', 'feb', 'mar'] * 2,
    'charge': [40.0, 42.0, 41.5, 55.0, 55.0, 60.0],
})

back = long.pivot(index='customer_id', columns='month', values='charge')
print(back)

# Aggregating while pivoting, when there are duplicates per cell
summary = long.pivot_table(index='customer_id', values='charge',
                           aggfunc=['mean', 'max'])
print('\n', summary)

month         feb   jan   mar
customer_id                  
C1           42.0  40.0  41.5
C2           55.0  55.0  60.0

                   mean    max
                charge charge
customer_id                  
C1           41.166667   42.0
C2           56.666667   60.0

Which shape do you want

Long is right for storage and for plotting with seaborn. Wide is right for modelling, because a model needs a fixed set of columns. Converting long event logs into wide per-customer features is exactly what week 8 calls feature engineering.

Joining tables

The merge types are the same four you know from SQL, and the argument that matters most is how.

how	Keeps	Use when
`inner`	Rows matching in both	You need complete records only
`left`	All left rows, matched right ones	Enriching a base table, the usual choice
`outer`	Everything from both	Reconciling two sources
`cross`	Every combination	Building a grid; rarely what you want

import pandas as pd

customers = pd.DataFrame({
    'customer_id': ['C1', 'C2', 'C3'],
    'contract': ['Month-to-month', 'Two year', 'One year'],
})
tickets = pd.DataFrame({
    'customer_id': ['C1', 'C1', 'C3', 'C9'],
    'issue': ['billing', 'speed', 'billing', 'billing'],
})

left = customers.merge(tickets, on='customer_id', how='left')
print(left)
print('\nrows before %d, after %d' % (len(customers), len(left)))

  customer_id        contract    issue
0          C1  Month-to-month  billing
1          C1  Month-to-month    speed
2          C2        Two year      NaN
3          C3        One year  billing

rows before 3, after 4

A left join can multiply your rows

C1 has two tickets, so C1 appears twice. Three customers became four rows. Join a one-per-customer table to a many-per-customer table and your target variable is now duplicated, which silently weights those customers more heavily in training. Aggregate the many side first, then join.

import pandas as pd

customers = pd.DataFrame({
    'customer_id': ['C1', 'C2', 'C3'],
    'contract': ['Month-to-month', 'Two year', 'One year'],
})
tickets = pd.DataFrame({
    'customer_id': ['C1', 'C1', 'C3', 'C9'],
    'issue': ['billing', 'speed', 'billing', 'billing'],
})

per_customer = tickets.groupby('customer_id').agg(
    ticket_count=('issue', 'size'),
    billing_tickets=('issue', lambda s: (s == 'billing').sum()),
)

safe = customers.merge(per_customer, on='customer_id', how='left')
safe[['ticket_count', 'billing_tickets']] = (
    safe[['ticket_count', 'billing_tickets']].fillna(0).astype(int))
print(safe)
print('\nrows: %d -- unchanged' % len(safe))

  customer_id        contract  ticket_count  billing_tickets
0          C1  Month-to-month             2                1
1          C2        Two year             0                0
2          C3        One year             1                1

rows: 3 -- unchanged

Three rows in, three rows out, and two new features. That is the shape every join into a modelling table should have.

Validate the join instead of hoping

import pandas as pd

left_tbl = pd.DataFrame({'k': ['a', 'b', 'c'], 'x': [1, 2, 3]})
right_tbl = pd.DataFrame({'k': ['a', 'a', 'b'], 'y': [9, 8, 7]})

try:
    left_tbl.merge(right_tbl, on='k', how='left', validate='one_to_one')
except Exception as e:
    print(type(e).__name__ + ':', e)

MergeError: Merge keys are not unique in right dataset; not a one-to-one merge

validate accepts one_to_one, one_to_many, many_to_one and many_to_many. Stating what you expect turns a silent row explosion into an immediate error.

Day 2 takeaway

Models want one row per observation. Use melt and pivot to reshape, aggregate a many-side table before joining it so you do not duplicate your target, and pass validate to merge so a wrong assumption raises instead of quietly inflating your data.

Week 02 · Day 3 of 7

Missing Data, Properly

Why a value is missing decides how to fill it, and where to fit the imputer

By Pawan Rama Mali971 words

Week 1 counted the gaps and moved on. Today you decide what to do about them, and the right answer depends on why they are missing.

Three mechanisms

Mechanism	Meaning	Example	Safe to impute?
MCAR	Missing completely at random	A sensor dropped packets	Yes
MAR	Missingness explained by other columns	Older customers skip the email field	Yes, using those columns
MNAR	Missingness depends on the missing value itself	High earners decline to state income	No, imputing biases it

You cannot test for MNAR from the data alone, which is the uncomfortable part. What you can do is check whether missingness relates to anything you can see, and record the fact that a value was missing.

Is the missingness informative?

import pandas as pd
df = pd.read_csv('customers.csv').drop_duplicates().copy()
df['contract'] = df['contract'].str.strip().str.title()
df['total_charges'] = pd.to_numeric(df['total_charges'], errors='coerce')

gap = df['has_dependents'].isna()
print('rows with the field blank: %d' % gap.sum())
print('churn rate when blank    : %.3f' % df.loc[gap, 'churned'].mean())
print('churn rate when present  : %.3f' % df.loc[~gap, 'churned'].mean())

rows with the field blank: 180
churn rate when blank    : 0.272
churn rate when present  : 0.268

Close enough to call it uninformative here, which is what you would expect, because the generator removed those values at random. Had the two rates differed sharply, the fact of the gap would itself be a feature worth keeping.

Keep the flag anyway

Adding an was_missing indicator column costs one bit per row and occasionally carries real signal: in medical data, a test that was not ordered tells you what the clinician was thinking. SimpleImputer(add_indicator=True) does it for you, inside the pipeline.

What each strategy actually does to the distribution

import pandas as pd
df = pd.read_csv('customers.csv').drop_duplicates().copy()
df['contract'] = df['contract'].str.strip().str.title()
df['total_charges'] = pd.to_numeric(df['total_charges'], errors='coerce')
from sklearn.impute import SimpleImputer
import numpy as np

col = df[['monthly_charges']]
truth = col.dropna()['monthly_charges']
print('observed  : mean %.2f  std %.2f  n %d'
      % (truth.mean(), truth.std(), len(truth)))

for strategy in ['mean', 'median', 'most_frequent']:
    filled = SimpleImputer(strategy=strategy).fit_transform(col).ravel()
    print('%-14s: mean %.2f  std %.2f  n %d'
          % (strategy, filled.mean(), filled.std(), len(filled)))

observed  : mean 59.41  std 23.47  n 2835
mean          : mean 59.41  std 22.81  n 3000
median        : mean 59.62  std 22.83  n 3000
most_frequent : mean 56.96  std 24.96  n 3000

Imputation always shrinks the variance

Every value filled with the mean or median sits exactly at the centre, so the spread of the column falls, 23.47 to 22.81 here. most_frequent is different: the mode is not the centre, so it can widen the spread instead, as it does above. With 5 percent missing either is a rounding error. With 40 percent it is a serious distortion, and any model that relies on the variance of that feature is being misled. Check how much you are filling before you decide it is fine.

Using the other columns

A customer's charge is not independent of their internet service. Filling from the group is better than filling from the whole column.

import pandas as pd
df = pd.read_csv('customers.csv').drop_duplicates().copy()
df['contract'] = df['contract'].str.strip().str.title()
df['total_charges'] = pd.to_numeric(df['total_charges'], errors='coerce')

overall = df['monthly_charges'].median()
by_service = df.groupby('internet_service')['monthly_charges'].median()
print('overall median   %.2f' % overall)
print(by_service.round(2))

smart = df['monthly_charges'].fillna(
    df.groupby('internet_service')['monthly_charges'].transform('median'))
print('\nremaining gaps:', smart.isna().sum())

overall median   63.21
internet_service
DSL            54.97
Fibre optic    79.71
No internet    20.69
Name: monthly_charges, dtype: float64

remaining gaps: 0

Filling a fibre customer's charge with the overall median understates it by a wide margin. Grouping first respects a relationship you already know exists.

import pandas as pd
df = pd.read_csv('customers.csv').drop_duplicates().copy()
df['contract'] = df['contract'].str.strip().str.title()
df['total_charges'] = pd.to_numeric(df['total_charges'], errors='coerce')
from sklearn.impute import KNNImputer
from sklearn.preprocessing import StandardScaler
import numpy as np

num = df[['tenure_months', 'monthly_charges', 'support_calls']]
scaled = StandardScaler().fit_transform(num)
filled = KNNImputer(n_neighbors=5).fit_transform(scaled)

gaps = num['monthly_charges'].isna().values
back = StandardScaler().fit(num.dropna()).inverse_transform(filled)
print('imputed values, first five:')
print(np.round(back[gaps, 1][:5], 2))

imputed values, first five:
[55.45 54.23 54.45 81.7  55.19]

KNN imputation must be scaled first

It finds neighbours by distance, and distance is meaningless across columns measured in months and pounds. Scale, impute, and only then reverse the scaling. Getting this order wrong gives you neighbours chosen almost entirely by whichever column has the largest numbers.

The rule that matters more than the method

import pandas as pd
df = pd.read_csv('customers.csv').drop_duplicates().copy()
df['contract'] = df['contract'].str.strip().str.title()
df['total_charges'] = pd.to_numeric(df['total_charges'], errors='coerce')
from sklearn.model_selection import train_test_split
from sklearn.impute import SimpleImputer

X = df[['tenure_months', 'monthly_charges']]
y = df['churned']
X_tr, X_te, y_tr, y_te = train_test_split(X, y, test_size=0.25,
                                          random_state=42)

# Right: learn the median on train, apply it to both.
imp = SimpleImputer(strategy='median').fit(X_tr)
print('median learned from train only: %.2f' % imp.statistics_[1])

# Wrong: learn it from everything, including rows you will test on.
leaky = SimpleImputer(strategy='median').fit(X)
print('median learned from everything: %.2f' % leaky.statistics_[1])

median learned from train only: 63.05
median learned from everything: 63.21

The two numbers are close, and that is exactly what makes the mistake hard to catch. The leak is real regardless of its size, it grows with the proportion missing, and it always flatters the test score. Fit imputers on training data only, which pipelines enforce for you.

Day 3 takeaway

Ask why a value is missing before deciding how to fill it. Check whether missingness predicts the target, and keep an indicator if it might. Imputation shrinks variance, group-aware filling beats a global constant, and KNN imputation needs scaling first. Fit every imputer on training data only.

Week 02 · Day 4 of 7

Outliers, Skew and Robustness

Finding extreme values, deciding what they mean, and which models care

By Pawan Rama Mali972 words

An outlier is not a value that is large. It is a value that does not belong to the process you are modelling, and telling those apart requires knowing the domain, not just the numbers.

Find them three ways

import pandas as pd
df = pd.read_csv('customers.csv').drop_duplicates().copy()
df['contract'] = df['contract'].str.strip().str.title()
df['total_charges'] = pd.to_numeric(df['total_charges'], errors='coerce')
import numpy as np

x = df['support_calls']

# 1. Standard deviations from the mean
z = (x - x.mean()) / x.std()
print('|z| > 3      :', (z.abs() > 3).sum())

# 2. Interquartile range, the boxplot rule
q1, q3 = x.quantile([0.25, 0.75])
iqr = q3 - q1
print('outside 1.5 IQR:', ((x < q1 - 1.5 * iqr) | (x > q3 + 1.5 * iqr)).sum())

# 3. Just look at the extremes
print('largest values :', np.sort(x.unique())[-5:])

|z| > 3      : 1
outside 1.5 IQR: 8
largest values : [ 4  5  6  7 97]

The z-score method is broken by the thing it looks for

A z-score uses the mean and standard deviation, and one extreme value drags both. The outlier inflates the standard deviation, which shrinks its own z-score, which can push it under the threshold. The IQR rule uses quartiles, which a single extreme value cannot move. That is what robust means and it is why the boxplot rule is the safer default.

What to do about the one customer with 97 calls

import pandas as pd
df = pd.read_csv('customers.csv').drop_duplicates().copy()
df['contract'] = df['contract'].str.strip().str.title()
df['total_charges'] = pd.to_numeric(df['total_charges'], errors='coerce')

extreme = df.loc[df['support_calls'] > 20,
                 ['customer_id', 'tenure_months', 'support_calls',
                  'monthly_charges', 'churned']]
print(extreme)
print('\nsupport_calls: mean %.2f, median %.1f, max %d'
      % (df['support_calls'].mean(), df['support_calls'].median(),
         df['support_calls'].max()))

    customer_id  tenure_months  support_calls  monthly_charges  churned
710      C00008             48             97            76.42        0

support_calls: mean 1.27, median 1.0, max 97

One row. It is not a typo. A customer really can call ninety-seven times, and if anything they are the most interesting customer in the file. Deleting them removes a genuine, if rare, pattern. The options, in order of how much they assume:

Leave it and use a model that does not care. Trees split on order, not magnitude, so an extreme value changes nothing.
Clip it to a percentile, caps the influence without discarding the row.
Transform the column with a log, which compresses the long tail.
Drop it, only when you can show it is an error.

import pandas as pd
df = pd.read_csv('customers.csv').drop_duplicates().copy()
df['contract'] = df['contract'].str.strip().str.title()
df['total_charges'] = pd.to_numeric(df['total_charges'], errors='coerce')
import numpy as np

x = df['support_calls']
cap = x.quantile(0.99)

print('original : max %5.1f  mean %.3f  std %.3f' % (x.max(), x.mean(), x.std()))
print('clipped  : max %5.1f  mean %.3f  std %.3f'
      % (x.clip(upper=cap).max(), x.clip(upper=cap).mean(), x.clip(upper=cap).std()))
print('log1p    : max %5.1f  mean %.3f  std %.3f'
      % (np.log1p(x).max(), np.log1p(x).mean(), np.log1p(x).std()))

original : max  97.0  mean 1.267  std 2.100
clipped  : max   5.0  mean 1.233  std 1.156
log1p    : max   4.6  mean 0.672  std 0.527

log1p, not log

np.log1p(x) computes log(1 + x), which is defined at zero. Plain np.log on a column containing zeros gives you -inf, and one infinity poisons every calculation downstream. Count columns almost always contain zeros.

Which models care

import pandas as pd
df = pd.read_csv('customers.csv').drop_duplicates().copy()
df['contract'] = df['contract'].str.strip().str.title()
df['total_charges'] = pd.to_numeric(df['total_charges'], errors='coerce')
from sklearn.model_selection import cross_val_score
from sklearn.pipeline import make_pipeline
from sklearn.preprocessing import StandardScaler
from sklearn.impute import SimpleImputer
from sklearn.linear_model import LogisticRegression
from sklearn.tree import DecisionTreeClassifier
import numpy as np

X = df[['tenure_months', 'support_calls', 'monthly_charges']]
y = df['churned']
X_capped = X.copy()
X_capped['support_calls'] = X_capped['support_calls'].clip(
    upper=X_capped['support_calls'].quantile(0.99))

models = {
    'logistic': make_pipeline(SimpleImputer(strategy='median'),
                              StandardScaler(),
                              LogisticRegression(max_iter=1000)),
    'tree    ': make_pipeline(SimpleImputer(strategy='median'),
                              DecisionTreeClassifier(max_depth=4,
                                                     random_state=42)),
}
for name, m in models.items():
    raw = cross_val_score(m, X, y, cv=5, scoring='roc_auc').mean()
    cap = cross_val_score(m, X_capped, y, cv=5, scoring='roc_auc').mean()
    print('%s  raw %.4f   capped %.4f   change %+.4f'
          % (name, raw, cap, cap - raw))

logistic  raw 0.7762   capped 0.7786   change +0.0024
tree      raw 0.7545   capped 0.7545   change +0.0000

The tree score is identical to four decimal places, because a tree only ever asks “is this value above the split point” and 97 answers that exactly as 20 does. The linear model moves, because after scaling that row sits many standard deviations out and pulls the fitted coefficient toward itself. The movement is small here, one contaminated row in three thousand, and that is the honest lesson: the direction is reliable, the size depends entirely on how much of your data is affected.

Skew, and why it matters for linear models

import pandas as pd
df = pd.read_csv('customers.csv').drop_duplicates().copy()
df['contract'] = df['contract'].str.strip().str.title()
df['total_charges'] = pd.to_numeric(df['total_charges'], errors='coerce')
import numpy as np

for col in ['tenure_months', 'monthly_charges', 'support_calls']:
    s = df[col].dropna()
    print('%-16s skew %6.2f   log1p skew %6.2f'
          % (col, s.skew(), np.log1p(s).skew()))

tenure_months    skew   1.22   log1p skew  -0.26
monthly_charges  skew  -0.47   log1p skew  -1.15
support_calls    skew  31.75   log1p skew   0.14

A skew near zero means symmetric. support_calls is extremely right-skewed at 31.75, and the log transform pulls it to 0.14. But look at monthly_charges: already near-symmetric at -0.47, and the log makes it distinctly worse at -1.15. A log is a tool for right-skewed columns, not a default. Linear models benefit from roughly symmetric inputs; tree models do not care either way.

Day 4 takeaway

Use the IQR rule rather than z-scores, because one extreme value corrupts the mean and standard deviation the z-score depends on. An extreme value that is real is information, clip or transform rather than delete. Trees are indifferent to outliers and skew; linear models are not.

Week 02 · Day 5 of 7

Dates and Time Features

Decomposition, elapsed time, cyclical encoding, and the split dates force

By Pawan Rama Mali731 words

A date column is useless to a model as it stands. No algorithm can do anything with the integer 1704067200. What models can use is what the date means: how long ago, which month, which day of the week.

The accessor

import pandas as pd

df = pd.read_csv('customers.csv', parse_dates=['signup_date'])
d = df['signup_date']

parts = pd.DataFrame({
    'date': d,
    'year': d.dt.year,
    'month': d.dt.month,
    'quarter': d.dt.quarter,
    'dayofweek': d.dt.dayofweek,
    'is_month_start': d.dt.is_month_start,
})
print(parts.head())

        date  year  month  quarter  dayofweek  is_month_start
2024-03-01  2024      3        1          4            True
2024-05-01  2024      5        2          2            True
2024-10-01  2024     10        4          1            True
2024-10-01  2024     10        4          1            True
2023-04-01  2023      4        2          5            True

Elapsed time is usually the feature you want

import pandas as pd

df = pd.read_csv('customers.csv', parse_dates=['signup_date'])

# A fixed reference, not today's date -- see the warning below.
as_of = pd.Timestamp('2025-01-01')
df['days_since_signup'] = (as_of - df['signup_date']).dt.days
df['years_since_signup'] = df['days_since_signup'] / 365.25

print(df[['signup_date', 'days_since_signup', 'years_since_signup']]
      .head().round(2))

  signup_date  days_since_signup  years_since_signup
2024-03-01                306                0.84
2024-05-01                245                0.67
2024-10-01                 92                0.25
2024-10-01                 92                0.25
2023-04-01                641                1.75

Never build a feature from today's date

pd.Timestamp.now() gives a different answer every day you run it. The model trained in March sees different numbers in June, and nothing in your code changed. Pass a fixed reference date, store it with the model, and use the same one at prediction time. This is one of the most common causes of a model that silently degrades in production.

Cyclical features

Month 12 and month 1 are adjacent, but as numbers they are eleven apart. A linear model reads December and January as maximally different. The fix is to place the value on a circle.

import numpy as np
import pandas as pd

months = pd.Series(range(1, 13))
angle = 2 * np.pi * (months - 1) / 12
cyc = pd.DataFrame({
    'month': months,
    'sin': np.sin(angle).round(3),
    'cos': np.cos(angle).round(3),
})
print(cyc)

def gap(a, b):
    ra = cyc.loc[cyc['month'] == a, ['sin', 'cos']].values[0]
    rb = cyc.loc[cyc['month'] == b, ['sin', 'cos']].values[0]
    return np.linalg.norm(ra - rb)

print('\nraw distance Dec to Jan: %d' % abs(12 - 1))
print('raw distance Jun to Jul: %d' % abs(6 - 7))
print('circular Dec to Jan: %.3f' % gap(12, 1))
print('circular Jun to Jul: %.3f' % gap(6, 7))

    month    sin    cos
     1  0.000  1.000
     2  0.500  0.866
     3  0.866  0.500
     4  1.000  0.000
     5  0.866 -0.500
     6  0.500 -0.866
     7  0.000 -1.000
     8 -0.500 -0.866
     9 -0.866 -0.500
    10 -1.000 -0.000
   11 -0.866  0.500
   12 -0.500  0.866

raw distance Dec to Jan: 11
raw distance Jun to Jul: 1
circular Dec to Jan: 0.518
circular Jun to Jul: 0.518

On the circle, December to January is the same distance as June to July, which is the truth. Use the same trick for hour of day and day of week.

The split that dates force on you

A random split leaks the future

If your rows are ordered in time and you split at random, the model trains on March and tests on February. It has seen the future. Every score is inflated, sometimes enormously, and the failure only shows up in production. For anything time-ordered, split by date, and scikit-learn gives you TimeSeriesSplit for cross-validation.

import numpy as np
from sklearn.model_selection import TimeSeriesSplit

x = np.arange(12)
for i, (train_idx, test_idx) in enumerate(TimeSeriesSplit(n_splits=4).split(x), 1):
    print('fold %d  train %-18s test %s'
          % (i, str(x[train_idx]), str(x[test_idx])))

fold 1  train [0 1 2 3]          test [4 5]
fold 2  train [0 1 2 3 4 5]      test [6 7]
fold 3  train [0 1 2 3 4 5 6 7]  test [8 9]
fold 4  train [0 1 2 3 4 5 6 7 8 9] test [10 11]

Each fold trains only on rows that came before the ones it tests on, and the training window grows. No fold ever sees data from after its test period, which is the only honest way to score a time-ordered problem.

Day 5 takeaway

Decompose dates into parts a model can use, and prefer elapsed time from a fixed reference over anything derived from today. Encode cyclical values as sine and cosine so December sits next to January. When rows are ordered in time, split by time.

Week 02 · Day 6 of 7

Categories and Text

Cardinality, ordinal against nominal, unseen levels and rare levels

By Pawan Rama Mali855 words

Categories and free text are where most of the information in business data lives, and where most of the leakage and most of the silent breakage comes from too.

Cardinality decides the treatment

import pandas as pd
df = pd.read_csv('customers.csv').drop_duplicates().copy()
df['contract'] = df['contract'].str.strip().str.title()
df['total_charges'] = pd.to_numeric(df['total_charges'], errors='coerce')

for col in df.select_dtypes('object').columns:
    print('%-18s %5d distinct  %5.1f%% of rows'
          % (col, df[col].nunique(), 100 * df[col].nunique() / len(df)))

customer_id         3000 distinct  100.0% of rows
signup_date           72 distinct    2.4% of rows
contract               3 distinct    0.1% of rows
internet_service       3 distinct    0.1% of rows
payment_method         4 distinct    0.1% of rows
has_dependents         2 distinct    0.1% of rows

Distinct values	Treatment	Why
2	Map to 0/1	One column is enough
3 to about 15	One-hot encode	Manageable width, no false ordering
15 to a few hundred	Group the rare ones, then one-hot	A level seen twice cannot be learned from
Thousands	Target or hashing encoding	One-hot would be wider than the dataset is long
Unique per row	Drop it	An identifier is not a feature

customer_id is not a feature

It is unique per row, so a sufficiently flexible model can memorise the target for every training row through it and score perfectly. On new customers it knows nothing. Drop identifiers explicitly rather than trusting yourself to remember.

Ordinal is not the same as categorical

import pandas as pd
from sklearn.preprocessing import OrdinalEncoder, OneHotEncoder

sat = pd.DataFrame({'rating': ['low', 'high', 'medium', 'low']})

order = OrdinalEncoder(categories=[['low', 'medium', 'high']])
print('ordinal, order stated:')
print(order.fit_transform(sat).ravel())

print('\nordinal, order left to chance (alphabetical):')
print(OrdinalEncoder().fit_transform(sat).ravel())

ordinal, order stated:
[0. 2. 1. 0.]

ordinal, order left to chance (alphabetical):
[1. 0. 2. 1.]

Left to itself the encoder sorts alphabetically, making high 0, low 1 and medium 2. An ordering that says medium is more than low is more than high. If a variable has a genuine order, state it. If it does not, do not use an ordinal encoder at all.

One-hot, and the argument you must not omit

import pandas as pd
from sklearn.preprocessing import OneHotEncoder

train = pd.DataFrame({'contract': ['Month-to-month', 'One year', 'Two year']})
new = pd.DataFrame({'contract': ['Two year', 'Weekly trial']})

enc = OneHotEncoder(handle_unknown='ignore', sparse_output=False)
enc.fit(train)
print('columns:', list(enc.get_feature_names_out()))
print('unseen category encodes as all zeros:')
print(enc.transform(new))

columns: ['contract_Month-to-month', 'contract_One year', 'contract_Two year']
unseen category encodes as all zeros:
[[0. 0. 1.]
 [0. 0. 0.]]

Without handle_unknown='ignore' this raises

Marketing launches a Weekly trial contract, your scoring job meets a category the encoder has never seen, and it throws. At three in the morning. handle_unknown='ignore' encodes the unknown as all zeros, which is the sane default: the model treats it as none of the known categories rather than falling over.

Rare categories

import pandas as pd
import numpy as np

rng = np.random.default_rng(0)
cities = (['London'] * 400 + ['Manchester'] * 250 + ['Leeds'] * 120 +
          ['Bristol'] * 60 + ['Hull'] * 8 + ['Truro'] * 5 + ['Kirkwall'] * 2)
s = pd.Series(rng.permutation(cities), name='city')

counts = s.value_counts()
print(counts)

keep = counts[counts >= 50].index
grouped = s.where(s.isin(keep), 'Other')
print('\nafter grouping rare levels:')
print(grouped.value_counts())

city
London        400
Manchester    250
Leeds         120
Bristol        60
Hull            8
Truro           5
Kirkwall        2
Name: count, dtype: int64

after grouping rare levels:
city
London        400
Manchester    250
Leeds         120
Bristol        60
Other          15
Name: count, dtype: int64

Kirkwall appears twice. A one-hot column that is 1 for two rows out of a thousand cannot support a reliable estimate, and in cross-validation it may be entirely absent from some folds. Grouping the tail into Other keeps the information that these are unusual without pretending you can learn each one.

min_frequency does this for you

OneHotEncoder(min_frequency=50) groups anything rarer into a single infrequent bucket, and it learns the threshold on training data only. Prefer it to a manual pass, because doing it by hand before the split means the decision was made using test rows.

Text, briefly

from sklearn.feature_extraction.text import TfidfVectorizer

notes = [
    'billing error on the latest invoice',
    'speed very slow in the evening',
    'invoice wrong again, second billing error',
    'router keeps dropping the connection',
]
vec = TfidfVectorizer(stop_words='english', min_df=1)
X = vec.fit_transform(notes)
print('shape:', X.shape)
print('vocabulary:', sorted(vec.vocabulary_)[:10])
print('\nrow 0 non-zero terms:')
names = vec.get_feature_names_out()
row = X[0].toarray().ravel()
for i in row.argsort()[:-1][:4]:
    if row[i] > 0:
        print('  %-12s %.3f' % (names[i], row[i]))

shape: (4, 13)
vocabulary: ['billing', 'connection', 'dropping', 'error', 'evening', 'invoice', 'keeps', 'latest', 'router', 'second']

row 0 non-zero terms:

TF-IDF weights a term by how often it appears in this document against how rare it is across all of them, so common words score low and distinctive ones score high. It produces a sparse matrix that goes straight into any scikit-learn model. Week 13 covers the sequence models that improve on it.

Day 6 takeaway

Let cardinality choose the encoding. State the order for genuinely ordinal variables and never let an encoder infer one alphabetically. Always pass handle_unknown='ignore'. Group rare levels with min_frequency so the decision is learned from training data. Drop identifiers.

Week 02 · Day 7 of 7

A Reproducible Data Quality Report

Turning a week of manual checks into one function you run on every extract

By Pawan Rama Mali651 words

Exploration produces understanding, and understanding evaporates. Today you turn a week of scattered checks into one function you can run against any new extract.

What a data quality report should answer

What shape is it, and did that change since last time?
Which columns have the wrong type?
Where are the gaps, and how big?
Which columns are constant, or nearly so?
Which are duplicated in content under different names?
Are there duplicate rows?
What is the target's base rate?

The report

import pandas as pd
import numpy as np

def profile(df, target=None):
    """One row per column, with the checks worth running every time."""
    rows = []
    for col in df.columns:
        s = df[col]
        top = s.value_counts(dropna=True)
        rows.append({
            'column': col,
            'dtype': str(s.dtype),
            'missing_pct': round(100 * s.isna().mean(), 2),
            'distinct': s.nunique(dropna=True),
            'top_value': None if top.empty else str(top.index[0])[:18],
            'top_share': 0.0 if top.empty else round(100 * top.iloc[0] / len(s), 1),
        })
    out = pd.DataFrame(rows)
    out['constant'] = out['distinct'] <= 1
    out['id_like'] = out['distinct'] == len(df)
    return out

df = pd.read_csv('customers.csv')
print(profile(df).to_string(index=False))

          column   dtype  missing_pct  distinct        top_value  top_share  constant  id_like
     customer_id  object         0.00      3000           C00507        0.1     False    False
     signup_date  object         0.00        72       2019-01-01        4.2     False    False
   tenure_months   int64         0.00        72               72        4.2     False    False
        contract  object         0.00        15   Month-to-month       51.3     False    False
internet_service  object         0.00         3      Fibre optic       45.1     False    False
  payment_method  object         0.00         4 Electronic check       33.6     False    False
  has_dependents  object         6.09         2               No       65.8     False    False
   support_calls   int64         0.00         9                1       34.1     False    False
 monthly_charges float64         5.45      2232             15.0        5.3     False    False
   total_charges  object         0.00      2969                         0.5     False    False
         churned   int64         0.00         2                0       73.2     False    False

The checks that belong beside it

import pandas as pd

def integrity(df, target=None):
    print('rows %d, columns %d' % df.shape)
    print('duplicate rows      %d' % df.duplicated().sum())
    obj = df.select_dtypes('object').columns
    coercible = [c for c in obj
                 if pd.to_numeric(df[c], errors='coerce').notna().mean() > 0.9]
    print('text but ~numeric   %s' % (list(coercible) or 'none'))
    padded = [c for c in obj if (df[c].fillna('') != df[c].fillna('').str.strip()).any()]
    print('has padded values   %s' % (list(padded) or 'none'))
    if target:
        rate = df[target].mean()
        print('target base rate    %.3f  (majority guess scores %.3f)'
              % (rate, max(rate, 1 - rate)))

df = pd.read_csv('customers.csv')
integrity(df, target='churned')

rows 3138, columns 11
duplicate rows      138
text but ~numeric   ['total_charges']
has padded values   ['contract', 'total_charges']
target base rate    0.268  (majority guess scores 0.732)

Every defect week 1 found by hand, found automatically: the numeric column stored as text, the padded category values, the duplicated rows, and the base rate any model has to beat.

Correlated pairs worth knowing about

import pandas as pd
df = pd.read_csv('customers.csv').drop_duplicates().copy()
df['contract'] = df['contract'].str.strip().str.title()
df['total_charges'] = pd.to_numeric(df['total_charges'], errors='coerce')
import numpy as np

num = df.select_dtypes('number').drop(columns=['churned'])
corr = num.corr().abs()

# Upper triangle only, so each pair appears once and never against itself.
mask = np.triu(np.ones(corr.shape, dtype=bool), k=1)
pairs = (corr.where(mask).stack().sort_values(ascending=False))
print(pairs[pairs > 0.5].round(3))

tenure_months  total_charges    0.829
dtype: float64

tenure_months and total_charges at 0.83. Not a reason to delete a column yet. It is a reason to expect unstable coefficients from a linear model, which week 4 will demonstrate and fix with regularisation.

Your assignment

Run profile and integrity against a dataset you have not seen, anything from your own work, or a public CSV. Write down three things the report told you that you would not have checked by hand. Then add a check of your own for whatever it missed.

Day 7 takeaway

Turn exploration into a function. A profile that reports dtype, missingness, cardinality and dominance per column, alongside integrity checks for duplicates, text-that-is-really-numeric and padded values, catches on any new extract what took you a week to find by hand on this one.