<jupyter_start><jupyter_text>The percent above should multiply 100! We can see the first 6 columns have too much of missing values. We can drop them from our RVD (the ChangeData table will never be modified: all the actions are only on our RVD).<jupyter_code># drop columns that have too many nulls
ChangeData["FINANCIAL_IMPACT"].drop_column()
ChangeData["COLLISION_SEVERITY"].drop_column()
ChangeData["IMPACT_SEVERITY"].drop_column()
ChangeData["RISK_SEVERITY"].drop_column()
ChangeData["TIME_PERIOD_CONFLICT"].drop_column()
ChangeData["SEVERITY"].drop_column()
ChangeData["GL_NUMBER"].drop_column()
# Fill missing value 
ChangeData["HP_CR_HIGH_THLD_NR"].fillna(val=0) # set missing as 0
ChangeData["HP_CR_SCORE_NR"].fillna(val=0) # set missing as 0
ChangeData["ML_CR_SCORE_RSLT_KY"].fillna(val=0) # set missing as 0
ChangeData["HP_IMPC_ANAL_FND_CT"].fillna(val=0) # set missing as 0
# As the missing number of row is very small, we can delete them
ChangeData["PRIORITY_CODE"].dropna()
# Convert some values using decode method
ChangeData["RISK_ASSESSMENT"].decode(["low","medium", "high","0","10","20", "30"], ["10","20", "30","0","10","20", "30"],"0")

# decode strings to numbers
ChangeData["ML_CHG_IMPC_ASMT_KY"].decode(["0","10","2","20","3","30","40","45","46","47","48",", High - Multiple Domains", "High - Multiple Domains","High - Multiple Domainsd","High - Multiple Domainss","High - Multiple Domainst","High – Multiple Domains","I High - Multiple Domainsd","Medium - One Domaind","Medium – One Domain","cd /Medium - One Domain","medium","application","6ow - One Sub-Domain","LOW","Low - One Sub-Domaina","Low - One Sub-Domainhank","Low - One Sub-Domains","Low - One Sub-Doman","Low – One Sub-Domain","iLow - One Sub-Domaint","inLow - One Sub-Domainp","low"], ["0","10","2","20","3","30","40","45","46","47","48","41","41","41","41","41","41","41","31","31","31","31","31","21","21","21","21","21","21","21","21","21","21"],"0")


# convert category with one hot encoding
ChangeData["CATEGORY"].one_hot_encoder()
# We filter rows where HP_IMPC_ANAL_FND_CT<0
ChangeData.filter("HP_IMPC_ANAL_FND_CT >= 0")

# We filter rows where REQ_IMP_DURATION<0
ChangeData.filter("REQ_IMP_DURATION >= 0")
# We filter rows where REQ_IMP_DURATION<1e10
ChangeData.filter("REQ_IMP_DURATION < 1e10")

# We filter rows where PLN_IMP_DURATION<0
ChangeData.filter("PLN_IMP_DURATION >= 0")
# We filter rows where PLN_IMP_DURATION<1e10
ChangeData.filter("PLN_IMP_DURATION < 1e10")
#We can see the current RVD virtual table.
ChangeData.current_table()
ChangeData.history()
# save cleansed data to Vertica database 
drop_table("ChangeData_Cleansed",cur)
drop_view("ChangeData_Cleansed",cur)
ChangeData.sql_on_off()
ChangeData.save(name="ChangeData_Cleansed", mode="table")<jupyter_output>The table ChangeData_Cleansed was successfully dropped.
/!\ Warning: The view ChangeData_Cleansed doesn't exist !<jupyter_text>Load cleansed data<jupyter_code># If the view is already in the Database. We can create the RVD as follows:
ChangeData_Cleansed=RVD('ChangeData_Cleansed',cur)
# nomalize the data
ChangeData_Cleansed.normalize() # only apply to float type columns
ChangeData_Cleansed.scatter_matrix ()
# find correlation matrix
ChangeData_Cleansed.corr()<jupyter_output><empty_output><jupyter_text>K-Means Clustering<jupyter_code># Kmeans Clustering

from vertica_ml_python import kmeans
from vertica_ml_python import drop_model

