<jupyter_start><jupyter_text>Dataset  df_viagem<jupyter_code>df_viagem = pd.read_csv('./dataset/2020_Viagem.csv', delimiter=";", encoding='latin1')
df_viagem.info()
df_viagem = df_viagem.drop_duplicates(subset=['Identificador do processo de viagem'])
df_viagem = df_viagem.drop('Motivo', axis=1)
df_viagem = df_viagem.drop('Valor outros gastos', axis=1)
df_viagem = df_viagem.drop('Valor diárias', axis=1)
df_viagem = df_viagem.drop('Destinos', axis=1)
df_viagem = df_viagem.drop('Justificativa Urgência Viagem', axis=1)
df_viagem = df_viagem.drop('Código órgão solicitante', axis=1)
df_viagem = df_viagem.drop('Nome órgão solicitante', axis=1)
df_viagem = df_viagem.drop('Número da Proposta (PCDP)', axis=1)
df_viagem = df_viagem.reset_index(drop=True)
df_viagem = df_viagem.rename(columns={'Identificador do processo de viagem': 'id_viagem'})
df_viagem = df_viagem.replace({np.nan: "Sem informação"})
# Tratamento de texto: acentos e simbolos, lower case e strip
for column in df_viagem.columns:
    if df_viagem[column].dtypes == 'object':
        df_viagem[column] = df_viagem[column].apply(clean_text)
df_viagem['Período - Data de início'] = df_viagem['Período - Data de início'].apply(to_datetime)
df_viagem['Período - Data de fim'] = df_viagem['Período - Data de fim'].apply(to_datetime)
df_viagem = df_viagem.replace({"nao informado": "sem informacao"})
df_viagem = df_viagem.replace({"-1": "sem informacao"})
df_viagem = df_viagem.replace({-1: "sem informacao"})
df_viagem['Código do órgão superior'] = df_viagem['Código do órgão superior'].replace({"sem informacao": -1})
df_viagem['Valor passagens'] = df_viagem['Valor passagens'].apply(lambda x: float(x.replace(',','.')))
df_remove = df_viagem[df_viagem['CPF viajante'].str.contains(r'ID.*|\*\*\*.000.000-\*\*')]
lista_id_remove = list(df_remove['id_viagem'])
df_viagem = df_viagem.drop(index=df_remove.index)
df_viagem = df_viagem.reset_index(drop=True)
df_viagem.index += 1
df_viagem.info()
df_viagem.head()<jupyter_output><empty_output><jupyter_text>df_passagem<jupyter_code>df_passagem = pd.read_csv('./dataset/2020_Passagem.csv', delimiter=";", encoding='latin1')
df_passagem.info()
df_passagem = df_passagem[df_passagem['Identificador do processo de viagem'].isin(df_viagem['id_viagem'])]
df_passagem['Valor da passagem'] = df_passagem['Valor da passagem'].apply(lambda x: float(x.replace(',','.')))
df_passagem['Taxa de serviço'] = df_passagem['Taxa de serviço'].apply(lambda x: float(x.replace(',','.')))
df_passagem['Total'] = df_passagem['Valor da passagem'] + df_passagem['Taxa de serviço']
df_passagem = df_passagem.rename(columns={'Identificador do processo de viagem': 'id_viagem'})
df_passagem = df_passagem[~df_passagem['id_viagem'].isin(lista_id_remove)]
df_passagem = df_passagem.drop('Hora da emissão/compra', axis=1)
df_passagem = df_passagem.drop('Número da Proposta (PCDP)', axis=1)
df_passagem = df_passagem.drop('Data da emissão/compra', axis=1)
df_passagem = df_passagem.reset_index(drop=True)
df_passagem.index += 1
df_passagem['id_passagem'] = df_passagem.index
df_passagem = df_passagem.replace({np.nan: "Sem informação"})
# Tratamento de texto: acentos e simbolos, lower case e strip
for column in df_passagem.columns:
    if df_passagem[column].dtypes == 'object':
        df_passagem[column] = df_passagem[column].apply(clean_text)
df_passagem = df_passagem.replace({"nao informado": "sem informacao"})
df_passagem.info()
df_passagem.head()<jupyter_output><empty_output><jupyter_text>Analise e tabelas secundárias  Cidade, UF, Pais<jupyter_code>df_puc = df_passagem[['País - Origem ida', 'UF - Origem ida',
       'Cidade - Origem ida', 'País - Destino ida', 'UF - Destino ida',
       'Cidade - Destino ida', 'País - Origem volta', 'UF - Origem volta',
       'Cidade - Origem volta', 'Pais - Destino volta', 'UF - Destino volta',
       'Cidade - Destino volta']]
