{"id":46626,"date":"2021-01-14T00:00:00","date_gmt":"2021-01-14T08:00:00","guid":{"rendered":"https:\/\/griddb-linux-hte8hndjf8cka8ht.westus-01.azurewebsites.net\/blog\/predicting-credit-card-attrition-using-python-and-griddb\/"},"modified":"2025-11-13T12:55:07","modified_gmt":"2025-11-13T20:55:07","slug":"predicting-credit-card-attrition-using-python-and-griddb","status":"publish","type":"post","link":"https:\/\/www.griddb.net\/en\/blog\/predicting-credit-card-attrition-using-python-and-griddb\/","title":{"rendered":"Predicting Credit Card Attrition Using Python and GridDB"},"content":{"rendered":"

Data Analysis aims to extract useful information from data and then aids the decision making process. However, the raw data we get from external sources, be it mobile devices or sensors, has many outliers. Moreover, the data may be high dimensional, so it becomes hard to interpret the data's summary statistics. As a result, nowadays, data analysis is the umbrella term for the process of getting raw data to getting human interpretable results. Thus, data analysis consists of data cleaning, transforming and modelling such that meaningful information can be extracted from it. <\/p>\n

FULL SOURCE CODE <\/a><\/p>\n

The most crucial precursor of a good data analysis system has a reliable database. Our database should be scalable, and we should be able to query large datasets easily from it. One such modern database that allows for all these functionalities is GridDB. GridDB is a high performance and can easily be integrated with many programming languages. In this post, we will analyze some data with python and GridDB. As there are many types of analyses we can do, we will focus on a random forest model in this post.<\/p>\n

GridDB setup<\/h3>\n

This video<\/a> has the setup guide for the GridDB python client.<\/p>\n

Python libraries<\/h3>\n

We will use python 3.6<\/code> and pandas to do our analysis.
\nTo install libraries we use the following command:<\/p>\n

\n
pip install <\/span>pandas\npip install <\/span>scikit-learn\n<\/span>pip install <\/span>plotly\npip install <\/span>matplotlib\n<\/code><\/pre>\n<\/div>\n
import libraries<\/p>\n
\n
import<\/span> pandas as<\/span> pd\nimport<\/span> matplotlib.pyplot as<\/span> plt\nimport<\/span> plotly.graph_objs as<\/span> go\nfrom<\/span> plotly.subplots import<\/span> make_subplots\nfrom<\/span> sklearn.model_selection import<\/span> train_test_split,cross_val_score\nfrom<\/span> sklearn.ensemble import<\/span> RandomForestClassifier\nfrom<\/span> sklearn.metrics import<\/span> f1_score as<\/span> f1\n<\/code><\/pre>\n<\/div>\n
Data Collection<\/h3>\n
GridDB provides an excellent interface to access data. The GridDB python client blog<\/a> goes into great detail to link a GridDB database and push all the data to a pandas data frame.<\/p>\n
For this analysis we will use credit card data to predict attrition or churn. The data can be found here<\/a>.<\/p>\n
We can set up GridDB as our database by instantiating the container and dumbing all the data into a pandas dataframe.<\/p>\n
\n
import<\/span> griddb_python as griddb\n# Initialize container<\/span>\ngridstore<\/span> = factory.get_store(host=<\/span> host, port=port,<\/span> \n            cluster_name=cluster_name,<\/span> username=uname,<\/span> \n            password=pwd)<\/span>\n\nconInfo<\/span> = griddb.ContainerInfo(\"attrition\"<\/span>,\n                    [[\"CLIENTNUM\"<\/span>, griddb.Type.LONG],\n                    [\"Gender\"<\/span>,griddb.Type.STRING],\n              .... #for all 23 variables      <\/span>\n                    griddb.ContainerType.COLLECTION, True)\n\ncont<\/span> = gridstore.put_container(conInfo)    \ncont.create_index(\"CLIENTNUM\"<\/span>, griddb.IndexType.DEFAULT)\n<\/code><\/pre>\n<\/div>\n
We can retrive data from GridDB using the following SQL query:<\/p>\n
\n
  query<\/span> = cont.query<\/span>(\"select *\"<\/span>)\n<\/code><\/pre>\n<\/div>\n
The data analysis pipeline has the following steps:<\/p>\n
    \n
  1. \n     Data exploration<\/strong><\/a>: We first run some summary statistics on the various variables we have and try to understand the correlation with the dependent variable, i.e. survival. We also clean the dataset to remove outliers, if any.\n  <\/li>\n
  2. \n     Feature Engineering<\/strong><\/a>: We will then select the features that can be used for modelling. We can create new features either from existing data or open-source resources.\n  <\/li>\n
  3. \n     Modelling<\/strong><\/a>: We then use a machine learning model, random forest, in our case. We would first split the data into a test set and a training set. Typically we train the model on the training data and evaluate it on the test set. Sometimes we either have a validation set or do cross-validation to tune the hyperparameters of the model.\n  <\/li>\n
  4. \n    Evaluation<\/strong><\/a>: Finally, we will use the model for prediction and analyze its performance.\n  <\/li>\n<\/ol>\n
    Data Collection and Exploration<\/h3>\n
    We load the data using pandas<\/code>. We remove the last two columns as they are the results of a different classifier.<\/p>\n
    \n
    data<\/span> = pd.read_csv('\/kaggle<\/span>\/input<\/span>\/credit<\/span>-card<\/span>-customers<\/span>\/BankChurners<\/span>.csv'<\/span>)<\/span>\ndata<\/span> = data<\/span>[data<\/span>.columns[:-2]]<\/span>\n<\/code><\/pre>\n<\/div>\n
    We first create summary statistics of some of the variables. Ideally, we would check every variable, but for brevity, we showcase a few important ones.<\/p>\n
    Attrition_Flag:<\/h4>\n
    \n
    attdata = data.groupby(['Attrition_Flag']).count()[[\"CLIENTNUM\"]].reset_index()\nattdata['percentage'] = attdata['CLIENTNUM']\/attdata['CLIENTNUM'].sum()\nattdata[attdata.Attrition_Flag  == \"Attrited Customer\"]<\/code><\/pre>\n<\/div>\n\n\n\n\n\n
    CLIENTNUM<\/th>\npercentage<\/th>\n<\/tr>\n<\/thead>\n
    1627<\/td>\n0.16066<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    <\/p>\n
    We have an attrition of about 16%.<\/p>\n
    Demographic Variables<\/h4>\n
    Gender<\/h5>\n
    \n
    genderdata = data.groupby(['Gender']).count()[[\"CLIENTNUM\"]].reset_index()\ngenderdata['percentage'] = genderdata['CLIENTNUM']\/genderdata['CLIENTNUM'].sum()\ngenderdata[genderdata.Gender  == \"F\"]<\/code><\/pre>\n<\/div>\n\n\n\n\n\n
    CLIENTNUM<\/th>\npercentage<\/th>\n<\/tr>\n<\/thead>\n
    5358<\/td>\n0.529081<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    <\/p>\n
    We see that there are 52.9% females. But the difference in the genders is not that significant.<\/p>\n
    Education<\/h5>\n
    \n
    data<\/span>.groupby(['Education_Level<\/span>']).count()[[\"CLIENTNUM<\/span>\"]].reset_index()<\/span>\n<\/code><\/pre>\n<\/div>\n
    Education_Level<\/h5>\n\n\n\n\n\n\n\n\n\n\n\n
    Type<\/th>\nNumber<\/th>\n<\/tr>\n<\/thead>\n
    College<\/td>\n1013<\/td>\n<\/tr>\n
    Doctorate<\/td>\n451<\/td>\n<\/tr>\n
    Graduate<\/td>\n3128<\/td>\n<\/tr>\n
    High School<\/td>\n2013<\/td>\n<\/tr>\n
    Post-Graduate<\/td>\n516<\/td>\n<\/tr>\n
    Uneducated<\/td>\n1487<\/td>\n<\/tr>\n
    Unknown<\/td>\n1519<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     We see about 70% of the customers are educated. <\/p>\n
    Income_Category<\/h5>\n
    \n
    data<\/span>.groupby(['Income_Category<\/span>']).count()[[\"CLIENTNUM<\/span>\"]].reset_index()<\/span>\n<\/code><\/pre>\n<\/div>\n\n\n\n\n\n\n\n\n\n
    Income_Category<\/th>\nNumber<\/th>\n<\/tr>\n<\/thead>\n
    $120K +<\/td>\n727<\/td>\n<\/tr>\n
    40k\u00e2\u02c6\u2019 60k<\/td>\n1790<\/td>\n<\/tr>\n
    60k\u00e2\u02c6\u2019 *80K<\/td>\n1402<\/td>\n<\/tr>\n
    Less than $40k<\/td>\n3561<\/td>\n<\/tr>\n
    Unknown<\/td>\n1112<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     We see that most people earn less than $40k.<\/p>\n
    Bank variables<\/h4>\n
    We will draw histograms for Months_on_book<\/strong>, 'Total_Relationship_Count<\/strong>',
    \n  'Months_Inactive_12_mon<\/strong>' and 'Credit_Limit<\/strong>'<\/p>\n
    \n
    fig = make_subplots(rows=2<\/span>, cols=2<\/span>)\ntr1=go<\/span>.Histogram(x<\/span>=data['Months_on_book'<\/span>],name='Months on book Box Plot'<\/span>)\ntr2=go<\/span>.Histogram(x<\/span>=data['Total_Relationship_Count'<\/span>],name='Total no. of products Histogram'<\/span>)\ntr3=go<\/span>.Histogram(x<\/span>=data['Months_Inactive_12_mon'<\/span>],name='number of months inactive Histogram'<\/span>)\ntr4=go<\/span>.Histogram(x<\/span>=data['Credit_Limit'<\/span>],name='Credit_Limit Histogram'<\/span>)\n\nfig.add_trace(tr1,row=1<\/span>,col<\/span>=1<\/span>)\nfig.add_trace(tr2,row=1<\/span>,col<\/span>=2<\/span>)\nfig.add_trace(tr3,row=2<\/span>,col<\/span>=1<\/span>)\nfig.add_trace(tr4,row=2<\/span>,col<\/span>=2<\/span>)\n\nfig.update_layout(height=700<\/span>, width=1200<\/span>, title_text=\"Distribution of bank variables\"<\/span>)\nfig.show()\n<\/code><\/pre>\n<\/div>\n
    \"\"<\/p>\n
    We see that the distribution of the total number of products is mostly uniform so ideally this variable can be removed from our analysis. Other variables show a lot of variation so we can keep them.<\/p>\n
    Card_Category<\/h4>\n
    \n
    data<\/span>.groupby(['Card_Category<\/span>']).count()[[\"CLIENTNUM<\/span>\"]].reset_index()<\/span>\n<\/code><\/pre>\n<\/div>\n\n\n\n\n\n\n\n\n
    Card_Category<\/th>\nTotal<\/th>\n<\/tr>\n<\/thead>\n
    Blue<\/td>\n9496<\/td>\n<\/tr>\n
    Gold<\/td>\n116<\/td>\n<\/tr>\n
    Platinum<\/td>\n20<\/td>\n<\/tr>\n
    Silver<\/td>\n555<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     We see that most customers use Blue<\/strong> so we can ignore this variable as well.<\/p>\n
    Feature Engineering<\/h3>\n
    We can ignore the client number<\/strong> for this analysis. However, if the bank had more data source then the client number<\/strong> could be used to match different datasets.<\/p>\n
    The attrition flag<\/strong> is our dependent variable as we want to predict attrition. We will code this as a 0,1 variable.<\/p>\n
    Next, we create dummy variables. Dummy variables essentially encode the variables of one category. We will leave one category out. Otherwise, we'll face the issue of collinearity. We create dummy variables from the Demographic variable. This is also known as Hot-One Encoding.<\/p>\n