# Import packages

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import re
import kagglehub
import os
from IPython.display import display
import numpy as np
import joblib
import time
from collections import Counter

from sklearn.model_selection import train_test_split, GridSearchCV, cross_val_score
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_squared_error, r2_score, mean_absolute_error, classification_report, confusion_matrix, accuracy_score
from sklearn.ensemble import RandomForestRegressor, StackingRegressor, VotingClassifier, VotingRegressor
from sklearn.linear_model import LinearRegression
from sklearn.preprocessing import StandardScaler, PolynomialFeatures
from sklearn.feature_selection import RFE
from sklearn.impute import KNNImputer
from sklearn.neural_network import MLPRegressor
from xgboost import XGBRegressor

# Enable experimental IterativeImputer
from sklearn.experimental import enable_iterative_imputer
from sklearn.impute import IterativeImputer
from sklearn.linear_model import BayesianRidge

# Suppress all warnings
import warnings
warnings.filterwarnings('ignore')

# magic word for producing visualizations in Jupyter Notebook
%matplotlib inline

# Ensure all columns are displayed
pd.set_option('display.max_columns', None)  # Show all columns
pd.set_option('display.width', None)       # Adjust display width to avoid wrapping
pd.set_option('display.max_colwidth', None) # Show full content of each column (if needed)

# Define a function to print bold headers
def print_bold(text):
    print(f"\033[1m{text}\033[0m")

def analyze_dataset(df, dataset_name):
    """
    Function to analyze a given dataset.
    Displays basic dataset information and a preview of the data.

    Parameters:
    df (DataFrame): The DataFrame to analyze.

    Returns:
    None
    """
    # Get the total number of rows and columns
    print(f"\033[1m\nDataset: {dataset_name} \033[0m")
    row_count, column_count = df.shape
    print(f"\033[1m\nTotal number of rows: {row_count} \033[0m")
    print(f"\033[1mTotal number of columns: {column_count} \033[0m\n")

    # Display the first 5 rows of the dataset
    print("\nFirst 3 rows of the dataset:")
    display(df.head(n=3))

    # Display all column names
    print("\n\nAll Columns in the Dataset:")
    print(df.columns.tolist())

    # Identify and list repeated (duplicate) column names
    repeated_columns = df.columns[df.columns.duplicated()].tolist()

    # Display the repeated columns
    if repeated_columns:
        print("\nRepeated Columns:")
        print(repeated_columns)
    else:
        print("\nNo repeated columns found.")

def quality_issue_inspection_part1(df, dataset_name):
    """
    Function to do quality inspection on a given dataset.
    Displays missing values, duplicate rows, invalid values, outliers, 
    trailing spaces, column entries with mixed format, non-numeric data 
    in numeric columns, and numeric data in non-numeric columns.

    Parameters:
    df (DataFrame): The DataFrame to analyze.
    dataset_name (str): Name of the dataset.

    Returns:
    None
    """
    # Bold formatting
    bold_start = "\033[1m"
    bold_end = "\033[0m"

    print(f"\n\n{bold_start} ---------------------- Quality Issues Inspection Part 1 ---------------------- {bold_end}\n")
    print(f"{bold_start}Dataset:{bold_end} {dataset_name} \n\n")

    
    # 1. Missing Values
    print("1. Missing Values:")
    
    #  1.a: Analyze Missing Values in Columns
    missing_values = df.isnull().sum()  # Total missing values per column
    if missing_values.sum() == 0:
        print("Missing Values: None\n")
    else:
        # Calculate the percentage of missing values for each column
        missing_percentage = (missing_values / len(df)) * 100
        # Create a summary DataFrame
        missing_summary = pd.DataFrame({'Missing Values': missing_values, 'Percentage': missing_percentage.round(2)})
        print("Missing Values Summary (Columns):")
        print(missing_summary, "\n")

    # 1.b: Analyze Missing Values in Rows (without permanently adding columns)
    # Temporary calculation of total and percentage of missing values per row
    df_temp = df.copy()  # Create a temporary copy of the dataframe
    df_temp['Total_Missing_Values'] = df.isnull().sum(axis=1)  # Total missing values per row
    df_temp['Missing_Percentage'] = (df_temp['Total_Missing_Values'] / len(df.columns)) * 100  # Percentage missing per row

    # Total rows and rows with missing values
    total_rows = df_temp.shape[0]  # Total number of rows in the dataset
    total_rows_with_missing = df_temp[df_temp['Total_Missing_Values'] > 0].shape[0]

    # Percentage of rows with missing values
    percentage_rows_with_missing = (total_rows_with_missing / total_rows) * 100

    # Threshold for identifying rows with high missing values
    row_threshold = 30  # Rows with >30% missing values

    # Classify rows based on missing percentages
    rows_high_missing = df_temp[df_temp['Missing_Percentage'] > row_threshold]  # Rows with >threshold% missing
    rows_low_missing = df_temp[(df_temp['Missing_Percentage'] > 0) & (df_temp['Missing_Percentage'] <= row_threshold)]  # Rows with 0%-threshold%
    rows_no_missing = df_temp[df_temp['Missing_Percentage'] == 0]  # Rows with no missing values

    # Display summary results for rows
    print("Missing Values Summary (Rows):")
    print(f"     Total rows with missing values: {total_rows_with_missing} "
          f"({percentage_rows_with_missing:.2f}%)")
    print(f"     Rows with high missing values (> {row_threshold}%): {len(rows_high_missing)} "
          f"({(len(rows_high_missing) / total_rows * 100):.2f}%)")
    print(f"     Rows with low missing values (0% - {row_threshold}%): {len(rows_low_missing)} "
          f"({(len(rows_low_missing) / total_rows * 100):.2f}%)")
    print(f"     Rows with no missing values: {len(rows_no_missing)} "
          f"({(len(rows_no_missing) / total_rows * 100):.2f}%)\n")

    # 2. Duplicate Rows
    duplicate_rows = df.duplicated().sum()
    print(f"2. Duplicate Rows: Number of duplicate rows: {duplicate_rows}" if duplicate_rows > 0 else "2. Duplicate Rows: None\n")

    # 3. Invalid Values (Negative or Out-of-Range Values)
    invalid_found = False
    for col in df.select_dtypes(include=['int64', 'float64']):
        invalid_rows = df[df[col] < 0]  
        if not invalid_rows.empty:
            invalid_found = True
            print(f"3. Invalid values in '{col}':")
            print(invalid_rows[[col]].head(), "\n")
    if not invalid_found:
        print("3. Invalid and/or Negative Values: None\n")

    # 4. Outliers (Using IQR)
    print("4. Outliers")
    # Combine outlier detection and column listing
    outliers_found = False
    numeric_cols = df.select_dtypes(include=['int64', 'float64']).columns
    columns_with_outliers = []
    total_outliers = 0  # Counter to track the total number of outliers
    total_rows = len(df)  # Total number of rows in the dataset

    for col in numeric_cols:
        Q1 = df[col].quantile(0.25)  # First quartile
        Q3 = df[col].quantile(0.75)  # Third quartile
        IQR = Q3 - Q1  # Interquartile Range
        lower_bound = Q1 - 1.5 * IQR  # Lower bound for outliers
        upper_bound = Q3 + 1.5 * IQR  # Upper bound for outliers

        # Find rows with outliers in the column
        outliers = df[(df[col] < lower_bound) | (df[col] > upper_bound)]
    
        if not outliers.empty:
            outliers_found = True
            columns_with_outliers.append(col)
            print(f"Outliers detected in '{col}': {len(outliers)} rows")
            total_outliers += len(outliers)  # Add to the total count of outliers

    if not outliers_found:
        print("No outliers detected in any column.\n")

    # Print the list of columns with outliers
    if columns_with_outliers:
        print("\nColumns with outliers:", columns_with_outliers)
    else:
        print("\nNo columns with outliers found.")

    # Calculate and print the total percentage of outliers
    if total_outliers > 0:
        outlier_percentage = (total_outliers / (total_rows * len(numeric_cols))) * 100
        print(f"\nTotal number of outliers across all columns: {total_outliers}")
        print(f"Percentage of total outliers in the dataset: {outlier_percentage:.2f}%\n")
    else:
        print("\nNo outliers found in the dataset.\n")


    # 5. Redundant Leading/Trailing Spaces
    print("\n5. Redundant Leading/Trailing Spaces:")
    spaces_found = False
    for col in df.select_dtypes(include=['object']):
        spaces = df[col].str.contains(r'^\s|\s$', na=False)
        if spaces.any():
            spaces_found = True
            print(f"   Leading/trailing spaces found in column '{col}'.")
    if not spaces_found:
        print("   None\n")      

    # 6. Column Entries with Mixed Formats
    print("6. Column Entries with Mixed Formats:")
    mixed_formats_found = False
    for col in df.columns:
        if df[col].dtype == 'object':
            mixed_format = df[col].apply(lambda x: bool(re.search(r'\d{4}-\d{2}-\d{2}', str(x))) if pd.notnull(x) else False)
            if mixed_format.any():
                mixed_formats_found = True
                print(f"   Mixed formats detected in '{col}':")
                print(df[col][mixed_format].head(), "\n")
    if not mixed_formats_found:
        print("   None\n")


    # 7. Non-Numeric Data in Numeric Columns
    print("7. Non-Numeric Data in Numeric Columns:")
    non_numeric_data = []
    numeric_cols = df.select_dtypes(include=['int64', 'float64']).columns

    for col in numeric_cols:
        # Identify non-numeric data
        invalid_data = df[pd.to_numeric(df[col], errors='coerce').isnull()][col]
        if not invalid_data.empty:
            non_numeric_data.append({
                'Column': col,
                'Non-Numeric Data': invalid_data.unique().tolist(),
                'Count': invalid_data.shape[0]
            })

    if non_numeric_data:
        # Create and display a summary DataFrame
        non_numeric_summary = pd.DataFrame(non_numeric_data)
        print(non_numeric_summary.to_string(index=False))
    else:
        print("   None\n")

    # 8. Numeric Data in Non-Numeric Columns
    print("\n8. Numeric Data in Non-Numeric Columns:")
    numeric_in_non_numeric_data = []
    non_numeric_cols = df.select_dtypes(include=['object']).columns

    for col in non_numeric_cols:
        # Identify numeric data in non-numeric columns
        numeric_data = df[col].apply(lambda x: pd.to_numeric(x, errors='coerce')).dropna()
        if not numeric_data.empty:
            numeric_in_non_numeric_data.append({
                'Column': col,
                'Numeric Data': numeric_data.unique().tolist(),
                'Count': numeric_data.shape[0]
            })

    if numeric_in_non_numeric_data:
        # Create and display a summary DataFrame
        numeric_data_summary = pd.DataFrame(numeric_in_non_numeric_data)
        print(numeric_data_summary.to_string(index=False))
    else:
        print("   None\n")

def quality_issue_inspection_part2(df, dataset_name):
    """
    Analyze a dataset to detect:
    1. Special characters or formatting issues in object columns.
    2. Inconsistent units in object columns with recommendations.
    3. Unique categorical values in object columns.
    4. Analyze a dataset for data types and classification.
    
    Displays the results in a tabular format and saves them as CSV files.

    Args:
        df (pd.DataFrame): The dataset to analyze.
        dataset_name (str): Name of the dataset.

    Returns:
        1. Summary DataFrame with data types and classification.
        2. Summary of Special Characters
        3. Summary of Columns with Inconsistent Units and Recommendations
    """

    # Bold formatting
    bold_start = "\033[1m"
    bold_end = "\033[0m"

    print(f"\n\n{bold_start} ---------------------- Quality Issues Inspection Part 2 ---------------------- {bold_end}\n")
    print(f"{bold_start}Dataset:{bold_end} {dataset_name} \n\n")

    #  1. Detect Special Characters: Separated into 2 functions to avoid the IOPub data rate exceeded warning
    # 1.a. To display the summary 
    def detect_special_characters(df):
        """Detects special characters or formatting issues in object columns."""
        special_characters_details = {}
        special_characters_summary = {}

        for col in df.select_dtypes(include=['object']).columns:
            # Collect rows with special characters in the column
            special_char_rows = []
            unique_chars = set()
            for value in df[col].dropna():
                chars_in_value = re.findall(r'[^\w\s]', str(value))
                if chars_in_value:
                    special_char_rows.append(value)
                    unique_chars.update(chars_in_value)
            
            # Add to the dictionaries if special characters are found
            if special_char_rows:
                special_characters_details[col] = special_char_rows
                special_characters_summary[col] = sorted(unique_chars)

        # Remove columns without special characters
        special_characters_details = {k: v for k, v in special_characters_details.items() if v}
        special_characters_summary = {k: v for k, v in special_characters_summary.items() if v}

        return special_characters_details, special_characters_summary

    def display_special_characters_summary(special_characters_summary):
        """Displays the summary of special characters in a tabular format."""
        if special_characters_summary:
            summary_table = pd.DataFrame(
                [{"Column": col, "Special Characters or Formatting Issues": ', '.join(chars)} 
                 for col, chars in special_characters_summary.items()]
            )
            print("\n1. Summary of Special Characters:")
            print(summary_table.to_string(index=False))
        else:
            print(f"\nNo special characters or formatting issues found in the dataset '{dataset_name}'.")

    # To save the summary into csv files
    def save_special_characters_summary(special_characters_summary, special_characters_details):
        """Saves the detailed and summary results to CSV files."""
        if special_characters_details:
            # Create and save the detailed CSV
            max_len = max(len(v) for v in special_characters_details.values())
            formatted_data = {col: v + [""] * (max_len - len(v)) for col, v in special_characters_details.items()}
            special_characters_table = pd.DataFrame(formatted_data)

            detailed_output_file = f"{dataset_name}_special_characters_details.csv"
            special_characters_table.to_csv(detailed_output_file, index=False)
            print(f"\nDetailed special characters (delete if not needed) saved to '{detailed_output_file}'.")

            # Create and save the summary CSV
            summary_table = pd.DataFrame(
                [{"Column": col, "Special Characters or Formatting Issues": ', '.join(chars)} 
                 for col, chars in special_characters_summary.items()]
            )
            summary_output_file = f"{dataset_name}_special_characters_summary.csv"
            summary_table.to_csv(summary_output_file, index=False)
            print(f"\nSummary of special characters (delete if not needed) saved to '{summary_output_file}'.")

    # Detect and handle special characters
    special_characters_details, special_characters_summary = detect_special_characters(df)
    display_special_characters_summary(special_characters_summary)
    save_special_characters_summary(special_characters_summary, special_characters_details)

    
    #  2. Inconsistent Units 
    print("\n\n2. Columns with Inconsistent Units and Recommendations:")

    def check_units(col):
        """Checks for alphabetic characters in a column."""
        return df[col].str.contains(r'[a-zA-Z]', na=False) if df[col].dtype == 'object' else False

    inconsistent_units_summary = []

    for col in df.select_dtypes(include=['object']).columns:
        inconsistent_units = df[check_units(col)]
        if not inconsistent_units.empty:
            # Determine if reengineering or reencoding is needed
            if any(any(char.isdigit() for char in unit) for unit in set(inconsistent_units[col])):
                recommendation = "Reengineer (Separate numeric values and units)"
            else:
                recommendation = "Reencode (Standardize categories)"
            inconsistent_units_summary.append({"Column": col, "Recommendation": recommendation})

    if inconsistent_units_summary:
        inconsistent_units_table = pd.DataFrame(inconsistent_units_summary)
        print(inconsistent_units_table.to_string(index=False))

    
    # 3. Unique Categorical Values
    print("\n\n3. Unique Categorical Values:")

    unique_values_summary = {}

    for col in df.select_dtypes(include=['object']).columns:
        unique_values = df[col].dropna().unique()
        if unique_values.size > 0:
            unique_values_summary[col] = unique_values.tolist()

    if unique_values_summary:
        unique_values_table = pd.DataFrame(dict([(k, pd.Series(v)) for k, v in unique_values_summary.items()]))

        output_file = f"{dataset_name}_unique_categorical_values.csv"
        unique_values_table.to_csv(output_file, index=False)
        print(f"\nUnique categorical values (delete if not needed) saved to '{output_file}'.")
    else:
        print(f"\nNo unique categorical values found in the dataset '{dataset_name}'.")



    # 4. Analyze a dataset for data types, unexpected values, and generate a data dictionary
    print(f"\n\n4. Analyzing Dataset: '{dataset_name}' for data types and classification")

    # 4.a: Classify Column Type
    def classify_column(col):
        """
        Dynamically classify a column as 'categorical', 'ordinal', 'numeric', or 'other'.
        """
        unique_vals = df[col].dropna().unique()  # Drop NaN for unique values
        if df[col].dtype == 'object':
            return 'categorical'
        elif df[col].dtype in ['int64', 'float64'] and len(unique_vals) <= 10:
            return 'ordinal'  # Numeric with limited unique values
        elif df[col].dtype in ['int64', 'float64']:
            return 'numeric'
        else:
            return 'other'  # For columns that don’t fit the above types

    # 4.b: Check If Data Type is Correct or Incorrect
    def check_correct_data_type(col):
        """
        Check if the actual data type of a column matches the expected type.
        """
        expected_type = classify_column(col)
        actual_dtype = df[col].dtype

        # For categorical data
        if expected_type == 'categorical':
            if actual_dtype != 'object' and actual_dtype.name != 'category':
                return 'Incorrect'

        # For ordinal data (numeric with limited unique values)
        elif expected_type == 'ordinal':
            if actual_dtype not in ['int64', 'float64']:
                return 'Incorrect'

        # For numeric data
        elif expected_type == 'numeric':
            if actual_dtype not in ['int64', 'float64']:
                return 'Incorrect'

        # For other types (e.g., dates or mixed types)
        elif expected_type == 'other':
            try:
                # Check if the column can be converted to datetime without errors
                pd.to_datetime(df[col], errors='coerce')
            except Exception:
                return 'Incorrect'

        # Correct if all checks pass
        return 'Correct'

    # 4.c: Generate Data Dictionary
    data_dictionary = pd.DataFrame({
        'Column': df.columns,  # Column names
        'Data Type': df.dtypes.astype(str),  # Actual data type
        'Classification': [classify_column(col) for col in df.columns],  # Dynamic classification
        'Correct/Incorrect Data Type': [check_correct_data_type(col) for col in df.columns],  # Correctness check
        'Unique Values': [df[col].nunique() for col in df.columns],  # Count of unique values
        'Sample Values': [df[col].dropna().unique()[:7] for col in df.columns]  # First 7 unique non-NaN values
    })

    return data_dictionary

