Exploring Alzheimer’s Cognitive Decline and Mental Distress through the Lens of GridDB

#Libraries for basic data manipulation
import os
import pandas as pd
import numpy as np

#Libraries for the webAPI requests
import http
http.client.HTTPConnection.debuglevel = 0
import json
import requests

import pyarrow as pa
import pyarrow.csv as csv
import time

## Setting
from IPython.display import Image, Markdown,HTML, display
import warnings
# Suppress all warnings
warnings.filterwarnings('ignore')
#warnings.filterwarnings("ignore", category=pd.errors.SettingWithCopyWarning)
os.environ['OMP_NUM_THREADS'] = '1' # to run KMeans clustering without memory leak issues

# Visualizations & Unsupervised Learning
import folium
import matplotlib.pyplot as plt
from sklearn.preprocessing import OneHotEncoder
from sklearn.cluster import KMeans
from sklearn.metrics import silhouette_score
from sklearn.preprocessing import StandardScaler
import seaborn as sns
import re  # Importing the regex module for parsing
import json

# Mention your working directory here
os.chdir("C:\\XX\\")

#Read the CSV dataset file that was downloaded in the earlier step
Alzheimer_Data = pd.read_csv('../Alzheimer_s_Disease_and_Healthy_Aging_Data.csv')

#Subset the data to include the data that we want to use
Alzheimer_subset = Alzheimer_Data[
    (Alzheimer_Data['Topic'] == 'Frequent mental distress') |
    (Alzheimer_Data['Topic'].str.contains('Cognitive Decline', case=False, na=False))]

# Defining a function to map pandas dtypes to GridDB types
def map_dtype_to_griddb(pandas_dtype):
    """
    # Maps pandas data types to GridDB-compatible data types.
    e.g. pandas_dtype (str): Data type of a pandas column; Returns str: The corresponding GridDB data type.
    """
    dtype_map = {
        'int64': 'INTEGER',
        'float64': 'DOUBLE',
        'object': 'STRING',  # for strings/objects
        'datetime64[ns]': 'TIMESTAMP',
        'bool': 'BOOL'
    }
    return dtype_map.get(str(pandas_dtype), 'STRING')  # Default to STRING if no match

# Function to create the GridDB collection structure from a DataFrame
def create_griddb_structure(df, container_name, container_type="COLLECTION", rowkey=False):
    # Initializing the container structure
    container_structure = {
        "container_name": container_name,
        "container_type": container_type,
        "rowkey": rowkey,
        "columns": []
    }

    # Looping through columns in the DataFrame and mapping their data types to GridDB types
    for col_name, col_dtype in df.dtypes.items():
        column_structure = {
            "name": col_name,
            "type": map_dtype_to_griddb(col_dtype)
        }
        container_structure["columns"].append(column_structure)
    
    return container_structure

# Create the GridDB container structure
griddb_container_structure = create_griddb_structure(Alzheimer_subset, "Alzheimer_Analysis", container_type="COLLECTION")

data_obj1 = griddb_container_structure

#Construct an object to hold the request headers (ensure that you replace the XXX placeholder with the correct value that matches the credentials for your GridDB instance)
header_obj = {"Authorization":"Basic XXX","Content-Type":"application/json; charset=UTF-8","User-Agent":"PostmanRuntime/7.29.0"}

#Construct the base URL based on your GridDB cluster you'd like to connect to (ensure that you replace the placeholders in the URL below with the correct values that correspond to your GridDB instance)
base_url = 'https://[host]:[port]/griddb/v2/[clustername]/dbs/[database_name]/'

#Set up the GridDB WebAPI URL
url = base_url + 'containers'

#Invoke the GridDB WebAPI with the headers and the request body (constructed in the earlier step)
x = requests.post(url, json = data_obj1, headers = header_obj)

# Step 1: Convert the DataFrame to a JSON format with column names (key-value pairs)
Alzheimer_subset_json = Alzheimer_subset.to_json(orient='values')

# Parse the JSON string into a Python list of dictionaries
request_body_Alzheimer_Data = Alzheimer_subset_json

#Setup the URL to be used to invoke the GridDB WebAPI to register rows in the container created previously
url = base_url + 'containers/Alzheimer_Analysis/rows'

#Invoke the GridDB WebAPI using the request constructed
x = requests.put(url, data=request_body_Alzheimer_Data, headers=header_obj)

sql_query1 = (f"""
                SELECT LocationDesc, Geolocation,
                       (COUNT(CASE
                       WHEN LOWER(Topic) LIKE '%distress%' THEN 1
                       END
                             ) * 100.0 / COUNT(*)
                       ) AS Percentage_Frequent_Mental_Distress
                FROM Alzheimer_Analysis
                GROUP BY LocationDesc, Geolocation
                ORDER BY Percentage_Frequent_Mental_Distress DESC
                LIMIT 10;
        """)

