<jupyter_start><jupyter_text>Load Libraries<jupyter_code>import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Load total CSV after downloading from STRAVA API<jupyter_code># Load it into a Dataframe using pandas
path = 'python_data/strava_angelos_20170812122309.csv'
df = pd.read_csv(path)
df.head()
# Comparing with test data to spot any difference
path_test = 'python_data/strava_angelos_test.csv'
df_test = pd.read_csv(path_test)
df_test.head()<jupyter_output><empty_output><jupyter_text>Useful Functions<jupyter_code># Trim data per specific time threshold
def trimmer(time_df, thresh):
    cur = None
    for i, item in time_df.iteritems():
        if (cur is None) or (item - cur >= thresh):
            yield i
            cur = item
# Create a string format for the Day column
def dayConverter(s):
    # Set date formats
    time_format = "%Y-%m-%d"

    # Convert from period to string
    converted = s.strftime(time_format)
    
    return converted
# Add distance to all activities
def distanceEnhancer(added,x):
    return x + added
    
# Transforms seconds to hours
def secToHours(d):
    # Create the rule
    seconds = d
    minutes, seconds = divmod(seconds, 60)
    hours, minutes = divmod(minutes, 60)
    
    #Convert all to string
    seconds = str(seconds)
    minutes = str(minutes)
    hours = str(hours)
    
    #Add 0(zeros) for single digit numbers
    if len(seconds)==1:
        seconds = '0'+seconds
    if len(minutes)==1:
        minutes = '0'+minutes
    if len(hours)==1:
        hours = '0'+hours
    return hours+':'+minutes+':'+seconds
# Transform hours to seconds
def hoursToSec(s, factors=(1, 60, 3600)): 
    """[[hours:]minutes:]seconds -> seconds"""
    return int(sum(x*y for x, y in zip(map(float, s.split(':')[::-1]), factors)))<jupyter_output><empty_output><jupyter_text>Modify the data<jupyter_code># Convert to act_StartDate to datetime
df['act_startDate'] = pd.to_datetime(df['act_startDate'])
df['timestamp'] = pd.to_datetime(df['timestamp'])

# Keep only date as a period in a seperate column
df['day'] = pd.DatetimeIndex(df['act_startDate']).to_period('D')

# Convert days to strings
df['day'] = df['day'].apply(lambda x: dayConverter(x))

# Sort df by ascending day and timestamp
df = df.sort_values(by=['day','timestamp'],ascending=[True,True])

# Check main df
df.head()<jupyter_output><empty_output><jupyter_text>Create a day number flag for keeping track of the trip days     Create an iteration number flag for keeping track of activities per day<jupyter_code># Create helper dataframe with unique days
df_helper = df.groupby(by=['day','act_id']).count().iloc[:,0].reset_index().filter(items=['day','act_id'])

# Create columns for iter_no(per activity) and day_no(per day)
days = list(set(df_helper['day']))
days.sort()
day_no = list()
iter_no = list()
for index,day in enumerate(days):
    counter=1
    for dfday in df_helper['day']:
        if dfday == day:
            iter_no.append(counter)
            day_no.append(index+1)
            counter+=1

df_helper['day_no'] = pd.Series(day_no).values
df_helper['iter_no'] = pd.Series(iter_no).values      

df_helper
# Merge flagger with the main dataframe
df = pd.merge(df,df_helper,on=['day','act_id'])

# Check main df
df.head()<jupyter_output><empty_output><jupyter_text>Create cumulative distance/time when changing activity Keep the last row of each iteration of the same day and add its number to all the rest of the next day and do the same for the next activity<jupyter_code># Transform distance to cumulative distance when changing activity (per day)
for day in days:
    act_day = list(df_helper[df_helper['day']==day]['act_id'])
    if len(act_day)>1:
        last_dist = []
        last_time = []
        for i,act in enumerate(act_day):
            adding_dist = float(df[(df['day']==day) & (df['act_id']==act)]['distance'].iloc[-1:]) # +1
            adding_time = int(df[(df['day']==day) & (df['act_id']==act)]['time'].iloc[-1:]) # +1 if problem with division
            if i == 0:
                last_dist.append(adding_dist)
                last_time.append(adding_time)
            else:
                adding_dist_plus = adding_dist+last_dist[i-1]
                adding_time_plus = adding_time+last_time[i-1]
                last_dist.append(adding_dist_plus)
                last_time.append(adding_time_plus)
                df.loc[(df['day']==day) & (df['act_id']==act),'distance'] = df[(df['day']==day) & (df['act_id']==act)]['distance'].apply(
                    lambda x: distanceEnhancer(last_dist[i-1],x))
                df.loc[(df['day']==day) & (df['act_id']==act),'time'] = df[(df['day']==day) & (df['act_id']==act)]['time'].apply(
                    lambda x: distanceEnhancer(last_time[i-1],x))
        