def tidiness_issue_inspection(df, dataset_name):
    """
    Inspects a DataFrame for tidiness issues, including:
    1. Column headers as values
    2. Wide vs. long format
    3. Poorly named columns
    4. Inconsistent Data Structure
    5. Unnecessary Columns
    6. Mixed Granularity
    7. Repeated Index or Hierarchical Issues

    Args:
        df (pd.DataFrame): The DataFrame to analyze.
        dataset_name (str): Name of the dataset.

    Returns:
        None
    """
    # Bold formatting for terminal output
    bold_start = "\033[1m"
    bold_end = "\033[0m"

    print(f"\n\n{bold_start} ---------------------- Tidiness Issues Inspection ---------------------- {bold_end}\n")
    print(f"{bold_start}Dataset:{bold_end} {dataset_name} \n\n")

    
    # 1. Column Headers as Values
    print(f"{bold_start}1. Column Headers as Values:{bold_end}")
    if any(char.isdigit() for col in df.columns for char in str(col)):
        print("   Column headers may contain values (e.g., '2021-Q1').\n")
    else:
        print("   None\n")

    
    # 2. Wide Format Instead of Long Format
    print(f"{bold_start}2. Wide Format Instead of Long Format:{bold_end}")
    wide_columns = [col for col in df.columns if any(char.isdigit() for char in str(col))]
    if wide_columns:
        print(f"   Wide-format columns detected: {wide_columns}\n")
    else:
        print("   None\n")

    
    # 3. Poorly Named Columns
    print(f"{bold_start}3. Poorly Named Columns:{bold_end}")
    poorly_named_columns = [
        col for col in df.columns
        if col.strip() == "" or col.isnumeric() or col.lower().startswith("unnamed")
    ]
    if poorly_named_columns:
        print(f"   Poorly named columns detected: {poorly_named_columns}\n")
    else:
        print("   None\n")

    
    # 4. Inconsistent Data Structure
    print(f"{bold_start}4. Inconsistent Data Structure:{bold_end}")
    inconsistent_rows = df.isnull().all(axis=1).sum()
    if inconsistent_rows > 0:
        print(f"   Number of completely empty rows: {inconsistent_rows}\n")
    else:
        print("   None\n")

    
    # 5. Unnecessary Columns
    print(f"{bold_start}5. Unnecessary Columns:{bold_end}")
    unnecessary_columns = [col for col in df.columns if df[col].nunique() == 1]
    if unnecessary_columns:
        print(f"   Unnecessary columns detected: {unnecessary_columns}\n")
    else:
        print("   None\n")


    # 6. Mixed Granularity
    print(f"{bold_start}6. Mixed Granularity:{bold_end}")

    potential_columns = []

    # Iterate through each column to detect mixed granularity
    for col in df.columns:
        if df[col].dtype == 'object':  # Only check object/string columns
            # Safely convert the column to strings and handle NaN values
            df[col] = df[col].apply(lambda x: str(x) if not pd.isnull(x) else "")
        
            # Check for mixed date formats (e.g., YYYY-MM-DD and YYYY-MM)
            contains_full_date = df[col].str.contains(r'\d{4}-\d{2}-\d{2}', na=False).any()
            contains_partial_date = df[col].str.contains(r'\d{4}-\d{2}', na=False).any()

            # Append columns with mixed formats to the potential_columns list
            if contains_full_date and contains_partial_date:
                potential_columns.append(col)

        # Check for numeric columns with inconsistent hierarchical group sizes
        elif df[col].dtype in ['int64', 'float64']:
            unique_group_sizes = df.groupby(col).size().nunique()
            if unique_group_sizes > 1:  # If group sizes vary, it may indicate mixed granularity
                potential_columns.append(col)

    # Output the results
    if potential_columns:
        print(f"   Potential mixed granularity detected in columns: {potential_columns}\n")
    else:
        print("   None\n")

    
    # 7. Repeated Index or Hierarchical Issues
    print(f"{bold_start}7. Repeated Index or Hierarchical Issues:{bold_end}")

    # Check for duplicate indices
    if not df.index.is_unique:
        print("   Index contains duplicates.\n")
        duplicate_indices = df.index[df.index.duplicated()].unique()
        print(f"   Duplicate index values: {list(duplicate_indices)}\n")
        print("   Consider resetting the index using `df.reset_index()` or aggregating rows by index.\n")
    else:
        print("   Index is unique.\n")

    # Check if the DataFrame has a MultiIndex
    if isinstance(df.index, pd.MultiIndex):
        print("   Checking for missing levels in MultiIndex...\n")
    
        # Convert MultiIndex to a DataFrame for inspection
        index_frame = df.index.to_frame()

        # Identify missing levels in each level of the MultiIndex
        missing_levels = index_frame.isnull().any(axis=0)
        if missing_levels.any():
            missing_levels_list = missing_levels[missing_levels].index.tolist()
            print(f"   Missing levels detected in MultiIndex: {missing_levels_list}\n")
        else:
            print("   No missing levels in MultiIndex.\n")
    else:
        print("   Index is not a MultiIndex. No hierarchical issues detected.\n")

def analyze_df_columns(merged_dataset_file=None, original_dataset_files=None):
    """
    Analyzes datasets dynamically.
    
    This function:
      - Optionally loads and analyzes a merged dataset if a file is provided.
      - Loads each original dataset (from file names provided in a dictionary) if provided.
      - Prints the shape, columns, and preview (first few rows) of the merged dataset if available.
      - Prints the column names for each original dataset.
      - Computes and prints common columns across all original datasets.
      - Computes and prints columns that appear in at least two datasets.
      - Computes and prints unique columns in each dataset (columns that appear only in that one dataset).
    
    Parameters:
        merged_dataset_file (str, optional): File name (or path) of the merged dataset CSV.
                                               If None, the merged dataset analysis is skipped.
        original_dataset_files (dict, optional): Dictionary mapping dataset labels to their CSV file names.
                                                 For example: {"Enrollment": "enrollment.csv", "Attendance": "attendance.csv", ...}
                                                 If None or empty, the original dataset analysis is skipped.
    """
    # Process the merged dataset if provided
    if merged_dataset_file:
        try:
            merged_df = pd.read_csv(merged_dataset_file)
            print(f"Analyzing merged dataset: {merged_dataset_file}")
            print(f"Shape of merged dataset: {merged_df.shape}")
            print("Columns in merged dataset:", merged_df.columns.tolist())
            print("\nPreview of merged dataset:")
            print(merged_df.head())
        except Exception as e:
            print(f"Error loading merged dataset '{merged_dataset_file}': {e}")
    else:
        print("No merged dataset provided. Skipping merged dataset analysis.")
    
    # Process the original datasets if provided
    if original_dataset_files:
        datasets = {}
        for label, file in original_dataset_files.items():
            try:
                datasets[label] = pd.read_csv(file)
                print(f"\nLoaded dataset '{label}' from file: {file}")
            except Exception as e:
                print(f"Error loading dataset '{label}' from file '{file}': {e}")
        
        if datasets:
            # Display columns for each original dataset
            for label, df in datasets.items():
                print(f"\nAll Columns in the '{label}' dataset:")
                print(df.columns.tolist())
            
            # Create a list of sets containing column names from each dataset
            column_sets = [set(df.columns) for df in datasets.values()]
            
            # Compute common columns across all datasets (intersection)
            common_columns_all = set.intersection(*column_sets) if column_sets else set()
            print("\nCommon columns across all original datasets:")
            print(common_columns_all)
            
            # Compute columns that appear in at least two datasets
            col_counter = Counter()
            for col_set in column_sets:
                col_counter.update(col_set)
            common_columns_any = {col for col, count in col_counter.items() if count >= 2}
            print("\nColumns that appear in at least two original datasets:")
            print(common_columns_any)
            
            # Compute unique columns in each dataset (columns that only appear in that one dataset)
            for label, df in datasets.items():
                unique_cols = {col for col in df.columns if col_counter[col] == 1}
                print(f"\nUnique columns in the '{label}' dataset:")
                print(unique_cols)
        else:
            print("No valid original datasets loaded.")
    else:
        print("No original datasets provided. Skipping original datasets analysis.")

def analyze_df_features(df, dataset_name, feat_info=None, type_col='type', feature_col='attribute', ordinal_threshold=10):
    """
    Analyze a dataset and print summary information regarding feature types.
    
    This function prints the dataset name and shape, and then:
    
    - If a feature summary DataFrame (feat_info) is provided and contains the required columns,
      it uses that DataFrame to group features by type and print the counts and column names.
    - If no feat_info is provided, it classifies columns from the main DataFrame using heuristics:
        * Numeric: Columns with a numeric dtype and more than `ordinal_threshold` unique values.
        * Ordinal: Numeric columns with `ordinal_threshold` or fewer unique values.
        * Categorical: Columns with string or categorical dtype.
        * Mixed: Object columns with mixed underlying types.
    
    Parameters:
      - df (pd.DataFrame): The main dataset to analyze.
      - dataset_name (str): A name for the dataset (for display purposes).
      - feat_info (pd.DataFrame, optional): A separate feature summary DataFrame.
      - type_col (str, optional): Column name that holds the feature type in feat_info (default 'type').
      - feature_col (str, optional): Column name that holds the feature name in feat_info (default 'attribute').
      - ordinal_threshold (int, optional): Maximum number of unique values for a numeric column to be considered ordinal.
    """
    # Print basic dataset info
    print(f"\033[1m\nDataset: {dataset_name}\033[0m")
    print(f"\033[1mShape: {df.shape[0]} rows, {df.shape[1]} columns\033[0m\n")
    
    print("\033[1m\nHow many features are there of each data type?\033[0m\n")
    
    # Expected feature types in order
    expected_types = ['numeric', 'ordinal', 'categorical', 'mixed']
    
    def print_feature_info(feature_dict):
        for f_type in expected_types:
            features = feature_dict.get(f_type, [])
            print(f"\033[1mNumber of {f_type} features: {len(features)}\033[0m")
            print(f"Columns: {features}\n")
    
    # If a feature summary DataFrame is provided, use it.
    if feat_info is not None:
        if type_col in feat_info.columns and feature_col in feat_info.columns:
            feature_dict = {ft: feat_info.loc[feat_info[type_col] == ft, feature_col].tolist() for ft in expected_types}
            print("\033[1mFeature counts by data type (from provided feat_info):\033[0m\n")
            print_feature_info(feature_dict)
        else:
            print(f"Error: The provided feat_info DataFrame must contain the columns '{type_col}' and '{feature_col}'.")
    
    # Otherwise, classify columns in df using heuristics.
    else:
        feature_dict = {'numeric': [], 'ordinal': [], 'categorical': [], 'mixed': []}
        
        for col in df.columns:
            col_data = df[col]
            # Check for numeric columns
            if pd.api.types.is_numeric_dtype(col_data):
                # Use the number of unique values to differentiate ordinal vs numeric
                if col_data.nunique() <= ordinal_threshold:
                    feature_dict['ordinal'].append(col)
                else:
                    feature_dict['numeric'].append(col)
            # Check for categorical columns (string or categorical dtype)
            elif pd.api.types.is_string_dtype(col_data) or pd.api.types.is_categorical_dtype(col_data):
                feature_dict['categorical'].append(col)
            # For object types, see if the column contains mixed types.
            elif pd.api.types.is_object_dtype(col_data):
                unique_types = col_data.dropna().map(type).unique()
                if len(unique_types) > 1:
                    feature_dict['mixed'].append(col)
                else:
                    feature_dict['categorical'].append(col)
            else:
                # Fallback: classify as categorical
                feature_dict['categorical'].append(col)
                
        print("\033[1mFeature counts by data type (from main DataFrame using heuristics):\033[0m\n")
        print_feature_info(feature_dict)
    
    print("\n\033[1;32mAll requests processed successfully!\033[0m\n")

def assess_categorical_variables(main_df, dataset_name, feat_info=None, 
                                 type_col='type', feature_col='attribute'):
    """
    Assess categorical variables in a dataset.

    This function prints basic dataset information and then evaluates categorical
    variables to determine which are binary, which are multi-level, and which need 
    re-encoding (i.e. multi-level categoricals).

    If a feature summary DataFrame (feat_info) is provided and contains the required 
    columns, the function will use it to identify categorical features. Otherwise, it 
    assumes that the main DataFrame already includes the categorical features of interest.

    Parameters:
      - main_df (pd.DataFrame): The primary dataset for assessment.
      - dataset_name (str): Name of the dataset (for display purposes).
      - feat_info (pd.DataFrame, optional): A separate feature summary DataFrame.
      - type_col (str): Column name that holds the feature type in feat_info. Default is 'type'.
      - feature_col (str): Column name that holds the feature name in feat_info. Default is 'attribute'.
    """
    # Print dataset name and shape
    print(f"\033[1m\nDataset: {dataset_name}\033[0m")
    print(f"\033[1mShape: {main_df.shape[0]} rows, {main_df.shape[1]} columns\033[0m\n")
    
    # Determine categorical features:
    if feat_info is not None:
        if type_col in feat_info.columns and feature_col in feat_info.columns:
            categorical_features = feat_info.loc[feat_info[type_col] == 'categorical', feature_col].tolist()
            print(f"\033[1mFeature summary loaded from provided feat_info.\033[0m\n")
        else:
            print(f"Error: The provided feat_info DataFrame must contain columns '{type_col}' and '{feature_col}'.")
            return
    else:
        # Fallback: if no feat_info is provided, assume all objects or categorical columns are categorical
        categorical_features = main_df.select_dtypes(include=['object', 'category']).columns.tolist()
        print(f"\033[1mNo feature summary provided; using object and categorical columns from the main DataFrame.\033[0m\n")
    
    # Initialize lists for binary, multi-level, and re-encoding candidates
    binary_categoricals = []
    multilevel_categoricals = []
    to_be_reencoded = []
    
    # Assess each categorical feature
    for feature in categorical_features:
        if feature in main_df.columns:
            unique_vals = main_df[feature].nunique()
            if unique_vals == 2:
                binary_categoricals.append(feature)
            elif unique_vals > 2:
                multilevel_categoricals.append(feature)
                # Here, we mark multi-level categoricals as needing re-encoding.
                to_be_reencoded.append(feature)
        else:
            print(f"Warning: Feature '{feature}' not found in main DataFrame.")
    
    # Print the assessment results
    print("\n\033[1mCategorical Variable Assessment\033[0m")
    print(f"\n\033[1mBinary Categorical Variables (2 unique values):\033[0m")
    print(binary_categoricals)
    
    print(f"\n\033[1mMulti-level Categorical Variables (3+ unique values):\033[0m")
    print(multilevel_categoricals)
    
    print(f"\n\033[1mVariables Needing Re-encoding (multi-level categorical):\033[0m")
    print(to_be_reencoded)
    
    print("\033[1;32m\nAll requests processed successfully!\033[0m\n")

# Call functions for quality and tidiness inspection

# Load the dataset - 1_assessment.csv
df = pd.read_csv("1_assessment.csv")
dataset_name = "1_assessment.csv"

# Call the analyze_dataset function
analyze_dataset(df, dataset_name)

# Check tidiness issues 
tidiness_issue_inspection(df, dataset_name)

# Call the function for Quality Issues Inspection 1
quality_issue_inspection_part1(df, dataset_name)

# Call the function for Quality Issues Inspection 2
quality_issue_inspection_part2(df, dataset_name)


Dataset: 1_assessment.csv 

Total number of rows: 1943310 
Total number of columns: 18 


First 3 rows of the dataset:


All Columns in the Dataset:
['School Year', 'District Code', 'District', 'School Code', 'Organization', 'Assessment Name', 'ContentArea', 'Race', 'Gender', 'Grade', 'SpecialDemo', 'Geography', 'SubGroup', 'RowStatus', 'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg']

No repeated columns found.


 ---------------------- Tidiness Issues Inspection ---------------------- 

Dataset: 1_assessment.csv 


1. Column Headers as Values:
   None

2. Wide Format Instead of Long Format:
   None

3. Poorly Named Columns:
   None

4. Inconsistent Data Structure:
   None

5. Unnecessary Columns:
   Unnecessary columns detected: ['Geography']

6. Mixed Granularity:
   Potential mixed granularity detected in columns: ['School Year', 'District Code', 'School Code', 'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg']

7. Repeated Index or Hierarchical Issues:
   Index is unique.

   Index is not a MultiIndex. No hierarchical issues detected.


 ---------------------- Quality Issues Inspection Part 1 ---------------------- 

Dataset: 1_assessment.csv 


1. Missing Values:
Missing Values Summary (Columns):
                 Missing Values  Percentage
School Year                   0        0.00
District Code                 0        0.00
District                      0        0.00
School Code                   0        0.00
Organization                  0        0.00
Assessment Name               0        0.00
ContentArea                   0        0.00
Race                          0        0.00
Gender                        0        0.00
Grade                         0        0.00
SpecialDemo                   0        0.00
Geography                     0        0.00
SubGroup                      0        0.00
RowStatus                     0        0.00
Tested                  1410496       72.58
Proficient              1583473       81.48
PctProficient           1583473       81.48
ScaleScoreAvg           1598977       82.28 

Missing Values Summary (Rows):
     Total rows with missing values: 1598977 (82.28%)
     Rows with high missing values (> 30%): 0 (0.00%)
     Rows with low missing values (0% - 30%): 1598977 (82.28%)
     Rows with no missing values: 344333 (17.72%)

2. Duplicate Rows: None

3. Invalid and/or Negative Values: None

4. Outliers
Outliers detected in 'School Year': 101178 rows
Outliers detected in 'District Code': 359124 rows
Outliers detected in 'School Code': 36017 rows
Outliers detected in 'Tested': 56814 rows
Outliers detected in 'Proficient': 38799 rows
Outliers detected in 'PctProficient': 675 rows

Columns with outliers: ['School Year', 'District Code', 'School Code', 'Tested', 'Proficient', 'PctProficient']

Total number of outliers across all columns: 592607
Percentage of total outliers in the dataset: 4.36%


5. Redundant Leading/Trailing Spaces:
   None

6. Column Entries with Mixed Formats:
   None

7. Non-Numeric Data in Numeric Columns:
       Column Non-Numeric Data   Count
       Tested            [nan] 1410496
   Proficient            [nan] 1583473
PctProficient            [nan] 1583473
ScaleScoreAvg            [nan] 1598977

8. Numeric Data in Non-Numeric Columns:
   None


 ---------------------- Quality Issues Inspection Part 2 ---------------------- 

Dataset: 1_assessment.csv 


1. Summary of Special Characters:
         Column Special Characters or Formatting Issues
       District                           &, (, ), -, .
   Organization                           &, (, ), -, .
Assessment Name                                 (, ), -
           Race                                    -, /
          Grade                                 (, ), -
    SpecialDemo                              (, ), ,, -
       SubGroup                           (, ), ,, -, /

Detailed special characters (delete if not needed) saved to '1_assessment.csv_special_characters_details.csv'.

Summary of special characters (delete if not needed) saved to '1_assessment.csv_special_characters_summary.csv'.


2. Columns with Inconsistent Units and Recommendations:
         Column                                 Recommendation
       District              Reencode (Standardize categories)
   Organization Reengineer (Separate numeric values and units)
Assessment Name              Reencode (Standardize categories)
    ContentArea              Reencode (Standardize categories)
           Race              Reencode (Standardize categories)
         Gender              Reencode (Standardize categories)
          Grade Reengineer (Separate numeric values and units)
    SpecialDemo              Reencode (Standardize categories)
      Geography              Reencode (Standardize categories)
       SubGroup Reengineer (Separate numeric values and units)
      RowStatus              Reencode (Standardize categories)


3. Unique Categorical Values:

Unique categorical values (delete if not needed) saved to '1_assessment.csv_unique_categorical_values.csv'.


4. Analyzing Dataset: '1_assessment.csv' for data types and classification

# ==================================================
# Call functions for quality and tidiness inspection

# Load the dataset - 1_discipline.csv
df = pd.read_csv("1_discipline.csv")
dataset_name = "1_discipline.csv"

# Call the analyze_dataset function
analyze_dataset(df, dataset_name)

# Check tidiness issues 
tidiness_issue_inspection(df, dataset_name)

# Call the function for Quality Issues Inspection 1
quality_issue_inspection_part1(df, dataset_name)

# Call the function for Quality Issues Inspection 2
quality_issue_inspection_part2(df, dataset_name)


Dataset: 1_discipline.csv 

Total number of rows: 1368553 
Total number of columns: 18 


First 3 rows of the dataset:


