{"cells":[{"cell_type":"markdown","metadata":{"id":"8wFwUS7Tq6Hq"},"source":["##Cargamos bibliotecas y todo lo que usaremos"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"wz2NB2-Rq_gE"},"outputs":[],"source":["from google.colab import files\n","import pandas as pd"]},{"cell_type":"markdown","metadata":{"id":"1taD7hr4e00L"},"source":["##Panel de buen gobierno 1984-2021\n","1. limpiamos bases de datos\n","2. Seleccionamos lo de interés para el estudio\n"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":73},"executionInfo":{"elapsed":513862,"status":"ok","timestamp":1694890484304,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"456093-eSQnn","outputId":"5e083078-83b7-447e-9c76-847cbd718504"},"outputs":[{"output_type":"display_data","data":{"text/plain":[""],"text/html":["\n"," \n"," \n"," Upload widget is only available when the cell has been executed in the\n"," current browser session. Please rerun this cell to enable.\n"," \n"," "]},"metadata":{}},{"output_type":"stream","name":"stdout","text":["Saving PANEL_BUEN_GOBIERNO(2022) (1).xlsx to PANEL_BUEN_GOBIERNO(2022) (1).xlsx\n"]}],"source":["uploaded = files.upload()\n","buen_gobierno = pd.read_excel('PANEL_BUEN_GOBIERNO(2022) (1).xlsx')"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":256},"executionInfo":{"elapsed":131701,"status":"ok","timestamp":1694890680305,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"Pyb_9pd_v_1L","outputId":"f9e6cdae-3aaf-4963-fc75-298a9cf54fd6"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio ano y_total y_corr y_corr_tribut y_corr_tribut_predial \\\n","0 5001 1984 7977.000 7901.200 5579.800 2546.700 \n","1 5001 1985 9145.100 9108.300 6651.300 2914.600 \n","2 5001 1986 14371.700 14346.000 9965.800 4959.400 \n","3 5001 1987 18913.102 18905.226 13640.715 5850.728 \n","4 5001 1988 26960.038 25953.670 17951.819 7858.774 \n","\n"," y_corr_tribut_IyC y_corr_tribut_gasol y_corr_tribut_otros_1 \\\n","0 2573.900 NaN 459.200012 \n","1 3073.300 NaN 663.400024 \n","2 4228.200 NaN 778.200012 \n","3 6470.930 NaN 1319.057007 \n","4 8166.824 NaN 1926.220947 \n","\n"," y_corr_tribut_otros_2 ... IGA_secep_datos_abiertos_16 \\\n","0 NaN ... NaN \n","1 NaN ... NaN \n","2 NaN ... NaN \n","3 NaN ... NaN \n","4 NaN ... NaN \n","\n"," IGA_sicep_publicidad_16 IGA_suit_16 IGA_transrend_cuentas_16 \\\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","\n"," IGA_sicep_anticorr_16 IGA_sicep_mapa_riesgos_16 IGA_sicep_control_16 \\\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","\n"," IGA_sicep_rendicion_16 IGA_atencion_ciud_16 IGA_sicep_ciudadano_16 \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","\n","[5 rows x 264 columns]"],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpioanoy_totaly_corry_corr_tributy_corr_tribut_predialy_corr_tribut_IyCy_corr_tribut_gasoly_corr_tribut_otros_1y_corr_tribut_otros_2...IGA_secep_datos_abiertos_16IGA_sicep_publicidad_16IGA_suit_16IGA_transrend_cuentas_16IGA_sicep_anticorr_16IGA_sicep_mapa_riesgos_16IGA_sicep_control_16IGA_sicep_rendicion_16IGA_atencion_ciud_16IGA_sicep_ciudadano_16
0500119847977.0007901.2005579.8002546.7002573.900NaN459.200012NaN...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
1500119859145.1009108.3006651.3002914.6003073.300NaN663.400024NaN...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
25001198614371.70014346.0009965.8004959.4004228.200NaN778.200012NaN...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
35001198718913.10218905.22613640.7155850.7286470.930NaN1319.057007NaN...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
45001198826960.03825953.67017951.8197858.7748166.824NaN1926.220947NaN...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
\n","

5 rows × 264 columns

\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":3}],"source":["#mostramos el archivo\n","buen_gobierno = pd.read_excel('PANEL_BUEN_GOBIERNO(2022) (1).xlsx')\n","buen_gobierno.head()\n"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"NgZKzzU221U2"},"outputs":[],"source":["#Convertir codmpio a str\n","buen_gobierno['codmpio'] = buen_gobierno['codmpio'].astype(str)\n"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":256},"executionInfo":{"elapsed":481,"status":"ok","timestamp":1694890687792,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"rVLAZnQJygDo","outputId":"e688a1b5-c298-48bb-9051-5f061ac15ce8"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio ano y_total y_corr y_corr_tribut y_corr_tribut_predial \\\n","32077 68001 1984 904.800 904.800 542.700 181.800 \n","32078 68001 1985 1101.600 1101.600 581.600 220.400 \n","32079 68001 1986 1713.300 1713.300 904.600 335.200 \n","32080 68001 1987 2678.162 2678.162 1170.913 372.011 \n","32081 68001 1988 2724.948 2724.948 1239.013 367.826 \n","\n"," y_corr_tribut_IyC y_corr_tribut_gasol y_corr_tribut_otros_1 \\\n","32077 256.400 NaN 104.500000 \n","32078 301.000 NaN 60.200001 \n","32079 360.600 NaN 208.800003 \n","32080 548.265 NaN 250.636993 \n","32081 672.525 NaN 198.662003 \n","\n"," y_corr_tribut_otros_2 ... IGA_secep_datos_abiertos_16 \\\n","32077 NaN ... NaN \n","32078 NaN ... NaN \n","32079 NaN ... NaN \n","32080 NaN ... NaN \n","32081 NaN ... NaN \n","\n"," IGA_sicep_publicidad_16 IGA_suit_16 IGA_transrend_cuentas_16 \\\n","32077 NaN NaN NaN \n","32078 NaN NaN NaN \n","32079 NaN NaN NaN \n","32080 NaN NaN NaN \n","32081 NaN NaN NaN \n","\n"," IGA_sicep_anticorr_16 IGA_sicep_mapa_riesgos_16 IGA_sicep_control_16 \\\n","32077 NaN NaN NaN \n","32078 NaN NaN NaN \n","32079 NaN NaN NaN \n","32080 NaN NaN NaN \n","32081 NaN NaN NaN \n","\n"," IGA_sicep_rendicion_16 IGA_atencion_ciud_16 IGA_sicep_ciudadano_16 \n","32077 NaN NaN NaN \n","32078 NaN NaN NaN \n","32079 NaN NaN NaN \n","32080 NaN NaN NaN \n","32081 NaN NaN NaN \n","\n","[5 rows x 264 columns]"],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpioanoy_totaly_corry_corr_tributy_corr_tribut_predialy_corr_tribut_IyCy_corr_tribut_gasoly_corr_tribut_otros_1y_corr_tribut_otros_2...IGA_secep_datos_abiertos_16IGA_sicep_publicidad_16IGA_suit_16IGA_transrend_cuentas_16IGA_sicep_anticorr_16IGA_sicep_mapa_riesgos_16IGA_sicep_control_16IGA_sicep_rendicion_16IGA_atencion_ciud_16IGA_sicep_ciudadano_16
32077680011984904.800904.800542.700181.800256.400NaN104.500000NaN...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
320786800119851101.6001101.600581.600220.400301.000NaN60.200001NaN...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
320796800119861713.3001713.300904.600335.200360.600NaN208.800003NaN...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
320806800119872678.1622678.1621170.913372.011548.265NaN250.636993NaN...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
320816800119882724.9482724.9481239.013367.826672.525NaN198.662003NaN...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
\n","

5 rows × 264 columns

\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":5}],"source":["# Conservar los municipios cuyos códigos comienzan con \"68\" que es el código de santander según el DANE\n","buen_gobierno['codmpio'] = buen_gobierno['codmpio'].astype(str)\n","mpios_santander = buen_gobierno[buen_gobierno['codmpio'].str.startswith('68')]\n","mpios_santander.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":881,"status":"ok","timestamp":1694890690529,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"SrUqsfQi_Jy_","outputId":"77b10486-2eba-4488-a95b-9f26677dee9d"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio ano y_corr y_corr_tribut y_cap_regalias y_cap_transf \\\n","0 68001 1984 904.800 542.700 0.0 0.0 \n","1 68001 1985 1101.600 581.600 0.0 0.0 \n","2 68001 1986 1713.300 904.600 0.0 0.0 \n","3 68001 1987 2678.162 1170.913 0.0 0.0 \n","4 68001 1988 2724.948 1239.013 0.0 0.0 \n","\n"," DF_desemp_fisc \n","0 NaN \n","1 NaN \n","2 NaN \n","3 NaN \n","4 NaN "],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpioanoy_corry_corr_tributy_cap_regaliasy_cap_transfDF_desemp_fisc
0680011984904.800542.7000.00.0NaN
16800119851101.600581.6000.00.0NaN
26800119861713.300904.6000.00.0NaN
36800119872678.1621170.9130.00.0NaN
46800119882724.9481239.0130.00.0NaN
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":6}],"source":["#Vamos a eliminar las columnas que no necesitamos\n","columnas= [\"codmpio\",\"ano\",\"y_cap_regalias\",\"y_cap_transf\",\"y_corr_tribut\",\"y_corr\",\"DF_desemp_fisc\"]\n","\n","mpios_santander = mpios_santander[mpios_santander.columns[mpios_santander.columns.isin(columnas)]]\n","mpios_santander = mpios_santander.reset_index(drop=True)\n","mpios_santander.to_excel('mpios_sant.xlsx')\n","mpios_santander.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"yjrwLGv-DRAe"},"outputs":[],"source":["#Eliminar años que no necesitamos\n","\n","mpios_sant = pd.read_excel('mpios_sant.xlsx')\n","anos_a_eliminar = [1984, 1985, 1986, 1987, 1988, 1989, 1990, 1991, 1992,1993,1994,1995,1996,1997,1998,1999,2020,2019,2021]\n","mpios_sant = mpios_sant.reset_index(drop=True)\n","mpios_sant = mpios_sant[~mpios_sant['ano'].isin(anos_a_eliminar)]\n","mpios_sant.to_excel('mpios_sant.xlsx', index=False)\n"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":771},"executionInfo":{"elapsed":246,"status":"ok","timestamp":1694890700535,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"_PVk2YdBGba7","outputId":"0a3b5aca-a76c-4e4d-9026-38d973abd429"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" Unnamed: 0 codmpio ano y_corr y_corr_tribut y_cap_regalias \\\n","16 16 68001 2000 38027.384000 29644.251000 11.744000 \n","17 17 68001 2001 55361.275000 32617.510000 0.254000 \n","18 18 68001 2002 52049.264000 45163.474000 2.604000 \n","19 19 68001 2003 66579.133000 49429.412000 10.665000 \n","20 20 68001 2004 75115.795000 60964.019000 0.000000 \n","21 21 68001 2005 124223.587000 90557.093000 0.000000 \n","22 22 68001 2006 122419.521000 96331.467000 11.297000 \n","23 23 68001 2007 132778.625000 112534.465000 14.499000 \n","24 24 68001 2008 137651.775000 127507.448000 15.022000 \n","25 25 68001 2009 173908.027000 156260.894000 13828.888000 \n","26 26 68001 2010 181524.864000 168307.129000 31.584000 \n","27 27 68001 2011 197990.659000 183306.851000 43.103000 \n","28 28 68001 2012 208772.208000 194044.065000 20.640000 \n","29 29 68001 2013 249167.925000 236800.685000 10.187000 \n","30 30 68001 2014 344259.928000 266205.744000 0.580000 \n","31 31 68001 2015 357765.156250 306018.093750 25.518999 \n","32 32 68001 2016 325807.049000 312202.558000 0.000000 \n","33 33 68001 2017 351391.384766 318846.054688 13.180396 \n","34 34 68001 2018 357825.145434 325972.544080 3.327649 \n","54 54 68013 2000 242.602000 13.323000 0.000000 \n","55 55 68013 2001 377.824000 15.486000 0.000000 \n","56 56 68013 2002 272.870000 16.342000 7.857000 \n","57 57 68013 2003 323.562000 14.817000 0.000000 \n","\n"," y_cap_transf DF_desemp_fisc \n","16 28731.245000 57.250000 \n","17 7635.891000 64.620000 \n","18 37284.485000 60.550000 \n","19 95735.166000 59.500000 \n","20 121536.434000 64.570000 \n","21 140453.156000 70.540875 \n","22 143607.574000 70.320000 \n","23 144499.081000 75.420000 \n","24 186072.932000 75.410000 \n","25 199026.924000 71.850000 \n","26 192458.587000 87.010000 \n","27 204361.351000 79.540000 \n","28 209814.755000 79.530000 \n","29 238482.753000 83.120000 \n","30 252234.580000 82.808481 \n","31 271347.375000 83.429074 \n","32 279230.949000 75.960000 \n","33 325309.312500 79.759843 \n","34 346592.259808 79.544426 \n","54 1002.753000 50.540000 \n","55 1107.451000 57.130000 \n","56 1559.399000 52.290000 \n","57 1321.637000 50.440000 "],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
Unnamed: 0codmpioanoy_corry_corr_tributy_cap_regaliasy_cap_transfDF_desemp_fisc
161668001200038027.38400029644.25100011.74400028731.24500057.250000
171768001200155361.27500032617.5100000.2540007635.89100064.620000
181868001200252049.26400045163.4740002.60400037284.48500060.550000
191968001200366579.13300049429.41200010.66500095735.16600059.500000
202068001200475115.79500060964.0190000.000000121536.43400064.570000
2121680012005124223.58700090557.0930000.000000140453.15600070.540875
2222680012006122419.52100096331.46700011.297000143607.57400070.320000
2323680012007132778.625000112534.46500014.499000144499.08100075.420000
2424680012008137651.775000127507.44800015.022000186072.93200075.410000
2525680012009173908.027000156260.89400013828.888000199026.92400071.850000
2626680012010181524.864000168307.12900031.584000192458.58700087.010000
2727680012011197990.659000183306.85100043.103000204361.35100079.540000
2828680012012208772.208000194044.06500020.640000209814.75500079.530000
2929680012013249167.925000236800.68500010.187000238482.75300083.120000
3030680012014344259.928000266205.7440000.580000252234.58000082.808481
3131680012015357765.156250306018.09375025.518999271347.37500083.429074
3232680012016325807.049000312202.5580000.000000279230.94900075.960000
3333680012017351391.384766318846.05468813.180396325309.31250079.759843
3434680012018357825.145434325972.5440803.327649346592.25980879.544426
5454680132000242.60200013.3230000.0000001002.75300050.540000
5555680132001377.82400015.4860000.0000001107.45100057.130000
5656680132002272.87000016.3420007.8570001559.39900052.290000
5757680132003323.56200014.8170000.0000001321.63700050.440000
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":8}],"source":["mpios_sant.head(23)"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"elapsed":413,"status":"ok","timestamp":1694890703432,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"6VvZF1s4JuTw","outputId":"0d194dea-0e8d-4b5f-f5c9-2e1fc4faf10b"},"outputs":[{"output_type":"stream","name":"stdout","text":["Unnamed: 0 int64\n","codmpio int64\n","ano int64\n","y_corr float64\n","y_corr_tribut float64\n","y_cap_regalias float64\n","y_cap_transf float64\n","DF_desemp_fisc float64\n","dtype: object\n"]}],"source":["#Conocer el tipo de variable que tenemos\n","variables = pd.read_excel('mpios_sant.xlsx')\n","tipos_variables = variables.dtypes\n","print(tipos_variables)"]},{"cell_type":"markdown","metadata":{"id":"rrEsRCL6y7GC"},"source":["##IPC 1992-2022\n","1. Cargamos archivo con los IPC para poder pasar todos los valores a términos reales"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":262},"executionInfo":{"elapsed":15149,"status":"ok","timestamp":1694890721148,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"kXX-g5f0gPip","outputId":"e08c0d4e-e72e-47a4-c303-56f4c39cb4e2"},"outputs":[{"output_type":"display_data","data":{"text/plain":[""],"text/html":["\n"," \n"," \n"," Upload widget is only available when the cell has been executed in the\n"," current browser session. Please rerun this cell to enable.\n"," \n"," "]},"metadata":{}},{"output_type":"stream","name":"stdout","text":["Saving Índice de precios al consumidor.xlsx to Índice de precios al consumidor.xlsx\n"]},{"output_type":"execute_result","data":{"text/plain":[" Año IPC año base 2018 último mes IPC año base 2018 prom Dividido en 100\n","0 1992 12.14 11.326667 0.113267\n","1 1993 14.89 13.868333 0.138683\n","2 1994 18.25 17.035833 0.170358\n","3 1995 21.80 20.594167 0.205942\n","4 1996 26.52 24.879167 0.248792"],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
AñoIPC año base 2018 último mesIPC año base 2018 promDividido en 100
0199212.1411.3266670.113267
1199314.8913.8683330.138683
2199418.2517.0358330.170358
3199521.8020.5941670.205942
4199626.5224.8791670.248792
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":10}],"source":["uploaded = files.upload()\n","indice_precios = pd.read_excel('Índice de precios al consumidor.xlsx')\n","indice_precios.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":257,"status":"ok","timestamp":1694890723946,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"-3Bhy5gRDjop","outputId":"d7948257-4b06-4284-bc6d-9504d4437098"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" ano IPC año base 2018 último mes IPC año base 2018 prom Dividido en 100\n","0 1992 12.14 11.326667 0.113267\n","1 1993 14.89 13.868333 0.138683\n","2 1994 18.25 17.035833 0.170358\n","3 1995 21.80 20.594167 0.205942\n","4 1996 26.52 24.879167 0.248792"],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
anoIPC año base 2018 último mesIPC año base 2018 promDividido en 100
0199212.1411.3266670.113267
1199314.8913.8683330.138683
2199418.2517.0358330.170358
3199521.8020.5941670.205942
4199626.5224.8791670.248792
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":11}],"source":["#Renombramos columnas\n","indice_precios = indice_precios.rename(columns={'Año': 'ano'})\n","indice_precios.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"elapsed":244,"status":"ok","timestamp":1694890726958,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"uuv1U8Bj3k_j","outputId":"f502f80e-3424-420b-e1aa-1abf8a61677d"},"outputs":[{"output_type":"stream","name":"stdout","text":[" ano IPC año base 2018 último mes IPC año base 2018 prom \\\n","8 2000 43.27 42.358333 \n","9 2001 46.58 45.735000 \n","10 2002 49.83 48.638333 \n","11 2003 53.07 52.107500 \n","12 2004 55.99 55.184167 \n","13 2005 58.70 57.968333 \n","14 2006 61.33 60.460000 \n","15 2007 64.82 63.811667 \n","16 2008 69.80 68.277500 \n","17 2009 71.20 71.146667 \n","18 2010 73.45 72.760833 \n","19 2011 76.19 75.246667 \n","20 2012 78.05 77.631667 \n","21 2013 79.56 79.198333 \n","22 2014 82.47 81.495833 \n","23 2015 88.05 85.560000 \n","24 2016 93.11 91.988333 \n","25 2017 96.92 95.958333 \n","26 2018 100.00 100.000000 \n","\n"," Dividido en 100 \n","8 0.423583 \n","9 0.457350 \n","10 0.486383 \n","11 0.521075 \n","12 0.551842 \n","13 0.579683 \n","14 0.604600 \n","15 0.638117 \n","16 0.682775 \n","17 0.711467 \n","18 0.727608 \n","19 0.752467 \n","20 0.776317 \n","21 0.791983 \n","22 0.814958 \n","23 0.855600 \n","24 0.919883 \n","25 0.959583 \n","26 1.000000 \n"]}],"source":["#Eliminamos años que no necesitamos\n","anos_a_eliminar = [1992, 1993,1994,1995,1996,1997,1998,1999, 2021,2019,2020,2022]\n","indice_precios = indice_precios.reset_index(drop=True)\n","indice_precios = indice_precios[~indice_precios['ano'].isin(anos_a_eliminar)]\n","\n","indice_precios.to_excel('Índice de precios al consumidor.xlsx', index=False)\n","print(indice_precios)"]},{"cell_type":"markdown","metadata":{"id":"PRtfhPpwpKKy"},"source":["##Indicador de descentralización\n","1. Pasamos valores a términos reales\n","2. Creamos indicador\n","\n"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":1003,"status":"ok","timestamp":1694890732751,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"MTrcBJaWAoG4","outputId":"93e76eeb-a7a5-46f0-d2ad-5d6a509ef69f"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" Unnamed: 0 codmpio ano y_corr y_corr_tribut y_cap_regalias \\\n","0 16 68001 2000 38027.384 29644.251 11.744 \n","1 54 68013 2000 242.602 13.323 0.000 \n","2 92 68020 2000 467.448 21.287 0.000 \n","3 130 68051 2000 448.296 142.836 0.000 \n","4 168 68077 2000 1119.658 797.966 0.000 \n","\n"," y_cap_transf DF_desemp_fisc IPC año base 2018 último mes \\\n","0 28731.245 57.25 43.27 \n","1 1002.753 50.54 43.27 \n","2 1455.724 51.35 43.27 \n","3 1190.770 49.30 43.27 \n","4 2550.389 52.22 43.27 \n","\n"," IPC año base 2018 prom Dividido en 100 \n","0 42.358333 0.423583 \n","1 42.358333 0.423583 \n","2 42.358333 0.423583 \n","3 42.358333 0.423583 \n","4 42.358333 0.423583 "],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
Unnamed: 0codmpioanoy_corry_corr_tributy_cap_regaliasy_cap_transfDF_desemp_fiscIPC año base 2018 último mesIPC año base 2018 promDividido en 100
01668001200038027.38429644.25111.74428731.24557.2543.2742.3583330.423583
154680132000242.60213.3230.0001002.75350.5443.2742.3583330.423583
292680202000467.44821.2870.0001455.72451.3543.2742.3583330.423583
3130680512000448.296142.8360.0001190.77049.3043.2742.3583330.423583
41686807720001119.658797.9660.0002550.38952.2243.2742.3583330.423583
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":13}],"source":["#Uniendo IPC, Buen gobierno y Balance nacional\n","mpios_sant = pd.read_excel('mpios_sant.xlsx')\n","ipc = pd.read_excel('Índice de precios al consumidor.xlsx')\n","data = pd.merge(mpios_sant, ipc, on='ano')\n","data.to_excel('data.xlsx', index=False)\n","data.head()\n"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":224},"executionInfo":{"elapsed":796,"status":"ok","timestamp":1694890736217,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"WpHCDwwqFqSV","outputId":"eab34e07-b041-4cb8-b20e-94ebb970eef5"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" Unnamed: 0 codmpio ano y_corr y_corr_tribut y_cap_regalias \\\n","0 16 68001 2000 38027.384 29644.251 11.744 \n","63 2410 68573 2000 0.000 0.000 0.000 \n","62 2372 68572 2000 1481.290 420.014 0.000 \n","61 2334 68549 2000 409.310 201.156 0.000 \n","60 2296 68547 2000 3602.585 3040.526 0.000 \n","\n"," y_cap_transf DF_desemp_fisc IPC año base 2018 último mes \\\n","0 28731.245 57.25 43.27 \n","63 0.000 NaN 43.27 \n","62 2635.163 64.23 43.27 \n","61 841.483 56.08 43.27 \n","60 3419.078 50.31 43.27 \n","\n"," IPC año base 2018 prom Dividido en 100 y_corr_real y_corr_tribut_real \\\n","0 42.358333 0.423583 89775.449144 69984.460358 \n","63 42.358333 0.423583 0.000000 0.000000 \n","62 42.358333 0.423583 3497.045052 991.573480 \n","61 42.358333 0.423583 966.303364 474.891206 \n","60 42.358333 0.423583 8505.020657 7178.105843 \n","\n"," y_cap_regalias_real y_cap_transf_real \n","0 27.725359 67829.026166 \n","63 0.000000 0.000000 \n","62 0.000000 6221.120598 \n","61 0.000000 1986.581940 \n","60 0.000000 8071.795396 "],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
Unnamed: 0codmpioanoy_corry_corr_tributy_cap_regaliasy_cap_transfDF_desemp_fiscIPC año base 2018 último mesIPC año base 2018 promDividido en 100y_corr_realy_corr_tribut_realy_cap_regalias_realy_cap_transf_real
01668001200038027.38429644.25111.74428731.24557.2543.2742.3583330.42358389775.44914469984.46035827.72535967829.026166
6324106857320000.0000.0000.0000.000NaN43.2742.3583330.4235830.0000000.0000000.0000000.000000
6223726857220001481.290420.0140.0002635.16364.2343.2742.3583330.4235833497.045052991.5734800.0000006221.120598
612334685492000409.310201.1560.000841.48356.0843.2742.3583330.423583966.303364474.8912060.0000001986.581940
6022966854720003602.5853040.5260.0003419.07850.3143.2742.3583330.4235838505.0206577178.1058430.0000008071.795396
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":14}],"source":["#Ordenamos la tabla y calculamos valores reales\n","data = pd.read_excel('data.xlsx')\n","data = data.sort_values(by='ano')\n","data['y_corr_real'] = data['y_corr'] / data['Dividido en 100']\n","data['y_corr_tribut_real'] = data['y_corr_tribut'] / data['Dividido en 100']\n","data['y_cap_regalias_real'] = data['y_cap_regalias'] / data['Dividido en 100']\n","data['y_cap_transf_real'] = data['y_cap_transf'] / data['Dividido en 100']\n","data.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":662,"status":"ok","timestamp":1694890739934,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"tCdsi4MNNC7e","outputId":"9d596a69-0b6e-4098-b609-55abe3ef01b8"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio ano DF_desemp_fisc y_corr_real y_corr_tribut_real \\\n","0 68001 2000 57.25 89775.449144 69984.460358 \n","1 68573 2000 NaN 0.000000 0.000000 \n","2 68572 2000 64.23 3497.045052 991.573480 \n","3 68549 2000 56.08 966.303364 474.891206 \n","4 68547 2000 50.31 8505.020657 7178.105843 \n","\n"," y_cap_regalias_real y_cap_transf_real \n","0 27.725359 67829.026166 \n","1 0.000000 0.000000 \n","2 0.000000 6221.120598 \n","3 0.000000 1986.581940 \n","4 0.000000 8071.795396 "],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpioanoDF_desemp_fiscy_corr_realy_corr_tribut_realy_cap_regalias_realy_cap_transf_real
068001200057.2589775.44914469984.46035827.72535967829.026166
1685732000NaN0.0000000.0000000.0000000.000000
268572200064.233497.045052991.5734800.0000006221.120598
368549200056.08966.303364474.8912060.0000001986.581940
468547200050.318505.0206577178.1058430.0000008071.795396
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":15}],"source":["#Eliminamos columnas que no necesitamos\n","columnas= [\"codmpio\",\"ano\",\"y_corr_real\",\"y_corr_tribut_real\",\"y_cap_regalias_real\",\"y_cap_transf_real\",\"DF_desemp_fisc\"]\n","data = data[data.columns[data.columns.isin(columnas)]]\n","data = data.reset_index(drop=True)\n","data.to_excel('data.xlsx')\n","data.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":258,"status":"ok","timestamp":1694890742907,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"MjDRBUkbN6GP","outputId":"a7260889-66f1-4a80-b510-08c047dfe5ee"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio ano DF_desemp_fisc y_cap_regalias_real y_cap_transf_real \\\n","0 68001 2000 57.25 27.725359 67829.026166 \n","1 68573 2000 NaN 0.000000 0.000000 \n","2 68572 2000 64.23 0.000000 6221.120598 \n","3 68549 2000 56.08 0.000000 1986.581940 \n","4 68547 2000 50.31 0.000000 8071.795396 \n","\n"," autonomia_tributaria \n","0 0.779550 \n","1 NaN \n","2 0.283546 \n","3 0.491451 \n","4 0.843985 "],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpioanoDF_desemp_fiscy_cap_regalias_realy_cap_transf_realautonomia_tributaria
068001200057.2527.72535967829.0261660.779550
1685732000NaN0.0000000.000000NaN
268572200064.230.0000006221.1205980.283546
368549200056.080.0000001986.5819400.491451
468547200050.310.0000008071.7953960.843985
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":16}],"source":["#Creamos índices\n","data['autonomia_tributaria'] = data['y_corr_tribut_real'] / data['y_corr_real']\n","columnas= [\"codmpio\",\"ano\", \"y_cap_regalias_real\",\"y_cap_transf_real\",\"autonomia_tributaria\",\"DF_desemp_fisc\"]\n","data = data[data.columns[data.columns.isin(columnas)]]\n","data = data.reset_index(drop=True)\n","data.to_excel('data_2.xlsx')\n","data.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":975,"status":"ok","timestamp":1694890762628,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"8qDfDs2jQdjZ","outputId":"d15d3694-ecf9-4fcc-f5dc-50090efb555d"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio ano DF_desemp_fisc y_cap_regalias_real y_cap_transf_real \\\n","0 68001 2000 57.25 27.725359 67829.026166 \n","1 68573 2000 NaN 0.000000 0.000000 \n","2 68572 2000 64.23 0.000000 6221.120598 \n","3 68549 2000 56.08 0.000000 1986.581940 \n","4 68547 2000 50.31 0.000000 8071.795396 \n","\n"," autonomia_tributaria \n","0 0.779550 \n","1 NaN \n","2 0.283546 \n","3 0.491451 \n","4 0.843985 "],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpioanoDF_desemp_fiscy_cap_regalias_realy_cap_transf_realautonomia_tributaria
068001200057.2527.72535967829.0261660.779550
1685732000NaN0.0000000.000000NaN
268572200064.230.0000006221.1205980.283546
368549200056.080.0000001986.5819400.491451
468547200050.310.0000008071.7953960.843985
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":17}],"source":["#Eliminamos columnas lo que no necesitamos\n","columnas= [\"codmpio\",\"ano\", \"y_cap_regalias_real\",\"y_cap_transf_real\",\"autonomia_tributaria\",\"DF_desemp_fisc\"]\n","data = data[data.columns[data.columns.isin(columnas)]]\n","data = data.reset_index(drop=True)\n","data.to_excel('data_2.xlsx')\n","data.head()"]},{"cell_type":"markdown","metadata":{"id":"Xo_WtZ8igMgK"},"source":["##Características generales\n","1. Creamos nuestras variables de control"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":398},"executionInfo":{"elapsed":123640,"status":"ok","timestamp":1694890889693,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"2x_9yZN5Qy8n","outputId":"6e114e9d-ec59-485c-ee57-3661a37edafc"},"outputs":[{"output_type":"display_data","data":{"text/plain":[""],"text/html":["\n"," \n"," \n"," Upload widget is only available when the cell has been executed in the\n"," current browser session. Please rerun this cell to enable.\n"," \n"," "]},"metadata":{}},{"output_type":"stream","name":"stdout","text":["Saving PANEL_CARACTERISTICAS_GENERALES(2022).xlsx to PANEL_CARACTERISTICAS_GENERALES(2022).xlsx\n"]},{"output_type":"execute_result","data":{"text/plain":[" coddepto codprovincia codmpio depto provincia municipio \\\n","0 5 595 5001 Antioquia Valle del aburra Medellín \n","1 5 595 5001 Antioquia Valle del aburra Medellín \n","2 5 595 5001 Antioquia Valle del aburra Medellín \n","3 5 595 5001 Antioquia Valle del aburra Medellín \n","4 5 595 5001 Antioquia Valle del aburra Medellín \n","\n"," ano ao_crea act_adm gandina ... ipm_ti_p ipm_tdep_p ipm_templeof_p \\\n","0 1993 1675 NaN 1 ... NaN NaN NaN \n","1 1994 1675 NaN 1 ... NaN NaN NaN \n","2 1995 1675 NaN 1 ... NaN NaN NaN \n","3 1996 1675 NaN 1 ... NaN NaN NaN \n","4 1997 1675 NaN 1 ... NaN NaN NaN \n","\n"," ipm_assalud_p ipm_accsalud_p ipm_accagua_p ipm_excretas_p ipm_pisos_p \\\n","0 NaN NaN NaN NaN NaN \n","1 NaN NaN NaN NaN NaN \n","2 NaN NaN NaN NaN NaN \n","3 NaN NaN NaN NaN NaN \n","4 NaN NaN NaN NaN NaN \n","\n"," ipm_paredes_p ipm_hacinam_p \n","0 NaN NaN \n","1 NaN NaN \n","2 NaN NaN \n","3 NaN NaN \n","4 NaN NaN \n","\n","[5 rows x 76 columns]"],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
coddeptocodprovinciacodmpiodeptoprovinciamunicipioanoao_creaact_admgandina...ipm_ti_pipm_tdep_pipm_templeof_pipm_assalud_pipm_accsalud_pipm_accagua_pipm_excretas_pipm_pisos_pipm_paredes_pipm_hacinam_p
055955001AntioquiaValle del aburraMedellín19931675NaN1...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
155955001AntioquiaValle del aburraMedellín19941675NaN1...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
255955001AntioquiaValle del aburraMedellín19951675NaN1...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
355955001AntioquiaValle del aburraMedellín19961675NaN1...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
455955001AntioquiaValle del aburraMedellín19971675NaN1...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
\n","

5 rows × 76 columns

\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":18}],"source":["#Cargamos el archivo\n","uploaded = files.upload()\n","carac_generales = pd.read_excel('PANEL_CARACTERISTICAS_GENERALES(2022).xlsx')\n","carac_generales.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":342},"executionInfo":{"elapsed":29139,"status":"ok","timestamp":1694890923347,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"zPx2OuYtR6Ze","outputId":"48f3b54f-d554-48c4-98e5-8e20611fe682"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" coddepto codprovincia codmpio depto provincia municipio \\\n","0 5 595 5001 Antioquia Valle del aburra Medellín \n","1 5 595 5001 Antioquia Valle del aburra Medellín \n","2 5 595 5001 Antioquia Valle del aburra Medellín \n","3 5 595 5001 Antioquia Valle del aburra Medellín \n","4 5 595 5001 Antioquia Valle del aburra Medellín \n","\n"," ano ao_crea act_adm gandina ... ipm_ti_p ipm_tdep_p ipm_templeof_p \\\n","0 1993 1675 NaN 1 ... NaN NaN NaN \n","1 1994 1675 NaN 1 ... NaN NaN NaN \n","2 1995 1675 NaN 1 ... NaN NaN NaN \n","3 1996 1675 NaN 1 ... NaN NaN NaN \n","4 1997 1675 NaN 1 ... NaN NaN NaN \n","\n"," ipm_assalud_p ipm_accsalud_p ipm_accagua_p ipm_excretas_p ipm_pisos_p \\\n","0 NaN NaN NaN NaN NaN \n","1 NaN NaN NaN NaN NaN \n","2 NaN NaN NaN NaN NaN \n","3 NaN NaN NaN NaN NaN \n","4 NaN NaN NaN NaN NaN \n","\n"," ipm_paredes_p ipm_hacinam_p \n","0 NaN NaN \n","1 NaN NaN \n","2 NaN NaN \n","3 NaN NaN \n","4 NaN NaN \n","\n","[5 rows x 76 columns]"],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
coddeptocodprovinciacodmpiodeptoprovinciamunicipioanoao_creaact_admgandina...ipm_ti_pipm_tdep_pipm_templeof_pipm_assalud_pipm_accsalud_pipm_accagua_pipm_excretas_pipm_pisos_pipm_paredes_pipm_hacinam_p
055955001AntioquiaValle del aburraMedellín19931675NaN1...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
155955001AntioquiaValle del aburraMedellín19941675NaN1...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
255955001AntioquiaValle del aburraMedellín19951675NaN1...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
355955001AntioquiaValle del aburraMedellín19961675NaN1...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
455955001AntioquiaValle del aburraMedellín19971675NaN1...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
\n","

5 rows × 76 columns

\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":19}],"source":["#mostramos el archivo\n","carac_generales = pd.read_excel('PANEL_CARACTERISTICAS_GENERALES(2022).xlsx')\n","carac_generales.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"iNgyikZWSbyH"},"outputs":[],"source":["#Convertir codmpio a str\n","carac_generales['codmpio'] = carac_generales['codmpio'].astype(str)"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":603},"executionInfo":{"elapsed":317,"status":"ok","timestamp":1694890929948,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"L9PGu1_NStHt","outputId":"239bd423-09b6-4f90-bde7-bacab37118d7"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" coddepto codprovincia codmpio depto provincia municipio ano \\\n","24505 68 6879 68001 Santander Soto Bucaramanga 1993 \n","24506 68 6879 68001 Santander Soto Bucaramanga 1994 \n","24507 68 6879 68001 Santander Soto Bucaramanga 1995 \n","24508 68 6879 68001 Santander Soto Bucaramanga 1996 \n","24509 68 6879 68001 Santander Soto Bucaramanga 1997 \n","\n"," ao_crea act_adm gandina ... \\\n","24505 1623 Decreto 1488 del 14 de Diciembre de 1906 1 ... \n","24506 1623 Decreto 1488 del 14 de Diciembre de 1906 1 ... \n","24507 1623 Decreto 1488 del 14 de Diciembre de 1906 1 ... \n","24508 1623 Decreto 1488 del 14 de Diciembre de 1906 1 ... \n","24509 1623 Decreto 1488 del 14 de Diciembre de 1906 1 ... \n","\n"," ipm_ti_p ipm_tdep_p ipm_templeof_p ipm_assalud_p ipm_accsalud_p \\\n","24505 NaN NaN NaN NaN NaN \n","24506 NaN NaN NaN NaN NaN \n","24507 NaN NaN NaN NaN NaN \n","24508 NaN NaN NaN NaN NaN \n","24509 NaN NaN NaN NaN NaN \n","\n"," ipm_accagua_p ipm_excretas_p ipm_pisos_p ipm_paredes_p \\\n","24505 NaN NaN NaN NaN \n","24506 NaN NaN NaN NaN \n","24507 NaN NaN NaN NaN \n","24508 NaN NaN NaN NaN \n","24509 NaN NaN NaN NaN \n","\n"," ipm_hacinam_p \n","24505 NaN \n","24506 NaN \n","24507 NaN \n","24508 NaN \n","24509 NaN \n","\n","[5 rows x 76 columns]"],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
coddeptocodprovinciacodmpiodeptoprovinciamunicipioanoao_creaact_admgandina...ipm_ti_pipm_tdep_pipm_templeof_pipm_assalud_pipm_accsalud_pipm_accagua_pipm_excretas_pipm_pisos_pipm_paredes_pipm_hacinam_p
2450568687968001SantanderSotoBucaramanga19931623Decreto 1488 del 14 de Diciembre de 19061...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
2450668687968001SantanderSotoBucaramanga19941623Decreto 1488 del 14 de Diciembre de 19061...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
2450768687968001SantanderSotoBucaramanga19951623Decreto 1488 del 14 de Diciembre de 19061...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
2450868687968001SantanderSotoBucaramanga19961623Decreto 1488 del 14 de Diciembre de 19061...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
2450968687968001SantanderSotoBucaramanga19971623Decreto 1488 del 14 de Diciembre de 19061...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
\n","

5 rows × 76 columns

\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":21}],"source":["# Conservar los municipios cuyos códigos comienzan con \"68\" que es el código de santander según el DANE\n","carac_generales['codmpio'] = carac_generales['codmpio'].astype(str)\n","mpios_santander_carac_gene = carac_generales[carac_generales['codmpio'].str.startswith('68')]\n","mpios_santander_carac_gene.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":1001,"status":"ok","timestamp":1694890933609,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"dpfYcciQTYGU","outputId":"81fad733-bf82-4a41-bdf6-f46b52930b07"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio ano pobl_urb pobl_tot areaoficialkm2 altura discapital \\\n","24505 68001 1993 456641.0 465485 154.0 960.0 0.0 \n","24506 68001 1994 463530.0 472251 154.0 960.0 0.0 \n","24507 68001 1995 470010.0 478635 154.0 960.0 0.0 \n","24508 68001 1996 476054.0 484598 154.0 960.0 0.0 \n","24509 68001 1997 481638.0 490110 154.0 960.0 0.0 \n","\n"," dismdo distancia_mercado \n","24505 0.0 0.0 \n","24506 0.0 0.0 \n","24507 0.0 0.0 \n","24508 0.0 0.0 \n","24509 0.0 0.0 "],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpioanopobl_urbpobl_totareaoficialkm2alturadiscapitaldismdodistancia_mercado
24505680011993456641.0465485154.0960.00.00.00.0
24506680011994463530.0472251154.0960.00.00.00.0
24507680011995470010.0478635154.0960.00.00.00.0
24508680011996476054.0484598154.0960.00.00.00.0
24509680011997481638.0490110154.0960.00.00.00.0
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":22}],"source":["#Conservamos columnas que usaremos\n","columnas= [\"codmpio\",\"ano\",\"pobl_urb\",\"pobl_tot\", \"areaoficialkm2\", \"altura\", \"discapital\", \"dismdo\", \"distancia_mercado\"]\n","mpios_santander_carac_gene = mpios_santander_carac_gene[mpios_santander_carac_gene.columns[mpios_santander_carac_gene.columns.isin(columnas)]]\n","mpios_santander_carac_gene.to_excel('Caracteristicas_gene.xlsx', index=False)\n","mpios_santander_carac_gene.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"Oc2ZQzHi3kwJ"},"outputs":[],"source":["#Eliminamos años que no necesitamos\n","mpios_santander_carac_gene = pd.read_excel('Caracteristicas_gene.xlsx')\n","anos_a_eliminar = [1993,1994,1995,1996,1997,1998,1999, 2020,2019,2021]\n","mpios_santander_carac_gene = mpios_santander_carac_gene.reset_index(drop=True)\n","mpios_santander_carac_gene = mpios_santander_carac_gene[~mpios_santander_carac_gene['ano'].isin(anos_a_eliminar)]\n","mpios_santander_carac_gene.to_excel('Caracteristicas_gene.xlsx', index=False)\n"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":677},"executionInfo":{"elapsed":341,"status":"ok","timestamp":1694890938645,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"3ybXgTQJ4wjT","outputId":"982d8c54-7d15-47d0-c49a-dccd223d30fa"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio ano pobl_urb pobl_tot areaoficialkm2 altura discapital \\\n","7 68001 2000 495391 503541 154.0 960 0.00000 \n","8 68001 2001 498972 506955 154.0 960 0.00000 \n","9 68001 2002 502064 509878 154.0 960 0.00000 \n","10 68001 2003 504738 512380 154.0 960 0.00000 \n","11 68001 2004 507069 514544 154.0 960 0.00000 \n","12 68001 2005 509135 516460 154.0 960 0.00000 \n","13 68001 2006 511052 518260 154.0 960 0.00000 \n","14 68001 2007 512818 519920 154.0 960 0.00000 \n","15 68001 2008 514432 521435 154.0 960 0.00000 \n","16 68001 2009 515892 522806 154.0 960 0.00000 \n","17 68001 2010 517202 524030 154.0 960 0.00000 \n","18 68001 2011 518370 525119 154.0 960 0.00000 \n","19 68001 2012 519384 526056 154.0 960 0.00000 \n","20 68001 2013 520229 526827 154.0 960 0.00000 \n","21 68001 2014 520917 527451 154.0 960 0.00000 \n","22 68001 2015 521446 527913 154.0 960 0.00000 \n","23 68001 2016 521857 528269 154.0 960 0.00000 \n","24 68001 2017 522141 528497 154.0 960 0.00000 \n","25 68001 2018 570752 581130 154.0 960 0.00000 \n","36 68013 2000 240 2412 41.0 1650 115.05381 \n","\n"," dismdo distancia_mercado \n","7 0.00000 0.000000 \n","8 0.00000 0.000000 \n","9 0.00000 0.000000 \n","10 0.00000 0.000000 \n","11 0.00000 0.000000 \n","12 0.00000 0.000000 \n","13 0.00000 0.000000 \n","14 0.00000 0.000000 \n","15 0.00000 0.000000 \n","16 0.00000 0.000000 \n","17 0.00000 0.000000 \n","18 0.00000 0.000000 \n","19 0.00000 0.000000 \n","20 0.00000 0.000000 \n","21 0.00000 0.000000 \n","22 0.00000 0.000000 \n","23 0.00000 0.000000 \n","24 0.00000 0.000000 \n","25 0.00000 0.000000 \n","36 115.05381 41.054234 "],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpioanopobl_urbpobl_totareaoficialkm2alturadiscapitaldismdodistancia_mercado
7680012000495391503541154.09600.000000.000000.000000
8680012001498972506955154.09600.000000.000000.000000
9680012002502064509878154.09600.000000.000000.000000
10680012003504738512380154.09600.000000.000000.000000
11680012004507069514544154.09600.000000.000000.000000
12680012005509135516460154.09600.000000.000000.000000
13680012006511052518260154.09600.000000.000000.000000
14680012007512818519920154.09600.000000.000000.000000
15680012008514432521435154.09600.000000.000000.000000
16680012009515892522806154.09600.000000.000000.000000
17680012010517202524030154.09600.000000.000000.000000
18680012011518370525119154.09600.000000.000000.000000
19680012012519384526056154.09600.000000.000000.000000
20680012013520229526827154.09600.000000.000000.000000
21680012014520917527451154.09600.000000.000000.000000
22680012015521446527913154.09600.000000.000000.000000
23680012016521857528269154.09600.000000.000000.000000
24680012017522141528497154.09600.000000.000000.000000
25680012018570752581130154.09600.000000.000000.000000
36680132000240241241.01650115.05381115.0538141.054234
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":24}],"source":["mpios_santander_carac_gene.head(20)"]},{"cell_type":"markdown","metadata":{"id":"FQH1-QXAdGPi"},"source":["##Índices de urbanuización y densidad poblacional\n"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":311,"status":"ok","timestamp":1694890941729,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"6ALoH851T1kl","outputId":"cd514b9c-51f9-4916-bd69-c61c9e2111c3"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio ano pobl_urb pobl_tot areaoficialkm2 altura discapital \\\n","7 68001 2000 495391 503541 154.0 960 0.0 \n","8 68001 2001 498972 506955 154.0 960 0.0 \n","9 68001 2002 502064 509878 154.0 960 0.0 \n","10 68001 2003 504738 512380 154.0 960 0.0 \n","11 68001 2004 507069 514544 154.0 960 0.0 \n","\n"," dismdo distancia_mercado indurb densidad \n","7 0.0 0.0 0.983815 3269.746753 \n","8 0.0 0.0 0.984253 3291.915584 \n","9 0.0 0.0 0.984675 3310.896104 \n","10 0.0 0.0 0.985085 3327.142857 \n","11 0.0 0.0 0.985473 3341.194805 "],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpioanopobl_urbpobl_totareaoficialkm2alturadiscapitaldismdodistancia_mercadoindurbdensidad
7680012000495391503541154.09600.00.00.00.9838153269.746753
8680012001498972506955154.09600.00.00.00.9842533291.915584
9680012002502064509878154.09600.00.00.00.9846753310.896104
10680012003504738512380154.09600.00.00.00.9850853327.142857
11680012004507069514544154.09600.00.00.00.9854733341.194805
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":25}],"source":["#Creamos índice de urbanización y densidad de población\n","mpios_santander_carac_gene['indurb'] = mpios_santander_carac_gene['pobl_urb'] / mpios_santander_carac_gene['pobl_tot']\n","mpios_santander_carac_gene['densidad'] = mpios_santander_carac_gene['pobl_tot'] / mpios_santander_carac_gene['areaoficialkm2']\n","mpios_santander_carac_gene.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":722,"status":"ok","timestamp":1694890944449,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"PlI4PSC-fOpQ","outputId":"cf602784-d9b7-418d-bbce-a6c199b0ecfb"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio ano altura discapital dismdo distancia_mercado indurb \\\n","7 68001 2000 960 0.0 0.0 0.0 0.983815 \n","8 68001 2001 960 0.0 0.0 0.0 0.984253 \n","9 68001 2002 960 0.0 0.0 0.0 0.984675 \n","10 68001 2003 960 0.0 0.0 0.0 0.985085 \n","11 68001 2004 960 0.0 0.0 0.0 0.985473 \n","\n"," densidad \n","7 3269.746753 \n","8 3291.915584 \n","9 3310.896104 \n","10 3327.142857 \n","11 3341.194805 "],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpioanoalturadiscapitaldismdodistancia_mercadoindurbdensidad
76800120009600.00.00.00.9838153269.746753
86800120019600.00.00.00.9842533291.915584
96800120029600.00.00.00.9846753310.896104
106800120039600.00.00.00.9850853327.142857
116800120049600.00.00.00.9854733341.194805
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":26}],"source":["#Vamos a eliminar las columnas que no necesitamos\n","columnas= [\"codmpio\",\"ano\", \"indurb\",\"densidad\", \"altura\", \"discapital\", \"dismdo\", \"distancia_mercado\"]\n","var_control = mpios_santander_carac_gene[mpios_santander_carac_gene.columns[mpios_santander_carac_gene.columns.isin(columnas)]]\n","var_control.to_excel('Caracteristicas_gene.xlsx', index=False)\n","var_control.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"elapsed":224,"status":"ok","timestamp":1694890947180,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"qyf1xaCqLq-O","outputId":"db080b34-c8ae-4111-e41b-8b96deb742a2"},"outputs":[{"output_type":"stream","name":"stdout","text":["codmpio int64\n","ano int64\n","altura int64\n","discapital float64\n","dismdo float64\n","distancia_mercado float64\n","indurb float64\n","densidad float64\n","dtype: object\n"]}],"source":["#Conocer tipos de variables\n","data = pd.read_excel('Caracteristicas_gene.xlsx')\n","tipos_variables = data.dtypes\n","print(tipos_variables)"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":420,"status":"ok","timestamp":1694890949615,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"_joAMMYyL0Ha","outputId":"388998b1-9771-4821-e9cf-c7f8b303cbf9"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio ano altura discapital dismdo distancia_mercado indurb \\\n","7 68001 2000 960 0.0 0.0 0.0 0.983815 \n","8 68001 2001 960 0.0 0.0 0.0 0.984253 \n","9 68001 2002 960 0.0 0.0 0.0 0.984675 \n","10 68001 2003 960 0.0 0.0 0.0 0.985085 \n","11 68001 2004 960 0.0 0.0 0.0 0.985473 \n","\n"," densidad \n","7 3269.746753 \n","8 3291.915584 \n","9 3310.896104 \n","10 3327.142857 \n","11 3341.194805 "],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpioanoalturadiscapitaldismdodistancia_mercadoindurbdensidad
76800120009600.00.00.00.9838153269.746753
86800120019600.00.00.00.9842533291.915584
96800120029600.00.00.00.9846753310.896104
106800120039600.00.00.00.9850853327.142857
116800120049600.00.00.00.9854733341.194805
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":28}],"source":["#Guardamos en archivo\n","var_control.to_excel('Caracteristicas_gene.xlsx', index=False)\n","var_control.head()"]},{"cell_type":"markdown","metadata":{"id":"PJWGF08d4PnK"},"source":["##NTL Variable dependiente\n","1. Importamos los valores de NTL y los organizamos"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":698},"executionInfo":{"elapsed":136678,"status":"ok","timestamp":1694891099178,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"ZyauoOcAdJ_2","outputId":"7d6a518e-9fba-4413-a063-9102d93f61e2"},"outputs":[{"output_type":"display_data","data":{"text/plain":[""],"text/html":["\n"," \n"," \n"," Upload widget is only available when the cell has been executed in the\n"," current browser session. Please rerun this cell to enable.\n"," \n"," "]},"metadata":{}},{"output_type":"stream","name":"stdout","text":["Saving 2000.xlsx to 2000.xlsx\n","Saving 2001.xlsx to 2001.xlsx\n","Saving 2002.xlsx to 2002.xlsx\n","Saving 2003.xlsx to 2003.xlsx\n","Saving 2004.xlsx to 2004.xlsx\n","Saving 2005.xlsx to 2005.xlsx\n","Saving 2006.xlsx to 2006.xlsx\n","Saving 2007.xlsx to 2007.xlsx\n","Saving 2008.xlsx to 2008.xlsx\n","Saving 2009.xlsx to 2009.xlsx\n","Saving 2010.xlsx to 2010.xlsx\n","Saving 2011.xlsx to 2011.xlsx\n","Saving 2012.xlsx to 2012.xlsx\n","Saving 2013.xlsx to 2013.xlsx\n","Saving 2014.xlsx to 2014.xlsx\n","Saving 2015.xlsx to 2015.xlsx\n","Saving 2016.xlsx to 2016.xlsx\n","Saving 2017.xlsx to 2017.xlsx\n","Saving 2018.xlsx to 2018.xlsx\n"]}],"source":["#Cargamos archivos\n","uploaded = files.upload()"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"2mwPp19AVOAF"},"outputs":[],"source":["#Almacenamos los diferentes archivos en sus respectivos dataframes\n","import pandas as pd\n","ntl_2000 = pd.read_excel('2000.xlsx', dtype=str)\n","ntl_2001 = pd.read_excel('2001.xlsx', dtype=str)\n","ntl_2002 = pd.read_excel('2002.xlsx', dtype=str)\n","ntl_2003 = pd.read_excel('2003.xlsx', dtype=str)\n","ntl_2004 = pd.read_excel('2004.xlsx', dtype=str)\n","ntl_2005 = pd.read_excel('2005.xlsx', dtype=str)\n","ntl_2006 = pd.read_excel('2006.xlsx', dtype=str)\n","ntl_2007 = pd.read_excel('2007.xlsx', dtype=str)\n","ntl_2008 = pd.read_excel('2008.xlsx', dtype=str)\n","ntl_2009 = pd.read_excel('2009.xlsx', dtype=str)\n","ntl_2010 = pd.read_excel('2010.xlsx', dtype=str)\n","ntl_2011 = pd.read_excel('2011.xlsx', dtype=str)\n","ntl_2012 = pd.read_excel('2012.xlsx', dtype=str)\n","ntl_2013 = pd.read_excel('2013.xlsx', dtype=str)\n","ntl_2014 = pd.read_excel('2014.xlsx', dtype=str)\n","ntl_2015 = pd.read_excel('2015.xlsx', dtype=str)\n","ntl_2016 = pd.read_excel('2016.xlsx', dtype=str)\n","ntl_2017 = pd.read_excel('2017.xlsx', dtype=str)\n","ntl_2018 = pd.read_excel('2018.xlsx', dtype=str)"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":290},"executionInfo":{"elapsed":440,"status":"ok","timestamp":1694891154938,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"N8NwghUagGae","outputId":"3cd18430-e8b1-4d46-dd19-7a57d8577ace"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" DPTO_CCDGO MPIO_CCDGO MPIO_CNMBR MPIO_CDPMP VERSION \\\n","0 18 001 FLORENCIA 18001 2018 \n","1 18 029 ALBANIA 18029 2018 \n","2 18 094 BELÉN DE LOS ANDAQUÍES 18094 2018 \n","3 18 247 EL DONCELLO 18247 2018 \n","4 18 256 EL PAUJÍL 18256 2018 \n","\n"," AREA LATITUD LONGITUD STCTNENCUE STP3_1_SI ... \\\n","0 2547637532.26 1.74913870382 -75.5582390643 71877 32 ... \n","1 414122070.187 1.22786549019 -75.8823265076 2825 24 ... \n","2 1191618571.63 1.50092300306 -75.8756454243 4243 54 ... \n","3 1106076151.1 1.79138590347 -75.1939441549 8809 0 ... \n","4 1234734144.92 1.61774635616 -75.2340433342 5795 0 ... \n","\n"," _mean _median _stdev _min _max _range _minority \\\n","0 1.229453203622945 0 4.844133945598424 0 52 52 18 \n","1 0 0 0 0 0 0 0 \n","2 0.1462536023054755 0 0.854430105978759 0 7 7 7 \n","3 0.3872966692486445 0 1.59694510487695 0 12 12 11 \n","4 0.1759002770083103 0 1.037830590002834 0 9 9 9 \n","\n"," _majority _variety _variance \n","0 0 40 23.46563368289896 \n","1 0 1 0 \n","2 0 4 0.7300508060028733 \n","3 0 8 2.550233667990454 \n","4 0 6 1.077092333545631 \n","\n","[5 rows x 102 columns]"],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
DPTO_CCDGOMPIO_CCDGOMPIO_CNMBRMPIO_CDPMPVERSIONAREALATITUDLONGITUDSTCTNENCUESTP3_1_SI..._mean_median_stdev_min_max_range_minority_majority_variety_variance
018001FLORENCIA1800120182547637532.261.74913870382-75.55823906437187732...1.22945320362294504.844133945598424052521804023.46563368289896
118029ALBANIA180292018414122070.1871.22786549019-75.8823265076282524...0000000010
218094BELÉN DE LOS ANDAQUÍES1809420181191618571.631.50092300306-75.8756454243424354...0.146253602305475500.8544301059787590777040.7300508060028733
318247EL DONCELLO1824720181106076151.11.79138590347-75.193944154988090...0.387296669248644501.596945104876950121211082.550233667990454
418256EL PAUJÍL1825620181234734144.921.61774635616-75.234043334257950...0.175900277008310301.0378305900028340999061.077092333545631
\n","

5 rows × 102 columns

\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":31}],"source":["#Muestra para ver la organización del archivo\n","ntl_2000.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"elapsed":224,"status":"ok","timestamp":1694891157586,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"J6qnr92903Uo","outputId":"66078d60-a96b-4089-826a-03fd625ca60c"},"outputs":[{"output_type":"stream","name":"stdout","text":[" codigo MPIO_CCDGO MPIO_CNMBR MPIO_CDPMP VERSION \\\n","0 18 001 FLORENCIA 18001 2018 \n","1 18 029 ALBANIA 18029 2018 \n","2 18 094 BELÉN DE LOS ANDAQUÍES 18094 2018 \n","3 18 247 EL DONCELLO 18247 2018 \n","4 18 256 EL PAUJÍL 18256 2018 \n","... ... ... ... ... ... \n","1117 25 290 FUSAGASUGÁ 25290 2018 \n","1118 25 662 SAN JUAN DE RIOSECO 25662 2018 \n","1119 73 349 HONDA 73349 2018 \n","1120 05 631 SABANETA 05631 2018 \n","1121 05 380 LA ESTRELLA 05380 2018 \n","\n"," AREA LATITUD LONGITUD STCTNENCUE STP3_1_SI ... \\\n","0 2547637532.26 1.74913870382 -75.5582390643 71877 32 ... \n","1 414122070.187 1.22786549019 -75.8823265076 2825 24 ... \n","2 1191618571.63 1.50092300306 -75.8756454243 4243 54 ... \n","3 1106076151.1 1.79138590347 -75.1939441549 8809 0 ... \n","4 1234734144.92 1.61774635616 -75.2340433342 5795 0 ... \n","... ... ... ... ... ... ... \n","1117 193952876.743 4.3235340284 -74.3885911387 78073 0 ... \n","1118 314087273.957 4.84109818196 -74.6584930113 5654 0 ... \n","1119 304886912.25 5.17941656125 -74.7830309377 11333 0 ... \n","1120 15835319.0425 6.13830518341 -75.6088059929 37843 0 ... \n","1121 36631794.4918 6.13759588272 -75.6489549906 27038 0 ... \n","\n"," _mean _median _stdev _min _max _range \\\n","0 1.229453203622945 0 4.844133945598424 0 52 52 \n","1 0 0 0 0 0 0 \n","2 0.1462536023054755 0 0.854430105978759 0 7 7 \n","3 0.3872966692486445 0 1.59694510487695 0 12 12 \n","4 0.1759002770083103 0 1.037830590002834 0 9 9 \n","... ... ... ... ... ... ... \n","1117 19.77533039647577 16 13.4458518979231 0 54 54 \n","1118 1.681318681318681 0 2.489004924428001 0 8 8 \n","1119 5.425352112676056 0 8.756932357431824 0 42 42 \n","1120 41.4 43.5 14.13294024610591 16 59 43 \n","1121 42.65853658536585 47 12.25861520070295 16 61 45 \n","\n"," _minority _majority _variety _variance \n","0 18 0 40 23.46563368289896 \n","1 0 0 1 0 \n","2 7 0 4 0.7300508060028733 \n","3 11 0 8 2.550233667990454 \n","4 9 0 6 1.077092333545631 \n","... ... ... ... ... \n","1117 31 5 44 180.7909332608822 \n","1118 8 0 5 6.195145513826837 \n","1119 20 0 29 76.68386431263647 \n","1120 22 16 18 199.7400000000001 \n","1121 16 49 27 150.2736466389054 \n","\n","[1122 rows x 102 columns]\n"]}],"source":["# Cambiar el nombre de la columna\n","ntl_2000 = ntl_2000.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2001 = ntl_2001.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2002 = ntl_2002.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2003 = ntl_2003.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2004 = ntl_2004.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2005 = ntl_2005.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2006 = ntl_2006.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2007 = ntl_2007.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2008 = ntl_2008.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2009 = ntl_2009.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2010 = ntl_2010.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2011 = ntl_2011.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2012 = ntl_2012.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2013 = ntl_2013.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2014 = ntl_2014.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2015 = ntl_2015.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2016 = ntl_2016.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2017 = ntl_2017.rename(columns={'DPTO_CCDGO': 'codigo'})\n","ntl_2018 = ntl_2018.rename(columns={'DPTO_CCDGO': 'codigo'})\n","# Imprimir el DataFrame con el nombre de columna actualizado\n","print(ntl_2000)"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"E02LNCsz2T-D"},"outputs":[],"source":["#Conservo los códigos que necesito\n","ntl_2000 = ntl_2000[ntl_2000['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2001 = ntl_2001[ntl_2001['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2002 = ntl_2002[ntl_2002['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2003 = ntl_2003[ntl_2003['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2004 = ntl_2004[ntl_2004['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2005 = ntl_2005[ntl_2005['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2006 = ntl_2006[ntl_2006['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2007 = ntl_2007[ntl_2007['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2008 = ntl_2008[ntl_2008['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2009 = ntl_2009[ntl_2009['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2010 = ntl_2010[ntl_2010['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2011 = ntl_2011[ntl_2011['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2012 = ntl_2012[ntl_2012['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2013 = ntl_2013[ntl_2013['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2014 = ntl_2014[ntl_2014['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2015 = ntl_2015[ntl_2015['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2016 = ntl_2016[ntl_2016['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2017 = ntl_2017[ntl_2017['codigo'].astype(str).str.startswith(('68'))]\n","ntl_2018 = ntl_2018[ntl_2018['codigo'].astype(str).str.startswith(('68'))]"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"elapsed":410,"status":"ok","timestamp":1694891170896,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"ke91uVbR2unl","outputId":"a2afe52c-17f4-4a3c-bda7-09bec122efee"},"outputs":[{"output_type":"stream","name":"stdout","text":["975 68\n","976 68\n","977 68\n","978 68\n","979 68\n"," ..\n","1057 68\n","1058 68\n","1059 68\n","1060 68\n","1061 68\n","Name: codigo, Length: 87, dtype: object\n"]}],"source":["print(ntl_2000['codigo'])"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"e0hJFI8X6jRu"},"outputs":[],"source":["#Eliminamos las columnas que no necesito\n","columnas= ['codigo', 'MPIO_CCDGO', '_mean']\n","ntl_2000 = ntl_2000[ntl_2000.columns[ntl_2000.columns.isin(columnas)]]\n","ntl_2001 = ntl_2001[ntl_2001.columns[ntl_2001.columns.isin(columnas)]]\n","ntl_2002 = ntl_2002[ntl_2002.columns[ntl_2002.columns.isin(columnas)]]\n","ntl_2003 = ntl_2003[ntl_2003.columns[ntl_2003.columns.isin(columnas)]]\n","ntl_2004 = ntl_2004[ntl_2004.columns[ntl_2004.columns.isin(columnas)]]\n","ntl_2005 = ntl_2005[ntl_2005.columns[ntl_2005.columns.isin(columnas)]]\n","ntl_2006 = ntl_2006[ntl_2006.columns[ntl_2006.columns.isin(columnas)]]\n","ntl_2007 = ntl_2007[ntl_2007.columns[ntl_2007.columns.isin(columnas)]]\n","ntl_2008 = ntl_2008[ntl_2008.columns[ntl_2008.columns.isin(columnas)]]\n","ntl_2009 = ntl_2009[ntl_2009.columns[ntl_2009.columns.isin(columnas)]]\n","ntl_2010 = ntl_2010[ntl_2010.columns[ntl_2010.columns.isin(columnas)]]\n","ntl_2011 = ntl_2011[ntl_2011.columns[ntl_2011.columns.isin(columnas)]]\n","ntl_2012 = ntl_2012[ntl_2012.columns[ntl_2012.columns.isin(columnas)]]\n","ntl_2013 = ntl_2013[ntl_2013.columns[ntl_2013.columns.isin(columnas)]]\n","ntl_2014 = ntl_2014[ntl_2014.columns[ntl_2014.columns.isin(columnas)]]\n","ntl_2015 = ntl_2015[ntl_2015.columns[ntl_2015.columns.isin(columnas)]]\n","ntl_2016 = ntl_2016[ntl_2016.columns[ntl_2016.columns.isin(columnas)]]\n","ntl_2017 = ntl_2017[ntl_2017.columns[ntl_2017.columns.isin(columnas)]]\n","ntl_2018 = ntl_2018[ntl_2018.columns[ntl_2018.columns.isin(columnas)]]"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":320,"status":"ok","timestamp":1694891175201,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"JcW2BPy1_fw5","outputId":"c37fc046-be9d-45d6-c867-e6c0fe7cd193"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codigo MPIO_CCDGO _mean\n","975 68 013 0.4505494505494506\n","976 68 020 0.4489795918367347\n","977 68 051 2.034653465346535\n","978 68 077 13.17857142857143\n","979 68 079 4.883435582822086"],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codigoMPIO_CCDGO_mean
975680130.4505494505494506
976680200.4489795918367347
977680512.034653465346535
9786807713.17857142857143
979680794.883435582822086
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":36}],"source":["ntl_2000.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"a0BT1tEhA-4p"},"outputs":[],"source":["#Cambiamos tipos de datos de columnas\n","ntl_2000 = ntl_2000.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2001 = ntl_2001.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2002 = ntl_2002.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2003 = ntl_2003.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2004 = ntl_2004.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2005 = ntl_2005.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2006 = ntl_2006.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2007 = ntl_2007.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2008 = ntl_2008.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2009 = ntl_2009.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2010 = ntl_2010.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2011 = ntl_2011.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2012 = ntl_2012.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2013 = ntl_2013.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2014 = ntl_2014.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2015 = ntl_2015.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2016 = ntl_2016.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2017 = ntl_2017.astype({'codigo': str, 'MPIO_CCDGO': str})\n","ntl_2018 = ntl_2018.astype({'codigo': str, 'MPIO_CCDGO': str})"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"BypObyb1FBp5"},"outputs":[],"source":["#Concatenamos columnas para obtener códigos de municipios\n","ntl_2000['codmpio'] = ntl_2000['codigo'] + '' + ntl_2000['MPIO_CCDGO']\n","ntl_2001['codmpio'] = ntl_2001['codigo'] + '' + ntl_2001['MPIO_CCDGO']\n","ntl_2002['codmpio'] = ntl_2002['codigo'] + '' + ntl_2002['MPIO_CCDGO']\n","ntl_2003['codmpio'] = ntl_2003['codigo'] + '' + ntl_2003['MPIO_CCDGO']\n","ntl_2004['codmpio'] = ntl_2004['codigo'] + '' + ntl_2004['MPIO_CCDGO']\n","ntl_2005['codmpio'] = ntl_2005['codigo'] + '' + ntl_2005['MPIO_CCDGO']\n","ntl_2006['codmpio'] = ntl_2006['codigo'] + '' + ntl_2006['MPIO_CCDGO']\n","ntl_2007['codmpio'] = ntl_2007['codigo'] + '' + ntl_2007['MPIO_CCDGO']\n","ntl_2008['codmpio'] = ntl_2008['codigo'] + '' + ntl_2008['MPIO_CCDGO']\n","ntl_2009['codmpio'] = ntl_2009['codigo'] + '' + ntl_2009['MPIO_CCDGO']\n","ntl_2010['codmpio'] = ntl_2010['codigo'] + '' + ntl_2010['MPIO_CCDGO']\n","ntl_2011['codmpio'] = ntl_2011['codigo'] + '' + ntl_2011['MPIO_CCDGO']\n","ntl_2012['codmpio'] = ntl_2012['codigo'] + '' + ntl_2012['MPIO_CCDGO']\n","ntl_2013['codmpio'] = ntl_2013['codigo'] + '' + ntl_2013['MPIO_CCDGO']\n","ntl_2014['codmpio'] = ntl_2014['codigo'] + '' + ntl_2014['MPIO_CCDGO']\n","ntl_2015['codmpio'] = ntl_2015['codigo'] + '' + ntl_2015['MPIO_CCDGO']\n","ntl_2016['codmpio'] = ntl_2016['codigo'] + '' + ntl_2016['MPIO_CCDGO']\n","ntl_2017['codmpio'] = ntl_2017['codigo'] + '' + ntl_2017['MPIO_CCDGO']\n","ntl_2018['codmpio'] = ntl_2018['codigo'] + '' + ntl_2018['MPIO_CCDGO']"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":8,"status":"ok","timestamp":1694891181838,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"gIYVPboUFeNa","outputId":"ea0d8f39-2390-45da-ee03-d7265de7b4db"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codigo MPIO_CCDGO _mean codmpio\n","975 68 013 0.3846153846153846 68013\n","976 68 020 0.1428571428571428 68020\n","977 68 051 2.227722772277228 68051\n","978 68 077 12.55357142857143 68077\n","979 68 079 6.263803680981595 68079"],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codigoMPIO_CCDGO_meancodmpio
975680130.384615384615384668013
976680200.142857142857142868020
977680512.22772277227722868051
9786807712.5535714285714368077
979680796.26380368098159568079
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":39}],"source":["ntl_2004.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"81AXBjrdFyNL"},"outputs":[],"source":["#Eliminamos columnas que no necesitaremos\n","columnas_eliminar = ['codigo', 'MPIO_CCDGO']\n","ntl_2000 = ntl_2000.drop(columns=columnas_eliminar)\n","ntl_2001 = ntl_2001.drop(columns=columnas_eliminar)\n","ntl_2002 = ntl_2002.drop(columns=columnas_eliminar)\n","ntl_2003 = ntl_2003.drop(columns=columnas_eliminar)\n","ntl_2004 = ntl_2004.drop(columns=columnas_eliminar)\n","ntl_2005 = ntl_2005.drop(columns=columnas_eliminar)\n","ntl_2006 = ntl_2006.drop(columns=columnas_eliminar)\n","ntl_2007 = ntl_2007.drop(columns=columnas_eliminar)\n","ntl_2008 = ntl_2008.drop(columns=columnas_eliminar)\n","ntl_2009 = ntl_2009.drop(columns=columnas_eliminar)\n","ntl_2010 = ntl_2010.drop(columns=columnas_eliminar)\n","ntl_2011 = ntl_2011.drop(columns=columnas_eliminar)\n","ntl_2012 = ntl_2012.drop(columns=columnas_eliminar)\n","ntl_2013 = ntl_2013.drop(columns=columnas_eliminar)\n","ntl_2014 = ntl_2014.drop(columns=columnas_eliminar)\n","ntl_2015 = ntl_2015.drop(columns=columnas_eliminar)\n","ntl_2016 = ntl_2016.drop(columns=columnas_eliminar)\n","ntl_2017 = ntl_2017.drop(columns=columnas_eliminar)\n","ntl_2018 = ntl_2018.drop(columns=columnas_eliminar)"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":308,"status":"ok","timestamp":1694891186466,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"iVw9FFYoJTNx","outputId":"3471bdd9-e6fe-49a6-a590-2c6aa4595371"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" _mean codmpio\n","975 0.2527472527472527 68013\n","976 0 68020\n","977 0.3514851485148515 68051\n","978 13.39285714285714 68077\n","979 3.736196319018405 68079"],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
_meancodmpio
9750.252747252747252768013
976068020
9770.351485148514851568051
97813.3928571428571468077
9793.73619631901840568079
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":41}],"source":["ntl_2003.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"Z-EpCFofJeMw"},"outputs":[],"source":["#Cambiamos orden de columnas\n","nuevo_orden = ['codmpio', '_mean']\n","ntl_2000 = ntl_2000.reindex(columns=nuevo_orden)\n","ntl_2001 = ntl_2001.reindex(columns=nuevo_orden)\n","ntl_2002 = ntl_2002.reindex(columns=nuevo_orden)\n","ntl_2003 = ntl_2003.reindex(columns=nuevo_orden)\n","ntl_2004 = ntl_2004.reindex(columns=nuevo_orden)\n","ntl_2005 = ntl_2005.reindex(columns=nuevo_orden)\n","ntl_2006 = ntl_2006.reindex(columns=nuevo_orden)\n","ntl_2007 = ntl_2007.reindex(columns=nuevo_orden)\n","ntl_2008 = ntl_2008.reindex(columns=nuevo_orden)\n","ntl_2009 = ntl_2009.reindex(columns=nuevo_orden)\n","ntl_2010 = ntl_2010.reindex(columns=nuevo_orden)\n","ntl_2011 = ntl_2011.reindex(columns=nuevo_orden)\n","ntl_2012 = ntl_2012.reindex(columns=nuevo_orden)\n","ntl_2013 = ntl_2013.reindex(columns=nuevo_orden)\n","ntl_2014 = ntl_2014.reindex(columns=nuevo_orden)\n","ntl_2015 = ntl_2015.reindex(columns=nuevo_orden)\n","ntl_2016 = ntl_2016.reindex(columns=nuevo_orden)\n","ntl_2017 = ntl_2017.reindex(columns=nuevo_orden)\n","ntl_2018 = ntl_2018.reindex(columns=nuevo_orden)"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":317,"status":"ok","timestamp":1694891190148,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"miwGpuxJKHs3","outputId":"5a129c8d-2a68-44ff-85d3-10ed3c41937a"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio _mean\n","975 68013 0.5604395604395604\n","976 68020 0.413265306122449\n","977 68051 2.00990099009901\n","978 68077 13.25\n","979 68079 5.257668711656442"],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpio_mean
975680130.5604395604395604
976680200.413265306122449
977680512.00990099009901
9786807713.25
979680795.257668711656442
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":43}],"source":["ntl_2008.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"0_0jEH7MKMIn"},"outputs":[],"source":["#Añadimos columnas de años a cada dataframe\n","valor_repetido_8 = 2000\n","ntl_2000['ano'] = pd.Series(valor_repetido_8, index=ntl_2000.index)\n","valor_repetido_9 = 2001\n","ntl_2001['ano'] = pd.Series(valor_repetido_9, index=ntl_2001.index)\n","valor_repetido_10 = 2002\n","ntl_2002['ano'] = pd.Series(valor_repetido_10, index=ntl_2002.index)\n","valor_repetido_11 = 2003\n","ntl_2003['ano'] = pd.Series(valor_repetido_11, index=ntl_2003.index)\n","valor_repetido_12 = 2004\n","ntl_2004['ano'] = pd.Series(valor_repetido_12, index=ntl_2004.index)\n","valor_repetido_13 = 2005\n","ntl_2005['ano'] = pd.Series(valor_repetido_13, index=ntl_2005.index)\n","valor_repetido_14 = 2006\n","ntl_2006['ano'] = pd.Series(valor_repetido_14, index=ntl_2006.index)\n","valor_repetido_15 = 2007\n","ntl_2007['ano'] = pd.Series(valor_repetido_15, index=ntl_2007.index)\n","valor_repetido_16 = 2008\n","ntl_2008['ano'] = pd.Series(valor_repetido_16, index=ntl_2008.index)\n","valor_repetido_17 = 2009\n","ntl_2009['ano'] = pd.Series(valor_repetido_17, index=ntl_2009.index)\n","valor_repetido_18 = 2010\n","ntl_2010['ano'] = pd.Series(valor_repetido_18, index=ntl_2010.index)\n","valor_repetido_19 = 2011\n","ntl_2011['ano'] = pd.Series(valor_repetido_19, index=ntl_2011.index)\n","valor_repetido_20 = 2012\n","ntl_2012['ano'] = pd.Series(valor_repetido_20, index=ntl_2012.index)\n","valor_repetido_21 = 2013\n","ntl_2013['ano'] = pd.Series(valor_repetido_21, index=ntl_2013.index)\n","valor_repetido_22 = 2014\n","ntl_2014['ano'] = pd.Series(valor_repetido_22, index=ntl_2014.index)\n","valor_repetido_23 = 2015\n","ntl_2015['ano'] = pd.Series(valor_repetido_23, index=ntl_2015.index)\n","valor_repetido_24 = 2016\n","ntl_2016['ano'] = pd.Series(valor_repetido_24, index=ntl_2016.index)\n","valor_repetido_25 = 2017\n","ntl_2017['ano'] = pd.Series(valor_repetido_25, index=ntl_2017.index)\n","valor_repetido_26 = 2018\n","ntl_2018['ano'] = pd.Series(valor_repetido_26, index=ntl_2018.index)"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":269},"executionInfo":{"elapsed":274,"status":"ok","timestamp":1694891194525,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"KmsM9XuvLKpW","outputId":"6782a4b8-6807-4b40-ed22-d19419ae15fc"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio _mean ano\n","975 68013 4.582417582417582 2018\n","976 68020 4.556122448979592 2018\n","977 68051 6.54950495049505 2018\n","978 68077 17.35714285714286 2018\n","979 68079 7.153374233128834 2018\n","980 68081 17.04300385109114 2018\n","981 68092 4.312871287128713 2018"],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpio_meanano
975680134.5824175824175822018
976680204.5561224489795922018
977680516.549504950495052018
9786807717.357142857142862018
979680797.1533742331288342018
9806808117.043003851091142018
981680924.3128712871287132018
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":45}],"source":["ntl_2018.head(7)"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"TTYJSqGOoocL","colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"status":"ok","timestamp":1694891196440,"user_tz":300,"elapsed":255,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"}},"outputId":"0242e76f-578b-4857-e7e6-b9ff77f84f7c"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio _mean ano\n","0 68013 0.450549 2000\n","1 68211 0.844720 2000\n","2 68575 0.908683 2000\n","3 68573 0.268156 2000\n","4 68572 2.880952 2000"],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpio_meanano
0680130.4505492000
1682110.8447202000
2685750.9086832000
3685730.2681562000
4685722.8809522000
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":46}],"source":["#Unimos todos los dataframes de ntl\n","ntl_ =[ ntl_2000, ntl_2001, ntl_2002, ntl_2003, ntl_2004, ntl_2005, ntl_2006, ntl_2007,\n"," ntl_2008, ntl_2009, ntl_2010, ntl_2011, ntl_2012, ntl_2013, ntl_2014 ,ntl_2015, ntl_2016, ntl_2017, ntl_2018]\n","\n","ntl = pd.concat(ntl_, axis=0, join='inner')\n","\n","ntl['codmpio'] = ntl['codmpio'].astype(int)\n","ntl['_mean'] = ntl['_mean'].astype(float)\n","ntl['ano'] = ntl['ano'].astype(int)\n","ntl = ntl.sort_values(by='ano', ascending=True)\n","ntl.to_excel('ntl.xlsx', index=False)\n","ntl= ntl.reset_index(drop=True)\n","ntl.head()\n"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":343,"status":"ok","timestamp":1694891199193,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"jojMwFQnrEXZ","outputId":"67e43063-c900-45c7-b909-588d532e1263"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio _mean ano\n","0 68013 0.450549 2000\n","1 68211 0.844720 2000\n","2 68575 0.908683 2000\n","3 68573 0.268156 2000\n","4 68572 2.880952 2000"],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpio_meanano
0680130.4505492000
1682110.8447202000
2685750.9086832000
3685730.2681562000
4685722.8809522000
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":47}],"source":["ntl.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"elapsed":520,"status":"ok","timestamp":1694891201616,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"PdRnT1QyrVN4","outputId":"4c648fcf-bdc5-4a5d-9e02-1f88a098dfb0"},"outputs":[{"output_type":"stream","name":"stdout","text":["Número de filas: 1653\n"]}],"source":["num_filas = ntl.shape[0]\n","print(\"Número de filas:\", num_filas)"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":357,"status":"ok","timestamp":1694891203524,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"8ZDiocT9sl2g","outputId":"5f4cf4af-cc4d-4ebc-8d6e-9c641d59d20a"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio _mean ano\n","0 68013 0.450549 2000\n","1 68377 0.405685 2000\n","2 68051 2.034653 2000\n","3 68077 13.178571 2000\n","4 68079 4.883436 2000"],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpio_meanano
0680130.4505492000
1683770.4056852000
2680512.0346532000
36807713.1785712000
4680794.8834362000
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":49}],"source":["#Convertimos filas de mean y año en número\n","ntl['codmpio'] = ntl['codmpio'].astype(int)\n","ntl['_mean'] = ntl['_mean'].astype(float)\n","ntl['ano'] = ntl['ano'].astype(int)\n","ntl = ntl.sort_values(by='ano', ascending=True)\n","ntl.to_excel('ntl.xlsx', index=False)\n","ntl= ntl.reset_index(drop=True)\n","ntl.head()"]},{"cell_type":"markdown","metadata":{"id":"2Z3fypTqvxob"},"source":["##Union de todas las bases de datos"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"ntkpLoNwzp-2"},"outputs":[],"source":["#Convertimos columnas a los mismos tipos de datos\n","data = pd.read_excel('data_2.xlsx')\n","var_control = pd.read_excel('Caracteristicas_gene.xlsx')\n","ntl = pd.read_excel('ntl.xlsx')\n","data['codmpio'] = data['codmpio'].astype(int)\n","data['ano'] = data['ano'].astype(int)\n","data['DF_desemp_fisc'] = data['DF_desemp_fisc'].astype(float)\n","data['autonomia_tributaria'] = data['autonomia_tributaria'].astype(float)\n","data['y_cap_regalias_real'] = data['y_cap_regalias_real'].astype(float)\n","data['y_cap_transf_real'] = data['y_cap_transf_real'].astype(float)\n","var_control['codmpio'] = var_control['codmpio'].astype(int)\n","var_control['ano'] = var_control['ano'].astype(int)\n","var_control['altura'] = var_control['altura'].astype(float)\n","var_control['discapital'] = var_control['discapital'].astype(float)\n","var_control['dismdo'] = var_control['dismdo'].astype(float)\n","var_control['distancia_mercado'] = var_control['distancia_mercado'].astype(float)\n","var_control['indurb'] = var_control['indurb'].astype(float)\n","var_control['densidad'] = var_control['densidad'].astype(float)\n","ntl['_mean'] = ntl['_mean'].astype(float)"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":1597,"status":"ok","timestamp":1694891211438,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"cmi2CdIFv00F","outputId":"02317747-bcec-4e61-b131-ad86571edb9c"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" Unnamed: 0 codmpio ano DF_desemp_fisc y_cap_regalias_real \\\n","0 0 68001 2000 57.25 27.725359 \n","1 1 68573 2000 NaN 0.000000 \n","2 2 68572 2000 64.23 0.000000 \n","3 3 68549 2000 56.08 0.000000 \n","4 4 68547 2000 50.31 0.000000 \n","\n"," y_cap_transf_real autonomia_tributaria altura discapital dismdo \\\n","0 67829.026166 0.779550 960 0.000000 0.000000 \n","1 0.000000 NaN 100 106.484978 106.484978 \n","2 6221.120598 0.283546 1625 157.643051 157.643051 \n","3 1986.581940 0.491451 1170 70.647644 70.647644 \n","4 8071.795396 0.843985 1009 27.782368 27.782368 \n","\n"," distancia_mercado indurb densidad \n","0 0.000000 0.983815 3269.746753 \n","1 36.569523 0.366513 7.551678 \n","2 31.261797 0.322928 57.498168 \n","3 8.720665 0.228318 77.296296 \n","4 27.782368 0.766046 213.814969 "],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
Unnamed: 0codmpioanoDF_desemp_fiscy_cap_regalias_realy_cap_transf_realautonomia_tributariaalturadiscapitaldismdodistancia_mercadoindurbdensidad
0068001200057.2527.72535967829.0261660.7795509600.0000000.0000000.0000000.9838153269.746753
11685732000NaN0.0000000.000000NaN100106.484978106.48497836.5695230.3665137.551678
2268572200064.230.0000006221.1205980.2835461625157.643051157.64305131.2617970.32292857.498168
3368549200056.080.0000001986.5819400.491451117070.64764470.6476448.7206650.22831877.296296
4468547200050.310.0000008071.7953960.843985100927.78236827.78236827.7823680.766046213.814969
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":51}],"source":["#Uniendo las bases de datos data y var_control\n","data=pd.read_excel('data_2.xlsx')\n","var_control=pd.read_excel('Caracteristicas_gene.xlsx')\n","ntl=pd.read_excel('ntl.xlsx')\n","datos = data.merge(var_control, on=['ano', 'codmpio'])\n","datos.to_excel('datos.xlsx', index=False)\n","datos.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":268,"status":"ok","timestamp":1694891213629,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"v1si5b0F5SD3","outputId":"3d70787d-9601-49d8-9fdf-900968ff288a"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" Unnamed: 0 codmpio ano DF_desemp_fisc y_cap_regalias_real \\\n","0 0 68001 2000 57.25 27.725359 \n","1 1 68573 2000 NaN 0.000000 \n","2 2 68572 2000 64.23 0.000000 \n","3 3 68549 2000 56.08 0.000000 \n","4 4 68547 2000 50.31 0.000000 \n","\n"," y_cap_transf_real autonomia_tributaria altura discapital dismdo \\\n","0 67829.026166 0.779550 960 0.000000 0.000000 \n","1 0.000000 NaN 100 106.484978 106.484978 \n","2 6221.120598 0.283546 1625 157.643051 157.643051 \n","3 1986.581940 0.491451 1170 70.647644 70.647644 \n","4 8071.795396 0.843985 1009 27.782368 27.782368 \n","\n"," distancia_mercado indurb densidad _mean \n","0 0.000000 0.983815 3269.746753 38.016484 \n","1 36.569523 0.366513 7.551678 0.268156 \n","2 31.261797 0.322928 57.498168 2.880952 \n","3 8.720665 0.228318 77.296296 6.348485 \n","4 27.782368 0.766046 213.814969 8.433798 "],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
Unnamed: 0codmpioanoDF_desemp_fiscy_cap_regalias_realy_cap_transf_realautonomia_tributariaalturadiscapitaldismdodistancia_mercadoindurbdensidad_mean
0068001200057.2527.72535967829.0261660.7795509600.0000000.0000000.0000000.9838153269.74675338.016484
11685732000NaN0.0000000.000000NaN100106.484978106.48497836.5695230.3665137.5516780.268156
2268572200064.230.0000006221.1205980.2835461625157.643051157.64305131.2617970.32292857.4981682.880952
3368549200056.080.0000001986.5819400.491451117070.64764470.6476448.7206650.22831877.2962966.348485
4468547200050.310.0000008071.7953960.843985100927.78236827.78236827.7823680.766046213.8149698.433798
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":52}],"source":["#Uniendo datos y ntl\n","base_datos = datos.merge(ntl, on=['ano', 'codmpio'])\n","base_datos.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":1204,"status":"ok","timestamp":1694891216666,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"EqBXsgBD56O1","outputId":"e7901321-9678-42f5-a3e7-d91d2d4e6645"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio ano _mean y_cap_regalias_real y_cap_transf_real \\\n","0 68001 2000 38.016484 27.725359 67829.026166 \n","1 68573 2000 0.268156 0.000000 0.000000 \n","2 68572 2000 2.880952 0.000000 6221.120598 \n","3 68549 2000 6.348485 0.000000 1986.581940 \n","4 68547 2000 8.433798 0.000000 8071.795396 \n","\n"," autonomia_tributaria DF_desemp_fisc altura discapital dismdo \\\n","0 0.779550 57.25 960 0.000000 0.000000 \n","1 NaN NaN 100 106.484978 106.484978 \n","2 0.283546 64.23 1625 157.643051 157.643051 \n","3 0.491451 56.08 1170 70.647644 70.647644 \n","4 0.843985 50.31 1009 27.782368 27.782368 \n","\n"," distancia_mercado indurb densidad \n","0 0.000000 0.983815 3269.746753 \n","1 36.569523 0.366513 7.551678 \n","2 31.261797 0.322928 57.498168 \n","3 8.720665 0.228318 77.296296 \n","4 27.782368 0.766046 213.814969 "],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpioano_meany_cap_regalias_realy_cap_transf_realautonomia_tributariaDF_desemp_fiscalturadiscapitaldismdodistancia_mercadoindurbdensidad
068001200038.01648427.72535967829.0261660.77955057.259600.0000000.0000000.0000000.9838153269.746753
16857320000.2681560.0000000.000000NaNNaN100106.484978106.48497836.5695230.3665137.551678
26857220002.8809520.0000006221.1205980.28354664.231625157.643051157.64305131.2617970.32292857.498168
36854920006.3484850.0000001986.5819400.49145156.08117070.64764470.6476448.7206650.22831877.296296
46854720008.4337980.0000008071.7953960.84398550.31100927.78236827.78236827.7823680.766046213.814969
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":53}],"source":["#Ordenamos columnas según la estructura de datos panel\n","orden_columnas = ['codmpio','ano','_mean','y_cap_regalias_real','y_cap_transf_real','autonomia_tributaria','DF_desemp_fisc','altura',\t'discapital',\t'dismdo',\t'distancia_mercado',\t'indurb',\t'densidad']\n","base_datos = base_datos.reindex(columns=orden_columnas)\n","base_datos.to_excel('base_datos.xlsx', index=False)\n","base_datos.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"executionInfo":{"elapsed":661,"status":"ok","timestamp":1694891219443,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"lZlQx7rXYx8K","outputId":"f8979cef-45f5-4b42-c7e0-89520abc20f4"},"outputs":[{"output_type":"execute_result","data":{"text/plain":[" codmpio ano _mean y_cap_regalias_real y_cap_transf_real \\\n","0 68001 2000 38.016484 27.725359 67829.026166 \n","413 68001 2004 35.758242 0.000000 220237.871370 \n","1192 68001 2013 41.631868 12.862644 301120.923841 \n","757 68001 2008 39.000000 22.001391 272524.524184 \n","1626 68001 2018 40.802198 3.327649 346592.259808 \n","\n"," autonomia_tributaria DF_desemp_fisc altura discapital dismdo \\\n","0 0.779550 57.250000 960 0.0 0.0 \n","413 0.811601 64.570000 960 0.0 0.0 \n","1192 0.950366 83.120000 960 0.0 0.0 \n","757 0.926304 75.410000 960 0.0 0.0 \n","1626 0.910983 79.544426 960 0.0 0.0 \n","\n"," distancia_mercado indurb densidad \n","0 0.0 0.983815 3269.746753 \n","413 0.0 0.985473 3341.194805 \n","1192 0.0 0.987476 3420.954545 \n","757 0.0 0.986570 3385.941558 \n","1626 0.0 0.982142 3773.571429 "],"text/html":["\n","
\n","
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
codmpioano_meany_cap_regalias_realy_cap_transf_realautonomia_tributariaDF_desemp_fiscalturadiscapitaldismdodistancia_mercadoindurbdensidad
068001200038.01648427.72535967829.0261660.77955057.2500009600.00.00.00.9838153269.746753
41368001200435.7582420.000000220237.8713700.81160164.5700009600.00.00.00.9854733341.194805
119268001201341.63186812.862644301120.9238410.95036683.1200009600.00.00.00.9874763420.954545
75768001200839.00000022.001391272524.5241840.92630475.4100009600.00.00.00.9865703385.941558
162668001201840.8021983.327649346592.2598080.91098379.5444269600.00.00.00.9821423773.571429
\n","
\n","
\n","\n","
\n"," \n","\n"," \n","\n"," \n","
\n","\n","\n","
\n"," \n","\n","\n","\n"," \n","
\n","
\n","
\n"]},"metadata":{},"execution_count":54}],"source":["#Ordenamos filas según la estructura de datos panel\n","columna_ordenar = 'codmpio'\n","base_datos = base_datos.sort_values(by=columna_ordenar, ascending=True)\n","base_datos.to_excel('base_datos.xlsx', index=False)\n","base_datos.head()"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":17},"executionInfo":{"elapsed":10,"status":"ok","timestamp":1694891221603,"user":{"displayName":"Nixon James Son","userId":"16406963522306073498"},"user_tz":300},"id":"q5_6_jEzpgew","outputId":"13d4d43a-57ed-4cce-b6a9-31d0708c5a9f"},"outputs":[{"output_type":"display_data","data":{"text/plain":[""],"application/javascript":["\n"," async function download(id, filename, size) {\n"," if (!google.colab.kernel.accessAllowed) {\n"," return;\n"," }\n"," const div = document.createElement('div');\n"," const label = document.createElement('label');\n"," label.textContent = `Downloading \"${filename}\": `;\n"," div.appendChild(label);\n"," const progress = document.createElement('progress');\n"," progress.max = size;\n"," div.appendChild(progress);\n"," document.body.appendChild(div);\n","\n"," const buffers = [];\n"," let downloaded = 0;\n","\n"," const channel = await google.colab.kernel.comms.open(id);\n"," // Send a message to notify the kernel that we're ready.\n"," channel.send({})\n","\n"," for await (const message of channel.messages) {\n"," // Send a message to notify the kernel that we're ready.\n"," channel.send({})\n"," if (message.buffers) {\n"," for (const buffer of message.buffers) {\n"," buffers.push(buffer);\n"," downloaded += buffer.byteLength;\n"," progress.value = downloaded;\n"," }\n"," }\n"," }\n"," const blob = new Blob(buffers, {type: 'application/binary'});\n"," const a = document.createElement('a');\n"," a.href = window.URL.createObjectURL(blob);\n"," a.download = filename;\n"," div.appendChild(a);\n"," a.click();\n"," div.remove();\n"," }\n"," "]},"metadata":{}},{"output_type":"display_data","data":{"text/plain":[""],"application/javascript":["download(\"download_ad71cb54-270a-4c94-a8a2-c56fc52bd1a8\", \"base_datos.xlsx\", 176687)"]},"metadata":{}}],"source":["#Guardamos nuestra base de datos\n","from google.colab import files\n","files.download('base_datos.xlsx')"]}],"metadata":{"colab":{"provenance":[{"file_id":"1oHdHC8P6RWToNviVsmywT9mR2DsRX58w","timestamp":1694453632354},{"file_id":"1LadG4UvprdEKmxa4Hhi3K-IsXLE8tJgV","timestamp":1694048378121},{"file_id":"19vKKLmcIsEtjjbBOihFPR67fHlkScZrt","timestamp":1693627854086},{"file_id":"1xsyPvE9cVaO6i6SC4YRoYPLaiSa_aENZ","timestamp":1693282215092}],"authorship_tag":"ABX9TyN/0uSgBO9p8dp5cP3kd+UF"},"kernelspec":{"display_name":"Python 3","name":"python3"},"language_info":{"name":"python"}},"nbformat":4,"nbformat_minor":0}