Predict credit card approvals with Netezza Python in-database analytics

This article presents an example analysis of data on granting credit cards to customers based on their personal data. A machine learning model was created to estimate the risk associated with granting a credit card and help to decide whether an applicant can receive it. This complete use case scenario on credit card data was accomplished with Netezza Python in-database analytics (nzpyida), which includes uploading data to a database, creating a connection between the database and client side, executing functions, and building a model directly in the database.

Historical data of credit card applicants is stored on an external Netezza Performance Server to which the client server connects via an ODBC or JDBC connection. This connection, along with the nzpyida package, allows data from Netezza Performance Server to be accessible on client side. The nzpyida package also allows for various operations (data transformation, model building, etc.) directly in the database, which increases the speed of operations, and at the same time allows the use of custom Python libraries, not limiting the user to the possibilities offered by the database.

Creating a JDBC connection between client and Netezza Performance Server

Creating a connection between the database and the client side will allow you to load data into the selected environment on the client side, but also perform functions and build a model directly in the database. Steps to establish a JDBC connection:

1. On Netezza Performance Server, install any INZA version starting from 11.2.1.0.
2. On client side, install nzpyida: pip install nzpyida
3. Copy the nzjdbc3.jar file from Netezza Performance Server to client.
4. Test the connection:

java -jar nzjdbc3.jar -t

TEST CONNECTION

Host(localhost)>ip_address
Port(5480)>
Database(system)>credit_card
User(admin)>
Password>
Schema>
Security Level(preferredUnsecured)>
Certificate file>
Login module>
Kerberos SSO delegation(false)>
Application name>
Client user>
Client host name>
User Domain Forest(false)>

Success
NPS Product Version: Release 11.2.1.0 [Build 30]
Netezza JDBC Driver Version: Release 11.0.7.0 driver [build 201005-159]

Then add the JDBC driver to CLASSPATH on the client side: export CLASSPATH=/root/nzjdbc3.jar:$CLASSPATH. You can also do it inside the notebook: %env CLASSPATH=/root/nzjdbc3.jar.

Uploading data to the database

The data used in this example comes from Kaggle and was downloaded as a CSV file. Below are the steps that allow you to upload the file with data to the Netezza Performance Server database using the nzload command:

1. Upload the dataset to Netezza Performance Server.
2. Create the table for the data:

   CREDIT_CARD.ADMIN(ADMIN)=> create table credit_card (ID INTEGER, Gender INTEGER, Own_car INTEGER, Own_property INTEGER, Work_phone INTEGER, Phone INTEGER, Email INTEGER, Unemployed INTEGER, Num_children INTEGER, Num_family INTEGER, Account_length INTEGER, Total_income REAL, Age REAL, Years_employed REAL, Income_type VARCHAR(30), Education_type VARCHAR(40), Family_status VARCHAR(30), Housing_type VARCHAR(30), Occupation_type VARCHAR(40), Target INTEGER);
   

3. Load the dataset into the table: nzload -df credit_card_approval.csv -t credit_card -db credit_card -pw password -nullValue NA -boolStyle Yes_No -skipRows 1 -delim ',' Load session of table 'CREDIT_CARD' completed successfully
4. Check the data: CREDIT_CARD.ADMIN(ADMIN)=> select * from credit_card;

Analyzing the data

When the data is uploaded to the database and the connection between Netezza Performance Server and the client side has been established, you can load the data into the selected environment and start analyzing it using any of the available tools. In this example, Jupyter Notebook was selected for creating the code and visualizations.

Connecting to the database tables with nzpyida

In the dsn string, <ip_address> is the address of the Netezza server, <port> is the default port for Netezza Performance Server, credit_card is the name of the database, and in the IdaDataFrame function, CREDIT_CARD is the name of the table:

from nzpyida import IdaDataBase, IdaDataFrame

dsn = "jdbc:netezza://<ip_address>:<port>/credit_card"

idadb = IdaDataBase(dsn, uid="admin", pwd="password")

idadf = IdaDataFrame(idadb, 'CREDIT_CARD')

Displaying the data properties

The nzpyida package offers many built-in SQL translations that represent the properties of the data used. Examples of these functions are as follows:

