<jupyter_start><jupyter_text>Defender OLS analysis<jupyter_code>%matplotlib inline
%config InlineBackend.figure_formats = {'png', 'retina'}

import pandas as pd
import numpy as np
import statsmodels.api as sm
import seaborn as sns
import MySQLdb

from sklearn.preprocessing import StandardScaler
from sklearn.linear_model import LinearRegression
from sklearn.cross_validation import cross_val_score<jupyter_output><empty_output><jupyter_text>Connect DB & Get Defender Player<jupyter_code>db = MySQLdb.connect(
    "db.fastcamp.us",
    "root",
    "dkstncks",
    "football",
    charset='utf8',
)

def make_query(position):
    """
    
    parameter------------
    position : M, D, F, G
    
    return---------------
    SQL_QUERY String
    
    """
    SQL_QUERY = """
        SELECT 
            age, tall, weight, apps_start, apps_sub, mins, goals, assists, yel, red
            , spg, ps_x, motm, aw, tackles, inter, fouls, offsides, clear, drb, blocks
            , owng, keyp_x, fouled, off, disp, unstch, avgp, ps_y, rating
        FROM player
        WHERE position like "%{position}%" and mins > 270
        ;
    """.format(position=position)
    
    return SQL_QUERY

# forword
SQL_QUERY = make_query("D")
defender_df = pd.read_sql(SQL_QUERY, db)

len(defender_df)<jupyter_output><empty_output><jupyter_text>Scaling<jupyter_code>X = defender_df.ix[:,:-1]
scaler = StandardScaler(with_mean=False)
X_scaled = scaler.fit_transform(X)

dfX0 = pd.DataFrame(X_scaled, columns=X.columns)
dfX = sm.add_constant(dfX0)
dfy = pd.DataFrame(defender_df.ix[:,-1], columns=["rating"])
d_df = pd.concat([dfX, dfy], axis=1)
d_df.head()<jupyter_output><empty_output><jupyter_text>Summary OLS<jupyter_code>model = sm.OLS(d_df.ix[:, -1], d_df.ix[:, :-1])
result = model.fit()
print(result.summary())<jupyter_output>OLS Regression Results                            
==============================================================================
Dep. Variable:                 rating   R-squared:                       0.785
Model:                            OLS   Adj. R-squared:                  0.780
Method:                 Least Squares   F-statistic:                     163.7
Date:                Mon, 27 Jun 2016   Prob (F-statistic):               0.00
Time:                        11:45:25   Log-Likelihood:                 844.16
No. Observations:                1285   AIC:                            -1630.
Df Residuals:                    1256   BIC:                            -1481.
Df Model:                          28                                         
Covariance Type:            nonrobust                                         
==============================================================================
                 coef    std err          t      P>|[...]<jupyter_text>Find Proper Model<jupyter_code># delete features based on t-value
remove_column_list = [
    "age", "tall", "weight", "apps_start", "apps_sub", "mins", "yel", "off"
]
removed_d_df = d_df.drop(remove_column_list, axis=1) 

model = sm.OLS(removed_d_df.ix[:, -1], removed_d_df.ix[:, :-1])
result = model.fit()
print(result.summary())
# check F-value using anova table
formula_str = """
rating ~ goals + assists + red + spg + ps_x + motm + aw
+ tackles + inter + fouls + offsides + clear + drb + blocks
+ owng + keyp_x + fouled + disp + unstch + avgp + ps_y
"""

model = sm.OLS.from_formula(formula_str, data=removed_d_df)
result = model.fit()
table_anova = sm.stats.anova_lm(result)
table_anova
# delete features based on F-value
remove_column_list = [
    "red", "offsides", "drb", "blocks", "disp", "ps_y"
]
removed2_d_df = removed_d_df.drop(remove_column_list, axis=1) 

model = sm.OLS(removed2_d_df.ix[:, -1], removed2_d_df.ix[:, :-1])
result = model.fit()
print(result.summary())<jupyter_output>OLS Regression Results                            
==============================================================================
Dep. Variable:                 rating   R-squared:                       0.768
Model:                            OLS   Adj. R-squared:                  0.765
Method:                 Least Squares   F-statistic:                     280.3
Date:                Mon, 27 Jun 2016   Prob (F-statistic):               0.00
Time:                        11:47:07   Log-Likelihood:                 795.93
No. Observations:                1285   AIC:                            -1560.
Df Residuals:                    1269   BIC:                            -1477.
Df Model:                          15                                         
Covariance Type:            nonrobust                                         
==============================================================================
                 coef    std err          t      P>|[...]
{'fraction_non_alphanumeric': 0.1722838601632824, 'fraction_numerical': 0.02512036843207034, 'mean_word_length': 1.9658597144630665, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 22, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '515633', 'license': 'MIT', 'n_tokens_mistral': 1557, 'n_tokens_neox': 1436, 'n_words': 322, 'path': '4_Defender OLS analysis.ipynb', 'repo_name': 'junhwanjang/whoscored', 'size': '4777'}