All Columns in the Dataset:
['School Year', 'District Code', 'District', 'School Code', 'Organization', 'Race', 'Gender', 'Grade', 'SpecialDemo', 'Geography', 'SubGroup', 'Category', 'RowStatus', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration']

No repeated columns found.


 ---------------------- Tidiness Issues Inspection ---------------------- 

Dataset: 1_discipline.csv 


1. Column Headers as Values:
   None

2. Wide Format Instead of Long Format:
   None

3. Poorly Named Columns:
   None

4. Inconsistent Data Structure:
   None

5. Unnecessary Columns:
   Unnecessary columns detected: ['Geography']

6. Mixed Granularity:
   Potential mixed granularity detected in columns: ['School Year', 'District Code', 'School Code', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration']

7. Repeated Index or Hierarchical Issues:
   Index is unique.

   Index is not a MultiIndex. No hierarchical issues detected.


 ---------------------- Quality Issues Inspection Part 1 ---------------------- 

Dataset: 1_discipline.csv 


1. Missing Values:
Missing Values Summary (Columns):
               Missing Values  Percentage
School Year                 0        0.00
District Code               0        0.00
District                    0        0.00
School Code                 0        0.00
Organization                0        0.00
Race                        0        0.00
Gender                      0        0.00
Grade                       0        0.00
SpecialDemo                 0        0.00
Geography                   0        0.00
SubGroup                    0        0.00
Category                    0        0.00
RowStatus                   0        0.00
Students               808561       59.08
Enrollment             569928       41.64
PctEnrollment         1019155       74.47
Incidents              808561       59.08
AvgDuration            808561       59.08 

Missing Values Summary (Rows):
     Total rows with missing values: 1019155 (74.47%)
     Rows with high missing values (> 30%): 0 (0.00%)
     Rows with low missing values (0% - 30%): 1019155 (74.47%)
     Rows with no missing values: 349398 (25.53%)

2. Duplicate Rows: None

3. Invalid and/or Negative Values: None

4. Outliers
Outliers detected in 'District Code': 180852 rows
Outliers detected in 'School Code': 19178 rows
Outliers detected in 'Students': 66037 rows
Outliers detected in 'Enrollment': 89787 rows
Outliers detected in 'PctEnrollment': 8053 rows
Outliers detected in 'Incidents': 64683 rows
Outliers detected in 'AvgDuration': 41412 rows

Columns with outliers: ['District Code', 'School Code', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration']

Total number of outliers across all columns: 470002
Percentage of total outliers in the dataset: 4.29%


5. Redundant Leading/Trailing Spaces:
   None

6. Column Entries with Mixed Formats:
   None

7. Non-Numeric Data in Numeric Columns:
       Column Non-Numeric Data   Count
     Students            [nan]  808561
   Enrollment            [nan]  569928
PctEnrollment            [nan] 1019155
    Incidents            [nan]  808561
  AvgDuration            [nan]  808561

8. Numeric Data in Non-Numeric Columns:
   None


 ---------------------- Quality Issues Inspection Part 2 ---------------------- 

Dataset: 1_discipline.csv 


1. Summary of Special Characters:
      Column Special Characters or Formatting Issues
    District                           &, (, ), -, .
Organization                           &, (, ), -, .
        Race                                    -, /
       Grade                                       -
 SpecialDemo                              (, ), ,, -
    SubGroup                           (, ), ,, -, /
    Category                              (, ), ,, -

Detailed special characters (delete if not needed) saved to '1_discipline.csv_special_characters_details.csv'.

Summary of special characters (delete if not needed) saved to '1_discipline.csv_special_characters_summary.csv'.


2. Columns with Inconsistent Units and Recommendations:
      Column                                 Recommendation
    District              Reencode (Standardize categories)
Organization              Reencode (Standardize categories)
        Race              Reencode (Standardize categories)
      Gender              Reencode (Standardize categories)
       Grade Reengineer (Separate numeric values and units)
 SpecialDemo              Reencode (Standardize categories)
   Geography              Reencode (Standardize categories)
    SubGroup Reengineer (Separate numeric values and units)
    Category              Reencode (Standardize categories)
   RowStatus              Reencode (Standardize categories)


3. Unique Categorical Values:

Unique categorical values (delete if not needed) saved to '1_discipline.csv_unique_categorical_values.csv'.


4. Analyzing Dataset: '1_discipline.csv' for data types and classification

# Load the dataset
discipline = pd.read_csv('discipline.csv')

# Rename column from 'Rowstatus' to 'RowStatus'
discipline.rename(columns={'Rowstatus': 'RowStatus'}, inplace=True)

# Save the updated dataset (overriding the original file)
discipline.to_csv('discipline.csv', index=False)

# Confirm the change
print("Updated Column Names:", discipline.columns)

Updated Column Names: Index(['School Year', 'District Code', 'District', 'School Code',
       'Organization', 'Race', 'Gender', 'Grade', 'SpecialDemo', 'Geography',
       'SubGroup', 'Category', 'RowStatus', 'Students', 'Enrollment',
       'PctEnrollment', 'Incidents', 'AvgDuration'],
      dtype='object')

# Call the funcions to check common, unique columns

merged_dataset_file = ".csv"
original_dataset_files = {
    "assessment": "1_assessment.csv",
    "discipline": "1_discipline.csv",
}

analyze_df_columns(original_dataset_files=original_dataset_files)

No merged dataset provided. Skipping merged dataset analysis.

Loaded dataset 'assessment' from file: 1_assessment.csv

Loaded dataset 'discipline' from file: 1_discipline.csv

All Columns in the 'assessment' dataset:
['School Year', 'District Code', 'District', 'School Code', 'Organization', 'Assessment Name', 'ContentArea', 'Race', 'Gender', 'Grade', 'SpecialDemo', 'Geography', 'SubGroup', 'RowStatus', 'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg']

All Columns in the 'discipline' dataset:
['School Year', 'District Code', 'District', 'School Code', 'Organization', 'Race', 'Gender', 'Grade', 'SpecialDemo', 'Geography', 'SubGroup', 'Category', 'RowStatus', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration']

Common columns across all original datasets:
{'District Code', 'District', 'Organization', 'Grade', 'School Code', 'Race', 'Gender', 'School Year', 'SpecialDemo', 'SubGroup', 'RowStatus', 'Geography'}

Columns that appear in at least two original datasets:
{'District Code', 'District', 'Organization', 'Grade', 'School Code', 'Race', 'Gender', 'School Year', 'SpecialDemo', 'SubGroup', 'RowStatus', 'Geography'}

Unique columns in the 'assessment' dataset:
{'Assessment Name', 'Proficient', 'ScaleScoreAvg', 'Tested', 'ContentArea', 'PctProficient'}

Unique columns in the 'discipline' dataset:
{'Enrollment', 'PctEnrollment', 'Category', 'AvgDuration', 'Students', 'Incidents'}

# Load your dataset
df = pd.read_csv("1_assessment.csv")

df.loc[df.sample(frac=0.2).index, 'ScaleScoreAvg'] = None

# Create a pivot table that counts the missing values in ScaleScoreAvg for each Grade and School Year
heatmap_data_missing = df.pivot_table(
    values='ScaleScoreAvg', 
    index='Grade', 
    columns='School Year', 
    aggfunc=lambda x: x.isnull().sum()
)

plt.figure(figsize=(10, 6))
sns.heatmap(
    heatmap_data_missing, 
    annot=True, 
    fmt='.0f', 
    cmap='Reds', 
    cbar_kws={'label': 'Missing Count'}
)
plt.title("\n\nHeatmap of Missing ScaleScoreAvg Values Across Grades and School Years", fontsize=16)
plt.xlabel("School Year", fontsize=12)
plt.ylabel("Grade", fontsize=12)
plt.tight_layout()
plt.show()

print("- This heatmap shows the distribution of missing values in ScaleScoreAvg across grades and school years using shades of red.")
print("- Darker shades indicate a higher count of missing values, highlighting potential data quality issues.")

- This heatmap shows the distribution of missing values in ScaleScoreAvg across grades and school years using shades of red.
- Darker shades indicate a higher count of missing values, highlighting potential data quality issues.

# Load your dataset
df = pd.read_csv("1_discipline.csv")

df.loc[df.sample(frac=0.2).index, 'Incidents'] = None

# Create a pivot table that counts the missing values for each Grade and School Year
heatmap_data_missing = df.pivot_table(
    values='Incidents', 
    index='Grade', 
    columns='School Year', 
    aggfunc=lambda x: x.isnull().sum()
)

plt.figure(figsize=(10, 6))
sns.heatmap(
    heatmap_data_missing, 
    annot=True, 
    fmt='.0f', 
    cmap='Reds', 
    cbar_kws={'label': 'Missing Count'}
)
plt.title("\n\nHeatmap of Missing Incidents Values Across Grades and School Years", fontsize=16)
plt.xlabel("School Year", fontsize=12)
plt.ylabel("Grade", fontsize=12)
plt.tight_layout()
plt.show()

print("- This heatmap shows the distribution of missing values in Incidents across grades and school years using shades of red.")
print("- Darker shades indicate a higher count of missing values, highlighting potential data quality issues.")

- This heatmap shows the distribution of missing values in Incidents across grades and school years using shades of red.
- Darker shades indicate a higher count of missing values, highlighting potential data quality issues.

# Load datasets
assessment = pd.read_csv('1_assessment.csv')
discipline = pd.read_csv('1_discipline.csv')

# Function to standardize the 'School Year' column to a 4-digit integer format
def standardize_school_year(df, column_name='School Year'):
    # Convert to string and extract the first four digits as the school year
    df[column_name] = df[column_name].astype(str).str.extract(r'(\d{4})')[0].astype(float)
    
    # Drop rows with invalid or missing years
    df.dropna(subset=[column_name], inplace=True)
    
    # Convert to integer type
    df[column_name] = df[column_name].astype(int)
    
    return df

# Standardize the 'School Year' column in both datasets
assessment = standardize_school_year(assessment, 'School Year')
discipline = standardize_school_year(discipline, 'School Year')

# Drop rows with years 2015, 2016, 2017, 2018, and 2024
years_to_drop = [2015, 2016, 2017, 2018, 2024]
assessment = assessment[~assessment['School Year'].isin(years_to_drop)]
discipline = discipline[~discipline['School Year'].isin(years_to_drop)]

# Display the unique school years to verify
print("Unique 'School Year' values in Assessment dataset:", assessment['School Year'].unique())
print("Unique 'School Year' values in Discipline dataset:", discipline['School Year'].unique())

# Save cleaned datasets
assessment.to_csv('1_assessment_cleaned.csv', index=False)
discipline.to_csv('1_discipline_cleaned.csv', index=False)

Unique 'School Year' values in Assessment dataset: [2019 2023 2020 2021 2022]
Unique 'School Year' values in Discipline dataset: [2022 2021 2019 2020 2023]

#  Checking columns for both original datasets
#  Datasets involve in this cell and process:
#            1. assessment.csv
#            2. discipline.csv


# Load the datasets
assessment = pd.read_csv('1_assessment_cleaned.csv')
discipline = pd.read_csv('1_discipline_cleaned.csv')

# Get the unique column names from both datasets
assessment_columns = set(assessment.columns)
discipline_columns = set(discipline.columns)

# Display unique column names
print("\n\nUnique columns in assessment Dataset:\n", assessment_columns)
print("\nUnique columns in Discipline Dataset:\n", discipline_columns)

# Columns in both datasets
common_columns = assessment_columns.intersection(discipline_columns)
print("\nColumns common in both datasets:\n", common_columns)

# Columns unique to each dataset
assessment_unique = assessment_columns - discipline_columns
discipline_unique = discipline_columns - assessment_columns

print("\nColumns unique to Assessment Dataset:\n", assessment_unique)
print("\nColumns unique to Discipline Dataset:\n", discipline_unique)


Unique columns in assessment Dataset:
 {'Assessment Name', 'Organization', 'School Code', 'ScaleScoreAvg', 'Grade', 'District', 'Proficient', 'School Year', 'District Code', 'Race', 'ContentArea', 'RowStatus', 'Gender', 'Geography', 'Tested', 'SubGroup', 'SpecialDemo', 'PctProficient'}

Unique columns in Discipline Dataset:
 {'Organization', 'School Code', 'Grade', 'District', 'Enrollment', 'School Year', 'District Code', 'Race', 'RowStatus', 'Gender', 'Geography', 'Category', 'SubGroup', 'Incidents', 'SpecialDemo', 'AvgDuration', 'Students', 'PctEnrollment'}

Columns common in both datasets:
 {'Organization', 'School Code', 'Grade', 'District', 'School Year', 'District Code', 'Race', 'RowStatus', 'Gender', 'Geography', 'SubGroup', 'SpecialDemo'}

Columns unique to Assessment Dataset:
 {'Assessment Name', 'ScaleScoreAvg', 'Proficient', 'ContentArea', 'Tested', 'PctProficient'}

Columns unique to Discipline Dataset:
 {'Enrollment', 'Category', 'Incidents', 'AvgDuration', 'Students', 'PctEnrollment'}

# ==================================================
#  Merging/Combining two datasets
#  Datasets involve in this cell and process:
#            1. assessment.csv
#            2. discipline.csv
# ==================================================


# Load the datasets
assessment = pd.read_csv('1_assessment_cleaned.csv')
discipline = pd.read_csv('1_discipline_cleaned.csv')

# 1. Rename the column 'Rowstatus' in dataset 'discipline.csv' into 'RowStatus'. This part is only a precaution, just in case the column name
#    did not work.
discipline.rename(columns={'Rowstatus': 'RowStatus'}, inplace=True)

# 2. Merging 2 datasets using join
# 2.a Identify common columns and unique columns
common_columns = ['SpecialDemo', 'Geography', 'School Year', 'District', 'Organization', 
                  'RowStatus', 'SubGroup', 'Gender', 'Race', 'School Code', 'District Code', 'Grade']

unique_assessment_columns = ['PctProficient', 'Proficient', 'ScaleScoreAvg', 'Assessment Name', 
                             'Tested', 'ContentArea']

unique_discipline_columns = ['Students', 'Enrollment', 'Incidents', 'PctEnrollment', 
                             'Category', 'AvgDuration']

# 2.b Perform the inner join on common columns
merged_df = pd.merge(assessment, discipline, on=list(common_columns), how='inner')

# 2.c Add the unique columns from the Assessment dataset
for col in unique_assessment_columns:
    if col in assessment.columns:
        merged_df[col] = assessment[col]

# 2.d Add the unique columns from the Discipline dataset
for col in unique_discipline_columns:
    if col in discipline.columns:
        merged_df[col] = discipline[col]


# Save the result to a CSV file
output_file = '2_merged.csv'
merged_df.to_csv(output_file, index=False)

print(f"\n\nMerged dataset saved to '{output_file}'.\n\n")


Merged dataset saved to '2_merged.csv'.

# ==================================================
# Call functions for quality and tidiness inspection
#    Dataset - merged.csv
# ==================================================
# Note: Call these functions in this specific order


# Load the dataset - final_merged_df
df = pd.read_csv("2_merged.csv")
dataset_name = "2_merged.csv"

# Call the analyze_dataset function
analyze_dataset(df, dataset_name)

# Check tidiness issues 
tidiness_issue_inspection(df, dataset_name)

# Call the function for Quality Issues Inspection 1
quality_issue_inspection_part1(df, dataset_name)

# Call the function for Quality Issues Inspection 2
quality_issue_inspection_part2(df, dataset_name)


Dataset: 2_merged.csv 

Total number of rows: 697305 
Total number of columns: 24 


First 3 rows of the dataset:


All Columns in the Dataset:
['School Year', 'District Code', 'District', 'School Code', 'Organization', 'Assessment Name', 'ContentArea', 'Race', 'Gender', 'Grade', 'SpecialDemo', 'Geography', 'SubGroup', 'RowStatus', 'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg', 'Category', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration']

No repeated columns found.


 ---------------------- Tidiness Issues Inspection ---------------------- 

Dataset: 2_merged.csv 


1. Column Headers as Values:
   None

2. Wide Format Instead of Long Format:
   None

3. Poorly Named Columns:
   None

4. Inconsistent Data Structure:
   None

5. Unnecessary Columns:
   Unnecessary columns detected: ['Geography']

6. Mixed Granularity:
   Potential mixed granularity detected in columns: ['School Year', 'District Code', 'School Code', 'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration']

7. Repeated Index or Hierarchical Issues:
   Index is unique.

   Index is not a MultiIndex. No hierarchical issues detected.


 ---------------------- Quality Issues Inspection Part 1 ---------------------- 

Dataset: 2_merged.csv 


1. Missing Values:
Missing Values Summary (Columns):
                 Missing Values  Percentage
School Year                   0        0.00
District Code                 0        0.00
District                      0        0.00
School Code                   0        0.00
Organization                  0        0.00
Assessment Name               0        0.00
ContentArea                   0        0.00
Race                          0        0.00
Gender                        0        0.00
Grade                         0        0.00
SpecialDemo                   0        0.00
Geography                     0        0.00
SubGroup                      0        0.00
RowStatus                     0        0.00
Tested                   543099       77.89
Proficient               592970       85.04
PctProficient            592970       85.04
ScaleScoreAvg            593972       85.18
Category                      0        0.00
Students                 420449       60.30
Enrollment               341636       48.99
PctEnrollment            547700       78.55
Incidents                420449       60.30
AvgDuration              420449       60.30 

Missing Values Summary (Rows):
     Total rows with missing values: 674408 (96.72%)
     Rows with high missing values (> 30%): 344986 (49.47%)
     Rows with low missing values (0% - 30%): 329422 (47.24%)
     Rows with no missing values: 22897 (3.28%)

2. Duplicate Rows: Number of duplicate rows: 47468
3. Invalid and/or Negative Values: None

4. Outliers
Outliers detected in 'District Code': 78991 rows
Outliers detected in 'School Code': 9621 rows
Outliers detected in 'Tested': 16403 rows
Outliers detected in 'Proficient': 11606 rows
Outliers detected in 'PctProficient': 307 rows
Outliers detected in 'Students': 31445 rows
Outliers detected in 'Enrollment': 39644 rows
Outliers detected in 'PctEnrollment': 3518 rows
Outliers detected in 'Incidents': 30984 rows
Outliers detected in 'AvgDuration': 17457 rows

Columns with outliers: ['District Code', 'School Code', 'Tested', 'Proficient', 'PctProficient', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration']

Total number of outliers across all columns: 239976
Percentage of total outliers in the dataset: 2.87%


5. Redundant Leading/Trailing Spaces:
   None

6. Column Entries with Mixed Formats:
   None

7. Non-Numeric Data in Numeric Columns:
       Column Non-Numeric Data  Count
       Tested            [nan] 543099
   Proficient            [nan] 592970
PctProficient            [nan] 592970
ScaleScoreAvg            [nan] 593972
     Students            [nan] 420449
   Enrollment            [nan] 341636
PctEnrollment            [nan] 547700
    Incidents            [nan] 420449
  AvgDuration            [nan] 420449

8. Numeric Data in Non-Numeric Columns:
   None


 ---------------------- Quality Issues Inspection Part 2 ---------------------- 

Dataset: 2_merged.csv 


1. Summary of Special Characters:
         Column Special Characters or Formatting Issues
       District                              (, ), -, .
   Organization                              (, ), -, .
Assessment Name                                 (, ), -
           Race                                    -, /
    SpecialDemo                                       -
       SubGroup                                    -, /
       Category                              (, ), ,, -

Detailed special characters (delete if not needed) saved to '2_merged.csv_special_characters_details.csv'.

Summary of special characters (delete if not needed) saved to '2_merged.csv_special_characters_summary.csv'.


2. Columns with Inconsistent Units and Recommendations:
         Column                                 Recommendation
       District              Reencode (Standardize categories)
   Organization              Reencode (Standardize categories)
Assessment Name              Reencode (Standardize categories)
    ContentArea              Reencode (Standardize categories)
           Race              Reencode (Standardize categories)
         Gender              Reencode (Standardize categories)
          Grade Reengineer (Separate numeric values and units)
    SpecialDemo              Reencode (Standardize categories)
      Geography              Reencode (Standardize categories)
       SubGroup Reengineer (Separate numeric values and units)
      RowStatus              Reencode (Standardize categories)
       Category              Reencode (Standardize categories)


3. Unique Categorical Values:

Unique categorical values (delete if not needed) saved to '2_merged.csv_unique_categorical_values.csv'.


4. Analyzing Dataset: '2_merged.csv' for data types and classification

### Call the function to assess categorical class

# Load the  dataset
main_df = pd.read_csv("2_merged.csv", low_memory=False)
dataset_name = "2_merged.csv"

# other way of using this function: Without a feature summary DataFrame
# assess_categorical_variables(main_df, dataset_name)


Dataset: 2_merged.csv
Shape: 697305 rows, 24 columns

No feature summary provided; using object and categorical columns from the main DataFrame.


Categorical Variable Assessment

Binary Categorical Variables (2 unique values):
['RowStatus']

Multi-level Categorical Variables (3+ unique values):
['District', 'Organization', 'Assessment Name', 'ContentArea', 'Race', 'Gender', 'Grade', 'SpecialDemo', 'SubGroup', 'Category']

Variables Needing Re-encoding (multi-level categorical):
['District', 'Organization', 'Assessment Name', 'ContentArea', 'Race', 'Gender', 'Grade', 'SpecialDemo', 'SubGroup', 'Category']

All requests processed successfully!

df = pd.read_csv("2_merged.csv")

# Determine missing data percentage in each column
missing_data_percent = (df.isnull().sum() / len(df)) * 100
threshold = 30  # Set threshold for excessive missing data

# Define thresholds for high, low, and no missing data
high_missing_threshold = 30  # Columns with more than 30% missing data
low_missing_threshold = 1  # Columns with 1% to 30% missing data

total_columns = df.shape[1]

# Identify columns for each subset
columns_high_missing = missing_data_percent[missing_data_percent > high_missing_threshold].index
columns_low_missing = missing_data_percent[(missing_data_percent <= high_missing_threshold) & (missing_data_percent > low_missing_threshold)].index
columns_no_missing = missing_data_percent[missing_data_percent == 0].index

# Create subsets of the dataset based on columns with different levels of missing data
subset_high_missing_columns = df[columns_high_missing]
subset_low_missing_columns = df[columns_low_missing]
subset_no_missing_columns = df[columns_no_missing]

# Count the number of columns in each subset
num_columns_high_missing = len(columns_high_missing)
num_columns_low_missing = len(columns_low_missing)
num_columns_no_missing = len(columns_no_missing)

# Calculate percentages of each subset relative to the total columns
percent_columns_high_missing = (num_columns_high_missing / total_columns) * 100
percent_columns_low_missing = (num_columns_low_missing / total_columns) * 100
percent_columns_no_missing = (num_columns_no_missing / total_columns) * 100


# Print the count of columns and save messages
print(f"\033[1m\n\nColumns with high missing values: These are columns with more than {threshold}% missing data.\033[0m")
print(f"\033[1mColumns with high missing values: {num_columns_high_missing}.\033[0m")
print(columns_high_missing)
print(f"These columns (columns with high missing values) make up {percent_columns_high_missing:.2f}% of the entire dataset.")

print(f"\033[1m\nColumns with low missing values: These are columns with 1% to {threshold}% missing data.\033[0m")
print(f"\033[1mColumns with low missing values: {num_columns_low_missing}.\033[0m")
print(columns_low_missing)
print(f"These columns (columns with low missing values) make up {percent_columns_low_missing:.2f}% of the entire dataset.")

print(f"\033[1m\nColumns without missing values: These are columns with 0% missing data.\033[0m")
print(f"\033[1mColumns without missing values: {num_columns_no_missing}.\033[0m")
print(columns_no_missing)
print(f"These columns (columns without missing values) make up {percent_columns_no_missing:.2f}% of the entire dataset.")

print("\033[1;32m\n\nAll requests processed successfully!\033[0m\n\n")  # Success message


###################################################################

#  Calculate the percentage of missing data in each row
missing_data_percent_rows = (df.isnull().sum(axis=1) / df.shape[1]) * 100

#  Identify rows with more than a threshold percentage of missing data
threshold = 30  # Set threshold for excessive missing data
outlier_rows = missing_data_percent_rows[missing_data_percent_rows > threshold].index

#  Divide the data into two subsets based on row-wise missing values
subset_high_missing = df.loc[outlier_rows]  # Rows with many missing values
subset_low_missing = df.drop(outlier_rows)  # Rows with fewer missing values

# Identify rows with no missing values
subset_no_missing = df.dropna()  # Rows without any missing values

# Count the total number of rows (excluding the header row)
total_rows = df.shape[0]

# Count the number of rows in each subset
num_rows_high_missing = subset_high_missing.shape[0]   # Count rows with high missing values
num_rows_low_missing = subset_low_missing.shape[0]  # Count rows with low missing values
num_rows_no_missing = subset_no_missing.shape[0]  # Count rows without missing values

# Calculate percentages of each subset against the total rows
percent_high_missing = (num_rows_high_missing / total_rows) * 100
percent_low_missing = (num_rows_low_missing / total_rows) * 100
percent_no_missing = (num_rows_no_missing / total_rows) * 100

# Print the count of rows and save messages, along with percentages
print(f"\033[1m\n\nTotal rows in dataset: {total_rows}\033[0m")

print(f"\033[1m\nRows with high missing values: These are rows with more than {threshold}% missing data.\033[0m")
print(f"\033[1mRows with high missing values: {num_rows_high_missing} ({percent_high_missing:.2f}% of the entire dataset)'.\033[0m")

print(f"\033[1m\nRows with low missing values: These are rows with 1% to {threshold}% missing data.\033[0m")
print(f"\033[1mRows with low missing values: {num_rows_low_missing} ({percent_low_missing:.2f}% of the entire dataset)'.\033[0m")

print(f"\033[1m\nRows without missing values: These are rows with 0% missing data.\033[0m")
print(f"\033[1mRows without missing values: {num_rows_no_missing} ({percent_no_missing:.2f}% of the entire dataset)'.\033[0m\n")

print("\033[1;32m\n\nAll requests processed successfully!\033[0m\n\n")  # Success message



Columns with high missing values: These are columns with more than 30% missing data.
Columns with high missing values: 9.
Index(['Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg', 'Students',
       'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration'],
      dtype='object')
These columns (columns with high missing values) make up 37.50% of the entire dataset.

Columns with low missing values: These are columns with 1% to 30% missing data.
Columns with low missing values: 1.
Index(['Category'], dtype='object')
These columns (columns with low missing values) make up 4.17% of the entire dataset.

Columns without missing values: These are columns with 0% missing data.
Columns without missing values: 14.
Index(['School Year', 'District Code', 'District', 'School Code',
       'Organization', 'Assessment Name', 'ContentArea', 'Race', 'Gender',
       'Grade', 'SpecialDemo', 'Geography', 'SubGroup', 'RowStatus'],
      dtype='object')
These columns (columns without missing values) make up 58.33% of the entire dataset.


All requests processed successfully!


Total rows in dataset: 697305

Rows with high missing values: These are rows with more than 30% missing data.
Rows with high missing values: 344986 (49.47% of the entire dataset)'.

Rows with low missing values: These are rows with 1% to 30% missing data.
Rows with low missing values: 352319 (50.53% of the entire dataset)'.

Rows without missing values: These are rows with 0% missing data.
Rows without missing values: 22897 (3.28% of the entire dataset)'.


All requests processed successfully!

# Load the dataset - merged_cleaned
df = pd.read_csv("2_merged.csv")
dataset_name = "2_merged.csv"

# Call the analyze_dataset function
analyze_dataset(df, dataset_name)

# Check tidiness issues 
tidiness_issue_inspection(df, dataset_name)

# Call the function for Quality Issues Inspection 1
quality_issue_inspection_part1(df, dataset_name)

# Call the function for Quality Issues Inspection 2
quality_issue_inspection_part2(df, dataset_name)


Dataset: 2_merged.csv 

Total number of rows: 697305 
Total number of columns: 24 


First 3 rows of the dataset:


All Columns in the Dataset:
['School Year', 'District Code', 'District', 'School Code', 'Organization', 'Assessment Name', 'ContentArea', 'Race', 'Gender', 'Grade', 'SpecialDemo', 'Geography', 'SubGroup', 'RowStatus', 'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg', 'Category', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration']

No repeated columns found.


 ---------------------- Tidiness Issues Inspection ---------------------- 

Dataset: 2_merged.csv 


1. Column Headers as Values:
   None

2. Wide Format Instead of Long Format:
   None

3. Poorly Named Columns:
   None

4. Inconsistent Data Structure:
   None

5. Unnecessary Columns:
   Unnecessary columns detected: ['Geography']

6. Mixed Granularity:
   Potential mixed granularity detected in columns: ['School Year', 'District Code', 'School Code', 'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration']

7. Repeated Index or Hierarchical Issues:
   Index is unique.

   Index is not a MultiIndex. No hierarchical issues detected.


 ---------------------- Quality Issues Inspection Part 1 ---------------------- 

Dataset: 2_merged.csv 


1. Missing Values:
Missing Values Summary (Columns):
                 Missing Values  Percentage
School Year                   0        0.00
District Code                 0        0.00
District                      0        0.00
School Code                   0        0.00
Organization                  0        0.00
Assessment Name               0        0.00
ContentArea                   0        0.00
Race                          0        0.00
Gender                        0        0.00
Grade                         0        0.00
SpecialDemo                   0        0.00
Geography                     0        0.00
SubGroup                      0        0.00
RowStatus                     0        0.00
Tested                   543099       77.89
Proficient               592970       85.04
PctProficient            592970       85.04
ScaleScoreAvg            593972       85.18
Category                      0        0.00
Students                 420449       60.30
Enrollment               341636       48.99
PctEnrollment            547700       78.55
Incidents                420449       60.30
AvgDuration              420449       60.30 

Missing Values Summary (Rows):
     Total rows with missing values: 674408 (96.72%)
     Rows with high missing values (> 30%): 344986 (49.47%)
     Rows with low missing values (0% - 30%): 329422 (47.24%)
     Rows with no missing values: 22897 (3.28%)

2. Duplicate Rows: Number of duplicate rows: 47468
3. Invalid and/or Negative Values: None

4. Outliers
Outliers detected in 'District Code': 78991 rows
Outliers detected in 'School Code': 9621 rows
Outliers detected in 'Tested': 16403 rows
Outliers detected in 'Proficient': 11606 rows
Outliers detected in 'PctProficient': 307 rows
Outliers detected in 'Students': 31445 rows
Outliers detected in 'Enrollment': 39644 rows
Outliers detected in 'PctEnrollment': 3518 rows
Outliers detected in 'Incidents': 30984 rows
Outliers detected in 'AvgDuration': 17457 rows

Columns with outliers: ['District Code', 'School Code', 'Tested', 'Proficient', 'PctProficient', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration']

Total number of outliers across all columns: 239976
Percentage of total outliers in the dataset: 2.87%


5. Redundant Leading/Trailing Spaces:
   None

6. Column Entries with Mixed Formats:
   None

7. Non-Numeric Data in Numeric Columns:
       Column Non-Numeric Data  Count
       Tested            [nan] 543099
   Proficient            [nan] 592970
PctProficient            [nan] 592970
ScaleScoreAvg            [nan] 593972
     Students            [nan] 420449
   Enrollment            [nan] 341636
PctEnrollment            [nan] 547700
    Incidents            [nan] 420449
  AvgDuration            [nan] 420449

8. Numeric Data in Non-Numeric Columns:
   None


 ---------------------- Quality Issues Inspection Part 2 ---------------------- 

Dataset: 2_merged.csv 


1. Summary of Special Characters:
         Column Special Characters or Formatting Issues
       District                              (, ), -, .
   Organization                              (, ), -, .
Assessment Name                                 (, ), -
           Race                                    -, /
    SpecialDemo                                       -
       SubGroup                                    -, /
       Category                              (, ), ,, -

Detailed special characters (delete if not needed) saved to '2_merged.csv_special_characters_details.csv'.

Summary of special characters (delete if not needed) saved to '2_merged.csv_special_characters_summary.csv'.


2. Columns with Inconsistent Units and Recommendations:
         Column                                 Recommendation
       District              Reencode (Standardize categories)
   Organization              Reencode (Standardize categories)
Assessment Name              Reencode (Standardize categories)
    ContentArea              Reencode (Standardize categories)
           Race              Reencode (Standardize categories)
         Gender              Reencode (Standardize categories)
          Grade Reengineer (Separate numeric values and units)
    SpecialDemo              Reencode (Standardize categories)
      Geography              Reencode (Standardize categories)
       SubGroup Reengineer (Separate numeric values and units)
      RowStatus              Reencode (Standardize categories)
       Category              Reencode (Standardize categories)


3. Unique Categorical Values:

Unique categorical values (delete if not needed) saved to '2_merged.csv_unique_categorical_values.csv'.


4. Analyzing Dataset: '2_merged.csv' for data types and classification

#    Data cleaning:  2_merged.csv


df = pd.read_csv("2_merged.csv")

# Step 1: Drop Unnecessary Columns
print_bold("\n\nProcessing Step 1: Drop Unnecessary Columns...")
columns_to_keep = ['School Year', 'District Code', 'School Code', 'Assessment Name', 'ContentArea', 'Race', 'Gender', 'Grade', 'SpecialDemo', 'RowStatus', 'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg', 'Category', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration']
df = df[columns_to_keep]

# Step 2: Remove Duplicate Rows
print_bold("Processing Step 2: Remove Duplicate Rows...")
df = df.drop_duplicates()

# Step 3: Reengineer and reencode the `Grade` column
print_bold("Processing Step 3: Reengineer and reencode the `Grade` column...")
# Define grade mapping for standardization
grade_mapping = {
    "Kindergarten": 0,
    "1st Grade": 1,
    "2nd Grade": 2,
    "3rd Grade": 3,
    "4th Grade": 4,
    "5th Grade": 5,
    "6th Grade": 6,
    "7th Grade": 7,
    "8th Grade": 8,
    "9th Grade": 9,
    "10th Grade": 10,
    "11th Grade": 11,
    "12th Grade": 12,
    "Twelfth": 12,         # Standardizing "Twelfth" to 12
    "Pre-Kindergarten": -1,  # Use -1 to represent Pre-K
    "Early Childhood": -1,   # This column is already dropped. Kept for future datasets.
    "Birth to 2 Years": -1,  # No data will be added, as there are no reports for the included years. Kept for future datasets.
    "All Students": 13,      # Use 13 to denote "All Students".
    "Elementary (K-5)": 3,   # Approximate median numeric value; no data will be added, as there are no reports for the included years. Kept for future datasets.
    "Middle (6-8)": 7,       # Approximate median numeric value; no data will be added, as there are no reports for the included years. Kept for future datasets.
    "High (9-12)": 11        # Approximate median numeric value; no data will be added, as there are no reports for the included years. Kept for future datasets.
}

def assign_grade_group(grade):
    if pd.isna(grade):
        return 'Unknown'
    if grade == -1:
        return 'Pre-K'
    elif grade in [0, 1, 2, 3, 4, 5]:
        return 'Elementary'
    elif grade in [6, 7, 8]:
        return 'Middle'
    elif grade in [9, 10, 11, 12]:
        return 'High'
    elif grade == 13:
        return 'All'
    else:
        return 'Unknown'
        

# Apply Mapping and Create New Columns
print("     Standardizing Grade Labels...")

# Map the original Grade values to numeric ones.
# We do not fill missing values with "Unknown" here so that the column remains numeric.
df['Grade_Numeric'] = df['Grade'].map(grade_mapping)

# Assign grade groups based on the numeric grade.
df['Grade_Group'] = df['Grade_Numeric'].apply(assign_grade_group)



# One-Hot Encoding
# One-Hot Encode the Grade_Group column with all categories retained.
grade_group_dummies = pd.get_dummies(df['Grade_Group'], prefix='grade_group')
df = pd.concat([df, grade_group_dummies], axis=1)

# One-Hot Encode the Grade_Numeric column (dummy variables for every numeric grade)
print("     One-Hot Encoding the Grade_Numeric column...")
grade_numeric_dummies = pd.get_dummies(df['Grade_Numeric'], prefix='grade_numeric')
df = pd.concat([df, grade_numeric_dummies], axis=1)



# Step 4: Encoding column 'ContentArea'
print_bold("Processing Step 4: Encoding column 'ContentArea'")

content_area_mapping = {
    'ELA': 'English Language Arts',
    'MATH': 'Mathematics',
    'MAT': 'Mathematics',
    'SCI': 'Science',
    'SOC': 'Social Studies',
    'Math': 'Mathematics',
    'Write': 'Writing',
    'ESSAY': 'Essay',
    'CMP': 'Computer Science',
    'Essay': 'Essay'
}

# Map the ContentArea values using the provided mapping
df['ContentArea'] = df['ContentArea'].map(content_area_mapping)
print("     Unmapped Values in ContentArea:", df['ContentArea'].isnull().sum())


# Step 5.a: Impute Missing Values for Numeric Columns
print_bold("Processing Step 5a: Impute missing values for numeric columns...")

numeric_columns = ['Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg',
                   'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration']

# Identify columns with missing values to optimize scaling
columns_with_missing = df[numeric_columns].columns[df[numeric_columns].isnull().any()]

# Standardize only the columns with missing values
scaler = StandardScaler()
df[columns_with_missing] = scaler.fit_transform(df[columns_with_missing])

# Initialize the Iterative Imputer with optimized RandomForestRegressor settings
iterative_imputer = IterativeImputer(
    estimator=RandomForestRegressor(
        n_estimators=50,  # Reduced number of trees
        max_depth=10,      # Limit tree depth
        max_samples=0.9,   # Use 90% of samples for faster training
        n_jobs=-1,         # Utilize all available CPU cores
        random_state=42
    ),
    max_iter=10,  # Fewer iterations for faster convergence
    random_state=42
)

# Measure execution time
start_time = time.time()
df[columns_with_missing] = iterative_imputer.fit_transform(df[columns_with_missing])
end_time = time.time()

print(f"Imputation completed in {end_time - start_time:.2f} seconds.")
print("\nRemaining Missing Values After Imputation:")
print(df[columns_with_missing].isnull().sum())


# Save the result to a CSV file
output_file = '3_cleaned_part1.csv'
df.to_csv(output_file, index=False)

print_bold(f"\n\nDataset saved to '{output_file}'.\n\n")



Processing Step 1: Drop Unnecessary Columns...
Processing Step 2: Remove Duplicate Rows...
Processing Step 3: Reengineer and reencode the `Grade` column...
     Standardizing Grade Labels...
     One-Hot Encoding the Grade_Numeric column...
Processing Step 4: Encoding column 'ContentArea'
     Unmapped Values in ContentArea: 0
Processing Step 5a: Impute missing values for numeric columns...
Imputation completed in 1072.05 seconds.

Remaining Missing Values After Imputation:
Tested           0
Proficient       0
PctProficient    0
ScaleScoreAvg    0
Students         0
Enrollment       0
PctEnrollment    0
Incidents        0
AvgDuration      0
dtype: int64


Dataset saved to '3_cleaned_part1.csv'.

# Load the dataset - merged_cleaned
df = pd.read_csv("3_cleaned_part1.csv")
dataset_name = "3_cleaned_part1.csv"

# Call the analyze_dataset function
analyze_dataset(df, dataset_name)

# Check tidiness issues 
tidiness_issue_inspection(df, dataset_name)

# Call the function for Quality Issues Inspection 1
quality_issue_inspection_part1(df, dataset_name)

# Call the function for Quality Issues Inspection 2
quality_issue_inspection_part2(df, dataset_name)


Dataset: 3_cleaned_part1.csv 

Total number of rows: 649837 
Total number of columns: 40 


First 3 rows of the dataset:


All Columns in the Dataset:
['School Year', 'District Code', 'School Code', 'Assessment Name', 'ContentArea', 'Race', 'Gender', 'Grade', 'SpecialDemo', 'RowStatus', 'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg', 'Category', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration', 'Grade_Numeric', 'Grade_Group', 'grade_group_All', 'grade_group_Elementary', 'grade_group_High', 'grade_group_Middle', 'grade_numeric_0', 'grade_numeric_1', 'grade_numeric_2', 'grade_numeric_3', 'grade_numeric_4', 'grade_numeric_5', 'grade_numeric_6', 'grade_numeric_7', 'grade_numeric_8', 'grade_numeric_9', 'grade_numeric_10', 'grade_numeric_11', 'grade_numeric_12', 'grade_numeric_13']

No repeated columns found.


 ---------------------- Tidiness Issues Inspection ---------------------- 

Dataset: 3_cleaned_part1.csv 


1. Column Headers as Values:
   Column headers may contain values (e.g., '2021-Q1').

2. Wide Format Instead of Long Format:
   Wide-format columns detected: ['grade_numeric_0', 'grade_numeric_1', 'grade_numeric_2', 'grade_numeric_3', 'grade_numeric_4', 'grade_numeric_5', 'grade_numeric_6', 'grade_numeric_7', 'grade_numeric_8', 'grade_numeric_9', 'grade_numeric_10', 'grade_numeric_11', 'grade_numeric_12', 'grade_numeric_13']

3. Poorly Named Columns:
   None

4. Inconsistent Data Structure:
   None

5. Unnecessary Columns:
   None

6. Mixed Granularity:
   Potential mixed granularity detected in columns: ['School Year', 'District Code', 'School Code', 'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration', 'Grade_Numeric']

7. Repeated Index or Hierarchical Issues:
   Index is unique.

   Index is not a MultiIndex. No hierarchical issues detected.


 ---------------------- Quality Issues Inspection Part 1 ---------------------- 

Dataset: 3_cleaned_part1.csv 


1. Missing Values:
Missing Values: None

Missing Values Summary (Rows):
     Total rows with missing values: 0 (0.00%)
     Rows with high missing values (> 30%): 0 (0.00%)
     Rows with low missing values (0% - 30%): 0 (0.00%)
     Rows with no missing values: 649837 (100.00%)

2. Duplicate Rows: None

3. Invalid values in 'Tested':
     Tested
0 -0.118531
1 -0.118531
2 -0.118531
3 -0.118531
4 -0.118531 

3. Invalid values in 'Proficient':
   Proficient
0   -0.119791
1   -0.119791
2   -0.119791
3   -0.119791
4   -0.119791 

3. Invalid values in 'PctProficient':
    PctProficient
6       -1.925470
7       -1.848394
8       -1.790324
9       -0.934050
10      -0.765118 

3. Invalid values in 'ScaleScoreAvg':
   ScaleScoreAvg
0      -0.264482
1      -0.080228
4      -0.080228
5      -0.082495
6      -1.133270 

3. Invalid values in 'Students':
   Students
0 -0.041977
1 -0.016751
3 -0.112673
4 -0.016751
5 -0.022163 

3. Invalid values in 'Enrollment':
    Enrollment
1    -0.009157
3    -0.109857
4    -0.009157
5    -0.064574
11   -0.111318 

3. Invalid values in 'PctEnrollment':
   PctEnrollment
0      -1.335605
1      -0.579191
2      -0.772110
4      -0.579191
6      -0.833330 

3. Invalid values in 'Incidents':
   Incidents
0  -0.063156
1  -0.050588
3  -0.111221
4  -0.050588
5  -0.021410 

3. Invalid values in 'AvgDuration':
   AvgDuration
0    -0.154427
1    -0.102099
2    -0.141254
3    -0.115444
4    -0.102099 

4. Outliers
Outliers detected in 'District Code': 65334 rows
Outliers detected in 'School Code': 8267 rows
Outliers detected in 'Tested': 152962 rows
Outliers detected in 'Proficient': 152514 rows
Outliers detected in 'PctProficient': 152514 rows
Outliers detected in 'Students': 36462 rows
Outliers detected in 'Enrollment': 21339 rows
Outliers detected in 'PctEnrollment': 21516 rows
Outliers detected in 'Incidents': 50621 rows
Outliers detected in 'AvgDuration': 65399 rows

Columns with outliers: ['District Code', 'School Code', 'Tested', 'Proficient', 'PctProficient', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration']

Total number of outliers across all columns: 726928
Percentage of total outliers in the dataset: 8.60%


5. Redundant Leading/Trailing Spaces:
   None

6. Column Entries with Mixed Formats:
   None

7. Non-Numeric Data in Numeric Columns:
   None


8. Numeric Data in Non-Numeric Columns:
   None


 ---------------------- Quality Issues Inspection Part 2 ---------------------- 

Dataset: 3_cleaned_part1.csv 


1. Summary of Special Characters:
         Column Special Characters or Formatting Issues
Assessment Name                                 (, ), -
           Race                                    -, /
    SpecialDemo                                       -
       Category                              (, ), ,, -

Detailed special characters (delete if not needed) saved to '3_cleaned_part1.csv_special_characters_details.csv'.

Summary of special characters (delete if not needed) saved to '3_cleaned_part1.csv_special_characters_summary.csv'.


2. Columns with Inconsistent Units and Recommendations:
         Column                                 Recommendation
Assessment Name              Reencode (Standardize categories)
    ContentArea              Reencode (Standardize categories)
           Race              Reencode (Standardize categories)
         Gender              Reencode (Standardize categories)
          Grade Reengineer (Separate numeric values and units)
    SpecialDemo              Reencode (Standardize categories)
      RowStatus              Reencode (Standardize categories)
       Category              Reencode (Standardize categories)
    Grade_Group              Reencode (Standardize categories)


3. Unique Categorical Values:

Unique categorical values (delete if not needed) saved to '3_cleaned_part1.csv_unique_categorical_values.csv'.


4. Analyzing Dataset: '3_cleaned_part1.csv' for data types and classification

### Continuation

df = pd.read_csv("3_cleaned_part1.csv")


# Step 6: Mapping and One-hot encoding Category (Punishment) columns according to severity
print_bold("Continuation...")
print_bold("Processing Step 6: Column category...")

# Standardize the category name by removing the comma
df['Category'] = df['Category'].replace(
    {'Out-of-School Suspension, No CDAP Placement': 'Out-of-School Suspension No CDAP Placement'}
)

# Verify the replacement
print("\n Unique Values in 'Category' Column After Replacement")
print(df['Category'].unique())


# Define the severity ranking and suspension status
punishment_info = {
    'In-School Suspension': {'Severity': 1, 'Suspended': 0},
    'Out-of-School Suspension': {'Severity': 2, 'Suspended': 1},
    'Out-of-School Suspension No CDAP Placement': {'Severity': 2, 'Suspended': 1},
    'Out-of-School Suspension with CDAP Placement': {'Severity': 3, 'Suspended': 1},
    'Expulsion (Permanent Removal from School)': {'Severity': 4, 'Suspended': 1},
    'Out-of-School Suspension without CDAP Placement': {'Severity': 2, 'Suspended': 1}
}


# Split the 'Category' column into multiple rows if necessary
df_expanded = df['Category'].str.split(', ', expand=True).stack().reset_index(level=1, drop=True).to_frame('Category')

# Ensure the expanded DataFrame retains the original DataFrame index
df_expanded.index = df.index.repeat(df['Category'].str.split(', ').apply(lambda x: len(x) if isinstance(x, list) else 0))

# Map the severity rank and suspension status to each category
df_expanded['Severity'] = df_expanded['Category'].map(lambda x: punishment_info.get(x, {}).get('Severity', 0))
df_expanded['Suspended'] = df_expanded['Category'].map(lambda x: punishment_info.get(x, {}).get('Suspended', 0))

# One-hot encode the categories with prefixed column names
one_hot_encoded = pd.get_dummies(df_expanded['Category'], prefix='category')

# Aggregate the severity values for each row
severity_result = one_hot_encoded.groupby(df_expanded.index).max()

# Calculate suspension status (1 if any punishment in the row leads to suspension)
suspension_status = df_expanded.groupby(df_expanded.index)['Suspended'].max().reset_index()

# Concatenate the original DataFrame with the one-hot encoded severity and suspension status
df_result = pd.concat([df, severity_result, suspension_status['Suspended']], axis=1)

# Rename the 'Suspended' column to 'Category_Suspended' as requested
df_result = df_result.rename(columns={'Suspended': 'category_Suspended'})


# Save the updated DataFrame to the original 'df'
df = df_result.copy()

# Save the result to a CSV file
output_file = '3_cleaned_part2.csv'
df.to_csv(output_file, index=False)

print_bold(f"\nData saved to {output_file}\n")

# Verify by loading the saved file and checking the columns
df_check = pd.read_csv(output_file)
print("Columns in saved CSV file:")
print(df_check.columns)

Continuation...
Processing Step 8: Column category...

=== Unique Values in 'Category' Column After Replacement ===
['In-School Suspension' 'Out-of-School Suspension'
 'Out-of-School Suspension without CDAP Placement'
 'Out-of-School Suspension with CDAP Placement'
 'Expulsion (Permanent Removal from School)'
 'Out-of-School Suspension No CDAP Placement' nan]

Data saved to 3_cleaned_part2.csv

Columns in saved CSV file:
Index(['School Year', 'District Code', 'School Code', 'Assessment Name',
       'ContentArea', 'Race', 'Gender', 'Grade', 'SpecialDemo', 'RowStatus',
       'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg', 'Category',
       'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration',
       'Grade_Numeric', 'Grade_Group', 'grade_group_All',
       'grade_group_Elementary', 'grade_group_High', 'grade_group_Middle',
       'grade_numeric_0', 'grade_numeric_1', 'grade_numeric_2',
       'grade_numeric_3', 'grade_numeric_4', 'grade_numeric_5',
       'grade_numeric_6', 'grade_numeric_7', 'grade_numeric_8',
       'grade_numeric_9', 'grade_numeric_10', 'grade_numeric_11',
       'grade_numeric_12', 'grade_numeric_13',
       'category_Expulsion (Permanent Removal from School)',
       'category_In-School Suspension', 'category_Out-of-School Suspension',
       'category_Out-of-School Suspension No CDAP Placement',
       'category_Out-of-School Suspension with CDAP Placement',
       'category_Out-of-School Suspension without CDAP Placement',
       'category_Suspended'],
      dtype='object')

### Continuation

df = pd.read_csv("3_cleaned_part2.csv")

# Define the severity ranking for each punishment category
punishment_info = {
    'In-School Suspension': 1,
    'Out-of-School Suspension': 2,
    'Out-of-School Suspension No CDAP Placement': 2,
    'Out-of-School Suspension without CDAP Placement': 2,
    'Out-of-School Suspension with CDAP Placement':3,
    'Expulsion (Permanent Removal from School)': 4
}


# Strip whitespace from all column names to avoid KeyError due to trailing/leading spaces
df.columns = df.columns.str.strip()

# Automatically set empty rows to severity for 'Category_' columns if they exist
for col, severity in punishment_info.items():
    column_name = f"category_{col}"
    if column_name in df.columns:
        df[column_name] = df[column_name].fillna(0).apply(lambda x: severity if x > 0 else 0).astype(int)
    else:
        print(f"Warning: Column '{column_name}' not found in DataFrame.")

# Ensure 'Category_Suspended' column is also filled correctly
if 'category_Suspended' in df.columns:
    df['category_Suspended'] = df['category_Suspended'].fillna(0).astype(int)
else:
    print("Warning: 'Category_Suspended' column not found in DataFrame.")

# Save the result to a CSV file
output_file = '3_cleaned_part3.csv'
df.to_csv(output_file, index=False)

print(f"\nData saved to {output_file}\n")

# Verify by loading the saved file and checking the columns
df_check = pd.read_csv(output_file)
print("Columns in saved CSV file:")
print(df_check.columns)

Data saved to 3_cleaned_part3.csv

Columns in saved CSV file:
Index(['School Year', 'District Code', 'School Code', 'Assessment Name',
       'ContentArea', 'Race', 'Gender', 'Grade', 'SpecialDemo', 'RowStatus',
       'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg', 'Category',
       'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration',
       'Grade_Numeric', 'Grade_Group', 'grade_group_All',
       'grade_group_Elementary', 'grade_group_High', 'grade_group_Middle',
       'grade_numeric_0', 'grade_numeric_1', 'grade_numeric_2',
       'grade_numeric_3', 'grade_numeric_4', 'grade_numeric_5',
       'grade_numeric_6', 'grade_numeric_7', 'grade_numeric_8',
       'grade_numeric_9', 'grade_numeric_10', 'grade_numeric_11',
       'grade_numeric_12', 'grade_numeric_13',
       'category_Expulsion (Permanent Removal from School)',
       'category_In-School Suspension', 'category_Out-of-School Suspension',
       'category_Out-of-School Suspension No CDAP Placement',
       'category_Out-of-School Suspension with CDAP Placement',
       'category_Out-of-School Suspension without CDAP Placement',
       'category_Suspended'],
      dtype='object')

# Load the dataset - merged_cleaned
df = pd.read_csv("3_cleaned_part3.csv")
dataset_name = "3_cleaned_part3.csv"

# Call the analyze_dataset function
analyze_dataset(df, dataset_name)

# Check tidiness issues 
tidiness_issue_inspection(df, dataset_name)

# Call the function for Quality Issues Inspection 1
quality_issue_inspection_part1(df, dataset_name)

# Call the function for Quality Issues Inspection 2
quality_issue_inspection_part2(df, dataset_name)


Dataset: 3_cleaned_part3.csv 

Total number of rows: 649837 
Total number of columns: 47 


First 3 rows of the dataset:


All Columns in the Dataset:
['School Year', 'District Code', 'School Code', 'Assessment Name', 'ContentArea', 'Race', 'Gender', 'Grade', 'SpecialDemo', 'RowStatus', 'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg', 'Category', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration', 'Grade_Numeric', 'Grade_Group', 'grade_group_All', 'grade_group_Elementary', 'grade_group_High', 'grade_group_Middle', 'grade_numeric_0', 'grade_numeric_1', 'grade_numeric_2', 'grade_numeric_3', 'grade_numeric_4', 'grade_numeric_5', 'grade_numeric_6', 'grade_numeric_7', 'grade_numeric_8', 'grade_numeric_9', 'grade_numeric_10', 'grade_numeric_11', 'grade_numeric_12', 'grade_numeric_13', 'category_Expulsion (Permanent Removal from School)', 'category_In-School Suspension', 'category_Out-of-School Suspension', 'category_Out-of-School Suspension No CDAP Placement', 'category_Out-of-School Suspension with CDAP Placement', 'category_Out-of-School Suspension without CDAP Placement', 'category_Suspended']

No repeated columns found.


 ---------------------- Tidiness Issues Inspection ---------------------- 

Dataset: 3_cleaned_part3.csv 


1. Column Headers as Values:
   Column headers may contain values (e.g., '2021-Q1').

2. Wide Format Instead of Long Format:
   Wide-format columns detected: ['grade_numeric_0', 'grade_numeric_1', 'grade_numeric_2', 'grade_numeric_3', 'grade_numeric_4', 'grade_numeric_5', 'grade_numeric_6', 'grade_numeric_7', 'grade_numeric_8', 'grade_numeric_9', 'grade_numeric_10', 'grade_numeric_11', 'grade_numeric_12', 'grade_numeric_13']

3. Poorly Named Columns:
   None

4. Inconsistent Data Structure:
   None

5. Unnecessary Columns:
   None

6. Mixed Granularity:
   Potential mixed granularity detected in columns: ['School Year', 'District Code', 'School Code', 'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration', 'Grade_Numeric', 'category_Expulsion (Permanent Removal from School)', 'category_In-School Suspension', 'category_Out-of-School Suspension', 'category_Out-of-School Suspension No CDAP Placement', 'category_Out-of-School Suspension with CDAP Placement', 'category_Out-of-School Suspension without CDAP Placement', 'category_Suspended']

7. Repeated Index or Hierarchical Issues:
   Index is unique.

   Index is not a MultiIndex. No hierarchical issues detected.


 ---------------------- Quality Issues Inspection Part 1 ---------------------- 

Dataset: 3_cleaned_part3.csv 


1. Missing Values:
Missing Values: None

Missing Values Summary (Rows):
     Total rows with missing values: 0 (0.00%)
     Rows with high missing values (> 30%): 0 (0.00%)
     Rows with low missing values (0% - 30%): 0 (0.00%)
     Rows with no missing values: 649837 (100.00%)

2. Duplicate Rows: None

3. Invalid values in 'Tested':
     Tested
0 -0.118531
1 -0.118531
2 -0.118531
3 -0.118531
4 -0.118531 

3. Invalid values in 'Proficient':
   Proficient
0   -0.119791
1   -0.119791
2   -0.119791
3   -0.119791
4   -0.119791 

3. Invalid values in 'PctProficient':
    PctProficient
6       -1.925470
7       -1.848394
8       -1.790324
9       -0.934050
10      -0.765118 

3. Invalid values in 'ScaleScoreAvg':
   ScaleScoreAvg
0      -0.264482
1      -0.080228
4      -0.080228
5      -0.082495
6      -1.133270 

3. Invalid values in 'Students':
   Students
0 -0.041977
1 -0.016751
3 -0.112673
4 -0.016751
5 -0.022163 

3. Invalid values in 'Enrollment':
    Enrollment
1    -0.009157
3    -0.109857
4    -0.009157
5    -0.064574
11   -0.111318 

3. Invalid values in 'PctEnrollment':
   PctEnrollment
0      -1.335605
1      -0.579191
2      -0.772110
4      -0.579191
6      -0.833330 

3. Invalid values in 'Incidents':
   Incidents
0  -0.063156
1  -0.050588
3  -0.111221
4  -0.050588
5  -0.021410 

3. Invalid values in 'AvgDuration':
   AvgDuration
0    -0.154427
1    -0.102099
2    -0.141254
3    -0.115444
4    -0.102099 

4. Outliers
Outliers detected in 'District Code': 65334 rows
Outliers detected in 'School Code': 8267 rows
Outliers detected in 'Tested': 152962 rows
Outliers detected in 'Proficient': 152514 rows
Outliers detected in 'PctProficient': 152514 rows
Outliers detected in 'Students': 36462 rows
Outliers detected in 'Enrollment': 21339 rows
Outliers detected in 'PctEnrollment': 21516 rows
Outliers detected in 'Incidents': 50621 rows
Outliers detected in 'AvgDuration': 65399 rows
Outliers detected in 'category_Expulsion (Permanent Removal from School)': 3859 rows
Outliers detected in 'category_Out-of-School Suspension No CDAP Placement': 64079 rows
Outliers detected in 'category_Out-of-School Suspension with CDAP Placement': 12050 rows
Outliers detected in 'category_Out-of-School Suspension without CDAP Placement': 110272 rows

Columns with outliers: ['District Code', 'School Code', 'Tested', 'Proficient', 'PctProficient', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration', 'category_Expulsion (Permanent Removal from School)', 'category_Out-of-School Suspension No CDAP Placement', 'category_Out-of-School Suspension with CDAP Placement', 'category_Out-of-School Suspension without CDAP Placement']

Total number of outliers across all columns: 917188
Percentage of total outliers in the dataset: 7.06%


5. Redundant Leading/Trailing Spaces:
   None

6. Column Entries with Mixed Formats:
   None

7. Non-Numeric Data in Numeric Columns:
   None


8. Numeric Data in Non-Numeric Columns:
   None


 ---------------------- Quality Issues Inspection Part 2 ---------------------- 

Dataset: 3_cleaned_part3.csv 


1. Summary of Special Characters:
         Column Special Characters or Formatting Issues
Assessment Name                                 (, ), -
           Race                                    -, /
    SpecialDemo                                       -
       Category                                 (, ), -

Detailed special characters (delete if not needed) saved to '3_cleaned_part3.csv_special_characters_details.csv'.

Summary of special characters (delete if not needed) saved to '3_cleaned_part3.csv_special_characters_summary.csv'.


2. Columns with Inconsistent Units and Recommendations:
         Column                                 Recommendation
Assessment Name              Reencode (Standardize categories)
    ContentArea              Reencode (Standardize categories)
           Race              Reencode (Standardize categories)
         Gender              Reencode (Standardize categories)
          Grade Reengineer (Separate numeric values and units)
    SpecialDemo              Reencode (Standardize categories)
      RowStatus              Reencode (Standardize categories)
       Category              Reencode (Standardize categories)
    Grade_Group              Reencode (Standardize categories)


3. Unique Categorical Values:

Unique categorical values (delete if not needed) saved to '3_cleaned_part3.csv_unique_categorical_values.csv'.


4. Analyzing Dataset: '3_cleaned_part3.csv' for data types and classification

df = pd.read_csv("3_cleaned_part3.csv")

### No need for category_ derived columns

# Handling of Outliers
print_bold("Handling of Outliers...")

columns_with_outliers = ['Tested', 'Proficient', 'PctProficient', 'Students',
                         'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration']

for column in columns_with_outliers:
    if column in df.columns:
        # Calculate the 0th and 100th percentiles as bounds for capping
        lower_bound = df[column].quantile(0.05)
        upper_bound = df[column].quantile(0.95)

        # Cap the outliers using the computed bounds
        df[column] = df[column].clip(lower=lower_bound, upper=upper_bound)

        # Add an outlier flag column to check capping
        df[f'{column}_outlier_flag'] = ((df[column] < lower_bound) | (df[column] > upper_bound)).astype(int)

        # Check if any outliers remain after capping (should be zero if capping is done correctly)
        remaining_outliers = df[df[f'{column}_outlier_flag'] == 1]
        print(f"Remaining outliers in {column}: {len(remaining_outliers)} rows")

# Remove the outlier flag columns to clean up the dataframe
outlier_flags = [f'{column}_outlier_flag' for column in columns_with_outliers if f'{column}_outlier_flag' in df.columns]
df.drop(columns=outlier_flags, inplace=True)



# Define the relevant columns to merge
suspension_columns = [
    'category_Out-of-School Suspension',
    'category_Out-of-School Suspension No CDAP Placement',
    'category_Out-of-School Suspension without CDAP Placement'
]

# Check which of these columns exist in the DataFrame
existing_columns = [col for col in suspension_columns if col in df.columns]
missing_columns = [col for col in suspension_columns if col not in df.columns]

# Display which columns exist and which are missing
print("\nColumn Existence Check")
print("Existing Columns in DataFrame:")
print(existing_columns if existing_columns else "None found.")
print("\nMissing Columns in DataFrame:")
print(missing_columns if missing_columns else "None missing.")

# If at least one column exists, proceed with merging
if existing_columns:
    # Combine all relevant columns into a single column
    df['category_Out-of-School Suspension_Merged'] = df[existing_columns].sum(axis=1).clip(upper=6)

    # Drop the original columns that were merged
    df.drop(columns=existing_columns, inplace=True)
    
    print("\nColumns merged and originals dropped successfully.")
else:
    print("\nNo relevant columns found to merge.")
    

# Save the result to a CSV file
output_file = '3_cleaned_part4.csv'
df.to_csv(output_file, index=False)

print(f"\nData saved to {output_file}\n")

Final Handling of Outliers...
Remaining outliers in Tested: 0 rows
Remaining outliers in Proficient: 0 rows
Remaining outliers in PctProficient: 0 rows
Remaining outliers in Students: 0 rows
Remaining outliers in Enrollment: 0 rows
Remaining outliers in PctEnrollment: 0 rows
Remaining outliers in Incidents: 0 rows
Remaining outliers in AvgDuration: 0 rows

=== Column Existence Check ===
Existing Columns in DataFrame:
['category_Out-of-School Suspension', 'category_Out-of-School Suspension No CDAP Placement', 'category_Out-of-School Suspension without CDAP Placement']

Missing Columns in DataFrame:
None missing.

Columns merged and originals dropped successfully.

Data saved to 3_cleaned_part4.csv

# Load the dataset - merged_cleaned
df = pd.read_csv("3_cleaned_part4.csv")
dataset_name = "3_cleaned_part4.csv"

# Call the analyze_dataset function
analyze_dataset(df, dataset_name)

# Check tidiness issues 
tidiness_issue_inspection(df, dataset_name)

# Call the function for Quality Issues Inspection 1
quality_issue_inspection_part1(df, dataset_name)

# Call the function for Quality Issues Inspection 2
quality_issue_inspection_part2(df, dataset_name)


Dataset: 3_cleaned_part4.csv 

Total number of rows: 649837 
Total number of columns: 45 


First 3 rows of the dataset:


All Columns in the Dataset:
['School Year', 'District Code', 'School Code', 'Assessment Name', 'ContentArea', 'Race', 'Gender', 'Grade', 'SpecialDemo', 'RowStatus', 'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg', 'Category', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration', 'Grade_Numeric', 'Grade_Group', 'grade_group_All', 'grade_group_Elementary', 'grade_group_High', 'grade_group_Middle', 'grade_numeric_0', 'grade_numeric_1', 'grade_numeric_2', 'grade_numeric_3', 'grade_numeric_4', 'grade_numeric_5', 'grade_numeric_6', 'grade_numeric_7', 'grade_numeric_8', 'grade_numeric_9', 'grade_numeric_10', 'grade_numeric_11', 'grade_numeric_12', 'grade_numeric_13', 'category_Expulsion (Permanent Removal from School)', 'category_In-School Suspension', 'category_Out-of-School Suspension with CDAP Placement', 'category_Suspended', 'category_Out-of-School Suspension_Merged']

No repeated columns found.


 ---------------------- Tidiness Issues Inspection ---------------------- 

Dataset: 3_cleaned_part4.csv 


1. Column Headers as Values:
   Column headers may contain values (e.g., '2021-Q1').

2. Wide Format Instead of Long Format:
   Wide-format columns detected: ['grade_numeric_0', 'grade_numeric_1', 'grade_numeric_2', 'grade_numeric_3', 'grade_numeric_4', 'grade_numeric_5', 'grade_numeric_6', 'grade_numeric_7', 'grade_numeric_8', 'grade_numeric_9', 'grade_numeric_10', 'grade_numeric_11', 'grade_numeric_12', 'grade_numeric_13']

3. Poorly Named Columns:
   None

4. Inconsistent Data Structure:
   None

5. Unnecessary Columns:
   None

6. Mixed Granularity:
   Potential mixed granularity detected in columns: ['School Year', 'District Code', 'School Code', 'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration', 'Grade_Numeric', 'category_Expulsion (Permanent Removal from School)', 'category_In-School Suspension', 'category_Out-of-School Suspension with CDAP Placement', 'category_Suspended', 'category_Out-of-School Suspension_Merged']

7. Repeated Index or Hierarchical Issues:
   Index is unique.

   Index is not a MultiIndex. No hierarchical issues detected.


 ---------------------- Quality Issues Inspection Part 1 ---------------------- 

Dataset: 3_cleaned_part4.csv 


1. Missing Values:
Missing Values: None

Missing Values Summary (Rows):
     Total rows with missing values: 0 (0.00%)
     Rows with high missing values (> 30%): 0 (0.00%)
     Rows with low missing values (0% - 30%): 0 (0.00%)
     Rows with no missing values: 649837 (100.00%)

2. Duplicate Rows: Number of duplicate rows: 694
3. Invalid values in 'Tested':
     Tested
0 -0.118531
1 -0.118531
2 -0.118531
3 -0.118531
4 -0.118531 

3. Invalid values in 'Proficient':
   Proficient
0   -0.119791
1   -0.119791
2   -0.119791
3   -0.119791
4   -0.119791 

3. Invalid values in 'PctProficient':
    PctProficient
6       -0.593018
7       -0.593018
8       -0.593018
9       -0.593018
10      -0.593018 

3. Invalid values in 'ScaleScoreAvg':
   ScaleScoreAvg
0      -0.264482
1      -0.080228
4      -0.080228
5      -0.082495
6      -1.133270 

3. Invalid values in 'Students':
   Students
0 -0.041977
1 -0.016751
3 -0.112673
4 -0.016751
5 -0.022163 

3. Invalid values in 'Enrollment':
    Enrollment
1    -0.009157
3    -0.109857
4    -0.009157
5    -0.064574
11   -0.111318 

3. Invalid values in 'PctEnrollment':
   PctEnrollment
0      -1.318352
1      -0.579191
2      -0.772110
4      -0.579191
6      -0.833330 

3. Invalid values in 'Incidents':
   Incidents
0  -0.063156
1  -0.050588
3  -0.111221
4  -0.050588
5  -0.021410 

3. Invalid values in 'AvgDuration':
   AvgDuration
0    -0.144547
1    -0.102099
2    -0.141254
3    -0.115444
4    -0.102099 

4. Outliers
Outliers detected in 'District Code': 65334 rows
Outliers detected in 'School Code': 8267 rows
Outliers detected in 'Tested': 152962 rows
Outliers detected in 'Proficient': 152514 rows
Outliers detected in 'PctProficient': 152514 rows
Outliers detected in 'Students': 36462 rows
Outliers detected in 'Incidents': 50621 rows
Outliers detected in 'AvgDuration': 50193 rows
Outliers detected in 'category_Expulsion (Permanent Removal from School)': 3859 rows
Outliers detected in 'category_Out-of-School Suspension with CDAP Placement': 12050 rows

Columns with outliers: ['District Code', 'School Code', 'Tested', 'Proficient', 'PctProficient', 'Students', 'Incidents', 'AvgDuration', 'category_Expulsion (Permanent Removal from School)', 'category_Out-of-School Suspension with CDAP Placement']

Total number of outliers across all columns: 684776
Percentage of total outliers in the dataset: 5.85%


5. Redundant Leading/Trailing Spaces:
   None

6. Column Entries with Mixed Formats:
   None

7. Non-Numeric Data in Numeric Columns:
   None


8. Numeric Data in Non-Numeric Columns:
   None


 ---------------------- Quality Issues Inspection Part 2 ---------------------- 

Dataset: 3_cleaned_part4.csv 


1. Summary of Special Characters:
         Column Special Characters or Formatting Issues
Assessment Name                                 (, ), -
           Race                                    -, /
    SpecialDemo                                       -
       Category                                 (, ), -

Detailed special characters (delete if not needed) saved to '3_cleaned_part4.csv_special_characters_details.csv'.

Summary of special characters (delete if not needed) saved to '3_cleaned_part4.csv_special_characters_summary.csv'.


2. Columns with Inconsistent Units and Recommendations:
         Column                                 Recommendation
Assessment Name              Reencode (Standardize categories)
    ContentArea              Reencode (Standardize categories)
           Race              Reencode (Standardize categories)
         Gender              Reencode (Standardize categories)
          Grade Reengineer (Separate numeric values and units)
    SpecialDemo              Reencode (Standardize categories)
      RowStatus              Reencode (Standardize categories)
       Category              Reencode (Standardize categories)
    Grade_Group              Reencode (Standardize categories)


3. Unique Categorical Values:

Unique categorical values (delete if not needed) saved to '3_cleaned_part4.csv_unique_categorical_values.csv'.


4. Analyzing Dataset: '3_cleaned_part4.csv' for data types and classification

df = pd.read_csv("3_cleaned_part4.csv")

#  Final One hot encoding
print_bold("One hot encoding more columns...")

# One-Hot Encode the specified columns without dropping the original columns
df = df.join(pd.get_dummies(df['ContentArea'], prefix='ContentArea', drop_first=False))
df = df.join(pd.get_dummies(df['SpecialDemo'], prefix='SpecialDemo', drop_first=False))
df = df.join(pd.get_dummies(df['District Code'], prefix='DistrictCode', drop_first=False))
df = df.join(pd.get_dummies(df['School Year'], prefix='SchoolYear', drop_first=False))
df = df.join(pd.get_dummies(df['Assessment Name'], prefix='AssessmentName', drop_first=False))
df = df.join(pd.get_dummies(df['Grade_Group'], prefix='Grade_Group', drop_first=False))


# Can remove this code if retaining true and false values
# Convert True/False to 1/0 for all columns
df = df.replace({True: 1, False: 0})

# Save the result to a CSV file
output_file = '4_ready_training.csv'
df.to_csv(output_file, index=False)

print(f"\nData saved to {output_file}\n")

One hot encoding more columns...

Data saved to 4_ready_training.csv

# Call functions for quality and tidiness inspection
#    Dataset - 4_imputation_onehot_part1.csv


# Load df
df = pd.read_csv("4_ready_training.csv")
dataset_name = "4_ready_training.csv"

# Call the analyze_dataset function
analyze_dataset(df, dataset_name)

# Check tidiness issues 
tidiness_issue_inspection(df, dataset_name)

# Call the function for Quality Issues Inspection 1
quality_issue_inspection_part1(df, dataset_name)

# Call the function for Quality Issues Inspection 2
quality_issue_inspection_part2(df, dataset_name)


Dataset: 4_ready_training.csv 

Total number of rows: 649837 
Total number of columns: 121 


First 3 rows of the dataset:


All Columns in the Dataset:
['School Year', 'District Code', 'School Code', 'Assessment Name', 'ContentArea', 'Race', 'Gender', 'Grade', 'SpecialDemo', 'RowStatus', 'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg', 'Category', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration', 'Grade_Numeric', 'Grade_Group', 'grade_group_All', 'grade_group_Elementary', 'grade_group_High', 'grade_group_Middle', 'grade_numeric_0', 'grade_numeric_1', 'grade_numeric_2', 'grade_numeric_3', 'grade_numeric_4', 'grade_numeric_5', 'grade_numeric_6', 'grade_numeric_7', 'grade_numeric_8', 'grade_numeric_9', 'grade_numeric_10', 'grade_numeric_11', 'grade_numeric_12', 'grade_numeric_13', 'category_Expulsion (Permanent Removal from School)', 'category_In-School Suspension', 'category_Out-of-School Suspension with CDAP Placement', 'category_Suspended', 'category_Out-of-School Suspension_Merged', 'ContentArea_Computer Science', 'ContentArea_English Language Arts', 'ContentArea_Essay', 'ContentArea_Mathematics', 'ContentArea_Science', 'ContentArea_Social Studies', 'SpecialDemo_Active EL Students', 'SpecialDemo_All Students', 'SpecialDemo_Foster Care', 'SpecialDemo_Homeless', 'SpecialDemo_Low-Income', 'SpecialDemo_Military Connected Youth', 'SpecialDemo_Non Low-Income', 'SpecialDemo_Non-EL Students', 'SpecialDemo_Non-Foster Care', 'SpecialDemo_Non-Homeless', 'SpecialDemo_Non-SWD', 'SpecialDemo_Students with Disabilities', 'DistrictCode_0', 'DistrictCode_10', 'DistrictCode_13', 'DistrictCode_15', 'DistrictCode_16', 'DistrictCode_17', 'DistrictCode_18', 'DistrictCode_23', 'DistrictCode_24', 'DistrictCode_29', 'DistrictCode_31', 'DistrictCode_32', 'DistrictCode_33', 'DistrictCode_34', 'DistrictCode_35', 'DistrictCode_36', 'DistrictCode_37', 'DistrictCode_38', 'DistrictCode_39', 'DistrictCode_40', 'DistrictCode_68', 'DistrictCode_69', 'DistrictCode_71', 'DistrictCode_72', 'DistrictCode_74', 'DistrictCode_76', 'DistrictCode_77', 'DistrictCode_80', 'DistrictCode_82', 'DistrictCode_85', 'DistrictCode_86', 'DistrictCode_87', 'DistrictCode_88', 'DistrictCode_89', 'DistrictCode_92', 'DistrictCode_9604', 'DistrictCode_9605', 'DistrictCode_9606', 'DistrictCode_9607', 'DistrictCode_9609', 'DistrictCode_9611', 'DistrictCode_9612', 'DistrictCode_9614', 'SchoolYear_2019', 'SchoolYear_2020', 'SchoolYear_2021', 'SchoolYear_2022', 'SchoolYear_2023', 'AssessmentName_Annual EL ACCESS', 'AssessmentName_Annual EL ACCESS-Alt', 'AssessmentName_DeSSA Alternate Assessment', 'AssessmentName_Delaware System of Student Assessment (DeSSA)', 'AssessmentName_SAT School-Day (Spring)', 'AssessmentName_Smarter Balanced Summative Assessment', 'Grade_Group_All', 'Grade_Group_Elementary', 'Grade_Group_High', 'Grade_Group_Middle']

No repeated columns found.


 ---------------------- Tidiness Issues Inspection ---------------------- 

Dataset: 4_ready_training.csv 


1. Column Headers as Values:
   Column headers may contain values (e.g., '2021-Q1').

2. Wide Format Instead of Long Format:
   Wide-format columns detected: ['grade_numeric_0', 'grade_numeric_1', 'grade_numeric_2', 'grade_numeric_3', 'grade_numeric_4', 'grade_numeric_5', 'grade_numeric_6', 'grade_numeric_7', 'grade_numeric_8', 'grade_numeric_9', 'grade_numeric_10', 'grade_numeric_11', 'grade_numeric_12', 'grade_numeric_13', 'DistrictCode_0', 'DistrictCode_10', 'DistrictCode_13', 'DistrictCode_15', 'DistrictCode_16', 'DistrictCode_17', 'DistrictCode_18', 'DistrictCode_23', 'DistrictCode_24', 'DistrictCode_29', 'DistrictCode_31', 'DistrictCode_32', 'DistrictCode_33', 'DistrictCode_34', 'DistrictCode_35', 'DistrictCode_36', 'DistrictCode_37', 'DistrictCode_38', 'DistrictCode_39', 'DistrictCode_40', 'DistrictCode_68', 'DistrictCode_69', 'DistrictCode_71', 'DistrictCode_72', 'DistrictCode_74', 'DistrictCode_76', 'DistrictCode_77', 'DistrictCode_80', 'DistrictCode_82', 'DistrictCode_85', 'DistrictCode_86', 'DistrictCode_87', 'DistrictCode_88', 'DistrictCode_89', 'DistrictCode_92', 'DistrictCode_9604', 'DistrictCode_9605', 'DistrictCode_9606', 'DistrictCode_9607', 'DistrictCode_9609', 'DistrictCode_9611', 'DistrictCode_9612', 'DistrictCode_9614', 'SchoolYear_2019', 'SchoolYear_2020', 'SchoolYear_2021', 'SchoolYear_2022', 'SchoolYear_2023']

3. Poorly Named Columns:
   None

4. Inconsistent Data Structure:
   None

5. Unnecessary Columns:
   None

6. Mixed Granularity:
   Potential mixed granularity detected in columns: ['School Year', 'District Code', 'School Code', 'Tested', 'Proficient', 'PctProficient', 'ScaleScoreAvg', 'Students', 'Enrollment', 'PctEnrollment', 'Incidents', 'AvgDuration', 'Grade_Numeric', 'grade_group_All', 'grade_group_Elementary', 'grade_group_High', 'grade_group_Middle', 'grade_numeric_0', 'grade_numeric_1', 'grade_numeric_2', 'grade_numeric_3', 'grade_numeric_4', 'grade_numeric_5', 'grade_numeric_6', 'grade_numeric_7', 'grade_numeric_8', 'grade_numeric_9', 'grade_numeric_10', 'grade_numeric_11', 'grade_numeric_12', 'grade_numeric_13', 'category_Expulsion (Permanent Removal from School)', 'category_In-School Suspension', 'category_Out-of-School Suspension with CDAP Placement', 'category_Suspended', 'category_Out-of-School Suspension_Merged', 'ContentArea_Computer Science', 'ContentArea_English Language Arts', 'ContentArea_Essay', 'ContentArea_Mathematics', 'ContentArea_Science', 'ContentArea_Social Studies', 'SpecialDemo_Active EL Students', 'SpecialDemo_All Students', 'SpecialDemo_Foster Care', 'SpecialDemo_Homeless', 'SpecialDemo_Low-Income', 'SpecialDemo_Military Connected Youth', 'SpecialDemo_Non Low-Income', 'SpecialDemo_Non-EL Students', 'SpecialDemo_Non-Foster Care', 'SpecialDemo_Non-Homeless', 'SpecialDemo_Non-SWD', 'SpecialDemo_Students with Disabilities', 'DistrictCode_0', 'DistrictCode_10', 'DistrictCode_13', 'DistrictCode_15', 'DistrictCode_16', 'DistrictCode_17', 'DistrictCode_18', 'DistrictCode_23', 'DistrictCode_24', 'DistrictCode_29', 'DistrictCode_31', 'DistrictCode_32', 'DistrictCode_33', 'DistrictCode_34', 'DistrictCode_35', 'DistrictCode_36', 'DistrictCode_37', 'DistrictCode_38', 'DistrictCode_39', 'DistrictCode_40', 'DistrictCode_68', 'DistrictCode_69', 'DistrictCode_71', 'DistrictCode_72', 'DistrictCode_74', 'DistrictCode_76', 'DistrictCode_77', 'DistrictCode_80', 'DistrictCode_82', 'DistrictCode_85', 'DistrictCode_86', 'DistrictCode_87', 'DistrictCode_88', 'DistrictCode_89', 'DistrictCode_92', 'DistrictCode_9604', 'DistrictCode_9605', 'DistrictCode_9606', 'DistrictCode_9607', 'DistrictCode_9609', 'DistrictCode_9611', 'DistrictCode_9612', 'DistrictCode_9614', 'SchoolYear_2019', 'SchoolYear_2020', 'SchoolYear_2021', 'SchoolYear_2022', 'SchoolYear_2023', 'AssessmentName_Annual EL ACCESS', 'AssessmentName_Annual EL ACCESS-Alt', 'AssessmentName_DeSSA Alternate Assessment', 'AssessmentName_Delaware System of Student Assessment (DeSSA)', 'AssessmentName_SAT School-Day (Spring)', 'AssessmentName_Smarter Balanced Summative Assessment', 'Grade_Group_All', 'Grade_Group_Elementary', 'Grade_Group_High', 'Grade_Group_Middle']

7. Repeated Index or Hierarchical Issues:
   Index is unique.

   Index is not a MultiIndex. No hierarchical issues detected.


 ---------------------- Quality Issues Inspection Part 1 ---------------------- 

Dataset: 4_ready_training.csv 


1. Missing Values:
Missing Values: None

Missing Values Summary (Rows):
     Total rows with missing values: 0 (0.00%)
     Rows with high missing values (> 30%): 0 (0.00%)
     Rows with low missing values (0% - 30%): 0 (0.00%)
     Rows with no missing values: 649837 (100.00%)

2. Duplicate Rows: Number of duplicate rows: 694
3. Invalid values in 'Tested':
     Tested
0 -0.118531
1 -0.118531
2 -0.118531
3 -0.118531
4 -0.118531 

3. Invalid values in 'Proficient':
   Proficient
0   -0.119791
1   -0.119791
2   -0.119791
3   -0.119791
4   -0.119791 

3. Invalid values in 'PctProficient':
    PctProficient
6       -0.593018
7       -0.593018
8       -0.593018
9       -0.593018
10      -0.593018 

3. Invalid values in 'ScaleScoreAvg':
   ScaleScoreAvg
0      -0.264482
1      -0.080228
4      -0.080228
5      -0.082495
6      -1.133270 

3. Invalid values in 'Students':
   Students
0 -0.041977
1 -0.016751
3 -0.112673
4 -0.016751
5 -0.022163 

3. Invalid values in 'Enrollment':
    Enrollment
1    -0.009157
3    -0.109857
4    -0.009157
5    -0.064574
11   -0.111318 

3. Invalid values in 'PctEnrollment':
   PctEnrollment
0      -1.318352
1      -0.579191
2      -0.772110
4      -0.579191
6      -0.833330 

3. Invalid values in 'Incidents':
   Incidents
0  -0.063156
1  -0.050588
3  -0.111221
4  -0.050588
5  -0.021410 

3. Invalid values in 'AvgDuration':
   AvgDuration
0    -0.144547
1    -0.102099
2    -0.141254
3    -0.115444
4    -0.102099 

4. Outliers
Outliers detected in 'District Code': 65334 rows
Outliers detected in 'School Code': 8267 rows
Outliers detected in 'Tested': 152962 rows
Outliers detected in 'Proficient': 152514 rows
Outliers detected in 'PctProficient': 152514 rows
Outliers detected in 'Students': 36462 rows
Outliers detected in 'Incidents': 50621 rows
Outliers detected in 'AvgDuration': 50193 rows
Outliers detected in 'grade_group_All': 92264 rows
Outliers detected in 'grade_numeric_0': 5592 rows
Outliers detected in 'grade_numeric_1': 8422 rows
Outliers detected in 'grade_numeric_2': 8952 rows
Outliers detected in 'grade_numeric_3': 29987 rows
Outliers detected in 'grade_numeric_4': 50099 rows
Outliers detected in 'grade_numeric_5': 67503 rows
Outliers detected in 'grade_numeric_6': 49257 rows
Outliers detected in 'grade_numeric_7': 65223 rows
Outliers detected in 'grade_numeric_8': 75888 rows
Outliers detected in 'grade_numeric_9': 29821 rows
Outliers detected in 'grade_numeric_10': 44558 rows
Outliers detected in 'grade_numeric_11': 86966 rows
Outliers detected in 'grade_numeric_12': 35305 rows
Outliers detected in 'grade_numeric_13': 92264 rows
Outliers detected in 'category_Expulsion (Permanent Removal from School)': 3859 rows
Outliers detected in 'category_Out-of-School Suspension with CDAP Placement': 12050 rows
Outliers detected in 'ContentArea_Computer Science': 133892 rows
Outliers detected in 'ContentArea_English Language Arts': 157648 rows
Outliers detected in 'ContentArea_Essay': 33926 rows
Outliers detected in 'ContentArea_Mathematics': 157621 rows
Outliers detected in 'ContentArea_Science': 97649 rows
Outliers detected in 'ContentArea_Social Studies': 69101 rows
Outliers detected in 'SpecialDemo_Active EL Students': 33343 rows
Outliers detected in 'SpecialDemo_Foster Care': 4112 rows
Outliers detected in 'SpecialDemo_Homeless': 17790 rows
Outliers detected in 'SpecialDemo_Low-Income': 80778 rows
Outliers detected in 'SpecialDemo_Military Connected Youth': 7048 rows
Outliers detected in 'SpecialDemo_Non Low-Income': 53504 rows
Outliers detected in 'SpecialDemo_Non-EL Students': 50665 rows
Outliers detected in 'SpecialDemo_Non-Foster Care': 42817 rows
Outliers detected in 'SpecialDemo_Non-Homeless': 42239 rows
Outliers detected in 'SpecialDemo_Non-SWD': 51862 rows
Outliers detected in 'SpecialDemo_Students with Disabilities': 77387 rows
Outliers detected in 'DistrictCode_0': 31991 rows
Outliers detected in 'DistrictCode_10': 31023 rows
Outliers detected in 'DistrictCode_13': 32221 rows
Outliers detected in 'DistrictCode_15': 18892 rows
Outliers detected in 'DistrictCode_16': 23302 rows
Outliers detected in 'DistrictCode_17': 23640 rows
Outliers detected in 'DistrictCode_18': 26317 rows
Outliers detected in 'DistrictCode_23': 23249 rows
Outliers detected in 'DistrictCode_24': 23109 rows
Outliers detected in 'DistrictCode_29': 33923 rows
Outliers detected in 'DistrictCode_31': 37976 rows
Outliers detected in 'DistrictCode_32': 65113 rows
Outliers detected in 'DistrictCode_33': 82275 rows
Outliers detected in 'DistrictCode_34': 49537 rows
Outliers detected in 'DistrictCode_35': 15667 rows
Outliers detected in 'DistrictCode_36': 37158 rows
Outliers detected in 'DistrictCode_37': 11414 rows
Outliers detected in 'DistrictCode_38': 9692 rows
Outliers detected in 'DistrictCode_39': 4419 rows
Outliers detected in 'DistrictCode_40': 3585 rows
Outliers detected in 'DistrictCode_68': 92 rows
Outliers detected in 'DistrictCode_69': 7833 rows
Outliers detected in 'DistrictCode_71': 3605 rows
Outliers detected in 'DistrictCode_72': 1284 rows
Outliers detected in 'DistrictCode_74': 3571 rows
Outliers detected in 'DistrictCode_76': 1853 rows
Outliers detected in 'DistrictCode_77': 1365 rows
Outliers detected in 'DistrictCode_80': 3634 rows
Outliers detected in 'DistrictCode_82': 2542 rows
Outliers detected in 'DistrictCode_85': 4346 rows
Outliers detected in 'DistrictCode_86': 7125 rows
Outliers detected in 'DistrictCode_87': 2165 rows
Outliers detected in 'DistrictCode_88': 3513 rows
Outliers detected in 'DistrictCode_89': 3312 rows
Outliers detected in 'DistrictCode_92': 4020 rows
Outliers detected in 'DistrictCode_9604': 2038 rows
Outliers detected in 'DistrictCode_9605': 3081 rows
Outliers detected in 'DistrictCode_9606': 1166 rows
Outliers detected in 'DistrictCode_9607': 3148 rows
Outliers detected in 'DistrictCode_9609': 430 rows
Outliers detected in 'DistrictCode_9611': 2935 rows
Outliers detected in 'DistrictCode_9612': 2241 rows
Outliers detected in 'DistrictCode_9614': 35 rows
Outliers detected in 'SchoolYear_2019': 116242 rows
Outliers detected in 'SchoolYear_2020': 65991 rows
Outliers detected in 'SchoolYear_2021': 61212 rows
Outliers detected in 'AssessmentName_Annual EL ACCESS': 131258 rows
Outliers detected in 'AssessmentName_Annual EL ACCESS-Alt': 2634 rows
Outliers detected in 'AssessmentName_DeSSA Alternate Assessment': 60377 rows
Outliers detected in 'AssessmentName_Delaware System of Student Assessment (DeSSA)': 154672 rows
Outliers detected in 'AssessmentName_SAT School-Day (Spring)': 102335 rows
Outliers detected in 'Grade_Group_All': 92264 rows

Columns with outliers: ['District Code', 'School Code', 'Tested', 'Proficient', 'PctProficient', 'Students', 'Incidents', 'AvgDuration', 'grade_group_All', 'grade_numeric_0', 'grade_numeric_1', 'grade_numeric_2', 'grade_numeric_3', 'grade_numeric_4', 'grade_numeric_5', 'grade_numeric_6', 'grade_numeric_7', 'grade_numeric_8', 'grade_numeric_9', 'grade_numeric_10', 'grade_numeric_11', 'grade_numeric_12', 'grade_numeric_13', 'category_Expulsion (Permanent Removal from School)', 'category_Out-of-School Suspension with CDAP Placement', 'ContentArea_Computer Science', 'ContentArea_English Language Arts', 'ContentArea_Essay', 'ContentArea_Mathematics', 'ContentArea_Science', 'ContentArea_Social Studies', 'SpecialDemo_Active EL Students', 'SpecialDemo_Foster Care', 'SpecialDemo_Homeless', 'SpecialDemo_Low-Income', 'SpecialDemo_Military Connected Youth', 'SpecialDemo_Non Low-Income', 'SpecialDemo_Non-EL Students', 'SpecialDemo_Non-Foster Care', 'SpecialDemo_Non-Homeless', 'SpecialDemo_Non-SWD', 'SpecialDemo_Students with Disabilities', 'DistrictCode_0', 'DistrictCode_10', 'DistrictCode_13', 'DistrictCode_15', 'DistrictCode_16', 'DistrictCode_17', 'DistrictCode_18', 'DistrictCode_23', 'DistrictCode_24', 'DistrictCode_29', 'DistrictCode_31', 'DistrictCode_32', 'DistrictCode_33', 'DistrictCode_34', 'DistrictCode_35', 'DistrictCode_36', 'DistrictCode_37', 'DistrictCode_38', 'DistrictCode_39', 'DistrictCode_40', 'DistrictCode_68', 'DistrictCode_69', 'DistrictCode_71', 'DistrictCode_72', 'DistrictCode_74', 'DistrictCode_76', 'DistrictCode_77', 'DistrictCode_80', 'DistrictCode_82', 'DistrictCode_85', 'DistrictCode_86', 'DistrictCode_87', 'DistrictCode_88', 'DistrictCode_89', 'DistrictCode_92', 'DistrictCode_9604', 'DistrictCode_9605', 'DistrictCode_9606', 'DistrictCode_9607', 'DistrictCode_9609', 'DistrictCode_9611', 'DistrictCode_9612', 'DistrictCode_9614', 'SchoolYear_2019', 'SchoolYear_2020', 'SchoolYear_2021', 'AssessmentName_Annual EL ACCESS', 'AssessmentName_Annual EL ACCESS-Alt', 'AssessmentName_DeSSA Alternate Assessment', 'AssessmentName_Delaware System of Student Assessment (DeSSA)', 'AssessmentName_SAT School-Day (Spring)', 'Grade_Group_All']

Total number of outliers across all columns: 3975081
Percentage of total outliers in the dataset: 5.46%


5. Redundant Leading/Trailing Spaces:
   None

6. Column Entries with Mixed Formats:
   None

7. Non-Numeric Data in Numeric Columns:
   None


8. Numeric Data in Non-Numeric Columns:
   None


 ---------------------- Quality Issues Inspection Part 2 ---------------------- 

Dataset: 4_ready_training.csv 


1. Summary of Special Characters:
         Column Special Characters or Formatting Issues
Assessment Name                                 (, ), -
           Race                                    -, /
    SpecialDemo                                       -
       Category                                 (, ), -

Detailed special characters (delete if not needed) saved to '4_ready_training.csv_special_characters_details.csv'.

Summary of special characters (delete if not needed) saved to '4_ready_training.csv_special_characters_summary.csv'.


2. Columns with Inconsistent Units and Recommendations:
         Column                                 Recommendation
Assessment Name              Reencode (Standardize categories)
    ContentArea              Reencode (Standardize categories)
           Race              Reencode (Standardize categories)
         Gender              Reencode (Standardize categories)
          Grade Reengineer (Separate numeric values and units)
    SpecialDemo              Reencode (Standardize categories)
      RowStatus              Reencode (Standardize categories)
       Category              Reencode (Standardize categories)
    Grade_Group              Reencode (Standardize categories)


3. Unique Categorical Values:

Unique categorical values (delete if not needed) saved to '4_ready_training.csv_unique_categorical_values.csv'.


4. Analyzing Dataset: '4_ready_training.csv' for data types and classification

# Load the dataset
df = pd.read_csv("4_ready_training.csv")

# Columns to analyze for outliers
columns_with_outliers = ['District Code', 'School Code', 'Tested', 'Proficient', 'PctProficient', 'Students',
                         'Incidents', 'AvgDuration', 'grade_group_All', 'grade_numeric_0', 'grade_numeric_1',
                         'grade_numeric_2', 'grade_numeric_3', 'grade_numeric_4', 'grade_numeric_5', 'grade_numeric_6',
                         'grade_numeric_7', 'grade_numeric_8', 'grade_numeric_9', 'grade_numeric_10',
                         'grade_numeric_11', 'grade_numeric_12', 'grade_numeric_13',
                         'category_Expulsion (Permanent Removal from School)',
                         'category_Out-of-School Suspension with CDAP Placement', 'ContentArea_Computer Science',
                         'ContentArea_English Language Arts', 'ContentArea_Essay', 'ContentArea_Mathematics',
                         'ContentArea_Science', 'ContentArea_Social Studies', 'SpecialDemo_Active EL Students',
                         'SpecialDemo_Foster Care', 'SpecialDemo_Homeless', 'SpecialDemo_Low-Income',
                         'SpecialDemo_Military Connected Youth', 'SpecialDemo_Non Low-Income',
                         'SpecialDemo_Non-EL Students', 'SpecialDemo_Non-Foster Care', 'SpecialDemo_Non-Homeless',
                         'SpecialDemo_Non-SWD', 'SpecialDemo_Students with Disabilities', 'DistrictCode_0',
                         'DistrictCode_10', 'DistrictCode_13', 'DistrictCode_15', 'DistrictCode_16', 'DistrictCode_17',
                         'DistrictCode_18', 'DistrictCode_23', 'DistrictCode_24', 'DistrictCode_29', 'DistrictCode_31',
                         'DistrictCode_32', 'DistrictCode_33', 'DistrictCode_34', 'DistrictCode_35', 'DistrictCode_36',
                         'DistrictCode_37', 'DistrictCode_38', 'DistrictCode_39', 'DistrictCode_40', 'DistrictCode_68',
                         'DistrictCode_69', 'DistrictCode_71', 'DistrictCode_72', 'DistrictCode_74', 'DistrictCode_76',
                         'DistrictCode_77', 'DistrictCode_80', 'DistrictCode_82', 'DistrictCode_85', 'DistrictCode_86',
                         'DistrictCode_87', 'DistrictCode_88', 'DistrictCode_89', 'DistrictCode_92', 'DistrictCode_9604',
                         'DistrictCode_9605', 'DistrictCode_9606', 'DistrictCode_9607', 'DistrictCode_9609',
                         'DistrictCode_9611', 'DistrictCode_9612', 'DistrictCode_9614', 'SchoolYear_2019',
                         'SchoolYear_2020', 'SchoolYear_2021', 'AssessmentName_Annual EL ACCESS',
                         'AssessmentName_Annual EL ACCESS-Alt', 'AssessmentName_DeSSA Alternate Assessment',
                         'AssessmentName_Delaware System of Student Assessment (DeSSA)',
                         'AssessmentName_SAT School-Day (Spring)', 'Grade_Group_All']

# Define column types to help evaluate real vs. false outliers
categorical_columns = ['District Code', 'School Code']
standardized_columns = ['Tested', 'Proficient', 'PctProficient', 'Students', 
                        'Incidents', 'AvgDuration']
count_based_columns = ['grade_group_All', 'grade_numeric_0', 'grade_numeric_1',
                         'grade_numeric_2', 'grade_numeric_3', 'grade_numeric_4', 'grade_numeric_5', 'grade_numeric_6',
                         'grade_numeric_7', 'grade_numeric_8', 'grade_numeric_9', 'grade_numeric_10',
                         'grade_numeric_11', 'grade_numeric_12', 'grade_numeric_13',
                         'category_Expulsion (Permanent Removal from School)',
                         'category_Out-of-School Suspension with CDAP Placement', 'ContentArea_Computer Science',
                         'ContentArea_English Language Arts', 'ContentArea_Essay', 'ContentArea_Mathematics',
                         'ContentArea_Science', 'ContentArea_Social Studies', 'SpecialDemo_Active EL Students',
                         'SpecialDemo_Foster Care', 'SpecialDemo_Homeless', 'SpecialDemo_Low-Income',
                         'SpecialDemo_Military Connected Youth', 'SpecialDemo_Non Low-Income',
                         'SpecialDemo_Non-EL Students', 'SpecialDemo_Non-Foster Care', 'SpecialDemo_Non-Homeless',
                         'SpecialDemo_Non-SWD', 'SpecialDemo_Students with Disabilities', 'DistrictCode_0',
                         'DistrictCode_10', 'DistrictCode_13', 'DistrictCode_15', 'DistrictCode_16', 'DistrictCode_17',
                         'DistrictCode_18', 'DistrictCode_23', 'DistrictCode_24', 'DistrictCode_29', 'DistrictCode_31',
                         'DistrictCode_32', 'DistrictCode_33', 'DistrictCode_34', 'DistrictCode_35', 'DistrictCode_36',
                         'DistrictCode_37', 'DistrictCode_38', 'DistrictCode_39', 'DistrictCode_40', 'DistrictCode_68',
                         'DistrictCode_69', 'DistrictCode_71', 'DistrictCode_72', 'DistrictCode_74', 'DistrictCode_76',
                         'DistrictCode_77', 'DistrictCode_80', 'DistrictCode_82', 'DistrictCode_85', 'DistrictCode_86',
                         'DistrictCode_87', 'DistrictCode_88', 'DistrictCode_89', 'DistrictCode_92', 'DistrictCode_9604',
                         'DistrictCode_9605', 'DistrictCode_9606', 'DistrictCode_9607', 'DistrictCode_9609',
                         'DistrictCode_9611', 'DistrictCode_9612', 'DistrictCode_9614', 'SchoolYear_2019',
                         'SchoolYear_2020', 'SchoolYear_2021', 'AssessmentName_Annual EL ACCESS',
                         'AssessmentName_Annual EL ACCESS-Alt', 'AssessmentName_DeSSA Alternate Assessment',
                         'AssessmentName_Delaware System of Student Assessment (DeSSA)',
                         'AssessmentName_SAT School-Day (Spring)', 'Grade_Group_All']


# Function to classify outliers as real or false, including standard deviation analysis
def classify_outliers(df, columns, threshold=1.5):
    outlier_results = []
    real_outlier_columns = set()
    
    for column in columns:
        if column in df.columns:
            Q1 = df[column].quantile(0.25)
            Q3 = df[column].quantile(0.75)
            IQR = Q3 - Q1
            lower_bound = Q1 - threshold * IQR
            upper_bound = Q3 + threshold * IQR
            
            # Calculate mean and standard deviation
            mean = df[column].mean()
            std_dev = df[column].std()
            
            # Calculate outliers using IQR method
            outliers = df[(df[column] < lower_bound) | (df[column] > upper_bound)]
            
            if not outliers.empty:
                # Check if the column is categorical (IDs should not have outliers)
                if column in categorical_columns:
                    outlier_type = "False Outlier (Categorical ID)"
                
                # Check if the column is count-based (small integers likely valid)
                elif column in count_based_columns:
                    if outliers[column].max() <= 10:  # Assuming valid counts do not exceed 10
                        outlier_type = "False Outlier (Valid Count Data)"
                    else:
                        outlier_type = "Real Outlier (Abnormal Count)"
                        real_outlier_columns.add(column)
                
                # Check standardized columns (expected within ±3 sd)
                elif column in standardized_columns:
                    if (outliers[column].abs() <= 3).all():
                        outlier_type = "False Outlier (Standardized within ±3 std)"
                    else:
                        outlier_type = "Real Outlier (Beyond ±3 std)"
                        real_outlier_columns.add(column)
                
                # General numeric columns using standard deviation
                else:
                    std_lower_bound = mean - (3 * std_dev)
                    std_upper_bound = mean + (3 * std_dev)
                    
                    if (outliers[column] < std_lower_bound).any() or (outliers[column] > std_upper_bound).any():
                        outlier_type = "Real Outlier (Beyond ±3 std)"
                        real_outlier_columns.add(column)
                    else:
                        outlier_type = "False Outlier (Within ±3 std)"
                
                outlier_count = len(outliers)
                outlier_results.append({
                    'Column': column,
                    'Outlier Count': outlier_count,
                    'Outlier Type': outlier_type,
                    'Mean': mean,
                    'Standard Deviation': std_dev,
                    'Lower Bound (IQR)': lower_bound,
                    'Upper Bound (IQR)': upper_bound,
                    'Lower Bound (±3 std)': std_lower_bound if 'std_lower_bound' in locals() else None,
                    'Upper Bound (±3 std)': std_upper_bound if 'std_upper_bound' in locals() else None,
                    'Min Outlier Value': outliers[column].min(),
                    'Max Outlier Value': outliers[column].max()
                })
    
    return pd.DataFrame(outlier_results), real_outlier_columns

# Get the outlier classification results and identify columns with real outliers
outlier_report, real_outlier_columns = classify_outliers(df, columns_with_outliers)

# Display the outlier report
print_bold("\n--- Outlier Classification Report with Standard Deviation Analysis ---")
if not outlier_report.empty:
    display(outlier_report)
else:
    print("No outliers found.")

# Display the summary report
if real_outlier_columns:
    print(f"\nSummary Report: Columns with real outliers: {list(real_outlier_columns)}")
else:
    print_bold("\n\nSummary Report: No real outlier found.")


--- Outlier Classification Report with Standard Deviation Analysis ---



Summary Report: No real outlier found.

# Load the datasets
df = pd.read_csv('4_ready_training.csv')

# Ensure data types are correct
df['School Year'] = df['School Year'].astype('category')
df['Grade_Numeric'] = df['Grade_Numeric'].astype('category')

# ==================== TREND ANALYSIS ====================

# Step 1: Analyze Trends for ScaleScoreAvg
plt.figure(figsize=(10, 6))
sns.lineplot(data=scale_score_summary, x="School Year", y="ScaleScoreAvg", hue="Grade_Numeric", marker="o", palette="viridis")
plt.title("Trends in Academic Performance (ScaleScoreAvg) Across School Years")
plt.xlabel("School Year")
plt.ylabel("Average Scale Score")
plt.legend(title="Grade", bbox_to_anchor=(1.05, 1), loc='upper left')
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

print("\nThis plot shows how ScaleScoreAvg varies across school years, grouped by grade level.")
print("If lines for certain grades slope upward, it suggests improvement in academic performance over time.")
print("Steeper slopes indicate stronger trends, while flat lines suggest stability in performance.\n")


# Step 2: Analyze Trends for Incidents
plt.figure(figsize=(10, 6))
sns.lineplot(data=incident_summary, x="School Year", y="Incidents", hue="Grade_Numeric", marker="o", palette="viridis")
plt.title("Trends in Discipline Incidents Across School Years")
plt.xlabel("School Year")
plt.ylabel("Number of Incidents")
plt.legend(title="Grade", bbox_to_anchor=(1.05, 1), loc='upper left')
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

print("\nThis plot shows the frequency of discipline incidents across school years by grade.")
print("Increasing lines indicate rising incidents, while decreasing lines suggest better behavioral outcomes.\n")


# Step 3: Analyze Trends for AvgDuration
plt.figure(figsize=(10, 6))
sns.lineplot(data=duration_summary, x="School Year", y="AvgDuration", hue="Grade_Numeric", marker="o", palette="viridis")
plt.title("Trends in AvgDuration Across School Years")
plt.xlabel("School Year")
plt.ylabel("Average Duration (minutes)")
plt.legend(title="Grade", bbox_to_anchor=(1.05, 1), loc='upper left')
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

print("This plot examines the average duration of incidents over time.")
print("Decreasing durations may reflect improved behavioral interventions.\n")

This plot shows how ScaleScoreAvg varies across school years, grouped by grade level.
If lines for certain grades slope upward, it suggests improvement in academic performance over time.
Steeper slopes indicate stronger trends, while flat lines suggest stability in performance.

This plot shows the frequency of discipline incidents across school years by grade.
Increasing lines indicate rising incidents, while decreasing lines suggest better behavioral outcomes.

This plot examines the average duration of incidents over time.
Decreasing durations may reflect improved behavioral interventions.

Capstone: Comparative Machine Learning Approaches for Predicting Scale Score Averages¶

Data Preparation and Processing¶

Important: I added comments within the code and/or before/after the code for added explanation.¶

Data¶

Research Question¶

Hypothesis¶

Dataset 1¶

Dataset 2¶

Data storing: All datasets used here are saved in my local data store for a seamless process.¶

Below are customized functions for checking data quality and tidiness¶

Inspecting the data quality and tidiness: 1_assessment.csv (assessment performance dataset)¶

Inspecting the data quality and tidiness: 1_discipline.csv (discipline dataset)¶

Preparing the datasets for merging¶

Changing column names and saving it back to the original df before merging the datasets¶

Checking the heatmap of missing values for both datasets¶

After checking the datasets,¶

Upon checking the heatmap, I noticed that several years of data are missing for dataset assessment. To ensure data quality, these years are dropped for both datasets.¶

Checking the columns for each dataset¶

Merging/Combining two datasets¶

Merged df: Quality and Tidiness Issues Inspection¶

Assessing the merged df for categorical variables and missing values¶

Perform a categorical assessment on the newly merged dataset to identify which columns require reencoding and reengineering.¶

Examine the missing data across both columns and rows.¶

After examining the missing values, I found that they are intentional due to redacted information concerning protected demographics. Therefore, these columns will be retained, and imputation will be applied to columns containing empirical values.¶

Cleaning the dataset and ensuring it is ready for model training¶

Cleaning starts with this dataset: 2_merged.csv¶

Datasets saved throughout the process.¶

Cleaning ends with this dataset and should be ready for training: 4_ready_training.csv¶

Cleaning ends here and df should be ready for training: 4_ready_training.csv¶

Check if these are Real Outliers:¶

No real outliers found using standard deviation.¶

All datasets are saved locally.¶

	School Year	District	Organization	Assessment Name	ContentArea	Race	Gender	Grade	SpecialDemo	Geography	SubGroup	RowStatus	Tested	Proficient	PctProficient	ScaleScoreAvg
0	2015	State of Delaware	State of Delaware	Smarter Balanced Summative Assessment	ELA	All Students	Female	8th Grade	All Students	All Students	Female/8th Grade	REPORTED	4667.0	2594.0	55.58	2576.55
1	2015	State of Delaware	State of Delaware	Smarter Balanced Summative Assessment	ELA	All Students	Female	11th Grade	All Students	All Students	Female/11th Grade	REPORTED	3720.0	2222.0	59.73	2602.39
2	2015	State of Delaware	State of Delaware	Smarter Balanced Summative Assessment	ELA	All Students	Female	All Students	All Students	All Students	Female	REPORTED	32942.0	19116.0	58.03	NaN

	Column	Data Type	Classification	Correct/Incorrect Data Type	Unique Values	Sample Values
School Year	School Year	int64	ordinal	Correct	10	[2015, 2017, 2016, 2019, 2018, 2023, 2020]
District Code	District Code	int64	numeric	Correct	49	[0, 10, 13, 15, 16, 17, 18]
District	District	object	categorical	Correct	55	[State of Delaware, Caesar Rodney School District, Capital School District, Lake Forest School District, Laurel School District, Cape Henlopen School District, Milford School District]
School Code	School Code	int64	numeric	Correct	250	[0, 648, 610, 612, 621, 615, 616]
Organization	Organization	object	categorical	Correct	291	[State of Delaware, Caesar Rodney School District, Dover High School, Allen Frear Elementary School, Major George S. Welch Elementary School, F. Niel Postlethwait Middle School, Kent Elementary Intensive Learning Center]
Assessment Name	Assessment Name	object	categorical	Correct	7	[Smarter Balanced Summative Assessment, DCAS Alternate Assessment, SAT School-Day (Spring), Annual EL ACCESS, DeSSA Alternate Assessment, Annual EL ACCESS-Alt, Delaware System of Student Assessment (DeSSA)]
ContentArea	ContentArea	object	categorical	Correct	10	[ELA, MATH, MAT, SCI, SOC, Math, Write]
Race	Race	object	categorical	Correct	8	[All Students, Hispanic/Latino, Native American, African American, White, Asian American, Native Hawaiian/Pacific Islander]
Gender	Gender	object	categorical	Correct	3	[Female, Male, All Students]
Grade	Grade	object	categorical	Correct	16	[8th Grade, 11th Grade, All Students, 3rd Grade, 4th Grade, 5th Grade, 6th Grade]
SpecialDemo	SpecialDemo	object	categorical	Correct	13	[All Students, Active EL Students, Homeless, Low-Income, Students with Disabilities, Military Connected Youth, Non-Foster Care]
Geography	Geography	object	categorical	Correct	1	[All Students]
SubGroup	SubGroup	object	categorical	Correct	3632	[Female/8th Grade, Female/11th Grade, Female, Male/3rd Grade, Male/4th Grade, Male/5th Grade, Male/6th Grade]
RowStatus	RowStatus	object	categorical	Correct	2	[REPORTED, REDACTED]
Tested	Tested	float64	numeric	Correct	4313	[4667.0, 3720.0, 32942.0, 5107.0, 4978.0, 5031.0, 5005.0]
Proficient	Proficient	float64	numeric	Correct	2536	[2594.0, 2222.0, 19116.0, 2515.0, 2464.0, 2090.0, 2152.0]
PctProficient	PctProficient	float64	numeric	Correct	8566	[55.58, 59.73, 58.03, 49.25, 49.5, 49.99, 41.76]
ScaleScoreAvg	ScaleScoreAvg	float64	numeric	Correct	50892	[2576.55, 2602.39, 2428.17, 2468.35, 2496.66, 2508.5, 2530.95]

	School Year	District Code	District	Organization	Assessment Name	ContentArea	Race	Gender	Grade	SpecialDemo	Geography	SubGroup	RowStatus	Tested	Proficient	PctProficient	ScaleScoreAvg	Category	Students	Enrollment	PctEnrollment	Incidents	AvgDuration
0	2019	16	Laurel School District	Laurel School District	Annual EL ACCESS	CMP	All Students	Male	11th Grade	All Students	All Students	Male/11th Grade	REDACTED	NaN	NaN	NaN	NaN	In-School Suspension	18.0	1919.0	0.94	27.0	0.7
1	2019	16	Laurel School District	Laurel School District	Annual EL ACCESS	CMP	All Students	Male	11th Grade	All Students	All Students	Male/11th Grade	REDACTED	NaN	NaN	NaN	NaN	Out-of-School Suspension	NaN	NaN	NaN	NaN	NaN
2	2019	16	Laurel School District	Laurel School District	Annual EL ACCESS	CMP	All Students	Male	12th Grade	All Students	All Students	Male/12th Grade	REDACTED	NaN	NaN	NaN	NaN	In-School Suspension	43.0	617.0	6.97	59.0	1.1

	School Year	District Code	Assessment Name	ContentArea	Race	Gender	Grade	SpecialDemo	RowStatus	Tested	Proficient	PctProficient	ScaleScoreAvg	Category	Students	Enrollment	PctEnrollment	Incidents	AvgDuration	Grade_Numeric	Grade_Group	grade_group_All	grade_group_Elementary	grade_group_High	grade_group_Middle	grade_numeric_0	grade_numeric_1	grade_numeric_2	grade_numeric_3	grade_numeric_4	grade_numeric_5	grade_numeric_6	grade_numeric_7	grade_numeric_8	grade_numeric_9	grade_numeric_10	grade_numeric_11	grade_numeric_12	grade_numeric_13
0	2019	16	Annual EL ACCESS	Computer Science	All Students	Male	11th Grade	All Students	REDACTED	-0.118531	-0.119791	0.302007	-0.264482	In-School Suspension	-0.041977	0.810896	-1.335605	-0.063156	-0.154427	11	High	False	False	True	False	False	False	False	False	False	False	False	False	False	False	False	True	False	False
1	2019	16	Annual EL ACCESS	Computer Science	All Students	Male	11th Grade	All Students	REDACTED	-0.118531	-0.119791	0.302007	-0.080228	Out-of-School Suspension	-0.016751	-0.009157	-0.579191	-0.050588	-0.102099	11	High	False	False	True	False	False	False	False	False	False	False	False	False	False	False	False	True	False	False
2	2019	16	Annual EL ACCESS	Computer Science	All Students	Male	12th Grade	All Students	REDACTED	-0.118531	-0.119791	0.302007	0.087946	In-School Suspension	0.123137	0.176935	-0.772110	0.058287	-0.141254	12	High	False	False	True	False	False	False	False	False	False	False	False	False	False	False	False	False	True	False

	Column	Outlier Count	Outlier Type	Mean	Standard Deviation	Lower Bound (IQR)	Upper Bound (IQR)	Lower Bound (±3 std)	Upper Bound (±3 std)	Min Outlier Value	Max Outlier Value
0	District Code	65334	False Outlier (Categorical ID)	252.158440	1441.819990	-6.000000	58.000000	None	None	68.000000	9614.000000
1	School Code	8267	False Outlier (Categorical ID)	332.403872	512.728482	-915.000000	1525.000000	None	None	4040.000000	4090.000000
2	Tested	152962	False Outlier (Standardized within ±3 std)	-0.114826	0.022102	-0.118531	-0.118531	None	None	-0.145608	-0.035491
3	Proficient	152514	False Outlier (Standardized within ±3 std)	-0.119883	0.014657	-0.119791	-0.119791	None	None	-0.150264	-0.073613
4	PctProficient	152514	False Outlier (Standardized within ±3 std)	0.233404	0.236626	0.302007	0.302007	None	None	-0.593018	0.481284
...	...	...	...	...	...	...	...	...	...	...	...
89	AssessmentName_Annual EL ACCESS-Alt	2634	False Outlier (Valid Count Data)	0.004053	0.063537	0.000000	0.000000	None	None	1.000000	1.000000
90	AssessmentName_DeSSA Alternate Assessment	60377	False Outlier (Valid Count Data)	0.092911	0.290308	0.000000	0.000000	None	None	1.000000	1.000000
91	AssessmentName_Delaware System of Student Assessment (DeSSA)	154672	False Outlier (Valid Count Data)	0.238017	0.425870	0.000000	0.000000	None	None	1.000000	1.000000
92	AssessmentName_SAT School-Day (Spring)	102335	False Outlier (Valid Count Data)	0.157478	0.364251	0.000000	0.000000	None	None	1.000000	1.000000
93	Grade_Group_All	92264	False Outlier (Valid Count Data)	0.141980	0.349030	0.000000	0.000000	None	None	1.000000	1.000000