[ https://issues.apache.org/jira/browse/MADLIB-1240?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16533991#comment-16533991 ]
ASF GitHub Bot commented on MADLIB-1240: ---------------------------------------- Github user njayaram2 commented on a diff in the pull request: https://github.com/apache/madlib/pull/288#discussion_r200444598 --- Diff: src/ports/postgres/modules/cols_vec/cols2vec.py_in --- @@ -0,0 +1,110 @@ +""" +@file cols2vec.py_in + +@brief Utility to convert Columns to array + +""" + +import plpy +from utilities.control import MinWarning +from utilities.utilities import split_quoted_delimited_str +from utilities.utilities import _string_to_array +from utilities.validate_args import columns_exist_in_table +from utilities.validate_args import is_var_valid +from utilities.validate_args import get_cols +from utilities.validate_args import quote_ident +from utilities.utilities import py_list_to_sql_string + + +m4_changequote(`<!', `!>') + + +def validate_cols2vec_args(source_table, output_table, + list_of_features, list_of_features_to_exclude, cols_to_output, **kwargs): + """ + Function to validate input parameters + """ + if list_of_features.strip() != '*': + if not (list_of_features and list_of_features.strip()): + plpy.error("Features to include is empty") + + if list_of_features.strip() != '*': + if not columns_exist_in_table( + source_table, split_quoted_delimited_str(list_of_features)): + plpy.error( + "Invalid columns to list_of_features ({0})".format(list_of_features)) + + if cols_to_output and cols_to_output.strip() != '*': + if not columns_exist_in_table( + source_table, _string_to_array(cols_to_output)): + plpy.error("Invalid columns to output list ({0})". + format(cols_to_output)) + + +def cols2vec(schema_madlib, source_table, output_table, list_of_features, + list_of_features_to_exclude=None, cols_to_output=None, **kwargs): + """ + Args: + @param schema_madlib: Name of MADlib schema + @param model: Name of table containing the tree model + @param source_table: Name of table containing prediction data + @param output_table: Name of table to output the results + @param list_of_features: Comma-separated string of column names or + expressions to put into feature array. + Can also be a '*' implying all columns + are to be put into feature array. + @param list_of_features_to_exclude: Comma-separated string of column names + to exclude from the feature array + @param cols_to_output: Comma-separated string of column names + from the source table to keep in the output table, + in addition to the feature array. + + Returns: + None + + """ + + with MinWarning('warning'): + validate_cols2vec_args(source_table, output_table, list_of_features, + list_of_features_to_exclude, cols_to_output, **kwargs) + + all_cols = '' + feature_cols = '' + if list_of_features.strip() == '*': + all_cols = get_cols(source_table, schema_madlib) + all_col_set = set(list(all_cols)) + exclude_set = set(split_quoted_delimited_str( + list_of_features_to_exclude)) + feature_set = all_col_set - exclude_set + feature_cols = py_list_to_sql_string( + list(feature_set), "text", False) + filtered_list_of_features = ",".join( + feat for feat in list(feature_set)) + else: + feature_list = split_quoted_delimited_str(list_of_features) + feature_exclude = split_quoted_delimited_str( + list_of_features_to_exclude) + return_set = set(feature_list) - set(feature_exclude) + feature_cols = py_list_to_sql_string( + list(return_set), "text", False) + filtered_list_of_features = ",".join( + feat for feat in feature_list if feat in return_set) + + output_cols = '' + if cols_to_output: + output_cols_list = [', '.join(get_cols(source_table, schema_madlib)) if col == '*' else col + for col in split_quoted_delimited_str(cols_to_output)] + output_cols = ', '.join(output_cols_list) + "," + + plpy.execute(""" + CREATE TABLE {output_table} AS + select {output_cols} + array[{filtered_list_of_features}] as feature_vector + from {source_table} + """.format(**locals())) + + plpy.execute(""" --- End diff -- @iyerr3 The vec2cols story (https://issues.apache.org/jira/browse/MADLIB-1240) might consume this summary table if provided as the `dictionary` param in that module. > Vector to Columns > ----------------- > > Key: MADLIB-1240 > URL: https://issues.apache.org/jira/browse/MADLIB-1240 > Project: Apache MADlib > Issue Type: New Feature > Components: Module: Utilities > Reporter: Frank McQuillan > Assignee: Nandish Jayaram > Priority: Major > Fix For: v1.15 > > > related to https://issues.apache.org/jira/browse/MADLIB-1239 > Vector to Columns > Converts a feature array in a single column of an output table into multiple > columns. This process can be used to reverse the function cols2vec. > {code} > vec2cols( > source_table, > out_table, > vector_col, > dictionary, > cols_to_output > ) > source_table > TEXT. Name of the table containing the source data. > out_table > TEXT. Name of the generated table containing the output. If a table with the > same name already exists, an error will be returned. > vector_col > TEXT. Name of the column containing the feature array. Must be a > one-dimensional array. > dictionary (optional) > TEXT. Name of the table containing the array of names associated with the > feature array. This table is created by the function 'cols2vec'. If the > dictionary table is not specified, column names will be automatically > generated of the form 'feature_1, feature_2, ...feature_n' > cols_to_output (optional) > TEXT, default NULL. Comma-separated string of column names from the source > table to keep in the output table, in addition to the feature columns. To > keep all columns from the source table, use '*'. > Output > The output table produced by the vec2cols function contains the following > columns: > <...> > Columns from source table, depending on which ones are kept (if any). > feature columns > Columns for each of the features in 'vector_col'. Column type will depend on > the feature array type in the source table. Column naming will depend on > whether the parameter 'dictionary' is used. > {code} > Notes > (1) > The function > http://pivotalsoftware.github.io/PDLTools/group__ArrayUtilities.html > is similar but the proposed MADlib one has more options. To do the > equivalent of the PDL Tools one in MADlib, you would do: > {code} > vec2cols( > table_name, > output_table, > vector_column, > NULL, > '*' > ) > {code} > (2) > Please put the generated feature columns on the right side of the output > table, i.e., they will be the last column on the right. Maintain the order > of the array. -- This message was sent by Atlassian JIRA (v7.6.3#76005)