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