Forem: Kelvin

Machine learning Ensemble models.

Kelvin — Fri, 01 May 2026 12:42:43 +0000

Ensemble Learning in machine learning integrates multiple models called weak learners to create a single effective model for prediction. This technique is used to enhance accuracy, minimizing variance and removing overfitting. Here we will learn different ensemble techniques and their algorithms.

Main types of ensemble models

1. Bagging - Bootstrap Aggregating.

Bagging is a technique that involves creating multiple versions of a model and combining their outputs to improve overall performance.
In bagging several base models are trained on different subsets of the training data, then aggregate their predictions to make the final decision. The subsets of the data are created using bootstrapping, a statistical technique where samples are drawn with replacement, meaning some data points can appear more than once in a subset.
The final prediction from the ensemble is typically made by either:

Averaging the predictions (for regression problems), or
Majority voting (for classification problems).

Common Bagging Algorithms.

1. Random Forest.

Random forest is an ensemble method based on decision trees. Multiple decision trees are trained using different bootstrapped samples of the data.
In addition to bagging, Random Forest also introduces randomness by selecting a random subset of features at each node, further reducing variance and overfitting.

Loading data from sklearn datasets.

#loading data
from sklearn.datasets import load_iris
data = load_iris()

splitting data.

#split data
X = data.data
y = data.target

from sklearn.model_selection import train_test_split

X_train,X_test, y_train, y_test = train_test_split(X,y, test_size=0.2, random_state= 42)

Training the model on random forest.

#Training the random forest

from sklearn.ensemble import RandomForestClassifier
rf_model = RandomForestClassifier(
    n_estimators=100, #number of trees
    max_depth= 5,
    random_state= 42)
rf_model.fit(X_train,y_train)

Predict.

# predict
from sklearn.metrics import accuracy_score, classification_report

print(f"Train accuracy: {accuracy_score(y_train, rf_model.predict(X_train)):.2%}")
print(f"Test accuracy: {accuracy_score(y_test, rf_model.predict(X_test)):.2%}")

2. Decision Trees.

In Bagged Decision Trees, multiple decision trees are trained using bootstrapped samples of the data.
Each tree is trained independently and the final prediction is made by averaging the predictions of all the trees in the ensemble.
Splitting data.

X = data.data
y = data.target
from sklearn.model_selection import train_test_split

X_train,X_test, y_train, y_test = train_test_split(X,y, test_size = 0.2, random_state = 42)

Training model.
unconstrained tree (will overfit on the data)

from sklearn.tree import DecisionTreeClassifier, plot_tree

tree_big = DecisionTreeClassifier(random_state = 42)
tree_big.fit(X_train,y_train)

constrained tree (better generalization)

tree_small = DecisionTreeClassifier(max_depth = 3, random_state = 42)
tree_small.fit(X_train,y_train)

Predictions

print ("Constrained tree results")
print(f"Train accuracy: {accuracy_score(y_train, tree_small.predict(X_train)):.2%}")
print(f"Test accuracy: {accuracy_score(y_test, tree_small.predict(X_test)):.2%}")

Visualization

# Vizualization
import matplotlib.pyplot as plt
plt.figure (figsize= (15,6))
plot_tree(tree_small, feature_names=data.feature_names, class_names=data.target_names, filled= True, rounded= True, fontsize=10)
plt.title('decision tree(depth = 3)')
plt.show()

2. Boosting.

Boosting is an ensemble technique where multiple models are trained sequentially, with each new model attempting to correct the errors made by the previous ones.
Boosting focuses on adjusting the weights of incorrectly classified data points, so the next model pays more attention to those difficult cases. By combining the outputs of these models, boosting typically improves the accuracy of the final prediction.

Common Boosting Algorithms.

1. AdaBoost - Adaptive Boosting.

AdaBoost works by adjusting the weights of misclassified instances and combining the predictions of weak learners (usually decision trees). Each subsequent model is trained to correct the mistakes of the previous model.
AdaBoost can significantly improve the performance of weak models, especially when used for classification problems.

2. Gradient Boosting.

Gradient Boosting is a more general approach to boosting that builds models sequentially, with each new model fitting the residual errors of the previous model.
The models are trained to minimize a loss function, which can be customized based on the specific task.
We can perform regression and classification tasks using Gradient Boosting.

loading data.

#loading data
from sklearn.datasets import load_iris
iris = load_iris()

splitting data.

#split data
from sklearn.model_selection import train_test_split

X_train,X_test,y_train,y_test = train_test_split (iris.data, iris.target, test_size = 0.2, random_state = 42)

Gradient boosting training.

#Gradient boosting training
from sklearn.ensemble import GradientBoostingClassifier
from sklearn.metrics import accuracy_score

gb = GradientBoostingClassifier(
    n_estimators = 100,
    learning_rate = 0.1, # how much each tree contributes
    max_depth = 3, # shallow trees works best for boosting 
    random_state = 42
    )
gb.fit(X_train, y_train)
print(f"Gradient boosting accuracy score:{accuracy_score(y_test, gb.predict(X_test)):.2%}")

3. XGBoost - Extreme Gradient Boosting.

XGBoost is an optimized version of gradient boosting. It includes regularization to prevent overfitting and supports parallelization to speed up training.
XGBoost has become a popular choice in machine learning competitions due to its high performance.

#create model XGBClassifier. eXtreme Gradient Boosting Classifier.

from xgboost import XGBClassifier
xgb = XGBClassifier(
    n_estimators = 100,
    learning_rate = 0.1,
    max_depth = 3,
    eval_metric = 'mlogloss',
    random_state = 42,
    verbosity = 0
)
#model training
xgb.fit(X_train, y_train)
print(f"XGBoost accuracy score: {accuracy_score(y_test,xgb.predict(X_test)):.2%}")

3. Stacking.

Stacking combines multiple models of different types, where each model makes independent predictions and a meta-model is trained to combine these predictions. Instead of simply averaging or voting, as in bagging and boosting, stacking trains a higher-level model (meta-model) to learn how to best combine the predictions of the base models.
In stacking, the base models are trained on the original data and their predictions are then used as features for the meta-model, which learns how to combine them effectively. The final prediction is made by the meta-model based on the combined outputs of all the base models.
Meta-model is a model that learns how to combine the predictions of the base models to generate the final output.

Common Stacking Algorithms.

1. Generalized Stacking.

In Generalized Stacking, multiple different models (e.g., decision trees, logistic regression, neural networks) are trained on the same dataset.
And a meta-model (such as a logistic regression or another decision tree) is trained on the predictions made by th andese base models.
The meta-model learns how to combine the predictions of the base models to make the final prediction.

2. Stacking with Cross-Validation.

In stacking with cross-validation, the base models are trained using cross-validation and their predictions on the validation set are used to train the meta-model.
This prevents overfitting and ensures that the meta-model is trained on unbiased data.

3. Multi-Layer Stacking

Multi-layer stacking involves multiple levels of base models, where the outputs of the first level of models are fed into a second level of base models and so on, before reaching the meta-model.
This approach creates a more complex ensemble that can capture a wider variety of patterns in the data.

Dimensionality Reduction in Machine Learning: PCA and t-SNE.

Kelvin — Fri, 01 May 2026 10:34:42 +0000

Dimensionality reduction is a fundamental concept in machine learning used to reduce the number of input features (dimensions) in a dataset while preserving as much important information as possible.

Principal Component Analysis (PCA)
Principal Component Analysis (PCA) is a linear dimensionality reduction technique that transforms data into a new coordinate system.

Instead of using the original features, PCA creates new variables called principal components, which are:

Linear combinations of the original features
Ordered by importance (variance explained)

PCA works by identifying directions (called principal axes) where the data varies the most.

The first principal component captures the maximum variance while the second principal component captures the next highest variance.

This allows us to:

Keep only the most informative components
Discard less important ones

How PCA Works

Standardize the data - Features must be scaled (very important for PCA)
Compute the covariance matrix - Shows relationships between features.
Compute eigenvalues and eigenvectors
Eigenvectors - directions (principal components)
Eigenvalues - importance (variance explained)
Sort components by eigenvalues - Highest variance first.
Select top K components - Reduce dimensions.
Transform the data - Project data onto new axes.

Workflow
Splitting data.

#splitting data
from sklearn.model_selection import train_test_split

X_train, X_test, y_train, y_test = train_test_split(X,y, random_state=42, test_size=0.2)
scaler = StandardScaler()
X_train = scaler.fit_transform(X_train)
X_test = scaler.transform(X_test)

Scaling data.

#scaling
from sklearn.preprocessing import StandardScaler
X_scaled = StandardScaler().fit_transform(X)

Training

#PCA Principal Components
from sklearn.decomposition import PCA

pca_full = PCA()
pca_full.fit(X_scaled)

This line of code is about understanding how much information the PCA model is capturing as you add more components.

import numpy as np
cumvar = np.cumsum(pca_full.explained_variance_ratio_)

Plotting a cumvar on 95% threshold.
You look for the point where the curve starts flattening (diminishing returns).

import matplotlib.pyplot as plt

plt.figure(figsize = (9,4))
plt.plot (cumvar, linewidth = 2)
plt.axhline(0.95, c = 'red', linestyle = '--', label = '95% threshold')
plt.axhline(0.99, c = 'orange', linestyle = '--', label = '99% threshold')
plt.xlabel('number of components')
plt.ylabel('cumulative explained variance')
plt.title('how many components to explain 95% of the variance')
plt.grid(alpha= 0.3)
plt.legend()
plt.show()

Training

# fit pca on training only
pca_train = PCA(n_components=0.95)

X_train_r = pca_train.fit_transform(X_train)
X_test_r = pca_train.transform(X_test)

Visualization
using the trained PCA model dimensions.

import matplotlib.pyplot as plt

plt.figure(figsize=(6,3))
scatter = plt.scatter(X_2d[:,0], X_2d[:,1], c = y, cmap = 'tab10', alpha=0.7, s = 20)
plt.colorbar(scatter, label = 'digit class')

plt.title('64-dimensional digit data projected to 2d via PCA')
plt.xlabel('PC1(highest variance direction)')
plt.ylabel('pc2(second highest variance direction)')
plt.show()

Distributed Stochastic Neighbor Embedding (t-SNE)

t-SNE is a non-linear dimensionality reduction technique specifically designed for visualizing high-dimensional data in 2D or 3D spaces. It works by modelling focuses pairwise similarities between data points in the high-dimensional space and optimizing their representation in a lower-dimensional space to preserve these similarities.

Unlike PCA t-SNE focuses on maintaining local relationships by minimizing the Kullback–Leibler divergence (KL divergence) between the high-dimensional and low-dimensional distributions of data points making it highly effective to find clusters and patterns in complex datasets.
However it takes a lot of time to run the results and it doesn't work well with very large datasets.
t-SNE is primarily used for exploratory data analysis and visualization rather than feature reduction or pre processing.

Importing load digits from sklearn datasets.

from sklearn.datasets import load_digits
data = load_digits()

X = data.data
y = data.target

Scaling data

# scaling data
from sklearn.preprocessing import StandardScaler
X_scaled = StandardScaler().fit_transform(X)

Generating 1000 figures randomly for easy training from the scaled data.

import numpy as np
ids = np.random.choice(len(X_scaled),1000, replace=False)
X_sub, y_sub = X_scaled[ids], y[ids]

Running t-sne.