#new = df.groupby(by=['day','act_id']).count().iloc[:,0].reset_index().filter(items=['day','act_id'])<jupyter_output><empty_output><jupyter_text>Create elevation gain per day  Create elevation gain per day by summing only the positive differences between each consecutive point way before trimming.<jupyter_code># Create total elevation per day dataframe
result = {}
for day in days:
    temp_alt_df = df[df['day'] == day]['altitude']
    deltas = []
    for i in range(len(temp_alt_df)):
        if i>0:
            delta = temp_alt_df.iloc[i]-temp_alt_df.iloc[i-1]
            if delta>0:
                deltas.append(delta)
    result[day] = sum(deltas)
    
# Create dataframe from result dictionary
alt_ttl_df = pd.DataFrame(result.items(), columns=['day', 'elevation_gain'])
alt_ttl_df<jupyter_output><empty_output><jupyter_text>Create total distance in KM per day  Create total distance in km per day by picking the last value of distance column for each day<jupyter_code>temp = {}
for day in days:
    temp_dist_df = df[df['day'] == day]['distance']
    temp[day]=round(temp_dist_df.iloc[-1]/1000,1)

# Create dataframe from temp dictionary
dist_ttl_df = pd.DataFrame(temp.items(), columns=['day', 'ttl_distance'])
dist_ttl_df<jupyter_output><empty_output><jupyter_text>Create a speed column km/h<jupyter_code># Create an empty dataframe
speed_df = pd.DataFrame(columns = ['time','distance','speed'])

# Calculate the speed by measuring the m/s times 3.6 for km/h
for day in days:
    temp_speed_df = df[df['day'] == day].filter(items = ['time','distance'])
    speed_list = []
    for i in range(len(temp_speed_df)):
        if i==0:
            speed_list.append(0)
        else:
            dist_delta = float(temp_speed_df['distance'].iloc[i]-temp_speed_df['distance'].iloc[i-1])
            time_delta = int(temp_speed_df['time'].iloc[i]-temp_speed_df['time'].iloc[i-1])
            if time_delta == 0: # handles division error
                speed = speed_list[i-1]
                speed_list.append(speed)
            else:
                speed = (dist_delta/time_delta) * 3.6
                speed_list.append(speed)
    # Add new speed column to temp_speed_df
    temp_speed_df['speed'] = np.asarray(speed_list)
    speed_df = pd.concat([temp_speed_df,speed_df])<jupyter_output><empty_output><jupyter_text>Check if the lengths match each other<jupyter_code>print len(speed_df)
print len(df)
# Merge speed_df with the main dataframe on indexes
df = df.join(speed_df['speed'], how='outer')<jupyter_output><empty_output><jupyter_text>Make sure that the speed makes sense<jupyter_code>df[df['speed']>100]
df.iloc[21945:21950]
# Remove those lines with extremely high speed. There must be inaccurate tracking
df = df[df['speed']<100]<jupyter_output><empty_output><jupyter_text>Create max and average speed per day and heartbeat<jupyter_code># Create table with avg_speed
avg_speed_df = df.groupby('day').mean().reset_index().sort_values(by='day', ascending=1).filter(items=['day','speed'])

# Change column name
avg_speed_df.rename(columns={'speed': 'avg_speed'}, inplace=True)

# Create table with avg_speed
max_speed_df = df.groupby('day').max().reset_index().sort_values(by='day', ascending=1).filter(items=['day','speed'])

# Change column name
max_speed_df.rename(columns={'speed': 'max_speed'}, inplace=True)

# Merge two dfs
ttl_speed_df = pd.merge(avg_speed_df,max_speed_df,on='day')

# Create table with avg_speed
avg_heartbeat_df = df.groupby('day').mean().reset_index().sort_values(by='day', ascending=1).filter(items=['day','heartrate'])

# Change column name
avg_heartbeat_df.rename(columns={'heartrate': 'avg_active_HR'}, inplace=True)

# Merge two dfs
ttl_speed_df = pd.merge(ttl_speed_df,avg_heartbeat_df,on='day')

