Cheat sheet · BI & Data Science

Two columns, no chrome, one section per half. Ctrl+P (Windows) / ⌘P (Mac).

BI & Data Science · Pre mid-sem

The vocabulary

BI: the systematic process of turning raw data into meaningful information. Data science: an interdisciplinary field combining mathematics, statistics and computer science.
Three analytics: descriptive (what happened) - predictive (what will happen) - prescriptive (what to do).
4 Vs: Volume, Velocity, Variety, Veracity. DIKW: Data - Information - Knowledge - Wisdom.
Data types: quantitative (continuous / discrete) against qualitative (nominal / ordinal). The type decides the chart.
Six-step ML process: input data - analyse - find patterns - build model - predict - feedback. Section B's five-step version: sampling, preparation, partitioning, construction, assessment.
Supervised labelled, direct feedback, prediction. Unsupervised unlabelled, no feedback, hidden structure. Classification = categorical output; regression = continuous. Linear regression is a regression algorithm, not a classifier.

Optimisation: formulate, then solve

Decision variables (with units) Objective: Max or Min, linear in the variables Constraints: one per limited resource, with units Non-negativity: x, y >= 0
Corner points. A linear objective over a convex region is optimised at a corner. Plot every constraint, list every corner including intersections, evaluate the objective at each, take the best.
Slack = unused capacity on a <= constraint. Surplus = excess on a >= constraint. Zero means binding. A shadow price is only non-zero on a binding constraint.
0-1 patterns mutually exclusive: x1 + x2 <= 1 contingent: x4 <= x3 at least one of: x1 + x2 + x3 >= 1 at most k: sum of x <= k
Rounding an LP answer is not the integer answer. It may be infeasible, and even when feasible it may be worse than the true integer optimum. Add int or bin constraints instead.
Solver engines. Simplex LP when everything is linear (global optimum). GRG Nonlinear when smooth but non-linear (local only, use multistart). Evolutionary when non-smooth: IF, MAX, MIN, ABS, VLOOKUP.
Layout rule. Changing cells hold numbers, never formulas. Every right-hand side in its own cell. Objective as SUMPRODUCT. Point Solver at the cells that change.

Distance-based methods

Euclidean: d = sqrt( (x1-x2)^2 + (y1-y2)^2 ) Min-max: x' = (x - min) / (max - min) Standardise: z = (x - mean) / sd
Always rescale first. Unnormalised, the widest column decides every distance: on the class credit data revolving balance supplies over 99.9 % of the squared distance against utilisation.
k-NN in three steps: distance to every training point, rank and keep k, then majority vote (classification) or mean (regression). Odd k for two classes. No training phase: lazy learning, so all training data stays in memory.
k small follows noise (high variance). k large tends to the majority class (high bias). Tune on validation data, never on the test set you report.
k-means loop: pick k centroids, assign each point to its nearest, recompute each centroid as the mean of its members, repeat until nothing moves.
WCSS = sum over clusters of squared distances to that centroid Elbow: run k = 1..5, plot WCSS, keep k where the fall flattens
Four clustering families: centroid-based, connectivity-based, distribution-based, density-based. Two algorithms taught: k-means and hierarchical. Hierarchical gives a dendrogram and needs no k in advance; linkage rules are single, complete, average, centroid and Ward.

Decision trees (Section B only)

Entropy = -p1*log2(p1) - p2*log2(p2) Weighted entropy = SUM (n_branch/n) x Entropy(branch) Information gain = parent entropy - weighted entropy
Split on the attribute with the lowest weighted entropy, equivalently the highest information gain. Pure node = 0 bits; 50/50 node = 1 bit.
Excel: =-I4*LOG(I4,2)-I5*LOG(I5,2). Guard a zero-probability branch with LOG(p + 0.000001, 2).
The class's own numbers on ten insurance customers, 6 Yes and 4 No: root entropy 0.9710. Age split weighted entropy 0.8464; Income split 0.5510. Income has the lower weighted entropy, so Income is chosen first, gain 0.4201 bits.

Logistic regression in Excel

Z = b0 + b1x1 + ... + bnxn P(y=1) = EXP(Z)/(1+EXP(Z)) Likelihood = IF(y=1, P, 1-P) LLH = LN(Likelihood) Objective = SUM(LLH) -> Max, GRG Nonlinear, changing the coefficients Class = IF(P >= 0.5, 1, 0)
Why not a straight line: it leaves the interval 0 to 1, its errors cannot be normal on a 0/1 target, and it forces one constant effect everywhere.
Why logs: multiplying 800 numbers below 1 underflows to zero. Why not maximise accuracy: it is a step function with no gradient for Solver to climb.
Coefficients. Positive raises the probability, negative lowers it. Never rank two coefficients by raw size: each is per one unit of its own predictor. Class model: intercept -1.1119, Age -0.00088, Income +0.03772, Education +0.01903, Married -0.52709.
The split. 800 train, 200 holdout. Solver sees only the 800. Metrics are computed on the 200 only. A large gap between the two is over-fitting.

Judging a classifier

