You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@madlib.apache.org by njayaram2 <gi...@git.apache.org> on 2018/01/30 22:38:31 UTC
[GitHub] madlib pull request #230: Balanced sets final
GitHub user njayaram2 opened a pull request:
https://github.com/apache/madlib/pull/230
Balanced sets final
Refactor code, and add keep_null parameter.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/njayaram2/madlib balanced_sets_final
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/madlib/pull/230.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 #230
----
commit 87f6ffa4c9d1fcfafda5735adc7b76561dec6d9b
Author: Swatisoni <so...@...>
Date: 2018-01-10T20:07:36Z
Balance datasets : re-sampling technique
JIRA:MADLIB-1168
Additional Authors:
Orhan Kislal okislal@pivotal.io
Jingyi Mei jmei@pivotal.io
Balanced datasets Phase 1 and Phase 2 implementation which performs balanced sampling in following specified re-sampling techniques
1. Under-sampling the majority class(es), with- and without replacement
2. Over-sampling the minority class
3. Combining over- and under-sampling
- Uniform sampling of all classes (default case)
4. Create ensemble balanced sets
- Re-sampling given comma-delimited string of specific class and respective sample sizes
5. IC tests
Balanced sampling with grouping functionality will be implemented in phase 3
commit 40d1275504a107e7ae8809ab7f37f0aaa8ed0799
Author: Jingyi Mei <jm...@...>
Date: 2018-01-12T00:33:51Z
troubleshoot float issue
commit 01ade3c8dbc108237aec0866060f6ee5acaacaac
Author: Jingyi Mei <jm...@...>
Date: 2018-01-12T17:54:18Z
troubleshoot float issue 2
commit 276b3b8628488eaa281688e5115658cc8318abfa
Author: Jingyi Mei <jm...@...>
Date: 2018-01-22T19:25:07Z
Wip for refactor
commit f97e2742bb5a0150328798db064c3ab21c335def
Author: Rahul Iyer <ri...@...>
Date: 2018-01-23T01:13:16Z
Refactor sampling strategy and class counts
commit 445cbfe4d79c48c103b36f1d7bafdd171afb390b
Author: Nandish Jayaram <nj...@...>
Date: 2018-01-23T02:04:40Z
refactor WIP
commit a0db130061bf23e857ae85d602db7f6937c71c58
Author: Nandish Jayaram <nj...@...>
Date: 2018-01-23T20:50:40Z
update some code and add unit test cases for it
commit c15c5b537aaf233b6da906a988f8f7257fb9e83c
Author: Nandish Jayaram <nj...@...>
Date: 2018-01-23T23:31:08Z
handle all cases in _get_target_class_sizes
commit 1e4165eae46200109219920df276774c2b44ec29
Author: Rahul Iyer <ri...@...>
Date: 2018-01-24T01:26:59Z
Refactor get_target_sizes function
commit 36e4b75edc48ed3e7c7f64ad1944b0d12db191a4
Author: Nandish Jayaram <nj...@...>
Date: 2018-01-24T18:29:12Z
add comments, rename some variable, and undo changes to utilities.py_in from a previous commit
commit e643e6d8a70ad211152a6a41b64e6b35eee31f32
Author: Nandish Jayaram <nj...@...>
Date: 2018-01-24T23:26:06Z
done with creating strategy specific count dict, and subquery for no sample
commit b50a13330e3b7ceff71165f1385d7117f0f1a047
Author: Rahul Iyer <ri...@...>
Date: 2018-01-25T21:42:46Z
Add with_replacement subquery generation
commit b37a775af9e816cded3e3f35b8ada3bb8e9fbcf0
Author: Nandish Jayaram <nj...@...>
Date: 2018-01-26T00:29:23Z
most coding done???? yet to test.
commit 02113b98b670e0118d1766215f8d9619d951c2d3
Author: Nandish Jayaram <nj...@...>
Date: 2018-01-26T19:02:29Z
fix issues wip
commit 714db2a68afc325292748b3146afc07d81ab813e
Author: Rahul Iyer <ri...@...>
Date: 2018-01-26T20:26:23Z
Fix some errors to pass IC, update docs
commit 1dc6c0600b171a475b74e19fa5d39fbd070313ec
Author: Nandish Jayaram <nj...@...>
Date: 2018-01-27T01:27:46Z
replace Poisson based sampling with row_number()
commit d67a4f6e926aa1a5189716bb3f06ab53ef0d0cc4
Author: Nandish Jayaram <nj...@...>
Date: 2018-01-30T01:35:02Z
add new param for keep_null, code to handle that scenario, and some install check test cases to test it
commit cc8f6cd8816470c68df9beccc6141fa2fad4a62c
Author: Nandish Jayaram <nj...@...>
Date: 2018-01-30T20:00:39Z
Add more validations, test cases, and rename a function
commit f4d02c67a106dea902a5926fe2cc266ab9d44e0f
Author: Nandish Jayaram <nj...@...>
Date: 2018-01-30T22:35:30Z
reverting changes to stratified_sample.sql_in
----
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165737045
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
+ if strategy == UNDERSAMPLE:
+ chosen_strategy = undersample_level_dict
+ elif strategy == OVERSAMPLE:
+ chosen_strategy = oversample_level_dict
+ else:
+ chosen_strategy = nosample_level_dict
+ chosen_strategy[level] = count
+ return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
+# ------------------------------------------------------------------------------
+
+
+def _get_nosample_subquery(source_table, class_col, nosample_levels):
+ """ Return the subquery for fetching all rows as is from the input table
+ for specific class levels.
+ """
+ if not nosample_levels:
+ return ''
+ subquery = """
+ SELECT *
+ FROM {0}
+ WHERE {1} in ({2}) OR {1} IS NULL
+ """.format(source_table, class_col,
+ ','.join(["'{0}'".format(level)
+ for level in nosample_levels if level]))
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_without_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the subquery for sampling without replacement for specific
+ class levels.
+ """
+ if not desired_level_counts:
+ return ''
+ class_col_tmp = unique_string()
--- End diff --
can we pass the desp col to these unique_string calls ?
---
[GitHub] madlib issue #230: Balanced sets final
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit commented on the issue:
https://github.com/apache/madlib/pull/230
Refer to this link for build results (access rights to CI server needed):
https://builds.apache.org/job/madlib-pr-build/337/
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165516241
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
--- End diff --
Do we need to have `supported_strategies` as an argument, why not always use `[UNIFORM, UNDERSAMPLE, OVERSAMPLE]` ? Do we foresee any other sampling strategies ?
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by njayaram2 <gi...@git.apache.org>.
Github user njayaram2 commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r166165800
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
+ if strategy == UNDERSAMPLE:
+ chosen_strategy = undersample_level_dict
+ elif strategy == OVERSAMPLE:
+ chosen_strategy = oversample_level_dict
+ else:
+ chosen_strategy = nosample_level_dict
+ chosen_strategy[level] = count
+ return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
+# ------------------------------------------------------------------------------
+
+
+def _get_nosample_subquery(source_table, class_col, nosample_levels):
+ """ Return the subquery for fetching all rows as is from the input table
+ for specific class levels.
+ """
+ if not nosample_levels:
+ return ''
+ subquery = """
+ SELECT *
+ FROM {0}
+ WHERE {1} in ({2}) OR {1} IS NULL
+ """.format(source_table, class_col,
+ ','.join(["'{0}'".format(level)
+ for level in nosample_levels if level]))
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_without_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the subquery for sampling without replacement for specific
+ class levels.
+ """
+ if not desired_level_counts:
+ return ''
+ class_col_tmp = unique_string()
+ row_number_col = unique_string()
+ desired_count_col = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_level_counts_str = "VALUES " + \
+ ','.join("({0}, {1})".
+ format("'{0}'::text".format(k) if k else null_value_string, v)
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT {source_table_columns},
+ row_number() OVER (PARTITION BY {class_col} ORDER BY random()) AS {row_number_col},
+ {desired_count_col}
+ FROM
+ (
+ SELECT {source_table_columns},
+ {desired_count_col}
+ FROM
+ {source_table} s,
+ ({desired_level_counts_str})
+ q({class_col_tmp}, {desired_count_col})
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}')
+ ) q2
+ ) q3
+ WHERE {row_number_col} <= {desired_count_col}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_with_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the query for sampling with replacement for specific class
+ levels (always used for oversampling, and used for undersampling if
+ with_replacement flag is set to TRUE).
+ """
+ if not desired_level_counts:
+ return ''
+
+ class_col_tmp = unique_string()
+ desired_count_col = unique_string()
+ actual_count_col = unique_string()
+ q1_row_no = unique_string()
+ q2_row_no = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_and_actual_level_counts = "VALUES " + \
+ ','.join("({0}, {1}, {2})".
+ format("'{0}'::text".format(k) if k else null_value_string,
+ v, actual_level_counts[k])
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT
+ {class_col_tmp},
+ generate_series(1, {desired_count_col}::int) AS _i,
+ ((random()*({actual_count_col}-1)+1)::int) AS {q1_row_no}
+ FROM
+ ({desired_and_actual_level_counts})
+ q({class_col_tmp}, {desired_count_col}, {actual_count_col})
+ ) q1,
+ (
+ SELECT
+ *,
+ row_number() OVER(PARTITION BY {class_col}) AS {q2_row_no}
+ FROM
+ {source_table}
+ ) q2
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}') AND
+ q1.{q1_row_no} = q2.{q2_row_no}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+def balance_sample(schema_madlib, source_table, output_table, class_col,
+ class_sizes, output_table_size, grouping_cols,
+ with_replacement, keep_null, **kwargs):
+ """
+ Balance sampling function
+ Args:
+ @param source_table Input table name.
+ @param output_table Output table name.
+ @param class_col Name of the column containing the class to be
+ balanced.
+ @param class_sizes Parameter to define the size of the different
--- End diff --
Discussions on changing param names should probably move to the JIRA, since the interface was kind of decided there. Other folks can comment on it too.
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165526039
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
+ if strategy == UNDERSAMPLE:
+ chosen_strategy = undersample_level_dict
+ elif strategy == OVERSAMPLE:
+ chosen_strategy = oversample_level_dict
+ else:
+ chosen_strategy = nosample_level_dict
+ chosen_strategy[level] = count
+ return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
+# ------------------------------------------------------------------------------
+
+
+def _get_nosample_subquery(source_table, class_col, nosample_levels):
+ """ Return the subquery for fetching all rows as is from the input table
+ for specific class levels.
+ """
+ if not nosample_levels:
+ return ''
+ subquery = """
+ SELECT *
+ FROM {0}
+ WHERE {1} in ({2}) OR {1} IS NULL
+ """.format(source_table, class_col,
+ ','.join(["'{0}'".format(level)
+ for level in nosample_levels if level]))
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_without_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the subquery for sampling without replacement for specific
+ class levels.
+ """
+ if not desired_level_counts:
+ return ''
+ class_col_tmp = unique_string()
+ row_number_col = unique_string()
+ desired_count_col = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_level_counts_str = "VALUES " + \
+ ','.join("({0}, {1})".
+ format("'{0}'::text".format(k) if k else null_value_string, v)
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT {source_table_columns},
+ row_number() OVER (PARTITION BY {class_col} ORDER BY random()) AS {row_number_col},
+ {desired_count_col}
+ FROM
+ (
+ SELECT {source_table_columns},
+ {desired_count_col}
+ FROM
+ {source_table} s,
+ ({desired_level_counts_str})
+ q({class_col_tmp}, {desired_count_col})
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}')
+ ) q2
+ ) q3
+ WHERE {row_number_col} <= {desired_count_col}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_with_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the query for sampling with replacement for specific class
+ levels (always used for oversampling, and used for undersampling if
+ with_replacement flag is set to TRUE).
+ """
+ if not desired_level_counts:
+ return ''
+
+ class_col_tmp = unique_string()
+ desired_count_col = unique_string()
+ actual_count_col = unique_string()
+ q1_row_no = unique_string()
+ q2_row_no = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_and_actual_level_counts = "VALUES " + \
+ ','.join("({0}, {1}, {2})".
+ format("'{0}'::text".format(k) if k else null_value_string,
+ v, actual_level_counts[k])
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT
+ {class_col_tmp},
+ generate_series(1, {desired_count_col}::int) AS _i,
+ ((random()*({actual_count_col}-1)+1)::int) AS {q1_row_no}
+ FROM
+ ({desired_and_actual_level_counts})
+ q({class_col_tmp}, {desired_count_col}, {actual_count_col})
+ ) q1,
+ (
+ SELECT
+ *,
+ row_number() OVER(PARTITION BY {class_col}) AS {q2_row_no}
+ FROM
+ {source_table}
+ ) q2
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}') AND
+ q1.{q1_row_no} = q2.{q2_row_no}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+def balance_sample(schema_madlib, source_table, output_table, class_col,
+ class_sizes, output_table_size, grouping_cols,
+ with_replacement, keep_null, **kwargs):
+ """
+ Balance sampling function
+ Args:
+ @param source_table Input table name.
+ @param output_table Output table name.
+ @param class_col Name of the column containing the class to be
+ balanced.
+ @param class_sizes Parameter to define the size of the different
+ class values.
+ @param output_table_size Desired size of the output data set.
+ @param grouping_cols The columns that define the grouping.
+ @param with_replacement The sampling method.
+ @param keep_null Flag to include rows with class level values
+ NULL. Default is False.
+
+ """
+ with MinWarning("warning"):
+
+ desired_sample_per_class = unique_string(desp='desired_sample_per_class')
+ desired_counts = unique_string(desp='desired_counts')
+
+ # set all default values
+ if not class_sizes:
+ class_sizes = UNIFORM
+ if not with_replacement:
+ with_replacement = False
+ keep_null = False if not keep_null else True
+ if class_sizes:
+ class_sizes = class_sizes.strip()
+
+ _validate_strs(source_table, output_table, class_col,
+ output_table_size, grouping_cols)
+ source_table_columns = ','.join(get_cols(source_table))
+
+ new_source_table = source_table
+ # If keep_null=False, create a view of the input table ignoring NULL
+ # values for class levels.
+ if not keep_null:
+ new_source_table = unique_string(desp='source_table')
+ plpy.execute("""
+ CREATE VIEW {new_source_table} AS
+ SELECT * FROM {source_table}
+ WHERE {class_col} IS NOT NULL
+ """.format(**locals()))
+ actual_level_counts = _get_frequency_distribution(new_source_table,
+ class_col)
+ # class_sizes can be of two forms:
+ # 1. A string describing sampling strategy (as described in
+ # _validate_and_get_sampling_strategy).
+ # In this case, 'sampling_strategy_str' is set to one of
+ # [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ # 2. Class sizes for all (or a subset) of the class levels
+ # In this case, sampling_strategy_str = None and parsed_class_sizes
+ # is used for the sampling.
+ parsed_class_sizes = extract_keyvalue_params(class_sizes,
+ allow_duplicates=False,
+ lower_case_names=False)
+ if not parsed_class_sizes:
+ sampling_strategy_str = _validate_and_get_sampling_strategy(class_sizes,
+ output_table_size)
+ else:
+ sampling_strategy_str = None
+ try:
+ all_levels = actual_level_counts.keys()
+ for each_level, each_class_size in parsed_class_sizes.items():
+ _assert(each_level in all_levels,
+ "Sample: Invalid class value specified ({0})".
+ format(each_level))
+ each_class_size = int(each_class_size)
--- End diff --
Haven't looked carefully into the implementation of `extract_keyvalue_params` but can't we use the `input_param_types` argument of `extract_keyvalue_params` to achieve this ?
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165503656
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
+ if strategy == UNDERSAMPLE:
+ chosen_strategy = undersample_level_dict
+ elif strategy == OVERSAMPLE:
+ chosen_strategy = oversample_level_dict
+ else:
+ chosen_strategy = nosample_level_dict
+ chosen_strategy[level] = count
+ return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
+# ------------------------------------------------------------------------------
+
+
+def _get_nosample_subquery(source_table, class_col, nosample_levels):
+ """ Return the subquery for fetching all rows as is from the input table
+ for specific class levels.
+ """
+ if not nosample_levels:
+ return ''
+ subquery = """
+ SELECT *
+ FROM {0}
+ WHERE {1} in ({2}) OR {1} IS NULL
+ """.format(source_table, class_col,
+ ','.join(["'{0}'".format(level)
+ for level in nosample_levels if level]))
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_without_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the subquery for sampling without replacement for specific
+ class levels.
+ """
+ if not desired_level_counts:
+ return ''
+ class_col_tmp = unique_string()
+ row_number_col = unique_string()
+ desired_count_col = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_level_counts_str = "VALUES " + \
+ ','.join("({0}, {1})".
+ format("'{0}'::text".format(k) if k else null_value_string, v)
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT {source_table_columns},
+ row_number() OVER (PARTITION BY {class_col} ORDER BY random()) AS {row_number_col},
+ {desired_count_col}
+ FROM
+ (
+ SELECT {source_table_columns},
+ {desired_count_col}
+ FROM
+ {source_table} s,
+ ({desired_level_counts_str})
+ q({class_col_tmp}, {desired_count_col})
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}')
+ ) q2
+ ) q3
+ WHERE {row_number_col} <= {desired_count_col}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_with_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the query for sampling with replacement for specific class
+ levels (always used for oversampling, and used for undersampling if
+ with_replacement flag is set to TRUE).
+ """
+ if not desired_level_counts:
+ return ''
+
+ class_col_tmp = unique_string()
+ desired_count_col = unique_string()
+ actual_count_col = unique_string()
+ q1_row_no = unique_string()
+ q2_row_no = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_and_actual_level_counts = "VALUES " + \
+ ','.join("({0}, {1}, {2})".
+ format("'{0}'::text".format(k) if k else null_value_string,
+ v, actual_level_counts[k])
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT
+ {class_col_tmp},
+ generate_series(1, {desired_count_col}::int) AS _i,
+ ((random()*({actual_count_col}-1)+1)::int) AS {q1_row_no}
+ FROM
+ ({desired_and_actual_level_counts})
+ q({class_col_tmp}, {desired_count_col}, {actual_count_col})
+ ) q1,
+ (
+ SELECT
+ *,
+ row_number() OVER(PARTITION BY {class_col}) AS {q2_row_no}
+ FROM
+ {source_table}
+ ) q2
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}') AND
+ q1.{q1_row_no} = q2.{q2_row_no}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+def balance_sample(schema_madlib, source_table, output_table, class_col,
+ class_sizes, output_table_size, grouping_cols,
+ with_replacement, keep_null, **kwargs):
+ """
+ Balance sampling function
+ Args:
+ @param source_table Input table name.
+ @param output_table Output table name.
+ @param class_col Name of the column containing the class to be
+ balanced.
+ @param class_sizes Parameter to define the size of the different
+ class values.
+ @param output_table_size Desired size of the output data set.
+ @param grouping_cols The columns that define the grouping.
+ @param with_replacement The sampling method.
+ @param keep_null Flag to include rows with class level values
--- End diff --
should we call this `ignore_null` instead of `keep_null`since we always use the negation of this variable.
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by njayaram2 <gi...@git.apache.org>.
Github user njayaram2 commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r166056096
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
--- End diff --
There is precedence for supporting prefix for parameter values, in modules such as SVM.
Yes, the error messages could be similar.
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165537394
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
+ if strategy == UNDERSAMPLE:
+ chosen_strategy = undersample_level_dict
+ elif strategy == OVERSAMPLE:
+ chosen_strategy = oversample_level_dict
+ else:
+ chosen_strategy = nosample_level_dict
+ chosen_strategy[level] = count
+ return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
+# ------------------------------------------------------------------------------
+
+
+def _get_nosample_subquery(source_table, class_col, nosample_levels):
+ """ Return the subquery for fetching all rows as is from the input table
+ for specific class levels.
+ """
+ if not nosample_levels:
+ return ''
+ subquery = """
+ SELECT *
+ FROM {0}
+ WHERE {1} in ({2}) OR {1} IS NULL
+ """.format(source_table, class_col,
+ ','.join(["'{0}'".format(level)
+ for level in nosample_levels if level]))
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_without_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the subquery for sampling without replacement for specific
+ class levels.
+ """
+ if not desired_level_counts:
+ return ''
+ class_col_tmp = unique_string()
+ row_number_col = unique_string()
+ desired_count_col = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_level_counts_str = "VALUES " + \
+ ','.join("({0}, {1})".
+ format("'{0}'::text".format(k) if k else null_value_string, v)
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT {source_table_columns},
+ row_number() OVER (PARTITION BY {class_col} ORDER BY random()) AS {row_number_col},
+ {desired_count_col}
+ FROM
+ (
+ SELECT {source_table_columns},
+ {desired_count_col}
+ FROM
+ {source_table} s,
+ ({desired_level_counts_str})
+ q({class_col_tmp}, {desired_count_col})
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}')
+ ) q2
+ ) q3
+ WHERE {row_number_col} <= {desired_count_col}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_with_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the query for sampling with replacement for specific class
+ levels (always used for oversampling, and used for undersampling if
+ with_replacement flag is set to TRUE).
+ """
+ if not desired_level_counts:
+ return ''
+
+ class_col_tmp = unique_string()
+ desired_count_col = unique_string()
+ actual_count_col = unique_string()
+ q1_row_no = unique_string()
+ q2_row_no = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_and_actual_level_counts = "VALUES " + \
+ ','.join("({0}, {1}, {2})".
+ format("'{0}'::text".format(k) if k else null_value_string,
+ v, actual_level_counts[k])
+ for k, v in desired_level_counts.items())
+ subquery = """
--- End diff --
can we add more comments explaining the building blocks of this query, mostly q1 ?
---
[GitHub] madlib issue #230: Balanced sets final
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit commented on the issue:
https://github.com/apache/madlib/pull/230
Refer to this link for build results (access rights to CI server needed):
https://builds.apache.org/job/madlib-pr-build/338/
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165511625
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
--- End diff --
Minor suggestion: should we add that `None` can also be a key in this dictionary
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/madlib/pull/230
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165527448
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
--- End diff --
We can reword this as something like`If the previous arg sampling_strategy_str is None, this dict contains the class levels and the corresponding number of rows specified by the user`
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165736819
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
+ if strategy == UNDERSAMPLE:
+ chosen_strategy = undersample_level_dict
+ elif strategy == OVERSAMPLE:
+ chosen_strategy = oversample_level_dict
+ else:
+ chosen_strategy = nosample_level_dict
+ chosen_strategy[level] = count
+ return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
+# ------------------------------------------------------------------------------
+
+
+def _get_nosample_subquery(source_table, class_col, nosample_levels):
+ """ Return the subquery for fetching all rows as is from the input table
+ for specific class levels.
+ """
+ if not nosample_levels:
+ return ''
+ subquery = """
+ SELECT *
+ FROM {0}
+ WHERE {1} in ({2}) OR {1} IS NULL
+ """.format(source_table, class_col,
+ ','.join(["'{0}'".format(level)
+ for level in nosample_levels if level]))
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_without_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the subquery for sampling without replacement for specific
+ class levels.
+ """
+ if not desired_level_counts:
+ return ''
+ class_col_tmp = unique_string()
+ row_number_col = unique_string()
+ desired_count_col = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_level_counts_str = "VALUES " + \
+ ','.join("({0}, {1})".
+ format("'{0}'::text".format(k) if k else null_value_string, v)
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT {source_table_columns},
+ row_number() OVER (PARTITION BY {class_col} ORDER BY random()) AS {row_number_col},
+ {desired_count_col}
+ FROM
+ (
+ SELECT {source_table_columns},
+ {desired_count_col}
+ FROM
+ {source_table} s,
+ ({desired_level_counts_str})
+ q({class_col_tmp}, {desired_count_col})
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}')
+ ) q2
+ ) q3
+ WHERE {row_number_col} <= {desired_count_col}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_with_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the query for sampling with replacement for specific class
--- End diff --
maybe reword this as
"""
Return the query for sampling with replacement for specific class levels. Always used for oversampling since oversampling will always need to use replacement. Used for under sampling only if with_replacement flag is set to TRUE.
"""
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165520599
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
--- End diff --
Why do we want the user to specify a prefix of the substring ? Isn't it more clear if the user choices are limited to UNIFORM, UNDERSAMPLE or OVERSAMPLE ?
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165522340
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
+ if strategy == UNDERSAMPLE:
+ chosen_strategy = undersample_level_dict
+ elif strategy == OVERSAMPLE:
+ chosen_strategy = oversample_level_dict
+ else:
+ chosen_strategy = nosample_level_dict
+ chosen_strategy[level] = count
+ return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
+# ------------------------------------------------------------------------------
+
+
+def _get_nosample_subquery(source_table, class_col, nosample_levels):
+ """ Return the subquery for fetching all rows as is from the input table
+ for specific class levels.
+ """
+ if not nosample_levels:
+ return ''
+ subquery = """
+ SELECT *
+ FROM {0}
+ WHERE {1} in ({2}) OR {1} IS NULL
+ """.format(source_table, class_col,
+ ','.join(["'{0}'".format(level)
+ for level in nosample_levels if level]))
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_without_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the subquery for sampling without replacement for specific
+ class levels.
+ """
+ if not desired_level_counts:
+ return ''
+ class_col_tmp = unique_string()
+ row_number_col = unique_string()
+ desired_count_col = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_level_counts_str = "VALUES " + \
+ ','.join("({0}, {1})".
+ format("'{0}'::text".format(k) if k else null_value_string, v)
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT {source_table_columns},
+ row_number() OVER (PARTITION BY {class_col} ORDER BY random()) AS {row_number_col},
+ {desired_count_col}
+ FROM
+ (
+ SELECT {source_table_columns},
+ {desired_count_col}
+ FROM
+ {source_table} s,
+ ({desired_level_counts_str})
+ q({class_col_tmp}, {desired_count_col})
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}')
+ ) q2
+ ) q3
+ WHERE {row_number_col} <= {desired_count_col}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_with_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the query for sampling with replacement for specific class
+ levels (always used for oversampling, and used for undersampling if
+ with_replacement flag is set to TRUE).
+ """
+ if not desired_level_counts:
+ return ''
+
+ class_col_tmp = unique_string()
+ desired_count_col = unique_string()
+ actual_count_col = unique_string()
+ q1_row_no = unique_string()
+ q2_row_no = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_and_actual_level_counts = "VALUES " + \
+ ','.join("({0}, {1}, {2})".
+ format("'{0}'::text".format(k) if k else null_value_string,
+ v, actual_level_counts[k])
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT
+ {class_col_tmp},
+ generate_series(1, {desired_count_col}::int) AS _i,
+ ((random()*({actual_count_col}-1)+1)::int) AS {q1_row_no}
+ FROM
+ ({desired_and_actual_level_counts})
+ q({class_col_tmp}, {desired_count_col}, {actual_count_col})
+ ) q1,
+ (
+ SELECT
+ *,
+ row_number() OVER(PARTITION BY {class_col}) AS {q2_row_no}
+ FROM
+ {source_table}
+ ) q2
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}') AND
+ q1.{q1_row_no} = q2.{q2_row_no}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+def balance_sample(schema_madlib, source_table, output_table, class_col,
+ class_sizes, output_table_size, grouping_cols,
+ with_replacement, keep_null, **kwargs):
+ """
+ Balance sampling function
+ Args:
+ @param source_table Input table name.
+ @param output_table Output table name.
+ @param class_col Name of the column containing the class to be
+ balanced.
+ @param class_sizes Parameter to define the size of the different
+ class values.
+ @param output_table_size Desired size of the output data set.
+ @param grouping_cols The columns that define the grouping.
+ @param with_replacement The sampling method.
+ @param keep_null Flag to include rows with class level values
+ NULL. Default is False.
+
+ """
+ with MinWarning("warning"):
+
+ desired_sample_per_class = unique_string(desp='desired_sample_per_class')
+ desired_counts = unique_string(desp='desired_counts')
+
+ # set all default values
+ if not class_sizes:
+ class_sizes = UNIFORM
+ if not with_replacement:
+ with_replacement = False
+ keep_null = False if not keep_null else True
+ if class_sizes:
+ class_sizes = class_sizes.strip()
+
+ _validate_strs(source_table, output_table, class_col,
+ output_table_size, grouping_cols)
+ source_table_columns = ','.join(get_cols(source_table))
+
+ new_source_table = source_table
+ # If keep_null=False, create a view of the input table ignoring NULL
+ # values for class levels.
+ if not keep_null:
+ new_source_table = unique_string(desp='source_table')
+ plpy.execute("""
+ CREATE VIEW {new_source_table} AS
+ SELECT * FROM {source_table}
+ WHERE {class_col} IS NOT NULL
+ """.format(**locals()))
+ actual_level_counts = _get_frequency_distribution(new_source_table,
--- End diff --
Not really a big deal but the name `_get_frequency_distribution` seems too generic, maybe we should make it more specific ?
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165530126
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
+ if strategy == UNDERSAMPLE:
+ chosen_strategy = undersample_level_dict
+ elif strategy == OVERSAMPLE:
+ chosen_strategy = oversample_level_dict
+ else:
+ chosen_strategy = nosample_level_dict
+ chosen_strategy[level] = count
+ return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
+# ------------------------------------------------------------------------------
+
+
+def _get_nosample_subquery(source_table, class_col, nosample_levels):
+ """ Return the subquery for fetching all rows as is from the input table
+ for specific class levels.
+ """
+ if not nosample_levels:
+ return ''
+ subquery = """
+ SELECT *
+ FROM {0}
+ WHERE {1} in ({2}) OR {1} IS NULL
+ """.format(source_table, class_col,
+ ','.join(["'{0}'".format(level)
+ for level in nosample_levels if level]))
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_without_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the subquery for sampling without replacement for specific
+ class levels.
+ """
+ if not desired_level_counts:
+ return ''
+ class_col_tmp = unique_string()
+ row_number_col = unique_string()
+ desired_count_col = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_level_counts_str = "VALUES " + \
+ ','.join("({0}, {1})".
+ format("'{0}'::text".format(k) if k else null_value_string, v)
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT {source_table_columns},
+ row_number() OVER (PARTITION BY {class_col} ORDER BY random()) AS {row_number_col},
+ {desired_count_col}
+ FROM
+ (
+ SELECT {source_table_columns},
+ {desired_count_col}
+ FROM
+ {source_table} s,
+ ({desired_level_counts_str})
+ q({class_col_tmp}, {desired_count_col})
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}')
+ ) q2
+ ) q3
+ WHERE {row_number_col} <= {desired_count_col}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_with_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the query for sampling with replacement for specific class
+ levels (always used for oversampling, and used for undersampling if
+ with_replacement flag is set to TRUE).
+ """
+ if not desired_level_counts:
+ return ''
+
+ class_col_tmp = unique_string()
+ desired_count_col = unique_string()
+ actual_count_col = unique_string()
+ q1_row_no = unique_string()
+ q2_row_no = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_and_actual_level_counts = "VALUES " + \
+ ','.join("({0}, {1}, {2})".
+ format("'{0}'::text".format(k) if k else null_value_string,
+ v, actual_level_counts[k])
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT
+ {class_col_tmp},
+ generate_series(1, {desired_count_col}::int) AS _i,
+ ((random()*({actual_count_col}-1)+1)::int) AS {q1_row_no}
+ FROM
+ ({desired_and_actual_level_counts})
+ q({class_col_tmp}, {desired_count_col}, {actual_count_col})
+ ) q1,
+ (
+ SELECT
+ *,
+ row_number() OVER(PARTITION BY {class_col}) AS {q2_row_no}
+ FROM
+ {source_table}
+ ) q2
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}') AND
+ q1.{q1_row_no} = q2.{q2_row_no}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+def balance_sample(schema_madlib, source_table, output_table, class_col,
+ class_sizes, output_table_size, grouping_cols,
+ with_replacement, keep_null, **kwargs):
+ """
+ Balance sampling function
+ Args:
+ @param source_table Input table name.
+ @param output_table Output table name.
+ @param class_col Name of the column containing the class to be
+ balanced.
+ @param class_sizes Parameter to define the size of the different
+ class values.
+ @param output_table_size Desired size of the output data set.
+ @param grouping_cols The columns that define the grouping.
+ @param with_replacement The sampling method.
+ @param keep_null Flag to include rows with class level values
+ NULL. Default is False.
+
+ """
+ with MinWarning("warning"):
+
+ desired_sample_per_class = unique_string(desp='desired_sample_per_class')
+ desired_counts = unique_string(desp='desired_counts')
+
+ # set all default values
+ if not class_sizes:
+ class_sizes = UNIFORM
+ if not with_replacement:
+ with_replacement = False
+ keep_null = False if not keep_null else True
+ if class_sizes:
+ class_sizes = class_sizes.strip()
+
+ _validate_strs(source_table, output_table, class_col,
+ output_table_size, grouping_cols)
+ source_table_columns = ','.join(get_cols(source_table))
+
+ new_source_table = source_table
+ # If keep_null=False, create a view of the input table ignoring NULL
+ # values for class levels.
+ if not keep_null:
+ new_source_table = unique_string(desp='source_table')
+ plpy.execute("""
+ CREATE VIEW {new_source_table} AS
+ SELECT * FROM {source_table}
+ WHERE {class_col} IS NOT NULL
+ """.format(**locals()))
+ actual_level_counts = _get_frequency_distribution(new_source_table,
+ class_col)
+ # class_sizes can be of two forms:
+ # 1. A string describing sampling strategy (as described in
+ # _validate_and_get_sampling_strategy).
+ # In this case, 'sampling_strategy_str' is set to one of
+ # [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ # 2. Class sizes for all (or a subset) of the class levels
+ # In this case, sampling_strategy_str = None and parsed_class_sizes
+ # is used for the sampling.
+ parsed_class_sizes = extract_keyvalue_params(class_sizes,
+ allow_duplicates=False,
+ lower_case_names=False)
+ if not parsed_class_sizes:
+ sampling_strategy_str = _validate_and_get_sampling_strategy(class_sizes,
+ output_table_size)
+ else:
+ sampling_strategy_str = None
+ try:
+ all_levels = actual_level_counts.keys()
+ for each_level, each_class_size in parsed_class_sizes.items():
+ _assert(each_level in all_levels,
+ "Sample: Invalid class value specified ({0})".
+ format(each_level))
+ each_class_size = int(each_class_size)
+ _assert(each_class_size >= 1,
+ "Sample: Class size has to be greater than zero")
+ parsed_class_sizes[each_level] = each_class_size
+
+ except TypeError:
+ plpy.error("Sample: Invalid value for class_sizes ({0})".
+ format(class_sizes))
+
+ # Get the number of rows to be sampled for each class level, based on
+ # the input table, class_sizes, and output_table_size params. This also
+ # includes info about the resulting sampling strategy, i.e., one of
+ # UNDERSAMPLE, OVERSAMPLE, or NOSAMPLE for each level.
+ target_class_sizes = _get_target_level_counts(sampling_strategy_str,
+ parsed_class_sizes,
+ actual_level_counts,
+ output_table_size)
+
+ undersample_level_dict, oversample_level_dict, nosample_level_dict = \
+ _get_sampling_strategy_specific_dict(target_class_sizes)
+
+ # Get subqueries for each sampling strategy, so that they can be used
+ # together in one big query.
+ nosample_subquery = _get_nosample_subquery(
+ new_source_table, class_col, nosample_level_dict.keys())
+ oversample_subquery = _get_with_replacement_subquery(
+ schema_madlib, new_source_table, source_table_columns, class_col,
+ actual_level_counts, oversample_level_dict)
+ if with_replacement:
+ undersample_subquery = _get_with_replacement_subquery(
+ schema_madlib, new_source_table, source_table_columns, class_col,
+ actual_level_counts, undersample_level_dict)
+ else:
+ undersample_subquery = _get_without_replacement_subquery(
+ schema_madlib, new_source_table, source_table_columns, class_col,
+ actual_level_counts, undersample_level_dict)
+
+ # Merge the three subqueries using a UNION ALL clause.
+ union_all_subquery = ' UNION ALL '.join(
+ ['({0})'.format(subquery)
+ for subquery in [undersample_subquery, oversample_subquery, nosample_subquery]
+ if subquery])
+
+ final_query = """
+ CREATE TABLE {output_table} AS
+ SELECT row_number() OVER() AS {new_col_name}, *
+ FROM (
+ {union_all_subquery}
+ ) union_query
+ """.format(new_col_name=NEW_ID_COLUMN, **locals())
+ plpy.execute(final_query)
+ if not keep_null:
+ plpy.execute("DROP VIEW {0}".format(new_source_table))
+
+
+def _validate_strs(source_table, output_table, class_col, output_table_size,
+ grouping_cols):
+ _assert(source_table and table_exists(source_table),
+ "Sample: Source table ({source_table}) does not exist.".format(**locals()))
+ _assert(not table_is_empty(source_table),
+ "Sample: Source table ({source_table}) is empty.".format(**locals()))
+
+ _assert(output_table,
+ "Sample: Output table name is missing.".format(**locals()))
+ _assert(not table_exists(output_table),
+ "Sample: Output table ({output_table}) already exists.".format(**locals()))
+
+ _assert(class_col,
+ "Sample: Class column name is missing.".format(**locals()))
+ _assert(columns_exist_in_table(source_table, [class_col]),
+ ("""Sample: Class column ({class_col}) does not exist in""" +
+ """ table ({source_table}).""").format(**locals()))
+
+ _assert(not columns_exist_in_table(source_table, [NEW_ID_COLUMN]),
+ ("""Sample: Please ensure the source table ({0})""" +
+ """ does not contain a column named {1}""").format(source_table, NEW_ID_COLUMN))
+
+ _assert((not output_table_size) or (output_table_size > 0),
+ "Sample: Invalid output table size ({output_table_size}).".format(
+ **locals()))
+
+ _assert(grouping_cols is None,
+ "grouping_cols is not supported at the moment."
+ .format(**locals()))
+
+
+def balance_sample_help(schema_madlib, message, **kwargs):
+ """
+ Help function for balance_sample
+
+ Args:
+ @param schema_madlib
+ @param message: string, Help message string
+ @param kwargs
+
+ Returns:
+ String. Help/usage information
+ """
+ if not message:
+ help_string = """
+-----------------------------------------------------------------------
+ SUMMARY
+-----------------------------------------------------------------------
+Given a table with varying set of records for each class label,
+this function will create an output table with a varying types (by
+default: uniform) of sampling distributions of each class label. It is
+possible to use with or without replacement sampling methods, specify
+different proportions of each class, multiple grouping columns and/or
+output table size.
+
+For more details on function usage:
+ SELECT {schema_madlib}.balance_sample('usage');
+ SELECT {schema_madlib}.balance_sample('example');
+ """
+ elif message.lower() in ['usage', 'help', '?']:
+ help_string = """
+
+Given a table, stratified sampling returns a proportion of records for
+each group (strata). It is possible to use with or without replacement
+sampling methods, specify a set of target columns, and assume the
+whole table is a single strata.
+
+----------------------------------------------------------------------------
+ USAGE
+----------------------------------------------------------------------------
+
+ SELECT {schema_madlib}.balance_sample(
+ source_table TEXT, -- Input table name.
+ output_table TEXT, -- Output table name.
+ class_col TEXT, -- Name of column containing the class to be
+ -- balanced.
+ class_size TEXT, -- (Default: NULL) Parameter to define the size
+ -- of the different class values.
+ output_table_size INTEGER, -- (Default: NULL) Desired size of the output
+ -- data set.
+ grouping_cols TEXT, -- (Default: NULL) The columns columns that
+ -- defines the grouping.
+ with_replacement BOOLEAN -- (Default: FALSE) The sampling method.
+ keep_null BOOLEAN -- (Default: FALSE) Consider class levels with
+ NULL values or not.
+
+If class_size is NULL, the source table is uniformly sampled.
+
+If output_table_size is NULL, the resulting output table size will depend on
+the settings for the ‘class_size’ parameter. It is ignored if ‘class_size’
+parameter is set to either ‘oversample’ or ‘undersample’.
+
+If grouping_cols is NULL, the whole table is treated as a single group and
+sampled accordingly.
+
+If with_replacement is TRUE, each sample is independent (the same row may
+be selected in the sample set more than once). Else (if with_replacement
+is FALSE), a row can be selected at most once.
+);
+
+The output_table would contain the required number of samples, along with a
+new column named __madlib_id__, that contain unique numbers for all
+sampled rows.
+"""
+ elif message.lower() in ("example", "examples"):
+ help_string = """
+----------------------------------------------------------------------------
+ EXAMPLES
+----------------------------------------------------------------------------
+
+-- Create an input table
+DROP TABLE IF EXISTS test;
+
+CREATE TABLE test(
+ id1 INTEGER,
+ id2 INTEGER,
+ gr1 INTEGER,
+ gr2 INTEGER
+);
+
+INSERT INTO test VALUES
+(1,0,1,1),
+(2,0,1,1),
+(3,0,1,1),
+(4,0,1,1),
+(5,0,1,1),
+(6,0,1,1),
+(7,0,1,1),
+(8,0,1,1),
+(9,0,1,1),
+(9,0,1,1),
+(9,0,1,1),
+(9,0,1,1),
+(0,1,1,2),
+(0,2,1,2),
+(0,3,1,2),
+(0,4,1,2),
+(0,5,1,2),
+(0,6,1,2),
+(10,10,2,2),
+(20,20,2,2),
+(30,30,2,2),
+(40,40,2,2),
+(50,50,2,2),
+(60,60,2,2),
+(70,70,2,2)
+;
+
+-- Sample without replacement
+DROP TABLE IF EXISTS out;
+SELECT balance_sample('test', 'out', 'gr1', 'undersample', NULL, NULL, FALSE);
+SELECT * FROM out;
+
+--- Sample with replacement
+DROP TABLE IF EXISTS out_sr2;
+SELECT balance_sample('test', 'out', 'gr1', 'undersample', NULL, NULL, TRUE);
+SELECT * FROM out;
+"""
+ else:
+ help_string = "No such option. Use {schema_madlib}.graph_sssp()"
+
+ return help_string.format(schema_madlib=schema_madlib)
+
+
+import unittest
+
+
+class UtilitiesTestCase(unittest.TestCase):
+ """
+ Comment "import plpy" and replace plpy.error calls with appropriate
+ Python Exceptions to successfully run the test cases
+ """
+
+ def setUp(self):
+ self.input_class_level_counts1 = {'a': 20, 'b': 30, 'c': 25}
+ self.level1a = 'a'
+ self.level1a_cnt1 = 15
+ self.level1a_cnt2 = 25
+ self.level1a_cnt3 = 20
+
+ self.sampling_strategy_str0 = ''
+ self.sampling_strategy_str1 = 'uniform'
+ self.sampling_strategy_str2 = 'oversample'
+ self.sampling_strategy_str3 = 'undersample'
+ self.user_specified_class_size0 = ''
+ self.user_specified_class_size1 = {'a': 25, 'b': 25}
+ self.user_specified_class_size2 = {'b': 25}
+ self.user_specified_class_size3 = {'a': 30}
+ self.output_table_size1 = None
+ self.output_table_size2 = 60
+ # self.input_class_level_counts2 = {'a':100, 'b':100, 'c':100}
+
+ def test__choose_strategy(self):
+ self.assertEqual(UNDERSAMPLE, _choose_strategy(35, 25))
+ self.assertEqual(OVERSAMPLE, _choose_strategy(15, 25))
+ self.assertEqual(UNDERSAMPLE, _choose_strategy(25, 25))
+
+ def test__get_target_level_counts(self):
+ # Test cases for user defined class level samples, without output table size
+ self.assertEqual({'a': (25, OVERSAMPLE), 'b': (25, UNDERSAMPLE), 'c': (25, NOSAMPLE)},
+ _get_target_level_counts(self.sampling_strategy_str0,
+ self.user_specified_class_size1,
+ self.input_class_level_counts1,
+ self.output_table_size1))
+ self.assertEqual({'a': (20, NOSAMPLE), 'b': (25, UNDERSAMPLE), 'c': (25, NOSAMPLE)},
+ _get_target_level_counts(self.sampling_strategy_str0,
+ self.user_specified_class_size2,
+ self.input_class_level_counts1,
+ self.output_table_size1))
+ self.assertEqual({'a': (30, OVERSAMPLE), 'b': (30, NOSAMPLE), 'c': (25, NOSAMPLE)},
+ _get_target_level_counts(self.sampling_strategy_str0,
+ self.user_specified_class_size3,
+ self.input_class_level_counts1,
+ self.output_table_size1))
+ # Test cases for user defined class level samples, with output table size
+ self.assertEqual({'a': (25, OVERSAMPLE), 'b': (25, UNDERSAMPLE), 'c': (10, UNDERSAMPLE)},
+ _get_target_level_counts(self.sampling_strategy_str0,
+ self.user_specified_class_size1,
+ self.input_class_level_counts1,
+ self.output_table_size2))
+ self.assertEqual({'a': (18, UNDERSAMPLE), 'b': (25, UNDERSAMPLE), 'c': (18, UNDERSAMPLE)},
+ _get_target_level_counts(self.sampling_strategy_str0,
+ self.user_specified_class_size2,
+ self.input_class_level_counts1,
+ self.output_table_size2))
+ self.assertEqual({'a': (30, OVERSAMPLE), 'b': (15, UNDERSAMPLE), 'c': (15, UNDERSAMPLE)},
+ _get_target_level_counts(self.sampling_strategy_str0,
+ self.user_specified_class_size3,
+ self.input_class_level_counts1,
+ self.output_table_size2))
+ # Test cases for UNIFORM, OVERSAMPLE, and UNDERSAMPLE without any output table size
+ self.assertEqual({'a': (25, OVERSAMPLE), 'b': (25, UNDERSAMPLE), 'c': (25, UNDERSAMPLE)},
+ _get_target_level_counts(self.sampling_strategy_str1,
+ self.user_specified_class_size0,
+ self.input_class_level_counts1,
+ self.output_table_size1))
+ self.assertEqual({'a': (30, OVERSAMPLE), 'b': (30, UNDERSAMPLE), 'c': (30, OVERSAMPLE)},
+ _get_target_level_counts(self.sampling_strategy_str2,
+ self.user_specified_class_size0,
+ self.input_class_level_counts1,
+ self.output_table_size1))
+ self.assertEqual({'a': (20, UNDERSAMPLE), 'b': (20, UNDERSAMPLE), 'c': (20, UNDERSAMPLE)},
+ _get_target_level_counts(self.sampling_strategy_str3,
+ self.user_specified_class_size0,
+ self.input_class_level_counts1,
+ self.output_table_size1))
+ # Test cases for UNIFORM with output table size
+ self.assertEqual({'a': (20, UNDERSAMPLE), 'b': (20, UNDERSAMPLE), 'c': (20, UNDERSAMPLE)},
+ _get_target_level_counts(self.sampling_strategy_str1,
+ self.user_specified_class_size0,
+ self.input_class_level_counts1,
+ self.output_table_size2))
+
+ def test__get_sampling_strategy_specific_dict(self):
+ # Test cases for getting sampling strategy specific counts
+ target_level_counts_1 = {'a': (25, OVERSAMPLE), 'b': (25, UNDERSAMPLE), 'c': (25, NOSAMPLE)}
+ target_level_counts_2 = {'a': (25, OVERSAMPLE), 'b': (25, UNDERSAMPLE)}
+ target_level_counts_3 = {'a': (25, OVERSAMPLE), 'b': (25, NOSAMPLE), 'c': (25, NOSAMPLE)}
+ self.assertEqual(({'b': 25}, {'a': 25}, {'c': 25}),
+ _get_sampling_strategy_specific_dict(target_level_counts_1))
+ self.assertEqual(({'b': 25}, {'a': 25}, {}),
+ _get_sampling_strategy_specific_dict(target_level_counts_2))
+ self.assertEqual(({}, {'a': 25}, {'c': 25, 'b': 25}),
+ _get_sampling_strategy_specific_dict(target_level_counts_3))
+
+
+if __name__ == '__main__':
+ unittest.main()
+ # print(_get_with_replacement_subquery('madlib', 'madlibtestdata.log_ornstein_wi',
--- End diff --
Can we remove this commented out code ?
---
[GitHub] madlib issue #230: Balanced sets final
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit commented on the issue:
https://github.com/apache/madlib/pull/230
Refer to this link for build results (access rights to CI server needed):
https://builds.apache.org/job/madlib-pr-build/332/
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165479832
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
+ if strategy == UNDERSAMPLE:
+ chosen_strategy = undersample_level_dict
+ elif strategy == OVERSAMPLE:
+ chosen_strategy = oversample_level_dict
+ else:
+ chosen_strategy = nosample_level_dict
+ chosen_strategy[level] = count
+ return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
+# ------------------------------------------------------------------------------
+
+
+def _get_nosample_subquery(source_table, class_col, nosample_levels):
+ """ Return the subquery for fetching all rows as is from the input table
+ for specific class levels.
+ """
+ if not nosample_levels:
+ return ''
+ subquery = """
+ SELECT *
+ FROM {0}
+ WHERE {1} in ({2}) OR {1} IS NULL
+ """.format(source_table, class_col,
+ ','.join(["'{0}'".format(level)
+ for level in nosample_levels if level]))
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_without_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the subquery for sampling without replacement for specific
+ class levels.
+ """
+ if not desired_level_counts:
+ return ''
+ class_col_tmp = unique_string()
+ row_number_col = unique_string()
+ desired_count_col = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_level_counts_str = "VALUES " + \
+ ','.join("({0}, {1})".
+ format("'{0}'::text".format(k) if k else null_value_string, v)
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT {source_table_columns},
+ row_number() OVER (PARTITION BY {class_col} ORDER BY random()) AS {row_number_col},
+ {desired_count_col}
+ FROM
+ (
+ SELECT {source_table_columns},
+ {desired_count_col}
+ FROM
+ {source_table} s,
+ ({desired_level_counts_str})
+ q({class_col_tmp}, {desired_count_col})
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}')
+ ) q2
+ ) q3
+ WHERE {row_number_col} <= {desired_count_col}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_with_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the query for sampling with replacement for specific class
+ levels (always used for oversampling, and used for undersampling if
+ with_replacement flag is set to TRUE).
+ """
+ if not desired_level_counts:
+ return ''
+
+ class_col_tmp = unique_string()
+ desired_count_col = unique_string()
+ actual_count_col = unique_string()
+ q1_row_no = unique_string()
+ q2_row_no = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_and_actual_level_counts = "VALUES " + \
+ ','.join("({0}, {1}, {2})".
+ format("'{0}'::text".format(k) if k else null_value_string,
+ v, actual_level_counts[k])
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT
+ {class_col_tmp},
+ generate_series(1, {desired_count_col}::int) AS _i,
+ ((random()*({actual_count_col}-1)+1)::int) AS {q1_row_no}
+ FROM
+ ({desired_and_actual_level_counts})
+ q({class_col_tmp}, {desired_count_col}, {actual_count_col})
+ ) q1,
+ (
+ SELECT
+ *,
+ row_number() OVER(PARTITION BY {class_col}) AS {q2_row_no}
+ FROM
+ {source_table}
+ ) q2
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}') AND
+ q1.{q1_row_no} = q2.{q2_row_no}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+def balance_sample(schema_madlib, source_table, output_table, class_col,
+ class_sizes, output_table_size, grouping_cols,
+ with_replacement, keep_null, **kwargs):
+ """
+ Balance sampling function
+ Args:
+ @param source_table Input table name.
--- End diff --
schema_madlib param is missing from the docstring
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165748923
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
--- End diff --
Minor suggestion : an alternative to using the chosen_strategy dict is to assign all the three dicts in the if check itself. something like
```
for level, (count, strategy) in target_class_sizes.items():
if strategy == UNDERSAMPLE:
undersample_level_dict[level] = count
elif strategy == OVERSAMPLE:
oversample_level_dict[level] = count
else:
nosample_level_dict[level] = count
return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
```
It is a bit redundant but more direct. I will leave the decision up to you
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165505215
--- Diff: src/ports/postgres/modules/sample/balance_sample.sql_in ---
@@ -0,0 +1,355 @@
+/* ----------------------------------------------------------------------- *//**
+ *
+ * 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.
+ *
+ *
+ * @file balance_sample.sql_in
+ *
+ * @brief SQL functions for balanced data sets sampling.
+ * @date 12/14/2017
+ *
+ * @sa Given a table, balanced sampling returns a sampled data set
+ * with specified balancing for each class (defaults to uniform sampleing).
+ *
+ *//* ----------------------------------------------------------------------- */
+
+m4_include(`SQLCommon.m4')
+
+
+/**
+@addtogroup grp_balance_sampling
+
+<div class="toc"><b>Contents</b>
+<ul>
+<li><a href="#strs">Balanced Data Sets Sampling</a></li>
+<li><a href="#examples">Examples</a></li>
+</ul>
+</div>
+
+@brief A method for independently sampling classes to receive a
+balanced data set.
+It is commonly used to where classes have greater imbalanced ratio.
+It ensures the subclasses are adequately represented in the sample.
+
+@anchor strs
+@par Balanced Sampling
+
+<pre class="syntax">
+balance_sample( source_table,
+ output_table,
+ class_col,
+ class_sizes,
+ output_table_size,
+ grouping_cols,
+ with_replacement,
+ keep_null
+ )
+</pre>
+
+\b Arguments
+<dl class="arglist">
+<dt>source_table</dt>
+<dd>TEXT. Name of the table containing the input data.</dd>
+
+<dt>output_table</dt>
+<dd>TEXT. Name of output table that contains the sampled data.
+The output table contains all columns present in the source
+table.</dd>
+
+<dt>class_col</dt>
+<dd>TEXT, Name of the column containing the class to be balanced.
+</dd>
+
+<dt>class_sizes (optional)</dt>
+<dd>VARCHAR, default ‘uniform’.
+
+@note
+Current implementation only supports 'undersample'.
+
+Parameter to define the size of the different class values.
+(Class values are sometimes also called levels).
+
+Can take the following forms:
+
+<ul>
+<li><b>‘uniform’</b>:
+All class values will be resampled to have the same number of rows.
+</li>
+<li><b>'undersample'</b>:
+Under-sample such that all class values end up with the same number of
+observations as the minority class. Done without replacement by default
+unless the parameter ‘with_replacement’ is set to TRUE.
+</li>
+<li><b>'oversample'</b>:
+Over-sample with replacement such that all class values end up with the
+same number of observations as the majority class. Not affected by the
+parameter ‘with_replacement’ since over-sampling is always done with
+replacement.
+</li>
+</ul>
+
+You can also explicitly set class size in a string containing a
+comma-delimited list. Order does not matter and all class values do not
+need to be specified. Use the format “class_value_1=x, class_value_2=y, ...”
+where the class value in the list must exist in the column ‘class_col’.
+
+E.g., ‘male=3000, female=7000’ means you want to resample the dataset
+to result in 3000 male and 7000 female rows in the ‘output_table’.
+
+@note
+The allowed names for class values follows object naming rules in
+PostgreSQL [6]. Quoted identifiers are allowed and should be enclosed
+in double quotes “ in the usual way. If for some reason the class values
+in the examples above were “MaLe” and “FeMaLe” then the comma delimited
+list for ‘class_size’ would be: ‘“MaLe”=3000, “FeMaLe”=7000’.
+</dd>
+
+<dt>output_table_size (optional)</dt>
+<dd>INTEGER, default NULL. Desired size of the output data set.
+
+This parameter is ignored if ‘class_size’ parameter is set to either
+‘oversample’ or ‘undersample’ since output table size is already determined.
+If NULL, the resulting output table size will depend on the settings for the
+‘class_size’ parameter.
+
+Example: ‘3000’ will result in 3000 rows in the ‘ouput_table’.
+</dd>
+
+<dt>grouping_cols (optional)</dt>
+<dd>
+TEXT, default: NULL. A single column or a list of comma-separated columns that
+defines the strata. When this parameter is NULL, no grouping is used so the
+sampling is non-stratified, that is, the whole table is treated as a single
+group.
+
+@note Current implementation does not support grouping_cols
+</dd>
+
+<dt>with_replacement (optional)</dt>
+<dd>
+BOOLEAN, default FALSE. Determines whether to sample with replacement or
+without replacement (default). With replacement means that it is possible that
+the same row may appear in the sample set more than once. Without replacement
+means a given row can be selected only once. This parameter affects
+under-sampling only since over-sampling is always performed with replacement.
+</dd>
+
+<dt>keep_null (optional)</dt>
+<dd>
+BOOLEAN, default FALSE. Determines whether to sample rows whose class values
+are NULL. By default, all rows with NULL class values are ignored. If this is
+set to TRUE, then NULL is treated as another class value.
+</dd>
+
+</dl>
+
+@anchor examples
+@par Examples
+
+Please note that due to the random nature of sampling, your
+results may look different from those below.
+
+-# Create an input table:
+<pre class="syntax">
+DROP TABLE IF EXISTS test;
+CREATE TABLE test(
+ id1 INTEGER,
+ id2 INTEGER,
+ gr1 INTEGER,
+ gr2 INTEGER
+);
+INSERT INTO test VALUES
+(1,0,1,1),
+(2,0,1,1),
+(3,0,1,1),
+(4,0,1,1),
+(5,0,1,1),
+(6,0,1,1),
+(7,0,1,1),
+(8,0,1,1),
+(9,0,1,1),
+(9,0,1,1),
+(9,0,1,1),
+(9,0,1,1),
+(0,1,1,2),
+(0,2,1,2),
+(0,3,1,2),
+(0,4,1,2),
+(0,5,1,2),
+(0,6,1,2),
+(10,10,2,2),
+(20,20,2,2),
+(30,30,2,2),
+(40,40,2,2),
+(50,50,2,2),
+(60,60,2,2),
+(70,70,2,2);
+</pre>
+
+-# Sample without replacement:
+<pre class="syntax">
+DROP TABLE IF EXISTS out;
+SELECT madlib.balance_sample(
+ 'test', -- Source table
+ 'out', -- Output table
+ 'gr1'); -- Class column
--- End diff --
can we rename all occurrences of `gr1` since it can be confused as a grouping column as well.
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165546601
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
+ if strategy == UNDERSAMPLE:
+ chosen_strategy = undersample_level_dict
+ elif strategy == OVERSAMPLE:
+ chosen_strategy = oversample_level_dict
+ else:
+ chosen_strategy = nosample_level_dict
+ chosen_strategy[level] = count
+ return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
+# ------------------------------------------------------------------------------
+
+
+def _get_nosample_subquery(source_table, class_col, nosample_levels):
+ """ Return the subquery for fetching all rows as is from the input table
+ for specific class levels.
+ """
+ if not nosample_levels:
+ return ''
+ subquery = """
+ SELECT *
+ FROM {0}
+ WHERE {1} in ({2}) OR {1} IS NULL
+ """.format(source_table, class_col,
+ ','.join(["'{0}'".format(level)
+ for level in nosample_levels if level]))
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_without_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the subquery for sampling without replacement for specific
+ class levels.
+ """
+ if not desired_level_counts:
+ return ''
+ class_col_tmp = unique_string()
+ row_number_col = unique_string()
+ desired_count_col = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_level_counts_str = "VALUES " + \
+ ','.join("({0}, {1})".
+ format("'{0}'::text".format(k) if k else null_value_string, v)
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT {source_table_columns},
+ row_number() OVER (PARTITION BY {class_col} ORDER BY random()) AS {row_number_col},
+ {desired_count_col}
+ FROM
+ (
+ SELECT {source_table_columns},
+ {desired_count_col}
+ FROM
+ {source_table} s,
+ ({desired_level_counts_str})
+ q({class_col_tmp}, {desired_count_col})
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}')
+ ) q2
+ ) q3
+ WHERE {row_number_col} <= {desired_count_col}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_with_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the query for sampling with replacement for specific class
+ levels (always used for oversampling, and used for undersampling if
+ with_replacement flag is set to TRUE).
+ """
+ if not desired_level_counts:
+ return ''
+
+ class_col_tmp = unique_string()
+ desired_count_col = unique_string()
+ actual_count_col = unique_string()
+ q1_row_no = unique_string()
+ q2_row_no = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_and_actual_level_counts = "VALUES " + \
+ ','.join("({0}, {1}, {2})".
+ format("'{0}'::text".format(k) if k else null_value_string,
+ v, actual_level_counts[k])
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT
+ {class_col_tmp},
+ generate_series(1, {desired_count_col}::int) AS _i,
+ ((random()*({actual_count_col}-1)+1)::int) AS {q1_row_no}
+ FROM
+ ({desired_and_actual_level_counts})
+ q({class_col_tmp}, {desired_count_col}, {actual_count_col})
+ ) q1,
+ (
+ SELECT
+ *,
+ row_number() OVER(PARTITION BY {class_col}) AS {q2_row_no}
+ FROM
+ {source_table}
+ ) q2
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}') AND
+ q1.{q1_row_no} = q2.{q2_row_no}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+def balance_sample(schema_madlib, source_table, output_table, class_col,
+ class_sizes, output_table_size, grouping_cols,
+ with_replacement, keep_null, **kwargs):
+ """
+ Balance sampling function
+ Args:
+ @param source_table Input table name.
+ @param output_table Output table name.
+ @param class_col Name of the column containing the class to be
+ balanced.
+ @param class_sizes Parameter to define the size of the different
+ class values.
+ @param output_table_size Desired size of the output data set.
+ @param grouping_cols The columns that define the grouping.
+ @param with_replacement The sampling method.
+ @param keep_null Flag to include rows with class level values
+ NULL. Default is False.
+
+ """
+ with MinWarning("warning"):
+
+ desired_sample_per_class = unique_string(desp='desired_sample_per_class')
+ desired_counts = unique_string(desp='desired_counts')
+
+ # set all default values
+ if not class_sizes:
+ class_sizes = UNIFORM
+ if not with_replacement:
+ with_replacement = False
+ keep_null = False if not keep_null else True
+ if class_sizes:
+ class_sizes = class_sizes.strip()
+
+ _validate_strs(source_table, output_table, class_col,
+ output_table_size, grouping_cols)
+ source_table_columns = ','.join(get_cols(source_table))
+
+ new_source_table = source_table
+ # If keep_null=False, create a view of the input table ignoring NULL
+ # values for class levels.
+ if not keep_null:
+ new_source_table = unique_string(desp='source_table')
+ plpy.execute("""
+ CREATE VIEW {new_source_table} AS
+ SELECT * FROM {source_table}
+ WHERE {class_col} IS NOT NULL
+ """.format(**locals()))
+ actual_level_counts = _get_frequency_distribution(new_source_table,
+ class_col)
+ # class_sizes can be of two forms:
+ # 1. A string describing sampling strategy (as described in
+ # _validate_and_get_sampling_strategy).
+ # In this case, 'sampling_strategy_str' is set to one of
+ # [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ # 2. Class sizes for all (or a subset) of the class levels
+ # In this case, sampling_strategy_str = None and parsed_class_sizes
+ # is used for the sampling.
+ parsed_class_sizes = extract_keyvalue_params(class_sizes,
+ allow_duplicates=False,
+ lower_case_names=False)
+ if not parsed_class_sizes:
+ sampling_strategy_str = _validate_and_get_sampling_strategy(class_sizes,
+ output_table_size)
+ else:
+ sampling_strategy_str = None
+ try:
+ all_levels = actual_level_counts.keys()
+ for each_level, each_class_size in parsed_class_sizes.items():
+ _assert(each_level in all_levels,
+ "Sample: Invalid class value specified ({0})".
+ format(each_level))
+ each_class_size = int(each_class_size)
+ _assert(each_class_size >= 1,
+ "Sample: Class size has to be greater than zero")
+ parsed_class_sizes[each_level] = each_class_size
+
+ except TypeError:
+ plpy.error("Sample: Invalid value for class_sizes ({0})".
+ format(class_sizes))
+
+ # Get the number of rows to be sampled for each class level, based on
+ # the input table, class_sizes, and output_table_size params. This also
+ # includes info about the resulting sampling strategy, i.e., one of
+ # UNDERSAMPLE, OVERSAMPLE, or NOSAMPLE for each level.
+ target_class_sizes = _get_target_level_counts(sampling_strategy_str,
+ parsed_class_sizes,
+ actual_level_counts,
+ output_table_size)
+
+ undersample_level_dict, oversample_level_dict, nosample_level_dict = \
+ _get_sampling_strategy_specific_dict(target_class_sizes)
+
+ # Get subqueries for each sampling strategy, so that they can be used
--- End diff --
can we explain our sampling strategy in brief so that it's easier to understand the three functions that follow ?
---
[GitHub] madlib issue #230: Balanced sets final
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit commented on the issue:
https://github.com/apache/madlib/pull/230
Refer to this link for build results (access rights to CI server needed):
https://builds.apache.org/job/madlib-pr-build/333/
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165523942
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
+ if strategy == UNDERSAMPLE:
+ chosen_strategy = undersample_level_dict
+ elif strategy == OVERSAMPLE:
+ chosen_strategy = oversample_level_dict
+ else:
+ chosen_strategy = nosample_level_dict
+ chosen_strategy[level] = count
+ return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
+# ------------------------------------------------------------------------------
+
+
+def _get_nosample_subquery(source_table, class_col, nosample_levels):
+ """ Return the subquery for fetching all rows as is from the input table
+ for specific class levels.
+ """
+ if not nosample_levels:
+ return ''
+ subquery = """
+ SELECT *
+ FROM {0}
+ WHERE {1} in ({2}) OR {1} IS NULL
+ """.format(source_table, class_col,
+ ','.join(["'{0}'".format(level)
+ for level in nosample_levels if level]))
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_without_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the subquery for sampling without replacement for specific
+ class levels.
+ """
+ if not desired_level_counts:
+ return ''
+ class_col_tmp = unique_string()
+ row_number_col = unique_string()
+ desired_count_col = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_level_counts_str = "VALUES " + \
+ ','.join("({0}, {1})".
+ format("'{0}'::text".format(k) if k else null_value_string, v)
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT {source_table_columns},
+ row_number() OVER (PARTITION BY {class_col} ORDER BY random()) AS {row_number_col},
+ {desired_count_col}
+ FROM
+ (
+ SELECT {source_table_columns},
+ {desired_count_col}
+ FROM
+ {source_table} s,
+ ({desired_level_counts_str})
+ q({class_col_tmp}, {desired_count_col})
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}')
+ ) q2
+ ) q3
+ WHERE {row_number_col} <= {desired_count_col}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_with_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the query for sampling with replacement for specific class
+ levels (always used for oversampling, and used for undersampling if
+ with_replacement flag is set to TRUE).
+ """
+ if not desired_level_counts:
+ return ''
+
+ class_col_tmp = unique_string()
+ desired_count_col = unique_string()
+ actual_count_col = unique_string()
+ q1_row_no = unique_string()
+ q2_row_no = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_and_actual_level_counts = "VALUES " + \
+ ','.join("({0}, {1}, {2})".
+ format("'{0}'::text".format(k) if k else null_value_string,
+ v, actual_level_counts[k])
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT
+ {class_col_tmp},
+ generate_series(1, {desired_count_col}::int) AS _i,
+ ((random()*({actual_count_col}-1)+1)::int) AS {q1_row_no}
+ FROM
+ ({desired_and_actual_level_counts})
+ q({class_col_tmp}, {desired_count_col}, {actual_count_col})
+ ) q1,
+ (
+ SELECT
+ *,
+ row_number() OVER(PARTITION BY {class_col}) AS {q2_row_no}
+ FROM
+ {source_table}
+ ) q2
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}') AND
+ q1.{q1_row_no} = q2.{q2_row_no}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+def balance_sample(schema_madlib, source_table, output_table, class_col,
+ class_sizes, output_table_size, grouping_cols,
+ with_replacement, keep_null, **kwargs):
+ """
+ Balance sampling function
+ Args:
+ @param source_table Input table name.
+ @param output_table Output table name.
+ @param class_col Name of the column containing the class to be
+ balanced.
+ @param class_sizes Parameter to define the size of the different
+ class values.
+ @param output_table_size Desired size of the output data set.
+ @param grouping_cols The columns that define the grouping.
+ @param with_replacement The sampling method.
+ @param keep_null Flag to include rows with class level values
+ NULL. Default is False.
+
+ """
+ with MinWarning("warning"):
+
+ desired_sample_per_class = unique_string(desp='desired_sample_per_class')
+ desired_counts = unique_string(desp='desired_counts')
+
+ # set all default values
+ if not class_sizes:
+ class_sizes = UNIFORM
+ if not with_replacement:
+ with_replacement = False
+ keep_null = False if not keep_null else True
+ if class_sizes:
+ class_sizes = class_sizes.strip()
+
+ _validate_strs(source_table, output_table, class_col,
+ output_table_size, grouping_cols)
+ source_table_columns = ','.join(get_cols(source_table))
+
+ new_source_table = source_table
+ # If keep_null=False, create a view of the input table ignoring NULL
+ # values for class levels.
+ if not keep_null:
+ new_source_table = unique_string(desp='source_table')
+ plpy.execute("""
+ CREATE VIEW {new_source_table} AS
+ SELECT * FROM {source_table}
+ WHERE {class_col} IS NOT NULL
+ """.format(**locals()))
+ actual_level_counts = _get_frequency_distribution(new_source_table,
+ class_col)
+ # class_sizes can be of two forms:
+ # 1. A string describing sampling strategy (as described in
+ # _validate_and_get_sampling_strategy).
+ # In this case, 'sampling_strategy_str' is set to one of
+ # [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ # 2. Class sizes for all (or a subset) of the class levels
+ # In this case, sampling_strategy_str = None and parsed_class_sizes
+ # is used for the sampling.
+ parsed_class_sizes = extract_keyvalue_params(class_sizes,
+ allow_duplicates=False,
+ lower_case_names=False)
+ if not parsed_class_sizes:
+ sampling_strategy_str = _validate_and_get_sampling_strategy(class_sizes,
+ output_table_size)
+ else:
+ sampling_strategy_str = None
+ try:
+ all_levels = actual_level_counts.keys()
+ for each_level, each_class_size in parsed_class_sizes.items():
+ _assert(each_level in all_levels,
--- End diff --
Consider doing the `in` operation on the dict itself (which can be done in constant time) instead of doing it on the list of keys which will be O(n).
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165529067
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
--- End diff --
The more i look at this function the more it feels like that the if and else conditions could be two different functions but I understand that it does add a bit more redirection. I don't feel strongly about this, just my two cents.
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165734791
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
+ if strategy == UNDERSAMPLE:
+ chosen_strategy = undersample_level_dict
+ elif strategy == OVERSAMPLE:
+ chosen_strategy = oversample_level_dict
+ else:
+ chosen_strategy = nosample_level_dict
+ chosen_strategy[level] = count
+ return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
+# ------------------------------------------------------------------------------
+
+
+def _get_nosample_subquery(source_table, class_col, nosample_levels):
+ """ Return the subquery for fetching all rows as is from the input table
+ for specific class levels.
+ """
+ if not nosample_levels:
+ return ''
+ subquery = """
+ SELECT *
+ FROM {0}
+ WHERE {1} in ({2}) OR {1} IS NULL
+ """.format(source_table, class_col,
+ ','.join(["'{0}'".format(level)
+ for level in nosample_levels if level]))
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_without_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the subquery for sampling without replacement for specific
+ class levels.
+ """
+ if not desired_level_counts:
+ return ''
+ class_col_tmp = unique_string()
+ row_number_col = unique_string()
+ desired_count_col = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_level_counts_str = "VALUES " + \
+ ','.join("({0}, {1})".
+ format("'{0}'::text".format(k) if k else null_value_string, v)
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT {source_table_columns},
+ row_number() OVER (PARTITION BY {class_col} ORDER BY random()) AS {row_number_col},
+ {desired_count_col}
+ FROM
+ (
+ SELECT {source_table_columns},
+ {desired_count_col}
+ FROM
+ {source_table} s,
+ ({desired_level_counts_str})
+ q({class_col_tmp}, {desired_count_col})
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}')
+ ) q2
+ ) q3
+ WHERE {row_number_col} <= {desired_count_col}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_with_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the query for sampling with replacement for specific class
+ levels (always used for oversampling, and used for undersampling if
+ with_replacement flag is set to TRUE).
+ """
+ if not desired_level_counts:
+ return ''
+
+ class_col_tmp = unique_string()
+ desired_count_col = unique_string()
+ actual_count_col = unique_string()
+ q1_row_no = unique_string()
+ q2_row_no = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_and_actual_level_counts = "VALUES " + \
+ ','.join("({0}, {1}, {2})".
+ format("'{0}'::text".format(k) if k else null_value_string,
+ v, actual_level_counts[k])
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT
+ {class_col_tmp},
+ generate_series(1, {desired_count_col}::int) AS _i,
+ ((random()*({actual_count_col}-1)+1)::int) AS {q1_row_no}
+ FROM
+ ({desired_and_actual_level_counts})
+ q({class_col_tmp}, {desired_count_col}, {actual_count_col})
+ ) q1,
+ (
+ SELECT
+ *,
+ row_number() OVER(PARTITION BY {class_col}) AS {q2_row_no}
+ FROM
+ {source_table}
+ ) q2
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}') AND
+ q1.{q1_row_no} = q2.{q2_row_no}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+def balance_sample(schema_madlib, source_table, output_table, class_col,
+ class_sizes, output_table_size, grouping_cols,
+ with_replacement, keep_null, **kwargs):
+ """
+ Balance sampling function
+ Args:
+ @param source_table Input table name.
+ @param output_table Output table name.
+ @param class_col Name of the column containing the class to be
+ balanced.
+ @param class_sizes Parameter to define the size of the different
+ class values.
+ @param output_table_size Desired size of the output data set.
+ @param grouping_cols The columns that define the grouping.
+ @param with_replacement The sampling method.
+ @param keep_null Flag to include rows with class level values
+ NULL. Default is False.
+
+ """
+ with MinWarning("warning"):
+
+ desired_sample_per_class = unique_string(desp='desired_sample_per_class')
+ desired_counts = unique_string(desp='desired_counts')
+
+ # set all default values
+ if not class_sizes:
+ class_sizes = UNIFORM
+ if not with_replacement:
+ with_replacement = False
+ keep_null = False if not keep_null else True
+ if class_sizes:
+ class_sizes = class_sizes.strip()
+
+ _validate_strs(source_table, output_table, class_col,
+ output_table_size, grouping_cols)
+ source_table_columns = ','.join(get_cols(source_table))
+
+ new_source_table = source_table
+ # If keep_null=False, create a view of the input table ignoring NULL
+ # values for class levels.
+ if not keep_null:
+ new_source_table = unique_string(desp='source_table')
+ plpy.execute("""
+ CREATE VIEW {new_source_table} AS
+ SELECT * FROM {source_table}
+ WHERE {class_col} IS NOT NULL
+ """.format(**locals()))
+ actual_level_counts = _get_frequency_distribution(new_source_table,
+ class_col)
+ # class_sizes can be of two forms:
+ # 1. A string describing sampling strategy (as described in
+ # _validate_and_get_sampling_strategy).
+ # In this case, 'sampling_strategy_str' is set to one of
+ # [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ # 2. Class sizes for all (or a subset) of the class levels
+ # In this case, sampling_strategy_str = None and parsed_class_sizes
+ # is used for the sampling.
+ parsed_class_sizes = extract_keyvalue_params(class_sizes,
+ allow_duplicates=False,
+ lower_case_names=False)
+ if not parsed_class_sizes:
+ sampling_strategy_str = _validate_and_get_sampling_strategy(class_sizes,
+ output_table_size)
+ else:
+ sampling_strategy_str = None
+ try:
+ all_levels = actual_level_counts.keys()
+ for each_level, each_class_size in parsed_class_sizes.items():
+ _assert(each_level in all_levels,
+ "Sample: Invalid class value specified ({0})".
+ format(each_level))
+ each_class_size = int(each_class_size)
+ _assert(each_class_size >= 1,
+ "Sample: Class size has to be greater than zero")
+ parsed_class_sizes[each_level] = each_class_size
+
+ except TypeError:
+ plpy.error("Sample: Invalid value for class_sizes ({0})".
+ format(class_sizes))
+
+ # Get the number of rows to be sampled for each class level, based on
+ # the input table, class_sizes, and output_table_size params. This also
+ # includes info about the resulting sampling strategy, i.e., one of
+ # UNDERSAMPLE, OVERSAMPLE, or NOSAMPLE for each level.
+ target_class_sizes = _get_target_level_counts(sampling_strategy_str,
+ parsed_class_sizes,
+ actual_level_counts,
+ output_table_size)
+
+ undersample_level_dict, oversample_level_dict, nosample_level_dict = \
+ _get_sampling_strategy_specific_dict(target_class_sizes)
+
+ # Get subqueries for each sampling strategy, so that they can be used
+ # together in one big query.
+ nosample_subquery = _get_nosample_subquery(
+ new_source_table, class_col, nosample_level_dict.keys())
+ oversample_subquery = _get_with_replacement_subquery(
+ schema_madlib, new_source_table, source_table_columns, class_col,
+ actual_level_counts, oversample_level_dict)
+ if with_replacement:
+ undersample_subquery = _get_with_replacement_subquery(
+ schema_madlib, new_source_table, source_table_columns, class_col,
+ actual_level_counts, undersample_level_dict)
+ else:
+ undersample_subquery = _get_without_replacement_subquery(
+ schema_madlib, new_source_table, source_table_columns, class_col,
+ actual_level_counts, undersample_level_dict)
+
+ # Merge the three subqueries using a UNION ALL clause.
+ union_all_subquery = ' UNION ALL '.join(
+ ['({0})'.format(subquery)
+ for subquery in [undersample_subquery, oversample_subquery, nosample_subquery]
+ if subquery])
+
+ final_query = """
+ CREATE TABLE {output_table} AS
+ SELECT row_number() OVER() AS {new_col_name}, *
+ FROM (
+ {union_all_subquery}
+ ) union_query
+ """.format(new_col_name=NEW_ID_COLUMN, **locals())
+ plpy.execute(final_query)
+ if not keep_null:
+ plpy.execute("DROP VIEW {0}".format(new_source_table))
+
+
+def _validate_strs(source_table, output_table, class_col, output_table_size,
+ grouping_cols):
+ _assert(source_table and table_exists(source_table),
+ "Sample: Source table ({source_table}) does not exist.".format(**locals()))
+ _assert(not table_is_empty(source_table),
+ "Sample: Source table ({source_table}) is empty.".format(**locals()))
+
+ _assert(output_table,
+ "Sample: Output table name is missing.".format(**locals()))
+ _assert(not table_exists(output_table),
+ "Sample: Output table ({output_table}) already exists.".format(**locals()))
+
+ _assert(class_col,
+ "Sample: Class column name is missing.".format(**locals()))
+ _assert(columns_exist_in_table(source_table, [class_col]),
+ ("""Sample: Class column ({class_col}) does not exist in""" +
+ """ table ({source_table}).""").format(**locals()))
+
+ _assert(not columns_exist_in_table(source_table, [NEW_ID_COLUMN]),
+ ("""Sample: Please ensure the source table ({0})""" +
+ """ does not contain a column named {1}""").format(source_table, NEW_ID_COLUMN))
+
+ _assert((not output_table_size) or (output_table_size > 0),
+ "Sample: Invalid output table size ({output_table_size}).".format(
+ **locals()))
+
+ _assert(grouping_cols is None,
+ "grouping_cols is not supported at the moment."
+ .format(**locals()))
+
+
+def balance_sample_help(schema_madlib, message, **kwargs):
+ """
+ Help function for balance_sample
+
+ Args:
+ @param schema_madlib
+ @param message: string, Help message string
+ @param kwargs
+
+ Returns:
+ String. Help/usage information
+ """
+ if not message:
+ help_string = """
+-----------------------------------------------------------------------
+ SUMMARY
+-----------------------------------------------------------------------
+Given a table with varying set of records for each class label,
+this function will create an output table with a varying types (by
+default: uniform) of sampling distributions of each class label. It is
+possible to use with or without replacement sampling methods, specify
+different proportions of each class, multiple grouping columns and/or
+output table size.
+
+For more details on function usage:
+ SELECT {schema_madlib}.balance_sample('usage');
+ SELECT {schema_madlib}.balance_sample('example');
+ """
+ elif message.lower() in ['usage', 'help', '?']:
+ help_string = """
+
+Given a table, stratified sampling returns a proportion of records for
+each group (strata). It is possible to use with or without replacement
+sampling methods, specify a set of target columns, and assume the
+whole table is a single strata.
+
+----------------------------------------------------------------------------
+ USAGE
+----------------------------------------------------------------------------
+
+ SELECT {schema_madlib}.balance_sample(
+ source_table TEXT, -- Input table name.
+ output_table TEXT, -- Output table name.
+ class_col TEXT, -- Name of column containing the class to be
+ -- balanced.
+ class_size TEXT, -- (Default: NULL) Parameter to define the size
+ -- of the different class values.
+ output_table_size INTEGER, -- (Default: NULL) Desired size of the output
+ -- data set.
+ grouping_cols TEXT, -- (Default: NULL) The columns columns that
+ -- defines the grouping.
+ with_replacement BOOLEAN -- (Default: FALSE) The sampling method.
+ keep_null BOOLEAN -- (Default: FALSE) Consider class levels with
+ NULL values or not.
+
+If class_size is NULL, the source table is uniformly sampled.
+
+If output_table_size is NULL, the resulting output table size will depend on
+the settings for the ‘class_size’ parameter. It is ignored if ‘class_size’
+parameter is set to either ‘oversample’ or ‘undersample’.
+
+If grouping_cols is NULL, the whole table is treated as a single group and
+sampled accordingly.
+
+If with_replacement is TRUE, each sample is independent (the same row may
+be selected in the sample set more than once). Else (if with_replacement
+is FALSE), a row can be selected at most once.
+);
+
+The output_table would contain the required number of samples, along with a
+new column named __madlib_id__, that contain unique numbers for all
+sampled rows.
+"""
+ elif message.lower() in ("example", "examples"):
+ help_string = """
+----------------------------------------------------------------------------
+ EXAMPLES
+----------------------------------------------------------------------------
+
+-- Create an input table
+DROP TABLE IF EXISTS test;
+
+CREATE TABLE test(
+ id1 INTEGER,
+ id2 INTEGER,
+ gr1 INTEGER,
+ gr2 INTEGER
+);
+
+INSERT INTO test VALUES
+(1,0,1,1),
+(2,0,1,1),
+(3,0,1,1),
+(4,0,1,1),
+(5,0,1,1),
+(6,0,1,1),
+(7,0,1,1),
+(8,0,1,1),
+(9,0,1,1),
+(9,0,1,1),
+(9,0,1,1),
+(9,0,1,1),
+(0,1,1,2),
+(0,2,1,2),
+(0,3,1,2),
+(0,4,1,2),
+(0,5,1,2),
+(0,6,1,2),
+(10,10,2,2),
+(20,20,2,2),
+(30,30,2,2),
+(40,40,2,2),
+(50,50,2,2),
+(60,60,2,2),
+(70,70,2,2)
+;
+
+-- Sample without replacement
+DROP TABLE IF EXISTS out;
+SELECT balance_sample('test', 'out', 'gr1', 'undersample', NULL, NULL, FALSE);
+SELECT * FROM out;
+
+--- Sample with replacement
+DROP TABLE IF EXISTS out_sr2;
+SELECT balance_sample('test', 'out', 'gr1', 'undersample', NULL, NULL, TRUE);
+SELECT * FROM out;
+"""
+ else:
+ help_string = "No such option. Use {schema_madlib}.graph_sssp()"
--- End diff --
This should be `{schema_madlib}.balance_sample()`
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165520949
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
--- End diff --
Also if the input is `un` , the error message is
```
ERROR: plpy.Error: Sample: Invalid class_sizes parameter (plpython.c:4960)
```
but if the input is `foo`, the error message is
```
plpy.Error: Sample: Invalid class_sizes parameter: foo. Supported class_size parameters are (oversample,undersample,uniform) (plpython.c:4960)
```
It should ideally be the same error message (the more verbose one) for the both the cases.
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165741803
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
+ if strategy == UNDERSAMPLE:
+ chosen_strategy = undersample_level_dict
+ elif strategy == OVERSAMPLE:
+ chosen_strategy = oversample_level_dict
+ else:
+ chosen_strategy = nosample_level_dict
+ chosen_strategy[level] = count
+ return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
+# ------------------------------------------------------------------------------
+
+
+def _get_nosample_subquery(source_table, class_col, nosample_levels):
+ """ Return the subquery for fetching all rows as is from the input table
+ for specific class levels.
+ """
+ if not nosample_levels:
+ return ''
+ subquery = """
+ SELECT *
+ FROM {0}
+ WHERE {1} in ({2}) OR {1} IS NULL
+ """.format(source_table, class_col,
+ ','.join(["'{0}'".format(level)
+ for level in nosample_levels if level]))
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_without_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the subquery for sampling without replacement for specific
+ class levels.
+ """
+ if not desired_level_counts:
+ return ''
+ class_col_tmp = unique_string()
+ row_number_col = unique_string()
+ desired_count_col = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_level_counts_str = "VALUES " + \
--- End diff --
This could be a table in sql which can get referenced in all the subqueries. Are we doing this in python to be more efficient ?
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by njayaram2 <gi...@git.apache.org>.
Github user njayaram2 commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r166161100
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
+ if strategy == UNDERSAMPLE:
+ chosen_strategy = undersample_level_dict
+ elif strategy == OVERSAMPLE:
+ chosen_strategy = oversample_level_dict
+ else:
+ chosen_strategy = nosample_level_dict
+ chosen_strategy[level] = count
+ return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
+# ------------------------------------------------------------------------------
+
+
+def _get_nosample_subquery(source_table, class_col, nosample_levels):
+ """ Return the subquery for fetching all rows as is from the input table
+ for specific class levels.
+ """
+ if not nosample_levels:
+ return ''
+ subquery = """
+ SELECT *
+ FROM {0}
+ WHERE {1} in ({2}) OR {1} IS NULL
+ """.format(source_table, class_col,
+ ','.join(["'{0}'".format(level)
+ for level in nosample_levels if level]))
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_without_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the subquery for sampling without replacement for specific
+ class levels.
+ """
+ if not desired_level_counts:
+ return ''
+ class_col_tmp = unique_string()
+ row_number_col = unique_string()
+ desired_count_col = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_level_counts_str = "VALUES " + \
+ ','.join("({0}, {1})".
+ format("'{0}'::text".format(k) if k else null_value_string, v)
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT {source_table_columns},
+ row_number() OVER (PARTITION BY {class_col} ORDER BY random()) AS {row_number_col},
+ {desired_count_col}
+ FROM
+ (
+ SELECT {source_table_columns},
+ {desired_count_col}
+ FROM
+ {source_table} s,
+ ({desired_level_counts_str})
+ q({class_col_tmp}, {desired_count_col})
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}')
+ ) q2
+ ) q3
+ WHERE {row_number_col} <= {desired_count_col}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_with_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the query for sampling with replacement for specific class
+ levels (always used for oversampling, and used for undersampling if
+ with_replacement flag is set to TRUE).
+ """
+ if not desired_level_counts:
+ return ''
+
+ class_col_tmp = unique_string()
+ desired_count_col = unique_string()
+ actual_count_col = unique_string()
+ q1_row_no = unique_string()
+ q2_row_no = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_and_actual_level_counts = "VALUES " + \
+ ','.join("({0}, {1}, {2})".
+ format("'{0}'::text".format(k) if k else null_value_string,
+ v, actual_level_counts[k])
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT
+ {class_col_tmp},
+ generate_series(1, {desired_count_col}::int) AS _i,
+ ((random()*({actual_count_col}-1)+1)::int) AS {q1_row_no}
+ FROM
+ ({desired_and_actual_level_counts})
+ q({class_col_tmp}, {desired_count_col}, {actual_count_col})
+ ) q1,
+ (
+ SELECT
+ *,
+ row_number() OVER(PARTITION BY {class_col}) AS {q2_row_no}
+ FROM
+ {source_table}
+ ) q2
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}') AND
+ q1.{q1_row_no} = q2.{q2_row_no}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+def balance_sample(schema_madlib, source_table, output_table, class_col,
+ class_sizes, output_table_size, grouping_cols,
+ with_replacement, keep_null, **kwargs):
+ """
+ Balance sampling function
+ Args:
+ @param source_table Input table name.
+ @param output_table Output table name.
+ @param class_col Name of the column containing the class to be
+ balanced.
+ @param class_sizes Parameter to define the size of the different
+ class values.
+ @param output_table_size Desired size of the output data set.
+ @param grouping_cols The columns that define the grouping.
+ @param with_replacement The sampling method.
+ @param keep_null Flag to include rows with class level values
--- End diff --
I think the module `pivot` has the same param name, so continuing the same format.
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165515209
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
+ if strategy == UNDERSAMPLE:
+ chosen_strategy = undersample_level_dict
+ elif strategy == OVERSAMPLE:
+ chosen_strategy = oversample_level_dict
+ else:
+ chosen_strategy = nosample_level_dict
+ chosen_strategy[level] = count
+ return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
+# ------------------------------------------------------------------------------
+
+
+def _get_nosample_subquery(source_table, class_col, nosample_levels):
+ """ Return the subquery for fetching all rows as is from the input table
+ for specific class levels.
+ """
+ if not nosample_levels:
+ return ''
+ subquery = """
+ SELECT *
+ FROM {0}
+ WHERE {1} in ({2}) OR {1} IS NULL
+ """.format(source_table, class_col,
+ ','.join(["'{0}'".format(level)
+ for level in nosample_levels if level]))
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_without_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the subquery for sampling without replacement for specific
+ class levels.
+ """
+ if not desired_level_counts:
+ return ''
+ class_col_tmp = unique_string()
+ row_number_col = unique_string()
+ desired_count_col = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_level_counts_str = "VALUES " + \
+ ','.join("({0}, {1})".
+ format("'{0}'::text".format(k) if k else null_value_string, v)
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT {source_table_columns},
+ row_number() OVER (PARTITION BY {class_col} ORDER BY random()) AS {row_number_col},
+ {desired_count_col}
+ FROM
+ (
+ SELECT {source_table_columns},
+ {desired_count_col}
+ FROM
+ {source_table} s,
+ ({desired_level_counts_str})
+ q({class_col_tmp}, {desired_count_col})
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}')
+ ) q2
+ ) q3
+ WHERE {row_number_col} <= {desired_count_col}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_with_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the query for sampling with replacement for specific class
+ levels (always used for oversampling, and used for undersampling if
+ with_replacement flag is set to TRUE).
+ """
+ if not desired_level_counts:
+ return ''
+
+ class_col_tmp = unique_string()
+ desired_count_col = unique_string()
+ actual_count_col = unique_string()
+ q1_row_no = unique_string()
+ q2_row_no = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_and_actual_level_counts = "VALUES " + \
+ ','.join("({0}, {1}, {2})".
+ format("'{0}'::text".format(k) if k else null_value_string,
+ v, actual_level_counts[k])
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT
+ {class_col_tmp},
+ generate_series(1, {desired_count_col}::int) AS _i,
+ ((random()*({actual_count_col}-1)+1)::int) AS {q1_row_no}
+ FROM
+ ({desired_and_actual_level_counts})
+ q({class_col_tmp}, {desired_count_col}, {actual_count_col})
+ ) q1,
+ (
+ SELECT
+ *,
+ row_number() OVER(PARTITION BY {class_col}) AS {q2_row_no}
+ FROM
+ {source_table}
+ ) q2
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}') AND
+ q1.{q1_row_no} = q2.{q2_row_no}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+def balance_sample(schema_madlib, source_table, output_table, class_col,
+ class_sizes, output_table_size, grouping_cols,
+ with_replacement, keep_null, **kwargs):
+ """
+ Balance sampling function
+ Args:
+ @param source_table Input table name.
+ @param output_table Output table name.
+ @param class_col Name of the column containing the class to be
+ balanced.
+ @param class_sizes Parameter to define the size of the different
--- End diff --
The name of this variable seems to indicate that it only takes class sizes as input. Only after I looked at the sql comments did I understand that this can be either 'under/over/uniform` or `class_sizes'. Also I had see the comment below which explains in detail the two different forms that this can take.
Can we consider changing the name to be more reflective of it's purpose ?
---
[GitHub] madlib pull request #230: Balanced sets final
Posted by kaknikhil <gi...@git.apache.org>.
Github user kaknikhil commented on a diff in the pull request:
https://github.com/apache/madlib/pull/230#discussion_r165733978
--- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
@@ -0,0 +1,748 @@
+# 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.
+
+m4_changequote(`<!', `!>')
+
+import math
+
+if __name__ != "__main__":
+ import plpy
+ from utilities.control import MinWarning
+ from utilities.utilities import _assert
+ from utilities.utilities import extract_keyvalue_params
+ from utilities.utilities import unique_string
+ from utilities.validate_args import columns_exist_in_table
+ from utilities.validate_args import get_cols
+ from utilities.validate_args import table_exists
+ from utilities.validate_args import table_is_empty
+else:
+ # Used only for Unit Testing
+ # FIXME: repeating a function from utilities that is needed by the unit test.
+ # This should be removed once a unittest framework in used for testing.
+ import random
+ import time
+
+ def unique_string(desp='', **kwargs):
+ """
+ Generate random remporary names for temp table and other names.
+ It has a SQL interface so both SQL and Python functions can call it.
+ """
+ r1 = random.randint(1, 100000000)
+ r2 = int(time.time())
+ r3 = int(time.time()) % random.randint(1, 100000000)
+ u_string = "__madlib_temp_" + desp + str(r1) + "_" + str(r2) + "_" + str(r3) + "__"
+ return u_string
+# ------------------------------------------------------------------------------
+
+UNIFORM = 'uniform'
+UNDERSAMPLE = 'undersample'
+OVERSAMPLE = 'oversample'
+NOSAMPLE = 'nosample'
+
+NEW_ID_COLUMN = '__madlib_id__'
+NULL_IDENTIFIER = '__madlib_null_id__'
+
+def _get_frequency_distribution(source_table, class_col):
+ """ Returns a dict containing the number of rows associated with each class
+ level. Each class level value is converted to a string using ::text.
+ """
+ query_result = plpy.execute("""
+ SELECT {class_col}::text AS classes,
+ count(*) AS class_count
+ FROM {source_table}
+ GROUP BY {class_col}
+ """.format(**locals()))
+ actual_level_counts = {}
+ for each_row in query_result:
+ level = each_row['classes']
+ if level:
+ level = level.strip()
+ actual_level_counts[level] = each_row['class_count']
+ return actual_level_counts
+
+
+def _validate_and_get_sampling_strategy(sampling_strategy_str, output_table_size,
+ supported_strategies=None, default=UNIFORM):
+ """ Returns the sampling strategy based on the class_sizes input param.
+ @param sampling_strategy_str The sampling strategy specified by the
+ user (class_sizes param)
+ @returns:
+ Str. One of [UNIFORM, UNDERSAMPLE, OVERSAMPLE]. Default is UNIFORM.
+ """
+ if not sampling_strategy_str:
+ sampling_strategy_str = default
+ else:
+ if len(sampling_strategy_str) < 3:
+ # Require at least 3 characters since UNIFORM and UNDERSAMPLE have
+ # common prefix substring
+ plpy.error("Sample: Invalid class_sizes parameter")
+
+ if not supported_strategies:
+ supported_strategies = [UNIFORM, UNDERSAMPLE, OVERSAMPLE]
+ try:
+ # allow user to specify a prefix substring of
+ # supported strategies.
+ sampling_strategy_str = next(x for x in supported_strategies
+ if x.startswith(sampling_strategy_str.lower()))
+ except StopIteration:
+ # next() returns a StopIteration if no element found
+ plpy.error("Sample: Invalid class_sizes parameter: "
+ "{0}. Supported class_size parameters are ({1})"
+ .format(sampling_strategy_str, ','.join(sorted(supported_strategies))))
+
+ _assert(sampling_strategy_str.lower() in (UNIFORM, UNDERSAMPLE, OVERSAMPLE) or
+ (sampling_strategy_str.find('=') > 0),
+ "Sample: Invalid class size ({sampling_strategy_str}).".format(**locals()))
+
+ _assert(not(sampling_strategy_str.lower() == 'oversample' and output_table_size),
+ "Sample: Cannot set output_table_size with oversampling.")
+
+ _assert(not(sampling_strategy_str.lower() == 'undersample' and output_table_size),
+ "Sample: Cannot set output_table_size with undersampling.")
+
+ return sampling_strategy_str
+# ------------------------------------------------------------------------------
+
+
+def _choose_strategy(actual_count, desired_count):
+ """ Choose sampling strategy by comparing actual and desired sample counts
+
+ @param actual_count: Actual number of samples for some level
+ @param desired_count: Desired number of sample for the level
+ @returns:
+ Str. Sampling strategy string (either UNDERSAMPlE or OVERSAMPLE)
+ """
+ # OVERSAMPLE when the actual count is less than the desired count
+ # UNDERSAMPLE when the actual count is more than the desired count
+
+ # If the actual count for a class level is the same as desired count, then
+ # we could potentially return the input rows as is. This, however,
+ # precludes the case of bootstrapping (i.e. returning same number of rows
+ # but after sampling with replacement). Hence, we treat the actual=desired
+ # as UNDERSAMPLE. It's specifically set to UNDERSAMPLE since it provides
+ # both 'with' and 'without' replacement (OVERSAMPLE is always with
+ # replacement and NOSAMPLE is always without replacement)
+ if actual_count < desired_count:
+ return OVERSAMPLE
+ else:
+ return UNDERSAMPLE
+# -------------------------------------------------------------------------
+
+def _get_target_level_counts(sampling_strategy_str, desired_level_counts,
+ actual_level_counts, output_table_size):
+ """
+ @param sampling_strategy_str: one of [UNIFORM, UNDERSAMPLE, OVERSAMPLE, None].
+ This is 'None' only if this is user-defined, i.e.,
+ a comma separated list of class levels and number of
+ rows desired pairs.
+ @param desired_level_counts: Dict that is defined and populated only when
+ sampling_strategy_str is None.
+ @param actual_level_counts: Dict of various class levels and number of rows
+ in each of them in the input table
+ @param output_table_size: Size of the desired output table (NULL or Integer)
+
+ @returns:
+ Dict. Number of samples to be drawn, and the sampling strategy to be
+ used for each class level.
+ """
+ target_level_counts = {}
+ if not sampling_strategy_str:
+ # This case implies user has provided a desired count for one or more
+ # levels. Counts for the rest of the levels depend on 'output_table_size'.
+ # if 'output_table_size' = NULL, unspecified level counts remain as is
+ # if 'output_table_size' = <Integer>, divide remaining row count
+ # uniformly among unspecified level counts
+ for each_level, desired_count in desired_level_counts.items():
+ sample_strategy = _choose_strategy(actual_level_counts[each_level],
+ desired_count)
+ target_level_counts[each_level] = (desired_count, sample_strategy)
+
+ remaining_levels = (set(actual_level_counts.keys()) -
+ set(desired_level_counts.keys()))
+ if output_table_size:
+ # Uniformly distribute across the remaining class levels
+ remaining_rows = output_table_size - sum(desired_level_counts.values())
+ if remaining_rows > 0:
+ rows_per_level = math.ceil(float(remaining_rows) /
+ len(remaining_levels))
+ for each_level in remaining_levels:
+ sample_strategy = _choose_strategy(
+ actual_level_counts[each_level], rows_per_level)
+ target_level_counts[each_level] = (rows_per_level,
+ sample_strategy)
+ else:
+ # When output_table_size is unspecified, rows from the input table
+ # are sampled as is for remaining class levels. This is same as the
+ # NOSAMPLE strategy.
+ for each_level in remaining_levels:
+ target_level_counts[each_level] = (actual_level_counts[each_level],
+ NOSAMPLE)
+ else:
+ def ceil_of_mean(numbers):
+ return math.ceil(float(sum(numbers)) / max(len(numbers), 1))
+
+ # UNIFORM: Ensure all level counts are same (size determined by output_table_size)
+ # UNDERSAMPLE: Ensure all level counts are same as the minimum count
+ # OVERSAMPLE: Ensure all level counts are same as the maximum count
+ size_function = {UNDERSAMPLE: min,
+ OVERSAMPLE: max,
+ UNIFORM: ceil_of_mean
+ }[sampling_strategy_str]
+ if sampling_strategy_str == UNIFORM and output_table_size:
+ # Ignore actual counts for computing target sizes
+ # if output_table_size is specified
+ target_size_per_level = math.ceil(float(output_table_size) /
+ len(actual_level_counts))
+ else:
+ target_size_per_level = size_function(actual_level_counts.values())
+ for each_level, actual_count in actual_level_counts.items():
+ sample_strategy = _choose_strategy(actual_count, target_size_per_level)
+ target_level_counts[each_level] = (target_size_per_level,
+ sample_strategy)
+ return target_level_counts
+
+# -------------------------------------------------------------------------
+
+
+def _get_sampling_strategy_specific_dict(target_class_sizes):
+ """ Return three dicts, one each for undersampling, oversampling, and
+ nosampling. The dict contains the number of samples to be drawn for
+ each class level.
+ """
+ undersample_level_dict = {}
+ oversample_level_dict = {}
+ nosample_level_dict = {}
+ for level, (count, strategy) in target_class_sizes.items():
+ if strategy == UNDERSAMPLE:
+ chosen_strategy = undersample_level_dict
+ elif strategy == OVERSAMPLE:
+ chosen_strategy = oversample_level_dict
+ else:
+ chosen_strategy = nosample_level_dict
+ chosen_strategy[level] = count
+ return (undersample_level_dict, oversample_level_dict, nosample_level_dict)
+# ------------------------------------------------------------------------------
+
+
+def _get_nosample_subquery(source_table, class_col, nosample_levels):
+ """ Return the subquery for fetching all rows as is from the input table
+ for specific class levels.
+ """
+ if not nosample_levels:
+ return ''
+ subquery = """
+ SELECT *
+ FROM {0}
+ WHERE {1} in ({2}) OR {1} IS NULL
+ """.format(source_table, class_col,
+ ','.join(["'{0}'".format(level)
+ for level in nosample_levels if level]))
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_without_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the subquery for sampling without replacement for specific
+ class levels.
+ """
+ if not desired_level_counts:
+ return ''
+ class_col_tmp = unique_string()
+ row_number_col = unique_string()
+ desired_count_col = unique_string()
+
+ null_value_string = "'{0}'".format(NULL_IDENTIFIER)
+
+ desired_level_counts_str = "VALUES " + \
+ ','.join("({0}, {1})".
+ format("'{0}'::text".format(k) if k else null_value_string, v)
+ for k, v in desired_level_counts.items())
+ subquery = """
+ SELECT {source_table_columns}
+ FROM
+ (
+ SELECT {source_table_columns},
+ row_number() OVER (PARTITION BY {class_col} ORDER BY random()) AS {row_number_col},
+ {desired_count_col}
+ FROM
+ (
+ SELECT {source_table_columns},
+ {desired_count_col}
+ FROM
+ {source_table} s,
+ ({desired_level_counts_str})
+ q({class_col_tmp}, {desired_count_col})
+ WHERE {class_col_tmp} = coalesce({class_col}::text, '{null_level_val}')
+ ) q2
+ ) q3
+ WHERE {row_number_col} <= {desired_count_col}
+ """.format(null_level_val=NULL_IDENTIFIER, **locals())
+ return subquery
+# ------------------------------------------------------------------------------
+
+
+def _get_with_replacement_subquery(schema_madlib, source_table,
+ source_table_columns, class_col,
+ actual_level_counts, desired_level_counts):
+ """ Return the query for sampling with replacement for specific class
+ levels (always used for oversampling, and used for undersampling if
+ with_replacement flag is set to TRUE).
+ """
+ if not desired_level_counts:
+ return ''
+
+ class_col_tmp = unique_string()
--- End diff --
can we pass the desp col to these unique_string calls ?
---