What does this notebook do?

• Load the exported CGM values from NutriSense
• Print out what days are included in the dataset
• Pair down data to only one day, include CGM values, meals and exercise
• Smooth CGM data and interpolate missing values
• Pull in Garmin step information and "run activities" and plot them
• Calculate key metrics for that day, both glucose and steps
• Retrieve sleep data from an outside Excel file for that day
• 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])
df.head()

	class	value	time	length	photo_url	description	occurred_at	body	updated_at	started_at	ended_at	created_by	occurred_at_day
0	GlucoseMeasurement	110.0	NaN	NaN	NaN	NaN	2021-09-02 07:44:20	NaN	NaN	NaN	NaN	NaN	2021-09-02
1	GlucoseMeasurement	110.0	NaN	NaN	NaN	NaN	2021-09-02 07:29:20	NaN	NaN	NaN	NaN	NaN	2021-09-02
2	GlucoseMeasurement	109.0	NaN	NaN	NaN	NaN	2021-09-02 07:12:27	NaN	NaN	NaN	NaN	NaN	2021-09-02
3	GlucoseMeasurement	104.0	NaN	NaN	NaN	NaN	2021-09-02 06:57:27	NaN	NaN	NaN	NaN	NaN	2021-09-02
4	GlucoseMeasurement	105.0	NaN	NaN	NaN	NaN	2021-09-02 06:42:27	NaN	NaN	NaN	NaN	NaN	2021-09-02

# 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']

# Filter down to one day, pick the second day in the dataset
df = df[df['occurred_at_day']==daysWithData[2]]
day = daysWithData[2]

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

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

# 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
    # Put your userID and password for https://connect.garmin.com/ here
    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
    dayOfInterest = datetime.datetime.strptime(day, '%Y-%m-%d').date()
    # Get steps for the whole day
    allDayStepData = client.get_steps_data(dayOfInterest.isoformat())
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.")

# 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

	startGMT	endGMT	steps	primaryActivityLevel	activityLevelConstant	time
0	2021-08-31T04:00:00.0	2021-08-31T04:15:00.0	0	sedentary	True	2021-08-31 00:00:00
1	2021-08-31T04:15:00.0	2021-08-31T04:30:00.0	0	sedentary	True	2021-08-31 00:15:00
2	2021-08-31T04:30:00.0	2021-08-31T04:45:00.0	0	sedentary	True	2021-08-31 00:30:00
3	2021-08-31T04:45:00.0	2021-08-31T05:00:00.0	0	sedentary	True	2021-08-31 00:45:00
4	2021-08-31T05:00:00.0	2021-08-31T05:15:00.0	0	sedentary	True	2021-08-31 01:00:00
...	...	...	...	...	...	...
91	2021-09-01T02:45:00.0	2021-09-01T03:00:00.0	0	sedentary	True	2021-08-31 22:45:00
92	2021-09-01T03:00:00.0	2021-09-01T03:15:00.0	0	sedentary	True	2021-08-31 23:00:00
93	2021-09-01T03:15:00.0	2021-09-01T03:30:00.0	0	sedentary	True	2021-08-31 23:15:00
94	2021-09-01T03:30:00.0	2021-09-01T03:45:00.0	0	sedentary	True	2021-08-31 23:30:00
95	2021-09-01T03:45:00.0	2021-09-01T04:00:00.0	0	sedentary	True	2021-08-31 23:45:00

96 rows × 6 columns

# Just for exploring the data, lets look at all 15 minute segments that have non-zero steps
dfGarmin = dfGarmin[dfGarmin.steps != 0]
print(dfGarmin[['time', 'steps', 'primaryActivityLevel']])
#dfGarmin.head(n=20)

                  time  steps primaryActivityLevel