Predicted Yes Predicted No Actual Yes TP FN Actual No FP TN TP = COUNTIFS(actual,1,pred,1) TN = COUNTIFS(actual,0,pred,0) FP = COUNTIFS(actual,0,pred,1) FN = COUNTIFS(actual,1,pred,0)
Accuracy = (TP+TN)/(TP+TN+FP+FN) Precision = TP/(TP+FP) Recall = TP/(TP+FN) (Sensitivity) Specificity = TN/(TN+FP) F1 = 2(P x R)/(P + R) harmonic mean
Type I = FP = rejecting a true hypothesis = loss of resources. Type II = FN = accepting a false one = loss of opportunity.
Which metric. FP expensive then precision. FN expensive then recall. Both cost and you need one number then F1. Accuracy alone fails on imbalanced classes: quote the base rate beside it.
The cut-off is the lever. Raising it: fewer Yes predictions, precision up, recall down. Nothing about the coefficients changes. Section B sweeps several cut-offs; the ROC curve plots the trade-off at every one, and its area (AUC) summarises the classifier.
Insurance worked example: 20 predictions, TP 6, TN 8, FP 4, FN 2. Accuracy 70 %, precision 60 %, recall 75 %, specificity 8/12 = 66.67 %, F1 66.67 %. The slide's 80 % specificity contradicts its own other figures.

Bayes

P(A|B) = P(A and B) / P(B) P(A|B) = P(B|A)P(A) / [ P(B|A)P(A) + P(B|notA)P(notA) ]
Prior P(A) before the evidence. Likelihood P(B|A) how well A explains it. Posterior P(A|B) after.
Base-rate trap. Sensitivity 99 %, false-positive rate 5 %, prevalence 1 %: P(disease | positive) = 0.0099 / 0.0594 = 1 in 6. Raise prevalence to 30 % and the same test gives 89 %.
Naive Bayes: score(class) = P(class) x P(x1|class) x ... x P(xn|class) then normalise by the sum of the class scores P(level|class) = COUNTIFS(target,class,pred,level) / COUNTIF(target,class)
Zero-frequency problem: one absent level makes the whole product zero. Fix with Laplace smoothing, adding 1 to every count.

Formula cards

Mean of a column

mean = SUM(values) / n Excel: =AVERAGE(range)

Turning raw data into information, the second rung of the DIKW ladder. Always check the answer lies between the smallest and largest values.

SUM(values): Total of every observation
n: Number of observations

Growth and uplift percentage

growth % = (later - earlier) / earlier x 100

Comparing two periods or two groups. The denominator is always the figure you are comparing against; swapping it changes the answer.

later: Value of the newer period or the group being described
earlier: Value of the base period or the reference group

Relative frequency

relative frequency = count in category / n Excel: =COUNTIF(range, category) / COUNTA(range)

Turning a raw categorical column into a distribution that can be charted. The relative frequencies must sum to 1.

count in category: Number of observations with that value
n: Total number of observations, not the number of categories

Row and column percentage in a cross-tab

row % = cell / row total column % = cell / column total

Reading a cross-tabulation. Decide first which conditional question you are asking; the two percentages answer different questions and rarely agree.

cell: Count in one row-column combination
row total, column total: Margin totals of the cross-tab

Equal-interval class width for a choropleth

class width = (max - min) / number of classes

Choosing shading bands for a map. Compare against quantile bands before publishing: the same data looks calm or alarming depending on the choice.

max, min: Largest and smallest values in the variable
number of classes: How many shading bands the legend will have

The LP model template

Maximise (or Minimise) Z = c1 x1 + c2 x2 + ... + cn xn subject to a11 x1 + a12 x2 + ... <= b1 a21 x1 + a22 x2 + ... <= b2 x1, x2, ..., xn >= 0

Every formulation question. Write the four blocks in this order: variables in words, objective with direction, one line per constraint, non-negativity.

xj: Decision variable j, defined in words with units
cj: Objective coefficient: profit or cost per unit of xj
aij: Amount of resource i used by one unit of xj
bi: Amount of resource i available, the right-hand side

Corner point of two constraints

Solve the two boundary equations simultaneously: a11 x + a12 y = b1 a21 x + a22 y = b2 Subtract when a term matches; else substitute.

Listing the corners of a two-variable feasible region. Discard any intersection that violates another constraint or non-negativity.

b1, b2: Right-hand sides of the two constraints
(x, y): The candidate corner point

Slack and surplus

slack = RHS - LHS (for a <= constraint) surplus = LHS - RHS (for a >= constraint) Binding <=> slack = 0

After solving, to say which resource limits the business. Solver's Answer Report prints this column for you.

LHS: Left-hand side evaluated at the optimal solution
RHS: The stated limit

Binary decision model

Maximise SUM (return_j x x_j) subject to SUM (cost_ij x x_j) <= budget_i for each period i x_j in {0, 1}

Any accept-or-reject list: projects, plants, machines, product launches. Declared in Solver as a bin constraint on the changing cells.

x_j: 1 if item j is chosen, 0 otherwise
return_j: NPV or return of item j
cost_ij: Resource or cash item j consumes in period i
budget_i: Resource available in period i

Logical constraints on binaries

at most one of 1, 2 x1 + x2 <= 1 exactly one of 1, 2 x1 + x2 = 1 4 only if 3 x4 <= x3 at least one of a set SUM x_j >= 1 at most k of a set SUM x_j <= k if 1 or 3 then 5 x5 >= x1 AND x5 >= x3

Translating the words of a problem statement into algebra. Always test a conditional constraint by substituting 0 and 1 for the trigger.

x_j: Binary decision, 1 for chosen
k: The stated maximum number of items

