def keys_from_icg(conn=get_conn()):
curs = conn.cursor()
select = 'SELECT * FROM insulin_carb_grouped '
curs.execute(select)
global IC_KEYS
IC_KEYS = [d[0] for d in curs.description]
print 'IC_KEYS ', IC_KEYS
def bad_row(label):
conn = get_conn()
curs = conn.cursor()
rows = curs.execute(
"select * from insulin_carb_smoothed where rtime = '2014-03-14 17:15:00'"
)
print label, 'bad row is there'
def get_first_corrective_insulin(year=2018):
conn = get_conn()
curs = conn.cursor(MySQLdb.cursors.DictCursor)
curs.execute('''SELECT min(rtime) as rtime from insulin_carb_smoothed_2
where corrective_insulin = 1 and year(rtime) = %s''',
[year])
start = curs.fetchone()['rtime']
return start
def browse_isf2(date=None,time=None):
# not sufficiently general, but okay for now
first = isf.get_first_corrective_insulin(year=2018)
first_date, first_time = (first.strftime('%Y-%m-%d'), first.strftime('%H:%M:%S'))
if date == None:
date,time = first_date,first_time
elif time == None:
date = first_date
try:
rtime_str,rtime_dt = date_ui.to_datestr(date,time)
except:
flash('invalid date: {} {}'.format(date,time))
return redirect(url_for('browse_isf2'))
print('browsing w/ start time = {}'.format(rtime_str))
if request.method == "POST":
# POST almost certainly means the user clicked the "next" button
# we'll assume that
conn = get_conn()
print('getting next ISF after {}'.format(rtime_str))
start_dt = isf.get_isf_next(conn,rtime_str)
return redirect(url_for('browse_isf2',
date=start_dt.strftime('%Y-%m-%d'),
time=start_dt.strftime('%H:%M:%S')))
else:
conn = get_conn()
rows = isf.get_isf_at(conn,rtime_str)
trouble = rows[0]['ISF_trouble']
# convert 'ok' to empty
trouble = '' if trouble == 'ok' else trouble
details = isf.get_isf_details(conn,rtime_dt)
return render_template('isf2.html',
isf_trouble = trouble,
script = url_for('browse_isf2',
date = rtime_dt.strftime('%Y-%m-%d'),
time = rtime_dt.strftime('%H:%M:%S')),
rows = rows,
details = details,
# decoration
page_title = ('''ISF for {dt:%A},
{dt:%B} {dt.day}, {dt.year},
at {dt.hour}:{dt.minute:02d}'''
.format(dt=rtime_dt)))
def get_all_isf_plus_buckets():
''' Returns all good isf values and all isf values in 2-hour time buckets'''
conn = get_conn()
curs = conn.cursor()
curs.execute('''SELECT isf from isf_details;''')
allData = curs.fetchall()
curs.execute('''select time_bucket(rtime),isf from isf_details;''')
bucketed = curs.fetchall()
bucket_list = [ [ row[1] for row in bucketed if row[0] == b ]
for b in range(0,24,2) ]
return(allData,bucket_list)
def join_bg_values():
conn = get_conn()
curs = conn.cursor()
# this takes 18.71 seconds, so be patient
curs.execute(
'''update insulin_carb_smoothed as ics inner join cgm_noduplicates as cgm using (rtime)
set ics.cgm = cgm.mgdl''')
# this only takes 0.77 seconds
curs.execute(
'''update insulin_carb_smoothed as ics inner join mgm_noduplicates as mgm using (rtime)
set ics.bg = mgm.mgdl''')
def get_isf(rtime):
conn = get_conn()
curs = conn.cursor(MySQLdb.cursors.DictCursor)
curs.execute('''SELECT rtime from insulin_carb_smoothed_2 where corrective_insulin = 1 and rtime > %s''',[rtime])
start = curs.fetchone()['rtime']
#get table from rtime
curs.execute('''SELECT rtime, corrective_insulin, bg, cgm, total_bolus_volume,ISF,ISF_rounded,ISF_trouble
from insulin_carb_smoothed_2
where rtime>= %s and rtime<= addtime(%s,'2:00')''',
[start,start])
return curs.fetchall()
def icg(start='2017-01-01', end='2017-01-02'):
conn = get_conn()
curs = conn.cursor()
get_cursor_columns(tablename='insulin_carb_grouped')
curs.execute((
"select * from insulin_carb_grouped where rtime >= '{start}' and rtime <= '{end}' "
.format(start=start, end=end)))
while True:
row = curs.fetchone()
if row is None:
return
yield row
def get_isf_for_years(start_year,end_year):
'''returns all isf values and all isf values in 2-hour time buckets for a specific time period (in years) '''
conn = get_conn()
curs = conn.cursor()
curs.execute('''SELECT isf from isf_details where year(rtime) >= %s and year(rtime)<= %s''',[start_year, end_year])
allData = curs.fetchall()
curs.execute('''SELECT time_bucket(rtime), isf from isf_details where year(rtime)>= %s and year(rtime) <= %s''',
[start_year, end_year])
bucketed = curs.fetchall()
bucket_list = [[ row[1] for row in bucketed if row[0] == b ]for b in range(0,24,2)]
return (allData, bucket_list)
def get_isf_for_bg (bg_value):
''' returns isf values and isf values in 2-hour time buckets for a specific starting bg value'''
conn = get_conn()
curs = conn.cursor()
curs.execute('''SELECT time_bucket(rtime), isf from isf_details where bg0 < %s ''', [bg_value])
less_than = curs.fetchall()
less_than_list = [ [ row[1] for row in less_than if row[0] == b] for b in range(0,24,2)]
curs.execute('''SELECT time_bucket(rtime), isf from isf_details where bg0 > %s ''', [bg_value])
greater_than = curs.fetchall()
greater_than_list = [[row[1] for row in greater_than if row[0] == b] for b in range(0,24,2)]
return (less_than_list, greater_than_list)
def csv_dump(table, CSVfilename):
conn = get_conn()
curs = conn.cursor(MySQLdb.cursors.DictCursor) # results as Dictionaries
curs.execute('select * from {table} limit 1'.format(table=table))
row = curs.fetchone()
keys = row.keys()
curs = conn.cursor() # results as lists
curs.execute('select * from {table}'.format(table=table))
with open(CSVfilename, 'wb') as csvfile:
writer = csv.writer(csvfile) # default format is Excel
writer.writerow(keys)
while True:
row = curs.fetchone()
if row is None:
break
writer.writerow(row)
def ic_output_list(rows, tablename, keys):
'''Write rows to the given table. Rows can be an iterable. Each row is a list. '''
conn = get_conn()
curs = conn.cursor()
# clear out old data
curs.execute('delete from {table}'.format(table=tablename))
sql = 'insert into {table}({cols}) values({vals})'.format(
table=tablename,
cols=','.join(keys),
vals=','.join(['%s' for k in cursor_columns]))
print('insert using ', sql)
insert_count = 0
for row in rows:
insert_count += 1
if insert_count % 10000 == 0:
print 'ic_output_list to {} '.format(tablename), str(insert_count)
curs.execute(sql, row)
def ic_output_dict(rows, tablename, keys):
'''Write rows (can be an iterable) but each row is a dict not list to the given table'''
conn = get_conn()
curs = conn.cursor()
# clear out old data
curs.execute('delete from {table}'.format(table=tablename))
sql = 'insert into {table}({cols}) values({vals})'.format(
table=tablename,
cols=','.join(keys),
vals=','.join(['%s' for k in keys]))
print('insert using ', sql)
insert_count = 0
for row in rows:
insert_count += 1
if insert_count % 10000 == 0:
print 'ic_output_dict to {} '.format(tablename), str(insert_count)
# this would also be more efficient if we kept things as lists throughout
curs.execute(sql, [row[k] for k in keys])
def gen_rows_ic_dictionaries(conn=get_conn(),
start=None,
end=None,
tablename='insulin_carb_smoothed'):
curs = conn.cursor(MySQLdb.cursors.DictCursor) # results as Dictionaries
select = 'SELECT * FROM {} '.format(tablename)
if start is not None:
select += " WHERE rtime >= '{start}' and rtime <= '{end}' ".format(
start=start, end=end)
print 'gen_rows_ic in table {} with query {} '.format(tablename, select)
curs.execute(select)
global cursor_columns
cursor_columns = [desc[0] for desc in curs.description]
print 'cursor_columns: ', cursor_columns
while True:
row = curs.fetchone()
if row is None:
return
yield row
def test_compute_minutes_since():
rows = gen_rows_ic(
end='2014-05-01'
) # this gets more columns than we need, but maybe we can optimize that later
global cursor_columns
get_cursor_columns()
col1 = cursor_columns.index('minutes_since_last_bolus')
col2 = cursor_columns.index('minutes_since_last_meal')
col3 = cursor_columns.index('rtime')
conn = get_conn()
num_rows = 0
update = conn.cursor()
for row in compute_minutes_since(rows):
num_rows += 1
if num_rows % 1000 == 0:
print('updated ', num_rows)
update.execute(
'update insulin_carb_smoothed set minutes_since_last_bolus = %s, minutes_since_last_meal = %s where rtime = %s',
[row[col1], row[col2], row[col3]])
def getRecentISF (time_bucket, num_weeks, min_data, debug=False):
'''returns at least min_data isf values for a specific 2-hour time bucket looking back num_weeks (or more depending on available data points) '''
conn = get_conn ()
curs = conn.cursor()
def try_weeks(num_weeks):
time_end = datetime.strptime("18/09/10", '%y/%m/%d') - timedelta(weeks = num_weeks)
curs.execute ('''SELECT isf FROM isf_details where time_bucket(rtime) = %s and rtime > %s ''',
[time_bucket, time_end])
def doubling_up(min_weeks):
if debug:
print(('doubling up ',min_weeks))
try_weeks(min_weeks)
if curs.rowcount >= min_data:
return recur(int(min_weeks/2), min_weeks)
else:
return doubling_up(min_weeks*2)
def recur(min_weeks, max_weeks):
mid = int((min_weeks+max_weeks)/2)
if debug:
print(('recur ',min_weeks, mid, max_weeks))
try_weeks(mid)
if max_weeks == min_weeks + 1:
# done, so either use min or max
if (curs.rowcount < min_data):
min_weeks = max_weeks
try_weeks(min_weeks)
# base case:
results = curs.fetchall()
isf = [result[0] for result in results]
isf = sorted(isf)
return min_weeks, isf
elif curs.rowcount >= min_data:
# try lower half
return recur(min_weeks,mid)
else:
# try upper half
return recur(mid,max_weeks)
return doubling_up(num_weeks)
def gen_rows_ic(
conn=get_conn(), start=None, end=None,
tablename='insulin_carb_smoothed'):
curs = conn.cursor()
select = 'SELECT * FROM {} '.format(tablename)
if start is not None:
select += " WHERE rtime >= '{start}' and rtime <= '{end}' ".format(
start=start, end=end)
print 'gen_rows_ic in table {} with query {} '.format(tablename, select)
curs.execute(select)
get_cursor_columns(
) # caller may have to do this anyhow, because of the deferred execution of a generator
global cursor_columns
print 'cursor_columns: ', cursor_columns
while True:
row = curs.fetchone()
if row is None:
return
yield list(
row
) # conses a lot, but fetchone() returns tuples, and we can't modify those.
def ztest(where, split):
conn = get_conn()
curs = conn.cursor()
curs.execute('''
select {cond} as grp, avg(isf) as mean, stddev(isf) as sd,count(isf) as n
from isf_details
where {where}
group by {cond}
'''.format(where=where, cond=split))
print 'where: ', where
print 'split condition: ', split
(grp1, mean1, sd1, n1) = curs.fetchone()
(grp2, mean2, sd2, n2) = curs.fetchone()
print(grp1, mean1, sd1, n1)
print(grp2, mean2, sd2, n2)
sp = math.sqrt(
((n1 - 1) * sd1 * sd1 + (n2 - 1) * sd2 * sd2) / (n1 + n2 - 2))
print 'pooled sp', sp
diff = mean2 - mean1
print 'diff ', diff
t = diff / (sp * math.sqrt((1 / float(n1)) + (1 / float(n2))))
return (mean1, mean2, diff, sp, t)
def db_update_rtime(rows, tablename, keys=cursor_columns):
'''Update rows (can be an iterable) to the given table'''
conn = get_conn()
curs = conn.cursor()
curs.execute('select * from {table}'.format(table=tablename))
global cursor_columns
cursor_columns = [desc[0] for desc in curs.description]
print '\n in db_update_rtime, cursor_columns is ', cursor_columns
settings = ','.join(
['{col} = %s'.format(col=key) for key in cursor_columns])
curs = conn.cursor()
sql = 'update {} set {} where rtime = %s'.format(tablename, settings)
print('update using ', sql)
insert_count = 0
for row in rows:
if len(row) != len(cursor_columns):
raise Exception('row has wrong length')
insert_count += 1
if insert_count % 10000 == 0:
print 'updated ', str(insert_count)
data = list(row)
data.append(row_get(row, 'rtime')) # extra occurrence for the key
curs.execute(sql, data)
print 'done updating'
def compute_isf():
conn = get_conn()
curs = conn.cursor(MySQLdb.cursors.DictCursor)
curs.execute('update insulin_carb_smoothed_2 SET ISF_trouble = null, isf = null, ISF_rounded = null')
curs.execute('delete from isfvals') # make sure table is initially empty
curs.execute('delete from isf_details') # make sure table is initially empty
curs.execute('''select rtime, total_bolus_volume from insulin_carb_smoothed_2
where corrective_insulin = 1 ''')
# and year(rtime) = 2018''')
rows = curs.fetchall()
# some stats
total = len(rows)
skipped_before = 0 # events skipped because of insulin in BEFORE period
skipped_small = 0 # events skipped because bolus too small
skipped_middle = 0 # events skipped because bolus during MIDDLE period
skipped_nobg_beg = 0 # events skipped because start or end BG value missing (or both)
skipped_nobg_end = 0
good_isf = 0 # events with good ISF value
insulin_before = 0 #events with insulin 4 hours prior to the event
for row in rows:
start = row
t2 = start['rtime']
t1 = t2 - timedelta(minutes=100)
prior_insulin = any_bolus_in_time_span(conn, t2 - timedelta(hours=4),t1)
if any_bolus_in_time_span(conn, t1,t2 - timedelta(minutes=5)):
# print 'skipping {} because of insulin in the BEFORE period: {} to {}'.format(t2,t1,t2)
skipped_before += 1
curs.execute('''UPDATE insulin_carb_smoothed_2 SET ISF_trouble = %s where rtime = %s''',
['insulin before', t2])
continue
# get values for the next 2:30 (worst case scenario)
rows = get_window(curs, start['rtime'])
# Sum the boluses in the first 30 minutes
bolus_sum, t3 = bolus_sum_during_start(rows,t2)
if t3 != t2:
# this is just informational. Can be deleted
# print 'boluses in beginning from {} to {} sum to {}'.format(t2,t3,bolus_sum)
pass
if bolus_sum < min_bolus:
# print 'bolus sum {} is too small; skipping {}'.format(bolus_sum,t3)
curs.execute('''UPDATE insulin_carb_smoothed_2 SET ISF_trouble = %s where rtime = %s''',
['bolus too small', t3])
skipped_small += 1
continue
# from now on, use t3 rather than t2, particularly for looking up BG
t4 = t3 + timedelta(minutes=100)
t5 = t4 + timedelta(minutes=20)
# Check for no boluses in middle time
if boluses_in_time_range(rows, t3+timedelta(minutes=5), t4-timedelta(minutes=5)):
# print 'skipping {} because of insulin in the middle period'.format(t3)
skipped_middle += 1
curs.execute('''UPDATE insulin_carb_smoothed_2 SET ISF_trouble = %s where rtime = %s''',
['insulin in middle', t3])
if t2 != t3:
curs.execute('''UPDATE insulin_carb_smoothed_2 SET ISF_trouble = %s where rtime = %s''',
['insulin in middle', t2])
continue
# Check whether there were boluses in end time
boluses_in_end = boluses_in_time_range(rows, t4, t5)
#boluses_in_time_range(rows, t4+timedelta(minutes=5), t5)
if boluses_in_end:
# print 'insulin in the end period: {} to {}'.format(t4,t5)
pass
# Okay, ready for calculation.
bg_at_t3 = bg_at_time(rows, t3)
bg_rows = get_bg_time_window(curs,t5)
bg_at_t5 = bg_at_time_extended(bg_rows,t5)
if bg_at_t3 and bg_at_t5:
if prior_insulin:
insulin_before += 1
isf = (bg_at_t3 - bg_at_t5) / bolus_sum
#print 'isf {} to {} => ( {} - {} ) / {} => {:.2f} '.format(t3,t5,bg_at_t3, bg_at_t5, bolus_sum, isf)
good_isf += 1
if boluses_in_end:
curs.execute('''UPDATE insulin_carb_smoothed_2 SET ISF_trouble = %s, ISF_rounded = %s where rtime = %s''',
['insulin at end', isf, t3])
else:
curs.execute('''UPDATE insulin_carb_smoothed_2 SET isf = %s where rtime = %s''', [isf, t3])
# New: record details of the calc in the isfvals table. [Scott 12/13/2019]
#curs.execute('''insert into isf_details(rtime,isf,bg0,bg1,bolus) values (%s,%s,%s,%s,%s)''',
# [t3, isf, bg_at_t3, bg_at_t5, bolus_sum])
curs.execute('''insert into isf_details(rtime,isf,bg0,bg1,bolus,prior_insulin) values (%s,%s,%s,%s,%s,%s)''',[t3,isf,bg_at_t3,bg_at_t5, bolus_sum,prior_insulin])
else:
#skipped_bg += 1
skipped = False
if not bg_at_t3:
skipped_nobg_beg += 1
skipped = True
curs.execute('''UPDATE insulin_carb_smoothed_2 set ISF_trouble = %s where rtime = %s''',
[ 'nobg', t3]) # 'missing start BG value'
if not bg_at_t5 and not skipped:
skipped_nobg_end += 1
#print 'nobg at end', t3
curs.execute('''UPDATE insulin_carb_smoothed_2 set ISF_trouble = %s where rtime = %s''',
[ 'nobg', t3]) # 'missing end BG value'
#print "start: ", startbg, "end: ", endbg
# check if any additional insulin given within 30 minutes of start -- done
# check if any additional insulin given later in that time range -- done
# check if we have a CGM or BG value near the beginning of the range AND -- done
# check if we have a CGM or BG value near the end of the range -- done
# if we have *both* CGM and BG, take the BG
# compute ISF based on starting and ending CGM or BG
# update database using start (the primary key for ICS2)
#raw_input('another?')
# end of loop
print('''There were {} corrective insulin events from 2014 - 2018.
{} events were skipped because of insulin before the START period
{} events were skipped because the bolus was too small
{} events were skipped because there was a bolus in the MIDDLE period
{} events were skipped because start BG was not available,
{} events were skipped because end BG was not available, leaving
{} events with a good ISF and
{} good events with insulin within 4 hours before event
{} skipped + {} good is {} total.'''.format(total, skipped_before, skipped_small, skipped_middle, skipped_nobg_beg,skipped_nobg_end,good_isf,insulin_before,
(skipped_before + skipped_small + skipped_middle + skipped_nobg_beg + skipped_nobg_end),
good_isf,total))
def get_cursor_columns(conn=get_conn(), tablename='insulin_carb_smoothed'):
curs = conn.cursor()
curs.execute('select * from {} limit 0'.format(tablename))
global cursor_columns
cursor_columns = [desc[0] for desc in curs.description]
return cursor_columns
def num_rows(conn=get_conn(), tablename='insulin_carb_smoothed'):
curs = conn.cursor()
curs.execute('select count(*) from {}'.format(tablename))
row = curs.fetchone()
return row[0]
def gen_insulin_carb_vrows(conn=get_conn(), rows=None, pipeIn=None):
'''This version generates virtual rows from rows (real ones), with
timestamps at 5 minute intervals. It's the basis of the
insulin_carb_smoothed table.'''
# Loop over real rows, creating virtual rows. The invariant is that
# curr <= vrow < next. When vrow catches up to next, discard curr,
# make curr be next, and get another next. If timestamp of new next is
# equal to curr, merge them and get another next.
# Now that we are reading from ICG, merging should never happen.
curr_row = rows.next()
next_row = rows.next()
curr_time = curr_row['rtime']
next_time = next_row['rtime']
if curr_time == next_time:
raise Exception('before loop: next row time equals curr row time')
vrow_time = curr_time
delta5 = timedelta(minutes=5)
global merged_rows # should stay empty
debug = False
merged_rows = []
# print('vrow_time',vrow_time,'next_time',next_time)
try:
# loop over vrows. Exit condition is when we get StopIteration from real rows
while True:
# This outer loop produces vrows
# print('vrow_time',vrow_time,'next_time',next_time)
if vrow_time >= next_time:
# this inner loop pulls in real rows
# advance everything. if there's no next row, this will raise
# StopIteration, which is perfect
while True:
curr_row = next_row
curr_time = next_time
next_row = rows.next()
next_time = next_row['rtime']
if next_time > curr_time:
break
# merge from curr into next, since the top of the
# loop discards curr_row. Should not happen
merge_rows(curr_row, next_row)
if vrow_time == curr_time:
# yield a real row
yield curr_row
else:
# yield a virtual row.
vrow = {
'rtime': vrow_time,
'real_row': 0,
'user': curr_row['user']
}
# fill in other fields with None
for key in curr_row.iterkeys():
if key not in vrow:
vrow[key] = None
yield vrow
vrow_time = vrow_time + delta5
except StopIteration:
yield curr_row
print '''Done generating virtual rows. End time is {end}
and we merged {nmerge} rows'''.format(end=curr_row['rtime'],
nmerge=len(merged_rows))
