<jupyter_start><jupyter_text>Iris Dataset A Vertica ML Python tutorial to understand how to use the library.The Iris flower data set or Fisher's Iris data set is a multivariate data set introduced by the British statistician and biologist <NAME> in his 1936 paper The use of multiple measurements in taxonomic problems as an example of linear discriminant analysis. The data set consists of 50 samples from each of three species of Iris (Iris setosa, Iris virginica and Iris versicolor). Four features were measured from each sample: the length and the width of the sepals and petals, in centimetres. Based on the combination of these four features, Fisher developed a linear discriminant model to distinguish the species from each other. Connection to the Vertica Database using a DSN.    In order to use the Vertica ML Python Library and begin the searches, we need first to connect to the Vertica Database. We can use an ODBC connection using a DSN.<jupyter_code>import vertica_python
# fill in your database info here
conn_info = {'host': '192.168.1.244', 
             'port' : 5433, 
             'user': 'dbadmin', 
             'password':'<PASSWORD>', 
             'database': 'docker', 
             'read_timeout': 3600, 'unicode_error': 'strict', 'ssl': False, 'connection_timeout': 60}
# quick connection test
cur=vertica_python.connect(**conn_info).cursor()
cur.execute("SELECT VERSION();")
for row in cur.iterate():
    print(row)<jupyter_output>['Vertica Analytic Database v9.1.0-1']<jupyter_text>InitializationWe can now import the VPython library and especially the RVD (Resilient Vertica Dataset) object.<jupyter_code>from vertica_ml_python import RVD
from vertica_ml_python import read_csv # This function will help us to load the csv file in the Database.
from vertica_ml_python import drop_table # This function will help us to drop the unnecessary tables.<jupyter_output><empty_output><jupyter_text>Let's now drop the table iris from our Database (if it exists) in order to use the read_csv parser.<jupyter_code># this does not work on Docker
# drop_table("Iris",cur)<jupyter_output>The table Iris was successfully dropped.<jupyter_text>Let's now try to parse the csv files.<jupyter_code># this also does not work on Docker.  Load the dataset "Iris.csv" into Vertica table public.Iris some other way
# iris=read_csv('../datasets/Iris.csv',cur)<jupyter_output>The parser guess the following columns and types:
Id: Integer
PetalLengthCm: Numeric(5,2)
PetalWidthCm: Numeric(5,2)
SepalLengthCm: Numeric(5,2)
SepalWidthCm: Numeric(5,2)
Species: Varchar(30)
Illegal characters in the columns names will be erased.
Is any type wrong?
If one of the types is not correct, it will be considered as Varchar(100).
0 - There is one type that I want to modify.
1 - I wish to continue.
2 - I wish to see the columns and their types again.
1
The table Iris has been successfully created.<jupyter_text>If the table is already in the Database. We can create the RVD as follows:<jupyter_code>iris=RVD('Iris',cur)<jupyter_output><empty_output><jupyter_text>We can also create the RVD using directly the DSN:<jupyter_code># this does not work on Docker
# iris=RVD('Iris',dsn="VerticaDSN")<jupyter_output><empty_output><jupyter_text>Data Exploration and CleaningGreat, we have now our RVD object. All its methods will send SQL queries directly to Vertica in order to compute all the aggregations needed. In that case, nothing is loaded inside our machine except the final results computed by Vertica. All the users transformations are kept in mind and then used to send the final query. During this tutorial, we will try to understand the object thanks to this small dataset. Let first see what it looks like.<jupyter_code>iris.head(5)<jupyter_output><empty_output><jupyter_text>The column Id is useless, we can drop it.<jupyter_code>iris["id"].drop_column()<jupyter_output>RVC 'id' deleted from the RVD.<jupyter_text>In this dataset, we have four relevant columns that will be used for predictions.<jupyter_code>iris.describe()<jupyter_output><empty_output><jupyter_text>In this dataset, there is no outliers and no missing values as the biologist <NAME> measured himself all the flowers.<jupyter_code>iris.missing()<jupyter_output><empty_output><jupyter_text>Let's explore a little deeper the dataset.<jupyter_code>iris.scatter(columns=["petallengthcm","sepallengthcm","species"])
iris.scatter(columns=["petalwidthcm","sepalwidthcm","species"])
iris.scatter(columns=["petalwidthcm","petallengthcm","species"])
iris.scatter(columns=["sepalwidthcm","petallengthcm","species"])<jupyter_output><empty_output><jupyter_text>Wow, there is really a border between each species! Let's see some 3D plots.<jupyter_code>iris.scatter(columns=["petallengthcm","sepallengthcm","sepalwidthcm","species"])
iris.scatter(columns=["petallengthcm","sepallengthcm","petalwidthcm","species"])
iris.scatter(columns=["petallengthcm","sepalwidthcm","petalwidthcm","species"])<jupyter_output><empty_output><jupyter_text>Iris setosa flowers are very easy to predict. However, some of the two other species can be hard to predict. In order to do so, many algorithms are possibles:    Logistic Regression or SVM using the one hot encoding of our 3 species    Naive Bayes or Random Forest     Kmeans with 3 clustersFirst we will use a Kmeans Clustering to try to predict the 3 species. After we will use the SVM in order to see how we can predict variables of multiple classes using a binary classifier.  Kmeans ClusteringFirst let's import the Kmeans algorithm.<jupyter_code>from vertica_ml_python import kmeans
from vertica_ml_python import drop_model