from sklearn.manifold import TSNE
tsne = TSNE(n_components=2,
           perplexity=30,
           max_iter=1000,
           random_state=42,
)

X_tsne = tsne.fit_transform(X_sub)

Visualization.

#plotting
import matplotlib.pyplot as plt
plt.figure(figsize= (10,7))
scatter = plt.scatter(X_tsne[:,0],X_tsne[:,1], c = y_sub,cmap='tab10',alpha=0.7, s=25)
cbar= plt.colorbar(scatter)
cbar.set_ticks(range(10))
cbar.set_ticklabels([str(i)for i in range(10)])
cbar.set_label('Digit class')

Unsupervised Machine Learning. K-Means & Hierarchical Clustering

Kelvin — Thu, 30 Apr 2026 11:40:27 +0000

Unsupervised machine learning is a branch of machine learning where models are trained on data without labelled outcomes. Unlike supervised learning, where the goal is to predict a known target, unsupervised learning focuses on discovering hidden patterns, structures, or relationships within the data.

Common tasks in unsupervised learning include:

Clustering (grouping similar data points)
Dimensionality reduction

Clustering is the process of grouping data points such that points within the same cluster are similar and points in different clusters are dissimilar.

Similarity is usually measured using distance metrics like:

Euclidean distance (most common)
Manhattan distance
Cosine similarity

K-Means Clustering.

K-Means is a partition-based clustering algorithm that divides data into K distinct clusters, where K is predefined. The goal is to minimize the within-cluster variance, also called inertia.

How K-Means Works

Choose K (number of clusters) - Example: K = 3
Initialize centroids randomly - These are K points representing cluster centers.
Assign data points to nearest centroid - Each point is assigned to the cluster with the closest centroid (using distance, usually Euclidean).
Update centroids - Compute the new centroid as the mean of all points in that cluster. iterate steps 3 and 4 until Centroids stop changing, or maximum iterations is reached.

K-Means Workflow.

Scaling data

#scaling data
from sklearn.preprocessing import StandardScaler
scaler = StandardScaler()
X_scaled = scaler.fit_transform(customers)

Finding the best K.

# elbow method - finding the best number of clusters (K)
from sklearn.cluster import KMeans
inertias = []

for k in range (1, 11):
    km = KMeans (n_clusters=k, random_state=42, n_init=10)
    km.fit(X_scaled)
    inertias.append(km.inertia_)

Plotting the elbow curve. This helps identify the best K - where the curve starts to plateau.

#Plotting the elbow curve
import matplotlib.pyplot as plt
plt.figure(figsize=(8, 4))
plt.plot(range(1, 11), inertias, 'o--', linewidth=2, markersize=8)

plt.xlabel('Number of clusters K')
plt.ylabel('Inertia (Within-Cluster Sum of Squares)')
plt.title('Elbow method - Finding the optimal K')
plt.xticks(range(1, 11))
plt.grid(alpha=0.3)
plt.show()

Elbow curve. (4 is our best k)

Training the model
Fits our model on 4 clusters then creates a new column named 'cluster'.

km_final =KMeans(n_clusters=4, random_state=42, n_init=10)
customers['cluster'] = km_final.fit_predict(X_scaled)
customers

Profiling clusters.
This code is all about understanding what each cluster actually represents after you’ve created them with K-Means.

#profile for each cluster
profile = customers.groupby('cluster').agg({
'annual_spend': 'mean',
'visit_frequency': 'mean',
'avg_basket_size': 'mean',
'cluster': 'count'
}).rename(columns={'cluster': 'count'}).round(0)

print(" Cluster profiles")
print(profile)

Visualization of the clusters and the centroids.

colours = [ '#3498db','#2ecc71', '#e74c3c', "#12f3a4"]
labels = ['Mid-value','VIP customers', 'Low spenders' , 'Occasional']

plt.figure(figsize=(9, 5))
for c in range(4):
    mask = customers['cluster'] == c
    plt.scatter(
    customers[mask]['annual_spend'],
    customers[mask]['visit_frequency'],
    c=colours[c],
    label= labels[c],
    alpha=0.7,
    s=50
)
centroids_orig = scaler.inverse_transform(km_final.cluster_centers_)
plt.scatter(
    centroids_orig[:, 0], 
    centroids_orig[:, 1],
    s = 200, 
    marker = 'X', 
    c = 'black', 
    zorder = 5, 
    label = 'Centroids'
)

plt.xlabel('Annual Spend (KES)')
plt.ylabel('Visit Frequency (per month)')
plt.title('Customer Segments - K-Means (K=4)')
plt.legend()
plt.grid(alpha = 0.2)

Advantages of K-Means

Simple and fast
Works well on large datasets
Easy to interpret

Limitations of K-Means

Must specify K in advance
Sensitive to - Initial centroid placement & Outliers
Assumes clusters are spherical and equally sized

Hierarchical Clustering.

Hierarchical clustering builds a tree-like structure of clusters, called a dendrogram. Unlike K-Means, it does not require specifying the number of clusters upfront.

There are two types:

Agglomerative (bottom-up) – most common
Divisive (top-down)

Agglomerative clustering
steps

Start with all points separate: Treat each data point as its own cluster like A, B, C, ... Initially, you have n clusters for n data points.
Compute pairwise distances: Calculate the distance between every pair of clusters. Common choices include Euclidean, Manhattan or Cosine distance. Store these values in a distance matrix. To know more about them refer to: Measures of Distance
Merge the nearest clusters: Identify the two clusters that are closest based on the chosen linkage method such as single, complete, average or Ward linkage. Combine them into a single new cluster.
Update distances: Recalculate the distances between the newly formed cluster and all remaining clusters. Use the same linkage rule to ensure consistency.
Repeat the process: Continue merging clusters and updating distances iteratively. Stop when you reach a predefined number of clusters (k) or a distance threshold.
Visualize the results: Create a dendrogram to visualize how clusters merged at each step. Choose a suitable cut on the dendrogram to obtain the final cluster groups.

Linkage methods
How we measure the distance between clusters.

Single Linkage: Minimum distance between points
Complete Linkage: Maximum distance
Average Linkage: Average distance
Ward’s Method: Minimizes variance (most common)

Dendrogram
A dendrogram is a tree diagram that shows:

How clusters are merged
At what distance they are merged You can “cut” the dendrogram at a certain height to decide the number of clusters.

Hierarchical model workflow.
Picking a dataset.

# Picking a subset of 60 customers for readability
import numpy as np
subset_ids = np.random.choice(len(X_scaled),60, replace = False)
X_sub = X_scaled[subset_ids]

Linkage (Ward) to minimize variance.

# Linkage method
from scipy.cluster.hierarchy import linkage, dendrogram

Z = linkage(X_sub,method = 'ward')

Plotting the dendrogram.

# plot dendogram
import matplotlib.pyplot as plt

plt.figure (figsize= (14, 5))
dendrogram (Z, truncate_mode= 'level')
plt.axhline(y=6, c="red", linestyle = '--', linewidth = 1.5, label = 'cut here for 3 clusters')
plt.legend()
plt.show()

Fitting the model.
Fits our model on 4 clusters then creates a new column named 'hc-cluster'.

from sklearn.cluster import AgglomerativeClustering
hc = AgglomerativeClustering(n_clusters = 4, linkage = 'ward')
customers ['hc_cluster'] = hc.fit_predict(X_scaled)
customers

Profiling.
This step helps understand what each cluster represents.

print('Hierachical cluster profiles')
print(customers.groupby('hc_cluster')[['annual_spend', 'visit_frequency']].mean ().round(0))

Visualization.
A comparison between the two models. K-Means & Hierarchical clustering.

fig, axes = plt.subplots(1, 2, figsize=(14, 5))

for ax, col, title in zip(
    axes,
    ['cluster', 'hc_cluster'],
    ['K-means cluster', 'Hierarchical clusters']
):
    for c in range(4):
        mask = customers[col] == c
        ax.scatter(
            customers[mask]['annual_spend'],
            customers[mask]['visit_frequency'],
            alpha=0.7, s=40, label=f'cluster {c}'
        )


plt.tight_layout()
plt.show()

Advantages of Hierarchical Clustering

No need to predefine number of clusters
Produces interpretable tree structure
Works well for small datasets

Limitations of Hierarchical Clustering

Computationally expensive (slow for large datasets)
Once clusters are merged, they cannot be undone
Sensitive to noise and outliers

Machine learning - Supervised learning: Regression & Classification.

Kelvin — Mon, 20 Apr 2026 13:33:30 +0000

Machine learning is a branch of Artificial Intelligence that focuses on building systems that can learn from data and improve their performance over time without being explicitly programmed.

TYPES OF MACHINE LEARNING.

Supervised learning.
Unsupervised learning.
Reinforcement learning.

Supervised learning.

Supervised learning is a type of machine learning where a model learns from labelled data.

Unsupervised learning.

Unsupervised learning is a type of machine learning where the model works with unlabelled data.
Instead of predicting a known output, the model tries to discover hidden patterns, structures, or relationships in the data on its own.

Reinforcement learning.

Reinforcement learning is a type of machine learning where a model learns by interacting with an environment and receiving rewards or penalties based on its actions.
Instead of learning from labelled data or finding patterns, it learns through trial and error.

Supervised learning.

Regression - is a type of supervised learning used to predict continuous numerical values.
Classification - Classification is a type of supervised learning where the goal is to predict categories (classes) instead of numbers.

Regression.

How models learn in regression.

The model starts with random values for y = mx + c ### Example: Predicting house prices. y = mx + c price(y) = 100(m) * size(x) + 5000(c) 5000 is the base price if x = 0
The model compares predictions with actual prices using loss function. error = (prediction price - actual price)
The model adjusts the formula to reduce the error using gradient descent. price = 150 * size + 30000
The model repeats until error is minimized. price = 200 * size + 10000

Supervised learning workflow.

Collect data.
Prepare data - Cleaning, pre-processing, visualization.
Split data into train and test sets.
Choose the algorithm to use.
Training the model - learning patterns from training data.
Make predictions on the test data.
Evaluate the model's performance.
Tune and improve the model.
Deploy - use on new data.

Linear regression workflow.

Collect data
Collecting data involves gathering relevant CSV's, databases, Excel, APIs etc

import pandas as pd
df = pd.read_csv("Kenya_Crops_Dataset.csv")
print(df)

Prepare data
Data pre-processing involves cleaning, handling missing values, removing duplicates etc

examples.

df.info() - gets a summary of the dataset structure in pandas.
df.drop(columns = ['Cabin'], inplace = True) - dropping columns with lots of missing values.
df['Age'] = df['Age'].fillna(df['Age'].median()) df.info() - filling missing values with median. Used median in case of outliers.

Visualization.

# visualization
import seaborn as sns
import matplotlib.pyplot as plt
plt.figure(figsize=(5,3))
sns.heatmap(df.corr(),annot=True, cmap='coolwarm', center=0)
plt.title('Feature correlation')
plt.show()

example heatmap.

Split data into train and test sets.
Splitting data helps avoid overfitting of the models i.e. models performing very well on training data but poorly on new data. It also ensures the model doesn't memorize data.

#separate features(X) from label(y)
X = df[['size', 'bedrooms', 'bathrooms','age']]
y = df['price']
#split data
from sklearn.model_selection import train_test_split
X_train,X_test,y_train,y_test = train_test_split (X, y, test_size= 0.2, random_state=42)

