sinasc.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type='text/css'>\n",
       ".datatable table.frame { margin-bottom: 0; }\n",
       ".datatable table.frame thead { border-bottom: none; }\n",
       ".datatable table.frame tr.coltypes td {  color: #FFFFFF;  line-height: 6px;  padding: 0 0.5em;}\n",
       ".datatable .bool    { background: #DDDD99; }\n",
       ".datatable .object  { background: #565656; }\n",
       ".datatable .int     { background: #5D9E5D; }\n",
       ".datatable .float   { background: #4040CC; }\n",
       ".datatable .str     { background: #CC4040; }\n",
       ".datatable .time    { background: #40CC40; }\n",
       ".datatable .row_index {  background: var(--jp-border-color3);  border-right: 1px solid var(--jp-border-color0);  color: var(--jp-ui-font-color3);  font-size: 9px;}\n",
       ".datatable .frame tbody td { text-align: left; }\n",
       ".datatable .frame tr.coltypes .row_index {  background: var(--jp-border-color0);}\n",
       ".datatable th:nth-child(2) { padding-left: 12px; }\n",
       ".datatable .hellipsis {  color: var(--jp-cell-editor-border-color);}\n",
       ".datatable .vellipsis {  background: var(--jp-layout-color0);  color: var(--jp-cell-editor-border-color);}\n",
       ".datatable .na {  color: var(--jp-cell-editor-border-color);  font-size: 80%;}\n",
       ".datatable .sp {  opacity: 0.25;}\n",
       ".datatable .footer { font-size: 9px; }\n",
       ".datatable .frame_dimensions {  background: var(--jp-border-color3);  border-top: 1px solid var(--jp-border-color0);  color: var(--jp-ui-font-color3);  display: inline-block;  opacity: 0.6;  padding: 1px 10px 1px 5px;}\n",
       "</style>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import sqlite3\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import config, data_load\n",
    "from datatable import dt, f\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "cache_length = 25\n",
    "con = data_load.create_connection()\n",
    "path_zip = config.PATH_DATABASE_ZIP\n",
    "files = data_load.get_namelist_in_zip(path_zip)\n",
    "sinasc_cols = pd.read_csv('data/check/sinasc_cols_count.csv')['variavel'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def adjust_df(df, sinasc_cols):\n",
    "\tdf = df.to_pandas()\n",
    "\tfor col in sinasc_cols:\n",
    "\t\tif col not in df.columns:\n",
    "\t\t\tdf[col] = np.nan\n",
    "\tdf = df.drop(['CONTADOR'], axis=1)\n",
    "\treturn df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 15%|█▌        | 103/675 [02:43<09:50,  1.03s/it] "
     ]
    }
   ],
   "source": [
    "with (con):\n",
    "\tdfs = list()\n",
    "\tfor f in tqdm(files):\n",
    "\t\tdf = dt.fread(f'{path_zip}/{f}')\n",
    "\t\tdf = adjust_df(df, sinasc_cols)\n",
    "\t\tdfs.append(df)\n",
    "\t\tif len(dfs) > cache_length:\n",
    "\t\t\tdata_load.append_table_to_con(\n",
    "\t\t\t\tpd.concat(dfs),con)\n",
    "\t\t\tdfs = list()\n",
    "\tdata_load.append_table_to_con(\n",
    "\t\t\tpd.concat(dfs),con)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "15% = `1.82GB`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cnx = sqlite3.connect(config.PATH_DB)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df = pd.read_sql_query('select * from sinasc order by data_nasc, DTNASCMAE limit 5', cnx)\n",
    "# df[sorted(df.columns)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sinasc_cols = pd.read_csv('data/check/sinasc_cols_count.csv')\n",
    "sinasc_cols = sinasc_cols[sinasc_cols['percent'] >= 80]['variavel'].values\n",
    "path_query = 'queries/check/sinasc.sql'\n",
    "with open(path_query) as f:\n",
    "\tquery_base = f.read()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(sinasc_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "counts = list()\n",
    "for var in tqdm(sinasc_cols):\n",
    "\tquery = query_base.replace('*', var)\n",
    "\trecords = data_load.df_query(query)['records'].values[0]\n",
    "\tcounts.append({\n",
    "\t\t'variavel' : var,\n",
    "\t\t'recods' : records})\n",
    "df_counts = pd.DataFrame(counts).sort_values(by='recods', ascending=False)\n",
    "df = df_counts.sort_values(\n",
    "\tby=['recods', 'variavel'], ascending=[False, True]\n",
    ")\n",
    "df['percent'] = df['recods'] / 28672255 * 100\n",
    "\n",
    "df.to_csv('data/check/sinasc_cols_count.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "path_zip = config.PATH_DATABASE_ZIP\n",
    "files = data_load.get_namelist_in_zip(path_zip)\n",
    "pd.Series(files)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = dt.fread(f'{path_zip}/{files[2]}')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "d = list()\n",
    "for f in tqdm(files):\n",
    "\tdf = dt.fread(f'{path_zip}/{f}')\n",
    "\trows, columns = df.shape\n",
    "\td.append({'rows':rows, 'columns':columns})\n",
    "df_shapes = pd.DataFrame(d)\n",
    "df_shapes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_shapes.rows.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with (con := data_load.create_connection()):\n",
    "\tfor f in tqdm(files[:10]):\n",
    "\t\tdf = dt.fread(f'{path_zip}/{f}')\n",
    "\t\tdf = df.to_pandas()\n",
    "\t\tdf = df[sinasc_cols]\n",
    "\t\t# df.to_csv(f'{config.PATH_DATABASE}/csvs/{f}', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with (con := data_load.create_connection()):\n",
    "\tdfs = list()\n",
    "\tfor f in tqdm(files):\n",
    "\t\tdf = dt.fread(f'{path_zip}/{f}')\n",
    "\t\tdf = df.to_pandas()\n",
    "\t\tfor col in sinasc_cols:\n",
    "\t\t\tif col not in df.columns:\n",
    "\t\t\t\tdf[col] = np.nan\n",
    "\t\tdf = df.drop(['CONTADOR'], axis=1)\n",
    "\t\tdfs.append(df)\n",
    "\t\tif len(dfs) > 50:\n",
    "\t\t\tdf.to_sql(\n",
    "\t\t\t\tname = 'sinasc',\n",
    "\t\t\t\tcon = con,\n",
    "\t\t\t\tif_exists = 'append', # append | replace\n",
    "\t\t\t\tindex = False,\n",
    "\t\t\t)\n",
    "\t\t\tdfs = list()\n",
    "\ttry:\n",
    "\t\tdf.to_sql(\n",
    "\t\t\tname = 'sinasc',\n",
    "\t\t\tcon = con,\n",
    "\t\t\tif_exists = 'append', # append | replace\n",
    "\t\t\tindex = False,\n",
    "\t\t)\n",
    "\texcept Exception as exc:\n",
    "\t\tprint(exc)\n",
    "\t\tprint(df.columns)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.0rc2 ('venv': venv)",
   "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.10.0"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "f601cc5507805c915a29cbc7efc7fe46bb00e1fada85c390a8ea6a8a3967e90a"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}