drop_model("iris_kmeans",cur)<jupyter_output>/!\ Warning: The model iris_kmeans doesn't exist !<jupyter_text>We can see the optimal number of clusters using the elbow curve.<jupyter_code>from vertica_ml_python import elbow
elbow(input_relation="Iris",input_columns=["PetalLengthCm","SepalLengthCm","PetalWidthCm","SepalWidthCm"],cursor=cur,
         max_num_cluster=8)<jupyter_output><empty_output><jupyter_text>As expected, 3 is the optimal number of clusters as the all_within_cluster_SS decreases slowly after this point. We can then create the model.<jupyter_code>iris_kmeans=kmeans(model_name="iris_kmeans",input_relation="Iris",
                   input_columns=["PetalLengthCm","SepalLengthCm","PetalWidthCm","SepalWidthCm"],
                   num_clusters=3,cursor=cur,initial_centers=[(1,2,3,4),(2,3,4,5),(3,4,5,6)])<jupyter_output><empty_output><jupyter_text>Let's see what the model looks like.<jupyter_code>print(iris_kmeans)
iris_kmeans.cluster_SS()<jupyter_output><empty_output><jupyter_text>We can also see the importance of each feature.<jupyter_code>iris_kmeans.features_importance()<jupyter_output><empty_output><jupyter_text>Let's add the clusters to our RVD.<jupyter_code>name=iris_kmeans.add_to_rvd(iris)
print(iris)<jupyter_output>The new RVC 'kmeans_cluster2489' was added to the RVD.<jupyter_text>As expected the Iris Setosa is perfectly predicted. Let's explore our new column.<jupyter_code>iris[name].hist(method="count")
iris["species"].hist(method="count")<jupyter_output><empty_output><jupyter_text>Let's compare the most interesting plots.<jupyter_code>iris.scatter(columns=["petalwidthcm","sepalwidthcm","species"])
iris.scatter(columns=["petalwidthcm","sepalwidthcm",name])<jupyter_output><empty_output><jupyter_text>Okay! So cluster 0 is the Iris Setosa, 1 is the Iris Versicolor and 2 is the Iris Virginica. Iris Setosa is perfectly predicted while there are some mistakes for the two others. Let's see our accuracy.<jupyter_code>iris["species"].decode(["iris-setosa","iris-versicolor"],[0,1],2)
iris.add_feature("cluster_comparaison",iris[name].final_transformation()+"="+iris["species"].final_transformation())
iris["cluster_comparaison"].describe()<jupyter_output>The new RVC 'cluster_comparaison' was added to the RVD.<jupyter_text>We have an accuracy of {{round(134/150*100,2)}}% which is quite good. However, we can still improve it.  SVM Classification SVM Classification on Iris SetosaLet's now try to see what we can do with the SVM classifier. We must use the one hot encoder in order to have multiple binary variables.<jupyter_code>iris=RVD("Iris",cur)
iris["species"].one_hot_encoder()
iris["species_Iris_setosa"].hist(method="count")<jupyter_output>3 new features: species_Iris_setosa, species_Iris_versicolor, species_Iris_virginica<jupyter_text>We can save our RVD as a view.<jupyter_code>from vertica_ml_python import drop_view
drop_view("iris_temp",cur)
iris.save("iris_temp")<jupyter_output>/!\ Warning: The view iris_temp doesn't exist !
The RVD was successfully saved.<jupyter_text>We can now try to find the hyperplan which will separate the data. We will create many SVM in order to see the plan in many different dimensions.  SVM with only one feature<jupyter_code>from vertica_ml_python import svm_classifier
drop_model("svm_iris",cur)
svm_iris=svm_classifier(model_name="svm_iris",input_relation="iris_temp",response_column="Species_Iris_setosa",
                   predictor_columns=["PetalLengthCm"],cursor=cur)
