import pg8000

con = pg8000.connect(host='training.c1erymiua9dx.us-east-1.rds.amazonaws.com',database='training',port=5432,user='dot_student',password='qgis')

cursor = con.cursor()

cursor.execute('SELECT * FROM winequality')

data = []
for item in cursor.fetchall():
    data.append(item)

data

from numpy import array

myarray = array(data)

import pandas as pd

df = pd.read_sql('SELECT * FROM winequality',con)

wine = df.as_matrix()

wine

x = wine[:,:-1]
y = wine[:,-1]

x

y

"""
Explanation: Create a classifier to predict the wine color from wine quality attributes using this dataset: http://archive.ics.uci.edu/ml/datasets/Wine+Quality
The data is in the database we've been using

host='training.c1erymiua9dx.us-east-1.rds.amazonaws.com'
database='training'
port=5432
user='dot_student'
password='qgis'
table name = 'winequality'

Query for the data and create a numpy array
End of explanation
"""


x = myarray[:,:11]
y = myarray[:,11:]
y2 = myarray[:,11]

y

y2

"""
Explanation: Split the data into features (x) and target (y, the last column in the table)
Remember you can cast the results into an numpy array and then slice out what you want
End of explanation
"""


from sklearn.tree import DecisionTreeClassifier

dt = DecisionTreeClassifier()

dt = dt.fit(x,y)

"""
Explanation: Create a decision tree with the data
End of explanation
"""


from sklearn.cross_validation import cross_val_score

scores = cross_val_score(dt,x,y2,cv=10)

scores

"""
Explanation: Run 10-fold cross validation on the model
End of explanation
"""


import matplotlib.pyplot as plt
%matplotlib inline

plt.plot(dt.feature_importances_,'o')
plt.ylim(0,1)

import numpy as np

df

np.asarray(df['residual_sugar'])

df.columns = 'fixed_acidity', 'volatile_acidity', 'citric_acid', 'residual_sugar', 'chlorides', 'free_sulfur_dioxide', 'total_sulfur_dioxide', 'density', 'ph', 'sulphates', 'alcohol', 'color'

df.as_matrix()

"""
Explanation: If you have time, calculate the feature importance and graph based on the code in the slides from last class
Use this tip for getting the column names from your cursor object
End of explanation
"""