paises = list(set(list(df_puc['País - Origem ida'].unique()) + list(df_puc['País - Destino ida'].unique()) + list(df_puc['País - Origem volta'].unique()) + list(df_puc['Pais - Destino volta'].unique())))
df_paises= pd.DataFrame(paises, columns=['nome'])
df_paises.index += 1
df_paises['id_paises'] = df_paises.index
df_paises
uf = list(set(list(df_puc['UF - Origem ida'].unique()) + list(df_puc['UF - Destino ida'].unique()) + list(df_puc['UF - Origem volta'].unique()) + list(df_puc['Pais - Destino volta'].unique())))
df_uf = pd.DataFrame(uf, columns=['nome'])
df_uf.index += 1
df_uf['id_uf'] = df_uf.index
df_uf
cidades = list(set(list(df_puc['Cidade - Origem ida'].unique()) + list(df_puc['Cidade - Destino ida'].unique()) + list(df_puc['Cidade - Origem volta'].unique()) + list(df_puc['Pais - Destino volta'].unique())))
df_cidades = pd.DataFrame(cidades, columns=['nome'])
df_cidades.index += 1
df_cidades['id_cidade'] = df_cidades.index
df_cidades<jupyter_output><empty_output><jupyter_text>Ministério<jupyter_code>df_ministerios = df_viagem.copy()
df_ministerios = df_ministerios.filter(['Código do órgão superior', 'Nome do órgão superior'])
df_ministerios = df_ministerios.rename(columns=
                                       {'Código do órgão superior': 'id_ministerio', 
                                        'Nome do órgão superior': 'nome_ministerio'})
df_ministerios = df_ministerios.drop_duplicates(subset=['id_ministerio'])
df_ministerios.head()<jupyter_output><empty_output><jupyter_text>Pessoa<jupyter_code>df_pessoa = df_viagem.copy()
df_pessoa = df_pessoa.filter(['CPF viajante', 'Nome', 'Cargo', 'Função', 'Descrição Função'])
df_pessoa.head()
df_pessoa.drop_duplicates(subset=['CPF viajante'], inplace=True)
df_pessoa = df_pessoa.reset_index(drop=True)
df_pessoa_final = df_pessoa[['Nome', 'CPF viajante']]
df_pessoa_final.index += 1
df_pessoa_final['id_pessoa'] = df_pessoa_final.index
df_pessoa_final<jupyter_output><empty_output><jupyter_text>Cargo<jupyter_code>df_cargo = df_viagem[['Cargo']]
df_cargo = df_cargo.drop_duplicates(subset=['Cargo'])
df_cargo = df_cargo.reset_index(drop=True)
df_cargo.index += 1
df_cargo['id_cargo'] = df_cargo.index
df_cargo<jupyter_output><empty_output><jupyter_text>Função<jupyter_code>df_funcao = df_viagem[['Função', 'Descrição Função']]
df_funcao = df_funcao.drop_duplicates(subset=['Função'])
df_funcao = df_funcao.reset_index(drop=True)
df_funcao.index += 1
df_funcao['id_funcao'] = df_funcao.index
df_funcao<jupyter_output><empty_output><jupyter_text>Meio de transporte<jupyter_code>meio = df_passagem['Meio de transporte']
meio = list(meio.unique())
df_meio = pd.DataFrame(meio, columns=['nome'])
df_meio.index += 1

df_meio['id_meio'] = df_meio.index
df_meio<jupyter_output><empty_output><jupyter_text>Tabelas centralizadoras<jupyter_code>df_viagem.info()
df_passagem.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 107015 entries, 1 to 107015
Data columns (total 18 columns):
 #   Column                  Non-Null Count   Dtype  
