Forem: Mendy Kevin

Introduction to Server-less and AWS Lambda

Mendy Kevin — Fri, 12 Dec 2025 08:23:42 +0000

AWS Lambda enables the deployment of various applications, including machine learning models.

For our case, it will involve sending the URL of a picture of pants to our deployed model, and the service responds with multiple classes along with their respective scores. For our use case, we will use TensorFlow Lite instead of the standard TensorFlow.

To access AWS Lambda, simply type “lambda” in the AWS console. A service named “Lambda Run Code without Thinking about Servers” will be displayed. This encapsulates the essence of what you can expect from Lambda. All you need to do is write some functions without concerning yourself with EC2 instances or similar infrastructure; AWS Lambda takes care of everything.

Benefits of using AWS Lambda

There are three primary advantages of utilizing Lambda functions:

**Infrastructure Abstraction:**
    With Lambda functions, you are relieved from the burden of managing infrastructure for serving models. This eliminates the need to consider EC2 instances.

**Cost-Efficiency:**
    AWS Lambda operates on a pay-per-request model, meaning you only incur charges when the Lambda function is actively performing tasks. This pay-as-you-go approach can lead to cost savings compared to maintaining constantly running infrastructure.

**Free Tier Usage:**
    AWS Lambda provides a free tier, offering a certain amount of free Lambda requests per month (1 million requests per month), for each account. This can be advantageous for small-scale or experimental projects.

After testing, if you no longer require the Lambda function, you can delete it by navigating to the “Actions” dropdown and selecting “Delete function.”

Create a sample lambda function

Creating a simple Lambda function is straightforward; just provide some basic parameters. Choose “Author from scratch,” assign a function name, and specify Python 3.x as the runtime. This is sufficient to create the function, resulting in a Python file with the specified function name.

Understanding Function Parameters:

event: Contains the input data passed to the function (e.g., a JSON payload).
context: Provides details about the invocation, configuration, and execution environment.

To make it easy just change the code to:

# event is whatever we'll pass to the lambda function
import json
def lambda_handler(event, context):
    print("parameters: ", event)
    return "PONG"

Lets adjust our code to accommodate url of test-event-code:

# test event code IE: add this code to the test event:
{
    "url": "some-url-of-pants"
}

# lambda function
import json
def lambda_handler(event, context):
    print("parameters: ", event)
    url = event['url']
    results = predict(url)
    return results

Check the next post as we discuss tensor flow vs tensor flow lite.

Complete Guide to Deploying Machine Learning Models with Flask and Docker(NO fluff configure and run like a pro)

Mendy Kevin — Sun, 02 Nov 2025 13:15:23 +0000

Hello all! Welcome. This article addresses the technical aspects of deploying Machine Learning models that use Logistic Regression, a linear model used to make predictions based on trained data. I promise you'll be technical like a pro in configuring machine learning models , so stick around till the end.

What You'll Learn

Packaging models with Pickle
Serving ML models with Flask
Containerizing apps with Docker

- Exposing inference endpoints in Docker

The Big Picture: Understanding ML Model Deployment

Let's understand the overall workflow of deploying a machine learning model:

Save the Model: Start by taking your Jupyter notebook where the model resides and save it to a file with a .bin extension.
Load as a Web Service: Load this model from a different process (using a Python script) in a web service—for example, a "churn service" that predicts a customer's churn rate. We'll use Flask to transform the model into a web service.
Isolate Python Dependencies: Use pipenv (similar to conda or pip) to isolate the dependencies for this service and prevent interference with other services on your machine.
Isolate System Dependencies: Add another layer using Docker to isolate system dependencies.
Deploy to the Cloud: Once the local setup is complete, deploy the service to the cloud. You can use any cloud platform, but we'll use AWS Elastic Beanstalk (EB) for this tutorial.

Setting Up the Environment

Before training our model, we need to ensure our development environment has the right dependencies without interfering with other projects (which might require different versions of scikit-learn, pandas, etc.). We'll use pipenv for this.

Installing Pipenv

Note: If you have Anaconda installed and added to your system variables, it will automatically activate the (base) conda environment when you open a new shell/terminal. We don't want to install pipenv inside conda. Instead, we'll install it globally.

# Deactivate conda
conda deactivate

# Install uv (a faster Python package manager)
pip install uv

# Install pipenv globally
uv pip install pipenv

Creating a Pipenv Virtual Environment

Once you're in your project directory, manage all Python libraries and dependencies via pipenv:

# Create a pipenv virtual environment
# This automatically creates Pipfile and Pipfile.lock
pipenv --python 3.12

# Activate the virtual environment
pipenv shell

# Install all requirements for your project
# (Using scikit-learn==1.5.1 because this project requires this specific version)
pipenv install flask scikit-learn==1.5.1 numpy pandas requests

# Note: pickle is built into Python, no need to install it separately

# Check dependencies
pipenv graph

# Update Pipfile.lock according to your current Pipfile
pipenv lock

Launching Jupyter Notebook

After installation, launch Jupyter notebook inside your virtual environment:

# If you prefer VS Code
code .

# Or if you have Anaconda installed
jupyter lab
# or
jupyter notebook

If done correctly, you'll see your virtual environment in the Jupyter launcher.

Training the Model

Let's look at how we trained our model. (This isn't the primary focus, so I'll keep it brief.)

Making Necessary Imports

import pandas as pd
import numpy as np

from sklearn.model_selection import train_test_split
from sklearn.model_selection import KFold

from sklearn.feature_extraction import DictVectorizer
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import roc_auc_score

Data Preparation

# Read and prepare data
df = pd.read_csv('data-week-3.csv')

# Make column names homogeneous
df.columns = df.columns.str.lower().str.replace(' ', '_')

# Handle categorical columns
categorical_columns = list(df.dtypes[df.dtypes == 'object'].index)

for c in categorical_columns:
    df[c] = df[c].str.lower().str.replace(' ', '_')

# Handle numerical data
df.totalcharges = pd.to_numeric(df.totalcharges, errors='coerce')
df.totalcharges = df.totalcharges.fillna(0)

# Convert target variable to binary
df.churn = (df.churn == 'yes').astype(int)

# Define feature types
numerical = ['tenure', 'monthlycharges', 'totalcharges']

categorical = [
    'gender', 'seniorcitizen', 'partner', 'dependents',
    'phoneservice', 'multiplelines', 'internetservice',
    'onlinesecurity', 'onlinebackup', 'deviceprotection', 
    'techsupport', 'streamingtv', 'streamingmovies', 
    'contract', 'paperlessbilling', 'paymentmethod'
]

Data Splitting

# Split into training and test sets
df_full_train, df_test = train_test_split(df, test_size=0.2, random_state=1)

Define Training Function

Important: To apply the model later, we need to return both the DictVectorizer and the model. Otherwise, the function will return None.

def train(df_train, y_train, C=1.0):
    dicts = df_train[categorical + numerical].to_dict(orient='records')

    dv = DictVectorizer(sparse=False)
    X_train = dv.fit_transform(dicts)

    model = LogisticRegression(C=C, max_iter=1000)
    model.fit(X_train, y_train)

    return dv, model

Define Prediction Function