idadf.head()

idadf.describe()

idadf.count()

ID                 9709
GENDER             9709
OWN_CAR            9709
OWN_PROPERTY       9709
WORK_PHONE         9709
PHONE              9709
EMAIL              9709
UNEMPLOYED         9709
NUM_CHILDREN       9709
NUM_FAMILY         9709
ACCOUNT_LENGTH     9709
TOTAL_INCOME       9709
AGE                9709
YEARS_EMPLOYED     9709
INCOME_TYPE        9709
EDUCATION_TYPE     9709
FAMILY_STATUS      9709
HOUSING_TYPE       9709
OCCUPATION_TYPE    9709
TARGET             9709
dtype: int64

idadf.min()

ID                              5008804
GENDER                                0
OWN_CAR                               0
OWN_PROPERTY                          0
WORK_PHONE                            0
PHONE                                 0
EMAIL                                 0
UNEMPLOYED                            0
NUM_CHILDREN                          0
NUM_FAMILY                            1
ACCOUNT_LENGTH                        0
TOTAL_INCOME                      27000
AGE                             20.5042
YEARS_EMPLOYED                        0
INCOME_TYPE        Commercial associate
EDUCATION_TYPE          Academic degree
FAMILY_STATUS            Civil marriage
HOUSING_TYPE            Co-op apartment
OCCUPATION_TYPE             Accountants
TARGET                                0
dtype: object

idadf.max()

ID                                       5150479
GENDER                                         1
OWN_CAR                                        1
OWN_PROPERTY                                   1
WORK_PHONE                                     1
PHONE                                          1
EMAIL                                          1
UNEMPLOYED                                     1
NUM_CHILDREN                                  19
NUM_FAMILY                                    20
ACCOUNT_LENGTH                                60
TOTAL_INCOME                           1.575e+06
AGE                                      68.8638
YEARS_EMPLOYED                           43.0207
INCOME_TYPE                              Working
EDUCATION_TYPE     Secondary / secondary special
FAMILY_STATUS                              Widow
HOUSING_TYPE                        With parents
OCCUPATION_TYPE             Waiters/barmen staff
TARGET                                         1
dtype: object

idadf.unique('INCOME_TYPE')

0                 Working
1    Commercial associate
2               Pensioner
3           State servant
4                 Student
Name: INCOME_TYPE, dtype: object

idadf.unique('EDUCATION_TYPE')

0                 Higher education
1    Secondary / secondary special
2                Incomplete higher
3                  Lower secondary
4                  Academic degree
Name: EDUCATION_TYPE, dtype: object

idadf.summary()

import seaborn as sns
import numpy as np
import matplotlib.pyplot as plt
plt.figure(figsize=(10,10))
sns.heatmap(data=idadf.corr())

Visualizing the data

The best way to get to know the data is to visualize it. In the credit_card dataset, there are three types of data:

• Binary features, where 0 and 1 describe whether the feature is true or not (for example, whether someone owns a car).
• Categorical features, in which there is a specific number of categories in which a given feature is divided (for example, education type is divided into five categories: higher education, secondary/secondary special, incomplete higher, lower secondary, and academic degree).
• Numeric features, where the value is any number, integer, or float (for example, number of children is an integer equal to 0 or more).

For visualizing the data, the table can loaded as pandas dataframe using the as_dataframe() function available in nzpyida, but this approach will cause the data to be copied from the database to the client, and with their large size may take some time. Fortunately, it is not necessary: The columns with the data needed for analysis and creating graphs can be read from the database separately in two different ways:

• Using column name in double square brackets, which returns IdaDataFrame, and combining it with the as_dataframe() function to display the data. Here, only one column will be copied to the client, so it won't take as long as downloading the whole table:

idadf[['AGE']] <nzpyida.frame.IdaDataFrame at 0x7fe6247f0be0> idadf[['AGE']].as_dataframe()

0       32.868572
1       58.793816
2       52.321404
3       61.504341
4       46.193966
          ...
9704    56.400883
9705    43.360233
9706    52.296761
9707    33.914455
9708    25.155890
Name: CREDIT_CARD, Length: 9709, dtype: float64

