In [1]:
%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
In [11]:
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 "%M%" and mins > 270
;
""".format(position=position)
return SQL_QUERY
# midfilder
SQL_QUERY = make_query("M")
midfilder_df = pd.read_sql(SQL_QUERY, db)
len(midfilder_df)
Out[11]:
In [12]:
X = midfilder_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(midfilder_df.ix[:,-1], columns=["rating"])
m_df = pd.concat([dfX, dfy], axis=1)
m_df.head()
Out[12]:
In [13]:
model = sm.OLS(m_df.ix[:, -1], m_df.ix[:, :-1])
result = model.fit()
print(result.summary())
In [15]:
# remove features
remove_column_list = [
"tall", "weight", "apps_start", "apps_sub", "mins", "offsides", "blocks", "owng", "off", "disp", "unstch"
]
removed_m_df = m_df.drop(remove_column_list, axis=1)
model = sm.OLS(removed_m_df.ix[:, -1], removed_m_df.ix[:, :-1])
result = model.fit()
print(result.summary())
In [16]:
formula_str = """
rating ~ age + goals + assists + yel + red
+ spg + ps_x + motm + aw + tackles + inter + fouls + clear + drb
+ keyp_x + fouled + avgp + ps_y
"""
model = sm.OLS.from_formula(formula_str, data=removed_m_df)
result = model.fit()
table_anova = sm.stats.anova_lm(result)
table_anova
Out[16]:
In [17]:
# remove feature 2
remove_column_list = [
"age", "red", "drb", "ps_y"
]
removed2_m_df = removed_m_df.drop(remove_column_list, axis=1)
model = sm.OLS(removed2_m_df.ix[:, -1], removed2_m_df.ix[:, :-1])
result = model.fit()
print(result.summary())
In [ ]:
# midfilder
# tackles
# keyp_x
# goals
# inter
# spg