Project 1 - Automação análise de dados.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "50660f1b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import pathlib\n",
    "import win32com.client as win32"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7178f430",
   "metadata": {},
   "outputs": [],
   "source": [
    "emails = pd.read_excel(r'Bases de Dados\\Emails.xlsx')\n",
    "lojas = pd.read_csv(r'Bases de Dados\\Lojas.csv', encoding = 'latin1', sep=';')\n",
    "vendas = pd.read_excel(r'Bases de Dados\\Vendas.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d6feb70a",
   "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>Código Venda</th>\n",
       "      <th>Data</th>\n",
       "      <th>ID Loja</th>\n",
       "      <th>Produto</th>\n",
       "      <th>Quantidade</th>\n",
       "      <th>Valor Unitário</th>\n",
       "      <th>Valor Final</th>\n",
       "      <th>Loja</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>Sapato Estampa</td>\n",
       "      <td>1</td>\n",
       "      <td>358</td>\n",
       "      <td>358</td>\n",
       "      <td>Iguatemi Esplanada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>Camiseta</td>\n",
       "      <td>2</td>\n",
       "      <td>180</td>\n",
       "      <td>360</td>\n",
       "      <td>Iguatemi Esplanada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>Sapato Xadrez</td>\n",
       "      <td>1</td>\n",
       "      <td>368</td>\n",
       "      <td>368</td>\n",
       "      <td>Iguatemi Esplanada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>2019-01-02</td>\n",
       "      <td>1</td>\n",
       "      <td>Camisa Gola V Listrado</td>\n",
       "      <td>2</td>\n",
       "      <td>116</td>\n",
       "      <td>232</td>\n",
       "      <td>Iguatemi Esplanada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>34</td>\n",
       "      <td>2019-01-02</td>\n",
       "      <td>1</td>\n",
       "      <td>Sapato Listrado</td>\n",
       "      <td>1</td>\n",
       "      <td>363</td>\n",
       "      <td>363</td>\n",
       "      <td>Iguatemi Esplanada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100994</th>\n",
       "      <td>69846</td>\n",
       "      <td>2019-12-25</td>\n",
       "      <td>8</td>\n",
       "      <td>Short Estampa</td>\n",
       "      <td>2</td>\n",
       "      <td>96</td>\n",
       "      <td>192</td>\n",
       "      <td>Salvador Shopping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100995</th>\n",
       "      <td>69846</td>\n",
       "      <td>2019-12-25</td>\n",
       "      <td>8</td>\n",
       "      <td>Tênis Estampa</td>\n",
       "      <td>5</td>\n",
       "      <td>256</td>\n",
       "      <td>1280</td>\n",
       "      <td>Salvador Shopping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100996</th>\n",
       "      <td>69850</td>\n",
       "      <td>2019-12-25</td>\n",
       "      <td>8</td>\n",
       "      <td>Calça Estampa</td>\n",
       "      <td>4</td>\n",
       "      <td>177</td>\n",
       "      <td>708</td>\n",
       "      <td>Salvador Shopping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100997</th>\n",
       "      <td>69972</td>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>8</td>\n",
       "      <td>Terno Liso</td>\n",
       "      <td>3</td>\n",
       "      <td>720</td>\n",
       "      <td>2160</td>\n",
       "      <td>Salvador Shopping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100998</th>\n",
       "      <td>69972</td>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>8</td>\n",
       "      <td>Sapato Estampa</td>\n",
       "      <td>5</td>\n",
       "      <td>358</td>\n",
       "      <td>1790</td>\n",
       "      <td>Salvador Shopping</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100999 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Código Venda       Data  ID Loja                 Produto  Quantidade  \\\n",
       "0                  1 2019-01-01        1          Sapato Estampa           1   \n",
       "1                  1 2019-01-01        1                Camiseta           2   \n",
       "2                  1 2019-01-01        1           Sapato Xadrez           1   \n",
       "3                 21 2019-01-02        1  Camisa Gola V Listrado           2   \n",
       "4                 34 2019-01-02        1         Sapato Listrado           1   \n",
       "...              ...        ...      ...                     ...         ...   \n",
       "100994         69846 2019-12-25        8           Short Estampa           2   \n",
       "100995         69846 2019-12-25        8           Tênis Estampa           5   \n",
       "100996         69850 2019-12-25        8           Calça Estampa           4   \n",
       "100997         69972 2019-12-26        8              Terno Liso           3   \n",
       "100998         69972 2019-12-26        8          Sapato Estampa           5   \n",
       "\n",
       "        Valor Unitário  Valor Final                Loja  \n",
       "0                  358          358  Iguatemi Esplanada  \n",
       "1                  180          360  Iguatemi Esplanada  \n",
       "2                  368          368  Iguatemi Esplanada  \n",
       "3                  116          232  Iguatemi Esplanada  \n",
       "4                  363          363  Iguatemi Esplanada  \n",
       "...                ...          ...                 ...  \n",
       "100994              96          192   Salvador Shopping  \n",
       "100995             256         1280   Salvador Shopping  \n",
       "100996             177          708   Salvador Shopping  \n",
       "100997             720         2160   Salvador Shopping  \n",
       "100998             358         1790   Salvador Shopping  \n",
       "\n",
       "[100999 rows x 8 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "vendas = vendas.merge(lojas, on='ID Loja')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e4f68c05",
   "metadata": {},
   "outputs": [],
   "source": [
    "dic_lojas = {}\n",
    "for loja in lojas['Loja']:\n",
    "    dic_lojas[loja] = vendas.loc[vendas['Loja'] == loja,:]\n",
    "dia_indic = vendas['Data'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6f56fe3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#C:\\Users\\GAMER\\Favorites\\Python_programs\\Project 1\\Backup Arquivos Lojas\n",
    "caminho = pathlib.Path(r'C:\\Users\\GAMER\\Favorites\\Python_programs\\Project 1\\Backup Arquivos Lojas')\n",
    "arq_past_backup = caminho.iterdir()\n",
    "lista_nomes = [arquivo.name for arquivo in arq_past_backup]\n",
    "for loja in dic_lojas:\n",
    "    if loja not in lista_nomes:\n",
    "        nova_loja = caminho / loja\n",
    "        nova_loja.mkdir()\n",
    "        \n",
    "        \n",
    "    nome_arquivo = f'{dia_indic.month}_{dia_indic.day}_{loja}.xlsx'\n",
    "    local_arquivo = caminho / loja / nome_arquivo\n",
    "    dic_lojas[loja].to_excel(local_arquivo)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9079f167",
   "metadata": {},
   "outputs": [],
   "source": [
    "meta_fat_dia = 1000\n",
    "meta_fat_ano = 1650000\n",
    "meta_qtdeprod_dia = 4\n",
    "meta_qtdeprod_ano = 120\n",
    "meta_tkmed_dia = 500\n",
    "meta_tkmed_ano = 500"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db1f02d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "for loja in dic_lojas:\n",
    "    vendas_loja = dic_lojas[loja]\n",
    "    vendas_loja_dia = vendas_loja.loc[vendas_loja['Data'] == dia_indic, :]\n",
    "\n",
    "    #Faturamento\n",
    "    fat_ano = vendas_loja['Valor Final'].sum()\n",
    "    fat_dia = vendas_loja_dia['Valor Final'].sum()\n",
    "\n",
    "    #Diversidade de produtos\n",
    "    qtde_ano = len(vendas_loja['Produto'].unique())\n",
    "    qtde_dia = len(vendas_loja_dia['Produto'].unique())\n",
    "\n",
    "    #Ticket médio\n",
    "    valor_venda_ano = vendas_loja.groupby('Código Venda').sum()\n",
    "    valor_venda_dia = vendas_loja_dia.groupby('Código Venda').sum()\n",
    "    tk_med_ano = valor_venda_ano['Valor Final'].mean()\n",
    "    tk_med_dia = valor_venda_dia['Valor Final'].mean()\n",
    "    if fat_dia >= meta_fat_dia:\n",
    "        cor_fat_dia = 'green'\n",
    "    else:\n",
    "        cor_fat_dia = 'red' \n",
    "    if qtde_dia >= meta_qtdeprod_dia:\n",
    "        cor_qtde_dia = 'green'\n",
    "    else:\n",
    "        cor_qtde_dia = 'red' \n",
    "    if tk_med_dia >= meta_tkmed_dia:\n",
    "        cor_tk_med_dia = 'green'\n",
    "    else:\n",
    "        cor_tk_med_dia = 'red'\n",
    "\n",
    "    if fat_ano >= meta_fat_ano:\n",
    "        cor_fat_ano = 'green'\n",
    "    else:\n",
    "        cor_fat_ano = 'red' \n",
    "    if qtde_ano >= meta_qtdeprod_ano:\n",
    "        cor_qtde_ano = 'green'\n",
    "    else:\n",
    "        cor_qtde_ano = 'red' \n",
    "    if tk_med_ano >= meta_tkmed_ano:\n",
    "        cor_tk_med_ano = 'green'\n",
    "    else:\n",
    "        cor_tk_med_ano = 'red' \n",
    "\n",
    "    outlook = win32.Dispatch('outlook.application')\n",
    "\n",
    "    nome = email.loc[emails['Loja']==loja,'Gerente'].values[0]\n",
    "    mail = outlook.CreateItem(0)\n",
    "    mail.To = email.loc[emails['Loja']==loja,'E-mail'].values[0]\n",
    "    mail.Subject = f'OnePage Dia {dia_indic.day}/{dia_indic.month} - Loja {loja}'\n",
    "    mail.HTMLBody = f'''\n",
    "    <p>Bom dia, {nome}</p>\n",
    "    <p>O resultado de ontem <strong>({dia_indic.day}/{dia_indic.month})</strong> da Loja {loja} foi:</p>\n",
    "\n",
    "    <table>\n",
    "      <tr>\n",
    "        <th>Indicador</th>\n",
    "        <th>Valor Dia</th>\n",
    "        <th>Meta Dia</th>\n",
    "        <th>Cenário Dia</th>\n",
    "      </tr>\n",
    "      <tr>\n",
    "        <td>Faturamento</td>\n",
    "        <td style=\"text-align: center;\"> R${fat_dia:.2f} </td>\n",
    "        <td style=\"text-align: center;\"> R${meta_fat_dia:.2f} </td>\n",
    "        <td style=\"text-align: center; font-size: 20px;\"><font color=\"{cor_fat_dia}\"> ֍ </font></td>\n",
    "      </tr>\n",
    "      <tr>\n",
    "        <td>Diversidade de Produtos</td>\n",
    "        <td style=\"text-align: center;\"> {qtde_dia} </td>\n",
    "        <td style=\"text-align: center;\"> {meta_qtdeprod_dia} </td>\n",
    "        <td style=\"text-align: center; font-size: 20px;\"><font color=\"{cor_qtde_dia}\"> ֍ </font></td>\n",
    "      </tr>\n",
    "      <tr>\n",
    "        <td>Ticket Médio</td>\n",
    "        <td style=\"text-align: center;\"> R${tk_med_dia:.2f} </td>\n",
    "        <td style=\"text-align: center;\"> R${meta_tkmed_dia:.2f} </td>\n",
    "        <td style=\"text-align: center; font-size: 20px;\"><font color=\"{cor_tk_med_dia}\"> ֍ </font></td>\n",
    "      </tr>\n",
    "    </table>\n",
    "\n",
    "    <table>\n",
    "      <tr>\n",
    "        <th>Indicador</th>\n",
    "        <th>Valor Ano</th>\n",
    "        <th>Meta Ano</th>\n",
    "        <th>Cenário Ano</th>\n",
    "      </tr>\n",
    "      <tr>\n",
    "        <td>Faturamento</td>\n",
    "        <td style=\"text-align: center;\"> R${fat_ano:.2f} </td>\n",
    "        <td style=\"text-align: center;\"> R${meta_fat_ano:.2f} </td>\n",
    "        <td style=\"text-align: center; font-size: 20px;\"><font color=\"{cor_fat_ano}\"> ֍ </font></td>\n",
    "      </tr>\n",
    "      <tr>\n",
    "        <td>Diversidade de Produtos</td>\n",
    "        <td style=\"text-align: center;\"> {qtde_ano} </td>\n",
    "        <td style=\"text-align: center;\"> {meta_qtdeprod_ano} </td>\n",
    "        <td style=\"text-align: center; font-size: 20px;\"><font color=\"{cor_qtde_ano}\"> ֍ </font></td>\n",
    "      </tr>\n",
    "      <tr>\n",
    "        <td>Ticket Médio</td>\n",
    "        <td style=\"text-align: center;\"> R${tk_med_ano:.2f} </td>\n",
    "        <td style=\"text-align: center;\"> R${meta_tkmed_ano:.2f} </td>\n",
    "        <td style=\"text-align: center; font-size: 20px;\"><font color=\"{cor_tk_med_ano}\"> ֍ </font></td>\n",
    "      </tr>\n",
    "    </table>\n",
    "\n",
    "    <p>Segue em anexo a planilha com todos os dados ara mais detalhes.</p>\n",
    "    <p>Qualquer dúvida estou a disposição.</p>\n",
    "    <p>Att.,</p>\n",
    "    <p>Yuri</p>\n",
    "    '''\n",
    "    attachment = pathlib.Path.cwd() / caminho / loja / f'{dia_indic.month}_{dia_indic.day}_{loja}.xlsx'\n",
    "    mail.Attachments.Add(str(attachment))\n",
    "\n",
    "    mail.Send()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a259d1fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "fat_lojas = vendas.groupby('Loja')[['Loja', 'Valor Final']].sum()\n",
    "fat_lojas_ano = fat_lojas.sort_values(by='Valor Final', ascending=False)\n",
    "\n",
    "nome_arquivo = f'{dia_indic.month}_{dia_indic.day}_Ranking Anual.xlsx'\n",
    "local_arquivo = r'Backup Arquivos Lojas\\{}'.format(nome_arquivo)\n",
    "fat_lojas_ano.to_excel(local_arquivo)\n",
    "\n",
    "vendas_dia = vendas.loc[vendas['Data']==dia_indic, :]\n",
    "fat_lojas_dia = vendas_dia.groupby('Loja')[['Loja', 'Valor Final']].sum()\n",
    "fat_lojas_dia = fat_lojas_dia.sort_values(by='Valor Final', ascending=False)\n",
    "\n",
    "nome_arquivo = f'{dia_indic.month}_{dia_indic.day}_Ranking Diário.xlsx'\n",
    "local_arquivo = r'Backup Arquivos Lojas\\{}'.format(nome_arquivo)\n",
    "fat_lojas_dia.to_excel(local_arquivo)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18a07fa3",
   "metadata": {},
   "outputs": [],
   "source": [
    "outlook = win32.Dispatch('outlook.application')\n",
    "\n",
    "mail = outlook.CreateItem(0)\n",
    "mail.To = email.loc[emails['Loja']=='Diretoria','E-mail'].values[0]\n",
    "mail.Subject = f'Ranking Dia {dia_indic.day}/{dia_indic.month}'\n",
    "mail.Body = f'''\n",
    "Bom dia.\n",
    "\n",
    "Melhor loja do dia em Faturamento: Loja {fat_lojas_dia.index[0]} = R${fat_lojas_dia.iloc[0, 0]:.2f}\n",
    "Pior loja do dia em Faturamento: Loja {fat_lojas_dia.index[-1]} = R${fat_lojas_dia.iloc[-1, 0]:.2f}\n",
    "\n",
    "Melhor loja do ano em Faturamento: Loja {fat_lojas_ano.index[0]} = R${fat_lojas_ano.iloc[0, 0]:.2f}\n",
    "Pior loja do ano em Faturamento: Loja {fat_lojas_ano.index[-1]} = R${fat_lojas_ano.iloc[-1, 0]:.2f} \n",
    "\n",
    "\n",
    "Segue em anexo os rankings do ano e do dia de todas as lojas.\n",
    "\n",
    "Qualquer dúvida estou a disposição.\n",
    "\n",
    "Att.,\n",
    "Yuri\n",
    "'''\n",
    "attachment = pathlib.Path.cwd() / caminho / f'{dia_indic.month}_{dia_indic.day}_Ranking Diário.xlsx'\n",
    "mail.Attachments.Add(str(attachment))\n",
    "attachment = pathlib.Path.cwd() / caminho / f'{dia_indic.month}_{dia_indic.day}_Ranking Anual.xlsx'\n",
    "mail.Attachments.Add(str(attachment))\n",
    "\n",
    "mail.Send()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}