<jupyter_start><jupyter_text>Summary The CFPB receives several thousands complaints filed by consumers each year concerning the practices of finanical companies. After reviewing each complaint, the CFPB makes a judgement regarding how the complaint should be resolved. These resolutions historically fall into seven categories with the most punitive being 'closed with monetary relief'.    GoalThe goal of this project is to use This project focuses on predicting consumer complaints received by CFPB.  This section of code is general data wrangling and data management.  The objective of merging files, renaming, and organizing being further exploration and analysis.  Exploratory AnalysisThis section provides details of the exploratory analysis as well as data cleaning.<jupyter_code>import pandas as pd
import numpy as np

import matplotlib.pyplot as plt
%matplotlib inline

#read saved csv file
conComplaintDf=pd.read_csv("Consumer_Complaints.csv",converters={'ZIP code': lambda x: str(x)})

#ccDf=conComplaintDf.groupby(['Company response to consumer']).count()<jupyter_output><empty_output><jupyter_text>Recode the resolutions as integers from 0-8. Recoding should make it easier to explore the data and use with machine learning algorithms.<jupyter_code>#order the responses
#0 closed with monetary relief
#1 closed with non-monetary relief
#2 closed with relief
#3 closed with explanation
#4 closed
#5 closed without relief
#6 untimely response
#7 in progress
conComplaintDf['respCode']=np.where(conComplaintDf['Company response to consumer']== 'Closed with monetary relief',0,
    np.where(conComplaintDf['Company response to consumer']== 'Closed with non-monetary relief',1,
        np.where(conComplaintDf['Company response to consumer']== 'Closed with relief',2,
            np.where(conComplaintDf['Company response to consumer']== 'Closed with explanation',3,
                np.where(conComplaintDf['Company response to consumer']== 'Closed',4,
                    np.where(conComplaintDf['Company response to consumer']== 'Closed without relief',5,
                        np.where(conComplaintDf['Company response to consumer']== 'Untimely response',6,
                            np.where(conComplaintDf['Company response to consumer']== 'In progress',7,8))))))))<jupyter_output><empty_output><jupyter_text>The data received field should be adjusted to a python datetime format<jupyter_code>#clean the date received field
#convert 'date received' column from string to datetime
#align all complaints to end of month 
#create a month-year column

import calendar
import datetime

#conComplaintDfStg['Date received'].dtype
conComplaintDf['dateRec']=pd.to_datetime(conComplaintDf['Date received'])#,format='%B/%d/%y')
conComplaintDf['adjDate'] = conComplaintDf['dateRec'].map(
    lambda x: datetime.datetime(
        x.year,
        x.month,
        max(calendar.monthcalendar(x.year, x.month)[-1][:5])
    )
)
conComplaintDf['monYear']=conComplaintDf['adjDate'].apply(lambda x: x.strftime('%B-%Y'))<jupyter_output><empty_output><jupyter_text>There are three response codes(6, 7, 8) that indicate the entire process was not completed.<jupyter_code>#drop rows without complete information(6&7) create data frames for data analysis

#conComplaintDfStg=
conComplaintDf.drop(conComplaintDf[conComplaintDf.respCode >=6].index, inplace=True)
conComplaintDf['zip3']=conComplaintDf['ZIP code'].str[:3]


#create dataframe with complaints resulting in monetary relief
#response0Df=conComplaintDf.loc[(conComplaintDf.respCode== 0)]<jupyter_output><empty_output><jupyter_text>I downloaded and saved the CFPB complaint database but we could download from the website and load directly into a pandas dataframe.  Below I load census files directly into pandas dataframes from their host websites.<jupyter_code>#download urban area to cbsa file
#UA:urban area number
#UANAME: urban area name
#CBSA: corebased statistical area number(micro/metropolitan statistical area)
#MNAME: cbsa name
#MEMI: 1=metropolitan statistical area; 2=micropolitan statistical area
url="https://www2.census.gov/geo/docs/maps-data/data/rel/ua_cbsa_rel_10.txt"
uaToCbsaDf=pd.read_csv(url,encoding='latin1')#converters={'ZCTA5': lambda x: str(x)})
uaToCbsaDf=uaToCbsaDf[['UA','UANAME','CBSA','MNAME','MEMI','POPPT']]
uaToCbsaDf.drop(uaToCbsaDf[(uaToCbsaDf.MEMI != 1) | (uaToCbsaDf.UA== 99999)|(uaToCbsaDf.CBSA==99999) ].index, inplace=True)


#download zip to cbsa file

import urllib.request
import requests
import io
url="https://www2.census.gov/geo/docs/maps-data/data/rel/zcta_cbsa_rel_10.txt"
#zipToSaDf=pd.read_csv(url,'ZCTA5',index_col=0)
#preserve leading zeros in zip5
zipToSaDf=pd.read_csv(url,converters={'ZCTA5': lambda x: str(x)})
zipToSaDf=zipToSaDf[['ZCTA5','CBSA','ZPOP','MEMI']]
zipToSaDf.drop(zipToSaDf[(zipToSaDf.MEMI)!=1].index,inplace=True)
zipToSaDf['zip3']=zipToSaDf.ZCTA5.str[:3]

#some records contain zip5 and some contain zip3.  This causes problems when merging
#I will split the cfpb data into two files
#1- with full 5-digit zip code
#2- with 3-digit zip code
conComplaintZ5=conComplaintDf[conComplaintDf['ZIP code'].str.contains("XX")==False]

conComplaintZ3=conComplaintDf[conComplaintDf['ZIP code'].str.contains("XX")==True]

