<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Recomend Better Postion Who Have Multy Position Player
- Using Decision Tree, Gaussian and Ensemble
Index
Connect DB and Make QUERY
Make Pandas DataFrame Each Position Player
Set Position Category and Concat Each Datafream
Make Training and Test Data
Make Decision Tree Classifier Model
Check Confusion Matrix
Cecck Classification Report
Recomend Position
Import Package
Step7:  1. Connect DB and Make QUERY
Step8:  2. Make Pandas DataFrame Each Position Player
Step9:  3. Set Position Category and Concat Each Datafream
Step10:  4. Make Training and Test Data
Step11:  5. Make Decision Tree Classifier Model
Step12:  6. Check Confusion Matrix
Step13:  7. Cecck Classification Report
Step15:  8. Recomend Position
<ASSISTANT_TASK:>
Python Code:
%matplotlib inline
%config InlineBackend.figure_formats = {'png', 'retina'}
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
import matplotlib as mpl
import pandas as pd
import MySQLdb
from sklearn.tree import export_graphviz
from sklearn.cross_validation import train_test_split
from sklearn.metrics import confusion_matrix
from sklearn.metrics import classification_report
Explanation: Recomend Better Postion Who Have Multy Position Player
- Using Decision Tree, Gaussian and Ensemble
Index
Connect DB and Make QUERY
Make Pandas DataFrame Each Position Player
Set Position Category and Concat Each Datafream
Make Training and Test Data
Make Decision Tree Classifier Model
Check Confusion Matrix
Cecck Classification Report
Recomend Position
Import Package
End of explanation
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 *
        FROM player
    
    
    if position == "F":
        SQL_QUERY += 
            WHERE position not like "%,%" and position like "%FW%" and mins > 270
        
    
    if position == "M":
        SQL_QUERY += 
            WHERE position not like "%,%" and position like "%M%" and mins > 270
        
    
    if position == "D":
        SQL_QUERY += 
            WHERE position not like "%,%" and position like "%D%" and position not like " DMC"  and mins > 270
        
    
    if position == "G":
        SQL_QUERY += 
            WHERE position not like "%,%" and position like "%G%" and mins > 270
        
    
    return SQL_QUERY
Explanation: 1. Connect DB and Make QUERY
End of explanation
# forword
SQL_QUERY = make_query("F")
forword_df = pd.read_sql(SQL_QUERY, db)
# midfilder
SQL_QUERY = make_query("M")
midfilder_df = pd.read_sql(SQL_QUERY, db)
# defencer
SQL_QUERY = make_query("D")
defencer_df = pd.read_sql(SQL_QUERY, db)
# goalkeeper
SQL_QUERY = make_query("G")
goalkeeper_df = pd.read_sql(SQL_QUERY, db)
len(forword_df), len(midfilder_df), len(defencer_df), len(goalkeeper_df)
Explanation: 2. Make Pandas DataFrame Each Position Player
End of explanation
forword_df["position"] = 0
forword_df
midfilder_df["position"] = 1
midfilder_df
defencer_df["position"] = 2
defencer_df
goalkeeper_df["position"] = 3
goalkeeper_df
concated_df = pd.concat([forword_df, midfilder_df, defencer_df, goalkeeper_df])
concated_df.tail()
Explanation: 3. Set Position Category and Concat Each Datafream
End of explanation
X_train, X_test, y_train, y_test = train_test_split(concated_df.ix[:,:-1], concated_df.ix[:,-1], test_size=0.2, random_state=1)
Explanation: 4. Make Training and Test Data
End of explanation
from sklearn.tree import DecisionTreeClassifier
model_entropy = DecisionTreeClassifier(criterion='entropy', max_depth=3).fit(X_train, y_train)
model_gini = DecisionTreeClassifier(criterion='gini', max_depth=3).fit(X_train, y_train)
from sklearn.naive_bayes import GaussianNB
model_gaussian = GaussianNB().fit(X_train, y_train)
from sklearn.ensemble import VotingClassifier
clf1 = DecisionTreeClassifier(criterion='entropy', max_depth=3)
clf2 = DecisionTreeClassifier(criterion='gini', max_depth=3)
clf3 = GaussianNB()
eclf = VotingClassifier(estimators=[('entropy', clf1), ('gini', clf2), ('naive', clf3)], voting='soft', weights=[2, 1, 1])
model_ensemble = eclf.fit(X_train, y_train)
Explanation: 5. Make Decision Tree Classifier Model
End of explanation
cm_entropy = confusion_matrix(y_test, model_entropy.predict(X_test))
cm_gini = confusion_matrix(y_test, model_gini.predict(X_test))
cm_gaussian = confusion_matrix(y_test, model_gaussian.predict(X_test))
cm_ensemble = confusion_matrix(y_test, model_ensemble.predict(X_test))
print("entropy"+"="*12)
print(cm_entropy)
print("gini"+"="*15)
print(cm_gini)
print("gaussian"+"="*11)
print(cm_gaussian)
print("ensemble"+"="*11)
print(cm_ensemble)
Explanation: 6. Check Confusion Matrix
End of explanation
print("entropy"+"="*50)
print(classification_report(y_test, model_entropy.predict(X_test)))
print("gini"+"="*50)
print(classification_report(y_test, model_gini.predict(X_test)))
print("gaussian"+"="*50)
print(classification_report(y_test, model_gaussian.predict(X_test)))
print("ensemble"+"="*50)
print(classification_report(y_test, model_ensemble.predict(X_test)))
Explanation: 7. Cecck Classification Report
End of explanation
SQL_QUERY = 
    SELECT 
        tall, weight, apps_sub, mins, goals, assists
        , spg, ps_x, motm, aw, tackles, inter, fouls, clear, drb
        , owng, keyp_x, fouled, off, disp, unstch, avgp, position
    FROM player
    WHERE position like "%,%" and mins > 270
    ;
many_position_player_df = pd.read_sql(SQL_QUERY, db)
len(many_position_player_df)
predict_data = model_ensemble.predict(many_position_player_df.ix[:,:-1])
many_position_player_df["recomend_position"] = predict_data
# Recomend Result
# 0 : Forword, 1 : Midfilder, 2 : Defencer, 3 : Goalkeeper
many_position_player_df.ix[:10,-2:]
Explanation: 8. Recomend Position
End of explanation
<END_TASK>