def predict(df, dv, model):
    dicts = df[categorical + numerical].to_dict(orient='records')

    X = dv.transform(dicts)
    y_pred = model.predict_proba(X)[:, 1]

    return y_pred

K-Fold Cross Validation

C = 1.0
n_splits = 5
kfold = KFold(n_splits=n_splits, shuffle=True, random_state=1)

scores = []

for train_idx, val_idx in kfold.split(df_full_train):
    df_train = df_full_train.iloc[train_idx]
    df_val = df_full_train.iloc[val_idx]

    y_train = df_train.churn.values
    y_val = df_val.churn.values

    dv, model = train(df_train, y_train, C=C)
    y_pred = predict(df_val, dv, model)

    auc = roc_auc_score(y_val, y_pred)
    scores.append(auc)

print('C=%s %.3f +- %.3f' % (C, np.mean(scores), np.std(scores)))

Saving the Model with Pickle

We use 'wb' (Write Binary) mode to save the model. Include the DictVectorizer in your file so that when you load the model in your churn service, you can convert customer data from a dictionary into a feature matrix (which the model requires for predictions).

import pickle

output_file = 'model_C=1.0.bin'

with open(output_file, 'wb') as f_out:
    pickle.dump((dv, model), f_out)

Creating the Churn Service

Use a Python script for this. Load the model and make sure to use the POST HTTP method since we need to send information to the web service.

predict.py:

import pickle
from flask import Flask, request, jsonify

model_file = 'model_C=1.0.bin'

# Load the model
with open(model_file, 'rb') as f_in:
    dv, model = pickle.load(f_in)

app = Flask('churn')

@app.route('/predict', methods=['POST'])
def predict():
    # Get customer data from JSON request
    customer = request.get_json()

    # Transform and predict
    X = dv.transform([customer])
    y_pred = model.predict_proba(X)[0, 1]
    churn = y_pred >= 0.5

    result = {
        'churn_probability': float(y_pred),  # Convert to native Python type
        'churn': bool(churn)  # Convert to native Python type
    }

    return jsonify(result)

if __name__ == "__main__":
    app.run(debug=True, host='0.0.0.0', port=9696)

Running the Service

Launch your app on your local server. The --host=0.0.0.0 flag makes the server publicly available. The --debug flag auto-reloads the app when you save changes.

flask --app predict.py run --debug --host=0.0.0.0

Querying the Service

Now we'll send a POST request to our server with customer details and receive a churn prediction. Use a Jupyter notebook or separate Python script:

import requests

url = 'http://localhost:9696/predict'

# Sample test data from the test dataset
customer = {
    'customerid': '4183-myfrb',
    'gender': 'female',
    'seniorcitizen': 0,
    'partner': 'no',
    'dependents': 'no',
    'tenure': 21,
    'phoneservice': 'yes',
    'multiplelines': 'no',
    'internetservice': 'fiber_optic',
    'onlinesecurity': 'no',
    'onlinebackup': 'yes',
    'deviceprotection': 'yes',
    'techsupport': 'no',
    'streamingtv': 'no',
    'streamingmovies': 'yes',
    'contract': 'month-to-month',
    'paperlessbilling': 'yes',
    'paymentmethod': 'electronic_check',
    'monthlycharges': 90.05,
    'totalcharges': 1862.9
}

response = requests.post(url, json=customer).json()
print(response)

if response['churn'] == True:
    print(f'Sending promo email to {customer["customerid"]}')
else:
    print(f'Not sending promo email to {customer["customerid"]}')

The server responds with a 200 OK response:

The query was successful:

Docker Time!

Understanding Docker Components

DOCKERFILE: A text file (usually named Dockerfile) containing a series of instructions for building Docker images. Each line represents a new instruction, forming a stack of layers. Each layer is cacheable—when you build an image twice, it uses the cache. When you change a line, it rebuilds all instructions after and including the change.

IMAGE: The output of building a Dockerfile. Think of it as an executable—just like clicking an icon launches an application, you start an image to launch a container. The image encapsulates your application code and all dependencies, ensuring consistency across environments.

CONTAINER: A dynamic, running instance of a Docker image. One image can spawn many containers. On Linux, containers run as processes on the host machine. On Windows/macOS, Docker runs in a VM. Containers share the kernel but have isolated file systems—they appear like VMs but are much lighter.

Creating a Dockerfile

# Create a new Dockerfile
touch Dockerfile

# Open in your editor
code Dockerfile

Important Notes:

Make sure your Python script name matches the name in the ENTRYPOINT layer
Gunicorn is only for Unix-based systems; use Waitress for Windows
Leave a space after every line of code

Dockerfile:

FROM python:3.12-slim

RUN pip install pipenv

WORKDIR /app

# Copy dependency files
COPY ["Pipfile", "Pipfile.lock", "./"]

RUN pipenv install --system --deploy

# Copy application files
COPY ["LOAD.py", "model_C=1.0.bin", "./"]

EXPOSE 9696

# Start the application with Gunicorn
ENTRYPOINT ["gunicorn", "--bind=0.0.0.0:9696", "LOAD:app"]

CMD vs ENTRYPOINT

ENTRYPOINT defines the main command that must always run—it's like the container's executable.

ENTRYPOINT ["python", "app.py"]

Running docker run myapp executes python app.py. You can pass parameters: docker run myapp --debug becomes python app.py --debug.

CMD defines default arguments or a fallback command that can be completely overridden.

CMD ["python", "app.py"]

Running docker run myapp bash overrides CMD and runs bash instead.

Combining Both:

ENTRYPOINT ["python", "app.py"]
CMD ["--port=9696"]

docker run myapp → python app.py --port=9696
docker run myapp --debug → python app.py --debug (overrides CMD only)

ENTRYPOINT can only be overridden with the --entrypoint flag.

Multistage Dockerfiles

For Machine Learning applications, you often need a large environment to build/train your model but only a small runtime environment to serve predictions. Multistage Dockerfiles help create:

Smaller images – no unused dependencies
More secure – fewer libraries = less attack surface
Faster deployment – smaller images push/pull faster
Better maintainability – clean separation of concerns

Building and Running the Container

Build the Docker image:
churn-prediction is just a name for the docker image you can literally call it anything. But make sure the name of the build image is the same you pass to the docker run command.

docker build -t churn-prediction .

Run the container:
PS: The errors you are seing on terminal of a running docker instance is because I somehow installed scikit-learn==1.6.1 instead of 1.5.1 but otherwise our model is working just fine.

docker run -it --rm -p 9696:9696 churn-prediction

Docker Commands Reference

Managing Images

# List all local images
docker image ls

# Run an image
docker run churn-prediction

Docker first looks at the local registry for images. If not found locally, it checks Docker Hub. You can also use custom registries:

# Pull from a custom registry
docker run https://registrydomain.com/repository-server:0.1.0

Managing Containers

List containers:

# List running containers
docker container ls
# or
docker ps

# List all containers (including stopped)
docker container ls --all

Start, stop, and remove containers:

# Stop a container
docker container stop <container-id>

# Restart a container
docker container restart <container-id>

# Remove a stopped container
docker container rm <container-id>

# Kill a running container
docker kill <container-id>

Cleanup Commands

# Remove unused containers, networks, and images
docker system prune

