<jupyter_start><jupyter_text>Openpowerlifting datasetThe latest dataset can be downloaded [here](https://www.openpowerlifting.org/data)  Loading data and all necessary packages<jupyter_code>%matplotlib inline
import pandas as pd
import numpy as np
import math
import zipfile
import matplotlib.pyplot as plt
from datetime import datetime as dt
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
from sklearn.tree import DecisionTreeRegressor  
from sklearn.ensemble import RandomForestRegressor
from sklearn.metrics import r2_score, mean_squared_error

zf = zipfile.ZipFile('./openpowerlifting-latest.zip') 
df = pd.read_csv(zf.open('openpowerlifting-2020-01-03/openpowerlifting-2020-01-03.csv'))
df.head()<jupyter_output>C:\ProgramData\Anaconda3\lib\site-packages\IPython\core\interactiveshell.py:3058: DtypeWarning: Columns (36) have mixed types. Specify dtype option on import or set low_memory=False.
  interactivity=interactivity, compiler=compiler, result=result)<jupyter_text>Explore the data**1.** Investigate how many observations and columns are included and look at the names of the variables<jupyter_code>print('Number of columns:', df.shape[1])
print('Number of observations:', df.shape[0])
print()
print('Name of variables:', list(df.columns))<jupyter_output>Number of columns: 38
Number of observations: 1731478

