<jupyter_start><jupyter_text>Trabalho 1Seguem instruções:1) Baixar dataset em : https://www.kaggle.com/c/home-credit-default-risk/data2) Explorar, conforme instruções:* 'application_train.csv': Esse csv possui 122 colunas: explorar 40 colunas, sendo as primeiras 30 obrigatórias e 10 adicionais a escolher* 30 colunas obrigatórias:['SK_ID_CURR', 'TARGET', 'NAME_CONTRACT_TYPE', 'CODE_GENDER',       'FLAG_OWN_CAR', 'FLAG_OWN_REALTY', 'CNT_CHILDREN', 'AMT_INCOME_TOTAL',       'AMT_CREDIT', 'AMT_ANNUITY', 'AMT_GOODS_PRICE', 'NAME_TYPE_SUITE',       'NAME_INCOME_TYPE', 'NAME_EDUCATION_TYPE', 'NAME_FAMILY_STATUS',       'NAME_HOUSING_TYPE', 'REGION_POPULATION_RELATIVE', 'DAYS_BIRTH',       'DAYS_EMPLOYED', 'DAYS_REGISTRATION', 'DAYS_ID_PUBLISH', 'OWN_CAR_AGE',       'FLAG_MOBIL', 'FLAG_EMP_PHONE', 'FLAG_WORK_PHONE', 'FLAG_CONT_MOBILE',       'FLAG_PHONE', 'FLAG_EMAIL', 'OCCUPATION_TYPE', 'CNT_FAM_MEMBERS']       * alunos deverão se organizar para que todas as colunas sejam exploradas* escolher dois arquivos, para exploração e join/merge/concat com o 'application_train.csv'['HomeCredit_columns_description.csv', 'POS_CASH_balance.csv', 'credit_card_balance.csv', 'installments_payments.csv', 'application_train.csv', 'bureau.csv', 'previous_application.csv', 'bureau_balance.csv', 'sample_submission.csv'] 3) objetivos:* entendimento do problema de negócio* carregamento do dataset* identificação e tratamento de NaN* exploração univariadaj, bivariada* visualizações* insights de negócio* formatação do dataset para algoritmos de machine learning* criação de features: agregação de dados, junção de dois ou mais dataframes4) entregável: jupyter notebook. Não será necessário apresentação em powerpoint, nem relatório.5) entrega limite: 8/agosto/2018 6) trabalho individual. Haverá aplicação sistema anti-plágio nas submissões. (http://www.fgv.br/mailing/codigo-etica-conduta/24/)   suspeitas de plágio estarão sujeitos a arguição oral de 5 mins por aluno no ultimo dia de aula (11/agosto/2018),7) avaliação:   - jupyter notebook e arguição (se houver)  Problema de Negócio Do Kaggle:Many people struggle to get loans due to insufficient or non-existent credit histories. And, unfortunately, this population is often taken advantage of by untrustworthy lenders.Home Credit strives to broaden financial inclusion for the unbanked population by providing a positive and safe borrowing experience. In order to make sure this underserved population has a positive loan experience, Home Credit makes use of a variety of alternative data--including telco and transactional information--to predict their clients' repayment abilities.While Home Credit is currently using various statistical and machine learning methods to make these predictions, they're challenging Kagglers to help them unlock the full potential of their data. Doing so will ensure that clients capable of repayment are not rejected and that loans are given with a principal, maturity, and repayment calendar that will empower their clients to be successful. Entendimento do problema:Como fornecer crédito para pessoas com histórico insuficiente ou inexistente?Utilizando estes dados alternativos, pode ser criado um modelo preditivo quer será usado para fazer uma análise das pessoas, para saber se elas tem um histórico de bom pagamento, e fornecer crédito para estas pessoas.  importando as bibliotecas que serão utilizadas<jupyter_code>import pandas as pd
import numpy as np
%matplotlib inline

import matplotlib.pyplot as plt
import missingno as mn

