Classifying data using naive bayes
This Naive Bayes example uses the HouseVotes84 data set to show you how to build a model. With this model, you can predict which party the member of the United States Congress is affiliated based on their voting record. To aid in classifying the data it has been cleaned, and any missed votes have been replaced. The cleaned data replaces missed votes with the voter's party majority vote. For example, suppose a member of the Democrats had a missing value for vote1 and majority of the Democrats voted in favor. This example replaces all missing Democrats' votes for vote1 with a vote in favor.
In this example, approximately 75% of the cleaned HouseVotes84 data is randomly selected and copied to a training table. The remaining cleaned HouseVotes84 data is used as a testing table.
Before you begin the example, load the Machine Learning sample data.You must also load the naive_bayes_data_prepration.sql
script:
$ /opt/vertica/bin/vsql d <name of your database> f naive_bayes_data_preparation.sql

Create the Naive Bayes model, named
naive_house84_model
, using thehouse84_train
training data.=> SELECT NAIVE_BAYES('naive_house84_model', 'house84_train', 'party', '*' USING PARAMETERS exclude_columns='party, id'); NAIVE_BAYES  Finished. Accepted Rows: 315 Rejected Rows: 0 (1 row)

Create a new table, named
predicted_party_naive
. Populate this table with the prediction outputs you obtain from the PREDICT_NAIVE_BAYES function on your test data.=> CREATE TABLE predicted_party_naive AS SELECT party, PREDICT_NAIVE_BAYES (vote1, vote2, vote3, vote4, vote5, vote6, vote7, vote8, vote9, vote10, vote11, vote12, vote13, vote14, vote15, vote16 USING PARAMETERS model_name = 'naive_house84_model', type = 'response') AS Predicted_Party FROM house84_test; CREATE TABLE

Calculate the accuracy of the model's predictions.
=> SELECT (Predictions.Num_Correct_Predictions / Count.Total_Count) AS Percent_Accuracy FROM ( SELECT COUNT(Predicted_Party) AS Num_Correct_Predictions FROM predicted_party_naive WHERE party = Predicted_Party ) AS Predictions, ( SELECT COUNT(party) AS Total_Count FROM predicted_party_naive ) AS Count; Percent_Accuracy  0.933333333333333333 (1 row)
The model correctly predicted the party of the members of Congress based on their voting patterns with 93% accuracy.
Viewing the probability of each class
You can also view the probability of each class. Use PREDICT_NAIVE_BAYES_CLASSES to see the probability of each class.
=> SELECT PREDICT_NAIVE_BAYES_CLASSES (id, vote1, vote2, vote3, vote4, vote5,
vote6, vote7, vote8, vote9, vote10,
vote11, vote12, vote13, vote14,
vote15, vote16
USING PARAMETERS model_name = 'naive_house84_model',
key_columns = 'id', exclude_columns = 'id',
classes = 'democrat, republican')
OVER() FROM house84_test;
id  Predicted  Probability  democrat  republican
++++
368  democrat  1  1  0
372  democrat  1  1  0
374  democrat  1  1  0
378  republican  0.999999962214987  3.77850125111219e08  0.999999962214987
384  democrat  1  1  0
387  democrat  1  1  0
406  republican  0.999999945980143  5.40198564592332e08  0.999999945980143
419  democrat  1  1  0
421  republican  0.922808855631005  0.0771911443689949  0.922808855631005
.
.
.
(109 rows)