# Remove everything including unused images
docker system prune -a

# Also remove volumes (deletes data!)
docker system prune -a --volumes

Accessing Containers

For debugging purposes:

# Access a container's shell
docker exec -it <container-id> bash

Deploying to AWS Elastic Beanstalk

Now that we have our containerized application, let's deploy it to AWS Elastic Beanstalk using the EB CLI.

Prerequisites

Before deploying, ensure you have:

An AWS account
AWS credentials configured
A credit card to verify and activate your account
Your Docker image working locally

Other free alternatives include render, railway,fly.io, heroku etc. But the deployment process is almost pretty follows the same logic only that you have to check out their documentation to know the commands they use. However, for now let's use aws elastic beanstalk service.

Installing the EB CLI

First, install the Elastic Beanstalk CLI:

# Install EB CLI using pipenv (recommended for this project)
pipenv install awsebcli --dev

# Or install globally using pip
pip install awsebcli

Verify the installation:

eb --version

Configuring AWS Credentials

If you haven't configured your AWS credentials yet:

# Configure AWS CLI
aws configure

You'll be prompted to enter:

AWS Access Key ID
AWS Secret Access Key
Default region (e.g., us-east-1)
Default output format (e.g., json)

Initializing Elastic Beanstalk

Navigate to your project directory and initialize EB:

# Initialize Elastic Beanstalk
eb init -p docker -r us-east-1 churn-prediction

Flags explained:

-p docker: Specifies the platform (Docker)
-r us-east-1: AWS region (choose your preferred region)
churn-prediction: Your application name

You'll be prompted with questions:

Select your region
Enter application name (or accept default)
Choose whether to use CodeCommit (typically select "no")
Set up SSH for your instances (recommended for debugging)

Creating an Environment

Create an environment to run your application:

# Create an environment named 'churn-prediction-env'
eb create churn-prediction-env

This process takes several minutes as AWS:

Creates an EC2 instance
Sets up load balancers
Configures security groups
Deploys your Docker container

You'll see real-time logs of the deployment process.

Monitoring Deployment Status

Check the status of your environment:

# Check environment status
eb status

# View recent events
eb events

# Follow logs in real-time
eb logs --stream

Testing Your Deployed Application

Once deployment is complete, get your application's URL:

# Open your application in a browser
eb open

Or manually test the endpoint:

import requests

# Replace with your actual EB URL
url = 'http://churn-prediction-env.us-east-1.elasticbeanstalk.com/predict'

customer = {
    'customerid': '4183-myfrb',
    'gender': 'female',
    'seniorcitizen': 0,
    'partner': 'no',
    'dependents': 'no',
    'tenure': 21,
    'phoneservice': 'yes',
    'multiplelines': 'no',
    'internetservice': 'fiber_optic',
    'onlinesecurity': 'no',
    'onlinebackup': 'yes',
    'deviceprotection': 'yes',
    'techsupport': 'no',
    'streamingtv': 'no',
    'streamingmovies': 'yes',
    'contract': 'month-to-month',
    'paperlessbilling': 'yes',
    'paymentmethod': 'electronic_check',
    'monthlycharges': 90.05,
    'totalcharges': 1862.9
}

response = requests.post(url, json=customer).json()
print(response)

Updating Your Application

When you make changes to your code:

# Deploy updates
eb deploy

# Monitor deployment
eb status

Environment Configuration

You can modify environment variables and settings:

# Set environment variables
eb setenv FLASK_ENV=production MODEL_VERSION=1.0

# View current configuration
eb config

Scaling Your Application

Scale your application based on traffic:

# Enable auto-scaling (via AWS Console or CLI)
# Minimum 1 instance, maximum 4 instances
eb scale 2  # Set to 2 instances

# Or configure auto-scaling through the console

Monitoring and Logs

Access logs and monitoring:

# Download logs
eb logs

# Stream logs in real-time
eb logs --stream

# View health status
eb health

Troubleshooting Common Issues

Issue: Deployment fails

# Check detailed logs
eb logs

# SSH into the instance for debugging
eb ssh

Issue: Health status is degraded

Check if the application is responding on the correct port (9696)
Verify the Docker container is running
Check environment variables

Issue: Connection timeout

Ensure security groups allow inbound traffic on port 80/443
Verify the load balancer health check settings

Cost Management

Important: Elastic Beanstalk environments incur costs. To avoid unnecessary charges:

# Terminate the environment when not in use
eb terminate churn-prediction-env

# Or just stop it temporarily (still incurs some costs)
eb stop

Configuration Files

For more control, create an .ebextensions directory with configuration files:

.ebextensions/01_flask.config:

option_settings:
  aws:elasticbeanstalk:application:environment:
    PYTHONPATH: "/var/app/current:$PYTHONPATH"
  aws:elasticbeanstalk:container:python:
    WSGIPath: predict:app

Best Practices

Use environment variables for sensitive data (API keys, database credentials)
Set up health checks to ensure your application is responding correctly
Enable logging to CloudWatch for better monitoring
Use HTTPS by configuring SSL certificates
Implement auto-scaling based on your traffic patterns
Tag your resources for better cost tracking

Useful EB CLI Commands Summary

# Initialize EB in your project
eb init

# Create a new environment
eb create <environment-name>

# Deploy application updates
eb deploy

# Open application in browser
eb open

# Check environment status
eb status

# View logs
eb logs

# SSH into instance
eb ssh

# Terminate environment
eb terminate

# List all environments
eb list

# Set environment variables
eb setenv KEY=VALUE

# Scale application
eb scale <number-of-instances>

Conclusion

You've successfully:

Trained a machine learning model
Packaged it with Pickle
Created a Flask web service
Containerized the application with Docker
Deployed it to AWS Elastic Beanstalk

The containerized approach ensures consistency across environments, and AWS Elastic Beanstalk handles scaling, monitoring, and infrastructure management automatically. Congratulations on learning how to deploy your models. See you in the next one.

Additional Resources

Microsoft Just Laid Off 6000 Employees(but why?)Is AI to blame

Mendy Kevin — Fri, 23 May 2025 06:36:21 +0000

Microsoft just laid off 6,000 employees and the internet has always freaked out. You have probably seen the headlines. "AI is replacing all the developers!" or "Tech is dead!" But before we spiral into yet another panic cycle, it’s worth taking a step back to assess what’s actually happening.

Tech Layoffs Aren’t New

Let’s set the record straight. Layoffs in tech aren’t some AI-induced anomaly. They’ve occurred consistently over the years, long before artificial intelligence became a mainstream buzzword.

2014: Microsoft laid off 18,000 after acquiring Nokia.
2015: HP slashed 30,000 jobs during a major restructure.
2016: Intel let go of 12,000, shifting away from legacy PC products. PCs, IBM, Yahoo, and plenty of other giants have done the same. Companies change direction. They restructure. They cut costs. Layoffs like these are about strategy, not science fiction .

What did these Layoffs mean

That's what a pivot looks like. Letting go in one area to reinvest in another. These layoffs were strategic — part of larger business pivots. It’s called restructuring, not extinction. When tech companies lay people off, it’s often a sign not of decline, but of redirection. Intel’s layoffs, for example, weren’t just about trimming the workforce. They were about shifting focus to emerging sectors like cloud computing and smart devices. Over time, that strategic move created new opportunities and thousands of jobs in those high-growth areas. This is what a corporate pivot looks like: reducing investment in legacy operations to reinvest in the future.