import seaborn as sns

pd.__version__<jupyter_output><empty_output><jupyter_text>lendo o dataframe<jupyter_code>df = pd.read_csv('application_train.csv',index_col=0)<jupyter_output><empty_output><jupyter_text>escolhendo as colunas que serão trabalhadas<jupyter_code>semente = 2701 #Data de aniversário
df40 = pd.concat([df.iloc[:,0:30], df.iloc[:,30:].sample(10,axis=1,random_state=semente)], axis=1 )
df40.shape
df40.to_csv('application_train_40c.csv')
del df<jupyter_output><empty_output><jupyter_text>conhecendo o dataframe<jupyter_code>df40 = pd.read_csv('application_train_40c.csv',index_col=0)
df40.shape
df40.head()
df40.info(verbose=True,null_counts=True)
mn.bar(df40,figsize=(40,6))
np.sort(df40.columns)
df40.AMT_ANNUITY.head()
df40a = pd.DataFrame()
df40a['TARGET'] = df40.TARGET
df40a['NORM_AMT_ANNUITY'] = np.nan
df40a.NORM_AMT_ANNUITY = (df40.AMT_ANNUITY - df40.AMT_ANNUITY.mean()) / (df40.AMT_ANNUITY.max() - df40.AMT_ANNUITY.min())
df40a.head()
df40.AMT_CREDIT.head()
df40a['NORM_AMT_CREDIT'] = np.nan
df40a.NORM_AMT_CREDIT = (df40.AMT_CREDIT - df40.AMT_CREDIT.mean()) / (df40.AMT_CREDIT.max() - df40.AMT_CREDIT.min())
df40a.head()
df40a['NORM_AMT_GOODS_PRICE'] = np.nan
df40a.NORM_AMT_GOODS_PRICE = (df40.AMT_GOODS_PRICE - df40.AMT_GOODS_PRICE.mean()) / (df40.AMT_GOODS_PRICE.max() - df40.AMT_CREDIT.min())
df40.BASEMENTAREA_MODE.head()
df40a['NORM_BASEMENTAREA_MODE'] = np.nan
df40a.NORM_BASEMENTAREA_MODE = (df40.BASEMENTAREA_MODE - df40.BASEMENTAREA_MODE.mean()) / (df40.BASEMENTAREA_MODE.max() - df40.BASEMENTAREA_MODE.min())

df40a.head()

df40a.NORM_BASEMENTAREA_MODE.fillna(df40a.NORM_BASEMENTAREA_MODE.mean(),inplace=True)
df40a.head()

df40a['NORM_CNT_CHILDREN'] = np.nan
df40a.NORM_CNT_CHILDREN = (df40.CNT_CHILDREN - df40.CNT_CHILDREN.mean()) / (df40.CNT_CHILDREN.max() - df40.CNT_CHILDREN.min())
df40a.NORM_CNT_CHILDREN.fillna(0,inplace=True)
df40a.head()