• Using the ida_query() function with the appropriate SQL command, which will directly return the desired column from the table:

idadf.ida_query('select AGE from CREDIT_CARD;')

0       32.868572
1       58.793816
2       52.321404
3       61.504341
4       46.193966
          ...
9704    56.400883
9705    43.360233
9706    52.296761
9707    33.914455
9708    25.155890
Name: AGE, Length: 9709, dtype: float64

Multiple columns are used in this analysis, including a column() function, which uses ida_query() to select a column from a specific table.

def column(column_name, df=idadf, table='credit_card'):
    return df.ida_query("select {} from {};".format(column_name, table))

Binary features

In the credit card data, there are seven binary features, which describe gender, whether the client owns a car, property, phone, work phone, has an email, or is unemployed. The graphs show the size of each group.

fig, ax = plt.subplots(2,3,figsize= (17,10))
sns.countplot(x=column('gender'), ax=ax[0,0])
sns.countplot(x=column('own_car'), ax=ax[0,1])
sns.countplot(x=column('own_property'), ax=ax[0,2])
sns.countplot(x=column('work_phone'), ax=ax[1,0])
sns.countplot(x=column('unemployed'), ax=ax[1,1])
sns.countplot(x=column('email'), ax=ax[1,2])
fig.show()

Categorical features

There are five features divided into specific categories: income, marital status, education, way of living , and occupation. On the charts presenting belonging to given groups, it can be seen that the largest number of people are working, married, living in their own house/apartment and having secondary/secondary special education:

fig, ax = plt.subplots(1,2, figsize= (20,7))
sns.countplot(x=column('income_type'), ax=ax[0])
sns.countplot(x=column('family_status'), ax=ax[1])

fig, ax = plt.subplots(1,2, figsize= (20,7))

sns.countplot(x=column('education_type'), ax=ax[0])
sns.countplot(x=column('housing_type'), ax=ax[1])

fig.autofmt_xdate(rotation=20)

sns.set(rc={'figure.figsize':(15,5)})
sns.countplot(x=column('occupation_type'))
plt.xticks(rotation=40, ha="right");

Numeric features

In the dataset, there is also numeric data. The first is the length of the account. On the graph below, we see that the values are in the range of 0-60 months from when the account was opened:

sns.countplot(x=column('account_length'))

The next numeric features are the number of children and number of family members:

fig, ax = plt.subplots(1,2, figsize= (17, 3))

num_children = column('num_children')
sns.countplot(x=num_children, ax=ax[0])
num_family = column('num_family')
sns.countplot(x=num_family, ax=ax[1])

The plots for NUM_CHILDREN and NUM_CHILDREN show that there are some outliers in the data, which significantly differ from most of the data:

fig, ax = plt.subplots(1,2, figsize=(17, 3))

ids = column('id')
outliers1 = (num_children > 5)
sns.scatterplot(x=ids, y=num_children, hue=outliers1, legend=False, ax=ax[0])
outliers2 = (num_family > 7)
sns.scatterplot(x=ids, y=num_family, hue=outliers2, legend=False, ax=ax[1])

These outliers should be removed, so they don't affect the model. The removal process can be performed later, during preprocessing and before building the model.

This is how the data should look without detected outliers:

fig, ax = plt.subplots(1,2, figsize=(17, 3))

num_children = column('num_children')
num_children = num_children[num_children <= 5]
num_family = column('num_family')
num_family = num_family[num_family <= 7]
ids = column('id')
sns.scatterplot(x=ids, y=num_children, legend=False, ax=ax[0]);
sns.scatterplot(x=ids, y=num_family, legend=False, ax=ax[1]);

Next, there are AGE, TOTAL_INCOME, and YEARS_EMPLOYED columns, where the variation of values is large, so distribution and box plots have been created. For the age data, we see that there are no values that are significantly different from the rest:

fig, ax = plt.subplots(1,2, figsize=(20, 5))
sns.distplot(x=column('AGE'), ax=ax[0])
sns.boxplot(x=column('AGE'), ax=ax[1])

