Health Nexus in aging population.

The Health Nexus: Aging, Chronic Disease, and Mental Health in Canada

Project Goal

To create a Power BI dashboard that helps healthcare administrators and researchers monitor the efficiency of clinical trials, from patient recruitment to trial completion. This project complements population health research by focusing on operational efficiency, showcasing ETL (Extract, Transform, Load) skills.

Skills Utilized

• Data Modeling: Integrated data from multiple sources (CCDSS and Statistics Canada) to build a robust data model in Power BI.
• DAX (Data Analysis Expressions): Created complex calculated columns and measures, e.g., to calculate co-occurrence of chronic diseases with mental health conditions across age groups.
• Data Visualization and Storytelling: Developed a compelling narrative through a dashboard allowing exploration by region, age group, and chronic conditions.
• Regulatory and Policy Awareness: Used publicly available, anonymized data to adhere to privacy and regulatory standards, critical for health research.

  ---

Dataset

• Canadian Chronic Disease Surveillance System (CCDSS): Data on dementia (including Alzheimer’s disease) from 2002–2024. Source for dataset: CCHS
• Canadian Community Health Survey (CCHS): Public Use Microdata File (PUMF) 2019–2020, with relevant data selected using the provided dictionary. Source for dataset: CCHS

Methods

Phase 0: Setup and Installation

This phase involved installing Power BI Desktop to set up the necessary tools for the project.

Phase 1: Data Acquisition

• Chronic Disease Data: Downloaded data tables from the Public Health Agency of Canada’s CCDSS, selecting national-level data on dementia, mood/anxiety disorders, and diabetes.
• Demographics/Health Survey Data: Acquired public use microdata files (PUMFs) from the CCHS via Statistics Canada, providing data on age, location, and self-reported mental health.
• File Organization: Saved all .csv and .xlsx files into a dedicated project folder for easy access.

Phase 2: Data Cleaning and Modeling

• Data Import: Imported CCDSS and CCHS .csv files into Power BI.
• Data Transformation: Used Power Query Editor to remove unnecessary columns, handle missing values, correct data types, and filter for required information.
• Model Building: Established relationships between datasets in the ‘Model’ view by linking demographic and chronic disease data via a common field.

Phase 3: Analysis and Visualization

• Measure Creation: Wrote DAX measures in Power BI, e.g., Prevalence Rate = DIVIDE(SUM(ChronicDiseaseCases), SUM(Population)).
• Visual Construction: Built a dashboard in the ‘Report’ view using:
  • A Map to show disease prevalence by province.
  • Bar Charts to compare conditions across age groups.
  • Line Charts to display trends over time.
  • Slicers for interactive filtering.
• Dashboard Design: Arranged visuals with titles, cards, and text boxes to create a clear, compelling narrative.

Phase 4: Publishing and Sharing

• Account Creation: Signed up for a free Microsoft account to use the Power BI service.
• Publishing: Published the report from Power BI Desktop to ‘My Workspace’ in the online service.
• Public Link Generation: Navigated to app.powerbi.com, selected File -> Publish to web (public) to generate a public URL and HTML embed code for portfolio integration.

Note: Due to public sharing limitations in Power BI’s free tier, a programmatic approach was adopted using Python (pandas for data manipulation, PygWalker for interactive EDA) and JavaScript for web deployment.

Visualization 1: The Provincial Health Snapshot

Phase 1: Data Ingestion

• Initial Data Loading:

import pandas as pd

# Load geospatial and health data into pandas DataFrames
df_map = pd.read_json('/content/canada-provinces.json')
df_health = pd.read_json('/content/health_data2.json')

Phase 2: Data Wrangling and Preprocessing

• GeoJSON Feature Extraction: Extracted province names from the nested GeoJSON structure using a lambda function.
• Health Data Transposition: Transposed health data using .T and reset the index to create a ‘province’ column.
• Data Type Standardization: Cast ‘province’ columns to string type for consistent merging.

# Extract province name from the nested GeoJSON structure
df_map['province'] = df_map['features'].apply(lambda x: x['properties']['prov_name_en'])
# Transpose health data and reset index to create a 'province' column
df_health = df_health.T.reset_index()
df_health = df_health.rename(columns={'index': 'province'})
# Convert province columns to string type for consistent merging
df_map['province'] = df_map['province'].astype(str)
df_health['province'] = df_health['province'].astype(str)

Phase 3: Data Integration