The Social Clickbait Circus.

But of course, social media clowns turn it into something bigger than it really is. They don't waste a second before yelling, “AI took their jobs!” Something goes wrong in tech? "Blame AI." —Plenty of news outlets do the same. Spin fear into clicks because fear gets attention. They're not trying to explain what's actually happening. They're just chasing views. It’s no different of the vaccine panic during the pandemic when nearly every misfortune was blamed on vaccines. Heart attacks, car crashes, even bad weather. Someone stubbed their toe, must be the vaccine. Today, AI has become the new scapegoat. If something goes wrong in tech, AI is immediately is to blame.

Tech Isn’t Dying — It’s Evolving

The truth, however, is far more grounded. Companies still need engineers, not just people who know how to write code. I'm talking about real engineers. People who can think clearly, write clean, maintainable code, understand how systems behave, and solve real world problems. With AI in the picture, the tech career is entering a new chapter. We'll be building tools and platforms we can't even imagine today. These tools will create new challenges. And those challenges will need you, thoughtful, capable engineers, solutions architects(you name them) to solve them.

Vibe Coders Won’t Survive

Unfortunately, vibe coding has become a trend lately. People pasting prompts into an LLM and calling themselves engineers and yet they don't understand data structures, design patterns, system design, security, or even how to debug. Relying solely on AI-generated output without the ability to evaluate, improve, or even understand it renders you dangerously unprepared. It's like flying a plane by watching YouTube tutorials midair. That makes you reckless and unsustainable. The reality is, if your skills begin and end with copying code without comprehension, you're not just replaceable by AI. You’re irrelevant.

What the Industry Needs Now

If you want to keep growing in the industry and stay relevant, you need real skills. Solid fundamentals. Deep understanding of how things work under the hood. Companies are going to pay you for what you actually know. Not for the vibes. For example, Did you know the demand on engineers are going up, not down? Back in the 80s or '90s, most tech job listings focused on knowing one language, a bit of databases, maybe some basic algorithms. But today, you have to know multiple languages, frameworks, CI/CD, cloud platforms, architecture, APIs, security, automated testing, the list goes on. And tomorrow it'll be even more. AI will help us automate boring repetitive tasks and help us to focus on the bigger more complex problems. So to some point, AI will make it possible for everyone to develop code just like anyone can use a calculator but not everyone can do math.

Final Thoughts

If all the noise lately has left you feeling anxious, don't let it. Don't let the social media clowns get to you. Don't let fear-based headlines distract you. Stay curious, keep learning, keep growing. The future doesn't belong to people yelling on the internet. It belongs to the ones who show up, level up, and keep building.
So, if all the noise has left you feeling anxious, take heart.

That’s all for now. Let’s keep pushing boundaries with AI. I'll see you in the next one.

Ultimate guide to creating a pipeline(Apache Airflow)

Mendy Kevin — Thu, 22 May 2025 03:28:08 +0000

Hello there data enthusiasts. Today's guide walks you through building a complete data pipeline using Apache Airflow. Apache Airflow is an open-source platform designed to programmatically author, schedule, and monitor workflows. A workflow—such as an ETL process, machine learning pipeline, or reporting task—is a directed sequence of dependent tasks that transforms raw data into valuable output. In this article, we’ll cover setting up WSL (for Windows users), installing PostgreSQL via the terminal, setting up and configuring Apache Airflow, and creating your first DAG (a Python-defined graph representing the workflow). We’ll conclude by executing the DAG and observing it in action. So buckle up and lets dive in:

Installation WSL

We'll begin by installing the Windows Subsystem for Linux (WSL), which allows Windows users to run a native Linux environment directly on Windows—ideal for working with tools like Apache Airflow.

Step 1: Open PowerShell as Administrator

wsl --install

This command installs WSL 2 as the default with the latest Ubuntu distribution and required kernel updates. Restart your machine if prompted.

Step 2: Verify Installation

wsl --version

Step 3: Launch Ubuntu

Search for Ubuntu in the Start menu, launch it, and set up your UNIX username and password.

From here, all commands in this guide will be run from within the Ubuntu terminal which is native for all linux users and we have just configured it for windows users.

Setting up Postgresql

After successfully setting up your ubuntu account, lets proceed to setting up postgresql that will serve as the metadata database for Apache Airflow—storing DAG states, task history, logs, and configurations

Step 1: Update package lists

sudo apt update

Step 2: Install Postgresql

sudo apt install postgresql postgresql-contrib

Step 3: Start the PostgreSQL Service

sudo service postgresql start

# Check whether postgresql is running
sudo service postgresql status

Step 4: Access the PostgreSQL Shell

sudo -i -u postgres
psql

You should see postgres#.

Step 5: Create Airflow Database and User

CREATE DATABASE airflow;
CREATE USER airflow WITH PASSWORD 'airflowpass';
GRANT ALL PRIVILEGES ON DATABASE airflow TO airflow;
\q

Setting up Apache Airflow

Download python to your environment

sudo apt install python3 python3-venv python3-pip

Step 1: Create and Activate a Virtual Environment

python3.10 -m venv airflow_env #make sure the python version is between 3.7 and 3.11
source airflow_env/bin/activate

N/B: Always activate your virtual environment when starting apache airflow.

Step 2: Set Environment Variables for Airflow

export AIRFLOW_HOME=~/airflow
export AIRFLOW__CORE__SQL_ALCHEMY_CONN='postgresql+psycopg2://airflow:airflowpass@localhost:5432/airflow'

Replace 'airflowpass' with your actual password if different.

Step 3: Install Apache Airflow with PostgreSQL Support

export AIRFLOW_VERSION=2.8.1
export PYTHON_VERSION="$(python3 --version | cut -d " " -f 2 | cut -d "." -f 1,2)"
export CONSTRAINT_URL="https://raw.githubusercontent.com/apache/airflow/constraints-${AIRFLOW_VERSION}/constraints-${PYTHON_VERSION}.txt"

pip install "apache-airflow[postgres]==${AIRFLOW_VERSION}" --constraint "${CONSTRAINT_URL}"

Step 4: Initialize the Airflow Database

airflow db init

N/B: This command is only used during after fresh install of Apache airflow or when you create a new environment for Apache airflow.

Step 5: Create Admin User

airflow users create \
    --username admin \
    --firstname Admin \
    --lastname User \
    --role Admin \
    --email admin@example.com

Step 6: Start Webserver and Scheduler

Open another ubuntu terminal instance and add:

airflow webserver --port 8080

airflow scheduler

N/B: Always restart the scheduler after making changes to the dag .py files or after rebooting your device or after killing the terminal instance.

Step 7: Access the Airflow UI

Open your browser and go to: http://localhost:8080

Proper Configuration of Airflow

Before we create our first DAGs, we need to optimize apache airflow for performance by confirming some of its configurations.

Step 1: Editing the `airflow.cfg` file

source airflow_env/bin/activate
cd airflow
ls airflow.cfg

Now edit the file using nano:

nano airflow.cfg

