<jupyter_start><jupyter_text>Table of Contents1&nbsp;&nbsp;Kiva Dataset cleaning1.1&nbsp;&nbsp;Data description1.1.1&nbsp;&nbsp;Kaggle datasets1.1.2&nbsp;&nbsp;Additional Kiva Datasets1.2&nbsp;&nbsp;Preprocessing1.3&nbsp;&nbsp;Data transformation1.3.1&nbsp;&nbsp;Data cleaning1.3.2&nbsp;&nbsp;Gender reclassified and borrower_count1.4&nbsp;&nbsp;Preprocessing step 2 (for every dataframe)1.4.1&nbsp;&nbsp;MERGE 11.4.1.1&nbsp;&nbsp;missing values1.4.2&nbsp;&nbsp;THEMEREGION1.4.3&nbsp;&nbsp;REGLOC1.5&nbsp;&nbsp;Data transformation1.5.1&nbsp;&nbsp;Resampling  Kiva Dataset cleaning             Step      Explanation      Dataset size                  1      Original dataset (without the datasets df_theme,df_regloc and df_themeregion)      1,409,421                2      Remove refunded/fundraising/2011/2018      1,029,584              3      Create gender reclassified/borrower count/loan type and remove NaN gender reclassified      1,003,488              4      Merge with df_theme      744,217              5      Merge with df_regloc      614,010              6      Down-sampling of majority class      69,938        Data description  Kaggle datasets- df_loans: dataset containing loans posted in the period 2014-2017    * id, funded_amount, loan_amount, activity, sector, use ,country_code, country, region, currency, partner_id, posted_time, disbursed_time, funded_time, lender_count, tags, borrower_genders    * term_in_months: number of months over which loan was scheduled to be paid back    * repayment_interval:        - 'irregular': repayments due along harvest or market cycles that do not align neatly with calendar-based time periods.         - 'monthly':        - 'bullet': means the bulk of the repayment is due nearer to the end term date.        - 'weekly':    * date: date on which loan was posted → same as variable 'posted_time' so can be removed !!!- df_theme: Loan Themes are loan categories within a given partnership. For instance, a microfinance lender may have small business loans, farming loans, and health loans, all with different terms, prices, and repayment schedules, so those all count as different loan themes.    * id: Unique ID for loan (Loan ID)     * Loan Theme ID: Unique ID for loan theme     * Loan Theme Type: General description of the loan theme category     * Partner ID: Unique ID for field partners  - df_themeregion    *	Partner ID: Unique ID for field partners     *	Field Partner Name: Name of Field Partner     *	sector: Sector in which a loan is placed on Kiva's main page     *	Loan Theme ID: Unique ID for loan theme     *	Loan Theme Type: General description of the loan theme category     *	country: country name     *	forkiva: Was this loan theme created specifically for Kiva?     *	region: Region within country     *	geocode_old: Kiva's old geocoding system Lots of missing values    *	ISO: Unique ID for country     *	number: Number of loans funded in this LocationName and this loan theme     *	amount: Dollar value of loans funded in this LocationName and this loan theme     *	LocationName: "{region}, {country}" - Unique ID for region     *	geocode: Lat-Lon pair     *	names: All placenames that the Gmaps API associates with LocationName     *	geo: Lat-Lon pair     *	lat: latitude     *	lon: longitude     *	mpi_region: MPI Region where we think this loan theme is located     *	mpi_geo: Lat-Lon pair where we think this MPI region is located     *	rural_pct: The percentage of this field partners' borrowers that are in rural areas- df_regloc     *	LocationName: "{region}, {country}" - Unique ID for region     *	ISO: Unique ID for country     *	country: country name     *	region: name of location within country     *	world_region: General global region     *	MPI: Multi-dimensional poverty index for this region     *	geo: Lat-Lon pair     *	lat: latitude     *	lon: longitude   Additional Kiva Datasets- df_loans2: dataset containing loans posted in the period 2006-2018    * Status:        - 'expired': if a loan doesn’t fully fund within 30 days of being listed on the website, it “expires.”        - 'fundRaising': This status indicates that the loan has not yet been funded. Lenders can only lend to loans that are in the fundraising status.        - 'funded': indicates that the loan request has been completely funded and is not available for new loans by lenders.→ Take this as target variable???         - 'refunded': refund the funded portion of the loan to the lenders mostly because of an error (rare situation)            * disburse_time: The date on which the loan was/will actually be disbursed to the borrower     * posted_time: Date when loan was posted on Kiva.      * planned_expiration_time: When the loan will expire if it is not fully funded    * raised_time (=funded_time): When the loan became raised, e.g. fully funded.     * lender_term: The number of months it will take the borrower to repay the loan    * num_journal_entries: Number of journal entries (updates on the Kiva website) by borrower.    * num_bulk_entries:    * repayment_interval:        - 'irregular': repayments due along harvest or market cycles that do not align neatly with calendar-based time periods.         - 'monthly':        - 'bullet': means the bulk of the repayment is due nearer to the end term date.        - 'weekly':    * distribution_model: How the loan is distributed to the borrower, e.g. 'field_partner' or 'direct'- df_lenders: contains information about lenders who became a member in the period 2006-2018    * permanent_name    * display_name    * member_since    * loan_because    * num_invited: The number of registered users invited by this lender.    * loan_purchase_num: The total number of loans made by the lender. (Niet zeker of dit klopt???)- df_loans_lenders- df_descriptions (File is 1,75GB !!!): this file contains borrower descriptions belonging to the df_loans2 dataset    * description     * description_translated      * original_language  Preprocessing<jupyter_code>#Importing necessary libraries
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
from sklearn.datasets import load_boston
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
from sklearn.feature_selection import RFE
from sklearn.linear_model import RidgeCV, LassoCV, Ridge, Lasso
color = sns.color_palette()
#Suppress unnecessary warnings so presentation looks clean
import warnings
warnings.filterwarnings("ignore")
from datetime import datetime, timezone
#Peter
df_theme= pd.read_csv(r"C:\Users\peter\Google Drive\ThesisMap\JupyterNotebook\Datasets\KivaDataSet\loan_theme.csv",index_col=None)
df_regloc= pd.read_csv(r"C:\Users\peter\Google Drive\ThesisMap\JupyterNotebook\Datasets\KivaDataSet\kiva_regloc.csv",index_col=None)
#Peter
df_loans2= pd.read_csv(r"C:\Users\peter\Documents\2e master HIR\Thesis Kiva\loans.csv",index_col=None)
df_loans= pd.read_csv(r"C:\Users\peter\Google Drive\ThesisMap\JupyterNotebook\Datasets\KivaDataSet\kiva_loans.csv",index_col=None)
len(df_loans)
len(df_theme)
df_theme=df_theme.rename(columns={"id": "primary_key_loan"})<jupyter_output><empty_output><jupyter_text>Data transformation  Data cleaning<jupyter_code>merge1=df_loans2.copy()
merge1.drop(merge1[ merge1['status'] == 'refunded' ].index , inplace=True)
merge1.drop(merge1[ merge1['status'] == 'fundRaising' ].index , inplace=True)
merge1.status.value_counts()
merge1['posted_time'] = pd.to_datetime(merge1['posted_time'])
merge1['year'], merge1['month'] = merge1['posted_time'].dt.year, merge1['posted_time'].dt.month
# Sample of loans from 2012-2017 and only field_partner loans
merge1=merge1[(merge1['year']>=2012) & (merge1['year']<=2017) & (merge1['distribution_model']=='field_partner')]
len(merge1)
#histogram of target variable 
hist = sns.countplot(x='status', data=merge1)
hist.set(xlabel='Funded (1) vs Expired (0)', ylabel='Number of observations')
#hist.figure.savefig("status_imbalance.png", bbox_inches='tight')
#hist.set_title('The number of non-predisbursed loans')
# create column with english descriptions
merge1['description_ENG']=merge1['description_translated'].combine_first(merge1['description'])
del merge1['description']
del merge1['description_translated']
merge1.isnull().sum()
merge1['planned_expiration_time'] = pd.to_datetime(merge1['planned_expiration_time'])
merge1['disburse_time'] = pd.to_datetime(merge1['disburse_time'])
merge1['raised_time'] = pd.to_datetime(merge1['raised_time'])
merge1['funded_time'] = merge1['raised_time']
merge1.iloc[0,:]<jupyter_output><empty_output><jupyter_text>Gender reclassified and borrower_count<jupyter_code>merge1['borrower_count'] = merge1['borrower_genders'].str.split().str.len()
merge1['loan_type'] = np.where(merge1['borrower_count']<= 1, 'individual', 'group')
merge1.loan_type.value_counts()
merge1_original = merge1.copy()