• Key Cleaning with Regular Expressions: Removed extraneous characters from province names using .str.replace() with regex [\\[\\]'].
• Inner Join Operation: Merged DataFrames on the ‘province’ key using an inner join.

# Clean the province names in df_map using a regular expression
df_map['province'] = df_map['province'].str.replace("[\\[\\]']", "", regex=True)
# Merge the dataframes on the unified 'province' key
df_merged = pd.merge(df_map, df_health, on='province', how='inner')

display(df_merged.head())

Phase 4: Exploratory Data Analysis (EDA) with PygWalker

• Environment Setup: Installed PygWalker using a try-except block for seamless setup.

try:
    import pygwalker as pyg
except ImportError:
    print("pygwalker not found. Installing...")
    !pip install pygwalker -q
    import pygwalker as pyg

print("pygwalker is installed and ready.")

• Interactive Visualization Interface: Used pyg.walk() to generate a Tableau-like interface for drag-and-drop visualizations.

import pygwalker as pyg

# Launch the PygWalker interface
gwalker = pyg.walk(df_merged)

Phase 5: Data Structure Analysis and Deployment Strategy

• Analysis of Merged JSON Structure:
  1. GeoJSON Feature Collection: Stored in the features column, encoding provincial shapes and locations.
  2. Health Metrics: Quantitative and categorical data (e.g., ‘Perceived_mental_health’, ‘BMI_classification’) stored in separate columns.
• Limitations for Standard Charting Libraries: GeoJSON’s complex geometry requires geospatial libraries like Leaflet.js, not standard charting libraries like Chart.js or D3.js.
• Scope Limitations and Future Work: The dataset lacks CCDSS time-series data, limiting time-series or correlational analyses. Future work requires a new pipeline to incorporate CCDSS data.
• Proposed JavaScript Implementation:
  1. Save the DataFrame as a static .json file.
  2. Use JavaScript’s fetch API to load the JSON.
  3. Create an HTML <select> dropdown populated with health metric keys.
  4. Initialize a Chart.js bar chart for a default metric (e.g., “High Blood Pressure”).
  5. Add an event listener to update the chart based on dropdown selections.

Visualization 2: Time-Series Analysis of Chronic Disease in Seniors 📈

Phase 1: Data Subsetting and Feature Selection

• Subsetting the Demographic Cohort: Filtered df_ccdss for age 65+ using .copy().
• Feature Selection: Selected Province, Year, and Rate_per_100k columns.

# Filter df_ccdss for Age Group '65' and create a safe copy
df_seniors = df_ccdss[df_ccdss['Age Group'] == 65].copy()

# Select only the columns required for the time-series analysis
df_seniors = df_seniors[['Province', 'Year', 'Rate_per_100k']]

Phase 2: Data Structuring for Client-Side Rendering

• Defining the Time-Axis: Extracted unique, sorted years as integers.
• Building the Datasets Array: Created dataset objects for each province, handling missing years with None.
• Dynamic Styling and Serialization: Assigned random border colors and serialized data to JSON.

import json
import random

# Get the unique, sorted years for the x-axis labels
years = sorted(df_seniors['Year'].unique())
years = [int(year) for year in years]

# Prepare the list of dataset objects for Chart.js
datasets = []
for province in df_seniors['Province'].unique():
    df_province = df_seniors[df_seniors['Province'] == province].copy()
    df_province = df_province.sort_values(by='Year')

    # Create a dictionary mapping year to rate for the province
    province_rates = df_province.set_index('Year')['Rate_per_100k'].to_dict()

    # Create a list of rates for all years, filling missing ones with None
    data_points = [province_rates.get(year, None) for year in years]
    data_points = [int(point) if point is not None else None for point in data_points]

    datasets.append({
        'label': province,
        'data': data_points,
        'borderColor': '#' + ''.join([random.choice('0123456789ABCDEF') for i in range(6)]),
        'fill': False
    })

# Combine into the final structure for Chart.js
chart_data = {
    'labels': years,
    'datasets': datasets
}

# Convert the data to a JSON string
chart_data_json = json.dumps(chart_data, indent=4)

print(chart_data_json)

Phase 3: Implementation Strategy for Web Visualization

• Data Persistence and Retrieval: Saved chart_data_json as a static asset (e.g., seniors_disease_rates.json) for fetch API retrieval.
• Chart Configuration and Interactivity:
  • Created interactive controls (checkboxes or multi-select dropdown) for provinces.
  • Initialized a Chart.js line chart with JSON data.
  • Added event listeners to filter datasets and call chart.update() for dynamic rendering.

Visualization 3: Health Factors vs. Outcomes Correlator 🧬

Phase 1: Data Sourcing and Ingestion

• Data Source Specification:

import pandas as pd

# Load the CSV files into pandas DataFrames
df_cchs = pd.read_csv('CCHS_c1.csv')
df_ccdss = pd.read_csv('CCDSS_c1.csv')

# The df_seniors DataFrame is created by filtering the CCDSS data
df_seniors = df_ccdss[df_ccdss['Age Group'] == 65].copy()

Phase 2: Temporal Synchronization and Data Filtering

• Temporal Synchronization: Identified 2019 as the common year for both datasets.

print("Years in CCHS data:")
print(df_cchs['Year'].unique())

print("\nYears in CCDSS seniors data:")
print(df_seniors['Year'].unique())

• Data Filtering by Common Year: Filtered both DataFrames for 2019.

Phase 3: Metric Calculation and Data Aggregation

• CCHS Health Factor Quantification: Calculated prevalence of ‘Has_high_blood_pressure’ as a percentage per province.
• CCDSS Senior Rate Extraction: Isolated Rate_per_100k for 2019 per province.

Phase 4: Final Data Integration and Structuring

• Merging Datasets for Correlation: Performed an inner merge on ‘province’ for 2019 data.
• Structuring Data for Chart.js: Converted rows to a JSON array of {x, y, province} objects.

# Filter both dataframes for the common year 2019
df_cchs_2019 = df_cchs[df_cchs['Year'] == 2019].copy()
df_seniors_2019 = df_seniors[df_seniors['Year'] == 2019].copy()

# Calculate the prevalence of high blood pressure per province for 2019
df_cchs_hp_2019 = df_cchs_2019.groupby('Province')['Has_high_blood_pressure'].apply(lambda x: (x == 'Yes').sum() / len(x) * 100).reset_index()
df_cchs_hp_2019 = df_cchs_hp_2019.rename(columns={'Has_high_blood_pressure': 'High Blood Pressure (%)', 'Province': 'province'})

# Isolate the senior disease rate per province for 2019
df_seniors_rate_2019 = df_seniors_2019[['Province', 'Rate_per_100k']].copy()
df_seniors_rate_2019 = df_seniors_rate_2019.rename(columns={'Province': 'province'})

# Merge the two datasets to align the metrics for each province
df_correlation_2019 = pd.merge(df_cchs_hp_2019, df_seniors_rate_2019, on='province', how='inner')

# Transform the final DataFrame into a list of objects suitable for Chart.js
chart_data_correlation = df_correlation_2019.apply(lambda row: {'x': row['High Blood Pressure (%)'], 'y': row['Rate_per_100k'], 'province': row['province']}, axis=1).tolist()

# Convert the list to a JSON string
import json
chart_data_correlation_json = json.dumps(chart_data_correlation, indent=4)

print("\nJSON data for Chart.js Scatter Plot (2019 - High Blood Pressure vs. Senior Rate):")
print(chart_data_correlation_json)

Phase 5: Implementation Strategy for Interactive Web Visualization

• Data Payload: Designed a nested JSON structure for dynamic selection of X-metric, Y-metric, and Year, with each plot as an array of {x, y, province} objects.
• Front-End Architecture: Used a <canvas> for Chart.js and three <select> dropdowns for X-axis metric, Y-axis metric, and Year.
• Client-Side Logic and Interactivity:
  • Loaded data with fetch API.
  • Added event listeners to dropdowns to update the Chart.js instance.
  • Configured tooltips to display the ‘province’ property on hover.

Project Summary: A Multi-faceted Analysis of Canadian Health Data

This project processed public health data from the CCHS and CCDSS to create three interactive visualizations using Python (pandas, PygWalker) and JavaScript (Chart.js).

• Visualization 1: The Provincial Health Snapshot 📊: Merged geospatial and CCHS data to create a JSON object for interactive bar charts comparing provincial health factors. Requires separate GeoJSON for cartographic libraries like Leaflet.js.
• Visualization 2: The Chronic Disease Rate Explorer 📈: Filtered CCDSS data for seniors (65+), transforming it into a JSON structure for a Chart.js multi-line chart showing provincial disease trends over time.
• Visualization 3: Health Factors vs. Outcomes Correlator 🧬: Merged 2019 CCHS and CCDSS data to create a JSON array for a Chart.js scatter plot, exploring correlations between health factors and senior disease rates.