default_timezone default_timezone = Africa/Nairobi
executor executor = LocalExecutor
load_examples load_examples = False
sql_alchemy_conn sql_alchemy_conn = postgresql+psycopg2://postgres:<password>@localhost:5432/postgres

To save: ctrl + x, press y, press enter.

Step 2: Creating a dags folder

mkdir dags
cd dags
touch DAG.py

Open VS Code and paste the following into DAG.py:

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime
import requests
import psycopg2

DB_CONFIG = {
    "dbname": "postgres",
    "user": "postgres",
    "password": "15304232",
    "host": "localhost",
    "port": "5432"
}

def extract():
    url = "https://api.coingecko.com/api/v3/simple/price?ids=bitcoin,ethereum&vs_currencies=usd"
    response = requests.get(url)
    return response.json()

def transform(raw_data):
    return [(coin, price["usd"], datetime.now()) for coin, price in raw_data.items()]

def load(data):
    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor()
    cur.execute("""
        CREATE SCHEMA IF NOT EXISTS crypto;
        CREATE TABLE IF NOT EXISTS crypto.crypto_prices (
            coin TEXT,
            usd_price NUMERIC,
            timestamp TIMESTAMP
        );
    """)
    cur.executemany(
        "INSERT INTO crypto.crypto_prices (coin, usd_price, timestamp) VALUES (%s, %s, %s);",
        data
    )
    conn.commit()
    cur.close()
    conn.close()

def etl():
    raw = extract()
    clean = transform(raw)
    load(clean)

with DAG(
    dag_id="simple_coingecko_etl",
    start_date=datetime(2023, 1, 1),
    schedule_interval="@hourly",
    catchup=False
) as dag:

    run_etl = PythonOperator(
        task_id="run_etl",
        python_callable=etl
    )

    run_etl

N/B: Make sure you have the correct connection strings.

DB_CONFIG = {
    "dbname": "postgres",
    "user": "postgres",
    "password": "your_password",
    "host": "localhost",
    "port": "5432"
}

Step 3: Open the file `DAG.py`

nano DAG.py

Paste the contents from VS Code.

N/B: After the configuration, make sure you restart the scheduler.

Airflow UI configuration

Refresh the tab hosted locally at: http://localhost:8080

If your DAG is working correctly, you should see:

Confirm on DBeaver that the database is created and tables are loading

Before checking on DBeaver, edit the postgresql.conf file:

sudo nano /etc/postgresql/<your_postgresversion>/main/postgresql.conf

Change:

listen_addresses = '*'

Edit the connection in DBeaver:

Enter your database info and test the connection.

To know your host name:

hostname -I

Its been a long and bumpy article but I hope it was helpful. Feel free to leave a comment and add insights to help me improve on the article.
That's all for now and let's keep it data. Bye for now!

Mistakes Beginners Make When Visualizing(Power BI)

Mendy Kevin — Tue, 20 May 2025 04:57:28 +0000

Hello there data enthusiast. Today I'm back with Mistakes beginners make when creating dashboards using Power BI. I hope you stick till the end because I won’t waste your time. With that said, let's get fraudy, shall we:

When creating dashboards, it is very important that you do not mix up yourself by coloring too much and forgetting the main goal of BI tools which is to show trends, patterns, and insights in a way that is easily understandable. Today I want you to learn from the mistakes I made when creating a real estate dashboard so that you won’t have to make the same mistakes again. For that reason, I would like you to pause for a minute and look at the following visual and spot out its mistakes before you dive all in.

Mistakes Beginners Make When Creating Dashboards Using Power BI

1: Overuse of Colors Without Hierarchy

Power BI makes it easy to go wild with colors. But in the dashboard shown above, we see bold red, multiple greens, and highlighted blocks all competing for attention. Color should guide the eye, not confuse it. Use color sparingly—assign it to draw attention to critical insights only, not every card. Remember the goal is to turn raw data into meaningful charts and graphs that give actionable insights and not to showcase your graphic design abilities.

2: Overcrowding and Visual Clutter

The dashboard tries to cram a lot of information into a single screen. There are many visuals, and some of them feel squeezed. This makes it hard for the user to focus on the most important insights. By wanting to show everything you end up having cluttered, overwhelming dashboards. A key principle of dashboard design is less is more. For example, the tile (was intended to be a slicer) labeled “Locations” displays six regions in huge blocks, but adds no visual value. This can be replaced with a complete unfiltered slicer or drop-down filter to allow interaction without crowding the canvas. The number of cards, along with the two bar charts and scatter plot, completely confuse the audience. It's not immediately clear what the most important information is. The dashboard lacks a strong visual hierarchy to guide the user's eye.

3: Inconsistent Number Formatting

Check the value cards:

“Max Price: $350M” vs. “Min Price: $6.5M” — USD.
“Total Revenue: Ksh9.3bn” and “Average Revenue: Ksh65.30M” — KES.

Mixing currencies without proper labels or context confuses your audience. Imagine presenting such an inconsistent dashboard report to your stakeholder (let's not even go there). Always maintain a consistent currency or provide clear legends and conversion rates.

4: Inconsistent and Inappropriate Visual Choices

Some of the visual choices don't seem optimal. Don't just pick visuals because they look fancy, but because they effectively communicate the data. For example a line chart is not the best visual for representing the price by bedrooms. Line charts are used to show trends over time. In this case the best fit would have been a bar chart. Also the bar charts, while not terrible, might not be the most effective way to compare price by location. The location should be used as with a slicer.

Again, the “Average Price” visual using a gauge chart— is commonly discouraged unless you're showing progress toward a goal. In this context, the average value is better suited for a card or bar comparison.

6: Overloading Summary Cards and Scatter Plots

Six KPI cards are presented upfront—Max Price, Min Price, Total Houses, Revenue, Average Revenue, and Missing Values. It’s a cognitive overload. Group related metrics together and only highlight the 3–4 most actionable ones.

Again, the “Price Bedroom Scatter” chart lacks axis titles and has dense plotting. While scatter plots can reveal patterns, tooltips and filters are essential for large datasets. Consider applying zoom or grouping logic.

How it should be

Thank you for sticking around to the end. Now that we have highlighted the mistakes, let's have a look at the corrected version of the dashboard report.

Wrap-up

Dashboards aren’t just about showing data—they’re about communicating insight.

As you build your BI skills, remember: clarity, consistency, and usability > decoration.

What did I miss? Feel free to drop a comment (Critics are highly appreciated by the way).

Until next time, stay data-driven and don’t get fraudy! (just kidding)

Machine Learning for beginners

Mendy Kevin — Sun, 18 May 2025 07:39:43 +0000

Hello there, data enthusiast! Glad to have you back for another deep dive. So buckle up. Today, I will walk you through one of the major field in data that is the talk of on everyone's lips. Machine learning. Today, we will have a look at what machine learning is, what problems require machine learning domain, how to know when to use machine learning and then we will wrap it by creating a simple regression model.(do not worry if you do not get what a model is or what regression involves I'll walk you through all the basics you need to understand machine learning more intuitively.) Lets get fraudy, shall we:

What is machine Learning

