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'
}