NZFunTApply

This corresponds to use-case 2. Building an ML model against all the available rows.. This leads to two variations: building a model against the slice data or building a model against the entire table data.

Applying functions on each and every slice of the data

A data slice is a piece of the table data. Netezza distributes data to different slices based on the column that was specified at the table creation time. If no column was specified, the first column would be considered as the distribution column. Hence, it is important to first identify how the data was distributed before using NZFunTApply. Otherwise, you might have unintended results with the function being applied to slices that you didn’t intend to.

Sample scenario: transform the data, build an ML model, and measure the accuracy of the model.

Let's define a function to do all these steps. The function gets two parameters by default, self (which represents the AE context) and df (dataframe for the incoming slice data). It first imputes the columns (assigning some default values for null values), then builds a decision tree classifier and measures a 3 fold CV accuracy. And the result (size of the dataset, location of the first record, accuracy) is printed with self.output.

from nzpyida import IdaDataBase, IdaDataFrame
from nzpyida.ae import  NZFunTApply

idadb = IdaDataBase('weather', 'admin', 'password', verbose=True)

idadf = IdaDataFrame(idadb, 'WEATHER')

code_str_host_spus="""def decision_tree_ml(self, df):
    from sklearn.model_selection import cross_val_score
    from sklearn.impute import SimpleImputer
    from sklearn.tree import DecisionTreeClassifier

    from sklearn.preprocessing import LabelEncoder
    import numpy as np

    location = df.LOCATION[0]

    # data preparation
    imputed_df = df.copy()
    ds_size = len(imputed_df)
    imputed_df['CLOUD9AM'] = imputed_df.CLOUD9AM.astype('str')
    imputed_df['CLOUD3PM'] = imputed_df.CLOUD3PM.astype('str')
    imputed_df['SUNSHINE'] = imputed_df.SUNSHINE.astype('float')
    imputed_df['EVAPORATION'] = imputed_df.EVAPORATION.astype('float')


    #remove columns which have only null values
    columns = imputed_df.columns
    for column in columns:
        if imputed_df[column].isnull().sum()==len(imputed_df):
            imputed_df=imputed_df.drop(column, 1)
        for column in columns:

        if (imputed_df[column].dtype == 'float64' or imputed_df[column].dtype == 'int64'):
            imp = SimpleImputer(missing_values=np.nan, strategy='mean')
            imputed_df[column] = imp.fit_transform(imputed_df[column].values.reshape(-1, 1))

        if (imputed_df[column].dtype == 'object'):
            # impute missing values for categorical variables
            imp = SimpleImputer(missing_values=None, strategy='constant', fill_value='missing')
            imputed_df[column] = imp.fit_transform(imputed_df[column].values.reshape(-1, 1))
            imputed_df[column] = imputed_df[column].astype('str')
            le = LabelEncoder()
            #print(imputed_df[column].unique())

            le.fit(imputed_df[column].unique())
            # print(le.classes_)
            imputed_df[column] = le.transform(imputed_df[column])



    X = imputed_df.drop(['RISK_MM', 'RAINTOMORROW'], axis=1)
    y = imputed_df['RAINTOMORROW']

    # Create a decision tree
    dt = DecisionTreeClassifier(max_depth=5)

    cvscores_3 = cross_val_score(dt, X, y, cv=3)

    self.output(ds_size, location, np.mean(cvscores_3))

As you want to apply the function on each and every data slice, choose parallel=True in the module invocation.

output_signature = {'DATASET_SIZE': 'int', 'LOCATION':'str', 'CLASSIFIER_ACCURACY':'double'}

nz_fun_tapply = NZFunTApply(df=idadf, code_str=code_str_host_spus, fun_name ="decision_tree_ml", parallel=True, output_signature=output_signature)
result = nz_fun_tapply.get_result()

Notice that you have multiple rows in result (corresponding to the number of data slices generated at the time of table creation). The first column is the size of the dataset, the second column is the location of the first record in the dataset, and the third column is the accuracy of the classifier built.

     DATASET_SIZE LOCATION  CLASSIFIER_ACCURACY
0         23673   Albury             0.824822
1         23734   Albury             0.827126
2         23686   Albury             0.813898
3         23706   Albury             0.818485
4         23739   Albury             0.832175
5         23655   Albury             0.826168

Applying functions on the complete dataset

You might want to apply a function to the entire dataset and not on slice basis. This means that the data needs to be aggregated in a single place before the function can be applied. This is not an optimal scenario but this option (parallel=False) is provided for the sake of completeness.

output_signature = {'DATASET_SIZE': 'int', 'LOCATION':'str', 'CLASSIFIER_ACCURACY':'double'}
nz_fun_tapply = NZFunTApply(df=idadf, code_str=code_str_host_spus, fun_name ="decision_tree_ml", parallel=False, output_signature=output_signature)
result = nz_fun_tapply.get_result()

Because you chose parallel=False (meaning you want to perform the function on the complete dataset), you can see only one row in the output. The first column is the size of the dataset, the second column is the location of the first record in the dataset, and the third column is the accuracy of the classifier built.

DATASET_SIZE        LOCATION           CLASSIFIER_ACCURACY
  142193            Albury             0.827115

Note: For functions which are complex, it is better to first test those on the client before running them on the server. You could download a small subset (for example, 1000 records) with a select query (select * from weather limit 1000), test the function on the client dataframe, and then push the function to the server to run against the entire dataset.