Class03 Answer:

Work through these demos: https://github.com/danbikle/madlib_demos

I captured screen activity of me starting this lab:


[ann@cen7 ~]$ 
[ann@cen7 ~]$ ll
total 12
-rw-------. 1 ann ann   45 Aug 20  2016 config
drwxr-xr-x. 3 ann ann   29 Jun 25 12:16 Desktop
drwxr-xr-x. 2 ann ann    6 Aug 19  2016 Documents
drwxr-xr-x. 2 ann ann    6 Aug 19  2016 Downloads
drwxr-xr-x. 3 ann ann 4096 Jun 25 15:06 madlib_demos
drwxr-xr-x. 2 ann ann    6 Aug 19  2016 Music
drwxr-xr-x. 2 ann ann    6 Aug 19  2016 Pictures
drwxr-xr-x. 2 ann ann    6 Aug 19  2016 Public
-rw-rw-r--. 1 ann ann  448 Aug 22  2016 readme.txt
drwxr-xr-x. 2 ann ann    6 Aug 19  2016 Templates
drwxr-xr-x. 2 ann ann    6 Aug 19  2016 Videos
[ann@cen7 ~]$ 
[ann@cen7 ~]$ 
[ann@cen7 ~]$ cd madlib_demos/
[ann@cen7 madlib_demos]$ 
[ann@cen7 madlib_demos]$ git pull origin master
From https://github.com/danbikle/madlib_demos
 * branch            master     -> FETCH_HEAD
Already up-to-date.
[ann@cen7 madlib_demos]$ 
[ann@cen7 madlib_demos]$ ll
total 1468
-rwxrwxr-x. 1 ann ann      51 Jun 25 14:51 curl_gspc.bash
-rw-rw-r--. 1 ann ann    4703 Aug 20  2016 demo10.sql
-rw-rw-r--. 1 ann ann    2835 Aug 20  2016 demo11.sql
-rw-rw-r--. 1 ann ann    1959 Jun 25 14:52 demo12.sql
-rw-rw-r--. 1 ann ann    5629 Aug 22  2016 demo13.sql
-rw-rw-r--. 1 ann ann    4169 Aug 22  2016 demo14.sql
-rw-rw-r--. 1 ann ann    3113 Aug 21  2016 demo15.sql
-rw-rw-r--. 1 ann ann    3828 Aug 22  2016 demo16.sql
-rw-rw-r--. 1 ann ann    3162 Aug 21  2016 demo17.sql
-rwxrwxr-x. 1 ann ann     313 Aug 21  2016 demo18.bash
-rw-rw-r--. 1 ann ann    5607 Aug 22  2016 demo18.sql
-rw-rw-r--. 1 ann ann    1680 Aug 21  2016 demo18.txt
-rwxrwxr-x. 1 ann ann     313 Aug 21  2016 demo19.bash
-rw-rw-r--. 1 ann ann    6171 Aug 21  2016 demo19.sql
-rw-rw-r--. 1 ann ann    1670 Aug 22  2016 demo19.txt
-rw-rw-r--. 1 ann ann     502 Jun 25 15:05 fillprices.sql
-rw-rw-r--. 1 ann ann 1271705 Jun 25 15:02 gspc.csv
-rw-rw-r--. 1 ann ann     193 Aug 21  2016 j.sql
-rw-rw-r--. 1 ann ann     318 Jun 25 14:44 logr_compare.txt
-rw-rw-r--. 1 ann ann    1611 Aug 20  2016 madlib_installation_steps.txt
-rwxrwxr-x. 1 ann ann     924 Jun 25 14:44 many_whatif.bash
-rw-rw-r--. 1 ann ann     306 Jun 25 14:44 many_whatif_get.sql
-rw-rw-r--. 1 ann ann     229 Jun 25 14:44 many_whatif_prep.sql
-rw-rw-r--. 1 ann ann     172 Jun 25 14:44 many_whatif_rpt.sql
-rwxrwxr-x. 1 ann ann     234 Aug 20  2016 psqlmad
-rw-rw-r--. 1 ann ann    4696 Jun 25 14:44 README.md
-rw-rw-r--. 1 ann ann    2091 Aug 22  2016 some_results.txt
-rw-rw-r--. 1 ann ann    4468 Aug 21  2016 sqldemo1.sql
-rw-rw-r--. 1 ann ann    7926 Aug 21  2016 sqldemo1.txt
-rwxrwxr-x. 1 ann ann     311 Jun 25 14:44 svmreg11.bash
-rw-rw-r--. 1 ann ann     239 Jun 25 14:44 svmreg11collect_pred.sql
-rw-rw-r--. 1 ann ann     309 Jun 25 14:44 svmreg11cr_pred.sql
-rw-rw-r--. 1 ann ann     878 Jun 25 14:44 svmreg11rpt.sql
-rw-rw-r--. 1 ann ann    3999 Aug 21  2016 svmreg11.sql
-rw-rw-r--. 1 ann ann   19040 Aug 22  2016 svmreg11.txt
-rwxrwxr-x. 1 ann ann     347 Jun 25 14:44 svmreg12.bash
-rw-rw-r--. 1 ann ann    3747 Jun 25 14:44 svmreg12.sql
-rw-rw-r--. 1 ann ann     787 Jun 25 14:44 svmreg12.txt
-rwxrwxr-x. 1 ann ann     485 Jun 25 14:44 svmreg13.bash
-rw-rw-r--. 1 ann ann    4366 Jun 25 14:44 svmreg13.sql
-rw-rw-r--. 1 ann ann     853 Jun 25 14:44 svmreg13.txt
-rwxrwxr-x. 1 ann ann     607 Jun 25 14:44 whatif.bash
-rw-rw-r--. 1 ann ann    4934 Jun 25 14:44 whatif.sql
[ann@cen7 madlib_demos]$ 
[ann@cen7 madlib_demos]$ 
[ann@cen7 madlib_demos]$ bash -x curl_gspc.bash 
+ curl -L ml4.us/csv/GSPC.csv
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
100 1241k  100 1241k    0     0   418k      0  0:00:02  0:00:02 --:--:--  754k
[ann@cen7 madlib_demos]$ 
[ann@cen7 madlib_demos]$ 
[ann@cen7 madlib_demos]$ head gspc.csv
Date,Open,High,Low,Close,Adj Close,Volume
1950-01-03,16.660000,16.660000,16.660000,16.660000,16.660000,1260000
1950-01-04,16.850000,16.850000,16.850000,16.850000,16.850000,1890000
1950-01-05,16.930000,16.930000,16.930000,16.930000,16.930000,2550000
1950-01-06,16.980000,16.980000,16.980000,16.980000,16.980000,2010000
1950-01-09,17.090000,17.090000,17.080000,17.080000,17.080000,3850000
1950-01-10,17.030001,17.030001,17.030001,17.030001,17.030001,2160000
1950-01-11,17.090000,17.090000,17.090000,17.090000,17.090000,2630000
1950-01-12,16.760000,16.760000,16.760000,16.760000,16.760000,2970000
1950-01-13,16.670000,16.670000,16.670000,16.670000,16.670000,3330000
[ann@cen7 madlib_demos]$ 
[ann@cen7 madlib_demos]$ ./psqlmad -f demo10.sql
DROP TABLE
CREATE TABLE
INSERT 0 20
 id | second_attack | treatment | trait_anxiety 