LP relaxation as a bound

Maximisation: Z(integer) <= Z(relaxation) Minimisation: Z(integer) >= Z(relaxation)

Judging how much the integer restriction costs, and knowing when further search is pointless. Example: relaxation 93,333, integer optimum 92,000, gap 1.4 %.

Z(relaxation): Objective with the integer requirement dropped
Z(integer): Best objective achievable with whole numbers

Revenue as a non-linear function of price

R = p x q with q = a - b p => R = a p - b p^2 Peak at p = a / (2b)

Any pricing question where quantity falls with price. The p^2 term makes it non-linear, and the negative coefficient makes it concave, so the peak is a global maximum.

p: Price, the decision variable
a: Demand at a price of zero
b: Units of demand lost per rupee of price

Convex cost with a ratio term

TC(Q) = (D / Q) x S + (Q / 2) x H Minimum at Q* = sqrt( 2 D S / H ) At Q*, ordering cost = holding cost

The standard example of a non-linear but convex model. Useful as a check: if the two cost components are not equal, you are not at the optimum.

D: Annual demand in units
S: Cost of placing one order
H: Cost of holding one unit for one year
Q: Order quantity, the decision variable

Engine choice rule

linear everywhere -> Simplex LP (global, exact) smooth non-linear -> GRG Nonlinear (local; global if convex/concave) kinked or discontinuous -> Evolutionary (no guarantee)

Before every Solver run. Take the strongest engine the model allows, and reformulate a kink away if you can.

smooth: Has a gradient everywhere: products, powers, ratios, exponentials
kinked: Contains IF, ABS, MAX, MIN or VLOOKUP

The supervised learning relation

Y = f(X) fit f on labelled rows, then predict Y for new x

Every supervised task. If you cannot point at the column that holds Y, the task is unsupervised and this does not apply.

X: Input variables, the predictors or features
Y: Output variable, the label or target
f: The mapping the algorithm learns

Train-test split

training rows = 0.80 x n validation rows = 0.20 x n accept when performance(train) ~ performance(validation)

Before fitting anything. The class logistic-regression workbook uses 800 train and 200 test on 1,000 rows, exactly this ratio.

n: Total labelled rows available
0.80 / 0.20: Section B's stated default split; the ratio is a decision to record

KNN aggregation rule

classification: predicted class = majority vote of the k nearest labels regression: predicted value = average of the k nearest targets

Whenever the same neighbours must produce a label or a number. It is the one line of the algorithm that changes between the two tasks.

k: How many nearest neighbours are consulted

Euclidean distance

D(X1, X2) = sqrt( (x11 - x21)^2 + (x12 - x22)^2 + ... + (x1n - x2n)^2 ) For assignment, compare the squared value and skip the root.

Any continuous-scale similarity: k-means assignment, KNN, load-distance. Standardise the columns first or the widest one decides the answer.

x1j, x2j: Value of variable j for observations 1 and 2
n: Number of variables

Standardising a column

z = (x - mean) / standard deviation (z-score) or x' = (x - min) / (max - min) (min-max, range 0 to 1)

Before any distance-based method. z-scoring in Excel is =(x - AVERAGE(col)) / STDEV.S(col); in R it is scale(data).

mean, standard deviation: Computed down the column, not across the row
min, max: Smallest and largest values in the column

Centroid and WCSS

centroid_j = mean of variable j over the cluster's members WCSS = SUM over clusters SUM over members ( distance to own centroid )^2

The update step of k-means, and the elbow plot. WCSS falls monotonically in k and is zero at k = n, so read the bend rather than minimising it.

centroid_j: The cluster's mean on variable j
k: Number of clusters, fixed before k-means runs

Euclidean distance (KNN)

d = sqrt( (x1 - x2)^2 + (y1 - y2)^2 ) Excel: =SQRT(($B$6-B2)^2 + ($C$6-C2)^2)

Step 1 of KNN, once per training row. Normalise every feature first, or the widest column decides the neighbours.

(x1, y1): The test point being classified
(x2, y2): A training point with a known label

Min-max normalisation

x' = (x - min) / (max - min) R: preProcess(data, method = c("range"))

Before every distance-based method. The min and max are taken down each column of the training data, and the same figures must be reused on the test rows.

min, max: Smallest and largest value of that feature in the training data
x': The rescaled value, between 0 and 1

KNN prediction rule

classification: predicted class = mode of the k nearest labels regression: predicted value = mean of the k nearest targets k odd for two classes

Step 3, after the distances are ranked. The same neighbour set serves both tasks; only this line changes.

k: Number of neighbours consulted, chosen on held-back data
mode: The most common label; ties broken by the nearer neighbour

Entropy of a node

Entropy = -SUM p_i log2(p_i) Excel: =-p1*LOG(p1,2) - p2*LOG(p2+0.000001,2) Pure node 0; two-class 50/50 node 1

At every node of a tree, and for every candidate branch. The 0.000001 nudge avoids #NUM! when a branch has a zero-probability class.

p_i: Proportion of the node's observations in class i
log2: Logarithm to base 2, so entropy is in bits

Weighted entropy and information gain

Weighted entropy = SUM ( n_branch / n_parent ) x Entropy(branch) Information gain = Entropy(parent) - Weighted entropy Choose the split with the lowest weighted entropy

