DEV Community: Dorin Jerotich

From API to Dashboard: Tracking Kenya’s Debt with Python,PostgreSQL &Grafana

Dorin Jerotich — Sun, 22 Jun 2025 19:39:59 +0000

The objective of this project is to build a complete data pipeline to extract, transform, store and visualize Kenya's external debt data between 2010 and 2024 using the World Bank’s public data API.

Project requirements

Python for scripting and data processing
Pandas for data transformation
PostgreSQL for data storage
Grafana for interactive dashboards
World Bank API as the data source

Steps:

1.Extracting external debt data for Kenya from the World Bank API

import requests

debt_url = "https://api.worldbank.org/v2/country/KE/indicator/DT.DOD.DECT.CD?format=json"
response = requests.get(debt_url )
data = response.json()
records = data[1]  # The second element contains the actual data 
print(records)

json output for the records

[ { "indicator": { "id": "DT.DOD.DECT.CD", "value": "External debt stocks, total (DOD, current US$)" }, "country": { "id": "KE", "value": "Kenya" }, "countryiso3code": "KEN", "date": "2024", "value": null, "unit": "", "obs_status": "", "decimal": 0 } ]

2.Transforming the dataset

Extracting the required fields (country,date and value)

df = pd.DataFrame([{
    'country': d['country']['value'],
    'date': d['date'],
    'value': d['value']
} for d in records])

This uses list comprehension to loop through each dictionary d in the records list and extracts a simpler dictionary with the 3 key-value pairs.

Drop or handle any missing values

df = df.dropna() ##Dropping the missing values

Ensuring correct formatting and consistency of values

Convert the date column to a datetime value for easier time-series analysis

df['date']=pd.to_datetime(df['date'],format='%Y')

Sample output of our transformed data

3.Loading the transformed data into a PostgreSQL database

Import required packages

import os
import pandas as pd
from sqlalchemy import create_engine
from dotenv import load_dotenv

Prepare .env file (Optional but recommended)

Create .env file to store your database credentials

DB_USER=your username
DB_PASS=database password
DB_HOST=database hostname
DB_PORT=database port
DB_NAME=database name

Get DB credentials from .env

Use python-dotenv to read the .env file securely and fetch variables from the environment.

load_dotenv()  # This loads environment variables from .env file
username = os.getenv("DB_USER")
password = os.getenv("DB_PASS")
localhost = os.getenv("DB_HOST")
port = os.getenv("DB_PORT")
dbname = os.getenv("DB_NAME")

Create SQLAlchemy connection string

Create a connection string using your credentials. The engine is your connection to the PostgreSQL database.

engine = create_engine(f"postgresql+psycopg2://{user}:{password}@{host}:{port}/{dbname}")

Load the Dataframe into PostgreSQL

Use df.to_sql() to write your Dataframe to the database

df.to_sql('external_debtKe', con=engine, if_exists='replace', index=False)

Confirming that the data has loaded successfully to the postgres database.

4.Visualize the data using Grafana by connecting to your PostgreSQL database

Connect PostgreSQL to Grafana with your database credentials to enable time series visualization of the data

Line Chart displaying total external debt from 2010 to 2024

Bar Chart visualizing the annual change in debt using calculated year-over-year differences

Insights & Observations

Kenya’s external debt has grown every year between 2010 and 2024.
No single year showed a decline, suggesting that debt repayment may be limited or outpaced by new borrowing.

Conclusion
This project demonstrates how open data APIs, Python, SQL, and Grafana can be combined into a real-world data engineering solution. By building a full pipeline, we gained insights not just into Kenya’s debt trajectory, but also into the technical processes required to turn raw data into meaningful insights.

Extracting data from API using python

Dorin Jerotich — Sun, 22 Jun 2025 12:26:56 +0000

In today’s data-driven world, APIs play a vital role in connecting services and applications. APIs allow access to real-time information from anywhere on the web.

We will learn how to extract data from an API using Python with the powerful requests library.

💡 What Is an API?

When we say "extracting data from an API," we're typically referring to making HTTP requests to an endpoint provided by a service, and getting back structured data .

1.📦 Getting Started: Installing the `requests` Library

pip install requests

2.Python Script to fetch data

# importing the requests library

import requests

### Defining the url to fetch data

url = "https://jsonplaceholder.typicode.com/posts"

# Make the GET request to get data from the url  defined

response = requests.get(url)

# Check if the request was successful

if response.status_code == 200:
    data = response.json()  # parse the json data

else:
    print("Failed to retrieve data:", response.status_code)

Understanding the Code
requests.get(url) – Sends an HTTP GET request to the specified URL.

response.status_code – Shows the result of the request (200 means OK).

response.json() – Parses the JSON response into a Python list/dictionary.

When we run requests to the API ,the status codes are essential in determining the result of our requests.