df = merge1[merge1['loan_type'] == 'group'].copy()
df = df[df['description_ENG'].notnull()]
df['description_ENG_Parsed'] = df['description_ENG']

# Convert to lowercase
df['description_ENG_Parsed'] = df['description_ENG_Parsed'].str.lower()

#tokenize
import nltk
from nltk.tokenize import word_tokenize
df['description_ENG_Parsed'] = df.apply(lambda row: nltk.word_tokenize(row['description_ENG_Parsed']), axis=1)
tokenized_list = df['description_ENG_Parsed'].tolist()

female_words = ['she',"she's",'her','hers','herself']
male_words = ['he',"he's",'him','his','himself']

female_count_list = []
male_count_list = []


for sentence in tokenized_list:
    female_count = 0
    male_count = 0
    for word in sentence:
        if word in male_words:
            male_count = male_count + 1
        elif word in female_words:
            female_count = female_count + 1
        else:
            continue
    male_count_list.append(male_count)
    female_count_list.append(female_count)
    
df['male counter'] = male_count_list
df['female counter'] = female_count_list
df['gender_reclassified'] = np.where(df['male counter']< df['female counter'], 'female', 'male')


len(df)
merge1 = pd.merge(merge1, df[['loan_name','description_ENG','male counter','female counter','gender_reclassified']], on=['loan_name','description_ENG'], how='outer')
merge1['gender_reclassified']=merge1['gender_reclassified'].combine_first(merge1['borrower_genders'])
merge1.groupby(['loan_type', 'gender_reclassified']).size()
merge1.isnull().sum()<jupyter_output><empty_output><jupyter_text>Preprocessing step 2 (for every dataframe)  MERGE 1  - Checking missing values/NaN of columns  - Check for duplicate values - Check for outliers - Correlation plots  missing values<jupyter_code>merge1.dtypes
merge1.columns[merge1.isnull().any()]
del merge1['borrower_genders']
del merge1['borrower_pictured']
del merge1['male counter']
del merge1['female counter']
del merge1['tags']
merge1 = merge1[merge1['gender_reclassified'].notnull()]<jupyter_output><empty_output><jupyter_text>- original language<jupyter_code>merge1.original_language.isnull().sum().sum()
merge1.original_language.unique()
len(merge1)<jupyter_output><empty_output><jupyter_text>THEMEREGION<jupyter_code>df_themeregion = pd.read_csv(r"C:\Users\peter\Google Drive\ThesisMap\JupyterNotebook\Datasets\KivaDataSet\loan_themeRegion.csv",index_col=None)<jupyter_output><empty_output><jupyter_text>REGLOC<jupyter_code>df_regloc.columns[df_regloc.isnull().any()]
df_regloc.shape[0]
df_regloc.geo[2771]
#Those values are incorrect: longitude must be in between -90 and 90
df_regloc = df_regloc[df_regloc.geo != '(1000.0, 1000.0)']<jupyter_output><empty_output><jupyter_text>Data transformation<jupyter_code>df3= pd.read_csv(r"C:\Users\peter\Documents\2e master HIR\Thesis Kiva\df3.csv", index_col= None)
df3.dtypes
len(merge1)
merge3 = pd.merge(merge1, df3[['dominant_topic','loan_id']], left_on='loan_id', right_on="loan_id", how='inner')
len(merge3)
merge2= merge3.copy()
merge2.isnull().sum()
del merge2['funded_amount']
del merge2['country_code']
del merge2['lender_term']
del merge2['loan_use']
del merge2['town_name']
del merge2['planned_expiration_time']
del merge2['disburse_time']
del merge2['raised_time']
del merge2['num_lenders_total']
df_theme.dtypes
df_theme['Loan Theme Type'].isnull().sum()
df_theme['Loan Theme ID'].isnull().sum()
len(df_theme)
merge3 = pd.merge(merge2, df_theme[['primary_key_loan','Loan Theme Type']], left_on='loan_id', right_on="primary_key_loan", how='inner')
merge3['Loan Theme Type'].isnull().sum()
df_regloc.head()
len(merge3)
df_regloc.dtypes
len(df_regloc)
len(merge3)
len(df_regloc)
df_regloc= df_regloc.groupby(['country','world_region'],as_index=False)['MPI'].mean()
df_regloc.head()
merge3.dtypes
merge2 = pd.merge(merge3, df_regloc[['country','world_region','MPI']], left_on='country_name', right_on="country", how='inner')
len(merge2)
merge2.dtypes
del merge2['primary_key_loan']
merge2.dtypes
merge2['posted_time'] = pd.to_datetime(merge2['posted_time'])
#merge2['month'] = merge2['posted_time'].dt.month
del merge2['posted_time']
merge1 = merge2.copy()
merge1.isnull().sum()
merge1['country_name']=merge1['country_name'].replace({"Lao People's Democratic Republic": "Lao People"})
merge1.dtypes
del merge1['loan_name']
del merge1['currency_exchange_coverage_rate']
del merge1['num_journal_entries']
del merge1['num_bulk_entries']
del merge1['currency']
del merge1['currency_policy']
#histogram of target variable 
hist = sns.countplot(x='status', data=merge1)
hist.set(xlabel='Funded (1) vs Expired (0)', ylabel='Number of observations')
hist.figure.savefig("status_imbalance.png", bbox_inches='tight')
#hist.set_title('The number of non-predisbursed loans')<jupyter_output><empty_output><jupyter_text>Resampling<jupyter_code>from sklearn.utils import resample
merge1.groupby('status').size()
#histogram of target variable 
hist = sns.countplot(x='status', data=merge1)
hist.set(xlabel='Funded (1) vs Expired (0)', ylabel='Number of observations')
hist.figure.savefig("status_imbalance.png", bbox_inches='tight')
#hist.set_title('The number of non-predisbursed loans')
df_majority = merge1[merge1.status=='funded']
df_minority = merge1[merge1.status=='expired']
len(df_minority)
from sklearn.utils import resample
# Downsample majority class
df_majority_downsampled = resample(df_majority, 
                                 replace=False,    # sample without replacement
                                 n_samples=34969,     # to match minority class
                                 random_state=42) # reproducible results
