DEV Community: Sharon-nyabuto

Writing Reusable code in Python

Sharon-nyabuto — Mon, 03 Aug 2026 21:41:41 +0000

Understanding Functions

Previous articles in this series have guided us on how to make decisions, repeat tasks, and solve increasingly complex problems using Python.
As our programs grow, another challenge begins to emerge; repeating codes we need to use more than once.

Say you've written a program that checks a dataset for missing values. Later on, you need to perform the same check on a different dataset. Do you copy and paste the same code, or is there a better way?
Fortunately, Python allows us to group reusable code into a function, making it easy to use the same logic whenever we need it, entirely avoiding the repetition.

What is a Function?

A function is a reusable block of code that performs a specific task.
Functions allow us to package reusable code into a named block so we can call it whenever we need it, instead of writing the same logic repeatedly.
Functions don't just save typing, they make code reusable.

Defining your first function

Consider a task on analyzing several household survey datasets collected over different years. Before analyzing each one, you want Python to display the same message indicating that the analysis has started.

Without functions, you would need to write the same line of code every time.

print("Analyzing household survey dataset...")
print("Analyzing household survey dataset...")
print("Analyzing household survey dataset...")

While this works, repeating the same code easily becomes difficult to maintain. If you later decide to change the message, you would have to edit it everywhere it appears.

Instead, Python allows us to define a function once and call it whenever we need it.

def analyse_dataset():
    print("Analysing household survey dataset...")

Let's break it down:

def Is the keyword that tells Python that you are defining a new function.
analyse_dataset is the function's name. It should be a descriptive name, i.e. it should explicitly describe what the function does.
() is where any information the function needs (known as parameters) will go. For now, the parentheses are empty because our function doesn't require any input.
: marks the beginning of the function body.
The indented code underneath is the set of instructions Python executes each time the function is called.

Notice that nothing happens yet. This is because defining a function only tells Python that it exists. The code inside the function will not run until we explicitly call it.

Calling a function

Once a function has been defined, you can execute it simply by writing its name followed by parentheses. This is what is known as calling the function

In the case of our example above, we simply write the name of the function whenever we need it, as many times as we would need it.

analyse_dataset()
analyse_dataset()

the output:

Analysing household survey dataset...
Analysing household survey dataset...

Let's see how we can have a function performing a specific task.

Scenario: You've received a household survey dataset. Before analyzing it, you want to know how many records are complete.

# The dataset
household_survey = [
    ["HH001", 25000, 4], ["HH002", None, 3],
    ["HH003", 18000, None], ["HH004", 32000, 5],
    ["HH005", None, 6]]  

# The function
def count_complete_records():
    complete = 0

    for row in household_survey:
        if None not in row:
            complete += 1
    print(f"Complete records: {complete}")

# Calling the function
count_complete_records()

What happens Step by Step:

Python defines the function count_complete_records().
When the function is called, it loops through the dataset.
Each row is checked to see if it contains any missing values (None).
If the row is complete, the counter increases by one.
After checking every record, Python prints the total number of complete records, i.e.

Complete records: 2

This function works well, but it has one limitation. It is written specifically for the household_survey dataset because the dataset is hardcoded inside the function.
If we needed to count complete records in a different survey, we would either have to edit the function or create another one. When working with multiple datasets, this becomes a little inefficient.
Fortunately, it is possible to write one function that works with different datasets.

Parameters

Rather than storing the dataset inside the function (as we did in the example above), Python allows us to pass the dataset into the function whenever it is called. We do this using parameters.

A parameter is a variable listed inside a function's parentheses. It acts as a placeholder that receives the information passed to the function when it is called.

def count_complete_records(dataset):
    complete = 0

    for row in dataset:
        if None not in row:
            complete += 1
    print(f"Complete records: {complete}")

Notice the difference between this function and the previous one. This time, the function includes a parameter, dataset.

In this case, dataset acts as a placeholder for the dataset we want the function to work with. Because the dataset is no longer hardcoded into the function, the same code can be used with different datasets.

But where does the parameter get its value?

It receives its value when the function is called. For example:

count_complete_records(household_survey)

Notice another difference here. This time, the parentheses contain the name of the dataset we want to analyse. This is known as an argument.

When the function is called, Python takes the value of household_survey and passes it to the parameter dataset.
From that point onwards, every time the function refers to dataset, it is working with household_survey.

Parameter             Argument

dataset   <─────── household_survey

In simple terms, the parameter is the placeholder inside the function, while the argument is the actual value you provide when calling the function.

Now if we needed to count the number of complete records in the economic impact survey below;

economic_impact_survey = [
    ["B001", None, "Farmer"],
    ["B002", 150000, "Trader"],
    ["B003", None, "Fisher"],
    ["B004", 12000, "Farmer"],
    ["B005", 15000, "Farmer"], 
    ["B006", None, None]
]

we would not need to modify or define the function all over again, but just pass the argument(name of the dataset) when calling the function;

count_complete_records(economic_impact_survey)

The output:

Complete records: 3

Because the function uses a parameter, it can work with any dataset that follows the expected structure.

An important point to remember is that simply existing does not make functions reusable. It is the presence of parameters that allows them to work with different inputs.

Returning Values

In the examples above, we have been displaying the results of our functions immediately using print. This is helpful for when we want to see the result.
In some situations, we do not want to see the result, but want to use the result elsewhere in the program.

Suppose we wanted to compare the number of complete records in two different datasets. Printing the result alone wouldn't allow us to perform that comparison. We would need a way for the function to send the result back to the rest of the program.

The return statement sends a value back to the place where the function was called.
Think of print() as showing the result to the user, while return gives the result back to the program so it can continue working with it.

Let's modify our function;

def count_complete_records(dataset):
    complete = 0

    for row in dataset:
        if None not in row:
            complete += 1

    return complete

Notice that we've replaced:

    print(f"Complete records: {complete}")

with:

    return complete

Now, instead of displaying the result immediately, the function hands the value back to the program. These values can then be stored in variables;

household_complete = count_complete_records(household_survey)
economic_complete = count_complete_records(economic_impact_survey)

The variables now contain the number of complete records for each dataset, and since the values have been stored, they can be printed, compared or used in other calculations.

For example, we can compare the number of complete records in the two datasets as follows:

if household_complete > economic_complete:
    print("The household survey has more complete records.")
else:
    print("The economic impact survey has more complete records.")

The output:

The economic impact survey has more complete records.

Using the return statement makes functions far more flexible and reusable, because they can be stored in a variable and be used later.

Conclusion

In this article, we have seen how functions enable us to write reusable code, by defining them once and calling them whenever we need them. By using parameters, the same function can even work with different datasets, making our programs more flexible and easier to maintain.

This brings us to the end of our introduction to Python series. Throughout this series, we have built the skills needed to write clear, reusable Python programs, from making decisions and repeating tasks to organizing code with functions.

Continue practising beyond the examples we've explored, experiment with your own ideas, and challenge yourself with new problems. The confidence you build comes not from reading code, but from writing it.

In future articles, we will shift our focus from learning Python itself to applying these skills to data analysis, showing how they come together to solve practical problems.

Repeating Tasks Within Tasks

Sharon-nyabuto — Thu, 30 Jul 2026 06:28:44 +0000

Understanding nested loops in Python

Think about an analog clock such as the one below.

Figure 1. An analog clock.

Image: Wikimedia Commons.

For every complete rotation of the second hand, the minute hand advances by one minute. Same goes for the hour hand; for every complete rotation of the minute hand, the hour hand advances by one hour.

Programming sometimes requires this same kind of repetition. But how can we tell Python to repeat one task inside another repeated task?

The answer lies in nestedloops. By definition, a nested loop is simply a loop placed inside another loop.
For every iteration of the outer loop, the inner loop runs from beginning to end before the outer loop moves to its next iteration.

Much like the hour and minute hands on an analog clock, the outer loop advances one step only after the inner loop has completed all of its iterations.

In data analysis, nested loops are particularly useful when you need to work through two levels of data, such as rows and columns in a dataset, households within counties, or regions and their characteristics.

A nested loop can be made from any combination of for and while loops. However, you'll most commonly use a for loop inside another for loop.

Syntax

for outer_variable in outer_sequence:
    # Outer loop
    for inner_variable in inner_sequence:
        # Code to repeat

Let us revisit the clock analogy in Python;

for hour in range(1, 4):
    print(f"Hour {hour}")

    for minute in range(1, 61):
        print(f" Minute {minute}")

Here's what happens, step by step;

Python starts with the outer loop and sets hour to 1.
Before moving to the next hour, Python enters the inner loop.
The inner loop starts with minute = 1 and continues printing every minute until it reaches 60.
Once all 60 minutes have been printed, the inner loop finishes.
Python returns to the outer loop and updates hour to 2.
The inner loop starts again, printing minutes 1 through 60 for the second hour.
The same process repeats for hour = 3.
After the final hour has completed all 60 minutes, both loops end.

Result;

Hour 1
 Minute 1
 Minute 2
 Minute 3
 ...
 Minute 58
 Minute 59
 Minute 60
Hour 2
 Minute 1
 Minute 2
 Minute 3
 ..
 Minute 58
 Minute 59
 Minute 60
Hour 3
 Minute 1
 Minute 2
 Minute 3
 ...
 Minute 58
 Minute 59
 Minute 60

Notice that the minutes restart from 1 every time the hour changes. This is because the inner loop begins again from the start for every iteration of the outer loop, just as the minute hand completes a full revolution before the hour hand moves to the next hour.

Nested Loops Use cases

1. To find missing values (data cleaning)

Imagine you're checking a dataset before analysis. You need to examine every row, and within each row, inspect every column for missing or invalid values. This is a situation for nested loops because you're repeating one task (checking columns) inside another (moving through rows).

dataset = [
    ["HH001", 4, 25000, "Nairobi"],
    ["HH002", None, 18000, "Kisumu"],
    ["HH003", 6, None, "Mombasa"],
    ["HH004", 3, 32000, "Nakuru"]
]
for row in dataset:
    household_id = row[0]
    for value in row:
        if value is None:
            print(f"{household_id} has a missing value.")

The result:

HH002 has a missing value.
HH003 has a missing value.

2. To identify duplicate records (Data Quality)

Imagine you're cleaning survey data before analysis. Some respondents may have been entered twice. You can use nested for loop to compare every respondent against every other respondent to identify duplicate IDs.

respondent_ids = ["R001", "R002", "R003", "R002", "R004"]