Name of variables: ['Name', 'Sex', 'Event', 'Equipment', 'Age', 'AgeClass', 'BirthYearClass', 'Division', 'BodyweightKg', 'WeightClassKg', 'Squat1Kg', 'Squat2Kg', 'Squat3Kg', 'Squat4Kg', 'Best3SquatKg', 'Bench1Kg', 'Bench2Kg', 'Bench3Kg', 'Bench4Kg', 'Best3BenchKg', 'Deadlift1Kg', 'Deadlift2Kg', 'Deadlift3Kg', 'Deadlift4Kg', 'Best3DeadliftKg', 'TotalKg', 'Place', 'Wilks', 'McCulloch', 'Glossbrenner', 'IPFPoints', 'Tested', 'Country', 'Federation', 'Date', 'MeetCountry', 'MeetState', 'MeetName']<jupyter_text>**2.** Investigate how many missing values are in every column<jupyter_code>print('Number of NaNs per variable:')
print(df.isnull().sum().sort_values(ascending=False))<jupyter_output>Number of NaNs per variable:
Squat4Kg           1726886
Deadlift4Kg        1719967
Bench4Kg           1719653
Squat3Kg           1321801
Squat2Kg           1309647
Squat1Kg           1304523
Deadlift3Kg        1288338
Deadlift2Kg        1267272
Deadlift1Kg        1257522
Bench3Kg           1110486
Bench2Kg           1090647
Bench1Kg           1082410
Country             875505
BirthYearClass      844867
Age                 767714
MeetState           600324
AgeClass            592651
Best3SquatKg        518854
Tested              489626
Best3DeadliftKg     439552
Best3BenchKg        194547
IPFPoints           180355
McCulloch           142159
Wilks               141716
Glossbrenner        141716
TotalKg             127008
BodyweightKg         23239
WeightClassKg        19257
Division              3504
Equipment                0
Event                    0
Sex                      0
MeetName                 0
Place                    0
Federation               0
Date                     0[...]<jupyter_text>**3.** Take a look at the summary of the numeric and categorical variables<jupyter_code># Seperate numeric and categorical variables
num_vars = df.select_dtypes(include=['float64'])
cat_vars = df.select_dtypes(include=['object'])
pd.set_option('display.max_columns', 40)
num_vars.describe()<jupyter_output><empty_output><jupyter_text>There are negative values in the records of the three lifts. On first sight this seems strange, but these might indicate that this attempts were failures.<jupyter_code>cat_vars.describe()<jupyter_output><empty_output><jupyter_text>**4.** Further investigations<jupyter_code>print('Number of meets:', len(df['MeetName'].unique()))
print('Number of lifters', len(df['Name'].unique()))<jupyter_output>Number of meets: 14585
Number of lifters 487947<jupyter_text>Data preparation**1.** Anonymise the data by replacing the names with 'LifterID'<jupyter_code>df.Name = df.Name.astype('category').cat.rename_categories(range(1, df.Name.nunique()+1))
df.rename(columns={'Name': 'LifterID'}, inplace=True)
pd.set_option('display.max_columns', 27)
df.head()<jupyter_output><empty_output><jupyter_text>**2.** Replace negative values (failed lifts) with NaNs, because they are not relevant for this project<jupyter_code>df['Best3SquatKg'][(df['Best3SquatKg']<0)==True] = np.nan
df['Best3BenchKg'][(df['Best3BenchKg']<0)==True] = np.nan
df['Best3DeadliftKg'][(df['Best3DeadliftKg']<0)==True] = np.nan<jupyter_output>C:\ProgramData\Anaconda3\lib\site-packages\ipykernel_launcher.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  """Entry point for launching an IPython kernel.
C:\ProgramData\Anaconda3\lib\site-packages\ipykernel_launcher.py:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  
C:\ProgramData\Anaconda3\lib\site-packages\ipykernel_launcher.py:3: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  This is separate from the ipykernel package so we c[...]<jupyter_text>Question 1: Where is powerlifting (outside USA) most popular?<jupyter_code>country = df[['MeetName', 'MeetCountry']].drop_duplicates()
country = pd.DataFrame(country['MeetCountry'].value_counts())
country = country[country.index!='USA']
country.head(5)<jupyter_output><empty_output><jupyter_text>**1.** Compute the percentage of the top 5 countries<jupyter_code>sum = country['MeetCountry'].sum()
norway = country[country.index=='Norway']['MeetCountry']/sum
russia = country[country.index=='Russia']['MeetCountry']/sum
canada = country[country.index=='Canada']['MeetCountry']/sum
sweden = country[country.index=='Sweden']['MeetCountry']/sum
australia = country[country.index=='Australia']['MeetCountry']/sum
rest = 1-(norway[0]+russia[0]+canada[0]+sweden[0]+australia[0])<jupyter_output><empty_output><jupyter_text>**2.** Plot a pie chart with the percentages<jupyter_code>labels = 'Australia', 'Canada', 'Russia', 'Sweden', 'Norway', 'Rest'
sizes = [australia[0], canada[0], russia[0], sweden[0], norway[0], rest]

fig1, ax1 = plt.subplots()
ax1.pie(sizes, labels=labels, autopct='%1.1f%%',
        shadow=False, startangle=90)
ax1.axis('equal')
plt.savefig('./plots/country.png')
plt.show()<jupyter_output><empty_output><jupyter_text>**3.** Compute the percentage of the scandinavian countries (Sweden, Norway, Denmark, Finland, Iceland) combined<jupyter_code>scandinavia = country[country.index.isin(['Sweden', 'Norway', 'Denmark', 'Finland', 'Iceland'])]
sum_scan = scandinavia['MeetCountry'].sum()
print(round(sum_scan/sum * 100, 2))
# Question: In which countries are the best lifters?
print(round(df[['MeetCountry', 'Best3SquatKg']].groupby('MeetCountry').mean().sort_values(by='Best3SquatKg', ascending=False).head(3), 2))
print()
print(round(df[['MeetCountry', 'Best3BenchKg']].groupby('MeetCountry').mean().sort_values(by='Best3BenchKg', ascending=False).head(3), 2))
print()
print(round(df[['MeetCountry', 'Best3DeadliftKg']].groupby('MeetCountry').mean().sort_values(by='Best3DeadliftKg', ascending=False).head(3), 2))<jupyter_output>Best3SquatKg
MeetCountry              
UAE                276.40
Puerto Rico        241.54
Azerbaijan         237.30

             Best3BenchKg
MeetCountry              
Azerbaijan         177.65
Tahiti             156.36
Egypt              155.30

             Best3DeadliftKg
MeetCountry                 
UAE                   249.64
Azerbaijan            236.95
Egypt                 236.52<jupyter_text>Question 2: How much do the lifts interfere each other?**1.** Compute the mean of every lift for every event and then compute the difference to the reference (one-lift-only)<jupyter_code># How big is the difference when they do more than one lift?
def get_diff(one_lift, two_lifts1, two_lifts2, three_lifts):
    '''
    INPUT:
    one_lift - best attempt of a lift on an event where only this lift was performed
    two_lifts1 - best attempt of a lift on an event where two lifts were performed
    two_lifts2 - best attempt of a lift on an event where two lifts were performed
    three_lifts - best attempt of a lift on an event where all three lifts were performed
    
    OUTPUT:
    first output - average performance on the lift on events where only this lift was perfromed
    second output - difference of the average performance on the lift on events where two lifts were perfromed
    third output - difference of the average performance on the lift on events where two lifts were perfromed
    fourth output - difference of the average performance on the lift on events where all three lifts were perfromed
    
    '''
    print(round(one_lift.mean(),2))
    print(round(two_lifts1.mean()-one_lift.mean(),2))
    print(round(two_lifts2.mean()-one_lift.mean(),2))
    print(round(three_lifts.mean()-one_lift.mean(),2))
    

get_diff(df['Best3SquatKg'][df['Event']=='S'], df['Best3SquatKg'][df['Event']=='SB'], df['Best3SquatKg'][df['Event']=='SD'],
         df['Best3SquatKg'][df['Event']=='SBD'])<jupyter_output>180.09
-15.92
-34.38
-4.28<jupyter_text>On average the best squat attempt at a squat-only event is 180.09kg. If the benchpress or the deadlift is also performed the average of the squat decreased by 15.92kg resp. 34.38kg. On a classical powerlifting meet (SBD) the average decreased by 4.28kg.<jupyter_code>get_diff(df['Best3BenchKg'][df['Event']=='B'], df['Best3BenchKg'][df['Event']=='SB'], df['Best3BenchKg'][df['Event']=='BD'],
         df['Best3BenchKg'][df['Event']=='SBD'])<jupyter_output>147.68
-44.22
-28.72
-36.75<jupyter_text>On average the best benchpress attempt at a benchpress-only event is 147.68kg. If the squat or the deadlift is also performed the average of the benchpress decreased by 44.22kg resp. 28.72kg. On a classical powerlifting meet (SBD) the average decreased by 36.75kg.<jupyter_code>get_diff(df['Best3DeadliftKg'][df['Event']=='D'], df['Best3DeadliftKg'][df['Event']=='SD'],
         df['Best3DeadliftKg'][df['Event']=='BD'], df['Best3DeadliftKg'][df['Event']=='SBD'])<jupyter_output>203.78
-45.08
-12.95
-14.91<jupyter_text>On average the best deadlift attempt at a deadlift-only event is 203.78kg. If the squat or the benchpress is also performed the average of the deadlift decreased by 45.08kg resp. 12.95kg. On a classical powerlifting meet (SBD) the average decreased by 14.91kg. **2.** Isolate the mean of the lifts from the rest to get an overview of the absolute numbers<jupyter_code>lifts = df[['Event', 'Best3SquatKg', 'Best3BenchKg', 'Best3DeadliftKg']]
lifts = lifts.groupby('Event').mean()
lifts.head(10)<jupyter_output><empty_output><jupyter_text>**3.** Drop the NaNs and plot the means in bar plots<jupyter_code>squat = pd.DataFrame(lifts.dropna(subset=['Best3SquatKg'], axis=0)['Best3SquatKg'])
squat.sort_values(by=['Best3SquatKg'], inplace=True, ascending=False)

bench = pd.DataFrame(lifts.dropna(subset=['Best3BenchKg'], axis=0)['Best3BenchKg'])
bench.sort_values(by=['Best3BenchKg'], inplace=True, ascending=False)

deadlift = pd.DataFrame(lifts.dropna(subset=['Best3DeadliftKg'], axis=0)['Best3DeadliftKg'])
deadlift.sort_values(by=['Best3DeadliftKg'], inplace=True, ascending=False)<jupyter_output><empty_output><jupyter_text>**3.1** Best squat<jupyter_code>height = squat['Best3SquatKg']
bars = squat.index
y_pos = np.arange(len(bars))

plt.bar(y_pos, height)
plt.xticks(y_pos, bars)
plt.ylim([0, 210])
plt.title('Best Squat (mean)')
plt.xlabel('Event')
plt.rcParams["figure.figsize"] = (3,5)
plt.savefig('./plots/squat.png')
plt.show()<jupyter_output><empty_output><jupyter_text>**3.2** Best benchpress<jupyter_code>height = bench['Best3BenchKg']
bars = bench.index
y_pos = np.arange(len(bars))

plt.bar(y_pos, height)
plt.xticks(y_pos, bars)
plt.ylim([0, 210])
plt.title('Best Benchpress (mean)')
plt.xlabel('Event')
plt.rcParams["figure.figsize"] = (3,5)
plt.savefig('./plots/bench.png')
plt.show()<jupyter_output><empty_output><jupyter_text>**3.3** Best deadlift<jupyter_code>height = deadlift['Best3DeadliftKg']
bars = deadlift.index
y_pos = np.arange(len(bars))

plt.bar(y_pos, height)
plt.xticks(y_pos, bars)
plt.ylim([0, 210])
plt.title('Best Deadlift (mean)')
plt.xlabel('Event')
plt.rcParams["figure.figsize"] = (3,5)
plt.savefig('./plots/deadlift.png')
plt.show()<jupyter_output><empty_output><jupyter_text>Question 3: How much can you deadlift?To answer this question the last lift (deadlift) on a SBD-Event (classical powerlifting event) will be predicted **1.** Use only the best squat and benchpress for the prediction**1.1** Prepare data for ML-models<jupyter_code># Separate the columns of interest from the rest
# Drop missing values because ML-models cannot use them
pred_df1 = df[['Best3SquatKg', 'Best3BenchKg', 'Best3DeadliftKg']]
pred_df1 = pred_df1.dropna()

# Divide pred_df into features (X) and labels (y)
y1 = pred_df1['Best3DeadliftKg']
X1 = pred_df1[['Best3SquatKg', 'Best3BenchKg']]

# Divide features and labels into training and testing data
X1_train, X1_test, y1_train, y1_test = train_test_split(X1, y1, test_size=0.3, random_state=42)
print(X1_train.shape)
print(X1_test.shape)<jupyter_output>(811367, 2)
(347730, 2)<jupyter_text>**1.2** Train models<jupyter_code># Linear model
lm1 = LinearRegression()
lm1.fit(X1_train, y1_train)

# Decision tree
seed = 42
tree1 = DecisionTreeRegressor(random_state=seed)
tree1.fit(X1_train, y1_train)

# Random forest
seed = 42
trees = 100
rf1 = RandomForestRegressor(n_estimators=trees, random_state=seed)
rf1.fit(X1_train, y1_train)<jupyter_output><empty_output><jupyter_text>**1.3** Predict test data with fitted models<jupyter_code># Linear model
yhat_lm1 = lm1.predict(X1_test)
r2_lm1 = r2_score(y1_test, yhat_lm1)

# Decision tree
yhat_tree1 = tree1.predict(X1_test)
r2_tree1 = r2_score(y1_test, yhat_tree1)

# Random forest
yhat_rf1 = rf1.predict(X1_test)
r2_rf1 = r2_score(y1_test, yhat_rf1)<jupyter_output><empty_output><jupyter_text>**2.** Include some categorical variables (sex, equipment)**2.1** Prepare data for ML-models<jupyter_code># Separate the columns of interest from the rest
# Drop missing values because ML-models cannot use them
pred_df2 = df[['Best3SquatKg', 'Best3BenchKg', 'Best3DeadliftKg', 'Sex', 'Equipment']]
pred_df2 = pred_df2.dropna()

# Divide pred_df into features (X) and labels (y)
y2 = pred_df2['Best3DeadliftKg']
X2 = pred_df2[['Best3SquatKg', 'Best3BenchKg', 'Sex', 'Equipment']]

# Create dummies for categorical features, because they have more than two categories
sex_dummy = pd.get_dummies(X2['Sex']) 
equip_dummy = pd.get_dummies(X2['Equipment'])
X2 = X2.merge(sex_dummy, left_index=True, right_index=True)
X2 = X2.merge(equip_dummy, left_index=True, right_index=True)
X2 = X2.loc[:, X2.columns != 'Sex']
X2 = X2.loc[:, X2.columns != 'Equipment']

# Divide features and labels into training and testing data
X2_train, X2_test, y2_train, y2_test = train_test_split(X2, y2, test_size=0.3, random_state=42)
print(X2_train.shape)
print(X2_test.shape)
X2.head()<jupyter_output>(811367, 9)
(347730, 9)<jupyter_text>**2.2** Train models<jupyter_code># Linear model
lm2 = LinearRegression()
lm2.fit(X2_train, y2_train)

# Decision tree
seed = 42
tree2 = DecisionTreeRegressor(random_state=seed)
tree2.fit(X2_train, y2_train)

# Random forest
seed = 42
trees = 100
rf2 = RandomForestRegressor(n_estimators=trees, random_state=seed)
rf2.fit(X2_train, y2_train)<jupyter_output><empty_output><jupyter_text>**2.3** Predict test data with fitted models<jupyter_code># Linear model
yhat_lm2 = lm2.predict(X2_test)
r2_lm2 = r2_score(y2_test, yhat_lm2)

# Decision tree
yhat_tree2 = tree2.predict(X2_test)
r2_tree2 = r2_score(y2_test, yhat_tree2)

# Random forest
yhat_rf2 = rf2.predict(X2_test)
r2_rf2 = r2_score(y2_test, yhat_rf2)<jupyter_output><empty_output><jupyter_text>**3.1** Compare rsquared of all models in a plot<jupyter_code>xs=['Linear Model', 'Decision Tree', 'Random Forest']
y1s=[r2_lm1, r2_tree1, r2_rf1]
y2s=[r2_lm2, r2_tree2, r2_rf2]

plt.scatter(xs,y1s)
plt.plot(xs, y1s, label='without categorical')
plt.scatter(xs,y2s)
plt.plot(xs, y2s, label='with categorical')

# Add values to points
for x,y in zip(xs,y1s):
    label = "{:.4f}".format(y)
    plt.annotate(label, (x,y), textcoords="offset points", xytext=(0,10), ha='center') 

for x,y in zip(xs,y2s):
    label = "{:.4f}".format(y)
    plt.annotate(label, (x,y), textcoords="offset points", xytext=(0,10), ha='center') 
    
plt.ylabel('R-Squared')
plt.legend()
plt.rcParams["figure.figsize"] = (6,7)
plt.savefig('./plots/rsquared.png')
plt.show()<jupyter_output><empty_output><jupyter_text>**3.2** Compare rmse of all models in a plot<jupyter_code># Linear model
rmse_lm1 = math.sqrt(mean_squared_error(y1_test, yhat_lm1))
rmse_lm2 = math.sqrt(mean_squared_error(y2_test, yhat_lm2))

# Decision tree
rmse_tree1 = math.sqrt(mean_squared_error(y1_test, yhat_tree1))
rmse_tree2 = math.sqrt(mean_squared_error(y2_test, yhat_tree2))

# Random forest
rmse_rf1 = math.sqrt(mean_squared_error(y1_test, yhat_rf1))
rmse_rf2 = math.sqrt(mean_squared_error(y2_test, yhat_rf2))

xs=['Linear Model', 'Decision Tree', 'Random Forest']
y1s=[rmse_lm1, rmse_tree1, rmse_rf1]
y2s=[rmse_lm2, rmse_tree2, rmse_rf2]

plt.scatter(xs,y1s)
plt.plot(xs, y1s, label='without categorical')
plt.scatter(xs,y2s)
plt.plot(xs, y2s, label='with categorical')

# Add values to points
for x,y in zip(xs,y1s):
    label = "{:.4f}".format(y)
    plt.annotate(label, (x,y), textcoords="offset points", xytext=(0,10), ha='center')

for x,y in zip(xs,y2s):
    label = "{:.4f}".format(y)
    plt.annotate(label, (x,y), textcoords="offset points", xytext=(0,10), ha='center') 
    
plt.ylabel('MSE')
plt.legend()
plt.rcParams["figure.figsize"] = (6,7)
plt.savefig('./plots/rmse.png')
plt.show()<jupyter_output><empty_output><jupyter_text>How much can I deadlift?<jupyter_code>myself = {'Best3SquatKg': [150.0], 'Best3BenchKg': [130.0], 'F': [0], 'M': [1], 'Mx': [0],
          'Multi-ply': [0], 'Raw': [1], 'Single-ply': [0], 'Wraps': [0]}
myself = pd.DataFrame(data=myself)
yhat = rf2.predict(myself)
print(yhat)<jupyter_output>[196.5128834]
{'fraction_non_alphanumeric': 0.13759491625827994, 'fraction_numerical': 0.04572136356292746, 'mean_word_length': 5.511220196353436, 'pattern_counts': {'":': 0, '<': 108, '<?xml version=': 0, '>': 105, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, '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': '29', 'dir': 'jupyter-structured-clean-dedup', 'id': '119178', 'license': 'CNRI-Python', 'n_tokens_mistral': 7404, 'n_tokens_neox': 6754, 'n_words': 1496, 'path': 'openpowerlifting.ipynb', 'repo_name': 'kevinossner/openpowerlifting', 'size': '18569'}