You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@madlib.apache.org by orhankislal <gi...@git.apache.org> on 2016/05/03 20:41:11 UTC

[GitHub] incubator-madlib pull request: Prediction Metrics: New module

GitHub user orhankislal opened a pull request:

    https://github.com/apache/incubator-madlib/pull/41

    Prediction Metrics: New module

    JIRA: MADLIB-907
    A collection of summary statistics to gauge model accuracy
    based on predicted values vs. ground-truth values.

You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/orhankislal/incubator-madlib feature/pred_metrics

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/incubator-madlib/pull/41.patch

To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:

    This closes #41
    
----

----


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request: Prediction Metrics: New module

Posted by orhankislal <gi...@git.apache.org>.
Github user orhankislal commented on the pull request:

    https://github.com/apache/incubator-madlib/pull/41#issuecomment-216662424
  
    You can use "make doc" to generate the docs locally. The madlib documentation can be found at http://madlib.incubator.apache.org/docs/latest/ but the docs for this module will not be there until the code is merged into master. 


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request: Prediction Metrics: New module

Posted by orhankislal <gi...@git.apache.org>.
Github user orhankislal closed the pull request at:

    https://github.com/apache/incubator-madlib/pull/41


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request: Prediction Metrics: New module

Posted by orhankislal <gi...@git.apache.org>.
Github user orhankislal commented on the pull request:

    https://github.com/apache/incubator-madlib/pull/41#issuecomment-216651893
  
    Thanks for your comments decibel. I'll close this pull request, review your comments, update the code and documentation accordingly and create a new pull request.


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request: Prediction Metrics: New module