for i in range(len(respondent_ids)):
    for j in range(i + 1, len(respondent_ids)):
        if respondent_ids[i] == respondent_ids[j]:
            print(f"Duplicate respondent found: {respondent_ids[i]}")

The result:

Duplicate respondent found: R001
Duplicate respondent found: R002

i and j are index variables. The outer loop i selects one respondent at a time, while the inner loop j compares that respondent with every respondent that follows in the list. This ensures every unique pair is checked once, making it possible to detect duplicate IDs.

3. To find a specific value in your table (Data exploration)

dataset = [
    [12, 25, 18],
    [42, 33, 17],
    [29, 56, 40]
]
target = 33
for row in range(len(dataset)):
    for column in range(len(dataset[row])):
        if dataset[row][column] == target:
            print(f"{target}: Found at Row {row + 1}, Column {column + 1}")

The result:

33: Found at Row 2, Column 2

Although nested for loops are the most common when working with collections of data, you can also combine for and while loops. This is useful when you need to process a collection while repeating a task until a condition is met.

Here are some examples that incorporate both the for and while loops.

4. Data Validation (for + while)

You're collecting survey data. Every respondent must provide a valid age before you move on to the next respondent.

respondents = ["HH001", "HH002", "HH003"]

for respondent in respondents:
    print(f"\nRecording data for {respondent}")

    age = int(input("Enter age: "))

    while age < 0 or age > 120:
        print("Invalid age. Please enter an age between 1 and 120.")
        age = int(input("Enter age: "))
    print("Age recorded successfully.")

The for loop processes each respondent.
The while loop keeps asking until a valid value is entered.

The result:


Recording data for HH001
0: Invalid age. Please enter an age between 1 and 120.
-2: Invalid age. Please enter an age between 1 and 120.
21: Age recorded successfully.

Recording data for HH002
68: Age recorded successfully.

Recording data for HH003
178: Invalid age. Please enter an age between 1 and 120.
89: Age recorded successfully.

Limitations of Nested Loops

Like every other tool, nested loops have situations where they're less efficient.

As the amount of data grows, the inner loop must run for every iteration of the outer loop, increasing the total number of operations and potentially slowing down your program.

Imagine comparing every household in a dataset of 10,000 records with every other household to identify duplicates. Although this approach works, it performs a very large number of comparisons. As datasets grow, there are more efficient techniques that avoid checking every possible pair.

Nested loops are an excellent way to solve problems involving multiple levels of repetition. As we continue learning Python, we'll discover more efficient approaches for handling large datasets, particularly when working with libraries such as pandas.(We will cover these in a later series)

Conclusion

In this article, we've seen that nested loops are a practical way to solve real-world problems. Whether you're validating survey responses, checking for missing values, or comparing records, nested loops help you work through data that has multiple levels or repeated relationships.

As usual, keep experimenting by creating your own examples and thinking about tasks that involve one repeated process inside another. The more you practise, the more natural it will become to recognise when a nested loop is the right solution.

What's next?

"We've learned how to solve complex problems with loops, but what if we want to reuse that logic without writing it again?

In the next article, we'll explore functions and learn how to package reusable code into named blocks, making our programs less repetitive and easier to maintain.

Until then, happy coding!

Controlling Loops with break and continue

Sharon-nyabuto — Thu, 23 Jul 2026 08:45:15 +0000

Knowing when to stop or skip a loop in Python

In our last article we saw how we can efficiently repeat processes with for and while loops.
But what if we do not want our loop going all the way? How can we tell Python to stop a loop early or skip a single iteration, without ending the entire program?

The `break` statement

Loops are important, but sometimes we do not want every iteration to execute. This is where the break statement is helpful.
The break statement immediately terminates a loop. Once Python encounters break it exits the loop and goes on to execute the rest of the program.

Syntax:

for item in sequence:
    if condition:
        break

For example:

respondents = ["R001", "R002", "R003", "R004", "R005","R006","R007","R008"]

for respondent in respondents:
    if respondent == "R005":
        print("Respondent found!")
        break
    print(f"Checking respondents : {respondent}")

Here is what happens step by step:

Python starts the for loop and assigns the first value, "R001", to the variable respondent.
It checks whether respondent == "R005", and since "R001" is not equal to "R005", the condition is False.
Python skips the break statement and executes print("Checking", respondent), displaying:
```
   Checking respondents: R001
```
The loop repeats for "R002" all through "R004", and since none of those are still ==R005, Checking respondent is still printed.
On the next iteration, respondent becomes "R005". This time, the condition respondent == "R005"evaluates to True. Python prints:
```
    Respondent found!
```
The break statement is executed, causing Python to immediately exit the loop. The remaining items, "R006" through "R008", are never processed. The output therefore becomes:
```
    Checking respondents : R001
    Checking respondents : R002
    Checking respondents : R003
    Checking respondents : R004
    Respondent found!
```

It is important to note that the break statement does not stop the entire program, it only stops the current loop. Python proceeds to execute the next tines of code after the loop.

Here are a few more examples;

Example 1:

# Q1. A school bus can carry 30 students. Students keep boarding one by one. Stop boarding as soon as the bus is full. Print how many are on board after each student boards.

capacity = 30
students = 0

while True:
    students += 1
    capacity -= 1

    print(f"Students aboard: {students} | Seats left: {capacity}")

    if capacity == 0:
        break 

# The break statement provides the loop with an exit condition by terminating it as soon as capacity reaches 0. Otherwise it would keep running infinitely beyond 0.

Result:

Students aboard: 1 | Seats left: 29
Students aboard: 2 | Seats left: 28
Students aboard: 3 | Seats left: 27
....
Students aboard: 28 | Seats left: 2
Students aboard: 29 | Seats left: 1
Students aboard: 30 | Seats left: 0

Example 2:

#Q2:  A car park has 20 spaces. Cars keep arriving one by one. Ask if a car wants to park. If yes, reduce spaces by 1. Stop when the car park is full. Show spaces remaining after each car.

spaces = 20
cars_parked = 0

while True:
    car_parking = input("Do you want to park? (yes/no): ")

    if car_parking.lower() == "yes":
        cars_parked += 1
        spaces -= 1
        print("Go ahead and park.")
        print(f"Cars parked: {cars_parked}")
        print(f"Spaces left: {spaces}\n")

        if spaces == 0:
            print("The car park is now full.")
            break
    else:
        print("Thank you. Have a good day!")
        break

These are just two examples of where break could be used. Other situations that would require break include:

Stop searching once a specific respondent ID is found.
Exit a password prompt after the correct password is entered.
Stop processing transactions once the daily limit has been reached.
End a quiz when the user chooses to quit.

The `continue` statement

We've seen that thebreakstatement terminates a loop as soon as a specified condition is met. The continue statement behaves differently. Instead of ending the loop, it skips the rest of the current iteration and immediately moves on to the next one, without terminating the loop.

Syntax:

for item in sequence:
    if condition:
        continue

To better understand the difference between break and continue, let's use the same initial example from the break section.
Notice how changing one statement will affect the behavior of the loop.

respondents = ["R001", "R002", "R003", "R004", "R005","R006","R007","R008"]

for respondent in respondents:
    if respondent == "R005":
        continue
    print(f"Checking respondents : {respondent}")

Here is what happens step by step:

Python starts the for loop and assigns the first value, "R001", to the variable respondent.
It checks whether respondent == "R005". Since "R001" is not equal to "R005", the condition evaluates to False.
Python skips the continue statement and executes:
```
print(f"Checking respondent: {respondent}")
```
displaying:
```
Checking respondent: R001
```
The same process repeats for "R002", "R003", and "R004", with each respondent being printed.
On the next iteration, respondent becomes "R005". This time, the condition respondent == "R005" evaluates to True.
Python executes the continue statement, immediately skipping the print() statement for this iteration. As a result, "R005" is NOT displayed.
Instead of ending the loop, Python moves directly to the next iteration and continues processing "R006", "R007", and "R008" until all respondents have been checked.
The output therefore becomes;
```
Checking respondents : R001
Checking respondents : R002
Checking respondents : R003
Checking respondents : R004
Checking respondents : R006
Checking respondents : R007
Checking respondents : R008
```
As you will see from the output, the continue statement skips only the current iteration, which in our case is "R005", then proceeds with the remaining iterations until there are no more items to process.

Here are a few more examples;

#Scenario 1: A customer wants to withdraw money from an ATM. If they enter an amount greater than their account balance, ask them to enter another amount instead of ending the transaction.

balance = 10000

while True:
    amount = int(input("Enter withdrawal amount: "))
    if amount > balance:
        print("Insufficient balance.\n")
        continue

    balance -= amount
    print(f"Successfully withdrawn Kshs. {amount}. New balance: Ksh {balance}")
    break

#Scenario 2: Imagine you're analysing a survey dataset containing household incomes. Respondents who didnt disclose income are marked as -1. How can we skip these missing values while continuing to analyse the rest of the dataset?
household_ids = ["HH001", "HH002", "HH003", "HH004", "HH005", "HH006", "HH007"]
household_incomes = [25000, 18000, -1, 32000, 15000, -1, 27000]

for i in range(len(household_ids)):
    if household_incomes[i] == -1:
        continue

    print(f"{household_ids[i]} | Income: Ksh {household_incomes[i]}")

These are just two examples of where continue could be used. Other situations that would require continue include:

Ignoring duplicate records when cleaning a dataset.
Skipping products that are out of stock while processing customer orders.
Ignoring files with an unsupported format when processing multiple files in a folder.
Skipping weekends and holidays when generating a work schedule or calculating business days.

Conclusion

At the beginning of the article we asked ; How can we tell Python to stop a loop early or skip a single iteration, without ending the entire program?

We've now seen how the break and continue statements give us control over loops. While break allows us to exit a loop as soon as a condition is met, continue lets us skip the current iteration and move on to the next one. Together, they help us write programs that are efficient, flexible and more managable.

As always, don't stop with the examples in this article. Modify them, experiment with different scenarios, and challenge yourself to find other situations where break and continue can simplify your code.

What's next?

So far, we've learned how to control a single loop. But what happens when one loop isn't enough?

In the next article, we'll answer that question as we explore nested loops.

Repeating Tasks Without Repeating Code

Sharon-nyabuto — Fri, 17 Jul 2026 19:38:12 +0000

Using `for` and `while` loops in Python

Now that Python can make decisions, how do we apply those decisions repeatedly?

In the last article, we learned how to guide Python through multiple levels of decision-making using nested if statements. But making decisions is only part of what makes Python pretty powerful.