Training the model
Training involves adjusting model parameters to minimize error using training data.

#training model
from sklearn.linear_model import LinearRegression
model = LinearRegression()
model.fit(X_train, y_train)

print(f"price: {model.intercept_:.2f}")

Make predictions on the test data.
After training, the model uses learned patterns to predict outcomes on new data.

# evaluation of the model
y_pred = model.predict(X_test)

Evaluate the model's performance.
Model evaluation involves measuring how well predictions match real values using test data

from sklearn.metrics import mean_squared_error, r2_score
from sklearn.metrics import root_mean_squared_error
mse = mean_squared_error(y_test, y_pred)
rmse = root_mean_squared_error(y_test, y_pred)
r2 = r2_score(y_test, y_pred)

print(f"the rmse is {rmse:.2f}")
print(f"the r2 is {r2:.2f}")

Regularization.

Regularization is a technique used to reduce overfitting by preventing the model from becoming too complex.

Ridge (l2) and Lasso (L1)

Ridge

Adds squared penalty on coefficients. Shrinks coefficients but doesn’t make them zero. Keeps all features but reduces magnitude of weights

#Ridge l2 regularization
from sklearn.linear_model import Ridge
# create a rigde model
ridge = Ridge(alpha=1.0)

# train linear model with reguralization
ridge.fit(X_train,y_train)

#make prediction
y_pred_ridge = ridge.predict(X_test)

#Evaluation
r2_ridge = r2_score(y_test, y_pred_ridge)
print(f"r2_ridge= {r2_ridge}")
print(f"R2 linear = {r2}")
print(f"Difference : {r2_ridge - r2}")

Lasso

Adds absolute value penalty. Can reduce some coefficients to zero. it removes useless features.

# create a lasso model
from sklearn.linear_model import Lasso
lasso = Lasso(alpha=1.0)

# train linear model with reguralization
lasso.fit(X_train,y_train)

#make predictions
y_pred_lasso = lasso.predict(X_test)

#Evaluation
r2_lasso = r2_score(y_test, y_pred_ridge)

print(r2_lasso)
print(r2)
print(f"Difference : {r2_lasso - r2}")

Classification.

Classification is a supervised machine learning technique used to predict labels or categories from input data. It assigns each data point to a predefined class based on learned patterns.

Predict categories: Determines the class of new data points.
Uses labeled data: Trained on datasets where the correct class is known.
Common examples: Spam vs non spam emails, diseased vs. healthy patients.

Types of Classification.

1. Binary Classification.

Binary classification is the simplest type of classification where data is divided into two possible categories. The model analyzes input features and decides which of the two classes the data belongs to.
Some important aspects of binary classification are:

Two classes only: Each data point is assigned to one of two categories.
Common examples: Spam vs not spam emails, diseased vs healthy patients.
Decision based on features: The model uses input features to determine the correct class.

2. Multiclass Classification.

Multiclass classification is used when data needs to be divided into more than two categories. The model analyzes the input features and selects the class that best matches the data.
Some important aspects of multiclass classification are:

Multiple classes: Each data point is assigned to one of several possible categories.
Single final prediction: The model selects only one class for each input.
Common examples: Image classification such as identifying animals like cat, dog or bird.

3. Multi Label Classification.

Multi label classification allows a single piece of data to belong to multiple categories at the same time. Unlike multiclass classification, where each data point is assigned only one class, this approach allows the model to assign multiple labels to the same input.
Key aspects include:

Multiple labels per data point: One input can belong to more than one category.
Labels can overlap: Classes are not mutually exclusive.
Common example: A movie recommendation system may tag a movie as both action and comedy based on features like plot, actors or genre tags.

Logistic regression workflow.

# Hands on - Breast cancer diagnosis
import numpy as np
from sklearn.datasets import load_breast_cancer

data = load_breast_cancer()
X = data.data 
y = data.target

split data
Splitting data helps avoid overfitting of the models i.e. models performing very well on training data but poorly on new data. It also ensures the model doesn't memorize data.

# split data into train and test datasets.
from sklearn.model_selection import train_test_split

X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

scaling data
Feature scaling is the process of putting all numerical features on a similar scale so that no feature dominates others just because of its size.

# Scale features.
# Logistic regression is sensitive to feature scale
from sklearn.preprocessing import StandardScaler

scale = StandardScaler()
X_train = scale.fit_transform(X_train) #Learn scale from training data only.
X_test = scale.transform(X_test)

Training the model.
Training involves adjusting model parameters to minimize error using training data.

# Train model.
from sklearn.linear_model import LogisticRegression

model = LogisticRegression(max_iter=1000)
model.fit(X_train, y_train)
**making predictions**

Predict.
After training, the model uses learned patterns to predict outcomes on new data.

# predict
y_pred = model.predict(X_test)
y_prob = model.predict_proba(X_test)[:, 1]

print(f"Sample predictions: {y_pred[:5]}")
print(f"Sample probabilities: {y_prob[:5].round(3)}")

Evaluating the model
Model evaluation involves measuring how well predictions match real values using test data

# Evaluate
from sklearn.metrics import accuracy_score, classification_report

accuracy = accuracy_score(y_test, y_pred)
classificationR = classification_report(y_test, y_pred, target_names=data.target_names)

print(f"Accuracy: {accuracy:.2%}")
print(classificationR)

Display the confusion matrix
The confusion matrix is used to evaluate classification models by showing correct and incorrect predictions.

from sklearn.metrics import ConfusionMatrixDisplay
import matplotlib.pyplot as plt

ConfusionMatrixDisplay.from_estimator(model, X_test, y_test, display_labels=data.target_names, cmap='Blues')
plt.show() 
# TP FN
# FP TN

Example of confusion matrix.

Advanced SQL for Data Analytics:

Kelvin — Tue, 17 Mar 2026 15:20:06 +0000

WINDOW FUNCTIONS IN SQL.

Window functions perform calculations across a set of rows related to the current row without grouping the rows into a single output. They preserve the individual row details while providing additional contextual insights.

Key components.
Window functions are defined using the mandatory OVER() clause which specifies how the rows are partitioned and ordered for the calculation.

Common types of window functions.
1: RANKING - Row_number, rank, dense_rank - It assigns numbers or ranks to rows based on order.
Rank

Row number

2: AGGREGATE - sum, avg, min, max, count - calculates sum, averages or extremes across the window.
Total

Average

Count

Max

3: VALUE/OFFSET - lag, lead - Accesses data from rows before, after or at specific points in the window.

lag
Used to compare rows i.e. comparing current value to previous value

lead
looks forward to the next row or a specified number of rows ahead.

Subqueries

A subquery (or nested query) is a SQL query written inside another query. It allows you to
perform an operation that depends on the result of another query.

The SELECT clause - Subqueries in the SELECT clause add an extra computed column to the result set. Each subquery executes once for every row of the outer query.

SELECT  
name, 
(SELECT COUNT(*)  
FROM projects  
WHERE projects.employee_id = employees.employee_id) AS total_projects 
FROM employees;

The FROM clause - A subquery in the FROM clause creates a temporary or derived table. The outer query can then select or filter data from it.

SELECT * 
FROM ( 
SELECT  
department_id,  
COUNT(*) AS total_employees 
FROM employees 
GROUP BY department_id 
) AS dept_summary;

The WHERE or HAVING clause - Subqueries in the WHERE clause allow the outer query to filter results based on another query’s output.

SELECT  
department_name 
FROM departments 
WHERE department_id IN ( 
SELECT department_id 
FROM employees 
GROUP BY department_id 
HAVING COUNT(*) > 1 
);

Correlated vs Non-Correlated Subqueries

Non-correlated subquery - A non-correlated subquery does not depend on the outer query. It can be executed on its own.
Correlated subquery - A correlated subquery depends on the outer query. It is evaluated once per row of the outer query.

Common Table Expressions (CTEs)

A CTE (Common Table Expression) is like creating a temporary result set (or virtual table)
that exists only during the execution of a single SQL query.

WITH high_salary AS ( 
SELECT name, salary 
FROM employees 
WHERE salary > 45000 
) 
SELECT * 
FROM high_salary;

CTE with Joins

You can use CTEs to simplify joins

WITH emp_dept AS ( 
SELECT e.name, d.department_name, e.salary 
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.department_id 
) 
SELECT * FROM emp_dept;

Stored Procedures

A stored procedure is a named block of SQL logic stored inside the database that you can
execute using: CALL procedure_name(parameters);

Procedures are for actions, such as:

Insert data (add customer, add book, add order)
Update data (update contact, update email)
Delete data (delete customer safely)
Validation and enforcement of business rules
Multi-step operations (create order + check customer + log)

Procedure Syntax

CREATE OR REPLACE PROCEDURE procedure_name(param_name param_type, ...)
LANGUAGE plpgsql
AS $$
BEGIN -- SQL statements
END;
$$;

Explanation of each keyword

CREATE - creates the procedure
OR REPLACE - updates the procedure if it already exists (no need to drop first)
PROCEDURE procedure_name(...) - name + inputs (parameters)
LANGUAGE plpgsql - enables procedural features (IF, LOOP, variables)
AS $$ ... $$ - defines the body of the procedure
BEGIN ... END; - start/end of procedure logic
Call with: CALL procedure_name(...)

CREATE OR REPLACE PROCEDURE add_customer( 
p_first_name VARCHAR, 
p_last_name  VARCHAR, 
p_email      VARCHAR, 
p_contact    VARCHAR, 
p_city       VARCHAR) 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
INSERT INTO customers(first_name, last_name, email, contact, city) 
VALUES (p_first_name, p_last_name, p_email, p_contact, p_city); 
END; 
$$;

Explanation

CREATE OR REPLACE PROCEDURE add_customer(...) - Creates a reusable procedure named add_customer.
Parameters like p_first_name VARCHAR - Inputs we pass when calling the procedure.
LANGUAGE plpgsql - Enables procedural execution.
BEGIN - Starts execution block.
INSERT INTO customers(...) - Specifies the table and columns being inserted.
VALUES (...) - Inserts the parameter values.
END;- Ends procedure.
CALL add_customer(...) - Runs the procedure by inserting values into our table.

Delete Customer Safely

CREATE OR REPLACE PROCEDURE delete_customer_safely( 
p_customer_id INT 
) 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
DELETE FROM orders 
WHERE customer_id = p_customer_id; 
DELETE FROM customers 
WHERE customer_id = p_customer_id; 
END; 
$$;

Introduction to Python

Kelvin — Tue, 17 Mar 2026 14:59:28 +0000

Python is a powerful and flexible programming language created by Guido van Rossum and first released in 1991.Over the years, Python has become one of the most loved and used languages in the world.

Python installation.

python installation from the web.
python.

checking the version of python installed in your pc

Open your command line and type python --version

This confirms the version of python installed in your computer.

example of a python code.

What Can You Use Python For

Web Development, and software development.
Data analysis, machine learning, data engineering, AI, and math.
System scripting.

Why Use Python?

Platform-independent - Python works everywhere: Windows, Mac, Linux, Raspberry Pi, or any other platform.
Simple & Readable Syntax - python is simple to read and understand.
Fewer Lines of Code - Python lets you do more with less typing.
Fast Prototyping - Python runs your code line by line, immediately showing results or errors.
Multiple Programming Styles - Python lets you write code in different ways i.e. procedural, object oriented and functional (using functions like math operations)

