<jupyter_start><jupyter_text>Introduction Credit score cards are a common risk control method in the financial industry. It uses personal information and data submitted by credit card applicants to predict the probability of future defaults and credit card borrowings. The bank is able to decide whether to issue a credit card to the applicant. Credit scores can objectively quantify the magnitude of risk.Credit card application is one of the most classic problems in the field of credit information, and it is also the most mature credit evaluation problem in practice. In recent years, various emerging consumer finance businesses have emerged one after another. Banking credit card centers are an important source of risk control talents in financial industries. The risk evaluation system they have established is ideologically and technically the same as credit card risk management. It can be said that learning the credit card risk evaluation model well will provide a valuable Important  key for us to fully understand the data analysis methods of the financial risk control industry. Generally speaking, credit score cards are based on historical data. Here we will go through detailed steps to develop a data-driven credit risk model in Python to predict and assign credit card elilibility status to existing borrowers. We will determine credit scores using a highly interpretable, easy to understand and implement scorecard that makes calculating the credit score a breeze.  Individual Datasets and Data Exploration  Application Record [[1]](https://www.kaggle.com/rikdifos/credit-card-approval-prediction) - By <NAME>  Dataset ExplainationThere are two tables, which are connected by ID. Application Record dataset contains appliers personal information, which you could use as features for predicting.![image-2.png](attachment:image-2.png)  Loading and displaying the dataset Application Record<jupyter_code>application_record = pd.read_csv('./CreditCardPrediction/application_record.csv')

display(application_record)<jupyter_output><empty_output><jupyter_text>Dropping columns as they do not have much of the role and impact in determining whether the person should be given a credit card or not. We identified these factors based on the research we did on identifying potential arguments, references to which are as [[8]](https://www.transunion.ca/what-affect-credit-score), [[9]](https://www.canada.ca/en/financial-consumer-agency/services/credit-reports-score/improve-credit-score.html),  [[10]](https://www.experian.com/blogs/ask-experian/credit-education/score-basics/what-affects-your-credit-scores/). Links to other factors are added to references. These columns include,  - CODE_GENDER - FLAG_OWN_CAR  - CNT_CHILDREN - NAME_EDUCATION_TYPE - NAME_FAMILY_STATUS - FLAG_WORK_PHONE - FLAG_PHONE  - FLAG_EMAIL - OCCUPATION_TYPE - CNT_FAM_MEMBERS<jupyter_code>application_record = application_record.drop(['CODE_GENDER', 'FLAG_OWN_CAR', 'CNT_CHILDREN', 'NAME_EDUCATION_TYPE', 
                                                 'NAME_FAMILY_STATUS', 'FLAG_WORK_PHONE', 'FLAG_PHONE', 'FLAG_EMAIL',
                                                 'OCCUPATION_TYPE', 'CNT_FAM_MEMBERS'], axis = 1)