---  ------                  --------------   -----  
 0   id_viagem               107015 non-null  int64  
 1   Meio de transporte      107015 non-null  object 
 2   País - Origem ida       107015 non-null  object 
 3   UF - Origem ida         107015 non-null  object 
 4   Cidade - Origem ida     107015 non-null  object 
 5   País - Destino ida      107015 non-null  object 
 6   UF - Destino ida        107015 non-null  object 
 7   Cidade - Destino ida    107015 non-null  object 
 8   País - Origem volta     107015 non-null  object 
 9   UF - Origem volta       107015 non-null  object 
 10  Cidade - Origem volta   107015 non-null  object 
 11  Pais - Destino volta    107015 non-null  object 
 12  UF - Destino volta      107015 non-null  object 
 13  Cidade - Destino volta  107015 non-null  object 
 14  Valor da passagem   [...]<jupyter_text>pagamento_table<jupyter_code>df_pag_final = df_passagem.copy()
df_pag_final
df_pag_final = df_pag_final[['Valor da passagem', 'Taxa de serviço', 'id_passagem']]
df_pag_final['id_pagamento'] = df_pag_final.index
df_pag_final<jupyter_output><empty_output><jupyter_text>PUC_table  Substituição passagem table<jupyter_code>df_passagem_final = df_passagem.copy()
# Subs pelo index de cada p,u,c
for i, row in df_passagem_final.iterrows():
    df_passagem_final.at[i, 'País - Origem ida'] = paises.index(row['País - Origem ida']) + 1
    df_passagem_final.at[i, 'País - Destino ida'] = paises.index(row['País - Destino ida']) + 1
    df_passagem_final.at[i, 'País - Origem volta'] = paises.index(row['País - Origem volta']) + 1
    df_passagem_final.at[i, 'Pais - Destino volta'] = paises.index(row['Pais - Destino volta']) + 1
    
    df_passagem_final.at[i, 'UF - Origem ida'] = uf.index(row['UF - Origem ida']) + 1
    df_passagem_final.at[i, 'UF - Destino ida'] = uf.index(row['UF - Destino ida']) + 1
    df_passagem_final.at[i, 'UF - Origem volta'] = uf.index(row['UF - Origem volta']) + 1
    df_passagem_final.at[i, 'UF - Destino volta'] = uf.index(row['UF - Destino volta']) + 1
    
    df_passagem_final.at[i, 'Cidade - Origem ida'] = cidades.index(row['Cidade - Origem ida']) + 1
    df_passagem_final.at[i, 'Cidade - Destino ida'] = cidades.index(row['Cidade - Destino ida']) + 1
    df_passagem_final.at[i, 'Cidade - Origem volta'] = cidades.index(row['Cidade - Origem volta']) + 1
    df_passagem_final.at[i, 'Cidade - Destino volta'] = cidades.index(row['Cidade - Destino volta']) + 1
df_passagem_final<jupyter_output><empty_output><jupyter_text>puc table<jupyter_code># Criação lista puc
puc_lista = []

for i, row in df_passagem_final.iterrows():
    puc_origem_ida = (row['País - Origem ida'], row['UF - Origem ida'], row['Cidade - Origem ida'])
    puc_destino_ida = (row['País - Destino ida'], row['UF - Destino ida'], row['Cidade - Destino ida'])
    puc_origem_volta = (row['País - Origem volta'], row['UF - Origem volta'], row['Cidade - Origem volta'])
    puc_destino_volta = (row['Pais - Destino volta'], row['UF - Destino volta'], row['Cidade - Destino volta'])
    
    puc_lista.append(puc_origem_ida)
    puc_lista.append(puc_destino_ida)
    puc_lista.append(puc_origem_volta)
    puc_lista.append(puc_destino_volta)
df_puc_final = pd.DataFrame(puc_lista, columns=['id_pais', 'id_uf', 'id_cidade'])
df_puc_final = df_puc_final.drop_duplicates()
df_puc_final = df_puc_final.reset_index(drop=True)
df_puc_final.index +=1
df_puc_final['id_puc'] = df_puc_final.index
df_puc_final<jupyter_output><empty_output><jupyter_text>CPF_table<jupyter_code>df_viagem_final = df_viagem.copy()<jupyter_output><empty_output><jupyter_text>Substituição viagem table<jupyter_code># Subs pelo index de cada c,p,f
pessoas = list(df_pessoa_final['CPF viajante'])
cargos = list(df_cargo['Cargo'])
funcoes = list(df_funcao['Função'])

for i, row in df_viagem_final.iterrows():
    df_viagem_final.at[i, 'CPF viajante'] = pessoas.index(row['CPF viajante']) + 1
    df_viagem_final.at[i, 'Cargo'] = cargos.index(row['Cargo']) + 1
    df_viagem_final.at[i, 'Função'] = funcoes.index(row['Função']) + 1
