My LiveCompare Diabetes App, Part 2

This is the second in a series of posts that introduces LiveCompare's new "apps" system. I'm building a Diabetes Tracker app that will replace my current Excel-based tracker. By the end of the process I'll have migrated all my existing data, produced a dashboard that I can use with my Diabetic screenings and I'll run everything from my smartphone. The first post covered the basic data model and data entry workflows. In this post I'll cover migrating the Excel data to LiveCompare.

When I was first diagnosed with Diabetes, my local hospital gave me a little book for recording my blood sugar readings. Rather than record with paper and pencil I copied the book's layout into Excel and have used that ever since – I record each month on a separate sheet. As it happens the layout is not ideal for data processing because of the multiple header rows and there are columns from the book that I don't use and each sheet maintains a few summary stats and graphs. Here's an example from way back in 2009:

 

Insulin Injection Time

Blood glucose level (mmol/l)

Date

    

Before breakfast

2 hours after breakfast

Before midday meal

2 hours after midday meal

Before evening meal

2 hours after evening meal

Befor ebed

During night

28/01/2009

    

  

  

  

  

  

  

8.7

 

29/01/2009

    

10.3

15.1

12.5

13.2

11.8

12.4

12.0

 

 

There are a couple of things to note:

  • Multiple headings: rows 1 and 2

  • A time slot that I'd forgotten about – During Night (that I've never used). Since added as slot 7.

To prepare my data I wrote a little Python program using the excellent pyodbc library:

import pyodbc

 

def table_exists(cursor, tableName):

result = False

 

# row.table_name has leading and trailing "'".

table_name_to_compare = "'{0}'".format(tableName)

 

for row in cursor.tables():

if table_name_to_compare == row.table_name:

result = True

 

return result

 

def process_table(cursor, tableName):

if not table_exists(cursor, tableName):

return;

 

cursor.execute('SELECT * FROM [' + tableName + ']')

 

# Skip multiple header rows

while 1:

row = cursor.fetchone()

 

if row[0]:

break;

 

while 1:

dt = row[0]

 

for iii in xrange(5, 13):

slot = iii - 5

reading = row[iii]

 

if None != reading:

print dt, slot, reading

 

row = cursor.fetchone()

 

if None == row[0]:

break;

 

 

cn = pyodbc.connect('DRIVER={Microsoft Excel Driver (*.xls)};DBQ=*******************;', autocommit=True)

c = cn.cursor()

 

for yy in xrange(9, 14):

for mm in xrange(1, 13):

table_name = '{:02d}-{:02d}$'.format(mm, yy)

process_table(c, table_name)

 

 

The program works its way through the sheets – I used a simple naming pattern of [MM-YY] when I created the workbook, so the program generates all possible values (there will be gaps, I've not been totally diligent in tracking my blood sugar levels). When a sheet is found, the first rows are skipped until a date value is found. The program outputs a simpler table of values:

<DATE>, <SLOT>, <MMOL>

This structure matches the Bloods table I created in Part 1.

The processing of the Excel sheets gave me 641 records. As a quick example of how much easier it is to analyse the data now that I have it in LiveCompare I wrote a simple query to compute the MIN, MAX and AVG over all 641 records:

YEAR

MIN_MMOL

MAX_MMOL

AVG_MMOL

2009

3.2

15.1

6.270579268

2010

3.1

8.8

5.514814815

2011

4

12

6.265656566

2012

4.3

16.7

6.893902439

2013

2.7

18.4

7.212820513


The data for Weights and BPs is easier to manually prepare because there is much less of it.

My final workflow for populating the app XDS looks like:

That's it for this blog post. Next time I'll cover the workflows that run queries to support the dashboard before finally registering the app and running it from my smartphone.