drop_model("ChangeRisk_kmeans",cur)
# find the optimal number of clusters using the elbow curve.
from vertica_ml_python import elbow
elbow(input_relation="ChangeData_Cleansed",
      input_columns=["PREDECESSOR_CHANGES","PRIORITY_CODE","PROBLEMS","REQ_IMP_DURATION","TASKS",
                     "CATEGORY_Emergency_Changes","CATEGORY_KM_Document","CATEGORY_Normal_Changes",
                     "CATEGORY_Routine_Changes","ASSETS","ASSOCIATED_CHANGES","CHILD_CHANGES","DEPENDENT_CHANGES",
                     "HP_CR_HIGH_THLD_NR","HP_CR_SCORE_NR","HP_IMPC_ANAL_FND_CT","IMPACT_ORGS","INCIDENTS","INTERACTIONS",
                     "KNOWNERRORS","MAJOR_INCIDENTS","ML_CR_SCORE_RSLT_KY","OVO_OUTAGES","PARENT_CHANGES",
                     "PLN_IMP_DURATION"],
      cursor=cur,max_num_cluster=8)
# Create Kmeans model, let's try 4 for the number of cluster
ChangeRisk_kmeans=kmeans(model_name="ChangeRisk_kmeans",input_relation="ChangeData_Cleansed",
                   input_columns=["PREDECESSOR_CHANGES","PRIORITY_CODE","PROBLEMS","REQ_IMP_DURATION","TASKS",
                     "CATEGORY_Emergency_Changes","CATEGORY_KM_Document","CATEGORY_Normal_Changes",
                     "CATEGORY_Routine_Changes","ASSETS","ASSOCIATED_CHANGES","CHILD_CHANGES","DEPENDENT_CHANGES",
                     "HP_CR_HIGH_THLD_NR","HP_CR_SCORE_NR","HP_IMPC_ANAL_FND_CT","IMPACT_ORGS","INCIDENTS","INTERACTIONS",
                     "KNOWNERRORS","MAJOR_INCIDENTS","ML_CR_SCORE_RSLT_KY","OVO_OUTAGES","PARENT_CHANGES",
                     "PLN_IMP_DURATION"],
                   num_clusters=4,cursor=cur)

#Let's see what the model looks like.
print(ChangeRisk_kmeans)
ChangeRisk_kmeans.cluster_SS()
# see the importance of each feature.
ChangeRisk_kmeans.features_importance()
#Let's add the clusters to our RVD.
name=ChangeRisk_kmeans.add_to_rvd(ChangeData_Cleansed)
ChangeData_Cleansed["kmeans_cluster5983"].describe()
ChangeData_Cleansed.head(10)
ChangeData_Cleansed["RISK_ASSESSMENT"].label_encode()
ChangeData_Cleansed["RISK_ASSESSMENT"].describe()
ChangeData_Cleansed.dtypes()
ChangeData_Cleansed.scatter2D(columns=["RISK_ASSESSMENT","kmeans_cluster5983","ASSETS"])<jupyter_output><empty_output><jupyter_text>SVM ClassificationLet's now try to see what we can do with the SVM classifier. We must use the one hot encoder for the Risk assesment in order to have multiple binary variables.Looks like in Vertica SVM function, there is no kernel option! And not clear what kernel is used!?<jupyter_code># reload RVD
ChangeData_Cleansed=RVD('ChangeData_Cleansed',cur)
# nomalize the data
ChangeData_Cleansed.normalize() # only apply to float type columns
# convert Risk assesment with one hot encoding
ChangeData_Cleansed["RISK_ASSESSMENT"].one_hot_encoder()
ChangeData_Cleansed.head()
ChangeData_Cleansed["RISK_ASSESSMENT_10"].hist(method="count")
# save RVD to a temporary view
drop_view("ChangeData_temp",cur)
ChangeData_Cleansed.save("ChangeData_temp")
from vertica_ml_python import svm_classifier

# classify RISK_ASSESSMENT_10 first, 
# try one feature only, from kmeans clustereing above we can see, REQ_IMP_DURATION is most important
drop_model("svm_ChangeData",cur)
svm_ChangeData=svm_classifier(model_name="svm_ChangeData",input_relation="ChangeData_temp",response_column="RISK_ASSESSMENT_10",
                   predictor_columns=["REQ_IMP_DURATION"],cursor=cur)

