csv column converter for centro de vigilancia epidemiologica - Gov.SP.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true,
      "authorship_tag": "ABX9TyM3MKsYK4+PBWT6y9esKMLP",
      "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/Edgard-Lopes/dengue/blob/main/csv%20column%20converter%20for%20centro%20de%20vigilancia%20epidemiologica%20-%20Gov.SP.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Import"
      ],
      "metadata": {
        "id": "kfdJzzkHn-Ok"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "import csv"
      ],
      "metadata": {
        "id": "_hW5x8L-oAcF"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Variables"
      ],
      "metadata": {
        "id": "CKUraZOtn-7e"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "fields = [\n",
        "            \"Id_DRS\",\n",
        "            \"DRS\",\n",
        "            \"ID_GVE\",\n",
        "            \"GVE\",\n",
        "            \"ID_RdS\",\n",
        "            \"RdS\",\n",
        "            \"Id_Município\",\n",
        "            \"Município\",\n",
        "            \"Data\",\n",
        "            \"notificados\",\n",
        "            \"confirm_autoc\",\n",
        "            \"confirm_import\"]\n",
        "head_lines = {\n",
        "            \"Id_DRS\": [],       #posição 0 in final_line\n",
        "            \"DRS\": [],          #posição 1\n",
        "            \"ID_GVE\": [],       #posição 2\n",
        "            \"GVE\": [],          #posição 3\n",
        "            \"ID_RdS\": [],       #posição 4\n",
        "            \"RdS\": [],          #posição 5\n",
        "            \"Id_Município\": [], #posição 6\n",
        "            \"Município\": [],    #posição 7\n",
        "            }\n",
        "head_lines_part2 = {\n",
        "        \"data\": [],\n",
        "        \"notificados\": [],\n",
        "        \"confirm_autoc\": [],\n",
        "        \"confirm_import\":  []\n",
        "        }\n"
      ],
      "metadata": {
        "id": "4et7RVgjoBPF"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Body"
      ],
      "metadata": {
        "id": "5SpBTlQ6n_CN"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 211
        },
        "id": "w2Tuv1N9mdBB",
        "outputId": "72759d46-71b6-4e7d-bfb3-d4ea9af6ded6"
      },
      "outputs": [
        {
          "output_type": "error",
          "ename": "FileNotFoundError",
          "evalue": "[Errno 2] No such file or directory: '/sitedenguejan.dez.2022.entrega13.02.23.xlsx%3Fattach=true2.csv'",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-1-9266e6bcb50e>\u001b[0m in \u001b[0;36m<cell line: 39>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     37\u001b[0m \u001b[0mfile_path\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m\"\"\u001b[0m \u001b[0;31m#introduce file path in tour computer\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     38\u001b[0m \u001b[0mfile\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m\"sitedenguejan.dez.2022.entrega13.02.23.xlsx%3Fattach=true2.csv\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 39\u001b[0;31m \u001b[0;32mwith\u001b[0m \u001b[0mopen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfile_path\u001b[0m\u001b[0;34m+\u001b[0m\u001b[0;34m\"/\"\u001b[0m\u001b[0;34m+\u001b[0m\u001b[0mfile\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"r\"\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0marquivo\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     40\u001b[0m     \u001b[0marquivo_csv\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcsv\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreader\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marquivo\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdelimiter\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\",\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     41\u001b[0m     \u001b[0mlist\u001b[0m \u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: '/sitedenguejan.dez.2022.entrega13.02.23.xlsx%3Fattach=true2.csv'"
          ]
        }
      ],
      "source": [
        "#used for csv converted excel spreadsheet in www.saude.sp.gov.brcve-centro-de-vigilancia-epidemiologica-prof.-alexandre-vranja\n",
        "#the head lines in the excel are merged, making it difficult to directly use it, so I made this program to convert it into a funcitonal data\n",
        "#so I can use it in Power BI for a personal project\n",
        "\n",
        "file_path = \"\" #introduce file path in tour computer\n",
        "file = \"sitedenguejan.dez.2022.entrega13.02.23.xlsx%3Fattach=true2.csv\"\n",
        "with open(file_path+\"/\"+file, \"r\") as arquivo:\n",
        "    arquivo_csv = csv.reader(arquivo, delimiter=\",\")\n",
        "    list =[]\n",
        "    for i, linha in enumerate(arquivo_csv):\n",
        "        list.append(str(linha))\n",
        "date = (file.split(\".\"))[2]\n",
        "\n",
        "count = 0\n",
        "while count < 5:\n",
        "    del list[0]\n",
        "    del list[-1]\n",
        "    count = count + 1\n",
        "while count < 6:\n",
        "    del list[-1]\n",
        "    count = count + 1\n",
        "\n",
        "final_line = []\n",
        "counter = 0\n",
        "for lista in list:\n",
        "    x = list[counter][:-17]\n",
        "    counter = counter + 1\n",
        "    x = x[2:]\n",
        "    x = x.split(\";\")\n",
        "    y = x[6].split(\" \")\n",
        "    final_line = x[0:6] + y + x[7:-3] #remove as 3 últimas colunas que estão com as somas da linha\n",
        "    lines = []\n",
        "    item = 0\n",
        "\n",
        "    count = 0\n",
        "    while count <12:\n",
        "        for k, value in head_lines.items():\n",
        "                lines = value\n",
        "                lines.append(final_line[item])\n",
        "                head_lines[k] = lines\n",
        "                if item == 7:\n",
        "                    item = 0;\n",
        "                else:\n",
        "                    item = item + 1\n",
        "\n",
        "\n",
        "        lines = head_lines_part2[\"data\"]\n",
        "        lines.append(\"1\"+\"/\"+ str(count+1) + \"/\"+ date)\n",
        "        head_lines_part2[\"data\"] = lines\n",
        "        multiplier = count*3\n",
        "        #print(head_lines_part2[\"notificados\"])\n",
        "        lines = (head_lines_part2[\"notificados\"])\n",
        "        lines.append(final_line[8+multiplier])\n",
        "        head_lines_part2[\"notificados\"] = lines\n",
        "        lines = (head_lines_part2[\"confirm_autoc\"])\n",
        "        lines.append(final_line[9+multiplier])\n",
        "        head_lines_part2[\"confirm_autoc\"]  = lines\n",
        "        lines = (head_lines_part2[\"confirm_import\"])\n",
        "        lines.append(final_line[10+multiplier])\n",
        "        head_lines_part2[\"confirm_import\"] = lines\n",
        "\n",
        "        count = count + 1\n",
        "\n",
        "head_lines.update(head_lines_part2)\n",
        "print(pd.DataFrame(head_lines))\n",
        "\n",
        "#file = file[:-23] + \"convertido\" + \".csv\"\n",
        "#print(\"Abrindo arquivo: \", file)"
      ]
    }
  ]
}