Python Syntax.

Python has rules about how code should be written so it can be understood and executed properly. It relies on indentation and clean line-by-line execution and throws errors if the rules are not followed. Python emphasizes readability and simplicity, making it easier for beginners to learn and professionals to maintain.

Key Syntax Rules in Python.

Python uses indentation to define blocks of code.
Indentation means adding spaces at the beginning of a line to show that this line belongs to a group or block of code.
You can technically use any number of spaces - at least one. But the convention is to use four spaces for each indentation level.

if 5 > 2: 
    print("Five is greater than two!")

Mixing indentation levels is not allowed Python needs consistency. Once you choose a number of spaces, stick with it throughout the block.

Python Variables.

A variable is a named memory location.

name = "lee"
    print("name")

here name is a variable for storing the string "lee"

In Python, you don’t have to declare the data type of a variable like you do in many other languages. Python understands it based on the value you assign.

Rules of naming variables.

A variable name must start with a letter (a–z, A–Z) or an underscore _.
It can only contain alphanumeric characters and underscores (A–Z, a–z, 0–9, and _)
Variable names are case-sensitive.
It cannot start with a number.
You cannot use reserved words i.e. (int, True ETC)

Best Practices.

Use descriptive names
Use snake_case for variables
Avoid short, vague names like x, y, unless in quick examples or math

Assigning Multiple Variables

Python lets you assign values to multiple variables at once: a, b, c = 1, 2, 3
All at once! Or assign the same value to multiple variables: x = y = z = "Python"

Checking the Type of a Variable - You can use the type() function to find out what kind of value a variable holds.

name = "Alice" 
print(type(name))

the output will be str (string)

Python Constants.

Values that don’t change during normal program execution
In real life, think of your birthdate. You store it once and it never changes - that's a constant.
In Python, we use uppercase letters to indicate that a variable is meant to be a constant e.g.

PI = 3.14159

Python does not have built-in constants, but we use uppercase variable names to show they are constants.

Python Data Types.

A data type is the classification of data that tells the computer what kind of data you are working with and what operations can be performed on it.

Python’s Built-in Data Types.

1. Numeric Types

int – Integer (whole numbers)

age = 25

float – Floating point (decimal numbers)

height = 5.9

complex – Complex numbers (used in scientific computing)

complex_num = 3 + 2j

2. Text Type

str – String (a sequence of characters)

"Python is amazing!"

3. Boolean Type

bool - Can only be True or False.

is_logged_in = True

Arithmetic Operators in Python

Addition (+) -- Adds two numbers
Subtraction (-) -- Subtract right from left
Multiplication (*) -- Multiply numbers
Division (/) -- Divide and get float
Modulus (%) -- Get the remainder

comparison operators

Greater than (>)
Less than (<)
Equal to (==) note that (=) is an assignment operator
Not equal to (=!)
Greater than or equal to (>=)
Less than or equal to (>=)

Logical operators (and,or,not)

AND - Returns true only if both conditions are true
OR - Returns true if at least one condition is true
NOT - Reverses the value (true becomes false, false becomes true)

Data types and data structures

Datatypes
A datatype define the type of a value a variable can store

Boolean - returns true or false when a certain condition is met.
String - Sequence of characters or text enclosed in quotation marks.
on command line python dir(str)

f-string - An f-string (formatted string literal) is a way to insert variables or expressions directly inside a string.
user = 'admin'
password = 12345
database = 'postgres'

database_url = f"PostgreSQL:// {user:}{password:}@localhost:5432{testdb}"

Int - whole numbers without decimals
floats - represents decimals

Data structures.

Is a way of organizing and storing data so that it can be modified and accessed efficiently.

on command line python dir(list)

list [] - is a collection of items in a specified order
students =["Sam", "Gloria", "Stacy", "Kevin", "Ornella"]

characteristics

(i) They are ordered
(ii) They are mutable - can be changed
(iii) Indexed
(iv) Allows duplicates

common list methods

.append() - students.append("kevin") - adds an item at the end of a list
.remove() - students.remove("kevin") - removes a specific item
.pop() - students.pop(1) - removes an item based on index and last one if no index is provided.
.sort() - students.sort() - sorts items in the list based on a particular order
.reverse() - students.reverse() - reverses the order of items in a list.

Tuple () - is a collection of items that is ordered but cannot be changed.

characteristics

(i) They are immutable - cannot be changed.

common tuple methods

.count() - my_tuple.count(35)
.index() - my_tuple.index(35)

Dictionary {}- it is a collection of key value pairs >> each key maps to a value

characteristics

(i)Key value pairs
(ii)They are mutable
(iii)The keys must be unique

common Dictionary methods

.items() - student.items() Returns key-value pairs
.keys() - student.keys() Returns all keys
.values() - student.values() Returns all values
.get() - student.get()Safely gets a value
.update() - student.update() Updates/adds items

Sets {}- is a collection of Unique items stored in an unordered structure.

Characteristics
(i) They are unordered - no indexing
(ii) No duplicates
(iii) They are mutable - can add or remove elements

common set methods

.add() fruits.add("mango") - adds mango to the set.
.pop() fruits.pop() removes items
.union() print(fruits.union(veggies)) - joins fruits and veggies
Control Flow, Loops and Functions.

Control Flow
-if else and elif

Operators and Expressions
Arithmetic operators (+,-,*,/,^,%)
comparison operators (<,>,=,=!...)
Logical operators (and,or,not)

Conditional statements.

score = int(input("enter score"))

if score < 0 or score > 100:
    print("Invalid score")
elif score >= 80:
    print("A")
elif score >= 70:
    print("B")
elif score >= 50:
    print("C")
elif score >= 35:
    print("D")
elif score >= 15:
    print("E")
else:
    print("Repeat")

loops
for loop -- used to iterate over a sequence (list, tuple or a string)

while loop -- iterates a block of code until a certain condition is met.

Break and continue

break stops/ends the loop immediately
continue - skips the current iteration -- continue skips to next.

Functions
A function is a block of named reusable block of code that perform a specific task when called.

List comprehension
list comprehension provides a concise and efficient way to create a new list by applying an expression to each item in an existing iterable, optionally filtering the items

Object Oriented Programming (OOP)
is a way of programming that organizes code into classes and objects instead of just functions.

classes - blueprint of an object
object - instance of a class

It provides a clear structure to write programs

It allows you to build reusable applications with less code
Dry principle - Do Not Repeat Yourself - it keeps your code dry

CORE CONCEPTS:
1 class
2 Objects

pillars of oop

Inheritance -- this allows you to reuse code from another class

Encapsulation-- this means bundling data and methods together inside a class and controlling access to them
polymorphism - this means the same method name behaves differently
Abstraction -- means hiding unnecessary implementation details and showing only the essential features of an object.

sql and databases

Kelvin — Fri, 13 Mar 2026 13:04:02 +0000

What is a database.

A database is an organized collection of data stored electronically.

What is SQL

SQL stands for structured query language. It is used for storing data, retrieving data, updating data and deleting data.

Schema in SQL

A schema is a logical container or namespace inside a database that groups related objects like tables, views, indexes, stored procedures, functions and sequences.
You can think of a schema as a folder inside a database helping you organize and manage objects.

Why use schemas.

Schemas help organize large datasets into sections, control access to different parts of the database and avoiding name conflicts,

Creating a schema.

CREATE SCHEMA sales_data;

this creates a schema named sales_data.

Setting search path.

set search_path to sales_data;

this sets the search path to the schema. To check what schema you are using show search_path;

Creating a table in that schema.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100));

this creates a table named customers.

Types of Database management systems(dbms).

DBMS means Database management system. It is a software that allows us to create, manage and interact with databases.

Types of databases.

Relational database - uses tables with rows and columns. examples: PostgreSQL, MySQL, Oracle.
NoSQL DBMS - non tabular (key-value, Json, Graph) examples: MongoDB, Cassandra.
In-Memory DBMS - stores data in RAM. example: Redis
Cloud DBMS - cloud-hosted & managed by providers. examples: Amazon RDS, Azure PostreSQL

SQL datatypes

Data types define the type of data that can be stored in a column. They enforce data integrity and data accuracy.

Main data types categories.

1. Numeric Types

Integer/int - whole numbers e.g. 10,50,-4
Serial - Auto-incrementing ID e.g. 1,2,3
Numeric - precision numbers (money) e.g. 12345.76
Decimal - same as numeric - 146505.95

2. Character Types

CHAR - fixed length string e.g. 'Kenya'
VARCHAR - variable-length string e.g. 'John Kamau'
TEXT - unlimited string length e.g. long comments, emails

3. Date & Time Types

DATE - stored date only e.g. '2020-01-01'
TIMESTAMP - stores date and time e.g.'2024-10-10 12:20:00'
TIME - time of the day e.g. '13:30:00'

4. Boolean

Boolean - True or False e.g. True or False

Creating tables in SQL

Basic syntax

A table consists of rows and columns

create table customers(
Customer_id serial primary key,
First_name varchar(50) not null,
Last_name varchar (50) not null,
Email varchar (100) unique not null,
Phone char(13) );

Constraints

PRIMARY KEY - uniquely identifies each record
NOT NULL - cannot be empty
UNIQUE - all values must be different
DEFAULT - provides a default value
FOREIGN KEY - links to another table's primary key

Best practices
Use singular table names e.g. employees
Define primary keys
Use meaningful column names
Prefer VARCHAR over CHAR unless fixed
Use foreign keys for relational integrity

Inserting Data into a database.

INSERT INTO customers (First_name, Last_name, Email, Phone) 
VALUES   
('John', 'Doe', 'john.doe@example.com', '+254712345678'), 
('Jane', 'Smith', 'jane.smith@example.com', '+254798765432'), 
('Paul', 'Otieno', 'paul.otieno@example.com', '+254701234567'), 
('Mary', 'Okello', 'mary.okello@example.com', '+254711223344');

Viewing and querying Tables.

Viewing databases

-- from postgreSQL

SELECT datname
FROM pg_database
WHERE datistemplate = false;

-- from MySQL

SHOW DATABASES;

Viewing all tables in PostgreSQL

SELECT table_name 
FROM schema_name.tables
WHERE table_schema = 'sales_data';

View data from a table.

SELECT *
FROM customers;

Table manipulation and common SQL key words.

Add a column to a table

ALTER TABLE customers  
ADD COLUMN city VARCHAR(100);

this adds a new city column to the customers table.

Updating existing data

Updating a single row

UPDATE customers  
SET city = 'Nairobi'  
WHERE customer_id = 1;

Updating multiple rows with a CASE statement.

UPDATE customers  
SET city = CASE customer_id  
WHEN 1 THEN 'Nairobi'  
WHEN 2 THEN 'Mombasa'  
WHEN 3 THEN 'Kisumu'  
WHEN 4 THEN 'Nakuru'  
ELSE city  -- Retain original city if no match 
END;

Updating specific quantity

UPDATE orders  
SET quantity = CASE order_id  
WHEN 1 THEN 2  
WHEN 2 THEN 1  
WHEN 3 THEN 3  
WHEN 4 THEN 2  
WHEN 5 THEN 1  
ELSE quantity 
END;

Updates orders with specific quantity.

Delete columns from SQL

Remove column from a table.

ALTER TABLE books  
DROP COLUMN published_date;

Delete rows from SQL

