Sprint3.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyPhmYAIUaQhfkWGL16ni5Em",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/wabastos/Engenharia-de-Dados/blob/main/Sprint3.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Curso: Ciência de Dados e Analytics (PUC Rio)\n",
        "\n",
        "## Sprint: Engenharia de Dados\n",
        "\n",
        "## Professores:\n",
        "\n",
        "> **Profs. Dr. Sérgio Lifschitz,**\n",
        "\n",
        "> **Profa. Dra. Fernanda Baião,**\n",
        "\n",
        "> **Prof. Msc. Antony Seabra,**\n",
        "\n",
        "> **Prof. Dr. Marcos Villas,**\n",
        "\n",
        "> **Prof. Dr. Victor Almeida, e**\n",
        "\n",
        "> **Prof. M.Sc. Silvio Alonso**\n",
        "\n",
        "### Aluno:\n",
        "> **Wellington de Araújo Bastos, M.Sc.**\n",
        "\n",
        "\n",
        "---\n",
        "\n"
      ],
      "metadata": {
        "id": "Nlp7f62rxE7J"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "\n",
        "# TÍTULO: Análise da Qualidade de Serviço em Telecomunicações: Uma Abordagem de Engenharia de Dados na Nuvem para os Indicadores IQS do RQUAL\n",
        "---\n",
        "\n",
        "\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "ZWwXZ5obkaqW"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "<!-- Quebra de página -->\n",
        "<div style=\"page-break-after: always;\"></div>\n"
      ],
      "metadata": {
        "id": "G2KthGnknoBA"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# INTRODUÇÃO\n",
        "\n",
        "Este trabalho busca atender aos Requisitos para o Desenvolvimento do MVP referente ao Sprint 3 do Módulo Engenharia de Dados do curso de pós graduação da PUC-RIO, com ênfase deformação em Ciência de Dados e Analytics.\n",
        "\n",
        "\n",
        "\n",
        "O trabalho consiste em construir um pipeline de dados orientado as fases de busca, modelagem, carga e análise de dados.  "
      ],
      "metadata": {
        "id": "aocWcnZUvy0d"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 1. Objetivo Principal\n",
        "\n",
        "Construir um pipeline de análise de dados para os indicadores de qualidade, RQUAL da Anatel, definidos para os serviços de Telecomunicações, sobretudo para o serviço de telefonia móvel ou Serviço Móvel Pessoal (SMP).   \n",
        "\n",
        "Os indicadores e dados utilizados nesse trabalho podem ser encontrados no portal da Agência Nacional de Telecomunicações - ANATEL no link  Anatel — [Anatel — Agência Nacional de Telecomunicações](www.gov.br), e mais especificamente no link Anatel - [Indicadores - Telefonia Móvel](https://informacoes.anatel.gov.br/paineis/qualidade/indicadores-telefonia-movel).\n",
        "\n",
        "A definição dos indicadores podem ser encontradas em no documento Manual Operacional (MOP) e o Documento de Valores de Referência (DVR) disponibilizado através do Sobre o Sobre o [RQUAL — Agência Nacional de Telecomunicações](https://www.gov.br/anatel/pt-br/dados/qualidade/qualidade-dos-servicos/sobre-o-rqual), acessando o documento [MOP Versão 2.1](https://sistemas.anatel.gov.br/anexar-api/publico/anexos/download/d7ca2180dbad14e59e27176fa8b43517).\n",
        "\n",
        "Nesse trabalho focaremos nos Indicadores de Qualidade de Serviço - IQS, resumido no infografo com os principais conceitos/aspectos do RQUAL.\n",
        "\n",
        "![Figura 1](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/Infografo%20Anatel-Rqual.png)\n",
        "\n",
        "Figura 1 – Infografo com os principais conceitos do RQUAL - Anatel\n",
        "\n",
        "Construiremos um pipeline que reproduza as consultas dos usuários do site para os principais indicadores da telefonia móvel - IND1 até IND8, cujos significadores passaremos descrever:\n",
        "\n",
        "| Acrônimo             | Indicador | Serviço | (Anexo Nº)                                | Tipo               | Grupo |\n",
        "|------------------|-------|----------|------------------------------------------|------------------------|----------------------------------|\n",
        "| IND1             | Conexão de Chamadas na Rede de Acesso | SMP   | 01                                     | Indicador do IQS       | Redes            |\n",
        "| IND2             | Queda de Chamadas                    | SMP   | 01                                     | Indicador do IQS       | Redes            |\n",
        "| IND3             | Conexão de Dados Medida na Rede de Acesso | SMP   | 01                                     | Indicador do IQS       | Redes            |\n",
        "| IND4             | Cumprimento da Velocidade de download e upload | SMP e SCM | 05 (SMP) e 06 (SCM) | Indicador do IQS | Redes            |\n",
        "| IND5             | Latência bidirecional da Conexão de Dados | SMP e SCM | 05 (SMP) e 06 (SCM) | Indicador do IQS | Redes            |\n",
        "| IND6             | Variação de Latência da Conexão de Dados | SMP e SCM | 05 (SMP) e 06 (SCM) | Indicador do IQS | Redes            |\n",
        "| IND7             | Perda de Pacotes da Conexão de Dados | SMP e SCM | 05 (SMP) e 06 (SCM) | Indicador do IQS | Redes            |\n",
        "| IND8             | Disponibilidade | SMP, STFC, SCM e TV assinatura | 07 | Indicador do IQS | Redes            |\n",
        "\n",
        "Quadro 1 - Indicadores e suas descrições\n",
        "\n",
        "Uma amostra de uma consulta para os indicadores das principais operadoras de serviço da cidade do Rio de Janeiro-RJ pode ser visto na Figura 2.\n",
        "\n",
        "![Figura 2](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/Consulta%20IQS.png)\n",
        "\n",
        "Figura 2 - Figura 2 – Consulta aos indicadores IQS da cidade do Rio de Janeiro-RJ\n",
        "\n",
        "Questões a serem respondidas:\n",
        "\n",
        "\n",
        "\n",
        "1. É possível reproduzir as consultas dos indicadores  IQS do RQUAL, individualmente ou grupos, através de um pipeline de dados na nuvem selecionando atributos como ano, mês, indicador, cidade, estado e prestadora?, ou seja reproduzir a mesma consulta feita pelo site Anatel?\n",
        "\n",
        "1. É possível incrementar a essas consultas filtros que considerem o tamanho da população das cidades, quantidade de assinantes por operadora e renda percapita ou PIB?\n",
        "\n",
        "1. Fazer uma análise como:\n",
        "\n",
        "> a. melhores e piores IQS – por município\n",
        "\n",
        "> b. melhores e piores IQS – por prestadora\n",
        "\n",
        "> c. melhores e piores IQS – por população\n",
        "\n",
        "> d. melhores e piores IQS – por renda\n",
        "\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "2gBGhP44wrn0"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Restrições\n",
        "\n",
        "* Para efeito desse trabalho será considerado apenas o período de 01/2023 a 06/2023 e que atendam a tecnologia 4G.\n",
        "\n",
        "* Serão considerados apenas um estado por região do Brasil: Paraná, São Paulo, Pernambuco, Goiás, Belém e Distrito Federal\n"
      ],
      "metadata": {
        "id": "blmhsJ40DGiG"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "<!-- Quebra de página -->\n",
        "<div style=\"page-break-after: always;\"></div>\n"
      ],
      "metadata": {
        "id": "p2iNURaPnw9E"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 2. Base de Dados\n",
        "\n",
        "## 2.1 Dados Principais\n",
        "\n",
        "Os dados do IQS – RQUAL podem ser obtidos no link Dados Abertos do site da Anatel na opção Home>Paineis de Dados>Qualidade na opção Indicadores – Telefonia Móvel [Anatel - Indicadores - Telefonia Móvel](https://informacoes.anatel.gov.br/paineis/qualidade/indicadores-telefonia-movel). Nessa página, através dos seletores de filtro o usuário pode realizar as opções de filtros para os indicadores IND1 a IND8, podendo optar por baixar os dados de cada indicador filtrado, ou realizar o download da base completa na seleção [Dados Abretos > Indicadores de Qualidade](https://www.anatel.gov.br/dadosabertos/paineis_de_dados/qualidade/indicadores_rqual.zip).\n",
        "\n",
        "![Figura 3](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/Dados%20Abertos.png)\n",
        "\n",
        "Figura 3 - Dados Abertos para Download\n",
        "\n",
        "## 2.2 Dados Complementares\n",
        "\n",
        "Para podermos atender as questões dois e três do trabalho proposto serão coletados dados complementares no próprio site da Anatel que agregam informações de Dados de Cobertura do serviço por operadora, e dados de velocidade de navegação de dados por renda.  \n",
        "\n",
        "\n",
        "\n",
        "Os dados podem ser coletados nos links abaixo:\n",
        "\n",
        "\n",
        "1. [Velocidade x Renda](https://www.anatel.gov.br/dadosabertos/paineis_de_dados/infraestrutura/renda_velocidade_smp.zip)\n",
        "\n",
        "1. [Dados de Cobertura do Município](https://informacoes.anatel.gov.br/paineis/infraestrutura)\n",
        "\n",
        "1. [Dados de Cobertura por Operadora](https://informacoes.anatel.gov.br/paineis/infraestrutura)\n",
        "\n",
        "\n",
        "## Restrições\n",
        "\n",
        "* Todos os dados utilizados nesse trabalho são abertos e público através da Agencia Nacional de telecominicações\n"
      ],
      "metadata": {
        "id": "Fe-OiuQfDatH"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 3. Modelagem\n",
        "\n",
        "### Tabela 1: Dados do IQS - Telefonia Móvel\n",
        "\n",
        "\n",
        "| Nome do Campo         | Tipo    |Descrição|\n",
        "|-----------------------|---------|---------|\n",
        "| Ano                   | INTEGER |Ano da coleta|\n",
        "| Mês                   | INTEGER |Mês da coleta|\n",
        "| Serviço               | STRING  |Serviço Móvel Pessoal|\n",
        "| Indicador             | STRING  |Acrônimo do KPI|\n",
        "| Descrição             | STRING  |Descrição do KPI|\n",
        "| Tipo                  | STRING  |Indicador IQS|\n",
        "| Grupo                 | INTEGER |Rede|\n",
        "| Código IBGE           | INTEGER |Código do Município no IBGE|\n",
        "| Município             | STRING  |Nome do Município|\n",
        "| UF                    | STRING  |Unidade da Federação|\n",
        "| Prestadora            | STRING  |Nome da Operadora SMP|\n",
        "| Resultado             | FLOAT   |Valor calculado para o Indicador IQS|\n",
        "| Unidade de Medida     | STRING  |Unidade de medida (%)|\n",
        "| Número de Coletores   | INTEGER |Quantidade de terminais que representam a amostra de coleta|\n",
        "| Número de Medidas     | FLOAT   |Quantidade de medições coletadas|\n",
        "| Erro Amostral         | FLOAT   |Erro de amostragem|\n",
        "| Limite Inferior       | FLOAT   | |\n",
        "| Limite Superior       | FLOAT   | |\n",
        "| Data                  | STRING  |Data de coleta|\n",
        "\n",
        "### Tabela 2: Dimensão e Cobertura\n",
        "\n",
        "| Nome do Campo                     | Tipo    |Descrição|\n",
        "|-----------------------------------|---------|---------|\n",
        "| Código IBGE Município (Cobertura) | INTEGER |Código do Município no IBGE|\n",
        "| Município (Cobertura)             | STRING  |Nome do Município|\n",
        "| UF (Cobertura)                   | STRING  |Unidade da Federação|\n",
        "| Operadora                         | STRING  |Nome da Operadora SMP|\n",
        "| Tecnologia                        | STRING  |Tecnologia usada para a cobertura|\n",
        "| % área coberta                    | FLOAT   |Percentual de cobertura alcançada|\n",
        "| % moradores cobertos              | FLOAT   |% de moradores atendidos pela cobertura|\n",
        "| % domicílios cobertos             | FLOAT   |% de domicílios alcançados pela cobertura|\n",
        "| Área km2                          | FLOAT   |Área em Km2 do município|\n",
        "| Moradores                         | STRING  |Quantidade de moradores do município|\n",
        "| Domicílios                        | STRING  |Quantidade de domicílios do município|\n",
        "| Tipo de compromisso               | STRING  |Tipo de compromisso de cobertura assumindo em leilão|\n",
        "| Compromisso                       | STRING  |Descrição do compromisso|\n",
        "| Origem compromisso                | STRING  |Indicação do leilão|\n",
        "| Operadora com compromisso         | STRING  |Nome da Operarora com compromisso|\n",
        "| Status compromisso                | STRING  |Status atual do compromisso|\n",
        "| Prazo compromisso                 | STRING  |Prazo final para atender o compromisso|\n",
        "\n",
        "### Tabela 3: Velocidade\n",
        "\n",
        "| Nome do Campo       | Tipo    |Descrição|\n",
        "|---------------------|---------|---------|\n",
        "| Código Setor        | STRING  |Código do setor aferido|\n",
        "| Bairro Setor        | STRING  |Bairro do setor aferido|\n",
        "| Tipo Setor          | STRING  |tipo de setor|\n",
        "| Velocidade Mediana  | FLOAT   |Velocidade de up/downlink aferido|\n",
        "| Renda Média         | FLOAT   |Renda média do setor de aferição|\n",
        "| Categoria           | FLOAT   | |\n",
        "| Código Município    | INTEGER |Código do Município no IBGE|\n",
        "| Município (Velocidade) | STRING  |Nome do Município|\n",
        "| UF (Velocidade)     | STRING  |Unidade da Federação|\n",
        "\n",
        "### Tabela 4: Acessos\n",
        "\n",
        "| Nome do Campo            | Tipo    |Descrição|\n",
        "|--------------------------|---------|---------|\n",
        "| Ano                      | INTEGER |Ano da aferição|\n",
        "| Mês                      | INTEGER |Mês da aferição|\n",
        "| Grupo Econômico          | STRING  |Nome do Grupo Econômico|\n",
        "| Empresa                  | STRING  |Nome da Operadora|\n",
        "| CNPJ                     | STRING  |CNPJ da Operadora|\n",
        "| Porte da Prestadora      | STRING  |Indica o porte da operadora em relação a detenção de mercado|\n",
        "| UF                       | STRING  |Unidade da Federação|\n",
        "| Município                | STRING  |Nome do Município|\n",
        "| Código IBGE Município    | INTEGER |Código do Município no IBGE|\n",
        "| Código Nacional           | STRING  |Código DDD|\n",
        "| Código Nacional (Chip)   | STRING  |Código identificador do chip para SMP|\n",
        "| Modalidade de Cobrança   | STRING  |Tipo de cobrança do serviço Pós/Pré pago|\n",
        "| Tecnologia               | STRING  |Tecnologia usada pela rede de Acesso|\n",
        "| Tecnologia Geração       | STRING  |Geração da Tecnologia|\n",
        "| Tipo de Pessoa           | STRING  |Tipo de pessoa detentora do acesso Jurídica ou Física\n",
        "| Tipo de Produto          | STRING  | |\n",
        "| Acessos                  | STRING  |Quantidade de Acessos|\n",
        "\n",
        "Para a modelagem do banco de dados e na aplicação proposta para este trabalho, não é necessário utilizar todos os atributos contidos nas tabelas de dados coletadas nos datasets da Anatel. Portanto, na modelagem do esquema de relacionamento estrela mostrado na Figura 4, faremos uso apenas dos campos necessários. Contudo, a ideia desse trabalho é implementar uma tabela flat (desnormalizada) que contenha todos os dados em uma única tabela com o objetivo de ganhar eficiência de consultas, facilitar o entendimento e velocidade na ingestão dos dados no Google BigQuery, que  possui algumas considerações especiais quando se trata de operações de junção (joins) de tabelas, especialmente em comparação com bancos de dados relacionais tradicionais. Isso se deve ao fato de que o BigQuery é um banco de dados distribuído e colunar projetado para lidar com grandes volumes de dados e consultas em paralelo.\n",
        "\n",
        "\n",
        "![Figura 4](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/Modelagem.png)\n",
        "\n",
        "Figura 4 - Modelagem de um DW Estrela para o RQUAL - IQS da Anatel\n",
        "\n",
        "A Tabela Flat final que será inserida no Cloud BigQuery terá um esquema como indicado abaixo:\n",
        "\n",
        "\n",
        "| Nome do campo         | Tipo      | Modo     | Descrição                                            |\n",
        "|-----------------------|-----------|----------|-----------------------------------------------------|\n",
        "| Ano                   | INTEGER   | NULLABLE | Ano da medição e cálculo do KPI                     |\n",
        "| Mes                   | INTEGER   | NULLABLE | Mês de medição e cálculo do KPI                     |\n",
        "| Indicador             | STRING    | NULLABLE | Identificação do Indicador IQS                       |\n",
        "| Descricao             | STRING    | NULLABLE | Descrição do Indicador IQS                           |\n",
        "| Codigo_IBGE_R         | INTEGER   | NULLABLE | Código IBGE da Cidade                                |\n",
        "| Municipio             | STRING    | NULLABLE | Nome da cidade (normalizado)                        |\n",
        "| UF                    | STRING    | NULLABLE | Sigla da Unidade da Federação (Estado)              |\n",
        "| Prestadora            | STRING    | NULLABLE | Nome da Prestadora do Serviço Móvel Pessoal (SMP)   |\n",
        "| Resultado             | FLOAT     | NULLABLE | Resultado do indicador IQS (Unidade %)              |\n",
        "| data                  | STRING    | NULLABLE | Data da Medição                                     |\n",
        "| ANF                   | STRING    | NULLABLE | Código de Identificação da área de numeração nacional (ANF) |\n",
        "| Plano                 | STRING    | NULLABLE | Tipo de Plano Comercial                            |\n",
        "| Acessos_sum           | INTEGER   | NULLABLE | Número de Acessos ou Terminais                      |\n",
        "| Moradores_avg         | FLOAT     | NULLABLE | Número médio de Moradores da cidade                 |\n",
        "| Domicilios_avg        | FLOAT     | NULLABLE | Número médio de Domicílios da Cidade               |\n",
        "| Perc_moracores_cobertos | FLOAT  | NULLABLE | Percentual de moradores com cobertura de serviço móvel |\n",
        "| Velocidade_mediana    | FLOAT     | NULLABLE | Velocidade média de throughput                      |\n",
        "| Renda_Media           | FLOAT     | NULLABLE | Renda Média da por Usuário                          |\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "-7HLVpeSHyaB"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "<!-- Quebra de página -->\n",
        "<div style=\"page-break-after: always;\"></div>\n"
      ],
      "metadata": {
        "id": "HXE9MTAAn1aw"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 4. Pipeline de Dados\n",
        "\n",
        "A Figura 5 mostra todos os passos a serem utilizados para criar um Pipeline de dados desde a coleta dos dados até sua ingestão no Banco de Dados. Aqui definimos quatro passos sequenciais que utilizam recursos da Google Cloud:\n",
        "\n",
        "1. Google Drive,\n",
        "2. GCS (Google Cloud Storage),\n",
        "3. Data Fusion, e\n",
        "4. BigQuery.\n",
        "\n",
        "Nas seções que seguem, mostraremos como cada passo foi executado, com evidências em forma de capturas de tela para cada etapa do processo.\n",
        "\n",
        "![Fifgura 5](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/Pipeline%20de%20dados.png)\n",
        "\n",
        "Figura 5 - Pipeline de dados para tratamento do RQUAL-IQS da Anatel\n",
        "\n",
        "**Passo 1: Configurando o Google Drive**\n",
        "\n",
        "Neste primeiro passo, no Console da Google Cloud, criaremos uma pasta no Google Drive onde os dados a serem processados no pipeline serão armazenados.\n",
        "\n",
        "**Passo 2: Google Cloud Storage (GCS)**\n",
        "\n",
        "No segundo passo, acessaremos o Google Cloud Storage (GCS) para criar um novo bucket de armazenamento para os dados que serão processados pelo pipeline.\n",
        "\n",
        "**Passo 3: Google Cloud Data Fusion**\n",
        "\n",
        "Neste terceiro passo, utilizaremos o Google Cloud Data Fusion para criar um ambiente de gerenciamento do pipeline e configurar a conexão com o GCS. Aqui, definiremos o pipeline para ler, transformar, processar e realizar a ingestão dos dados no banco de dados.\n",
        "\n",
        "**Passo 4: Google BigQuery**\n",
        "\n",
        "No último passo, será criado um conjunto de dados (dataset) onde os dados processados serão armazenados. Além disso,  configuraremos o BigQuery para carregar automaticamente os dados do Data Fusion na tabela correspondente do BigQuery.\n",
        "\n",
        "Esses passos fornecem uma visão geral do processo para criar um pipeline de dados usando a Google Cloud. Detalhes adicionais e configurações específicas podem ser necessários dependendo dos requisitos e ferramentas exatas utilizadas em seu projeto."
      ],
      "metadata": {
        "id": "LcXVZ4GYYAK6"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 4.1 Passo 1 - Coleta de Dados\n",
        "\n",
        "Os dados referentes aos indicadores Rqual, Cobertura, Renda x Velocidade e Acessos foram coletados a partir dos sites da Anatel, conforme mencionado na seção de Objetivo Principal deste documento. Os links para acesso aos dados são os seguintes:\n",
        "\n",
        "1. Rqual - [Link para Download](https://www.anatel.gov.br/dadosabertos/paineis_de_dados/qualidade/indicadores_rqual.zip);\n",
        "1. Renda x Velocidade - [Link para Download](https://www.anatel.gov.br/dadosabertos/paineis_de_dados/infraestrutura/renda_velocidade_smp.zip );\n",
        "1. Cobertura - [Link para Download](https://informacoes.anatel.gov.br/paineis/infraestrutura);\n",
        "1. Acessos - [Link para Download](https://informacoes.anatel.gov.br/paineis/infraestrutura)\n",
        "\n",
        "Conforme estabelecido no Objetivo Principal, serão considerados apenas dados de um estado por região do Brasil, incluindo Paraná, São Paulo, Pernambuco, Goiás, Belém e Distrito Federal. Para reduzir o tamanho dos arquivos de dados, realizamos um filtro nesses arquivos utilizando a ferramenta Knime. É importante ressaltar que esse processo de filtragem não afeta as transformações de dados efetivas que serão realizadas nas ferramentas ETL do Google Cloud.\n",
        "\n",
        "![Figura 6](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/Knime.png)\n",
        "\n",
        "Figura 6 - Fluxo no Knime para filtrar os datasets relacionados aos estados do PA, PE, GO, DF, SP e PR.\n",
        "\n",
        "Na sequencia os arquivos foram salvos no Google Drive para utilização pelo Pipeline que fará o ETL do dataset final.\n",
        "\n",
        "![Figura 7](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/GoogleDrive.png)\n",
        "\n",
        "Figura 7 - Dataset no Google Drive\n",
        "\n",
        "Links para os arquivos filtrados:\n",
        "\n",
        "1. *RQUAL_Movel_Reg2023.csv* - [Link para Download](https://drive.google.com/file/d/1L0qJlHnluOsQnx7JR4OT0sejJAguIrE1/view?usp=sharing)\n",
        "1. *Velocidade_Movel_Reg2023.csv* - [Link para Download](https://drive.google.com/file/d/1WzQ-qlPEvr-epqQJrKmZlD7E7eLLcSkJ/view?usp=sharing)\n",
        "1. *Cobertura_Movel_Reg2023.csv* - [Link para Download](https://drive.google.com/file/d/106VX3PpyYioXMrXssKlzTzs89xxkmAJ7/view?usp=sharing)\n",
        "1. *Acesso_Movel_Reg2023.csv* - [Link para Download](https://drive.google.com/file/d/106VX3PpyYioXMrXssKlzTzs89xxkmAJ7/view?usp=sharing)\n",
        "\n",
        "Na sequência os arquivos foram transferidos para a área de armazenamento temporário do Cloud Shell do projeto Sprint3-PUC-DataAnalytics da conta *w******@gmail.com*.\n",
        "\n",
        "![Figura 8](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/ContaGCP.png)\n",
        "\n",
        "Figura 8 - Evidência da Conta Google Cloud criada para o e-mail *w******@gmail.com*.\n",
        "\n",
        "### 4.1.1 Carga do dataset para o Google Cloud Shell\n",
        "\n",
        "Os arquivos foram coletados utilizando a ferramenta *gdown*, que é uma linha de comando que facilita o download de arquivos do Google Drive diretamente para o ambiente do Cloud Shell. Esta ferramenta simplifica o processo de obtenção dos arquivos necessários para a análise de dados.\n",
        "\n",
        "Sintaxe:\n",
        "\n",
        "gdown https://drive.google.com/uc?id=ID-DE-ARQUIVO /DESTINO\n",
        "\n",
        "Id dos arquivos:\n",
        "\n",
        "1. 1WzQ-qlPEvr-epqQJrKmZlD7E7eLLcSkJ\n",
        "1. 1L0qJlHnluOsQnx7JR4OT0sejJAguIrE1\n",
        "1. 106VX3PpyYioXMrXssKlzTzs89xxkmAJ7\n",
        "1. 19NZskmHMUKmlY9rvkzWIQPp2kziE6H6I\n",
        "\n",
        "![Figura 9](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/promptCloudShell.png)\n",
        "\n",
        "Figura 9 - Prompt do Cloud Shell e criação do diretório destino (/Anatel)\n",
        "\n",
        "![Figura 10](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/Instala%C3%A7%C3%A3o%20gdown.png)\n",
        "\n",
        "Figura 10 - Instalação do *gdown*\n",
        "\n",
        "\n",
        "![Figura 11](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/ColetaGdown.png)\n",
        "\n",
        "Figura 11 - Coletando os arquivos do Google Drive para o Google Shell\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "qRSrfsKgz9Yi"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 4.2 Passo 2 - Criando Buckets no Clound Stores\n",
        "\n",
        "Essa etapa demonstra a criação de uma área para armazenamento no Cloud Store para o acesso dos dados pela ferramenta de ETL. Na sequência serão demonstradas a criação do Bucket, a transferência dos arquivos csv ( *RQUAL_Movel_Reg2023.csv*, *Velocidade_Movel_Reg2023.csv*, *Cobertura_Movel_Reg2023.csv*, *Acesso_Movel_Reg2023.csv*) e suas evidências.\n",
        "\n",
        "![Figura 12](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/AcessoCloudStorage.png)\n",
        "\n",
        "Figura 12 - Acessando o Cloud Storege\n",
        "\n",
        "> **Criando um Bucket**\n",
        "\n",
        "Na sequência abaixo mostramos como criar um Bucket e as evidências do Bucket ciado para receber os arquivos de dataset referente aos RQUAL.\n",
        "\n",
        "![Figura13-1](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/NovoBucket.png)\n",
        "![Figura13-2](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/NovoBucket(1).png)\n",
        "![Figura13-3](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/NovoBucket%20(2).png)\n",
        "![Figura13-4](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/NovoBucket(3).png)\n",
        "![Figura13-5](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/NovoBucket(4).png)\n",
        "\n",
        "Figura 13 - Criando o novo Bucket: sprint3-bucket-anatel\n",
        "\n",
        "> **Bucket criado**\n",
        "\n",
        "![Figura 14-1](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/Bucket(1).png)\n",
        "![Figura 14-2](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/Bucket(2).png)\n",
        "![Figura 15](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/BucketPastas.png)\n",
        "\n",
        "Figura 14 - Evidência dos Bucketes criados\n",
        "\n",
        "> Transferindo os arquivos do Cloud Shell para o Cloud Storage\n",
        "\n",
        "O comando gsutil cp * gs://sprint3-bucket-anatel/arquivos_in foi utilizado para realizar a transferência dos arquivos do Cloud Shell para o Cloud Storage. Abaixo as evidências dos comandos e dos arquivos devidamente coletados para o bucket sprint3-bucket-anatel/arquinos_in.\n",
        "\n",
        "![Figura 15](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/gsutil.png)\n",
        "![Figura 15-1](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/gsutil%20completo.png)\n",
        "\n",
        "Figura 15 - Dataset transferido para o Bucket-Anatel\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "1LFTR1kCDiUi"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 4.3 Análise dos dados\n",
        "\n",
        "Durante a etapa de Modelagem, detalhada anteriormente neste documento, foram definidos atributos para cada arquivo no dataset, identificados como Tabela 1 a Tabela 4. Durante essa etapa, avaliamos que apenas alguns atributos eram necessários para realizar a análise dos indicadores RQUAL-IQS proposta nas questões do objetivo principal, os quais foram consolidados na Tabela Flat.\n",
        "\n",
        "Abaixo estão os atributos e seus tipos:\n",
        "\n",
        "| Nome do campo         | Tipo      | Modo     | Descrição                                            |\n",
        "|-----------------------|-----------|----------|-----------------------------------------------------|\n",
        "| Ano                   | INTEGER   | NULLABLE | Ano da medição e cálculo do KPI                     |\n",
        "| Mes                   | INTEGER   | NULLABLE | Mês de medição e cálculo do KPI                     |\n",
        "| Indicador             | STRING    | NULLABLE | Identificação do Indicador IQS                       |\n",
        "| Descricao             | STRING    | NULLABLE | Descrição do Indicador IQS                           |\n",
        "| Codigo_IBGE_R         | INTEGER   | NULLABLE | Código IBGE da Cidade                                |\n",
        "| Municipio             | STRING    | NULLABLE | Nome da cidade (normalizado)                        |\n",
        "| UF                    | STRING    | NULLABLE | Sigla da Unidade da Federação (Estado)              |\n",
        "| Prestadora            | STRING    | NULLABLE | Nome da Prestadora do Serviço Móvel Pessoal (SMP)   |\n",
        "| Resultado             | FLOAT     | NULLABLE | Resultado do indicador IQS (Unidade %)              |\n",
        "| data                  | STRING    | NULLABLE | Data da Medição                                     |\n",
        "| ANF                   | STRING    | NULLABLE | Código de Identificação da área de numeração nacional (ANF) |\n",
        "| Plano                 | STRING    | NULLABLE | Tipo de Plano Comercial                            |\n",
        "| Acessos_sum           | INTEGER   | NULLABLE | Número de Acessos ou Terminais                      |\n",
        "| Moradores_avg         | FLOAT     | NULLABLE | Número médio de Moradores da cidade                 |\n",
        "| Domicilios_avg        | FLOAT     | NULLABLE | Número médio de Domicílios da Cidade               |\n",
        "| Perc_moracores_cobertos | FLOAT  | NULLABLE | Percentual de moradores com cobertura de serviço móvel |\n",
        "| Velocidade_mediana    | FLOAT     | NULLABLE | Velocidade média de throughput                      |\n",
        "| Renda_Media           | FLOAT     | NULLABLE | Renda Média da por Usuário                          |\n",
        "\n",
        "> **Transformação de Dados**\n",
        "\n",
        "Identificamos que alguns atributos com representatividade numérica estavam definidos como tipo string nas tabelas de origem, exigindo a correta conversão para tipos numéricos durante o processo de ETL. Como exemplo, temos o atributo Número de Acessos na Tabela Acessos, originalmente definido como string, mas transformado em integer durante o ETL.\n",
        "\n",
        ">**Agrupamento de Dados**\n",
        "\n",
        "Outro aspecto relevante a ser considerado é que a simples exclusão de atributos pode não ser a transformação adequada, já que isso pode afetar o significado ou a representatividade de outros atributos. Por exemplo, a quantidade de Acessos em um município está subdividida em Pré-pago e Pós-pago com base no atributo Modalidade de Cobrança. Assim, a eliminação direta desse atributo pode resultar na perda de informações cruciais, a menos que esses dados sejam agrupados de alguma forma no contexto municipal. Portanto, nas tabelas Velocidade, Acessos e Cobertura, optamos por manter alguns atributos por meio de agrupamentos (group by), quando relevante, para preservar informações essenciais.\n",
        "\n",
        ">**Dados Ausentes, Nulos e Caso**\n",
        "\n",
        "O tratamento de dados ausentes, nulos, letras maiúsculas e diacríticos foi realizado durante o pré-processamento no Knime, antes da importação dos dados para o ambiente em nuvem. De fato, a presença de dados ausentes e nulos foi observada apenas no arquivo RQUAL, mais especificamente nos atributos Limites Inferiores, Limites Superiores e Número de Coletas. No entanto, os registros foram mantidos caso o atributo correspondente, Resultado, não fosse nulo. Além disso, as conversões para letras minúsculas foram aplicadas aos atributos do tipo string, assim como a remoção de diacríticos.\n",
        "\n",
        "Em resumo, os dados estavam, em sua maioria, bem comportados e normalizados, não sendo necessária a aplicação de técnicas adicionais além das mencionadas acima.\n",
        "\n"
      ],
      "metadata": {
        "id": "vJTGTY3BNvSw"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 4.4 Passo 3 - Pipeline de Dados com Data Fusion\n",
        "\n",
        "Essa é uma fase crucial do processo, pois cria o Pipeline de Dados, no qual os *raw data* são lidos, transformados e carregados no banco de dados, ou seja, é executado o processo de ETL.\n",
        "\n",
        "A seguir descrevemos todos os passos para criar um Pipeline de Dados no Cloud Data Fusion (evidências das Figuras 16 a 26):\n",
        "\n",
        "*   Criar uma instância do Data Fusion e um bucket temporário (Figuras 16, 17 e 18)\n",
        "*   Criar uma instância de Banco de Dados no BiGQuary (Figura 19)\n",
        "*   Criar um Pipeline ETL na interface gráfica do Data Fusion (Firura 20)\n",
        "*   Realizar o Deploy do fluxo criado (Figura 21)\n",
        "*   Configurar o Deploy para execução (Figura 22)\n",
        "*   Avaliar a execução (Figuras 23.a até 23.d)\n",
        "\n",
        "\n",
        "---\n",
        "---\n",
        "\n",
        "\n",
        "![Figura 16](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/31372e8e453db0ff70e2eb6fff4c1c084692f3e3/Inicando%20DataFusion.png)\n",
        "Figura 16 - Criando uma Instância do Data Fusion\n",
        "\n",
        "\n",
        "---\n",
        "---\n",
        "\n",
        "\n",
        "![Figura 17](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/31372e8e453db0ff70e2eb6fff4c1c084692f3e3/EvidenciaDataFusion.png)\n",
        "Figura 17 - Instância do Data Fusion criada\n",
        "\n",
        "\n",
        "---\n",
        "---\n",
        "\n",
        "\n",
        "![Figura 18](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/31372e8e453db0ff70e2eb6fff4c1c084692f3e3/BucketTempor%C3%A1rio.png)\n",
        "Figura 18 - Criando um bucklet temporário para uso pela instância Fusion: sprint3-bucket-anatel-temp\n",
        "\n",
        "---\n",
        "---\n",
        "\n",
        "![Figura 19](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/31372e8e453db0ff70e2eb6fff4c1c084692f3e3/Inst%C3%A2ncia%20BigQuary.png)\n",
        "Figura 19 - Instância do BigQuery criada como *sprint3rqual*\n",
        "\n",
        "---\n",
        "---\n",
        "\n",
        "Nome do Fluxo:\n",
        "\"description\": \"ETL para os arquivos referentes ao Indicador de Qualidade IQS do RQUAL Anatel - dados de Cobertura e velocidade de dados\",\n",
        "\"name\": \"sprint3-PUC-RQUAL_Cobertura\",\n",
        "\n",
        "![Figura 20](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/31372e8e453db0ff70e2eb6fff4c1c084692f3e3/PipelineDataFusion.png)\n",
        "Figura 20 - Evidência do Pipeline ETL para ingestão no BigQuery dos dados do RQUAL e suas dimensões como uma Tabela Flat\n",
        "\n",
        "---\n",
        "---\n",
        "\n",
        "![Figura 21](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/31372e8e453db0ff70e2eb6fff4c1c084692f3e3/DeployDataFusion.png)\n",
        "Figura 21 - Deploy do fluxo ETL para execução no Data Fusion\n",
        "\n",
        "---\n",
        "---\n",
        "\n",
        "![Figura 22](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/31372e8e453db0ff70e2eb6fff4c1c084692f3e3/ConfigDeployDF.png)\n",
        "Figura 22 - Configurando a instância Data Fusion com autoscaling para execução do fluxo.\n",
        "\n",
        "*  Observação: Na etapa de execução, inicialmente tentamos executar na configuração padrão (Dataproc), que define o número de núcleos e memória fixados para a máquina virtual (VM). Após várias tentativas, nas quais o fluxo era finalizado sem sucesso, e após analisar os registros de execução, foi constatado que poderia ser um problema de recursos da VM. Portanto, optamos pela configuração de Autoscaling, que prontamente solucionou o problema de execução.\n",
        "\n",
        "---\n",
        "---\n",
        "\n",
        "![Figura 23.a](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/31372e8e453db0ff70e2eb6fff4c1c084692f3e3/Executado_DF1.png)\n",
        "Figura 23.a\n",
        "![Figura 23.b](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/31372e8e453db0ff70e2eb6fff4c1c084692f3e3/Executado_DF2.png)\n",
        "Figura 23.b\n",
        "![Figura 23.c](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/31372e8e453db0ff70e2eb6fff4c1c084692f3e3/Executado_DF3.png)\n",
        "Figura 23.c\n",
        "![Figura 23.d](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/31372e8e453db0ff70e2eb6fff4c1c084692f3e3/Executado_DF4.png)\n",
        "Figura 23.d\n",
        "\n",
        "Figura 23.a até Figura 23.d - Fluxo executado com sucesso em 12min e 44 sec, sem alarmes de erros e 225.195 registros carregados no Banco de Dados (BigQuery).\n",
        "\n",
        "\n",
        "\n",
        "### Explicando o Fluxo ETL da Figura 20\n",
        "\n",
        "#### Objetivo\n",
        "\n",
        "O objetivo do fluxo é extrair, transformar e carregar dados de qualidade do indicador (IQS) do RQUAL da Anatel, especificamente dados de acessos, cobertura e velocidade de dados.\n",
        "\n",
        "#### Descrição\n",
        "\n",
        "O fluxo é composto por cinco etapas principais:\n",
        "\n",
        "> Extração: O fluxo extrai os dados do RQUAL, Acessos, Cobertura e Velocidade dos arquivo CSV armazenados no Google Cloud Storage (GCS). O plugin GCSFile é usado para essa tarefa.\n",
        "\n",
        "> Transformação: O fluxo transforma os dados RQUAL, de acesso e velocidade e cobertura usando o plugin Wrangler. Os campos são renomeados, removidos e alterados de tipo de dados, conforme necessário. Abaixo seguem os exemplos de transformações executadas no arquivo RQUAL:\n",
        "\n",
        "> Renomeando atributos:\n",
        "\n",
        "> * M_s para Mes\n",
        "> * Servi_o para Prestadora\n",
        "> * Tipo para Tipo_IQS\n",
        "> * Grupo para Grupo_IQS\n",
        "> * C_digo_IBGE para Codigo_IBGE_R\n",
        "> * data para Data\n",
        "> * Indicador para Indicador\n",
        "> * Descrição para Descrição\n",
        "> * ANF para ANF_Acesso>\n",
        "> * Plano para Plano_Acesso\n",
        "> * data para Data\n",
        "> * Indicador para Indicador\n",
        "> * Descrição para Descrição\n",
        "> * Codigo_IBGE para Codigo_IBGE_R\n",
        "> * Municipio para Municipio\n",
        "> * UF para UF\n",
        "> * Prestadora para Prestadora\n",
        "> * Resultado para Resultado\n",
        "\n",
        "> Alterado tipo:\n",
        "\n",
        "> * Resultado **de** string **para** double\n",
        "\n",
        "> Join: O fluxo junta os dados RQUAL, de acesso, velocidade e cobertura usando o plugin Joiner.\n",
        "\n",
        "> Carregamento: O fluxo carrega os dados agregados na tabela BigQuery-Rqual.\n",
        "\n",
        "> Validação: O fluxo valida os dados carregados na tabela BigQuery-Rqual.\n",
        "\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "K_BFe2XIz4Jl"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 4.5 Passo 4 - Dados no BigQuary\n",
        "\n",
        "O fluxo ETL do Data Fusion fez a ingestão da tabela flat resultante,*DatasetRqualPlus*, na instância *sprint3rqual* do BigQuary criado para o projeto *sprint3-puc-dataanalytics*.\n",
        "Nas Figuras que acompanharemos na sequência são mostradas as evidências da tabela flat *DatasetRqualPlus* carregada, o esquema correspondente, o catálogo no Dataplex e uma consulta SQL para contagem de linhas da tabela.\n",
        "\n",
        "![Figura 24](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/evidencia-BigQuery.png)\n",
        "Figura 24 - Evidência de Esquema do DatasetRqualPlus no BigQuery\n",
        "\n",
        "---\n",
        "---\n",
        "\n",
        "```sql\n",
        "SELECT COUNT(*) FROM   `sprint3-puc-dataanalytics.sprint3rqual.DatasetRqualPlus`;\n",
        "```\n",
        "\n",
        "![Figura 25](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/QuerySimples.png)\n",
        "Figura 25 - Query simples para contagem do número de registros da tabela\n",
        "\n",
        "---\n",
        "---\n",
        "\n",
        "A construção do Catálogo de Dados da tabela *DatasetRqualPlus* pode ser realizada utilizando o Cloud Dataplex. Na sequencia de Figuras mostramos uma descrição da **Visão Geal**, a documentação de cada atributo do esquema e um Glossário dos indicadores IQS.\n",
        "\n",
        "![Figura 26](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/DataCatalog.png)\n",
        "Figura 26 - Seleção do BigQuery no Dataplex para selecionar a tabela a ser documentada\n",
        "\n",
        "---\n",
        "---\n",
        "\n",
        "![Figura 27](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/Dataplex%20Vis%C3%A3oGeral.png)\n",
        "Figura 27 - Detalhes da Tabela do BigQuery com a descrição da Visão Geral do *DatasetRqualPlus*\n",
        "\n",
        "Detalhe da descrição inserida no Dataplex para a Visão Geral da Tabela Flat carregada no BigQuary referente ao RQUAL.\n",
        "\n",
        "|**Visão Geral**|\n",
        "|---------------------------------------------------|\n",
        "|Esta tabela possui os dados cruzados a partir de uma Inner Join entre quatro bases de dados que contem dados do Serviço Móvel Pessoal (SMP) referente ao serviço 4G ou LTE. As bases de dados são:|\n",
        "|RQUAL que contém os dados dos indicadores IQS mensão e por ano. Nessa versão dos dados estão contidos apenas 06 meses de dados referentes aos meses de Janeiro a Junho de 2023 e para as cidades dos estados do Paraná, São paulo, Goiás, Distrito Federal, Prnambuco e Pará. Poderiamos chamar essa Tabela como equivalente a tabela Fato de um esquema estrela.|\n",
        "|Acesso que contém os dados agrupados de terminais por município brasileiro e os respectivos planos comercializados pelas operadoras. Poderiámos pensar como uma Tabela Dimensão.|\n",
        "|A base Cobertura possui os dados agrupados de cobertura dos municíos brasileiros com informações de domicílios, número de moradores e percentual de moradores cobertos.|\n",
        "|Base de Velocidade por Renda contém os dados agrupados de velocidade média de up/down link e renda média por habitante. poderiamos pensar como uma tabela Dimensão.|\n",
        "|Importante considerar que essa tabela por ser desnormalizada através de INNER JOIN perde informação em que não há combinação/coincidência dos dados de chave. Isso foi feito de propósito a fim de reduzir o tamanho da Tabela resultante.|\n",
        "\n",
        "---\n",
        "---\n",
        "\n",
        "![Figura 28](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/Dataplex%20Esquema.png)\n",
        "Figura 28 - Documentação dos Atributos do esquema da tabela *DatasetRqualPlus*\n",
        "\n",
        "---\n",
        "---\n",
        "\n",
        "![Figura 29.a](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/Glossario1.png)\n",
        "\n",
        "![Figura 29.b](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/Glossario2.png)\n",
        "\n",
        "Figura 29 - Glossário com o significado dos Indicadores RQUAL-IQS\n",
        "\n"
      ],
      "metadata": {
        "id": "j6Qq6nmIVFZX"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "<!-- Quebra de página -->\n",
        "<div style=\"page-break-after: always;\"></div>\n"
      ],
      "metadata": {
        "id": "YxlbwJJBn-KA"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 5. Análise e Discussão\n",
        "\n",
        "A fim de responder as questões formuladas no Objetivo principal, é necessário dividir essa seção em três etapas referentes a perguntas 1, 2 e 3, respectivamente.\n",
        "\n",
        "## 5.1 Pergunta 1\n",
        "\n",
        "### Questão:\n",
        "**É possível reproduzir as consultas dos indicadores IQS do RQUAL, individualmente ou grupos, através de um pipeline de dados na nuvem selecionando atributos como ano, mês, indicador, cidade, estado e prestadora?, ou seja reproduzir a mesma consulta feita pelo site Anatel?**\n",
        "\n",
        "\n",
        "### Evidências 1\n",
        "\n",
        "No exemplo abaixo selecionamos os atributos (Mes,  Indicador,   Prestadora,  Municipio,  Resultado) para os ano de 2023 e estado de São Paulo. Na sequência, mostramos os resultados agregados por Indicador e por período para cada indicador.\n",
        "Para gerar o resultado realizamos uma consulta no Banco (Figura 30) e plotamos os resultados através do Looker Studio, que é incorporado ao BigQuery.\n",
        "\n",
        "```sql\n",
        "SELECT   Mes,  Indicador,   Prestadora,  Municipio,  AVG(Resultado) AS Resultado_Medio\n",
        "FROM  `sprint3-puc-dataanalytics.sprint3rqual.DatasetRqualPlus`\n",
        "WHERE  UF = 'sp' AND  Ano = 2023\n",
        "GROUP BY  Mes,  Indicador,  Prestadora,  Municipio\n",
        "ORDER BY  Mes,  Indicador;\n",
        "```\n",
        "\n",
        "![Figura 30](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/SQLresp1.png)\n",
        "Figura 30 - Consulta SQL para gerar os dados para a Resposta 1.\n",
        "\n",
        "![Figura 31](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/LookerResp1.png)\n",
        "Figura 31 - Gráficos co os indicadores IQS para Resposta 1 - dados Agregados para o Estado de São Paulo.\n",
        "\n",
        "![Figura 32](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/LookerResp1-SAMPA.png)\n",
        "Figura 32 - Gráficos co os indicadores IQS para Resposta 1 - dados Agregados para a cidade de São Paulo.\n",
        "\n",
        "Legenda para os Indicadores:\n",
        "* IND1 - Conexão de Chamada na ree de Acesso\n",
        "* IND2 - Queda de Chamada\n",
        "* IND3 - Conexão de Dados Medida na Rede de Acesso\n",
        "* IND4 - Cumprimento de Velocidade de Download e Upload\n",
        "* IND5 - Latência bidirecional da Conexão de Dados\n",
        "* IND6 - Variação de Latência da Conexão de Dados\n",
        "* IND7 - Perda de Pacotes da Conexão de Dados\n",
        "* IND8 - Disponibilidade\n",
        "\n",
        "### Analise Descritiva 1\n",
        "\n",
        "Com a consulta SQL realizada, na Figura 30, é possível gerar Dashboars através do Cloud Looker Studio ou outra ferramenta de visusalização. Nossa opção foi gerar através do Lokker Studio que já é integrada ao BigQuery.\n",
        "Nas Figuras 31 e 32 mostramos um exemplo de Dashboard em que usando os filtros Indicador, Prestadora e Município é possível selecionar um ou vários valores de atributos e avaliar os dados gerados para os indicadores agregados no tempo ou visializar cada indicador entre os meses 1 e 6. As Figuras mostram os gráficos comparáveis entre as Operadoras, podendo ser verificado o desempenho de cada uma por Estado ou Município.\n",
        "\n",
        "\n",
        "### Resposta 1:\n",
        "**Sim. Uma vez que a tabela do IQS do RQUAL foi carregada no BigQuery em forma de Banco de Dados é perfeitamente possível reproduzir os resultados mostrados pelo site da Anatel com os mesmos dados e atributos.**\n",
        "\n",
        "\n",
        "## 5.2 Pergunta 2\n",
        "\n",
        "### Questão:\n",
        "\n",
        "**É possível incrementar a essas consultas filtros que considerem o tamanho da população das cidades, quantidade de assinantes por operadora e renda percapita ou PIB?**\n",
        "\n",
        "### Evidências 2\n",
        "\n",
        "A consulta SQL mostrada abaixo faz uma seleção dos atributos do RQUAL acrescidos dos dados de Renda, Moradores, Velocidade e Quantidade de Acessos por Município e Operadora, respectivamente.\n",
        "\n",
        "```sql\n",
        "SELECT   Mes,  Indicador,  Prestadora,  Municipio, ANF,  \n",
        "  AVG(Resultado) AS Resultado_Medio,\n",
        "  AVG(Acessos_sum) AS Acessos,\n",
        "  AVG(Renda_Media) AS Renda_Media,  \n",
        "  AVG(Velocidade_mediana) As Thp_Medio,  \n",
        "  AVG(Moradores_avg) AS Numero_habitantes\n",
        "FROM  `sprint3-puc-dataanalytics.sprint3rqual.DatasetRqualPlus`\n",
        "WHERE  UF = 'sp' AND  Ano = 2023\n",
        "GROUP BY  Mes,  Indicador,  Prestadora,  Municipio,  ANF\n",
        "ORDER BY  Mes,  Indicador;\n",
        "```\n",
        "![Figura 33](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/SQLresp2.png)\n",
        "Figura 31 - SQL que acresnta os dados de Renda, Velocidade e Quantidades de Acesso aos dados de RQUAL\n",
        "\n",
        "![Figura 34](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/QueryVizualResp2.png)\n",
        "Figura 34 - Print com o resultado da consulta\n",
        "\n",
        "![Figura 35](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/ExportSQL.png)\n",
        "Figura 35 - Opções do BigQuery para exportação do resultado da consulta (Planilha, Looker ou Notebook Py)\n",
        "\n",
        "### Resposta 2\n",
        "\n",
        "**Sim. Esta consulta SQL recupera dados da tabela *DatasetRqualPlus* a partir do banco de dados *sprint3-puc-dataanalytics.sprint3rqual* para o estado de São Paulo (UF = 'sp') no ano de 2023. Ela calcula médias das métricas (Resultado, Velocidade, Acessos, Renda e Moradores)agrupadas por mês, indicador, prestadora, município e ANF, e, em seguida, ordena os resultados com base em mês e indicador.\n",
        "Com os dados recuperados nessa consulta é possível partirmos para responder as questões da Perguntas 3.**\n",
        "\n",
        "## 5.3 Pergunta 3\n",
        "\n",
        "### Questão\n",
        "\n",
        "**Fazer uma análise como:**\n",
        "\n",
        "> **a. melhores e piores IQS – por município**\n",
        "\n",
        "> **b. melhores e piores IQS – por prestadora**\n",
        "\n",
        "> **c. melhores e piores IQS – por população**\n",
        "\n",
        "> **d. melhores e piores IQS – por renda**\n",
        "\n",
        "\n",
        "### Evidências 3\n",
        "\n",
        "![Figura 36](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/Vis%C3%A3o%20RAHT%20-%20Claro.png)\n",
        "\n",
        "Figura 36 - Variância dos Indicadores - Operadora 1\n",
        "\n",
        "[Link para Detalhe dos Indicadores da Operadora 1](https://github.com/wabastos/Engenharia-de-Dados/blob/main/Renda_UF_Claro.pdf)\n",
        "\n",
        "[Link para Detalhe dos Indicadores da Operadora 1 e ANF](https://github.com/wabastos/Engenharia-de-Dados/blob/main/Detalhe_Claro_ANF.pdf)\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "![Figura 37](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/Vis%C3%A3o%20RAHT%20-%20Tim.png)\n",
        "\n",
        "Figura 37 - Variância dos Indicadores - Operadora 2\n",
        "\n",
        "[Link para Detalhe dos Indicadores da Operadora 2](https://github.com/wabastos/Engenharia-de-Dados/blob/main/Renda_UF_Tim.pdf)\n",
        "\n",
        "[Link para Detalhe dos Indicadores da Operadora 2 e ANF](https://github.com/wabastos/Engenharia-de-Dados/blob/main/Detalhe_Tim_ANF.pdf)\n",
        "\n",
        "\n",
        "\n",
        "![Figura 38](https://raw.githubusercontent.com/wabastos/Engenharia-de-Dados/main/Vis%C3%A3o%20RAHT%20-%20Vivo.png)\n",
        "\n",
        "Figura 38 - Variância dos Indicadores - Operadora 3\n",
        "\n",
        "[Link para Detalhe dos Indicadores da Operadora 3](https://github.com/wabastos/Engenharia-de-Dados/blob/main/Renda_UF_Vivo.pdf)\n",
        "\n",
        "[Link para Detalhe dos Indicadores da Operadora 3 e ANF](https://github.com/wabastos/Engenharia-de-Dados/blob/main/Detalhe_Vivo_ANF.pdf)\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "[Link para Detalhe Operadoras](https://github.com/wabastos/Engenharia-de-Dados/blob/main/Detalhe_Operadoras.pdf)\n",
        "\n",
        "\n",
        "### Analise Descritiva 3\n",
        "\n",
        "As evidências de análises geradas e exibidas nas Figuras e Links da seção anterior referem-se às três principais operadoras do Serviço Móvel Pessoal (SMP) com presença em todas as unidades federativas do Brasil. As análises se repetem igualitariamente para as três operadoras, fazendo uso dos mesmos atributos, mas com os dados restritos a cada uma delas. Evitaremos comparação de rotulação entre elas porque a Anatel, detentora do selo RQUAL, ainda não realizou ou divulgou o resultado de sua classificação. Portanto, qualquer análise nesse sentido poderia não considerar aspectos relevantes, como a pesquisa qualitativa, ou até contestações, considerações e justificativas das operadoras acerca dos dados coletados. Vamos focar nossa análise na Operadora 1, considerando que o perfil entre elas é muito semelhante como indicado acima.\n",
        "\n",
        "---\n",
        "\n",
        "Legenda para os Indicadores:\n",
        "* IND3 - Conexão de Dados Medida na Rede de Acesso\n",
        "* IND4 - Cumprimento de Velocidade de Download e Upload\n",
        "* IND5 - Latência bidirecional da Conexão de Dados\n",
        "* IND6 - Variação de Latência da Conexão de Dados\n",
        "* IND7 - Perda de Pacotes da Conexão de Dados\n",
        "* IND8 - Disponibilidade\n",
        "\n",
        "---\n",
        "\n",
        "Os gráficos da Figura 36 mostram, para cada Indicador (IND3 a IND8), a dispersão das variâncias dos dados referentes aos atributos Média dos Resultados por Renda Média, Acessos, Moradores e Thp_médio, considerando o conjunto de municípios do Estado de São Paulo.\n",
        "\n",
        "```sql\n",
        "SELECT COUNT(DISTINCT Municipio) AS NumeroDeMunicipios\n",
        "FROM `sprint3-puc-dataanalytics.sprint3rqual.DatasetRqualPlus`\n",
        "WHERE UF = 'sp' AND Ano = 2023;\n",
        "\n",
        "/* Resultado\n",
        "NumeroDeMunicipios\n",
        "534\n",
        "*/\n",
        "\n",
        "```\n",
        "Primeiramente, vamos fazer uma distinção para os dados de Acessos e Número de Assistentes, pois é certo que existe uma linearidade ou proporcionalidade entre eles. Portanto, nas análises que se referem apenas a um deles.\n",
        "\n",
        "Para a Figura 36, é notável que os gráficos estão organizados de forma semelhante a uma tabela, cujas linhas são os indicadores (IND1 a IND8) e as colunas são os atributos de Renda, Acessos, habitantes e Velocidade (Thp).\n",
        "\n",
        "Quando observamos os gráficos verticalmente, ou seja, pelos atributos Renda, Acessos/Habitantes e Thp, verificamos um perfil de variância dos Resultados semelhantes para todos os Indicadores. Por exemplo, para os atributos Acessos/Habitantes, notamos que em todos os indicadores, a dispersão dos resultados ocorre com maior amplitude em torno das menores populações, que também são mais numerosas em quantidade de municípios. Entretanto, quando avaliamos pelo recorte de Renda_média, verifica-se uma maior variação de renda entre os municípios, contudo, sem grandes variações nos resultados dos Indicadores. Evidentemente, também há de se destacar que as maiores variações ocorrem mais concentradas nas menores rendas. Para o atributo Thp, que é um parâmetro de Rede, percebemos entre os indicadores gráficos ligeiramente diferentes, visto que o thp_médio é influenciado pelo parâmetro medido em cada indicador. Mesmo assim, percebemos, em média, que o comportamento dos indicadores varia pouco, o que indica estabilidade no comportamento da Rede.\n",
        "\n",
        "Continuando com a Operadora 1, a análise dos gráficos contidos nos dois links de arquivos em PDF que seguem a Figura 36. Salientamos que o conteúdo dos dois links é o mesmo, distinguindo-se pela plotagem das ANFs (que representam as regiões de DDD). Por exemplo, as ANFs do Estado de São Paulo variam de 11 (região da Grande São Paulo) até 19 (região de Campinas). No entanto, os gráficos contidos nos arquivos representam todas as ANFs para os Estados do Pará, Pernambuco, Goiás, Distrito Federal, São Paulo e Paraná.\n",
        "\n",
        "Os gráficos estão organizados em três blocos, páginas distintas, onde cada página contém todos os indicadores (IND3 a IND8) por um atributo (Renda, Thp ou Acesso).\n",
        "\n",
        "Na primeira página, avaliamos os Indicadores por Renda e os distribuímos por ANF. Aqui podemos perceber dois grupos distintos quando avaliamos o eixo da renda. À esquerda, onde a renda é menor, estão as ANFs dos Estados do Norte (91, 93 e 94) e Nordeste (81 e 87), e à direita, estão as ANFs dos Estados com maior renda. Também na visão do eixo dos indicadores, destacamos que as ANFs dos Estados do Norte possuem um desempenho inferior em relação às demais ANFs. Quando passamos para a página seguinte, que trata do Thp, também notamos a presença das ANFs da região Norte com desempenho inferior, porém, agora acompanhadas de outras ANFs eventualmente. Por fim, no recorte de Acessos, também há um destaque para as ANFs do Norte, especialmente no IND8, que trata da disponibilidade do serviço, onde se destaca um desempenho inferior.\n",
        "\n",
        "Nas Figuras 37 e 38, bem como nos links que as seguem, o leitor poderá verificar e realizar, com a análise metodológica, os insights para as demais Operadoras.\n",
        "\n",
        "\n",
        "### Resposta 3\n",
        "\n",
        "A resposta a questão 3 é um desdobramento da resposta a questão 2, ou seja é a evidência que podemos enrriquecer a avaliação dos indicadores do RQUAL agregando os recortes de Renda, Número de habitantes, \"Market Share\" através do Número de Acessos, Velocidade de Dados.\n",
        "\n",
        "\n",
        "## 5.4 Discussão Final\n",
        "\n",
        "Em resumo, as análises e respostas as questões elaboradas confirmam que é plenamente viável replicar as consultas dos indicadores IQS do RQUAL na nuvem, permitindo uma análise personalizada dos dados. Além disso, é possível enrriquecer ou aprimorar essas consultas com atributos e filtros adicionais, como tamanho da população, quantidade de assinantes e renda per capita, ampliando as oportunidades de exploração e compreensão dos indicadores. A segmentação de dados também se revelou importante, demonstrando padrões de comportamento distintos com base em atributos como renda, acessos e população. No entanto, vale destacar que a análise se concentrou nas três principais operadoras do SMP e evitou comparações diretas entre elas, reconhecendo a necessidade de considerar aspectos qualitativos e contestações dos dados. Em suma, esta análise fornece uma base incipiente para a compreensão e exploração dos indicadores IQS do RQUAL, com potencial de aplicação em diversos contextos,serviços e operadoras adicionais.\n",
        "\n",
        "Destacamos também a viabilidade de replicar consultas, acrescentar filtros, segmentar dados, além de ressaltar as suas limitações, o que é valioso para profissionais que buscam insights acionáveis a partir dos indicadores IQS do RQUAL, alinhando ao objetivo principal de explorar esses dados de forma mais personalizada e contextualizada.\n",
        "\n",
        "Importante mencionar que os dados utilizados foram restritos a apenas um subconjunto dos dados disponibilizados pela Anatel. Sendo perfeitamente possíve escalar o trabalho descrito acima para a totalidade dos dados disponibilizados pela Agência.\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "XY_YUtokPQLf"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "<!-- Quebra de página -->\n",
        "<div style=\"page-break-after: always;\"></div>\n"
      ],
      "metadata": {
        "id": "p1lqtJgWoCjb"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 6. Conclusão\n",
        "\n",
        "Antes de concluir este trabalho, é importante relembrar o objetivo principal, que era reproduzir os indicadores RQUAL-IQS da Anatel para o Serviço Móvel Pessoal, enquanto agregávamos novos atributos, como a Renda Média do Usuário, o Número de Acessos Móveis por Município, a Cobertura da Rede e a Velocidade de Navegação de Dados. Isso nos permitiu realizar avaliações detalhadas do comportamento desses indicadores, considerando os diferentes recortes adicionados. Especificamente para o MVP proposto durante o Sprint de Engenharia de Dados no curso de Ciência de Dados e Analytics da PUC-Rio, o requisito principal era construir todo o Pipeline de Dados usando tecnologia em nuvem, e neste trabalho, optamos pelo Google Cloud Console.\n",
        "\n",
        "Resumindo, podemos afirmar que ambos os objetivos, dentro do escopo e das restrições estabelecidas para o trabalho, foram completamente atendidos e detalhados no Capítulo 4, que trata da construção do Pipeline em Cloud, e no Capítulo 5, onde detalhamos os resultados obtidos. Além disso, demonstramos que é possível analisar a qualidade do serviço desde o nível municipal até agregações maiores, como Unidades Federativas (UF) e Áreas de Numeração Fixa (ANF). Isso inclui a detecção de comportamentos específicos para cada indicador e destaque para as Operadoras de Serviço. Como um insight deste trabalho, notamos que, para a Operadora 1, a região Norte do país pode ser um ponto de atenção e investigação em relação ao comportamento de sua rede. Existem outros pontos de destaque que podem ser observados em relação às outras operadoras, com base nos gráficos e recortes de dados gerados aqui, ou mesmo em novas agregações ou visualizações.\n",
        "\n",
        "Um aspecto relevante foi o impacto inicial de utilizar tecnologia em nuvem, especialmente para um usuário sem experiência nesse ambiente. No entanto, devido a abundância de recursos funcionais e uma extensa documentação disponível, essa curva de aprendizado foi rapidamente superada, permitindo a conclusão bem-sucedida do trabalho e a realização dos objetivos propostos.\n",
        "\n",
        "É fundamental mencionar que este trabalho se concentrou em um conjunto restrito de análises e dados, limitando-se a seis estados (PR, SP, GO, DF, PE e PA) e com um enfoque mais detalhado no estado de São Paulo. Uma possível evolução deste trabalho poderia incluir a correlação dos indicadores RQUAL-IQS com dados de sentimentos dos usuários, seja por meio da pesquisa da Anatel que resulta no Índice de Qualidade Percebida (IPQ) ou por meio de pesquisas em sites de reclamações e avaliações de satisfação disponíveis em sites especializados ou redes sociais.\n",
        "\n",
        "Obs: Cuidado com o uso excessivo da Instância do Data Fusion porque representa um grande consummo de créditos.\n"
      ],
      "metadata": {
        "id": "Szih70jfm-GQ"
      }
    }
  ]
}