#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container
url = base_url + 'sql'

# Construct the request body
request_body = json.dumps([
    {
        "type": "sql-select",
        "stmt": sql_query1
    }
])

# Validate the constructed request body
#print(request_body)

#Invoke the GridDB WebAPI
data_req1 = requests.post(url, data=request_body, headers=header_obj)

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req1.json()
Mental_Distress_by_state = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][0]["name"], myJson[0]["columns"][1]["name"],myJson[0]["columns"][2]["name"]])

sql_query2 = (f"""
				SELECT LocationDesc, Geolocation,Data_Value as Cognitive_Decline_Percentage
				FROM Alzheimer_Analysis
				WHERE Topic LIKE '%Cognitive Decline%'
				AND Data_Value > (
					SELECT AVG(Data_Value)
					FROM Alzheimer_Analysis
					WHERE (LOWER(Class) LIKE '%cognitive decline%'
                    OR LOWER(Topic) LIKE '%cognitive decline%')
				);
        """)

#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container
url = base_url + 'sql'

# Construct the request body
request_body = json.dumps([
    {
        "type": "sql-select",
        "stmt": sql_query2
    }
])

# Validate the constructed request body
#print(request_body)

#Invoke the GridDB WebAPI
data_req2 = requests.post(url, data=request_body, headers=header_obj)

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req2.json()
Cognitive_Decline = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][0]["name"], myJson[0]["columns"][1]["name"],myJson[0]["columns"][2]["name"]])

# Mapping of states and regions
state_to_region = {
    'Idaho': 'West', 
    'Arizona': 'West', 
    'Oklahoma': 'South', 
    'Tennessee': 'South',
    'South': 'South', 
    'Kentucky': 'South', 
    'Wyoming': 'West', 
    'Maryland': 'Northeast',
    'Midwest': 'Midwest', 
    'West': 'West', 
    'Arkansas': 'South', 
    'Delaware': 'Northeast', 
    'Illinois': 'Midwest', 
    'Rhode Island': 'Northeast', 
    'Connecticut': 'Northeast', 
    'Washington': 'West', 
    'Pennsylvania': 'Northeast', 
    'Texas': 'South', 
    'Oregon': 'West', 
    'Ohio': 'Midwest', 
    'Georgia': 'South', 
    'New Jersey': 'Northeast', 
    'New Mexico': 'West', 
    'New York': 'Northeast', 
    'South Carolina': 'South', 
    'United States, DC & Territories': 'DC & Territories',
    'California': 'West', 
    'Michigan': 'Midwest', 
    'Virginia': 'South', 
    'District of Columbia': 'Northeast', 
    'South Dakota': 'Midwest', 
    'Hawaii': 'West', 
    'Indiana': 'Midwest', 
    'Vermont': 'Northeast', 
    'Iowa': 'Midwest', 
    'Kansas': 'Midwest', 
    'New Hampshire': 'Northeast', 
    'Utah': 'West', 
    'Alabama': 'South', 
    'Nevada': 'West', 
    'Louisiana': 'South', 
    'Maine': 'Northeast', 
    'Nebraska': 'Midwest', 
    'Alaska': 'West', 
    'Mississippi': 'South', 
    'Wisconsin': 'Midwest', 
    'North Carolina': 'South', 
    'West Virginia': 'South', 
    'Florida': 'South', 
    'Missouri': 'Midwest', 
    'North Dakota': 'Midwest', 
    'Minnesota': 'Midwest', 
    'Colorado': 'West', 
    'Montana': 'West', 
    'Massachusetts': 'Northeast', 
    'Puerto Rico': 'DC & Territories',
    'Guam': 'DC & Territories', 
    'U.S. Virgin Islands': 'DC & Territories', 
    'American Samoa': 'DC & Territories',
    'Northern Mariana Islands': 'DC & Territories', 
    'Midway Atoll': 'DC & Territories', 
    'Baker Island': 'DC & Territories',
    'Howland Island': 'DC & Territories',
    'Jarvis Island': 'DC & Territories',
    'Johnston Atoll': 'DC & Territories',
    'Kingman Reef': 'DC & Territories',
    'Palmyra Atoll': 'DC & Territories'
}

