World Indicators Database: Implementing a Star Schema for Analytical Insights

import pandas as pd
import sqlite3
import os

def process_and_load_csvs_to_sql_with_duplicates_dropped(csv_paths, db_name, table_names, subset_columns=None):
    """
    Reads specific CSV files from provided paths, drops rows with missing values AND duplicates,
    and loads each processed DataFrame into a SQLite database with specified table names.

    Args:
        csv_paths (list): A list of strings, where each string is the full path to a CSV file.
        db_name (str): The name of the SQLite database file to create or connect to (e.g., 'world.db').
        table_names (list): A list of strings, where each string is the desired table name
                            in the database for the corresponding CSV file in csv_paths.
                            The order of table names should match the order of CSV paths.
        subset_columns (list, optional): A list of column names to consider when identifying
                                         duplicates. If None, all columns are considered.
                                         Defaults to None.
    """
    try:
        # Connect to the SQLite database (it will be created if it doesn't exist)
        conn = sqlite3.connect(db_name)
        cursor = conn.cursor()

        if len(csv_paths) != len(table_names):
            raise ValueError("The number of CSV paths must match the number of table names.")

        for i, csv_path in enumerate(csv_paths):
            table_name = table_names[i]
            print(f"Processing file: {csv_path} and loading into table: {table_name}")

            try:
                # Read the CSV file into a pandas DataFrame
                df = pd.read_csv(csv_path)
                print(f"  Shape before dropping NaNs: {df.shape}")

                # Drop rows with any missing values
                df_cleaned = df.dropna()
                print(f"  Shape after dropping NaNs: {df_cleaned.shape}")

                # Drop duplicate rows
                df_deduplicated = df_cleaned.drop_duplicates(subset=subset_columns)
                print(f"  Shape after dropping duplicates: {df_deduplicated.shape}")

                # Load the cleaned and deduplicated DataFrame into the SQLite database
                df_deduplicated.to_sql(table_name, conn, if_exists='replace', index=False)
                print(f"  DataFrame loaded into table: {table_name}")

            except FileNotFoundError:
                print(f"Error: File not found: {csv_path}")
            except pd.errors.EmptyDataError:
                print(f"Warning: File is empty: {file_path}")
            except Exception as e:
                print(f"An error occurred while processing {csv_path}: {e}")

        # Close the database connection
        if conn:
            conn.close()
            print(f"\nSuccessfully processed CSV files and loaded data into database: {db_name}")

    except ValueError as ve:
        print(f"Error: {ve}")
    except Exception as overall_e:
        print(f"An overall error occurred: {overall_e}")

# Example Usage (assuming you want to drop duplicates based on all columns):
if __name__ == "__main__":
    csv_file_paths = [
        'demographics.csv',
        'economy.csv',
        'education.csv',
        'labour.csv',
        'lifespan.csv'
    ]

    output_table_names = [
        'demographics',
        'economy',
        'education',
        'labour',
        'lifespan'
    ]

    database_name = 'world_data_v4.db'

    process_and_load_csvs_to_sql_with_duplicates_dropped(csv_file_paths, database_name, output_table_names)

    

pip install ipython-sql

%load_ext sql

%%sql sqlite:///world_data_v4.db
SELECT name
FROM sqlite_master
WHERE type='table';

%%sql

CREATE TABLE temp_demographics AS SELECT * FROM demographics;


DROP TABLE demographics;


CREATE TABLE demographics (
    country_code VARCHAR(2) PRIMARY KEY,
    name VARCHAR(255),
    region VARCHAR(100),
    urban_population REAL,
    pop_growth REAL,
    pop_density REAL,
    sex_ratio REAL
);


INSERT INTO demographics SELECT * FROM temp_demographics;

-- Drop the temporary table
DROP TABLE temp_demographics;

%%sql


CREATE TABLE temp_economy AS SELECT * FROM economy;


DROP TABLE economy;


CREATE TABLE economy (
    economy_id INTEGER PRIMARY KEY,
    country_code VARCHAR(2),
    gdp REAL,
    imports REAL,
    exports REAL,
    gdp_growth REAL,
    gdp_per_capita REAL
);

INSERT INTO economy SELECT * FROM temp_economy;

-- Drop the temporary table
DROP TABLE temp_economy;