Posted by decibel <gi...@git.apache.org>.
Github user decibel commented on a diff in the pull request:

    https://github.com/apache/incubator-madlib/pull/41#discussion_r61942899
  
    --- Diff: src/ports/postgres/modules/pred_metrics/pred_metrics.py_in ---
    @@ -0,0 +1,391 @@
    +# coding=utf-8
    +#
    +# Licensed to the Apache Software Foundation (ASF) under one
    +# or more contributor license agreements.  See the NOTICE file
    +# distributed with this work for additional information
    +# regarding copyright ownership.  The ASF licenses this file
    +# to you under the Apache License, Version 2.0 (the
    +# "License"); you may not use this file except in compliance
    +# with the License.  You may obtain a copy of the License at
    +#
    +#   http://www.apache.org/licenses/LICENSE-2.0
    +#
    +# Unless required by applicable law or agreed to in writing,
    +# software distributed under the License is distributed on an
    +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
    +# KIND, either express or implied.  See the License for the
    +# specific language governing permissions and limitations
    +# under the License.
    +
    +from utilities.utilities import unique_string
    +import plpy
    +
    +def mean_abs_error(
    +    schema_madlib, table_in, table_out, prediction_col, observed_col,
    +    grouping_cols=None):
    +    sql_st1 = """
    +        CREATE TABLE {table_out} AS
    +        SELECT
    +            SUM(ABS({prediction_col}- {observed_col}))
    +            /COUNT(*) AS mean_abs_error """.format(**locals())
    +    sql_st2= ""
    +    sql_st3= """ FROM {table_in} """.format(**locals())
    +    sql_st4= ""
    +    if grouping_cols:
    +        sql_st2= """ , {grouping_cols} """.format(**locals())
    +        sql_st4= """ GROUP BY {grouping_cols}""".format(**locals())
    +    sql_st = sql_st1+sql_st2+sql_st3+sql_st4
    +    plpy.execute(sql_st)
    +
    +def mean_abs_perc_error(
    +    schema_madlib, table_in, table_out, prediction_col, observed_col,
    +    grouping_cols=None):
    +    sql_st1 = """
    +        CREATE TABLE {table_out} AS
    +        SELECT
    +            SUM(ABS({prediction_col}- {observed_col})/{observed_col})
    +            /COUNT(*) AS mean_abs_perc_error """.format(**locals())
    +    sql_st2= ""
    +    sql_st3= """ FROM {table_in} """.format(**locals())
    +    sql_st4= ""
    +    if grouping_cols:
    +        sql_st2= """ , {grouping_cols} """.format(**locals())
    +        sql_st4= """ GROUP BY {grouping_cols}""".format(**locals())
    +    sql_st = sql_st1+sql_st2+sql_st3+sql_st4
    +    plpy.execute(sql_st)
    +
    +def mean_perc_error(
    +	schema_madlib, table_in, table_out, prediction_col, observed_col,
    +    grouping_cols=None):
    +    sql_st1 = """
    +        CREATE TABLE {table_out} AS
    +        SELECT
    +            SUM(({prediction_col}- {observed_col})/{observed_col})
    +            /COUNT(*) AS mean_perc_error """.format(**locals())
    +    sql_st2= ""
    +    sql_st3= """ FROM {table_in} """.format(**locals())
    +    sql_st4= ""
    +    if grouping_cols:
    +        sql_st2= """ , {grouping_cols} """.format(**locals())
    +        sql_st4= """ GROUP BY {grouping_cols}""".format(**locals())
    +    sql_st = sql_st1+sql_st2+sql_st3+sql_st4
    +    plpy.execute(sql_st)
    +
    +def mean_squared_error(
    +	schema_madlib, table_in, table_out, prediction_col, observed_col,
    +    grouping_cols=None):
    +    sql_st1 = """
    +        CREATE TABLE {table_out} AS
    +        SELECT
    +            SUM(({prediction_col}- {observed_col})^2)
    +            /COUNT(*) AS mean_squared_error """.format(**locals())
    +    sql_st2= ""
    +    sql_st3= """ FROM {table_in} """.format(**locals())
    +    sql_st4= ""
    +    if grouping_cols:
    +        sql_st2= """ , {grouping_cols} """.format(**locals())
    +        sql_st4= """ GROUP BY {grouping_cols}""".format(**locals())
    +    sql_st = sql_st1+sql_st2+sql_st3+sql_st4
    +    plpy.execute(sql_st)
    +
    +
    +def __r2_score(
    +	schema_madlib, table_in, table_out, prediction_col, observed_col):
    +
    +    sql_st = """
    +        SELECT
    +            SUM({observed_col})/COUNT(*) AS mean
    +        FROM {table_in}
    +        """.format(**locals())
    +    mean = plpy.execute(sql_st)[0]['mean']
    +
    +    sql_st = """
    +        SELECT
    +            SUM(({prediction_col}- {observed_col})^2) AS ssres
    +        FROM {table_in}
    +        """.format(**locals())
    +    ssres = plpy.execute(sql_st)[0]['ssres']
    +
    +    sql_st = """
    +        SELECT
    +            SUM(({observed_col}- {mean})^2) AS sstot
    +        FROM {table_in}
    +        """.format(**locals())
    +    sstot = plpy.execute(sql_st)[0]['sstot']
    +
    +    sql_st = """
    +        CREATE TABLE {table_out} AS
    +        SELECT 1-({ssres}/{sstot}) AS r2_score
    +        """.format(**locals())
    +    plpy.execute(sql_st)
    +
    +def r2_score(
    +	schema_madlib, table_in, table_out, prediction_col, observed_col,
    +    grouping_cols):
    +
    +    if grouping_cols is None:
    +        __r2_score(schema_madlib, table_in, table_out, prediction_col
    +            , observed_col)
    +        return
    +    ## temp table naming
    +    mean_tmp_table = "pg_temp." + unique_string() + "_mean"
    +    ssres_tmp_table = "pg_temp." + unique_string() + "_ssres"
    +    sstot_tmp_table = "pg_temp." + unique_string() + "_sstot"
    +    sql_st = """
    +        CREATE TABLE {mean_tmp_table} AS
    +        SELECT
    +            SUM({observed_col})/COUNT(*) AS mean, {grouping_cols}
    +        FROM {table_in}
    +        GROUP BY {grouping_cols}
    +        """.format(**locals())
    +    plpy.execute(sql_st)
    +
    +    sql_st = """
    +        CREATE TABLE {ssres_tmp_table} AS
    +        SELECT
    +            SUM(({prediction_col}- {observed_col})^2) AS ssres, {grouping_cols}
    +        FROM {table_in}
    +        GROUP BY {grouping_cols}
    +        """.format(**locals())
    +    plpy.execute(sql_st)
    +
    +    sql_st = """
    +        CREATE TABLE {sstot_tmp_table} AS
    +        SELECT
    +            SUM(({observed_col}- mean)^2) AS sstot, table_in.{grouping_cols}
    --- End diff --
    
    I'm pretty sure this will blow up if {grouping_cols} actually has more than one column, unless you're expected to define them as something like "column_1, table_in.column_2, table_in.column_3", which is obviously very ugly. It would be better to see if grouping_cols is a sequence and if it is do the necessary map().


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request: Prediction Metrics: New module

Posted by decibel <gi...@git.apache.org>.
Github user decibel commented on the pull request:

    https://github.com/apache/incubator-madlib/pull/41#issuecomment-216655743
  
    I suggest starting with documentation and hold off a bit on the code. 
    There might be some even better ways to do things that what I initially 
    thought of; I'm hoping that docs will help me figure that out.



---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request: Prediction Metrics: New module

Posted by decibel <gi...@git.apache.org>.
Github user decibel commented on a diff in the pull request:

    https://github.com/apache/incubator-madlib/pull/41#discussion_r61935728
  
    --- Diff: src/ports/postgres/modules/pred_metrics/pred_metrics.py_in ---
    @@ -0,0 +1,391 @@
    +# coding=utf-8
    +#
    +# Licensed to the Apache Software Foundation (ASF) under one
    +# or more contributor license agreements.  See the NOTICE file
    +# distributed with this work for additional information
    +# regarding copyright ownership.  The ASF licenses this file
    +# to you under the Apache License, Version 2.0 (the
    +# "License"); you may not use this file except in compliance
    +# with the License.  You may obtain a copy of the License at
    +#
    +#   http://www.apache.org/licenses/LICENSE-2.0
    +#
    +# Unless required by applicable law or agreed to in writing,
    +# software distributed under the License is distributed on an
    +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
    +# KIND, either express or implied.  See the License for the
    +# specific language governing permissions and limitations
    +# under the License.
    +
    +from utilities.utilities import unique_string
    +import plpy
    +
    +def mean_abs_error(
    +    schema_madlib, table_in, table_out, prediction_col, observed_col,
    +    grouping_cols=None):
    +    sql_st1 = """
    +        CREATE TABLE {table_out} AS
    +        SELECT
    +            SUM(ABS({prediction_col}- {observed_col}))
    +            /COUNT(*) AS mean_abs_error """.format(**locals())
    +    sql_st2= ""
    +    sql_st3= """ FROM {table_in} """.format(**locals())
    +    sql_st4= ""
    +    if grouping_cols:
    +        sql_st2= """ , {grouping_cols} """.format(**locals())
    --- End diff --
    
    FWIW, normal convention is to put grouping columns first in the select clause.


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request: Prediction Metrics: New module

Posted by decibel <gi...@git.apache.org>.
Github user decibel commented on the pull request:

    https://github.com/apache/incubator-madlib/pull/41#issuecomment-216642133
  
    I tried reviewing this patch, but I'm having great difficulty because I don't really know the details of what it's trying to accomplish. Maybe that's because I'm a database guy and not a statistician, but there's no documentation and very little in the way of comments.
    
    What would be very helpful to me would be docs/comments about what the module is trying to provide, as well as docstrings for any functions whose purpose is non-obvious. There also needs to be explanation about the handling of NULLs. If the normal aggregate behavior of ignoring NULLs isn't desired, I think something like avg(coalesce({observed_col}, 0)) is better than re-rolling the aggregate functions by hand.
    
    Making multiple independent queries against {table_in} will be very, very costly if that table is large. Additionally, creating lots of temp tables bloats the catalog. It's much better to write a single query that produces your output.
    
    Finally, please take a look at the built-in statistics aggregate functions (http://www.postgresql.org/docs/9.5/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE), and see if any of them will do what's necessary. If not then if I knew what the deficiencies were we could propose addressing them with the Postgres community.


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request: Prediction Metrics: New module

Posted by decibel <gi...@git.apache.org>.
Github user decibel commented on a diff in the pull request:

    https://github.com/apache/incubator-madlib/pull/41#discussion_r61940732
  
    --- Diff: src/ports/postgres/modules/pred_metrics/pred_metrics.py_in ---
    @@ -0,0 +1,391 @@
    +# coding=utf-8
    +#
    +# Licensed to the Apache Software Foundation (ASF) under one
    +# or more contributor license agreements.  See the NOTICE file
    +# distributed with this work for additional information
    +# regarding copyright ownership.  The ASF licenses this file
    +# to you under the Apache License, Version 2.0 (the
    +# "License"); you may not use this file except in compliance
    +# with the License.  You may obtain a copy of the License at
    +#
    +#   http://www.apache.org/licenses/LICENSE-2.0
    +#
    +# Unless required by applicable law or agreed to in writing,
    +# software distributed under the License is distributed on an
    +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
    +# KIND, either express or implied.  See the License for the
    +# specific language governing permissions and limitations
    +# under the License.
    +
    +from utilities.utilities import unique_string
    +import plpy
    +
    +def mean_abs_error(
    +    schema_madlib, table_in, table_out, prediction_col, observed_col,
    +    grouping_cols=None):
    +    sql_st1 = """
    +        CREATE TABLE {table_out} AS
    +        SELECT
    +            SUM(ABS({prediction_col}- {observed_col}))
    +            /COUNT(*) AS mean_abs_error """.format(**locals())
    +    sql_st2= ""
    +    sql_st3= """ FROM {table_in} """.format(**locals())
    +    sql_st4= ""
    +    if grouping_cols:
    +        sql_st2= """ , {grouping_cols} """.format(**locals())
    +        sql_st4= """ GROUP BY {grouping_cols}""".format(**locals())
    +    sql_st = sql_st1+sql_st2+sql_st3+sql_st4
    +    plpy.execute(sql_st)
    +
    +def mean_abs_perc_error(
    +    schema_madlib, table_in, table_out, prediction_col, observed_col,
    +    grouping_cols=None):
    +    sql_st1 = """
    +        CREATE TABLE {table_out} AS
    +        SELECT
    +            SUM(ABS({prediction_col}- {observed_col})/{observed_col})
    +            /COUNT(*) AS mean_abs_perc_error """.format(**locals())
    +    sql_st2= ""
    +    sql_st3= """ FROM {table_in} """.format(**locals())
    +    sql_st4= ""
    +    if grouping_cols:
    +        sql_st2= """ , {grouping_cols} """.format(**locals())
    +        sql_st4= """ GROUP BY {grouping_cols}""".format(**locals())
    +    sql_st = sql_st1+sql_st2+sql_st3+sql_st4
    +    plpy.execute(sql_st)
    +
    +def mean_perc_error(
    +	schema_madlib, table_in, table_out, prediction_col, observed_col,
    +    grouping_cols=None):
    +    sql_st1 = """
    +        CREATE TABLE {table_out} AS
    +        SELECT
    +            SUM(({prediction_col}- {observed_col})/{observed_col})
    +            /COUNT(*) AS mean_perc_error """.format(**locals())
    +    sql_st2= ""
    +    sql_st3= """ FROM {table_in} """.format(**locals())
    +    sql_st4= ""
    +    if grouping_cols:
    +        sql_st2= """ , {grouping_cols} """.format(**locals())
    +        sql_st4= """ GROUP BY {grouping_cols}""".format(**locals())
    +    sql_st = sql_st1+sql_st2+sql_st3+sql_st4
    +    plpy.execute(sql_st)
    +
    +def mean_squared_error(
    +	schema_madlib, table_in, table_out, prediction_col, observed_col,
    +    grouping_cols=None):
    +    sql_st1 = """
    +        CREATE TABLE {table_out} AS
    +        SELECT
    +            SUM(({prediction_col}- {observed_col})^2)
    +            /COUNT(*) AS mean_squared_error """.format(**locals())
    +    sql_st2= ""
    +    sql_st3= """ FROM {table_in} """.format(**locals())
    +    sql_st4= ""
    +    if grouping_cols:
    +        sql_st2= """ , {grouping_cols} """.format(**locals())
    +        sql_st4= """ GROUP BY {grouping_cols}""".format(**locals())
    +    sql_st = sql_st1+sql_st2+sql_st3+sql_st4
    +    plpy.execute(sql_st)
    +
    +
    +def __r2_score(
    --- End diff --
    
    This scans {table_in} 3 times for no good reason. It would be better to use the query below, but I'm not sure that's even necessary. Do any of the functions at http://www.postgresql.org/docs/9.5/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE do what's necessary here?
    
    CREATE TABLE {table_out} AS
    SELECT 1 - ssres/sstot AS r2_score FROM (
    SELECT sum(({prediction_col} - {observed_col})^2) AS ssres, sum(( {observed_col} - (SELECT SUM({observed_col})/count(*) AS mean FROM {table_in}) )^2) AS sstot FROM {table_in}
    ) intermediate


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request: Prediction Metrics: New module

Posted by orhankislal <gi...@git.apache.org>.
Github user orhankislal commented on the pull request:

    https://github.com/apache/incubator-madlib/pull/41#issuecomment-216656277
  
    The documentation is embedded in the sql file. I will add some comments to the python file as well.


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request: Prediction Metrics: New module

Posted by decibel <gi...@git.apache.org>.
Github user decibel commented on a diff in the pull request:

    https://github.com/apache/incubator-madlib/pull/41#discussion_r61935299
  
    --- Diff: src/ports/postgres/modules/pred_metrics/pred_metrics.py_in ---
    @@ -0,0 +1,391 @@
    +# coding=utf-8
    +#
    +# Licensed to the Apache Software Foundation (ASF) under one
    +# or more contributor license agreements.  See the NOTICE file
    +# distributed with this work for additional information
    +# regarding copyright ownership.  The ASF licenses this file
    +# to you under the Apache License, Version 2.0 (the
    +# "License"); you may not use this file except in compliance
    +# with the License.  You may obtain a copy of the License at
    +#
    +#   http://www.apache.org/licenses/LICENSE-2.0
    +#
    +# Unless required by applicable law or agreed to in writing,
    +# software distributed under the License is distributed on an
    +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
    +# KIND, either express or implied.  See the License for the
    +# specific language governing permissions and limitations
    +# under the License.
    +
    +from utilities.utilities import unique_string
    +import plpy
    +
    +def mean_abs_error(
    +    schema_madlib, table_in, table_out, prediction_col, observed_col,
    +    grouping_cols=None):
    +    sql_st1 = """
    +        CREATE TABLE {table_out} AS
    +        SELECT
    +            SUM(ABS({prediction_col}- {observed_col}))
    +            /COUNT(*) AS mean_abs_error """.format(**locals())
    --- End diff --
    
    Why not just use avg() instead of sum()/count()?


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request: Prediction Metrics: New module

Posted by decibel <gi...@git.apache.org>.
Github user decibel commented on the pull request:

    https://github.com/apache/incubator-madlib/pull/41#issuecomment-216661096
  
    Ahh, I didn't realize docs went with the SQL (I'm new around here...). 
    Is the generated output of the docs by chance available anywhere?
    
    It would be nice if there were SRF (set returning function) versions of 
    the functions that gave you the output directly, but maybe that's not 
    the MADlib way of doing things. If those functions existed then the 
    versions that created output tables would just be simple wrappers around 
    them. Ideally the SRFs would be plpgsql instead of plpython, as that 
    would eliminate data marshaling between plpython and Postgres. I'd 
    definitely solicit feedback from the community before coding that up 
    though, as it may not fit in with community standards.
    
    Personally, I think the unit test needs to actually verify that the 
    correct results are being generated, *especially* with data that 
    contains nulls.



---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---