Here are some common http status code:

Status Code	Meaning	Description
`200`	OK	The request was successful and the server returned the data.
`400`	Bad Request	The server could not understand the request due to invalid syntax.
`403`	Forbidden	You do not have permission to access the resource.
`404`	Not Found	The requested resource does not exist.
`500`	Internal Server Error	The server encountered an unexpected condition.

Handling Query Parameters

Many APIs require you to send additional information as query parameters in your URL. For example, let’s say we have an API that lets us search for users, and we can specify the user’s name as a query parameter, like this: https://api.example.com/users?name=john.
Here’s how you can do it with requests:

import requests url = "https://api.example.com/users" params = {'name': 'john'} response = requests.get(url, params=params) print(response.json())

Handling Errors

Things can go wrong; the server might be down. It's always good practise to include error handling:

try:
    response = requests.get(url, timeout=10)
    response.raise_for_status()  # Raise exception for bad status codes
    data = response.json()
except requests.exceptions.RequestException as e:
    print("An error occurred:", e)

Fetching Stock Data from Alpha Vantage

Alpha Vantage provides realtime and historical financial market data through a set of powerful data APIs and spreadsheets.
For this project we will use a free API to fetch the daily,weekly & monthly stock data and turn it into a clean, usable format.

We will define a python function fetch_data that connects to the API, requests stock data for a given symbol,formats the data into a neat pandas dataframe and returns the result.

import requests
import pandas as pd

API_KEY = 'your_api_key_here'  # Alpha Vantage API key
BASE_URL = 'https://www.alphavantage.co/query'  # Alpha Vantage API endpoint

def get_time_series(symbol, function):
    params = {
        'function': function,
        'symbol': symbol,
        'apikey': API_KEY,
        'datatype': 'json'
    }

    response = requests.get(BASE_URL, params=params)
    data = response.json()

    # Check for any API errors
    if 'Error Message' in data or 'Note' in data:
        raise ValueError(f"Error fetching data for {symbol}: {data.get('Error Message', data.get('Note', 'Unknown error'))}")

    # Determine the key for the time series data
    time_series_key = next(k for k in data.keys() if 'Time Series' in k)
    df = pd.DataFrame.from_dict(data[time_series_key], orient='index')
    df.index = pd.to_datetime(df.index)
    df = df.sort_index(ascending=False)  # Sort the data by most recent 
    return df

🧑‍💻 Function Breakdown: `get_time_series(symbol, function)`

The function get_time_series(symbol, function) does the following:

Takes Two Parameters:
- symbol: The stock ticker (e.g., 'AAPL' for Apple).
- function: The type of time series data (e.g., 'TIME_SERIES_DAILY', 'TIME_SERIES_WEEKLY').
Sends a Request to Alpha Vantage:
- Uses the provided symbol and function to request data from Alpha Vantage’s API.
Extracts the Time Series Data:
- Looks through the API response to find the key that holds the time series data (like "Time Series (Daily)").
Converts to a pandas DataFrame:
- The raw data from the API is turned into a DataFrame, which makes it easier to manipulate and analyze.
- The DataFrame is sorted so that the most recent data comes first.
Returns the Data:
- The function returns the cleaned-up DataFrame containing the stock's time series data.

In essence, this function grabs stock data from Alpha Vantage, converts it to a readable format, and sorts it for easy use!

Here’s how you’d use the function:

apple_data = get_time_series('AAPL', 'TIME_SERIES_DAILY')
print(apple_data.head())

This will show you Apple’s most recent daily stock data, with columns like open, high, low, close, and volume.

🧹 Conclusion

Extracting data from APIs using Python is an essential skill for developers, data scientists, and data engineers. With just a few lines of code using the requests library, you can tap into vast amounts of real-time information from across the internet.

Whether you're building something big or just experimenting, APIs open a gateway to endless possibilities.

Python 101:The Ultimate Beginner’s Guide

Dorin Jerotich — Fri, 16 May 2025 13:08:41 +0000

Python is a widely used high level general purpose programming language created by Guido van Rossum in 1991.

It is used for web development,software development,data analytics and Artificial Intelligence & Machine Learning.

Why Python

Python is widely preferred due to it's high capabilities below

It has a simple syntax similar to English language hence simple to use and understand.
Python runs on intepreter system meaning code can be executed as soon as it's written.You do not need to compile your program before executing it.
Python supports 3 programming paradigms - its procedural,object-oriented and functional.
Python has extensive library which includes almost every function imaginable

Python Basics

Variables in Python

A variable is a named location used to store data in memory and can hold different data types ie strings, integers, floats.
Python has no command for declaring a variable but rather created the moment it's assigned a value.

name = 'Maryam'
age = 62

Data Types

Variables can store data of different types, and different types can do different things. In Python, the data type is set when you assign a value to a variable.