sql_query3 = (f"""  
			SELECT
				Table_A.LocationDesc,
				Table_A.Geolocation,
				Table_A.Age_Group,
				Table_A.Gender,
				Table_B.Race_Ethnicity,
				Table_A.Cognitive_Decline_Percentage AS Gender_based_Cognitive_Decline,
				Table_B.Cognitive_Decline_Percentage AS Ethnicity_based_Cognitive_Decline
				FROM
					(SELECT 
                    LocationDesc,
                    Geolocation,
                    CASE 
                        WHEN Stratification1 = '50-64 years' THEN '50-64 years'
                        WHEN Stratification1 = '65 years or older' THEN '65 years or older'
                        ELSE 'Overall'
                    END AS Age_Group,
                    (CASE 
                        WHEN StratificationCategory2 = 'Gender' THEN Stratification2 
                        ELSE NULL
                    END) AS Gender,
					AVG(Data_Value) AS Cognitive_Decline_Percentage
					FROM 
						Alzheimer_Analysis
					WHERE 
						Stratification1 IN ('50-64 years', '65 years or older')
						AND 
						Class LIKE '%Cognitive Decline%'
						AND LocationDesc IN ('District of Columbia', 'Puerto Rico', 'Guam', 'Virgin Islands', 'American Samoa', 'Northern Mariana Islands')
						AND (StratificationCategory2 <> '' AND StratificationCategory2 IS NOT NULL)
						AND StratificationCategory2 = 'Gender'
					GROUP BY 
						LocationDesc,
						Geolocation,
						Age_Group,
						Gender
					ORDER BY 5 DESC
					) Table_A
					INNER JOIN
					 (SELECT 
						LocationDesc,
						Geolocation,
						CASE 
							WHEN Stratification1 = '50-64 years' THEN '50-64 years'
							WHEN Stratification1 = '65 years or older' THEN '65 years or older'
							ELSE 'Overall'
						END AS Age_Group,
						(CASE 
							WHEN StratificationCategory2 = 'Race/Ethnicity' THEN Stratification2 
						END) AS Race_Ethnicity,
						AVG(Data_Value) AS Cognitive_Decline_Percentage
					FROM 
						Alzheimer_Analysis
					WHERE 
						Stratification1 IN ('50-64 years', '65 years or older')
						AND 
						Class LIKE '%Cognitive Decline%'
						AND LocationDesc IN ('District of Columbia', 'Puerto Rico', 'Guam', 'Virgin Islands', 'American Samoa', 'Northern Mariana Islands')
						AND (StratificationCategory2 <> '' AND StratificationCategory2 IS NOT NULL)
						AND StratificationCategory2 = 'Race/Ethnicity'
					GROUP BY 
						LocationDesc,
						Geolocation,
						Age_Group,
						Race_Ethnicity
					ORDER BY 5 DESC
					) Table_B
				  on Table_A.LocationDesc = Table_B.LocationDesc
					AND Table_A.Geolocation = Table_B.Geolocation
					AND Table_A.Age_Group = Table_B.Age_Group           
				""")

#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container
url = base_url + 'sql'

# Construct the request body
request_body = json.dumps([
    {
        "type": "sql-select",
        "stmt": sql_query3
    }
])

#Invoke the GridDB WebAPI
data_req3 = requests.post(url, data=request_body, headers=header_obj)

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req3.json()
Cognitive_Decline_Indicators = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][i]["name"] for i in range(7)])

def plot_gender_based_cognitive_decline(df):
    # Pivot the data to make it easier for plotting
    gender_pivot = Cognitive_Decline_Indicators.pivot_table(index=['LocationDesc', 'Age_Group'], columns='Gender', values='Gender_based_Cognitive_Decline')

    # Plotting the stacked bar chart
    ax = gender_pivot.plot(kind='bar', stacked=True, figsize=(10, 6), color=['#1f77b4', '#ff7f0e'])

    # Loop through the bars to add percentage annotations
    for container in ax.containers:
        # Add annotations
        ax.bar_label(container, fmt='%.1f%%', label_type='center')

    # Labels and Titles
    plt.title('Gender-Based Cognitive Decline by Location and Age Group')
    plt.xlabel('Location and Age Group')
    plt.ylabel('Cognitive Decline Percentage')
    plt.xticks(rotation=45, ha='right')
    plt.legend(title='Gender')
    plt.tight_layout()

    plt.show()

sql_query4 = (f"""  
            SELECT
                Stratification2 AS Gender,
                LocationDesc AS Location,
                Geolocation,
                Question,
                COALESCE(AVG(Data_Value),0) AS Avg_Percentage
            FROM
                Alzheimer_Analysis
            WHERE
                Question IN (
                    'Percentage of older adults who are experiencing frequent mental distress',
                    'Percentage of older adults who reported subjective cognitive decline or memory loss that interferes with their ability to engage in social activities or household chores',
                    'Percentage of older adults with subjective cognitive decline or memory loss who reported talking with a health care professional about it',
                    'Percentage of older adults who reported subjective cognitive decline or memory loss that is happening more often or is getting worse in the preceding 12 months',
                    'Percentage of older adults who reported that as a result of subjective cognitive decline or memory loss that they need assistance with day-to-day activities'
                )
                AND StratificationCategory2 = 'Gender'
            GROUP BY
                Stratification2, LocationDesc, Geolocation,Question
            ORDER BY
                Avg_Percentage DESC;
                """)