Choosing which attribute to split on. Both rules pick the same attribute, because the parent entropy is a constant across candidates.

n_branch: Observations sent to that branch
n_parent: Observations at the node being split

Gini impurity

Gini = 1 - SUM (p_i)^2 Pure node 0; two-class 50/50 node 0.5

The alternative to entropy, used where logarithms are inconvenient. Usually picks the same split; on the class data both choose Income.

p_i: Proportion of the node's observations in class i

Logit

Z = b0 + b1*x1 + b2*x2 + ... + bn*xn

The first column of every logistic-regression sheet. Unbounded, and not yet a probability.

b0: Intercept, the value of Z when every predictor is zero
bi: Change in Z per one unit of predictor i, all else equal
xi: The value of predictor i for this row

Sigmoid (logistic function)

P(y=1) = e^Z / (1 + e^Z) Excel: =EXP(Z)/(1+EXP(Z))

Immediately after Z. Turns the score into a probability. Z = 0 gives 0.5.

Z: The logit for this row
P(y=1): Predicted probability that the row belongs to class 1

Likelihood and log-likelihood

Likelihood = IF(y = 1, P, 1 - P) Objective = SUM( LN(likelihood) ) -> maximise with GRG Nonlinear

The fitting objective. Solver changes only the coefficient cells; every other column recalculates.

y: The actual class of the row, 0 or 1
P: The row's predicted probability of class 1
SUM(LN): Log-likelihood of the whole training set, always negative

Classification cut-off

predicted class = IF( P(y=1) >= 0.5, 1, 0 )

On the holdout rows, once the coefficients are frozen. Change the 0.5 to trade precision against recall.

0.5: The cut-off, a business decision rather than a statistical one

The five classification metrics

Accuracy = (TP + TN) / (TP + TN + FP + FN) Precision = TP / (TP + FP) Recall = TP / (TP + FN) (Sensitivity) Specificity = TN / (TN + FP) F1 = 2 x (Precision x Recall) / (Precision + Recall)

On the test rows only, from the four COUNTIFS cells. Choose which one to report from the cost of each error.

TP: Actual 1, predicted 1
FN: Actual 1, predicted 0. Type II error, loss of opportunity
FP: Actual 0, predicted 1. Type I error, loss of resources
TN: Actual 0, predicted 0

Conditional probability

P(A | B) = P(A and B) / P(B) P(A and B) = P(A | B) x P(B)

Whenever the question says "given that". Read the numerator off the joint cell and the denominator off the B total.

P(A and B): Probability both happen, the joint cell over the grand total
P(B): Probability of the condition, the new denominator

Bayes' theorem

P(A | B) = P(B | A) x P(A) / P(B) P(B) = P(B | A) x P(A) + P(B | not A) x P(not A)

When you are given the likelihood the wrong way round: a test's sensitivity, a machine's defect rate, a per-class table. Build the denominator over every cause first.

P(A): Prior, belief before the evidence
P(B | A): Likelihood, how well A explains the evidence
P(A | B): Posterior, belief after the evidence
P(B): Evidence, the total probability of what was observed

Naive Bayes score

score(class) = P(class) x P(x1|class) x P(x2|class) x ... x P(xn|class) P(class | x) = score(class) / sum of scores over all classes

Classifying a row with several categorical predictors. The scores are not probabilities until you divide by their total.

P(class): Prior, that class's share of the training rows
P(xi|class): Count of that level within the class, over the class size
n: Number of predictors, each contributing one factor

Conditional probability table in Excel

P(level | class) = COUNTIFS(target, class, predictor, level) / COUNTIF(target, class) Laplace smoothing: add 1 to every count before dividing

Building the tables from raw rows. Each predictor's entries must add to 1 within a class.

target: The class column, here Subscriber
predictor: One categorical column, here Age, Gender, Income or Location

BI & Data Science · Post mid-sem

Recommenders

Content-based: average this user's ratings on items sharing the attribute. Needs item attributes plus own history. Survives item cold start. Weakness: over-specialisation, it can only echo genres already rated.
Collaborative: uses the ratings matrix only, never the attributes. Can surprise. Weakness: cold start and sparsity.
sim(u,v) = CORREL(u's ratings, v's ratings) over shared items only P(u,i) = mean(u) + SUM sim(u,v) x (r(v,i) - mean(v)) ------------------------------- SUM |sim(u,v)|
Absolute values in the denominator. Signed similarities can nearly cancel: the class sheet then gives a prediction above 22 on a five-point scale. Clip the answer to the rating range.
Mean-centring removes the harsh-rater effect, and the target user's own average is added back so the answer lands on her scale.
Item-based is the same arithmetic transposed, and is preferred at scale: fewer items than users, and item-item similarities can be precomputed.

R, the forty lines the course uses

