Exploração de Preços ORACLE.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/RedCanister/Just-Code/blob/main/Explora%C3%A7%C3%A3o%20de%20Pre%C3%A7os%20ORACLE.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Exploração dos preços das licenças da Oracle\n",
        "Olá, sejam todos bem-vindxs, sou Jorge Kayodê, estudante de Sistemas para Internet no 4º semestre, e estou trazendo para vocês um trabalho que fiz tratando e visualizando os dados de uma tabela de preços com todas as licenças da Oracle, com os preços atualizados pela última vez em Setembro de 2021, então, bem atualizados. Espero que esse notebook possa te ajudar quando tiver que realizar procedimentos enquanto estuda Ciência de Dados, a área que em que eu quero formar uma carreira."
      ],
      "metadata": {
        "id": "tn5P5e-KV0jo"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Motivação: \n",
        "Decidi realizar esse projeto depois de ter que fazer um trabalho onde pesquisamos e fizemos uma apresentação sobre Sistemas de Gerenciamento de Banco de dados, ou SGBDs, e fiquei responsável pelo Oracle, junto de Olenice Fátima, colega de sala. Durante as pesquisas, encontramos uma tabela de preços com todas as licenças da Oracle, e foi aí que decidi fazer um trabalho sobre esses dados, e trazê-los para a turma desfrutar de uma apresentação robusta sobre a Oracle, mas no fim, não pude fazer isso a tempo da apresentação, então fomos com o básico, e agora estou trazendo essas informações para que você, estudante de Ti aleatórix, possa saber sobre as licenças dessa empresa que está presente em tantos negócios no mundo todo.\n"
      ],
      "metadata": {
        "id": "j8dbfw7knR0W"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Estrutura do Projeto:\n",
        "Nesse notebook do Google Colaboratory, você vai encontrar todo o código que usei para realizar esse trabalho, também com as referências que eu me lembrei de guardar 🤓, na seguinte ordem:\n",
        "\n",
        "\n",
        "1.   Extração dos Dados do Dataset para o DataFrame Pandas\n",
        "2.   Transformação e formatação do DataFrame no objeto de análise\n",
        "3.   Exploração dos Dados em pandaSQL\n",
        "4.   [Planejado] Visualização dos Dados\n",
        "\n",
        "Isso por que estamos seguindo o processo ETL, bem conhecido na área de Business Intelligence como Extract, Transform, and Load, ou Extrair, Transformar e Carregar. Extraímos os dados de uma fonte, transformamos os mesmos em um formato que vamos utilizar e os carregamos para uma aplicação para que seja utilizado, nesse caso, um dashboard que ainda vou aprender a fazer, seja no Tableau, ou no Power BI."
      ],
      "metadata": {
        "id": "olY8ylSPoObw"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "SFonV3dnEa6Q",
        "outputId": "0ba34816-1fb5-4f36-df8d-01ad2d02bfee"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting pandasql\n",
            "  Downloading pandasql-0.7.3.tar.gz (26 kB)\n",
            "Requirement already satisfied: numpy in /usr/local/lib/python3.7/dist-packages (from pandasql) (1.21.6)\n",
            "Requirement already satisfied: pandas in /usr/local/lib/python3.7/dist-packages (from pandasql) (1.3.5)\n",
            "Requirement already satisfied: sqlalchemy in /usr/local/lib/python3.7/dist-packages (from pandasql) (1.4.35)\n",
            "Requirement already satisfied: python-dateutil>=2.7.3 in /usr/local/lib/python3.7/dist-packages (from pandas->pandasql) (2.8.2)\n",
            "Requirement already satisfied: pytz>=2017.3 in /usr/local/lib/python3.7/dist-packages (from pandas->pandasql) (2022.1)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.7/dist-packages (from python-dateutil>=2.7.3->pandas->pandasql) (1.15.0)\n",
            "Requirement already satisfied: importlib-metadata in /usr/local/lib/python3.7/dist-packages (from sqlalchemy->pandasql) (4.11.3)\n",
            "Requirement already satisfied: greenlet!=0.4.17 in /usr/local/lib/python3.7/dist-packages (from sqlalchemy->pandasql) (1.1.2)\n",
            "Requirement already satisfied: zipp>=0.5 in /usr/local/lib/python3.7/dist-packages (from importlib-metadata->sqlalchemy->pandasql) (3.8.0)\n",
            "Requirement already satisfied: typing-extensions>=3.6.4 in /usr/local/lib/python3.7/dist-packages (from importlib-metadata->sqlalchemy->pandasql) (4.2.0)\n",
            "Building wheels for collected packages: pandasql\n",
            "  Building wheel for pandasql (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for pandasql: filename=pandasql-0.7.3-py3-none-any.whl size=26784 sha256=f7d5534b432196d2139b4ebf911793a0f1f725f6e9ad829274871be40c2fad1b\n",
            "  Stored in directory: /root/.cache/pip/wheels/5c/4b/ec/41f4e116c8053c3654e2c2a47c62b4fca34cc67ef7b55deb7f\n",
            "Successfully built pandasql\n",
            "Installing collected packages: pandasql\n",
            "Successfully installed pandasql-0.7.3\n"
          ]
        }
      ],
      "source": [
        "!pip install pandasql -U"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hhoHXSaoZUoG"
      },
      "source": [
        "## Extraindo o Dataset\n",
        "\n",
        "Esta etapa de extração da tabela foi complicada, e provavelmente poderia ser melhorada com um refinamento dos parâmetros que ainda parece ser meio complexo pra mim, 27% dos dados foram perdidos no momento da conversão das tabelas do arquivo em pdf para um arquivo excel. Eu usei uma ferramenta da Adobe para realizar a conversão e então extrair os dados para realizar essa análise, com o resultado sendo o que verão agora. Conseguindo uma versão mais íntegra dessa tabela, muitos dos procedimentos desse notebook se tornariam irrelevantes, mas deixo aqui para que saibam o que fazer na pior das situações."
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Referências: \n",
        "\n",
        "https://pandas.pydata.org/pandas-docs/stable/index.html\n",
        "\n",
        "https://stackoverflow.com/questions/20937538/how-to-display-pandas-dataframe-of-floats-using-a-format-string-for-columns"
      ],
      "metadata": {
        "id": "d0Rx_L80vThW"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 179,
      "metadata": {
        "id": "q8JAPCg8ELBU"
      },
      "outputs": [],
      "source": [
        "# Estaremos utilizando essa duas bilbiotecas, Pandas, a mais popular biblioteca \n",
        "#de Ciência de Dados no Python, e pandasql, que traz consultas em SQL para cada projeto\n",
        "# e matplotlib é um biblioteca de visualização de dados\n",
        "import pandas as pd\n",
        "from pandasql import sqldf"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "VTCkPSlWF701",
        "outputId": "9760c0df-298f-4280-893c-cb1f596492d6"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ],
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Nessa linha vamos alterar a formatação de todos os valores float desse notebook,\n",
        "#assim adicionando uma cifra e limitando o valor a duas casas decimais\n",
        "pd.options.display.float_format = 'R${:,.2f}'.format"
      ],
      "metadata": {
        "id": "TpBQAnbYVNDQ"
      },
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "metadata": {
        "id": "ziYRC6wrFWwS"
      },
      "outputs": [],
      "source": [
        "# Aqui estamos conjurando o dataset que vamos utilizar a partir de um arquivo\n",
        "#que está salvo no meu drive, mas o mesmo pode ser feito a partir de um link\n",
        "file = \"/content/drive/MyDrive/Colab Notebooks/Exploração Oracle/precosOracle.xlsx\"\n",
        "url = \"https://docs.google.com/spreadsheets/d/1jqS2gpZL_HibEDuPJRuHZK5gefm3UNAh/edit?usp=sharing&ouid=106189476960706264778&rtpof=true&sd=true\"\n",
        "precosOracle = pd.read_excel(file)"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Observando o dataset abaixo, podemos ver que no momento ele está inoperável, sendo até difícil realizar as consultas mais básicas que temos. Algumas colunas que deveriam estar separadas se convergeram, e ainda foram criadas duas colunas vazias que não da pra dizer o que deveria ser, sem contar também com as colunas que sequer foram criadas, mas felizmente, vamos jogar tudo isso fora. Devo ressaltar que nesse projeto, o foco da análise será os preços das licenças, não considerando se é uma licença de 12 meses, ou um só, e nem para qual negócio ou qual licença seria a melhor escolha, estamos apenas compreendendo a distribuição dos preços, e quais relações entre eles podemos encontrar aqui.\n",
        "\n",
        "Referência:\n",
        "https://www.adobe.com/br/acrobat/online/convert-pdf.html\n",
        "\n",
        "https://betterprogramming.pub/convert-tables-from-pdfs-to-pandas-with-python-d74f8ac31dc2"
      ],
      "metadata": {
        "id": "BfJ8Xn6dsnw_"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "metadata": {
        "id": "jJYzUtZI0wwI",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "outputId": "b3db502a-6040-4f2d-9f0f-4d31d52cf720"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "     Moeda Mese  Forma de Distribuição                       Família  \\\n",
              "0     Real         Licença de Programa      On Premises - Technology   \n",
              "1     Real     12  Licença de Programa      On Premises - Technology   \n",
              "2     Real     12  Licença de Programa      On Premises - Technology   \n",
              "3     Real         Licença de Programa      On Premises - Technology   \n",
              "4     Real     12  Licença de Programa      On Premises - Technology   \n",
              "...    ...                         ...                           ...   \n",
              "6094  Real                      R$1.00  Outros serviços - Technology   \n",
              "6095  Real                      R$1.00  Outros serviços - Technology   \n",
              "6096  Real                      R$1.00  Outros serviços - Technology   \n",
              "6097  Real                      R$1.00  Outros serviços - Technology   \n",
              "6098  Real                      R$1.00  Outros serviços - Technology   \n",
              "\n",
              "                                 Contra Nome do Produto Unnamed: 4  \\\n",
              "0     7626  Oracle Database Enterprise Edition - Pro...        NaN   \n",
              "1     7626  Oracle Database Enterprise Edition - Pro...        NaN   \n",
              "2     7626  Oracle Database Enterprise Edition - Pro...        NaN   \n",
              "3     7626  Oracle Real Application Clusters - Proce...        NaN   \n",
              "4     7626  Oracle Real Application Clusters - Proce...        NaN   \n",
              "...                                                 ...        ...   \n",
              "6094  7626  Oracle Solution Support Center for PaaS ...        NaN   \n",
              "6095  7626  Oracle Solution Support Center for PaaS ...        NaN   \n",
              "6096  7626  Gen 2 Exadata Cloud at Customer Installa...        NaN   \n",
              "6097  7626  Oracle Cloud VMware Solution - OCPU Per ...        NaN   \n",
              "6098  7626  Oracle Cloud Infrastructure - Compute - ...        NaN   \n",
              "\n",
              "     Part Number Unnamed: 6                   Preço            Tipo de Licença  \n",
              "0         A90611        NaN              285.157,18  Permanente sem Manutenção  \n",
              "1        A90611M        NaN               61.538,41     Manutenção na Vigência  \n",
              "2        A90611P        NaN               11.795,04                    Suporte  \n",
              "3         A90619        NaN              138.075,47  Permanente sem Manutenção  \n",
              "4        A90619M        NaN               29.797,41     Manutenção na Vigência  \n",
              "...          ...        ...                     ...                        ...  \n",
              "6094      B90795        NaN   60.033,01  Assinatura                        NaN  \n",
              "6095      B90796        NaN              Assinatura                        NaN  \n",
              "6096      B91390        NaN  180.099,03  Assinatura                        NaN  \n",
              "6097      B92386        NaN    1,219237  Assinatura                        NaN  \n",
              "6098      B88318        NaN    0,552305  Assinatura                        NaN  \n",
              "\n",
              "[6099 rows x 9 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-a11a087f-4db4-431e-bcaf-53d239f0e5ad\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Moeda</th>\n",
              "      <th>Mese  Forma de Distribuição</th>\n",
              "      <th>Família</th>\n",
              "      <th>Contra Nome do Produto</th>\n",
              "      <th>Unnamed: 4</th>\n",
              "      <th>Part Number</th>\n",
              "      <th>Unnamed: 6</th>\n",
              "      <th>Preço</th>\n",
              "      <th>Tipo de Licença</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Real</td>\n",
              "      <td>Licença de Programa</td>\n",
              "      <td>On Premises - Technology</td>\n",
              "      <td>7626  Oracle Database Enterprise Edition - Pro...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>A90611</td>\n",
              "      <td>NaN</td>\n",
              "      <td>285.157,18</td>\n",
              "      <td>Permanente sem Manutenção</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Real</td>\n",
              "      <td>12  Licença de Programa</td>\n",
              "      <td>On Premises - Technology</td>\n",
              "      <td>7626  Oracle Database Enterprise Edition - Pro...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>A90611M</td>\n",
              "      <td>NaN</td>\n",
              "      <td>61.538,41</td>\n",
              "      <td>Manutenção na Vigência</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Real</td>\n",
              "      <td>12  Licença de Programa</td>\n",
              "      <td>On Premises - Technology</td>\n",
              "      <td>7626  Oracle Database Enterprise Edition - Pro...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>A90611P</td>\n",
              "      <td>NaN</td>\n",
              "      <td>11.795,04</td>\n",
              "      <td>Suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Real</td>\n",
              "      <td>Licença de Programa</td>\n",
              "      <td>On Premises - Technology</td>\n",
              "      <td>7626  Oracle Real Application Clusters - Proce...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>A90619</td>\n",
              "      <td>NaN</td>\n",
              "      <td>138.075,47</td>\n",
              "      <td>Permanente sem Manutenção</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Real</td>\n",
              "      <td>12  Licença de Programa</td>\n",
              "      <td>On Premises - Technology</td>\n",
              "      <td>7626  Oracle Real Application Clusters - Proce...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>A90619M</td>\n",
              "      <td>NaN</td>\n",
              "      <td>29.797,41</td>\n",
              "      <td>Manutenção na Vigência</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",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6094</th>\n",
              "      <td>Real</td>\n",
              "      <td>R$1.00</td>\n",
              "      <td>Outros serviços - Technology</td>\n",
              "      <td>7626  Oracle Solution Support Center for PaaS ...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>B90795</td>\n",
              "      <td>NaN</td>\n",
              "      <td>60.033,01  Assinatura</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6095</th>\n",
              "      <td>Real</td>\n",
              "      <td>R$1.00</td>\n",
              "      <td>Outros serviços - Technology</td>\n",
              "      <td>7626  Oracle Solution Support Center for PaaS ...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>B90796</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Assinatura</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6096</th>\n",
              "      <td>Real</td>\n",
              "      <td>R$1.00</td>\n",
              "      <td>Outros serviços - Technology</td>\n",
              "      <td>7626  Gen 2 Exadata Cloud at Customer Installa...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>B91390</td>\n",
              "      <td>NaN</td>\n",
              "      <td>180.099,03  Assinatura</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6097</th>\n",
              "      <td>Real</td>\n",
              "      <td>R$1.00</td>\n",
              "      <td>Outros serviços - Technology</td>\n",
              "      <td>7626  Oracle Cloud VMware Solution - OCPU Per ...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>B92386</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1,219237  Assinatura</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6098</th>\n",
              "      <td>Real</td>\n",
              "      <td>R$1.00</td>\n",
              "      <td>Outros serviços - Technology</td>\n",
              "      <td>7626  Oracle Cloud Infrastructure - Compute - ...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>B88318</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0,552305  Assinatura</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>6099 rows × 9 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-a11a087f-4db4-431e-bcaf-53d239f0e5ad')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-a11a087f-4db4-431e-bcaf-53d239f0e5ad button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-a11a087f-4db4-431e-bcaf-53d239f0e5ad');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ],
      "source": [
        "precosOracle"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "metadata": {
        "id": "O8Q1MMkI1lx4",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "outputId": "60674ca4-66ca-4870-f8cb-2a8dc476d184"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                       Preço            Tipo de Licença\n",
              "0                 285.157,18  Permanente sem Manutenção\n",
              "1                  61.538,41     Manutenção na Vigência\n",
              "2                  11.795,04                    Suporte\n",
              "3                 138.075,47  Permanente sem Manutenção\n",
              "4                  29.797,41     Manutenção na Vigência\n",
              "...                      ...                        ...\n",
              "6094   60.033,01  Assinatura                        NaN\n",
              "6095              Assinatura                        NaN\n",
              "6096  180.099,03  Assinatura                        NaN\n",
              "6097    1,219237  Assinatura                        NaN\n",
              "6098    0,552305  Assinatura                        NaN\n",
              "\n",
              "[6099 rows x 2 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-22623ca8-c9dd-4e0d-86f5-b95d23ce8c64\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Preço</th>\n",
              "      <th>Tipo de Licença</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>285.157,18</td>\n",
              "      <td>Permanente sem Manutenção</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>61.538,41</td>\n",
              "      <td>Manutenção na Vigência</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>11.795,04</td>\n",
              "      <td>Suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>138.075,47</td>\n",
              "      <td>Permanente sem Manutenção</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>29.797,41</td>\n",
              "      <td>Manutenção na Vigência</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6094</th>\n",
              "      <td>60.033,01  Assinatura</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6095</th>\n",
              "      <td>Assinatura</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6096</th>\n",
              "      <td>180.099,03  Assinatura</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6097</th>\n",
              "      <td>1,219237  Assinatura</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6098</th>\n",
              "      <td>0,552305  Assinatura</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>6099 rows × 2 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-22623ca8-c9dd-4e0d-86f5-b95d23ce8c64')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-22623ca8-c9dd-4e0d-86f5-b95d23ce8c64 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-22623ca8-c9dd-4e0d-86f5-b95d23ce8c64');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ],
      "source": [
        "# Aqui excluímos todas as outras colunas menos o preço e o tipo de licença, nos quais vamos\n",
        "#trabalhar a visualização dos dados\n",
        "precosDF = precosOracle.drop(columns = ['Moeda', 'Mese  Forma de Distribuição', 'Família', \n",
        "                     'Contra Nome do Produto', 'Unnamed: 4', 'Part Number',\n",
        "                     'Unnamed: 6'], axis = 1)\n",
        "precosDF"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Tratamento dos Dados"
      ],
      "metadata": {
        "id": "GUjrWl_CsXiZ"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zMUKOhTR-jkZ"
      },
      "source": [
        "Apenas os 17 primeiros itens da lista foram divididos corretamente, o resto, está com erro de dados concentrado na coluna de 'Preços'. Tendo feito a divisão dessa coluna, temos um objeto onde na mesma linha, encontramos um Pandas Series, com o preço na posição [0], e o tipo de licença na posição [1] (após a divisão da string com a função do Python), os dois únicos itens úteis para essa análise."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "metadata": {
        "id": "vDzjePLM37th",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "outputId": "7191a566-63ae-4765-a4b9-a46a78c4c3df"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "               0            1\n",
              "0     285.157,18         None\n",
              "1      61.538,41         None\n",
              "2      11.795,04         None\n",
              "3     138.075,47         None\n",
              "4      29.797,41         None\n",
              "...          ...          ...\n",
              "6094   60.033,01   Assinatura\n",
              "6095  Assinatura         None\n",
              "6096  180.099,03   Assinatura\n",
              "6097    1,219237   Assinatura\n",
              "6098    0,552305   Assinatura\n",
              "\n",
              "[6099 rows x 2 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-e7ba8bea-dff7-420e-a300-dd158bd0bb83\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>0</th>\n",
              "      <th>1</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>285.157,18</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>61.538,41</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>11.795,04</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>138.075,47</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>29.797,41</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6094</th>\n",
              "      <td>60.033,01</td>\n",
              "      <td>Assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6095</th>\n",
              "      <td>Assinatura</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6096</th>\n",
              "      <td>180.099,03</td>\n",
              "      <td>Assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6097</th>\n",
              "      <td>1,219237</td>\n",
              "      <td>Assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6098</th>\n",
              "      <td>0,552305</td>\n",
              "      <td>Assinatura</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>6099 rows × 2 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-e7ba8bea-dff7-420e-a300-dd158bd0bb83')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-e7ba8bea-dff7-420e-a300-dd158bd0bb83 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-e7ba8bea-dff7-420e-a300-dd158bd0bb83');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ],
      "source": [
        "# A maior parte dos itens estão com duas colunas juntas em uma, na coluna de preço, que separamos nesse\n",
        "#bloco, mas ainda precisamos remanejar os tipos de licença para sua própria coluna, com o parâmetro expand=True\n",
        "\n",
        "precoSplit = precosDF['Preço'].str.split(\" \", 1, expand=True)\n",
        "precoSplit"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dL3FegOnYYSC"
      },
      "source": [
        "### 6099 originais\n",
        "-----\n",
        "4464 restantes\n",
        "\n",
        "O que equivale a 73,19% sendo usado na análise comparada ao original, levando em consideração os seguintes fatos: A tabela original estava no formato PDF.  Existem outras ferramentas como a biblioteca Camelot e Tabula, que poderiam ajudar com esse procedimento, mas que ainda requeriram um nível de parametrização que eu não pude trazer para esse projeto."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {
        "id": "xbIySpucDm3a"
      },
      "outputs": [],
      "source": [
        "#Realizando o tratamento do dataset, excluindo linhas vazias e renomeando as colunas\n",
        "precoClean = precoSplit.dropna()\n",
        "precoClean = precoClean.reset_index()\n",
        "precoClean = precoClean.drop(columns=['index'])\n",
        "precoClean.columns = ['preco', 'tipo_licenca']"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Convertendo (a hilária) coluna de preços de string para float. A operação desse bloco de código foi feito\n",
        "#inplace, ou seja, sem gerar uma nova variável, então esse bloco poderá ser executado uma única vez, ou\n",
        "#com a criação de uma nova variável e DataFrame.\n",
        "\n",
        "# Esse passo foi necessário por que a função .split() do Python sempre retorna 2 ou mais strings\n",
        "precoClean['preco'] = [x.replace('.', '') for x in precoClean['preco']]\n",
        "precoClean['preco'] = precoClean['preco'].apply(lambda x: float(x.split()[0].replace(',', '.')))\n",
        "precoClean"
      ],
      "metadata": {
        "id": "IIcvhrHIsBiQ",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "outputId": "e5dede14-57c4-476b-e7e2-59a515b9e504"
      },
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "            preco tipo_licenca\n",
              "0     R$61,538.41   manutencao\n",
              "1     R$11,795.04      suporte\n",
              "2     R$34,819.78   permanente\n",
              "3      R$7,514.29   manutencao\n",
              "4      R$1,440.26      suporte\n",
              "...           ...          ...\n",
              "4459   R$7,504.13   assinatura\n",
              "4460  R$60,033.01   assinatura\n",
              "4461 R$180,099.03   assinatura\n",
              "4462       R$1.22   assinatura\n",
              "4463       R$0.55   assinatura\n",
              "\n",
              "[4464 rows x 2 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-19a55ce7-6d5c-49e7-834b-8cc158d96ced\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>preco</th>\n",
              "      <th>tipo_licenca</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>R$61,538.41</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>R$11,795.04</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>R$34,819.78</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>R$7,514.29</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>R$1,440.26</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4459</th>\n",
              "      <td>R$7,504.13</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4460</th>\n",
              "      <td>R$60,033.01</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4461</th>\n",
              "      <td>R$180,099.03</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4462</th>\n",
              "      <td>R$1.22</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4463</th>\n",
              "      <td>R$0.55</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>4464 rows × 2 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-19a55ce7-6d5c-49e7-834b-8cc158d96ced')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-19a55ce7-6d5c-49e7-834b-8cc158d96ced button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-19a55ce7-6d5c-49e7-834b-8cc158d96ced');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Fazendo o mesmo para os tipos de licença, retirando os espaços em branco das variáveis\n",
        "precoClean['tipo_licenca'] = [x.replace('Manutenção na Vigência', 'manutencao') for x in precoClean['tipo_licenca']]\n",
        "precoClean['tipo_licenca'] = [x.replace('Permanente sem Manutenção', 'permanente') for x in precoClean['tipo_licenca']]\n",
        "precoClean['tipo_licenca'] = [x.replace('Suporte', 'suporte') for x in precoClean['tipo_licenca']]\n",
        "precoClean['tipo_licenca'] = [x.replace('Assinatura', 'assinatura') for x in precoClean['tipo_licenca']]\n",
        "\n",
        "# Até eu descobrir que não havia removido o espaço em branco, apenas reduzido a label dos tipos de licença\n",
        "# Mais sobre isso abaixo "
      ],
      "metadata": {
        "id": "1dUKWGiiyHwY"
      },
      "execution_count": 20,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Lição de formatação de Dataframes\n",
        "Antes de começar a realizar consultas ou sei lá o que mais no conjunto de dados no qual estiver trabalhando, certifique-se de que os mesmos estão prontos para ser consultados, como, saiba em qual tipo estão os dados, e se eles tem algum artefato escondido no seu texto, como whitespace que pode atrapalhar suas consultas.\n",
        "\n",
        "Referência:\n",
        "https://www.roelpeters.be/how-to-remove-trailing-whitespaces-from-column-headers-in-pandas/"
      ],
      "metadata": {
        "id": "cfJ-Hrd4QeRq"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Uma compreensão de lista do DF (único ao Python) utilizado para encontrar qualquer \n",
        "#linha na coluna 'tipo_licenca' que possa ter whitespace no início ou no final da string \n",
        "[x for x in precoClean['tipo_licenca'] if x.endswith(' ') or x.startswith(' ')]"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ykuaf02XOfca",
        "outputId": "b82051fc-75d5-4a01-e737-e9753bfcc7c8"
      },
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# E a função para limpar as strings dessa coluna\n",
        "precoClean['tipo_licenca'] = precoClean['tipo_licenca'].str.strip()"
      ],
      "metadata": {
        "id": "lorehyMn0XBA"
      },
      "execution_count": 22,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "precoClean"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "id": "Muz2gH2Vrmoh",
        "outputId": "b93bcae3-ff6f-4454-c1a3-f91e602dd674"
      },
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "            preco tipo_licenca\n",
              "0     R$61,538.41   manutencao\n",
              "1     R$11,795.04      suporte\n",
              "2     R$34,819.78   permanente\n",
              "3      R$7,514.29   manutencao\n",
              "4      R$1,440.26      suporte\n",
              "...           ...          ...\n",
              "4459   R$7,504.13   assinatura\n",
              "4460  R$60,033.01   assinatura\n",
              "4461 R$180,099.03   assinatura\n",
              "4462       R$1.22   assinatura\n",
              "4463       R$0.55   assinatura\n",
              "\n",
              "[4464 rows x 2 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-04552d53-ff74-4a2f-b903-f19dd4e1dbd7\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>preco</th>\n",
              "      <th>tipo_licenca</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>R$61,538.41</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>R$11,795.04</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>R$34,819.78</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>R$7,514.29</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>R$1,440.26</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4459</th>\n",
              "      <td>R$7,504.13</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4460</th>\n",
              "      <td>R$60,033.01</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4461</th>\n",
              "      <td>R$180,099.03</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4462</th>\n",
              "      <td>R$1.22</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4463</th>\n",
              "      <td>R$0.55</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>4464 rows × 2 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-04552d53-ff74-4a2f-b903-f19dd4e1dbd7')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-04552d53-ff74-4a2f-b903-f19dd4e1dbd7 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-04552d53-ff74-4a2f-b903-f19dd4e1dbd7');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P1S_3PTFaKsz"
      },
      "source": [
        "## Exploração dos dados com SQL\n",
        "Todas as consultas feitas nesse notebook são realizadas através de SQL com o uso da biblioteca pandasql, que traz a sintaxe da linguagem de query para o Python.\n",
        "\n",
        "Referência:\n",
        "https://pypi.org/project/pandasql/\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XjJY9VPQZgWj"
      },
      "source": [
        "### Dados gerais do conjunto de dados\n",
        "A partir daqui, faremos a exploração do DataFrame com SQL, construindo tabelas de acordo com cada Tipo de Licença que podemos encontrar: \n",
        "\n",
        "1.   Permanente sem Manutenção\n",
        "2.   Manutenção na Vigência\n",
        "3.   Suporte\n",
        "4.   Assinatura"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 24,
      "metadata": {
        "id": "61u01d9lHsOg",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 300
        },
        "outputId": "08e802f2-bc66-40b9-ec4c-be87fd844d1a"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                preco\n",
              "count      R$4,464.00\n",
              "mean      R$53,498.91\n",
              "std      R$250,675.20\n",
              "min            R$0.00\n",
              "25%          R$126.65\n",
              "50%        R$1,339.40\n",
              "75%       R$20,715.36\n",
              "max   R$10,721,895.73"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-b056bedb-f2e4-42ec-8faf-59cbfdce01f0\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>preco</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>count</th>\n",
              "      <td>R$4,464.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>mean</th>\n",
              "      <td>R$53,498.91</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>std</th>\n",
              "      <td>R$250,675.20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>min</th>\n",
              "      <td>R$0.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25%</th>\n",
              "      <td>R$126.65</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>50%</th>\n",
              "      <td>R$1,339.40</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>75%</th>\n",
              "      <td>R$20,715.36</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>max</th>\n",
              "      <td>R$10,721,895.73</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-b056bedb-f2e4-42ec-8faf-59cbfdce01f0')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-b056bedb-f2e4-42ec-8faf-59cbfdce01f0 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-b056bedb-f2e4-42ec-8faf-59cbfdce01f0');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 24
        }
      ],
      "source": [
        "# describe() é uma função do pandas que pode rapidamente demonstrar todas as características\n",
        "#estatísticas do DataFrame selecionado. Observar que a contagem dessa função, por ser float\n",
        "#também foi formatada como um preço\n",
        "precoClean.describe()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 25,
      "metadata": {
        "id": "b7dulq5IFKAb"
      },
      "outputs": [],
      "source": [
        "# É com essa função anônima que vamos realizar os queries em SQL em cima do Dataframe, para não ter que escrever a função completa toda vez que for chamada \n",
        "pysqldf = lambda q: sqldf(q, globals())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 26,
      "metadata": {
        "id": "g8zwtBC3aN7a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 175
        },
        "outputId": "3d2d14d7-7fda-4f75-d248-9a24d3045a18"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  tipo_licenca  Total\n",
              "0   assinatura    849\n",
              "1   manutencao   1206\n",
              "2   permanente   1204\n",
              "3      suporte   1205"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-e2622a47-cbac-47bc-9cfe-03814e42329e\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>tipo_licenca</th>\n",
              "      <th>Total</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>assinatura</td>\n",
              "      <td>849</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>manutencao</td>\n",
              "      <td>1206</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>permanente</td>\n",
              "      <td>1204</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>suporte</td>\n",
              "      <td>1205</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-e2622a47-cbac-47bc-9cfe-03814e42329e')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-e2622a47-cbac-47bc-9cfe-03814e42329e button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-e2622a47-cbac-47bc-9cfe-03814e42329e');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 26
        }
      ],
      "source": [
        "# Consultando quantos itens temos para cada tipo de licença\n",
        "q = \"\"\"\n",
        "    SELECT tipo_licenca, COUNT(*) as Total\n",
        "    FROM precoClean\n",
        "    GROUP BY tipo_licenca\n",
        "    HAVING COUNT(*)\n",
        "    \"\"\"\n",
        "\n",
        "precoTotalItem = pysqldf(q)\n",
        "precoTotalItem"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 27,
      "metadata": {
        "id": "-rmzw7kZmD3c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 175
        },
        "outputId": "41d249bd-784f-472f-95f6-5c2862effcf2"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  tipo_licenca            Total\n",
              "0   assinatura  R$65,810,203.68\n",
              "1   manutencao  R$29,792,000.36\n",
              "2   permanente R$137,409,785.39\n",
              "3      suporte   R$5,807,166.02"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-d2235570-0663-4aec-8bd3-e71145caec79\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>tipo_licenca</th>\n",
              "      <th>Total</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>assinatura</td>\n",
              "      <td>R$65,810,203.68</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>manutencao</td>\n",
              "      <td>R$29,792,000.36</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>permanente</td>\n",
              "      <td>R$137,409,785.39</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>suporte</td>\n",
              "      <td>R$5,807,166.02</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d2235570-0663-4aec-8bd3-e71145caec79')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-d2235570-0663-4aec-8bd3-e71145caec79 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-d2235570-0663-4aec-8bd3-e71145caec79');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 27
        }
      ],
      "source": [
        "# Consultando qual o valor total para cada tipo de licença\n",
        "q = \"\"\"\n",
        "    SELECT tipo_licenca, SUM(preco) as Total\n",
        "    FROM precoClean\n",
        "    GROUP BY tipo_licenca\n",
        "    \"\"\"\n",
        "\n",
        "precoTotalValor = pysqldf(q)\n",
        "precoTotalValor"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Aqui observamos a média para cada tipo de licença\n",
        "precoMediaA = precoTotalValor['Total'][0] / precoTotalItem['Total'][0]\n",
        "precoMediaM = precoTotalValor['Total'][1] / precoTotalItem['Total'][1]\n",
        "precoMediaP = precoTotalValor['Total'][2] / precoTotalItem['Total'][2]\n",
        "precoMediaS = precoTotalValor['Total'][3] / precoTotalItem['Total'][3]\n",
        "\n",
        "print('Preço médio das licenças permanentes: R$%.3f' % (precoMediaP))\n",
        "print('Preço médio das licenças de manutenção: R$%.3f' % (precoMediaM))\n",
        "print('Preço médio das licenças de suporte: R$%.3f' % (precoMediaS))\n",
        "print('Preço médio das licenças assinaturas: R$%.3f' % (precoMediaA))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1_ch5PROFW0-",
        "outputId": "32246d78-6199-4986-86f3-66d7bdf91369"
      },
      "execution_count": 43,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Preço médio das licenças permanentes: R$114127.729\n",
            "Preço médio das licenças de manutenção: R$24703.151\n",
            "Preço médio das licenças de suporte: R$4819.225\n",
            "Preço médio das licenças assinaturas: R$77514.963\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Aqui realizamos a consulta para separarmos todas as licenças desse tipo específico, o que repetiremos para todos os outros tipos de licença. E para cada tipo de licença, selecionamos os 7 maiores e menores itens da lista de acordo com seu preço, e realizamos uma última consulta no final, com amostras aleatórias para cada tipo de licença, realizando assim a tão esperada exploração dos preços das licenças da Oracle."
      ],
      "metadata": {
        "id": "JLdszTVAljVo"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Tipo de licença Permanente sem Manutenção"
      ],
      "metadata": {
        "id": "8DO4A08RSLtZ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "q = \"\"\"\n",
        "    SELECT preco, tipo_licenca FROM precoClean\n",
        "    WHERE tipo_licenca = 'permanente'\n",
        "    \"\"\"\n",
        "\n",
        "precoPermanente = pysqldf(q)\n",
        "precoPermanente.sort_values(by=['preco'], inplace=True)\n",
        "precoPermanente.reset_index(inplace=True)\n",
        "precoPermanente.drop(columns=['index'], inplace=True)\n",
        "precoPermanente"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "id": "mFRTlgWPP-sa",
        "outputId": "8750cec3-4cfd-40c6-d442-375f6f8189cf"
      },
      "execution_count": 91,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "              preco tipo_licenca\n",
              "0            R$1.39   permanente\n",
              "1            R$2.10   permanente\n",
              "2            R$3.00   permanente\n",
              "3            R$4.21   permanente\n",
              "4            R$6.00   permanente\n",
              "...             ...          ...\n",
              "1199 R$2,941,617.53   permanente\n",
              "1200 R$2,941,617.53   permanente\n",
              "1201 R$3,001,650.54   permanente\n",
              "1202 R$3,451,898.88   permanente\n",
              "1203 R$3,451,898.88   permanente\n",
              "\n",
              "[1204 rows x 2 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-9ef965f2-579f-4d7f-8186-c39eb60bea5d\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>preco</th>\n",
              "      <th>tipo_licenca</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>R$1.39</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>R$2.10</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>R$3.00</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>R$4.21</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>R$6.00</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1199</th>\n",
              "      <td>R$2,941,617.53</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1200</th>\n",
              "      <td>R$2,941,617.53</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1201</th>\n",
              "      <td>R$3,001,650.54</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1202</th>\n",
              "      <td>R$3,451,898.88</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1203</th>\n",
              "      <td>R$3,451,898.88</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1204 rows × 2 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-9ef965f2-579f-4d7f-8186-c39eb60bea5d')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-9ef965f2-579f-4d7f-8186-c39eb60bea5d button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-9ef965f2-579f-4d7f-8186-c39eb60bea5d');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 91
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "precoPH = precoPermanente['preco'].head(7)\n",
        "precoPT = precoPermanente['preco'].tail(7)\n",
        "dados = zip(precoPH, precoPT)\n",
        "\n",
        "tabelaPermanente = pd.DataFrame(dados, columns=['bot7', 'top7'])\n",
        "tabelaPermanente"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 269
        },
        "id": "J6yFjsXGNu9t",
        "outputId": "7fced95f-e1c5-4949-8c10-3146cfd8e346"
      },
      "execution_count": 92,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "    bot7           top7\n",
              "0 R$1.39 R$2,491,370.70\n",
              "1 R$2.10 R$2,761,518.50\n",
              "2 R$3.00 R$2,941,617.53\n",
              "3 R$4.21 R$2,941,617.53\n",
              "4 R$6.00 R$3,001,650.54\n",
              "5 R$6.00 R$3,451,898.88\n",
              "6 R$6.00 R$3,451,898.88"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-5eea8905-ddc7-44d1-8c0b-5754abe8960e\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>bot7</th>\n",
              "      <th>top7</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>R$1.39</td>\n",
              "      <td>R$2,491,370.70</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>R$2.10</td>\n",
              "      <td>R$2,761,518.50</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>R$3.00</td>\n",
              "      <td>R$2,941,617.53</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>R$4.21</td>\n",
              "      <td>R$2,941,617.53</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>R$6.00</td>\n",
              "      <td>R$3,001,650.54</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>R$6.00</td>\n",
              "      <td>R$3,451,898.88</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>R$6.00</td>\n",
              "      <td>R$3,451,898.88</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-5eea8905-ddc7-44d1-8c0b-5754abe8960e')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-5eea8905-ddc7-44d1-8c0b-5754abe8960e button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-5eea8905-ddc7-44d1-8c0b-5754abe8960e');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 92
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "precoPermanente.sample(10, random_state=0)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "id": "QhSRA8hZlMR5",
        "outputId": "cd983ae5-1f45-49b8-8ebe-3a8eb3eb430d"
      },
      "execution_count": 167,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "            preco tipo_licenca\n",
              "486    R$2,101.10   permanente\n",
              "5          R$6.00   permanente\n",
              "438    R$1,800.95   permanente\n",
              "271      R$840.14   permanente\n",
              "966  R$138,075.47   permanente\n",
              "783   R$17,409.14   permanente\n",
              "268      R$751.15   permanente\n",
              "395    R$1,470.62   permanente\n",
              "478    R$2,101.10   permanente\n",
              "1012 R$210,116.29   permanente"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-610e1451-b73b-4ac8-9729-8d7ed3dc5f04\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>preco</th>\n",
              "      <th>tipo_licenca</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>486</th>\n",
              "      <td>R$2,101.10</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>R$6.00</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>438</th>\n",
              "      <td>R$1,800.95</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>271</th>\n",
              "      <td>R$840.14</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>966</th>\n",
              "      <td>R$138,075.47</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>783</th>\n",
              "      <td>R$17,409.14</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>268</th>\n",
              "      <td>R$751.15</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>395</th>\n",
              "      <td>R$1,470.62</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>478</th>\n",
              "      <td>R$2,101.10</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1012</th>\n",
              "      <td>R$210,116.29</td>\n",
              "      <td>permanente</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-610e1451-b73b-4ac8-9729-8d7ed3dc5f04')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-610e1451-b73b-4ac8-9729-8d7ed3dc5f04 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-610e1451-b73b-4ac8-9729-8d7ed3dc5f04');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 167
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Tipo de licença Manutenção na Vigência"
      ],
      "metadata": {
        "id": "_TKyHVwOSQO8"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "q = \"\"\"\n",
        "    SELECT preco, tipo_licenca FROM precoClean\n",
        "    WHERE tipo_licenca = 'manutencao'\n",
        "    \"\"\"\n",
        "\n",
        "precoManutencao = pysqldf(q)\n",
        "precoManutencao.sort_values(by=['preco'], inplace=True)\n",
        "precoManutencao.reset_index(inplace=True)\n",
        "precoManutencao.drop(columns=['index'], inplace=True)\n",
        "precoManutencao"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "id": "NcvMZuMcQMcR",
        "outputId": "e68b3f95-e5a1-4785-9627-95ce3293dd57"
      },
      "execution_count": 168,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "            preco tipo_licenca\n",
              "0          R$0.30   manutencao\n",
              "1          R$0.45   manutencao\n",
              "2          R$0.65   manutencao\n",
              "3          R$0.91   manutencao\n",
              "4          R$1.30   manutencao\n",
              "...           ...          ...\n",
              "1201 R$634,816.49   manutencao\n",
              "1202 R$634,816.49   manutencao\n",
              "1203 R$647,771.93   manutencao\n",
              "1204 R$744,937.88   manutencao\n",
              "1205 R$744,937.88   manutencao\n",
              "\n",
              "[1206 rows x 2 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-c83189c5-79ff-4aa4-a696-e06e730e30d6\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>preco</th>\n",
              "      <th>tipo_licenca</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>R$0.30</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>R$0.45</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>R$0.65</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>R$0.91</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>R$1.30</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1201</th>\n",
              "      <td>R$634,816.49</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1202</th>\n",
              "      <td>R$634,816.49</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1203</th>\n",
              "      <td>R$647,771.93</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1204</th>\n",
              "      <td>R$744,937.88</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1205</th>\n",
              "      <td>R$744,937.88</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1206 rows × 2 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-c83189c5-79ff-4aa4-a696-e06e730e30d6')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-c83189c5-79ff-4aa4-a696-e06e730e30d6 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-c83189c5-79ff-4aa4-a696-e06e730e30d6');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 168
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "precoMH = precoManutencao['preco'].head(7)\n",
        "precoMT = precoManutencao['preco'].tail(7)\n",
        "dados = zip(precoMH, precoMT)\n",
        "\n",
        "tabelaManutencao = pd.DataFrame(dados, columns=['bot7', 'top7'])\n",
        "tabelaManutencao"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 269
        },
        "id": "-PFJgYJNWGVL",
        "outputId": "e1fb1b47-629d-4294-d0d9-9580b1c9d644"
      },
      "execution_count": 169,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "    bot7         top7\n",
              "0 R$0.30 R$537,650.86\n",
              "1 R$0.45 R$595,950.17\n",
              "2 R$0.65 R$634,816.49\n",
              "3 R$0.91 R$634,816.49\n",
              "4 R$1.30 R$647,771.93\n",
              "5 R$1.30 R$744,937.88\n",
              "6 R$1.30 R$744,937.88"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-3cd378d5-df6c-439e-9381-7f4dd40d2c6f\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>bot7</th>\n",
              "      <th>top7</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>R$0.30</td>\n",
              "      <td>R$537,650.86</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>R$0.45</td>\n",
              "      <td>R$595,950.17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>R$0.65</td>\n",
              "      <td>R$634,816.49</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>R$0.91</td>\n",
              "      <td>R$634,816.49</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>R$1.30</td>\n",
              "      <td>R$647,771.93</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>R$1.30</td>\n",
              "      <td>R$744,937.88</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>R$1.30</td>\n",
              "      <td>R$744,937.88</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-3cd378d5-df6c-439e-9381-7f4dd40d2c6f')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-3cd378d5-df6c-439e-9381-7f4dd40d2c6f button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-3cd378d5-df6c-439e-9381-7f4dd40d2c6f');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 169
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "precoManutencao.sample(10, random_state=0)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "id": "ZyR2yMF9lVBX",
        "outputId": "b8cfa4e9-ca34-4f14-e364-f1995e887e40"
      },
      "execution_count": 170,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "           preco tipo_licenca\n",
              "395     R$323.88   manutencao\n",
              "5         R$1.30   manutencao\n",
              "141      R$71.29   manutencao\n",
              "452     R$414.69   manutencao\n",
              "52        R$1.30   manutencao\n",
              "1085 R$59,595.15   manutencao\n",
              "328     R$259.10   manutencao\n",
              "890  R$15,546.46   manutencao\n",
              "319     R$239.57   manutencao\n",
              "269     R$162.10   manutencao"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-f40cacc4-044c-44f4-95e2-f5cd4ba71b2c\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>preco</th>\n",
              "      <th>tipo_licenca</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>395</th>\n",
              "      <td>R$323.88</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>R$1.30</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>141</th>\n",
              "      <td>R$71.29</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>452</th>\n",
              "      <td>R$414.69</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>52</th>\n",
              "      <td>R$1.30</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1085</th>\n",
              "      <td>R$59,595.15</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>328</th>\n",
              "      <td>R$259.10</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>890</th>\n",
              "      <td>R$15,546.46</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>319</th>\n",
              "      <td>R$239.57</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>269</th>\n",
              "      <td>R$162.10</td>\n",
              "      <td>manutencao</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-f40cacc4-044c-44f4-95e2-f5cd4ba71b2c')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-f40cacc4-044c-44f4-95e2-f5cd4ba71b2c button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-f40cacc4-044c-44f4-95e2-f5cd4ba71b2c');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 170
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Tipo de licença Suporte"
      ],
      "metadata": {
        "id": "F48ZT4HeSZqZ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "q = \"\"\"\n",
        "    SELECT preco, tipo_licenca FROM precoClean\n",
        "    WHERE tipo_licenca = 'suporte'\n",
        "    \"\"\"\n",
        "\n",
        "precoSuporte = pysqldf(q)\n",
        "precoSuporte.sort_values(by=['preco'], inplace=True)\n",
        "precoSuporte.reset_index(inplace=True)\n",
        "precoSuporte.drop(columns=['index'], inplace=True)\n",
        "precoSuporte"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "id": "S4AFVtC8QSMR",
        "outputId": "6f9c0c29-c095-476d-ab3e-fa37cb9f5505"
      },
      "execution_count": 171,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "            preco tipo_licenca\n",
              "0          R$0.06      suporte\n",
              "1          R$0.09      suporte\n",
              "2          R$0.12      suporte\n",
              "3          R$0.17      suporte\n",
              "4          R$0.25      suporte\n",
              "...           ...          ...\n",
              "1200 R$121,675.00      suporte\n",
              "1201 R$121,675.00      suporte\n",
              "1202 R$124,158.16      suporte\n",
              "1203 R$142,781.91      suporte\n",
              "1204 R$142,781.91      suporte\n",
              "\n",
              "[1205 rows x 2 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-d5f8fdf1-b27d-439a-b7ad-21656639de25\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>preco</th>\n",
              "      <th>tipo_licenca</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>R$0.06</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>R$0.09</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>R$0.12</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>R$0.17</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>R$0.25</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1200</th>\n",
              "      <td>R$121,675.00</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1201</th>\n",
              "      <td>R$121,675.00</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1202</th>\n",
              "      <td>R$124,158.16</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1203</th>\n",
              "      <td>R$142,781.91</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1204</th>\n",
              "      <td>R$142,781.91</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1205 rows × 2 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d5f8fdf1-b27d-439a-b7ad-21656639de25')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-d5f8fdf1-b27d-439a-b7ad-21656639de25 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-d5f8fdf1-b27d-439a-b7ad-21656639de25');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 171
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "precoSH = precoSuporte['preco'].head(7)\n",
        "precoST = precoSuporte['preco'].tail(7)\n",
        "dados = zip(precoSH, precoST)\n",
        "\n",
        "tabelaSuporte = pd.DataFrame(dados, columns=['bot7', 'top7'])\n",
        "tabelaSuporte"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 269
        },
        "id": "cRstCkLlXT6T",
        "outputId": "9a47ff0d-db59-4361-8788-74964a89bbc1"
      },
      "execution_count": 172,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "    bot7         top7\n",
              "0 R$0.06 R$103,051.30\n",
              "1 R$0.09 R$114,225.51\n",
              "2 R$0.12 R$121,675.00\n",
              "3 R$0.17 R$121,675.00\n",
              "4 R$0.25 R$124,158.16\n",
              "5 R$0.25 R$142,781.91\n",
              "6 R$0.25 R$142,781.91"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-bf6bd59a-39eb-4405-9678-4313e198a05c\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>bot7</th>\n",
              "      <th>top7</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>R$0.06</td>\n",
              "      <td>R$103,051.30</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>R$0.09</td>\n",
              "      <td>R$114,225.51</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>R$0.12</td>\n",
              "      <td>R$121,675.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>R$0.17</td>\n",
              "      <td>R$121,675.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>R$0.25</td>\n",
              "      <td>R$124,158.16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>R$0.25</td>\n",
              "      <td>R$142,781.91</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>R$0.25</td>\n",
              "      <td>R$142,781.91</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-bf6bd59a-39eb-4405-9678-4313e198a05c')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-bf6bd59a-39eb-4405-9678-4313e198a05c button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-bf6bd59a-39eb-4405-9678-4313e198a05c');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 172
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "precoSuporte.sample(10, random_state=2)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "id": "iRLW6vM0lW-3",
        "outputId": "361d9e2a-0e61-4224-c876-b88988e2c42c"
      },
      "execution_count": 178,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "           preco tipo_licenca\n",
              "680     R$285.56      suporte\n",
              "615     R$142.81      suporte\n",
              "61        R$0.25      suporte\n",
              "824   R$1,141.04      suporte\n",
              "68        R$0.25      suporte\n",
              "922   R$4,345.55      suporte\n",
              "1096 R$13,036.59      suporte\n",
              "780     R$694.02      suporte\n",
              "888   R$2,855.63      suporte\n",
              "692     R$285.56      suporte"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-806c9f77-c4be-4368-9a38-f595c5ae94fa\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>preco</th>\n",
              "      <th>tipo_licenca</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>680</th>\n",
              "      <td>R$285.56</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>615</th>\n",
              "      <td>R$142.81</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>61</th>\n",
              "      <td>R$0.25</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>824</th>\n",
              "      <td>R$1,141.04</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>68</th>\n",
              "      <td>R$0.25</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>922</th>\n",
              "      <td>R$4,345.55</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1096</th>\n",
              "      <td>R$13,036.59</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>780</th>\n",
              "      <td>R$694.02</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>888</th>\n",
              "      <td>R$2,855.63</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>692</th>\n",
              "      <td>R$285.56</td>\n",
              "      <td>suporte</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-806c9f77-c4be-4368-9a38-f595c5ae94fa')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-806c9f77-c4be-4368-9a38-f595c5ae94fa button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-806c9f77-c4be-4368-9a38-f595c5ae94fa');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 178
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Tipo de licença Assinatura"
      ],
      "metadata": {
        "id": "k1xwXCGOShIK"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 174,
      "metadata": {
        "id": "qjf8HpVAuv2B",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "outputId": "107facaf-903a-4131-a4ac-b44f4b4ce76e"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "              preco tipo_licenca\n",
              "0            R$0.00   assinatura\n",
              "1            R$0.00   assinatura\n",
              "2            R$0.00   assinatura\n",
              "3            R$0.00   assinatura\n",
              "4            R$0.00   assinatura\n",
              "..              ...          ...\n",
              "844  R$1,440,792.26   assinatura\n",
              "845  R$1,560,858.28   assinatura\n",
              "846  R$1,680,924.30   assinatura\n",
              "847  R$1,800,990.32   assinatura\n",
              "848 R$10,721,895.73   assinatura\n",
              "\n",
              "[849 rows x 2 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-fff21198-0f7c-4bae-9e46-268694fbb889\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>preco</th>\n",
              "      <th>tipo_licenca</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>R$0.00</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>R$0.00</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>R$0.00</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>R$0.00</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>R$0.00</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>844</th>\n",
              "      <td>R$1,440,792.26</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>845</th>\n",
              "      <td>R$1,560,858.28</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>846</th>\n",
              "      <td>R$1,680,924.30</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>847</th>\n",
              "      <td>R$1,800,990.32</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>848</th>\n",
              "      <td>R$10,721,895.73</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>849 rows × 2 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-fff21198-0f7c-4bae-9e46-268694fbb889')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-fff21198-0f7c-4bae-9e46-268694fbb889 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-fff21198-0f7c-4bae-9e46-268694fbb889');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 174
        }
      ],
      "source": [
        "q = \"\"\"\n",
        "    SELECT preco, tipo_licenca FROM precoClean\n",
        "    WHERE tipo_licenca = 'assinatura'\n",
        "    \"\"\"\n",
        "\n",
        "precoAssinatura = pysqldf(q)\n",
        "precoAssinatura.sort_values(by=['preco'], inplace=True)\n",
        "precoAssinatura.reset_index(inplace=True)\n",
        "precoAssinatura.drop(columns=['index'], inplace=True)\n",
        "precoAssinatura"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Como nesse tipo de licença descobrimos que há itens que são de graça, os últimos\n",
        "# são todos zeros, mas considerando que mesmo assim não difere muito dos outros tipos de licença\n",
        "precoAH = precoAssinatura['preco'].head(7)\n",
        "precoAT = precoAssinatura['preco'].tail(7)\n",
        "dados = zip(precoAH, precoAT)\n",
        "\n",
        "tabelaAssinatura = pd.DataFrame(dados, columns=['bot7', 'top7'])\n",
        "tabelaAssinatura"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 269
        },
        "id": "bSQ8tfPIY_lS",
        "outputId": "9fc22abe-85b7-435b-81bd-7dc0652fee5d"
      },
      "execution_count": 175,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "    bot7            top7\n",
              "0 R$0.00  R$1,368,752.65\n",
              "1 R$0.00  R$1,404,772.45\n",
              "2 R$0.00  R$1,440,792.26\n",
              "3 R$0.00  R$1,560,858.28\n",
              "4 R$0.00  R$1,680,924.30\n",
              "5 R$0.00  R$1,800,990.32\n",
              "6 R$0.00 R$10,721,895.73"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-bdd55386-efc0-4a7c-a911-184dc2db7100\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>bot7</th>\n",
              "      <th>top7</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>R$0.00</td>\n",
              "      <td>R$1,368,752.65</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>R$0.00</td>\n",
              "      <td>R$1,404,772.45</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>R$0.00</td>\n",
              "      <td>R$1,440,792.26</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>R$0.00</td>\n",
              "      <td>R$1,560,858.28</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>R$0.00</td>\n",
              "      <td>R$1,680,924.30</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>R$0.00</td>\n",
              "      <td>R$1,800,990.32</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>R$0.00</td>\n",
              "      <td>R$10,721,895.73</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-bdd55386-efc0-4a7c-a911-184dc2db7100')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-bdd55386-efc0-4a7c-a911-184dc2db7100 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-bdd55386-efc0-4a7c-a911-184dc2db7100');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 175
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "precoAssinatura.sample(10, random_state=0)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "id": "nXBKg2Lilb5c",
        "outputId": "3a34a108-6df5-4478-ddfe-3ffd3a7bd1a4"
      },
      "execution_count": 176,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "          preco tipo_licenca\n",
              "214    R$150.08   assinatura\n",
              "293    R$600.33   assinatura\n",
              "241    R$240.13   assinatura\n",
              "193     R$86.45   assinatura\n",
              "162     R$30.02   assinatura\n",
              "55       R$0.40   assinatura\n",
              "175     R$42.48   assinatura\n",
              "247    R$300.17   assinatura\n",
              "460 R$12,006.60   assinatura\n",
              "392  R$3,001.65   assinatura"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-101dcf86-ef48-4c5d-8582-b2f5e8c105fb\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>preco</th>\n",
              "      <th>tipo_licenca</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>214</th>\n",
              "      <td>R$150.08</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>293</th>\n",
              "      <td>R$600.33</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>241</th>\n",
              "      <td>R$240.13</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>193</th>\n",
              "      <td>R$86.45</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>162</th>\n",
              "      <td>R$30.02</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>55</th>\n",
              "      <td>R$0.40</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>175</th>\n",
              "      <td>R$42.48</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>247</th>\n",
              "      <td>R$300.17</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>460</th>\n",
              "      <td>R$12,006.60</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>392</th>\n",
              "      <td>R$3,001.65</td>\n",
              "      <td>assinatura</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-101dcf86-ef48-4c5d-8582-b2f5e8c105fb')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-101dcf86-ef48-4c5d-8582-b2f5e8c105fb button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-101dcf86-ef48-4c5d-8582-b2f5e8c105fb');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 176
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Visualização de dados\n",
        "\n",
        "A ser planejado"
      ],
      "metadata": {
        "id": "enMb7RfPnHwv"
      }
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [],
      "name": "Exploração_de_Preços_ORACLE.ipynb",
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}