#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container
url = base_url + 'sql'

# Construct the request body
request_body = json.dumps([
    {
        "type": "sql-select",
        "stmt": sql_query4
    }
])

#Invoke the GridDB WebAPI
data_req4 = requests.post(url, data=request_body, headers=header_obj)

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req4.json()
Interrelationships = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][i]["name"] for i in range(5)])

def plot_map_for_question(df, question, gender_colors, offsets):
    """
    Plots a map for a given question in the dataframe, using gender-based markers and the average percentage.
    
    Parameters:
    df (DataFrame): DataFrame containing the data, with columns ['Location', 'Geolocation', 'Gender', 'Avg_Percentage', 'Question'].
    question (str): The specific question to filter and plot.
    gender_colors (dict): A dictionary mapping 'Male' and 'Female' to respective marker colors.
    offsets (dict): A dictionary mapping 'Male' and 'Female' to respective latitude and longitude offsets.
    """
    # Filter the dataframe for the given question
    question_filtered_df = df[df['Question'] == question]

    # Function to extract latitude and longitude from the POINT format
    def extract_lat_long(geolocation):
        if geolocation is None:  # Check if the geolocation is None
            return None, None
        match = re.match(r'POINT \(([-\d.]+) ([-\d.]+)\)', geolocation)
        if match:
            return float(match.group(2)), float(match.group(1))  # (latitude, longitude)
        return None, None  # Return None if the format is incorrect

    # Apply the extraction function to the Geolocation column
    question_filtered_df[['Latitude', 'Longitude']] = question_filtered_df['Geolocation'].apply(lambda x: extract_lat_long(x)).apply(pd.Series)

    # Filter out any NaN values for latitude and longitude
    valid_locations = question_filtered_df.dropna(subset=['Latitude', 'Longitude'])

    if not valid_locations.empty:
        # Create a base map centered around the US
        us_latitude = 37.0902  # Latitude for the U.S. center
        us_longitude = -95.7129  # Longitude for the U.S. center
        m = folium.Map(location=[us_latitude, us_longitude], zoom_start=4)

        # Add markers to the map for each gender
        for idx, row in valid_locations.iterrows():
            avg_percentage = row['Avg_Percentage']
            marker_color = gender_colors.get(row['Gender'], 'gray')  # Default color is gray if gender not found
            lat_offset, long_offset = offsets.get(row['Gender'], (0, 0))  # Apply the appropriate offset for gender
            folium.CircleMarker(
                location=[row['Latitude'] + lat_offset, row['Longitude'] + long_offset],
                radius=avg_percentage / 10,  # Scale the marker size by avg_percentage
                color=marker_color,
                fill=True,
                fill_color=marker_color,
                fill_opacity=0.8,
                popup=folium.Popup(f"{row['Location']}<br>{row['Gender']}<br>Avg Percentage: {avg_percentage:.2f}%", parse_html=True),
                tooltip=f"{row['Location']} - {row['Gender']} - Avg Percentage: {avg_percentage:.2f}%"
            ).add_to(m)

        display(Markdown(f"## {question} - Geographical Distribution"))

        display(m)
    else:
        print(f"No valid locations to display for {question}.")

sql_query5 = (f"""  
    SELECT 
        LocationDesc,
        Geolocation,
        MEDIAN(CASE WHEN Topic = 'Frequent mental distress' THEN Data_Value END) AS Avg_Mental_Distress,
        AVG(CASE WHEN lower(Topic) LIKE '%cognitive decline%' THEN Data_Value END) AS Avg_Cognitive_Decline
    FROM 
        Alzheimer_Analysis
    WHERE 
        (Topic ='Frequent mental distress' OR Lower(Topic) LIKE '%cognitive decline%')
        AND Data_Value IS NOT NULL
    GROUP BY 
        LocationDesc, 
        Geolocation
""")

#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container
url = base_url + 'sql'

# Construct the request body
request_body = json.dumps([
    {
        "type": "sql-select",
        "stmt": sql_query5
    }
])

#Invoke the GridDB WebAPI
data_req5 = requests.post(url, data=request_body, headers=header_obj)

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req5.json()
Relationship_df = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][i]["name"] for i in range(4)])

valid_data_cleaned = valid_data.dropna()
# One-Hot Encoding for the 'Location' column
valid_data_encoded = pd.get_dummies(valid_data_cleaned, columns=['Geolocation'])