Delete a specific row

DELETE FROM orders  
WHERE order_id = 3;

Drop a table

DROP TABLE books;

Deletes the entire table and all its data. Use with caution!!

Rename column

ALTER TABLE customers RENAME COLUMN phone_number TO contact_number;

Renames a column.

MODIFY COLUMN TYPE - Change a Column’s Data Type

ALTER TABLE orders  
ALTER COLUMN quantity TYPE DECIMAL(5,2);

SET DEFAULT Value for Column

ALTER TABLE orders  
ALTER COLUMN quantity SET DEFAULT 1;

DROP DEFAULT Value

ALTER TABLE orders  
ALTER COLUMN quantity DROP DEFAULT;

Add a NOT NULL Constraint

ALTER TABLE customers  
ALTER COLUMN email SET NOT NULL;

Drop a NOT NULL Constraint

ALTER TABLE customers  
ALTER COLUMN email DROP NOT NULL;

Add a Foreign Key Constraint

ALTER TABLE orders 
ADD CONSTRAINT fk_customer 
FOREIGN KEY (customer_id) 
REFERENCES customers(customer_id);

Drop a Foreign Key Constraint

You must know the constraint name. You can find it using:

SELECT conname  
FROM pg_constraint  
WHERE conrelid = 'orders'::regclass;

Then drop it:

ALTER TABLE orders DROP CONSTRAINT fk_customer;

COMMON SQL KEYWORDS

1. SELECT - Retrieve Data

SELECT column1, column2 FROM table_name;
Examples:

SELECT customer_name, city FROM customers; 
SELECT title, author FROM books; 
SELECT customer_id, book_id FROM orders;

2. WHERE - Filter Data

SELECT column FROM table WHERE condition;
Examples:

SELECT * FROM customers WHERE city = 'Nairobi'; 
SELECT title, price FROM books WHERE price > 2000; 
SELECT * FROM orders WHERE customer_id = 1;

3. ORDER BY - Sort Data

SELECT columns FROM table ORDER BY column ASC|DESC;
Examples:

SELECT title, price FROM books ORDER BY price ASC; 
SELECT customer_name FROM customers ORDER BY customer_name ASC;

SELECT * FROM orders ORDER BY order_date DESC;

4. GROUP BY - Group and Summarize Data

SELECT column, AGG_FUNCTION(column) FROM table GROUP BY column;
Examples:

SELECT author, COUNT(*) FROM books GROUP BY author; 
SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;

SELECT city, COUNT(*) FROM customers GROUP BY city;

5. HAVING - Filter After Grouping

SELECT column, COUNT() FROM table GROUP BY column HAVING COUNT() > 1;
Examples:

SELECT author, COUNT(*) FROM books GROUP BY author HAVING COUNT(*) > 1;

SELECT customer_id, COUNT(*)
FROM orders GROUP BY customer_id HAVING COUNT(*) 
> 1;

6. LIMIT - Restrict Number of Results

SELECT * FROM table LIMIT number;
Examples:

SELECT * FROM books LIMIT 3;

SELECT title, price FROM books 
ORDER BY price DESC LIMIT 2;

SELECT * FROM customers LIMIT 5;

Summary

SELECT -Retrieve data
WHERE - Filter records
ORDER BY - Sort records
GROUP BY - Group data
HAVING - Filter grouped data
LIMIT - Restrict number of results
INSERT - Add data
UPDATE - Modify existing data
DELETE - Remove rows
ALTER - Modify table structure

SQL Aggregate Functions

COUNT() Counts the number of rows (or non-NULL values)

SELECT COUNT(*) AS kisumu_customers FROM customer 
WHERE city = 'Kisumu';

SUM() Adds up numeric values in a column

SELECT SUM(price) AS total_book_price FROM books;

AVG() Calculates the average (mean) of numeric values

SELECT AVG(price) AS avg_price_2023 FROM books 
WHERE published_date >= '2023-01-01';

MAX() Finds the highest value in a column

SELECT MAX(order_date) AS latest_order FROM orders;

MIN() Finds the lowest value in a column

SELECT MIN(quantity) AS min_quantity FROM orders;

SQL Operators

1. Arithmetic Operators - Used for mathematical calculations.

Addition SELECT title, price, price + 200 AS new_price FROM books;
Subtraction SELECT book_name, price, price - 150 AS discounted_price FROM books;
Multiplication SELECT book_name, price, price * 2 AS double_price FROM books; / Division SELECT book_name, price, price / 2 AS half_price FROM books; % Modulo (Remainder) SELECT book_name, price, price % 3 AS remainder FROM books;

2. Comparison Operators - Used to compare values, often in `WHERE`.

= Equals - Selects rows where a column matches an exact value.
SELECT * FROM books WHERE author = 'David Kimani';
!= or <> Not equal to - Selects rows where a column value does not match the specified value.
SELECT * FROM orders WHERE order_date <> '2023-04-01';
Greater than - Selects rows where column value is greater than the given value.
SELECT * FROM orders WHERE order_date > '2023-04-03';
< Less than - Selects rows where column value is less than the given value.
SELECT * FROM orders WHERE order_date < '2023-04-04';
= Greater than or equal to - Selects rows where column value is greater than or equal to the given value.
SELECT * FROM customer WHERE customer_id >= 2;
<= Less than or equal to - Selects rows where column value is less than or equal to the given value.
SELECT * FROM books WHERE price <= 2200;
BETWEEN Between two values - Selects rows with column values between two values (inclusive).
SELECT * FROM orders WHERE order_date BETWEEN '2023-04-01' AND '2023-04-03';
LIKE Pattern matching - Use LIKE to search for patterns in text data.
SELECT * FROM customer WHERE email LIKE '%gmail.com';
IN Matches values in a list - Use IN to filter records by matching any value in a list.
SELECT * FROM books WHERE author IN ('David Kimani', 'Grace Achieng');

3. Logical Operators - combine multiple conditions.

AND - All conditions must be true
SELECT * FROM customer WHERE city = 'Kisumu' AND first_name = 'Paul';
OR - At least one condition is true
SELECT * FROM customer WHERE city = 'Nairobi' OR city = 'Kisumu';
NOT - Reverses the result of a condition
SELECT * FROM orders WHERE NOT order_date = '2023-04-01';

4. Bitwise Operators - Used for bit-level operations.

Bitwise AND
Bitwise OR
Bitwise XOR

5. Set Operators - Used to combine result sets of two SELECT queries.

UNION - Combines results, removes duplicates

SELECT first_name AS name FROM customer 
UNION 
SELECT author AS name FROM books;

UNION ALL - Combines results, keeps duplicates

SELECT first_name AS name FROM customer 
UNION ALL 
SELECT author AS name FROM books;

INTERSECT - Returns common records

SELECT first_name AS name FROM customer 
INTERSECT 
SELECT author AS name FROM books;

EXCEPT - Returns records in first query, not in second

SELECT first_name AS name FROM customer 
EXCEPT 
SELECT author AS name FROM books;

6. Other Useful Operators

IS NULL - Checks for missing values

SELECT * FROM customer 
WHERE city IS NULL;

IS NOT NULL - Checks for non-missing values

SELECT * FROM customer 
WHERE city IS NOT NULL;

DISTINCT - Removes duplicates

SELECT DISTINCT city FROM customer;

Aggregate Functions

1. COUNT() - Counting Rows

The COUNT() function counts the number of rows that match a condition.

SELECT COUNT(*) AS kisumu_customers FROM customer 
WHERE city = 'Kisumu';

2. SUM() - Total of Numbers

SUM() adds up the values in a numeric column.

SELECT SUM(price) AS total_book_price FROM books;

3. AVG() - Average Value

AVG() calculates the average (mean) of values in a numeric column.


SELECT AVG(price) AS avg_price_2023 FROM books 
WHERE published_date >= '2023-01-01';

4. MAX() - Maximum Value

MAX() finds the largest value in a numeric or date column.

SELECT MAX(order_date) AS latest_order FROM orders;

5. MIN() - Minimum Value

MIN() finds the smallest value in a numeric or date column.

SELECT MIN(quantity) AS min_quantity FROM orders;

String, Date, and Mathematical Functions

SQL String Functions

CONCAT(): Combine Strings - Joins multiple strings into one.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

SUBSTRING(): Extract Part of a String - Extracts a portion of a string

SELECT SUBSTRING(email, POSITION('@' IN email) + 1) AS domain FROM customers;

LENGTH(): String Length - Returns the number of characters in a string.

SELECT email, LENGTH(email) AS email_length FROM customers;

UPPER() and LOWER(): Change Case UPPER() – Converts text to uppercase.

SELECT UPPER(customer_name) AS upper_name FROM customers;

LOWER() – Converts text to lowercase.

SELECT LOWER(email) AS standardized_email FROM customers;

TRIM(), LTRIM(), RTRIM(): Remove Spaces - Cleans unwanted spaces from strings.

SELECT TRIM(string); 
SELECT LTRIM(string); 
SELECT RTRIM(string);

REPLACE(): Replace Text - Replaces all occurrences of a substring

SELECT REPLACE(country, 'Kenya', 'KE') AS short_country FROM customers;

SQL Date Functions

NOW(): Current Date and Time

SELECT NOW() AS current_datetime;

YEAR(), MONTH(), DAY(): Extract Date Parts

SELECT order_id, YEAR(order_date) AS order_year FROM orders; 
SELECT order_id, MONTH(order_date) AS order_month FROM orders; 
SELECT order_id, DAY(order_date) AS order_day FROM orders;

DATEDIFF() / Subtracting Dates Note: DATEDIFF() is for MySQL. In PostgreSQL, use subtraction.

SELECT order_id, CURRENT_DATE - order_date AS days_since_order FROM orders;

SQL Mathematical Functions

ROUND(): Round Numbers

SELECT ROUND(price, 2) AS rounded_price FROM books;

CEIL() and FLOOR(): Round Up/Down

SELECT order_id, CEIL(total_weight / quantity) AS shipping_cost_per_item FROM 
orders;

MOD(): Get Remainder

SELECT order_id, quantity, MOD(quantity, 2) AS remainder FROM orders;

POWER(): Raise to a Power

SELECT book_name, POWER(price, 3) AS cubed_price FROM books;

ABS(): Absolute Value

SELECT book_name, ABS(price - 500) AS price_difference FROM books;

1: INNER JOIN
INNER JOIN returns only matching rows from both tables. If there is no match between the tables the row is excluded from the result. It is used when you only want records that exist in both tables or want to combine related data.

2: LEFT JOIN
LEFT JOIN returns all rows from the left table and matching rows from the right table. Also known as left outer join. If no match exists null values are returned for the right table columns.

3: RIGHT JOIN
RIGHT JOIN returns all rows from the right table and matching rows from the left table. If no match exists null values are returned for the left table columns.

4: FULL OUTER JOIN
FULL OUTER JOIN returns all rows from both tables. If no match exists nulls appear on the missing side. It is used for combining two datasets and finding mismatches between systems.

5: CROSS JOIN
CROSS JOIN returns the cartesian product - every row from the first table combined with every row from the second table. No matching is required.

WINDOW FUNCTIONS IN SQL.

Key components.
Window functions are defined using the mandatory OVER() clause which specifies how the rows are partitioned and ordered for the calculation.

Average

Count

Max

3: VALUE/OFFSET - lag, lead - Accesses data from rows before, after or at specific points in the window.