For income and years of employment data, the distributions are clearly skewed right, and there are many values that can be qualified as outliers. Due to their large number, a different approach should be chosen to deal with them, as opposed to removing it, as in the case of number of children and number of family members:

fig, ax = plt.subplots(1,2, figsize=(20, 5))

a = sns.distplot(x=column('TOTAL_INCOME'), ax=ax[0])
b = sns.boxplot(x=column('TOTAL_INCOME'), ax=ax[1])

fig, ax = plt.subplots(1,2, figsize=(20, 5))
sns.distplot(x=column('YEARS_EMPLOYED'), ax=ax[0])
sns.boxplot(x=column('YEARS_EMPLOYED'), ax=ax[1])

We don't want to lose the data because the values in these two columns are outliers, so assigning them separate values is the best option. A mean or median imputation, for example, can be used to limit the result to a number determined from the quantiles. When dealing with outliers, Interquartile Range (IQR)-based filtering is a popular alternative. Deleting extraneous data, can be completed while preparing the data prior to developing the model. Here's how the data should look after the filtering:

Total income

total_income = column('TOTAL_INCOME')
first = total_income.quantile(0.25)
third = total_income.quantile(0.75)
iqr = third - first
upper_limit = third + 1.5 * iqr
lower_limit = first - 1.5 * iqr

total_income.mask(total_income > upper_limit, upper_limit, inplace=True)
total_income.mask(total_income < lower_limit, lower_limit, inplace=True)

fig, ax = plt.subplots(1,2, figsize=(20, 5))
sns.distplot(x=total_income, ax=ax[0])
sns.boxplot(x=total_income, ax=ax[1])

Years of employment

years_employed = column('YEARS_EMPLOYED')
first = years_employed.quantile(0.25)
third = years_employed.quantile(0.75)
iqr = third - first
upper_limit = third + 1.5 * iqr
lower_limit = first - 1.5 * iqr

years_employed.mask(years_employed > upper_limit, upper_limit, inplace=True)
years_employed.mask(years_employed < lower_limit, lower_limit, inplace=True)

fig, ax = plt.subplots(1,2, figsize=(20, 5))
sns.distplot(x=years_employed, ax=ax[0])
sns.boxplot(x=years_employed, ax=ax[1])

The charts above show that there are no more outliers in the data, thanks to the filtering operation. Data processed in this way is clean and ready to be used to build a machine learning model.

Training a model

After you've analyzed the data, you may move on to creating a machine learning model that can predict whether a certain person would be authorized for a credit card based on the acquired personal data.

In order to build the model, the data first needs to be cleared from the outliers. Then, text data has to be transformed into numeric values; using LabelEncoder is a simple way of doing that. To perform encoding inside the database, the NZFunTApply function was used with a parallel=False parameter, which allows the calculations to be made on the entire dataset. If parallel=True were set, the encoding would be performed in parallel on each specified slice of the data, which would make it incorrect.

