<jupyter_start><jupyter_text>If downloaded from github, data files will need to be unzipped.<jupyter_code>data = pd.read_csv("data/SBAnational.csv")
pd.options.display.max_rows = 999
data["ApprovalFY"].value_counts(dropna=False)

data.loc[data["ApprovalFY"] == "1976A", "ApprovalFY"] = "1976"
data["ApprovalFY"] = data["ApprovalFY"].astype(int)
data["ApprovalFY"].value_counts(dropna=False)
# first two digits are industry code. Extracted anc onverted to int
# code 0 may need investigating

data["NAICS"].astype(str).str[0:2].value_counts(dropna=False)

data["twoDigNAICS"] = data["NAICS"].astype(str).str[0:2]
data["twoDigNAICS"] = data["twoDigNAICS"].astype(int)
data["twoDigNAICS"].value_counts(dropna=False)
# 0 and 1 indicate not a franchise. Created binary is_franchise column
data["FranchiseCode"].value_counts(dropna=False).index.sort_values()
data.loc[data["FranchiseCode"] > 1, "is_franchise"] = 1
data.loc[data["FranchiseCode"] <= 1, "is_franchise"] = 0
data["is_franchise"].value_counts(dropna=False)
# similar to above. Created binary variable for having revolving line of credit

# some nonsense in here
data["RevLineCr"].value_counts(dropna=False)

revCR_map = {
    "N": 0,
    "0": 0,
    "Y": 1,
    "T": 1,  # T for true? maybe
}
data["RevLineCr"] = data["RevLineCr"].map(revCR_map)
# data["RevLineCr"] = data["RevLineCr"].astype(int, errors="ignore")
data["RevLineCr"].value_counts(dropna=False)
# clean state columns. Filled with BankState. May have biased new feature bank_out_of_state
# was only 14, so not that big of a deal
data["State"].isna().sum()
data[data["State"].isna()]

data["State"] = data["State"].fillna(data["BankState"])
data["State"].isna().sum()

# 1 left from 1981, looks like nothing we want
data[data["State"].isna()]

data = data.dropna(subset=["State"])
cleaned_States = list(data["State"].value_counts().sort_index().index)
#cleaned_States

# clean Bank State
# Handled weird out of country banks by selecting based on the State column
# definite bias here. Out of country banks may follow a trend
data["BankState"].value_counts().sort_index()

# keep rows where the BankState value appears more than 100 times. Want to match State column
valid_states = list(data["State"].value_counts().index)
bankState_valid = data["BankState"].isin(valid_states)
# only going to lose 1700 rows. Drop em.
cleaned_bankStates = list(
    data.loc[bankState_valid, "BankState"].value_counts().sort_index().index
)

data = data.loc[bankState_valid]
# made binary column bank_out_of_state

data["bank_out_of_state"] = data["State"] == data["BankState"]
data["bank_out_of_state"] = data["bank_out_of_state"].astype(int)
data["bank_out_of_state"].value_counts(dropna=False)
# Approval Date. No NA's originally
# ~some are in the future! Handle this. Maybe hit 2 instead of 1?~
# pd_to_datetime() behaves starngely across decades, or may have been a simple typo
# 2076 or whatever is definitely outside of our consideration either way
data["ApprovalDate_datetime"] = pd.to_datetime(data["ApprovalDate"])
data.loc[data["ApprovalDate_datetime"].dt.year > 2000, "ApprovalDate_datetime"].hist()
# clean term column. IN months, convert to years
# appears to be some skew.
data["Term"].isna().sum()
data["Term_years"] = data["Term"] / 12
data["Term_years"].hist()
plt.show()
sns.boxplot(data["Term_years"])
plt.show()
data["Term"].describe()
# clean noemp columns
# lots of skew to consider
data["NoEmp"].isna().sum()
data["NoEmp"].value_counts()
sns.boxplot(data["NoEmp"])
plt.show()
data["NoEmp"].describe()
# clean NewExist column. 1 is existing, 2 is new. Handle na's and 0's
# note: some approval dates are in the future!
pd.options.display.max_columns = 999
data["NewExist"].isna().sum
data["NewExist"].value_counts(dropna=False)

