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
• Calculate key metrics for that day
• Retrieve sleep data from an outside Excel file for that day
• Create a chart of the glucose values and include metrics

import pandas as pd
import plotly.express as px
import datetime
from datetime import date
import numpy as np
from numpy import trapz
from openpyxl import Workbook, load_workbook

# Read in CSV file
df = pd.read_csv('export.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	100.0	NaN	NaN	NaN	NaN	2021-08-15 23:48:06	NaN	NaN	NaN	NaN	NaN	2021-08-15
1	GlucoseMeasurement	99.0	NaN	NaN	NaN	NaN	2021-08-15 23:33:06	NaN	NaN	NaN	NaN	NaN	2021-08-15
2	GlucoseMeasurement	99.0	NaN	NaN	NaN	NaN	2021-08-15 23:18:06	NaN	NaN	NaN	NaN	NaN	2021-08-15
3	GlucoseMeasurement	98.0	NaN	NaN	NaN	NaN	2021-08-15 23:03:06	NaN	NaN	NaN	NaN	NaN	2021-08-15
4	GlucoseMeasurement	97.0	NaN	NaN	NaN	NaN	2021-08-15 22:48:06	NaN	NaN	NaN	NaN	NaN	2021-08-15

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

['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)

# 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))

Number of minutes above 120: 74
Time above Threshold = 5%
Average Glucose = 94
Median Glucose  = 91

# 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

fig = px.line(gm_data, y="value")

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

    # 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"

    # 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=140, line_color=eventColor,)
    
    # Alternate text placement so adjacent text doesn't overlap
    if (yText == 60): yText = 65
    else: yText = 60
    
    # Add text
    fig.add_annotation(text=row['description'], xref="x", yref="y", x=time, y=yText, showarrow=False, font=dict(color=eventColor))


# 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",)

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

# Show text box with summary values
fig.add_annotation(
                text='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),
                align='right', showarrow=False,
                xref='paper', yref='paper', x=0.002, y=0.005,
                bordercolor='black', borderwidth=1
            )

# Set x and y axis title and unit
fig.update_xaxes(title_text=str(day), tickformat='%H:%M')
fig.update_yaxes(title_text='mg/dL')
fig.show()