AnaliseDeDadosECriacaoDeRelatorio.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f07c345",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importar a base de dados e visualizá-la\n",
    "# Calcular o faturamento, a quantidade de produtos e o ticket médio de acordo com cada loja\n",
    "# Criar um relatório para ser encaminhado via e-mail "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5e007d89",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Importar as bibliotecas necessárias para manuseio dos dados\n",
    "import pandas as pd\n",
    "import win32com.client as win32\n",
    "\n",
    "# Importar a base de dados\n",
    "tabela_vendas = pd.read_excel('C:\\\\Users\\\\Maria Eduarda\\\\Downloads\\\\ProjetosPython\\\\Vendas.xlsx')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a82f206a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Código Venda       Data                     ID Loja           Produto  \\\n",
      "0                  1 2019-01-01          Iguatemi Esplanada    Sapato Estampa   \n",
      "1                  1 2019-01-01          Iguatemi Esplanada          Camiseta   \n",
      "2                  1 2019-01-01          Iguatemi Esplanada     Sapato Xadrez   \n",
      "3                  2 2019-01-02              Norte Shopping           Relógio   \n",
      "4                  2 2019-01-02              Norte Shopping      Chinelo Liso   \n",
      "...              ...        ...                         ...               ...   \n",
      "100994         69996 2019-12-26  Center Shopping Uberlândia    Short Listrado   \n",
      "100995         69996 2019-12-26  Center Shopping Uberlândia           Mochila   \n",
      "100996         69996 2019-12-26  Center Shopping Uberlândia  Pulseira Estampa   \n",
      "100997         69997 2019-12-26           Ribeirão Shopping   Camisa Listrado   \n",
      "100998         69997 2019-12-26           Ribeirão Shopping       Short Linho   \n",
      "\n",
      "        Quantidade  Valor Unitário  Valor Final  \n",
      "0                1             358          358  \n",
      "1                2             180          360  \n",
      "2                1             368          368  \n",
      "3                3             200          600  \n",
      "4                1              71           71  \n",
      "...            ...             ...          ...  \n",
      "100994           2             102          204  \n",
      "100995           4             270         1080  \n",
      "100996           1              87           87  \n",
      "100997           1             108          108  \n",
      "100998           2             133          266  \n",
      "\n",
      "[100999 rows x 7 columns]\n"
     ]
    }
   ],
   "source": [
    "# Visualizar a base de dados\n",
    "pd.set_option('display.max_columns', None)\n",
    "print(tabela_vendas)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e1ed0fa4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                  Valor Final\n",
      "ID Loja                                      \n",
      "Bourbon Shopping SP                   1726110\n",
      "Center Shopping Uberlândia            1668921\n",
      "Iguatemi Campinas                     1762419\n",
      "Iguatemi Esplanada                    1699681\n",
      "Norte Shopping                        1711968\n",
      "Novo Shopping Ribeirão Preto          1678225\n",
      "Palladium Shopping Curitiba           1721120\n",
      "Parque Dom Pedro Shopping             1631630\n",
      "Passei das Águas Shopping             1649014\n",
      "Ribeirão Shopping                     1707122\n",
      "Rio Mar Recife                        1722766\n",
      "Rio Mar Shopping Fortaleza            1698430\n",
      "Salvador Shopping                     1646739\n",
      "Shopping Barra                        1653103\n",
      "Shopping Center Interlagos            1640486\n",
      "Shopping Center Leste Aricanduva      1682870\n",
      "Shopping Eldorado                     1648111\n",
      "Shopping Ibirapuera                   1635060\n",
      "Shopping Iguatemi Fortaleza           1674824\n",
      "Shopping Midway Mall                  1590441\n",
      "Shopping Morumbi                      1586444\n",
      "Shopping Recife                       1624879\n",
      "Shopping SP Market                    1721763\n",
      "Shopping União de Osasco              1663770\n",
      "Shopping Vila Velha                   1731167\n",
      "                                  Quantidade\n",
      "ID Loja                                     \n",
      "Bourbon Shopping SP                     8863\n",
      "Center Shopping Uberlândia              8627\n",
      "Iguatemi Campinas                       8935\n",
      "Iguatemi Esplanada                      8580\n",
      "Norte Shopping                          9014\n",
      "Novo Shopping Ribeirão Preto            8751\n",
      "Palladium Shopping Curitiba             9091\n",
      "Parque Dom Pedro Shopping               8388\n",
      "Passei das Águas Shopping               8618\n",
      "Ribeirão Shopping                       8825\n",
      "Rio Mar Recife                          8863\n",
      "Rio Mar Shopping Fortaleza              8937\n",
      "Salvador Shopping                       8698\n",
      "Shopping Barra                          8638\n",
      "Shopping Center Interlagos              8675\n",
      "Shopping Center Leste Aricanduva        8938\n",
      "Shopping Eldorado                       8719\n",
      "Shopping Ibirapuera                     8723\n",
      "Shopping Iguatemi Fortaleza             8629\n",
      "Shopping Midway Mall                    8206\n",
      "Shopping Morumbi                        8508\n",
      "Shopping Recife                         8581\n",
      "Shopping SP Market                      8927\n",
      "Shopping União de Osasco                8730\n",
      "Shopping Vila Velha                     9224\n",
      "--------------------------------------------------\n",
      "                                  Ticket Médio\n",
      "ID Loja                                       \n",
      "Bourbon Shopping SP                 194.754598\n",
      "Center Shopping Uberlândia          193.453228\n",
      "Iguatemi Campinas                   197.248909\n",
      "Iguatemi Esplanada                  198.098019\n",
      "Norte Shopping                      189.923231\n",
      "Novo Shopping Ribeirão Preto        191.775226\n",
      "Palladium Shopping Curitiba         189.321307\n",
      "Parque Dom Pedro Shopping           194.519552\n",
      "Passei das Águas Shopping           191.345324\n",
      "Ribeirão Shopping                   193.441586\n",
      "Rio Mar Recife                      194.377299\n",
      "Rio Mar Shopping Fortaleza          190.044758\n",
      "Salvador Shopping                   189.323868\n",
      "Shopping Barra                      191.375666\n",
      "Shopping Center Interlagos          189.105014\n",
      "Shopping Center Leste Aricanduva    188.282614\n",
      "Shopping Eldorado                   189.025232\n",
      "Shopping Ibirapuera                 187.442394\n",
      "Shopping Iguatemi Fortaleza         194.092479\n",
      "Shopping Midway Mall                193.814404\n",
      "Shopping Morumbi                    186.464974\n",
      "Shopping Recife                     189.357767\n",
      "Shopping SP Market                  192.871401\n",
      "Shopping União de Osasco            190.580756\n",
      "Shopping Vila Velha                 187.680724\n"
     ]
    }
   ],
   "source": [
    "# Calcular o faturamento por loja\n",
    "faturamento = tabela_vendas[['ID Loja', 'Valor Final']].groupby('ID Loja').sum()\n",
    "print(faturamento)\n",
    "\n",
    "# Calcular a quantidade de produtos vendidos por loja\n",
    "quantidade = tabela_vendas[['ID Loja', 'Quantidade']].groupby('ID Loja').sum()\n",
    "print(quantidade)\n",
    "\n",
    "print('-' * 50)\n",
    "# Calcular o ticket médio por produto em cada loja\n",
    "ticket_medio = (faturamento['Valor Final'] / quantidade['Quantidade']).to_frame()\n",
    "ticket_medio = ticket_medio.rename(columns={0: 'Ticket Médio'})\n",
    "print(ticket_medio)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b19e2ffd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Enviar um e-mail com o relatório dos dados recolhidos \n",
    "# PS: Será necessário instalar o Microsoft Outlook na sua máquina\n",
    "outlook = win32.Dispatch('outlook.application')\n",
    "mail = outlook.CreateItem(0)\n",
    "\n",
    "# Insira aqui o seu endereço de e-mail favorito\n",
    "\n",
    "mail.To = 'mariaeduardaruck@gmail.com'\n",
    "mail.Subject = 'Relatório de Vendas por loja'\n",
    "mail.HTMLBody = f'''\n",
    "<p>Prezados,</p>\n",
    "\n",
    "<p>Segue o Relatório de Vendas por cada loja.</p>\n",
    "\n",
    "<p>Faturamento:</p>\n",
    "{faturamento.to_html(formatters={'Valor Final': 'R${:,.2f}'.format})}\n",
    "\n",
    "<p>Quantidade Vendida:</p>\n",
    "{quantidade.to_html()}\n",
    "\n",
    "<p>Ticket Médio dos Produtos em cada Loja:</p>\n",
    "{ticket_medio.to_html(formatters={'Ticket Médio': 'R${:,.2f}'.format})}\n",
    "\n",
    "<p>Qualquer dúvida estou à disposição.</p>\n",
    "\n",
    "<p>Att.,</p>\n",
    "<p>Lira</p>\n",
    "'''\n",
    "\n",
    "mail.Send()\n",
    "\n",
    "print('Email Enviado')"
   ]
  }
 ],
 "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}