<jupyter_start><jupyter_text>Expenses in closed companiesRecently we found out that there are many companies that are already closed or out of service, we are aiming to find if there are expenses made after the company situation as other than open.<jupyter_code>import pandas as pd
import numpy as np
from serenata_toolbox.datasets import fetch

fetch('2016-09-03-companies.xz', '../data')
fetch('2016-11-19-reimbursements.xz', '../data')
companies = pd.read_csv('../data/2016-09-03-companies.xz', low_memory=False)
reimbursements = pd.read_csv('../data/2016-11-19-reimbursements.xz',
                      dtype={'applicant_id': np.str,
                             'cnpj_cpf': np.str,
                             'congressperson_id': np.str,
                             'subquota_number': np.str},
                      low_memory=False)<jupyter_output><empty_output><jupyter_text>FormattingFormatting companies situation_date and reimbursements issue_date columns to correct date format (will be needed for a query later), and formatting the companies cpnj to a format without dash and dots.<jupyter_code>reimbursements['issue_date'] = pd.to_datetime(reimbursements['issue_date'], errors='coerce')
companies['situation_date'] = pd.to_datetime(companies['situation_date'], errors='coerce')
companies['cnpj'] = companies['cnpj'].str.replace(r'\D', '')
statuses = ['BAIXADA', 'NULA', 'SUSPENSA', 'INAPTA']
not_open = companies[companies['situation'].isin(statuses)]
not_open[['cnpj', 'situation_date','situation', 'situation_reason']].head(5)<jupyter_output><empty_output><jupyter_text>The column situation_date is the one that is interesting. Expenses made after that date should be considered suspicious.The inner join on merge will give reimbursements that were requested for out of service companies.<jupyter_code>dataset = pd.merge(reimbursements, not_open, how='inner',
                   left_on='cnpj_cpf', right_on='cnpj')
columns = ['congressperson_name', 'issue_date','cnpj', 'situation_date',
           'situation', 'situation_reason']
dataset[columns].head(10)
dataset.shape
dataset.iloc[0]<jupyter_output><empty_output><jupyter_text>Filtering suspicious reimbursementsWe have all reibursements requested for expenses made in companies that have situation other than "open".It is still necessary to check the reimbursement issue_date is "bigger" than the situation_date.<jupyter_code>expenses_in_closed_companies = dataset.query('issue_date > situation_date')
expenses_in_closed_companies[columns].head()
expenses_in_closed_companies.shape<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1102514506769826, 'fraction_numerical': 0.013926499032882012, 'mean_word_length': 5.084705882352941, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 18, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '285462', 'license': 'MIT', 'n_tokens_mistral': 848, 'n_tokens_neox': 769, 'n_words': 193, 'path': 'research/develop/2017-02-20-anaschwendler-expenses-in-closed-companies.ipynb', 'repo_name': 'richardljs/serenata-de-amor', 'size': '2585'}