data.loc[data["NewExist"].isna(), "ApprovalDate_datetime"].value_counts().sort_index()
data.loc[data["NewExist"].isna(), "bank_out_of_state"].value_counts()
data.loc[data["NewExist"].isna(), "ApprovalDate_datetime"].describe()
# some weirdness with the na's. All but one are from dec-2019 through around 2010.
# just going on a hunch and filling these missing values with 1: an existing business
NewExist_map = {1: 0, 2: 1, 0: 1, np.nan: 1}

data["NewExist"] = data["NewExist"].map(NewExist_map)
data["NewExist"].value_counts()
# clean urban/rural column. 1: urban, 2: rural, 0:undefined
data["UrbanRural"].value_counts()

# investigate na's for patterns, but will definitely remap to 0,1
data.loc[data["UrbanRural"] == 0, "ApprovalDate_datetime"].describe()
# looks lijke undefined (0's) are from mid-late 90's
# clue that future values might need to be 19 not 20
data.loc[data["UrbanRural"] == 0, "ApprovalDate_datetime"].hist(bins=20)
data["UrbanRural"].value_counts()

UrbanRural_map = {1: 0, 2: 1, 0: np.nan}

data["UrbanRural_cleaned"] = data["UrbanRural"].map(UrbanRural_map)
data["UrbanRural_cleaned"].value_counts(dropna=False)
# clean LowDoc. Lot's of Na's
# y = yes, n = no
data["LowDoc"].isna().sum()
data["LowDoc"].value_counts(dropna=False)
# more nonsense, awesome
LowDoc_map = {
    "N": 0,
    "Y": 1,
}
data['LowDoc'] = data["LowDoc"].map(LowDoc_map, na_action="ignore")
data['LowDoc'].value_counts(dropna=False)

# getting into dates, so I went ahead and selected my years.
# This will cut down on NA's and errant values

data = data.loc[
    data["ApprovalDate_datetime"].dt.year < 2020,
]
data = data.loc[data["ApprovalDate_datetime"].dt.year > 2000]
# Disbursement Date
# Converted to datetime. Extrcated year.
# converted month to cyclical feature with a unit circle transform
# may want to keep this in mind for scaling
# http://blog.davidkaleko.com/feature-engineering-cyclical-features.html

data["DisbursementDate"].isna().sum()
data.loc[data["DisbursementDate"].isna()]
data["DisbursementDate"] = data["DisbursementDate"].fillna(data["ApprovalDate"])

data["Disbr_datetime"] = pd.to_datetime(data["DisbursementDate"])

# cuts dout about 300k rows
data = data.loc[data["Disbr_datetime"].dt.year.isin(np.arange(2000, 2020, 1))]
data["Disbr_datetime"].dt.year.describe()
# # note the 2069 jumping out again. Just going to ignore it,
# # because it is from a time we don't care about
data["Disbr_year"] = data["Disbr_datetime"].dt.year
data["Disbr_month"] = data["Disbr_datetime"].dt.month - 1
data["Disbr_Month_sin"] = np.sin((data["Disbr_month"]) * (2.0 * np.pi / 12))
data["Disbr_Month_cos"] = np.cos((data["Disbr_month"]) * (2.0 * np.pi / 12))
# make a feature identifying if SBA approval was given before or after loan disbursed

data["time_to_disbr"] = data["Disbr_datetime"] - data["ApprovalDate_datetime"]

sba_pre_approved = data["time_to_disbr"] > np.timedelta64(0, "D")
sba_pre_approved.value_counts(dropna=False)
data.loc[sba_pre_approved, "sba_pre_approv"] = 1
data.loc[~sba_pre_approved, "sba_pre_approv"] = 0
data["sba_pre_approv"].value_counts(dropna=False)
data["DisbursementGross"].isna().sum()
data["DisbursementGross"] = data["DisbursementGross"].str.replace("$", "")
data["DisbursementGross"] = data["DisbursementGross"].str.replace(",", "")
data["DisbursementGross"] = data["DisbursementGross"].astype(float)
data["BalanceGross"].isna().sum()
# data["BalanceGross"].value_counts()
data["BalanceGross"] = data["BalanceGross"].str.replace("$", "")
data["BalanceGross"] = data["BalanceGross"].str.replace(",", "")
data["BalanceGross"] = data["BalanceGross"].astype(float)
# clean ChgOffPrinGr