df40a.TARGET.corr(df40a.NORM_BASEMENTAREA_MODE)
set(df40.NAME_EDUCATION_TYPE)
set(df40.NAME_FAMILY_STATUS)
set(df40.NAME_HOUSING_TYPE)
set(df40.REGION_POPULATION_RELATIVE)
set(df40.DAYS_BIRTH)
set(df40.DAYS_EMPLOYED)
set(df40.DAYS_REGISTRATION)
set(df40.DAYS_REGISTRATION)
set(df40.DAYS_ID_PUBLISH)
set(df40.OWN_CAR_AGE)
set(df40.FLAG_MOBIL)
set(df40.FLAG_EMP_PHONE)
set(df40.FLAG_WORK_PHONE)
set(df40.FLAG_CONT_MOBILE)
set(df40.FLAG_PHONE)
set(df40.FLAG_EMAIL)
set(df40.OCCUPATION_TYPE)
set(df40.CNT_FAM_MEMBERS)
set(df40.REGION_RATING_CLIENT)
set(df40.ENTRANCES_MODE)
set(df40.FLAG_DOCUMENT_18)
set(df40.ELEVATORS_AVG)
set(df40.EMERGENCYSTATE_MODE)
set(df40.AMT_REQ_CREDIT_BUREAU_DAY)
set(df40.AMT_REQ_CREDIT_BUREAU_QRT)
set(df40.YEARS_BEGINEXPLUATATION_MEDI)
set(df40.APARTMENTS_MEDI)
set(df40.LANDAREA_MODE)
set(df40.COMMONAREA_MODE)<jupyter_output><empty_output><jupyter_text>Analisando as variáveis relativas a CARROS<jupyter_code>df40.loc[:,['FLAG_OWN_CAR','OWN_CAR_AGE']].head(20)<jupyter_output><empty_output><jupyter_text>Foi observado que quando a pessoa não possuia carro ('FLAG_OWN_CAR'='N') o valor de 'OWN_CAR_AGE'=NaN, por isso substitui-se o NaN de OWN_CAR_AGE baseado na FLAG_OWN_CAR (N) por -1.<jupyter_code>df40.OWN_CAR_AGE.fillna(0, inplace = True)
df40.loc[:,['FLAG_OWN_CAR','OWN_CAR_AGE']].head(20)
set(df40.FLAG_OWN_CAR)
df40['FLAG_OWN_CAR_Y'] = np.nan
df40.FLAG_OWN_CAR_Y.loc[df40.FLAG_OWN_CAR == 'Y'] = 1
df40.FLAG_OWN_CAR_Y.loc[df40.FLAG_OWN_CAR != 'Y'] = 0
df40['FLAG_OWN_CAR_N'] = np.nan
df40.FLAG_OWN_CAR_N.loc[df40.FLAG_OWN_CAR == 'N'] = 1
df40.FLAG_OWN_CAR_N.loc[df40.FLAG_OWN_CAR != 'N'] = 0
df40.loc[:,['FLAG_OWN_CAR_Y','FLAG_OWN_CAR_N','OWN_CAR_AGE']].info()
df40.FLAG_OWN_CAR_Y
df40[['TARGET','FLAG_OWN_CAR_Y','FLAG_OWN_CAR_N']].corr()
df40.to_csv('application_train_40c1.csv') # guardo as alteracoes feitas<jupyter_output><empty_output><jupyter_text>Analisando as variáveis relativas as familias<jupyter_code>df40 = pd.read_csv('application_train_40c1.csv',index_col=0) #leio o arquivo com as alteracoes de carros
df40.loc[:,['CNT_CHILDREN','NAME_FAMILY_STATUS' == 'Unknown','CNT_FAM_MEMBERS']].head(10)
set(df40.NAME_FAMILY_STATUS) # descubro quais são os valores possíveis neste caso<jupyter_output><empty_output><jupyter_text>Os diferentes valores que temos em Family status nos ajudam a definir um valor para este status:{'Civil marriage' = 2, 'Married' = 2, 'Separated' = 1, 'Single / not married' = 1, 'Unknown' = CNT_FAM_MEMBERS - CNT_CHILDREN, 'Widow' = 1} Crio uma nova variavel para armazenar o numero de pessoas na casa<jupyter_code>df40.NAME_FAMILY_STATUS.loc[df40.NAME_FAMILY_STATUS == 'Civil marriage'] = 2
df40.NAME_FAMILY_STATUS.loc[df40.NAME_FAMILY_STATUS == 'Married'] = 2
df40.NAME_FAMILY_STATUS.loc[df40.NAME_FAMILY_STATUS == 'Separated'] = 1
df40.NAME_FAMILY_STATUS.loc[df40.NAME_FAMILY_STATUS == 'Single / not married'] = 1
df40.NAME_FAMILY_STATUS.loc[df40.NAME_FAMILY_STATUS == 'Widow'] = 1