The function code was written in the form of a variable that stores the main function as a text (putting the function inside the """ is required in the notebook environment), the output signature was created indicating the new columns, NZFunTApply was applied on the selected table (idadf), and the output table was merged with the input data as merge_output_with_df set to True. The result of this operation is the new table called CREDIT_CARD_ENCODED.

from nzpyida.ae import NZFunTApply

idadf = IdaDataFrame(idadb, 'CREDIT_CARD')

if (idadb.exists_table("credit_card_encoded")):
    idadb.drop_table("credit_card_encoded")

function_code = """def apply_fun(self, df):
    from sklearn.preprocessing import LabelEncoder
    data = df.copy()


    #outliers handling

    data = data.loc[data['NUM_CHILDREN'] <= 5]
    data = data.loc[data['NUM_FAMILY'] <= 7]

    total_income = data['TOTAL_INCOME']
    first = total_income.quantile(0.25)
    third = total_income.quantile(0.75)
    iqr = third - first
    upper_limit = third + 1.5 * iqr
    lower_limit = first - 1.5 * iqr

    data['TOTAL_INCOME'].mask(data['TOTAL_INCOME'] > upper_limit, upper_limit, inplace=True)
    data['TOTAL_INCOME'].mask(data['TOTAL_INCOME'] < lower_limit, lower_limit, inplace=True)

    years_employed = data['YEARS_EMPLOYED']
    first = years_employed.quantile(0.25)
    third = years_employed.quantile(0.75)
    iqr = third - first
    upper_limit = third + 1.5 * iqr
    lower_limit = first - 1.5 * iqr

    data['YEARS_EMPLOYED'].mask(data['YEARS_EMPLOYED'] > upper_limit, upper_limit, inplace=True)
    data['YEARS_EMPLOYED'].mask(data['YEARS_EMPLOYED'] < lower_limit, lower_limit, inplace=True)


    #encoding
    for column in data.columns:
        if data[column].dtype=='object':
            le = LabelEncoder()
            le.fit(list(data[column].values))
            data[column] = le.transform(data[column].values)

    def print_output(x):
        row = [x['ID'], x['INCOME_TYPE'], x['EDUCATION_TYPE'], x['FAMILY_STATUS'], x['HOUSING_TYPE'], x['OCCUPATION_TYPE']]
        self.output(row)

    data.apply(print_output, axis=1)
"""

output_signature = {'ID': 'double', 'INCOME_TYPE_ENC': 'float', 'EDUCATION_TYPE_ENC': 'float', 'FAMILY_STATUS_ENC': 'float', 'HOUSING_TYPE_ENC': 'float', 'OCCUPATION_TYPE_ENC': 'float'}

nz_apply = NZFunTApply(df=idadf, code_str=function_code, fun_name='apply_fun', parallel=False, output_table="credit_card_encoded",output_signature=output_signature, merge_output_with_df=True)
idadf = nz_apply.get_result()

When the dataset is encoded, the next step is to split data into train and test groups, then separate target values from other features:

idadf.ida_query('drop table train_table, test_table if exists;')
idadf.train_test_split('train_table', 'test_table', 'ID', 0.8, 42)

0    7765.0
Name: SPLIT_DATA, dtype: float64

train = IdaDataFrame(idadb, 'TRAIN_TABLE')
test = IdaDataFrame(idadb, 'TEST_TABLE')

y_train = column('TARGET', df=train, table='train_table')
y_test = column('TARGET', df=test, table='test_table')

columns = 'id, gender, own_car, own_property, work_phone, phone, email, unemployed, num_children, num_family, account_length, total_income, age, years_employed, income_type_enc, education_type_enc, family_status_enc, housing_type_enc, occupation_type_enc'

X_train = train.ida_query('select {} from train_table'.format(columns))
X_test = test.ida_query('select {} from test_table'.format(columns))

After this, the model can be built. There are many libraries that offer different types of classifiers; in this example, RandomForestClassifier from scikit-learn was used:

from sklearn.ensemble import RandomForestClassifier
from sklearn.metrics import accuracy_score

model = RandomForestClassifier(n_estimators=250, max_depth=12, min_samples_leaf=16)
model.fit(X_train, y_train)
y_predict = model.predict(X_test)

print('Accuracy Score is {:.5}'.format(accuracy_score(y_test, y_predict)))

Accuracy Score is 0.87635

The model created this way can be further tuned and analyzed, checking feature importance, for example:

import pandas as pd

importances = model.feature_importances_
std = np.std([tree.feature_importances_ for tree in model.estimators_], axis=0)

forest_importances = pd.Series(importances, index=X_train.columns)

fig, ax = plt.subplots()
forest_importances.plot.bar(yerr=std, ax=ax)
ax.set_ylabel("Mean decrease in impurity")

However, this way of creating a model (building it in a notebook environment) can be replaced with calculations performed directly in the database. This will make modeling faster, and the table with the results will be created right away in Netezza Performance Server. All of this is possible with the nzpyida package and the functions available in it, such as NZFunApply, NZFunTApply, NZFunGroupedApply.

idadf = IdaDataFrame(idadb, 'CREDIT_CARD')

The scikit-learn package was chosen to create the model, so in order to use it in the database, it has to be installed with the NZInstall function:

from nzpyida.ae.install import NZInstall
package_name='sklearn'
nzinstall = NZInstall(idadb, package_name)
result = nzinstall.getResultCode()
print(result)

For the creation of the model, NZFunGroupedApply was used. As in the previous examples, the definition of the function also has to be set as a text variable in """. Inside the function, the label encoding is performed, then the target and features are separated and split into train and test groups. After that, a decision tree classifier is built, the predictions are made, and the accuracy is scored. The results are printed into a new table called PREDICTIONS:

from nzpyida.ae import NZFunGroupedApply

if (idadb.exists_table("predictions")):
    idadb.drop_table("predictions")

function_code="""def decision_tree_model(self, df):

    from sklearn.preprocessing import LabelEncoder
    from sklearn.model_selection import train_test_split
    from sklearn.tree import DecisionTreeClassifier
    from sklearn.metrics import accuracy_score

    data = df.copy()


    #outliers handling

    data = data.loc[data['NUM_CHILDREN'] <= 5]
    data = data.loc[data['NUM_FAMILY'] <= 7]

    total_income = data['TOTAL_INCOME']
    first = total_income.quantile(0.25)
    third = total_income.quantile(0.75)
    iqr = third - first
    upper_limit = third + 1.5 * iqr
    lower_limit = first - 1.5 * iqr

    data['TOTAL_INCOME'].mask(data['TOTAL_INCOME'] > upper_limit, upper_limit, inplace=True)
    data['TOTAL_INCOME'].mask(data['TOTAL_INCOME'] < lower_limit, lower_limit, inplace=True)

    years_employed = data['YEARS_EMPLOYED']
    first = years_employed.quantile(0.25)
    third = years_employed.quantile(0.75)
    iqr = third - first
    upper_limit = third + 1.5 * iqr
    lower_limit = first - 1.5 * iqr

    data['YEARS_EMPLOYED'].mask(data['YEARS_EMPLOYED'] > upper_limit, upper_limit, inplace=True)
    data['YEARS_EMPLOYED'].mask(data['YEARS_EMPLOYED'] < lower_limit, lower_limit, inplace=True)


    #encoding

    for column in data.columns:
        if data[column].dtype=='object':
            le = LabelEncoder()
            le.fit(list(data[column].values))
            data[column] = le.transform(data[column].values)

    #building the model

    y = data['TARGET']
    X = data.drop('TARGET', axis=1)

    X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

    model = DecisionTreeClassifier(max_depth=12, min_samples_split=8,random_state=42)
    model.fit(X_train, y_train)

    predictions = X_test.copy()
    y_predict = model.predict(X_test)

    accuracy = accuracy_score(y_test, y_predict)

    data_size = len(X_test)

    predictions['PREDICT_APPROVED'] = y_predict
    predictions['DATA_SIZE'] = data_size
    predictions['ACCURACY'] = accuracy

    def print_output(x):
        row = [x['ID'], x['PREDICT_APPROVED'], x['DATA_SIZE'], x['ACCURACY']]
        self.output(row)

    predictions.apply(print_output, axis=1)
"""


output_signature = {'ID':'float', 'PREDICT_APPROVED' :'float',  'DATA_SIZE':'float', 'ACCURACY':'float'}
nz_groupapply = NZFunGroupedApply(df=idadf, code_str=function_code, index='ACCOUNT_LENGTH', fun_name="decision_tree_model", output_table="predictions", output_signature=output_signature, merge_output_with_df=True)
result_idadf = nz_groupapply.get_result()
result = result_idadf.as_dataframe()
print(result)

      PREDICT_APPROVED  DATA_SIZE  ACCURACY       ID  GENDER  OWN_CAR  \
0                  1.0       42.0  0.833333  5008804       1        1
1                  0.0       37.0  0.891892  5008812       0        0
2                  1.0       42.0  0.738095  5008815       1        1
3                  0.0       28.0  0.642857  5008834       0        0
4                  0.0       32.0  0.781250  5008844       1        1
...                ...        ...       ...      ...     ...      ...
1961               0.0       29.0  0.827586  5150217       1        1
1962               0.0       26.0  0.692308  5150304       0        1
1963               0.0       42.0  0.738095  5150330       1        0
1964               0.0       44.0  0.863636  5150337       1        0
1965               0.0       44.0  0.863636  5150428       1        1

      OWN_PROPERTY  WORK_PHONE  PHONE  EMAIL  ...  ACCOUNT_LENGTH  \
0                1           1      0      0  ...              15
1                1           0      0      0  ...              20
2                1           1      1      1  ...               5
3                1           0      0      0  ...              44
4                1           0      1      0  ...              43
...            ...         ...    ...    ...  ...             ...
1961             1           0      0      0  ...              29
1962             1           0      0      0  ...              50
1963             1           0      0      0  ...               5
1964             1           0      0      0  ...              13
1965             1           1      0      0  ...              13

      TOTAL_INCOME        AGE  YEARS_EMPLOYED           INCOME_TYPE  \
0         427500.0  32.868572       12.435574               Working
1         283500.0  61.504341        0.000000             Pensioner
2         270000.0  46.193966        2.105450               Working
3         112500.0  30.029364        4.435410               Working
4         112500.0  56.132568       12.183686  Commercial associate
...            ...        ...             ...                   ...
1961      116100.0  27.751425        6.683231               Working
1962       76500.0  57.835548        0.000000             Pensioner
1963      153000.0  33.328541        0.846013               Working
1964      112500.0  25.155890        3.266323               Working
1965      112500.0  27.034094        4.517547               Working

                     EDUCATION_TYPE         FAMILY_STATUS       HOUSING_TYPE  \
0                  Higher education        Civil marriage   Rented apartment
1                  Higher education             Separated  House / apartment
2                  Higher education               Married  House / apartment
3     Secondary / secondary special  Single / not married  House / apartment
4     Secondary / secondary special               Married  House / apartment
...                             ...                   ...                ...
1961  Secondary / secondary special               Married  House / apartment
1962  Secondary / secondary special               Married  House / apartment
1963  Secondary / secondary special               Married  House / apartment
1964  Secondary / secondary special  Single / not married   Rented apartment
1965  Secondary / secondary special  Single / not married  House / apartment

     OCCUPATION_TYPE TARGET
0              Other      1
1              Other      0
2        Accountants      0
3              Other      0
4            Drivers      0
...              ...    ...
1961        Laborers      0
1962           Other      0
1963        Laborers      0
1964        Laborers      1
1965  Medicine staff      0

[1966 rows x 23 columns]

Predictions prepared this way are easy to analyze and visualize. You can quickly check what percentage of applications have been accepted and rejected:

idadf = IdaDataFrame(idadb, 'PREDICTIONS')

rejected = idadf.ida_query('select count(*) from predictions where predict_approved=0;')
approved = idadf.ida_query('select count(*) from predictions where predict_approved=1;')
values = [rejected.values[0],approved.values[0]]

plt.pie(values, labels=['Rejected','Approved'], colors=['#c94036', '#069111'], startangle = 45, explode=[0.05,0], autopct='%1.1f%%');

The data can be further analyzed, for example, in terms of the fairness of the model. As there is a sensitive variable (gender) in the credit card data, you can verify that the model was not influenced by it when classyfing the applications for credit card as rejected:

fig, ax = plt.subplots(1,2, figsize=(17, 3))
sns.countplot(x=idadf.ida_query('select gender from predictions where predict_approved=1'), ax=ax[0]).set(title='APPROVED');
sns.countplot(x=idadf.ida_query('select gender from predictions where predict_approved=0'), ax=ax[1]).set(title='REJECTED');

We see that the applications were classified as accepted for both women and men, and the gender distribution among accepted and rejected applications is similar, so it can be assumed that the created model was not influenced by gender in the classification.

Summary

The scenario on how to connect the database with the environment for data analysis on the client side by using the nzpyida package was presented. In this example, an external Netezza Performance Server and a Jupyter notebook created on a client machine were used to analyze the credit card approval data.

With the nzpyida package and the JDBC connection between the client and Netezza Performance Server, you learned how to easily access data in an external database, use available functions that help in data analysis by running SQL queries, and how to build a machine learning model directly in the database.

Credit card data was visualized and analyzed for outliers, which were then removed or adjusted. The prepared dataset was used to build a model and calculate predictions that can be further analyzed and tuned.