data["ChgOffPrinGr"].isna().sum()
data["ChgOffPrinGr"].value_counts()
data["ChgOffPrinGr"] = data["ChgOffPrinGr"].str.replace("$", "")
data["ChgOffPrinGr"] = data["ChgOffPrinGr"].str.replace(",", "")
data["ChgOffPrinGr"] = data["ChgOffPrinGr"].astype(float)
# cleaned grappv
data["GrAppv"].isna().sum()
data["GrAppv"] = data["GrAppv"].str.replace("$", "")
data["GrAppv"] = data["GrAppv"].str.replace(",", "")
data["GrAppv"] = data["GrAppv"].astype(float)
# clean SBA_Appv

data["SBA_Appv"].isna().sum()
data["SBA_Appv"].value_counts()

data["SBA_Appv"] = data["SBA_Appv"].str.replace("$", "")
data["SBA_Appv"] = data["SBA_Appv"].str.replace(",", "")
data["SBA_Appv"] = data["SBA_Appv"].astype(float)
data["percent_SBA"] = data["SBA_Appv"] / data["GrAppv"]
data["percent_SBA"].describe()
data = data.loc[data["MIS_Status"].notna()]
MIS_Status_map = {"P I F": 0, "CHGOFF": 1, 0: 0, 1: 1}

data["MIS_Status"] = data["MIS_Status"].map(MIS_Status_map, na_action="ignore")
data["MIS_Status"].value_counts(dropna=False)
banks = data["Bank"].value_counts()

big_banks = banks.loc[banks.values >= 1000].index.to_list()

med_banks = banks.loc[banks.values >= 100]
med_banks = med_banks.loc[med_banks.values < 1000]
med_banks = med_banks.index.to_list()

small_banks = banks.loc[banks.values < 100].index.to_list()

data.loc[data["Bank"].isin(small_banks), "bank_size"] = 0
data.loc[data["Bank"].isin(med_banks), "bank_size"] = 1
data.loc[data["Bank"].isin(big_banks), "bank_size"] = 2

# data["Approval_Year"] = data["ApprovalDate_datetime"].dt.year
# ApprvFY moe informative, but want the months
data["Approval_Month"] = data["ApprovalDate_datetime"].dt.month - 1
data["Appv_Month_sin"] = np.sin((data["Approval_Month"]) * (2.0 * np.pi / 12))
data["Appv_Month_cos"] = np.cos((data["Approval_Month"]) * (2.0 * np.pi / 12))
data = data.dropna(subset=["RevLineCr", "LowDoc", "UrbanRural_cleaned"])
data.info()
trash_cols = [
    "LoanNr_ChkDgt",
    # "Name",
    # "City",
    # 'Bank State' expect colinearity
    # "Zip",  # might be useful, but State should be good enough
    "FranchiseCode",  # engineered to is_franchise
    "NAICS",
    "Term",
    #"CreateJob",
    #"RetainedJob",
    "ChgOffDate",
    "DisbursementDate",
    "ChgOffPrinGr",  # if this has a value, will give away target
    "ApprovalDate",
    #"Bank",
    "BalanceGross",  # redundant with MIS_Status
    "time_to_disbr",  # intermediate variable
    "SBA_Appv",  # engineered into percentage sba approved
    "UrbanRural",  # cleaned into new column
    "GrAppv",  # redundant to Disbursement gross
    "ApprovalDate_datetime",
    "Approval_Month",  # intermediate
    "Disbr_month",  # intermediate
    "Disbr_datetime",  # intermediate
]

data = data.drop(columns=trash_cols)

data.info()
data.to_csv("data/SBA_clnd.csv", index=False)
loans = pd.read_csv("data/SBA_clnd.csv")
loans.head()
loans.loc[loans["Bank"] == "CITIBANK, N.A.", "BankState"].value_counts()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17186443950951047, 'fraction_numerical': 0.016220913391908853, 'mean_word_length': 6.273876404494382, 'pattern_counts': {'":': 8, '<': 9, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '319178', 'license': 'MIT', 'n_tokens_mistral': 3922, 'n_tokens_neox': 3709, 'n_words': 780, 'path': 'loan_data_cleaning.ipynb', 'repo_name': 'DJGump/defualted_loans_clustering_analysis', 'size': '10357'}