for i in df40.index:
    if df40.NAME_FAMILY_STATUS[i] == 'Unknown': 
        df40.NAME_FAMILY_STATUS[i] = (df40.CNT_FAM_MEMBERS[i] - df40.CNT_CHILDREN[i])
#df40.NAME_FAMILY_STATUS_NO

df40.loc[:,['CNT_CHILDREN','NAME_FAMILY_STATUS','CNT_FAM_MEMBERS']].head(10)
df40.loc[:,['CNT_CHILDREN','NAME_FAMILY_STATUS','CNT_FAM_MEMBERS']].info()<jupyter_output><empty_output><jupyter_text>Considerando que somente as duas linhas da familia estao com valores faltantes, podemos ignorar estes valores, apagando estas duas linhas do dataframe<jupyter_code>mn.bar(df40,figsize=(40,6))
df40.to_csv('application_train_40c2.csv')<jupyter_output><empty_output><jupyter_text>Analisando a variável OCCUPATION_TYPE<jupyter_code>df40 = pd.read_csv('application_train_40c2.csv',index_col=0) #leio o arquivo com as alteracoes de carros e familias
df.loc[:,['DAYS_EMPLOYED','FLAG_WORK_PHONE','OCCUPATION_TYPE']].head(10)
set(df40.OCCUPATION_TYPE) # descubro quais são os valores possíveis neste caso
df40.OCCUPATION_TYPE.fillna('Unknown', inplace = True)
set(df40.OCCUPATION_TYPE) # confirmo que NaN foi trocado por Unknown
mn.bar(df40,figsize=(40,6))
df40.to_csv('application_train_40c3.csv') # guardo as alteracoes feitas<jupyter_output><empty_output><jupyter_text>Analisando variáveis do imóvel<jupyter_code>df40 = pd.read_csv('application_train_40c3.csv',index_col=0) #leio o arquivo com as alteracoes de carros e familias
df40.ENTRANCES_MODE.head()
df40.ELEVATORS_AVG.head()
df40.APARTMENTS_MEDI.head()
df40.LANDAREA_MODE.head()
df40.COMMONAREA_MODE.head()
df40.YEARS_BEGINEXPLUATATION_MEDI.head()<jupyter_output><empty_output><jupyter_text>Nestas 6 variaveis, optei por substituir o NaN pela media dos valores<jupyter_code>df40.ENTRANCES_MODE.fillna(df40.ENTRANCES_MODE.mean(), inplace = True)
df40.ELEVATORS_AVG.fillna(df40.ELEVATORS_AVG.mean(), inplace = True)
df40.APARTMENTS_MEDI.fillna(df40.APARTMENTS_MEDI.mean(), inplace = True)
df40.LANDAREA_MODE.fillna(df40.LANDAREA_MODE.mean(), inplace = True)
df40.COMMONAREA_MODE.fillna(df40.COMMONAREA_MODE.mean(), inplace = True)
df40.YEARS_BEGINEXPLUATATION_MEDI.fillna(df40.YEARS_BEGINEXPLUATATION_MEDI.mean(), inplace = True)
mn.bar(df40,figsize=(40,6))
df40.to_csv('application_train_40c4.csv') # guardo as alteracoes feitas<jupyter_output><empty_output><jupyter_text>Verificando EMERGENCYSTATE_MODE<jupyter_code>df40 = pd.read_csv('application_train_40c4.csv',index_col=0) #leio o arquivo com as alteracoes de carros, familias e dados do apartamento
df40.loc[:,['TARGET','EMERGENCYSTATE_MODE']].head(20)
set(df40.EMERGENCYSTATE_MODE)
df40['EMERGENCYSTATE_MODE_N'] = np.nan
df40.EMERGENCYSTATE_MODE_N.loc[df40.EMERGENCYSTATE_MODE == 'No'] = 1
df40.EMERGENCYSTATE_MODE_N.loc[df40.EMERGENCYSTATE_MODE != 'No'] = 0
df40['EMERGENCYSTATE_MODE_Y'] = np.nan
df40.EMERGENCYSTATE_MODE_Y.loc[df40.EMERGENCYSTATE_MODE == 'Yes'] = 1
df40.EMERGENCYSTATE_MODE_Y.loc[df40.EMERGENCYSTATE_MODE != 'Yes'] = 0
df40.EMERGENCYSTATE_MODE.fillna(-1, inplace = True)
df40.loc[:,['TARGET','EMERGENCYSTATE_MODE']].head()
df40[['TARGET','EMERGENCYSTATE_MODE_Y','EMERGENCYSTATE_MODE_N']].corr()
mn.bar(df40,figsize=(40,6))
df40.to_csv('application_train_40c5.csv') # guardo as alteracoes feitas<jupyter_output><empty_output><jupyter_text>Conhecendo as variaveis que ainda precisam de tratamento<jupyter_code>df40 = pd.read_csv('application_train_40c5.csv',index_col=0) #leio o arquivo com as alteracoes de carros, familias e dados do apartamento
df40[['TARGET','AMT_REQ_CREDIT_BUREAU_DAY','AMT_REQ_CREDIT_BUREAU_QRT']].head(30)
df40[['TARGET','AMT_REQ_CREDIT_BUREAU_DAY','AMT_REQ_CREDIT_BUREAU_QRT']].corr()
df40.AMT_REQ_CREDIT_BUREAU_DAY.fillna(df40.AMT_REQ_CREDIT_BUREAU_DAY.mean(), inplace = True)
df40.AMT_REQ_CREDIT_BUREAU_QRT.fillna(df40.AMT_REQ_CREDIT_BUREAU_QRT.mean(), inplace = True)
df40[['TARGET','AMT_REQ_CREDIT_BUREAU_DAY','AMT_REQ_CREDIT_BUREAU_QRT']].corr()
df40[['TARGET','AMT_REQ_CREDIT_BUREAU_DAY','AMT_REQ_CREDIT_BUREAU_QRT']].head(30)
mn.bar(df40,figsize=(40,6))<jupyter_output><empty_output><jupyter_text>Limpando eventuais NaNs que tenham sobrado<jupyter_code>df40.to_csv('application_train_40c6.csv') # guardo as alteracoes feitas<jupyter_output><empty_output><jupyter_text>Verificando as variavies Object para transformar em discreta<jupyter_code>df40 = pd.read_csv('application_train_40c6.csv',index_col=0)
df40.info()
set(df40.NAME_CONTRACT_TYPE)
df40['NAME_CONTRACT_TYPE_C'] = np.nan
df40.NAME_CONTRACT_TYPE_C.loc[df40.NAME_CONTRACT_TYPE == 'Cash loans'] = 1
df40.NAME_CONTRACT_TYPE_C.loc[df40.NAME_CONTRACT_TYPE != 'Cash loans'] = 0
df40['NAME_CONTRACT_TYPE_R'] = np.nan
df40.NAME_CONTRACT_TYPE_R.loc[df40.NAME_CONTRACT_TYPE == 'Revolving loans'] = 1
df40.NAME_CONTRACT_TYPE_R.loc[df40.NAME_CONTRACT_TYPE != 'Revolving loans'] = 0
df40[['TARGET','NAME_CONTRACT_TYPE_C','NAME_CONTRACT_TYPE_R']].corr()
set(df40.CODE_GENDER)
df40['CODE_GENDER_F'] = np.nan
df40.CODE_GENDER_F.loc[df40.CODE_GENDER == 'F'] = 1
df40.CODE_GENDER_F.loc[df40.CODE_GENDER != 'F'] = 0
df40['CODE_GENDER_M'] = np.nan
df40.CODE_GENDER_M.loc[df40.CODE_GENDER == 'M'] = 1
df40.CODE_GENDER_M.loc[df40.CODE_GENDER != 'M'] = 0
pd.crosstab(df40.TARGET,df40.CODE_GENDER_M) #,df40.CODE_GENDER_F)
pd.crosstab(df40.TARGET,df40.CODE_GENDER_F)
df40[['TARGET','CODE_GENDER_F','CODE_GENDER_M']].corr()
set(df40.FLAG_OWN_REALTY)
df40['FLAG_OWN_REALTY_Y'] = np.nan
df40.FLAG_OWN_REALTY_Y.loc[df40.FLAG_OWN_REALTY == 'Y'] = 1
df40.FLAG_OWN_REALTY_Y.loc[df40.FLAG_OWN_REALTY != 'Y'] = 0
df40['FLAG_OWN_REALTY_N'] = np.nan
df40.FLAG_OWN_REALTY_N.loc[df40.FLAG_OWN_REALTY == 'N'] = 1
df40.FLAG_OWN_REALTY_N.loc[df40.FLAG_OWN_REALTY != 'N'] = 0
pd.crosstab(df40.TARGET,df40.FLAG_OWN_REALTY)
df40[['TARGET','FLAG_OWN_REALTY_N','FLAG_OWN_REALTY_Y']].corr()
set(df40.NAME_TYPE_SUITE)
pd.crosstab(df40.TARGET,df40.NAME_TYPE_SUITE)
set(df40.NAME_INCOME_TYPE)
pd.crosstab(df40.TARGET,df40.NAME_INCOME_TYPE)
set(df40.NAME_EDUCATION_TYPE)
pd.crosstab(df40.TARGET,df40.NAME_EDUCATION_TYPE)
set(df40.OCCUPATION_TYPE)
pd.crosstab(df40.TARGET,df40.OCCUPATION_TYPE)
df40.to_csv('application_train_40c7.csv') # guardo as alteracoes feitas<jupyter_output><empty_output><jupyter_text>Verificando as correlacoes<jupyter_code>df40 = pd.read_csv('application_train_40c7.csv',index_col=0) #leio o arquivo com as alteracoes de carros, familias e dados do apartamento
df40.corr()
plt.matshow(df40.corr())
# Generate a mask for the upper triangle
mask = np.zeros_like(df40.corr(), dtype=np.bool)
mask[np.triu_indices_from(mask)] = True

