<jupyter_start><jupyter_text>EDA - Rais**E**xploratory **D**ata **A**nalysis on RAIS Database - Florianópolis, SC - Brasil**Authors:**- <NAME>- <NAME>- <NAME> Dictionary: ftp://ftp.mtps.gov.br/pdet/microdados/RAIS/Layouts/v%C3%ADnculos/RAIS_vinculos_layout2018.xls  Imports<jupyter_code>import pandas as pd
import numpy as np

pd.set_option('max_rows', 200)<jupyter_output><empty_output><jupyter_text>Open Data<jupyter_code>df = pd.read_parquet('dados/rais_floripa_2018.parquet')
print(df.shape)
df.head(2)<jupyter_output>(432486, 16)<jupyter_text>Pre Processing<jupyter_code>CAT_FEATURES = ['CNAE 2.0 Subclasse', 'Escolaridade após 2005', 'Mês Admissão', 'Mês Desligamento', 'Motivo Desligamento', 'Município', 'Raça Cor', 'Sexo Trabalhador', 'Tamanho Estabelecimento', 'Tipo Defic', 'UF', 'CBO 2002']

for cat_feat in CAT_FEATURES:
    df[cat_feat] = df[cat_feat].astype('str')

df['Tempo Emprego'] = df['Tempo Emprego'].str.replace(',','.').astype('float')

df['Vl Remun Média Nom'] = df['Vl Remun Média Nom'].str.replace('.', '').str.replace(',','.').astype('float')<jupyter_output><empty_output><jupyter_text>Tamanho Estabelecimento<jupyter_code>df['Tamanho Estabelecimento'].value_counts()
df['Tamanho Estabelecimento'] = (
    df['Tamanho Estabelecimento']
    .map(
        {
            '1': 'ZERO',
            '2': 'ATE_4',
            '3': 'DE_5_A_9',
            '4': 'DE_10_A_19',
            '5': 'DE_20_A_49',
            '6': 'DE_50_A_99',
            '7': 'DE_100_A_249',
            '8': 'DE_250_A_499',
            '9': 'DE_500_A_999',
            '10': '1000_OU_MAIS',
            '-1': 'IGNORADO',
        }
    )
)
df['Tamanho Estabelecimento'].value_counts()<jupyter_output><empty_output><jupyter_text>CBO<jupyter_code>df['CBO 2002'] = df['CBO 2002'].apply(lambda x: 'Unknown' if x == '0000-1' else x)<jupyter_output><empty_output><jupyter_text>Analysis<jupyter_code># All from Florianopolis

df['Município'].value_counts()
# List Categories

for col in df.columns:
    col_type = df[col].dtype
    print(f'     ***Type: {col_type}')
    if ((col_type == 'O') or (hasattr(df[col], 'cat'))):
        print(f'     Top 5 of Column') #: {col}')
        display(df[col].value_counts(normalize=True)[:5]*100)
        print('-----')
        print('\n')
    else:
        print(f'     Describe Column: {col}')
        display(df[col].describe())
        print('-----')
        print('\n')
df[df['Qtd Hora Contr'] == 1]['CBO 2002'].value_counts()[:4]<jupyter_output><empty_output><jupyter_text>**513405**: Garçom**421125**: Operador de Caixa**422105**: Recepcionista Atendente**234520**: Prof. Ensino Superior<jupyter_code>df[df['Qtd Hora Contr'] == 1]['CNAE 2.0 Subclasse'].value_counts()[:4]<jupyter_output><empty_output><jupyter_text>**5611204**: Bares e outros estabelecimentos especializados em servir bebidas, sem entretenimento**5611201**: Restaurantes e similares**4711302**: Comércio varejista de mercadorias em geral, com predominância de produtos alimentícios - supermercados**5223100**: Estacionamento de veículos  Challenge<jupyter_code>df.head()
df.median()
## Add Escolaridade, Cnae...


