ghost-mann/kenya-data-analysis

Data Platform for Analyzing Kenya’s Food Prices and Inflation Trends. # Food Prices ETL and Visualization Pipeline This project provides a complete ETL (Extract, Transform, Load) pipeline for processing Kenyan food price data from a CSV file. The data is cleaned, transformed, and loaded into a PostgreSQL database using a star schema model, which is optimized for analytics. Finally, this document provides a set of powerful SQL queries to visualize this data in Grafana. ## Project Overview The pipeline performs the following steps: 1. **Extract**: Reads food price data from `wfp_food_prices_ken.csv`. 2. **Transform**: * Cleans and standardizes column names. * Handles null values and removes duplicates. * Converts date strings to datetime objects. * Prepares the data for loading into a dimensional model. 3. **Load**: * Populates four dimension tables (`dim_date`, `dim_location`, `dim_commodity`, `dim_market_type`) with unique, descriptive data. * Populates a central fact table (`fact_food_prices`) with numerical data (prices) and foreign keys linking to the dimension tables. * The loading process is idempotent, meaning it intelligently checks for existing data to prevent duplicates on subsequent runs. ## Database Schema (Star Schema) The data is organized into a star schema to enable efficient querying and analysis. #### `dim_date` Stores date components. | Column | Type | Description | | :--- | :--- | :--- | | `date_id` | SERIAL PRIMARY KEY | Unique identifier for the date. | | `date_value` | DATE | The full date (e.g., 2023-10-27). | | `year` | INTEGER | The year component (e.g., 2023). | | `month` | INTEGER | The month component (e.g., 10). | | `day` | INTEGER | The day component (e.g., 27). | #### `dim_location` Stores geographical information. | Column | Type | Description | | :--- | :--- | :--- | | `location_id` | SERIAL PRIMARY KEY | Unique identifier for the location. | | `admin1` | VARCHAR(255) | The county name (e.g., Nairobi). | | `admin2` | VARCHAR(255) | The sub-county or region. | | `market` | VARCH …