03_gerando_csv.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Criação das planilhas CSV para importação\n",
    "\n",
    "Agora que as planilhas fictícias foram geradas e o tratamento na planilha de salário/cargo feito, é possível pensar no problema e começar a criação dos arquivos CSV para o time de TI completar a importação. "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Funcionários\n",
    "\n",
    "Cada funcionário precisa receber um ID que vai ser utilizado em quase todas as outras planilhas, afim de identificação. Então vou chamar o primeiro CSV de \"funcionarios\" e ele vai ser salvo na pasta \"output\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''Importando todos os pacotes'''\n",
    "import warnings\n",
    "import os\n",
    "from datetime import date\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import uuid\n",
    "\n",
    "pd.set_option('display.max_columns', 40) # Exibe todas as colunas\n",
    "# Ignora o seguinte alerta do Pandas\n",
    "# FutureWarning: In a future version of pandas all arguments of DataFrame.drop except for the argument 'labels' will be keyword-only.\n",
    "warnings.simplefilter(action='ignore', category=FutureWarning)\n",
    "\n",
    "'''\n",
    "Como vou precisar do ID não só para cada funcionário, mas também um ID para cada planilha,\n",
    "e possivelmente para cada cargo, a lógica vai sempre se repetir.\n",
    "Por essa razão é interessante criar uma função. \n",
    "\n",
    "'''\n",
    "\n",
    "def get_uuid4(\n",
    "    dataframe:pd.DataFrame,\n",
    "    check_column_name:str,\n",
    "    fill_column_name:str)->pd.DataFrame:\n",
    "    '''\n",
    "    Usa o UUID para gerar uma identificação aleatória.\n",
    "    O ID do funcionário é único para cada funcionário, \n",
    "    este ID é uma referência para outras planilhas.\n",
    "    \n",
    "    1. Esta função precisa passar o DataFrame onde os IDs serão gerados.\n",
    "    2. Nome da coluna que é usada como parâmetro do loop.\n",
    "    3. Nome da coluna onde os IDs gerados serão contidos.\n",
    "    '''\n",
    "    for inx, rows in dataframe.iterrows():\n",
    "        if rows[check_column_name] != '':\n",
    "            dataframe.loc[inx, fill_column_name] = uuid.uuid4()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>Nome</th>\n",
       "      <th>Telefone</th>\n",
       "      <th>Email</th>\n",
       "      <th>Situação</th>\n",
       "      <th>Criado_em</th>\n",
       "      <th>Upload_em</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a16a156e-aa4a-40c5-94a4-4bea0f443f39</td>\n",
       "      <td>Lucas Gabriel da Rosa</td>\n",
       "      <td>+55 (081) 0623-4078</td>\n",
       "      <td>viniciussales@example.com</td>\n",
       "      <td>Ativo</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>727208ef-44e7-4c85-8c25-199f7011e9f5</td>\n",
       "      <td>Clarice Almeida</td>\n",
       "      <td>(021) 1104-9825</td>\n",
       "      <td>vrezende@example.net</td>\n",
       "      <td>Ativo</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ad64f750-8850-444b-9bb1-536951a1819d</td>\n",
       "      <td>João Lucas Cunha</td>\n",
       "      <td>+55 11 5068 0895</td>\n",
       "      <td>isabellapeixoto@example.net</td>\n",
       "      <td>Ativo</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>945e364e-0bc3-4ec7-a693-32fc0f8db8a1</td>\n",
       "      <td>Cecília Fogaça</td>\n",
       "      <td>(061) 7064-1466</td>\n",
       "      <td>luizacosta@example.net</td>\n",
       "      <td>Ativo</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>f5fcda1f-5ef0-4e87-9768-70e048f232ae</td>\n",
       "      <td>Isadora Cavalcanti</td>\n",
       "      <td>+55 71 5348-5632</td>\n",
       "      <td>caio52@example.com</td>\n",
       "      <td>Ativo</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id                   Nome  \\\n",
       "0  a16a156e-aa4a-40c5-94a4-4bea0f443f39  Lucas Gabriel da Rosa   \n",
       "1  727208ef-44e7-4c85-8c25-199f7011e9f5        Clarice Almeida   \n",
       "2  ad64f750-8850-444b-9bb1-536951a1819d       João Lucas Cunha   \n",
       "3  945e364e-0bc3-4ec7-a693-32fc0f8db8a1         Cecília Fogaça   \n",
       "4  f5fcda1f-5ef0-4e87-9768-70e048f232ae     Isadora Cavalcanti   \n",
       "\n",
       "              Telefone                        Email Situação   Criado_em  \\\n",
       "0  +55 (081) 0623-4078    viniciussales@example.com    Ativo  01-30-2023   \n",
       "1      (021) 1104-9825         vrezende@example.net    Ativo  01-30-2023   \n",
       "2     +55 11 5068 0895  isabellapeixoto@example.net    Ativo  01-30-2023   \n",
       "3      (061) 7064-1466       luizacosta@example.net    Ativo  01-30-2023   \n",
       "4     +55 71 5348-5632           caio52@example.com    Ativo  01-30-2023   \n",
       "\n",
       "    Upload_em  \n",
       "0  01-30-2023  \n",
       "1  01-30-2023  \n",
       "2  01-30-2023  \n",
       "3  01-30-2023  \n",
       "4  01-30-2023  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_excel(\"planilhas\\\\rh_main.xlsx\")\n",
    "\n",
    "# Cria duas novas colunas no dataframe, para preencher com os IDs.\n",
    "data['employee_id'] = '' \n",
    "data['leader_id'] = ''\n",
    "\n",
    "# Também será criado duas colunas novas contendo a data que o script for rodado.\n",
    "# Garante a diferenciação dos funcinários importados e dos novos registros feitos no novo sistema.\n",
    "today = date.today().strftime(\"%m-%d-%Y\")\n",
    "\n",
    "data.replace('',np.nan,inplace=True)\n",
    "\n",
    "# Usa a função criada, para atribuir o ID na coluna nova\n",
    "get_uuid4(data,'Nome Funcionário','employee_id')\n",
    "\n",
    "#--------------------Gerando o CSV de Employee --------------------#\n",
    "employee_sheet = pd.DataFrame({'id': data['employee_id'],\n",
    "                            'Nome': data['Nome Funcionário'],\n",
    "                            'Telefone': data['Celular'],\n",
    "                            'Email': data['E-Mail'],\n",
    "                            'Situação': data['Situação'],\n",
    "                            'Criado_em':today,\n",
    "                            'Upload_em':today})\n",
    "\n",
    "# Save the new spreadsheet\n",
    "employee_sheet.to_csv('output\\\\funcionarios.csv', index=False)\n",
    "\n",
    "employee_sheet.head(5)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dados pessoais\n",
    "\n",
    "Agora com a planilha de funcionarios gerada, podemos criar a próxima que contém os dados pessoais de cada empregado. Vou utilizar o **pandas.merge** aqui, porque existem dados pessoais em mais de uma planilha. \n",
    "\n",
    "O merge serve para unir duas planilhas escolhendo uma coluna de igualdade. O parâmetro 'on' define que o merge vai acontecer de acordo com a coluna escolhida. Em outras palavras ele vai realizar uma junção parecida com PROCV do Excel.\n",
    "\n",
    "Se tiver um macth nessa coluna o pandas vai trazer os dados das demais. Com o parâmetro 'how=left' o dataframe da esquerda vai ser mantido, e as informações do dataframde da direita vão fazer parte do df da esquerda, caso eu quisse o contrário poderia passar how=right."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>employee_id</th>\n",
       "      <th>CPF</th>\n",
       "      <th>Nome</th>\n",
       "      <th>Email</th>\n",
       "      <th>Telefone</th>\n",
       "      <th>RG</th>\n",
       "      <th>Nacionalidade</th>\n",
       "      <th>Dt. Nascimento</th>\n",
       "      <th>Sexo</th>\n",
       "      <th>Raça e Cor</th>\n",
       "      <th>Estado Civil</th>\n",
       "      <th>Mãe</th>\n",
       "      <th>Pai</th>\n",
       "      <th>Endereço Completo</th>\n",
       "      <th>Criado_em</th>\n",
       "      <th>Upload_em</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>effbe3ff-c530-4872-a73d-cba6a4f7e785</td>\n",
       "      <td>a16a156e-aa4a-40c5-94a4-4bea0f443f39</td>\n",
       "      <td>42116450377</td>\n",
       "      <td>Lucas Gabriel da Rosa</td>\n",
       "      <td>viniciussales@example.com</td>\n",
       "      <td>+55 (081) 0623-4078</td>\n",
       "      <td>59530754</td>\n",
       "      <td>Brasileiro(a)</td>\n",
       "      <td>1972-05-19</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>Branco(a)</td>\n",
       "      <td>Casado(a)</td>\n",
       "      <td>Srta. Juliana da Conceição</td>\n",
       "      <td>Thomas Teixeira</td>\n",
       "      <td>Esplanada Novaes, 17\\nDona Clara\\n14010-821 Cu...</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>c9f4e84e-b16d-4820-aa56-1febbabddc29</td>\n",
       "      <td>727208ef-44e7-4c85-8c25-199f7011e9f5</td>\n",
       "      <td>78286620035</td>\n",
       "      <td>Clarice Almeida</td>\n",
       "      <td>vrezende@example.net</td>\n",
       "      <td>(021) 1104-9825</td>\n",
       "      <td>58917100</td>\n",
       "      <td>Brasileiro(a)</td>\n",
       "      <td>2018-02-21</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>Branco(a)</td>\n",
       "      <td>Viúvo(a)</td>\n",
       "      <td>Maitê da Cruz</td>\n",
       "      <td>Igor da Cruz</td>\n",
       "      <td>Ladeira de Gonçalves\\nVila Fumec\\n00081-863 Ca...</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29d0d7b5-8ce0-485d-8de1-11518ca36ba6</td>\n",
       "      <td>ad64f750-8850-444b-9bb1-536951a1819d</td>\n",
       "      <td>71222935371</td>\n",
       "      <td>João Lucas Cunha</td>\n",
       "      <td>isabellapeixoto@example.net</td>\n",
       "      <td>+55 11 5068 0895</td>\n",
       "      <td>23612766</td>\n",
       "      <td>Brasileiro(a)</td>\n",
       "      <td>2005-04-28</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>Preto(a)</td>\n",
       "      <td>Casado(a)</td>\n",
       "      <td>Yasmin Aragão</td>\n",
       "      <td>Matheus Vieira</td>\n",
       "      <td>Residencial Luiz Henrique Ramos, 124\\nVila Nov...</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f0ccc531-3922-4f94-867e-175d6a786ffe</td>\n",
       "      <td>945e364e-0bc3-4ec7-a693-32fc0f8db8a1</td>\n",
       "      <td>37308822484</td>\n",
       "      <td>Cecília Fogaça</td>\n",
       "      <td>luizacosta@example.net</td>\n",
       "      <td>(061) 7064-1466</td>\n",
       "      <td>69434628</td>\n",
       "      <td>Brasileiro(a)</td>\n",
       "      <td>1987-02-06</td>\n",
       "      <td>Masculino</td>\n",
       "      <td>Preto(a)</td>\n",
       "      <td>Casado(a)</td>\n",
       "      <td>Esther Souza</td>\n",
       "      <td>Sr. Isaac da Cruz</td>\n",
       "      <td>Praça de Rezende, 38\\nCaiçaras\\n61521803 Araúj...</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>589a0708-dbf6-4bb6-8211-878f6a299474</td>\n",
       "      <td>f5fcda1f-5ef0-4e87-9768-70e048f232ae</td>\n",
       "      <td>22628655033</td>\n",
       "      <td>Isadora Cavalcanti</td>\n",
       "      <td>caio52@example.com</td>\n",
       "      <td>+55 71 5348-5632</td>\n",
       "      <td>49289584</td>\n",
       "      <td>Brasileiro(a)</td>\n",
       "      <td>2013-02-14</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>Preto(a)</td>\n",
       "      <td>Divorciado(a)</td>\n",
       "      <td>Maria Alice Costela</td>\n",
       "      <td>Vicente Duarte</td>\n",
       "      <td>Praia de Souza, 19\\nCentro\\n81589-994 da Mota ...</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id                           employee_id  \\\n",
       "0  effbe3ff-c530-4872-a73d-cba6a4f7e785  a16a156e-aa4a-40c5-94a4-4bea0f443f39   \n",
       "1  c9f4e84e-b16d-4820-aa56-1febbabddc29  727208ef-44e7-4c85-8c25-199f7011e9f5   \n",
       "2  29d0d7b5-8ce0-485d-8de1-11518ca36ba6  ad64f750-8850-444b-9bb1-536951a1819d   \n",
       "3  f0ccc531-3922-4f94-867e-175d6a786ffe  945e364e-0bc3-4ec7-a693-32fc0f8db8a1   \n",
       "4  589a0708-dbf6-4bb6-8211-878f6a299474  f5fcda1f-5ef0-4e87-9768-70e048f232ae   \n",
       "\n",
       "           CPF                   Nome                        Email  \\\n",
       "0  42116450377  Lucas Gabriel da Rosa    viniciussales@example.com   \n",
       "1  78286620035        Clarice Almeida         vrezende@example.net   \n",
       "2  71222935371       João Lucas Cunha  isabellapeixoto@example.net   \n",
       "3  37308822484         Cecília Fogaça       luizacosta@example.net   \n",
       "4  22628655033     Isadora Cavalcanti           caio52@example.com   \n",
       "\n",
       "              Telefone        RG  Nacionalidade Dt. Nascimento       Sexo  \\\n",
       "0  +55 (081) 0623-4078  59530754  Brasileiro(a)     1972-05-19   Feminino   \n",
       "1      (021) 1104-9825  58917100  Brasileiro(a)     2018-02-21   Feminino   \n",
       "2     +55 11 5068 0895  23612766  Brasileiro(a)     2005-04-28   Feminino   \n",
       "3      (061) 7064-1466  69434628  Brasileiro(a)     1987-02-06  Masculino   \n",
       "4     +55 71 5348-5632  49289584  Brasileiro(a)     2013-02-14   Feminino   \n",
       "\n",
       "  Raça e Cor   Estado Civil                         Mãe                Pai  \\\n",
       "0  Branco(a)      Casado(a)  Srta. Juliana da Conceição    Thomas Teixeira   \n",
       "1  Branco(a)       Viúvo(a)               Maitê da Cruz       Igor da Cruz   \n",
       "2   Preto(a)      Casado(a)               Yasmin Aragão     Matheus Vieira   \n",
       "3   Preto(a)      Casado(a)                Esther Souza  Sr. Isaac da Cruz   \n",
       "4   Preto(a)  Divorciado(a)         Maria Alice Costela     Vicente Duarte   \n",
       "\n",
       "                                   Endereço Completo   Criado_em   Upload_em  \n",
       "0  Esplanada Novaes, 17\\nDona Clara\\n14010-821 Cu...  01-30-2023  01-30-2023  \n",
       "1  Ladeira de Gonçalves\\nVila Fumec\\n00081-863 Ca...  01-30-2023  01-30-2023  \n",
       "2  Residencial Luiz Henrique Ramos, 124\\nVila Nov...  01-30-2023  01-30-2023  \n",
       "3  Praça de Rezende, 38\\nCaiçaras\\n61521803 Araúj...  01-30-2023  01-30-2023  \n",
       "4  Praia de Souza, 19\\nCentro\\n81589-994 da Mota ...  01-30-2023  01-30-2023  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Além da planilha principal vamos usar outra planilha, a de raça e cor. \n",
    "raca_cor = pd.read_excel('planilhas\\\\raca_cor_dependentes.xlsx')\n",
    "\n",
    "# Chamando método melt\n",
    "data_raca = data.merge(raca_cor,\n",
    "                    on='Nome Funcionário',\n",
    "                    how='left')\n",
    "\n",
    "#--------------------Generating the personalData Spreadsheet--------------------#\n",
    "personaldata_sheet = pd.DataFrame({'employee_id': data_raca['employee_id'],\n",
    "                                'CPF': data_raca['CPF'],                        \n",
    "                                'Nome': data_raca['Nome Funcionário'],\n",
    "                                'Email': data_raca['E-Mail'],\n",
    "                                'Telefone': data_raca['Celular'],\n",
    "                                'RG': data_raca['RG'],\n",
    "                                'Nacionalidade': data_raca['Nacionalidade'],\n",
    "                                'Dt. Nascimento': data_raca['Dt. Nascimento'],\n",
    "                                'Sexo': data_raca['Sexo'],\n",
    "                                'Raça e Cor': data_raca['Raça/Cor'],\n",
    "                                'Estado Civil': data_raca['Est. Civil'],\n",
    "                                'Mãe': data_raca['Mãe'],\n",
    "                                'Pai': data_raca['Pai'],\n",
    "                                'Endereço Completo': data_raca['Endereço'],\n",
    "                                'Criado_em': today,\n",
    "                                'Upload_em': today})\n",
    "\n",
    "\n",
    "personaldata_sheet.insert(0,'id','') #Creat the new column on dataframe\n",
    "\n",
    "get_uuid4(personaldata_sheet,'employee_id','id') # Use the function to generate the ID\n",
    "\n",
    "# Save the new spreadsheet\n",
    "personaldata_sheet.to_csv('output\\\\dados_pessoais.csv', index=False)\n",
    "\n",
    "personaldata_sheet.head(5)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dependentes\n",
    "\n",
    "Na planilha anterior, com os dados pessoais, também constam informações dos dependêntes. Nesse caso se chamar **data_raca** essa informações de cada um dos funcionários também vão estar presentes. Para gerar esse CSV vou usar **pd.melt**. Na planilha \"raca_cor_dependentes\", criada anteriormente, possui uma organização onde cada linha representa um único funcionário, e contém todas as demais informações. \n",
    "\n",
    "![raca_cor](imagens/raca_cor.JPG)\n",
    "\n",
    "O primeiro funcionário, Lucas Gabriel da Rosa então tem três dependentes. Mas não quero que essa planilha mantenha essa organização. Para que seja importada corretamente, preciso que o CSV tenha somente uma coluna de dependentes, conforme a imagem abaixo:\n",
    "\n",
    "![raca_cor_ideal](imagens/raca_cor_ideal.JPG)\n",
    "\n",
    "E é exatamente isso que o método melt irá fazer. Vai organizar o dataframe saindo de um formato amplo para um longo. Depois de usar o pd.melt e agrupar o trio de colunas e uma. Vou utilizar o concat do pandas para unir essas três novas colunas (contendo todas as informações dos dependentes) no novo CSV."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>employee_id</th>\n",
       "      <th>Nome Dependentes</th>\n",
       "      <th>Possui Dependente?</th>\n",
       "      <th>CPF Dependente</th>\n",
       "      <th>Parentesco Dependente</th>\n",
       "      <th>Dt. Nascimento Dependentes</th>\n",
       "      <th>Criado_em</th>\n",
       "      <th>Upload_em</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7ea78c49-4118-4dfd-8a49-785de09eb014</td>\n",
       "      <td>a16a156e-aa4a-40c5-94a4-4bea0f443f39</td>\n",
       "      <td>Gabrielly Lopes</td>\n",
       "      <td>True</td>\n",
       "      <td>40784258520.0</td>\n",
       "      <td>Filho(a) menos de 21 anos</td>\n",
       "      <td>1979-10-31</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>d741fb15-c1d3-4160-9149-2fb77d18918e</td>\n",
       "      <td>727208ef-44e7-4c85-8c25-199f7011e9f5</td>\n",
       "      <td>Sophie Nunes</td>\n",
       "      <td>True</td>\n",
       "      <td>88215581447.0</td>\n",
       "      <td>Filho(a) mais de 21 anos</td>\n",
       "      <td>2003-07-04</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>98e497fe-d1a0-4218-b591-c8d7a078b15e</td>\n",
       "      <td>ad64f750-8850-444b-9bb1-536951a1819d</td>\n",
       "      <td>Cecília Duarte</td>\n",
       "      <td>True</td>\n",
       "      <td>33383153282.0</td>\n",
       "      <td>Filho(a) mais de 21 anos</td>\n",
       "      <td>2019-07-25</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>393a1390-c358-44c4-8f72-b9fcec33cfbb</td>\n",
       "      <td>945e364e-0bc3-4ec7-a693-32fc0f8db8a1</td>\n",
       "      <td>Matheus Rezende</td>\n",
       "      <td>True</td>\n",
       "      <td>78384204184.0</td>\n",
       "      <td>Filho(a) menos de 21 anos</td>\n",
       "      <td>2004-05-18</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ddd28bec-0e51-4c3b-adca-8b24fdf6293e</td>\n",
       "      <td>f5fcda1f-5ef0-4e87-9768-70e048f232ae</td>\n",
       "      <td>Lavínia da Mota</td>\n",
       "      <td>True</td>\n",
       "      <td>90655319469.0</td>\n",
       "      <td>Filho(a) menos de 21 anos</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id                           employee_id  \\\n",
       "0  7ea78c49-4118-4dfd-8a49-785de09eb014  a16a156e-aa4a-40c5-94a4-4bea0f443f39   \n",
       "1  d741fb15-c1d3-4160-9149-2fb77d18918e  727208ef-44e7-4c85-8c25-199f7011e9f5   \n",
       "2  98e497fe-d1a0-4218-b591-c8d7a078b15e  ad64f750-8850-444b-9bb1-536951a1819d   \n",
       "3  393a1390-c358-44c4-8f72-b9fcec33cfbb  945e364e-0bc3-4ec7-a693-32fc0f8db8a1   \n",
       "4  ddd28bec-0e51-4c3b-adca-8b24fdf6293e  f5fcda1f-5ef0-4e87-9768-70e048f232ae   \n",
       "\n",
       "  Nome Dependentes Possui Dependente? CPF Dependente  \\\n",
       "0  Gabrielly Lopes               True  40784258520.0   \n",
       "1     Sophie Nunes               True  88215581447.0   \n",
       "2   Cecília Duarte               True  33383153282.0   \n",
       "3  Matheus Rezende               True  78384204184.0   \n",
       "4  Lavínia da Mota               True  90655319469.0   \n",
       "\n",
       "       Parentesco Dependente Dt. Nascimento Dependentes   Criado_em  \\\n",
       "0  Filho(a) menos de 21 anos                 1979-10-31  01-30-2023   \n",
       "1   Filho(a) mais de 21 anos                 2003-07-04  01-30-2023   \n",
       "2   Filho(a) mais de 21 anos                 2019-07-25  01-30-2023   \n",
       "3  Filho(a) menos de 21 anos                 2004-05-18  01-30-2023   \n",
       "4  Filho(a) menos de 21 anos                 2021-01-03  01-30-2023   \n",
       "\n",
       "    Upload_em  \n",
       "0  01-30-2023  \n",
       "1  01-30-2023  \n",
       "2  01-30-2023  \n",
       "3  01-30-2023  \n",
       "4  01-30-2023  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#--------------------Gerando CSV de Dependentes--------------------#\n",
    "# Usando o pandas melt para mudar a disposição do dataframe\n",
    "# Perceba que a união dessas colunas vai ser pelo employee_id, é a variável indicadora. \n",
    "dependenttype_column = pd.melt(data_raca.reset_index(),\n",
    "                            id_vars=['employee_id'],\n",
    "                            value_vars=['Parentesco Dependente 1',\n",
    "                                        'Parentesco Dependente 2',\n",
    "                                        'Parentesco Dependente 3'],\n",
    "                            value_name='Parentesco Dependente').drop('variable',1)\n",
    "\n",
    "cpf_dependent = pd.melt(data_raca.reset_index(),\n",
    "                    id_vars=['employee_id'],\n",
    "                    value_vars=['CPF Dependente 1',\n",
    "                                'CPF Dependente 2',\n",
    "                                'CPF Dependente 3'],\n",
    "                    value_name='CPF Dependente').drop('variable',1)\n",
    "\n",
    "name_dependet = pd.melt(data_raca.reset_index(),\n",
    "                    id_vars=['employee_id'],\n",
    "                    value_vars=['Nome Dependente 1',\n",
    "                                'Nome Dependente 2',\n",
    "                                'Nome Dependente 3'],\n",
    "                    value_name='Nome Dependentes').drop('variable',1)\n",
    "\n",
    "birthdate_dependet = pd.melt(data_raca.reset_index(),\n",
    "                    id_vars = ['employee_id'],\n",
    "                    value_vars = ['Nascimento Dependente 1',\n",
    "                                'Nascimento Dependente 2',\n",
    "                                'Nascimento Dependente 3'],\n",
    "                    value_name='Dt. Nascimento Dependentes').drop('variable',1)\n",
    "\n",
    "# \n",
    "dependents_sheet = (pd.concat([name_dependet,\n",
    "                                cpf_dependent['CPF Dependente'],\n",
    "                                dependenttype_column['Parentesco Dependente'],\n",
    "                                birthdate_dependet['Dt. Nascimento Dependentes'],],axis=1).fillna(''))\n",
    "\n",
    "dependents_sheet.insert(0,'id','')# Criando a coluna nova de ID\n",
    "dependents_sheet.insert(3,'Possui Dependente?','')\n",
    "\n",
    "dependents_sheet['Criado_em'] = today\n",
    "dependents_sheet['Upload_em'] = today\n",
    "\n",
    "# Preenche a nova coluna \"Possui Dependete?\" baseado na coluna com os nomes.\n",
    "# Se tiver um nome nessa coluna então o funcionário tem dependente. \n",
    "for indicator, line in dependents_sheet.iterrows():\n",
    "    if line['Nome Dependentes'] != '':\n",
    "        dependents_sheet.loc[indicator, 'Possui Dependente?'] = True\n",
    "    else:\n",
    "        dependents_sheet.loc[indicator, 'Possui Dependente?'] = False\n",
    "\n",
    "get_uuid4(dependents_sheet,'employee_id','id') # Obtem o ID do CSV\n",
    "\n",
    "\n",
    "# Save the new spreadsheet\n",
    "dependents_sheet.to_csv('output\\\\dependentes.csv',index=False,encoding='utf-8')\n",
    "\n",
    "\n",
    "dependents_sheet.head(5)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Férias\n",
    "\n",
    "Vou trabalhar agora com uma planilha nova, nela contém informações sobre o período de férias de alguns funcionários e os dias gozados. Porém antes de partir para criação do CSV essa planilha vai precisar passar por um tratamento de dados. Ela está cheia de formatações, assim quando o pandas lê a planilha tenta encaixar todas as informações em um padrão. Então o tratamento aqui consiste em remover colunas, linhas e caracteres indesejáveis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Unnamed: 8</th>\n",
       "      <th>Unnamed: 9</th>\n",
       "      <th>Unnamed: 10</th>\n",
       "      <th>Unnamed: 11</th>\n",
       "      <th>Unnamed: 12</th>\n",
       "      <th>Unnamed: 13</th>\n",
       "      <th>Unnamed: 14</th>\n",
       "      <th>Unnamed: 15</th>\n",
       "      <th>Unnamed: 16</th>\n",
       "      <th>Unnamed: 17</th>\n",
       "      <th>Unnamed: 18</th>\n",
       "      <th>Unnamed: 19</th>\n",
       "      <th>Unnamed: 20</th>\n",
       "      <th>Unnamed: 21</th>\n",
       "      <th>Unnamed: 22</th>\n",
       "      <th>Unnamed: 23</th>\n",
       "      <th>Unnamed: 24</th>\n",
       "      <th>Unnamed: 25</th>\n",
       "      <th>Unnamed: 26</th>\n",
       "      <th>Unnamed: 27</th>\n",
       "      <th>Unnamed: 28</th>\n",
       "      <th>Unnamed: 29</th>\n",
       "      <th>Unnamed: 30</th>\n",
       "      <th>Unnamed: 31</th>\n",
       "      <th>Unnamed: 32</th>\n",
       "      <th>Unnamed: 33</th>\n",
       "      <th>Unnamed: 34</th>\n",
       "      <th>Unnamed: 35</th>\n",
       "      <th>Unnamed: 36</th>\n",
       "      <th>Unnamed: 37</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PROGRAMAÇÃO DE FÉRIAS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data</td>\n",
       "      <td>Vencto.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Fer.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Fer.</td>\n",
       "      <td>Início</td>\n",
       "      <td>Fim</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Início</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dias</td>\n",
       "      <td>Dias</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dias</td>\n",
       "      <td>Limite</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dias</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dias</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Código</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Empregado</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>admissão</td>\n",
       "      <td>férias</td>\n",
       "      <td>venc.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pro.</td>\n",
       "      <td>aquisitivo</td>\n",
       "      <td>aquisitivo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>gozo férias</td>\n",
       "      <td>Dias</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Abono</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13º</td>\n",
       "      <td>dir.</td>\n",
       "      <td>goz.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>rest.</td>\n",
       "      <td>p/ gozo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>afast.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>faltas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>74</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Caroline da Mota</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-03-01 00:00:00</td>\n",
       "      <td>2022-02-28 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>10/12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-03-01 00:00:00</td>\n",
       "      <td>2022-02-28 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..../..../......</td>\n",
       "      <td>....</td>\n",
       "      <td>....</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>....</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>2023-02-23 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-03-01 00:00:00</td>\n",
       "      <td>2023-02-28 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..../..../......</td>\n",
       "      <td>....</td>\n",
       "      <td>....</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>....</td>\n",
       "      <td>25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30</td>\n",
       "      <td>2024-01-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Arthur Teixeira</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-03-15 00:00:00</td>\n",
       "      <td>2022-03-14 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>10/12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-03-15 00:00:00</td>\n",
       "      <td>2022-03-14 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..../..../......</td>\n",
       "      <td>....</td>\n",
       "      <td>....</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>....</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>2023-03-10 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-03-15 00:00:00</td>\n",
       "      <td>2023-03-14 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..../..../......</td>\n",
       "      <td>....</td>\n",
       "      <td>....</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>....</td>\n",
       "      <td>25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30</td>\n",
       "      <td>2024-02-14 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Unnamed: 0  Unnamed: 1        Unnamed: 2  Unnamed: 3  Unnamed: 4  \\\n",
       "0        NaN         NaN               NaN         NaN         NaN   \n",
       "1        NaN         NaN               NaN         NaN         NaN   \n",
       "2        NaN         NaN               NaN         NaN         NaN   \n",
       "3        NaN         NaN               NaN         NaN         NaN   \n",
       "4        NaN         NaN               NaN         NaN         NaN   \n",
       "5     Código         NaN        Empregado          NaN         NaN   \n",
       "6         74         NaN  Caroline da Mota         NaN         NaN   \n",
       "7        NaN         NaN               NaN         NaN         NaN   \n",
       "8         80         NaN   Arthur Teixeira         NaN         NaN   \n",
       "9        NaN         NaN               NaN         NaN         NaN   \n",
       "\n",
       "   Unnamed: 5  Unnamed: 6  Unnamed: 7  Unnamed: 8           Unnamed: 9  \\\n",
       "0         NaN         NaN         NaN         NaN                  NaN   \n",
       "1         NaN         NaN         NaN         NaN                  NaN   \n",
       "2         NaN         NaN         NaN         NaN                  NaN   \n",
       "3         NaN         NaN         NaN         NaN                  NaN   \n",
       "4         NaN         NaN         NaN         NaN                 Data   \n",
       "5         NaN         NaN         NaN         NaN             admissão   \n",
       "6         NaN         NaN         NaN         NaN  2021-03-01 00:00:00   \n",
       "7         NaN         NaN         NaN         NaN                  NaN   \n",
       "8         NaN         NaN         NaN         NaN  2021-03-15 00:00:00   \n",
       "9         NaN         NaN         NaN         NaN                  NaN   \n",
       "\n",
       "           Unnamed: 10 Unnamed: 11 Unnamed: 12            Unnamed: 13  \\\n",
       "0                  NaN         NaN         NaN                    NaN   \n",
       "1                  NaN         NaN         NaN                    NaN   \n",
       "2                  NaN         NaN         NaN  PROGRAMAÇÃO DE FÉRIAS   \n",
       "3                  NaN         NaN         NaN                    NaN   \n",
       "4              Vencto.         NaN        Fer.                    NaN   \n",
       "5               férias       venc.         NaN                    NaN   \n",
       "6  2022-02-28 00:00:00         NaN         NaN                      1   \n",
       "7                  NaN         NaN         NaN                    NaN   \n",
       "8  2022-03-14 00:00:00         NaN         NaN                      1   \n",
       "9                  NaN         NaN         NaN                    NaN   \n",
       "\n",
       "  Unnamed: 14 Unnamed: 15          Unnamed: 16          Unnamed: 17  \\\n",
       "0         NaN         NaN                  NaN                  NaN   \n",
       "1         NaN         NaN                  NaN                  NaN   \n",
       "2         NaN         NaN                  NaN                  NaN   \n",
       "3         NaN         NaN                  NaN                  NaN   \n",
       "4         NaN        Fer.               Início                  Fim   \n",
       "5         NaN        pro.           aquisitivo           aquisitivo   \n",
       "6       10/12         NaN  2021-03-01 00:00:00  2022-02-28 00:00:00   \n",
       "7         NaN         NaN  2022-03-01 00:00:00  2023-02-28 00:00:00   \n",
       "8       10/12         NaN  2021-03-15 00:00:00  2022-03-14 00:00:00   \n",
       "9         NaN         NaN  2022-03-15 00:00:00  2023-03-14 00:00:00   \n",
       "\n",
       "   Unnamed: 18  Unnamed: 19  Unnamed: 20  Unnamed: 21       Unnamed: 22  \\\n",
       "0          NaN          NaN          NaN          NaN               NaN   \n",
       "1          NaN          NaN          NaN          NaN               NaN   \n",
       "2          NaN          NaN          NaN          NaN               NaN   \n",
       "3          NaN          NaN          NaN          NaN               NaN   \n",
       "4          NaN          NaN          NaN          NaN            Início   \n",
       "5          NaN          NaN          NaN          NaN       gozo férias   \n",
       "6          NaN          NaN          NaN          NaN  ..../..../......   \n",
       "7          NaN          NaN          NaN          NaN  ..../..../......   \n",
       "8          NaN          NaN          NaN          NaN  ..../..../......   \n",
       "9          NaN          NaN          NaN          NaN  ..../..../......   \n",
       "\n",
       "  Unnamed: 23 Unnamed: 24 Unnamed: 25  Unnamed: 26 Unnamed: 27 Unnamed: 28  \\\n",
       "0         NaN         NaN         NaN          NaN         NaN         NaN   \n",
       "1         NaN         NaN         NaN          NaN         NaN         NaN   \n",
       "2         NaN         NaN         NaN          NaN         NaN         NaN   \n",
       "3         NaN         NaN         NaN          NaN         NaN         NaN   \n",
       "4         NaN         NaN         NaN          NaN         NaN        Dias   \n",
       "5        Dias         NaN       Abono          NaN         13º        dir.   \n",
       "6        ....        ....         NaN          NaN        ....          30   \n",
       "7        ....        ....         NaN          NaN        ....          25   \n",
       "8        ....        ....         NaN          NaN        ....          30   \n",
       "9        ....        ....         NaN          NaN        ....          25   \n",
       "\n",
       "  Unnamed: 29  Unnamed: 30 Unnamed: 31 Unnamed: 32          Unnamed: 33  \\\n",
       "0         NaN          NaN         NaN         NaN                  NaN   \n",
       "1         NaN          NaN         NaN         NaN                  NaN   \n",
       "2         NaN          NaN         NaN         NaN                  NaN   \n",
       "3         NaN          NaN         NaN         NaN                  NaN   \n",
       "4        Dias          NaN         NaN        Dias               Limite   \n",
       "5        goz.          NaN         NaN       rest.              p/ gozo   \n",
       "6         NaN         24.0         NaN           6  2023-02-23 00:00:00   \n",
       "7         NaN          0.0         NaN          30  2024-01-30 00:00:00   \n",
       "8         NaN         15.0         NaN           5  2023-03-10 00:00:00   \n",
       "9         NaN          0.0         NaN          30  2024-02-14 00:00:00   \n",
       "\n",
       "   Unnamed: 34 Unnamed: 35  Unnamed: 36 Unnamed: 37  \n",
       "0          NaN         NaN          NaN         NaN  \n",
       "1          NaN         NaN          NaN         NaN  \n",
       "2          NaN         NaN          NaN         NaN  \n",
       "3          NaN         NaN          NaN         NaN  \n",
       "4          NaN        Dias          NaN        Dias  \n",
       "5          NaN      afast.          NaN      faltas  \n",
       "6          NaN           -          NaN           -  \n",
       "7          NaN           -          NaN           -  \n",
       "8          NaN           -          NaN           -  \n",
       "9          NaN           -          NaN           -  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Vou carregar a planilha e mostrar o resultado quando chamamos com o head\n",
    "df = pd.read_excel('planilhas\\\\historico_ferias.xls')\n",
    "\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>2</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>28</th>\n",
       "      <th>30</th>\n",
       "      <th>32</th>\n",
       "      <th>33</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Caroline da Mota</td>\n",
       "      <td>2021-03-01 00:00:00</td>\n",
       "      <td>2022-02-28 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>10/12</td>\n",
       "      <td>2021-03-01 00:00:00</td>\n",
       "      <td>2022-02-28 00:00:00</td>\n",
       "      <td>30</td>\n",
       "      <td>24.0</td>\n",
       "      <td>6</td>\n",
       "      <td>2023-02-23 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-03-01 00:00:00</td>\n",
       "      <td>2023-02-28 00:00:00</td>\n",
       "      <td>25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30</td>\n",
       "      <td>2024-01-30 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Arthur Teixeira</td>\n",
       "      <td>2021-03-15 00:00:00</td>\n",
       "      <td>2022-03-14 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>10/12</td>\n",
       "      <td>2021-03-15 00:00:00</td>\n",
       "      <td>2022-03-14 00:00:00</td>\n",
       "      <td>30</td>\n",
       "      <td>15.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2023-03-10 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-03-15 00:00:00</td>\n",
       "      <td>2023-03-14 00:00:00</td>\n",
       "      <td>25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30</td>\n",
       "      <td>2024-02-14 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Matheus Melo</td>\n",
       "      <td>2021-03-15 00:00:00</td>\n",
       "      <td>2022-03-14 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>10/12</td>\n",
       "      <td>2021-03-15 00:00:00</td>\n",
       "      <td>2022-03-14 00:00:00</td>\n",
       "      <td>30</td>\n",
       "      <td>22.0</td>\n",
       "      <td>8</td>\n",
       "      <td>2023-03-07 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  2                    9                    10   13     14  \\\n",
       "6   Caroline da Mota  2021-03-01 00:00:00  2022-02-28 00:00:00    1  10/12   \n",
       "7                NaN                  NaN                  NaN  NaN    NaN   \n",
       "8    Arthur Teixeira  2021-03-15 00:00:00  2022-03-14 00:00:00    1  10/12   \n",
       "9                NaN                  NaN                  NaN  NaN    NaN   \n",
       "10      Matheus Melo  2021-03-15 00:00:00  2022-03-14 00:00:00    1  10/12   \n",
       "\n",
       "                     16                   17  28    30  32  \\\n",
       "6   2021-03-01 00:00:00  2022-02-28 00:00:00  30  24.0   6   \n",
       "7   2022-03-01 00:00:00  2023-02-28 00:00:00  25   0.0  30   \n",
       "8   2021-03-15 00:00:00  2022-03-14 00:00:00  30  15.0   5   \n",
       "9   2022-03-15 00:00:00  2023-03-14 00:00:00  25   0.0  30   \n",
       "10  2021-03-15 00:00:00  2022-03-14 00:00:00  30  22.0   8   \n",
       "\n",
       "                     33  \n",
       "6   2023-02-23 00:00:00  \n",
       "7   2024-01-30 00:00:00  \n",
       "8   2023-03-10 00:00:00  \n",
       "9   2024-02-14 00:00:00  \n",
       "10  2023-03-07 00:00:00  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Então para facilitar o tratamento de dados vou converter essa planilha em CSV.\n",
    "df.to_csv('planilhas\\\\ferias_temp.csv',index=False, header=False)\n",
    "\n",
    "ferias = pd.read_csv('planilhas\\\\ferias_temp.csv',header=None)\n",
    "\n",
    "# Usando o drop para deletar as linhas e colunas desnecessárias.\n",
    "ferias.drop([0,1,2,3,4,5], inplace=True)\n",
    "ferias.drop(ferias.tail(3).index,inplace=True) # Combinando o drop com o tail eu consigo sempre excluir as ultimas linhas, não importa o conteúdo dessas\n",
    "ferias.drop([0,1,3,4,5,6,7,8,11,\n",
    "                    12,15,18,19,20,22,23,\n",
    "                    21,24,25,26,31,36,34,\n",
    "                    27,29,35,37],axis=1, inplace=True)\n",
    "\n",
    "ferias.head(5)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Agora já temos um resultado mais amigável e limpo, próximo passo é renomear as colunas. Vou usar **pd.rename**. Cada coluna recebeu como cabeçalho sua posição no index, vou alterar esses números por nomes, que fazem mais sentido. Também vou passar o parâmetro **\"inplace=True\"** (que por padrão é False). O inplace faz com que as alterações realizadas no dataframe sejam salvas, ou seja minha variável \"ferias\" vai exibir o DF com as alterações feitas e não mais o DF com os números no lugar do cabeçalho. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Nome Funcionário</th>\n",
       "      <th>Data Admissão</th>\n",
       "      <th>Vencimento Férias</th>\n",
       "      <th>Fer. Venc.</th>\n",
       "      <th>Fer. Pro.</th>\n",
       "      <th>Início aquisitivo</th>\n",
       "      <th>Fim aquisitivo</th>\n",
       "      <th>Dias dir.</th>\n",
       "      <th>Dias gozados</th>\n",
       "      <th>Dias restantes</th>\n",
       "      <th>Limite p/ gozo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Caroline da Mota</td>\n",
       "      <td>2021-03-01 00:00:00</td>\n",
       "      <td>2022-02-28 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>10/12</td>\n",
       "      <td>2021-03-01 00:00:00</td>\n",
       "      <td>2022-02-28 00:00:00</td>\n",
       "      <td>30</td>\n",
       "      <td>24.0</td>\n",
       "      <td>6</td>\n",
       "      <td>2023-02-23 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-03-01 00:00:00</td>\n",
       "      <td>2023-02-28 00:00:00</td>\n",
       "      <td>25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30</td>\n",
       "      <td>2024-01-30 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Arthur Teixeira</td>\n",
       "      <td>2021-03-15 00:00:00</td>\n",
       "      <td>2022-03-14 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>10/12</td>\n",
       "      <td>2021-03-15 00:00:00</td>\n",
       "      <td>2022-03-14 00:00:00</td>\n",
       "      <td>30</td>\n",
       "      <td>15.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2023-03-10 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-03-15 00:00:00</td>\n",
       "      <td>2023-03-14 00:00:00</td>\n",
       "      <td>25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30</td>\n",
       "      <td>2024-02-14 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Matheus Melo</td>\n",
       "      <td>2021-03-15 00:00:00</td>\n",
       "      <td>2022-03-14 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>10/12</td>\n",
       "      <td>2021-03-15 00:00:00</td>\n",
       "      <td>2022-03-14 00:00:00</td>\n",
       "      <td>30</td>\n",
       "      <td>22.0</td>\n",
       "      <td>8</td>\n",
       "      <td>2023-03-07 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Nome Funcionário        Data Admissão    Vencimento Férias Fer. Venc.  \\\n",
       "6   Caroline da Mota  2021-03-01 00:00:00  2022-02-28 00:00:00          1   \n",
       "7                NaN                  NaN                  NaN        NaN   \n",
       "8    Arthur Teixeira  2021-03-15 00:00:00  2022-03-14 00:00:00          1   \n",
       "9                NaN                  NaN                  NaN        NaN   \n",
       "10      Matheus Melo  2021-03-15 00:00:00  2022-03-14 00:00:00          1   \n",
       "\n",
       "   Fer. Pro.    Início aquisitivo       Fim aquisitivo Dias dir.  \\\n",
       "6      10/12  2021-03-01 00:00:00  2022-02-28 00:00:00        30   \n",
       "7        NaN  2022-03-01 00:00:00  2023-02-28 00:00:00        25   \n",
       "8      10/12  2021-03-15 00:00:00  2022-03-14 00:00:00        30   \n",
       "9        NaN  2022-03-15 00:00:00  2023-03-14 00:00:00        25   \n",
       "10     10/12  2021-03-15 00:00:00  2022-03-14 00:00:00        30   \n",
       "\n",
       "    Dias gozados Dias restantes       Limite p/ gozo  \n",
       "6           24.0              6  2023-02-23 00:00:00  \n",
       "7            0.0             30  2024-01-30 00:00:00  \n",
       "8           15.0              5  2023-03-10 00:00:00  \n",
       "9            0.0             30  2024-02-14 00:00:00  \n",
       "10          22.0              8  2023-03-07 00:00:00  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Usando o pd.rename e passando o inplace para salvar\n",
    "\n",
    "ferias.rename(columns={2:'Nome Funcionário',\n",
    "                    9: 'Data Admissão',\n",
    "                    10: 'Vencimento Férias',\n",
    "                    13: 'Fer. Venc.',\n",
    "                    14: 'Fer. Pro.',\n",
    "                    16: 'Início aquisitivo',\n",
    "                    17: 'Fim aquisitivo',\n",
    "                    33: 'Limite p/ gozo',\n",
    "                    30: 'Dias gozados',\n",
    "                    28: 'Dias dir.',\n",
    "                    32: 'Dias restantes'\n",
    "                    },inplace=True)\n",
    "\n",
    "ferias.head(5)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alguns funcionário possuem mais de um perído de férias. Quando isso ocorre na planilha original, gera uma linha em branco. \n",
    "\n",
    "![ferias](imagens/ferias.JPG)\n",
    "\n",
    "A funcionária fictícia Caroline da Mota tem direito a 30 dias de férias, mas decidiu não usar todos de uma vez. Os dias faltantes então geraram uma outra linha em branco (em azul). Isso faz com que o dataframe aparece com valores NaN (missing). Não tem como ter certeza qual o vencimento das férias seguintes, portanto a coluna \"Vencimento de Férias\" deve continuar com valor NaN. Mas as outras duas colunas, são valores que não se alteram ao longo do tempo, então posso dizer que o valor NaN é exatamente igual ao valor acima dele. Para contornar esse problema existe um método do pandas que faz exatamente o que eu preciso, chama **fillna**. Que quer dizer preencha o valor NaN."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Nome Funcionário</th>\n",
       "      <th>Data Admissão</th>\n",
       "      <th>Vencimento Férias</th>\n",
       "      <th>Fer. Venc.</th>\n",
       "      <th>Fer. Pro.</th>\n",
       "      <th>Início aquisitivo</th>\n",
       "      <th>Fim aquisitivo</th>\n",
       "      <th>Dias dir.</th>\n",
       "      <th>Dias gozados</th>\n",
       "      <th>Dias restantes</th>\n",
       "      <th>Limite p/ gozo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Caroline da Mota</td>\n",
       "      <td>2021-03-01 00:00:00</td>\n",
       "      <td>2022-02-28 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>10/12</td>\n",
       "      <td>2021-03-01 00:00:00</td>\n",
       "      <td>2022-02-28 00:00:00</td>\n",
       "      <td>30</td>\n",
       "      <td>24.0</td>\n",
       "      <td>6</td>\n",
       "      <td>2023-02-23 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Caroline da Mota</td>\n",
       "      <td>2021-03-01 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-03-01 00:00:00</td>\n",
       "      <td>2023-02-28 00:00:00</td>\n",
       "      <td>25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30</td>\n",
       "      <td>2024-01-30 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Arthur Teixeira</td>\n",
       "      <td>2021-03-15 00:00:00</td>\n",
       "      <td>2022-03-14 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>10/12</td>\n",
       "      <td>2021-03-15 00:00:00</td>\n",
       "      <td>2022-03-14 00:00:00</td>\n",
       "      <td>30</td>\n",
       "      <td>15.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2023-03-10 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Arthur Teixeira</td>\n",
       "      <td>2021-03-15 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-03-15 00:00:00</td>\n",
       "      <td>2023-03-14 00:00:00</td>\n",
       "      <td>25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30</td>\n",
       "      <td>2024-02-14 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Matheus Melo</td>\n",
       "      <td>2021-03-15 00:00:00</td>\n",
       "      <td>2022-03-14 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>10/12</td>\n",
       "      <td>2021-03-15 00:00:00</td>\n",
       "      <td>2022-03-14 00:00:00</td>\n",
       "      <td>30</td>\n",
       "      <td>22.0</td>\n",
       "      <td>8</td>\n",
       "      <td>2023-03-07 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Nome Funcionário        Data Admissão    Vencimento Férias Fer. Venc.  \\\n",
       "6   Caroline da Mota  2021-03-01 00:00:00  2022-02-28 00:00:00          1   \n",
       "7   Caroline da Mota  2021-03-01 00:00:00                  NaN        NaN   \n",
       "8    Arthur Teixeira  2021-03-15 00:00:00  2022-03-14 00:00:00          1   \n",
       "9    Arthur Teixeira  2021-03-15 00:00:00                  NaN        NaN   \n",
       "10      Matheus Melo  2021-03-15 00:00:00  2022-03-14 00:00:00          1   \n",
       "\n",
       "   Fer. Pro.    Início aquisitivo       Fim aquisitivo Dias dir.  \\\n",
       "6      10/12  2021-03-01 00:00:00  2022-02-28 00:00:00        30   \n",
       "7        NaN  2022-03-01 00:00:00  2023-02-28 00:00:00        25   \n",
       "8      10/12  2021-03-15 00:00:00  2022-03-14 00:00:00        30   \n",
       "9        NaN  2022-03-15 00:00:00  2023-03-14 00:00:00        25   \n",
       "10     10/12  2021-03-15 00:00:00  2022-03-14 00:00:00        30   \n",
       "\n",
       "    Dias gozados Dias restantes       Limite p/ gozo  \n",
       "6           24.0              6  2023-02-23 00:00:00  \n",
       "7            0.0             30  2024-01-30 00:00:00  \n",
       "8           15.0              5  2023-03-10 00:00:00  \n",
       "9            0.0             30  2024-02-14 00:00:00  \n",
       "10          22.0              8  2023-03-07 00:00:00  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Para que o fillna funcione corretamente, eu preciso dizer ao pandas com o que ele deve preencher os valores missing.\n",
    "No meu exmeplo eu preciso que ele preencha com o valor da célula acima e é exatamente isso que o parâmetro \"method='ffill'\" faz.\n",
    "Também vou passar o parâmetro axis=0 para fazer o processo por linhas e inplace para salvar na mesma variável o resultado.\n",
    "'''\n",
    "ferias['Nome Funcionário'].fillna(axis=0,method='ffill',inplace=True)\n",
    "ferias['Data Admissão'].fillna(axis=0,method='ffill',inplace=True)\n",
    "\n",
    "ferias.head(5) # Chamando o novo dataframe"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "O dataframe já está quase pronto. Agora olhando para as colunas que contém data, o pandas está identificando como do tipo objeto. \n",
    "\n",
    "![data](imagens/dtype_data.JPG)\n",
    "\n",
    "Não quero que fique dessa forma, pois caso haja necessidade de trabalhar com data é ideal que estaja no formato correto que é datetime64"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  Para corrigir o formato da data posso passar o método 'astype' \n",
    "ferias['Data Admissão'] = ferias['Início aquisitivo'].astype('datetime64')\n",
    "ferias['Início aquisitivo'] = ferias['Início aquisitivo'].astype('datetime64')\n",
    "ferias['Fim aquisitivo'] = ferias['Fim aquisitivo'].astype('datetime64')\n",
    "ferias['Limite p/ gozo'] = ferias['Limite p/ gozo'].astype('datetime64')\n",
    "\n",
    "# print(ferias['Data Admissão'].dtype)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>employee_id</th>\n",
       "      <th>Name</th>\n",
       "      <th>Início do período</th>\n",
       "      <th>Fim do período</th>\n",
       "      <th>Limite p/ gozo</th>\n",
       "      <th>Status</th>\n",
       "      <th>Criado_em</th>\n",
       "      <th>Upload_em</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7c6d232e-9654-4707-b9a4-437a71f129d3</td>\n",
       "      <td>bc044df8-4bac-45e4-a0c1-efe22bc6470c</td>\n",
       "      <td>Caroline da Mota</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2022-02-28</td>\n",
       "      <td>2023-02-23</td>\n",
       "      <td>True</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>429abf2d-36a4-480a-9c7e-bdca17f8a150</td>\n",
       "      <td>bc044df8-4bac-45e4-a0c1-efe22bc6470c</td>\n",
       "      <td>Caroline da Mota</td>\n",
       "      <td>2022-03-01</td>\n",
       "      <td>2023-02-28</td>\n",
       "      <td>2024-01-30</td>\n",
       "      <td>True</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>95648dc5-1d3e-4bad-bf98-531aaffdbf65</td>\n",
       "      <td>c723f79d-dcc1-4c1c-9570-5991a862ec79</td>\n",
       "      <td>Arthur Teixeira</td>\n",
       "      <td>2021-03-15</td>\n",
       "      <td>2022-03-14</td>\n",
       "      <td>2023-03-10</td>\n",
       "      <td>True</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>51d92796-79cd-4186-9e34-331f5f81bd50</td>\n",
       "      <td>c723f79d-dcc1-4c1c-9570-5991a862ec79</td>\n",
       "      <td>Arthur Teixeira</td>\n",
       "      <td>2022-03-15</td>\n",
       "      <td>2023-03-14</td>\n",
       "      <td>2024-02-14</td>\n",
       "      <td>True</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>853d4b0f-09b3-4590-8f79-6b9fccf31780</td>\n",
       "      <td>5fbe8ff8-76fc-40b1-bd2d-db2d0733f591</td>\n",
       "      <td>Matheus Melo</td>\n",
       "      <td>2021-03-15</td>\n",
       "      <td>2022-03-14</td>\n",
       "      <td>2023-03-07</td>\n",
       "      <td>True</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id                           employee_id  \\\n",
       "0  7c6d232e-9654-4707-b9a4-437a71f129d3  bc044df8-4bac-45e4-a0c1-efe22bc6470c   \n",
       "1  429abf2d-36a4-480a-9c7e-bdca17f8a150  bc044df8-4bac-45e4-a0c1-efe22bc6470c   \n",
       "2  95648dc5-1d3e-4bad-bf98-531aaffdbf65  c723f79d-dcc1-4c1c-9570-5991a862ec79   \n",
       "3  51d92796-79cd-4186-9e34-331f5f81bd50  c723f79d-dcc1-4c1c-9570-5991a862ec79   \n",
       "4  853d4b0f-09b3-4590-8f79-6b9fccf31780  5fbe8ff8-76fc-40b1-bd2d-db2d0733f591   \n",
       "\n",
       "               Name Início do período Fim do período Limite p/ gozo  Status  \\\n",
       "0  Caroline da Mota        2021-03-01     2022-02-28     2023-02-23    True   \n",
       "1  Caroline da Mota        2022-03-01     2023-02-28     2024-01-30    True   \n",
       "2   Arthur Teixeira        2021-03-15     2022-03-14     2023-03-10    True   \n",
       "3   Arthur Teixeira        2022-03-15     2023-03-14     2024-02-14    True   \n",
       "4      Matheus Melo        2021-03-15     2022-03-14     2023-03-07    True   \n",
       "\n",
       "    Criado_em   Upload_em  \n",
       "0  01-30-2023  01-30-2023  \n",
       "1  01-30-2023  01-30-2023  \n",
       "2  01-30-2023  01-30-2023  \n",
       "3  01-30-2023  01-30-2023  \n",
       "4  01-30-2023  01-30-2023  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Agora com o dataframe organizado e limpo é possível criar o CSV\n",
    "# Criar o CSV de ferias usando a mesma estrutura lógicas das demais planilhas\n",
    "\n",
    "'''\n",
    "Note que para esse pd.merge eu preciso alterar o parâmetro 'how'.\n",
    "Agora eu não quero manter a planilha principal, quero manter a nova planilha.\n",
    "Isso porque nem todos os funcionários são CLT e possuem direito a férias. Ou ainda não completaram um ano.\n",
    "Poucos funcionários possuem férias, e só quero que esses apareçam no CSV. \n",
    "'''\n",
    "data_ferias = data.merge(ferias,\n",
    "                        on='Nome Funcionário',\n",
    "                        how='right')\n",
    "\n",
    "#--------------------Gerando o CSV de férias--------------------#\n",
    "acquisition_sheet = pd.DataFrame({'employee_id': data_ferias['employee_id'],\n",
    "                                'Name': data_ferias['Nome Funcionário'],\n",
    "                                'Início do período': data_ferias['Início aquisitivo'],\n",
    "                                'Fim do período': data_ferias['Fim aquisitivo'],\n",
    "                                'Limite p/ gozo': data_ferias['Limite p/ gozo'],\n",
    "                                'Status': True,\n",
    "                                'Criado_em': today,\n",
    "                                'Upload_em': today})\n",
    "\n",
    "\n",
    "acquisition_sheet.insert(0,'id','') # Criando nova coluna para receber o ID\n",
    "\n",
    "get_uuid4(acquisition_sheet,'employee_id','id') # Gerando o ID\n",
    "\n",
    "# Salvando o nome CSV\n",
    "acquisition_sheet.to_csv('output\\\\ferias.csv', index=False)\n",
    "\n",
    "# Apagando o arquivo temporário criado \n",
    "if os.path.exists('planilhas\\\\ferias_temp.csv'):\n",
    "    os.remove('planilhas\\\\ferias_temp.csv')\n",
    "\n",
    "acquisition_sheet.head(5)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Histórico de Salário\n",
    "\n",
    "Depois de ter feito o tratamento de dados, que você pode conferir abrindo o arquivo [tratamendo_dados_salario_cargo](tratamento_dados_salario_cargo.ipynb) é possível preparar o CSV que vai ter as informações sobre as alterações de salário. Porém ainda tem mais um pequeno desafio no caminho. \n",
    "\n",
    "Não é um problema não ter uma data final para um salário nem para a troca de cargo, isso porque só vai existir uma data final caso tenha sido desligado da empresa ou tenha mudado de salário novamente. Nesse caso a coluna \"Final\" que contem as datas é normal e esperado que tenha alguns valores missing. Mas isso não deve acontecer para as datas Iniciais. Na planilha anterior que foi feita o tratamento não consta essa informação. Sabemos que toda data inicial tanto para primeiro salário quanto para o primeiro cargo é a data de admissão. Essa informação tem na planilha principal do RH e vou usar merge, para completar a planilha agora, antes de criar o CSV. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>employee_id</th>\n",
       "      <th>Salários</th>\n",
       "      <th>Data Inicial</th>\n",
       "      <th>Data FInal</th>\n",
       "      <th>Bonus</th>\n",
       "      <th>Criado_em</th>\n",
       "      <th>Upload_em</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0ef746d9-7533-4091-819c-8af30c02c151</td>\n",
       "      <td>a16a156e-aa4a-40c5-94a4-4bea0f443f39</td>\n",
       "      <td>2100,00</td>\n",
       "      <td>2014-05-23</td>\n",
       "      <td>2022-04-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>e26301c2-e9f9-4479-b04a-fd19de94eeda</td>\n",
       "      <td>a16a156e-aa4a-40c5-94a4-4bea0f443f39</td>\n",
       "      <td>2186,31</td>\n",
       "      <td>2022-04-01</td>\n",
       "      <td>2022-05-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>afea7dc9-a7d8-4301-8b5d-6a94b4bfb0a1</td>\n",
       "      <td>a16a156e-aa4a-40c5-94a4-4bea0f443f39</td>\n",
       "      <td>2276,40</td>\n",
       "      <td>2022-05-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23b10056-5d58-4373-bb2a-c9202de33c49</td>\n",
       "      <td>727208ef-44e7-4c85-8c25-199f7011e9f5</td>\n",
       "      <td>2740,00</td>\n",
       "      <td>1981-10-27</td>\n",
       "      <td>2022-04-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2ca0661e-434f-49e9-a084-a89c4ae1f671</td>\n",
       "      <td>727208ef-44e7-4c85-8c25-199f7011e9f5</td>\n",
       "      <td>2922,48</td>\n",
       "      <td>2022-04-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7a9d9d0b-8ec4-4d6a-a168-4a9029fc854e</td>\n",
       "      <td>ad64f750-8850-444b-9bb1-536951a1819d</td>\n",
       "      <td>2100,00</td>\n",
       "      <td>1993-06-25</td>\n",
       "      <td>2022-05-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8c26857e-5447-4af5-b767-a4aedc66a340</td>\n",
       "      <td>ad64f750-8850-444b-9bb1-536951a1819d</td>\n",
       "      <td>2276,40</td>\n",
       "      <td>2022-05-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>f9259090-d13e-407d-ac75-6b0ab8d6f3c8</td>\n",
       "      <td>945e364e-0bc3-4ec7-a693-32fc0f8db8a1</td>\n",
       "      <td>2100,00</td>\n",
       "      <td>2006-02-02</td>\n",
       "      <td>2022-04-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3fe8e8fe-b7e1-4939-8ba3-71214de0805a</td>\n",
       "      <td>945e364e-0bc3-4ec7-a693-32fc0f8db8a1</td>\n",
       "      <td>2186,31</td>\n",
       "      <td>2022-04-01</td>\n",
       "      <td>2022-04-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>f235cda6-ea07-4da0-889f-5cdfdcd45de9</td>\n",
       "      <td>945e364e-0bc3-4ec7-a693-32fc0f8db8a1</td>\n",
       "      <td>3300,00</td>\n",
       "      <td>2022-04-02</td>\n",
       "      <td>2022-05-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id                           employee_id  \\\n",
       "0  0ef746d9-7533-4091-819c-8af30c02c151  a16a156e-aa4a-40c5-94a4-4bea0f443f39   \n",
       "1  e26301c2-e9f9-4479-b04a-fd19de94eeda  a16a156e-aa4a-40c5-94a4-4bea0f443f39   \n",
       "2  afea7dc9-a7d8-4301-8b5d-6a94b4bfb0a1  a16a156e-aa4a-40c5-94a4-4bea0f443f39   \n",
       "3  23b10056-5d58-4373-bb2a-c9202de33c49  727208ef-44e7-4c85-8c25-199f7011e9f5   \n",
       "4  2ca0661e-434f-49e9-a084-a89c4ae1f671  727208ef-44e7-4c85-8c25-199f7011e9f5   \n",
       "5  7a9d9d0b-8ec4-4d6a-a168-4a9029fc854e  ad64f750-8850-444b-9bb1-536951a1819d   \n",
       "6  8c26857e-5447-4af5-b767-a4aedc66a340  ad64f750-8850-444b-9bb1-536951a1819d   \n",
       "7  f9259090-d13e-407d-ac75-6b0ab8d6f3c8  945e364e-0bc3-4ec7-a693-32fc0f8db8a1   \n",
       "8  3fe8e8fe-b7e1-4939-8ba3-71214de0805a  945e364e-0bc3-4ec7-a693-32fc0f8db8a1   \n",
       "9  f235cda6-ea07-4da0-889f-5cdfdcd45de9  945e364e-0bc3-4ec7-a693-32fc0f8db8a1   \n",
       "\n",
       "  Salários Data Inicial  Data FInal  Bonus   Criado_em   Upload_em  \n",
       "0  2100,00   2014-05-23  2022-04-01    NaN  01-30-2023  01-30-2023  \n",
       "1  2186,31   2022-04-01  2022-05-01    NaN  01-30-2023  01-30-2023  \n",
       "2  2276,40   2022-05-01         NaN    NaN  01-30-2023  01-30-2023  \n",
       "3  2740,00   1981-10-27  2022-04-01    NaN  01-30-2023  01-30-2023  \n",
       "4  2922,48   2022-04-01         NaN    NaN  01-30-2023  01-30-2023  \n",
       "5  2100,00   1993-06-25  2022-05-01    NaN  01-30-2023  01-30-2023  \n",
       "6  2276,40   2022-05-01         NaN    NaN  01-30-2023  01-30-2023  \n",
       "7  2100,00   2006-02-02  2022-04-01    NaN  01-30-2023  01-30-2023  \n",
       "8  2186,31   2022-04-01  2022-04-02    NaN  01-30-2023  01-30-2023  \n",
       "9  3300,00   2022-04-02  2022-05-01    NaN  01-30-2023  01-30-2023  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Carregando o csv temporário criado no tratamento de dados\n",
    "salario = pd.read_csv('planilhas\\\\salario_temp_historico.csv',sep = ',')\n",
    "\n",
    "data_salario = data.merge(salario,\n",
    "                        on='Nome Funcionário',\n",
    "                        how='left')\n",
    "\n",
    "#--------------------Gerando o CSV de Salário--------------------#\n",
    "# Aqui está a correção do nosso problema mencionado acima. \n",
    "# Se a data inicial for fazia eu completo pela data de admissão. O fillna do pandas é ideial!\n",
    "data_salario['Inicial'].fillna(data_salario['Dt. Admissão'],inplace=True)\n",
    "\n",
    "\n",
    "salary_sheet = pd.DataFrame({'employee_id': data_salario['employee_id'],\n",
    "                            'Salários': data_salario['Salário_y'],\n",
    "                            'Data Inicial': data_salario['Inicial'],\n",
    "                            'Data FInal': data_salario['Final'],\n",
    "                            'Bonus': data_salario['Adcional'],\n",
    "                            'Criado_em': today,\n",
    "                            'Upload_em': today})\n",
    "\n",
    "salary_sheet.insert(0,'id','') # Cria nova coluna no dataframe\n",
    "\n",
    "get_uuid4(salary_sheet,'employee_id','id')\n",
    "\n",
    "# Salva nova planilha\n",
    "salary_sheet.to_csv('output\\\\salario.csv', index=False)\n",
    "\n",
    "salary_sheet.head(10)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Histórico de Cargo\n",
    "\n",
    "Para gerar a planilha com os cargos, é interessante tratar cada um deles como único, assim como acontece com cada fucionário, e gerar um ID que vai ser utilizado sempre que alguém novo entrar ou caso receba uma promoção. Por essa razão para os cargos serão geradas duas planilhas, uma contendo o ID e outra com o histórico de cada funcionário. Vale lembrar que o mesmo processo de tratamento ocorreu aqui, já que a planilha é a mesma para o histórico de salário e o trabalho que foi feito pode ser conferido no outro [jupyter notebook](tratamento_dados_salario_cargo.ipynb)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>Nome Cargo</th>\n",
       "      <th>Senioridade</th>\n",
       "      <th>Setor</th>\n",
       "      <th>Criado_em</th>\n",
       "      <th>Upload_em</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5a1982d9-107e-411a-88b0-991113a95b8f</td>\n",
       "      <td>Auxiliar de serviços gerais</td>\n",
       "      <td>PLENO</td>\n",
       "      <td>Marketing</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>30926fe1-2fea-455e-b850-1051dad7016d</td>\n",
       "      <td>Montador de negativos</td>\n",
       "      <td>PLENO</td>\n",
       "      <td>TI</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>e605ce44-652d-439e-8389-e6accb925f2e</td>\n",
       "      <td>Agente de defesa sanitária</td>\n",
       "      <td>PLENO</td>\n",
       "      <td>RH</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3af79a73-2323-428d-9b19-7bed16a2f84e</td>\n",
       "      <td>Mestre cervejeiro</td>\n",
       "      <td>PLENO</td>\n",
       "      <td>Administrativo</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22c00a7b-a0d6-4907-bf80-14a1a2728210</td>\n",
       "      <td>Designer gráfico</td>\n",
       "      <td>PLENO</td>\n",
       "      <td>RH</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>9ae357f6-e1a0-4268-865b-88487728ec59</td>\n",
       "      <td>Marceneiro</td>\n",
       "      <td>PLENO</td>\n",
       "      <td>Vendas</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2f3a9353-17a6-4722-a64c-d4e5f5539817</td>\n",
       "      <td>Jogador de vôlei</td>\n",
       "      <td>PLENO</td>\n",
       "      <td>Administrativo</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>52d2afff-fa2f-44bc-a6b5-fc8566f4c373</td>\n",
       "      <td>Cortador de cana-de-açucar</td>\n",
       "      <td>PLENO</td>\n",
       "      <td>Vendas</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>36e55d80-cc9b-407a-9f33-bb315c2a40de</td>\n",
       "      <td>Tecnólogo em negócios imobiliários</td>\n",
       "      <td>PLENO</td>\n",
       "      <td>Marketing</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>a3bd6354-7e02-4f97-abb4-844c9f9d1446</td>\n",
       "      <td>Ombudsman</td>\n",
       "      <td>PLENO</td>\n",
       "      <td>TI</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id                          Nome Cargo  \\\n",
       "0  5a1982d9-107e-411a-88b0-991113a95b8f         Auxiliar de serviços gerais   \n",
       "1  30926fe1-2fea-455e-b850-1051dad7016d               Montador de negativos   \n",
       "2  e605ce44-652d-439e-8389-e6accb925f2e          Agente de defesa sanitária   \n",
       "3  3af79a73-2323-428d-9b19-7bed16a2f84e                   Mestre cervejeiro   \n",
       "4  22c00a7b-a0d6-4907-bf80-14a1a2728210                    Designer gráfico   \n",
       "5  9ae357f6-e1a0-4268-865b-88487728ec59                          Marceneiro   \n",
       "6  2f3a9353-17a6-4722-a64c-d4e5f5539817                    Jogador de vôlei   \n",
       "7  52d2afff-fa2f-44bc-a6b5-fc8566f4c373          Cortador de cana-de-açucar   \n",
       "8  36e55d80-cc9b-407a-9f33-bb315c2a40de  Tecnólogo em negócios imobiliários   \n",
       "9  a3bd6354-7e02-4f97-abb4-844c9f9d1446                           Ombudsman   \n",
       "\n",
       "  Senioridade           Setor   Criado_em   Upload_em  \n",
       "0       PLENO       Marketing  01-30-2023  01-30-2023  \n",
       "1       PLENO              TI  01-30-2023  01-30-2023  \n",
       "2       PLENO              RH  01-30-2023  01-30-2023  \n",
       "3       PLENO  Administrativo  01-30-2023  01-30-2023  \n",
       "4       PLENO              RH  01-30-2023  01-30-2023  \n",
       "5       PLENO          Vendas  01-30-2023  01-30-2023  \n",
       "6       PLENO  Administrativo  01-30-2023  01-30-2023  \n",
       "7       PLENO          Vendas  01-30-2023  01-30-2023  \n",
       "8       PLENO       Marketing  01-30-2023  01-30-2023  \n",
       "9       PLENO              TI  01-30-2023  01-30-2023  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Carregando a planilha que contém todos os cargos\n",
    "cargos = pd.read_excel('planilhas\\\\cargos.xlsx')\n",
    "\n",
    "# Criando uma coluna de senioridade, para que se caso o cargo possuir  ser preenchido nessa coluna\n",
    "cargos['Senioridade'] = np.nan\n",
    "\n",
    "# Se caso não encontrar uma senioridade, por padrão todas vão ser Pleno. \n",
    "# Criando as variáveis\n",
    "pleno = 'PLENO'\n",
    "pleno_sig = 'PL'\n",
    "junior = 'JUNIOR'\n",
    "junior_sig = 'JR'\n",
    "senior = 'SENIOR'\n",
    "\n",
    "# A lógica é, se tiver uma posição no nome do cargo, então escreva na nova coluna\n",
    "for index, row in enumerate(cargos['Nome Cargo']):\n",
    "    if pleno in row:\n",
    "        copy_pleno = pleno\n",
    "        cargos.loc[index, 'Senioridade'] = copy_pleno\n",
    "\n",
    "    if pleno_sig in row:\n",
    "        copy_pleno_sig = pleno\n",
    "        cargos.loc[index, 'Senioridade'] = copy_pleno_sig\n",
    "\n",
    "    if junior in row:\n",
    "        copy_junior = junior\n",
    "        cargos.loc[index, 'Senioridade'] = copy_junior\n",
    "\n",
    "    if junior_sig in row:\n",
    "        copy_junior_sig = junior\n",
    "        cargos.loc[index, 'Senioridade'] = copy_junior_sig\n",
    "\n",
    "    if senior in row:\n",
    "        copy_senior = senior\n",
    "        cargos.loc[index, 'Senioridade'] = copy_senior\n",
    "\n",
    "# Aqui preenche os valores em branco com Pleno.\n",
    "cargos['Senioridade'].fillna(pleno,inplace=True)\n",
    "\n",
    "# Criando a nova planilha com o ID para cada cargo\n",
    "position_sheet = pd.DataFrame({'Nome Cargo':cargos['Nome Cargo'],\n",
    "                                'Senioridade': cargos['Senioridade'],\n",
    "                                'Setor': cargos['Setor'],\n",
    "                                'Criado_em': today,\n",
    "                                'Upload_em': today})\n",
    "\n",
    "position_sheet.insert(0,'id','')\n",
    "\n",
    "get_uuid4(position_sheet,'Nome Cargo','id')\n",
    "\n",
    "position_sheet.to_csv('output\\\\todos_cargos.csv', index=False)\n",
    "\n",
    "position_sheet.head(10)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Agora com a planilha com os ID's dos cargos é possível criar a planilha final. Nela vai contar a última informação para ser importada. A atribuição de cada funcionário a um ID referente ao cargo. Também todo empregado possui um gestor, o ID desse gestor deve contar em uma coluna específica, para que seja fácil do RH identificar essa imformação. Vou criar uma função exatamente igual ao ProcV do Excel, que vai procurar um valor idêntico em uma planilha e colar as demais informações em outra. Essa função e útil em outras situações, também para poder explorar mais de uma solução, além do merge do pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def xlookup(lookup_value,\n",
    "            lookup_array,\n",
    "            return_array):\n",
    "    '''\n",
    "    Na primeira linha, estamos definindo uma função chamada xlookup com alguns argumentos.\n",
    "\n",
    "    lookup_value: o valor que nos interessa, este será um valor de string\n",
    "    lookup_array: esta é uma coluna dentro da fonte pandas dataframe,\n",
    "    estamos procurando o \"lookup_value\" dentro desta matriz/coluna\n",
    "    return_array: esta é uma coluna dentro da fonte pandas dataframe,\n",
    "    queremos retornar valores desta coluna\n",
    "    '''\n",
    "    match_value = return_array.loc[lookup_array == lookup_value]\n",
    "    if match_value.empty:\n",
    "        return np.nan\n",
    "    else:\n",
    "        return match_value.tolist()[0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>Cargo_id</th>\n",
       "      <th>employee_id</th>\n",
       "      <th>Inicial</th>\n",
       "      <th>Final</th>\n",
       "      <th>Vinc.Empreg.</th>\n",
       "      <th>ID_Lider</th>\n",
       "      <th>Criado_em</th>\n",
       "      <th>Upload_em</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>440af3f1-c1a8-43ad-a8d8-b8363119daa1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a16a156e-aa4a-40c5-94a4-4bea0f443f39</td>\n",
       "      <td>2014-05-23</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLT</td>\n",
       "      <td>4ac2c964-9996-445a-be5c-f9947fda5525</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>400282d4-894c-4d13-99f4-a4976b757ea5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>727208ef-44e7-4c85-8c25-199f7011e9f5</td>\n",
       "      <td>1981-10-27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PJ</td>\n",
       "      <td>cdb9998c-2334-4826-a439-c6dd85808f7a</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1b8f7e93-2c9a-4e88-9592-ad98e8185822</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ad64f750-8850-444b-9bb1-536951a1819d</td>\n",
       "      <td>1993-06-25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLT</td>\n",
       "      <td>3c35393c-9a82-4dee-a129-c0bc95050517</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>51fb966b-c383-4e9a-9882-23748f4d4bdd</td>\n",
       "      <td>NaN</td>\n",
       "      <td>945e364e-0bc3-4ec7-a693-32fc0f8db8a1</td>\n",
       "      <td>2006-02-02</td>\n",
       "      <td>2022-04-02</td>\n",
       "      <td>PJ</td>\n",
       "      <td>6e1ead10-be57-43b2-a484-32f5fa09f92a</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>75665c2a-a81b-4c1a-bebd-a91009e38c11</td>\n",
       "      <td>5733dca2-b17a-4386-b98d-0364a67fe49d</td>\n",
       "      <td>945e364e-0bc3-4ec7-a693-32fc0f8db8a1</td>\n",
       "      <td>2022-04-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PJ</td>\n",
       "      <td>6e1ead10-be57-43b2-a484-32f5fa09f92a</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0a835930-b4d0-43f9-bd18-dbc6da19e81e</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f5fcda1f-5ef0-4e87-9768-70e048f232ae</td>\n",
       "      <td>2018-09-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLT</td>\n",
       "      <td>ad32fa62-15d9-4fe0-a8cf-64b047e54268</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4af463ee-5178-41d0-8da2-ac3ce495adbe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9ecf4b0d-f2c6-43a6-bc78-9aec6feea1ae</td>\n",
       "      <td>1989-09-25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLT</td>\n",
       "      <td>234f2e33-d2ed-4a13-8e03-4b915cd2e4d6</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>59f93e44-360c-4109-9a31-2643765984a0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9aa8a752-61db-460f-8595-1c72d2d7160b</td>\n",
       "      <td>1988-07-16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLT</td>\n",
       "      <td>778e0a41-fe9c-411c-8439-7c46a09ef358</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9b6b5142-ffde-4b18-b84e-8f082114848c</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4e6cd5ab-b8dc-45b1-a1a8-d33b35f5d574</td>\n",
       "      <td>2021-02-05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PJ</td>\n",
       "      <td>c54b7014-b7e7-43fe-94a1-6da464d373d6</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>978fe7b9-2931-4aa8-af41-ccabb9991b08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a17c88e3-71f9-4ec8-9020-3a0fd5e23557</td>\n",
       "      <td>2021-10-26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLT</td>\n",
       "      <td>adaaf145-cb7c-457e-807e-b5ffccdf513e</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id                              Cargo_id  \\\n",
       "0  440af3f1-c1a8-43ad-a8d8-b8363119daa1                                   NaN   \n",
       "1  400282d4-894c-4d13-99f4-a4976b757ea5                                   NaN   \n",
       "2  1b8f7e93-2c9a-4e88-9592-ad98e8185822                                   NaN   \n",
       "3  51fb966b-c383-4e9a-9882-23748f4d4bdd                                   NaN   \n",
       "4  75665c2a-a81b-4c1a-bebd-a91009e38c11  5733dca2-b17a-4386-b98d-0364a67fe49d   \n",
       "5  0a835930-b4d0-43f9-bd18-dbc6da19e81e                                   NaN   \n",
       "6  4af463ee-5178-41d0-8da2-ac3ce495adbe                                   NaN   \n",
       "7  59f93e44-360c-4109-9a31-2643765984a0                                   NaN   \n",
       "8  9b6b5142-ffde-4b18-b84e-8f082114848c                                   NaN   \n",
       "9  978fe7b9-2931-4aa8-af41-ccabb9991b08                                   NaN   \n",
       "\n",
       "                            employee_id       Inicial         Final  \\\n",
       "0  a16a156e-aa4a-40c5-94a4-4bea0f443f39    2014-05-23           NaN   \n",
       "1  727208ef-44e7-4c85-8c25-199f7011e9f5    1981-10-27           NaN   \n",
       "2  ad64f750-8850-444b-9bb1-536951a1819d    1993-06-25           NaN   \n",
       "3  945e364e-0bc3-4ec7-a693-32fc0f8db8a1    2006-02-02  2022-04-02     \n",
       "4  945e364e-0bc3-4ec7-a693-32fc0f8db8a1  2022-04-02             NaN   \n",
       "5  f5fcda1f-5ef0-4e87-9768-70e048f232ae    2018-09-08           NaN   \n",
       "6  9ecf4b0d-f2c6-43a6-bc78-9aec6feea1ae    1989-09-25           NaN   \n",
       "7  9aa8a752-61db-460f-8595-1c72d2d7160b    1988-07-16           NaN   \n",
       "8  4e6cd5ab-b8dc-45b1-a1a8-d33b35f5d574    2021-02-05           NaN   \n",
       "9  a17c88e3-71f9-4ec8-9020-3a0fd5e23557    2021-10-26           NaN   \n",
       "\n",
       "  Vinc.Empreg.                              ID_Lider   Criado_em   Upload_em  \n",
       "0          CLT  4ac2c964-9996-445a-be5c-f9947fda5525  01-30-2023  01-30-2023  \n",
       "1           PJ  cdb9998c-2334-4826-a439-c6dd85808f7a  01-30-2023  01-30-2023  \n",
       "2          CLT  3c35393c-9a82-4dee-a129-c0bc95050517  01-30-2023  01-30-2023  \n",
       "3           PJ  6e1ead10-be57-43b2-a484-32f5fa09f92a  01-30-2023  01-30-2023  \n",
       "4           PJ  6e1ead10-be57-43b2-a484-32f5fa09f92a  01-30-2023  01-30-2023  \n",
       "5          CLT  ad32fa62-15d9-4fe0-a8cf-64b047e54268  01-30-2023  01-30-2023  \n",
       "6          CLT  234f2e33-d2ed-4a13-8e03-4b915cd2e4d6  01-30-2023  01-30-2023  \n",
       "7          CLT  778e0a41-fe9c-411c-8439-7c46a09ef358  01-30-2023  01-30-2023  \n",
       "8           PJ  c54b7014-b7e7-43fe-94a1-6da464d373d6  01-30-2023  01-30-2023  \n",
       "9          CLT  adaaf145-cb7c-457e-807e-b5ffccdf513e  01-30-2023  01-30-2023  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Carregando a planilha e unindo com a principal do RH.\n",
    "cargos_hist = pd.read_csv('planilhas\\\\cargo_temp_historico.csv',sep = ',')\n",
    "\n",
    "cargos_merge = data.merge(cargos_hist,\n",
    "                            on='Nome Funcionário',\n",
    "                            how='left')\n",
    "\n",
    "#--------------------Criando CSV do Histórico de Cargo--------------------#\n",
    "# Preenche as datas faltantes com a data de admissão, usando o fillna\n",
    "cargos_merge['Inicial'].fillna(\n",
    "cargos_merge['Dt. Admissão'],inplace=True)\n",
    "\n",
    "# cargos_merge['Inicial'] = cargos_merge['Inicial'].astype('datetime64')\n",
    "\n",
    "# Usando a função que defini acima. Passo primeiro a coluna onde deve conter o ID do gestor.\n",
    "# E na coluna com o nome do gestor eu uso o apply para aplicar a função. Onde ela deve procurar um match \n",
    "# com o nome de todos os funcionário, se encontrar vai retornar o ID. \n",
    "cargos_merge['get_leader_id'] = cargos_merge['Gestor'].apply(xlookup, args=(\n",
    "        employee_sheet['Nome'],employee_sheet['id']))\n",
    "\n",
    "\n",
    "# Usando a mesma função com o mesmo critério, mas agora buscando o ID do cargo.\n",
    "# Aqui vai pegar o ID do último cargo do histórico da troca.\n",
    "cargos_merge['get_position_id'] = cargos_merge['Cargo'].apply(xlookup, args=(\n",
    "        position_sheet['Nome Cargo'],position_sheet['id']))\n",
    "\n",
    "# Para funcionário que não tiveram troca de cargo, vou pegar o ID do primeiro cargo. Mesma forma de utilizar a função.\n",
    "cargos_merge['get_position_id_2'] = cargos_merge['Nome Cargo'].apply(xlookup, args=(\n",
    "        position_sheet['Nome Cargo'],position_sheet['id']))\n",
    "\n",
    "# Criando o CSV\n",
    "positionallocation_sheet = pd.DataFrame({'Cargo_id': cargos_merge['get_position_id'],\n",
    "                                        'employee_id': cargos_merge['employee_id'],\n",
    "                                        'Inicial': cargos_merge['Inicial'],\n",
    "                                        'Final': cargos_merge['Final'],\n",
    "                                        'Vinc.Empreg.': cargos_merge['Vinc.Empreg.'],\n",
    "                                        'ID_Lider': cargos_merge['get_leader_id'],\n",
    "                                        'Criado_em': today,\n",
    "                                        'Upload_em': today})\n",
    "\n",
    "\n",
    "positionallocation_sheet.insert(0,'id','') # Criando a nova coluna com o ID\n",
    "\n",
    "get_uuid4(positionallocation_sheet,'employee_id','id')\n",
    "\n",
    "positionallocation_sheet.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>Cargo_id</th>\n",
       "      <th>employee_id</th>\n",
       "      <th>Inicial</th>\n",
       "      <th>Final</th>\n",
       "      <th>Vinc.Empreg.</th>\n",
       "      <th>ID_Lider</th>\n",
       "      <th>Criado_em</th>\n",
       "      <th>Upload_em</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>440af3f1-c1a8-43ad-a8d8-b8363119daa1</td>\n",
       "      <td>5a1982d9-107e-411a-88b0-991113a95b8f</td>\n",
       "      <td>a16a156e-aa4a-40c5-94a4-4bea0f443f39</td>\n",
       "      <td>2014-05-23</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLT</td>\n",
       "      <td>4ac2c964-9996-445a-be5c-f9947fda5525</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>400282d4-894c-4d13-99f4-a4976b757ea5</td>\n",
       "      <td>30926fe1-2fea-455e-b850-1051dad7016d</td>\n",
       "      <td>727208ef-44e7-4c85-8c25-199f7011e9f5</td>\n",
       "      <td>1981-10-27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PJ</td>\n",
       "      <td>cdb9998c-2334-4826-a439-c6dd85808f7a</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1b8f7e93-2c9a-4e88-9592-ad98e8185822</td>\n",
       "      <td>e605ce44-652d-439e-8389-e6accb925f2e</td>\n",
       "      <td>ad64f750-8850-444b-9bb1-536951a1819d</td>\n",
       "      <td>1993-06-25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLT</td>\n",
       "      <td>3c35393c-9a82-4dee-a129-c0bc95050517</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>51fb966b-c383-4e9a-9882-23748f4d4bdd</td>\n",
       "      <td>3af79a73-2323-428d-9b19-7bed16a2f84e</td>\n",
       "      <td>945e364e-0bc3-4ec7-a693-32fc0f8db8a1</td>\n",
       "      <td>2006-02-02</td>\n",
       "      <td>2022-04-02</td>\n",
       "      <td>PJ</td>\n",
       "      <td>6e1ead10-be57-43b2-a484-32f5fa09f92a</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>75665c2a-a81b-4c1a-bebd-a91009e38c11</td>\n",
       "      <td>5733dca2-b17a-4386-b98d-0364a67fe49d</td>\n",
       "      <td>945e364e-0bc3-4ec7-a693-32fc0f8db8a1</td>\n",
       "      <td>2022-04-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PJ</td>\n",
       "      <td>6e1ead10-be57-43b2-a484-32f5fa09f92a</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0a835930-b4d0-43f9-bd18-dbc6da19e81e</td>\n",
       "      <td>22c00a7b-a0d6-4907-bf80-14a1a2728210</td>\n",
       "      <td>f5fcda1f-5ef0-4e87-9768-70e048f232ae</td>\n",
       "      <td>2018-09-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLT</td>\n",
       "      <td>ad32fa62-15d9-4fe0-a8cf-64b047e54268</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4af463ee-5178-41d0-8da2-ac3ce495adbe</td>\n",
       "      <td>9ae357f6-e1a0-4268-865b-88487728ec59</td>\n",
       "      <td>9ecf4b0d-f2c6-43a6-bc78-9aec6feea1ae</td>\n",
       "      <td>1989-09-25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLT</td>\n",
       "      <td>234f2e33-d2ed-4a13-8e03-4b915cd2e4d6</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>59f93e44-360c-4109-9a31-2643765984a0</td>\n",
       "      <td>2f3a9353-17a6-4722-a64c-d4e5f5539817</td>\n",
       "      <td>9aa8a752-61db-460f-8595-1c72d2d7160b</td>\n",
       "      <td>1988-07-16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLT</td>\n",
       "      <td>778e0a41-fe9c-411c-8439-7c46a09ef358</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9b6b5142-ffde-4b18-b84e-8f082114848c</td>\n",
       "      <td>52d2afff-fa2f-44bc-a6b5-fc8566f4c373</td>\n",
       "      <td>4e6cd5ab-b8dc-45b1-a1a8-d33b35f5d574</td>\n",
       "      <td>2021-02-05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PJ</td>\n",
       "      <td>c54b7014-b7e7-43fe-94a1-6da464d373d6</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>978fe7b9-2931-4aa8-af41-ccabb9991b08</td>\n",
       "      <td>36e55d80-cc9b-407a-9f33-bb315c2a40de</td>\n",
       "      <td>a17c88e3-71f9-4ec8-9020-3a0fd5e23557</td>\n",
       "      <td>2021-10-26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLT</td>\n",
       "      <td>adaaf145-cb7c-457e-807e-b5ffccdf513e</td>\n",
       "      <td>01-30-2023</td>\n",
       "      <td>01-30-2023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id                              Cargo_id  \\\n",
       "0  440af3f1-c1a8-43ad-a8d8-b8363119daa1  5a1982d9-107e-411a-88b0-991113a95b8f   \n",
       "1  400282d4-894c-4d13-99f4-a4976b757ea5  30926fe1-2fea-455e-b850-1051dad7016d   \n",
       "2  1b8f7e93-2c9a-4e88-9592-ad98e8185822  e605ce44-652d-439e-8389-e6accb925f2e   \n",
       "3  51fb966b-c383-4e9a-9882-23748f4d4bdd  3af79a73-2323-428d-9b19-7bed16a2f84e   \n",
       "4  75665c2a-a81b-4c1a-bebd-a91009e38c11  5733dca2-b17a-4386-b98d-0364a67fe49d   \n",
       "5  0a835930-b4d0-43f9-bd18-dbc6da19e81e  22c00a7b-a0d6-4907-bf80-14a1a2728210   \n",
       "6  4af463ee-5178-41d0-8da2-ac3ce495adbe  9ae357f6-e1a0-4268-865b-88487728ec59   \n",
       "7  59f93e44-360c-4109-9a31-2643765984a0  2f3a9353-17a6-4722-a64c-d4e5f5539817   \n",
       "8  9b6b5142-ffde-4b18-b84e-8f082114848c  52d2afff-fa2f-44bc-a6b5-fc8566f4c373   \n",
       "9  978fe7b9-2931-4aa8-af41-ccabb9991b08  36e55d80-cc9b-407a-9f33-bb315c2a40de   \n",
       "\n",
       "                            employee_id       Inicial         Final  \\\n",
       "0  a16a156e-aa4a-40c5-94a4-4bea0f443f39    2014-05-23           NaN   \n",
       "1  727208ef-44e7-4c85-8c25-199f7011e9f5    1981-10-27           NaN   \n",
       "2  ad64f750-8850-444b-9bb1-536951a1819d    1993-06-25           NaN   \n",
       "3  945e364e-0bc3-4ec7-a693-32fc0f8db8a1    2006-02-02  2022-04-02     \n",
       "4  945e364e-0bc3-4ec7-a693-32fc0f8db8a1  2022-04-02             NaN   \n",
       "5  f5fcda1f-5ef0-4e87-9768-70e048f232ae    2018-09-08           NaN   \n",
       "6  9ecf4b0d-f2c6-43a6-bc78-9aec6feea1ae    1989-09-25           NaN   \n",
       "7  9aa8a752-61db-460f-8595-1c72d2d7160b    1988-07-16           NaN   \n",
       "8  4e6cd5ab-b8dc-45b1-a1a8-d33b35f5d574    2021-02-05           NaN   \n",
       "9  a17c88e3-71f9-4ec8-9020-3a0fd5e23557    2021-10-26           NaN   \n",
       "\n",
       "  Vinc.Empreg.                              ID_Lider   Criado_em   Upload_em  \n",
       "0          CLT  4ac2c964-9996-445a-be5c-f9947fda5525  01-30-2023  01-30-2023  \n",
       "1           PJ  cdb9998c-2334-4826-a439-c6dd85808f7a  01-30-2023  01-30-2023  \n",
       "2          CLT  3c35393c-9a82-4dee-a129-c0bc95050517  01-30-2023  01-30-2023  \n",
       "3           PJ  6e1ead10-be57-43b2-a484-32f5fa09f92a  01-30-2023  01-30-2023  \n",
       "4           PJ  6e1ead10-be57-43b2-a484-32f5fa09f92a  01-30-2023  01-30-2023  \n",
       "5          CLT  ad32fa62-15d9-4fe0-a8cf-64b047e54268  01-30-2023  01-30-2023  \n",
       "6          CLT  234f2e33-d2ed-4a13-8e03-4b915cd2e4d6  01-30-2023  01-30-2023  \n",
       "7          CLT  778e0a41-fe9c-411c-8439-7c46a09ef358  01-30-2023  01-30-2023  \n",
       "8           PJ  c54b7014-b7e7-43fe-94a1-6da464d373d6  01-30-2023  01-30-2023  \n",
       "9          CLT  adaaf145-cb7c-457e-807e-b5ffccdf513e  01-30-2023  01-30-2023  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Como é possível notar na coluna Cargo_id contém valores missing. \n",
    "# Para corrigir, agora vou preencher com o ID do primeiro cargo. Para aqueles que não receberam uma promoção e portanto, não tiveram troca. \n",
    "\n",
    "positionallocation_sheet['Cargo_id'].fillna(cargos_merge['get_position_id_2'], inplace=True)\n",
    "\n",
    "# Salvando o novo CSV\n",
    "positionallocation_sheet.to_csv('output\\\\cargos_historico.csv',index=False)\n",
    "\n",
    "positionallocation_sheet.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Por fim é hora de excluir as planilhas temporárias. \n",
    "\n",
    "if os.path.exists('planilhas\\\\salario_temp_historico.csv'):\n",
    "    os.remove('planilhas\\\\salario_temp_historico.csv')\n",
    "\n",
    "if os.path.exists('planilhas\\\\cargo_temp_historico.csv'):\n",
    "    os.remove('planilhas\\\\cargo_temp_historico.csv')\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.5 (tags/v3.10.5:f377153, Jun  6 2022, 16:14:13) [MSC v.1929 64 bit (AMD64)]"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "02b04277306c2ea5e68d5a116435517dd2029806f1cff450d3f50faab723e5c0"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}