What does this notebook do?

• Load the exported CGM values from NutriSense
• Retrieve sleep data from an outside Excel file for that day
• Pull in Garmin step information and "run activities"
• Print out what days are included in the dataset
• Loop through the data, producing one chart vor every day in the dataset
  • Smooth CGM data and interpolate missing values
  • Calculate key metrics for that day, both glucose and steps
  • Create a chart of the glucose values, meals, activities, steps, sleep and include some metrics

import pandas as pd
import plotly.express as px
from plotly.subplots import make_subplots
import plotly.graph_objects as go
import datetime
from datetime import date
from garminconnect import (
    Garmin,
    GarminConnectConnectionError,
    GarminConnectTooManyRequestsError,
    GarminConnectAuthenticationError,
)
from openpyxl import Workbook, load_workbook

# Read in CSV file
df = pd.read_csv('export2.csv')

# Remove "time zone offset" from "occurred_at" column and add new "occurred_at_day" column
df['occurred_at_day'] = df['occurred_at'].apply(lambda x: x[:len(x) - 15])
df['occurred_at'] = df['occurred_at'].apply(lambda x: x[:len(x) - 6])

# Read Excel file
workbook = load_workbook(filename = 'tracking.xlsx')
# Load the sheet with the data I am interested in
todaySheet = workbook['Today']

# Get Garmin Data
# This may not be so great, defaulting to simply retrieving the last 100 activities on Garmin.
# If the day that is plotted is further in the past, this may not work.
numberOfActivities = 100
try:
    # Read UserID and Password from config file
    config  = {}
    with open("config.dat") as myfile:
        for line in myfile:
            name, var = line.partition("=")[::2]
            config[name.strip()] = str(var).strip()
    # Initialize Garmin client with credentials
    client = Garmin(config["uid"], config["password"])
    # Login to Garmin Connect portal
    client.login()
    # Get running activities
    allActivities = client.get_activities(0,numberOfActivities) # 0=start, numberOfActivities=limit  
except (GarminConnectConnectionError, GarminConnectAuthenticationError, GarminConnectTooManyRequestsError,) as err:
    print("Error occured during Garmin Connect Client init: %s" % err)
    quit()
except Exception:
    print("Unknown error occured during Garmin Connect Client init.")

# Print all days with data
daysWithData = df['occurred_at_day'].unique()
print(daysWithData)

['2021-09-02' '2021-09-01' '2021-08-31' '2021-08-30' '2021-08-29'
 '2021-08-28' '2021-08-27' '2021-08-26' '2021-08-25' '2021-08-24'
 '2021-08-23' '2021-08-22' '2021-08-21' '2021-08-20' '2021-08-19'
 '2021-08-18' '2021-08-17' '2021-08-16' '2021-08-15' '2021-08-14'
 '2021-08-13' '2021-08-12' '2021-08-11' '2021-08-10' '2021-08-09'
 '2021-08-08' '2021-08-07' '2021-08-06' '2021-08-05']

