<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Prediction metrics
This module provides a set of metrics to evaluate the quality of predictions of a model. A typical function will take a set of "prediction" and "observation" values and use them to calculate the desired metric, unless noted otherwise.
Step1:  Continuous variables
Step2:  Binary classification
Create the sample data for binary classifier metrics
Step3:  Run the Binary Classifier metrics function and View the True Positive Rate and the False Positive Rate
Step4:  View all metrics at a given threshold value
Step5:  Run the Area Under ROC curve function
Step6:  Multi-class classification
Create the sample data for confusion matrix.
<ASSISTANT_TASK:>
Python Code:
%load_ext sql
# %sql postgresql://gpdbchina@10.194.10.68:55000/madlib
%sql postgresql://fmcquillan@localhost:5432/madlib
%sql select madlib.version();
Explanation: Prediction metrics
This module provides a set of metrics to evaluate the quality of predictions of a model. A typical function will take a set of "prediction" and "observation" values and use them to calculate the desired metric, unless noted otherwise.
End of explanation
%%sql 
DROP TABLE IF EXISTS test_set;
CREATE TABLE test_set(
                  pred FLOAT8,  -- predicted values
                  obs FLOAT8    -- actual observed values
                );
INSERT INTO test_set VALUES
  (37.5,53.1), (12.3,34.2), (74.2,65.4), (91.1,82.1);
SELECT * FROM test_set;
%%sql
DROP TABLE IF EXISTS table_out;
SELECT madlib.mean_abs_error( 'test_set', 'table_out', 'pred', 'obs');
SELECT * FROM table_out;
%%sql
DROP TABLE IF EXISTS table_out;
SELECT madlib.mean_abs_perc_error( 'test_set', 'table_out', 'pred', 'obs');
SELECT * FROM table_out;
%%sql
DROP TABLE IF EXISTS table_out;
SELECT madlib.mean_perc_error( 'test_set', 'table_out', 'pred', 'obs');
SELECT * FROM table_out;
%%sql 
DROP TABLE IF EXISTS table_out;
SELECT madlib.mean_squared_error( 'test_set', 'table_out', 'pred', 'obs');
SELECT * FROM table_out;
%%sql
DROP TABLE IF EXISTS table_out;
SELECT madlib.r2_score( 'test_set', 'table_out', 'pred', 'obs');
SELECT * FROM table_out;
%%sql
DROP TABLE IF EXISTS table_out;
SELECT madlib.adjusted_r2_score( 'test_set', 'table_out', 'pred', 'obs', 3, 100);
SELECT * FROM table_out;
Explanation: Continuous variables
End of explanation
%%sql
DROP TABLE IF EXISTS test_set;
CREATE TABLE test_set AS
    SELECT ((a*8)::integer)/8.0 pred,   -- prediction probability TRUE
        ((a*0.5+random()*0.5)>0.5) obs  -- actual observations
    FROM (select random() as a from generate_series(1,100)) x;
SELECT * FROM test_set;
Explanation: Binary classification
Create the sample data for binary classifier metrics:
End of explanation
%%sql
DROP TABLE IF EXISTS table_out;
SELECT madlib.binary_classifier( 'test_set', 'table_out', 'pred', 'obs');
SELECT threshold, tpr, fpr FROM table_out ORDER BY threshold;
Explanation: Run the Binary Classifier metrics function and View the True Positive Rate and the False Positive Rate:
End of explanation
%%sql
SELECT * FROM table_out WHERE threshold=0.5;
Explanation: View all metrics at a given threshold value:
End of explanation
%%sql
DROP TABLE IF EXISTS table_out;
SELECT madlib.area_under_roc( 'test_set', 'table_out', 'pred', 'obs');
SELECT * FROM table_out;
Explanation: Run the Area Under ROC curve function:
End of explanation
%%sql
DROP TABLE IF EXISTS test_set;
CREATE TABLE test_set AS
    SELECT (x+y)%5+1 AS pred,
        (x*y)%5 AS obs
    FROM generate_series(1,5) x,
        generate_series(1,5) y;
SELECT * FROM test_set;
%%sql
DROP TABLE IF EXISTS table_out;
SELECT madlib.confusion_matrix( 'test_set', 'table_out', 'pred', 'obs');
SELECT * FROM table_out ORDER BY class;
Explanation: Multi-class classification
Create the sample data for confusion matrix.
End of explanation
<END_TASK>