06_receita_federal.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyMAwhbuuXJUysJhTscj8qFe",
      "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/luasampaio/data-engineering/blob/main/6_receita_federal.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "KsfHu-Nopok2"
      },
      "outputs": [],
      "source": [
        "import urllib.request\n",
        "from urllib.parse import urlparse\n",
        "import zipfile\n",
        "import os\n",
        "import pandas as pd\n",
        "from datetime import datetime, timedelta\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# prompt: salvar arquivos buscados\n",
        "\n",
        "import urllib.request\n",
        "from urllib.parse import urlparse\n",
        "import zipfile\n",
        "import os\n",
        "import pandas as pd\n",
        "from datetime import datetime, timedelta\n",
        "def get_data_ultima_atualizacao(url = 'https://dadosabertos.rfb.gov.br/CNPJ/'):\n",
        "\n",
        "    df_files = pd.read_html(url)[0][[\"Name\",\"Last modified\"]]\n",
        "\n",
        "    df_files = df_files[~df_files['Name'].isna()]\n",
        "\n",
        "    arquivos_buscados = ['Empresas', 'Socios', 'Estabelecimentos']\n",
        "\n",
        "    df_arquivos = df_files[df_files['Name'].str.contains('|'.join(arquivos_buscados))]\n",
        "\n",
        "    ultima_atualizacao = df_arquivos['Last modified'].max()[:10].replace('-','')\n",
        "\n",
        "    return ultima_atualizacao\n",
        "\n",
        "# COMMAND ----------\n",
        "\n",
        "data_ultima_atualização = get_data_ultima_atualizacao(url = 'https://dadosabertos.rfb.gov.br/CNPJ/')\n",
        "\n",
        "def download_and_extract_files(url = 'https://dadosabertos.rfb.gov.br/CNPJ/'):\n",
        "\n",
        "    df_files = pd.read_html(url)[0][[\"Name\",\"Last modified\"]]\n",
        "    df_files = df_files[~df_files['Name'].isna()]\n",
        "    arquivos_buscados = ['Empresas', 'Socios', 'Estabelecimentos']\n",
        "    df_arquivos = df_files[df_files['Name'].str.contains('|'.join(arquivos_buscados))]\n",
        "\n",
        "    for index, row in df_arquivos.iterrows():\n",
        "\n",
        "        file_url = url + row['Name']\n",
        "        parsed_url = urlparse(file_url)\n",
        "        filename = os.path.basename(parsed_url.path)\n",
        "\n",
        "        print(f'Downloading {filename}...')\n",
        "        urllib.request.urlretrieve(file_url, filename)\n",
        "\n",
        "        print(f'Extracting {filename}...')\n",
        "        with zipfile.ZipFile(filename, 'r') as zip_ref:\n",
        "            zip_ref.extractall('.')\n",
        "\n",
        "        print(f'{filename} downloaded and extracted successfully!\\n')\n",
        "\n",
        "download_and_extract_files(url = 'https://dadosabertos.rfb.gov.br/CNPJ/')\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "id": "ahF_ROVrqBO5",
        "outputId": "15afbc81-f65a-4889-f4a8-00f6689883a8"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Downloading Empresas0.zip...\n",
            "Extracting Empresas0.zip...\n",
            "Empresas0.zip downloaded and extracted successfully!\n",
            "\n",
            "Downloading Empresas1.zip...\n",
            "Extracting Empresas1.zip...\n",
            "Empresas1.zip downloaded and extracted successfully!\n",
            "\n",
            "Downloading Empresas2.zip...\n",
            "Extracting Empresas2.zip...\n",
            "Empresas2.zip downloaded and extracted successfully!\n",
            "\n",
            "Downloading Empresas3.zip...\n",
            "Extracting Empresas3.zip...\n",
            "Empresas3.zip downloaded and extracted successfully!\n",
            "\n",
            "Downloading Empresas4.zip...\n",
            "Extracting Empresas4.zip...\n",
            "Empresas4.zip downloaded and extracted successfully!\n",
            "\n",
            "Downloading Empresas5.zip...\n",
            "Extracting Empresas5.zip...\n",
            "Empresas5.zip downloaded and extracted successfully!\n",
            "\n",
            "Downloading Empresas6.zip...\n",
            "Extracting Empresas6.zip...\n",
            "Empresas6.zip downloaded and extracted successfully!\n",
            "\n",
            "Downloading Empresas7.zip...\n",
            "Extracting Empresas7.zip...\n",
            "Empresas7.zip downloaded and extracted successfully!\n",
            "\n",
            "Downloading Empresas8.zip...\n",
            "Extracting Empresas8.zip...\n",
            "Empresas8.zip downloaded and extracted successfully!\n",
            "\n",
            "Downloading Empresas9.zip...\n",
            "Extracting Empresas9.zip...\n",
            "Empresas9.zip downloaded and extracted successfully!\n",
            "\n",
            "Downloading Estabelecimentos0.zip...\n"
          ]
        },
        {
          "output_type": "error",
          "ename": "KeyboardInterrupt",
          "evalue": "",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-3-f53d413d05a5>\u001b[0m in \u001b[0;36m<cell line: 49>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     47\u001b[0m         \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mf'{filename} downloaded and extracted successfully!\\n'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     48\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 49\u001b[0;31m \u001b[0mdownload_and_extract_files\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0murl\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m'https://dadosabertos.rfb.gov.br/CNPJ/'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
            "\u001b[0;32m<ipython-input-3-f53d413d05a5>\u001b[0m in \u001b[0;36mdownload_and_extract_files\u001b[0;34m(url)\u001b[0m\n\u001b[1;32m     39\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     40\u001b[0m         \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mf'Downloading {filename}...'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 41\u001b[0;31m         \u001b[0murllib\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrequest\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0murlretrieve\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfile_url\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfilename\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     42\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     43\u001b[0m         \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mf'Extracting {filename}...'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/lib/python3.10/urllib/request.py\u001b[0m in \u001b[0;36murlretrieve\u001b[0;34m(url, filename, reporthook, data)\u001b[0m\n\u001b[1;32m    268\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    269\u001b[0m             \u001b[0;32mwhile\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 270\u001b[0;31m                 \u001b[0mblock\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mbs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    271\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mblock\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    272\u001b[0m                     \u001b[0;32mbreak\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/lib/python3.10/http/client.py\u001b[0m in \u001b[0;36mread\u001b[0;34m(self, amt)\u001b[0m\n\u001b[1;32m    464\u001b[0m                 \u001b[0;31m# clip the read to the \"end of response\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    465\u001b[0m                 \u001b[0mamt\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mlength\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 466\u001b[0;31m             \u001b[0ms\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mamt\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    467\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0ms\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mamt\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    468\u001b[0m                 \u001b[0;31m# Ideally, we would raise IncompleteRead if the content-length\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/lib/python3.10/socket.py\u001b[0m in \u001b[0;36mreadinto\u001b[0;34m(self, b)\u001b[0m\n\u001b[1;32m    703\u001b[0m         \u001b[0;32mwhile\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    704\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 705\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sock\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrecv_into\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    706\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mtimeout\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    707\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_timeout_occurred\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/lib/python3.10/ssl.py\u001b[0m in \u001b[0;36mrecv_into\u001b[0;34m(self, buffer, nbytes, flags)\u001b[0m\n\u001b[1;32m   1301\u001b[0m                   \u001b[0;34m\"non-zero flags not allowed in calls to recv_into() on %s\"\u001b[0m \u001b[0;34m%\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1302\u001b[0m                   self.__class__)\n\u001b[0;32m-> 1303\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnbytes\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbuffer\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1304\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1305\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0msuper\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrecv_into\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mbuffer\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mnbytes\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mflags\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/lib/python3.10/ssl.py\u001b[0m in \u001b[0;36mread\u001b[0;34m(self, len, buffer)\u001b[0m\n\u001b[1;32m   1157\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1158\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mbuffer\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1159\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sslobj\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlen\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbuffer\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1160\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1161\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sslobj\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlen\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "7D1tgUglqEoE"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}