svm_iris.plot()<jupyter_output>The model svm_iris was successfully dropped.<jupyter_text>SVM with only two features<jupyter_code>from vertica_ml_python import svm_classifier
drop_model("svm_iris",cur)
svm_iris=svm_classifier(model_name="svm_iris",input_relation="iris_temp",response_column="Species_Iris_setosa",
                   predictor_columns=["PetalLengthCm","SepalLengthCm",],cursor=cur)
print(svm_iris)
svm_iris.plot()<jupyter_output>The model svm_iris was successfully dropped.<jupyter_text>SVM with three features<jupyter_code>from vertica_ml_python import svm_classifier
drop_model("svm_iris",cur)
svm_iris=svm_classifier(model_name="svm_iris",input_relation="iris_temp",response_column="Species_Iris_setosa",
                   predictor_columns=["PetalLengthCm","SepalLengthCm","PetalWidthCm"],cursor=cur)
svm_iris.plot()<jupyter_output>The model svm_iris was successfully dropped.<jupyter_text>SVM with four features<jupyter_code>from vertica_ml_python import svm_classifier
drop_model("svm_iris",cur)
svm_iris=svm_classifier(model_name="svm_iris",input_relation="iris_temp",response_column="Species_Iris_setosa",
                   predictor_columns=["PetalLengthCm","SepalLengthCm","PetalWidthCm","SepalWidthCm"],cursor=cur)
svm_iris<jupyter_output>The model svm_iris was successfully dropped.<jupyter_text>For this model, we can not plot the hyperplan (sorry I do not know the fourth dimension) but we wan see the importance of each feature.<jupyter_code>svm_iris.features_importance()<jupyter_output><empty_output><jupyter_text>As expected the Petal Length is very important. Let's see the model accuracy.<jupyter_code>svm_iris.error_rate()<jupyter_output><empty_output><jupyter_text>We can see that no matter the number of variables, we have an accuracy of 100%. Besides, we can see the hyperplan separating all the data perfectly. For this dataset, we can build a very easy model with only the Petal Length variable to predict if the specie is Iris Setosa. We can now try to see how to evaluate the others.  SVM Classification on the two other speciesWe must delete the Iris Setosa from our RVD.<jupyter_code>iris=RVD('Iris',cur)
iris["species"].decode(["Iris-setosa","Iris-versicolor"],[None,0],1)
iris["species"].dropna()
iris["species"].convert_to_num()<jupyter_output>50 elements were dropped<jupyter_text>We can now save our RVD.<jupyter_code>drop_view("iris_temp",cur)
iris.save("iris_temp")<jupyter_output>The view iris_temp was successfully dropped.
The RVD was successfully saved.<jupyter_text>Let's now build our model.<jupyter_code>from vertica_ml_python import svm_classifier
drop_model("svm_iris",cur)
svm_iris=svm_classifier(model_name="svm_iris",input_relation="iris_temp",response_column="Species",
                   predictor_columns=["PetalLengthCm","SepalLengthCm","PetalWidthCm","SepalWidthCm"],cursor=cur)
print(svm_iris)
svm_iris.features_importance()
svm_iris.roc()
svm_iris.confusion_matrix()
svm_iris.error_rate()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09377325725626395, 'fraction_numerical': 0.007855784338046803, 'mean_word_length': 6.998677248677248, 'pattern_counts': {'":': 0, '<': 117, '<?xml version=': 0, '>': 117, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '32', 'dir': 'jupyter-structured-clean-dedup', 'id': '220085', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 4011, 'n_tokens_neox': 3850, 'n_words': 1119, 'path': 'example/iris_docker.ipynb', 'repo_name': 'bryanherger/jupyter-vertica-python', 'size': '12105'}