----+---------------+-----------+---------------
  1 |             1 |         1 |            70
  3 |             1 |         1 |            50
  5 |             1 |         0 |            40
  7 |             1 |         0 |            75
  9 |             1 |         0 |            70
 11 |             0 |         1 |            65
 13 |             0 |         1 |            45
 15 |             0 |         1 |            40
 17 |             0 |         0 |            55
 19 |             0 |         0 |            50
  2 |             1 |         1 |            80
  4 |             1 |         0 |            60
  6 |             1 |         0 |            65
  8 |             1 |         0 |            80
 10 |             1 |         0 |            60
 12 |             0 |         1 |            50
 14 |             0 |         1 |            35
 16 |             0 |         1 |            50
 18 |             0 |         0 |            45
 20 |             0 |         0 |            60
(20 rows)

 logregr_train 
---------------
 
(1 row)

Expanded display is on.
-[ RECORD 1 ]------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
coef                     | {-6.36346994178187,-1.02410605239327,0.119044916668606}
log_likelihood           | -9.41018298388876
std_err                  | {3.21389766375091,1.17107844860318,0.0549790458269303}
z_stats                  | {-1.9799852414576,-0.874498248699553,2.1652779686892}
p_values                 | {0.0477051870698109,0.381846973530448,0.0303664045046153}
odds_ratios              | {0.00172337630923231,0.359117354054955,1.12642051220895}
condition_no             | 326.081922791559
num_rows_processed       | 20
num_missing_rows_skipped | 0
num_iterations           | 5
variance_covariance      | {{10.3291381930635,-0.474304665195729,-0.171995901260048},{-0.474304665195729,1.37142473278283,-0.00119520703381591},{-0.171995901260048,-0.00119520703381591,0.00302269548003971}}