df_viagem_final<jupyter_output><empty_output><jupyter_text>cpf table<jupyter_code># Criação lista cpf
cpf_lista = []

for i, row in df_viagem_final.iterrows():
    cpf = (row['CPF viajante'], row['Cargo'], row['Função'])
    cpf_lista.append(cpf)
df_cpf = pd.DataFrame(cpf_lista, columns=['id_pessoa', 'id_cargo', 'id_funcao'])
df_cpf = df_cpf.drop_duplicates()
df_cpf = df_cpf.reset_index(drop=True)
df_cpf.index +=1
df_cpf['id_cpf'] = df_cpf.index
df_cpf<jupyter_output><empty_output><jupyter_text>passagem_table<jupyter_code>df_passagem_final_2 = df_passagem_final.copy()<jupyter_output><empty_output><jupyter_text>Meio de transporte merge<jupyter_code>df_passagem_final_2 = pd.merge(df_passagem_final_2, df_meio, left_on='Meio de transporte', how='left',right_on='nome')
df_passagem_final_2 = df_passagem_final_2.drop('Total', axis=1)
df_passagem_final_2 = df_passagem_final_2.drop('Meio de transporte', axis=1)
df_passagem_final_2 = df_passagem_final_2.drop('Valor da passagem', axis=1)
df_passagem_final_2 = df_passagem_final_2.drop('Taxa de serviço', axis=1)
df_passagem_final_2 = df_passagem_final_2.drop('nome', axis=1)
df_passagem_final_2.index += 1
df_passagem_final_2<jupyter_output><empty_output><jupyter_text>PUC merge<jupyter_code>df_puc_final
df_puc_merge = df_passagem_final_2.copy()
# Origem ida
df_puc_merge = pd.merge(df_puc_merge, df_puc_final, 
                        left_on=['País - Origem ida', 'UF - Origem ida', 'Cidade - Origem ida'], 
                        right_on=['id_pais', 'id_uf', 'id_cidade'],
                        how='left')

df_puc_merge = df_puc_merge.rename(columns={'id_puc': 'id_puc_origem_ida'})
# Destino ida
df_puc_merge = pd.merge(df_puc_merge, df_puc_final, 
                        left_on=['País - Destino ida', 'UF - Destino ida', 'Cidade - Destino ida'], 
                        right_on=['id_pais', 'id_uf', 'id_cidade'],
                        how='left')

df_puc_merge = df_puc_merge.rename(columns={'id_puc': 'id_puc_destino_ida'})
# Origem volta
df_puc_merge = pd.merge(df_puc_merge, df_puc_final, 
                        left_on=['País - Origem volta', 'UF - Origem volta', 'Cidade - Origem volta'], 
                        right_on=['id_pais', 'id_uf', 'id_cidade'],
                        how='left')

df_puc_merge = df_puc_merge.rename(columns={'id_puc': 'id_puc_origem_volta'})
# Destino volta
df_puc_merge = pd.merge(df_puc_merge, df_puc_final, 
                        left_on=['Pais - Destino volta', 'UF - Destino volta', 'Cidade - Destino volta'], 
                        right_on=['id_pais', 'id_uf', 'id_cidade'],
                        how='left')

df_puc_merge = df_puc_merge.rename(columns={'id_puc': 'id_puc_destino_volta'})
df_puc_merge = df_puc_merge[['id_passagem', 
                             'id_viagem', 
                             'id_meio', 
                             'id_puc_origem_ida', 
                             'id_puc_destino_ida', 
                             'id_puc_origem_volta', 
                             'id_puc_destino_volta']]
df_puc_merge.index += 1
df_passagem_final = df_puc_merge
df_passagem_final<jupyter_output><empty_output><jupyter_text>viagem_table<jupyter_code>df_viagem_final_2 = df_viagem_final.copy()<jupyter_output><empty_output><jupyter_text>CPF merge<jupyter_code># Origem ida
df_cpf_merge = pd.merge(df_viagem_final_2, df_cpf, 
                        left_on=['CPF viajante', 'Cargo', 'Função'], 
                        right_on=['id_pessoa', 'id_cargo', 'id_funcao'],
                        how='left')