# Go through the data and create a plot for the last 5 days
for y in range(1, 5):
    df1 = df[df['occurred_at_day']==daysWithData[y]]
    day = daysWithData[y]

    # Create a datasets just with glucose measurments
    gm = df1[df1['class']=='GlucoseMeasurement']

    # Create a dataset for meals and exercise, sort it
    mealsExercise = df1[((df1['class']=='Meal') | (df1['class']=='ExerciseActivity') )]
    mealsExerciseSorted = mealsExercise.sort_values(by=["occurred_at"], ascending=True)

    # Get steps of the day from Garmin
    dayOfInterest = datetime.datetime.strptime(day, '%Y-%m-%d').date()
    allDayStepData = client.get_steps_data(dayOfInterest.isoformat())

    # convert Garmin data in list form to Pandas dataframe
    dfGarmin = pd.DataFrame.from_dict(allDayStepData)

    # manipulate start time so that it is local (And not GMT)
    dfGarmin['time'] = dfGarmin['startGMT'].apply(lambda x: x[:len(x) - 5])
    dfGarmin['time'] = dfGarmin['time'].apply(lambda x: x[11:])
    offset = dfGarmin["time"][0]
    offsetHour = int(offset.split(':')[0])
    offsetMinutes=int(offset.split(':')[1])
    dfGarmin['time'] = pd.to_datetime(dfGarmin['startGMT'])
    dfGarmin['time'] = dfGarmin['time'].apply(lambda x: x - datetime.timedelta(hours=offsetHour, minutes=offsetMinutes))
    dfGarmin = dfGarmin[dfGarmin.steps != 0]

    # Create a dataset with just 2 columns, and rename columns
    gm_data = gm.filter(['occurred_at', 'value'])
    gm_data.columns = ['time', 'value']

    # turn time column into the index and delete time column
    gm_data['time']= pd.to_datetime(gm_data['time'])
    gm_data.index = gm_data['time']
    del gm_data['time']

    # Add rows and interpolate missing data
    gm_data = gm_data.resample('1T').mean()
    gm_data = gm_data.interpolate(method='cubic')

    # Calculate a few metrics
    threshold = 120  # this is an arbitrary threshold
    above = gm_data[gm_data['value'] > threshold] # create a dataset with glucose measuremnts over threshold
    minutesAboveThreshold = above.count()
    percentageAboveThreshold = int(round(minutesAboveThreshold/(60*24)*100,0))
    averageGlucose = int(round(gm_data['value'].mean()))
    medianGlucose = int(round(gm_data['value'].median()))

    # Calculate statistics on the Garmin data
    numberOfRunningActivitiesToday = 0
    numberOfActivitiesToday = 0
    for i in range(numberOfActivities):
        activity = allActivities[i]
        activityDateTime = activity['startTimeLocal']
        activityDate = datetime.datetime.strptime(activityDateTime, "%Y-%m-%d %H:%M:%S")
        if str(activityDate.date()) == day:
            numberOfActivitiesToday = numberOfActivitiesToday + 1
            if activity["activityType"]["typeKey"] == "running":
                numberOfRunningActivitiesToday = numberOfRunningActivitiesToday + 1

    # Retrieve sleep data
    for rowInExcel in range(3,55):
        cell = todaySheet[str("A"+str(rowInExcel))]
        # Skip over "empty" cells
        if cell.value is None: continue
        # Assume the cell contains a date value, thus convert it
        cellDate = cell.value.date()
        if str(cellDate) == str(day):
            sleepEnd = todaySheet[str("E"+str(rowInExcel))].value
            sleepBegin = todaySheet[str("F"+str(rowInExcel))].value
            sleepBegin = datetime.datetime.combine(date.min, datetime.datetime.strptime('23:59', '%H:%M').time()) - datetime.datetime.combine(date.min, sleepBegin)
            break

    # using subplots here to easily get a secondary y-axis
    fig = make_subplots(specs=[[{"secondary_y": True}]])
    # first add the glucose measurement data
    fig.add_trace( go.Scatter(x=gm_data.index, y=gm_data.value, mode='lines',line=dict(color="purple")))

    # add meals and exercise to the chart
    yText = 145
    eventColor = "green"
    for index, row in mealsExerciseSorted.iterrows():

        # If the activity has "run" in the description, don't use it as it is a duplicate from Garmin
        if "run" in str(row['description']).lower(): continue

        # Convert the time in pandas to something that we can use as an index for the x-axis placement
        time = datetime.datetime.strptime(row['occurred_at'], '%Y-%m-%d %H:%M:%S')

        # Pick a different color depending on the event
        if (row['class'] == "Meal"): eventColor = "black"
        else: eventColor = "green"

        # Alternate text placement so adjacent text doesn't overlap
        if (yText >= 175): yText = 145
        else: yText = yText + 8

        # draw a vertical line at the time of the meal/exercise
        gmAtThatTime = gm_data.loc[str(time.replace(second=0))].value
        fig.add_shape(type="line", xref="x", yref="y", x0=time, y0=gmAtThatTime, x1=time , y1=yText-2, line_color=eventColor,)
        
        # Add text
        fig.add_annotation(text=row['description'], xref="x", yref="y", x=time, y=yText, showarrow=False, font=dict(color=eventColor))

    # Add Garmin running activities
    for i in range(numberOfActivities):
        activity = allActivities[i]
        # only activities that are of type "running"
        if activity["activityType"]["typeKey"] == "running":
            activityDateTime = activity['startTimeLocal']
            activityDate = datetime.datetime.strptime(activityDateTime, "%Y-%m-%d %H:%M:%S")
            if str(activityDate.date()) == day:
                # draw a vertical line at the time of the running activity
                gmAtThatTime = gm_data.loc[str(activityDate.replace(second=0))].value
                fig.add_shape(type="line", xref="x", yref="y", x0=activityDateTime, y0=gmAtThatTime, x1=activityDateTime , y1=133, line_color="green",)
                # Add text... yes this is specific to kilometers. This may need changes for miles.
                textDescr = str(activity['activityName']) + " " + str(int(round(activity['distance']/1000))) + "K run"
                fig.add_annotation(text=textDescr, xref="x", yref="y", x=activityDateTime, y=135, showarrow=False, font=dict(color="green"))

    # Draw a line at the threshold
    fig.add_shape(type="line", xref="x", yref="y",
        x0=gm_data.index[0], y0=threshold, x1=gm_data.index.max(), y1=threshold, line_color="red",)

    # Show text box with summary values
    fig.add_annotation(
                    text='Glucose Threshold = '+str(threshold)+
                    '<br>Minutes above Threshold = '+str(int(round(minutesAboveThreshold,0)))+
                    '<br>Time above Threshold = '+str(percentageAboveThreshold)+"%"+
                    '<br>Average Glucose = '+str(averageGlucose)+
                    '<br>Median Glucose = '+str(medianGlucose)+
                    '<br>Steps Today = '+str(dfGarmin.steps.sum()),
                    align='right', showarrow=False,
                    xref='paper', yref='paper', x=0.001, y=0.005,
                    bordercolor='black', borderwidth=1
                )

    # Setting primary and secondary y axis titles and ticks
    fig.update_layout(yaxis = dict(range=[0, 180], tick0=0, dtick=20, title_text='mg/dL'),yaxis2=dict(tick0=0, dtick=500, range=[0,4500], title_text='Steps'))
    # Adding step data to the chart, using the secondary y axis
    fig.add_trace( go.Bar(x=dfGarmin.time, y=dfGarmin.steps), secondary_y=True)

    # Set x axis title
    fig.update_xaxes(title_text=str(dayOfInterest.strftime('%A'))+ ", " +str(day), tickformat='%H:%M')
    # Hide the legend
    fig.update_layout(showlegend=False)

    # Draw morning sleep
    fig.add_shape(type="rect", xref="x", yref="y",
        x0=gm_data.index[0], y0=57, x1=datetime.datetime.strptime(str(day) + " " + str(sleepEnd), '%Y-%m-%d %H:%M:%S'), y1=150,
        line=dict(color="RoyalBlue"),fillcolor="LightSkyBlue",opacity=0.5,line_width=0,)
    # Draw evening sleep
    fig.add_shape(type="rect", xref="x", yref="y",
        x0=gm_data.index.max(), y0=57, x1=datetime.datetime.strptime(str(day) + " " + str(sleepBegin), '%Y-%m-%d %H:%M:%S'), y1=150,
        line=dict(color="RoyalBlue"),fillcolor="LightSkyBlue",opacity=0.5,line_width=0,)

    # Size the chart
    fig.update_layout(autosize=False, width=1400, height=600,margin=dict(l=20, r=20, t=40, b=20),)
    fig.show()