Declaracion_patrimonio.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                                        1   \\\n",
      "0                                                      NaN   \n",
      "1           Declaración patrimonial de la iniciativa #3de3   \n",
      "2                                                      NaN   \n",
      "3        Favor de NO eliminar ninguna fila o celda del ...   \n",
      "4                                                      NaN   \n",
      "5                                  I. DATOS DEL DECLARANTE   \n",
      "6        En esta sección se encuentra la información qu...   \n",
      "7                                                  Nombre:   \n",
      "8                                                   Poder:   \n",
      "9                                                  Ámbito:   \n",
      "10                      Entidad de gobierno o institución:   \n",
      "11                           Cargo que desempeña o aspira:   \n",
      "12                                                     NaN   \n",
      "13       II. DATOS DEL CÓNYUGE, CONCUBINA/CONCUBINARIO ...   \n",
      "14       Complete la siguiente información que identifi...   \n",
      "15       Enliste e indique la relación entre el declara...   \n",
      "16                                                     NaN   \n",
      "17                                                     NaN   \n",
      "18                                                     NaN   \n",
      "19                                                     NaN   \n",
      "20                                                     NaN   \n",
      "21                                                     NaN   \n",
      "22                                                     NaN   \n",
      "23                                                     NaN   \n",
      "24                                III. DATOS SOBRE INGRESO   \n",
      "25       En esta sección se reporta la información sobr...   \n",
      "26       1. Ingreso neto anual del DECLARANTE entre el ...   \n",
      "27       El ingreso neto anual del declarante se refier...   \n",
      "28                                                     NaN   \n",
      "29       Remuneración neta anual del declarante por car...   \n",
      "...                                                    ...   \n",
      "1048546                                                NaN   \n",
      "1048547                                                NaN   \n",
      "1048548                                                NaN   \n",
      "1048549                                                NaN   \n",
      "1048550                                                NaN   \n",
      "1048551                                                NaN   \n",
      "1048552                                                NaN   \n",
      "1048553                                                NaN   \n",
      "1048554                                                NaN   \n",
      "1048555                                                NaN   \n",
      "1048556                                                NaN   \n",
      "1048557                                                NaN   \n",
      "1048558                                                NaN   \n",
      "1048559                                                NaN   \n",
      "1048560                                                NaN   \n",
      "1048561                                                NaN   \n",
      "1048562                                                NaN   \n",
      "1048563                                                NaN   \n",
      "1048564                                                NaN   \n",
      "1048565                                                NaN   \n",
      "1048566                                                NaN   \n",
      "1048567                                                NaN   \n",
      "1048568                                                NaN   \n",
      "1048569                                                NaN   \n",
      "1048570                                                NaN   \n",
      "1048571                                                NaN   \n",
      "1048572                                                NaN   \n",
      "1048573                                                NaN   \n",
      "1048574                                                NaN   \n",
      "1048575                                                      \n",
      "\n",
      "                                                        2             3    4   \\\n",
      "0                                                      NaN           NaN  NaN   \n",
      "1                                                      NaN           NaN  NaN   \n",
      "2                                                      NaN           NaN  NaN   \n",
      "3                                                      NaN           NaN  NaN   \n",
      "4                                                      NaN           NaN  NaN   \n",
      "5                                                      NaN           NaN  NaN   \n",
      "6                                                      NaN           NaN  NaN   \n",
      "7                            Norma Angélica Licona Alberto           NaN  NaN   \n",
      "8                                                Ejecutivo           NaN  NaN   \n",
      "9                                                  Estatal           NaN  NaN   \n",
      "10                                 Secretaría de Gobierno            NaN  NaN   \n",
      "11       Directora General de Desarrollo Institucional ...           NaN  NaN   \n",
      "12                                                     NaN           NaN  NaN   \n",
      "13                                                     NaN           NaN  NaN   \n",
      "14                                                     NaN           NaN  NaN   \n",
      "15                                                     NaN  Nacionalidad  NaN   \n",
      "16                                                     NaN           NaN  NaN   \n",
      "17                                                     NaN           NaN  NaN   \n",
      "18                                                     NaN           NaN  NaN   \n",
      "19                                                     NaN           NaN  NaN   \n",
      "20                                                     NaN           NaN  NaN   \n",
      "21                                                     NaN           NaN  NaN   \n",
      "22                                                     NaN           NaN  NaN   \n",
      "23                                                     NaN           NaN  NaN   \n",
      "24                                                     NaN           NaN  NaN   \n",
      "25                                                     NaN           NaN  NaN   \n",
      "26                                                     NaN           NaN  NaN   \n",
      "27                                                     NaN           NaN  NaN   \n",
      "28                                                     NaN           NaN  NaN   \n",
      "29                                                     NaN           NaN  NaN   \n",
      "...                                                    ...           ...  ...   \n",
      "1048546                                                NaN           NaN  NaN   \n",
      "1048547                                                NaN           NaN  NaN   \n",
      "1048548                                                NaN           NaN  NaN   \n",
      "1048549                                                NaN           NaN  NaN   \n",
      "1048550                                                NaN           NaN  NaN   \n",
      "1048551                                                NaN           NaN  NaN   \n",
      "1048552                                                NaN           NaN  NaN   \n",
      "1048553                                                NaN           NaN  NaN   \n",
      "1048554                                                NaN           NaN  NaN   \n",
      "1048555                                                NaN           NaN  NaN   \n",
      "1048556                                                NaN           NaN  NaN   \n",
      "1048557                                                NaN           NaN  NaN   \n",
      "1048558                                                NaN           NaN  NaN   \n",
      "1048559                                                NaN           NaN  NaN   \n",
      "1048560                                                NaN           NaN  NaN   \n",
      "1048561                                                NaN           NaN  NaN   \n",
      "1048562                                                NaN           NaN  NaN   \n",
      "1048563                                                NaN           NaN  NaN   \n",
      "1048564                                                NaN           NaN  NaN   \n",
      "1048565                                                NaN           NaN  NaN   \n",
      "1048566                                                NaN           NaN  NaN   \n",
      "1048567                                                NaN           NaN  NaN   \n",
      "1048568                                                NaN           NaN  NaN   \n",
      "1048569                                                NaN           NaN  NaN   \n",
      "1048570                                                NaN           NaN  NaN   \n",
      "1048571                                                NaN           NaN  NaN   \n",
      "1048572                                                NaN           NaN  NaN   \n",
      "1048573                                                NaN           NaN  NaN   \n",
      "1048574                                                NaN           NaN  NaN   \n",
      "1048575                                                NaN           NaN  NaN   \n",
      "\n",
      "                                                        5   \\\n",
      "0                                                      NaN   \n",
      "1                                                      NaN   \n",
      "2                                                      NaN   \n",
      "3                                                      NaN   \n",
      "4                                                      NaN   \n",
      "5                                                      NaN   \n",
      "6                                                      NaN   \n",
      "7                                                      NaN   \n",
      "8                                                      NaN   \n",
      "9                                                      NaN   \n",
      "10                                                     NaN   \n",
      "11                                                     NaN   \n",
      "12                                                     NaN   \n",
      "13                                                     NaN   \n",
      "14                                                     NaN   \n",
      "15       ¿Ha desempeñado un cargo de gobierno \\nen los ...   \n",
      "16                                                     NaN   \n",
      "17                                                      Sí   \n",
      "18                                                     NaN   \n",
      "19                                                     NaN   \n",
      "20                                                     NaN   \n",
      "21                                                     NaN   \n",
      "22                                                     NaN   \n",
      "23                                                     NaN   \n",
      "24                                                     NaN   \n",
      "25                                                     NaN   \n",
      "26                                                     NaN   \n",
      "27                                                     NaN   \n",
      "28                                                     NaN   \n",
      "29                                                     NaN   \n",
      "...                                                    ...   \n",
      "1048546                                                NaN   \n",
      "1048547                                                NaN   \n",
      "1048548                                                NaN   \n",
      "1048549                                                NaN   \n",
      "1048550                                                NaN   \n",
      "1048551                                                NaN   \n",
      "1048552                                                NaN   \n",
      "1048553                                                NaN   \n",
      "1048554                                                NaN   \n",
      "1048555                                                NaN   \n",
      "1048556                                                NaN   \n",
      "1048557                                                NaN   \n",
      "1048558                                                NaN   \n",
      "1048559                                                NaN   \n",
      "1048560                                                NaN   \n",
      "1048561                                                NaN   \n",
      "1048562                                                NaN   \n",
      "1048563                                                NaN   \n",
      "1048564                                                NaN   \n",
      "1048565                                                NaN   \n",
      "1048566                                                NaN   \n",
      "1048567                                                NaN   \n",
      "1048568                                                NaN   \n",
      "1048569                                                NaN   \n",
      "1048570                                                NaN   \n",
      "1048571                                                NaN   \n",
      "1048572                                                NaN   \n",
      "1048573                                                NaN   \n",
      "1048574                                                NaN   \n",
      "1048575                                                NaN   \n",
      "\n",
      "                                                        6   \\\n",
      "0                                                      NaN   \n",
      "1                                                      NaN   \n",
      "2                                                      NaN   \n",
      "3        La información declarada es exhaustiva, verídi...   \n",
      "4                                                      NaN   \n",
      "5                                                      NaN   \n",
      "6                                                      NaN   \n",
      "7                                            Estado civil:   \n",
      "8                                      Entidad federativa:   \n",
      "9                                  Municipio o Delegación:   \n",
      "10                                     Distrito electoral:   \n",
      "11                                        Partido político   \n",
      "12                                                     NaN   \n",
      "13                                                     NaN   \n",
      "14                                                     NaN   \n",
      "15                                                     NaN   \n",
      "16                                                     NaN   \n",
      "17                                                      No   \n",
      "18                                                     NaN   \n",
      "19                                                     NaN   \n",
      "20                                                     NaN   \n",
      "21                                                     NaN   \n",
      "22                                                     NaN   \n",
      "23                                                     NaN   \n",
      "24                                                     NaN   \n",
      "25                                                     NaN   \n",
      "26                                                     NaN   \n",
      "27                                                     NaN   \n",
      "28                                                     NaN   \n",
      "29                                                     NaN   \n",
      "...                                                    ...   \n",
      "1048546                                                NaN   \n",
      "1048547                                                NaN   \n",
      "1048548                                                NaN   \n",
      "1048549                                                NaN   \n",
      "1048550                                                NaN   \n",
      "1048551                                                NaN   \n",
      "1048552                                                NaN   \n",
      "1048553                                                NaN   \n",
      "1048554                                                NaN   \n",
      "1048555                                                NaN   \n",
      "1048556                                                NaN   \n",
      "1048557                                                NaN   \n",
      "1048558                                                NaN   \n",
      "1048559                                                NaN   \n",
      "1048560                                                NaN   \n",
      "1048561                                                NaN   \n",
      "1048562                                                NaN   \n",
      "1048563                                                NaN   \n",
      "1048564                                                NaN   \n",
      "1048565                                                NaN   \n",
      "1048566                                                NaN   \n",
      "1048567                                                NaN   \n",
      "1048568                                                NaN   \n",
      "1048569                                                NaN   \n",
      "1048570                                                NaN   \n",
      "1048571                                                NaN   \n",
      "1048572                                                NaN   \n",
      "1048573                                                NaN   \n",
      "1048574                                                NaN   \n",
      "1048575                                                NaN   \n",
      "\n",
      "                                                        7   \\\n",
      "0                                                      NaN   \n",
      "1                                                      NaN   \n",
      "2                                                      NaN   \n",
      "3                                                      NaN   \n",
      "4                                                      NaN   \n",
      "5                                                      NaN   \n",
      "6                                                      NaN   \n",
      "7                                          Soltero/Soltera   \n",
      "8                                        Distrito Federal    \n",
      "9                                                      NaN   \n",
      "10       Escribe el número del distrito electoral.\\nEst...   \n",
      "11                                                  MORENA   \n",
      "12                                                     NaN   \n",
      "13                                                     NaN   \n",
      "14                                                     NaN   \n",
      "15       Si la respuesta fue \"Sí\" responda la siguiente...   \n",
      "16                           Dependencia o entidad pública   \n",
      "17                                                     NaN   \n",
      "18                                                     NaN   \n",
      "19                                                     NaN   \n",
      "20                                                     NaN   \n",
      "21                                                     NaN   \n",
      "22                                                     NaN   \n",
      "23                                                     NaN   \n",
      "24                                                     NaN   \n",
      "25                                                     NaN   \n",
      "26                                                     NaN   \n",
      "27                                                     NaN   \n",
      "28                                                     NaN   \n",
      "29                                                     NaN   \n",
      "...                                                    ...   \n",
      "1048546                                                NaN   \n",
      "1048547                                                NaN   \n",
      "1048548                                                NaN   \n",
      "1048549                                                NaN   \n",
      "1048550                                                NaN   \n",
      "1048551                                                NaN   \n",
      "1048552                                                NaN   \n",
      "1048553                                                NaN   \n",
      "1048554                                                NaN   \n",
      "1048555                                                NaN   \n",
      "1048556                                                NaN   \n",
      "1048557                                                NaN   \n",
      "1048558                                                NaN   \n",
      "1048559                                                NaN   \n",
      "1048560                                                NaN   \n",
      "1048561                                                NaN   \n",
      "1048562                                                NaN   \n",
      "1048563                                                NaN   \n",
      "1048564                                                NaN   \n",
      "1048565                                                NaN   \n",
      "1048566                                                NaN   \n",
      "1048567                                                NaN   \n",
      "1048568                                                NaN   \n",
      "1048569                                                NaN   \n",
      "1048570                                                NaN   \n",
      "1048571                                                NaN   \n",
      "1048572                                                NaN   \n",
      "1048573                                                NaN   \n",
      "1048574                                                NaN   \n",
      "1048575                                                NaN   \n",
      "\n",
      "                              8       9                    10  \n",
      "0                            NaN     NaN                  NaN  \n",
      "1         Fecha de presentación:     NaN  2018-12-05 00:00:00  \n",
      "2                            NaN     NaN                  NaN  \n",
      "3                            NaN     NaN                  NaN  \n",
      "4                            NaN     NaN                  NaN  \n",
      "5                            NaN     NaN                  NaN  \n",
      "6                            NaN     NaN                  NaN  \n",
      "7                            NaN     NaN                  NaN  \n",
      "8                            NaN     NaN                  NaN  \n",
      "9                            NaN     NaN                  NaN  \n",
      "10                           NaN     NaN                  NaN  \n",
      "11                           NaN     NaN                  NaN  \n",
      "12                           NaN     NaN                  NaN  \n",
      "13                           NaN     NaN                  NaN  \n",
      "14                           NaN     NaN                  NaN  \n",
      "15                           NaN     NaN                  NaN  \n",
      "16       Cargo, puesto o función     NaN              Periodo  \n",
      "17                           NaN     NaN                  NaN  \n",
      "18                           NaN     NaN                  NaN  \n",
      "19                           NaN     NaN                  NaN  \n",
      "20                           NaN     NaN                  NaN  \n",
      "21                           NaN     NaN                  NaN  \n",
      "22                           NaN     NaN                  NaN  \n",
      "23                           NaN     NaN                  NaN  \n",
      "24                           NaN     NaN                  NaN  \n",
      "25                           NaN     NaN                  NaN  \n",
      "26                           NaN     NaN                  NaN  \n",
      "27                           NaN     NaN                  NaN  \n",
      "28                           NaN     NaN                  NaN  \n",
      "29                           NaN  911755                  NaN  \n",
      "...                          ...     ...                  ...  \n",
      "1048546                      NaN     NaN                  NaN  \n",
      "1048547                      NaN     NaN                  NaN  \n",
      "1048548                      NaN     NaN                  NaN  \n",
      "1048549                      NaN     NaN                  NaN  \n",
      "1048550                      NaN     NaN                  NaN  \n",
      "1048551                      NaN     NaN                  NaN  \n",
      "1048552                      NaN     NaN                  NaN  \n",
      "1048553                      NaN     NaN                  NaN  \n",
      "1048554                      NaN     NaN                  NaN  \n",
      "1048555                      NaN     NaN                  NaN  \n",
      "1048556                      NaN     NaN                  NaN  \n",
      "1048557                      NaN     NaN                  NaN  \n",
      "1048558                      NaN     NaN                  NaN  \n",
      "1048559                      NaN     NaN                  NaN  \n",
      "1048560                      NaN     NaN                  NaN  \n",
      "1048561                      NaN     NaN                  NaN  \n",
      "1048562                      NaN     NaN                  NaN  \n",
      "1048563                      NaN     NaN                  NaN  \n",
      "1048564                      NaN     NaN                  NaN  \n",
      "1048565                      NaN     NaN                  NaN  \n",
      "1048566                      NaN     NaN                  NaN  \n",
      "1048567                      NaN     NaN                  NaN  \n",
      "1048568                      NaN     NaN                  NaN  \n",
      "1048569                      NaN     NaN                  NaN  \n",
      "1048570                      NaN     NaN                  NaN  \n",
      "1048571                      NaN     NaN                  NaN  \n",
      "1048572                      NaN     NaN                  NaN  \n",
      "1048573                      NaN     NaN                  NaN  \n",
      "1048574                      NaN     NaN                  NaN  \n",
      "1048575                      NaN     NaN                  NaN  \n",
      "\n",
      "[1048576 rows x 10 columns]\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_excel('Data/3de3_CDMX/Secretaria_de_Gobierno/03_Norma_Angelica_Licona_Alberto/01_DP_NALA.xlsx',index_col=0, header=None)\n",
    "df.reset_index(inplace=True, drop=True)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [],
   "source": [
    "def all_null(row):\n",
    "    if len(row.unique())==1:\n",
    "        if np.isnan(row.unique()[0]):\n",
    "            return 'drop'\n",
    "    return 'keep'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-180-32a02730ce37>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'act'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;32mlambda\u001b[0m \u001b[0mrow\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mall_null\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mrow\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[0mdf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'act'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m==\u001b[0m\u001b[1;34m'keep'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreset_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0minplace\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdrop\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\viri\\appdata\\local\\programs\\python\\python37-32\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36mapply\u001b[1;34m(self, func, axis, broadcast, raw, reduce, result_type, args, **kwds)\u001b[0m\n\u001b[0;32m   6012\u001b[0m                          \u001b[0margs\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   6013\u001b[0m                          kwds=kwds)\n\u001b[1;32m-> 6014\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mop\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_result\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   6015\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   6016\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mapplymap\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\viri\\appdata\\local\\programs\\python\\python37-32\\lib\\site-packages\\pandas\\core\\apply.py\u001b[0m in \u001b[0;36mget_result\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    140\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply_raw\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    141\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 142\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply_standard\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    143\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    144\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mapply_empty_result\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\viri\\appdata\\local\\programs\\python\\python37-32\\lib\\site-packages\\pandas\\core\\apply.py\u001b[0m in \u001b[0;36mapply_standard\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    240\u001b[0m                                           \u001b[0maxis\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0maxis\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    241\u001b[0m                                           \u001b[0mdummy\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdummy\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 242\u001b[1;33m                                           labels=labels)\n\u001b[0m\u001b[0;32m    243\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_constructor_sliced\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mresult\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mlabels\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    244\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mException\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\reduction.pyx\u001b[0m in \u001b[0;36mpandas._libs.reduction.reduce\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\reduction.pyx\u001b[0m in \u001b[0;36mpandas._libs.reduction.Reducer.get_result\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32m<ipython-input-180-32a02730ce37>\u001b[0m in \u001b[0;36m<lambda>\u001b[1;34m(row)\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'act'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;32mlambda\u001b[0m \u001b[0mrow\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mall_null\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mrow\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[0mdf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'act'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m==\u001b[0m\u001b[1;34m'keep'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreset_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0minplace\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdrop\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<ipython-input-179-db2273a3d2cf>\u001b[0m in \u001b[0;36mall_null\u001b[1;34m(row)\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0mall_null\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mrow\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m     \u001b[1;32mif\u001b[0m \u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mrow\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munique\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m==\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0misnan\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mrow\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munique\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[1;34m'drop'\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[1;34m'keep'\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\viri\\appdata\\local\\programs\\python\\python37-32\\lib\\site-packages\\pandas\\core\\series.py\u001b[0m in \u001b[0;36munique\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1491\u001b[0m         \u001b[0mCategories\u001b[0m \u001b[1;33m(\u001b[0m\u001b[1;36m3\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mobject\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0ma\u001b[0m \u001b[1;33m<\u001b[0m \u001b[0mb\u001b[0m \u001b[1;33m<\u001b[0m \u001b[0mc\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1492\u001b[0m         \"\"\"\n\u001b[1;32m-> 1493\u001b[1;33m         \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msuper\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mSeries\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munique\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1494\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1495\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mis_datetime64tz_dtype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\viri\\appdata\\local\\programs\\python\\python37-32\\lib\\site-packages\\pandas\\core\\base.py\u001b[0m in \u001b[0;36munique\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1047\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1048\u001b[0m             \u001b[1;32mfrom\u001b[0m \u001b[0mpandas\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcore\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0malgorithms\u001b[0m \u001b[1;32mimport\u001b[0m \u001b[0munique1d\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1049\u001b[1;33m             \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0munique1d\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1050\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1051\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\viri\\appdata\\local\\programs\\python\\python37-32\\lib\\site-packages\\pandas\\core\\algorithms.py\u001b[0m in \u001b[0;36munique\u001b[1;34m(values)\u001b[0m\n\u001b[0;32m    363\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    364\u001b[0m     \u001b[0moriginal\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mvalues\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 365\u001b[1;33m     \u001b[0mhtable\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0m_\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalues\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mndtype\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_get_hashtable_algo\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    366\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    367\u001b[0m     \u001b[0mtable\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mhtable\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\viri\\appdata\\local\\programs\\python\\python37-32\\lib\\site-packages\\pandas\\core\\algorithms.py\u001b[0m in \u001b[0;36m_get_hashtable_algo\u001b[1;34m(values)\u001b[0m\n\u001b[0;32m    208\u001b[0m            ndtype)\n\u001b[0;32m    209\u001b[0m     \"\"\"\n\u001b[1;32m--> 210\u001b[1;33m     \u001b[0mvalues\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mndtype\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_ensure_data\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    211\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    212\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0mndtype\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m'object'\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\viri\\appdata\\local\\programs\\python\\python37-32\\lib\\site-packages\\pandas\\core\\algorithms.py\u001b[0m in \u001b[0;36m_ensure_data\u001b[1;34m(values, dtype)\u001b[0m\n\u001b[0;32m     81\u001b[0m         \u001b[1;32melif\u001b[0m \u001b[0mis_signed_integer_dtype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mor\u001b[0m \u001b[0mis_signed_integer_dtype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     82\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0m_ensure_int64\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'int64'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'int64'\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 83\u001b[1;33m         elif (is_unsigned_integer_dtype(values) or\n\u001b[0m\u001b[0;32m     84\u001b[0m               is_unsigned_integer_dtype(dtype)):\n\u001b[0;32m     85\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0m_ensure_uint64\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'uint64'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'uint64'\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\viri\\appdata\\local\\programs\\python\\python37-32\\lib\\site-packages\\pandas\\core\\dtypes\\common.py\u001b[0m in \u001b[0;36mis_unsigned_integer_dtype\u001b[1;34m(arr_or_dtype)\u001b[0m\n\u001b[0;32m    941\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0marr_or_dtype\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    942\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 943\u001b[1;33m     \u001b[0mtipo\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_get_dtype_type\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marr_or_dtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    944\u001b[0m     return (issubclass(tipo, np.unsignedinteger) and\n\u001b[0;32m    945\u001b[0m             not issubclass(tipo, (np.datetime64, np.timedelta64)))\n",
      "\u001b[1;32mc:\\users\\viri\\appdata\\local\\programs\\python\\python37-32\\lib\\site-packages\\pandas\\core\\dtypes\\common.py\u001b[0m in \u001b[0;36m_get_dtype_type\u001b[1;34m(arr_or_dtype)\u001b[0m\n\u001b[0;32m   1851\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marr_or_dtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1852\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0marr_or_dtype\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtype\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1853\u001b[1;33m     \u001b[1;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marr_or_dtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1854\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marr_or_dtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtype\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1855\u001b[0m     \u001b[1;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marr_or_dtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mCategoricalDtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "df['act'] = df.apply(lambda row: all_null(row), axis=1)\n",
    "df = df[df['act']=='keep']\n",
    "df.reset_index(inplace=True, drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables_num = ['I.','II.','III.','IV.','V.', 'VI.']\n",
    "def sections(x):\n",
    "    if (type(x)!=float)&(type(x)!=int):\n",
    "        for i in tables_num:\n",
    "            #print x, i\n",
    "            if x.split(' ')[0]==i:\n",
    "                return i\n",
    "    return np.nan\n",
    "df['section'] = df[1].apply(lambda x: sections(x))   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "x='drop'\n",
    "x_l=[]\n",
    "for i in df['section'].tolist():\n",
    "    if type(i)==str:\n",
    "        x=i\n",
    "    x_l.append(x)\n",
    "df['section']=x_l"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_empty(d):\n",
    "    l_t = []\n",
    "    if d.shape[0]==0:\n",
    "        for i in range(d.shape[1]):\n",
    "            l_t.append(np.nan)\n",
    "        d.loc[0] = l_t\n",
    "    return d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(87, 12)"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### I Section"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [],
   "source": [
    "sec_1 = df[df['section']=='I.']\n",
    "sec_1.reset_index(inplace=True, drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [],
   "source": [
    "sec_1_1 = sec_1.iloc[::,:2:].T\n",
    "sec_1_1.drop([0,1], axis=1, inplace=True)\n",
    "sec_1_1.columns = sec_1_1.loc[1]\n",
    "sec_1_1.drop(index=1, inplace=True)\n",
    "sec_1_1.reset_index(inplace=True, drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "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 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>Nombre:</th>\n",
       "      <th>Poder:</th>\n",
       "      <th>Ámbito:</th>\n",
       "      <th>Entidad de gobierno o institución:</th>\n",
       "      <th>Cargo que desempeña o aspira:</th>\n",
       "      <th>Estado civil:</th>\n",
       "      <th>Entidad federativa:</th>\n",
       "      <th>Municipio o Delegación:</th>\n",
       "      <th>Distrito electoral:</th>\n",
       "      <th>Partido político</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>José Antonio Peña Merino</td>\n",
       "      <td>Ejecutivo</td>\n",
       "      <td>Estatal</td>\n",
       "      <td>Dirección General de Gobernabilidad de Tecnolo...</td>\n",
       "      <td>Titular</td>\n",
       "      <td>Casado/Casada</td>\n",
       "      <td>Distrito Federal</td>\n",
       "      <td>No aplica</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Nombre:     Poder:  Ámbito:  \\\n",
       "0  José Antonio Peña Merino  Ejecutivo  Estatal   \n",
       "\n",
       "                  Entidad de gobierno o institución:  \\\n",
       "0  Dirección General de Gobernabilidad de Tecnolo...   \n",
       "\n",
       "  Cargo que desempeña o aspira:  Estado civil: Entidad federativa:  \\\n",
       "0                       Titular  Casado/Casada   Distrito Federal    \n",
       "\n",
       "  Municipio o Delegación: Distrito electoral: Partido político  \n",
       "0               No aplica                 NaN              NaN  "
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sec_1_2 =sec_1.iloc[2::,5:7:].T\n",
    "sec_1_2.columns = sec_1_2.loc[6]\n",
    "sec_1_2.drop(index=6, inplace=True)\n",
    "sec_1_2.reset_index(inplace=True, drop=True)\n",
    "sec_1_final = pd.concat([sec_1_1,sec_1_2], axis=1)\n",
    "sec_1_final.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### II Section"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7, 12)"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sec_2 = df[df['section']=='II.'].copy(deep=True)\n",
    "sec_2.reset_index(inplace=True, drop=True)\n",
    "sec_2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "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 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>Enliste e indique la relación entre el declarante con el cónyuge, concubina o concubinario y todo dependiente económico</th>\n",
       "      <th>Nacionalidad</th>\n",
       "      <th>¿Ha desempeñado un cargo de gobierno \n",
       "en los últimos 5 años?</th>\n",
       "      <th>Dependencia o entidad pública</th>\n",
       "      <th>Cargo, puesto o función</th>\n",
       "      <th>Periodo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Cónyuge</td>\n",
       "      <td>MÉXICO</td>\n",
       "      <td>No</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Padre/Madre</td>\n",
       "      <td>MÉXICO</td>\n",
       "      <td>No</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Enliste e indique la relación entre el declarante con el cónyuge, concubina o concubinario y todo dependiente económico  \\\n",
       "0                                           Cónyuge                                                                         \n",
       "1                                        Padre/Madre                                                                        \n",
       "\n",
       "  Nacionalidad ¿Ha desempeñado un cargo de gobierno \\nen los últimos 5 años?  \\\n",
       "0       MÉXICO                                                 No              \n",
       "1       MÉXICO                                                 No              \n",
       "\n",
       "  Dependencia o entidad pública Cargo, puesto o función Periodo  \n",
       "0                           NaN                     NaN     NaN  \n",
       "1                           NaN                     NaN     NaN  "
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sec_2.columns = sec_2.loc[2][:6].tolist() + sec_2.loc[3][6:].tolist()\n",
    "sec_2 = sec_2.iloc[5::,:-2:]\n",
    "col2=[u'Enliste e indique la relaci\\xf3n entre el declarante con el c\\xf3nyuge, concubina o concubinario y todo dependiente econ\\xf3mico',\n",
    " u'Nacionalidad',\n",
    " u'\\xbfHa desempe\\xf1ado un cargo de gobierno \\nen los \\xfaltimos 5 a\\xf1os?',\n",
    " u'Dependencia o entidad p\\xfablica',\n",
    " u'Cargo, puesto o funci\\xf3n',\n",
    " u'Periodo']\n",
    "sec_2 = sec_2[col2]\n",
    "sec_2 = check_empty(sec_2)\n",
    "sec_2.reset_index(inplace=True, drop=True)\n",
    "sec_2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### III Section"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(21, 12)"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sec_3 = df[df['section']=='III.'].copy(deep=True)\n",
    "sec_3.reset_index(inplace=True, drop=True)\n",
    "sec_3.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [],
   "source": [
    "subsets = ['1.','1.1']\n",
    "def sub_sections(x):\n",
    "    if (type(x)!=float)&(type(x)!=int):\n",
    "        for i in subsets:\n",
    "            #print x, i\n",
    "            if x.split(' ')[0]==i:\n",
    "                return i\n",
    "    return np.nan\n",
    "sec_3['section'] = sec_3[1].apply(lambda x: sub_sections(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [],
   "source": [
    "x='drop'\n",
    "x_l=[]\n",
    "for i in sec_3['section'].tolist():\n",
    "    if type(i)==str:\n",
    "        x=i\n",
    "    x_l.append(x)\n",
    "sec_3['section']=x_l\n",
    "sec_3 = sec_3[sec_3['section']!='drop']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "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 th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>1</th>\n",
       "      <th>Remuneración neta anual del declarante por cargos públicos:</th>\n",
       "      <th>– Por actividades industrial, empresarial o comercial</th>\n",
       "      <th>– Por actividad financiera</th>\n",
       "      <th>– Por servicios profesionales</th>\n",
       "      <th>– Por otras actividades</th>\n",
       "      <th>Ingreso neto anual total del declarante:</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1170000</td>\n",
       "      <td>0</td>\n",
       "      <td>198000</td>\n",
       "      <td>0</td>\n",
       "      <td>1368000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "1 Remuneración neta anual del declarante por cargos públicos:  \\\n",
       "0                                                  0            \n",
       "\n",
       "1 – Por actividades industrial, empresarial o comercial  \\\n",
       "0                                            1170000      \n",
       "\n",
       "1 – Por actividad financiera – Por servicios profesionales  \\\n",
       "0                          0                        198000   \n",
       "\n",
       "1 – Por otras actividades Ingreso neto anual total del declarante:  \n",
       "0                       0                                  1368000  "
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sec_3_1 = sec_3[sec_3['section']=='1.']\n",
    "sec_3_1.reset_index(inplace=True, drop=True)\n",
    "sec_3_1 = sec_3_1.iloc[2::,::8].T.copy(deep=True)\n",
    "sec_3_1.columns = sec_3_1.loc[1]\n",
    "sec_3_1.drop(1, axis=0, inplace=True)\n",
    "col_3_1=[u'Remuneraci\\xf3n neta anual del declarante por cargos p\\xfablicos:',\n",
    "# u'Nota 1: Incluye sueldos, honorarios, compensaciones, bonos y otras prestaciones.',\n",
    "# u'Otros ingresos del declarante:',\n",
    " u'\\u2013 Por actividades industrial, empresarial o comercial',\n",
    "# u'Nota 2: Se refiere a ingresos por actividades industriales, empresariales o comerciales en M\\xe9xico y en el extranjero.',\n",
    " u'\\u2013 Por actividad financiera',\n",
    "# u'Nota 3: Se refiere a ingresos por rendimiento de valores o contratos bancarios, plusval\\xedas de participaci\\xf3n accionaria e ingresos por pr\\xe9stamos.           ',\n",
    " u'\\u2013 Por servicios profesionales',\n",
    "# u'Nota 4: Los ingresos por servicios profesionales pueden incluir la participaci\\xf3n en consejos, consultor\\xedas o asesor\\xedas de forma permanente u ocasional, en M\\xe9xico y en el extranjero.',\n",
    " u'\\u2013 Por otras actividades',\n",
    "# u'Nota 5: Se refiere a ingresos por arrendamientos, regal\\xedas, sorteos, concursos, donaciones, entre otros.',\n",
    " u'Ingreso neto anual total del declarante:']\n",
    "sec_3_1 = sec_3_1[col_3_1]\n",
    "sec_3_1 = check_empty(sec_3_1)\n",
    "sec_3_1.reset_index(inplace=True, drop=True)\n",
    "sec_3_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "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 th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>1</th>\n",
       "      <th>Ingreso anual neto del cónyuge, concubina o concubinario:</th>\n",
       "      <th>Ingreso anual neto de otros dependientes económicos:</th>\n",
       "      <th>Ingreso neto anual total del cónyuge, concubina o concubinario y/o dependientes económicos:</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>240000</td>\n",
       "      <td>14000</td>\n",
       "      <td>254000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "1 Ingreso anual neto del cónyuge, concubina o concubinario:  \\\n",
       "0                                             240000          \n",
       "\n",
       "1 Ingreso anual neto de otros dependientes económicos:  \\\n",
       "0                                              14000     \n",
       "\n",
       "1 Ingreso neto anual total del cónyuge, concubina o concubinario y/o dependientes económicos:  \n",
       "0                                             254000                                           "
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sec_3_1_1 = sec_3[sec_3['section']=='1.1']\n",
    "sec_3_1_1.reset_index(inplace=True, drop=True)\n",
    "sec_3_1_1 = sec_3_1_1.iloc[::,:-3:8].T.copy(deep=True)\n",
    "sec_3_1_1.columns = sec_3_1_1.loc[1]\n",
    "col3_1_1=[#u'1.1 Ingreso anual neto del del C\\xd3NYUGE, CONCUBINA O CONCUBINARIO Y/O DEPENDIENTES ECON\\xd3MICOS entre el 1\\xb0 de enero y el 31 de diciembre del a\\xf1o inmediato anterior',\n",
    " #u'El ingreso neto anual del c\\xf3nyuge, concubina o concubinario y dependientes econ\\xf3micos se refiere a los ingresos netos \\u2013despu\\xe9s de impuestos\\u2013 que tuvieron en el \\xfaltimo a\\xf1o fiscal concluido por actividades en cargos p\\xfablicos, actividad industrial o comercial, actividad financiera, servicios profesionales \\u2013incluyendo participaciones en consejos, consultor\\xedas o asesor\\xedas\\u2013 as\\xed como cualquier otro ingreso por actividades diversas. No es necesario desagregar por tipo de ingreso. Las cifras expresadas en esta secci\\xf3n se declara en moneda nacional (MXN).',\n",
    " u'Ingreso anual neto del c\\xf3nyuge, concubina o concubinario:',\n",
    " u'Ingreso anual neto de otros dependientes econ\\xf3micos:',\n",
    " u'Ingreso neto anual total del c\\xf3nyuge, concubina o concubinario y/o dependientes econ\\xf3micos:']\n",
    "sec_3_1_1 = sec_3_1_1[col3_1_1]\n",
    "sec_3_1_1.drop(1, axis=0, inplace=True)\n",
    "sec_3_1_1 = check_empty(sec_3_1_1)\n",
    "sec_3_1_1.reset_index(inplace=True, drop=True)\n",
    "sec_3_1_1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### IV Section"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(40, 12)"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sec_4 = df[df['section']=='IV.'].copy(deep=True)\n",
    "sec_4.reset_index(inplace=True, drop=True)\n",
    "sec_4.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0      1.\n",
      "1     NaN\n",
      "2     NaN\n",
      "3     NaN\n",
      "4     NaN\n",
      "5     NaN\n",
      "6     NaN\n",
      "7     1.1\n",
      "8     NaN\n",
      "9     NaN\n",
      "10    NaN\n",
      "11    NaN\n",
      "12     2.\n",
      "13    NaN\n",
      "14    NaN\n",
      "15    NaN\n",
      "16    NaN\n",
      "17    NaN\n",
      "18    2.1\n",
      "19    NaN\n",
      "20    NaN\n",
      "21     3.\n",
      "22    NaN\n",
      "23    NaN\n",
      "24    NaN\n",
      "25    NaN\n",
      "26    3.1\n",
      "27    NaN\n",
      "28    NaN\n",
      "29    NaN\n",
      "30    NaN\n",
      "31     4.\n",
      "32    NaN\n",
      "33    NaN\n",
      "34    NaN\n",
      "35    NaN\n",
      "36    NaN\n",
      "37    NaN\n",
      "Name: section, dtype: object\n"
     ]
    }
   ],
   "source": [
    "subsets = ['1.', '1.1','2.', '2.1','3.', '3.1','4.','5.']\n",
    "sec_4['section'] = sec_4[1].apply(lambda x: sub_sections(x))\n",
    "print(sec_4['section'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [],
   "source": [
    "x='drop'\n",
    "x_l=[]\n",
    "for i in sec_4['section'].tolist():\n",
    "    if type(i)==str:\n",
    "        x=i\n",
    "    x_l.append(x)\n",
    "sec_4['section']=x_l\n",
    "sec_4 = sec_4[sec_4['section']!='drop']\n",
    "sec_4.reset_index(inplace=True, drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [],
   "source": [
    "sec_4_1 = sec_4[sec_4['section']=='1.']\n",
    "sec_4_1.reset_index(inplace=True, drop=True)\n",
    "sec_4_1.columns = sec_4_1.loc[2].tolist()[:1] + sec_4_1.loc[3].tolist()[1:3] + sec_4_1.loc[2].tolist()[3:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "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 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>Tipo de bien declarado</th>\n",
       "      <th>Terreno</th>\n",
       "      <th>Construcción</th>\n",
       "      <th>País donde se encuentra el bien inmueble</th>\n",
       "      <th>Entidad federativa y municipio o delegación donde se encuentra el bien inmueble</th>\n",
       "      <th>Forma de operación en la que se adquirió el bien inmueble</th>\n",
       "      <th>Año en que se realizó la adquisición del bien inmueble</th>\n",
       "      <th>Valor del bien inmueble conforme a escritura pública</th>\n",
       "      <th>Moneda</th>\n",
       "      <th>Titular del bien inmueble declarado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Terreno</td>\n",
       "      <td>4000</td>\n",
       "      <td>0</td>\n",
       "      <td>MÉXICO</td>\n",
       "      <td>Tulancingo; Hidalgo</td>\n",
       "      <td>Contado</td>\n",
       "      <td>2016</td>\n",
       "      <td>300000</td>\n",
       "      <td>MXN – Nuevos pesos</td>\n",
       "      <td>Declarante</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Terreno</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>MÉXICO</td>\n",
       "      <td>Tenango de Doria, Hidalgo</td>\n",
       "      <td>Contado</td>\n",
       "      <td>2017</td>\n",
       "      <td>400000</td>\n",
       "      <td>MXN – Nuevos pesos</td>\n",
       "      <td>Declarante</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Tipo de bien declarado Terreno Construcción  \\\n",
       "0                Terreno    4000            0   \n",
       "1                Terreno    5000            0   \n",
       "\n",
       "  País donde se encuentra el bien inmueble  \\\n",
       "0                                   MÉXICO   \n",
       "1                                   MÉXICO   \n",
       "\n",
       "  Entidad federativa y municipio o delegación donde se encuentra el bien inmueble  \\\n",
       "0                                Tulancingo; Hidalgo                                \n",
       "1                          Tenango de Doria, Hidalgo                                \n",
       "\n",
       "  Forma de operación en la que se adquirió el bien inmueble  \\\n",
       "0                                            Contado          \n",
       "1                                            Contado          \n",
       "\n",
       "  Año en que se realizó la adquisición del bien inmueble  \\\n",
       "0                                               2016       \n",
       "1                                               2017       \n",
       "\n",
       "  Valor del bien inmueble conforme a escritura pública              Moneda  \\\n",
       "0                                             300000    MXN – Nuevos pesos   \n",
       "1                                             400000    MXN – Nuevos pesos   \n",
       "\n",
       "  Titular del bien inmueble declarado  \n",
       "0                          Declarante  \n",
       "1                          Declarante  "
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sec_4_1 = sec_4_1.iloc[4:-1:,:-2:]\n",
    "sec_4_1 = check_empty(sec_4_1)\n",
    "sec_4_1.reset_index(inplace=True, drop=True)\n",
    "sec_4_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "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 th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>2</th>\n",
       "      <th>Tipo de bien declarado</th>\n",
       "      <th>País donde se encuentra el bien inmueble</th>\n",
       "      <th>Entidad federativa y municipio o delegación donde se encuentra el bien inmueble</th>\n",
       "      <th>Forma de operación en la que se adquirió el bien inmueble</th>\n",
       "      <th>Año en que se realizó la adquisición del bien inmueble</th>\n",
       "      <th>Titular del bien inmueble declarado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "2 Tipo de bien declarado País donde se encuentra el bien inmueble  \\\n",
       "0                    NaN                                      NaN   \n",
       "\n",
       "2 Entidad federativa y municipio o delegación donde se encuentra el bien inmueble  \\\n",
       "0                                                NaN                                \n",
       "\n",
       "2 Forma de operación en la que se adquirió el bien inmueble  \\\n",
       "0                                                NaN          \n",
       "\n",
       "2 Año en que se realizó la adquisición del bien inmueble  \\\n",
       "0                                                NaN       \n",
       "\n",
       "2 Titular del bien inmueble declarado  \n",
       "0                                 NaN  "
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sec_4_1_1 = sec_4[sec_4['section']=='1.1']\n",
    "sec_4_1_1.reset_index(inplace=True, drop=True)\n",
    "sec_4_1_1.columns = sec_4_1_1.loc[2]\n",
    "sec_4_1_1 = sec_4_1_1.iloc[4:-1:,:-5:]\n",
    "\n",
    "col4_1_1=[u'Tipo de bien declarado',\n",
    " u'Pa\\xeds donde se encuentra el bien inmueble',\n",
    " u'Entidad federativa y municipio o delegaci\\xf3n donde se encuentra el bien inmueble',\n",
    " u'Forma de operaci\\xf3n en la que se adquiri\\xf3 el bien inmueble',\n",
    " u'A\\xf1o en que se realiz\\xf3 la adquisici\\xf3n del bien inmueble',\n",
    " u'Titular del bien inmueble declarado']\n",
    "sec_4_1_1 = sec_4_1_1[col4_1_1]\n",
    "sec_4_1_1 = check_empty(sec_4_1_1)\n",
    "sec_4_1_1.reset_index(inplace=True, drop=True)\n",
    "sec_4_1_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "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 th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>2</th>\n",
       "      <th>Tipo de vehículo</th>\n",
       "      <th>Marca</th>\n",
       "      <th>Modelo</th>\n",
       "      <th>País donde está registrado el vehículo</th>\n",
       "      <th>Forma de operación en la que se adquirió el vehículo</th>\n",
       "      <th>Año en que se realizó la adquisición del vehículo</th>\n",
       "      <th>Valor del vehículo</th>\n",
       "      <th>Moneda</th>\n",
       "      <th>Titular del vehículo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Automotor</td>\n",
       "      <td>Ford</td>\n",
       "      <td>EcoSport</td>\n",
       "      <td>MÉXICO</td>\n",
       "      <td>Crédito</td>\n",
       "      <td>2015</td>\n",
       "      <td>263000</td>\n",
       "      <td>MXN – Nuevos pesos</td>\n",
       "      <td>Declarante</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "2 Tipo de vehículo Marca    Modelo País donde está registrado el vehículo  \\\n",
       "0        Automotor  Ford  EcoSport                                 MÉXICO   \n",
       "\n",
       "2 Forma de operación en la que se adquirió el vehículo  \\\n",
       "0                                            Crédito     \n",
       "\n",
       "2 Año en que se realizó la adquisición del vehículo Valor del vehículo  \\\n",
       "0                                              2015             263000   \n",
       "\n",
       "2              Moneda Titular del vehículo  \n",
       "0  MXN – Nuevos pesos           Declarante  "
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sec_4_2 = sec_4[sec_4['section']=='2.']\n",
    "sec_4_2.reset_index(inplace=True, drop=True)\n",
    "sec_4_2.columns = sec_4_2.loc[2]\n",
    "sec_4_2 = sec_4_2.iloc[4:-1:,:-2:]\n",
    "col4_2=[u'Tipo de veh\\xedculo',\n",
    " u'Marca',\n",
    " u'Modelo',\n",
    " u'Pa\\xeds donde est\\xe1 registrado el veh\\xedculo',\n",
    " u'Forma de operaci\\xf3n en la que se adquiri\\xf3 el veh\\xedculo',\n",
    " u'A\\xf1o en que se realiz\\xf3 la adquisici\\xf3n del veh\\xedculo',\n",
    " u'Valor del veh\\xedculo',\n",
    " u'Moneda',\n",
    " u'Titular del veh\\xedculo']\n",
    "sec_4_2 = sec_4_2[col4_2]\n",
    "sec_4_2 = check_empty(sec_4_2)\n",
    "sec_4_2.reset_index(inplace=True, drop=True)\n",
    "sec_4_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "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 th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>2</th>\n",
       "      <th>Tipo de vehículo</th>\n",
       "      <th>País donde está registrado el vehículo</th>\n",
       "      <th>Forma de operación en la que se adquirió el vehículo</th>\n",
       "      <th>Año en que se realizó la adquisición del vehículo</th>\n",
       "      <th>Titular del vehículo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "2 Tipo de vehículo País donde está registrado el vehículo  \\\n",
       "0              NaN                                    NaN   \n",
       "\n",
       "2 Forma de operación en la que se adquirió el vehículo  \\\n",
       "0                                                NaN     \n",
       "\n",
       "2 Año en que se realizó la adquisición del vehículo Titular del vehículo  \n",
       "0                                               NaN                  NaN  "
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sec_4_2_1 = sec_4[sec_4['section']=='2.1']\n",
    "sec_4_2_1.reset_index(inplace=True, drop=True)\n",
    "sec_4_2_1.columns = sec_4_2_1.loc[2]\n",
    "sec_4_2_1 = sec_4_2_1.iloc[3::,:-6:]\n",
    "col4_2_1=[u'Tipo de veh\\xedculo',\n",
    " u'Pa\\xeds donde est\\xe1 registrado el veh\\xedculo',\n",
    " u'Forma de operaci\\xf3n en la que se adquiri\\xf3 el veh\\xedculo',\n",
    " u'A\\xf1o en que se realiz\\xf3 la adquisici\\xf3n del veh\\xedculo',\n",
    " u'Titular del veh\\xedculo']\n",
    "sec_4_2_1 = sec_4_2_1[col4_2_1]\n",
    "sec_4_2_1 = check_empty(sec_4_2_1)\n",
    "sec_4_2_1.reset_index(inplace=True, drop=True)\n",
    "sec_4_2_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "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 th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>2</th>\n",
       "      <th>Tipo de bien mueble</th>\n",
       "      <th>Forma de operación en la que se adquirió el bien mueble</th>\n",
       "      <th>Año en que se realizó la adquisición del bien mueble</th>\n",
       "      <th>Valor estimado del bien mueble</th>\n",
       "      <th>Moneda</th>\n",
       "      <th>Titular del bien mueble</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "2 Tipo de bien mueble Forma de operación en la que se adquirió el bien mueble  \\\n",
       "0                 NaN                                                NaN        \n",
       "\n",
       "2 Año en que se realizó la adquisición del bien mueble  \\\n",
       "0                                                NaN     \n",
       "\n",
       "2 Valor estimado del bien mueble Moneda Titular del bien mueble  \n",
       "0                            NaN    NaN                     NaN  "
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sec_4_3 = sec_4[sec_4['section']=='3.']\n",
    "sec_4_3.reset_index(inplace=True, drop=True)\n",
    "sec_4_3.columns = sec_4_3.loc[2]\n",
    "sec_4_3 = sec_4_3.iloc[4:-1:,:-4:]\n",
    "col4_3=[u'Tipo de bien mueble',\n",
    " u'Forma de operaci\\xf3n en la que se adquiri\\xf3 el bien mueble',\n",
    " u'A\\xf1o en que se realiz\\xf3 la adquisici\\xf3n del bien mueble',\n",
    " u'Valor estimado del bien mueble',\n",
    " u'Moneda',\n",
    " u'Titular del bien mueble']\n",
    "sec_4_3 = sec_4_3[col4_3]\n",
    "sec_4_3 = check_empty(sec_4_3)\n",
    "sec_4_3.reset_index(inplace=True, drop=True)\n",
    "sec_4_3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "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 th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>2</th>\n",
       "      <th>Tipo de bien mueble</th>\n",
       "      <th>Forma de operación en la que se adquirió el bien mueble</th>\n",
       "      <th>Año en que se realizó la adquisición del bien mueble</th>\n",
       "      <th>Titular del bien mueble</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "2 Tipo de bien mueble Forma de operación en la que se adquirió el bien mueble  \\\n",
       "0                 NaN                                                NaN        \n",
       "\n",
       "2 Año en que se realizó la adquisición del bien mueble Titular del bien mueble  \n",
       "0                                                NaN                       NaN  "
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sec_4_3_1 = sec_4[sec_4['section']=='3.1']\n",
    "sec_4_3_1.reset_index(inplace=True, drop=True)\n",
    "sec_4_3_1.columns = sec_4_3_1.loc[2]\n",
    "sec_4_3_1 = sec_4_3_1.iloc[4:-1:,:-7:]\n",
    "col4_3_1=[u'Tipo de bien mueble',\n",
    " u'Forma de operaci\\xf3n en la que se adquiri\\xf3 el bien mueble',\n",
    " u'A\\xf1o en que se realiz\\xf3 la adquisici\\xf3n del bien mueble',\n",
    " u'Titular del bien mueble']\n",
    "sec_4_3_1 = sec_4_3_1[col4_3_1]\n",
    "sec_4_3_1 = check_empty(sec_4_3_1)\n",
    "sec_4_3_1.reset_index(inplace=True, drop=True)\n",
    "sec_4_3_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "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 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>Tipo de inversión</th>\n",
       "      <th>País donde está constituida la entidad donde se encuentra la inversión</th>\n",
       "      <th>Nombre o razón social de la entidad</th>\n",
       "      <th>Menor o igual a $100,000.00</th>\n",
       "      <th>Entre $100,000.01 y $500,000.00</th>\n",
       "      <th>Mayor o igual a $500,000.01</th>\n",
       "      <th>Moneda de la inversión</th>\n",
       "      <th>Titularidad de la inversión</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bancaria</td>\n",
       "      <td>MÉXICO</td>\n",
       "      <td>Banorte</td>\n",
       "      <td>Menor o igual a $100,000.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MXN – Nuevos pesos</td>\n",
       "      <td>Declarante</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bancaria</td>\n",
       "      <td>MÉXICO</td>\n",
       "      <td>Banorte</td>\n",
       "      <td>Menor o igual a $100,000.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MXN – Nuevos pesos</td>\n",
       "      <td>Declarante</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Tipo de inversión  \\\n",
       "0          Bancaria   \n",
       "1          Bancaria   \n",
       "\n",
       "  País donde está constituida la entidad donde se encuentra la inversión  \\\n",
       "0                                             MÉXICO                       \n",
       "1                                             MÉXICO                       \n",
       "\n",
       "  Nombre o razón social de la entidad  Menor o igual a $100,000.00  \\\n",
       "0                             Banorte  Menor o igual a $100,000.00   \n",
       "1                             Banorte  Menor o igual a $100,000.00   \n",
       "\n",
       "  Entre $100,000.01 y $500,000.00 Mayor o igual a $500,000.01  \\\n",
       "0                             NaN                         NaN   \n",
       "1                             NaN                         NaN   \n",
       "\n",
       "  Moneda de la inversión Titularidad de la inversión  \n",
       "0     MXN – Nuevos pesos                  Declarante  \n",
       "1     MXN – Nuevos pesos                  Declarante  "
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sec_4_4 = sec_4[sec_4['section']=='4.']\n",
    "sec_4_4.reset_index(inplace=True, drop=True)\n",
    "sec_4_4.columns = sec_4_4.loc[2].tolist()[:5] + sec_4_4.loc[3].tolist()[5:8] + sec_4_4.loc[2].tolist()[8:]\n",
    "sec_4_4 = sec_4_4.iloc[4:-1:,:-2:]\n",
    "col4_4=[u'Tipo de inversi\\xf3n',\n",
    " u'Pa\\xeds donde est\\xe1 constituida la entidad donde se encuentra la inversi\\xf3n',\n",
    " u'Nombre o raz\\xf3n social de la entidad',\n",
    " u'Menor o igual a $100,000.00',\n",
    " u'Entre $100,000.01 y $500,000.00',\n",
    " u'Mayor o igual a $500,000.01',\n",
    " u'Moneda de la inversi\\xf3n',\n",
    " u'Titularidad de la inversi\\xf3n']\n",
    "sec_4_4 = sec_4_4[col4_4]\n",
    "sec_4_4 = check_empty(sec_4_4)\n",
    "sec_4_4.reset_index(inplace=True, drop=True)\n",
    "sec_4_4.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### V Section"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [],
   "source": [
    "sec_5 = df[df['section']=='V.']\n",
    "sec_5.reset_index(inplace=True, drop=True)\n",
    "sec_5.columns = sec_5.loc[2]\n",
    "sec_5 = sec_5.loc[3::,::].copy(deep=True)\n",
    "sec_5.reset_index(inplace=True, drop=True)\n",
    "sec_5 = sec_5.iloc[:-2:,:-6:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "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 th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>2</th>\n",
       "      <th>No. de nota</th>\n",
       "      <th>Sección a la que hace referencia</th>\n",
       "      <th>Subsección a la que hace referencia</th>\n",
       "      <th>Descripción de la nota aclaratoria</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>IV. DATOS SOBRE BIENES</td>\n",
       "      <td>IV - 1.1 Bienes inmuebles del CÓNYUGE, CONCUBI...</td>\n",
       "      <td>Ambos terrenos se encuentran en proceso de cam...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "2 No. de nota Sección a la que hace referencia  \\\n",
       "0           1           IV. DATOS SOBRE BIENES   \n",
       "\n",
       "2                Subsección a la que hace referencia  \\\n",
       "0  IV - 1.1 Bienes inmuebles del CÓNYUGE, CONCUBI...   \n",
       "\n",
       "2                 Descripción de la nota aclaratoria  \n",
       "0  Ambos terrenos se encuentran en proceso de cam...  "
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def all_5_nan(row):\n",
    "    row = row[1:]\n",
    "    if len(row.unique())==1:\n",
    "        return 'drop'\n",
    "    else:\n",
    "        return 'keep'\n",
    "sec_5['tag'] = sec_5.apply(lambda row: all_5_nan(row), axis=1)\n",
    "sec_5 = sec_5[sec_5['tag'] =='keep'].copy(deep=True)\n",
    "sec_5.drop('tag', axis=1,inplace=True)\n",
    "col4_5=[u'No. de nota',\n",
    " u'Secci\\xf3n a la que hace referencia',\n",
    " u'Subsecci\\xf3n a la que hace referencia',\n",
    " u'Descripci\\xf3n de la nota aclaratoria']\n",
    "sec_5 = sec_5[col4_5]\n",
    "sec_5 = check_empty(sec_5)\n",
    "sec_5.reset_index(inplace=True, drop=True)\n",
    "sec_5.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_list = {'sec_1':sec_1_final, \n",
    "'sec_2':sec_2, #'sec_2_1': sec_2_1_1,'sec_2_2':sec_2_2, 'sec_2_2_1':sec_2_2_1,'sec_2_3':sec_2_3,'sec_2_3_1':sec_2_3_1,'sec_2_4':sec_2_4,'sec_2_5':sec_2_5, \n",
    "'sec_3_1':sec_3_1, 'sec_3_1_1':sec_3_1_1,# 'sec_3_3':sec_3_3, \n",
    "'sec_4_1':sec_4_1, 'sec_4_1_1':sec_4_1_1, 'sec_4_2':sec_4_2, 'sec_4_2_1':sec_4_2_1, 'sec_4_3':sec_4_3, 'sec_4_3_1':sec_4_3_1,  'sec_4_4':sec_4_4, #'sec_4_5':sec_4_5,\n",
    "'sec_5':sec_5}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sec_1\n",
      "sec_2\n",
      "sec_3_1\n",
      "sec_3_1_1\n",
      "sec_4_1\n",
      "sec_4_1_1\n",
      "sec_4_2\n",
      "sec_4_2_1\n",
      "sec_4_3\n",
      "sec_4_3_1\n",
      "sec_4_4\n",
      "sec_5\n"
     ]
    }
   ],
   "source": [
    "for key in table_list.keys():\n",
    "    print(key)\n",
    "    table_list[key].to_csv('Output/Patrimonio/'+key+'pat.csv', index=0,encoding='utf-8' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [],
   "source": [
    "the_json = {sec_1_final['Nombre:'].values[0] + '_' + str(df[10][0]).split(' ')[0]:\n",
    " {'sec_1':sec_1_final.to_json(),\n",
    "  'sec_2':sec_2.to_json(),\n",
    "  'sec_3':{\n",
    "    'sec_3_1':sec_3_1.to_json(), 'sec_3_1_1':sec_3_1_1.to_json(),\n",
    "  },\n",
    "  'sec_4':{\n",
    "      'sec_4_1':sec_4_1.to_json(), 'sec_4_1_1':sec_4_1_1.to_json(), 'sec_4_2':sec_4_2.to_json(), \n",
    "      'sec_4_2_1':sec_4_2_1.to_json(), 'sec_4_3':sec_4_3.to_json(), 'sec_4_3_1':sec_4_3_1.to_json(),  'sec_4_4':sec_4_4.to_json()\n",
    "  },\n",
    "  'sec_5':sec_5.to_json(),\n",
    " }  \n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('Output/patrimonio_'+sec_1_final['Nombre:'].values[0].replace(' ','') + '_' + str(df[10][0]).split(' ')[0] + '.json', 'w') as fp:\n",
    "    json.dump(the_json, fp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.2rc1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}