Picture this: You've just landed your first data analysis task. Your dataset contains 9,000 respondents, and you need to perform the same check on every single record. After writing the same line of code a few times, and dodging errors, you stop and think: There has to be a better way than copying and pasting this thousands of times... right?

There is. It's called a loop, and by the end of this article, you'll know exactly how to use one, and get that task done in a fraction of that time.

The FOR Loop

A for loop is used to repeat a block of code a specific number of times or to iterate over a sequence, such as a list, a string, or a range of numbers.

The general syntax of a for loop looks like this:

for variable in sequence:
    # Code to repeat

Where;

for : Indicates the beginning of the loop
variable: Stores the current value during each repetition. You can name it anything meaningful (number, student, county, letter, etc.).
in: Tells Python to go through each item in the sequence.
sequence: The collection being looped over, such as a range(), list, or string.
: Marks the beginning of the loop body.
Indentation : Indicates code blocks that are in the loop. Everything indented below the for statement is repeated.

Example 1: Looping over a range

for number in range(5):
    print(number)

The Result:

What happens step by Step:

Python reads the statement range(5), which produces the numbers 0, 1, 2, 3, and 4.
The first value, 0, is assigned to the variable number.
Python executes the indented code and prints:
```
0
```
Python returns to the top of the loop, assigns the next value (1) to number, and prints it, the process continues for 2, 3, and 4.
Once there are no more values left in the range, the loop ends and the program moves to the next line of code.

A quick note about range()

You may have noticed that range(5) prints the numbers 0 to 4, not 5. That's because the value passed to range() is the stopping point, and Python does not include it. In other words, range(5) means start at 0 and stop before 5.

Example 2: Looping over a list

students = ["Faith", "Sheila", "Bryan", "Sharon"]
for student in students:
    print(student)

Example 3: Combining a for loop and conditional if statement

temperatures = [22, 35, 18, 40, 29, 15, 33]

for temperature in temperatures:
        if temperature > 30:
            print(f"Temperature is: {temperature}")
        else:
         print(f"Temperature is: {temperature} (below 30)")

Output:

Temperature is: 22 (below 30)
Temperature is: 35
Temperature is: 18 (below 30)
Temperature is: 40
Temperature is: 29 (below 30)
Temperature is: 15 (below 30)
Temperature is: 33

We've seen how for loops make it easy to repeat a block of code when we know what we're looping over, whether that's a range of numbers, a list, or another sequence. But what happens when we don't know in advance how many times a task needs to be repeated? That's where the while loop comes in.

The WHILE loop

The while loop repeatedly executes a block of code as long as a specified condition remains True. Once the condition becomes False, the loop stops.

The general syntax of a while loop looks like this:

while condition:
    # Code to repeat

Where:

while: Indicates the start of the while loop
condition: The expression that evaluates to either True or False
: Marks the beginning of the loop body.
Indented code: The block of code that is repeatedly executed for as long as the condition remains True.

Let's see how this works in practice

Example 1: printing the numbers from 1 to 5.

count = 1

while count <= 5:
    print(count)
    count += 1

The Result:

What happens step by Step:

The variable count is initialized with the value 1.
Python checks the condition count <= 5. Since 1 is less than or equal to 5, the condition is True, so the loop begins.
The statement print(count) is executed, displaying:
```
1
```
The line count += 1 increases the value of count by 1, changing it from 1 to 2.
Python returns to the top of the loop and checks the condition again. Since 2 <= 5 is still True, the loop repeats.
This process continues until count becomes 6. At that point, the condition count <= 5 evaluates to False, so the loop stops and the program moves on to the next line of code.

Why do we need count += 1/count = count + 1?

Without this line, the value of count would never change. It would remain 1, meaning the condition count <= 5 would always be True. As a result, Python would keep printing 1 forever, creating an infinite loop.

For Example:

count = 1

while count <= 5:
    print(count)

The Result:

1
1
1
1
1
...

The loop never ends because count is never updated. Since the condition always remains True, Python keeps executing the loop forever. This is called an infinite loop.
Every while loop should have a stopping condition so that it knows when to stop.

Let's do a few more examples that demonstrate how while loops are commonly used.

#Q1: You have a starting balance of 5k, and you are withdrawing 1000. So you can keep withdrawing as long as the balance is greater than withdrawal

mpesa_balance = 5000
withdrawn_amount = 1000
while mpesa_balance >= withdrawn_amount:
    new_balance = mpesa_balance - withdrawn_amount
    print(f"Request successful.Your new Balance is: Kshs: {new_balance}")
    mpesa_balance -= 1000
print(f"You have insufficient funds. Your Balance is: Ksks: {new_balance}")

# Q2. A savings account starts with Ksh 1,000. It earns 10% interest every month. How many months does it take to reach Ksh 2,000? Print the balance each month.
start_amount = 1000
interest = start_amount * 0.1
month = 0

while start_amount <=2000:
    interest = start_amount * 0.1
    start_amount = interest + start_amount
    month += 1
    print (f"After {month} months, your interest is {interest}. The account balance is {start_amount}")

Conclusion

At the beginning of this article, we imagined having to process thousands of survey responses without writing the same code over and over again. As we've seen, loops provide a much more efficient solution.
Whether you use a for loop when you know what you're iterating over, or awhile loop when repetition depends on a condition, both allow Python to automate repetitive tasks efficiently and with far less effort and error than doing them manually.

As you continue learning, try modifying the examples in this article and create a few of your own. Remember, the more you practise, the more natural it will become to recognise when a loop is the right tool for the job.

Up Next...

We've learned how to repeat a single task. So far, our loops have executed until they naturally reached the end. In the next article, we'll learn how to stop a loop early or skip specific iterations using the break and continue statements. I look forward to sharing it with you!

Making Decisions Within Decisions in Python

Sharon-nyabuto — Wed, 15 Jul 2026 06:11:48 +0000

Taking Conditional Logic Further with Nested if Statements

Previously, we learnt how to use if, elif, and else to make decisions in Python. Those allowed our programs to choose between different paths depending on whether a condition evaluated to True or False.

Most of the real world decisions depend on more than one condition.

Think about logging into your bank account to withdraw money. Entering the correct PIN is only the first step. Once you're in, the system still needs to check whether you have sufficient funds before approving the withdrawal.
How do we build that kind of layered decision-making in Python?
That's exactly what we'll explore in this article as we learn about nested if statements.

Nested `if` Statements

A nested if statement is simply an if statement placed inside another if statement. It allows a program to evaluate another condition only after the first condition has been met.

Syntax

if condition_1:
    if condition_2:
        # Code to execute

The inner if statement is only evaluated if the outer if statement is True.

Example:

Suppose you're analysing survey responses for a scholarship programme. Applicants must first be at least 18 years old. If they meet that requirement, then you can check whether their household income qualifies them for financial assistance.

age = 21
monthly_income = 18000

if age >= 18:
    if monthly_income < 30000:
        print("Eligible for the scholarship.")
    else:
        print("Age requirement met, but income is above the eligibility threshold.")
else:
    print("Applicant does not meet the minimum age requirement.")

Here's what happens step by step:

Python stores the value 21 in the variable age and 18000 in the variable monthly_income.
It evaluates the first condition: age >= 18.
Since 21 is greater than or equal to 18, the first condition evaluates to True.
Because the first condition is True, Python moves to the nested if statement and evaluates the second condition: monthly_income < 30000.
Since 18000 is less than 30000, the second condition also evaluates to True.
Python executes the nested if block and prints:

  Eligible for the scholarship.

Because both conditions were satisfied, the two else blocks are skipped and the program ends.

Notice the order of the checks. Python only evaluates the applicant's income after confirming they meet the minimum age requirement.
If the applicant were under 18, the income would never be checked because the program would immediately execute the outer else block. This is what makes nested ifstatements useful; they allow one decision to depend on the outcome of another.

Here are a few other examples, that demonstrate how multiple conditions can be evaluated one after another.

# Scenario 1: Job Application Screener

# Check whether the applicant has tertiary education. If they do, ask for their years of experience. 
# If they have at least 3 years of experience, ask for their expected salary. If the salary expectation is Ksh 80,000 or less, shortlist them for an interview; otherwise, indicate that their salary expectation is too high. 
# If they have fewer than 3 years of experience, print "Minimum 3 years of experience required." If they do not have tertiary education, print "Higher education is required for this position."

tertiary_education= input("Do you have a diploma, degree or higher?").lower()

if degree == "yes":
    experience = int(input("How many years of experince"))
    if experience >= 3:
        salary = float(input("Enter your expected salary"))
        if salary <= 80000:
            print("Shortlisted for interview")
        else:
            print("Salary expectation too high")
    else:
        print("Minimum 3 years experience required")
else:
    print("Higher education is required for this position")

# Scenario 2: Mobile Money Transfer Validator

# Write a program to validate a mobile money transfer. 
# If the user has an account, check that they have sufficient funds and that the transfer amount does not exceed the daily limit of Ksh 150,000. 
# If the transfer is successful, display the amount sent and the remaining balance. If the user does not have an account, print "Please register first."

mobile_checker = input("Do they have an active account? (Yes/No)")

if mobile_checker == "yes":
    acc_balance = float(input("What is your account balance?"))
    if acc_balance > 0:
        amount_to_send = float(input("How much do you want to send??"))
        if amount_to_send <= acc_balance:
            new_balance = acc_balance - amount_to_send
            if amount_to_send > 150000:
                print("Amount exceeded daily limit")
            else:
                print("Successful transfer")
                print(f"Amount sent: Ksh., {amount_to_send}, Your account balance is: Kshs., {new_balance}")
        else:
            print(f"Insufficient funds: Kshs., {acc_balance}")
    else:
        print("You have a low balance")
else:
    print("Please register first.")

# Scenario 3: School Report Card Generator

# Write a program that generates a student's report card. Ask for the student's name and marks (out of 100) in Mathematics, English, Kiswahili, Science, and Social Studies. For each subject, assign a grade and remark using the grading scale provided. Then calculate the total marks (out of 500), the average score, and the overall grade based on the average using the same grading scale.
# Next, determine the student's promotion status: students with an average of 75 or above are promoted, those with an average between 50 and 74 are promoted but advised to work harder, while those below 50 should repeat the class.
# Finally, check whether the student received a grade E in any subject. If so, display a warning indicating that remedial lessons are required for the relevant subject(s). 

# Print a neatly formatted report showing the student's name, subject grades, total marks, average, overall grade, promotion status and any remedial warning.

subjects = ["Maths", "English", "Kiswahili", "Science", "Social Studies"]
student_name = input("What is your name?")
print(f"{student_name} : Performance Summary")