24 2021-08-31 06:00:00    271               active
25 2021-08-31 06:15:00    154            sedentary
26 2021-08-31 06:30:00     60            sedentary
27 2021-08-31 06:45:00    108            sedentary
32 2021-08-31 08:00:00     15            sedentary
33 2021-08-31 08:15:00      9            sedentary
35 2021-08-31 08:45:00    220            sedentary
36 2021-08-31 09:00:00   2685         highlyActive
37 2021-08-31 09:15:00   2682         highlyActive
38 2021-08-31 09:30:00    865              generic
39 2021-08-31 09:45:00    105            sedentary
46 2021-08-31 11:30:00     41            sedentary
47 2021-08-31 11:45:00    103            sedentary
48 2021-08-31 12:00:00   1000               active
49 2021-08-31 12:15:00    636               active
50 2021-08-31 12:30:00     34            sedentary
51 2021-08-31 12:45:00     11            sedentary
52 2021-08-31 13:00:00     81            sedentary
55 2021-08-31 13:45:00     33            sedentary
59 2021-08-31 14:45:00   1095               active
60 2021-08-31 15:00:00    121            sedentary
61 2021-08-31 15:15:00     11            sedentary
63 2021-08-31 15:45:00     85            sedentary
64 2021-08-31 16:00:00    690               active
65 2021-08-31 16:15:00   1524               active
66 2021-08-31 16:30:00    223            sedentary
67 2021-08-31 16:45:00     92            sedentary
68 2021-08-31 17:00:00     79            sedentary
70 2021-08-31 17:30:00     30            sedentary
71 2021-08-31 17:45:00    206            sedentary
72 2021-08-31 18:00:00     80            sedentary
73 2021-08-31 18:15:00    119            sedentary
74 2021-08-31 18:30:00     53            sedentary
75 2021-08-31 18:45:00    306            sedentary
77 2021-08-31 19:15:00    474               active
78 2021-08-31 19:30:00    235               active
79 2021-08-31 19:45:00    159            sedentary
80 2021-08-31 20:00:00     26            sedentary
82 2021-08-31 20:30:00    128               active

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

# rename the columns for easier readability
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']

gm_data = gm_data.resample('1T').mean() # add rows for every 1 minute
gm_data = gm_data.interpolate(method='cubic') # interpolate the new 1 minute points with data

# 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()
print('Number of minutes above '+str(threshold)+': '+ minutesAboveThreshold.to_string(index=False))

percentageAboveThreshold = int(round(minutesAboveThreshold/(60*24)*100,0))
print("Time above Threshold = "+str(percentageAboveThreshold)+"%")

averageGlucose = int(round(gm_data['value'].mean()))
medianGlucose = int(round(gm_data['value'].median()))
print("Average Glucose  = "+str(averageGlucose))
print("Median Glucose   = "+str(medianGlucose))

# 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

print("Steps today      = "+str(dfGarmin.steps.sum()))
print("Activities today = "+str(numberOfActivitiesToday))
print("Runs today       = "+str(numberOfRunningActivitiesToday))

Number of minutes above 120: 55
Time above Threshold = 4%
Average Glucose  = 105
Median Glucose   = 105
Steps today      = 14849
Activities today = 1
Runs today       = 1

# The following code is very specific to how I track my sleep data and is just meant to illustrate
# what is possible. The code below will basically retrieve 2 values from an Excel sheet, the first one
# is when I woke up in the morning and the second one is the time I fell asleep before midnight.

# Read Excel file
workbook = load_workbook(filename = 'tracking.xlsx')
# Load the sheet with the data I am interested in
todaySheet = workbook['Today']
# Loop through rows until I find the day I am looking for
for x in range(3,55):
    cell = todaySheet[str("A"+str(x))]
    # 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(x))].value
        sleepBegin = todaySheet[str("F"+str(x))].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 = 150
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 >= 170): yText = 150
    else: yText = yText + 8

    # draw a vertical line at the time of the meal/exercise
    fig.add_shape(type="line", xref="x", yref="y", x0=time, y0=70, 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
            fig.add_shape(type="line", xref="x", yref="y", x0=activityDateTime, y0=70, 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.002, 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(day), tickformat='%H:%M')
# Hide the legend
fig.update_layout(showlegend=False)

# Draw sleep
# 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,)
# 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,)

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

# Show the chart
fig.show()