That's a good place to start. So, what is machine learning? Machine learning is an art of programming computers so that they can learn from data. The part of machine learning that learns and makes predictions is called a model. That's right, it's an art. And if you are paying close, attention you should realize that machine learning is an art of developing models. If you are passionate about art you definitely will enjoy machine learning. (pun intended)

What does Machine Learning involve

Now that we know how to define machine learning in one long sentence, let's get a little deeper and get to understand what kind of problems or projects require to be solved using machine learning concepts:

1. Problems that require a lot of fine tuning or long lists of rules. Traditional rule-based systems require explicitly coded instructions for every condition. When the number of rules becomes massive or unmanageable (e.g., spam detection based on keywords or transaction fraud) machine learning becomes practical. A spam filter would learn which words and phrases are good predictors of spam by detecting unusually frequent patterns of words in the spam folder. That's a perfect example of how machine learning is helpful because that simple problem would require thousands of else if statements just to detect spam emails.

2. Complex programming problems that traditional hard coding yields zero to no results. No matter your coding skills, there are some real world problems you cannot hard code on your computer because there is no simple algorithmic solution to detect the problem let alone solving the problem. For example, Image recognition, natural language understanding, and speech-to-text conversion are governed by high-dimensional, non-linear patterns that can’t be feasibly encoded manually. ML models like Convolutional Neural Networks (CNNs) or Transformers automatically extract abstract features and model these relationships through training. (Don't get too scared about big words like Convolutional Neural Networks yet, there are simply no easier terms to explain this nut crack. But I hope you get the point.)

3. A highly fluctuating environment. A machine learning environment that can be retrained on new data. Think about a highly volatile system involving stock market price predictions. This kind of system requires constant update of the system model with new available information to make sure that the system performance remains optimal. This adaptability makes ML preferable over static, rule-based systems.

4. Getting insights from large amounts of data. Digging into large amounts of data to gain insights is called data mining and Machine learning excels in it.

A Simple Machine Learning Project

Machine learning models can be categorized in various ways, such as by the type of supervision (supervised, unsupervised, or reinforcement learning), their ability to learn incrementally (online vs. batch learning), and whether they rely on instance-based learning or model-based learning. In this context, we will build a simple model that falls under supervised learning, where the model is trained on labeled data to make predictions. It's just a simple model so just follow along.

Supervised Learning.

Under supervised learning, there can be various classifications:

Classification. Training a machine learning model based on a class. eg ( ham or spam emails)
Regression. Predicting a target numeric value with a set of data with given features.
Logistic regression. Classification of regression eg 20% chance of being spam.

The model we are going to create below will be a very simple regression model that will give you the big picture of what creating machine learning models involves. It uses a python code that loads data, separates the inputs x and the labels y, creates a scatter plot for visualization , and then trains a linear model that makes predictions.

A regression model

First you want to make sure you have python setup on your machine and have a code editor of your choice already fired ready to create a model. For this demo, I am going to use VS code. press ctrl+shift+pto open a new notebook and name the notebook with a .ipynbextension. Then make sure the data set you are going to use(for those with locally available datasets) is in the same directory as the .ipynb notebook. But for our case, we are going to obtain our dataset from an online Github repo. But for future models, where you have your dataset locally stored, make sure that you have it in the same directory as your notebook file.

Installing the Libraries.

pip install matplotlib numpy pandas scikit-learn

Importing the Libraries

import matplotlib.pyplot as plt
import numpy as np
import pandas as pd 
from sklearn.linear_model import LinearRegression

pyplot library provides functions to create static, animated, and interactive visualizations (e.g., scatter plots, line charts).
numpy library is a fundamental package for numerical computing in Python, used for handling arrays, mathematical operations, and linear algebra. (Today we are just going to use it for mathematical ops and handling of arrays. No linear algebra for today)
pandas library is a powerful data analysis library used for reading, writing, and manipulating structured data through DataFrames.
scikit-learn library is a popular and versatile Python library for machine learning. It provides a wide range of tools and algorithms for tasks like classification, regression, clustering, and data preprocessing.The LinearRegression class from Scikit-learn enables fitting linear models to data for regression tasks.

Obtaining, extracting and storing the datasets


data_root = "https://github.com/ageron/data/raw/main/"
lifesat=pd.read_csv(data_root + "lifesat/lifesat.csv")
X = lifesat[["GDP per capita (USD)"]].values
y = lifesat[["Life satisfaction"]].values

here is what the above code does:

data_root = "https://github.com/ageron/data/raw/main/"
Defines the base URL where the dataset is hosted; it's used to construct the full path to the CSV file.

lifesat = pd.read_csv(data_root + "lifesat/lifesat.csv")
Downloads and loads the CSV file into a pandas DataFrame named lifesat. A DataFrame is a datastructure used in python libraries like pandas and other languages like R used to organize raw csv data in rows and columns

X = lifesat[["GDP per capita (USD)"]].values
Extracts the GDP per capita column as a NumPy array X, formatted as a 2D array with shape (n_samples, 1).

y = lifesat[["Life satisfaction"]].values
Extracts the life satisfaction scores as a NumPy array y, also as a 2D array for model training compatibility.

Visualizing the dataset

Why visualize during training?: Visualizations (e.g., scatter plots, histograms) help reveal patterns, trends, outliers, and potential correlations, guiding feature selection and preprocessing.

lifesat.plot(kind='scatter',  grid=True, x="GDP per capita (USD)", y="Life satisfaction")
plt.axis([23500, 62500, 4, 9])
plt.show()

lifesat.plot(...)
Creates a scatter plot from the lifesat DataFrame, plotting GDP per capita on the x-axis and Life satisfaction on the y-axis, with grid lines enabled.

plt.axis([23500, 62500, 4, 9])
Sets the range of the x-axis from 23,500 to 62,500 and the y-axis from 4 to 9 to focus on a specific region of the data.

plt.show()
Displays the plotted figure.

Model selection

model = LinearRegression()
Select the model LinearRegression() which is an inbuilt function and the store it in the variable model.

Model Training.

The line model.fit(X, y) trains the machine learning model (e.g., LinearRegression) by finding the best parameters (e.g., slope and intercept) that minimize the prediction error between input features X and target values y. If I threw you off the bus a little, try to imagine your model is a student learning to draw a straight line through dots on a paper.

When you say model.fit(X, y), you're telling the student: "Look at these dots (X and y), and draw the best straight line that goes as close as possible to all of them." It learns the underlying relationship in the data to make future predictions on unseen input.
It is simply used to select the best features that will train the model to make the right predictions with minimal errors.

Can Our Model Make A Prediction?

X_new = [[37_655.2]] #cyprus gdp per capita in 20202
print(model.predict(X_new)) #output: [[6.30165767]]

This code tells the trained model: "If a country has a GDP per capita of 37,655.20, what life satisfaction should we expect?" The model answers: "Based on what I learned, about 6.30." — it's using the line it learned to make a guess.

And there you go, your first regression model. Based on what we did, was that difficult? Then what's stopping you from creating more models?

Leave a comment for critics or anything you have in mind. That's all for now and see you in the next one.

The Five Levels Of SQL

Mendy Kevin — Fri, 02 May 2025 21:02:28 +0000

Introduction.

Hello there data enthusiasts and welcome to yet another post on SQL. Today we are gonna dive into the five levels of SQL and I promise you are gonna love it and learn. So let's do some nerdy stuff. Shall we:

1. What is SQL:

Before we can get too ahead of ourselves, let's
develop a deeper more intuitive understanding of SQL. As you might have already heard, SQL (STRUCTURED QUERY LANGUAGE) is the language used to interact with structured relational database engines eg PostgreSQL or MySQL. The interaction with database using SQL may involve one or all of the following activities:

Retrieving records from database.-> SELECT.
Adding new records to the database. -> INSERT.
Modifying existing records. -> UPDATE.
Removing existing records. -> DELETE.

The most best database to write SQL queries is PostgreSQL because it is object relational database, it is modern and most importantly it is open source (no license required). Most other databases however like oracle and MySQL may require a license for you to use them. It is best to learn SQL queries from terminal(using psql shell) because if you happen to SSH to a server, you can be able to handle the database effectively unlike when you are used to using GUI related databases. Now let's dive into the 5 levels of SQL query writing.

2. The five levels of SQL

LEVEL ZERO
This involves knowing to use the SELECT * FROM table_name; This is an equivalent of opening an excel sheet or a word document to get your hands into the data. If you can play around with this query intuitively, then you are on the right track to mastering SQL.

LEVEL ONE
Do you know the rest of the key words like SELECT, FROM, WHERE, GROUP BY, HAVING, LIMIT, ORDER BY etc. Do you also understand the order of execution of these key words when you combine them in one query which is FROM, WHERE, GROUP BY, HAVING, SELECT,ORDER BY,LIMIT. See if you understand what is going on in the following sample code.

SELECT id,, first_name
FROM person
WHERE country_of_birth = 'Kenya'
GROUP BY gender
HAVING COUNT(*) > 5
ORDER BY id
LIMIT 10;

LEVEL TWO
Can you master joins that you are going to use like the inner join and left join which are the most common and the less common include full outer join. Joins that you are not going to use very much are going to be right join and cross join.example sample query

SELECT p.first_name, s.salary
FROM person p
INNER JOIN salary s ON p.id = s.person_id;

Also make sure you have a good understanding of common table expressions(CTEs) and more often use those than sub queries eg by using with the WITH keyword. sample code below. Can you explain to someone what is going on?

WITH recent_births AS (
  SELECT * FROM person WHERE date_of_birth > '2020-01-01'
)
SELECT * FROM recent_births WHERE gender = 'Female';

LEVEL THREE
You know window functions. IN this case you have a function eg SUM(), RANK() or AVG() and then you have the over clause and you have the window. A window is defined by partition, by order in rows. Also in this level you should be able to tell the difference between a RANK(skips ranks on ties), DENSE RANK(no rank gaps on ties) and ROW NUMBER( unique sequence). For example, do you relate with the following sample code.

SELECT id, first_name,
       RANK() OVER (PARTITION BY country_of_birth ORDER BY date_of_birth) AS birth_rank
FROM person;

LEVEL FOUR
Understand table scans to be able to get the right optimization techniques eg TABLE SCANS, INDICES, PARTITIONING etc. For example using EXPLAIN ANALYZE to inspect query plans. eg

EXPLAIN ANALYZE
SELECT * FROM person WHERE country_of_birth = 'Kenya'

LEVEL FIVE
If you have made it up to this far, you wouldnt be surprised to know that at level five, you should be able to write these queries effectively, debug them for errors without the help of an LLM. Only use LLMs to speed up your query writing process or automate repetitive tasks.

So at what level of SQL mastery are you at. I'd love to know. Leave a comment below. Hope you enjoyed the read and see you in the next one.

Mastering Microsoft Excel: From Basics to Advanced Analysis

Mendy Kevin — Sun, 27 Apr 2025 12:12:54 +0000

Whether you are a data analyst or a regular MS Excel user, you will definitely find this post useful because I will take you through a complete mastery of MS Excel. I will touch on how to:

Organize and manipulate data
Perform calculations using formulas and functions
Visualize data through charts and graphs
Create interactive dashboards and reports
Perform statistical, financial, and logical analysis using MS Excel

So buckle up and let's dive right in:

1. What is Excel?

You may be asking yourself, "What is Excel?" And you're right to. MS Excel is a powerful data analysis tool developed by the Microsoft company. It is used by business analysts, data analysts, project and operations managers, teachers and lecturers, researchers, sales and marketing teams, etc., to help them organize data, perform calculations, visualize information, create interactive dashboards, and conduct statistical analysis. Let's dive into an overview of Microsoft Excel:

2. Launching MS Excel

MS Excel is primarily a Windows application, so this article is Windows-based. To start MS Excel on Windows, click on the Windows button, type "excel," and click Enter to launch. You will see many templates, but for now, we are working with creating a new blank workbook. When you create a new workbook, you will be redirected to a new window with an empty worksheet. However, if for some reason your application doesn't have MS Excel, you can search for an online version of Excel on your favorite browser, which requires you to have a Microsoft account.

3. Key Concepts in Excel

Workbook

A workbook is an Excel file that contains one or more worksheets. You can open a workbook in Excel only if it has a .xlsx extension, which used to be .xls for older versions of Excel.

Worksheet

A worksheet is a single spreadsheet consisting of grids of rows and columns within a workbook. Each worksheet has a name (e.g., Sheet1) and can be renamed to your liking. You can create multiple worksheets by pressing the little '+' button at the bottom of the Excel workbook.

Cell

That's right, a cell. A cell is the basic unit of a worksheet where you enter data. It is identified by a cell reference, such as A1 (Column A, Row 1). A cell reference is generated according to the current active cell.

Data Types

General, Number, Currency, Date, Time, Scientific, Fraction, etc. You can change the type of data stored in multiple cells by highlighting them, going to the Home tab, where you will find the data type dropdown as shown below.

Range

A range is a group of two or more cells. It is used to define the group of data you would like to work on. A range can be:

Vertical: {A1:A5}
Horizontal: {A1:E1}
Rectangular block: {A1:C5}

Formula

Formulas in Excel are user-defined mathematical equations (meaning they start with an '=' sign) that the user employs to perform mathematical calculations, e.g., =A1+B1.

Functions

Functions are also mathematical tools; however, instead of being user-defined, they are built-in Excel functions.
NB: They also start with an '=' sign, e.g., =SUM(A2:A6) where A2:A6 is the range, and the built-in function SUM returns the sum of all the values in the given range. Other important functions in Excel include:

AVERAGE

=AVERAGE(C2:C6)

COUNT

=COUNT(B2:B6)

MAX AND MIN

=MAX(B2:B6) or =MIN(B2:B6)

More advanced functions include:

CONCATENATE

Combines text from multiple cells into one, e.g.,

=CONCATENATE(A2, " ", B2)

UPPER()

→ makes all text uppercase

LOWER()

→ makes all text lowercase

PROPER()

→ capitalizes first letters (e.g., “John Smith”)

LEFT

Extracts a specific number of characters from the beginning of a string. E.g.,

=LEFT(A2, 3)

returns the first 3 characters of cell A2.

IF, IFS, and SWITCH

IF: Used to make a decision based on true or false. Example:
```
=IF(C2>=50, "Pass", "Fail")
```

IFS: Used to make a decision based on multiple statements.

=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", TRUE, "F")

SWITCH: Used to compare one value or expression against a list of values and returns the matching result, e.g.,
```
=SWITCH(A2, "HR", "Human Resources", "IT", "Information Technology", "FIN", "Finance", "Unknown")
```
Returns "Information Technology" if A2 is "IT".

XLOOKUP

=XLOOKUP(lookup_value, lookup_array, return_array)

E.g.:

=XLOOKUP("Pen", A2:A6, B2:B6)

With those key concepts, let's now look at the key data operations in Excel, like Data filtering, sorting, validation, cleaning, visualization, and formatting. We will also touch on Pivot Tables and Pivot Charts.

4. DATA OPERATIONS IN EXCEL

DATA FILTERING and SORTING

Filtering allows you to display only rows that meet specific criteria. Sorting helps arrange your data in a meaningful order, e.g., in ascending or descending order.

There are two ways of doing this: the table way and the un-tabled format way:

TABLE WAY:

This brings us to the concept of a table in Excel. To create a table in Excel is easier than you might think. First, you might want to select all the data you want to put in a table. Now, there are two ways of selecting:

Click and drag your mouse, which is the easiest for small datasets.
Select the first row using your mouse and then use Shift + Down Arrow Keys or Ctrl + Click your mouse for advanced selective selections.

Once your data is selected, you can right-click, then click on "Table," then "Create Table."

Or, you could go to the Insert tab, select "Table," and voila!

You have a table you can use to filter and sort your data to your liking, only if you have auto-filter enabled for your table on the Home tab in MS Excel.

RANGE WAY:

Select the column data you want to sort or filter as explained earlier. Then, on the Home tab, you can find "Sort & Filter" buttons where you can sort and filter your data according to your preferences.

DATA VALIDATION

Restrict user input to only allow "Yes," "No," or "Maybe" in selected cells.

Steps in Excel:

Select the cells where the user should input a response (e.g., C2:C20).
Go to the Data tab.
Click on Data Validation.
In the dialog box, under the Settings tab:
- Allow: List
- Source: Yes,No,Maybe
Under the Input Message tab:
- Title: Response Required
- Input message: Please select Yes, No, or Maybe from the list.
Under the Error Alert tab:
- Style: Stop
- Title: Invalid Entry
- Error message: Only Yes, No, or Maybe are allowed.
Click OK.

Users will now see a dropdown list in each selected cell and can only choose "Yes," "No," or "Maybe," preventing invalid data entries.

DATA CLEANING

NB: Before cleaning your data, make sure you study it well enough so that you do not remove nulls or duplicates of crucial data that will be needed for analysis. Use your wisdom and knowledge as a data analyst to clean your data effectively.

a. DUPLICATES

Here's how you remove duplicates in Excel:

Select your data (click and drag to highlight the range, or click on any cell in the range if it's a full table).
Go to the Data tab on the ribbon.
Click Remove Duplicates.
A popup appears—check the columns you want to check for duplicates.
Click Remove Duplicates.

b. NULLS

Let's say you want to replace nulls in Excel using the mode:

Go to File > Options > Add-ins.
At the bottom, choose Excel Add-ins, click Go.
Check Analysis ToolPak, then click OK.
Go to the Data tab and click Data Analysis.
Choose Descriptive Statistics, then click OK.
Select your data range, choose where to output the results, and check Summary statistics.
Click OK.

You could also use the formula =MODE.SNGL(A2:A100), which returns the mode, but make sure you select the correct range.

After finding the mode, we need to replace the nulls with the mode value:

Click on the header of the column where the blanks are.
Open Find and Replace (Ctrl + H or Home > Find & Select > Replace).
In Find what: leave it completely blank (do NOT type a space).
In Replace with: type the mode value.
Click Replace All.

c. DATA TYPES

Make sure you select the correct data types for the columns, which is part of data cleaning.

d. Conditional Formatting

Highlight anomalies or errors visually:
Go to Home > Conditional Formatting. Example: Highlight cells with text in a numbers column, or highlight duplicates.

e. Data Visualization

We do data visualization using charts and graphs. The general way to insert a chart for your data is:

Select Your Data (include headers, as they act as labels for charts).
Go to the Insert Tab.
Choose a Chart Type you need.
Customize the Chart (change colors or chart styles, add titles, data labels, or legends, switch rows and columns if the chart isn't displayed as expected, move and resize the chart).

There are various types of visualization templates, but the most common include:

Line charts: Great for trends over time.
Column charts and bar charts: Good for comparing values.
Column chart:

Bar chart:
Pie chart: Shows parts of a whole.
Scatter Plot: For relationships between two variables, e.g., weight and height.
Area chart: Perfect for showing how values change over time.

f. PIVOT TABLES

Pivot tables are a powerful tool in Excel for summarizing and analyzing large datasets. They allow you to extract meaningful insights by grouping and aggregating data based on different fields.

g. Creating a Pivot Table:

Select the data range you want to analyze. Ensure your data has clear headers.
Go to the Insert tab and click PivotTable.
In the "Create PivotTable" dialog box, confirm the data range and choose where you want to place the pivot table (a new worksheet is usually recommended). Click OK.

The "Pivot Table Fields" pane will appear on the right. Drag and drop fields into the four areas:

* Rows: Fields placed here will appear as row labels in the pivot table.
* Columns: Fields placed here will appear as column labels.
* Values: Fields placed here will be aggregated (e.g., summed, averaged, counted). Excel will often automatically suggest an aggregation.
* Filters: Fields placed here can be used to filter the entire pivot table.

Benefits of Pivot Tables:

Easy Summarization: Quickly summarize large amounts of data.
Flexibility: Easily rearrange fields to view data from different perspectives.
Calculations: Perform calculations like sums, averages, counts, percentages, etc., without writing formulas.
Grouping: Group data by date, time, or other categories.
Filtering: Filter data to focus on specific subsets.

h. PIVOT CHARTS

A pivot chart is a dynamic chart that is directly connected to a pivot table. It provides a visual representation of the summarized data in the pivot table, making it easier to understand trends and patterns.

Creating a Pivot Chart:

Create a pivot table first (as described above).
Select any cell within your created pivot table.
Go to the PivotTable Analyze (or Options in older versions) tab.
In the Tools group, click PivotChart.
Choose the desired chart type (e.g., column, bar, line).
Click OK.

The pivot chart will automatically display the data from your pivot table. As you manipulate the fields in the pivot table, the pivot chart will update dynamically to reflect those changes.

You could also add a slicer for a more dedicated and easy viewing of the pivot chart as shown on the left of the pivot chart.

Benefits of Pivot Charts:

Visual Summarization: Provides a visual representation of pivot table data.
Interactivity: Changes in the pivot table are immediately reflected in the chart.
Enhanced Understanding: Makes it easier to identify trends and patterns in summarized data.
Dynamic Reporting: Creates dynamic reports where the visuals change based on data manipulation.

By leveraging Pivot Tables and Pivot Charts, you can significantly enhance your data analysis capabilities in Excel, allowing for more insightful and dynamic reporting.

With that, you are good to go in excel. Hope you enjoyed the post. Leave a comment for critics or anything you might have in mind. Thank you.