# Set up the matplotlib figure
f, ax = plt.subplots(figsize=(11, 9))

# Generate a custom diverging colormap
cmap = sns.diverging_palette(220, 10, as_cmap=True)

# Draw the heatmap with the mask and correct aspect ratio
sns.heatmap(df40.corr().round(2), mask=mask, cmap=cmap, vmax=.3, center=0,
            square=True, linewidths=.5, cbar_kws={"shrink": .5})
df40.TARGET.plot(kind='box', subplots=True, )<jupyter_output><empty_output><jupyter_text>Juntando outras tabelas<jupyter_code>dfa1 = pd.read_csv('credit_card_balance.csv', index_col=0)
dfa1.shape
mn.bar(dfa1,figsize=(22,6))
dfa2 = pd.read_csv('installments_payments.csv', index_col=0)
dfa2.shape
mn.bar(dfa2,figsize=(7,6))
#dfr= pd.concat([df40, dfa1], axis=1, join='inner')
dfr = pd.merge(df40, dfa1, on=['SK_ID_CURR', 'SK_ID_CURR'])
dfr = 
dfr.shape
dfa2.shape<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12391802443991853, 'fraction_numerical': 0.04429735234215886, 'mean_word_length': 8.046056419113414, 'pattern_counts': {'":': 1, '<': 73, '<?xml version=': 0, '>': 73, 'https://': 1, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '168588', 'license': 'MIT', 'n_tokens_mistral': 7082, 'n_tokens_neox': 6499, 'n_words': 1039, 'path': 'AIA/Trabalho1-Beta3.ipynb', 'repo_name': 'ricardoslee/Trabalhos', 'size': '15712'}