x <- 45; class(x) numeric / character / logical 1:10 seq(a, b, by=) seq(a, b, length.out=n) data.frame(A = c(...), B = c(...)) read.csv("file.csv") head() str() summary() dim() names()
Run str() first on every new file. It is the only one that shows column types, and a numeric column read as character breaks everything downstream.
df$Col one column, as a VECTOR df[rows, cols] comma separates the axes; blank = all df["Col"] no comma -> a one-column DATA FRAME subset(df, a & b) AND narrows | subset(df, a | b) OR widens df[order(df$x), ] ascending; order(-df$x) descending; keep the comma
|A or B| = |A| + |B| - |A and B|. An AND result can never exceed either condition; an OR result can never be smaller.
mean(x, na.rm = TRUE) drops NA from sum AND count df[which.max(df$y), ] the whole record of the maximum aggregate(y ~ g, data=df, FUN) R's PivotTable hist() barplot(names.arg=) plot(x,y,pch=19) boxplot(y ~ g) cor(x, y, use="complete.obs"); corrplot(cor(num_data), method="color")
NA is not 0. Coding a missing sale as zero drags every average down and lies about the count. Correlation matrix: n(n-1)/2 distinct pairs, diagonal always 1.

Association rules

Support(A to B) = both / ALL transactions Confidence(A to B) = both / transactions with A Lift(A to B) = Confidence / Support(B) = P(A and B) / (P(A) x P(B)) <- symmetric
How often, how sure, how strong. Lift > 1 real association, = 1 independent, < 1 negative (substitutes).
High confidence with lift 1 is worthless: the consequent is simply popular. Sort by lift, then check the count. Lift is symmetric, so it cannot tell you which item to discount; that is a margin decision.
apriori(trans, parameter = list(support = 0.005, confidence = 0.3, minlen = 2)) sort(rules, by = "lift", decreasing = TRUE)
Apriori principle: if an itemset is infrequent, every larger set containing it is too, so the branch is pruned unexamined. Minimum count = support x number of transactions, so 0.005 of 10,000 is 50 baskets.
Class figures: bread and butter, support 10 %, confidence 60 %, butter support 30 %, lift 2.0. On the class's own 400-row file every pairwise lift is between 0.90 and 1.08.

PCA

PCk score = w1*z1 + w2*z2 + ... + wp*zp (z standardised) prcomp(data, scale = TRUE, center = TRUE) eigenvalue = (reported standard deviation)^2 % variance = eigenvalue / p <- eigenvalues sum to p
Loadings are the weights, one per variable per component. Sign and size say how each contributes, and they are what a component is named from.
How many to keep: cumulative variance to 90 %, or Kaiser (eigenvalue >= 1, because a standardised variable has variance 1), or the scree elbow.
Section B's hospital table: 2.80, 1.60, 1.00, 0.35, 0.15, 0.10 summing to 6.00. Cumulative 46.7, 73.4, 90.0 %. Both rules keep 3.
Class car example: PC1 60 % is size and power (cyl, disp, hp, wt positive; mpg negative), PC2 24 % is performance and transmission (drat, gear, am). Together 84 %.
scale = TRUE is not optional. PCA maximises variance, and variance depends on units, so an unscaled large-unit column dominates. Biplot: points are observations, arrows are variables.
PCA is unsupervised, so it can discard a low-variance direction that would have predicted well. It also costs interpretability: a coefficient on a component cannot be acted on directly.

Reading a regression

y = b0 + b1x1 + ... + bpxp lm(y ~ x1 + x2, data = df) t = Estimate / Std. Error H0: coefficient = 0; p < 0.05 significant RSE = sqrt(SS_res / (n-p-1)) df = n - p - 1 R^2 = 1 - SS_res / SS_tot Adj R^2 = 1 - (1-R^2)(n-1)/(n-p-1) F = MS_reg / MS_res H0: every slope is zero
Interpret a slope in three parts: the unit of the predictor, the unit of the outcome, and holding the others constant. Drop the third and it is wrong.
Never rank coefficients by raw size. Each is per one unit of its own predictor; rescaling a predictor changes its coefficient without changing the model. Fix a realistic step for each, or standardise.
Adjusted R squared is the honest one: plain R squared can only rise when a predictor is added. Residuals: median near zero, 1Q and 3Q of similar magnitude.
F significant but one t not is normal: F tests all slopes jointly, t tests one given the others. Either that predictor is genuinely useless, or it is collinear.
VIF(xj) = 1 / (1 - R^2 of xj on the other predictors) > 10 high multicollinearity | < 5 comfortable standard error inflates by sqrt(VIF)
Section B's case: wait time and age correlate 0.969, VIF 28, and age shows p = 0.90. Remove wait time and age becomes p = 0.013. Adjusted R squared cost only 1.1 points.
Class figures. mtcars: mpg = 37.227 - 3.878 wt - 0.032 hp, RSE 2.6 on 29 df, adj R squared 81.48 %, F p 9.109e-12. 40 stores: R squared 0.99075, adj 0.98997, F 1284.63, StoreSize p 0.4051 (insignificant).

Classification in R

set.seed(123) sample.split(df$y, SplitRatio = 0.7) | initial_split(df, prop=0.7, strata=y) glm(y ~ x1 + x2, data = train, family = binomial) predict(model, test, type = "response") <- probabilities, not log-odds ifelse(prob > 0.5, 1, 0); factor(pred, levels = c(0,1)) confusionMatrix(pred, actual, positive = "1")
preProcess(df[,1:8], method = c("range")) min-max to [0,1] df_norm$target <- df$target re-attach: numeric only knn(train = trX, test = teX, cl = trY, k = 5) returns predictions, no model
strata keeps each class's share equal in both sets. Split before preprocessing, or the test rows shape the scaling.
Omit type = "response" and predict returns log-odds; a 0.5 threshold then applies to the wrong scale (the equivalent cut-off there is 0).
No Information Rate = largest class count / test rows. The accuracy of doing nothing. caret prints it under the accuracy; never quote accuracy without it.
positive = decides what sensitivity, specificity and precision mean. Predictions first, truth second, or every off-diagonal metric flips.
Eager against lazy: glm fits once and predicts instantly with interpretable coefficients; knn fits nothing and pays at every prediction, with no summary to report.