df_cpf_merge.index += 1
df_cpf_merge<jupyter_output><empty_output><jupyter_text>Finalização<jupyter_code>df_viagem_final = df_cpf_merge[['id_viagem', 
                             'Situação', 
                             'Viagem Urgente', 
                             'Período - Data de início', 
                             'Período - Data de fim', 
                             'Código do órgão superior',
                             'id_cpf', 
                             'Valor passagens']]
df_viagem_final = df_viagem_final.rename(columns={"Situação": 'is_realizado', 
                                                  'Viagem Urgente': 'is_urgente', 
                                                  'Período - Data de início': 'data_inicio', 
                                                  'Período - Data de fim': 'data_fim',
                                                  'Código do órgão superior': 'id_ministerio', 
                                                  'Valor passagens': 'total'})
df_viagem_final['is_realizado'] = df_viagem_final['is_realizado'].map({"nao realizada": False, "realizada": True})
df_viagem_final['is_urgente'] = df_viagem_final['is_urgente'].map({"nao": False, "sim": True})
df_viagem_final<jupyter_output><empty_output><jupyter_text>Resumo das tabelas  Viagem, passagem e pagamento<jupyter_code>df_viagem_final
df_passagem_final
df_pag_final = df_pag_final.rename(columns={'Taxa de serviço': 'taxa', 'Valor da passagem': 'valor',})
df_pag_final = df_pag_final[['id_pagamento', 'valor', 'taxa', 'id_passagem']]
df_pag_final<jupyter_output><empty_output><jupyter_text>CPF e PUC<jupyter_code>df_cpf = df_cpf[['id_cpf', 'id_pessoa', 'id_cargo', 'id_funcao']]
df_cpf
df_puc_final = df_puc_final[['id_puc', 'id_pais', 'id_uf', 'id_cidade',]]
df_puc_final<jupyter_output><empty_output><jupyter_text>Resto<jupyter_code>df_paises = df_paises[['id_paises', 'nome']]
df_paises
df_uf = df_uf[['id_uf', 'nome']]
df_uf
df_cidades = df_cidades[['id_cidade', 'nome']]
df_cidades
df_pessoa_final = df_pessoa_final.rename(columns={'Nome': 'nome', 'CPF viajante': 'cpf',})
df_pessoa_final = df_pessoa_final[['id_pessoa', 'nome', 'cpf']]
df_pessoa_final
df_cargo = df_cargo.rename(columns={'Cargo': 'nome', })
df_cargo = df_cargo[['id_cargo', 'nome']]
df_cargo
df_funcao = df_funcao.rename(columns={'Descrição Função': 'descricao', 'Função': 'nome'})
df_funcao = df_funcao[['id_funcao', 'nome', 'descricao']]
df_funcao
df_ministerios = df_ministerios.rename(columns={'nome_ministerio': 'nome'})
df_ministerios
df_meio = df_meio[['id_meio', 'nome']]
df_meio.head()<jupyter_output><empty_output><jupyter_text>Salvando<jupyter_code>df_viagem_final.to_csv('./dataset/gerados/viagem.csv')
df_passagem_final.to_csv('./dataset/gerados/passagem.csv')
df_pag_final.to_csv('./dataset/gerados/pagamento.csv')
df_cpf.to_csv('./dataset/gerados/cpf.csv')
df_puc_final.to_csv('./dataset/gerados/puc.csv')
df_paises.to_csv('./dataset/gerados/paises.csv')
df_uf.to_csv('./dataset/gerados/uf.csv')
df_cidades.to_csv('./dataset/gerados/cidades.csv')
df_pessoa_final.to_csv('./dataset/gerados/pessoa.csv')
df_cargo.to_csv('./dataset/gerados/cargo.csv')
df_funcao.to_csv('./dataset/gerados/funcao.csv')
df_ministerios.to_csv('./dataset/gerados/ministerio.csv')
df_meio.to_csv('./dataset/gerados/meio.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14330567391791882, 'fraction_numerical': 0.011381475667189953, 'mean_word_length': 4.423228944968075, 'pattern_counts': {'":': 9, '<': 101, '<?xml version=': 0, '>': 101, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '25', 'dir': 'jupyter-structured-clean-dedup', 'id': '606271', 'license': 'MIT', 'n_tokens_mistral': 7241, 'n_tokens_neox': 7053, 'n_words': 1103, 'path': 'fbd_grupo/Extract, transform.ipynb', 'repo_name': 'Leothi/fbd_2020', 'size': '17836'}