Python has the following data types built-in by default:

Type	Example	Description
`int`	`29`	Whole numbers
`float`	`1.25`	Decimal numbers
`str`	`"stylo"`	Text data
`bool`	`True`, `False`	Boolean values
`list`	`[40, 52, 13]`	Ordered, mutable collection
`tuple`	`(11, 23, 63)`	Ordered, immutable collection
`set`	`{1, 2, 3}`	Unordered, unique values
`dict`	`{"a": 1, "b": 2}`	Key-value pairs

1.Strings

Strings in python are surrounded by either single quotation marks, or double quotation marks.
Assigning a string to a variable is done with the variable name followed by an equal sign and the string:


a = "Hello"
print(a)

# slicing strings.
name = 'Aubameyang'
print(name[3:7]) # returns characters from position 3 to position 7(not included)
# amey
print(name[:5]) # _returns characters from the start to position 5(not inclusive)_
#Aubam
print(name[2:]) # returns characters from position 2 all the way to the end.
#bameyang

Python has built in methods used to modify strings

a = "Hello, World!"
print(a.upper()) #returns string in upper case
# HELLO, WORLD!
print(a.lower()) # returns the string in lower case
# Concatenating strings
d = "Hello"
b = "World"
c = d + " " + b
print(c) # Hello World

2.Lists

A list is an ordered mutable collection that allows duplicate values.
List items can be of any data type
Lists are created using square brackets


thislist = ["apple", "banana", "cherry", "apple", "cherry"]
print(thislist)

# Identifying the data type

fruits = ["apple", "banana", "cherry"]
print(type(mylist)) # <class 'list'>

# Accessing items in  lists

mylist = ['apple', 'banana', 'cherry']
print(mylist[1]) # banana

#Changing list items

thislist = ["apple", "banana", "cherry"]
thislist[1] = "blackcurrant"
print(thislist) # ['apple', 'blackcurrant', 'cherry']

#Inserting an item at specified index

thislist = ["apple", "banana", "cherry"]
thislist.insert(2, "watermelon")
print(thislist) # ['apple', 'banana', 'watermelon', 'cherry']

# Adding an item at the end of the list

thislist = ["apple", "banana", "cherry"]
thislist.append("orange")
print(thislist) # ['apple', 'banana', 'cherry', 'orange']

# Removing specified item from the list

thislist = ["apple", "banana", "cherry"]
thislist.remove("banana")
print(thislist) # ['apple', 'cherry']

# Removing an item on specified index

thislist = ["apple", "banana", "cherry"]
thislist.pop(1)
print(thislist) # ['apple', 'cherry']

thislist = ["orange", "mango", "kiwi", "pineapple", "banana"]
thislist.sort()
print(thislist)

thislist = ["banana", "Orange", "Kiwi", "cherry"]
thislist.reverse()
print(thislist)

3.Tuples

A tuple is an ordered, immutable collection of items that allows duplicates.
Tuples are created using double brackets ()

# Creating a tuple
my_tuple = (1, 2, 3)



``# Tuple with mixed data types
person = ("Lenny", 25, True)``

# Accessing elements
print(person[0])  # Output: Lenny

# Slicing
print(my_tuple[1:])  # Output: (2, 3)

# Nested tuple
nested = (1, (2, 3), 4)

# Tuple with one item 
single = (5,)

4.Dictionary

A dictionary is an unordered, mutable collection of key-value pairs. It uses {} curly braces.

#Creating a dictionary

person = {
    "name": "Warner",
    "age": 25,
    "is_student": False
}
# Access value
print(person["name"])  # Output: Warner

# Modify value
person["age"] = 25

# Add new key-value pair
person["city"] = "Kisumu"

# Delete a key
del person["is_student"]

# A dictionary of people and their ages
people = {
    "Juliette": 28,
    "Kenji": 34,
    "Warner": 41
}


# Updating a value
people["Warner"] = 42

# Removing a person
people.pop("Juliette")