Simulation and forecasting

What-if changes an input by hand. Risk analysis gives uncertain inputs distributions and reports a distribution of outcomes, so it can state the probability of a loss.
NORM.INV(RAND(), mean, sd) one draw one trial = draw every uncertain input, run the model, record P(outcome) = COUNTIF(results, criterion) / trials
Verification: does the sheet do what you intended. Validation: does the model represent reality. A verified model can be entirely invalid.
Four components: horizontal, trend, seasonal (fixed period), cyclical (no fixed length). Identify them before choosing a method.
Naive: F(t+1) = Y(t) <- the benchmark MA(k): mean of the last k actuals <- lags a trend WMA: weights summing to 1, newest largest ES: F(t+1) = a*Y(t) + (1-a)*F(t) = F(t) + a*(Y(t) - F(t)) Trend: F(t) = b0 + b1*t; seasonality: s-1 dummies
e(t) = Y(t) - F(t) MAD = mean|e| | MSE = mean e^2 | RMSE = sqrt(MSE) | MAPE = mean(|e|/Y) x 100 signed mean error = bias
Only MAPE is comparable across series, and it fails on a zero actual. Judge on held-back periods, and tune alpha or k there, not on the data you report.
Bass: S(t) = ( p + q x N(t-1)/m ) x ( m - N(t-1) ) year 1, N = 0: S(1) = p x m
p innovation (needs nobody else to own one), q imitation (times the ownership share), m market potential. Left bracket rises, right shrinks: the product is the hump. Take p and q from an analogous product.
This chapter has no class slides in either section. Open BIDS NOTES.pdf, the 51-page scan, before the exam.

Formula cards

Content-based prediction

P(u,i) = average of u's ratings on items sharing i's attribute

When the item has attributes and the user has some history. Works from the first rating, and on items nobody has rated yet.

u: The target user
i: The item whose rating is missing
attribute: In the assignment, the Type column: Sci-Fi or Action

Pearson similarity between users

sim(u,v) = CORREL(ratings of u, ratings of v) computed over the items both rated only

Step one of user-based collaborative filtering, once per other user. Anchor the target user's range with dollar signs so the formula fills down.

sim(u,v): Correlation from -1 to +1; +1 identical taste, -1 opposite
shared items: Columns where both users have a numeric rating; CORREL selects them automatically

User-based collaborative filtering prediction

P(u,i) = rubar + [ sum over v in N of sim(u,v) x (r(v,i) - rvbar) ] / [ sum over v in N of |sim(u,v)| ] class layout: Ans = (Sum of Correl x Difference) / (Sum of |Correl|) + Average(u)

Filling one blank cell of the ratings matrix. Clip the answer to the rating range: the formula is unbounded.

rubar: The target user's own average rating, the baseline
N: Neighbourhood: only the users who themselves rated item i
r(v,i) - rvbar: Neighbour v's mean-centred rating of i, the Difference column
|sim(u,v)|: Absolute similarity, so positive and negative neighbours cannot cancel in the denominator

Item-based collaborative filtering prediction

P(u,i) = sum over j of sim(i,j) x r(u,j) / sum over j of |sim(i,j)|

The same arithmetic with the matrix transposed. Preferred at scale, because items are fewer than users and item-item similarities can be precomputed.

sim(i,j): Similarity between items i and j, from how users rated them
r(u,j): This user's own rating of the similar item j

Assignment and type checking

name <- value class(object) # "numeric" | "character" | "logical"

Every line of R starts here. Run class() whenever a calculation refuses to work or returns something odd.

<-: Assignment. A double equals is a comparison and stores nothing
class(): The object's type; a numeric column read as character is the commonest bug

Sequences

1:10 # both endpoints included seq(from, to, by = step) # fixes the gap seq(from, to, length.out=n) # fixes the count; gap = (to-from)/(n-1)

Building index vectors and axis values. Use by or length.out, never both.

by: Step size; may be negative to count down
length.out: How many values; the gap follows from n - 1

Selecting from a data frame

df$Col # one column, as a vector df[rows, cols] # blank side means all of them df[c("A","B")] # columns by name, returns a data frame df[c(1, 6)] # columns by position

Whenever a function needs one column of numbers, use the dollar sign. When it needs a smaller table, use the no-comma bracket form.

$: Extracts one column as a vector, dropping the table
[rows, cols]: The comma separates the two axes; blank means all

Filtering and sorting

subset(df, cond1 & cond2) # AND: both must hold, narrows subset(df, cond1 | cond2) # OR: at least one, widens df[order(df$Col), ] # ascending, keep the trailing comma df[order(-df$Col), ] # descending

Filtering rows and reordering a table. Use a double equals for equality tests.

&: AND; the result can never be larger than either condition alone
|: OR; |A or B| = |A| + |B| - |A and B|
order(): Returns positions, not values, so it goes in the row slot

Missing values

mean(x, na.rm = TRUE) cor(x, y, use = "complete.obs")

Any calculation over a column that may have gaps. Never substitute zero for a missing value.