print(svm_ChangeData)
svm_ChangeData.plot()
# looks like not very clear boundary
#Let's see the model accuracy.
svm_ChangeData.error_rate()
svm_ChangeData.confusion_matrix()
# classify RISK_ASSESSMENT_10 first, try two features only, from kmeans clustereing above we can see, REQ_IMP_DURATION 
# and PLN_IMP_DURATION are two most important
drop_model("svm_ChangeData",cur)
svm_ChangeData=svm_classifier(model_name="svm_ChangeData",input_relation="ChangeData_temp",response_column="RISK_ASSESSMENT_10",
                   predictor_columns=["REQ_IMP_DURATION","PLN_IMP_DURATION"],cursor=cur)
print(svm_ChangeData)
svm_ChangeData.plot()
# looks better
#Let's see the model accuracy.
svm_ChangeData.error_rate()
svm_ChangeData.confusion_matrix()
# classify RISK_ASSESSMENT_10 first, try top 3 columns features
drop_model("svm_ChangeData",cur)
svm_ChangeData=svm_classifier(model_name="svm_ChangeData",input_relation="ChangeData_temp",response_column="RISK_ASSESSMENT_10",
                   predictor_columns=["REQ_IMP_DURATION","PLN_IMP_DURATION","TASKS"],cursor=cur)
print(svm_ChangeData)
svm_ChangeData.plot()
# looks ok
#Let's see the model accuracy.
svm_ChangeData.error_rate()
# classify RISK_ASSESSMENT_10 first, try all features
drop_model("svm_ChangeData",cur)
svm_ChangeData=svm_classifier(model_name="svm_ChangeData",input_relation="ChangeData_temp",response_column="RISK_ASSESSMENT_10",
                   predictor_columns=["PREDECESSOR_CHANGES","PRIORITY_CODE","PROBLEMS","REQ_IMP_DURATION","TASKS",
                     "CATEGORY_Emergency_Changes","CATEGORY_KM_Document","CATEGORY_Normal_Changes",
                     "CATEGORY_Routine_Changes","ASSETS","ASSOCIATED_CHANGES","CHILD_CHANGES","DEPENDENT_CHANGES",
                     "HP_CR_HIGH_THLD_NR","HP_CR_SCORE_NR","HP_IMPC_ANAL_FND_CT","IMPACT_ORGS","INCIDENTS","INTERACTIONS",
                     "KNOWNERRORS","MAJOR_INCIDENTS","ML_CR_SCORE_RSLT_KY","OVO_OUTAGES","PARENT_CHANGES",
                     "PLN_IMP_DURATION"],cursor=cur)
print(svm_ChangeData)

#Let's see the model accuracy.
svm_ChangeData.error_rate()
# not so good, total error rate is 14.6%
svm_ChangeData.confusion_matrix()
# For this model, we can not plot the hyperplane, but we wan see the importance of each feature.
svm_ChangeData.features_importance()<jupyter_output><empty_output><jupyter_text>SVM Classification on the other classes<jupyter_code># reload data 
ChangeData_Cleansed=RVD('ChangeData_Cleansed',cur)

# nomalize the data
ChangeData_Cleansed.normalize() # only apply to float type columns