def report_salary(df: pd.DataFrame, idade: int, cbo: str, raca: str, genero: str, threshold: int = 1) -> None:
    df_filtered = df[
        (df['Idade'] == idade) &
        (df['CBO 2002'] == cbo) &
        (df['Raça Cor'] == raca) &
        (df['Sexo Trabalhador'] == genero)
    ]
    
    row_count = df_filtered.shape[0]
    print(f'[debug] Rows after filter: {row_count}')

    # The filter returned few rows, so give it a second try!
    if row_count < threshold:
        print(f'[debug] The rows after filter are less than threshold!')
        df_filtered = _similar_cbo(df, idade, cbo, raca, genero)
        row_count = df_filtered.shape[0]
        print(f'[debug] Rows after second try: {row_count}')
    
    _print_report(df_filtered, 'Vl Remun Média Nom')
        
    return None


def _similar_cbo(df: pd.DataFrame, idade: int, cbo: str, raca: int, genero: int) -> pd.DataFrame:
    """Finds another CBO based on similarity.
    
    """
    # Filters without CBO
    df_filtered_no_cbo = df[
        (df['Idade'] == idade)
        & (df['Raça Cor'] == raca)
        & (df['Sexo Trabalhador'] == genero)
    ]
    
    # Get all CBOs with the filter above
    list_cbos = df_filtered_no_cbo['CBO 2002'].unique()
    
    # For all CBOs, find the most similar to original CBO
    cbo = int(cbo)
    closer_cbo = ''
    closer_cbo_score = 9999999

    for cbos in list_cbos:
        try:
            int_cbos = int(cbos)
        except:
            int_cbos = 9999999999
        diff_cbos = np.abs(int_cbos - cbo)
        if diff_cbos < closer_cbo_score:
            closer_cbo = cbos
            closer_cbo_score = diff_cbos
            print(f'[debug] Closer CBO: {cbos}')
            print(f'[debug] Closer CBO score: {closer_cbo_score}')

    # Return the data with all filters including new similar CBO together
    return df_filtered_no_cbo[df_filtered_no_cbo['CBO 2002'] == closer_cbo]


def _print_report(df: pd.DataFrame, col_print: str) -> None:
    print('\n')
    print('----- RESULTS -----')
    print(f"Median\t {df[col_print].median()}")
    print(f"Mean\t {df[col_print].mean()}")
    
    return None
report_salary(df, 23, '212205', '2', '1') # 212205
report_salary(df, 40, '211205', '2', '1') # 212205<jupyter_output>[debug] Rows after filter: 0
[debug] The rows after filter are less than threshold!
[debug] Closer CBO: 771105
[debug] Closer CBO score: 559900
[debug] Closer CBO: 513205
[debug] Closer CBO score: 302000
[debug] Closer CBO: 510310
[debug] Closer CBO score: 299105
[debug] Closer CBO: 341225
[debug] Closer CBO score: 130020
[debug] Closer CBO: 331110
[debug] Closer CBO score: 119905
[debug] Closer CBO: 317210
[debug] Closer CBO score: 106005
[debug] Closer CBO: 141510
[debug] Closer CBO score: 69695
[debug] Closer CBO: 261715
[debug] Closer CBO score: 50510
[debug] Closer CBO: 224120
[debug] Closer CBO score: 12915
[debug] Closer CBO: 212405
[debug] Closer CBO score: 1200
[debug] Closer CBO: 212215
[debug] Closer CBO score: 1010
[debug] Closer CBO: 211105
[debug] Closer CBO score: 100
[debug] Closer CBO: 211110
[debug] Closer CBO score: 95
[debug] Rows after second try: 2


----- RESULTS -----
Median	 12187.115
Mean	 12187.115
{'fraction_non_alphanumeric': 0.1516497461928934, 'fraction_numerical': 0.06091370558375635, 'mean_word_length': 3.6703703703703705, 'pattern_counts': {'":': 0, '<': 36, '<?xml version=': 0, '>': 37, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '312642', 'license': 'MIT', 'n_tokens_mistral': 2720, 'n_tokens_neox': 2417, 'n_words': 552, 'path': 'RAIS/results/2019-11-30-lfpelison/exploring_RAIS.ipynb', 'repo_name': 'dsfloripa/challenges', 'size': '6335'}