NA: Value exists in the world but is absent from the data
na.rm = TRUE: Drop NAs from both the sum and the count

Group summaries

aggregate(y ~ g, data = df, FUN) df[which.max(df$y), ]

Totals, averages or counts within each level of a category, and pulling the whole record of the largest value.

y ~ g: The number to summarise, broken down by the grouping column
FUN: sum, mean, length, max: the function applied within each group
which.max: Position of the maximum, so it belongs in the row slot

The four base charts

hist(x, main =, xlab =, col =) one variable's spread barplot(h, names.arg =, col =) categories compared plot(x, y, col =, pch = 19) two variables together boxplot(y ~ group, data =, col =) spread within groups

Choose from the question being asked. Aggregate before barplot, and scatter before correlating.

main / xlab / ylab: Title and axis labels
pch: Point shape; 19 is a solid dot
names.arg: Bar labels, taken from the aggregated group names

Correlation and its heatmap

r = sum((x - xbar)(y - ybar)) / sqrt( sum(x - xbar)^2 x sum(y - ybar)^2 ) cor(x, y, method = "pearson") cor(num_data) # the matrix corrplot(cor(num_data), method = "color") # the heatmap distinct pairs = n(n - 1) / 2

After the scatter plot, and before any regression. A block of strong correlation among predictors is a multicollinearity warning.

r: -1 to +1, no units, measures the straight-line relationship only
diagonal: Always 1: a variable against itself
n: Number of numeric columns in the matrix

Support

Support(A to B) = P(A and B) = transactions with both / ALL transactions

First measure to compute. Says how widespread the pattern is, and therefore whether it is worth a business decision.

both: Count of transactions containing every item in the rule
ALL: Total number of transactions, the denominator that distinguishes support from confidence

Confidence

Confidence(A to B) = P(B | A) = transactions with both / transactions with A

How reliable the rule is when the antecedent is present. Not symmetric: reversing the rule changes it.

A: The antecedent, the left-hand side
transactions with A: The conditioning denominator

Lift

Lift(A to B) = P(B | A) / P(B) = Confidence(A to B) / Support(B) = P(A and B) / ( P(A) x P(B) ) <- shows it is symmetric > 1 real association | = 1 independent | < 1 negative association

The ranking measure. Always sort rules by lift, then check the count before believing one.

Support(B): The consequent's own frequency, the baseline being divided out
symmetry: Lift(A to B) = Lift(B to A), so lift cannot tell you which way to act

Apriori in R

rules <- apriori(trans, parameter = list(support = 0.005, confidence = 0.3, minlen = 2)) rules_sorted <- sort(rules, by = "lift", decreasing = TRUE) inspect(head(rules_sorted, 10))

Generating rules in bulk. Minimum count for a rule = support x number of transactions.

support: Minimum fraction of baskets; 0.005 of 10,000 is 50 baskets
confidence: Minimum reliability; 0.3 means the rule must hold 30 % of the time
minlen: Minimum items in the rule; 2 excludes single-item results

Principal component score

PCk score = w1 x z1 + w2 x z2 + ... + wp x zp where zj is the standardised value of variable j

Placing one observation on a component. The values must be standardised with the same means and standard deviations the loadings were computed from.

wj: Loading of variable j on this component; sign and size say how it contributes
zj: Standardised value: (raw value minus mean) over standard deviation
score: Centred at zero, so positive means above average for this dataset

Variance explained

eigenvalue(k) = ( standard deviation of PCk )^2 % variance = eigenvalue(k) / sum of all eigenvalues = eigenvalue(k) / p on standardised data cumulative % = running total

Reading a summary(prcomp) or an eigenvalue table. Check the eigenvalues sum to p before computing anything.

eigenvalue: Variance captured by that component
p: Number of variables; the total variance on standardised data

How many components to keep

Method 1: keep components until cumulative variance reaches 90 % Method 2: Kaiser, keep every component with eigenvalue >= 1 Scree plot: keep the components before the elbow (fviz_eig)

After the eigenvalue table. Use both, and if they disagree say which you took and what variance was lost.

90 %: Section B's stated threshold; other thresholds are used, so state yours
eigenvalue >= 1: A standardised variable has variance 1, so a lesser component explains less than one raw column

PCA in R

pca_result <- prcomp(data, scale = TRUE, center = TRUE) summary(pca_result) # sd, proportion and cumulative per PC pca_result$x # the scores, one row per observation fviz_eig(pca_result) # scree plot fviz_pca_biplot(pca_result, repel = TRUE)

Always with scale and center TRUE when the variables are in different units, which is nearly always.

scale = TRUE: Standardises spread, so a large-unit variable cannot dominate
center = TRUE: Subtracts each mean
$x: Scores: where each observation sits on each component

The fitted model, and t

y = b0 + b1*x1 + b2*x2 + ... + bp*xp t = Estimate / Std. Error H0: the coefficient is zero; p < 0.05 => significant

Reading the coefficients block. Every slope is a partial effect, so always add the holding-constant clause.

b0: Intercept; the prediction when every predictor is zero, often not meaningful
bj: Change in y per one unit of xj, holding the other predictors constant
Std. Error: How much the estimate would vary on resampling

Fit and precision

R^2 = 1 - SS_residual / SS_total, SS_total = sum (y - ybar)^2 Adj R^2 = 1 - (1 - R^2) x (n - 1) / (n - p - 1) RSE = sqrt( SS_residual / (n - p - 1) ) df = n - p - 1

