Skip to content

Instantly share code, notes, and snippets.

@jjsantos01

jjsantos01/compranet_redes_dataton_anticorrupcion2019.ipynb

Created January 20, 2020 16:18
Show Gist options
  • Save jjsantos01/6711e638d681406b24106d205dbe8f20 to your computer and use it in GitHub Desktop.
Save jjsantos01/6711e638d681406b24106d205dbe8f20 to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
compranet_redes_dataton_anticorrupcion2019.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Imports y globals "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Imports"
]
},
{
"cell_type": "code",
"execution_count": 278,
"metadata": {},
"outputs": [],
"source": [
"import pymongo\n",
"import pandas as pd\n",
"import json\n",
"import matplotlib.pyplot as plt\n",
"import networkx as nx\n",
"from NetworkUtils import draw_network"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## globals"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [],
"source": [
"# Iniciar la base de datos con mongod --dbpath=\"d:\\mongodb\\data\"\n",
"dir_datos = 'd:/datos/licitaciones_compranet'\n",
"myclient = pymongo.MongoClient(\"mongodb://localhost:27017/\")\n",
"mydb = myclient['dataton2019']"
]
},
{
"cell_type": "markdown",
"metadata": {
"heading_collapsed": true
},
"source": [
"## Funciones"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"hidden": true
},
"outputs": [],
"source": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Análisis de asociados"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Contacpoints\n",
"\n",
"### Creación de la base\n",
"\n",
"* Creamos una base de datos que tenga identificado a cada punto de contacto.\n",
"* Los puntos de contacto se obtienen a partir de los contratistas que tienen información o su dirección."
]
},
{
"cell_type": "code",
"execution_count": 59,
"metadata": {},
"outputs": [],
"source": [
"resultado = mydb.contrataciones.find({}, {'_id': 0, 'parties.contactPoint': 1, 'parties.roles': 1, 'parties.id': 1, 'parties.address': 1})"
]
},
{
"cell_type": "code",
"execution_count": 60,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>name</th>\n",
" <th>email</th>\n",
" <th>telephone</th>\n",
" <th>streetAddress</th>\n",
" <th>locality</th>\n",
" <th>region</th>\n",
" <th>postalCode</th>\n",
" <th>countryName</th>\n",
" <th>tenderer_id</th>\n",
" <th>faxNumber</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>IRAM LIEVANOS VELAZQUEZ</td>\n",
" <td>laroca.canino@gmail.com</td>\n",
" <td>52 722 093749</td>\n",
" <td>PASEO DE LA ASUNCION NO. 536</td>\n",
" <td>METEPEC</td>\n",
" <td>MX-MEX</td>\n",
" <td>52148</td>\n",
" <td>MÉXICO</td>\n",
" <td>E9C1C827AE1234CCF7AC4D9070BB597C</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>NaN</td>\n",
" <td>servillantas@prodigy.net.mx</td>\n",
" <td>10191684</td>\n",
" <td>JOSE MORAN 66</td>\n",
" <td>MIGUEL HIDALGO</td>\n",
" <td>MX-CMX</td>\n",
" <td>11850</td>\n",
" <td>MÉXICO</td>\n",
" <td>SCA031118BX7</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>NaN</td>\n",
" <td>sportingautoreparaciones@gmail.com</td>\n",
" <td>55 56397681 55 54894622</td>\n",
" <td>PLUTARCO ELIAS CALLES No. 660. COL. SAN FRANCI...</td>\n",
" <td>Iztacalco</td>\n",
" <td>MX-CMX</td>\n",
" <td>08230</td>\n",
" <td>MÉXICO</td>\n",
" <td>SAU0505307M9</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>EDGAR GUSTAVO TREJO KEMPER</td>\n",
" <td>edgarg_kemper@hotmail.com; balcazar-sol@hotmai...</td>\n",
" <td>5543419836</td>\n",
" <td>CALZADA VALLEJO NUMERO 1020</td>\n",
" <td>AZCAPOTZALCO</td>\n",
" <td>MX-CMX</td>\n",
" <td>02300</td>\n",
" <td>MÉXICO</td>\n",
" <td>SCK070618C21</td>\n",
" <td>52 55 55873415 ext 201, 202, 203</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>Daniel Ernesto De la Fuente Barra</td>\n",
" <td>daniel.delafuente@segurossura.com.mx</td>\n",
" <td>5519636830</td>\n",
" <td>BLVD ADOLFO LOPEZ MATEOS 2448</td>\n",
" <td>ALVARO OBREGON</td>\n",
" <td>MX-CMX</td>\n",
" <td>01060</td>\n",
" <td>MÉXICO</td>\n",
" <td>R&amp;S811221KR6</td>\n",
" <td>57237999 Ext. 7965</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" name \\\n",
"0 IRAM LIEVANOS VELAZQUEZ \n",
"1 NaN \n",
"2 NaN \n",
"3 EDGAR GUSTAVO TREJO KEMPER \n",
"4 Daniel Ernesto De la Fuente Barra \n",
"\n",
" email \\\n",
"0 laroca.canino@gmail.com \n",
"1 servillantas@prodigy.net.mx \n",
"2 sportingautoreparaciones@gmail.com \n",
"3 edgarg_kemper@hotmail.com; balcazar-sol@hotmai... \n",
"4 daniel.delafuente@segurossura.com.mx \n",
"\n",
" telephone \\\n",
"0 52 722 093749 \n",
"1 10191684 \n",
"2 55 56397681 55 54894622 \n",
"3 5543419836 \n",
"4 5519636830 \n",
"\n",
" streetAddress locality region \\\n",
"0 PASEO DE LA ASUNCION NO. 536 METEPEC MX-MEX \n",
"1 JOSE MORAN 66 MIGUEL HIDALGO MX-CMX \n",
"2 PLUTARCO ELIAS CALLES No. 660. COL. SAN FRANCI... Iztacalco MX-CMX \n",
"3 CALZADA VALLEJO NUMERO 1020 AZCAPOTZALCO MX-CMX \n",
"4 BLVD ADOLFO LOPEZ MATEOS 2448 ALVARO OBREGON MX-CMX \n",
"\n",
" postalCode countryName tenderer_id \\\n",
"0 52148 MÉXICO E9C1C827AE1234CCF7AC4D9070BB597C \n",
"1 11850 MÉXICO SCA031118BX7 \n",
"2 08230 MÉXICO SAU0505307M9 \n",
"3 02300 MÉXICO SCK070618C21 \n",
"4 01060 MÉXICO R&S811221KR6 \n",
"\n",
" faxNumber \n",
"0 NaN \n",
"1 NaN \n",
"2 NaN \n",
"3 52 55 55873415 ext 201, 202, 203 \n",
"4 57237999 Ext. 7965 "
]
},
"execution_count": 60,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_contactos = pd.DataFrame([{**p.get('contactPoint', ''), **p.get('address', ''), 'tenderer_id': p['id'] }\n",
" for x in resultado for p in x['parties'] if (p['roles'] in [['tenderer'], ['tenderer', 'supplier']]) &\n",
" ((bool(p.get('address', None))) | (bool(p.get('contactPoint', None))))])\n",
"df_contactos.to_pickle(f'{dir_datos}/tenderers_contacpoint.pkl')\n",
"df_contactos.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* De la base de datos de contrataciones seleccionamos los datos de contacto de los que han ganado licitaciones. Estos tiene datos como nombre de la persona de contacto, email, teléfono, número de fax, dirección y id del proveedor"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Uso de la base"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>name</th>\n",
" <th>email</th>\n",
" <th>telephone</th>\n",
" <th>streetAddress</th>\n",
" <th>locality</th>\n",
" <th>region</th>\n",
" <th>postalCode</th>\n",
" <th>countryName</th>\n",
" <th>tenderer_id</th>\n",
" <th>faxNumber</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>IRAM LIEVANOS VELAZQUEZ</td>\n",
" <td>laroca.canino@gmail.com</td>\n",
" <td>52 722 093749</td>\n",
" <td>PASEO DE LA ASUNCION NO. 536</td>\n",
" <td>METEPEC</td>\n",
" <td>MX-MEX</td>\n",
" <td>52148</td>\n",
" <td>MÉXICO</td>\n",
" <td>E9C1C827AE1234CCF7AC4D9070BB597C</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>NaN</td>\n",
" <td>servillantas@prodigy.net.mx</td>\n",
" <td>10191684</td>\n",
" <td>JOSE MORAN 66</td>\n",
" <td>MIGUEL HIDALGO</td>\n",
" <td>MX-CMX</td>\n",
" <td>11850</td>\n",
" <td>MÉXICO</td>\n",
" <td>SCA031118BX7</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>NaN</td>\n",
" <td>sportingautoreparaciones@gmail.com</td>\n",
" <td>55 56397681 55 54894622</td>\n",
" <td>PLUTARCO ELIAS CALLES No. 660. COL. SAN FRANCI...</td>\n",
" <td>Iztacalco</td>\n",
" <td>MX-CMX</td>\n",
" <td>08230</td>\n",
" <td>MÉXICO</td>\n",
" <td>SAU0505307M9</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>EDGAR GUSTAVO TREJO KEMPER</td>\n",
" <td>edgarg_kemper@hotmail.com; balcazar-sol@hotmai...</td>\n",
" <td>5543419836</td>\n",
" <td>CALZADA VALLEJO NUMERO 1020</td>\n",
" <td>AZCAPOTZALCO</td>\n",
" <td>MX-CMX</td>\n",
" <td>02300</td>\n",
" <td>MÉXICO</td>\n",
" <td>SCK070618C21</td>\n",
" <td>52 55 55873415 ext 201, 202, 203</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>Daniel Ernesto De la Fuente Barra</td>\n",
" <td>daniel.delafuente@segurossura.com.mx</td>\n",
" <td>5519636830</td>\n",
" <td>BLVD ADOLFO LOPEZ MATEOS 2448</td>\n",
" <td>ALVARO OBREGON</td>\n",
" <td>MX-CMX</td>\n",
" <td>01060</td>\n",
" <td>MÉXICO</td>\n",
" <td>R&amp;S811221KR6</td>\n",
" <td>57237999 Ext. 7965</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" name \\\n",
"0 IRAM LIEVANOS VELAZQUEZ \n",
"1 NaN \n",
"2 NaN \n",
"3 EDGAR GUSTAVO TREJO KEMPER \n",
"4 Daniel Ernesto De la Fuente Barra \n",
"\n",
" email \\\n",
"0 laroca.canino@gmail.com \n",
"1 servillantas@prodigy.net.mx \n",
"2 sportingautoreparaciones@gmail.com \n",
"3 edgarg_kemper@hotmail.com; balcazar-sol@hotmai... \n",
"4 daniel.delafuente@segurossura.com.mx \n",
"\n",
" telephone \\\n",
"0 52 722 093749 \n",
"1 10191684 \n",
"2 55 56397681 55 54894622 \n",
"3 5543419836 \n",
"4 5519636830 \n",
"\n",
" streetAddress locality region \\\n",
"0 PASEO DE LA ASUNCION NO. 536 METEPEC MX-MEX \n",
"1 JOSE MORAN 66 MIGUEL HIDALGO MX-CMX \n",
"2 PLUTARCO ELIAS CALLES No. 660. COL. SAN FRANCI... Iztacalco MX-CMX \n",
"3 CALZADA VALLEJO NUMERO 1020 AZCAPOTZALCO MX-CMX \n",
"4 BLVD ADOLFO LOPEZ MATEOS 2448 ALVARO OBREGON MX-CMX \n",
"\n",
" postalCode countryName tenderer_id \\\n",
"0 52148 MÉXICO E9C1C827AE1234CCF7AC4D9070BB597C \n",
"1 11850 MÉXICO SCA031118BX7 \n",
"2 08230 MÉXICO SAU0505307M9 \n",
"3 02300 MÉXICO SCK070618C21 \n",
"4 01060 MÉXICO R&S811221KR6 \n",
"\n",
" faxNumber \n",
"0 NaN \n",
"1 NaN \n",
"2 NaN \n",
"3 52 55 55873415 ext 201, 202, 203 \n",
"4 57237999 Ext. 7965 "
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_contactos = pd.read_pickle(f'{dir_datos}/tenderers_contacpoint.pkl')\n",
"df_contactos.head()"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"(563693, 10)"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_contactos.shape"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* De todos los contratos encontramos 563693 puntos de contacto. Muchos de estos se repiten porque un contratista que ganó varias veces aparecerá como un contacto por cada contrato ganado.\n",
"* Calculamos todas las combinaciones únicas de telefono y tenderer_id.\n",
"* Luego verificamos si existen casos en los que varios tenderer_id comparten el:\n",
" * teléfono: 700 casos en los que eso ocurre.\n",
" * Email: 839 casos.\n",
" * Nombre: 706 casos\n",
" * Número de fax: 135 casos\n",
" * Dirección de la calle: 172 casos\n",
"* Todos estos son signos de sospecha.\n",
"* En muchos casos hay cuentas de funcionarios públicos. Habría que verificar cuál es su papel.\n",
"* La pregunta relevante es ¿Hay casos en los que contratistas que tienen contactos en común hayan participado en un mismo proceso de licitación?"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[{'parties.id': 'SLE021203RG1'}, {'parties.id': 'LGA111101NI7'}]\n",
"[{'parties.id': 'AA2EEEF597460501F7B8A50B4DE1F671'}, {'parties.id': '3CAB041C0551441CB0A31EAC594B2339'}]\n",
"[{'parties.id': 'MAN0710026M3'}, {'parties.id': 'MDI130927EM8'}, {'parties.id': 'LEC1202152T9'}]\n",
"[{'parties.id': '63455D66A674A6129E2963DDAB012081'}, {'parties.id': 'B235993476D58CC3750F77080E9DCA38'}]\n",
"[{'parties.id': '08094FA2070B800BA4DE5F265BD6563A'}, {'parties.id': '69017485557D5C0673A244B20E2C6F9C'}]\n",
"[{'parties.id': 'SIM1410088E7'}, {'parties.id': 'TME140911D25'}]\n",
"[{'parties.id': 'CBS170804R21'}, {'parties.id': 'CSR170804TR8'}]\n",
"[{'parties.id': 'TPA161207Q15'}, {'parties.id': 'PET130614137'}]\n",
"[{'parties.id': 'ADFD7D675FA05AD4BB1968B0F01B7B6C'}, {'parties.id': '690A5D3623AC6FA09A321C4C46931EE7'}]\n",
"[{'parties.id': 'PCE980303GT5'}, {'parties.id': 'ISM050530DL6'}]\n",
"[{'parties.id': 'KME590502HY7'}, {'parties.id': 'MED730308NF0'}]\n",
"[{'parties.id': 'AMC070628518'}, {'parties.id': 'ISC080722633'}]\n",
"[{'parties.id': 'SER110912T71'}, {'parties.id': 'SPS990322966'}]\n",
"[{'parties.id': 'AKA080904US4'}, {'parties.id': 'OGP060214H55'}]\n",
"[{'parties.id': 'MMH0510185GA'}, {'parties.id': 'ART95042071A'}]\n",
"[{'parties.id': '573C67FD9D50BD5D691A6B0E8A3E3051'}, {'parties.id': 'CAC6A7AA4761ED8731C9F5713AA5C944'}]\n",
"[{'parties.id': 'CIB150311KQA'}, {'parties.id': 'DGA150311QAA'}]\n",
"[{'parties.id': 'CAM140807S66'}, {'parties.id': 'LIB150414J15'}]\n",
"[{'parties.id': 'CDA010714TG6'}, {'parties.id': 'PAD060221U93'}]\n",
"[{'parties.id': 'CLA150108Q84'}, {'parties.id': 'CAI081014UJ6'}]\n",
"[{'parties.id': 'ACO040325KC8'}, {'parties.id': 'CEP000607D96'}]\n",
"[{'parties.id': 'CSI000905MZ6'}, {'parties.id': 'LAL140909SV7'}]\n",
"[{'parties.id': 'PSL160218F1A'}, {'parties.id': 'GCO160218GNA'}]\n",
"[{'parties.id': 'PTH1206294A9'}, {'parties.id': 'GCC950328H41'}]\n",
"[{'parties.id': 'CCO890116QA0'}, {'parties.id': 'CQC080313CP3'}]\n",
"[{'parties.id': 'DDU160607247'}, {'parties.id': 'ABU160112P45'}]\n",
"[{'parties.id': 'RDO070228V11'}, {'parties.id': 'ATD061228L34'}]\n",
"[{'parties.id': 'CSA041119GI7'}, {'parties.id': 'IHE0707254T8'}]\n",
"[{'parties.id': '50CAC81A1D1F540CFC320C0C4BEA4DC4'}, {'parties.id': 'TTR031030166'}]\n",
"[{'parties.id': '407987BAB90C9970656030877FFB1F7B'}, {'parties.id': '6592520DD93AA2A1D6188FB5904A661E'}]\n",
"[{'parties.id': 'MLI040112JU5'}, {'parties.id': 'IMI110617F39'}]\n",
"[{'parties.id': '903B041B0370D681986EBC58D18B685E'}, {'parties.id': '72EBDC80DB5BBD3FB79CCD2CD0F21571'}]\n",
"[{'parties.id': '1A4AAEB1791FCE9EEC1C5AA76BD22FFB'}, {'parties.id': '21ABB4A5BD5D736B6855B1E0DF3971D3'}]\n",
"[{'parties.id': 'PCO761025DL4'}, {'parties.id': 'EMI970219QD7'}]\n",
"[{'parties.id': 'GFE061004F65'}, {'parties.id': 'FES840823HH0'}]\n",
"[{'parties.id': 'EMA001219M72'}, {'parties.id': 'NEX0710043N1'}]\n",
"[{'parties.id': 'CBC110516NCA'}, {'parties.id': 'CEV061023TE4'}]\n",
"[{'parties.id': '8D230F328A3FA852A971E92FA3EE0823'}, {'parties.id': 'CII880407HS9'}]\n",
"[{'parties.id': 'D8E3FE62BB417952B7AF70684F2A7E89'}, {'parties.id': '5B663AF0A4D27117858E1850C3B6C5A0'}]\n",
"[{'parties.id': 'TCO9410128G7'}, {'parties.id': 'CPE9306306M7'}]\n",
"[{'parties.id': 'DIN1611245XA'}, {'parties.id': 'TCO161124L98'}]\n",
"[{'parties.id': 'VPP131009IJ5'}, {'parties.id': 'MVC151216BZ0'}]\n",
"[{'parties.id': 'EVA1605184H5'}, {'parties.id': 'SPE110628DZ2'}]\n",
"[{'parties.id': 'MPI120223V76'}, {'parties.id': 'STI151008119'}]\n",
"[{'parties.id': 'SBA160412574'}, {'parties.id': 'FC11786AB6953640172A8F01C8E663B8'}]\n",
"[{'parties.id': 'CZU150602CX1'}, {'parties.id': 'HCG150126N25'}]\n",
"[{'parties.id': 'JME000530MB8'}, {'parties.id': 'ETE080829B85'}]\n",
"[{'parties.id': '6A21C3C1E1707E26662C9F8A00D63708'}, {'parties.id': '64E598A985DCE69FC2F25F6E95450DAC'}]\n",
"[{'parties.id': '8D8DB6545FDCD231D053E3E6D6A60B8E'}, {'parties.id': '79D3BCC04DBB4A85BEFE73D040C8E749'}]\n",
"[{'parties.id': 'DCP151006355'}, {'parties.id': 'DCD151006KD2'}]\n",
"[{'parties.id': 'SCO120220CTA'}, {'parties.id': '51540917A00D4A6B9A1263D4FBBA321D'}]\n",
"[{'parties.id': 'C033E9C43B939496C708EB0CFCCCAB05'}, {'parties.id': 'A86984D882E22202471A1946A0FFC04F'}]\n",
"[{'parties.id': 'CEF0604012L3'}, {'parties.id': 'IMC100821SX2'}]\n",
"[{'parties.id': 'CPA9301221NA'}, {'parties.id': 'MCO030213D7A'}]\n",
"[{'parties.id': 'CFE0909025R9'}, {'parties.id': 'SIP060929ED6'}]\n",
"[{'parties.id': '830C50E64AF00F2D02273ACF97D66FF7'}, {'parties.id': '5310A47E56CF52E5B4009EFD6A102DDF'}]\n",
"[{'parties.id': '69C536B9AAD0A829246C8126B1481B85'}, {'parties.id': 'D5F97052F9EE5FA10937C04F85679359'}]\n",
"[{'parties.id': 'CPS110524V21'}, {'parties.id': 'FGC160419PX7'}]\n",
"[{'parties.id': 'FA35E31A1D75A092DE67A0002A7337DC'}, {'parties.id': '4050D2C40946894B46F7142D310BD974'}]\n",
"[{'parties.id': 'CDA9601297G9'}, {'parties.id': '23DF515587ED8B3F4A8B1C9E4D725CAD'}]\n",
"[{'parties.id': 'DKI910218I89'}, {'parties.id': 'CEU980706GL7'}]\n",
"[{'parties.id': 'DIC000410A76'}, {'parties.id': 'LBQ960708PK7'}]\n",
"[{'parties.id': 'MPE110624HD4'}, {'parties.id': '01AD46CB2D9A1680A5C45060C53FDE13'}]\n",
"[{'parties.id': 'EGR170228E93'}, {'parties.id': 'SLS170228544'}]\n",
"[{'parties.id': 'MPT110802PX1'}, {'parties.id': 'GCV1308193Y5'}]\n",
"[{'parties.id': 'FSI350701GV1'}, {'parties.id': 'SIN011023UC8'}]\n",
"[{'parties.id': 'CGU150217QE6'}, {'parties.id': 'CPD150303S70'}]\n",
"[{'parties.id': '53576D1E21D33ABA26B9BD334A681098'}, {'parties.id': '334444F73B6D02881CC40555DC6C318B'}]\n",
"[{'parties.id': 'GMM150226R81'}, {'parties.id': 'GLI131014IQ1'}]\n",
"[{'parties.id': 'TAS1308081N5'}, {'parties.id': 'CTD060131CY1'}]\n",
"[{'parties.id': 'DPM000113TI6'}, {'parties.id': '5B23F1CF24293B5FA07961E7D7AEC495'}]\n",
"[{'parties.id': 'JME110610456'}, {'parties.id': 'REH150724JT0'}]\n",
"[{'parties.id': 'GPA790523GV6'}, {'parties.id': 'DIE800905HX3'}]\n",
"[{'parties.id': 'HIC891031SZA'}, {'parties.id': 'HIC9207288B2'}]\n",
"[{'parties.id': '08E1A7D83DB35B72AFE39E034090A711'}, {'parties.id': 'D4505247954159BE074D5E2EDE3C734A'}]\n",
"[{'parties.id': 'ODI040329BP2'}, {'parties.id': 'IVI111116TY4'}]\n",
"[{'parties.id': 'LAC050701B66'}, {'parties.id': 'LAC920509FC4'}, {'parties.id': 'SDC010209LT5'}]\n",
"[{'parties.id': 'CCM120526CI6'}, {'parties.id': 'GCN931005DF7'}]\n",
"[{'parties.id': 'SCE0004124H4'}, {'parties.id': 'GSG130129M65'}, {'parties.id': 'ROC1107042U9'}, {'parties.id': 'SLA111029PK5'}, {'parties.id': 'GOF721005286'}, {'parties.id': 'SVE911021GQ7'}]\n",
"[{'parties.id': 'PAD111206JL7'}, {'parties.id': 'JML121005S41'}]\n",
"[{'parties.id': '40B50CE323B8BC137CEC75BEAF7B6832'}, {'parties.id': 'FIS900629ED6'}]\n",
"[{'parties.id': 'ICM110208MR0'}, {'parties.id': 'VCS180215DEA'}]\n",
"[{'parties.id': 'FA20E275B45D66B6A2D46A8806C66EE9'}, {'parties.id': 'EOV080922E83'}]\n",
"[{'parties.id': 'MCO131021P49'}, {'parties.id': 'DCM0701128T7'}]\n",
"[{'parties.id': 'GCG1502265R2'}, {'parties.id': 'ACM1711172K1'}]\n",
"[{'parties.id': '5F39200E194457C7291D091D83761DF9'}, {'parties.id': 'MDI100512355'}]\n",
"[{'parties.id': 'FLE001113KC9'}, {'parties.id': 'DRS130603GS8'}]\n",
"[{'parties.id': 'CD7F471D87AD6FDBA0170CBF356D7A0B'}, {'parties.id': 'E03BC1356495BFAB1D764B37C5ED7078'}]\n",
"[{'parties.id': '1388867A371B5CCEFFE6974D3578CCC6'}, {'parties.id': 'B4D2D2C07AD2E51695427D72A0974900'}]\n",
"[{'parties.id': 'SVK960605DP4'}, {'parties.id': 'ESM981001CI3'}]\n",
"[{'parties.id': 'RCO1305066Y1'}, {'parties.id': 'MIM130220NV2'}]\n",
"[{'parties.id': 'PEX051012GW2'}, {'parties.id': 'GTN160419130'}, {'parties.id': 'IRC151203F8A'}]\n",
"[{'parties.id': 'GME030704546'}, {'parties.id': 'PIT110909J75'}]\n",
"[{'parties.id': 'GRA9406213L3'}, {'parties.id': 'TEL990629JR9'}]\n",
"[{'parties.id': 'CAP910111U90'}, {'parties.id': 'RMC021114BA5'}]\n",
"[{'parties.id': 'ZUN1610148K6'}, {'parties.id': 'RAO1703135P9'}]\n",
"[{'parties.id': 'GCE140531CN5'}, {'parties.id': 'IOC170303SC0'}]\n",
"[{'parties.id': 'CDA010714TG6'}, {'parties.id': 'PAD060221U93'}]\n",
"[{'parties.id': 'DPI9008079U4'}, {'parties.id': '639AA7750C920647DDDF4A93AF45703B'}]\n",
"[{'parties.id': 'MBA960229SJ9'}, {'parties.id': 'IMA8012089S2'}, {'parties.id': 'CIN060724T73'}]\n",
"[{'parties.id': 'GBA160714LCA'}, {'parties.id': 'GRE100519U47'}]\n",
"[{'parties.id': 'CEO1208228P3'}, {'parties.id': 'OAW10121314A'}]\n",
"[{'parties.id': '488D206B7723816F085AF689E99FD4B0'}, {'parties.id': 'MKS130504D10'}]\n",
"[{'parties.id': 'GID040318GX6'}, {'parties.id': 'CBS940607LS8'}, {'parties.id': 'ICP050425HP1'}]\n",
"[{'parties.id': 'CPE100324QA5'}, {'parties.id': 'EDO0605053B6'}]\n",
"[{'parties.id': 'CTH120705DI0'}, {'parties.id': 'PTH1206294A9'}]\n",
"[{'parties.id': '2E5579D1ADF46842ADD140C62B8F62E0'}, {'parties.id': 'HCC950503NY4'}]\n"
]
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"[{'parties.id': 'CSR170804TR8'}, {'parties.id': 'CBS170804R21'}, {'parties.id': 'VIN1201135Q9'}]\n",
"[{'parties.id': 'ETA1410106EA'}, {'parties.id': 'ULJ141110G48'}]\n",
"[{'parties.id': '50CAC81A1D1F540CFC320C0C4BEA4DC4'}, {'parties.id': 'TTR031030166'}]\n",
"[{'parties.id': 'TME840315KT6'}, {'parties.id': 'BD03FBE666C3DBA5C57BCDC8BF0AA451'}]\n",
"[{'parties.id': 'ECA031114DB1'}, {'parties.id': 'MPU821007EM3'}]\n",
"[{'parties.id': 'PRO130131J16'}, {'parties.id': 'SPW170512TT2'}]\n",
"[{'parties.id': 'CTE080825JC2'}, {'parties.id': 'GHE060921FZA'}]\n",
"[{'parties.id': 'CTR970918574'}, {'parties.id': 'GVA010601FT9'}]\n",
"[{'parties.id': 'ECG090226QH3'}, {'parties.id': 'VCS030919PI9'}]\n",
"[{'parties.id': 'OPF150415AJ3'}, {'parties.id': 'COM140718L49'}]\n",
"[{'parties.id': 'MID050615IC2'}, {'parties.id': 'GCS160901RV6'}, {'parties.id': 'MIE1002042C5'}]\n",
"[{'parties.id': 'SSC040730SG9'}, {'parties.id': 'PGA930201M16'}, {'parties.id': 'WHA041213JC7'}, {'parties.id': 'CTA840227TR0'}, {'parties.id': 'OIN850708PF4'}, {'parties.id': 'TDG890317MT9'}, {'parties.id': 'AIC020201EW8'}]\n",
"[{'parties.id': 'DCE0908051U8'}, {'parties.id': 'TRI110217839'}]\n",
"[{'parties.id': 'GGG901128441'}, {'parties.id': 'IFM880216AF1'}]\n",
"[{'parties.id': 'GMC131015V97'}, {'parties.id': 'GMC090921JI8'}]\n",
"[{'parties.id': 'QCO020709KY8'}, {'parties.id': 'CER010914M7A'}, {'parties.id': 'GCA110620M19'}]\n",
"[{'parties.id': 'MSE010515SP2'}, {'parties.id': 'GPE9708147P8'}]\n",
"[{'parties.id': 'MBU1404103BA'}, {'parties.id': 'LRC071126QY4'}]\n",
"[{'parties.id': 'DIN1611245XA'}, {'parties.id': 'TCO161124L98'}]\n",
"[{'parties.id': 'D73016CAA1F8020E3BAC52068FB0B2D9'}, {'parties.id': 'MLA840208FN5'}]\n",
"[{'parties.id': 'PQU961210DJ9'}, {'parties.id': 'MOR020813I30'}]\n",
"[{'parties.id': 'CZU150602CX1'}, {'parties.id': 'HCG150126N25'}]\n",
"[{'parties.id': '6A21C3C1E1707E26662C9F8A00D63708'}, {'parties.id': '64E598A985DCE69FC2F25F6E95450DAC'}]\n",
"[{'parties.id': '8D8DB6545FDCD231D053E3E6D6A60B8E'}, {'parties.id': '79D3BCC04DBB4A85BEFE73D040C8E749'}]\n",
"[{'parties.id': '247AE9616FE58F0BF2C38A430711C7FF'}, {'parties.id': 'AB9A15D32A74ECD70CC1755734016C80'}]\n",
"[{'parties.id': '796E618431BE872B919388D1E2070F37'}, {'parties.id': '83882ED49A616D371EEF9F7FC74918DA'}]\n",
"[{'parties.id': 'BC76DBBE1172800C070419506156B2DA'}, {'parties.id': '41620D9F39AECF87A8E8109BE44DCC53'}]\n",
"[{'parties.id': 'CAJ160308ML4'}, {'parties.id': 'CAR130225JN6'}]\n",
"[{'parties.id': 'GCD0903207Y7'}, {'parties.id': 'CPA9301221NA'}]\n",
"[{'parties.id': 'FA35E31A1D75A092DE67A0002A7337DC'}, {'parties.id': '4050D2C40946894B46F7142D310BD974'}]\n",
"[{'parties.id': 'AMH140815CV5'}, {'parties.id': 'EBD4A5D63D1738C0A5A8CA4AEF839BE6'}]\n",
"[{'parties.id': 'GTA910608A32'}, {'parties.id': 'GRI9302081W7'}]\n",
"[{'parties.id': 'MMS160708JQA'}, {'parties.id': 'DIR070822774'}]\n",
"[{'parties.id': 'F54A547CB9CE80981FF6E7F1881835AF'}, {'parties.id': 'SSG180123IG1'}]\n",
"[{'parties.id': '39E9107BF49B418E5726C6FCB938FE81'}, {'parties.id': 'MPE110624HD4'}]\n",
"[{'parties.id': 'DLV131111TW9'}, {'parties.id': 'DCQ1110254V8'}]\n",
"[{'parties.id': 'OMS131023NU1'}, {'parties.id': 'DBM121023M10'}]\n",
"[{'parties.id': 'DMA170421L85'}, {'parties.id': 'DNO070226TWA'}]\n",
"[{'parties.id': 'OSM160921I24'}, {'parties.id': 'PGI100120N35'}]\n",
"[{'parties.id': '79005AFEE96D823B254D502C6D999E2D'}, {'parties.id': 'A495D416E886F6781B204AD6F798931E'}]\n",
"[{'parties.id': 'NUC051013L40'}, {'parties.id': 'CRE060905PK7'}]\n",
"[{'parties.id': 'GTA130827AP5'}, {'parties.id': 'UAC771230988'}, {'parties.id': 'QCA090930JAA'}]\n",
"[{'parties.id': 'ACO14O217NP9'}, {'parties.id': 'VAD0612O4V38'}]\n",
"[{'parties.id': 'JCC130918SI2'}, {'parties.id': 'CCA100406FV0'}]\n",
"[{'parties.id': 'BA9CE225986689791509C0B1A3061E32'}, {'parties.id': 'CUT130110US2'}]\n",
"[{'parties.id': 'IEC0502219G4'}, {'parties.id': 'GBC140110LW5'}]\n",
"[{'parties.id': 'ZPC090223IR5'}, {'parties.id': 'GCJ070307348'}]\n",
"[{'parties.id': 'CXB150928MB0'}, {'parties.id': 'MCE150223PI7'}]\n",
"[{'parties.id': 'AAD070326I33'}, {'parties.id': 'PFA020703GY8'}]\n",
"[{'parties.id': 'HIC891031SZA'}, {'parties.id': 'HIC9207288B2'}]\n",
"[{'parties.id': 'IIG050929AX5'}, {'parties.id': 'EIS981029CI1'}]\n",
"[{'parties.id': 'ROH1405075WA'}, {'parties.id': 'LRO0403116PA'}]\n",
"[{'parties.id': 'SCH120225BI9'}, {'parties.id': 'GEV091215AR4'}]\n",
"[{'parties.id': 'MED050715BE2'}, {'parties.id': 'CRI970421B93'}]\n",
"[{'parties.id': 'MIS130307KDA'}, {'parties.id': 'CME130307QC8'}]\n",
"[{'parties.id': 'CCM120526CI6'}, {'parties.id': 'GCN931005DF7'}]\n",
"[{'parties.id': 'SCE0004124H4'}, {'parties.id': 'GSG130129M65'}, {'parties.id': 'SSE001025NT9'}, {'parties.id': 'ROC1107042U9'}, {'parties.id': 'SLA111029PK5'}, {'parties.id': 'GOF721005286'}, {'parties.id': 'SVE911021GQ7'}]\n",
"[{'parties.id': 'ROX1603033T7'}, {'parties.id': 'SKA1503304G1'}]\n",
"[{'parties.id': 'CEC090716PZ7'}, {'parties.id': 'CDI980428392'}]\n",
"[{'parties.id': 'LPE091002911'}, {'parties.id': 'TSG990322IE4'}]\n",
"[{'parties.id': 'MAR140325334'}, {'parties.id': 'VCO160413J73'}]\n",
"[{'parties.id': 'MCO131021P49'}, {'parties.id': 'DCM0701128T7'}]\n",
"[{'parties.id': 'MIC140812GT3'}, {'parties.id': 'GCD160329LG4'}]\n",
"[{'parties.id': 'PPR120323G96'}, {'parties.id': 'CMC051128HW0'}]\n",
"[{'parties.id': '5F39200E194457C7291D091D83761DF9'}, {'parties.id': 'MDI100512355'}]\n",
"[{'parties.id': 'ASA940818HQ8'}, {'parties.id': 'PAU130419NZ0'}]\n",
"[{'parties.id': 'FLE001113KC9'}, {'parties.id': 'DRS130603GS8'}]\n",
"[{'parties.id': '8D0EB877613FBC6731F2D1A2812160CA'}, {'parties.id': 'B2D2583054883C71BBB05917320554E5'}]\n",
"[{'parties.id': 'PPA111228EG0'}, {'parties.id': 'ADA920610LK4'}, {'parties.id': 'GUC060613AY7'}]\n",
"[{'parties.id': 'RIN040823510'}, {'parties.id': 'RIS0907293B0'}, {'parties.id': 'CTA840227RT0'}, {'parties.id': 'PIN911004AK5'}]\n",
"[{'parties.id': '9F2A5568DB2EC16223C9072CD83E63AE'}, {'parties.id': '6121B916DD639C9A53D22C7F2943AFB9'}]\n",
"[{'parties.id': 'IDI9802234X8'}, {'parties.id': 'CCA120619SGO'}]\n",
"[{'parties.id': 'SAD050124Q50'}, {'parties.id': 'SAZ000925N86'}]\n",
"[{'parties.id': 'CD7F471D87AD6FDBA0170CBF356D7A0B'}, {'parties.id': 'E03BC1356495BFAB1D764B37C5ED7078'}]\n",
"[{'parties.id': 'LTI960220FP5'}, {'parties.id': 'ALA860227ID6'}]\n",
"[{'parties.id': 'CMD1101197FA'}, {'parties.id': 'CPR111109NA7'}, {'parties.id': 'KFO040609EA7'}, {'parties.id': 'KCO030922UM6'}]\n",
"[{'parties.id': 'CBA16031084A'}, {'parties.id': 'SIT180531P45'}]\n",
"[{'parties.id': 'TCM9207139S7'}, {'parties.id': 'CAM020227M88'}]\n",
"[{'parties.id': 'AAL860603DQ6'}, {'parties.id': 'RLI050504936'}, {'parties.id': 'PQL031105LE4'}, {'parties.id': 'QED051216531'}]\n",
"[{'parties.id': 'LCC971231PX3'}, {'parties.id': 'MSI020424I78'}]\n",
"[{'parties.id': 'ASF070131AV9'}, {'parties.id': 'IRC921125647'}]\n",
"[{'parties.id': 'ADC160331TG9'}, {'parties.id': 'RIN130322FEA'}, {'parties.id': 'CIN140717QD0'}]\n",
"[{'parties.id': 'R&G050928U8A'}, {'parties.id': 'EEJ101207SQ9'}]\n",
"[{'parties.id': '07EA267554E3BAB4B1D6CE4FF3B521AB'}, {'parties.id': 'SDI170508GE9'}]\n",
"[{'parties.id': 'SVK960605DP4'}, {'parties.id': 'ESM981001CI3'}]\n",
"[{'parties.id': 'AME0408107C9'}, {'parties.id': '81FC94938D0AEAEA32193502154D87DE'}]\n",
"[{'parties.id': 'TCS031021Q32'}, {'parties.id': 'CII930819177'}]\n",
"[{'parties.id': 'RDE020318D58'}, {'parties.id': 'COL840718D85'}, {'parties.id': 'DTN180416Q20'}, {'parties.id': '80517D0076CB1A1EB10FA9EC2EF97330'}]\n",
"[{'parties.id': 'CSE1601077U7'}, {'parties.id': 'URL151111GG7'}]\n",
"[{'parties.id': 'SLE021203RG1'}, {'parties.id': 'LGA111101NI7'}]\n",
"[{'parties.id': 'CAP910111U90'}, {'parties.id': 'SPI020108543'}]\n",
"[{'parties.id': 'GRA9406213L3'}, {'parties.id': 'TEL990629JR9'}]\n",
"[{'parties.id': 'GLA841203HGA'}, {'parties.id': 'VMI050120AU6'}]\n",
"[{'parties.id': 'HLO120315AW1'}, {'parties.id': 'MMA7608259J1'}]\n",
"[{'parties.id': 'DIB0306171I1'}, {'parties.id': 'COB130819CD5'}]\n",
"[{'parties.id': '812411525'}, {'parties.id': 'TIC1708093M3'}]\n",
"[{'parties.id': 'KME590502HY7'}, {'parties.id': 'MED730308NF0'}]\n",
"[{'parties.id': 'PFM891014836'}, {'parties.id': 'DST041201HL4'}]\n",
"[{'parties.id': 'AKU030910CH3'}, {'parties.id': 'CGR630227TQ0'}]\n",
"[{'parties.id': 'CIB150311KQA'}, {'parties.id': 'DGA150311QAA'}]\n",
"[{'parties.id': 'GAL060202CA5'}, {'parties.id': 'LLA160307QM6'}]\n",
"[{'parties.id': 'FRI0006282Q0'}, {'parties.id': 'MEL8605161U0'}]\n",
"[{'parties.id': 'GIX151118RT9'}, {'parties.id': '7DD171FE19ADBD9B69CB975598C293BC'}]\n",
"[{'parties.id': '41E41EAF0C1715D05BCE892CFFB3A1FA'}, {'parties.id': 'PSM120516T52'}]\n",
"[{'parties.id': 'IAP020717QR3'}, {'parties.id': 'COC0602143CA'}]\n"
]
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"[{'parties.id': 'PRS050620JW6'}, {'parties.id': 'SEG170828FP2'}]\n",
"[{'parties.id': 'DAA141006NB2'}, {'parties.id': 'NAR1102179D3'}]\n",
"[{'parties.id': 'CCO890116QA0'}, {'parties.id': 'CQC080313CP3'}]\n",
"[{'parties.id': '30792C7E87784AFA8986E72179624775'}, {'parties.id': 'GMP170315E57'}]\n",
"[{'parties.id': 'MAS090109T98'}, {'parties.id': 'MAM0702137S4'}]\n",
"[{'parties.id': 'ETA1410106EA'}, {'parties.id': 'ULJ141110G48'}]\n",
"[{'parties.id': 'TEC020701914'}, {'parties.id': 'TLA021022CM5'}]\n",
"[{'parties.id': '50CAC81A1D1F540CFC320C0C4BEA4DC4'}, {'parties.id': 'TTR031030166'}]\n",
"[{'parties.id': 'TME840315KT6'}, {'parties.id': 'BD03FBE666C3DBA5C57BCDC8BF0AA451'}]\n",
"[{'parties.id': '0B64525B0D937B9277C34E94460BB1F0'}, {'parties.id': 'B4C68D26AA652BA350706752576C672F'}]\n",
"[{'parties.id': 'FIH910606J83'}, {'parties.id': 'ELJ970709MB2'}]\n",
"[{'parties.id': 'ESA910101NY3'}, {'parties.id': 'RPU021127E66'}]\n",
"[{'parties.id': 'ELI1102188W5'}, {'parties.id': 'SUP060615BY8'}]\n",
"[{'parties.id': 'MLI040112JU5'}, {'parties.id': 'IMI110617F39'}]\n",
"[{'parties.id': '903B041B0370D681986EBC58D18B685E'}, {'parties.id': '72EBDC80DB5BBD3FB79CCD2CD0F21571'}]\n",
"[{'parties.id': '5F741438DA4DEF277EDFD0AF6754C9AD'}, {'parties.id': 'CMH140425IJ8'}]\n",
"[{'parties.id': 'GRE120517S7A'}, {'parties.id': 'DVA160209196'}]\n",
"[{'parties.id': 'NEX0710043N1'}, {'parties.id': 'EMA001219M72'}]\n",
"[{'parties.id': 'ECO140127QIA'}, {'parties.id': 'FF592E89C0D4FED4815F9DEECAFAAA47'}]\n",
"[{'parties.id': 'PRS140903DE8'}, {'parties.id': 'AME140819JG8'}]\n",
"[{'parties.id': 'RAL9206115U8'}, {'parties.id': 'DEN861217P3A'}]\n",
"[{'parties.id': 'STE160602HG2'}, {'parties.id': 'EAV090210QJ0'}]\n",
"[{'parties.id': 'POM891221EX9'}, {'parties.id': 'CDI140411FP3'}]\n",
"[{'parties.id': 'PIA090513817'}, {'parties.id': 'IMD021002183'}]\n",
"[{'parties.id': 'MOR020813I30'}, {'parties.id': 'PQU961210DJ9'}]\n",
"[{'parties.id': '68816D76FF0D334D718DB3F626AF2AF2'}, {'parties.id': 'MCS101122151'}]\n",
"[{'parties.id': '9825AE1F5028D9FA8A3B6DB305792B33'}, {'parties.id': '45CF05A5002EC4213E43A36C147AA96C'}]\n",
"[{'parties.id': 'OCE930624SW2'}, {'parties.id': 'OIM0808114Y6'}]\n",
"[{'parties.id': 'CUM9406175T9'}, {'parties.id': 'OMA101210J83'}]\n",
"[{'parties.id': 'SMM0809235UA'}, {'parties.id': 'DIR070822774'}]\n",
"[{'parties.id': 'GFE061004F65'}, {'parties.id': 'EEE830909BM4'}]\n",
"[{'parties.id': 'CMD080513MX1'}, {'parties.id': '63E7A3DE3AA7920EA8925D090B3695E6'}]\n",
"[{'parties.id': '543FECE89DA121C20A1E5395AB5D1804'}, {'parties.id': 'LVA050620HX7'}]\n",
"[{'parties.id': 'CPL990223QM8'}, {'parties.id': 'XAZ0208304U6'}]\n",
"[{'parties.id': 'CPA161219B72'}, {'parties.id': 'PME091210TJ9'}]\n",
"[{'parties.id': 'BA9CE225986689791509C0B1A3061E32'}, {'parties.id': 'CUT130110US2'}]\n",
"[{'parties.id': 'DIB800508G9A'}, {'parties.id': 'QMH841105255'}]\n",
"[{'parties.id': 'HIC891031SZA'}, {'parties.id': 'HIC9207288B2'}]\n",
"[{'parties.id': 'CCM120526CI6'}, {'parties.id': 'GCN931005DF7'}]\n",
"[{'parties.id': 'E1C3063CE8DD7DB3AC2AF8CC7DFB7A7C'}, {'parties.id': '708F60A1A1DD41FD33310F5E0E347087'}]\n",
"[{'parties.id': 'IST081203288'}, {'parties.id': 'GIC1404248L7'}]\n",
"[{'parties.id': 'FLE001113KC9'}, {'parties.id': 'DRS130603GS8'}]\n",
"[{'parties.id': 'LTI960220FP5'}, {'parties.id': 'ALA860227ID6'}]\n",
"[{'parties.id': 'UNI590804UB2'}, {'parties.id': 'MLA901022JF7'}]\n",
"[{'parties.id': 'AME130311L48'}, {'parties.id': 'AME181017JV4'}]\n",
"[{'parties.id': 'LOR110314NF9'}, {'parties.id': 'CDI980618JK2'}]\n",
"[{'parties.id': 'GTN160419130'}, {'parties.id': 'IRC151203F8A'}]\n",
"[{'parties.id': 'IME990322288'}, {'parties.id': 'CPI8908013SA'}]\n",
"[{'parties.id': '591C34B8CED778F60A8FA1E0E91C7917'}, {'parties.id': 'EDM080619398'}]\n",
"[{'parties.id': 'TCS1201237N3'}, {'parties.id': 'AVZ960506I1A'}]\n",
"[{'parties.id': 'NNC121031D79'}, {'parties.id': 'ATI101123F26'}]\n",
"[{'parties.id': 'TEC020701914'}, {'parties.id': 'TLA021022CM5'}]\n",
"[{'parties.id': 'ADO080314CC9'}, {'parties.id': 'ARA800729N99'}]\n",
"[{'parties.id': 'CVI041207JEA'}, {'parties.id': 'TDI160315HJ0'}]\n",
"[{'parties.id': 'ISE0910263D5'}, {'parties.id': 'INE120208FT1'}]\n",
"[{'parties.id': 'PRO160225UE2'}, {'parties.id': 'SEC170407PP3'}]\n",
"[{'parties.id': 'DECD12E11703BCDB67A076839A8D8041'}, {'parties.id': '230B385DD7A8ACD1605A590A5653FCA2'}]\n",
"[{'parties.id': 'ODN100106LN7'}, {'parties.id': 'CCO890116QA0'}]\n",
"[{'parties.id': '50CAC81A1D1F540CFC320C0C4BEA4DC4'}, {'parties.id': 'TTR031030166'}]\n",
"[{'parties.id': 'PEG860506CT9'}, {'parties.id': 'PSI741010UI1'}]\n",
"[{'parties.id': 'LFS040719JM4'}, {'parties.id': 'CCO050914F93'}]\n",
"[{'parties.id': 'NEX0710043N1'}, {'parties.id': 'EMA001219M72'}]\n",
"[{'parties.id': '907E98ECBB9F19A53CB0E7D0666A0895'}, {'parties.id': 'B086A50670DBE1FB7EC75D3B15D3D3BA'}]\n",
"[{'parties.id': 'DIC000410A76'}, {'parties.id': 'LBQ960708PK7'}]\n",
"[{'parties.id': 'CEF0604012L3'}, {'parties.id': 'IMC100821SX2'}]\n"
]
}
],
"source": [
"variables_contacto = ['telephone', 'email', 'name', 'streetAddress', 'faxNumber']\n",
"casos = []\n",
"for var_duplicated in variables_contacto:\n",
" # Encontramos todos los valores únicos de la variable de contacto y de tenderer_id\n",
" dups_direccion = df_contactos.loc[lambda x: (~x.duplicated(subset=['tenderer_id', var_duplicated])) & (x[var_duplicated].notnull())]\\\n",
" .loc[lambda x: (x[var_duplicated].duplicated()) & (~x['name'].str[:22].eq('- (Cuenta administrada')), var_duplicated].unique()\n",
" # Encontramos cuáles son los tenderers_id que comparten un mismo contacto\n",
" tenderers_dup_id = [df_contactos.loc[lambda x: x[var_duplicated].eq(dup)].drop_duplicates(subset=['tenderer_id'])['tenderer_id'].tolist()\n",
" for dup in dups_direccion]\n",
" # Buscamos los contratos en los que participaron los ids asociados\n",
" queries_dup = [[{'parties.id': i} for i in x] for x in tenderers_dup_id]\n",
" for q in queries_dup:\n",
" resultado = list(mydb.contrataciones.find({'$and': q}, {'_id': 0, 'ocid': 1}))\n",
" if resultado:\n",
" tenderers_id = [x['parties.id'] for x in q]\n",
" ocids = list({x['ocid'] for x in resultado})\n",
" casos.append({'tenderer_ids': tenderers_id, 'contratos_ocid': ocids, 'variable': var_duplicated})\n",
" print(q)\n",
" \n",
"with open('datos/casos_colusion.json', 'w', encoding='utf8') as jsonfile:\n",
" json.dump(casos, jsonfile)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* El resultado que encontramos es que existen 571 casos de contratistas posiblemente relacionados en una misma licitación."
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [],
"source": [
"with open('datos/casos_colusion.json', 'r', encoding='utf8') as jsonfile:\n",
" casos = json.load(jsonfile)"
]
},
{
"cell_type": "code",
"execution_count": 78,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"571"
]
},
"execution_count": 78,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"casos_ocid = list({c for cas in casos for c in cas['contratos_ocid']})\n",
"len(casos_ocid)"
]
},
{
"cell_type": "code",
"execution_count": 79,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"619"
]
},
"execution_count": 79,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"casos_contratos = list(mydb.contrataciones.find({'ocid': {'$in': casos_ocid}}))\n",
"len(casos_contratos)"
]
},
{
"cell_type": "code",
"execution_count": 331,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>titulo</th>\n",
" <th>descr</th>\n",
" <th>valor</th>\n",
" <th>dependencia_id</th>\n",
" <th>dependencia_nombre</th>\n",
" <th>uc_id</th>\n",
" <th>uc_name</th>\n",
" <th>fecha</th>\n",
" </tr>\n",
" <tr>\n",
" <th>ocid</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>ocds-07smqs-1317308</th>\n",
" <td>Servicio Integral de Suministro, Mantenimiento...</td>\n",
" <td>Servicio Integral de Suministro, Mantenimiento...</td>\n",
" <td>450000.00</td>\n",
" <td>CNBV-80</td>\n",
" <td>Comisión Nacional Bancaria y de Valores</td>\n",
" <td>CNB950501PT6-006B00001</td>\n",
" <td>CNBV-Dirección General Adjunta de Adquisicione...</td>\n",
" <td>2017-03-29T05:13:19Z</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1367848</th>\n",
" <td>PRESTADOR DE SERVICIOS INTEGRALES</td>\n",
" <td>PRESTADOR DE SERVICIOS INTEGRALES (HONORARIOS)</td>\n",
" <td>44542.62</td>\n",
" <td>SAGARPA-261</td>\n",
" <td>Secretaría de Agricultura, Ganadería, Desarrol...</td>\n",
" <td>SAG010710V98-008000995</td>\n",
" <td>SAGARPA-Delegacion Chihuahua #008000995</td>\n",
" <td>2017-05-22T12:24:14Z</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1430619</th>\n",
" <td>CONTRATACIÓN ABIERTA DEL SERVICIO DE LECTURA E...</td>\n",
" <td>CONTRATACIÓN ABIERTA DEL SERVICIO DE LECTURA E...</td>\n",
" <td>220000.00</td>\n",
" <td>SEP-265</td>\n",
" <td>Secretaría de Educación Pública</td>\n",
" <td>SEP210905778-011000999</td>\n",
" <td>SEP-Dirección de Adquisiciones #011000999</td>\n",
" <td>2017-07-20T06:22:43Z</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1444924</th>\n",
" <td>SERVICIO DE MANTENIMIENTO CORRECTIVO AL SISTEM...</td>\n",
" <td>SERVICIO DE MANTENIMIENTO CORRECTIVO AL SISTEM...</td>\n",
" <td>287780.00</td>\n",
" <td>CONAGUA-94</td>\n",
" <td>Comisión Nacional del Agua</td>\n",
" <td>CNA890116SF2-016B00009</td>\n",
" <td>CONAGUA-Gerencia de Resursos Materiales #016B0...</td>\n",
" <td>2017-08-03T01:58:53Z</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1452158</th>\n",
" <td>SERVICIO DE DIFUSIÓN EN MEDIOS DIGITALES DE LA...</td>\n",
" <td>SERVICIO DE DIFUSIÓN EN MEDIOS DIGITALES DE LA...</td>\n",
" <td>68950.00</td>\n",
" <td>CONUEE-98</td>\n",
" <td>Comisión Nacional para el Uso Eficiente de la ...</td>\n",
" <td>CNU800928K31-018E00999</td>\n",
" <td>CONUEE-Dirección de Recursos Materiales y Serv...</td>\n",
" <td>2017-08-30T04:21:41Z</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" titulo \\\n",
"ocid \n",
"ocds-07smqs-1317308 Servicio Integral de Suministro, Mantenimiento... \n",
"ocds-07smqs-1367848 PRESTADOR DE SERVICIOS INTEGRALES \n",
"ocds-07smqs-1430619 CONTRATACIÓN ABIERTA DEL SERVICIO DE LECTURA E... \n",
"ocds-07smqs-1444924 SERVICIO DE MANTENIMIENTO CORRECTIVO AL SISTEM... \n",
"ocds-07smqs-1452158 SERVICIO DE DIFUSIÓN EN MEDIOS DIGITALES DE LA... \n",
"\n",
" descr \\\n",
"ocid \n",
"ocds-07smqs-1317308 Servicio Integral de Suministro, Mantenimiento... \n",
"ocds-07smqs-1367848 PRESTADOR DE SERVICIOS INTEGRALES (HONORARIOS) \n",
"ocds-07smqs-1430619 CONTRATACIÓN ABIERTA DEL SERVICIO DE LECTURA E... \n",
"ocds-07smqs-1444924 SERVICIO DE MANTENIMIENTO CORRECTIVO AL SISTEM... \n",
"ocds-07smqs-1452158 SERVICIO DE DIFUSIÓN EN MEDIOS DIGITALES DE LA... \n",
"\n",
" valor dependencia_id \\\n",
"ocid \n",
"ocds-07smqs-1317308 450000.00 CNBV-80 \n",
"ocds-07smqs-1367848 44542.62 SAGARPA-261 \n",
"ocds-07smqs-1430619 220000.00 SEP-265 \n",
"ocds-07smqs-1444924 287780.00 CONAGUA-94 \n",
"ocds-07smqs-1452158 68950.00 CONUEE-98 \n",
"\n",
" dependencia_nombre \\\n",
"ocid \n",
"ocds-07smqs-1317308 Comisión Nacional Bancaria y de Valores \n",
"ocds-07smqs-1367848 Secretaría de Agricultura, Ganadería, Desarrol... \n",
"ocds-07smqs-1430619 Secretaría de Educación Pública \n",
"ocds-07smqs-1444924 Comisión Nacional del Agua \n",
"ocds-07smqs-1452158 Comisión Nacional para el Uso Eficiente de la ... \n",
"\n",
" uc_id \\\n",
"ocid \n",
"ocds-07smqs-1317308 CNB950501PT6-006B00001 \n",
"ocds-07smqs-1367848 SAG010710V98-008000995 \n",
"ocds-07smqs-1430619 SEP210905778-011000999 \n",
"ocds-07smqs-1444924 CNA890116SF2-016B00009 \n",
"ocds-07smqs-1452158 CNU800928K31-018E00999 \n",
"\n",
" uc_name \\\n",
"ocid \n",
"ocds-07smqs-1317308 CNBV-Dirección General Adjunta de Adquisicione... \n",
"ocds-07smqs-1367848 SAGARPA-Delegacion Chihuahua #008000995 \n",
"ocds-07smqs-1430619 SEP-Dirección de Adquisiciones #011000999 \n",
"ocds-07smqs-1444924 CONAGUA-Gerencia de Resursos Materiales #016B0... \n",
"ocds-07smqs-1452158 CONUEE-Dirección de Recursos Materiales y Serv... \n",
"\n",
" fecha \n",
"ocid \n",
"ocds-07smqs-1317308 2017-03-29T05:13:19Z \n",
"ocds-07smqs-1367848 2017-05-22T12:24:14Z \n",
"ocds-07smqs-1430619 2017-07-20T06:22:43Z \n",
"ocds-07smqs-1444924 2017-08-03T01:58:53Z \n",
"ocds-07smqs-1452158 2017-08-30T04:21:41Z "
]
},
"execution_count": 331,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"datos_contrato = [{'titulo': c['contracts'][0]['title'],\n",
" 'descr': c['contracts'][0].get('description', ''),\n",
" 'valor': c['contracts'][0]['value']['amount'],\n",
" 'dependencia_id': c['buyer']['id'],\n",
" 'dependencia_nombre': c['buyer']['name'],\n",
" 'uc_id': c['tender']['procuringEntity']['id'],\n",
" 'uc_name': c['tender']['procuringEntity']['name'],\n",
" 'ocid': c['ocid'],\n",
" 'fecha': c['date'],\n",
" }\n",
" \n",
" for c in casos_contratos if c.get('contracts', None)]\n",
"\n",
"df_datos_contratos = pd.DataFrame(datos_contrato).set_index('ocid')\n",
"df_datos_contratos.head()"
]
},
{
"cell_type": "code",
"execution_count": 123,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"{'_id': ObjectId('5dcdb0c10d84ead5c49d2e99'),\n",
" 'publisher': {'uid': '27511',\n",
" 'name': 'SECRETARÍA DE LA FUNCIÓN PÚBLICA',\n",
" 'uri': 'http://www.gob.mx/sfp'},\n",
" 'cycle': 2017,\n",
" 'ocid': 'ocds-07smqs-1317308',\n",
" 'id': 'SFP-1317308-2018-11-12',\n",
" 'date': '2017-03-29T05:13:19Z',\n",
" 'tag': ['tender', 'award'],\n",
" 'initiationType': 'tender',\n",
" 'parties': [{'name': 'Comisión Nacional Bancaria y de Valores',\n",
" 'id': 'CNBV-80',\n",
" 'roles': ['buyer']},\n",
" {'name': 'CNBV-Dirección General Adjunta de Adquisiciones y Contratos #006B00001',\n",
" 'id': 'CNB950501PT6-006B00001',\n",
" 'identifier': {'id': 'CNB950501PT6-006B00001',\n",
" 'legalName': 'CNBV-Dirección General Adjunta de Adquisiciones y Contratos #006B00001',\n",
" 'scheme': 'MX-RFC',\n",
" 'uri': 'https://portalsat.plataforma.sat.gob.mx/ConsultaRFC'},\n",
" 'address': {'streetAddress': 'Av. Insurgentes Sur No. 1971, Torre Sur, Piso 6, Col. Guadalupe Inn',\n",
" 'locality': 'Álvaro Obregón',\n",
" 'region': 'Ciudad de México',\n",
" 'postalCode': '01020',\n",
" 'countryName': 'MX'},\n",
" 'contactPoint': {'name': 'Jannet Miriam Martínez Sánchez',\n",
" 'email': 'jmartinezs@cnbv.gob.mx',\n",
" 'telephone': '1454-6537 y 1454-6538'},\n",
" 'roles': ['procuringEntity']},\n",
" {'name': 'MARIANA REGALADO SOBERON',\n",
" 'id': '3CAB041C0551441CB0A31EAC594B2339',\n",
" 'identifier': {'id': '3CAB041C0551441CB0A31EAC594B2339',\n",
" 'legalName': 'MARIANA REGALADO SOBERON',\n",
" 'scheme': 'MX-RFC',\n",
" 'uri': 'https://portalsat.plataforma.sat.gob.mx/ConsultaRFC'},\n",
" 'address': {},\n",
" 'contactPoint': {},\n",
" 'roles': ['tenderer']},\n",
" {'name': 'GRUPO SANMARI SA DE CV',\n",
" 'id': 'GSA0310175N4',\n",
" 'identifier': {'id': 'GSA0310175N4',\n",
" 'legalName': 'GRUPO SANMARI SA DE CV',\n",
" 'scheme': 'MX-RFC',\n",
" 'uri': 'https://portalsat.plataforma.sat.gob.mx/ConsultaRFC'},\n",
" 'address': {'streetAddress': 'AZTECAS 81 LA ROMANA',\n",
" 'locality': 'Tlalnepantla de Baz',\n",
" 'region': 'MX-MEX',\n",
" 'postalCode': '54050',\n",
" 'countryName': 'MÉXICO'},\n",
" 'contactPoint': {'email': 'rafael@sanmari.com.mx',\n",
" 'telephone': '55-52409421'},\n",
" 'roles': ['tenderer']},\n",
" {'name': 'HECTOR MANUEL SEGURA TORRE',\n",
" 'id': 'E2E9D6DA235621FC08C1A0EFC4201B95',\n",
" 'identifier': {'id': 'E2E9D6DA235621FC08C1A0EFC4201B95',\n",
" 'legalName': 'HECTOR MANUEL SEGURA TORRE',\n",
" 'scheme': 'MX-RFC',\n",
" 'uri': 'https://portalsat.plataforma.sat.gob.mx/ConsultaRFC'},\n",
" 'address': {},\n",
" 'contactPoint': {},\n",
" 'roles': ['tenderer']},\n",
" {'name': 'GABRIEL DEL POZO RUIZ',\n",
" 'id': 'AA2EEEF597460501F7B8A50B4DE1F671',\n",
" 'identifier': {'id': 'AA2EEEF597460501F7B8A50B4DE1F671',\n",
" 'legalName': 'GABRIEL DEL POZO RUIZ',\n",
" 'scheme': 'MX-RFC',\n",
" 'uri': 'https://portalsat.plataforma.sat.gob.mx/ConsultaRFC'},\n",
" 'address': {'streetAddress': 'CEIBAS 45',\n",
" 'locality': 'NAUCALPAN DE JUAREZ',\n",
" 'region': 'MX-MEX',\n",
" 'postalCode': '53240',\n",
" 'countryName': 'MÉXICO'},\n",
" 'contactPoint': {'name': 'GABRIEL DEL POZO RUIZ',\n",
" 'email': 'mascontrolmenoscosto@yahoo.com.mx',\n",
" 'telephone': '525536259819'},\n",
" 'roles': ['tenderer', 'supplier']}],\n",
" 'buyer': {'name': 'Comisión Nacional Bancaria y de Valores', 'id': 'CNBV-80'},\n",
" 'tender': {'id': '1317308',\n",
" 'title': 'Servicio Integral de Suministro, Mantenimiento Plantas y Macetas',\n",
" 'description': 'Servicio Integral de Suministro, Mantenimiento y Conservación de Plantas Naturales y Macetas Propiedad de la CNBV',\n",
" 'status': 'complete',\n",
" 'procuringEntity': {'name': 'CNBV-Dirección General Adjunta de Adquisiciones y Contratos #006B00001',\n",
" 'id': 'CNB950501PT6-006B00001'},\n",
" 'items': [{'id': '7044016',\n",
" 'description': 'Contratación de una Póliza de Seguro de Accidentes Personales para la protección de los participantes en acciones de capacitación del Programa de Apoyo al Empleo 2016.',\n",
" 'classification': {'id': '33900006',\n",
" 'description': 'Servicios de seguros de gastos medicos mayores'},\n",
" 'quantity': 1,\n",
" 'unit': {'name': 'Servicio'}}],\n",
" 'value': {'amount': 0},\n",
" 'procurementMethod': 'direct',\n",
" 'procurementMethodRationale': 'Art. 42 párrafo primero',\n",
" 'submissionMethod': ['electronicSubmission'],\n",
" 'tenderPeriod': {'startDate': '2017-03-29T05:13:19Z'},\n",
" 'enquiryPeriod': {'startDate': '2017-03-29T05:13:19Z'},\n",
" 'hasEnquiries': False,\n",
" 'awardPeriod': {'endDate': '2017-03-30T00:00:00Z'},\n",
" 'numberOfTenderers': 4,\n",
" 'tenderers': [{'name': 'MARIANA REGALADO SOBERON',\n",
" 'id': '3CAB041C0551441CB0A31EAC594B2339'},\n",
" {'name': 'GRUPO SANMARI SA DE CV', 'id': 'GSA0310175N4'},\n",
" {'name': 'GABRIEL DEL POZO RUIZ', 'id': 'AA2EEEF597460501F7B8A50B4DE1F671'},\n",
" {'name': 'HECTOR MANUEL SEGURA TORRE',\n",
" 'id': 'E2E9D6DA235621FC08C1A0EFC4201B95'}]},\n",
" 'language': 'es',\n",
" 'awards': [{'id': '1399908',\n",
" 'title': 'Servicio Integral de Suministro, Mantenimiento Plantas y Macetas',\n",
" 'description': 'Servicio Integral de Suministro, Mantenimiento y Conservación de Plantas Naturales y Macetas Propiedad de la CNBV',\n",
" 'status': 'active',\n",
" 'value': {'amount': 450000, 'currency': 'MXN'},\n",
" 'suppliers': [{'name': 'GABRIEL DEL POZO RUIZ',\n",
" 'id': 'AA2EEEF597460501F7B8A50B4DE1F671'}],\n",
" 'items': [{'id': '4645830',\n",
" 'description': 'Servicio Integral de Suministro, Mantenimiento Plantas y Macetas',\n",
" 'classification': {'scheme': 'CUCOP: Clasificador Único de las Contrataciones Públicas',\n",
" 'id': '35900004',\n",
" 'description': 'Servicios de jardineria',\n",
" 'uri': 'https://compranetinfo.funcionpublica.gob.mx/descargas/CUCOP.xlsx'},\n",
" 'quantity': 1,\n",
" 'unit': {'name': 'Servicio',\n",
" 'value': {'amount': 450000, 'currency': 'MXN'}}}],\n",
" 'contractPeriod': {'startDate': '2017-04-14T09:00:00Z',\n",
" 'endDate': '2018-06-18T03:59:00Z'}}],\n",
" 'contracts': [{'id': 1399908,\n",
" 'awardID': '1399908',\n",
" 'title': 'Servicio Integral de Suministro, Mantenimiento Plantas y Macetas',\n",
" 'description': 'Servicio Integral de Suministro, Mantenimiento y Conservación de Plantas Naturales y Macetas Propiedad de la CNBV',\n",
" 'status': 'terminated',\n",
" 'period': {'startDate': '2017-04-14T09:00:00Z',\n",
" 'endDate': '2018-06-18T03:59:00Z'},\n",
" 'value': {'amount': 450000, 'currency': 'MXN'},\n",
" 'items': [{'id': '4645830',\n",
" 'description': 'Servicio Integral de Suministro, Mantenimiento Plantas y Macetas',\n",
" 'classification': {'id': '35900004',\n",
" 'description': 'Servicios de jardineria'},\n",
" 'quantity': 1,\n",
" 'unit': {'name': 'Servicio',\n",
" 'value': {'amount': 450000, 'currency': 'MXN'}}}]}]}"
]
},
"execution_count": 123,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"casos_contratos[0]"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": 137,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>part</th>\n",
" <th>asoc</th>\n",
" <th>gana</th>\n",
" <th>N_part</th>\n",
" <th>N_asoc</th>\n",
" <th>N_gana</th>\n",
" <th>prop_asoc_part</th>\n",
" <th>part_mayo</th>\n",
" <th>asoc_ganadores</th>\n",
" <th>N_asoc_ganadores</th>\n",
" <th>part_nogana</th>\n",
" <th>titulo</th>\n",
" <th>descr</th>\n",
" <th>valor</th>\n",
" <th>dependencia_id</th>\n",
" <th>dependencia_nombre</th>\n",
" <th>uc_id</th>\n",
" <th>uc_name</th>\n",
" <th>fecha</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>ocds-07smqs-1043398</th>\n",
" <td>[TME840315KT6, BD03FBE666C3DBA5C57BCDC8BF0AA451]</td>\n",
" <td>[TME840315KT6, BD03FBE666C3DBA5C57BCDC8BF0AA451]</td>\n",
" <td>[TME840315KT6, BD03FBE666C3DBA5C57BCDC8BF0AA451]</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>1.000000</td>\n",
" <td>True</td>\n",
" <td>[BD03FBE666C3DBA5C57BCDC8BF0AA451, TME840315KT6]</td>\n",
" <td>2</td>\n",
" <td>[]</td>\n",
" <td>SERVICIO MPLS ATRAVES DE UN ENLACE DEDICADO CO...</td>\n",
" <td></td>\n",
" <td>169133.00</td>\n",
" <td>CIJ-66</td>\n",
" <td>Centros de Integración Juvenil, A.C.</td>\n",
" <td>CIJ731003QK3-012M7K001</td>\n",
" <td>CIJ-Departamento de Adquisiciones #012M7K001</td>\n",
" <td>2016-04-18T12:02:38Z</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1193763</th>\n",
" <td>[MLA840208FN5, D73016CAA1F8020E3BAC52068FB0B2D...</td>\n",
" <td>[D73016CAA1F8020E3BAC52068FB0B2D9, MLA840208FN5]</td>\n",
" <td>[MLA840208FN5, D73016CAA1F8020E3BAC52068FB0B2D...</td>\n",
" <td>4</td>\n",
" <td>2</td>\n",
" <td>4</td>\n",
" <td>0.500000</td>\n",
" <td>True</td>\n",
" <td>[MLA840208FN5, D73016CAA1F8020E3BAC52068FB0B2D9]</td>\n",
" <td>2</td>\n",
" <td>[]</td>\n",
" <td>ADQ. DE VIVERES PARA EJERCICIO 2017</td>\n",
" <td>ADQ. DE VIVERES PARA EJERCICIO 2017</td>\n",
" <td>3880739.50</td>\n",
" <td>IMSS-192</td>\n",
" <td>Instituto Mexicano del Seguro Social</td>\n",
" <td>IMS421231I45-050GYR045</td>\n",
" <td>IMSS-UMAE Hospital de Especilidades No.71 Dept...</td>\n",
" <td>2016-12-09T05:26:28Z</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1224403</th>\n",
" <td>[89E87098891F04A46318B7F775AD5E48, FAR100921AL...</td>\n",
" <td>[D73016CAA1F8020E3BAC52068FB0B2D9, MLA840208FN5]</td>\n",
" <td>[D73016CAA1F8020E3BAC52068FB0B2D9, 9D3346ADF0B...</td>\n",
" <td>8</td>\n",
" <td>2</td>\n",
" <td>5</td>\n",
" <td>0.250000</td>\n",
" <td>False</td>\n",
" <td>[MLA840208FN5, D73016CAA1F8020E3BAC52068FB0B2D9]</td>\n",
" <td>2</td>\n",
" <td>[FAR100921ALA, 89E87098891F04A46318B7F775AD5E4...</td>\n",
" <td>AA-019GYR026-E221-2016 DESIERTAS VIVERES</td>\n",
" <td></td>\n",
" <td>671930.69</td>\n",
" <td>IMSS-192</td>\n",
" <td>Instituto Mexicano del Seguro Social</td>\n",
" <td>IMS421231I45-050GYR026</td>\n",
" <td>IMSS-Coordinación de abastecimiento y equipami...</td>\n",
" <td>2016-12-02T05:49:52Z</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1240190</th>\n",
" <td>[RDO070228V11, ATD061228L34, MEX0301141G6]</td>\n",
" <td>[RDO070228V11, ATD061228L34]</td>\n",
" <td>[MEX0301141G6]</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>0.666667</td>\n",
" <td>True</td>\n",
" <td>[]</td>\n",
" <td>0</td>\n",
" <td>[RDO070228V11, ATD061228L34]</td>\n",
" <td>SERVICIO DE RESGUARDO, CUSTODIA, TRASLADO, ENV...</td>\n",
" <td>SERVICIO DE RESGUARDO, CUSTODIA, TRASLADO, ENV...</td>\n",
" <td>116379.72</td>\n",
" <td>CPTM-109</td>\n",
" <td>Consejo de Promoción Turística de México, S.A....</td>\n",
" <td>CPT991022DE7-021W3J001</td>\n",
" <td>CPTM-Gerencia de Adquisiciones y Licitaciones ...</td>\n",
" <td>2016-12-20T06:52:52Z</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1241959</th>\n",
" <td>[CGE130930JV2, CDA9601297G9, 23DF515587ED8B3F4...</td>\n",
" <td>[CDA9601297G9, 23DF515587ED8B3F4A8B1C9E4D725CAD]</td>\n",
" <td>[]</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0.666667</td>\n",
" <td>True</td>\n",
" <td>[]</td>\n",
" <td>0</td>\n",
" <td>[CGE130930JV2, 23DF515587ED8B3F4A8B1C9E4D725CA...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" part \\\n",
"ocds-07smqs-1043398 [TME840315KT6, BD03FBE666C3DBA5C57BCDC8BF0AA451] \n",
"ocds-07smqs-1193763 [MLA840208FN5, D73016CAA1F8020E3BAC52068FB0B2D... \n",
"ocds-07smqs-1224403 [89E87098891F04A46318B7F775AD5E48, FAR100921AL... \n",
"ocds-07smqs-1240190 [RDO070228V11, ATD061228L34, MEX0301141G6] \n",
"ocds-07smqs-1241959 [CGE130930JV2, CDA9601297G9, 23DF515587ED8B3F4... \n",
"\n",
" asoc \\\n",
"ocds-07smqs-1043398 [TME840315KT6, BD03FBE666C3DBA5C57BCDC8BF0AA451] \n",
"ocds-07smqs-1193763 [D73016CAA1F8020E3BAC52068FB0B2D9, MLA840208FN5] \n",
"ocds-07smqs-1224403 [D73016CAA1F8020E3BAC52068FB0B2D9, MLA840208FN5] \n",
"ocds-07smqs-1240190 [RDO070228V11, ATD061228L34] \n",
"ocds-07smqs-1241959 [CDA9601297G9, 23DF515587ED8B3F4A8B1C9E4D725CAD] \n",
"\n",
" gana \\\n",
"ocds-07smqs-1043398 [TME840315KT6, BD03FBE666C3DBA5C57BCDC8BF0AA451] \n",
"ocds-07smqs-1193763 [MLA840208FN5, D73016CAA1F8020E3BAC52068FB0B2D... \n",
"ocds-07smqs-1224403 [D73016CAA1F8020E3BAC52068FB0B2D9, 9D3346ADF0B... \n",
"ocds-07smqs-1240190 [MEX0301141G6] \n",
"ocds-07smqs-1241959 [] \n",
"\n",
" N_part N_asoc N_gana prop_asoc_part part_mayo \\\n",
"ocds-07smqs-1043398 2 2 2 1.000000 True \n",
"ocds-07smqs-1193763 4 2 4 0.500000 True \n",
"ocds-07smqs-1224403 8 2 5 0.250000 False \n",
"ocds-07smqs-1240190 3 2 1 0.666667 True \n",
"ocds-07smqs-1241959 3 2 0 0.666667 True \n",
"\n",
" asoc_ganadores \\\n",
"ocds-07smqs-1043398 [BD03FBE666C3DBA5C57BCDC8BF0AA451, TME840315KT6] \n",
"ocds-07smqs-1193763 [MLA840208FN5, D73016CAA1F8020E3BAC52068FB0B2D9] \n",
"ocds-07smqs-1224403 [MLA840208FN5, D73016CAA1F8020E3BAC52068FB0B2D9] \n",
"ocds-07smqs-1240190 [] \n",
"ocds-07smqs-1241959 [] \n",
"\n",
" N_asoc_ganadores \\\n",
"ocds-07smqs-1043398 2 \n",
"ocds-07smqs-1193763 2 \n",
"ocds-07smqs-1224403 2 \n",
"ocds-07smqs-1240190 0 \n",
"ocds-07smqs-1241959 0 \n",
"\n",
" part_nogana \\\n",
"ocds-07smqs-1043398 [] \n",
"ocds-07smqs-1193763 [] \n",
"ocds-07smqs-1224403 [FAR100921ALA, 89E87098891F04A46318B7F775AD5E4... \n",
"ocds-07smqs-1240190 [RDO070228V11, ATD061228L34] \n",
"ocds-07smqs-1241959 [CGE130930JV2, 23DF515587ED8B3F4A8B1C9E4D725CA... \n",
"\n",
" titulo \\\n",
"ocds-07smqs-1043398 SERVICIO MPLS ATRAVES DE UN ENLACE DEDICADO CO... \n",
"ocds-07smqs-1193763 ADQ. DE VIVERES PARA EJERCICIO 2017 \n",
"ocds-07smqs-1224403 AA-019GYR026-E221-2016 DESIERTAS VIVERES \n",
"ocds-07smqs-1240190 SERVICIO DE RESGUARDO, CUSTODIA, TRASLADO, ENV... \n",
"ocds-07smqs-1241959 NaN \n",
"\n",
" descr \\\n",
"ocds-07smqs-1043398 \n",
"ocds-07smqs-1193763 ADQ. DE VIVERES PARA EJERCICIO 2017 \n",
"ocds-07smqs-1224403 \n",
"ocds-07smqs-1240190 SERVICIO DE RESGUARDO, CUSTODIA, TRASLADO, ENV... \n",
"ocds-07smqs-1241959 NaN \n",
"\n",
" valor dependencia_id \\\n",
"ocds-07smqs-1043398 169133.00 CIJ-66 \n",
"ocds-07smqs-1193763 3880739.50 IMSS-192 \n",
"ocds-07smqs-1224403 671930.69 IMSS-192 \n",
"ocds-07smqs-1240190 116379.72 CPTM-109 \n",
"ocds-07smqs-1241959 NaN NaN \n",
"\n",
" dependencia_nombre \\\n",
"ocds-07smqs-1043398 Centros de Integración Juvenil, A.C. \n",
"ocds-07smqs-1193763 Instituto Mexicano del Seguro Social \n",
"ocds-07smqs-1224403 Instituto Mexicano del Seguro Social \n",
"ocds-07smqs-1240190 Consejo de Promoción Turística de México, S.A.... \n",
"ocds-07smqs-1241959 NaN \n",
"\n",
" uc_id \\\n",
"ocds-07smqs-1043398 CIJ731003QK3-012M7K001 \n",
"ocds-07smqs-1193763 IMS421231I45-050GYR045 \n",
"ocds-07smqs-1224403 IMS421231I45-050GYR026 \n",
"ocds-07smqs-1240190 CPT991022DE7-021W3J001 \n",
"ocds-07smqs-1241959 NaN \n",
"\n",
" uc_name \\\n",
"ocds-07smqs-1043398 CIJ-Departamento de Adquisiciones #012M7K001 \n",
"ocds-07smqs-1193763 IMSS-UMAE Hospital de Especilidades No.71 Dept... \n",
"ocds-07smqs-1224403 IMSS-Coordinación de abastecimiento y equipami... \n",
"ocds-07smqs-1240190 CPTM-Gerencia de Adquisiciones y Licitaciones ... \n",
"ocds-07smqs-1241959 NaN \n",
"\n",
" fecha \n",
"ocds-07smqs-1043398 2016-04-18T12:02:38Z \n",
"ocds-07smqs-1193763 2016-12-09T05:26:28Z \n",
"ocds-07smqs-1224403 2016-12-02T05:49:52Z \n",
"ocds-07smqs-1240190 2016-12-20T06:52:52Z \n",
"ocds-07smqs-1241959 NaN "
]
},
"execution_count": 137,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Número de particpantes totales de cada contrato\n",
"participantes_contrato = {c['ocid']: [p['id'] for p in c['parties'] if p['roles'] in [['tenderer'], ['tenderer', 'supplier']]] for c in casos_contratos}\n",
"# Número de particpantes que estaban asociados en cada contrato\n",
"asociados_contrato = {o: c['tenderer_ids'] for c in casos for o in c['contratos_ocid']}\n",
"# ganador contrato\n",
"ganadores_contrato = {c['ocid']: [p['id'] for p in c['parties'] if p['roles']==['tenderer', 'supplier']] for c in casos_contratos}\n",
"# dataframe\n",
"df_asoc = pd.DataFrame([participantes_contrato, asociados_contrato, ganadores_contrato]).T\\\n",
" .rename(columns={0: 'part', 1: 'asoc', 2: 'gana'})\\\n",
" .assign(N_part=lambda x: x['part'].str.len(),\n",
" N_asoc=lambda x: x['asoc'].str.len(),\n",
" N_gana=lambda x: x['gana'].str.len(),\n",
" prop_asoc_part=lambda x: x['N_asoc'].div(x['N_part']),\n",
" part_mayo=lambda x: x['prop_asoc_part'].ge(0.5),\n",
" asoc_ganadores=lambda x: x.apply(lambda y: list(set(y['gana']).intersection(set(y['asoc']))), axis=1),\n",
" N_asoc_ganadores=lambda x: x['asoc_ganadores'].str.len(),\n",
" part_nogana=lambda x: x.apply(lambda y: list(set(y['part']).difference(set(y['gana']))), axis=1))\\\n",
" .join(df_datos_contratos)\n",
"df_asoc.to_pickle(f'{dir_datos}/df_asociados.pkl')\n",
"df_asoc.head()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df_asoc = pd.read_pickle(f'{dir_datos}/df_asociados.pkl')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* ¿En cuántos de estos casos los contratistas representaban el 50% de los proponentes o más?"
]
},
{
"cell_type": "code",
"execution_count": 82,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Los contratistas representaban el 50% de los proponentes o más en 212 licitaciones\n"
]
}
],
"source": [
"print('Los contratistas representaban el 50% de los proponentes o más en', df_asoc.part_mayo.sum(), 'licitaciones')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* ¿En cuántos de estos casos los asociados fueron los únicos proponentes?"
]
},
{
"cell_type": "code",
"execution_count": 84,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"¿En cuántos de estos casos los asociados fueron los únicos proponentes? 41 licitaciones\n"
]
}
],
"source": [
"print('¿En cuántos de estos casos los asociados fueron los únicos proponentes?', df_asoc.prop_asoc_part.eq(1).sum(), 'licitaciones')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* De estos casos ¿en cuántas licitaciones los que estaban relacionados ganaron un concurso?"
]
},
{
"cell_type": "code",
"execution_count": 73,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"En 370 licitaciones ganó al menos uno de los contratistas asociados\n"
]
}
],
"source": [
"print('En', df_asoc.N_asoc_ganadores.gt(0).sum(), 'licitaciones ganó al menos uno de los contratistas asociados')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* ¿Cuántos contratistas asociados recibieron un contrato?"
]
},
{
"cell_type": "code",
"execution_count": 74,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"552 contratistas asociados ganaron una licitación\n"
]
}
],
"source": [
"print(df_asoc.N_asoc_ganadores.sum(), ' contratistas asociados ganaron una licitación')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* ¿En qué dependencias, unidades compradoras y servidores públicos ocurre más esto?"
]
},
{
"cell_type": "code",
"execution_count": 153,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"uc_name\n",
"CONALITEG-Dirección de Recursos Materiales y Servicios Generales #011L6J001 33\n",
"IMSS-Departamento de Adquisición de Bienes y Contratación de Servicios #050GYR033 16\n",
"IMSS-Coordinación de Abastecimiento y Equipamiento #050GYR009 10\n",
"IMSS-Coordinación de Adquisición de Bienes y Contratación de Serv, Dirección de Administración #050GYR047 9\n",
"INER-Departamento de Adquisiciones #012NCD001 9\n",
" ..\n",
"IMSS-UMAE HOSPITAL DE GINECO OBSTETRICIA No 03 DR VICTOR MANUEL ESPINOSA DE LOS REYES SANCHEZ CMN LA RAZA #050GYR050 1\n",
"IMSS-Coord. de Abastecimiento y Equipamiento Deleg. Ver. Sur #050GYR022 1\n",
"SCT-Centro SCT Chihuahua #009000980 1\n",
"SCT-CENTRO SCT EN CHIAPAS SUBDIRECCION DE ADMINISTRACION #009000992 1\n",
"Tribunales Agrarios-Dirección General de Recursos Materiales #031000001 1\n",
"Name: part, Length: 218, dtype: int64"
]
},
"execution_count": 153,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Por unidades compradoras\n",
"df_asoc.groupby(['uc_name'])['part'].count().sort_values(ascending=False)"
]
},
{
"cell_type": "code",
"execution_count": 155,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"dependencia_nombre\n",
"Instituto Mexicano del Seguro Social 215\n",
"Comisión Nacional de Libros de Texto Gratuitos 33\n",
"Instituto de Seguridad y Servicios Sociales de los Trabajadores del Estado 32\n",
"Comisión Federal de Electricidad 24\n",
"Comisión Nacional del Agua 15\n",
" ... \n",
"Hospital Regional de Alta Especialidad de Oaxaca 1\n",
"Exportadora de Sal, S.A. de C.V. 1\n",
"El Colegio de la Frontera Sur 1\n",
"Corporación Mexicana de Investigación en Materiales, S.A. de C.V. 1\n",
"Administración Portuaria Integral de Progreso, S.A. de C.V. 1\n",
"Name: part, Length: 92, dtype: int64"
]
},
"execution_count": 155,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_asoc.groupby(['dependencia_nombre'])['part'].count().sort_values(ascending=False)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Crea red para visualizar"
]
},
{
"cell_type": "code",
"execution_count": 192,
"metadata": {},
"outputs": [],
"source": [
"# Poner nombres en vez de ids\n",
"nodos = []\n",
"red = 1\n",
"for c, vals in df_asoc.iterrows():\n",
" nodos.append({'id': c, 'tipo': 'contrato'})\n",
" for p in vals['part_nogana']:\n",
" nodos.append({'id': p, 'tipo': 'tenderer'})\n",
" links.append({'origen_id': p, 'destino_id': c, 'accion': 'participa', 'red': red})\n",
" for p in vals['gana']:\n",
" nodos.append({'id': p, 'tipo': 'supplier'})\n",
" links.append({'origen_id': p, 'destino_id': c, 'accion': 'gana', 'red': red})\n",
" \n",
" for p1 in vals['asoc']:\n",
" for p2 in vals['asoc']:\n",
" if p1!=p2:\n",
" links.append({'origen_id': p1, 'destino_id': p2, 'accion': 'asociado', 'red': red})\n",
" \n",
" nodos.append({'id': vals['uc_id'], 'tipo': 'uc'})\n",
" links.append({'origen_id': vals['uc_id'], 'destino_id': c, 'accion': 'compra', 'red': red})\n",
" red+=1\n"
]
},
{
"cell_type": "code",
"execution_count": 277,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>origen_id</th>\n",
" <th>destino_id</th>\n",
" <th>accion</th>\n",
" <th>red</th>\n",
" <th>origen_num</th>\n",
" <th>destino_num</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>25696</th>\n",
" <td>TME840315KT6</td>\n",
" <td>ocds-07smqs-1043398</td>\n",
" <td>gana</td>\n",
" <td>1.0</td>\n",
" <td>287</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>25697</th>\n",
" <td>BD03FBE666C3DBA5C57BCDC8BF0AA451</td>\n",
" <td>ocds-07smqs-1043398</td>\n",
" <td>gana</td>\n",
" <td>1.0</td>\n",
" <td>286</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>25698</th>\n",
" <td>TME840315KT6</td>\n",
" <td>BD03FBE666C3DBA5C57BCDC8BF0AA451</td>\n",
" <td>asociado</td>\n",
" <td>1.0</td>\n",
" <td>287</td>\n",
" <td>286</td>\n",
" </tr>\n",
" <tr>\n",
" <th>25699</th>\n",
" <td>BD03FBE666C3DBA5C57BCDC8BF0AA451</td>\n",
" <td>TME840315KT6</td>\n",
" <td>asociado</td>\n",
" <td>1.0</td>\n",
" <td>286</td>\n",
" <td>287</td>\n",
" </tr>\n",
" <tr>\n",
" <th>25700</th>\n",
" <td>CIJ731003QK3-012M7K001</td>\n",
" <td>ocds-07smqs-1043398</td>\n",
" <td>compra</td>\n",
" <td>1.0</td>\n",
" <td>284</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" origen_id destino_id \\\n",
"25696 TME840315KT6 ocds-07smqs-1043398 \n",
"25697 BD03FBE666C3DBA5C57BCDC8BF0AA451 ocds-07smqs-1043398 \n",
"25698 TME840315KT6 BD03FBE666C3DBA5C57BCDC8BF0AA451 \n",
"25699 BD03FBE666C3DBA5C57BCDC8BF0AA451 TME840315KT6 \n",
"25700 CIJ731003QK3-012M7K001 ocds-07smqs-1043398 \n",
"\n",
" accion red origen_num destino_num \n",
"25696 gana 1.0 287 0 \n",
"25697 gana 1.0 286 0 \n",
"25698 asociado 1.0 287 286 \n",
"25699 asociado 1.0 286 287 \n",
"25700 compra 1.0 284 0 "
]
},
"execution_count": 277,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_nodos = pd.DataFrame(nodos)\\\n",
" .assign(num=lambda x:x.index)\n",
"dicc_nodo_num = {v:k for k,v in df_nodos['id'].to_dict().items()}\n",
"df_links = pd.DataFrame(links)\\\n",
" .assign(origen_num=lambda x: x['origen_id'].map(dicc_nodo_num),\n",
" destino_num=lambda x: x['destino_id'].map(dicc_nodo_num))\\\n",
" .dropna()\n",
"df_links.head()"
]
},
{
"cell_type": "code",
"execution_count": 206,
"metadata": {},
"outputs": [],
"source": [
"df_nodos.to_csv('datos/asociados_nodos.csv', index=False)\n",
"df_links.to_csv('datos/asociados_links.csv', index=False)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Visualziacion networkX "
]
},
{
"cell_type": "code",
"execution_count": 279,
"metadata": {},
"outputs": [],
"source": [
"df_nodos_graph = df_nodos.set_index('id')"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"for red in df_links.red.unique():\n",
" G = nx.from_pandas_edgelist(df_links.query('red==@red'), source='origen_id', target='destino_id', edge_attr=['accion'])\n",
" dicc_color_edges = {'gana': 'green', 'asociado': 'red', 'participa': '#3292a8', 'compra': 'orange'}\n",
" dicc_color_nodos = {'contrato': '#3292a8', 'supplier': 'pink', 'tenderer': 'blue', 'uc': 'orange'}\n",
" color_edges = [dicc_color_edges[e[2]['accion']] for e in G.edges(data=True)]\n",
" color_nodes = [dicc_color_nodos[df_nodos_graph.loc[[i], 'tipo'].tolist()[0]] for i in G.nodes]\n",
" fig, ax = plt.subplots()\n",
" draw_network(G, color_edges=color_edges, color_nodes=color_nodes, axes=ax, labels=[1, 2, 4, 5], text_size=8)\n",
" fig.savefig(f'graficas/redes/red_{red}.png', dpi=200)\n",
" plt.cla()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"scrolled": true
},
"outputs": [],
"source": [
"print(df_asoc.loc[[x for x in df_links.query('red==@red')['destino_id'].unique() if 'ocds' in x][0]])"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"\n",
"\n",
"**Tareas**:\n",
"\n",
"* Procesar telefonos\n",
"\n",
"* procesar múltiples mails\n",
"\n",
"* Es posible obtener más datos de los contratistas a partir del RUCP, como el sitio web, giro del negocio\n",
"\n",
"Buscar otra anomalía: todos los contratos con métodos abiertos en los que solo participa un proponente.\n",
"Buscar contratos en los que todos los particpantes reciben contrato.\n",
"* Buscar otra anomalía: todos los contratos con métodos abiertos en los que solo participa un proponente.\n",
"* Buscar contratos en los que todos los particpantes reciben contrato."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": 23,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/plain": [
"{'_id': ObjectId('5dcdaf7b0d84ead5c49c6dd8'),\n",
" 'publisher': {'uid': '27511',\n",
" 'name': 'SECRETARÍA DE LA FUNCIÓN PÚBLICA',\n",
" 'uri': 'http://www.gob.mx/sfp'},\n",
" 'cycle': 2016,\n",
" 'ocid': 'ocds-07smqs-1003803',\n",
" 'id': 'SFP-1003803-2018-11-12',\n",
" 'date': '2016-02-19T01:09:18Z',\n",
" 'tag': ['tender', 'award'],\n",
" 'initiationType': 'tender',\n",
" 'parties': [{'name': 'Servicio de Administración Tributaria',\n",
" 'id': 'SAT-284',\n",
" 'roles': ['buyer']},\n",
" {'name': 'SAT-Administración de Operación de Recursos y Servicios 6 #006E00002',\n",
" 'id': 'SAT970701NN3-006E00002',\n",
" 'identifier': {'id': 'SAT970701NN3-006E00002',\n",
" 'legalName': 'SAT-Administración de Operación de Recursos y Servicios 6 #006E00002',\n",
" 'scheme': 'MX-RFC',\n",
" 'uri': 'https://portalsat.plataforma.sat.gob.mx/ConsultaRFC'},\n",
" 'address': {'streetAddress': 'Avenida Hidalgo No. 77, Colonia Guerrero',\n",
" 'locality': 'Cuauhtémoc',\n",
" 'region': 'Ciudad de México',\n",
" 'postalCode': '06300',\n",
" 'countryName': 'MX'},\n",
" 'contactPoint': {'name': 'José Gabriel Ramos Martínez',\n",
" 'email': 'jose.ramosm@sat.gob.mx',\n",
" 'telephone': '5802-0318'},\n",
" 'roles': ['procuringEntity']},\n",
" {'name': 'IRAM LIEVANOS VELAZQUEZ',\n",
" 'id': 'E9C1C827AE1234CCF7AC4D9070BB597C',\n",
" 'identifier': {'id': 'E9C1C827AE1234CCF7AC4D9070BB597C',\n",
" 'legalName': 'IRAM LIEVANOS VELAZQUEZ',\n",
" 'scheme': 'MX-RFC',\n",
" 'uri': 'https://portalsat.plataforma.sat.gob.mx/ConsultaRFC'},\n",
" 'address': {'streetAddress': 'PASEO DE LA ASUNCION NO. 536',\n",
" 'locality': 'METEPEC',\n",
" 'region': 'MX-MEX',\n",
" 'postalCode': '52148',\n",
" 'countryName': 'MÉXICO'},\n",
" 'contactPoint': {'name': 'IRAM LIEVANOS VELAZQUEZ',\n",
" 'email': 'laroca.canino@gmail.com',\n",
" 'telephone': '52 722 093749'},\n",
" 'roles': ['tenderer', 'supplier']}],\n",
" 'buyer': {'name': 'Servicio de Administración Tributaria', 'id': 'SAT-284'},\n",
" 'tender': {'id': '1003803',\n",
" 'title': 'SERVICIO INTEGRAL DE CANINOS.',\n",
" 'description': 'SERVICIO INTEGRAL DE CANINOS.',\n",
" 'status': 'complete',\n",
" 'procuringEntity': {'name': 'SAT-Administración de Operación de Recursos y Servicios 6 #006E00002',\n",
" 'id': 'SAT970701NN3-006E00002'},\n",
" 'items': [],\n",
" 'value': {'amount': 0},\n",
" 'procurementMethod': 'direct',\n",
" 'procurementMethodRationale': 'Art. 41 fr. III',\n",
" 'submissionMethod': ['inPerson'],\n",
" 'tenderPeriod': {'startDate': '2016-02-19T01:09:18Z'},\n",
" 'enquiryPeriod': {'startDate': '2016-02-19T01:09:18Z'},\n",
" 'hasEnquiries': False,\n",
" 'awardPeriod': {'endDate': '2016-12-30T00:00:00Z'}},\n",
" 'language': 'es',\n",
" 'awards': [{'id': '1004248',\n",
" 'title': 'SERVICIO INTEGRAL DE CANINOS.',\n",
" 'status': 'active',\n",
" 'value': {'amount': 8451072, 'currency': 'MXN'},\n",
" 'suppliers': [{'name': 'IRAM LIEVANOS VELAZQUEZ',\n",
" 'id': 'E9C1C827AE1234CCF7AC4D9070BB597C'}],\n",
" 'contractPeriod': {'startDate': '2017-01-01T12:00:00Z',\n",
" 'endDate': '2017-03-08T11:59:00Z'}}],\n",
" 'contracts': [{'id': 1004248,\n",
" 'awardID': '1004248',\n",
" 'title': 'SERVICIO INTEGRAL DE CANINOS.',\n",
" 'status': 'terminated',\n",
" 'period': {'startDate': '2017-01-01T12:00:00Z',\n",
" 'endDate': '2017-03-08T11:59:00Z'},\n",
" 'value': {'amount': 8451072, 'currency': 'MXN'},\n",
" 'dateSigned': '2016-12-30T00:00:00Z'}]}"
]
},
"execution_count": 23,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"mydb.contrataciones.find_one()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Funcionarios que intervienen en contrataciones"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"113795"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"mydb.func_contrat.count_documents({})"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"{'_id': ObjectId('5deb255d723b95da59c6a01b'),\n",
" 'id': 'c6dbd706-b539-476f-a400-4dd69ed4a757',\n",
" 'fechaCaptura': '',\n",
" 'ejercicioFiscal': 2017,\n",
" 'periodoEjercicio': {'fechaInicial': '2017/01/01',\n",
" 'fechaFinal': '2017/12/31'},\n",
" 'idRamo': 6,\n",
" 'ramo': 'HACIENDA Y CRÉDITO PÚBLICO',\n",
" 'nombres': None,\n",
" 'primerApellido': None,\n",
" 'segundoApellido': None,\n",
" 'genero': None,\n",
" 'institucionDependencia': {'siglas': 'CNBV',\n",
" 'nombre': 'COMISIÓN NACIONAL BANCARIA Y DE VALORES',\n",
" 'clave': '6/B00'},\n",
" 'puesto': {'nombre': 'SUBDIRECTOR DE MEJORA A', 'nivel': None},\n",
" 'tipoArea': ['R'],\n",
" 'nivelResponsabilidad': ['A', 'T'],\n",
" 'tipoProcedimiento': 1,\n",
" 'tipoActos': 'CONTRATACIONES',\n",
" 'superiorInmediato': {'nombres': None,\n",
" 'primerApellido': None,\n",
" 'segundoApellido': None,\n",
" 'puesto': {'nombre': None, 'nivel': None}}}"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"mydb.func_contrat.find_one()"
]
},
{
"cell_type": "code",
"execution_count": 59,
"metadata": {},
"outputs": [],
"source": [
"nombre_func_contrat = [f'{r[\"nombres\"]} {r[\"primerApellido\"]} {r[\"segundoApellido\"]}'\n",
" for r in mydb.func_contrat.find({}, {'_id':0, 'nombres': 1, 'primerApellido': 1, 'segundoApellido':1}) if all([r[\"nombres\"], r[\"primerApellido\"], r[\"segundoApellido\"]])]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Funcionarios sancionados"
]
},
{
"cell_type": "code",
"execution_count": 169,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"3575"
]
},
"execution_count": 169,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"mydb.serv_sanc.count_documents({})"
]
},
{
"cell_type": "code",
"execution_count": 39,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"{'_id': ObjectId('5deb27a2432e395ca7ba4a62'),\n",
" 'nombres': 'ZACARIAS',\n",
" 'primerApellido': 'PEREZ',\n",
" 'segundoApellido': 'GARCIA',\n",
" 'institucionDependencia': {'nombre': 'PROCURADURIA GENERAL DE LA REPUBLICA',\n",
" 'siglas': ' '},\n",
" 'autoridadSancionadora': 'ORGANO INTERNO DE CONTROL',\n",
" 'expediente': '520/99',\n",
" 'resolucion': {'fechaResolucion': '17/11/2000'},\n",
" 'tipoSancion': 'INHABILITACION',\n",
" 'inhabilitacion': {'fechaInicial': '17/11/2000',\n",
" 'fechaFinal': '16/11/2020',\n",
" 'observaciones': None},\n",
" 'multa': {'monto': None, 'moneda': 'MXN'},\n",
" 'causaMotivoHechos': 'ABUSO DE AUTORIDAD',\n",
" 'puesto': 'AGENTE DE LA POLICIA JUDICIAL FEDERAL'}"
]
},
"execution_count": 39,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"mydb.serv_sanc.find_one()"
]
},
{
"cell_type": "code",
"execution_count": 170,
"metadata": {},
"outputs": [],
"source": [
"nombre_serv_sanc = [f'{r[\"nombres\"]} {r[\"primerApellido\"]} {r[\"segundoApellido\"]}' for r in mydb.serv_sanc.find({}, {'_id':0, 'nombres': 1, 'primerApellido': 1, 'segundoApellido':1})]"
]
},
{
"cell_type": "code",
"execution_count": 335,
"metadata": {},
"outputs": [],
"source": [
"casos_func = [p['contactPoint']['name'] for c in casos_contratos for p in c['parties'] if p['roles']==['procuringEntity']]"
]
},
{
"cell_type": "code",
"execution_count": 339,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"set()"
]
},
"execution_count": 339,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"set(casos_func).intersection(set(nombre_serv_sanc))"
]
},
{
"cell_type": "code",
"execution_count": 327,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"10"
]
},
"execution_count": 327,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"yt = list(set(df_contactos.name.unique().tolist()).intersection(set(nombre_serv_sanc)))\n",
"len(yt)"
]
},
{
"cell_type": "code",
"execution_count": 328,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"['MIGUEL ANGEL TORRES HERNANDEZ',\n",
" 'AGUSTIN TOLEDO GADEA',\n",
" 'OSCAR CHAVEZ MARTINEZ',\n",
" 'JOSE LUIS CHAVEZ FLORES',\n",
" 'MARIO HERNANDEZ DIAZ',\n",
" 'RODRIGO MALDONADO SAHAGUN',\n",
" 'ERIKA BENITEZ GARCIA',\n",
" 'FRANCISCO FIERRO SILVA',\n",
" 'JOSE LUIS GARCIA RODRIGUEZ',\n",
" 'JOSE DE LA CRUZ RAMIREZ']"
]
},
"execution_count": 328,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"yt"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Particulares sancionados"
]
},
{
"cell_type": "code",
"execution_count": 42,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"1853"
]
},
"execution_count": 42,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"mydb.part_sanc.count_documents({})"
]
},
{
"cell_type": "code",
"execution_count": 40,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"{'_id': ObjectId('5deb27d0715998a251b6be6b'),\n",
" 'fechaCaptura': '2019-08-22',\n",
" 'expediente': '000270074/2017',\n",
" 'nombreRazonSocial': 'CONSTRUCCIÓN ESPECIALIZADA Y TECNOLÓGICA DE MÉXICO, S.A. DE C.V.',\n",
" 'rfc': 'ACV990407',\n",
" 'telefono': '01 961 61 5 30 09',\n",
" 'domicilio': {'clave': 'MX'},\n",
" 'tipoSancion': 'ECONOMICA E INHABILITACIÓN',\n",
" 'institucionDependencia': {'nombre': 'SECRETARIA DE LA FUNCIÓN PÚBLICA',\n",
" 'siglas': 'SFP'},\n",
" 'tipoFalta': '',\n",
" 'causaMotivoHechos': 'NO ENTREGAR LA OBRA EN LA FECHA COMPROMETIDA PARA ELLO, ESTO ES EL 24 DE SEPTIEMBRE DE 2014',\n",
" 'objetoContrato': '',\n",
" 'autoridadSancionadora': 'SECRETARIA DE LA FUNCIÓN PÚBLICA',\n",
" 'responsableSancion': {'nombres': 'MARÍA GUADALUPE VARGAS ÁLVAREZ',\n",
" 'primerApellido': '',\n",
" 'segundoApellido': ''},\n",
" 'resolucion': {'sentido': 'SANCIONATORIA CON MULTA E INHABILITACIÓN'},\n",
" 'fechaNotificacion': '2019-08-14',\n",
" 'multa': {'monto': '504675.00', 'moneda': 'MXN'},\n",
" 'plazo': {'fechaInicial': '2019-08-23'},\n",
" 'observaciones': None}"
]
},
"execution_count": 40,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"mydb.part_sanc.find_one()"
]
},
{
"cell_type": "code",
"execution_count": 338,
"metadata": {},
"outputs": [],
"source": [
"rfc_sanc = [r['rfc'] for r in mydb.part_sanc.find({'rfc': {'$ne': ''}}, {'_id':0, 'rfc': 1})]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Red Mitchell"
]
},
{
"cell_type": "code",
"execution_count": 162,
"metadata": {},
"outputs": [],
"source": [
"result1 = mydb.contrataciones.find({'contracts': {'$exists': True}},\n",
" ['ocid', 'parties.id', 'parties.roles', 'parties.contactPoint', 'contracts.value.amount', 'date'])\n",
"\n",
"l1 = list(result1)"
]
},
{
"cell_type": "code",
"execution_count": 163,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>tenderer_id</th>\n",
" </tr>\n",
" <tr>\n",
" <th>ocid</th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>ocds-07smqs-1003803</th>\n",
" <td>E9C1C827AE1234CCF7AC4D9070BB597C</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1003123</th>\n",
" <td>SCA031118BX7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1003123</th>\n",
" <td>SAU0505307M9</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1003123</th>\n",
" <td>SCK070618C21</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1009245</th>\n",
" <td>R&amp;S811221KR6</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" tenderer_id\n",
"ocid \n",
"ocds-07smqs-1003803 E9C1C827AE1234CCF7AC4D9070BB597C\n",
"ocds-07smqs-1003123 SCA031118BX7\n",
"ocds-07smqs-1003123 SAU0505307M9\n",
"ocds-07smqs-1003123 SCK070618C21\n",
"ocds-07smqs-1009245 R&S811221KR6"
]
},
"execution_count": 163,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"ocid_tenderer = pd.DataFrame([(c['ocid'], p['id']) for c in l1 for p in c['parties']\n",
" if p['roles'] in [['tenderer', 'supplier'], ['tenderer']]],\n",
" columns=['ocid', 'tenderer_id'],\n",
" ).set_index('ocid')\n",
"ocid_tenderer.head()"
]
},
{
"cell_type": "code",
"execution_count": 164,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>funcionario_id</th>\n",
" <th>uc_id</th>\n",
" <th>valor_contrato</th>\n",
" <th>fecha</th>\n",
" </tr>\n",
" <tr>\n",
" <th>ocid</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>ocds-07smqs-1003803</th>\n",
" <td>José Gabriel Ramos Martínez</td>\n",
" <td>SAT970701NN3-006E00002</td>\n",
" <td>8451072.00</td>\n",
" <td>2016-02-19T01:09:18Z</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1003123</th>\n",
" <td>Ignacio Romero Sánchez</td>\n",
" <td>PGR850101RC6-017000017</td>\n",
" <td>168000.00</td>\n",
" <td>2016-02-19T01:49:22Z</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1009245</th>\n",
" <td>Juan Fernando Meza Zavala</td>\n",
" <td>STP401231P53-014000999</td>\n",
" <td>420689.55</td>\n",
" <td>2016-02-26T05:33:08Z</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1012355</th>\n",
" <td>Luis Eduardo Vega Becerra</td>\n",
" <td>CNU800928K31-018E00999</td>\n",
" <td>20000.00</td>\n",
" <td>2016-03-02T01:58:39Z</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1025654</th>\n",
" <td>Marco Antonio Brito Vidales</td>\n",
" <td>IAA6210025R4-006A00996</td>\n",
" <td>10604000.00</td>\n",
" <td>2016-03-18T06:40:28Z</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" funcionario_id uc_id \\\n",
"ocid \n",
"ocds-07smqs-1003803 José Gabriel Ramos Martínez SAT970701NN3-006E00002 \n",
"ocds-07smqs-1003123 Ignacio Romero Sánchez PGR850101RC6-017000017 \n",
"ocds-07smqs-1009245 Juan Fernando Meza Zavala STP401231P53-014000999 \n",
"ocds-07smqs-1012355 Luis Eduardo Vega Becerra CNU800928K31-018E00999 \n",
"ocds-07smqs-1025654 Marco Antonio Brito Vidales IAA6210025R4-006A00996 \n",
"\n",
" valor_contrato fecha \n",
"ocid \n",
"ocds-07smqs-1003803 8451072.00 2016-02-19T01:09:18Z \n",
"ocds-07smqs-1003123 168000.00 2016-02-19T01:49:22Z \n",
"ocds-07smqs-1009245 420689.55 2016-02-26T05:33:08Z \n",
"ocds-07smqs-1012355 20000.00 2016-03-02T01:58:39Z \n",
"ocds-07smqs-1025654 10604000.00 2016-03-18T06:40:28Z "
]
},
"execution_count": 164,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"ocid_funcionario = pd.DataFrame([(c['ocid'], p['contactPoint'].get('name', ''), p['id'],\n",
" c['contracts'][0]['value']['amount'], c['date'])\n",
" for c in l1 for p in c['parties']\n",
" if p['roles']==['procuringEntity']], columns=['ocid', 'funcionario_id', 'uc_id', 'valor_contrato', 'fecha'])\\\n",
" .set_index('ocid')\n",
"\n",
"ocid_dependencia = pd.DataFrame([(c['ocid'], p['id'])\n",
" for c in l1 for p in c['parties']\n",
" if p['roles']==['buyer']], columns=['ocid', 'dep_id'])\\\n",
" .set_index('ocid')\n",
"\n",
"ocid_funcionario.head()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": 165,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>tenderer_id</th>\n",
" <th>funcionario_id</th>\n",
" <th>uc_id</th>\n",
" <th>valor_contrato</th>\n",
" <th>fecha</th>\n",
" <th>dep_id</th>\n",
" </tr>\n",
" <tr>\n",
" <th>ocid</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>ocds-07smqs-1001024</th>\n",
" <td>ELE9012281G2</td>\n",
" <td>Evelyn López Valverde</td>\n",
" <td>LIC950821M84-020VST003</td>\n",
" <td>1.152540e+05</td>\n",
" <td>2016-03-15T01:02:50Z</td>\n",
" <td>LICONSA-231</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1001040</th>\n",
" <td>HIG090519H30</td>\n",
" <td>Nicolas Gonzalez Bustos</td>\n",
" <td>HIM871203BS0-012NBG001</td>\n",
" <td>2.603075e+07</td>\n",
" <td>2016-02-16T02:44:58Z</td>\n",
" <td>HIM-163</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1001984</th>\n",
" <td>282910F3163E9D7DBC543E53CD9347B6</td>\n",
" <td>Nicolas Gonzalez Bustos</td>\n",
" <td>HIM871203BS0-012NBG001</td>\n",
" <td>1.071380e+05</td>\n",
" <td>2016-02-17T04:42:35Z</td>\n",
" <td>HIM-163</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1002362</th>\n",
" <td>IPS040121S66</td>\n",
" <td>Nicolas Gonzalez Bustos</td>\n",
" <td>HIM871203BS0-012NBG001</td>\n",
" <td>2.115000e+05</td>\n",
" <td>2016-02-17T07:30:57Z</td>\n",
" <td>HIM-163</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-1003123</th>\n",
" <td>SCA031118BX7</td>\n",
" <td>Ignacio Romero Sánchez</td>\n",
" <td>PGR850101RC6-017000017</td>\n",
" <td>1.680000e+05</td>\n",
" <td>2016-02-19T01:49:22Z</td>\n",
" <td>PGR-251</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-999514</th>\n",
" <td>CPC131113AT4</td>\n",
" <td>Luis Enrique Mendoza Flores</td>\n",
" <td>IMS421231I45-050GYR026</td>\n",
" <td>7.317600e+04</td>\n",
" <td>2016-02-12T01:34:46Z</td>\n",
" <td>IMSS-192</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-999514</th>\n",
" <td>96A74A55F4E5DAEC0797B59049D8EC81</td>\n",
" <td>Luis Enrique Mendoza Flores</td>\n",
" <td>IMS421231I45-050GYR026</td>\n",
" <td>7.317600e+04</td>\n",
" <td>2016-02-12T01:34:46Z</td>\n",
" <td>IMSS-192</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-999514</th>\n",
" <td>TGH130612IK1</td>\n",
" <td>Luis Enrique Mendoza Flores</td>\n",
" <td>IMS421231I45-050GYR026</td>\n",
" <td>7.317600e+04</td>\n",
" <td>2016-02-12T01:34:46Z</td>\n",
" <td>IMSS-192</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-999514</th>\n",
" <td>SIN011023UC8</td>\n",
" <td>Luis Enrique Mendoza Flores</td>\n",
" <td>IMS421231I45-050GYR026</td>\n",
" <td>7.317600e+04</td>\n",
" <td>2016-02-12T01:34:46Z</td>\n",
" <td>IMSS-192</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ocds-07smqs-999514</th>\n",
" <td>GMC09121623A</td>\n",
" <td>Luis Enrique Mendoza Flores</td>\n",
" <td>IMS421231I45-050GYR026</td>\n",
" <td>7.317600e+04</td>\n",
" <td>2016-02-12T01:34:46Z</td>\n",
" <td>IMSS-192</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>726038 rows × 6 columns</p>\n",
"</div>"
],
"text/plain": [
" tenderer_id \\\n",
"ocid \n",
"ocds-07smqs-1001024 ELE9012281G2 \n",
"ocds-07smqs-1001040 HIG090519H30 \n",
"ocds-07smqs-1001984 282910F3163E9D7DBC543E53CD9347B6 \n",
"ocds-07smqs-1002362 IPS040121S66 \n",
"ocds-07smqs-1003123 SCA031118BX7 \n",
"... ... \n",
"ocds-07smqs-999514 CPC131113AT4 \n",
"ocds-07smqs-999514 96A74A55F4E5DAEC0797B59049D8EC81 \n",
"ocds-07smqs-999514 TGH130612IK1 \n",
"ocds-07smqs-999514 SIN011023UC8 \n",
"ocds-07smqs-999514 GMC09121623A \n",
"\n",
" funcionario_id uc_id \\\n",
"ocid \n",
"ocds-07smqs-1001024 Evelyn López Valverde LIC950821M84-020VST003 \n",
"ocds-07smqs-1001040 Nicolas Gonzalez Bustos HIM871203BS0-012NBG001 \n",
"ocds-07smqs-1001984 Nicolas Gonzalez Bustos HIM871203BS0-012NBG001 \n",
"ocds-07smqs-1002362 Nicolas Gonzalez Bustos HIM871203BS0-012NBG001 \n",
"ocds-07smqs-1003123 Ignacio Romero Sánchez PGR850101RC6-017000017 \n",
"... ... ... \n",
"ocds-07smqs-999514 Luis Enrique Mendoza Flores IMS421231I45-050GYR026 \n",
"ocds-07smqs-999514 Luis Enrique Mendoza Flores IMS421231I45-050GYR026 \n",
"ocds-07smqs-999514 Luis Enrique Mendoza Flores IMS421231I45-050GYR026 \n",
"ocds-07smqs-999514 Luis Enrique Mendoza Flores IMS421231I45-050GYR026 \n",
"ocds-07smqs-999514 Luis Enrique Mendoza Flores IMS421231I45-050GYR026 \n",
"\n",
" valor_contrato fecha dep_id \n",
"ocid \n",
"ocds-07smqs-1001024 1.152540e+05 2016-03-15T01:02:50Z LICONSA-231 \n",
"ocds-07smqs-1001040 2.603075e+07 2016-02-16T02:44:58Z HIM-163 \n",
"ocds-07smqs-1001984 1.071380e+05 2016-02-17T04:42:35Z HIM-163 \n",
"ocds-07smqs-1002362 2.115000e+05 2016-02-17T07:30:57Z HIM-163 \n",
"ocds-07smqs-1003123 1.680000e+05 2016-02-19T01:49:22Z PGR-251 \n",
"... ... ... ... \n",
"ocds-07smqs-999514 7.317600e+04 2016-02-12T01:34:46Z IMSS-192 \n",
"ocds-07smqs-999514 7.317600e+04 2016-02-12T01:34:46Z IMSS-192 \n",
"ocds-07smqs-999514 7.317600e+04 2016-02-12T01:34:46Z IMSS-192 \n",
"ocds-07smqs-999514 7.317600e+04 2016-02-12T01:34:46Z IMSS-192 \n",
"ocds-07smqs-999514 7.317600e+04 2016-02-12T01:34:46Z IMSS-192 \n",
"\n",
"[726038 rows x 6 columns]"
]
},
"execution_count": 165,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"ocid_tender_fun = ocid_tenderer.join([ocid_funcionario, ocid_dependencia])\n",
"ocid_tender_fun"
]
},
{
"cell_type": "code",
"execution_count": 166,
"metadata": {},
"outputs": [],
"source": [
"ocid_tender_fun.to_csv(f'{dir_datos}/ocid_tender_fun.csv')"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"gist": {
"data": {
"description": "compranet_mongodb.ipynb",
"public": true
},
"id": ""
},
"hide_input": false,
"kernel_info": {
"name": "python3"
},
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.7.3"
},
"nteract": {
"version": "0.15.0"
},
"toc": {
"base_numbering": 1,
"nav_menu": {},
"number_sections": true,
"sideBar": true,
"skip_h1_title": false,
"title_cell": "Table of Contents",
"title_sidebar": "Contents",
"toc_cell": false,
"toc_position": {},
"toc_section_display": true,
"toc_window_display": false
},
"varInspector": {
"cols": {
"lenName": 16,
"lenType": 16,
"lenVar": 40
},
"kernels_config": {
"python": {
"delete_cmd_postfix": "",
"delete_cmd_prefix": "del ",
"library": "var_list.py",
"varRefreshCmd": "print(var_dic_list())"
},
"r": {
"delete_cmd_postfix": ") ",
"delete_cmd_prefix": "rm(",
"library": "var_list.r",
"varRefreshCmd": "cat(var_dic_list()) "
}
},
"types_to_exclude": [
"module",
"function",
"builtin_function_or_method",
"instance",
"_Feature"
],
"window_display": false
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment