def compute_mean_max_power(self, dbinsert=False):
if self.max_watts is not None:
df = self.df_samples.copy()
df = df.rename(columns={
"watts": "power",
"velocity_smooth": "speed"
})
self.mmp_df = pd.Series(
WorkoutDataFrame(df).compute_mean_max_power(),
name='mmp').to_frame()
# Update time index so it starts at 1 second (instead of 0)
self.mmp_df['time'] = [x for x in range(1, len(self.mmp_df) + 1)]
self.mmp_df.set_index('time', inplace=True)
if dbinsert:
df = self.df_samples.copy()
df.rename(columns={'time': 'interval'}, inplace=True)
for col in [
'distance', 'velocity_smooth', 'heartrate', 'cadence',
'watts', 'moving', 'grade_smooth', 'latitude',
'longitude', 'altitude', 'power_zone', 'hr_zone',
'temp'
]:
if col in df.columns:
df = df.drop(columns=col)
df = df[df['interval'] != 0]
df['mmp'] = df['interval'].map(self.mmp_df['mmp'].to_dict())
df['watts_per_kg'] = df['mmp'] / self.kg
df['timestamp_local'] = df.index
df['type'] = self.type
df['athlete_id'] = self.athlete_id
df.set_index(['activity_id', 'interval'], inplace=True)
db_insert(df, 'strava_best_samples')
def write_dfs_to_db(self):
# Add athlete_id to df_summary
self.df_summary['athlete_id'] = [self.athlete_id]
self.df_summary['ftp'] = [self.ftp]
self.df_summary['trimp'] = [self.trimp]
self.df_summary['hrss'] = [self.hrss]
self.df_summary['relative_intensity'] = [self.ri]
self.df_summary['efficiency_factor'] = [self.efficiency_factor]
self.df_summary['tss'] = [self.tss]
self.df_summary['variability_index'] = [self.variability_index]
self.df_summary['weighted_average_power'] = [self.wap]
self.df_summary['weight'] = [self.weight]
# Add other columns to samples df
self.df_samples['type'] = self.type
self.df_samples['athlete_id'] = self.athlete_id
db_insert(self.df_summary.fillna(np.nan), 'strava_summary')
db_insert(self.df_samples.fillna(np.nan), 'strava_samples')
def insert_sleep_data(df_sleep_summary, df_sleep_samples, days_back=7):
session, engine = db_connect()
start = session.query(func.max(ouraSleepSummary.report_date))[0][0]
start = '1999-01-01' if start is None else datetime.strftime(
start - timedelta(days=days_back), '%Y-%m-%d')
# Delete latest dates records from db to ensure values are being overridden from api pull
try:
dash_app.server.logger.debug(
'Deleting >= {} records from oura_sleep_summary'.format(start))
session.execute(
delete(ouraSleepSummary).where(
ouraSleepSummary.summary_date >= start))
dash_app.server.logger.debug(
'Deleting >= {} records from oura_sleep_samples'.format(start))
session.execute(
delete(ouraSleepSamples).where(
ouraSleepSamples.summary_date >= start))
session.commit()
except BaseException as e:
dash_app.server.logger.error(e)
engine.dispose()
session.close()
# print(df_sleep_samples.index)
# print(df_sleep_summary.index)
# Insert Sleep Summary
dash_app.server.logger.debug('Inserting oura sleep summary')
try:
db_insert(df_sleep_summary, 'oura_sleep_summary')
except BaseException as e:
dash_app.server.logger.error(e)
# Insert Sleep Samples
# dash_app.server.logger.debug('Inserting oura sleep samples')
try:
db_insert(df_sleep_samples, 'oura_sleep_samples')
except BaseException as e:
dash_app.server.logger.error(e)
def insert_readiness_data(df_readiness_summary, days_back=7):
session, engine = db_connect()
start = session.query(func.max(ouraReadinessSummary.report_date))
start = '1999-01-01' if start[0][0] is None else datetime.strftime(
start[0][0] - timedelta(days=days_back), '%Y-%m-%d')
# Delete latest dates records from db to ensure values are being overridden from api pull
try:
dash_app.server.logger.debug(
'Deleting >= {} records from oura_readiness_summary'.format(start))
session.execute(
delete(ouraReadinessSummary).where(
ouraReadinessSummary.summary_date >= start))
session.commit()
except BaseException as e:
dash_app.server.logger.error(e)
engine.dispose()
session.close()
dash_app.server.logger.debug('Inserting oura readiness summary')
db_insert(df_readiness_summary, 'oura_readiness_summary')
def hrv_training_workflow(min_non_warmup_workout_time, athlete_id=1):
'''
Query db for oura hrv data, calculate rolling 7 day average, generate recommended workout and store in db.
Once stored, continuously check if workout has been completed and fill in 'Compelted' field
'''
# https://www.trainingpeaks.com/coach-blog/new-study-widens-hrv-evidence-for-more-athletes/
session, engine = db_connect()
# Check if entire table is empty, if so the earliest hrv plan can start is after 30 days of hrv readings
db_test = pd.read_sql(sql=session.query(hrvWorkoutStepLog).filter(
hrvWorkoutStepLog.athlete_id == athlete_id).statement,
con=engine,
index_col='date')
if len(db_test) == 0:
min_oura_date = session.query(func.min(
ouraSleepSummary.report_date))[0][0]
db_test.at[pd.to_datetime(min_oura_date + timedelta(29)),
'athlete_id'] = athlete_id
db_test.at[pd.to_datetime(min_oura_date + timedelta(29)),
'hrv_workout_step'] = 0
db_test.at[pd.to_datetime(min_oura_date + timedelta(29)),
'hrv_workout_step_desc'] = 'Low'
db_test.at[pd.to_datetime(min_oura_date + timedelta(29)),
'completed'] = 0
db_test.at[
pd.to_datetime(min_oura_date + timedelta(29)),
'rationale'] = 'This is the first date 30 day hrv thresholds could be calculated'
db_insert(db_test, 'hrv_workout_step_log')
# Check if a step has already been inserted for today and if so check if workout has been completed yet
todays_plan = session.query(hrvWorkoutStepLog).filter(
hrvWorkoutStepLog.athlete_id == athlete_id,
hrvWorkoutStepLog.date == datetime.today().date()).first()
if todays_plan:
# If not yet "completed" keep checking throughout day
if todays_plan.completed == 0:
# If rest day, mark as completed
if todays_plan.hrv_workout_step == 4 or todays_plan.hrv_workout_step == 5:
todays_plan.completed = 1
session.commit()
else:
workout = session.query(stravaSummary).filter(
stravaSummary.start_day_local == datetime.today().date(),
stravaSummary.elapsed_time >
min_non_warmup_workout_time).first()
if workout:
todays_plan.completed = 1
session.commit()
# If plan not yet created for today, create it
else:
hrv_df = pd.read_sql(
sql=session.query(ouraSleepSummary.report_date,
ouraSleepSummary.rmssd).statement,
con=engine,
index_col='report_date').sort_index(ascending=True)
# Wait for today's hrv to be loaded into cloud
if hrv_df.index.max() == datetime.today().date(
): # or (datetime.now() - timedelta(hours=12)) > pd.to_datetime(datetime.today().date()):
step_log_df = pd.read_sql(
sql=session.query(
hrvWorkoutStepLog.date, hrvWorkoutStepLog.hrv_workout_step,
hrvWorkoutStepLog.completed).filter(
hrvWorkoutStepLog.athlete_id == 1).statement,
con=engine,
index_col='date').sort_index(ascending=False)
step_log_df = step_log_df[step_log_df.index ==
step_log_df.index.max()]
# Store last step in variable for starting point in loop
last_db_step = step_log_df['hrv_workout_step'].iloc[0]
# Resample to today
step_log_df.at[pd.to_datetime(datetime.today().date()),
'hrv_workout_step'] = None
step_log_df = step_log_df.set_index(
pd.to_datetime(step_log_df.index))
step_log_df = step_log_df.resample('D').mean()
# Remove first row from df so it does not get re inserted into db
step_log_df = step_log_df.iloc[1:]
# We already know there is no step for today from "current_step" parameter, so manually add today's date
step_log_df.at[pd.to_datetime(datetime.today().date()),
'completed'] = 0
# Check if gap between today and max date in step log, if so merge in all workouts for 'completed' flag
if step_log_df['completed'].isnull().values.any():
workouts = pd.read_sql(sql=session.query(
stravaSummary.start_day_local,
stravaSummary.activity_id).filter(
stravaSummary.elapsed_time >
min_non_warmup_workout_time).statement,
con=engine,
index_col='start_day_local')
# Resample workouts to the per day level - just take max activity_id in case they were more than 1 workout for that day to avoid duplication of hrv data
workouts = workouts.set_index(pd.to_datetime(workouts.index))
workouts = workouts.resample('D').max()
step_log_df = step_log_df.merge(workouts,
how='left',
left_index=True,
right_index=True)
# Completed = True if a workout (not just warmup) was done on that day or was a rest day
for x in step_log_df.index:
step_log_df.at[x, 'completed'] = 0 if np.isnan(
step_log_df.at[x, 'activity_id']) else 1
# Generate row with yesterdays plan completions status for looping below through workout cycle logic
step_log_df['completed_yesterday'] = step_log_df[
'completed'].shift(1)
# Drop historical rows that were used for 'yesterday calcs' so we are only working with todays data
# step_log_df = step_log_df.iloc[1:]
# Calculate HRV metrics
hrv_df.set_index(pd.to_datetime(hrv_df.index), inplace=True)
hrv_df = hrv_df.resample('D').mean()
hrv_df['rmssd_7'] = hrv_df['rmssd'].rolling(7,
min_periods=0).mean()
hrv_df['rmssd_7_yesterday'] = hrv_df['rmssd_7'].shift(1)
hrv_df['rmssd_30'] = hrv_df['rmssd'].rolling(30,
min_periods=0).mean()
hrv_df['stdev_rmssd_30_threshold'] = hrv_df['rmssd'].rolling(
30, min_periods=0).std() * .5
hrv_df['swc_upper'] = hrv_df['rmssd_30'] + hrv_df[
'stdev_rmssd_30_threshold']
hrv_df['swc_lower'] = hrv_df['rmssd_30'] - hrv_df[
'stdev_rmssd_30_threshold']
hrv_df['under_low_threshold'] = hrv_df['rmssd_7'] < hrv_df[
'swc_lower']
hrv_df['under_low_threshold_yesterday'] = hrv_df[
'under_low_threshold'].shift(1)
hrv_df['over_upper_threshold'] = hrv_df['rmssd_7'] > hrv_df[
'swc_upper']
hrv_df['over_upper_threshold_yesterday'] = hrv_df[
'over_upper_threshold'].shift(1)
for i in hrv_df.index:
if hrv_df.at[
i,
'under_low_threshold_yesterday'] == False and hrv_df.at[
i, 'under_low_threshold'] == True:
hrv_df.at[i, 'lower_threshold_crossed'] = True
else:
hrv_df.at[i, 'lower_threshold_crossed'] = False
if hrv_df.at[
i,
'over_upper_threshold_yesterday'] == False and hrv_df.at[
i, 'over_upper_threshold'] == True:
hrv_df.at[i, 'upper_threshold_crossed'] = True
else:
hrv_df.at[i, 'upper_threshold_crossed'] = False
# Merge dfs
df = pd.merge(step_log_df,
hrv_df,
how='left',
right_index=True,
left_index=True)
last_step = last_db_step
for i in df.index:
# Completed / Completed_yesterday could show erroneous data for rest days, as the 0 is brought in based off if a workout is found in strava summary
df.at[
i,
'completed_yesterday'] = 1 if last_step == 4 or last_step == 5 else df.at[
i, 'completed_yesterday']
hrv_increase = df.at[i,
'rmssd_7'] >= df.at[i,
'rmssd_7_yesterday']
### Low Threshold Exceptions ###
# If lower threshold is crossed, switch to low intensity track
if df.at[i, 'lower_threshold_crossed'] == True:
current_step = 4
rationale = '7 day HRV average crossed the 30 day baseline lower threshold.'
dash_app.server.logger.debug(
'Lower threshold crossed. Setting current step = 4')
# If we are below lower threshold, rest until back over threshold
elif df.at[i, 'under_low_threshold'] == True:
current_step = 5
rationale = '7 day HRV average is under the 30 day baseline lower threshold.'
dash_app.server.logger.debug(
'HRV is under threshold. Setting current step = 5')
### Upper Threshold Exceptions ###
# If upper threshold is crossed, switch to high intensity
elif df.at[i, 'upper_threshold_crossed'] == True:
current_step = 1
rationale = '7 day HRV average crossed the 30 day baseline upper threshold.'
dash_app.server.logger.debug(
'Upper threshold crossed. Setting current step = 1')
# If we are above upper threshold, load high intensity until back under threshold
elif df.at[i, 'over_upper_threshold'] == True:
if hrv_increase:
current_step = 1
rationale = '7 day HRV average increased and is still over the 30 day baseline upper threshold.'
else:
current_step = 2
rationale = "7 day HRV average decreased but is still over the 30 day baseline upper threshold."
dash_app.server.logger.debug(
'HRV is above threshold. Setting current step = {}.'.
format(current_step))
### Missed Workout Exceptions ###
# If workout was not completed yesterday but we are still within thresholds and current step is high/moderate go high if hrv increases, or stay on moderate if hrv decreases
elif df.at[i, 'completed_yesterday'] == 0 and df.at[
i, 'under_low_threshold'] == False and df.at[
i, 'over_upper_threshold'] == False and (
last_step == 1 or last_step == 2):
if hrv_increase:
current_step = 1
rationale = "7 day HRV average increased and yesterday's workout was not completed."
else:
current_step = 2
rationale = "7 day HRV average decreased and yesterday's workout was not completed."
dash_app.server.logger.debug(
'No workout detected for previous day however still within thresholds. Maintaining last step = {}'
.format(current_step))
else:
dash_app.server.logger.debug(
'No exceptions detected. Following the normal workout plan workflow.'
)
rationale = '7 day HRV average is within the tresholds. Following the normal workout plan workflow.'
# Workout workflow logic when no exceptions
if last_step == 0:
current_step = 1
elif last_step == 1:
current_step = 2 if hrv_increase else 6
elif last_step == 2:
current_step = 3
elif last_step == 3:
current_step = 1 if hrv_increase else 4
elif last_step == 4:
current_step = 6 if hrv_increase else 5
elif last_step == 5:
current_step = 6
elif last_step == 6:
current_step = 1 if hrv_increase else 4
df.at[
i,
'completed'] = 1 if current_step == 4 or current_step == 5 else df.at[
i, 'completed']
df.at[i, 'hrv_workout_step'] = current_step
last_step = current_step
df.at[i, 'rationale'] = rationale
df['athlete_id'] = athlete_id
df['hrv_workout_step_desc'] = df['hrv_workout_step'].map({
0: 'Low',
1: 'High',
2: 'HIIT/MOD',
3: 'Low',
4: 'Rest',
5: 'Rest',
6: 'Low'
})
# Insert into db
df = df[[
'athlete_id', 'hrv_workout_step', 'hrv_workout_step_desc',
'completed', 'rationale'
]]
db_insert(df, 'hrv_workout_step_log')
engine.dispose()
session.close()