# convert Risk assesment with one hot encoding
ChangeData_Cleansed["RISK_ASSESSMENT"].one_hot_encoder()
# delete the class RISK_ASSESSMENT_10 that has been classified
ChangeData_Cleansed.filter("RISK_ASSESSMENT_10=0")
ChangeData_Cleansed["RISK_ASSESSMENT_20"].hist(method="count")
# save RVD to a temporary view
drop_view("ChangeData_temp",cur)
ChangeData_Cleansed.save("ChangeData_temp")
# classify RISK_ASSESSMENT_20 now, try all columns features
drop_model("svm_ChangeData",cur)
svm_ChangeData=svm_classifier(model_name="svm_ChangeData",input_relation="ChangeData_temp",response_column="RISK_ASSESSMENT_20",
                   predictor_columns=["PREDECESSOR_CHANGES","PRIORITY_CODE","PROBLEMS","REQ_IMP_DURATION","TASKS",
                     "CATEGORY_Emergency_Changes","CATEGORY_KM_Document","CATEGORY_Normal_Changes",
                     "CATEGORY_Routine_Changes","ASSETS","ASSOCIATED_CHANGES","CHILD_CHANGES","DEPENDENT_CHANGES",
                     "HP_CR_HIGH_THLD_NR","HP_CR_SCORE_NR","HP_IMPC_ANAL_FND_CT","IMPACT_ORGS","INCIDENTS","INTERACTIONS",
                     "KNOWNERRORS","MAJOR_INCIDENTS","ML_CR_SCORE_RSLT_KY","OVO_OUTAGES","PARENT_CHANGES",
                     "PLN_IMP_DURATION"],cursor=cur)
print(svm_ChangeData)

#Let's see the model accuracy.
svm_ChangeData.error_rate()
svm_ChangeData.confusion_matrix()
# For this model, we can not plot the hyperplane, but we wan see the importance of each feature.
svm_ChangeData.features_importance()
# Now for rest of two classes  RISK_ASSESSMENT_0 and RISK_ASSESSMENT_30

# reload data 
ChangeData_Cleansed=RVD('ChangeData_Cleansed',cur)

# nomalize the data
ChangeData_Cleansed.normalize() # only apply to float type columns

# convert Risk assesment with one hot encoding
ChangeData_Cleansed["RISK_ASSESSMENT"].one_hot_encoder()

# delete the class RISK_ASSESSMENT_10 and RISK_ASSESSMENT_20 that have been classified
ChangeData_Cleansed.filter(["RISK_ASSESSMENT_10=0","RISK_ASSESSMENT_20=0"])

ChangeData_Cleansed["RISK_ASSESSMENT_30"].hist(method="count")
# save RVD to a temporary view
drop_view("ChangeData_temp",cur)
#ChangeData_Cleansed.sql_on_off()
ChangeData_Cleansed.save("ChangeData_temp")
# classify RISK_ASSESSMENT_30 now, try all columns features
drop_model("svm_ChangeData",cur)
svm_ChangeData=svm_classifier(model_name="svm_ChangeData",input_relation="ChangeData_temp",response_column="RISK_ASSESSMENT_30",
                   predictor_columns=["PREDECESSOR_CHANGES","PRIORITY_CODE","PROBLEMS","REQ_IMP_DURATION","TASKS",
                     "CATEGORY_Emergency_Changes","CATEGORY_KM_Document","CATEGORY_Normal_Changes",
                     "CATEGORY_Routine_Changes","ASSETS","ASSOCIATED_CHANGES","CHILD_CHANGES","DEPENDENT_CHANGES",
                     "HP_CR_HIGH_THLD_NR","HP_CR_SCORE_NR","HP_IMPC_ANAL_FND_CT","IMPACT_ORGS","INCIDENTS","INTERACTIONS",
                     "KNOWNERRORS","MAJOR_INCIDENTS","ML_CR_SCORE_RSLT_KY","OVO_OUTAGES","PARENT_CHANGES",
                     "PLN_IMP_DURATION"],cursor=cur)
print(svm_ChangeData)

#Let's see the model accuracy.
svm_ChangeData.error_rate()
svm_ChangeData.confusion_matrix()
# For this model, we can not plot the hyperplane, but we wan see the importance of each feature.
svm_ChangeData.features_importance()
# summarize three classifiers
Total_error_rate=(1-(22241+5649+667+4594)/34084)*100
print(Total_error_rate,'%')
# Note, this notebook does not perform any prediction. In reality, data set should be split in to training and testing sets.<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11566583696328563, 'fraction_numerical': 0.017579340385812072, 'mean_word_length': 6.625741399762752, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 23, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '79139', 'license': 'Apache-2.0', 'n_tokens_mistral': 4685, 'n_tokens_neox': 4393, 'n_words': 773, 'path': 'notebooks/SmartChange2.ipynb', 'repo_name': 'wangjin2000/Vertica-ML-Python', 'size': '12856'}