df_downsampled = pd.concat([df_majority_downsampled, df_minority])
merge1 =df_downsampled.copy()
merge1.dtypes
len(merge1)
merge1.isnull().sum()
merge_sample = merge1.copy()
merge1.dtypes
merge1.corr()
#No strong correlations
merge1.MPI.min()
merge1.MPI = merge1.MPI.round(decimals=2)
merge1.dtypes
del merge1['country']
merge1.dominant_topic.value_counts()
#merge1['month']=merge1['month'].astype('category')
#merge1.month.value_counts()
merge1.dtypes
lijst = merge1.partner_id.to_list()
setje = set(lijst)
print(setje)
set2= set()
for i in setje:
    set2.add(int(i))
print(set2)
merge1.to_csv(r"C:\Users\peter\Documents\2e master HIR\Thesis Kiva\merge1_final_corrected.csv", index=False)
merge1.groupby(['loan_type', 'gender_reclassified']).size()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11475194966904778, 'fraction_numerical': 0.021823186316272364, 'mean_word_length': 5.345114345114345, 'pattern_counts': {'":': 2, '<': 40, '<?xml version=': 0, '>': 38, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '51304', 'license': 'MIT', 'n_tokens_mistral': 5123, 'n_tokens_neox': 4836, 'n_words': 1255, 'path': 'Create sample models.ipynb', 'repo_name': 'AlexanderPaulStevens/Explainability-and-Fairness-in-Machine-Learning-Improve-Fair-End-to-end-Lending-for-Kiva', 'size': '15259'}