print("==================================")
total_marks = 0
failed = ""
for subject in subjects:
    marks = int(input(f"What was your score in {subject}"))
    if 75<= marks <= 100:
        grade = "  A  - Excellent"
    elif 60<= marks <= 74:
        grade = "  B  - Very Good"
    elif 50<= marks <= 59:
        grade = "  C  - Good"
    elif 40<= marks <= 49:
        grade = "  D  - Pass"
    else:
        grade = "  E  - Need Improvement"
    total_marks += marks
    print(f"{subject} | Score: {marks} | Grade:{grade}")
    if grade == "  E  - Need Improvement":
        failed += subject + " "  
print(f"Total marks: {total_marks} / 500")
if failed: 
    print(f"NOTE: Remedial lessons required in {failed}")
    print()

average = total_marks / len(subjects)
print(f"Average Score: {average}")
if average >= 75:
    print("PROMOTED to next class")
elif 50 <= average <= 74:
    print("PROMOTED -  work harder next term")
else: 
    print("REPEAT - see your class teacher")

Result:

Mary Nancy : Performance Summary
==================================

Maths          | Score: 43 | Grade:  D  - Pass
English        | Score: 23 | Grade:  E  - Need Improvement
Kiswahili      | Score: 56 | Grade:  C  - Good
Science        | Score: 89 | Grade:  A  - Excellent
Social Studies | Score: 67 | Grade:  B  - Very Good
Total marks: 278 / 500

NOTE: Remedial lessons required in English 

Average Score: 55.6
PROMOTED - Work harder next term

Don't stop with the examples in this article. Modify them, build your own, and experiment with different scenarios, because the best way to master programming is by writing and testing your own code.
As you continue practicing, you may find yourself using nested conditionals whenever a problem requires multiple levels of decision-making.

Bonus Tip: f-Strings

Before we wrap up, there's one small feature you've probably noticed throughout the examples in this article. Many of the print() statements begin with the letter f, like this;

print(f"{subject} | Score: {marks} | Grade:{grade}")

The f stands for formatted string (or f-string). It allows you to insert the value of a variable directly into a string by placing it inside curly braces {}.

Without an f-string, you would have to write:

print(subject, " | Score: ", marks, " | Grade: ", grade)

While both approaches work, f-strings are easier to read and become especially useful as your programs grow longer and your output includes multiple variables.

As we continue through this series, we'll use f-strings regularly to produce cleaner and more readable output.

Teaching Python to Make Decisions

Sharon-nyabuto — Mon, 13 Jul 2026 21:11:20 +0000

Understanding if, elif, and else

In the previous article, we explored what Python is and why it's such a valuable tool for data analysts. We also wrote a few simple programs that followed instructions exactly as we wrote them.

In this article, we will explore how to teach Python to make decisions using conditional statements. By the end, you'll understand how to use if, elif, and else to control the flow of your programs and hopefully solve simple real-world problems.

So what is a conditional statement?

We make decisions all the time in our lives. If it rains, we carry an umbrella. If we're hungry, we eat. If an assignment is due tomorrow, we probably shouldn't leave it until midnight. Programs need to make decisions too, that is what conditional statements are for.

Conditional Statements allow a program to evaluate conditions and choose what to do based on whether those conditions are True or False.
The three keywords you'll most commonly use when writing conditional statements are if, elif, and else.

Comparison Operators

Before we can make decisions in Python, we need a way to compare values.
Comparison operators evaluate two values and return either True or False, which are the outcomes that drive if, elif, and else statements. Common comparison operators are:

Operator	Meaning
`==`	Equal to
`!=`	Not equal to
`>`	Greater than
`<`	Less than
`>=`	Greater than or equal to
`<=`	Less than or equal to

Example:


age = 25

print(age == 25)   # equal to        -> True
print(age != 18)   # not equal to    -> True
print(age > 18)    # greater than    -> True
print(age < 18)    # less than       -> False
print(age >= 25)   # greater or equal -> True
print(age <= 20)   # less or equal    -> False

Note: The = operator assigns a value to a variable, (like age = 25), while == checks whether two values are equal. This is one of the most common mix-ups I had when I started out.

The IF statement

The if statement is what lets your code actually make a decision : It runs a block of code only if a condition is True.

Example:

age = 25
if age >= 18:
    print("You're an adult.")

Here's what happens step by step:

age = 25 - Python stores the value 25 in the variable age.
age >= 18 - It evaluates the condition.
Since 25 is greater than 18 the condition is True
The indented line is executed, and the output prints:

You're an adult.

The colon : shows the end of the condition, and indicates everything after is what needs to be done if the condition is met.

The indentation (usually 4 spaces) is how Python knows which lines of code belong inside the if statement.

The ELSE statement

In an if statement, you specify what needs to be done if the condition is True.
You can also tell Python what to do if the condition is not met, using else. The statement then becomes;

Example:

score = 68
if score >= 70:
    print("You have passed")
else:  
    print("Please retake the test")

Here's what happens step by step:

Python stores the value 68 in the variable score.
It evaluates the condition score >= 70.
Since 68 is less than 70, the condition evaluates to False.
Because the if condition is False, Python skips the if block.
Python then executes the else block and prints:

Please retake the test

The else block acts as a fallback, and is only executed when the condition in the if statement evaluates to False.

The if-else statement is always used when you have two possible outcomes.

The ELIF statement

Suppose there are more than two possible outcomes?
The if-else statements will not sufficiently represent all of them. This is where the elif statement becomes useful

elif ("else if") lets you check additional conditions in order.

Example:

age = 34

if age >= 40:
    print("Older Adults")
elif age >= 20:
    print("Youth")
else:
    print("Teens")

Here's what happens step by step:

Python stores the value 34 in the variable age.
It evaluates the first condition: age >= 40.
Since 34 is less than 40, the first condition evaluates to False, so Python moves to the next condition.
Python evaluates the elif condition: age >= 20.
Since 34 is greater than 20, the elif condition evaluates to True.
Python executes the elif block and prints:

Youth

Python then skips the else block and the conditional statement ends.

Think of elif as Python saying, "If the first condition wasn't true, check this one instead."
The conditions are evaluated from top to bottom and only the first condition that evaluates to True is executed.

Putting Conditionals into Practice

Now that we've covered the basics of if, elif, and else, it's time to see how they work together.
The following examples are a little more practical, and demonstrate how Python can make decisions based on multiple conditions.

Examples

# Q1. A cinema allows entry if: age >= 18 AND has_ticket == True. Write the condition and test it with different values.

age = int(input("How old are you?")) # Ask for age
has_ticket = True

if age >= 18 and has_ticket == True:
    print("Welcome to the Movies!")
else: 
    print("Restricted")

# Q2. Build an electricity bill calculator. Units used: 0–50 → Ksh 12/unit, 51–200 → Ksh 15/unit, 201+ → Ksh 18/unit. Ask for units used and print the total bill.

electricity_units = int(input("How many units have you used?"))
print(electricity_units)
if electricity_units <= 50:
    print("Your bill is ",electricity_units * 12)
elif electricity_units <= 200:
      print("Your bill is ",electricity_units * 15)
else:
     print("Your bill is ",electricity_units * 18)

# Q3. Ask for a student's marks (0–100). Print their KCSE grade: A (75+), B (60–74), C (50–59), D (40–49), E (below 40).
student_marks = int(input("What were your marks?"))
if student_marks >= 75:
    print("A")
elif student_marks >= 60:
    print("B")
elif student_marks >= 50:
    print("C")
elif student_marks >= 40:
    print("D")
else:
    print("E")

Don't worry about counting your elif statements! You can have as many as your program needs, as you can see in the last example. Python simply checks each condition in order and stops as soon as it finds one that's True.

Your turn, try it yourself!

The examples in this article are just a starting point. Experiment with different values, create your own conditions, and see how changing the logic affects the output. Programming is one of those skills that's learned best by doing.

What's Next?

We've now learned how to make Python choose between different paths. Next, we'll build on that foundation with nested if statements, where one condition is evaluated inside another. It's a small step in syntax, but a big step towards writing more realistic programs.

Thanks for reading, and I hope you'll join me for the next part of the series. Until then, happy coding!

Python for Data Analysts: Getting Started

Sharon-nyabuto — Sun, 12 Jul 2026 14:44:43 +0000

Understanding what Python is, why it matters, and the concepts every beginner will encounter.

A month ago, if you'd asked whether I could write Python, the answer would have been no, simply because Python just looked intimidating.

As a research and data analyst however, I do love a challenge, and I asked myself the practical question: How could I actually use Python in my day-to-day work?
A dive into Python revealed one certainty: Python is not easy, but it's nowhere near as intimidating as I'd built it up to be. This is a look back at my first week learning Python: the assumptions I walked in with, and how quickly most of them fell apart.

So what is Python and why does it matter in Data analysis?

Python is a programming language: a way of giving a computer clear, step-by-step instructions to perform tasks. The tasks can be as simple as basic calculations or as involved as cleaning data, analysing thousands of data entries, automating repetitive tasks, or building applications.

One of the reasons Python is so popular among both beginners and data analysts is its readable syntax. It often resembles plain English, making it easier to learn while remaining powerful enough for real-world data analysis.

For example, one of the first programs most people write looks like this:

print("Hello, World!")

It simply tells Python to display Hello, World! on the screen. It's a tiny program, but it marks the beginning of almost every programmer's journey, including mine.

Why does Python matter?

If you've spent enough time working with spreadsheets, you've probably thought, "There has to be an easier way to do this." Whether it's cleaning data, repeating the same steps every week, or working with increasingly large datasets, there comes a point where spreadsheets begin to show their limits. That's where Python comes in.

In data analytics, Python is particularly useful for:

Automating repetitive tasks
- Tasks you'd normally repeat manually (cleaning files, renaming columns, merging spreadsheets) can be scripted once and reused.
Scale
- As datasets grow, spreadsheets can become slow, cumbersome, or even unresponsive. Python is designed to handle much larger amounts of data efficiently. We'll look at the tools that make this possible including Python libraries, in a later article.
Reproducibility
- Every step of your analysis is written into a script. That means you can run the same code tomorrow, next month, or even next year and reproduce the same results, without having to memorize or manually repeat every step.
Collaboration
- Because each step of the analysis is written into the script, colleagues can review, understand, and build on your work without having to guess what was done. This makes teams work seamlessly on the same project.
Flexibility in connectivity
- Python can pull data directly from a database, an API, or a folder of raw files, instead of relying on someone exporting a CSV for you.
Specialized Tools
- One of Python's greatest strengths is its ecosystem of libraries. Libraries are tools created by the Python community that extend what Python can do, without you having to build everything from scratch. (We'll introduce some of them as we progress through the series).