# Showing all keys and values
print("Keys(people.keys()) # dict_keys(['Kenji', 'Warner', 'Ella'])
print("Values:", people.values()) #dict_values([34, 42, 25])
print("Items:", people.items()) # dict_items([('Kenji', 34), ('Warner', 42), ('Ella', 25)])

Conditional Statements

Conditional statements in Python are used to execute certain blocks of code based on specific conditions. The conditional statement checks to see if a statement is True or False .

The if statement

This is the simplest form of a conditional statement. It executes a block of code if the given condition is true.

age = 30

if age > 25:
    print("You're still a child")

This checks if the age is greater than 25. Since 30 > 25, the condition is True, so the message gets printed.

If else statement

The if-else statement is used to execute both the true part and the false part of a given condition. If the condition is true, the if block code is executed and if the condition is false, the else block code is executed.

number = 51

if number % 2 == 0:
    print("The number is even.")
else:
    print("The number is odd.")

Since 51 is not divisible by two, the else statement is executed.

Elif statement

elif statement in Python stands for "else if." It allows us to check multiple conditions , providing a way to execute different blocks of code based on which condition is true.

score = 73

if score >= 70:
    print("Grade: A")
elif score >= 60:
    print("Grade: B")
elif score >= 50:
    print("Grade: C")
else:
    print("Grade: D")

The statement checks each condition in order, and once it finds a condition that's true—like score >= 70 for a score of 73,it runs that block and skips the rest. Hence the output will be Grade :A

Conclusion

Understanding core concepts like variables, data types, and conditional statements is just the beginning.
Keep learning and experimenting ,Python’s capabilities are vast and the journey has just begun.

Getting Started with SQL: The Language of Data

Dorin Jerotich — Thu, 15 May 2025 14:50:38 +0000

Structured Query Language (SQL) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them. A relational database organizes data into rows and columns, which collectively form a table. SQL can be termed as a language used to talk to a database.

SQL is useful for the following tasks

To create new databases and tables
To insert records in a database
To update records in a database
To delete records from a database
To retrieve data from a database

SQL Data Types

In SQL, data types define what kind of data can be stored in a column. Some common data types include:

INT: Integer numbers.
VARCHAR(n): Variable-length text (with a maximum length of n characters).
DATE: Date values (e.g., '2045-05-14').
BOOLEAN: Boolean values (TRUE or FALSE).

Basic SQL commands

CREATE

The CREATE TABLE statement is used to create a new table in a database.

Syntax
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, );

Example:
CREATE TABLE Students ( StudentID int, LastName varchar(255), FirstName varchar(255), Age int, Gender varchar(255);

The second query creates a table called Students with the following five columns.

The StudentID & Age columns are of type int and will hold an integer.

The LastName, FirstName and Gender columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.

INSERT INTO

The INSERT INTO statement is used to insert new records in a table.

Syntax

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

INSERT INTO Students (StudentID, FirstName, LastName, Departmentid) VALUES (101, 'Juliette', 'Warner',1998);

Output:

SELECT

SQL SELECT statement is used to select rows and columns from a table in
the database.

Syntax:

SELECT column1, column2, ...
FROM table_name;

SELECT * FROM table_name ; returns all records in a table.

WHERE

Used to filter data based on a specified condition .Often used with comparison operators(>,=,<=,>=,<,=!).

Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition

The query returns the records of employees who have salary greater than 90,000

GROUP BY

The GROUP BY statement groups rows that have the same values into summary rows, enabling one to run aggregate functions( COUNT() , MAX() , MIN() , SUM() , AVG() ).

Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition

A query calculating the average monthly salary for each department by grouping the employees based on their department.

SELECT department,AVG(monthly_salary) FROM employees GROUP BY department ;

Output:

ORDER BY

The command sorts the results set in either ascending or descending order.

Syntax:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

A query sorting the employees from based on their monthly salary in descending order

SELECT * FROM employees ORDER BY monthly_salary DESC ;

HAVING CLAUSE

The HAVING clause is mostly used with aggregate functions. Having clause filters the records from groups based on specified conditions.

Syntax:

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s)

A query that filters departments having their average salary greater than 70,000

DELETE

The DELETE statement is used to delete existing records in a table.

Syntax:
DELETE FROM table_name WHERE condition;

The WHERE clause specifies which records should be deleted and if omitted, all records in the table will be deleted.

It is also possible to delete all rows in a table without deleting the table. using DELETE FROM table_name; syntax

The query deletes all entries in the student table

Conclusion

In this introduction, we explored the basics of SQL and how it helps manage and interact with data stored in relational databases.Stay tuned for the next part, where we will dive deeper into intermediate SQL concepts.
Thank you for taking the time to read this article.

DEV Community: Dorin Jerotich

From API to Dashboard: Tracking Kenya’s Debt with Python,PostgreSQL &Grafana

Project requirements

Steps:

1.Extracting external debt data for Kenya from the World Bank API

2.Transforming the dataset

3.Loading the transformed data into a PostgreSQL database

4.Visualize the data using Grafana by connecting to your PostgreSQL database

Extracting data from API using python

💡 What Is an API?

1.📦 Getting Started: Installing the requests Library

2.Python Script to fetch data

Handling Errors

🧑‍💻 Function Breakdown: get_time_series(symbol, function)

🧹 Conclusion

Python 101:The Ultimate Beginner’s Guide

Why Python

Python Basics

Variables in Python

Data Types

Conditional Statements

Getting Started with SQL: The Language of Data

Basic SQL commands

1.📦 Getting Started: Installing the `requests` Library

🧑‍💻 Function Breakdown: `get_time_series(symbol, function)`