#later we will use zip codes to merge with census files(urban area and msa)
#since zip3 is truncated zip5, they may link with multiple zip5s and UA/MSA
#I want to remove the smaller(by population) instances of zip3
#sort by zip3 and zip-level population
#goal is to keep zip3 with largest population 
zipToSaDf.sort_values(['zip3','ZPOP'])
zipToSaDfZ3=zipToSaDf.drop_duplicates(['zip3'],keep='last')<jupyter_output><empty_output><jupyter_text>The next steps are focused on merging the census files to create a normalized dataset.<jupyter_code>#merge the zip To CBSA file onto the split(by zip3 and zip5) consumer complaint data
cfpbComplaintCbsaZ5= pd.merge(conComplaintZ5,zipToSaDf,left_on=['ZIP code','zip3'],right_on=['ZCTA5','zip3'],how='left')
cfpbComplaintCbsaZ3= pd.merge(conComplaintZ3,zipToSaDfZ3,on=['zip3'],how='left')

#concatenate the zip5 and zip3 files
cfpbComplaintCbsa= pd.concat([cfpbComplaintCbsaZ5,cfpbComplaintCbsaZ3])<jupyter_output><empty_output><jupyter_text>Since UA to CBSA is a 'one to many' relationship, I'm going to keep the CBSA with largest population for this exercise.  The idea, is that there is a greater probability of the consumer residing in the CBSA with a larger population.<jupyter_code>uaToCbsaDf.sort_values(['CBSA','POPPT'],inplace=True)
uaToCbsaDf=uaToCbsaDf.drop_duplicates(['CBSA'],keep='last')<jupyter_output><empty_output><jupyter_text>I need to change the data type from float to int for merging<jupyter_code>cfpbComplaintCbsa['CBSA']=cfpbComplaintCbsa['CBSA'].fillna(0.0).astype(int)
#now merge with the uaCbsa data to add urban area information
cfpbComplaintCbsaUa=pd.merge(cfpbComplaintCbsa,uaToCbsaDf, on='CBSA', how = 'inner')<jupyter_output><empty_output><jupyter_text>Downloading a zip file and loading into pandas is a bit different (and not as straight forward) than what I did above.<jupyter_code>from zipfile import ZipFile
from urllib.request import urlopen   
import pandas as pd
import os

URL = \
    'http://www2.census.gov/geo/docs/maps-data/data/gazetteer/Gaz_ua.zip'

# open and save the zip file onto computer
url = urlopen(URL)
output = open('zipFile.zip', 'wb')    # note the flag:  "wb"        
output.write(url.read())
output.close()

# read the zip file as a pandas dataframe
uaGaz = pd.read_csv('zipFile.zip',sep='\t',encoding='latin1')   # pandas version 0.18.1 takes zip files       

# if keeping on disk the zip file is not wanted, then:
#os.remove(zipName)   # remove the copy of the zipfile on disk
#keep ID, Lat, Long fields and merge onto cfpb data
#uaGaz.count()
#uaGaz=uaGaz[['GEOID','INTPTLAT','INTPTLONG']]
gazCols=['GEOID','NAME',
 'UATYPE',
 'POP10',
 'HU10',
 'ALAND',
 'AWATER',
 'ALAND_SQMI',
 'AWATER_SQMI',
 'INTPTLAT',
 'INTPTLONG']
uaGaz.columns=gazCols
cfpbComplaintCbsaUaLl= pd.merge(cfpbComplaintCbsaUa,uaGaz,left_on='UA',right_on='GEOID',how='left')

cfpbComplaintCbsaUaLl.iloc[:,-1]
cfpbComplaintCbsaUaLl.rename(columns={'INTPTLONG\n':'INTPTLONG'}, inplace=True)
cfpbComplaintCbsaUaLl['target']=np.where(cfpbComplaintCbsaUaLl['respCode']== 0,1,0)<jupyter_output><empty_output><jupyter_text>Below I take care of missing values and recode categorical values into binary<jupyter_code>#Text processing replace na with an empty string
cfpbComplaintCbsaUaLl['Consumer complaint narrative']= cfpbComplaintCbsaUaLl['Consumer complaint narrative'].fillna('')
cfpbComplaintCbsaUaLl['State']= cfpbComplaintCbsaUaLl['State'].fillna('')
#transform categorical variables into binary
cfpbComplaintCbsaUaLl['UA']=cfpbComplaintCbsaUaLl['UA'].astype('category')
#catCols= ['State','Product','Issue','UA']
catCols= ['Product','Issue','UA']
dfDummies= pd.get_dummies(cfpbComplaintCbsaUaLl[catCols])
dataStg= pd.concat([cfpbComplaintCbsaUaLl,dfDummies],axis=1)<jupyter_output><empty_output><jupyter_text>Now I'm going to split the data into train and test sets (80/20)<jupyter_code>#train/test splitting
from sklearn.model_selection import train_test_split 

#split dataStg into train/ test
train, test= train_test_split(dataStg,test_size=.2)

# recreate index in test and train sets so we can run through the text processing function
train.reset_index(drop=True, inplace=True)
test.reset_index(drop=True, inplace=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11249603887187071, 'fraction_numerical': 0.012147459596493082, 'mean_word_length': 6.672609400324149, 'pattern_counts': {'":': 0, '<': 45, '<?xml version=': 0, '>': 46, 'https://': 2, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '433111', 'license': 'MIT', 'n_tokens_mistral': 3374, 'n_tokens_neox': 3174, 'n_words': 840, 'path': 'draft/cfpbDataWrangling.ipynb', 'repo_name': 'augustineM/Cfpb-complaint-classification', 'size': '9467'}