2_EyCD_Clasificacion_Tumoresferas.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/luciabarg/datos_tumoresferas/blob/main/2_EyCD_Clasificacion_Tumoresferas.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-S_T19aNnGhb"
      },
      "source": [
        "\n",
        "<center>\n",
        "<h4>Universidad Nacional de Córdoba - Facultad de Matemática, Astronomía, Física y Computación</h4>\n",
        "<h3>Diplomatura en Ciencia de Datos, Aprendizaje Automático y sus Aplicaciones</h3>\n",
        " <h2>Mentoría: Clasificación de Tumoresferas </h2>\n",
        "</center>\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OCZIyaVatb3o"
      },
      "source": [
        "<a name=\"exploratory_data_analysis\"></a>\n",
        "#### **Práctico de Análisis Exploratorio y Curación**\n",
        "  \n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cIXIZi43XKcC"
      },
      "source": [
        "Importamos las librerías necesarias:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Ca29yxa57Sbz"
      },
      "outputs": [],
      "source": [
        "#! pip install SQLAlchemy"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "kdtKG4W2XL1d"
      },
      "outputs": [],
      "source": [
        "import numpy as np\n",
        "import pandas as pd\n",
        "\n",
        "from sqlalchemy import create_engine, text"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UBOGEc9m7Sb5"
      },
      "source": [
        "## Agrupaciones:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aesZowbr7Sb6"
      },
      "source": [
        "### SQL\n",
        "\n",
        "Vamos a explorar los datos por medio de algunas consultas (queries) al archivo original (con el que trabajaron en el TP1):\n",
        "\n",
        " * Cargar el archivo *'data/raw/fiji_datos_0al7mo_labels.csv'* a SQLite.\n",
        " * Realizar las siguientes consultas:\n",
        "\n",
        "    1) Verificar la cantidad de datos cargados (les debería dar 1018).\n",
        "  \n",
        "    2) Listar las primeras 7 líneas con las columnas *'Area'*, *'Round'* con alias *'redondez'*, *Diameter* con alias *'diametro'*, *n_diam* con alias  *poblacion_celular* y *esferoide*.\n",
        "     \n",
        "     Realizar la misma consulta pero filtrando por los días 3 y 5.\n",
        "  \n",
        "    3) Consultar los distintos días y aparte las etiquetas presentes. Contar cuántas filas hay por día y luego consultar cuantas son *esferoide = 'si'*.   \n",
        "    \n",
        "     Probar con GROUP BY de dos columnas si pueden contabilizar 'si' y 'no' por día.\n",
        "    \n",
        "    4 ) En algún día en particular (a partir del 3er día), consultar cuántos datos:\n",
        "        \n",
        "    * Son esferoides \"si\" y \"no\".        \n",
        "\n",
        "    * Tienen su diámetro entre 50 $\\mu$m y 200$\\mu$ m.\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Usando SQL en Python:"
      ],
      "metadata": {
        "id": "6EwDHCPAB66d"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "xa2UNOJ17Sb_",
        "outputId": "31db2cf4-9bbf-4c2e-9d17-7f0cf6f185c9",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:17:55,072 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:BEGIN (implicit)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:17:55,078 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"fiji_datos\")\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:PRAGMA main.table_info(\"fiji_datos\")\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:17:55,084 INFO sqlalchemy.engine.Engine [raw sql] ()\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:[raw sql] ()\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:17:55,087 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"fiji_datos\")\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:PRAGMA temp.table_info(\"fiji_datos\")\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:17:55,089 INFO sqlalchemy.engine.Engine [raw sql] ()\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:[raw sql] ()\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:17:55,092 INFO sqlalchemy.engine.Engine ROLLBACK\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:ROLLBACK\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:17:55,097 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:BEGIN (implicit)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:17:55,100 INFO sqlalchemy.engine.Engine \n",
            "CREATE TABLE fiji_datos (\n",
            "\t\"index\" BIGINT, \n",
            "\tlabels TEXT, \n",
            "\t\"Area\" FLOAT, \n",
            "\t\"X\" FLOAT, \n",
            "\t\"Y\" FLOAT, \n",
            "\t\"XM\" FLOAT, \n",
            "\t\"YM\" FLOAT, \n",
            "\t\"Perim.\" FLOAT, \n",
            "\t\"BX\" FLOAT, \n",
            "\t\"BY\" FLOAT, \n",
            "\t\"Width\" FLOAT, \n",
            "\t\"Height\" FLOAT, \n",
            "\t\"Circ.\" FLOAT, \n",
            "\t\"Feret\" FLOAT, \n",
            "\t\"FeretX\" BIGINT, \n",
            "\t\"FeretY\" BIGINT, \n",
            "\t\"FeretAngle\" FLOAT, \n",
            "\t\"MinFeret\" FLOAT, \n",
            "\t\"AR\" FLOAT, \n",
            "\t\"Round\" FLOAT, \n",
            "\t\"Solidity\" FLOAT, \n",
            "\t\"Esferoide\" TEXT, \n",
            "\tdia BIGINT, \n",
            "\t\"Diameter\" FLOAT, \n",
            "\tn_diam FLOAT\n",
            ")\n",
            "\n",
            "\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:\n",
            "CREATE TABLE fiji_datos (\n",
            "\t\"index\" BIGINT, \n",
            "\tlabels TEXT, \n",
            "\t\"Area\" FLOAT, \n",
            "\t\"X\" FLOAT, \n",
            "\t\"Y\" FLOAT, \n",
            "\t\"XM\" FLOAT, \n",
            "\t\"YM\" FLOAT, \n",
            "\t\"Perim.\" FLOAT, \n",
            "\t\"BX\" FLOAT, \n",
            "\t\"BY\" FLOAT, \n",
            "\t\"Width\" FLOAT, \n",
            "\t\"Height\" FLOAT, \n",
            "\t\"Circ.\" FLOAT, \n",
            "\t\"Feret\" FLOAT, \n",
            "\t\"FeretX\" BIGINT, \n",
            "\t\"FeretY\" BIGINT, \n",
            "\t\"FeretAngle\" FLOAT, \n",
            "\t\"MinFeret\" FLOAT, \n",
            "\t\"AR\" FLOAT, \n",
            "\t\"Round\" FLOAT, \n",
            "\t\"Solidity\" FLOAT, \n",
            "\t\"Esferoide\" TEXT, \n",
            "\tdia BIGINT, \n",
            "\t\"Diameter\" FLOAT, \n",
            "\tn_diam FLOAT\n",
            ")\n",
            "\n",
            "\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:17:55,103 INFO sqlalchemy.engine.Engine [no key 0.00228s] ()\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:[no key 0.00228s] ()\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:17:55,117 INFO sqlalchemy.engine.Engine CREATE INDEX ix_fiji_datos_index ON fiji_datos (\"index\")\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:CREATE INDEX ix_fiji_datos_index ON fiji_datos (\"index\")\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:17:55,119 INFO sqlalchemy.engine.Engine [no key 0.00211s] ()\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:[no key 0.00211s] ()\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:17:55,134 INFO sqlalchemy.engine.Engine COMMIT\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:COMMIT\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:17:55,146 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:BEGIN (implicit)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:17:55,175 INFO sqlalchemy.engine.Engine INSERT INTO fiji_datos (\"index\", labels, \"Area\", \"X\", \"Y\", \"XM\", \"YM\", \"Perim.\", \"BX\", \"BY\", \"Width\", \"Height\", \"Circ.\", \"Feret\", \"FeretX\", \"FeretY\", \"FeretAngle\", \"MinFeret\", \"AR\", \"Round\", \"Solidity\", \"Esferoide\", dia, \"Diameter\", n_diam) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:INSERT INTO fiji_datos (\"index\", labels, \"Area\", \"X\", \"Y\", \"XM\", \"YM\", \"Perim.\", \"BX\", \"BY\", \"Width\", \"Height\", \"Circ.\", \"Feret\", \"FeretX\", \"FeretY\", \"FeretAngle\", \"MinFeret\", \"AR\", \"Round\", \"Solidity\", \"Esferoide\", dia, \"Diameter\", n_diam) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:17:55,179 INFO sqlalchemy.engine.Engine [generated in 0.02093s] [(0, 'Esferas_BT474_dia_0_well_1_100X_1_blob_1', 324.444, 1129.2718, 102.2051, 1129.2718, 102.2051, 67.3603, 1118.8859, 92.3913, 21.0598, 19.7011, 0.8985, 22.2117, 1648, 157, 23.4287, 19.7011, 1.0653, 0.9387, 0.95, 'si', 0, 20.9564, 1.491356796983188), (1, 'Esferas_BT474_dia_0_well_1_100X_1_blob_2', 497.5115, 1517.5528, 126.1022, 1517.5528, 126.1022, 82.8004, 1504.7554, 113.4511, 25.8152, 25.1359, 0.9119, 26.8751, 2215, 192, 16.1443, 25.1289, 1.0475, 0.9546, 0.9569, 'si', 0, 26.002, 2.8487332309604083), (2, 'Esferas_BT474_dia_0_well_1_100X_1_blob_3', 282.9078, 1314.2815, 126.0988, 1314.2815, 126.0988, 62.1586, 1304.3478, 116.8478, 19.7011, 18.3424, 0.9201, 20.437, 1920, 189, 15.4222, 18.3424, 1.0395, 0.962, 0.9519, 'si', 0, 19.3897, 1.1812580710195366), (3, 'Esferas_BT474_dia_0_well_1_100X_1_blob_5', 500.7421, 1189.2841, 212.6155, 1189.2841, 212.6155, 84.5571, 1175.9511, 200.4076, 26.4946, 25.1359, 0.8801, 26.6336, 1731, 309, 174.144, 25.1359, 1.0278, 0.973, 0.9493, 'si', 0, 25.88475, 2.8103696347136573), (4, 'Esferas_BT474_dia_0_well_1_100X_1_blob_6', 492.8964, 1043.0782, 247.7667, 1043.0782, 247.7667, 82.0728, 1030.5707, 235.7337, 25.1359, 24.4565, 0.9195, 26.2232, 1520, 353, 143.42700000000005, 24.4565, 1.0186, 0.9817, 0.9643, 'si', 0, 25.33985, 2.6365963047688177), (5, 'Esferas_BT474_dia_0_well_1_100X_1_blob_7', 406.1319, 737.5332, 271.2736, 737.5332, 271.2736, 74.8814, 725.5435, 260.1902, 23.7772, 22.4185, 0.9102, 24.124, 1068, 401, 9.7276, 22.4185, 1.0489, 0.9533, 0.955, 'si', 0, 23.27125, 2.042164183920052), (6, 'Esferas_BT474_dia_0_well_1_100X_1_blob_8', 333.6742, 1123.7169, 318.7621, 1123.7169, 318.7621, 67.3603, 1112.7717, 309.1033, 21.7391, 19.7011, 0.9241, 21.9083, 1638, 470, 7.125, 19.7011, 1.0531, 0.9496, 0.9557, 'si', 0, 20.8047, 1.4592036020514103), (7, 'Esferas_BT474_dia_0_well_1_100X_1_blob_9', 385.8253, 739.2418, 346.0716, 739.2418, 346.0716, 73.1247, 727.5815, 334.9185, 23.0978, 22.4185, 0.9067, 23.7286, 1071, 504, 166.7595, 22.2892, 1.0607, 0.9428, 0.9489, 'si', 0, 23.0089, 1.973872468878176)  ... displaying 10 of 1018 total bound parameter sets ...  (1016, 'Esferas_BT474_dia_7_well_2_100X_6_blob_3', 531.202, 58.462, 261.9482, 58.462, 261.9482, 89.5939, 44.837, 248.6413, 26.4946, 26.4946, 0.8316, 31.2795, 66, 399, 34.3803, 24.978, 1.2569, 0.7956, 0.935, 'no', 7, 28.12875, 3.606474114918977), (1017, 'Esferas_BT474_dia_7_well_2_100X_6_blob_4', 1085.4797, 255.1506, 600.7884, 255.1506, 600.7884, 127.6657, 235.0543, 582.8804, 38.0435, 36.6848, 0.8369, 41.4903, 353, 863, 129.6855, 36.6296, 1.0724, 0.9325, 0.9544, 'no', 7, 39.05995, 9.65665173427902)]\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:[generated in 0.02093s] [(0, 'Esferas_BT474_dia_0_well_1_100X_1_blob_1', 324.444, 1129.2718, 102.2051, 1129.2718, 102.2051, 67.3603, 1118.8859, 92.3913, 21.0598, 19.7011, 0.8985, 22.2117, 1648, 157, 23.4287, 19.7011, 1.0653, 0.9387, 0.95, 'si', 0, 20.9564, 1.491356796983188), (1, 'Esferas_BT474_dia_0_well_1_100X_1_blob_2', 497.5115, 1517.5528, 126.1022, 1517.5528, 126.1022, 82.8004, 1504.7554, 113.4511, 25.8152, 25.1359, 0.9119, 26.8751, 2215, 192, 16.1443, 25.1289, 1.0475, 0.9546, 0.9569, 'si', 0, 26.002, 2.8487332309604083), (2, 'Esferas_BT474_dia_0_well_1_100X_1_blob_3', 282.9078, 1314.2815, 126.0988, 1314.2815, 126.0988, 62.1586, 1304.3478, 116.8478, 19.7011, 18.3424, 0.9201, 20.437, 1920, 189, 15.4222, 18.3424, 1.0395, 0.962, 0.9519, 'si', 0, 19.3897, 1.1812580710195366), (3, 'Esferas_BT474_dia_0_well_1_100X_1_blob_5', 500.7421, 1189.2841, 212.6155, 1189.2841, 212.6155, 84.5571, 1175.9511, 200.4076, 26.4946, 25.1359, 0.8801, 26.6336, 1731, 309, 174.144, 25.1359, 1.0278, 0.973, 0.9493, 'si', 0, 25.88475, 2.8103696347136573), (4, 'Esferas_BT474_dia_0_well_1_100X_1_blob_6', 492.8964, 1043.0782, 247.7667, 1043.0782, 247.7667, 82.0728, 1030.5707, 235.7337, 25.1359, 24.4565, 0.9195, 26.2232, 1520, 353, 143.42700000000005, 24.4565, 1.0186, 0.9817, 0.9643, 'si', 0, 25.33985, 2.6365963047688177), (5, 'Esferas_BT474_dia_0_well_1_100X_1_blob_7', 406.1319, 737.5332, 271.2736, 737.5332, 271.2736, 74.8814, 725.5435, 260.1902, 23.7772, 22.4185, 0.9102, 24.124, 1068, 401, 9.7276, 22.4185, 1.0489, 0.9533, 0.955, 'si', 0, 23.27125, 2.042164183920052), (6, 'Esferas_BT474_dia_0_well_1_100X_1_blob_8', 333.6742, 1123.7169, 318.7621, 1123.7169, 318.7621, 67.3603, 1112.7717, 309.1033, 21.7391, 19.7011, 0.9241, 21.9083, 1638, 470, 7.125, 19.7011, 1.0531, 0.9496, 0.9557, 'si', 0, 20.8047, 1.4592036020514103), (7, 'Esferas_BT474_dia_0_well_1_100X_1_blob_9', 385.8253, 739.2418, 346.0716, 739.2418, 346.0716, 73.1247, 727.5815, 334.9185, 23.0978, 22.4185, 0.9067, 23.7286, 1071, 504, 166.7595, 22.2892, 1.0607, 0.9428, 0.9489, 'si', 0, 23.0089, 1.973872468878176)  ... displaying 10 of 1018 total bound parameter sets ...  (1016, 'Esferas_BT474_dia_7_well_2_100X_6_blob_3', 531.202, 58.462, 261.9482, 58.462, 261.9482, 89.5939, 44.837, 248.6413, 26.4946, 26.4946, 0.8316, 31.2795, 66, 399, 34.3803, 24.978, 1.2569, 0.7956, 0.935, 'no', 7, 28.12875, 3.606474114918977), (1017, 'Esferas_BT474_dia_7_well_2_100X_6_blob_4', 1085.4797, 255.1506, 600.7884, 255.1506, 600.7884, 127.6657, 235.0543, 582.8804, 38.0435, 36.6848, 0.8369, 41.4903, 353, 863, 129.6855, 36.6296, 1.0724, 0.9325, 0.9544, 'no', 7, 39.05995, 9.65665173427902)]\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:17:55,192 INFO sqlalchemy.engine.Engine COMMIT\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:COMMIT\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "1018"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ],
      "source": [
        "## Para conectarse con sqlite:\n",
        "engine = create_engine('sqlite:///fiji_datos.sqlite3', echo=True)\n",
        "\n",
        "# Levantamos el archivo\n",
        "url = 'https://raw.githubusercontent.com/luciabarg/datos_tumoresferas/main/data/raw/fiji_datos_0al7mo_labels.csv'\n",
        "fiji_datos = pd.read_csv(url)\n",
        "\n",
        "fiji_datos.to_sql('fiji_datos', con=engine, if_exists=\"replace\")\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "aEpHBz5p7ScB",
        "outputId": "783b192c-8f0b-4645-d1f0-ff0d26eb8a4d",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "SELECT count(*) FROM fiji_datos;\n",
            "2023-07-02 19:18:05,311 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:BEGIN (implicit)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:18:05,315 INFO sqlalchemy.engine.Engine SELECT count(*) FROM fiji_datos;\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:SELECT count(*) FROM fiji_datos;\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:18:05,320 INFO sqlalchemy.engine.Engine [generated in 0.00930s] ()\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:[generated in 0.00930s] ()\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "   count(*)\n",
            "0      1018\n",
            "2023-07-02 19:18:05,332 INFO sqlalchemy.engine.Engine ROLLBACK\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:ROLLBACK\n"
          ]
        }
      ],
      "source": [
        "#Forma que les mostraron en la clase teórica:\n",
        "query1 = \"SELECT count(*) FROM fiji_datos;\"\n",
        "\n",
        "with engine.connect() as con:\n",
        "    print(query1)\n",
        "    rs = con.execute(text(query1))\n",
        "    df_rs = pd.DataFrame(rs.fetchall())\n",
        "    print(df_rs)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "aZ4hcqo27ScB",
        "outputId": "c204e6d3-b7c4-4c3f-b231-4eedf0986c66",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 220
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:22:15,466 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:BEGIN (implicit)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:22:15,476 INFO sqlalchemy.engine.Engine  SELECT count(*) FROM fiji_datos; \n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine: SELECT count(*) FROM fiji_datos; \n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:22:15,480 INFO sqlalchemy.engine.Engine [cached since 82.48s ago] ()\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:[cached since 82.48s ago] ()\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2023-07-02 19:22:15,483 INFO sqlalchemy.engine.Engine ROLLBACK\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "INFO:sqlalchemy.engine.Engine:ROLLBACK\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   count(*)\n",
              "0      1018"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-cef6665a-2933-4a50-acbe-6c6a550345e6\">\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>count(*)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1018</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-cef6665a-2933-4a50-acbe-6c6a550345e6')\"\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-cef6665a-2933-4a50-acbe-6c6a550345e6 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-cef6665a-2933-4a50-acbe-6c6a550345e6');\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": 13
        }
      ],
      "source": [
        "# Otra forma alternativa de usar sqlalchemy en Python\n",
        "\n",
        "query1 = \" SELECT count(*) FROM fiji_datos; \"\n",
        "with engine.connect() as con:\n",
        "  query_result1 = pd.read_sql_query(text(query1), con)\n",
        "\n",
        "query_result1"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### También pueden usar la versión [**gráfica online**](https://sqliteonline.com/) de SQLite ([SQLite instructivo](https://drive.google.com/drive/folders/1iW5OaNaNDafU4e4m87xID7HcEbvb1W0V?usp=drive_link) )\n"
      ],
      "metadata": {
        "id": "9EgdETIZECQu"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eRSwbb4n7ScC"
      },
      "source": [
        "Extras que podrían ayudar:\n",
        "\n",
        "* Documentación [*pandas.DataFrame.to_sql*](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html) y [*pandas.read_sql_query*](https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html).\n",
        "\n",
        "\n",
        "* [Recursos SQL](https://drive.google.com/drive/folders/1EDSgMGbrRjNZX7m-GXunkQjNXUxY1NSn?usp=drive_link).\n",
        "    \n",
        "* Presentación en [MeTCamp](https://docs.google.com/presentation/d/1URSQt1sJ8Th8Y4J62zBv9x3I1lV0rckT/edit?usp=sharing&ouid=107018266094379471830&rtpof=true&sd=true).\n",
        "\n",
        "* Súper súper buenas prácticas en [este repositorio](https://github.com/daianadte/wids-cba-2023).\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0qUyC2mB7ScC"
      },
      "source": [
        "### Python\n",
        "Probar diferentes agrupaciones en Python."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "S3CfhMGP7ScD",
        "outputId": "1ca9bb99-50f3-444c-c8dc-02b787026262"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "<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 tr th {\n",
              "        text-align: left;\n",
              "    }\n",
              "\n",
              "    .dataframe thead tr:last-of-type th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th></th>\n",
              "      <th>Esferoide</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th></th>\n",
              "      <th>count</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>dia</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>94</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>342</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>155</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>43</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>254</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>52</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>74</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    Esferoide\n",
              "        count\n",
              "dia          \n",
              "0          94\n",
              "1         342\n",
              "2         155\n",
              "3          43\n",
              "4         254\n",
              "5          52\n",
              "6           4\n",
              "7          74"
            ]
          },
          "execution_count": 5,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "#Por ej:\n",
        "fiji_datos.groupby('dia').agg({'Esferoide': ['count']}).sort_values(by = 'dia')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TaS2NrsG7ScD"
      },
      "source": [
        "## Análisis y Curación de Datos"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3Tp52fqQDfvB"
      },
      "source": [
        "1) Para empezar a trabajar la tabla, primero hay que combinar los datasets, que fueron separados por día.\n",
        "\n",
        "Estos son los siguientes archivos a unir:\n",
        "\n",
        "*   \"fiji_datos_0al7mo_modificado_dia_0.csv\"\n",
        "*   \"fiji_datos_0al7mo_modificado_dia_1.csv\"\n",
        "*   \"fiji_datos_0al7mo_modificado_dia_2.csv\"\n",
        "*   \"fiji_datos_0al7mo_modificado_dia_3.csv\"\n",
        "*   \"fiji_datos_0al7mo_modificado_dia_4.csv\"\n",
        "*   \"fiji_datos_0al7mo_modificado_dia_5.csv\"\n",
        "*   \"fiji_datos_0al7mo_modificado_dia_6.csv\"\n",
        "*   \"fiji_datos_0al7mo_modificado_dia_7a.csv\"\n",
        "*   \"fiji_datos_0al7mo_modificado_dia_7b.csv\"\n",
        "\n",
        "Se encuentran en la carpeta: **\"data/02_EyC\"** de la [carpeta compartida](https://drive.google.com/drive/folders/1RqGNySwACN33Qopmw0nHmj5Yv4M78ZXi?usp=drive_link) y en este repositorio.\n",
        "\n",
        "\n",
        "Probar hacerlo con Python y/o SQL (por separado, para probar!). Verificar que llegan a la misma cantidad de filas y columnas.\n",
        "\n",
        "2) Una vez armado el dataset modificado, explorar el dataset y buscar inconsistencias, por ej si hay valores nulos, duplicados, etc, tratando de encontrar todas las inconsistencias en los datos. Decidir el orden en cuál ir arreglándolas.\n",
        "\n",
        "3) Corregir las inconsistencias que van encontrando y sobre los datos faltantes (¡verificar primero si los hay!), determinar cuál método sería el mas adecuado para imputarlos, teniendo en cuenta lo que aprendieron sobre los datos en el práctico de *Análisis y Visualización*.\n",
        "\n",
        "4) Una vez que recuperan el dataset original, repasando lo que les dieron en la materia de *Exploración y Curación de datos* y ya pensando en las transformaciones que puedan servirles y que les conviene realizar para la clasificación en el práctico de aprendizaje supervisado:\n",
        "\n",
        "  * ¿Sobre cuáles columnas realizarían encoding y de qué tipo?. Elegir un método e implementarlo.\n",
        "\n",
        "  * Tenemos muchas columnas (¡aunque siempre puede haber mas!) por lo que es posible realizar algún método de reducción de dimensionalidad.\n",
        "\n",
        "  * Escalar y/o normalizar? Esa es la cuestión. Teniendo en cuenta lo aprendido a partir de los datos, cuál método se ajustaría a estos datos?\n",
        "\n",
        "  Sumar tales transformaciones como columnas para sumar características al conjunto de datos y guardar el achivo modificado.\n",
        "\n",
        "Si se traban mucho mucho en la limpieza de los datos, este paso pueden hacerlo con el dataset original que usaron en el práctico 1.\n",
        "\n",
        "5) Pueden identificar sesgos en la toma de los datos o en el etiquetado? Si les parece que es así, cómo sería un experimento o método para mejorarlos?  \n",
        "\n",
        "6) Realizar una documentación técnica de los procesos realizados.\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "\n",
        "### OPCIONAL\n",
        "\n",
        "7) Explorar la carpeta *py_folder*. El archivo *main.py* es solo un borrador. Agregar alguna función de transformación en el módulo de eda y que puedan ejecutarla corriendo el archivo.\n",
        "\n",
        " Para correr el archivo main, en una terminal:\n",
        "\n",
        "    (.venv) $ python main.py\n",
        "\n",
        " [Pdoc](https://pdoc.dev/) es una elección común para la documentación en Python. Para usarlo en este proyecto (está un poco personalizado):\n",
        "\n",
        "    1) Instalarlo:\n",
        "       (.venv) $ pip install pdoc\n",
        "    2) Crear la carpeta con la documentación\n",
        "       (.venv) $ pdoc main.py utils/ -o ./documentacion/Docs -t ./documentacion/pdoc_templates/\n"
      ],
      "metadata": {
        "id": "tPPVaduF7h1k"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Para usar desde Colab, ir hasta la [carpeta compartida](https://drive.google.com/drive/folders/1RqGNySwACN33Qopmw0nHmj5Yv4M78ZXi) y copiarla a su Drive."
      ],
      "metadata": {
        "id": "wgZfaXLF-h1D"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import os\n",
        "os.chdir(\"/content/drive/My Drive/Datos_tumoresferas/py_folder\")"
      ],
      "metadata": {
        "id": "j2OkOObv7sSv"
      },
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "! python main.py"
      ],
      "metadata": {
        "id": "xRs2JNcW_GG2",
        "outputId": "bcc16082-3d78-4d42-b5c1-75fcd6bc2de4",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "local_dir /content/drive/My Drive/Datos_tumoresferas/py_folder/main.py\n",
            "data_dir /content/drive/My Drive/Datos_tumoresferas/py_folder/data\n",
            "2023-07-17 22:33:19,233 - Main - Running Main experiment file\n",
            "2023-07-17 22:33:19,234 - read_dataset - Cargando el conjunto de datos...\n",
            "2023-07-17 22:33:19,400 - read_dataset - shapes:(1018, 24)\n",
            "2023-07-17 22:33:19,400 - eda_utils - Beginning of the process of transforming columns \n",
            "2023-07-17 22:33:19,400 - eda_utils - Initial shape of df = (1018, 24)\n",
            "2023-07-17 22:33:19,400 - eda_utils - columns to drop:= ['X', 'Y', 'XM', 'YM', 'BX', 'BY', 'FeretX', 'FeretY', 'Width', 'Height', 'Feret', 'FeretAngle', 'MinFeret', 'AR']\n",
            "2023-07-17 22:33:19,402 - eda_utils - Final shape of df = (1018, 10)\n",
            "2023-07-17 22:33:19,402 - eda_utils - len and list of columns dropped: len = 14,  list: ['X', 'Y', 'XM', 'YM', 'BX', 'BY', 'FeretX', 'FeretY', 'Width', 'Height', 'Feret', 'FeretAngle', 'MinFeret', 'AR'] \n",
            "2023-07-17 22:33:19,402 - eda_utils - Shape final de dataframe modificado: (1018, 10)\n",
            "2023-07-17 22:33:19,402 - eda_utils - Comparing the columns of two dataframes:\n",
            "2023-07-17 22:33:19,402 - eda_utils - extra_columns in 1st dataframe : {'Y', 'BY', 'AR', 'FeretX', 'Feret', 'Width', 'YM', 'XM', 'Height', 'FeretY', 'MinFeret', 'X', 'FeretAngle', 'BX'}\n",
            "2023-07-17 22:33:19,403 - eda_utils - There are not extra_columns in 2nd dataframe:\n",
            "2023-07-17 22:33:19,403 - eda_utils - Final dataframe has the following columns: {'Esferoide', 'Round', 'Area', 'labels', 'dia', 'Circ.', 'Perim.', 'Solidity', 'n_diam', 'Diameter'}\n",
            "2023-07-17 22:33:19,404 - eda_utils - shape of df_mod_si = (522, 10)\n",
            "2023-07-17 22:33:19,405 - eda_utils - shape of df_mod_no = (496, 10)\n",
            "2023-07-17 22:33:19,405 - eda_utils - Saving preprocesed dataframe to csv to further analysis and modeling\n",
            "data_return.shape (1018, 10)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Para la documentación:"
      ],
      "metadata": {
        "id": "YtXMfNud_QLa"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "! pip install pdoc"
      ],
      "metadata": {
        "id": "rvQdoAGM_Ivp"
      },
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "! pdoc main.py utils/ -o ./documentacion/Docs -t ./documentacion/pdoc_templates/"
      ],
      "metadata": {
        "id": "bbLAJw4H_MRG"
      },
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Acá aparece la carpeta Docs en el Drive y para ver los htmls tienen que descargarlas."
      ],
      "metadata": {
        "id": "pL8QXcDRB8OU"
      }
    }
  ],
  "metadata": {
    "colab": {
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "display_name": "Python 3 (ipykernel)",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.8.10"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}