lag
Used to compare rows i.e. comparing current value to previous value

lead
looks forward to the next row or a specified number of rows ahead.

Subqueries

A subquery (or nested query) is a SQL query written inside another query. It allows you to
perform an operation that depends on the result of another query.

The SELECT clause - Subqueries in the SELECT clause add an extra computed column to the result set. Each subquery executes once for every row of the outer query.

SELECT  
name, 
(SELECT COUNT(*)  
FROM projects  
WHERE projects.employee_id = employees.employee_id) AS total_projects 
FROM employees;

The FROM clause - A subquery in the FROM clause creates a temporary or derived table. The outer query can then select or filter data from it.

SELECT * 
FROM ( 
SELECT  
department_id,  
COUNT(*) AS total_employees 
FROM employees 
GROUP BY department_id 
) AS dept_summary;

The WHERE or HAVING clause - Subqueries in the WHERE clause allow the outer query to filter results based on another query’s output.

SELECT  
department_name 
FROM departments 
WHERE department_id IN ( 
SELECT department_id 
FROM employees 
GROUP BY department_id 
HAVING COUNT(*) > 1 
);

Correlated vs Non-Correlated Subqueries

Non-correlated subquery - A non-correlated subquery does not depend on the outer query. It can be executed on its own.
Correlated subquery - A correlated subquery depends on the outer query. It is evaluated once per row of the outer query.

Common Table Expressions (CTEs)

A CTE (Common Table Expression) is like creating a temporary result set (or virtual table)
that exists only during the execution of a single SQL query.

WITH high_salary AS ( 
SELECT name, salary 
FROM employees 
WHERE salary > 45000 
) 
SELECT * 
FROM high_salary;

CTE with Joins

You can use CTEs to simplify joins

WITH emp_dept AS ( 
SELECT e.name, d.department_name, e.salary 
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.department_id 
) 
SELECT * FROM emp_dept;

Stored Procedures

A stored procedure is a named block of SQL logic stored inside the database that you can
execute using: CALL procedure_name(parameters);

Procedures are for actions, such as:

Insert data (add customer, add book, add order)
Update data (update contact, update email)
Delete data (delete customer safely)
Validation and enforcement of business rules
Multi-step operations (create order + check customer + log)

Procedure Syntax

CREATE OR REPLACE PROCEDURE procedure_name(param_name param_type, ...)
LANGUAGE plpgsql
AS $$
BEGIN -- SQL statements
END;
$$;

Explanation of each keyword

CREATE OR REPLACE PROCEDURE add_customer( 
p_first_name VARCHAR, 
p_last_name  VARCHAR, 
p_email      VARCHAR, 
p_contact    VARCHAR, 
p_city       VARCHAR) 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
INSERT INTO customers(first_name, last_name, email, contact, city) 
VALUES (p_first_name, p_last_name, p_email, p_contact, p_city); 
END; 
$$;

Explanation

CREATE OR REPLACE PROCEDURE add_customer(...) - Creates a reusable procedure named add_customer.
Parameters like p_first_name VARCHAR - Inputs we pass when calling the procedure.
LANGUAGE plpgsql - Enables procedural execution.
BEGIN - Starts execution block.
INSERT INTO customers(...) - Specifies the table and columns being inserted.
VALUES (...) - Inserts the parameter values.
END;- Ends procedure.
CALL add_customer(...) - Runs the procedure by inserting values into our table.

Delete Customer Safely

CREATE OR REPLACE PROCEDURE delete_customer_safely( 
p_customer_id INT 
) 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
DELETE FROM orders 
WHERE customer_id = p_customer_id; 
DELETE FROM customers 
WHERE customer_id = p_customer_id; 
END; 
$$;

Connecting Power BI to SQL databases.

Kelvin — Fri, 13 Mar 2026 11:27:30 +0000

Power BI
Power BI is a business intelligence and data visualization tool that helps organizations collect, analyze and visualize data so as to make better and informed business decisions. It converts raw data from multiple sources into interactive dashboards, charts and reports that are used in decision making.

Power BI is used in data analysis and business intelligence because it allows the users to import data i.e. from MS Excel, databases and cloud services. Power BI has tools like power query which is used for data cleaning and Data Analysis Expressions(DAX) functions which are used to perform calculations. It has charts that are used for data visualization too.

Databases are used by large organizations to store large volumes of data. These databases are connected to Power BI to retrieve the data which is then used for analysis. Companies connect power bi to databases because the provide data security and integrity, can handle large sets of data and provide real time data access which allows dashboards to show regularly refreshed data.

SQL databases are important for storing and managing analytical data because they provide data storage, facilitate data management i.e. inserting new data, updating and deleting outdated data which helps maintain data consistency. databases also facilitate data retrieval which is used for analysis.

Connecting Power BI to a local postgreSQL database.

then open a blank report

from the home tab click on get data

from get data click on database then select postgreSQL database then click on connect.

On the connection window that appears enter the server and database details.

from the database window provide the authentication credentials then click on connect

from the navigator select the data you want to upload then load from the available tables.

wait for the data to load

Connecting Power BI to Cloud Databases.
From your browser search Aiven then create an account or login if having an existing account.
Once on the Aiven console click on services to create a new service

select service type

select service tier, cloud, plan then on service basic enter a name then create service.

open the service and copy the connection information.

open dbeaver to connect to the cloud database
from dbeaver click on database then new database connection

select the database you want to connect to. Ensure it matches the cloud database. Then click on next.

on the connection settings carefully replace them with the connection information copied from the cloud database (Aiven). i.e. host, database, port, username and password then test connection.

after testing the connection then click on finish.

Downloading secure sockets layer (SSL) certificates.
From the Aiven connection information click on the download button. A file named ca.pem is downloaded to your local computer. when connecting on Power BI when configuring SSL the SSL mode should be verify.ca then on root certificate select the downloaded ca.pem file from your downloads then click connect. SSL certificates are important because they encrypt data sent between Power BI and the postgreSQL server, verifies authentication and ensures data integrity between the client and the server.

Why Is an SSL Certificate Required?

When connecting to a cloud database over the internet, your credentials and data travel across public networks. An SSL (Secure Sockets Layer) certificate encrypts this connection, ensuring that:

Your data cannot be intercepted by third parties (man-in-the-middle attacks)
The server you're connecting to is verified and legitimate
Your username and password are transmitted securely
Aiven enforces SSL by default on all connections, which is a security best practice.

Using SSL certificate

step 1 -> download the ssh file from Aiven.
step 2 -> search for (certlm.msc from windows R) or (certmgr from search) then click on >Trusted Root Certification Authorities
right on certificates >> all tasks >> browse the downloaded file from your downloads >> next >> import file name >> next >> then finish.
From Power BI you can now connect to your cloud database
check below

Step 3: Connect in Power BI

Follow the same steps as the local connection:

Click Get Data - PostgreSQL Database
In the Server field, enter your Aiven host:port (e.g., pg-yourservice.aivencloud.com:12345)
Enter your database name
Expand Advanced Options and paste the following into the Additional Connection Parameters field:

sslmode=require;sslrootcert=C:\certs\ca.pem

Enter your Aiven username and password when prompted
Click Connect

Relationships in Power BI.
After loading data to Power BI click on model view to view the relationship between the tables

The primary keys connect with the foreign keys in the other tables.
Data modelling is the process of organizing data tables and defining how they relate to each other so that data can be interpreted correctly. Relationships define how records from one table relate to records in another allowing Power BI to correctly combine data. The common types of relationships are one to many, many to one and many to many. A star schema is the most common structure that is used in Power BI. The star schema contains the fact tables which contain measurable data and Dimension tables which contain descriptive data.

Why SQL skills are important for Analysts.
SQL skills enable analysts to retrieve, organize and prepare data efficiently before using it for visualization and reporting. SQL enables analysts extract specific information from large datasets using queries instead of importing the entire datasets which can be tiresome and time consuming. It also enables analysts to filter relevant records that are needed at a particular time using conditions like where. Aggregation functions like sum, count, avg, max and min help analysts to quickly summarize large datasets and identify trends before building visualizations. SQL also enables joining of tables, removing duplicates, sorting and grouping data which improve Power BI performance.

SQL Joins

Kelvin — Sun, 01 Mar 2026 16:18:49 +0000

Joins are used to combine rows from two or more tables based on a related column between them. This allows you to retrieve connected data stored across multiple tables in a single result.

Types of joins.

WINDOW FUNCTIONS IN SQL.

Key components.
Window functions are defined using the mandatory OVER() clause which specifies how the rows are partitioned and ordered for the calculation.

Average

Count

Max

3: VALUE/OFFSET - lag, lead - Accesses data from rows before, after or at specific points in the window.

lag
Used to compare rows i.e. comparing current value to previous value

lead
looks forward to the next row or a specified number of rows ahead.

Translating Messy Data into Action Using Power BI.

Kelvin — Sun, 08 Feb 2026 10:58:03 +0000

Power BI Desktop is a free Windows application where you create reports and
dashboards. You connect your data, clean it, build visualizations, and save your reports.

Power BI Interface.

Ribbon

Home tab: Get data, Transform data, New Measure, Publish.
modelling tab: Manage relationships, Insert tab, Add visuals.
view tab: Control layout, options.
Format tab: Customize visuals.
Insert: used to add visual and design elements to your report page

Fields pane
Displays all data fields in your model.

Visualizations Pane
Select and configure charts, tables, visuals.

Views

Report View: Create and visualize reports.
Table View: View data in tabular format.
Model View: Manage table relationships.

Uploading Excel files on Power BI.

Load the Data
Open Power BI desktop then click on get data then Excel workbook. Select the excel file you intend to use then load the file.

Transform Data
Power Query is used to clean, transform, and prepare raw data before analysis.
From the home tab, click on transform data to open power query.

Basic Transformations

Rename Queries
Give your query a meaningful name for easier reference.
In Power Query right-click the default query name (e.g., Sheet1). Click rename then type a meaningful name like KenyaCropsData or HospitalAdmissionsData.

Remove Rows
Helps clean blank or unwanted rows.

Options under remove rows:

Remove top rows – e.g., remove header rows accidentally repeated.
Remove bottom rows – to remove totals or notes at the bottom.
Remove blank rows – for completely empty rows.
Remove errors – from a column after converting to correct data types.
Remove duplicates – ensure data uniqueness.

Keep Rows

Opposite of "Remove"; helps retain only what you need.

Options under keep rows

Keep Top Rows – e.g., top 10 hospitals or counties.
Keep Bottom Rows
Keep Range of Rows
Keep Duplicates – to analyze duplicate entries.

Filter Rows
Exclude invalid entries like “N/A”, “None”, or “Error”.

Click the filter icon on the column.
Uncheck values like Error, N/A, None.
You can also apply Text Filters, Number Filters, or Date Filters for specific conditions.

Remove Blank or Error Rows

Select the first few rows to check for any completely blank rows or column headers repeated.
Use "Remove Rows" > "Remove Blank Rows" if any are found.
Also use "Remove Errors" on numeric columns like "Yield", "Market Price" etc., after converting them to proper types you can check for blank rows by clicking on the dropdown next to the column name and checking from the list.

Data Types
For each column, set the correct data type:

Text: Farmer Name, County, Crop Type, Season, Crop Variety, Soil Type, Pest Control, etc.
Decimal Number: Planted Area, Yield (Kg), Market Price, Revenue, Cost of Production, Profit.
Whole Number or Decimal: Farmer Code, depending on use.
Date: Planting Date, Harvest Date.
Use Transform > Detect Data Type to auto-detect, then adjust manually if needed.

Rename Columns
Click on each column header and rename using friendly names

Fix typos like Crop Varie to crop variety, farmer Na to Farmer Name.

Handling Errors.

Columns like “Yield”, “Crop Variety”, “Fertilizer Used” have errors like "Error" or invalid values.
For each column click the filter dropdown, deselect "Error", "N/A", or "None" if those aren't valid options.
Or use Replace Values to change "Error" to null.

Replacing problematic values
Use Transform - replace Values:

Replace "Error" with "N/A" or null.
Replace "None" with null (if not meaningful)
"Short Rain" with Short Rains, "Long Rain" with Long Rains for consistency.

Format Text Data

Standardize formatting of names and categories.
Tools under Transform > Format:

Uppercase – for consistency (e.g., county names).
Lowercase
Capitalize Each Word – best for names like "Crop Type", "County".
Trim – removes leading/trailing spaces.
Clean – removes non-printable characters.

Standardize text capitalization.

To capitalize the first letter, whole word in uppercase or lower case, clean or trim.
Use Transform - Format - capitalize each word or select the preferred format.

Remove Duplicates

If each row must be unique (e.g., by Farmer + Crop Type + Season), select those columns then remove duplicates.

Reorder or group columns

You can move related columns together (e.g., financials: Revenue, Cost, Profit).
Click and drag columns to reorder

Remove Columns/Rows

Right click column header then remove
Filter column (e.g. exclude "Paid" rows)

Split Columns

• Split Name column by space to get First and Last Name

Merge Columns

• Select First and Last Name > Merge > Add separator then OK

Group By

Aggregate data (e.g., total Revenue by County).

Select the column to group by (e.g., County).
Go to Transform > Group By.
Choose aggregation: Sum, Count, Average, etc.
Add additional groupings if needed.

Sort

Organize your data logically.

Click column > Home > Sort Ascending or Descending.
Can sort alphabetically (A-Z), numerically, or by date.

How to Deal with Blanks in Power Query

Identify Blank Cells

Blanks show up as:

Empty cells (null values).
Cells with “N/A”, “None”, “Error” — these are not technically blank, but should be treated as such.

How to Find Them:

Click the filter icon on any column.
If blanks exist, you'll see (null) as a filter option.
look for custom errors like "Error", "N/A", "None" especially in text columns.

Replace Blanks with Default or Meaningful Values
Use when the column is important and must not be left empty.

Select the column - go to Transform - Replace Values, Replace null with:

"Unknown" for text, 0 for numbers or "No Data" or "Not Provided" for descriptions.

You can also use Transform > Replace Errors for error-based blanks.

Fill Down or Fill Up
Use when the blank value should be copied from the row above or below.

Example Use Case: If a “County” or “Farmer Name” is listed only once and applies to several
rows.

Select the column.

Go to Transform > Fill > Down or Up.

Remove Rows With Blanks

Use when:

The blank row has missing critical info (like Revenue, Yield, or Crop Type).
The row has too many blanks and is not usable.

After cleaning

Click Close & Apply to load your cleaned data into Power BI.
Always check the resulting table in the data view for issues.
Ensure your work is saved.

DAX

DAX (Data Analysis Expressions) is a formula language used in Power BI to create
calculations and data analysis logic. DAX is used to build measures, calculated columns, and
calculated tables that help transform raw data into meaningful insights.

Aggregation
Aggregation is the process of combining multiple rows of data into a single summarized value.

Types of Aggregation Functions in DAX

Simple aggregation functions (work directly on a column)
Iterator aggregation functions (end with X and work row by row on an expression)

SUM
SUM adds all numeric values in a column.
SUM works on one numeric column and returns the total based on the current filters.
Total Revenue = SUM('Kenya Crops'[Revenue (KES)])
SUMX
SUMX evaluates an expression for each row in a table and then sums those results.
SUMX is used when the value you want to sum is not stored as a single column but must be calculated row by row first.
Total Revenue (SUMX) = SUMX('Kenya Crops', 'Kenya Crops'[Yield (Kg)] * 'Kenya Crops'[Market Price (KES/Kg)])

AVERAGE
AVERAGE calculates the mean of a numeric column.
Average Yield = AVERAGE('Kenya Crops'[Yield (Kg)])
AVERAGEX
AVERAGEX evaluates an expression for each row and then returns the average of those results.
Average Profit per Acre = AVERAGEX('Kenya Crops', DIVIDE('Kenya Crops'[Profit (KES)], 'Kenya Crops'[Planted Area (Acres)]))

MEDIAN
MEDIAN returns the middle value in a column when the values are sorted.
Median Yield = MEDIAN('Kenya Crops'[Yield (Kg)])

MEDIANX
MEDIANX evaluates an expression for each row and then returns the middle value of the results
Median Revenue = MEDIANX('Kenya Crops', 'Kenya Crops'[Revenue (KES)])

MIN
MIN returns the smallest value in a column.
Minimum Yield = MIN('Kenya Crops'[Yield (Kg)])

MINX
MINX evaluates an expression for each row and returns the smallest result.
Minimum Profit per Acre = MINX( 'Kenya Crops', DIVIDE( 'Kenya Crops'[Profit (KES)], 'Kenya Crops'[Planted Area(Acres)]))

MAX
MAX returns the largest value in a column.
Maximum Yield = MAX('Kenya Crops'[Yield (Kg)])

MAXX
MAXX evaluates an expression for each row and returns the largest result.
Maximum Profit per Acre = MAXX('Kenya Crops', DIVIDE('Kenya Crops'[Profit (KES)], 'Kenya Crops'[Planted Area (Acres)]))

COUNT counts numeric values in a single column.
COUNTROWS counts the number of rows in a table.
COUNTX counts rows where an expression returns a non-blank value.
ABS (Absolute Value) returns the absolute (positive) value of a number.
POWER raises a number to a given power.
SQRT (Square Root) returns the square root of a number.
MOD returns the remainder after division.

LOGICAL FUNCTIONS IN DAX.

Logical functions in DAX are used to make decisions based on conditions. They allow Power BI
to answer “yes or no” questions, classify data into categories, apply business rules, and control
how results are calculated and displayed.

IF FUNCTION
Evaluates a condition and returns one value if the condition is true and another value if the condition is false.
Profit Status = IF(SUM('Kenya Crops'[Profit (KES)]) > 0, "Profitable", "Loss")

NESTED IF STATEMENTS
A nested IF occurs when one IF function is placed inside another IF. This is used when more than two outcomes are required. Profit Level = IF('Kenya Crops'[Profit (KES)] < 0, "Loss", IF('Kenya Crops'[Profit (KES)] < 50000, "Low Profit", "High Profit"))
This logic first checks for losses. If the farm is not making a loss, it then checks whether profit is
low or high.

IF WITH AND (AND FUNCTION)
The AND function is used when all conditions must be true for a result to be returned.
High Yield & Profitable = IF( AND( 'Kenya Crops'[Yield (Kg)] > 2000, 'Kenya Crops'[Profit (KES)] > 0 ), "Yes", "No")
Here, a farm is marked “Yes” only if it has both high yield and positive profit.

IF WITH && (LOGICAL AND OPERATOR)
The && operator performs the same function as AND, but it is more concise and commonly used in professional DAX code.
High Yield Large Farm = IF('Kenya Crops'[Yield (Kg)] > 2000 &&'Kenya Crops'[Planted Area (Acres)] > 10, "Qualified", "Not Qualified")

IF WITH OR (OR FUNCTION)
The OR function is used when at least one condition must be true.
Risk Category = IF(OR('Kenya Crops'[Profit (KES)] < 0, 'Kenya Crops'[Weather Impact] = "Severe" ), "High Risk", "Normal")
A farm is classified as high risk if it made a loss or experienced severe weather.

IF WITH || (LOGICAL OR OPERATOR)
High Risk Farm = IF('Kenya Crops'[Profit (KES)] < 0 || 'Kenya Crops'[Weather Impact] = "Severe", "High Risk", "Low Risk")

THE SWITCH FUNCTION
The SWITCH function is a cleaner and more readable alternative to nested IF statements. It is
ideal when there are many possible outcomes.
Profit Category = SWITCH(TRUE(), 'Kenya Crops'[Profit (KES)] < 0, "Loss", 'Kenya Crops'[Profit (KES)] < 50000, "Low Profit", 'Kenya Crops'[Profit (KES)] < 200000, "Medium Profit", "High Profit")

NOT FUNCTION
The NOT function reverses a logical condition.
Irrigation Type = IF(NOT('Kenya Crops'[Irrigation Method] = "Irrigated"), "Rain-fed", "Irrigated")

ISBLANK FUNCTION
The ISBLANK function checks whether a value is blank.
Yield Availability = IF(ISBLANK('Kenya Crops'[Yield (Kg)]), "Missing", "Available")

CALCULATE FUNCTION
The CALCULATE function is the most important function in DAX. It evaluates an expression under a modified filter context.
In simple terms, CALCULATE changes “what data is being used” for a calculation.
Maize Revenue = CALCULATE( SUM('Kenya Crops'[Revenue (KES)]), 'Kenya Crops'[Crop Type] = "Maize")

CALCULATE WITH MULTIPLE FILTERS
CALCULATE can accept more than one filter. All filters are combined using AND logic,
meaning all conditions must be true.
Maize Long Rains Revenue = CALCULATE(SUM('Kenya Crops'[Revenue (KES)]), 'Kenya Crops'[Crop Type] = "Maize", 'Kenya Crops'[Season] = "Long Rains")

DAX TEXT FUNCTIONS

They help clean text, create readable labels, combine fields, and standardize values for analysis and
reporting.
CONCATENATE / CONCATENATEX
CONCATENATE joins two text values into one. Used to create readable labels, combine fields, or build descriptive columns.
County Crop = 'Kenya Crops'[County] & " - " & 'Kenya Crops'[Crop Type]

LEFT, RIGHT, MID

LEFT extracts characters from the start.
RIGHT extracts characters from the end.
MID extracts text from the middle.

Extract first 3 letters
Crop Code = LEFT('Kenya Crops'[Crop Type], 3)

Extracts last 4 digits
Farmer Code Short = RIGHT('Kenya Crops'[Farmer Code], 4)

UPPER, LOWER, PROPER

UPPER → all caps
LOWER → all lowercase
PROPER → first letter capitalized

Standardize county names
County Clean = UPPER('Kenya Crops'[County])
Make crop types readable
Crop Type Clean = PROPER('Kenya Crops'[Crop Type])

LEN, TRIM, CLEAN

LEN counts characters
TRIM removes extra spaces
CLEAN removes hidden non-printable characters

Removes extra spaces
County Cleaned = TRIM('Kenya Crops'[County])
Checks text length
County Length = LEN('Kenya Crops'[County])

DATE AND TIME FUNCTIONS

Date and time functions are used to work with dates, extract parts of dates, and perform date
based calculations.

DATE, YEAR, MONTH, DAY

DATE creates a date
YEAR extracts the year
MONTH extracts the month
DAY extracts the day

Extracts tear from planting date
Planting Year = YEAR('Kenya Crops'[Planting Date])

TODAY and NOW