Judging how much of the variation is explained and how large a typical error is. Quote the adjusted figure in a multiple regression.

n: Number of observations
p: Number of predictors, excluding the intercept
RSE: Average prediction error, in the units of y; read it against y's own scale

The F test

F = MS_regression / MS_residual = (SS_reg / p) / (SS_res / (n - p - 1)) H0: every slope coefficient is zero

Judging the model as a whole. A significant F with an insignificant t on one predictor is normal.

MS_regression: SS explained divided by p
MS_residual: SS unexplained divided by n - p - 1; its square root is the RSE

VIF, for multicollinearity

VIF(xj) = 1 / ( 1 - R^2 of xj regressed on the other predictors ) VIF > 10 high multicollinearity | VIF < 5 comfortable in business standard error inflates by sqrt(VIF)

Before trusting any individual coefficient, and always when a predictor is insignificant despite a strong model.

R^2 of xj: How well the other predictors already explain this one
sqrt(VIF): The factor by which the coefficient's standard error is widened

Min-max normalisation

x' = (x - min) / (max - min) R: preProcess(df[, features], method = c("range")) then predict(pre_proc_values, df[, features])

Before every distance-based method. Learn min and max on the training data and reuse them unchanged on the test rows.

min, max: Taken down each feature column of the training data
x': Rescaled value; the training minimum maps to 0 and the maximum to 1

The logistic classification loop in R

set.seed(123) split <- sample.split(df$y, SplitRatio = 0.7) train <- subset(df, split == TRUE); test <- subset(df, split == FALSE) model <- glm(y ~ x1 + x2, data = train, family = binomial) prob <- predict(model, newdata = test, type = "response") pred <- factor(ifelse(prob > 0.5, 1, 0), levels = c(0, 1))

A binary outcome with predictors on any scale. The 0.5 is a business choice, not a statistical one.

family = binomial: Selects the logistic link; without it glm fits a straight line
type = "response": Returns probabilities; the default returns log-odds
SplitRatio = 0.7: The training share, so the test set is 30 %

The k-NN loop in R

initial_split(df_norm, prop = 0.7, strata = target) train_data <- training(split); test_data <- testing(split) knn(train = trainX, test = testX, cl = trainY, k = 5)

All features numeric and normalised. Returns predictions directly: there is no model object, because nothing is fitted.

strata: Keeps each class's share the same in both sets
cl: Class labels of the training rows, passed separately from the features
k: Neighbours consulted; odd for two classes, tuned on validation data

caret's confusion matrix

confusionMatrix(pred, actual, positive = "1") Accuracy = (TP + TN) / n Sensitivity (recall)= TP / (TP + FN) Specificity = TN / (TN + FP) Pos Pred Value = TP / (TP + FP) (precision) No Information Rate = largest class count / n

Evaluating on the test set. Predictions first, truth second, and always read accuracy against the No Information Rate.

positive: Which class is the event; every off-diagonal metric is defined relative to it
No Information Rate: Accuracy of always predicting the majority class; the baseline to beat

Simulation in Excel

RAND() uniform 0 to 1 NORM.INV(RAND(), mean, sd) a normal draw P(outcome) = COUNTIF(results, criterion) / number of trials

Whenever an input is uncertain. A Data Table with one row per trial turns one model into a thousand.

deterministic input: Known and fixed, e.g. price or unit cost
probabilistic input: Uncertain, given a distribution, e.g. demand
trial: One draw of every uncertain input, run through the model

The classical forecasting methods

Naive: F(t+1) = Y(t) MA(k): F(t+1) = ( Y(t) + Y(t-1) + ... + Y(t-k+1) ) / k WMA: F(t+1) = w1*Y(t) + w2*Y(t-1) + ..., sum of w = 1 ES: F(t+1) = alpha*Y(t) + (1 - alpha)*F(t) = F(t) + alpha*( Y(t) - F(t) ) Trend: F(t) = b0 + b1*t

Choose from the components present. Naive is the benchmark; a moving average lags a trend; seasonality needs dummies or deseasonalising.

k: Periods in the moving average; larger smooths more and lags more
alpha: Between 0 and 1; the fraction of the last error that is corrected
t: Time index 1, 2, 3, ... for a trend regression

Forecast accuracy

e(t) = Y(t) - F(t) MAD = mean of |e(t)| MSE = mean of e(t)^2 RMSE = sqrt(MSE) MAPE = mean of ( |e(t)| / Y(t) ) x 100 bias = mean of e(t), signed

On held-back periods, never on the data the method was tuned to. Use MAPE to compare across series.

MAD / RMSE: In the data's own units, so not comparable between series
MAPE: Unitless percentage; undefined when an actual is zero
bias: Signed average error; consistently positive means under-forecasting

Bass diffusion model

S(t) = ( p + q * N(t-1)/m ) * ( m - N(t-1) ) year 1, with N = 0: S(1) = p * m

Forecasting a product with no sales history. Take p and q from an analogous product and m from market research.

m: Total market potential: everyone who will ever buy
p: Coefficient of innovation; drives sales when nobody owns one
q: Coefficient of imitation; multiplied by the ownership share N/m
N(t-1): Cumulative sales up to the end of the previous period

Back to BI & Data Science