Learning why Python matters gave me the motivation to begin. Learning how to use it, however, came with a few surprises. Looking back, many of the things that intimidated me at first weren't really difficult, but simply unfamiliar.

Common Terms You'll Encounter in Python - Getting familiar with the Python Environment

Far from jumping into data analysis, my first few days were spent learning the basic concepts and getting familiar with common terms, most of which had names that were new to me.

Here are five that stood out to me when I was getting started;

Terminal: I assumed one wrong command could break my computer, only to realize it's really just another way of talking to your machine, and it stops feeling like a 'black box' after a handful of basic commands.
Integrated Development Environment/IDE: This is where you actually write your code. Think of it as a text editor built specifically for writing and running programs, with extra features like error highlighting, autocomplete, and a built-in terminal. VS Code is one of the most popular choices.
Script: This is the file containing Python code, usually with a .py extension. Instead of typing commands one at a time into the terminal, you write them all into a script and run the whole thing at once.
Library: Pre-written code that someone else has already built, so you don't have to write everything from scratch. Need to work with dates, analyse data, or make charts? There's very likely a library for that — you just import it into your script and use it. Pandas is one of the most common ones for data analysis.
Indentation: Indentation isn't cosmetic in Python, it's part of the syntax, defining which lines belong together. It took me a couple of errors and examples before fully comprehending, but once that clicked, it stopped feeling confusing.

Early on, I felt like I had to fully understand every new term, variables, functions, loops, modules, packages, libraries, before I could write code. Turns out you only need to understand the concept you're using today. The rest will fall into place as you keep writing code.

My first few lines of Python

A variable is just a name that stores information:

name = "Sharon"
print(name)

name , nationality, city = "Sharon", "Kenyan", "Kisumu" 
print("My name is ",name,". I am a ",nationality," and I live in ",city)

Python can do calculations for you:

income = 25000
expenses = 18000
print(income - expenses)

Or take input from the user:

name = input("What is your name? ")
print("Welcome, ", name)

While these may not be impressive on their own, together they show programming isn't about writing something complex from day one, but about stacking small blocks to solve a bigger problem.

Biggest lessons so far

Progress comes from consistent practice.
Errors are part of the workflow, not a verdict on your ability.
Typing code yourself teaches more than copy-pasting it.
Small projects teach more than another hour of tutorials.
Looking things up is normal, sometimes even the best analysts do it.

Programming isn't about knowing every answer. It's about knowing how to find one. This is the first article in my Python learning series, documenting the process as I go.

In the next article, we'll look at how Python uses if statements nested if statements to make choices based on different conditions. This i think is one of the biggest steps towards writing programs that actually think before they act.

Final thoughts

If you're on the fence, don't wait until you feel "ready." Write one line of code and trust that understanding builds with reps.

I'm still learning too, and that's exactly what this series is for.

Cleaning Messy Phone Numbers in PostgreSQL Using REGEXP_REPLACE

Sharon-nyabuto — Mon, 18 May 2026 22:34:00 +0000

Introduction

The first thing anyone that has worked with real world data will tell you is that rarely does data come in the clean, uniform format you expect. Phone numbers are one of the biggest culprits.

One person enters +254712345678, another writes 0712345678, someone else throws in 254-712-345-678, another (254)-712345678 and another 254 712 345 678. Before you know it, your database has a different version of phone numbers in every row.

In this article, we will explore how to fix that, using a powerful PostgreSQL function called regexp_replace.

By the end, you will understand what it does, how to build the pattern, and how to apply it to actually clean your data.

First, What Even Is `regexp_replace`?

First things first, we need to understand what this function is and what it does.

regexp_replace is a PostgreSQL function that finds a pattern inside a string and replaces whatever matches that pattern with something else you specify. The word regexp is short for regular expression.

The syntax looks like this:

regexp_replace(source, pattern, replacement_string, flag)

Let us break each part down simply:

Part	What It Means
`source`	The string you are searching inside. In our case, this is the column `passenger_phone`
`pattern`	What you are looking for : What do you want to find and remove or replace?
`replacement_string`	What do you want to put in place of what you found? If you just want to delete it, you pass an empty string `''`
`flag`	Controls how the matching behaves. The two you will use most are `i` for case-insensitive matching and `g` for global, meaning it applies the replacement to everything it finds in the string, not just the first match

Now that we have the hang of it, let us move on to building the pattern.

Understanding the Special Characters in Patterns

A pattern is basically a set of rules that tells PostgreSQL what to look for. It is like saying "find any character that is not a digit" or "find this pattern, but only at the beginning" etc.

To write these rules/instructions, there are special characters that are used.

Here are the ones we will use today:

The `^` character

Depending on where it is placed, this will do either of 2 things;

When ^ is at the beginning of a pattern it means: start matching from the very beginning of the string.

^'hello'

This means: only match hello if it appears at the start of the string.

^[0-9]

This means: Only the first character of a string is checked and matched if it is a digit. Anything after that is ignored.

When ^ is inside square brackets [ ], it means: NOT these characters.

[^0-9]

This means: Every character in the string is checked, any one that is not a digit gets matched.

Same symbol, two different purposes depending on its position.

