Classifying data using naive bayes

This Naive Bayes example uses the HouseVotes84 data set to show you how to build a model.

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

  1. Create the Naive Bayes model, named naive_house84_model, using the house84_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)

  2. 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

  3. 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.77850125111219e-08 |    0.999999962214987
 384 | democrat   |                 1 |                    1 |                    0
 387 | democrat   |                 1 |                    1 |                    0
 406 | republican | 0.999999945980143 | 5.40198564592332e-08 |    0.999999945980143
 419 | democrat   |                 1 |                    1 |                    0
 421 | republican | 0.922808855631005 |   0.0771911443689949 |    0.922808855631005
.
.
.
(109 rows)

See also