Automatizar_patrimonio.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pathlib\n",
    "import json\n",
    "import re\n",
    "\n",
    "tables_num = ['I.','II.','III.','IV.','V.', 'VI.']\n",
    "\n",
    "def all_null(row):\n",
    "    if len(row.unique())==1:\n",
    "        if np.isnan(row.unique()[0]):\n",
    "            return 'drop'\n",
    "    return 'keep'\n",
    "\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",
    "\n",
    "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\n",
    "\n",
    "def sub_sections(x,subsets):\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",
    "\n",
    "def all_5_nan(row):\n",
    "    row = row[1:]\n",
    "    if len(row.unique())==1:\n",
    "        return 'drop'\n",
    "    else:\n",
    "        return 'keep'\n",
    "\n",
    "def put_nulls(df):\n",
    "    for col in df:\n",
    "        df[col]=df[col].str.replace(r\"^(\\[)(.*?)(\\])$\",'null')\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def crea_json(ruta):\n",
    "    df = pd.read_excel(ruta,index_col=0, header=None)\n",
    "    df.reset_index(inplace=True, drop=True)\n",
    "    \n",
    "    df.dropna(how='all')\n",
    "    df['act'] = df.apply(lambda row: all_null(row), axis=1)\n",
    "    df = df[df['act']=='keep']\n",
    "    \n",
    "    #Para verificar el encabezado del dataframe para saber si de verdad es xlxs de patrimonio\n",
    "    patron = re.compile('^(.*?)(patrimonial)(.*?)$')\n",
    "    \n",
    "    if(patron.search(df.iloc[0][0])):\n",
    "        df.reset_index(inplace=True, drop=True)\n",
    "        df['section'] = df[1].apply(lambda x: sections(x))  \n",
    "        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\n",
    "\n",
    "        # SECCION I *****************************************************************************************\n",
    "        sec_1 = df[df['section']=='I.']\n",
    "        sec_1.reset_index(inplace=True, drop=True)\n",
    "        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)\n",
    "        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",
    "        put_nulls(sec_1_final)\n",
    "\n",
    "        # SECCION II ****************************************************************************************\n",
    "        sec_2 = df[df['section']=='II.'].copy(deep=True)\n",
    "        sec_2.reset_index(inplace=True, drop=True)\n",
    "        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",
    "        put_nulls(sec_2)\n",
    "\n",
    "        # SECCION III ***************************************************************************************\n",
    "        sec_3 = df[df['section']=='III.'].copy(deep=True)\n",
    "        sec_3.reset_index(inplace=True, drop=True)\n",
    "        subsets = ['1.','1.1']\n",
    "        sec_3['section'] = sec_3[1].apply(lambda x: sub_sections(x,subsets))\n",
    "        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']\n",
    "\n",
    "        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",
    "        put_nulls(sec_3_1)\n",
    "\n",
    "        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",
    "        put_nulls(sec_3_1_1)\n",
    "\n",
    "        # SECCION IV ******************************************************************************\n",
    "        sec_4 = df[df['section']=='IV.'].copy(deep=True)\n",
    "        sec_4.reset_index(inplace=True, drop=True)\n",
    "        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,subsets))\n",
    "        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)\n",
    "        # *********************************************************************************************\n",
    "        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:]\n",
    "        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",
    "        put_nulls(sec_4_1)\n",
    "        # *********************************************************************************************\n",
    "        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",
    "        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",
    "        put_nulls(sec_4_1_1)\n",
    "        # *********************************************************************************************\n",
    "        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",
    "        put_nulls(sec_4_2)\n",
    "        # *********************************************************************************************\n",
    "        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",
    "        put_nulls(sec_4_2_1)\n",
    "        # *********************************************************************************************\n",
    "        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','nan',\n",
    "         u'A\\xf1o en que se realiz\\xf3 la adquisici\\xf3n del bien mueble',\n",
    "         u'Valor estimado del bien mueble', 'nan',\n",
    "         u'Moneda',\n",
    "         u'Titular del bien mueble']\n",
    "        if(len(sec_4_3.columns)==len(col4_3)):\n",
    "            sec_4_3.columns = col4_3\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",
    "        put_nulls(sec_4_3)\n",
    "        # *********************************************************************************************\n",
    "        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",
    "        put_nulls(sec_4_3_1)\n",
    "        # *********************************************************************************************\n",
    "        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",
    "        put_nulls(sec_4_4)\n",
    "        # SECCION V ******************************************************************************************\n",
    "        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:]\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",
    "        put_nulls(sec_5)\n",
    "        \n",
    "        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}\n",
    "\n",
    "        the_json = {'persona':{sec_1_final['Nombre:'].values[0] + '_' + str(df[10][0]).split(' ')[0]:\n",
    "             {'sec_1':sec_1_final.to_json(orient='records'),\n",
    "              'sec_2':sec_2.to_json(orient='records'),\n",
    "              'sec_3':{\n",
    "                'sec_3_1':sec_3_1.to_json(orient='records'), 'sec_3_1_1':sec_3_1_1.to_json(orient='records'),\n",
    "              },\n",
    "              'sec_4':{\n",
    "                  'sec_4_1':sec_4_1.to_json(orient='records'), 'sec_4_1_1':sec_4_1_1.to_json(orient='records'), 'sec_4_2':sec_4_2.to_json(orient='records'), \n",
    "                  'sec_4_2_1':sec_4_2_1.to_json(orient='records'), 'sec_4_3':sec_4_3.to_json(orient='records'), 'sec_4_3_1':sec_4_3_1.to_json(orient='records'),  'sec_4_4':sec_4_4.to_json(orient='records')\n",
    "              },\n",
    "              'sec_5':sec_5.to_json(orient='records'),\n",
    "             }\n",
    "            }\n",
    "        }\n",
    "\n",
    "        with open('Output/Patrimonio/jsons/patrimonio_'+sec_1_final['Nombre:'].values[0].replace(' ','')+'.json', 'w') as fp:\n",
    "            json.dump(the_json, fp)\n",
    "    else:\n",
    "        \"El excel dice que es patrimonial pero es de intereses\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_DP_xlsx(cadena):\n",
    "    #Para verificar si el archivo .xlsx se trata de un excel de patrimonio\n",
    "    patron = re.compile('^(.*?)(_DP| DP|DP |declaracion_patrimonial|DP_)(.*?)(\\.xlsx)$')\n",
    "    if(patron.search(cadena)):\n",
    "        print(cadena)\n",
    "        crea_json(cadena)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data/3de3_CDMX/Secretaria_de_Inclusion_y_Bienestar_Social/03_Beatriz_Adriana_Olivares_Pinal/01 DP BAOP.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Inclusion_y_Bienestar_Social/03_Rafael_Perez_Laurrabaquio/01_DP_RPL.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Inclusion_y_Bienestar_Social/02_Jaime_Ruben_Morales_Beltran/01_DP_JRMB.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Inclusion_y_Bienestar_Social/02_Araceli_Damian_Gonzalez/01_DP_ADG.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Inclusion_y_Bienestar_Social/01_Almudena_Ocejo_Rojo/01_DP_AOR.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Inclusion_y_Bienestar_Social/03_Rafael_Vargas_Morales/01_DP_RMV.xlsx\n",
      "Data/3de3_CDMX/Procuraduria_de_Justicia/02_Rodrigo_de_la_Riva/01_DP_RRR.xlsx\n",
      "Data/3de3_CDMX/Coordinacion_General_de_Comunicacion_Social/01_Cesar_Ivan_Escalante_Ruiz/01_DP_CIER.xlsx\n",
      "Data/3de3_CDMX/Consejo_Ciudadano/declaracion_patrimonial (1).xlsx\n",
      "Data/3de3_CDMX/Secretaria_para_la_Autonomia_de_la_Mujer/03_Maria_Luisa_Leticia_Silva_Canaan/01_DP_MLLSC.xlsx\n",
      "Data/3de3_CDMX/Secretaria_para_la_Autonomia_de_la_Mujer/03_Geru_Aparicio_Avina/01_DP_GAA.xlsx\n",
      "Data/3de3_CDMX/Secretaria_para_la_Autonomia_de_la_Mujer/02_Maria_Elena_Ortega_Hernandez/01_DP_MEOH.xlsx\n",
      "Data/3de3_CDMX/Secretaria_para_la_Autonomia_de_la_Mujer/03_Yazmin_Perez_Haro/01_DP_YPH.xlsx\n",
      "Data/3de3_CDMX/Secretaria_para_la_Autonomia_de_la_Mujer/01_Gabriela_Rodriguez_Ramirez/01_DP_GRR.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Seguridad_Ciudadana/01_Jesus_Orta_Martinez/01_DP_JOM.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Seguridad_Ciudadana/02_Maria_de_los_Angeles_Ocampo/01_DP_MAO.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Seguridad_Ciudadana/02_Maria_Bartolucci_Blanco/01_DP_MBB.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Seguridad_Ciudadana/02_Gabriela_Balzatar_Machaen/01_DP_GBM.xlsx\n",
      "Data/3de3_CDMX/Secretarбa_de_Educacion/02_Jacinto_Samuel_Salinas_Alvarez/01_DP_JSSA.xlsx\n",
      "Data/3de3_CDMX/Secretarбa_de_Educacion/03_Marбa_Isabel_Vazquez_Padilla/01_DP_MIVP.xlsx\n",
      "Data/3de3_CDMX/Secretarбa_de_Educacion/03_Ofelia_Angulo_Guerrero/01_DP_JOA.xlsx\n",
      "Data/3de3_CDMX/Secretarбa_de_Educacion/01_Rosaura_Ruiz_Gutierrez/01_DP_RRG.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Salud/01_Olivia_Lopez_Arellano/01_DP_OLA.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Salud/02_Jose_Manuel_Castro_Albarran/01_DP_JMECA.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Salud/03_Ricardo_Arturo_Barreiro_Pereira/01_DP_RABP.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gobierno/03_Guillermo_Garduдo_Aguilar/01_DP_GGA.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gobierno/03_Luis_Gustavo_Vela_Sanchez/01_DP_LGVS.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gobierno/02_Dunia_Ludlow/02_DP_DL.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gobierno/03_Primitivo_Basaldua_Morales/01_DP_PBM.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gobierno/03_Norma_Angelica_Licona_Alberto/01_DP_NALA.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gobierno/03_Adriana_Contreras_Vera/01_DP_ACV.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gobierno/02_Rene_Cervera_Garcia/01_DP_RCG.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gobierno/02_Avelino_Mendez_Rangel/01 DP AMR.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gobierno/03_Juan_Gutierrez_Marquez/01_DP_JGM.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gobierno/03_Paola_Becerra_Silva/01_DP_PBS.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gobierno/01_Rosa_Icela_Rodriguez_Velazquez/01_DP_RIRV.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gobierno/02_Felix_Arturo_Medina_Padilla/01_DP_FAMP.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gobierno/03_Gustavo_Vela_Sanchez/01_DP_GVS.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gobierno/03_Gilberto_Ensastiga_Santiago/01_DP_GES.xlsx\n",
      "Data/3de3_CDMX/Secretaria_Desarrollo_Urbano_Vivienda/01_DP_IVE.xlsx\n",
      "Data/3de3_CDMX/Secretaria_del_Trabajo/03_Oscar_Hugo_Ortiz/01_DP_OOM.xlsx\n",
      "Data/3de3_CDMX/Secretaria_del_Trabajo/01_Soledad_Haydee_Aragon_Martinez/01_DP_HSAM.xlsx\n",
      "Data/3de3_CDMX/Secretaria_del_Trabajo/03_Victor_Martinez_Corona/01_DP_VMC.xlsx\n",
      "Data/3de3_CDMX/Secretaria_del_Trabajo/03_Sara_Elvira_Morgan_Hermida/01_DP_SEMH.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Obras_y_Servicios/03_Efrain_Morales_Lopez/01_DP_EML.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Obras_y_Servicios/03_Gabriela_Del_Olmo_Barragan/01_DP_GDOB.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Obras_y_Servicios/03_Juan_Carlos_Fuentes_Orrala/01_DP_JCFO.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Obras_y_Servicios/01_Jesus_Antonio_Esteva_Medina/01_DP_JAEM.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Obras_y_Servicios/02_Rosalba_Cruz_Jimenez/01_DP_RCJ.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Obras_y_Servicios/03_Felipe_Franco_Zaragoza/01_DP_FJFZ.xlsx\n",
      "Data/3de3_CDMX/Agencia_Digital_de_Innovacion_Publica/03_Eduardo_Clark_Garcбa_Dobarganes/01_DP_ECGD.xlsx\n",
      "Data/3de3_CDMX/Agencia_Digital_de_Innovacion_Publica/01_Jose_Antonio_Pena_Merino/01_DP_JAPM.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Pueblos_Indigenas/01_Larisa_Ortiz_Quintero/01_DP_LOQ.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/03_Agustin_Rodriguez_Bello/01_DP_ARB.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/03_Dionicio_Rosas_Flores/01_DP_DR.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/03_Norma_Carolina_Magaдa_Lopez/01_DP_NCML.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/03_Liliana_Rosales_Contreras/01_DP_LR.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/03_Alejandro_ArВvalo_Cardoso/01_DP_AA.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/03_Alejandro_ArВvalo_Cardoso/02_DP_AA.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/02_Jose_Luis_Balsadua_Ramos/01_DP_JLBR.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/03_Jose_Luis_Garcia_Martinez/01_DP_JLGM.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/02_Bertha_Gomez_Castro/01_DP_BMEGC.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/03_Ximena_Jacinta_Garcia_Ramirez/01_DP_XJGR.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/03_Samanta_Merit_Boone/01_DP_SMBG.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/03_Salvador_Pineda/01_DP_SCPH.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/03_Jorge_Cisneros_Armas/01_DP_JCA.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/03_Ramon_Ley_Becerra/01_DP_RLB.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/03_Juan_Manuel_Hermida_Escobedo/03_DP_JMHE.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/03_Edgar_Cruz/01_DP_EC.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/03_Violeta_Abreu/01_DP_VA.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/03_Gerardo_Almonte/01_DP_GA.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/02_Edwin_Meraz/01_DP_EM.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/02_Roberto_Fernаndez/01_DP_RCFG.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Administracion_y_Finanzas/01_Luz_Elena_Gonzalez_Escobar/01_DP_LEGE.xlsx\n",
      "Data/3de3_CDMX/Secretarбa_de_Cultura/01_JosВ_Alfonso_Suаrez_del_Real/01_DP_JASR.xlsx\n",
      "Data/3de3_CDMX/Jefatura_de_Gobierno/03_Luisa_Abreu_Gonzalez/01DP_LEAG.xlsx\n",
      "Data/3de3_CDMX/Jefatura_de_Gobierno/02_Diana_Alarcon_Gonzalez/01_DP_DAG.xlsx\n",
      "Data/3de3_CDMX/Jefatura_de_Gobierno/01_Claudia_Sheinbaum_Pardo/01_DP_CSP.xlsx\n",
      "Data/3de3_CDMX/Jefatura_de_Gobierno/02_Tomas_Pliego_Calvo/01_DP_TPC.xlsx\n",
      "Data/3de3_CDMX/Jefatura_de_Gobierno/02_Carlos_Alberto_Ulloa_Perez/01_DP_CAUP.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Turismo/01_Carlos_Mackinlay_Grohmann/01DP_CMG.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Turismo/03_Jorge_Guerrero_Carrasco/01_DP_JGC.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Turismo/03_Sara_Riojas_de_la_Peдa/01_DP_SRP.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Turismo/03_Armando_Mujica_Romo/01_DP_ARM.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Movilidad/03_Guillermo_Calderon_Aguilera/01_DP_GCA.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Movilidad/03_Maria_del_Rosario_Castro/01_DP_MRCE.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Movilidad/02_Roberto_Capuano_Metrobus/01 DP RSCT Metrobus.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Movilidad/03_Margarito_Rojas_Salas/01_DP_MRS.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Movilidad/02_Rodrigo_Diaz_Gonzalez/01_DP_RDG.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Movilidad/02_Florencia_Serrania_Soto/01_DP_FSS.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Movilidad/03_Alfredo_Hernandez_Raigosa/01_DP_AHR.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Movilidad/01_Andres_Lajous_Loaeza/01_DP_ALL.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Movilidad/03_Monica_Martinez_Cadena/01_DP_MMMC.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Movilidad/03_Salvador_Medina_Ramirez/01_DP_SM.xlsx\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data/3de3_CDMX/Secretaria_de_Movilidad/02_Maria_de_los_Angeles_Munoz/01_DP_MAM.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Desarrollo_Economico/02_Roxana_Aguirre_Elizondo/01_DP_RAE.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Desarrollo_Economico/03_Jose_Alberto_Valdes_Palacios/01_DP_JAVP.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Desarrollo_Economico/01_Jose_Luis_Beato_Gonzalez/01_DP_JLBG.xlsx\n",
      "Data/3de3_CDMX/Comision_de_Reconstruccion/01_Cesar_Arnulfo_Cravioto_Romero/01_DP_CARC.xlsx\n",
      "Data/3de3_CDMX/Comision_de_Reconstruccion/03_Mario_Isaac_Valderrama_Gonzalez/01_DP_MIGV.xlsx\n",
      "Data/3de3_CDMX/Centro_Comando_Control_Computo_Comunicaciones_Contacto_Ciudadano_C5/03_Juan_Antonio_Suarez_Sanchez/01_DP_JASS.xlsx\n",
      "Data/3de3_CDMX/Centro_Comando_Control_Computo_Comunicaciones_Contacto_Ciudadano_C5/03_Edith_Palomera/01_DP_EPM.xlsx\n",
      "Data/3de3_CDMX/Centro_Comando_Control_Computo_Comunicaciones_Contacto_Ciudadano_C5/03_Francisco_Saldaдa/01_DP_FSL.xlsx\n",
      "Data/3de3_CDMX/Centro_Comando_Control_Computo_Comunicaciones_Contacto_Ciudadano_C5/01_Juan_Manuel_Garcia_Ortegon/01_DP_JMGO.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gestion_Integral_de_Riesgos_y_Proteccion_Civil/02_Carlos_Miguel_Sainz_Luna/01_DP_CMSL.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gestion_Integral_de_Riesgos_y_Proteccion_Civil/03_Hector_Asdrubal_Scobell/01_DP_HASU.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gestion_Integral_de_Riesgos_y_Proteccion_Civil/03_Rafael_Marin_Cambranis/01_DP_RMC.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gestion_Integral_de_Riesgos_y_Proteccion_Civil/01_Myriam_Urzua_Vanegas/01_DP_MUV.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gestion_Integral_de_Riesgos_y_Proteccion_Civil/03_Veronica_Carmona_Avila/01_DP_VCA.xlsx\n",
      "Data/3de3_CDMX/Secretaria_de_Gestion_Integral_de_Riesgos_y_Proteccion_Civil/03_Norlang_Marcel_Garcia_Arroliga/01_DP_NMGA.xlsx\n",
      "Data/3de3_CDMX/Consejeria_Juridica/01_Hector_Villegas_Sandoval/01_DP_HVS.xlsx\n",
      "Data/3de3_CDMX/Secretaria_del_Medio_Ambiente/03_Claudia_Hernandez_Fernandez/01_DP_CHF.xlsx\n",
      "Data/3de3_CDMX/Secretaria_del_Medio_Ambiente/01_Marina_Robles_Garcia/01_DP_MRG.xlsx\n"
     ]
    }
   ],
   "source": [
    "directorio_raiz =  pathlib.Path('Data/3de3_CDMX/')\n",
    "\n",
    "for archivos_1 in directorio_raiz.iterdir():\n",
    "    directorio_2=str(archivos_1)\n",
    "    directorio_2=pathlib.Path(directorio_2)\n",
    "    for archivos_2 in directorio_2.iterdir():\n",
    "        directorio_3=str(archivos_2)\n",
    "        directorio_3_aux=str(directorio_3)\n",
    "        if(directorio_3_aux.count('pdf')==0 and directorio_3_aux.count('xlsx')==0):\n",
    "            directorio_3 = pathlib.Path(directorio_3)\n",
    "            for archivos_3 in directorio_3.iterdir():\n",
    "                excel=str(archivos_3)\n",
    "                if(excel.count('xls')==1):\n",
    "                    find_DP_xlsx(str(archivos_3))\n",
    "        else:\n",
    "            find_DP_xlsx(str(directorio_3_aux))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pprint import pprint\n",
    "\n",
    "json_completo=[]\n",
    "directorio_jsons =  pathlib.Path('Output/Patrimonio/jsons/')\n",
    "\n",
    "for archivo in directorio_jsons.iterdir():\n",
    "    with open(str(archivo)) as f:\n",
    "        data = json.load(f)\n",
    "        json_completo.append(data)\n",
    "#pprint(json_completo)\n",
    "\n",
    "with open('Output/Patrimonio/json_completo/json_completo_patrimonio.json', 'w') as fp:\n",
    "    json.dump(json_completo, fp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}