The `\` character (backslash)

The backslash is used to tell PostgreSQL: treat the next character as a character, not as a special operator.

For example, the + sign in regular expressions has a special meaning (we will cover it in a moment). So if you want to find a plus sign + in your data, like +254, you write it as \+ to tell PostgreSQL "I mean the actual plus sign, not the operator."

Square brackets `[ ]`

Square brackets let you define a group of characters to match. For example:

[0-9] means match any digit from 0 to 9
[a-z] means match any lowercase letter
[^0-9] means match anything that is NOT a digit (the ^ inside means NOT, as we've mentioned above)

The `+` operator

When used outside brackets, + means: keep matching one or more of the preceding character or group.

So [0-9]+ means: Match the digits as long as they are all next to each other with no other characters in between. (Without the + , it stops after the first match)

The `$` character

Just like ^ marks the start of a string, $ marks the end. When you write $ at the end of your pattern, you are saying: the string must end here, nothing else allowed after this.

Putting them together: `^[0-9]+$`

Now that you know each piece, read this pattern again:

^[0-9]+$

^ → start from the beginning of the string
[0-9] → the characters must be digits
+ → keep going, there can be one or more of them
$ → and this must be the end of the string, nothing else after

All together: the entire string, from start to finish, must contain only digits.

This is the pattern we will use to identify which phone numbers are already clean and which ones need fixing.

Our Problem: What Does the Messy Data Look Like?

Let us say when we run this:

SELECT passenger_phone FROM dirty_safari_data;

We get back a mix of things like:

+254712345678
254712345678
0712345678
0712-345-678
+254 712 345 678
(0) 712345678

The goal is to standardize all of these into the local format starting with 0, containing only digits, like:

0712345678

Two problems to solve:

Some numbers start with +254 or 254 instead of 0
Some numbers have extra characters like brackets, spaces, dashes, or plus signs

Step 1: Replace `+254` or `254` at the Start With `0`

The first thing we want to do is find any phone number that starts with +254 or 254 and swap that prefix out for 0.

SELECT regexp_replace(passenger_phone, '^(\+254|254)', '0', 'g')
FROM contacts;

Let us read the pattern ^(\+254|254) carefully:

^ → look at the start of the string only
( ) → group what is inside together
\+254 → literally the characters +254 (the backslash makes + a literal character)
| → OR
254 → literally the characters 254

So the full pattern says: at the start of the string, find either +254 or 254.

The replacement is '0', so wherever that prefix is found, replace it with 0.

The flag is 'g' for global.

After this step, +254712345678 becomes 0712345678 and 254712345678 also becomes 0712345678.

Step 2: Remove Any Character That Is Not a Digit

Some numbers still have spaces, dashes, or other characters sitting in them. The second regexp_replace cleans all of that up.

SELECT regexp_replace(passenger_phone, '[^0-9]', '', 'g')
FROM contacts;

The pattern [^0-9] means: any character that is NOT a digit.

The replacement is '' (an empty string), so we are just deleting those characters.

The 'g' flag means do this for every non-digit character found in the string, not just the first one.

So 0712-345-678 becomes 0712345678 and 0712 345 678 also becomes 0712345678.

Step 3: The `WHERE` Clause — Only Update What Needs Fixing

We do not want to touch phone numbers that are already clean. That is where our ^[0-9]+$ pattern comes in handy. We use it in the WHERE clause to filter for records where the phone number is not already a clean string of digits.

You might be tempted to write it like this:

WHERE passenger_phone != '^[0-9]+$'

But this is not correct. The != operator just compares strings literally, so this would check if the phone number is literally equal to the text ^[0-9]+$, which is not what we want.

In PostgreSQL, to check whether a string does NOT match a regular expression, you use the !~ operator. The corrected version should be:

WHERE passenger_phone !~ '^[0-9]+$'

This now correctly says: only return (or update) rows where the phone number does not match the pattern of being purely digits from start to finish.

Putting It All Together

The SELECT query (to preview your cleaned data before changing anything)

Always preview before you update. This is a good habit.

SELECT 
    passenger_phone AS original,
    regexp_replace(
        regexp_replace(passenger_phone, '^(\+254|254)', '0', 'g'),
        '[^0-9]', '', 'g'
    ) AS cleaned_phone
FROM contacts
WHERE passenger_phone !~ '^[0-9]+$';

Notice how we nested one regexp_replace inside another. The inner one runs first (fixing the prefix), and then the outer one runs on the result (removing non-digits). It is like running two cleaning steps in one go.

The UPDATE query (to actually apply the changes)

Once you are happy with the preview, run the update:

UPDATE contacts
SET passenger_phone = regexp_replace(
    regexp_replace(passenger_phone, '^(\+254|254)', '0', 'g'),
    '[^0-9]', '', 'g'
)
WHERE passenger_phone !~ '^[0-9]+$';

This updates only the rows that have messy phone numbers, leaving the already clean ones untouched.

Quick Recap

Here is a summary of everything covered:

Pattern	What It Does
`^` at the start of a pattern	Anchors the match to the start of the string
`^` inside `[ ]`	Means NOT — match anything except what follows
`\+`	Treat `+` as a literal plus sign, not an operator
`[0-9]`	Match any digit from 0 to 9
`[^0-9]`	Match any character that is NOT a digit
`+`	One or more of the preceding character/group
`$`	Anchors the match to the end of the string
`^[0-9]+$`	The entire string must be digits only, nothing else
`g` flag	Apply the replacement globally (every match, not just the first)

Final Thoughts

Regular expressions can look intimidating and confusing at first, but once you understand what each character means and why it is there, they start making a lot of sense. The key takeaway here is to build your pattern step by step, rather than trying to write the whole thing at once.

Also, always test with a SELECT before running an UPDATE. You can never be too careful when modifying data directly in a table.

SQL Subqueries vs. CTEs: A Guide to Writing Better Queries

Sharon-nyabuto — Tue, 21 Apr 2026 19:54:31 +0000

When you first learn SQL, queries are usually straightforward: you SELECT some columns from a table and apply a WHERE filter to narrow down the results.

As your data questions become more complex, however, you realize sometimes you need the answer to one question before you can answer another.
For example: "Which employees earn above the company average?", requires calculating the average salary first, then use the result to filter employees.

To solve this, SQL provides two incredibly powerful tools: Subqueries and Common Table Expressions (CTEs).

This guide will break down these tools and cover:

Part 1: Subqueries
- What subqueries are and how they work
- The different types of subqueries
- When to use subqueries
Part 2: CTEs (Common Table Expressions)
- What CTEs are and why they matter
- The different types of CTEs
- When to use CTEs
Part 3: A clear comparison of Subqueries vs. CTEs

PART 1: SUBQUERIES

Also known as an inner query or a nested query, a subquery is a query embedded within another SQL query.

Subqueries are typically found nested inside standard SQL clauses such as SELECT, FROM, WHERE, or HAVING.

SQL follows a strict order of execution:

The innermost subquery is always executed first. Its result is then passed on to be used by the outer query (the containing query).

Subqueries allow you to use a single query rather than multiple queries, making your code more flexible and dynamic. They are categorized either by their output or by execution.

Subqueries Categorized by Output

One way to understand subqueries is by looking at the result they return.
A subquery can return a single value, a list of values, or an entire table, depending on the query.

Scalar Subquery

Returns exactly one value (one row, one column).

Scalar subqueries are used with single value comparison operators; =, <, >, <= & >=

For example: "Which students scored above the class average on the math test?" requires a scalar subquery.

(Assuming the class average is 80.)

SELECT student_name, score
FROM students
WHERE score > (SELECT AVG(score)
               FROM students);

Result:

student_name	score
Awino Judy	95
Wanjiku Stacy	88
Kiprop Bryan	82

Note: The scalar subquery SELECT AVG(score) FROM students returns a single value (80), then the outer query uses this value to filter students who scored above the class average

Multi-row Subquery (List Subquery)

Returns a column of values, i.e. multiple rows but one column.
It is often used with IN and NOT IN operators to filter results against a list of values.

For example: "Which students registered for the math exam?" requires a multi-row subquery

SELECT student_id,student_name
FROM students
WHERE student_id 
IN (SELECT student_id 
  FROM exam_registrations 
  WHERE exam_subject = 'Math');

Result:

student_id	student_name
1	Wanjiku Stacy
2	Kiprop Bryan
3	Awino Judy
4	Mutua Henry

The list subquery first generates a list of student IDs from the exam_registrations table, then the outer query uses IN to filter the students table against that list.

Table Subquery (Derived Table)

Returns multiple rows and columns, acting like a temporary table.
This subquery is used in the FROM clause, and must always have an alias.

For example: "Which students scored more than 160 total points in all 3 math tests?" requires a table subquery.

SELECT student_name, total_score
FROM 
(SELECT student_name, 
SUM(score) AS total_score
  FROM exam_results
  GROUP BY student_name) AS student_totals
WHERE total_score > 160;

Result;

student_name	total_score
Awino Nancy	185
Wanjiku Stacy	170
Kiprop Bryan	165

The table subquery ran first to calculate everyone's total score, creating a temporary virtual table which we aliased (student_totals). The outer query used this table to filter the results to get the students with more than 160 marks.

Subqueries Categorized by Execution

Subqueries can also be categorized based on how they work with the rest of the query.

Correlated Subquery

A correlated subquery is dependent on data from the outer query.
It runs for every row in the outer query, using values from the outer query to filter its result.

Uncorrelated (Independent) Subquery

An uncorrelated subquery runs once, and is fully independent of the outer query.
This means that it evaluates its logic exactly once, gets its result, and simply hands that result over to the main query to complete the task.

If you're ever uncertain as to whether a subquery is correlated (dependent) or uncorrelated (independent), you can use the highlight test on your SQL editor.

Highlight just the subquery and run it:

If it returns a result: It is an uncorrelated subquery. It can stand on its own.

If it throws an error: It is a correlated subquery. It depends on information from the outer query to make sense and run correctly.

When to use Subqueries

Subqueries are useful when;

Calculating metrics like averages or totals on the fly, without hardcoding numbers.
Filtering results using a dynamically generated list of values i.e. (List subqueries).
Using row-specific logic
There's need to aggregate data before filtering.
Working with older legacy databases that don't support more modern features.

Subqueries are excellent for simple logic, but when they are deeply nested, they become difficult to read and more difficult to maintain. This is where Common Table Expressions (CTEs) come in.

PART 2: The CTE (COMMON TABLE EXPRESSION)

A CTE is a temporary, named result set defined at the top of a query using the WITH clause, that exists only for the duration of your query.

Once it is defined, you can query and reference the CTE as you would a normal table in your database.

Because a CTE is defined at the top of your script, it allows you to read the code logically from top-to-bottom

The Basic Syntax of a CTE

No matter how complex a query gets, almost every CTE follows this simple, two-part structure:

Define the CTE using the WITH clause at the top.
Use the CTE in your main query.

Syntax;

WITH cte_name AS (
    -- Step 1: Write your query here. 
    -- This creates your temporary, virtual table.
    SELECT column1, column2
    FROM table1
    WHERE condition
)
-- Step 2: Write your main query here, reading from the CTE!
SELECT column1
FROM cte_name;

Now that we know the basic syntax, we can dive into the two types of CTEs commonly encountered: Recursive and Non-Recursive (based on whether or not they reference themselves).

Non-Recursive CTEs
They are temporary result sets that run once and don’t reference themselves.
They’re commonly used for breaking complex queries into modular blocks.

The CTE syntax is;

WITH cte_name AS (
  -- Your SQL logic here (SELECT, JOIN, GROUP BY, etc.)
)
SELECT * FROM cte_name;

Use Case: Replacing nested subqueries for better readability and reusability.

Earlier when we wanted to find students who scored a total of more than 160 points across all their maths tests, We solved it using a Table Subquery in the FROM clause.

Let's try the exact same question, using a CTE.
"Which students scored more than 160 total points in all 3 math tests?"

WITH student_totals AS (
  SELECT student_name, SUM(score) AS total_score
  FROM exam_scores
  GROUP BY student_name
)
SELECT student_name, total_score
FROM student_totals
WHERE total_score > 160;

Result;

student_name	total_score
Awino Nancy	185
Wanjiku Stacy	170
Kiprop Bryan	165

This CTE achieves the same result as our Table Subquery Example, in a cleaner, more readable way.

Recursive CTEs

Recursive CTEs are temporary result sets that reference themselves to explore and map out hierarchical (tree-like) data (e.g., organizational charts, category trees).
Syntax;

WITH RECURSIVE cte_name AS (
  -- Base case: Start with the root of the hierarchy
  SELECT column1, column2 FROM table WHERE parent_id IS NULL
  UNION ALL
  -- Recursive case: Reference the CTE itself to traverse child rows
  SELECT t.column1, t.column2 FROM table t
  JOIN cte_name ON t.parent_id = cte_name.id
)
SELECT * FROM cte_name;

Use Case: Exploring hierarchical or tree structured data (e.g., reporting structures or category hierarchies).

For example: "Alice is a manager at a Nairobi tech startup. Who are the employees in her team hierarchy?" requires a recursive CTE.

WITH RECURSIVE team_hierarchy AS (
  SELECT employee_id, name, manager_id FROM employees WHERE name = 'Alice'
  UNION ALL
  SELECT e.employee_id, e.name, e.manager_id FROM employees e
  JOIN team_hierarchy th 
ON e.manager_id = th.employee_id
)
SELECT * FROM team_hierarchy;

Result;

employee_id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	Diana	2
5	Eve	2

This result shows Alice, her direct reports (Bob, Charlie), and Bob’s direct reports (Diana, Eve)—the full team hierarchy.
The line JOIN team_hierarchy th ON e.manager_id = th.employee_id is where the recursion happens.
team_hierarchy is the CTE itself, we are joining the query to its own result. It takes every employee already in the CTE (e.g., Alice) and finds all employees who report to them. This repeats in a loop until no more reports are found.

When to use CTEs

Subqueries are useful when;

A query has multiple logical steps
You need to use the same data multiple times
Querying hierarchical data
You temporarily want to replace a view.
When replacing nested subqueries.

PART 3: SUBQUERIES VS CTEs

Here is a table comparison of subqueries and CTEs, based on architecture, readability and performance.

Feature	Subqueries	CTEs
Readability & Flow	Reads inside-out. Prone to creating "spaghetti code" if nested multiple times.	Reads top-to-bottom. Keeps code clean and highly modular.
Reusability	Single-use only. If you need the same data twice, you have to type the whole subquery again.	Highly reusable. Can be referenced multiple times in the main query
Recursion	Cannot handle self-referencing or hierarchical data.	Perfect for hierarchical/tree-structured data using Recursive CTEs.
Performance	Fast for basic filters. Correlated Subqueries can be slow on large datasets because they loop row-by-row.	Generally the same performance (optimizers treat standard CTEs and subqueries equally). However, CTEs can be temporarily cached in memory in databases like PostgreSQL for speed boosts.
Best Use Case	Quick, single-step math (Scalar), checking lists (`IN` / `EXISTS`), or creating simple derived tables.	Multi-step data transformations, chaining queries, building org charts, or acting as temporary views.

SQL is not just about writing queries, it’s about writing queries that others can easily understand.

When in doubt, choose the option that is easiest for the next developer to understand.

If you only remember one thing from this guide:

Use Subqueries for quick, single-step tasks and simple filters.
Use CTEs for multi-step queries, reusability, and keeping your code readable from top to bottom.

SQL is all about practice. Don’t get discouraged if subqueries and CTEs take a few tries to master, with consistent practice, they get and feel more intuitive.

If you found this guide helpful, don't forget to save it for later.

Do you prefer using subqueries or CTEs in your work?
Let me know in the comments below. Happy querying!

📘SQL JOINs Explained Simply - A Beginner's Guide

Sharon-nyabuto — Sun, 19 Apr 2026 03:47:31 +0000

Introduction

SQL JOINs are used to combine data from two or more tables into a single result based on a shared column.

They allow you to retrieve related data that is stored separately.
For example, a database might have customer names in one table and purchase details in another.

A Join bridges the tables, giving insights that one table alone may not provide.

In this article, we’ll break down the different types of SQL JOINs and when to use them. Specifically, we’ll cover:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
CROSS JOIN
SELF JOIN

🔗 INNER JOIN

An INNER JOIN combines records based on a related column and returns only matching rows from both tables.

Syntax

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example; If the customers table and the orders table share a column - the customer_id, an INNER JOIN is used to identify the customers who made orders;

SELECT customers.customer_id, first_name, last_name, order_id
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

Result;

customer_id	first_name	last_name	order_id
1	john	doe	1
2	mary	wanjiku	2
3	peter	otieno	3
1	john	doe	4
4	lucy	njeri	5

An INNER JOIN can be used to combine more than two tables.

For example, to find out the customers, books they purchased and the corresponding order IDs;

SELECT orders.order_id, first_name, last_name, title
FROM customers
INNER JOIN orders 
ON customers.customer_id = orders.customer_id
INNER JOIN books b 
ON books.book_id = orders.book_id;

Result;

order_id	first_name	last_name	title
1	john	doe	Learning SQL
2	mary	wanjiku	Data Analytics Basics
3	peter	otieno	Python for Data Science
4	john	doe	Data Analytics Basics
5	lucy	njeri	Advanced SQL Queries

⬅️ LEFT JOIN/LEFT OUTER JOIN

The LEFT JOIN returns all records from the left table, and the matching records from the right table. Where no match exists, a NULL value is returned.

In a LEFT JOIN, the table specified in the FROM clause is treated as the left table.

Syntax

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

To show all the books and the customers who ordered them, a LEFT JOIN is used;

SELECT title,first_name,order_id
FROM books 
LEFT JOIN orders 
ON books.book_id = orders.book_id
LEFT JOIN customers 
ON orders.customer_id = customers.customer_id;

Result;

title	first_name	order_id
Learning SQL	john	1
Data Analytics Basics	mary	2
Python for Data Science	peter	3
Data Analytics Basics	john	4
Advanced SQL Queries	lucy	5
Machine Learning Intro	NULL	NULL

Notice that the row with Machine Learning Intro has NULL values because it was not ordered.

➡️ RIGHT JOIN/RIGHT OUTER JOIN

The RIGHT JOIN works like the LEFT JOIN, but it keeps all the rows from the right table and matching rows from the left table.

Syntax;

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

To show every book in the library, whether it was ordered or not;

SELECT books.book_id, title, order_id
FROM orders
RIGHT JOIN books
ON books.book_id = orders.book_id;

Result;

book_id	title	order_id
1	Learning SQL	1
2	Data Analytics Basics	2
3	Python for Data Science	3
2	Data Analytics Basics	4
4	Advanced SQL Queries	5
5	Machine Learning Intro	NULL

⚠️ Note: For both LEFT and RIGHT outer joins, the order in which you write your tables determines which one returns all of its values. You need to be keen about which table you put on the left and which goes on the right to get your intended result.

🔄 FULL JOIN/FULL OUTER JOIN

A FULL JOIN returns all rows from both tables.
As is the case in both the right and Left outer joins, for the rows that do not have a match, NULL is returned.

Syntax;

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

To retrieve all the customers and all the books, whether ordered or not, a FULL JOIN will be used;

SELECT order_id, first_name, last_name, title
FROM orders 
FULL JOIN customers  
ON customers.customer_id = orders.customer_id 
FULL JOIN books
ON orders.book_id = books.book_id;

Result;

order_id	first_name	last_name	title
1	john	doe	Learning SQL
2	peter	otieno	Python for Data Science
3	lucy	njeri	Advanced SQL Queries
2	mary	wanjiku	Data Analytics Basics
NULL	david	kimani	NULL
NULL	NULL	NULL	Machine Learning Intro

To retrieve only the matched or un-matched records, you can filter with the WHERE clause.

❌ CROSS JOIN

A CROSS JOIN returns the combination of each row from one table with each row from another table.

Syntax;

SELECT *
FROM table1
CROSS JOIN table2;

A CROSS JOIN does not have an ON clause, because we are not looking for matching rows.

If you have 2 tables; t-shirts color and t-shirt size tables, and wanted to find all possible combinations, a CROSS JOIN is used.

SELECT 
  tshirt_colors.color_name, 
  tshirt_sizes.size_name
FROM tshirt_colors
CROSS JOIN tshirt_sizes;

Result;

color_name	size_name
Black	Small
Black	Medium
Black	Large
Black	Extra Large
White	Small
White	Medium
White	Large
White	Extra Large
Navy	Small
Navy	Medium
Navy	Large
Navy	Extra Large

🔁 SELF JOIN

A SELF JOIN is used when comparing rows within the same table.

When using self joins, it is essential to use Aliases, to distinguish the two "roles" of the same table for exact execution.
AS is used to introduce an alias

Syntax;

SELECT column name (s) as alias
FROM employees A
JOIN employees B
ON A.column = B.column;

To best illustrate self joins, we will use the employees table below;

employee_id	name	department_id	manager_id	salary
1	Alice	1	NULL	50000
2	Bob	2	1	45000
3	Charlie	1	1	47000
4	Diana	3	NULL	60000
5	Eve	NULL	NULL	40000
6	Brian	2	4	50000
7	Joy	3	4	35000
8	Luke	5	1	45000

To show employees and their managers, we use a SELF JOIN as follows;

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m 
ON e.manager_id = m.employee_id;

Result;

employee	manager
Bob	Alice
Charlie	Alice
Brian	Diana
Joy	Diana
Luke	Alice

I hope this guide made joins less intimidating!
If you found this beginner's guide helpful, save it for later or share it with a friend who's just starting their SQL journey! Happy learning!!

Are there any joins I left out? Do you have any join tricks you want to share?
Let me know in the comment section below, I’d love to hear from you!

Applying SQL Concepts: DDL, DML, Filtering, and Data Transformation

Sharon-nyabuto — Sun, 12 Apr 2026 18:25:54 +0000

Structured Query Language (SQL) is the standard language used to interact with relational databases.
SQL is a command type language. Whether you want to create, delete, update or read data, SQL provides commands to perform these operations.
SQL commands are categorized based on their specific functionalities, and allow for the creation, manipulation, retrieval and control of data and database structures.
Among the command categories include;

Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Query Language (DQL)
Data Control Language (DCL)
Transaction Control Language (TCL)

In this article, we will look at Data Definition Language (DDL) and Data Manipulation Language (DML).

DDL (DATA DEFINITION LANGUAGE)

Data Definition Language (DDL) is a set of commands used to define and manage the structure of a database.
It focuses on creating and modifying database objects such as tables, schemas, and indexes.
DDL uses commands like CREATE, ALTER, and DROP, to determine how data is organized and stored within the database.
These commands affect the structure of the database and not the data itself.
DDL operations are often automatically committed, therefore changes take effect immediately without needing a manual COMMIT.

DML (DATA MANIPULATION LANGUAGE)

Data Manipulation Language (DDL) is a set of SQL commands used to manage and manipulate the data _stored within database tables.
Unlike DDL, which focuses on defining the structure of the database, DML is concerned with performing operations on the data itself.
Common DML commands include INSERT, UPDATE, and DELETE, which are used to add new records, modify existing data, and remove records from a table respectively.
In most database systems, DML changes are not automatically committed, meaning they can be rolled back if necessary before being finalized.

Below are some SQL commands and how they are used;

CREATE
DDL command used to create an object in a database, e.g a table

The syntax;
CREATE TABLE table_name(
column1 datatype constraints,
column2 datatype constraints,
column3 datatype constraints
);
For example, to create a schema in a database for a school, and add a table called students in the Schema you use CREATE

create table students
(student_id INT primary key,
first_name VARCHAR(50) not null,
last_name VARCHAR(50) not null,
gender VARCHAR(1),
date_of_birth DATE, 
class VARCHAR(10), 
city VARCHAR(50) 
);

INSERT

This command is used to add a new record in a database.

The syntax;
INSERT INTO table_name (column1, column2, column3)
VALUES
(value1, value2, value3),
(value1, value2, value3);

To add student details to the students table created above, you use INSERT

insert into students (student_id,first_name,last_name,gender,date_of_birth,class,city)
values 
(1,'Amina','Wanjiku','F','2008-03-12','Form 3','Nairobi'),
(2,'Brian','Ochieng','M','2007-07-25','Form 4','Mombasa'),
(3,'Cynthia','Mutua','F','2008-11-05','Form 3','Kisumu'),
(4,'David','Kamau','M','2007-02-18','Form 4','Nairobi'),
(5,'Esther','Akinyi','F','2009-06-30','Form 2','Nakuru');

UPDATE
The update command is used to modify existing records in a database.

The syntax;
UPDATE table_name
SET column1 = value1,
WHERE condition;

In the students table created, if student id number 2, moved to Nairobi from Mombasa, UPDATE is used.

update students
set City = 'Nairobi'
where student_id = 2;

DELETE

This deletes one or more records from a database object.
It is used together with the _WHERE _clause to ensure that only specific rows are deleted.

The syntax;

DELETE FROM table_name
WHERE condition;

In the students table, if student_id 4 is no longer a student there, his records are removed using DELETE

delete 
from students
 where student_id = 4;

FILTERING WITH WHERE

The WHERE clause filters rows based on one or more conditions, so your query only modifies the records that match.
It is often used across SELECT, UPDATE, and DELETE statements.

The Where clause is used with logical and comparison operators such as equal to (=), greater than (>) and less than (<).

It can also be used with other operators including;

BETWEEN

Where clause is used with the BETWEEN operator, to filter records within a specified range.
When using between, the result will include both the start and end values of the range.

For example, to find students that scored between 55 and 95 marks from a results table, the values returned will include 55 and 95

select result_id,student_id,subject_id,marks,grade
from exam_results 
where marks between 55 and 95;

The WHERE clause can be used with the IN operator to filter records based on multiple values.
It makes queries shorter and easier to understand, instead of writing multiple conditions using the (=) operator combined with OR.

Syntax;

SELECT column1, column2
FROM table_name
WHERE column_name IN (value1, value2, value3);

To find students from different cities in our students table, we use IN

select first_name, last_name, city
from students 
where city in ('Nairobi','Nakuru','Mombasa');

LIKE

WHERE clause is used with the LIKE operator when searching for a particular pattern.
When using LIKE, a wildcard is used;
‘%’- signifies any number of characters including 0.
‘_’ wildcard is used for a specific number of characters.

Syntax is;
SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';

For example, to find the list of subjects that contain the word 'studies' in a subjects table, LIKE is used.

select subject_name
from subjects
where subject_name like '%Studies%';

CASE WHEN STATEMENT

The CASE WHEN statement is used to add conditional logic inside queries, similar to an if-else structure.
It checks conditions one by one and returns a value as soon as a matching condition is found.
It helps categorize data dynamically and allows creation of new categories based on the specific conditions within a query.

The syntax is;

select column name,
case
when condition1 then 'result 1'
when condition2 then 'result 2'
else 'default result
end as new column_name

For example, CASE WHEN can be used to group marks into a new column called Performance, in the results table, as shown below.

select result_id, student_id, subject_id,marks,
    case
        when marks >= 80 then 'Distinction'
        when marks >= 60 then 'Merit'
        when marks >= 40 then 'Pass'
        else 'Fail'
    end as Performance
from exam_results;

REFLECTION

Learning Databases and SQL can seem difficult, but once you get into it, it becomes interesting especially if you are curious to see how you can create and modify your database along the way.

The most interesting thing with SQL is that there are some rules for consistency and functionality across databases. Some of those rules are;

Always end your statements with a semicolon (;)
SQL commands and key words such as SELECT and INSERT are not case-sensitive.
Spaces and new lines are allowed for better readability.
Do not use SQL keywords as names, if you have to, write them in quotes.
To include comments in your script, begin single line comments with --- and for multi-line comments you use /* at the beginning and */ at the end.
Commands like DROP and DELETE should be used with caution as they could result in permanent data loss without proper use of transactions or backups.

A Comprehensive Guide to Publishing and Embedding Power BI Reports on the Web with IFrames

Sharon-nyabuto — Mon, 06 Apr 2026 23:25:01 +0000

Microsoft PowerBI is one powerful tool when it comes to analyzing and visualizing data.

Power BI allows users to connect to different data sources, transform data, build interactive dashboards and share insights with others.

I have spent the last two weeks learning how to build queries, clean data on power query, use DAX functions, data modelling and relationships, as well as building dashboards and reports in Power BI.

Reports and dashboards in Power BI
Reports and dashboards are important for visualizing data.

While dashboards **are often a one-page summary of key insights, **reports are multiple pages and with a detailed analysis.
In the day to day operations, reports have to be shared with managers, teams and other stakeholders.
Instead of sending ‘.pbix’ files, which require Power BI desktop, embedding reports enables viewing them in browsers, interaction with filters and accessing live and updated data.

Embedding reports is a necessary move from static reports, to interactive web-based dashboards, that no one has a challenge accessing.

In this article, we will learn step by step how to;

Create a Workspace in Power BI Services
Upload and Publish a Power BI report from Power BI desktop
Generate an Embed code
Embed the report on a web page using an iframe.

Brief Overview of Power BI and the Publishing Process
Power BI consists of two components;

- Power BI Desktop– Where the data is loaded, cleaned and transformed, relationships and visuals are built and reports designed.
- Power BI Service- Where the reports are published, workspaces and embedded links are created, dashboards are shared and work is done in collaboration with others.

Creating a Workspace in Power BI Services

A Workspace is an online folder, that is dedicated to storing and organizing your dashboards and reports.

The default workspace is always ‘My Workspace’ which is often just for personal testing, and as a result, you cannot get a working public embed code if you generate your report here. Therefore, do NOT use My Workspace.

How to create a Workspace step-by-step;

Open your browser and go to https://app.powerbi.com
Log in using your Microsoft account
On the left menu, click Workspaces
Click the New Workspace button at the top right
Enter a Workspace Name, e.g. Electronics Sales
Click Apply

Once this is complete, your Workspace is ready. You can now publish your report.

Uploading and publishing a Power BI report from Power BI desktop.

Now we will upload the .pbix file to the workspace we just created.

How to publish a report to the Workspace step-by-step;

Open your report in Power BI desktop.
On the top right corner of the home ribbon, click ‘Publish’.
Select the workspace you just crated, (e.g. Electronic Sales) from the list of Workspaces that Power BI will display.
Click Select
Power BI will now upload your report. It may take a few seconds to 2 minutes, so give it some time.
You will see a message that shows you have successfully published your report to Power BI Services.

Until you have confirmed that the report has been published, do not close your Power BI Desktop.
If you close and there’s something wrong, you may have to start the process all over again.

To confirm if the report has been uploaded to the Power BI Service;

Go back to https://app.powerbi.com
Open your Workspace and refresh. You should see your report

Generating the Embed Code.

Embedding allows the report to be displayed inside a website.
Power BI provides an embed code, that can be copied and pasted in a website.

How to generate a code, step-by-step;

Open your report in the Workspace.
Click ‘File’ at the top of the page.
Select ‘Embed Report’, then click on Website or portal
Click on create Embed code.
Then click Confirm.
Copy the embed code to be used later.

You will now see a pop-up window with the link to embed, and a box with the embed code needed, this is the only correct place to get your embed code from.

The embed code, is an Iframe (inline frame) which is a HTML tag that allows you to display another webpage inside your webpage.

A typical iframe will look like this;

<iframe title="Electronic_Data" width="1140" height="541.25" src="https://app.powerbi.com/reportEmbed?reportId=1e766837-7d75-4a37-bc3d-3e0ea1f56073&autoAuth=true&ctid=c95984e8-6a70-4512-a440-1c79bca9cc37" frameborder="0" allowFullScreen="true"></iframe>

The width and height parts of the code are flexible, and you can change those to whatever you like. All the other parts however, should remain exactly as they are.

A common mistake often made is using the Share button to embed a report.
The share button generates links accessible only to those that have accounts in your organization. It will therefore not generate an appropriate embed code.

Embedding the report on a webpage

Embedding codes allow integration into websites.

How to do it, step-by-step;

Open any plain text editor, such as VS code or Notepad. Do not use Microsoft Word.
Create an empty .html file.
Add basic HTML structure;

html
<!DOCTYPE html>
<html>
<head>
<title>Title of report e.g. Electronic Sales Data</title>
</head>
<body>
<h1>Heading e.g. Electronic Sales Dashboard</h1>
<Power BI iframe goes here>
</body>
</html>

Click file > Save as, and name the file Index.html

That is it, if you double click on the saved html file, it will open on any browser and you can load your report, and see all the interactive report running perfectly.

Troubleshooting common Issues

If something is not working, it could be one of the following;

Problem: Report requires users to log in
Fix: New workspaces are sometimes set to private by default. Go back to your workspace, click manage access, and add Everyone as Viewer.

Problem: Blank Screen after the publishing and embedding
Fix: Give it time. This is a normal occurrence, as Power BI can take a few minutes for the embed code to work, after publishing.

Problem: The report works on your end but no one else can see it.
Fix:It is possible you used ‘Share’ Button, when embedding, instead of generating the embed code. You might need to generate the embed code anew.

Key Insights and Conclusion

Now that you have learnt this process, you can go ahead and use it for your student projects, portfolios, and for work, absolutely free.

You do not need a paid premium license to embed your Power BI reports.
Every singe feature of your report will work exactly as it does on Power BI Desktop, the slicers, filters and interactions will work just fine inside the Iframe.

In conclusion, you could build the best Power BI report, but if you cannot publish it and share it with other people, it's use may not be maximized, and a lot of insight could be lost.

DEV Community: Sharon-nyabuto

Writing Reusable code in Python

Understanding Functions

What is a Function?

Defining your first function

Calling a function

Parameters

Returning Values

Conclusion

Repeating Tasks Within Tasks

Understanding nested loops in Python

Syntax

Nested Loops Use cases

1. To find missing values (data cleaning)

2. To identify duplicate records (Data Quality)

3. To find a specific value in your table (Data exploration)

4. Data Validation (for + while)

Limitations of Nested Loops

Conclusion

What's next?

Controlling Loops with break and continue

Knowing when to stop or skip a loop in Python

The break statement

The continue statement

Conclusion

What's next?

Repeating Tasks Without Repeating Code

Using for and while loops in Python

The FOR Loop

The WHILE loop

Conclusion

Up Next...

Making Decisions Within Decisions in Python

Taking Conditional Logic Further with Nested if Statements

Nested if Statements

Syntax

Bonus Tip: f-Strings

Teaching Python to Make Decisions

Understanding if, elif, and else

So what is a conditional statement?

Comparison Operators

The IF statement

The ELSE statement

The ELIF statement

Putting Conditionals into Practice

Your turn, try it yourself!

What's Next?

Python for Data Analysts: Getting Started

Understanding what Python is, why it matters, and the concepts every beginner will encounter.

So what is Python and why does it matter in Data analysis?

Why does Python matter?

Common Terms You'll Encounter in Python - Getting familiar with the Python Environment

My first few lines of Python

Biggest lessons so far

Final thoughts

Cleaning Messy Phone Numbers in PostgreSQL Using REGEXP_REPLACE

Introduction

First, What Even Is regexp_replace?

Understanding the Special Characters in Patterns

The ^ character

The \ character (backslash)

Square brackets [ ]

The + operator

The $ character

Putting them together: ^[0-9]+$

Our Problem: What Does the Messy Data Look Like?

Step 1: Replace +254 or 254 at the Start With 0

Step 2: Remove Any Character That Is Not a Digit

Step 3: The WHERE Clause — Only Update What Needs Fixing

Putting It All Together

The SELECT query (to preview your cleaned data before changing anything)

The UPDATE query (to actually apply the changes)

Quick Recap

Final Thoughts

SQL Subqueries vs. CTEs: A Guide to Writing Better Queries

PART 1: SUBQUERIES

Subqueries Categorized by Output

Scalar Subquery

Multi-row Subquery (List Subquery)

Table Subquery (Derived Table)

The `break` statement

The `continue` statement

Using `for` and `while` loops in Python

Nested `if` Statements

First, What Even Is `regexp_replace`?

The `^` character

The `\` character (backslash)

Square brackets `[ ]`

The `+` operator

The `$` character

Putting them together: `^[0-9]+$`

Step 1: Replace `+254` or `254` at the Start With `0`

Step 3: The `WHERE` Clause — Only Update What Needs Fixing