display(application_record)<jupyter_output><empty_output><jupyter_text>Here we are formatting the DAYS_BIRTH column to the DATE_OF_BIRTH of the person by formatting it. DAYS_BIRTH column count backwards from current day (0), -1 means yesterday. Similarly we format DAYS_EMPLOYED column with a Y and N flag with Y being an indicator that person is employed and N implying a person being unemployed [[2]](https://stackoverflow.com/questions/40104946/how-to-get-date-after-subtracting-days-in-pandas).  For the column DAYS_EMPLOYED if positive, it means the person is currently unemployed. We renamed the column FLAG_OWN_REALTY to OWN_PROPERTY making the naming for understandable that the person owns a property. Further after making changes with the help of DAYS_BIRTH and DAYS_EMPLOYED columns we drop them as they are not required further.<jupyter_code>application_record['DATE_OF_BIRTH'] = pd.to_datetime('today').strftime("%d-%m-%Y")
application_record['DATE_OF_BIRTH'] = application_record['DATE_OF_BIRTH'].astype('datetime64')

application_record['DATE_OF_BIRTH'] = application_record['DATE_OF_BIRTH'] + application_record.DAYS_BIRTH.apply(lambda x: pd.Timedelta(x, unit='D'))

application_record['EMPLOYMENT_STATUS'] = np.where(application_record['DAYS_EMPLOYED'] < 0, 'Y', 'N')

application_record = application_record.rename(columns={'FLAG_OWN_REALTY': 'OWN_PROPERTY'})

application_record_data = application_record.drop(['DAYS_BIRTH', 'DAYS_EMPLOYED'], axis = 1)

display(application_record_data)<jupyter_output><empty_output><jupyter_text>Making connection with local database and showing schemas available(we will be inserting data to deshant_sachdeva schema)<jupyter_code>with open("password.txt") as f:
    passw = f.read()

# attempt a connection
try:
    connectionString = 'mysql+mysqlconnector://root:{}@localhost:3306/deshant_sachdeva'.format(passw)
    engine = sq.create_engine(connectionString)
    connection = engine.raw_connection()

except:
    print("Problem with connection!!!")
    connection.connect()
    
schema = pd.read_sql_query("SHOW SCHEMAS;", connection)

display(schema)<jupyter_output><empty_output><jupyter_text>Creating table application_record and columns associated with it. ID represents the primary key<jupyter_code>try:

    cursor = connection.cursor()

    createTableApplicationRecord = ("""CREATE TABLE IF NOT EXISTS `application_record` (
      `ID` INT(8),
      `OWN_PROPERTY` CHAR,
      `AMT_INCOME_TOTAL` FLOAT,
      `NAME_INCOME_TYPE` VARCHAR(255),
      `NAME_HOUSING_TYPE` VARCHAR(255),
      `FLAG_MOBIL` INT(2), 
      `DATE_OF_BIRTH` DATETIME,
      `EMPLOYMENT_STATUS` CHAR,
      PRIMARY KEY (ID)
    ) ENGINE=InnoDB;""")
    
    cursor.execute(createTableApplicationRecord)
except:
    print("Problem with connection!!!")
    connection.connect()<jupyter_output><empty_output><jupyter_text>Inserting data into table application_record<jupyter_code>application_record_data.to_sql(name="application_record", con = engine, schema="deshant_sachdeva", if_exists='replace', chunksize=10000, index=False)

print("Application record data added.")<jupyter_output>Application record data added.<jupyter_text>Displaying tables<jupyter_code>tables = pd.read_sql_query("SHOW TABLES;", connection)

display(tables)<jupyter_output><empty_output><jupyter_text>Displaying all of the data inserted into table application_record<jupyter_code>select_all = pd.read_sql_query("""SELECT * FROM application_record;""" , connection)

display(select_all)<jupyter_output><empty_output><jupyter_text>Here in this query we are going to filter records of people that are employed and also they can be pensioners. In other ways we filter out records who have some associated income recorded. This is a necessary filter as we do not want to issue credit card to a person who can not afford to have it in first place.<jupyter_code>select_with_some_income = pd.read_sql_query("""SELECT * FROM application_record where EMPLOYMENT_STATUS = 'Y'
                                                    and NAME_INCOME_TYPE <> 'Pensioner';""", connection)

display(select_with_some_income)<jupyter_output><empty_output><jupyter_text>This query filters the records of people having some specific income and also have their own property. This query will help us figure out people who have a less probability to default on credit card payment as they might be less likely to take a home loan in near future.<jupyter_code>select_with_own_property_and_income = pd.read_sql_query("""SELECT * FROM application_record where EMPLOYMENT_STATUS = 'Y'
                                                    and NAME_INCOME_TYPE <> 'Pensioner' and OWN_PROPERTY = 'Y';""", connection)

display(select_with_own_property_and_income)<jupyter_output><empty_output><jupyter_text>Here in this query we want to filter out people having a mobile number. Mobile number has become an important part of banking and is n=one of the methods by which we identify and notify potential credit card users and their due payments. It is highly likely that a person might miss his/her payment if they are not notified about it. Hence to decrease such risks we will be issuing a credit card to person who has a mobile number.<jupyter_code>remove_people_with_no_mobileno = pd.read_sql_query("""SELECT * FROM application_record where EMPLOYMENT_STATUS = 'Y'
                                                    and NAME_INCOME_TYPE <> 'Pensioner' and OWN_PROPERTY = 'Y' and 
                                                    FLAG_MOBIL = 1;""", connection)

display(remove_people_with_no_mobileno)<jupyter_output><empty_output><jupyter_text>This query filters the records of people having some specific income, have their own property and are living in their own house or appartment. This query will help us figure out people who will have less probability to default on credit card payment as they are less likely to spend their income to rental expenses.<jupyter_code>remove_people_with_rented_apartment = pd.read_sql_query("""SELECT * FROM application_record where EMPLOYMENT_STATUS = 'Y'
                                                    and NAME_INCOME_TYPE <> 'Pensioner' and OWN_PROPERTY = 'Y' and 
                                                    FLAG_MOBIL = 1 and NAME_HOUSING_TYPE <> 'Rented apartment';""", connection)

display(remove_people_with_rented_apartment)<jupyter_output><empty_output><jupyter_text>Here we are going to find the average of all the values from column AMT_INCOME_TOTAL. We are going to take this amount as a criteria whether a person should be given a credit card or not.<jupyter_code>avg_total_income = pd.read_sql_query("""SELECT AVG(AMT_INCOME_TOTAL) FROM application_record;""", connection)

display(avg_total_income)<jupyter_output><empty_output><jupyter_text>Here in this nested query we are comparing the AMT_INCOME_TOTAL and determining the amount greater then the average as a criteria to predict whether a person is eligible for credit card or not. On the individual front we are going to consider the result from this query and we will be using another dataset credit_data to strengthen the predictability criteria. We will be merging both the datasets or we will be queries from both the datasets to determine the result.<jupyter_code>eligible_people_for_creditcard = pd.read_sql_query("""SELECT * FROM application_record where EMPLOYMENT_STATUS = 'Y'
                                                    and NAME_INCOME_TYPE <> 'Pensioner' and OWN_PROPERTY = 'Y' and 
                                                    FLAG_MOBIL = 1 and NAME_HOUSING_TYPE <> 'Rented apartment'
                                                    and AMT_INCOME_TOTAL > (SELECT AVG(AMT_INCOME_TOTAL) 
                                                    FROM application_record);""", connection)

display(eligible_people_for_creditcard)<jupyter_output><empty_output><jupyter_text>This query gives the total count of the records present after the filters are applied.<jupyter_code>eligible_people_for_creditcard_count = pd.read_sql_query("""SELECT Count(*) FROM application_record where EMPLOYMENT_STATUS = 'Y'
                                                    and NAME_INCOME_TYPE <> 'Pensioner' and OWN_PROPERTY = 'Y' and 
                                                    FLAG_MOBIL = 1 and NAME_HOUSING_TYPE <> 'Rented apartment'
                                                    and AMT_INCOME_TOTAL > (SELECT AVG(AMT_INCOME_TOTAL) 
                                                    FROM application_record);""", connection)

display(eligible_people_for_creditcard_count)<jupyter_output><empty_output><jupyter_text>Credit Record [[1]](https://www.kaggle.com/rikdifos/credit-card-approval-prediction) - By <NAME>  Dataset ExplainationThere are two tables, which are connected by ID. Credit Record dataset records users' behaviors of credit card.![image.png](attachment:image.png)  Loading and displaying the dataset Credit Record<jupyter_code>credit_record = pd.read_csv('./CreditCardPrediction/credit_record.csv')

display(credit_record)<jupyter_output><empty_output><jupyter_text>SQL Queries 1. Cleanup and organization the dataset From the original dataset, each ID has different number of monthly balances, so I try to restructure the dataframe by groupby each ID. Before doing restructure, I need to check that each ID has different number of monthly balance or not? The reason why do that, if each ID has same number of monthly balance, I can restructure the dataframe by groupby() mean value of monthly balance.<jupyter_code>group_by_creditrecord_id = pd.read_sql_query("""SELECT ID, COUNT(ID)
                                                FROM deshant_sachdeva.credit_record
                                                GROUP BY ID;""", connection)

display(group_by_creditrecord_id)<jupyter_output><empty_output><jupyter_text>SQL Queries 2. Once I confirmed that each ID has different number of ID by the SQL. Hence, I are going to do restructure the dataframe by gourpby each ID.<jupyter_code>grouping_month_id = pd.read_sql_query("""SELECT ID, MONTHS_BALANCE, STATUS
FROM  deshant_sachdeva.credit_record
GROUP BY ID, MONTHS_BALANCE;""", connection)

display(grouping_month_id)<jupyter_output><empty_output><jupyter_text>SQL Queries 3. Each ID has different number of monthly balances, so it's hard to make a decision the client who is credit card company approval the applicants base on the monthly balance. All ID has a credit record from our dataset, so we think correcting the data which is the most recently monthly balance's status each ID after that using an inner or outer join between two datasets and various groupings, we will structure our data in SQL. It will help to client can make a decision the application of credit card.<jupyter_code>latest_month = pd.read_sql_query("""SELECT ID, MAX(MONTHS_BALANCE), STATUS
FROM  deshant_sachdeva.credit_record
GROUP BY ID;""", connection)

display(latest_month)<jupyter_output><empty_output><jupyter_text>SQL Queries 4. If applicants complain that it is unfair reject or approval by only last month of balances, so we should think other ways. In other way, we can drop the data that applicants who have any overdue or bad debts by their credit status. If applicants have any overdue or bad debts before, the client will reject their credit card application. Based on the dataset, starting from 2 is that overdue or bad debts at STATUS. For example, the value of STATUS is over 1.5, applicants will be rejected their application.<jupyter_code>filter_applications = pd.read_sql_query("""SELECT ID, MONTHS_BALANCE, STATUS
FROM deshant_sachdeva.credit_record
WHERE STATUS not like '2' and '3' and '4' and '5'
GROUP BY ID;""", connection)

display(filter_applications)
# citation from W3 Schools - (6)<jupyter_output><empty_output><jupyter_text>SQL Queries 5. Moreover, each ID has different number of monthly balances, so we can calculate the average of STATUS and we set up the criteria of average of STATUS. If applicants who have over our criteria of average of STATUS, applicants will get reject their credit card application.<jupyter_code>avg_status = pd.read_sql_query("""SELECT ID, MONTHS_BALANCE, AVG(STATUS)
FROM deshant_sachdeva.credit_record
WHERE STATUS
GROUP BY ID;""", connection)

display(avg_status)
# citation from W3 Schools - (7)<jupyter_output><empty_output><jupyter_text>Final merging  Credit record final data<jupyter_code>credit_records_final_data = pd.read_sql_query("""SELECT DISTINCT(ID), STATUS
FROM deshant_sachdeva.credit_record
WHERE (STATUS = 'C' or STATUS = 'X') and MONTHS_BALANCE = 0
GROUP BY ID;""", connection)

display(credit_records_final_data)<jupyter_output><empty_output><jupyter_text>Merging Application record and Credit record dataset  Credit record Final Data<jupyter_code>credit_records_final_data = pd.read_sql_query("""SELECT DISTINCT(ID), STATUS
FROM deshant_sachdeva.credit_record
WHERE (STATUS = 'C' or STATUS = 'X') and MONTHS_BALANCE = 0
GROUP BY ID;""", connection)

display(credit_records_final_data)<jupyter_output><empty_output><jupyter_text>Application record Final Data<jupyter_code>eligible_people_for_creditcard = pd.read_sql_query("""SELECT * FROM application_record where EMPLOYMENT_STATUS = 'Y'
                                                    and NAME_INCOME_TYPE <> 'Pensioner' and OWN_PROPERTY = 'Y' and 
                                                    FLAG_MOBIL = 1 and NAME_HOUSING_TYPE <> 'Rented apartment'
                                                    and AMT_INCOME_TOTAL > (SELECT AVG(AMT_INCOME_TOTAL) 
                                                    FROM application_record);""", connection)

display(eligible_people_for_creditcard)<jupyter_output><empty_output><jupyter_text>Final Result<jupyter_code>final_result_set = pd.read_sql_query("""SELECT app_rec.ID, credit_rec.STATUS, app_rec.OWN_PROPERTY, app_rec.AMT_INCOME_TOTAL, 
app_rec.NAME_INCOME_TYPE, NAME_HOUSING_TYPE, app_rec.FLAG_MOBIL, 
app_rec.DATE_OF_BIRTH, app_rec.EMPLOYMENT_STATUS
FROM deshant_sachdeva.application_record as 
app_rec

INNER JOIN 

deshant_sachdeva.credit_record as credit_rec
on app_rec.ID = credit_rec.ID

where app_rec.EMPLOYMENT_STATUS = 'Y' and 
app_rec.NAME_INCOME_TYPE <> 'Pensioner' and app_rec.OWN_PROPERTY = 'Y' and 
app_rec.FLAG_MOBIL = 1 and app_rec.NAME_HOUSING_TYPE <> 'Rented apartment'
and 
app_rec.AMT_INCOME_TOTAL > (SELECT AVG(AMT_INCOME_TOTAL) FROM deshant_sachdeva.application_record) 
and 
(credit_rec.STATUS = 'C' or credit_rec.STATUS = 'X') and 
credit_rec.MONTHS_BALANCE = 0
GROUP BY credit_rec.ID;""", connection)

display(final_result_set)<jupyter_output><empty_output><jupyter_text>Final Count<jupyter_code>final_count = pd.read_sql_query("""SELECT count(*)
FROM deshant_sachdeva.application_record as 
app_rec

INNER JOIN 

deshant_sachdeva.credit_record as credit_rec
on app_rec.ID = credit_rec.ID

where app_rec.EMPLOYMENT_STATUS = 'Y' and 
app_rec.NAME_INCOME_TYPE <> 'Pensioner' and app_rec.OWN_PROPERTY = 'Y' and 
app_rec.FLAG_MOBIL = 1 and app_rec.NAME_HOUSING_TYPE <> 'Rented apartment'
and app_rec.AMT_INCOME_TOTAL > (SELECT AVG(AMT_INCOME_TOTAL) 
FROM deshant_sachdeva.application_record) and 
(credit_rec.STATUS = 'C' or credit_rec.STATUS = 'X') and 
credit_rec.MONTHS_BALANCE = 0
;""", connection)

display(final_count)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07161318113288821, 'fraction_numerical': 0.003661425802283007, 'mean_word_length': 4.395990703079605, 'pattern_counts': {'":': 0, '<': 122, '<?xml version=': 0, '>': 126, 'https://': 6, 'lorem ipsum': 0, 'www.': 5, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 44, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '26', 'dir': 'jupyter-structured-clean-dedup', 'id': '141046', 'license': 'MIT', 'n_tokens_mistral': 5522, 'n_tokens_neox': 5219, 'n_words': 1732, 'path': 'Code/CreditCardPrediction.ipynb', 'repo_name': 'deshant19/Credit-Card-Prediction', 'size': '18586'}