TODAY returns today’s date
NOW returns current date and time

TIME INTELLIGENCE FUNCTIONS

Time intelligence functions allow you to compare performance across time periods.

DATEADD

DATEADD shifts the current date context backward or forward.
Revenue Last Year = CALCULATE([Total Revenue], DATEADD('Date'[Date], -1, YEAR))

DATEDIFF
Calculates the difference between two dates
Growth Days = DATEDIFF( 'Kenya Crops'[Planting Date], 'Kenya Crops'[Harvest Date], DAY )

SAMEPERIODLASTYEAR
Returns the same period as the current one, but last year.
Revenue last year = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Date'[Date]))

TOTALYTD, TOTALMTD, TOTALQTD

Year-to-date
Month-to-date
Quarter-to-date

Year-to-date revenue
Revenue YTD = TOTALYTD([Total Revenue], 'Date'[Date])
Month-to-date profit
Profit MTD = TOTALMTD( [Total Profit], 'Date'[Date])

Dashboards

A dashboard is a single-page, interactive summary view of your most important data and KPIs.
Dashboards helps you monitor performance, track metrics, make quick decisions and share insights easily.

From Report view on visualizations select the presentation you would like to use. i.e. column charts, pie charts, slicers, bar charts, tables, etc.
From data select the data you want on the x and y axis. i.e. county, Total revenue, Harvest date.

Add KPIs using the card visualization and slicers using the slicer.
Create a dashboard by copying visualizations from one sheet to the Dashboard sheet.
Ensure your dashboard is not redundant. The visualizations should show different comparisons and should have a theme.

Schemas and Data modelling in Power BI.

Kelvin — Sun, 01 Feb 2026 16:56:17 +0000

In Power BI, schemas and data modelling are about how you structure tables and define relationships so your data is accurate, fast, and easy to analyze.

A schema describes how tables are organized and related. It is framework that defines how data is structured, organized, and related within a system.

Data modelling is the process of connecting multiple data sources, defining relationships between them, and creating calculations to transform raw data into a structured model.

Dimensional schemas

Star schema.
Snowflake schema.

Star schema.

The Star Schema is the industry standard data modelling approach for Power BI. It gets its name from its visual layout: a central fact table surrounded by dimension tables, resembling a star.

Components of a Star schema

Fact table stores quantitative or measurable data.
Dimension table stores descriptive data e.g. date, product name.
Relationships describe how fact tables and dimension tables are connected and how filters flow between them. Features of Dimension Tables.

Fields.
Primary Keys
Every record in a dimension table is uniquely identified by its main key.

Attributes
These are descriptive fields that provide context.
Features of Fact Tables

Foreign Keys
The keys connect to dimension tables and offer context for the measures.

Measures
The provided figures are numerical data that quantify business performance indicators, such as sales income and units sold.

Relationships in star schema

One-to-Many - one row from a table is linked to many rows in another table.
One-to-One - Each row in one table matches one row in another table, and vice versa.
Many-to-One - many rows in a table relate to one row in another table.
Many-to-Many - multiple rows from one table match multiple rows in another table.

Normalization and Denormalization.

Normalization is the term used to describe data that's stored in a way that reduces redundancy.
Denormalization is the process of flattening related dimension data into single wide tables to improve analytical performance and simplify reporting.

Snowflake schema.
A snowflake schema is a variation of the star schema where dimension tables are normalized into multiple related tables instead of being kept as one wide table.

Key Features of snowflake schema

Normalized dimension tables - dimension tables are normalized into third normal form (3NF) or higher splitting them into smaller and granular sub tables.
Hierarchical structure - dimensions are organized into hierarchical layers (e.g. product - category -manufacturer) which naturally represents complex business relationships.
Reduced data redundancy - by eliminating repeated values in dimension tables, this schema significantly reduces the risk of data inconsistencies.

Components of snowflake schema.

Fact table is the core of the schema storing the primary quantitative data or measures of business processes. It contains multiple foreign keys that link directly to the primary dimension tables. It captures events at their most atomic level, typically resulting in the largest table in the warehouse.
Normalized dimension tables that surround the fact table and provide descriptive context for the recorded facts. Unlike the star schema these tables are normalized meaning repeated descriptive values are moved into separate tables reducing redundancy.
Lookup function that acts as the first level of description for the fact table.
Sub dimension tables are formed by further splitting or snowflaking the main dimension tables.
Hierarchical layers represent multiple levels of a hierarchy. e.g. a product dimension may link to a category table which then links to a manufacturer table.
Maintenance enhance data integrity because updates to a category name that only need to happen in one record rather than multiple records.

Key fields in Snowflake schema.

Primary keys are unique identifiers in dimension and sub dimension tables e.g. ProductID.
Foreign Keys are fields in the fact table or dimension tables that reference a primary key in a related table to form a join.
Surrogate keys are system generated integers used as keys instead of natural business keys to improve performance.

Importance of good modelling.

Proper modelling prevents logical errors in relationships that can lead to double counting or missing data.
Clear naming conventions and hidden technical fields make the model easier for non technical users to navigate.
Simple relationships between a central fact table and surrounding dimension tables reduce ambiguity.
Centralized dimension tables ensure every visual uses the same definitions preventing different charts from showing conflicting totals for the same metrics.
A modular model allows you to add new data sources or dimensions without rebuilding the entire system.

Introduction to MS Excel for data analytics

Kelvin — Fri, 23 Jan 2026 17:24:07 +0000

Overview.

Microsoft Excel is a spreadsheet program used to organize, analyze, calculate, and visualize data in rows and columns. Its core structure uses cells, where data is entered, allowing for complex calculations and data insights, making it essential for professionals in many fields.

Excel interface.

Excel's user interface is designed for efficiency, with a grid-based workspace where data is organized into rows and columns.
A workbook is the file containing multiple tabs at the bottom
A worksheet is a grid of cells identified by column letters A,B,C and row numbers 1,2,3.forming addresses like A2,b3.

Ribbon is the top toolbar with tabs like Home, Insert, Formulas, Data, and Review.
The Formula bar displays the content of the selected cell.
The Name box shows the active cell's address or named ranges.
The Status bar at the bottom, it provides quick stats like sum or average of selected cells.

Removing duplicates.

In Excel, you can remove duplicates by selecting your data, going to the Data tab, clicking Remove Duplicates, choosing the columns to check, and confirming to keep only unique records

Conditional formatting.

Conditional formatting in Excel lets you automatically change the appearance of cells (colour, icons, data bars) based on rules, making patterns and issues easy to spot. You apply it by selecting your data, going to Home then conditional Formatting, choosing a rule such as highlighting values greater than a target, and setting the desired format.

Freeze panes.

Freeze Panes in Excel keeps specific rows or columns visible while you scroll through your worksheet, making large datasets easier to read. You use it by selecting a cell below the row and to the right of the column you want to keep visible.

Data filter

A data filter in Excel lets you display only the rows that meet specific criteria, making it easier to analyze large datasets. You apply it by selecting your data and clicking Data then Filter, then using the dropdown arrows in the column headers to filter by values, text, numbers, dates, or conditions.

Data sorting.

Data sorting in Excel arranges data in a specific order—such as A to Z, Z to A, smallest to largest, or by date—to make information easier to analyze. You sort data by selecting a column, going to Data then Sort, choosing the sort order and if needed multiple levels for more complex sorting

Excel functions.

Microsoft Excel functions are predefined formulas that perform specific calculations or operations on data. They are essential for data analytics, enabling users to manipulate, analyze, and visualize datasets efficiently without writing custom code from scratch

Types of excel functions.

Aggregate functions

Aggregate functions summarize multiple rows of data into a single meaningful value.
sum =SUM(B2:B100)

average =AVERAGE(B2:B100)

maximum =MAX(B2:B100)
minimum =MIN(B2:B100)
count =COUNT(B2:B100)
Conditional aggregation
Conditional aggregation is the process of summarizing data only when specific conditions are met.
SUMIF() aggregates values based on a single condition.
SUMIFS() aggregates values based on multiple conditions.
COUNTIF Used to count records that meet one or more conditions.
COUNTIFS Used to count records that meet multiple conditions.

Logical functions

logical functions automate decision making.
IF
If function performs a logical test and returns value if the test is true.
=IF(J2>6, "high performance", "low performance")
Nested IF
A nested if is simply an if formula inside another if formula. It lets you test multiple conditions.
=IF(R2>30,"Highly Experienced",IF(R2>20,"Moderately Experienced",IF(R2>10,"Low Experience","Very Low Experience")))
And
And function returns value when two conditions have to be met.
=IF(AND(R2>30,W2>10),"assign bonus", "do not assign")
Or
Or function returns value when either of the conditions have been met
=IF(AND(G2>1/1/2006, L2>6), "promoted", "not promoted")

Lookup functions.

VLOOKUP (vertical lookup) function is used to look up a value in the first column of a table and return a related value from another column.
=VLOOKUP(10871, A2:AA877, 5, FALSE) 5 is column index number.
HLOOKUP (Horizontal Lookup) is used when your lookup values are in the top row of a table and you want to return a value from a row below.
=HLOOKUP(10011, A1:AGS21, 4, FALSE)
10011 is data in a cell
INDEX
Index function returns the value from a actual position in a range.
=INDEX(G2:G877, MATCH(10871, F2:F877, 0))
MATCH
Match function Finds the position of a value in a range

Text functions.

Trim removes extra spaces. =TRIM(A2)
Len returns length of a string.=LEN(A2)
Left extracts the leftmost characters of a string. =LEFT(A2,4)returns first 4 characters.
Right function extracts the rightmost characters of a string. =RIGHT(A2,2) returns last 2 characters.
Mid extracts characters from the middle of a string.
=MID(b2,2,3)
Concatenation used to combine two texts.
=CONCAT(C2, " ", C3

Date and Time functions.

Now function returns the current date and time and updates automatically.
=NOW()
today function returns the current date
=today()
end of month returns end of 3 months =eomonth(f2,3) i.e. 22/1/2026 returns 30/4/2026
network function returns difference between two days excluding weekends.=networkdays(k2,j2)
date difference returns difference between two dates. =datedif(k2,j2, "M")
edate returns today's date added 3 months =edate(f2,3) ie 22/1/2026 returns 22/4/2026

Pivot tables, Pivot charts & Dashboards

Pivot table.

A Pivot Table is an Excel tool used to summarize, analyze, and explore data quickly without writing formulas.
To insert a pivot table on excel click on the data you want to create a pivot table then click on insert on the toolbar, then click on pivot table.

From the pivot table fields on the right, drag fields to the desired area i.e. filter, rows, columns, and values.

Slicers.

A Slicer is a visual filter that lets you interactively filter data in Pivot Tables and using clickable buttons.

Pivort charts.

Pivot Charts are charts that are directly linked to Pivot Tables. When the Pivot Table changes the chart updates automatically.
From the pivot table analyze menu click on the pivot chart and select the desired chart.

Dashboards.

A Dashboard is a single screen report that shows key metrics and insights using charts, PivotTables, and visuals so decision makers can understand performance at a glance.
To create a dashboard create a new worksheet, rename it to Dashboard. open the worksheet and select a wide area or ctrl+A then fill with your desired background colour. Copy your pivot charts and slicers from the separate worksheets and paste them on your dashboard worksheet. Add shapes to add text e.g. "dashboard title" then resize them to the desired sizes.