Expanded display is off.
   attribute   |    coefficient    |   standard_error   |       z_stat       |       pvalue       |     odds_ratio      
---------------+-------------------+--------------------+--------------------+--------------------+---------------------
 intercept     | -6.36346994178187 |   3.21389766375091 |   -1.9799852414576 | 0.0477051870698109 | 0.00172337630923231
 treatment     | -1.02410605239327 |   1.17107844860318 | -0.874498248699553 |  0.381846973530448 |   0.359117354054955
 trait_anxiety | 0.119044916668606 | 0.0549790458269303 |    2.1652779686892 | 0.0303664045046153 |    1.12642051220895
(3 rows)

 id | logregr_predict | second_attack 
----+-----------------+---------------
  1 | t               |             1
  2 | t               |             1
  3 | f               |             1
  4 | t               |             1
  5 | f               |             1
  6 | t               |             1
  7 | t               |             1
  8 | t               |             1
  9 | t               |             1
 10 | t               |             1
 11 | t               |             0
 12 | f               |             0
 13 | f               |             0
 14 | f               |             0
 15 | f               |             0
 16 | f               |             0
 17 | t               |             0
 18 | f               |             0
 19 | f               |             0
 20 | t               |             0
(20 rows)

 id | logregr_predict | second_attack 
----+-----------------+---------------
  1 | t               |             1
  2 | t               |             1
  4 | t               |             1
  6 | t               |             1
  7 | t               |             1
  8 | t               |             1
  9 | t               |             1
 10 | t               |             1
(8 rows)

 id | logregr_predict | second_attack 
----+-----------------+---------------
 12 | f               |             0
 13 | f               |             0
 14 | f               |             0
 15 | f               |             0
 16 | f               |             0
 18 | f               |             0
 19 | f               |             0
(7 rows)

 id | logregr_predict | second_attack 
----+-----------------+---------------
 11 | t               |             0
 17 | t               |             0
 20 | t               |             0
(3 rows)

 id | logregr_predict | second_attack 
----+-----------------+---------------
  3 | f               |             1
  5 | f               |             1
(2 rows)

 id | logregr_predict_prob | second_attack 
----+----------------------+---------------
  1 |    0.720223028941525 |             1
  2 |    0.894354902502046 |             1
  3 |    0.192269541755172 |             1
  4 |    0.685513072239347 |             1
  5 |     0.16774788150886 |             1
  6 |     0.79809810891514 |             1
  7 |    0.928568075752502 |             1
  8 |     0.95930576369357 |             1
  9 |    0.877576117431451 |             1
 10 |    0.685513072239347 |             1
 11 |    0.586700895943316 |             0
 12 |    0.192269541755172 |             0
 13 |    0.116032010632995 |             0
 14 |   0.0383829143134989 |             0
 15 |   0.0674976224147607 |             0
 16 |    0.192269541755172 |             0
 17 |    0.545870774302622 |             0
 18 |    0.267675422387135 |             0
 19 |    0.398618639285114 |             0
 20 |    0.685513072239347 |             0
(20 rows)

 id | logregr_predict_prob | second_attack 
----+----------------------+---------------
 14 |   0.0383829143134989 |             0
 15 |   0.0674976224147607 |             0
 13 |    0.116032010632995 |             0
  5 |     0.16774788150886 |             1
 12 |    0.192269541755172 |             0
  3 |    0.192269541755172 |             1
 16 |    0.192269541755172 |             0
 18 |    0.267675422387135 |             0
 19 |    0.398618639285114 |             0
 17 |    0.545870774302622 |             0
 11 |    0.586700895943316 |             0
 10 |    0.685513072239347 |             1
  4 |    0.685513072239347 |             1
 20 |    0.685513072239347 |             0
  1 |    0.720223028941525 |             1
  6 |     0.79809810891514 |             1
  9 |    0.877576117431451 |             1
  2 |    0.894354902502046 |             1
  7 |    0.928568075752502 |             1
  8 |     0.95930576369357 |             1
(20 rows)

[ann@cen7 madlib_demos]$ 
[ann@cen7 madlib_demos]$

To understand the heart-attack prediction demo, read the comments in this script:

https://github.com/danbikle/madlib_demos/blob/master/demo10.sql


Class03 Lab


ml4.us About Blog Contact Class01 Class02 Class03 Class04 Class05 Class06 Class07 Class08 Class09 Class10 dan101 Forum Google Hangout Vboxen