ttl_speed_df<jupyter_output><empty_output><jupyter_text>Create cumulative distance in string format (hours:minutes:seconds)<jupyter_code># Create column with converted seconds to hours:minutes:seconds format (string)
df['time_form'] = df['time'].apply(lambda x: secToHours(x))<jupyter_output><empty_output><jupyter_text>Keep only selected columns<jupyter_code>df = df.filter(items=['day','act_startDate','timestamp','day_no','iter_no','altitude','distance','heartrate','time','time_form','speed','long','lat'])
df.head()<jupyter_output><empty_output><jupyter_text>Extract data to a json to check D3<jupyter_code># Exclude this filter. It is just to test NaN functionality at D3
#df = df[(df['day']=='2017-04-11') | (df['day']=='2017-05-01')]
def create_json(df):
    init_list = []
    for i,day in enumerate(days):
        temp = {}
        temp['day'] = day
        temp['ttl_dist'] = dist_ttl_df[dist_ttl_df['day']==day]['ttl_distance'].iloc[0]
        temp['day_no'] = i+1
        temp['path'] = df[df['day'] == day].ix[:,['lat','long']].values.tolist()
        
        #.loc[:,df.columns.isin(['lat','long'])].values.tolist()
        temp['distance'] = [i for i in df[df['day'] == day]['distance']]
        temp['elevation'] = [i for i in df[df['day'] == day]['altitude']]
        temp['elev_gain'] = alt_ttl_df[alt_ttl_df['day']==day]['elevation_gain'].iloc[0]
        temp['speed'] = [i for i in df[df['day'] == day]['speed']]
        temp['avg_speed'] = ttl_speed_df[ttl_speed_df['day']==day]['avg_speed'].iloc[0]
        temp['max_speed'] = ttl_speed_df[ttl_speed_df['day']==day]['max_speed'].iloc[0]
        temp['heartrate'] = [i for i in df[df['day'] == day]['heartrate']]
        temp['avg_active_HR'] = ttl_speed_df[ttl_speed_df['day']==day]['avg_active_HR'].iloc[0]
        temp['time_form'] = [i for i in df[df['day'] == day]['time_form']]

        init_list.append(temp)
    return init_list
# Transform the list of dicts into a json file
with open('../app/interactive_map/original_active.json', 'w') as outfile:  
    json.dump(create_json(df), outfile)<jupyter_output><empty_output><jupyter_text>Trim data points if they are too many to be handled by D3  If the json from above is too big too be parsed by the browser, trim the df and run the json creation after the trim<jupyter_code>df_trim = df.loc[list(trimmer(df['timestamp'], pd.to_timedelta(30, 's')))]
# Check if the trimming is correct
df_trim.head()
# Transform the list of dicts into a json file
with open('../app/interactive_map/trimmed_active.json', 'w') as outfile:  
    json.dump(create_json(df_trim), outfile)<jupyter_output><empty_output><jupyter_text>Corrections There has been an issue after transforming commulative distances. Day25 (29/07/2017) had a duplicate activity and the graphs mismatch. The duplicates have been manually excluded from the trimmed json file.  Correct data issues with day25<jupyter_code>df_trim[df_trim['day_no']==25]<jupyter_output><empty_output><jupyter_text>Totals df merge  Merge totals and averages in order to create a summing up comparison among the days<jupyter_code># Merge the totals tables
ttl_df = pd.merge(ttl_speed_df,dist_ttl_df,on='day')
ttl_df = pd.merge(ttl_df,alt_ttl_df,on='day')

# Create day_no column
ttl_df['day_no']= 1 + ttl_df.index
# Add the total time spent on bike per day
for i,day in enumerate(days):
    ttl_df['ttl_time'].iloc[i] = df_trim[df_trim['day']==day]['time_form'].iloc[-1]
ttl_df[:5]
# Calculate the totals to add them as hardcoded variables in D3
print "Total Distance Traveled: %.2f km" %sum(ttl_df['ttl_distance'])
print "Total Number of Days Traveled: %s days"% ttl_df['day_no'].iloc[-1]
print "Total Cycling Hours: %s hours" %secToHours(sum(ttl_df['ttl_time'].map(lambda x: hoursToSec(x))))
print "Total Elevation Gain: %.2f m" %sum(ttl_df['elevation_gain'])
print "Average Speed of Whole Trip: %.2f km/h" %round(np.mean(ttl_df['avg_speed']),2)
# Save it to a csv for D3
ttl_df.to_csv('../app/totals_linegraphs/totals_linegraphs.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14656063618290258, 'fraction_numerical': 0.010894632206759442, 'mean_word_length': 4.574468085106383, 'pattern_counts': {'":': 0, '<': 74, '<?xml version=': 0, '>': 79, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '504334', 'license': 'MIT', 'n_tokens_mistral': 4446, 'n_tokens_neox': 4289, 'n_words': 995, 'path': 'data_manging/Parsing STRAVA csv - ANGELOS.ipynb', 'repo_name': 'oikonang/bike_trip_project', 'size': '12575'}