Data_exploration_and_creation_of_a_new_dataset.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data_exploration_and_creation_of_a_new_dataset.ipynb\r\n",
      "contratacionesabiertas_bulk.json\r\n"
     ]
    }
   ],
   "source": [
    "%ls\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1.2.4'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "pd.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sucio = pd.read_json('contratacionesabiertas_bulk.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Taking a look at our dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(292289, 14)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sucio.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>_id</th>\n",
       "      <th>publisher</th>\n",
       "      <th>cycle</th>\n",
       "      <th>ocid</th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>tag</th>\n",
       "      <th>initiationType</th>\n",
       "      <th>parties</th>\n",
       "      <th>buyer</th>\n",
       "      <th>tender</th>\n",
       "      <th>language</th>\n",
       "      <th>awards</th>\n",
       "      <th>contracts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'$oid': '5e2a3797575b631d3c62539e'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1003123</td>\n",
       "      <td>SHCP-1003123-2018-11-12</td>\n",
       "      <td>2016-02-19 01:49:22+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Procuraduría General de la Repúblic...</td>\n",
       "      <td>{'name': 'Procuraduría General de la República...</td>\n",
       "      <td>{'id': '1003123', 'title': 'MANTENIMIENTO PREV...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1377619', 'title': 'MANTENIMIENTO PRE...</td>\n",
       "      <td>[{'id': 1377619, 'awardID': '1377619', 'title'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'$oid': '5e2a3798325093834fd90805'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1003803</td>\n",
       "      <td>SHCP-1003803-2018-11-12</td>\n",
       "      <td>2016-02-19 01:09:18+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Servicio de Administración Tributar...</td>\n",
       "      <td>{'name': 'Servicio de Administración Tributari...</td>\n",
       "      <td>{'id': '1003803', 'title': 'SERVICIO INTEGRAL ...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1004248', 'title': 'SERVICIO INTEGRAL...</td>\n",
       "      <td>[{'id': 1004248, 'awardID': '1004248', 'title'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'$oid': '5e2a379837be1c3fcb93dd99'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1009245</td>\n",
       "      <td>SHCP-1009245-2018-11-12</td>\n",
       "      <td>2016-02-26 05:33:08+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Secretaría del Trabajo y Previsión ...</td>\n",
       "      <td>{'name': 'Secretaría del Trabajo y Previsión S...</td>\n",
       "      <td>{'id': '1009245', 'title': 'CONTRATACION DE UN...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1011086', 'title': 'PÓLIZA DE SEGURO ...</td>\n",
       "      <td>[{'id': 1011086, 'awardID': '1011086', 'title'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'$oid': '5e2a3798d0ac91b80a73a1af'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1012355</td>\n",
       "      <td>SHCP-1012355-2018-11-12</td>\n",
       "      <td>2016-03-02 01:58:39+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Comisión Nacional para el Uso Efici...</td>\n",
       "      <td>{'name': 'Comisión Nacional para el Uso Eficie...</td>\n",
       "      <td>{'id': '1012355', 'title': 'SERVICIO INTEGRAL ...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1013973', 'title': 'SERVICIO DE ASESO...</td>\n",
       "      <td>[{'id': 1013973, 'awardID': '1013973', 'title'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'$oid': '5e2a3798cb04247fb1dcbd1b'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1017118</td>\n",
       "      <td>SHCP-1017118-2018-11-12</td>\n",
       "      <td>2016-03-08 07:17:10+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Nacional de Antropología ...</td>\n",
       "      <td>{'name': 'Instituto Nacional de Antropología e...</td>\n",
       "      <td>{'id': '1017118', 'title': 'Servicios de Jardi...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1172348', 'title': 'SERVICIOS DE JARD...</td>\n",
       "      <td>[{'id': 1172348, 'awardID': '1172348', 'title'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>{'$oid': '5e2a3798ccd1a3c28b5d561e'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1025654</td>\n",
       "      <td>SHCP-1025654-2018-11-12</td>\n",
       "      <td>2016-03-18 06:40:28+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto de Administración y Avalú...</td>\n",
       "      <td>{'name': 'Instituto de Administración y Avalúo...</td>\n",
       "      <td>{'id': '1025654', 'title': 'PLATAFORMA DEL SIS...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1030997', 'title': 'PLATAFORMA DEL SI...</td>\n",
       "      <td>[{'id': 1030997, 'awardID': '1030997', 'title'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>{'$oid': '5e2a379810fc9bce0138fcb2'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1025723</td>\n",
       "      <td>SHCP-1025723-2018-11-12</td>\n",
       "      <td>2016-03-18 08:45:03+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto de Administración y Avalú...</td>\n",
       "      <td>{'name': 'Instituto de Administración y Avalúo...</td>\n",
       "      <td>{'id': '1025723', 'title': 'TRANSPORTACIÓN AÉR...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1031035', 'title': 'TRANSPORTACIÓN AÉ...</td>\n",
       "      <td>[{'id': 1031035, 'awardID': '1031035', 'title'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>{'$oid': '5e2a379849d82cbe460d7d03'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1026720</td>\n",
       "      <td>SHCP-1026720-2018-11-12</td>\n",
       "      <td>2016-03-22 12:54:35+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Comisión Nacional para el Uso Efici...</td>\n",
       "      <td>{'name': 'Comisión Nacional para el Uso Eficie...</td>\n",
       "      <td>{'id': '1026720', 'title': 'SERVICIO DE PENSIO...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1066219', 'title': 'ALMACENAJE DE BIE...</td>\n",
       "      <td>[{'id': 1066219, 'awardID': '1066219', 'title'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>{'$oid': '5e2a3798062f9471dec60004'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1028067</td>\n",
       "      <td>SHCP-1028067-2018-11-12</td>\n",
       "      <td>2016-03-28 11:22:52+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto de Administración y Avalú...</td>\n",
       "      <td>{'name': 'Instituto de Administración y Avalúo...</td>\n",
       "      <td>{'id': '1028067', 'title': 'SERVICIO DE JARDIN...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1033955', 'title': 'SERVICIO DE JARDI...</td>\n",
       "      <td>[{'id': 1033955, 'awardID': '1033955', 'title'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>{'$oid': '5e2a3798e15634080b1a95a0'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1028116</td>\n",
       "      <td>SHCP-1028116-2018-11-12</td>\n",
       "      <td>2016-03-28 11:48:31+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto de Administración y Avalú...</td>\n",
       "      <td>{'name': 'Instituto de Administración y Avalúo...</td>\n",
       "      <td>{'id': '1028116', 'title': 'EFECTIVALE, S. DE ...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1034007', 'title': 'EFECTIVALE, S. DE...</td>\n",
       "      <td>[{'id': 1034007, 'awardID': '1034007', 'title'...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    _id  \\\n",
       "0  {'$oid': '5e2a3797575b631d3c62539e'}   \n",
       "1  {'$oid': '5e2a3798325093834fd90805'}   \n",
       "2  {'$oid': '5e2a379837be1c3fcb93dd99'}   \n",
       "3  {'$oid': '5e2a3798d0ac91b80a73a1af'}   \n",
       "4  {'$oid': '5e2a3798cb04247fb1dcbd1b'}   \n",
       "5  {'$oid': '5e2a3798ccd1a3c28b5d561e'}   \n",
       "6  {'$oid': '5e2a379810fc9bce0138fcb2'}   \n",
       "7  {'$oid': '5e2a379849d82cbe460d7d03'}   \n",
       "8  {'$oid': '5e2a3798062f9471dec60004'}   \n",
       "9  {'$oid': '5e2a3798e15634080b1a95a0'}   \n",
       "\n",
       "                                           publisher  cycle  \\\n",
       "0  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2016   \n",
       "1  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2016   \n",
       "2  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2016   \n",
       "3  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2016   \n",
       "4  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2016   \n",
       "5  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2016   \n",
       "6  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2016   \n",
       "7  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2016   \n",
       "8  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2016   \n",
       "9  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2016   \n",
       "\n",
       "                  ocid                       id                      date  \\\n",
       "0  ocds-07smqs-1003123  SHCP-1003123-2018-11-12 2016-02-19 01:49:22+00:00   \n",
       "1  ocds-07smqs-1003803  SHCP-1003803-2018-11-12 2016-02-19 01:09:18+00:00   \n",
       "2  ocds-07smqs-1009245  SHCP-1009245-2018-11-12 2016-02-26 05:33:08+00:00   \n",
       "3  ocds-07smqs-1012355  SHCP-1012355-2018-11-12 2016-03-02 01:58:39+00:00   \n",
       "4  ocds-07smqs-1017118  SHCP-1017118-2018-11-12 2016-03-08 07:17:10+00:00   \n",
       "5  ocds-07smqs-1025654  SHCP-1025654-2018-11-12 2016-03-18 06:40:28+00:00   \n",
       "6  ocds-07smqs-1025723  SHCP-1025723-2018-11-12 2016-03-18 08:45:03+00:00   \n",
       "7  ocds-07smqs-1026720  SHCP-1026720-2018-11-12 2016-03-22 12:54:35+00:00   \n",
       "8  ocds-07smqs-1028067  SHCP-1028067-2018-11-12 2016-03-28 11:22:52+00:00   \n",
       "9  ocds-07smqs-1028116  SHCP-1028116-2018-11-12 2016-03-28 11:48:31+00:00   \n",
       "\n",
       "               tag initiationType  \\\n",
       "0  [tender, award]         tender   \n",
       "1  [tender, award]         tender   \n",
       "2  [tender, award]         tender   \n",
       "3  [tender, award]         tender   \n",
       "4  [tender, award]         tender   \n",
       "5  [tender, award]         tender   \n",
       "6  [tender, award]         tender   \n",
       "7  [tender, award]         tender   \n",
       "8  [tender, award]         tender   \n",
       "9  [tender, award]         tender   \n",
       "\n",
       "                                             parties  \\\n",
       "0  [{'name': 'Procuraduría General de la Repúblic...   \n",
       "1  [{'name': 'Servicio de Administración Tributar...   \n",
       "2  [{'name': 'Secretaría del Trabajo y Previsión ...   \n",
       "3  [{'name': 'Comisión Nacional para el Uso Efici...   \n",
       "4  [{'name': 'Instituto Nacional de Antropología ...   \n",
       "5  [{'name': 'Instituto de Administración y Avalú...   \n",
       "6  [{'name': 'Instituto de Administración y Avalú...   \n",
       "7  [{'name': 'Comisión Nacional para el Uso Efici...   \n",
       "8  [{'name': 'Instituto de Administración y Avalú...   \n",
       "9  [{'name': 'Instituto de Administración y Avalú...   \n",
       "\n",
       "                                               buyer  \\\n",
       "0  {'name': 'Procuraduría General de la República...   \n",
       "1  {'name': 'Servicio de Administración Tributari...   \n",
       "2  {'name': 'Secretaría del Trabajo y Previsión S...   \n",
       "3  {'name': 'Comisión Nacional para el Uso Eficie...   \n",
       "4  {'name': 'Instituto Nacional de Antropología e...   \n",
       "5  {'name': 'Instituto de Administración y Avalúo...   \n",
       "6  {'name': 'Instituto de Administración y Avalúo...   \n",
       "7  {'name': 'Comisión Nacional para el Uso Eficie...   \n",
       "8  {'name': 'Instituto de Administración y Avalúo...   \n",
       "9  {'name': 'Instituto de Administración y Avalúo...   \n",
       "\n",
       "                                              tender language  \\\n",
       "0  {'id': '1003123', 'title': 'MANTENIMIENTO PREV...       es   \n",
       "1  {'id': '1003803', 'title': 'SERVICIO INTEGRAL ...       es   \n",
       "2  {'id': '1009245', 'title': 'CONTRATACION DE UN...       es   \n",
       "3  {'id': '1012355', 'title': 'SERVICIO INTEGRAL ...       es   \n",
       "4  {'id': '1017118', 'title': 'Servicios de Jardi...       es   \n",
       "5  {'id': '1025654', 'title': 'PLATAFORMA DEL SIS...       es   \n",
       "6  {'id': '1025723', 'title': 'TRANSPORTACIÓN AÉR...       es   \n",
       "7  {'id': '1026720', 'title': 'SERVICIO DE PENSIO...       es   \n",
       "8  {'id': '1028067', 'title': 'SERVICIO DE JARDIN...       es   \n",
       "9  {'id': '1028116', 'title': 'EFECTIVALE, S. DE ...       es   \n",
       "\n",
       "                                              awards  \\\n",
       "0  [{'id': '1377619', 'title': 'MANTENIMIENTO PRE...   \n",
       "1  [{'id': '1004248', 'title': 'SERVICIO INTEGRAL...   \n",
       "2  [{'id': '1011086', 'title': 'PÓLIZA DE SEGURO ...   \n",
       "3  [{'id': '1013973', 'title': 'SERVICIO DE ASESO...   \n",
       "4  [{'id': '1172348', 'title': 'SERVICIOS DE JARD...   \n",
       "5  [{'id': '1030997', 'title': 'PLATAFORMA DEL SI...   \n",
       "6  [{'id': '1031035', 'title': 'TRANSPORTACIÓN AÉ...   \n",
       "7  [{'id': '1066219', 'title': 'ALMACENAJE DE BIE...   \n",
       "8  [{'id': '1033955', 'title': 'SERVICIO DE JARDI...   \n",
       "9  [{'id': '1034007', 'title': 'EFECTIVALE, S. DE...   \n",
       "\n",
       "                                           contracts  \n",
       "0  [{'id': 1377619, 'awardID': '1377619', 'title'...  \n",
       "1  [{'id': 1004248, 'awardID': '1004248', 'title'...  \n",
       "2  [{'id': 1011086, 'awardID': '1011086', 'title'...  \n",
       "3  [{'id': 1013973, 'awardID': '1013973', 'title'...  \n",
       "4  [{'id': 1172348, 'awardID': '1172348', 'title'...  \n",
       "5  [{'id': 1030997, 'awardID': '1030997', 'title'...  \n",
       "6  [{'id': 1031035, 'awardID': '1031035', 'title'...  \n",
       "7  [{'id': 1066219, 'awardID': '1066219', 'title'...  \n",
       "8  [{'id': 1033955, 'awardID': '1033955', 'title'...  \n",
       "9  [{'id': 1034007, 'awardID': '1034007', 'title'...  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sucio.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Seeing what the situation is whith our NaN values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sucio.isnull().values.any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17524"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sucio.isnull().sum().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['_id', 'publisher', 'cycle', 'ocid', 'id', 'date', 'tag',\n",
       "       'initiationType', 'parties', 'buyer', 'tender', 'language', 'awards',\n",
       "       'contracts'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sucio.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([False, False, False, ..., False, False, False])"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sucio['_id'].isnull().values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Here we can see where the NaN values are situated"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "La columna (_id) tiene nan values: False\n",
      "La columna (publisher) tiene nan values: False\n",
      "La columna (cycle) tiene nan values: False\n",
      "La columna (ocid) tiene nan values: False\n",
      "La columna (id) tiene nan values: False\n",
      "La columna (date) tiene nan values: False\n",
      "La columna (tag) tiene nan values: False\n",
      "La columna (initiationType) tiene nan values: False\n",
      "La columna (parties) tiene nan values: False\n",
      "La columna (buyer) tiene nan values: False\n",
      "La columna (tender) tiene nan values: False\n",
      "La columna (language) tiene nan values: False\n",
      "La columna (awards) tiene nan values: True\n",
      "La columna (contracts) tiene nan values: True\n"
     ]
    }
   ],
   "source": [
    "for column in df_sucio.columns:\n",
    "    print(f\"La columna ({column}) tiene nan values: {df_sucio[column].isnull().values.any()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "La columna (awards) tiene nan values: 8762\n",
      "La columna (contracts) tiene nan values: 8762\n"
     ]
    }
   ],
   "source": [
    "for column in [\"awards\",'contracts']:\n",
    "    print(f\"La columna ({column}) tiene nan values: {df_sucio[column].isnull().sum()}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>_id</th>\n",
       "      <th>publisher</th>\n",
       "      <th>cycle</th>\n",
       "      <th>ocid</th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>tag</th>\n",
       "      <th>initiationType</th>\n",
       "      <th>parties</th>\n",
       "      <th>buyer</th>\n",
       "      <th>tender</th>\n",
       "      <th>language</th>\n",
       "      <th>awards</th>\n",
       "      <th>contracts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>59386</th>\n",
       "      <td>{'$oid': '5e2a3eec2bbf1f50330895d5'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1237609</td>\n",
       "      <td>SHCP-1237609-2018-11-13</td>\n",
       "      <td>2016-12-16 03:05:15+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Comisión Nacional del Agua', 'id': ...</td>\n",
       "      <td>{'name': 'Comisión Nacional del Agua', 'id': '...</td>\n",
       "      <td>{'id': '1237609', 'title': 'Proyecto ejecutivo...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59387</th>\n",
       "      <td>{'$oid': '5e2a3eec18822ab5a75220c0'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2017</td>\n",
       "      <td>ocds-07smqs-1238108</td>\n",
       "      <td>SHCP-1238108-2018-11-13</td>\n",
       "      <td>2017-01-09 09:37:54+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Secretaría de Comunicaciones y Tran...</td>\n",
       "      <td>{'name': 'Secretaría de Comunicaciones y Trans...</td>\n",
       "      <td>{'id': '1238108', 'title': 'IO-009000970-E1-20...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59388</th>\n",
       "      <td>{'$oid': '5e2a3eecabe212bfe24b84ee'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2017</td>\n",
       "      <td>ocds-07smqs-1238119</td>\n",
       "      <td>SHCP-1238119-2018-11-13</td>\n",
       "      <td>2017-01-09 09:43:45+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Secretaría de Comunicaciones y Tran...</td>\n",
       "      <td>{'name': 'Secretaría de Comunicaciones y Trans...</td>\n",
       "      <td>{'id': '1238119', 'title': 'IO-009000970-E2-20...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59389</th>\n",
       "      <td>{'$oid': '5e2a3eec0cc8eb032882f31c'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2017</td>\n",
       "      <td>ocds-07smqs-1238285</td>\n",
       "      <td>SHCP-1238285-2018-11-13</td>\n",
       "      <td>2017-01-09 09:45:03+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Secretaría de Comunicaciones y Tran...</td>\n",
       "      <td>{'name': 'Secretaría de Comunicaciones y Trans...</td>\n",
       "      <td>{'id': '1238285', 'title': 'IO-009000970-E3-20...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59390</th>\n",
       "      <td>{'$oid': '5e2a3eec9108970b721cbad8'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2017</td>\n",
       "      <td>ocds-07smqs-1238286</td>\n",
       "      <td>SHCP-1238286-2018-11-13</td>\n",
       "      <td>2017-01-09 09:45:49+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Secretaría de Comunicaciones y Tran...</td>\n",
       "      <td>{'name': 'Secretaría de Comunicaciones y Trans...</td>\n",
       "      <td>{'id': '1238286', 'title': 'IO-009000970-E4-20...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292284</th>\n",
       "      <td>{'$oid': '5e2a5afdb544edd3a56b61e5'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821401</td>\n",
       "      <td>SHCP-1821401-2018-11-13</td>\n",
       "      <td>2018-11-12 05:05:02+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821401', 'title': 'Suministro y Aplic...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292285</th>\n",
       "      <td>{'$oid': '5e2a5afd1ec2f64ba6d4f3d0'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821466</td>\n",
       "      <td>SHCP-1821466-2018-11-13</td>\n",
       "      <td>2018-11-12 05:06:39+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821466', 'title': 'IA-050GYR041-E156-...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292286</th>\n",
       "      <td>{'$oid': '5e2a5afd89eb30723bddafc7'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821529</td>\n",
       "      <td>SHCP-1821529-2018-11-13</td>\n",
       "      <td>2018-11-12 02:35:26+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821529', 'title': 'IA-050GYR008-E146-...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292287</th>\n",
       "      <td>{'$oid': '5e2a5afd75f61fad75ab12e3'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821698</td>\n",
       "      <td>SHCP-1821698-2018-11-13</td>\n",
       "      <td>2018-11-12 03:17:48+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821698', 'title': 'IA-050GYR012-E170-...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292288</th>\n",
       "      <td>{'$oid': '5e2a5afd23c0b9ab3c372fda'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821703</td>\n",
       "      <td>SHCP-1821703-2018-11-13</td>\n",
       "      <td>2018-11-12 02:49:57+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821703', 'title': 'IA-050GYR036-E126-...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8762 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         _id  \\\n",
       "59386   {'$oid': '5e2a3eec2bbf1f50330895d5'}   \n",
       "59387   {'$oid': '5e2a3eec18822ab5a75220c0'}   \n",
       "59388   {'$oid': '5e2a3eecabe212bfe24b84ee'}   \n",
       "59389   {'$oid': '5e2a3eec0cc8eb032882f31c'}   \n",
       "59390   {'$oid': '5e2a3eec9108970b721cbad8'}   \n",
       "...                                      ...   \n",
       "292284  {'$oid': '5e2a5afdb544edd3a56b61e5'}   \n",
       "292285  {'$oid': '5e2a5afd1ec2f64ba6d4f3d0'}   \n",
       "292286  {'$oid': '5e2a5afd89eb30723bddafc7'}   \n",
       "292287  {'$oid': '5e2a5afd75f61fad75ab12e3'}   \n",
       "292288  {'$oid': '5e2a5afd23c0b9ab3c372fda'}   \n",
       "\n",
       "                                                publisher  cycle  \\\n",
       "59386   {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2016   \n",
       "59387   {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2017   \n",
       "59388   {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2017   \n",
       "59389   {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2017   \n",
       "59390   {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2017   \n",
       "...                                                   ...    ...   \n",
       "292284  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2018   \n",
       "292285  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2018   \n",
       "292286  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2018   \n",
       "292287  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2018   \n",
       "292288  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2018   \n",
       "\n",
       "                       ocid                       id  \\\n",
       "59386   ocds-07smqs-1237609  SHCP-1237609-2018-11-13   \n",
       "59387   ocds-07smqs-1238108  SHCP-1238108-2018-11-13   \n",
       "59388   ocds-07smqs-1238119  SHCP-1238119-2018-11-13   \n",
       "59389   ocds-07smqs-1238285  SHCP-1238285-2018-11-13   \n",
       "59390   ocds-07smqs-1238286  SHCP-1238286-2018-11-13   \n",
       "...                     ...                      ...   \n",
       "292284  ocds-07smqs-1821401  SHCP-1821401-2018-11-13   \n",
       "292285  ocds-07smqs-1821466  SHCP-1821466-2018-11-13   \n",
       "292286  ocds-07smqs-1821529  SHCP-1821529-2018-11-13   \n",
       "292287  ocds-07smqs-1821698  SHCP-1821698-2018-11-13   \n",
       "292288  ocds-07smqs-1821703  SHCP-1821703-2018-11-13   \n",
       "\n",
       "                            date              tag initiationType  \\\n",
       "59386  2016-12-16 03:05:15+00:00  [tender, award]         tender   \n",
       "59387  2017-01-09 09:37:54+00:00  [tender, award]         tender   \n",
       "59388  2017-01-09 09:43:45+00:00  [tender, award]         tender   \n",
       "59389  2017-01-09 09:45:03+00:00  [tender, award]         tender   \n",
       "59390  2017-01-09 09:45:49+00:00  [tender, award]         tender   \n",
       "...                          ...              ...            ...   \n",
       "292284 2018-11-12 05:05:02+00:00  [tender, award]         tender   \n",
       "292285 2018-11-12 05:06:39+00:00  [tender, award]         tender   \n",
       "292286 2018-11-12 02:35:26+00:00  [tender, award]         tender   \n",
       "292287 2018-11-12 03:17:48+00:00  [tender, award]         tender   \n",
       "292288 2018-11-12 02:49:57+00:00  [tender, award]         tender   \n",
       "\n",
       "                                                  parties  \\\n",
       "59386   [{'name': 'Comisión Nacional del Agua', 'id': ...   \n",
       "59387   [{'name': 'Secretaría de Comunicaciones y Tran...   \n",
       "59388   [{'name': 'Secretaría de Comunicaciones y Tran...   \n",
       "59389   [{'name': 'Secretaría de Comunicaciones y Tran...   \n",
       "59390   [{'name': 'Secretaría de Comunicaciones y Tran...   \n",
       "...                                                   ...   \n",
       "292284  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292285  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292286  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292287  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292288  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "\n",
       "                                                    buyer  \\\n",
       "59386   {'name': 'Comisión Nacional del Agua', 'id': '...   \n",
       "59387   {'name': 'Secretaría de Comunicaciones y Trans...   \n",
       "59388   {'name': 'Secretaría de Comunicaciones y Trans...   \n",
       "59389   {'name': 'Secretaría de Comunicaciones y Trans...   \n",
       "59390   {'name': 'Secretaría de Comunicaciones y Trans...   \n",
       "...                                                   ...   \n",
       "292284  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292285  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292286  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292287  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292288  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "\n",
       "                                                   tender language awards  \\\n",
       "59386   {'id': '1237609', 'title': 'Proyecto ejecutivo...       es    NaN   \n",
       "59387   {'id': '1238108', 'title': 'IO-009000970-E1-20...       es    NaN   \n",
       "59388   {'id': '1238119', 'title': 'IO-009000970-E2-20...       es    NaN   \n",
       "59389   {'id': '1238285', 'title': 'IO-009000970-E3-20...       es    NaN   \n",
       "59390   {'id': '1238286', 'title': 'IO-009000970-E4-20...       es    NaN   \n",
       "...                                                   ...      ...    ...   \n",
       "292284  {'id': '1821401', 'title': 'Suministro y Aplic...       es    NaN   \n",
       "292285  {'id': '1821466', 'title': 'IA-050GYR041-E156-...       es    NaN   \n",
       "292286  {'id': '1821529', 'title': 'IA-050GYR008-E146-...       es    NaN   \n",
       "292287  {'id': '1821698', 'title': 'IA-050GYR012-E170-...       es    NaN   \n",
       "292288  {'id': '1821703', 'title': 'IA-050GYR036-E126-...       es    NaN   \n",
       "\n",
       "       contracts  \n",
       "59386        NaN  \n",
       "59387        NaN  \n",
       "59388        NaN  \n",
       "59389        NaN  \n",
       "59390        NaN  \n",
       "...          ...  \n",
       "292284       NaN  \n",
       "292285       NaN  \n",
       "292286       NaN  \n",
       "292287       NaN  \n",
       "292288       NaN  \n",
       "\n",
       "[8762 rows x 14 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "is_NaN = df_sucio.isnull()\n",
    "row_has_NaN = is_NaN.any(axis=1)\n",
    "rows_with_NaN = df_sucio[row_has_NaN]\n",
    "rows_with_NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8762, 14)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows_with_NaN.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Off of this analisys we can understand that our missing values in both columns are always situated in the same rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Let's check  how the column publisher is formed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "publisher = df_sucio[\"publisher\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'uid': '6716',\n",
       " 'name': 'SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO',\n",
       " 'uri': 'http://www.gob.mx/shcp'}"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "publisher.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'6716'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "publisher.iloc[0][\"uid\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now we can see that the publisher column is just a series of json and each one has three entries.\n",
    "### We will separate each of this values in new columns so that the data is more accessible"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "publisher = pd.json_normalize(df_sucio['publisher'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>uid</th>\n",
       "      <th>name</th>\n",
       "      <th>uri</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292284</th>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292285</th>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292286</th>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292287</th>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292288</th>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>292289 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         uid                                      name                     uri\n",
       "0       6716  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp\n",
       "1       6716  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp\n",
       "2       6716  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp\n",
       "3       6716  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp\n",
       "4       6716  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp\n",
       "...      ...                                       ...                     ...\n",
       "292284  6716  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp\n",
       "292285  6716  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp\n",
       "292286  6716  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp\n",
       "292287  6716  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp\n",
       "292288  6716  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp\n",
       "\n",
       "[292289 rows x 3 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "publisher"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sucio[\"publisherUid\"] = publisher[\"uid\"]\n",
    "df_sucio[\"publisherName\"] = publisher[\"name\"]\n",
    "df_sucio[\"publisherUri\"] = publisher[\"uri\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>_id</th>\n",
       "      <th>publisher</th>\n",
       "      <th>cycle</th>\n",
       "      <th>ocid</th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>tag</th>\n",
       "      <th>initiationType</th>\n",
       "      <th>parties</th>\n",
       "      <th>buyer</th>\n",
       "      <th>tender</th>\n",
       "      <th>language</th>\n",
       "      <th>awards</th>\n",
       "      <th>contracts</th>\n",
       "      <th>publisherUid</th>\n",
       "      <th>publisherName</th>\n",
       "      <th>publisherUri</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'$oid': '5e2a3797575b631d3c62539e'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1003123</td>\n",
       "      <td>SHCP-1003123-2018-11-12</td>\n",
       "      <td>2016-02-19 01:49:22+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Procuraduría General de la Repúblic...</td>\n",
       "      <td>{'name': 'Procuraduría General de la República...</td>\n",
       "      <td>{'id': '1003123', 'title': 'MANTENIMIENTO PREV...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1377619', 'title': 'MANTENIMIENTO PRE...</td>\n",
       "      <td>[{'id': 1377619, 'awardID': '1377619', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'$oid': '5e2a3798325093834fd90805'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1003803</td>\n",
       "      <td>SHCP-1003803-2018-11-12</td>\n",
       "      <td>2016-02-19 01:09:18+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Servicio de Administración Tributar...</td>\n",
       "      <td>{'name': 'Servicio de Administración Tributari...</td>\n",
       "      <td>{'id': '1003803', 'title': 'SERVICIO INTEGRAL ...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1004248', 'title': 'SERVICIO INTEGRAL...</td>\n",
       "      <td>[{'id': 1004248, 'awardID': '1004248', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'$oid': '5e2a379837be1c3fcb93dd99'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1009245</td>\n",
       "      <td>SHCP-1009245-2018-11-12</td>\n",
       "      <td>2016-02-26 05:33:08+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Secretaría del Trabajo y Previsión ...</td>\n",
       "      <td>{'name': 'Secretaría del Trabajo y Previsión S...</td>\n",
       "      <td>{'id': '1009245', 'title': 'CONTRATACION DE UN...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1011086', 'title': 'PÓLIZA DE SEGURO ...</td>\n",
       "      <td>[{'id': 1011086, 'awardID': '1011086', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'$oid': '5e2a3798d0ac91b80a73a1af'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1012355</td>\n",
       "      <td>SHCP-1012355-2018-11-12</td>\n",
       "      <td>2016-03-02 01:58:39+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Comisión Nacional para el Uso Efici...</td>\n",
       "      <td>{'name': 'Comisión Nacional para el Uso Eficie...</td>\n",
       "      <td>{'id': '1012355', 'title': 'SERVICIO INTEGRAL ...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1013973', 'title': 'SERVICIO DE ASESO...</td>\n",
       "      <td>[{'id': 1013973, 'awardID': '1013973', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'$oid': '5e2a3798cb04247fb1dcbd1b'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1017118</td>\n",
       "      <td>SHCP-1017118-2018-11-12</td>\n",
       "      <td>2016-03-08 07:17:10+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Nacional de Antropología ...</td>\n",
       "      <td>{'name': 'Instituto Nacional de Antropología e...</td>\n",
       "      <td>{'id': '1017118', 'title': 'Servicios de Jardi...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1172348', 'title': 'SERVICIOS DE JARD...</td>\n",
       "      <td>[{'id': 1172348, 'awardID': '1172348', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292284</th>\n",
       "      <td>{'$oid': '5e2a5afdb544edd3a56b61e5'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821401</td>\n",
       "      <td>SHCP-1821401-2018-11-13</td>\n",
       "      <td>2018-11-12 05:05:02+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821401', 'title': 'Suministro y Aplic...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292285</th>\n",
       "      <td>{'$oid': '5e2a5afd1ec2f64ba6d4f3d0'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821466</td>\n",
       "      <td>SHCP-1821466-2018-11-13</td>\n",
       "      <td>2018-11-12 05:06:39+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821466', 'title': 'IA-050GYR041-E156-...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292286</th>\n",
       "      <td>{'$oid': '5e2a5afd89eb30723bddafc7'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821529</td>\n",
       "      <td>SHCP-1821529-2018-11-13</td>\n",
       "      <td>2018-11-12 02:35:26+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821529', 'title': 'IA-050GYR008-E146-...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292287</th>\n",
       "      <td>{'$oid': '5e2a5afd75f61fad75ab12e3'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821698</td>\n",
       "      <td>SHCP-1821698-2018-11-13</td>\n",
       "      <td>2018-11-12 03:17:48+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821698', 'title': 'IA-050GYR012-E170-...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292288</th>\n",
       "      <td>{'$oid': '5e2a5afd23c0b9ab3c372fda'}</td>\n",
       "      <td>{'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821703</td>\n",
       "      <td>SHCP-1821703-2018-11-13</td>\n",
       "      <td>2018-11-12 02:49:57+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821703', 'title': 'IA-050GYR036-E126-...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>292289 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         _id  \\\n",
       "0       {'$oid': '5e2a3797575b631d3c62539e'}   \n",
       "1       {'$oid': '5e2a3798325093834fd90805'}   \n",
       "2       {'$oid': '5e2a379837be1c3fcb93dd99'}   \n",
       "3       {'$oid': '5e2a3798d0ac91b80a73a1af'}   \n",
       "4       {'$oid': '5e2a3798cb04247fb1dcbd1b'}   \n",
       "...                                      ...   \n",
       "292284  {'$oid': '5e2a5afdb544edd3a56b61e5'}   \n",
       "292285  {'$oid': '5e2a5afd1ec2f64ba6d4f3d0'}   \n",
       "292286  {'$oid': '5e2a5afd89eb30723bddafc7'}   \n",
       "292287  {'$oid': '5e2a5afd75f61fad75ab12e3'}   \n",
       "292288  {'$oid': '5e2a5afd23c0b9ab3c372fda'}   \n",
       "\n",
       "                                                publisher  cycle  \\\n",
       "0       {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2016   \n",
       "1       {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2016   \n",
       "2       {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2016   \n",
       "3       {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2016   \n",
       "4       {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2016   \n",
       "...                                                   ...    ...   \n",
       "292284  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2018   \n",
       "292285  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2018   \n",
       "292286  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2018   \n",
       "292287  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2018   \n",
       "292288  {'uid': '6716', 'name': 'SECRETARÍA DE HACIEND...   2018   \n",
       "\n",
       "                       ocid                       id  \\\n",
       "0       ocds-07smqs-1003123  SHCP-1003123-2018-11-12   \n",
       "1       ocds-07smqs-1003803  SHCP-1003803-2018-11-12   \n",
       "2       ocds-07smqs-1009245  SHCP-1009245-2018-11-12   \n",
       "3       ocds-07smqs-1012355  SHCP-1012355-2018-11-12   \n",
       "4       ocds-07smqs-1017118  SHCP-1017118-2018-11-12   \n",
       "...                     ...                      ...   \n",
       "292284  ocds-07smqs-1821401  SHCP-1821401-2018-11-13   \n",
       "292285  ocds-07smqs-1821466  SHCP-1821466-2018-11-13   \n",
       "292286  ocds-07smqs-1821529  SHCP-1821529-2018-11-13   \n",
       "292287  ocds-07smqs-1821698  SHCP-1821698-2018-11-13   \n",
       "292288  ocds-07smqs-1821703  SHCP-1821703-2018-11-13   \n",
       "\n",
       "                            date              tag initiationType  \\\n",
       "0      2016-02-19 01:49:22+00:00  [tender, award]         tender   \n",
       "1      2016-02-19 01:09:18+00:00  [tender, award]         tender   \n",
       "2      2016-02-26 05:33:08+00:00  [tender, award]         tender   \n",
       "3      2016-03-02 01:58:39+00:00  [tender, award]         tender   \n",
       "4      2016-03-08 07:17:10+00:00  [tender, award]         tender   \n",
       "...                          ...              ...            ...   \n",
       "292284 2018-11-12 05:05:02+00:00  [tender, award]         tender   \n",
       "292285 2018-11-12 05:06:39+00:00  [tender, award]         tender   \n",
       "292286 2018-11-12 02:35:26+00:00  [tender, award]         tender   \n",
       "292287 2018-11-12 03:17:48+00:00  [tender, award]         tender   \n",
       "292288 2018-11-12 02:49:57+00:00  [tender, award]         tender   \n",
       "\n",
       "                                                  parties  \\\n",
       "0       [{'name': 'Procuraduría General de la Repúblic...   \n",
       "1       [{'name': 'Servicio de Administración Tributar...   \n",
       "2       [{'name': 'Secretaría del Trabajo y Previsión ...   \n",
       "3       [{'name': 'Comisión Nacional para el Uso Efici...   \n",
       "4       [{'name': 'Instituto Nacional de Antropología ...   \n",
       "...                                                   ...   \n",
       "292284  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292285  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292286  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292287  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292288  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "\n",
       "                                                    buyer  \\\n",
       "0       {'name': 'Procuraduría General de la República...   \n",
       "1       {'name': 'Servicio de Administración Tributari...   \n",
       "2       {'name': 'Secretaría del Trabajo y Previsión S...   \n",
       "3       {'name': 'Comisión Nacional para el Uso Eficie...   \n",
       "4       {'name': 'Instituto Nacional de Antropología e...   \n",
       "...                                                   ...   \n",
       "292284  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292285  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292286  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292287  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292288  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "\n",
       "                                                   tender language  \\\n",
       "0       {'id': '1003123', 'title': 'MANTENIMIENTO PREV...       es   \n",
       "1       {'id': '1003803', 'title': 'SERVICIO INTEGRAL ...       es   \n",
       "2       {'id': '1009245', 'title': 'CONTRATACION DE UN...       es   \n",
       "3       {'id': '1012355', 'title': 'SERVICIO INTEGRAL ...       es   \n",
       "4       {'id': '1017118', 'title': 'Servicios de Jardi...       es   \n",
       "...                                                   ...      ...   \n",
       "292284  {'id': '1821401', 'title': 'Suministro y Aplic...       es   \n",
       "292285  {'id': '1821466', 'title': 'IA-050GYR041-E156-...       es   \n",
       "292286  {'id': '1821529', 'title': 'IA-050GYR008-E146-...       es   \n",
       "292287  {'id': '1821698', 'title': 'IA-050GYR012-E170-...       es   \n",
       "292288  {'id': '1821703', 'title': 'IA-050GYR036-E126-...       es   \n",
       "\n",
       "                                                   awards  \\\n",
       "0       [{'id': '1377619', 'title': 'MANTENIMIENTO PRE...   \n",
       "1       [{'id': '1004248', 'title': 'SERVICIO INTEGRAL...   \n",
       "2       [{'id': '1011086', 'title': 'PÓLIZA DE SEGURO ...   \n",
       "3       [{'id': '1013973', 'title': 'SERVICIO DE ASESO...   \n",
       "4       [{'id': '1172348', 'title': 'SERVICIOS DE JARD...   \n",
       "...                                                   ...   \n",
       "292284                                                NaN   \n",
       "292285                                                NaN   \n",
       "292286                                                NaN   \n",
       "292287                                                NaN   \n",
       "292288                                                NaN   \n",
       "\n",
       "                                                contracts publisherUid  \\\n",
       "0       [{'id': 1377619, 'awardID': '1377619', 'title'...         6716   \n",
       "1       [{'id': 1004248, 'awardID': '1004248', 'title'...         6716   \n",
       "2       [{'id': 1011086, 'awardID': '1011086', 'title'...         6716   \n",
       "3       [{'id': 1013973, 'awardID': '1013973', 'title'...         6716   \n",
       "4       [{'id': 1172348, 'awardID': '1172348', 'title'...         6716   \n",
       "...                                                   ...          ...   \n",
       "292284                                                NaN         6716   \n",
       "292285                                                NaN         6716   \n",
       "292286                                                NaN         6716   \n",
       "292287                                                NaN         6716   \n",
       "292288                                                NaN         6716   \n",
       "\n",
       "                                   publisherName            publisherUri  \n",
       "0       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp  \n",
       "1       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp  \n",
       "2       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp  \n",
       "3       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp  \n",
       "4       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp  \n",
       "...                                          ...                     ...  \n",
       "292284  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp  \n",
       "292285  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp  \n",
       "292286  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp  \n",
       "292287  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp  \n",
       "292288  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp  \n",
       "\n",
       "[292289 rows x 17 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sucio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique publisher uid: ['6716']\n",
      "Unique publisher name: ['SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO']\n",
      "Unique publisher uri: ['http://www.gob.mx/shcp']\n"
     ]
    }
   ],
   "source": [
    "print(f\"Unique publisher uid: {df_sucio.publisherUid.unique()}\")\n",
    "print(f\"Unique publisher name: {df_sucio.publisherName.unique()}\")\n",
    "print(f\"Unique publisher uri: {df_sucio.publisherUri.unique()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Look we learned that every single contract has the dame publisher"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# We will follow a very similar process with the other columns that need some love :)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ID \n",
    "# For whatever reason id contains what im guessing is the finalisation date\n",
    "# so let's extract it and add in to a new column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2018', '11', '12']"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sucio[\"id\"].values[0].split(\"-\")[2:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "finalisation_date = df_sucio[\"id\"].apply(lambda x: x.split(\"-\")[2:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "finalisation_date = finalisation_date.apply(lambda x: \"-\".join(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         2018-11-12\n",
       "1         2018-11-12\n",
       "2         2018-11-12\n",
       "3         2018-11-12\n",
       "4         2018-11-12\n",
       "             ...    \n",
       "292284    2018-11-13\n",
       "292285    2018-11-13\n",
       "292286    2018-11-13\n",
       "292287    2018-11-13\n",
       "292288    2018-11-13\n",
       "Name: id, Length: 292289, dtype: object"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "finalisation_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sucio[\"finalisation_date\"] = finalisation_date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## with this done we don't really need the date on the id column so let's get rid of it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "actual_id = df_sucio[\"id\"].apply(lambda x: x.split(\"-\")[1])\n",
    "institution_abbreviation = df_sucio[\"id\"].apply(lambda x: x.split(\"-\")[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         1003123\n",
       "1         1003803\n",
       "2         1009245\n",
       "3         1012355\n",
       "4         1017118\n",
       "           ...   \n",
       "292284    1821401\n",
       "292285    1821466\n",
       "292286    1821529\n",
       "292287    1821698\n",
       "292288    1821703\n",
       "Name: id, Length: 292289, dtype: object"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "actual_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         SHCP\n",
       "1         SHCP\n",
       "2         SHCP\n",
       "3         SHCP\n",
       "4         SHCP\n",
       "          ... \n",
       "292284    SHCP\n",
       "292285    SHCP\n",
       "292286    SHCP\n",
       "292287    SHCP\n",
       "292288    SHCP\n",
       "Name: id, Length: 292289, dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "institution_abbreviation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sucio[\"institution_abbreviation\"] = institution_abbreviation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sucio[\"id\"] = actual_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sucio = df_sucio.drop(columns = ['oid','publisher'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>_id</th>\n",
       "      <th>cycle</th>\n",
       "      <th>ocid</th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>tag</th>\n",
       "      <th>initiationType</th>\n",
       "      <th>parties</th>\n",
       "      <th>buyer</th>\n",
       "      <th>tender</th>\n",
       "      <th>language</th>\n",
       "      <th>awards</th>\n",
       "      <th>contracts</th>\n",
       "      <th>publisherUid</th>\n",
       "      <th>publisherName</th>\n",
       "      <th>publisherUri</th>\n",
       "      <th>finalisation_date</th>\n",
       "      <th>institution_abbreviation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'$oid': '5e2a3797575b631d3c62539e'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1003123</td>\n",
       "      <td>1003123</td>\n",
       "      <td>2016-02-19 01:49:22+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Procuraduría General de la Repúblic...</td>\n",
       "      <td>{'name': 'Procuraduría General de la República...</td>\n",
       "      <td>{'id': '1003123', 'title': 'MANTENIMIENTO PREV...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1377619', 'title': 'MANTENIMIENTO PRE...</td>\n",
       "      <td>[{'id': 1377619, 'awardID': '1377619', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'$oid': '5e2a3798325093834fd90805'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1003803</td>\n",
       "      <td>1003803</td>\n",
       "      <td>2016-02-19 01:09:18+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Servicio de Administración Tributar...</td>\n",
       "      <td>{'name': 'Servicio de Administración Tributari...</td>\n",
       "      <td>{'id': '1003803', 'title': 'SERVICIO INTEGRAL ...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1004248', 'title': 'SERVICIO INTEGRAL...</td>\n",
       "      <td>[{'id': 1004248, 'awardID': '1004248', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'$oid': '5e2a379837be1c3fcb93dd99'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1009245</td>\n",
       "      <td>1009245</td>\n",
       "      <td>2016-02-26 05:33:08+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Secretaría del Trabajo y Previsión ...</td>\n",
       "      <td>{'name': 'Secretaría del Trabajo y Previsión S...</td>\n",
       "      <td>{'id': '1009245', 'title': 'CONTRATACION DE UN...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1011086', 'title': 'PÓLIZA DE SEGURO ...</td>\n",
       "      <td>[{'id': 1011086, 'awardID': '1011086', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'$oid': '5e2a3798d0ac91b80a73a1af'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1012355</td>\n",
       "      <td>1012355</td>\n",
       "      <td>2016-03-02 01:58:39+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Comisión Nacional para el Uso Efici...</td>\n",
       "      <td>{'name': 'Comisión Nacional para el Uso Eficie...</td>\n",
       "      <td>{'id': '1012355', 'title': 'SERVICIO INTEGRAL ...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1013973', 'title': 'SERVICIO DE ASESO...</td>\n",
       "      <td>[{'id': 1013973, 'awardID': '1013973', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'$oid': '5e2a3798cb04247fb1dcbd1b'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1017118</td>\n",
       "      <td>1017118</td>\n",
       "      <td>2016-03-08 07:17:10+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Nacional de Antropología ...</td>\n",
       "      <td>{'name': 'Instituto Nacional de Antropología e...</td>\n",
       "      <td>{'id': '1017118', 'title': 'Servicios de Jardi...</td>\n",
       "      <td>es</td>\n",
       "      <td>[{'id': '1172348', 'title': 'SERVICIOS DE JARD...</td>\n",
       "      <td>[{'id': 1172348, 'awardID': '1172348', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292284</th>\n",
       "      <td>{'$oid': '5e2a5afdb544edd3a56b61e5'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821401</td>\n",
       "      <td>1821401</td>\n",
       "      <td>2018-11-12 05:05:02+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821401', 'title': 'Suministro y Aplic...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292285</th>\n",
       "      <td>{'$oid': '5e2a5afd1ec2f64ba6d4f3d0'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821466</td>\n",
       "      <td>1821466</td>\n",
       "      <td>2018-11-12 05:06:39+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821466', 'title': 'IA-050GYR041-E156-...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292286</th>\n",
       "      <td>{'$oid': '5e2a5afd89eb30723bddafc7'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821529</td>\n",
       "      <td>1821529</td>\n",
       "      <td>2018-11-12 02:35:26+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821529', 'title': 'IA-050GYR008-E146-...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292287</th>\n",
       "      <td>{'$oid': '5e2a5afd75f61fad75ab12e3'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821698</td>\n",
       "      <td>1821698</td>\n",
       "      <td>2018-11-12 03:17:48+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821698', 'title': 'IA-050GYR012-E170-...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292288</th>\n",
       "      <td>{'$oid': '5e2a5afd23c0b9ab3c372fda'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821703</td>\n",
       "      <td>1821703</td>\n",
       "      <td>2018-11-12 02:49:57+00:00</td>\n",
       "      <td>[tender, award]</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821703', 'title': 'IA-050GYR036-E126-...</td>\n",
       "      <td>es</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>292289 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         _id  cycle                 ocid  \\\n",
       "0       {'$oid': '5e2a3797575b631d3c62539e'}   2016  ocds-07smqs-1003123   \n",
       "1       {'$oid': '5e2a3798325093834fd90805'}   2016  ocds-07smqs-1003803   \n",
       "2       {'$oid': '5e2a379837be1c3fcb93dd99'}   2016  ocds-07smqs-1009245   \n",
       "3       {'$oid': '5e2a3798d0ac91b80a73a1af'}   2016  ocds-07smqs-1012355   \n",
       "4       {'$oid': '5e2a3798cb04247fb1dcbd1b'}   2016  ocds-07smqs-1017118   \n",
       "...                                      ...    ...                  ...   \n",
       "292284  {'$oid': '5e2a5afdb544edd3a56b61e5'}   2018  ocds-07smqs-1821401   \n",
       "292285  {'$oid': '5e2a5afd1ec2f64ba6d4f3d0'}   2018  ocds-07smqs-1821466   \n",
       "292286  {'$oid': '5e2a5afd89eb30723bddafc7'}   2018  ocds-07smqs-1821529   \n",
       "292287  {'$oid': '5e2a5afd75f61fad75ab12e3'}   2018  ocds-07smqs-1821698   \n",
       "292288  {'$oid': '5e2a5afd23c0b9ab3c372fda'}   2018  ocds-07smqs-1821703   \n",
       "\n",
       "             id                      date              tag initiationType  \\\n",
       "0       1003123 2016-02-19 01:49:22+00:00  [tender, award]         tender   \n",
       "1       1003803 2016-02-19 01:09:18+00:00  [tender, award]         tender   \n",
       "2       1009245 2016-02-26 05:33:08+00:00  [tender, award]         tender   \n",
       "3       1012355 2016-03-02 01:58:39+00:00  [tender, award]         tender   \n",
       "4       1017118 2016-03-08 07:17:10+00:00  [tender, award]         tender   \n",
       "...         ...                       ...              ...            ...   \n",
       "292284  1821401 2018-11-12 05:05:02+00:00  [tender, award]         tender   \n",
       "292285  1821466 2018-11-12 05:06:39+00:00  [tender, award]         tender   \n",
       "292286  1821529 2018-11-12 02:35:26+00:00  [tender, award]         tender   \n",
       "292287  1821698 2018-11-12 03:17:48+00:00  [tender, award]         tender   \n",
       "292288  1821703 2018-11-12 02:49:57+00:00  [tender, award]         tender   \n",
       "\n",
       "                                                  parties  \\\n",
       "0       [{'name': 'Procuraduría General de la Repúblic...   \n",
       "1       [{'name': 'Servicio de Administración Tributar...   \n",
       "2       [{'name': 'Secretaría del Trabajo y Previsión ...   \n",
       "3       [{'name': 'Comisión Nacional para el Uso Efici...   \n",
       "4       [{'name': 'Instituto Nacional de Antropología ...   \n",
       "...                                                   ...   \n",
       "292284  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292285  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292286  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292287  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292288  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "\n",
       "                                                    buyer  \\\n",
       "0       {'name': 'Procuraduría General de la República...   \n",
       "1       {'name': 'Servicio de Administración Tributari...   \n",
       "2       {'name': 'Secretaría del Trabajo y Previsión S...   \n",
       "3       {'name': 'Comisión Nacional para el Uso Eficie...   \n",
       "4       {'name': 'Instituto Nacional de Antropología e...   \n",
       "...                                                   ...   \n",
       "292284  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292285  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292286  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292287  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292288  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "\n",
       "                                                   tender language  \\\n",
       "0       {'id': '1003123', 'title': 'MANTENIMIENTO PREV...       es   \n",
       "1       {'id': '1003803', 'title': 'SERVICIO INTEGRAL ...       es   \n",
       "2       {'id': '1009245', 'title': 'CONTRATACION DE UN...       es   \n",
       "3       {'id': '1012355', 'title': 'SERVICIO INTEGRAL ...       es   \n",
       "4       {'id': '1017118', 'title': 'Servicios de Jardi...       es   \n",
       "...                                                   ...      ...   \n",
       "292284  {'id': '1821401', 'title': 'Suministro y Aplic...       es   \n",
       "292285  {'id': '1821466', 'title': 'IA-050GYR041-E156-...       es   \n",
       "292286  {'id': '1821529', 'title': 'IA-050GYR008-E146-...       es   \n",
       "292287  {'id': '1821698', 'title': 'IA-050GYR012-E170-...       es   \n",
       "292288  {'id': '1821703', 'title': 'IA-050GYR036-E126-...       es   \n",
       "\n",
       "                                                   awards  \\\n",
       "0       [{'id': '1377619', 'title': 'MANTENIMIENTO PRE...   \n",
       "1       [{'id': '1004248', 'title': 'SERVICIO INTEGRAL...   \n",
       "2       [{'id': '1011086', 'title': 'PÓLIZA DE SEGURO ...   \n",
       "3       [{'id': '1013973', 'title': 'SERVICIO DE ASESO...   \n",
       "4       [{'id': '1172348', 'title': 'SERVICIOS DE JARD...   \n",
       "...                                                   ...   \n",
       "292284                                                NaN   \n",
       "292285                                                NaN   \n",
       "292286                                                NaN   \n",
       "292287                                                NaN   \n",
       "292288                                                NaN   \n",
       "\n",
       "                                                contracts publisherUid  \\\n",
       "0       [{'id': 1377619, 'awardID': '1377619', 'title'...         6716   \n",
       "1       [{'id': 1004248, 'awardID': '1004248', 'title'...         6716   \n",
       "2       [{'id': 1011086, 'awardID': '1011086', 'title'...         6716   \n",
       "3       [{'id': 1013973, 'awardID': '1013973', 'title'...         6716   \n",
       "4       [{'id': 1172348, 'awardID': '1172348', 'title'...         6716   \n",
       "...                                                   ...          ...   \n",
       "292284                                                NaN         6716   \n",
       "292285                                                NaN         6716   \n",
       "292286                                                NaN         6716   \n",
       "292287                                                NaN         6716   \n",
       "292288                                                NaN         6716   \n",
       "\n",
       "                                   publisherName            publisherUri  \\\n",
       "0       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "1       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "2       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "3       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "4       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "...                                          ...                     ...   \n",
       "292284  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292285  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292286  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292287  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292288  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "\n",
       "       finalisation_date institution_abbreviation  \n",
       "0             2018-11-12                     SHCP  \n",
       "1             2018-11-12                     SHCP  \n",
       "2             2018-11-12                     SHCP  \n",
       "3             2018-11-12                     SHCP  \n",
       "4             2018-11-12                     SHCP  \n",
       "...                  ...                      ...  \n",
       "292284        2018-11-13                     SHCP  \n",
       "292285        2018-11-13                     SHCP  \n",
       "292286        2018-11-13                     SHCP  \n",
       "292287        2018-11-13                     SHCP  \n",
       "292288        2018-11-13                     SHCP  \n",
       "\n",
       "[292289 rows x 18 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sucio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tag"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "tag = df_sucio[\"tag\"].apply(lambda x: \"-\".join(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['tender-award'], dtype=object)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tag.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ok so now we know that we got the same thing in every single entry on the tag column and its also free of those square brackets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sucio[\"tag\"] = tag"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## I think it's time for us to figure out our date situation, for starters we know that at some point we'll need to use the year, trimester and month of each contract, it looks like cycle already takes care of the \"year\" issue but I don't trust it so we'll need to handle the three of them on our own"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2016, 2018, 2017, 2015], dtype=int64)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sucio.cycle.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "years = df_sucio['date'].apply(lambda x: x.year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2016, 2018, 2017, 2015], dtype=int64)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "years.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "months = df_sucio['date'].apply(lambda x: x.month)\n",
    "days =  df_sucio['date'].apply(lambda x: x.day)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 2,  3,  4,  5,  6,  7,  8,  9,  1, 10, 11, 12], dtype=int64)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "months.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sucio[\"expeditionDay\"] = days\n",
    "df_sucio[\"expeditionMonth\"] = months\n",
    "df_sucio[\"expeditionYear\"] = years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>_id</th>\n",
       "      <th>cycle</th>\n",
       "      <th>ocid</th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>tag</th>\n",
       "      <th>initiationType</th>\n",
       "      <th>parties</th>\n",
       "      <th>buyer</th>\n",
       "      <th>tender</th>\n",
       "      <th>...</th>\n",
       "      <th>awards</th>\n",
       "      <th>contracts</th>\n",
       "      <th>publisherUid</th>\n",
       "      <th>publisherName</th>\n",
       "      <th>publisherUri</th>\n",
       "      <th>finalisation_date</th>\n",
       "      <th>institution_abbreviation</th>\n",
       "      <th>expeditionDay</th>\n",
       "      <th>expeditionMonth</th>\n",
       "      <th>expeditionYear</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'$oid': '5e2a3797575b631d3c62539e'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1003123</td>\n",
       "      <td>1003123</td>\n",
       "      <td>2016-02-19 01:49:22+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Procuraduría General de la Repúblic...</td>\n",
       "      <td>{'name': 'Procuraduría General de la República...</td>\n",
       "      <td>{'id': '1003123', 'title': 'MANTENIMIENTO PREV...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'id': '1377619', 'title': 'MANTENIMIENTO PRE...</td>\n",
       "      <td>[{'id': 1377619, 'awardID': '1377619', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'$oid': '5e2a3798325093834fd90805'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1003803</td>\n",
       "      <td>1003803</td>\n",
       "      <td>2016-02-19 01:09:18+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Servicio de Administración Tributar...</td>\n",
       "      <td>{'name': 'Servicio de Administración Tributari...</td>\n",
       "      <td>{'id': '1003803', 'title': 'SERVICIO INTEGRAL ...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'id': '1004248', 'title': 'SERVICIO INTEGRAL...</td>\n",
       "      <td>[{'id': 1004248, 'awardID': '1004248', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'$oid': '5e2a379837be1c3fcb93dd99'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1009245</td>\n",
       "      <td>1009245</td>\n",
       "      <td>2016-02-26 05:33:08+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Secretaría del Trabajo y Previsión ...</td>\n",
       "      <td>{'name': 'Secretaría del Trabajo y Previsión S...</td>\n",
       "      <td>{'id': '1009245', 'title': 'CONTRATACION DE UN...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'id': '1011086', 'title': 'PÓLIZA DE SEGURO ...</td>\n",
       "      <td>[{'id': 1011086, 'awardID': '1011086', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>26</td>\n",
       "      <td>2</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'$oid': '5e2a3798d0ac91b80a73a1af'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1012355</td>\n",
       "      <td>1012355</td>\n",
       "      <td>2016-03-02 01:58:39+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Comisión Nacional para el Uso Efici...</td>\n",
       "      <td>{'name': 'Comisión Nacional para el Uso Eficie...</td>\n",
       "      <td>{'id': '1012355', 'title': 'SERVICIO INTEGRAL ...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'id': '1013973', 'title': 'SERVICIO DE ASESO...</td>\n",
       "      <td>[{'id': 1013973, 'awardID': '1013973', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'$oid': '5e2a3798cb04247fb1dcbd1b'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1017118</td>\n",
       "      <td>1017118</td>\n",
       "      <td>2016-03-08 07:17:10+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Nacional de Antropología ...</td>\n",
       "      <td>{'name': 'Instituto Nacional de Antropología e...</td>\n",
       "      <td>{'id': '1017118', 'title': 'Servicios de Jardi...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'id': '1172348', 'title': 'SERVICIOS DE JARD...</td>\n",
       "      <td>[{'id': 1172348, 'awardID': '1172348', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292284</th>\n",
       "      <td>{'$oid': '5e2a5afdb544edd3a56b61e5'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821401</td>\n",
       "      <td>1821401</td>\n",
       "      <td>2018-11-12 05:05:02+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821401', 'title': 'Suministro y Aplic...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292285</th>\n",
       "      <td>{'$oid': '5e2a5afd1ec2f64ba6d4f3d0'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821466</td>\n",
       "      <td>1821466</td>\n",
       "      <td>2018-11-12 05:06:39+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821466', 'title': 'IA-050GYR041-E156-...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292286</th>\n",
       "      <td>{'$oid': '5e2a5afd89eb30723bddafc7'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821529</td>\n",
       "      <td>1821529</td>\n",
       "      <td>2018-11-12 02:35:26+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821529', 'title': 'IA-050GYR008-E146-...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292287</th>\n",
       "      <td>{'$oid': '5e2a5afd75f61fad75ab12e3'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821698</td>\n",
       "      <td>1821698</td>\n",
       "      <td>2018-11-12 03:17:48+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821698', 'title': 'IA-050GYR012-E170-...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292288</th>\n",
       "      <td>{'$oid': '5e2a5afd23c0b9ab3c372fda'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821703</td>\n",
       "      <td>1821703</td>\n",
       "      <td>2018-11-12 02:49:57+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821703', 'title': 'IA-050GYR036-E126-...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>292289 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         _id  cycle                 ocid  \\\n",
       "0       {'$oid': '5e2a3797575b631d3c62539e'}   2016  ocds-07smqs-1003123   \n",
       "1       {'$oid': '5e2a3798325093834fd90805'}   2016  ocds-07smqs-1003803   \n",
       "2       {'$oid': '5e2a379837be1c3fcb93dd99'}   2016  ocds-07smqs-1009245   \n",
       "3       {'$oid': '5e2a3798d0ac91b80a73a1af'}   2016  ocds-07smqs-1012355   \n",
       "4       {'$oid': '5e2a3798cb04247fb1dcbd1b'}   2016  ocds-07smqs-1017118   \n",
       "...                                      ...    ...                  ...   \n",
       "292284  {'$oid': '5e2a5afdb544edd3a56b61e5'}   2018  ocds-07smqs-1821401   \n",
       "292285  {'$oid': '5e2a5afd1ec2f64ba6d4f3d0'}   2018  ocds-07smqs-1821466   \n",
       "292286  {'$oid': '5e2a5afd89eb30723bddafc7'}   2018  ocds-07smqs-1821529   \n",
       "292287  {'$oid': '5e2a5afd75f61fad75ab12e3'}   2018  ocds-07smqs-1821698   \n",
       "292288  {'$oid': '5e2a5afd23c0b9ab3c372fda'}   2018  ocds-07smqs-1821703   \n",
       "\n",
       "             id                      date           tag initiationType  \\\n",
       "0       1003123 2016-02-19 01:49:22+00:00  tender-award         tender   \n",
       "1       1003803 2016-02-19 01:09:18+00:00  tender-award         tender   \n",
       "2       1009245 2016-02-26 05:33:08+00:00  tender-award         tender   \n",
       "3       1012355 2016-03-02 01:58:39+00:00  tender-award         tender   \n",
       "4       1017118 2016-03-08 07:17:10+00:00  tender-award         tender   \n",
       "...         ...                       ...           ...            ...   \n",
       "292284  1821401 2018-11-12 05:05:02+00:00  tender-award         tender   \n",
       "292285  1821466 2018-11-12 05:06:39+00:00  tender-award         tender   \n",
       "292286  1821529 2018-11-12 02:35:26+00:00  tender-award         tender   \n",
       "292287  1821698 2018-11-12 03:17:48+00:00  tender-award         tender   \n",
       "292288  1821703 2018-11-12 02:49:57+00:00  tender-award         tender   \n",
       "\n",
       "                                                  parties  \\\n",
       "0       [{'name': 'Procuraduría General de la Repúblic...   \n",
       "1       [{'name': 'Servicio de Administración Tributar...   \n",
       "2       [{'name': 'Secretaría del Trabajo y Previsión ...   \n",
       "3       [{'name': 'Comisión Nacional para el Uso Efici...   \n",
       "4       [{'name': 'Instituto Nacional de Antropología ...   \n",
       "...                                                   ...   \n",
       "292284  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292285  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292286  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292287  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292288  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "\n",
       "                                                    buyer  \\\n",
       "0       {'name': 'Procuraduría General de la República...   \n",
       "1       {'name': 'Servicio de Administración Tributari...   \n",
       "2       {'name': 'Secretaría del Trabajo y Previsión S...   \n",
       "3       {'name': 'Comisión Nacional para el Uso Eficie...   \n",
       "4       {'name': 'Instituto Nacional de Antropología e...   \n",
       "...                                                   ...   \n",
       "292284  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292285  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292286  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292287  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292288  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "\n",
       "                                                   tender  ...  \\\n",
       "0       {'id': '1003123', 'title': 'MANTENIMIENTO PREV...  ...   \n",
       "1       {'id': '1003803', 'title': 'SERVICIO INTEGRAL ...  ...   \n",
       "2       {'id': '1009245', 'title': 'CONTRATACION DE UN...  ...   \n",
       "3       {'id': '1012355', 'title': 'SERVICIO INTEGRAL ...  ...   \n",
       "4       {'id': '1017118', 'title': 'Servicios de Jardi...  ...   \n",
       "...                                                   ...  ...   \n",
       "292284  {'id': '1821401', 'title': 'Suministro y Aplic...  ...   \n",
       "292285  {'id': '1821466', 'title': 'IA-050GYR041-E156-...  ...   \n",
       "292286  {'id': '1821529', 'title': 'IA-050GYR008-E146-...  ...   \n",
       "292287  {'id': '1821698', 'title': 'IA-050GYR012-E170-...  ...   \n",
       "292288  {'id': '1821703', 'title': 'IA-050GYR036-E126-...  ...   \n",
       "\n",
       "                                                   awards  \\\n",
       "0       [{'id': '1377619', 'title': 'MANTENIMIENTO PRE...   \n",
       "1       [{'id': '1004248', 'title': 'SERVICIO INTEGRAL...   \n",
       "2       [{'id': '1011086', 'title': 'PÓLIZA DE SEGURO ...   \n",
       "3       [{'id': '1013973', 'title': 'SERVICIO DE ASESO...   \n",
       "4       [{'id': '1172348', 'title': 'SERVICIOS DE JARD...   \n",
       "...                                                   ...   \n",
       "292284                                                NaN   \n",
       "292285                                                NaN   \n",
       "292286                                                NaN   \n",
       "292287                                                NaN   \n",
       "292288                                                NaN   \n",
       "\n",
       "                                                contracts publisherUid  \\\n",
       "0       [{'id': 1377619, 'awardID': '1377619', 'title'...         6716   \n",
       "1       [{'id': 1004248, 'awardID': '1004248', 'title'...         6716   \n",
       "2       [{'id': 1011086, 'awardID': '1011086', 'title'...         6716   \n",
       "3       [{'id': 1013973, 'awardID': '1013973', 'title'...         6716   \n",
       "4       [{'id': 1172348, 'awardID': '1172348', 'title'...         6716   \n",
       "...                                                   ...          ...   \n",
       "292284                                                NaN         6716   \n",
       "292285                                                NaN         6716   \n",
       "292286                                                NaN         6716   \n",
       "292287                                                NaN         6716   \n",
       "292288                                                NaN         6716   \n",
       "\n",
       "                                   publisherName            publisherUri  \\\n",
       "0       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "1       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "2       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "3       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "4       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "...                                          ...                     ...   \n",
       "292284  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292285  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292286  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292287  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292288  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "\n",
       "       finalisation_date institution_abbreviation expeditionDay  \\\n",
       "0             2018-11-12                     SHCP            19   \n",
       "1             2018-11-12                     SHCP            19   \n",
       "2             2018-11-12                     SHCP            26   \n",
       "3             2018-11-12                     SHCP             2   \n",
       "4             2018-11-12                     SHCP             8   \n",
       "...                  ...                      ...           ...   \n",
       "292284        2018-11-13                     SHCP            12   \n",
       "292285        2018-11-13                     SHCP            12   \n",
       "292286        2018-11-13                     SHCP            12   \n",
       "292287        2018-11-13                     SHCP            12   \n",
       "292288        2018-11-13                     SHCP            12   \n",
       "\n",
       "        expeditionMonth  expeditionYear  \n",
       "0                     2            2016  \n",
       "1                     2            2016  \n",
       "2                     2            2016  \n",
       "3                     3            2016  \n",
       "4                     3            2016  \n",
       "...                 ...             ...  \n",
       "292284               11            2018  \n",
       "292285               11            2018  \n",
       "292286               11            2018  \n",
       "292287               11            2018  \n",
       "292288               11            2018  \n",
       "\n",
       "[292289 rows x 21 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sucio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 2,  2,  2, ..., 11, 11, 11], dtype=int64)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sucio[\"expeditionMonth\"].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "trimester = np.where(df_sucio[\"expeditionMonth\"].values <= 3,1,\n",
    "                     np.where((df_sucio[\"expeditionMonth\"].values > 3) & (df_sucio[\"expeditionMonth\"].values <= 6),2,\n",
    "                             np.where((df_sucio[\"expeditionMonth\"].values > 6) & (df_sucio[\"expeditionMonth\"].values <= 9),3,4)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{1, 2, 3, 4}"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set_trimester=set(trimester)\n",
    "set_trimester"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>_id</th>\n",
       "      <th>cycle</th>\n",
       "      <th>ocid</th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>tag</th>\n",
       "      <th>initiationType</th>\n",
       "      <th>parties</th>\n",
       "      <th>buyer</th>\n",
       "      <th>tender</th>\n",
       "      <th>...</th>\n",
       "      <th>contracts</th>\n",
       "      <th>publisherUid</th>\n",
       "      <th>publisherName</th>\n",
       "      <th>publisherUri</th>\n",
       "      <th>finalisation_date</th>\n",
       "      <th>institution_abbreviation</th>\n",
       "      <th>expeditionDay</th>\n",
       "      <th>expeditionMonth</th>\n",
       "      <th>expeditionYear</th>\n",
       "      <th>expeditionTrimester</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'$oid': '5e2a3797575b631d3c62539e'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1003123</td>\n",
       "      <td>1003123</td>\n",
       "      <td>2016-02-19 01:49:22+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Procuraduría General de la Repúblic...</td>\n",
       "      <td>{'name': 'Procuraduría General de la República...</td>\n",
       "      <td>{'id': '1003123', 'title': 'MANTENIMIENTO PREV...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'id': 1377619, 'awardID': '1377619', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'$oid': '5e2a3798325093834fd90805'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1003803</td>\n",
       "      <td>1003803</td>\n",
       "      <td>2016-02-19 01:09:18+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Servicio de Administración Tributar...</td>\n",
       "      <td>{'name': 'Servicio de Administración Tributari...</td>\n",
       "      <td>{'id': '1003803', 'title': 'SERVICIO INTEGRAL ...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'id': 1004248, 'awardID': '1004248', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'$oid': '5e2a379837be1c3fcb93dd99'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1009245</td>\n",
       "      <td>1009245</td>\n",
       "      <td>2016-02-26 05:33:08+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Secretaría del Trabajo y Previsión ...</td>\n",
       "      <td>{'name': 'Secretaría del Trabajo y Previsión S...</td>\n",
       "      <td>{'id': '1009245', 'title': 'CONTRATACION DE UN...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'id': 1011086, 'awardID': '1011086', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>26</td>\n",
       "      <td>2</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'$oid': '5e2a3798d0ac91b80a73a1af'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1012355</td>\n",
       "      <td>1012355</td>\n",
       "      <td>2016-03-02 01:58:39+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Comisión Nacional para el Uso Efici...</td>\n",
       "      <td>{'name': 'Comisión Nacional para el Uso Eficie...</td>\n",
       "      <td>{'id': '1012355', 'title': 'SERVICIO INTEGRAL ...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'id': 1013973, 'awardID': '1013973', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'$oid': '5e2a3798cb04247fb1dcbd1b'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1017118</td>\n",
       "      <td>1017118</td>\n",
       "      <td>2016-03-08 07:17:10+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Nacional de Antropología ...</td>\n",
       "      <td>{'name': 'Instituto Nacional de Antropología e...</td>\n",
       "      <td>{'id': '1017118', 'title': 'Servicios de Jardi...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'id': 1172348, 'awardID': '1172348', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292284</th>\n",
       "      <td>{'$oid': '5e2a5afdb544edd3a56b61e5'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821401</td>\n",
       "      <td>1821401</td>\n",
       "      <td>2018-11-12 05:05:02+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821401', 'title': 'Suministro y Aplic...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292285</th>\n",
       "      <td>{'$oid': '5e2a5afd1ec2f64ba6d4f3d0'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821466</td>\n",
       "      <td>1821466</td>\n",
       "      <td>2018-11-12 05:06:39+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821466', 'title': 'IA-050GYR041-E156-...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292286</th>\n",
       "      <td>{'$oid': '5e2a5afd89eb30723bddafc7'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821529</td>\n",
       "      <td>1821529</td>\n",
       "      <td>2018-11-12 02:35:26+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821529', 'title': 'IA-050GYR008-E146-...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292287</th>\n",
       "      <td>{'$oid': '5e2a5afd75f61fad75ab12e3'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821698</td>\n",
       "      <td>1821698</td>\n",
       "      <td>2018-11-12 03:17:48+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821698', 'title': 'IA-050GYR012-E170-...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292288</th>\n",
       "      <td>{'$oid': '5e2a5afd23c0b9ab3c372fda'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821703</td>\n",
       "      <td>1821703</td>\n",
       "      <td>2018-11-12 02:49:57+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821703', 'title': 'IA-050GYR036-E126-...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>292289 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         _id  cycle                 ocid  \\\n",
       "0       {'$oid': '5e2a3797575b631d3c62539e'}   2016  ocds-07smqs-1003123   \n",
       "1       {'$oid': '5e2a3798325093834fd90805'}   2016  ocds-07smqs-1003803   \n",
       "2       {'$oid': '5e2a379837be1c3fcb93dd99'}   2016  ocds-07smqs-1009245   \n",
       "3       {'$oid': '5e2a3798d0ac91b80a73a1af'}   2016  ocds-07smqs-1012355   \n",
       "4       {'$oid': '5e2a3798cb04247fb1dcbd1b'}   2016  ocds-07smqs-1017118   \n",
       "...                                      ...    ...                  ...   \n",
       "292284  {'$oid': '5e2a5afdb544edd3a56b61e5'}   2018  ocds-07smqs-1821401   \n",
       "292285  {'$oid': '5e2a5afd1ec2f64ba6d4f3d0'}   2018  ocds-07smqs-1821466   \n",
       "292286  {'$oid': '5e2a5afd89eb30723bddafc7'}   2018  ocds-07smqs-1821529   \n",
       "292287  {'$oid': '5e2a5afd75f61fad75ab12e3'}   2018  ocds-07smqs-1821698   \n",
       "292288  {'$oid': '5e2a5afd23c0b9ab3c372fda'}   2018  ocds-07smqs-1821703   \n",
       "\n",
       "             id                      date           tag initiationType  \\\n",
       "0       1003123 2016-02-19 01:49:22+00:00  tender-award         tender   \n",
       "1       1003803 2016-02-19 01:09:18+00:00  tender-award         tender   \n",
       "2       1009245 2016-02-26 05:33:08+00:00  tender-award         tender   \n",
       "3       1012355 2016-03-02 01:58:39+00:00  tender-award         tender   \n",
       "4       1017118 2016-03-08 07:17:10+00:00  tender-award         tender   \n",
       "...         ...                       ...           ...            ...   \n",
       "292284  1821401 2018-11-12 05:05:02+00:00  tender-award         tender   \n",
       "292285  1821466 2018-11-12 05:06:39+00:00  tender-award         tender   \n",
       "292286  1821529 2018-11-12 02:35:26+00:00  tender-award         tender   \n",
       "292287  1821698 2018-11-12 03:17:48+00:00  tender-award         tender   \n",
       "292288  1821703 2018-11-12 02:49:57+00:00  tender-award         tender   \n",
       "\n",
       "                                                  parties  \\\n",
       "0       [{'name': 'Procuraduría General de la Repúblic...   \n",
       "1       [{'name': 'Servicio de Administración Tributar...   \n",
       "2       [{'name': 'Secretaría del Trabajo y Previsión ...   \n",
       "3       [{'name': 'Comisión Nacional para el Uso Efici...   \n",
       "4       [{'name': 'Instituto Nacional de Antropología ...   \n",
       "...                                                   ...   \n",
       "292284  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292285  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292286  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292287  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292288  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "\n",
       "                                                    buyer  \\\n",
       "0       {'name': 'Procuraduría General de la República...   \n",
       "1       {'name': 'Servicio de Administración Tributari...   \n",
       "2       {'name': 'Secretaría del Trabajo y Previsión S...   \n",
       "3       {'name': 'Comisión Nacional para el Uso Eficie...   \n",
       "4       {'name': 'Instituto Nacional de Antropología e...   \n",
       "...                                                   ...   \n",
       "292284  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292285  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292286  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292287  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292288  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "\n",
       "                                                   tender  ...  \\\n",
       "0       {'id': '1003123', 'title': 'MANTENIMIENTO PREV...  ...   \n",
       "1       {'id': '1003803', 'title': 'SERVICIO INTEGRAL ...  ...   \n",
       "2       {'id': '1009245', 'title': 'CONTRATACION DE UN...  ...   \n",
       "3       {'id': '1012355', 'title': 'SERVICIO INTEGRAL ...  ...   \n",
       "4       {'id': '1017118', 'title': 'Servicios de Jardi...  ...   \n",
       "...                                                   ...  ...   \n",
       "292284  {'id': '1821401', 'title': 'Suministro y Aplic...  ...   \n",
       "292285  {'id': '1821466', 'title': 'IA-050GYR041-E156-...  ...   \n",
       "292286  {'id': '1821529', 'title': 'IA-050GYR008-E146-...  ...   \n",
       "292287  {'id': '1821698', 'title': 'IA-050GYR012-E170-...  ...   \n",
       "292288  {'id': '1821703', 'title': 'IA-050GYR036-E126-...  ...   \n",
       "\n",
       "                                                contracts publisherUid  \\\n",
       "0       [{'id': 1377619, 'awardID': '1377619', 'title'...         6716   \n",
       "1       [{'id': 1004248, 'awardID': '1004248', 'title'...         6716   \n",
       "2       [{'id': 1011086, 'awardID': '1011086', 'title'...         6716   \n",
       "3       [{'id': 1013973, 'awardID': '1013973', 'title'...         6716   \n",
       "4       [{'id': 1172348, 'awardID': '1172348', 'title'...         6716   \n",
       "...                                                   ...          ...   \n",
       "292284                                                NaN         6716   \n",
       "292285                                                NaN         6716   \n",
       "292286                                                NaN         6716   \n",
       "292287                                                NaN         6716   \n",
       "292288                                                NaN         6716   \n",
       "\n",
       "                                   publisherName            publisherUri  \\\n",
       "0       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "1       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "2       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "3       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "4       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "...                                          ...                     ...   \n",
       "292284  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292285  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292286  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292287  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292288  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "\n",
       "       finalisation_date institution_abbreviation expeditionDay  \\\n",
       "0             2018-11-12                     SHCP            19   \n",
       "1             2018-11-12                     SHCP            19   \n",
       "2             2018-11-12                     SHCP            26   \n",
       "3             2018-11-12                     SHCP             2   \n",
       "4             2018-11-12                     SHCP             8   \n",
       "...                  ...                      ...           ...   \n",
       "292284        2018-11-13                     SHCP            12   \n",
       "292285        2018-11-13                     SHCP            12   \n",
       "292286        2018-11-13                     SHCP            12   \n",
       "292287        2018-11-13                     SHCP            12   \n",
       "292288        2018-11-13                     SHCP            12   \n",
       "\n",
       "       expeditionMonth  expeditionYear  expeditionTrimester  \n",
       "0                    2            2016                    1  \n",
       "1                    2            2016                    1  \n",
       "2                    2            2016                    1  \n",
       "3                    3            2016                    1  \n",
       "4                    3            2016                    1  \n",
       "...                ...             ...                  ...  \n",
       "292284              11            2018                    4  \n",
       "292285              11            2018                    4  \n",
       "292286              11            2018                    4  \n",
       "292287              11            2018                    4  \n",
       "292288              11            2018                    4  \n",
       "\n",
       "[292289 rows x 22 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sucio[\"expeditionTrimester\"] = trimester\n",
    "df_sucio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Day two"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sucio = pd.read_csv(\"NotDoneYet.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>_id</th>\n",
       "      <th>cycle</th>\n",
       "      <th>ocid</th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>tag</th>\n",
       "      <th>initiationType</th>\n",
       "      <th>parties</th>\n",
       "      <th>buyer</th>\n",
       "      <th>tender</th>\n",
       "      <th>...</th>\n",
       "      <th>contracts</th>\n",
       "      <th>publisherUid</th>\n",
       "      <th>publisherName</th>\n",
       "      <th>publisherUri</th>\n",
       "      <th>finalisation_date</th>\n",
       "      <th>institution_abbreviation</th>\n",
       "      <th>expeditionDay</th>\n",
       "      <th>expeditionMonth</th>\n",
       "      <th>expeditionYear</th>\n",
       "      <th>expeditionTrimester</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'$oid': '5e2a3797575b631d3c62539e'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1003123</td>\n",
       "      <td>1003123</td>\n",
       "      <td>2016-02-19 01:49:22+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Procuraduría General de la Repúblic...</td>\n",
       "      <td>{'name': 'Procuraduría General de la República...</td>\n",
       "      <td>{'id': '1003123', 'title': 'MANTENIMIENTO PREV...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'id': 1377619, 'awardID': '1377619', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'$oid': '5e2a3798325093834fd90805'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1003803</td>\n",
       "      <td>1003803</td>\n",
       "      <td>2016-02-19 01:09:18+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Servicio de Administración Tributar...</td>\n",
       "      <td>{'name': 'Servicio de Administración Tributari...</td>\n",
       "      <td>{'id': '1003803', 'title': 'SERVICIO INTEGRAL ...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'id': 1004248, 'awardID': '1004248', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'$oid': '5e2a379837be1c3fcb93dd99'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1009245</td>\n",
       "      <td>1009245</td>\n",
       "      <td>2016-02-26 05:33:08+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Secretaría del Trabajo y Previsión ...</td>\n",
       "      <td>{'name': 'Secretaría del Trabajo y Previsión S...</td>\n",
       "      <td>{'id': '1009245', 'title': 'CONTRATACION DE UN...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'id': 1011086, 'awardID': '1011086', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>26</td>\n",
       "      <td>2</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'$oid': '5e2a3798d0ac91b80a73a1af'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1012355</td>\n",
       "      <td>1012355</td>\n",
       "      <td>2016-03-02 01:58:39+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Comisión Nacional para el Uso Efici...</td>\n",
       "      <td>{'name': 'Comisión Nacional para el Uso Eficie...</td>\n",
       "      <td>{'id': '1012355', 'title': 'SERVICIO INTEGRAL ...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'id': 1013973, 'awardID': '1013973', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'$oid': '5e2a3798cb04247fb1dcbd1b'}</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1017118</td>\n",
       "      <td>1017118</td>\n",
       "      <td>2016-03-08 07:17:10+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Nacional de Antropología ...</td>\n",
       "      <td>{'name': 'Instituto Nacional de Antropología e...</td>\n",
       "      <td>{'id': '1017118', 'title': 'Servicios de Jardi...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'id': 1172348, 'awardID': '1172348', 'title'...</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292284</th>\n",
       "      <td>{'$oid': '5e2a5afdb544edd3a56b61e5'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821401</td>\n",
       "      <td>1821401</td>\n",
       "      <td>2018-11-12 05:05:02+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821401', 'title': 'Suministro y Aplic...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292285</th>\n",
       "      <td>{'$oid': '5e2a5afd1ec2f64ba6d4f3d0'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821466</td>\n",
       "      <td>1821466</td>\n",
       "      <td>2018-11-12 05:06:39+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821466', 'title': 'IA-050GYR041-E156-...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292286</th>\n",
       "      <td>{'$oid': '5e2a5afd89eb30723bddafc7'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821529</td>\n",
       "      <td>1821529</td>\n",
       "      <td>2018-11-12 02:35:26+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821529', 'title': 'IA-050GYR008-E146-...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292287</th>\n",
       "      <td>{'$oid': '5e2a5afd75f61fad75ab12e3'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821698</td>\n",
       "      <td>1821698</td>\n",
       "      <td>2018-11-12 03:17:48+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821698', 'title': 'IA-050GYR012-E170-...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292288</th>\n",
       "      <td>{'$oid': '5e2a5afd23c0b9ab3c372fda'}</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821703</td>\n",
       "      <td>1821703</td>\n",
       "      <td>2018-11-12 02:49:57+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'name': 'Instituto Mexicano del Seguro Social...</td>\n",
       "      <td>{'id': '1821703', 'title': 'IA-050GYR036-E126-...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>292289 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         _id  cycle                 ocid  \\\n",
       "0       {'$oid': '5e2a3797575b631d3c62539e'}   2016  ocds-07smqs-1003123   \n",
       "1       {'$oid': '5e2a3798325093834fd90805'}   2016  ocds-07smqs-1003803   \n",
       "2       {'$oid': '5e2a379837be1c3fcb93dd99'}   2016  ocds-07smqs-1009245   \n",
       "3       {'$oid': '5e2a3798d0ac91b80a73a1af'}   2016  ocds-07smqs-1012355   \n",
       "4       {'$oid': '5e2a3798cb04247fb1dcbd1b'}   2016  ocds-07smqs-1017118   \n",
       "...                                      ...    ...                  ...   \n",
       "292284  {'$oid': '5e2a5afdb544edd3a56b61e5'}   2018  ocds-07smqs-1821401   \n",
       "292285  {'$oid': '5e2a5afd1ec2f64ba6d4f3d0'}   2018  ocds-07smqs-1821466   \n",
       "292286  {'$oid': '5e2a5afd89eb30723bddafc7'}   2018  ocds-07smqs-1821529   \n",
       "292287  {'$oid': '5e2a5afd75f61fad75ab12e3'}   2018  ocds-07smqs-1821698   \n",
       "292288  {'$oid': '5e2a5afd23c0b9ab3c372fda'}   2018  ocds-07smqs-1821703   \n",
       "\n",
       "             id                       date           tag initiationType  \\\n",
       "0       1003123  2016-02-19 01:49:22+00:00  tender-award         tender   \n",
       "1       1003803  2016-02-19 01:09:18+00:00  tender-award         tender   \n",
       "2       1009245  2016-02-26 05:33:08+00:00  tender-award         tender   \n",
       "3       1012355  2016-03-02 01:58:39+00:00  tender-award         tender   \n",
       "4       1017118  2016-03-08 07:17:10+00:00  tender-award         tender   \n",
       "...         ...                        ...           ...            ...   \n",
       "292284  1821401  2018-11-12 05:05:02+00:00  tender-award         tender   \n",
       "292285  1821466  2018-11-12 05:06:39+00:00  tender-award         tender   \n",
       "292286  1821529  2018-11-12 02:35:26+00:00  tender-award         tender   \n",
       "292287  1821698  2018-11-12 03:17:48+00:00  tender-award         tender   \n",
       "292288  1821703  2018-11-12 02:49:57+00:00  tender-award         tender   \n",
       "\n",
       "                                                  parties  \\\n",
       "0       [{'name': 'Procuraduría General de la Repúblic...   \n",
       "1       [{'name': 'Servicio de Administración Tributar...   \n",
       "2       [{'name': 'Secretaría del Trabajo y Previsión ...   \n",
       "3       [{'name': 'Comisión Nacional para el Uso Efici...   \n",
       "4       [{'name': 'Instituto Nacional de Antropología ...   \n",
       "...                                                   ...   \n",
       "292284  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292285  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292286  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292287  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292288  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "\n",
       "                                                    buyer  \\\n",
       "0       {'name': 'Procuraduría General de la República...   \n",
       "1       {'name': 'Servicio de Administración Tributari...   \n",
       "2       {'name': 'Secretaría del Trabajo y Previsión S...   \n",
       "3       {'name': 'Comisión Nacional para el Uso Eficie...   \n",
       "4       {'name': 'Instituto Nacional de Antropología e...   \n",
       "...                                                   ...   \n",
       "292284  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292285  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292286  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292287  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "292288  {'name': 'Instituto Mexicano del Seguro Social...   \n",
       "\n",
       "                                                   tender  ...  \\\n",
       "0       {'id': '1003123', 'title': 'MANTENIMIENTO PREV...  ...   \n",
       "1       {'id': '1003803', 'title': 'SERVICIO INTEGRAL ...  ...   \n",
       "2       {'id': '1009245', 'title': 'CONTRATACION DE UN...  ...   \n",
       "3       {'id': '1012355', 'title': 'SERVICIO INTEGRAL ...  ...   \n",
       "4       {'id': '1017118', 'title': 'Servicios de Jardi...  ...   \n",
       "...                                                   ...  ...   \n",
       "292284  {'id': '1821401', 'title': 'Suministro y Aplic...  ...   \n",
       "292285  {'id': '1821466', 'title': 'IA-050GYR041-E156-...  ...   \n",
       "292286  {'id': '1821529', 'title': 'IA-050GYR008-E146-...  ...   \n",
       "292287  {'id': '1821698', 'title': 'IA-050GYR012-E170-...  ...   \n",
       "292288  {'id': '1821703', 'title': 'IA-050GYR036-E126-...  ...   \n",
       "\n",
       "                                                contracts publisherUid  \\\n",
       "0       [{'id': 1377619, 'awardID': '1377619', 'title'...         6716   \n",
       "1       [{'id': 1004248, 'awardID': '1004248', 'title'...         6716   \n",
       "2       [{'id': 1011086, 'awardID': '1011086', 'title'...         6716   \n",
       "3       [{'id': 1013973, 'awardID': '1013973', 'title'...         6716   \n",
       "4       [{'id': 1172348, 'awardID': '1172348', 'title'...         6716   \n",
       "...                                                   ...          ...   \n",
       "292284                                                NaN         6716   \n",
       "292285                                                NaN         6716   \n",
       "292286                                                NaN         6716   \n",
       "292287                                                NaN         6716   \n",
       "292288                                                NaN         6716   \n",
       "\n",
       "                                   publisherName            publisherUri  \\\n",
       "0       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "1       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "2       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "3       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "4       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "...                                          ...                     ...   \n",
       "292284  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292285  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292286  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292287  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292288  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "\n",
       "       finalisation_date institution_abbreviation expeditionDay  \\\n",
       "0             2018-11-12                     SHCP            19   \n",
       "1             2018-11-12                     SHCP            19   \n",
       "2             2018-11-12                     SHCP            26   \n",
       "3             2018-11-12                     SHCP             2   \n",
       "4             2018-11-12                     SHCP             8   \n",
       "...                  ...                      ...           ...   \n",
       "292284        2018-11-13                     SHCP            12   \n",
       "292285        2018-11-13                     SHCP            12   \n",
       "292286        2018-11-13                     SHCP            12   \n",
       "292287        2018-11-13                     SHCP            12   \n",
       "292288        2018-11-13                     SHCP            12   \n",
       "\n",
       "       expeditionMonth  expeditionYear  expeditionTrimester  \n",
       "0                    2            2016                    1  \n",
       "1                    2            2016                    1  \n",
       "2                    2            2016                    1  \n",
       "3                    3            2016                    1  \n",
       "4                    3            2016                    1  \n",
       "...                ...             ...                  ...  \n",
       "292284              11            2018                    4  \n",
       "292285              11            2018                    4  \n",
       "292286              11            2018                    4  \n",
       "292287              11            2018                    4  \n",
       "292288              11            2018                    4  \n",
       "\n",
       "[292289 rows x 22 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sucio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# _ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "_id = df_sucio['_id'].apply(lambda x: re.sub(\"'\",'\"',x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "_id = _id.apply(lambda x: json.loads(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sucio['_id'] = _id.apply(lambda x: x['$oid'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         5e2a3797575b631d3c62539e\n",
       "1         5e2a3798325093834fd90805\n",
       "2         5e2a379837be1c3fcb93dd99\n",
       "3         5e2a3798d0ac91b80a73a1af\n",
       "4         5e2a3798cb04247fb1dcbd1b\n",
       "                    ...           \n",
       "292284    5e2a5afdb544edd3a56b61e5\n",
       "292285    5e2a5afd1ec2f64ba6d4f3d0\n",
       "292286    5e2a5afd89eb30723bddafc7\n",
       "292287    5e2a5afd75f61fad75ab12e3\n",
       "292288    5e2a5afd23c0b9ab3c372fda\n",
       "Name: _id, Length: 292289, dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sucio['_id'] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Buyers\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sucio.to_csv('NotDoneYet.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Regex was needed to extract exactly what we need from the info\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "buyer = df_sucio['buyer'].apply(lambda x: re.sub(\"'\",'\"',x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "buyer = buyer.apply(lambda x: x.split('\"'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in buyer.index:\n",
    "    if len(buyer[i]) > 9:\n",
    "        buyer[i].pop(4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in buyer.index:\n",
    "    if len(buyer[i]) > 9:\n",
    "        buyer[i].pop(4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "buyer = buyer.apply(lambda x: '\"'.join(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"name\": \"Procuraduría General de la República\", \"id\": \"PGR-251\"}'"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "buyer[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in buyer.index:\n",
    "    buyer[i] = json.loads(buyer[i])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aver = []\n",
    "for i in buyer.index:\n",
    "    if type(buyer[i]) != dict:\n",
    "        aver.append(i)\n",
    "aver"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'name': 'Hospital General de México ', 'id': 'HGM-162'}"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "buyer[74959]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sucio['buyer'] = buyer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ok lets keep going :c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "buyer_name = df_sucio['buyer'].apply(lambda x: x['name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "262"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(buyer_name.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "buyer_id = df_sucio['buyer'].apply(lambda x: x['id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "262"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(buyer_id.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sucio['buyerName'] = buyer_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sucio['buyerId'] = buyer_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sucio = df_sucio.drop(columns=[\"buyer\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "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>_id</th>\n",
       "      <th>cycle</th>\n",
       "      <th>ocid</th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>tag</th>\n",
       "      <th>initiationType</th>\n",
       "      <th>parties</th>\n",
       "      <th>tender</th>\n",
       "      <th>language</th>\n",
       "      <th>...</th>\n",
       "      <th>publisherName</th>\n",
       "      <th>publisherUri</th>\n",
       "      <th>finalisation_date</th>\n",
       "      <th>institution_abbreviation</th>\n",
       "      <th>expeditionDay</th>\n",
       "      <th>expeditionMonth</th>\n",
       "      <th>expeditionYear</th>\n",
       "      <th>expeditionTrimester</th>\n",
       "      <th>buyerName</th>\n",
       "      <th>buyerId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5e2a3797575b631d3c62539e</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1003123</td>\n",
       "      <td>1003123</td>\n",
       "      <td>2016-02-19 01:49:22+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Procuraduría General de la Repúblic...</td>\n",
       "      <td>{'id': '1003123', 'title': 'MANTENIMIENTO PREV...</td>\n",
       "      <td>es</td>\n",
       "      <td>...</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>Procuraduría General de la República</td>\n",
       "      <td>PGR-251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5e2a3798325093834fd90805</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1003803</td>\n",
       "      <td>1003803</td>\n",
       "      <td>2016-02-19 01:09:18+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Servicio de Administración Tributar...</td>\n",
       "      <td>{'id': '1003803', 'title': 'SERVICIO INTEGRAL ...</td>\n",
       "      <td>es</td>\n",
       "      <td>...</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>Servicio de Administración Tributaria</td>\n",
       "      <td>SAT-284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5e2a379837be1c3fcb93dd99</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1009245</td>\n",
       "      <td>1009245</td>\n",
       "      <td>2016-02-26 05:33:08+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Secretaría del Trabajo y Previsión ...</td>\n",
       "      <td>{'id': '1009245', 'title': 'CONTRATACION DE UN...</td>\n",
       "      <td>es</td>\n",
       "      <td>...</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>26</td>\n",
       "      <td>2</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>Secretaría del Trabajo y Previsión Social</td>\n",
       "      <td>STPS-278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5e2a3798d0ac91b80a73a1af</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1012355</td>\n",
       "      <td>1012355</td>\n",
       "      <td>2016-03-02 01:58:39+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Comisión Nacional para el Uso Efici...</td>\n",
       "      <td>{'id': '1012355', 'title': 'SERVICIO INTEGRAL ...</td>\n",
       "      <td>es</td>\n",
       "      <td>...</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>Comisión Nacional para el Uso Eficiente de la ...</td>\n",
       "      <td>CONUEE-98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5e2a3798cb04247fb1dcbd1b</td>\n",
       "      <td>2016</td>\n",
       "      <td>ocds-07smqs-1017118</td>\n",
       "      <td>1017118</td>\n",
       "      <td>2016-03-08 07:17:10+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Nacional de Antropología ...</td>\n",
       "      <td>{'id': '1017118', 'title': 'Servicios de Jardi...</td>\n",
       "      <td>es</td>\n",
       "      <td>...</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>Instituto Nacional de Antropología e Historia</td>\n",
       "      <td>INAH-195</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292284</th>\n",
       "      <td>5e2a5afdb544edd3a56b61e5</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821401</td>\n",
       "      <td>1821401</td>\n",
       "      <td>2018-11-12 05:05:02+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'id': '1821401', 'title': 'Suministro y Aplic...</td>\n",
       "      <td>es</td>\n",
       "      <td>...</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "      <td>Instituto Mexicano del Seguro Social</td>\n",
       "      <td>IMSS-192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292285</th>\n",
       "      <td>5e2a5afd1ec2f64ba6d4f3d0</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821466</td>\n",
       "      <td>1821466</td>\n",
       "      <td>2018-11-12 05:06:39+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'id': '1821466', 'title': 'IA-050GYR041-E156-...</td>\n",
       "      <td>es</td>\n",
       "      <td>...</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "      <td>Instituto Mexicano del Seguro Social</td>\n",
       "      <td>IMSS-192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292286</th>\n",
       "      <td>5e2a5afd89eb30723bddafc7</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821529</td>\n",
       "      <td>1821529</td>\n",
       "      <td>2018-11-12 02:35:26+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'id': '1821529', 'title': 'IA-050GYR008-E146-...</td>\n",
       "      <td>es</td>\n",
       "      <td>...</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "      <td>Instituto Mexicano del Seguro Social</td>\n",
       "      <td>IMSS-192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292287</th>\n",
       "      <td>5e2a5afd75f61fad75ab12e3</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821698</td>\n",
       "      <td>1821698</td>\n",
       "      <td>2018-11-12 03:17:48+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'id': '1821698', 'title': 'IA-050GYR012-E170-...</td>\n",
       "      <td>es</td>\n",
       "      <td>...</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "      <td>Instituto Mexicano del Seguro Social</td>\n",
       "      <td>IMSS-192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292288</th>\n",
       "      <td>5e2a5afd23c0b9ab3c372fda</td>\n",
       "      <td>2018</td>\n",
       "      <td>ocds-07smqs-1821703</td>\n",
       "      <td>1821703</td>\n",
       "      <td>2018-11-12 02:49:57+00:00</td>\n",
       "      <td>tender-award</td>\n",
       "      <td>tender</td>\n",
       "      <td>[{'name': 'Instituto Mexicano del Seguro Socia...</td>\n",
       "      <td>{'id': '1821703', 'title': 'IA-050GYR036-E126-...</td>\n",
       "      <td>es</td>\n",
       "      <td>...</td>\n",
       "      <td>SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO</td>\n",
       "      <td>http://www.gob.mx/shcp</td>\n",
       "      <td>2018-11-13</td>\n",
       "      <td>SHCP</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "      <td>Instituto Mexicano del Seguro Social</td>\n",
       "      <td>IMSS-192</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>292289 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             _id  cycle                 ocid       id  \\\n",
       "0       5e2a3797575b631d3c62539e   2016  ocds-07smqs-1003123  1003123   \n",
       "1       5e2a3798325093834fd90805   2016  ocds-07smqs-1003803  1003803   \n",
       "2       5e2a379837be1c3fcb93dd99   2016  ocds-07smqs-1009245  1009245   \n",
       "3       5e2a3798d0ac91b80a73a1af   2016  ocds-07smqs-1012355  1012355   \n",
       "4       5e2a3798cb04247fb1dcbd1b   2016  ocds-07smqs-1017118  1017118   \n",
       "...                          ...    ...                  ...      ...   \n",
       "292284  5e2a5afdb544edd3a56b61e5   2018  ocds-07smqs-1821401  1821401   \n",
       "292285  5e2a5afd1ec2f64ba6d4f3d0   2018  ocds-07smqs-1821466  1821466   \n",
       "292286  5e2a5afd89eb30723bddafc7   2018  ocds-07smqs-1821529  1821529   \n",
       "292287  5e2a5afd75f61fad75ab12e3   2018  ocds-07smqs-1821698  1821698   \n",
       "292288  5e2a5afd23c0b9ab3c372fda   2018  ocds-07smqs-1821703  1821703   \n",
       "\n",
       "                             date           tag initiationType  \\\n",
       "0       2016-02-19 01:49:22+00:00  tender-award         tender   \n",
       "1       2016-02-19 01:09:18+00:00  tender-award         tender   \n",
       "2       2016-02-26 05:33:08+00:00  tender-award         tender   \n",
       "3       2016-03-02 01:58:39+00:00  tender-award         tender   \n",
       "4       2016-03-08 07:17:10+00:00  tender-award         tender   \n",
       "...                           ...           ...            ...   \n",
       "292284  2018-11-12 05:05:02+00:00  tender-award         tender   \n",
       "292285  2018-11-12 05:06:39+00:00  tender-award         tender   \n",
       "292286  2018-11-12 02:35:26+00:00  tender-award         tender   \n",
       "292287  2018-11-12 03:17:48+00:00  tender-award         tender   \n",
       "292288  2018-11-12 02:49:57+00:00  tender-award         tender   \n",
       "\n",
       "                                                  parties  \\\n",
       "0       [{'name': 'Procuraduría General de la Repúblic...   \n",
       "1       [{'name': 'Servicio de Administración Tributar...   \n",
       "2       [{'name': 'Secretaría del Trabajo y Previsión ...   \n",
       "3       [{'name': 'Comisión Nacional para el Uso Efici...   \n",
       "4       [{'name': 'Instituto Nacional de Antropología ...   \n",
       "...                                                   ...   \n",
       "292284  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292285  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292286  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292287  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "292288  [{'name': 'Instituto Mexicano del Seguro Socia...   \n",
       "\n",
       "                                                   tender language  ...  \\\n",
       "0       {'id': '1003123', 'title': 'MANTENIMIENTO PREV...       es  ...   \n",
       "1       {'id': '1003803', 'title': 'SERVICIO INTEGRAL ...       es  ...   \n",
       "2       {'id': '1009245', 'title': 'CONTRATACION DE UN...       es  ...   \n",
       "3       {'id': '1012355', 'title': 'SERVICIO INTEGRAL ...       es  ...   \n",
       "4       {'id': '1017118', 'title': 'Servicios de Jardi...       es  ...   \n",
       "...                                                   ...      ...  ...   \n",
       "292284  {'id': '1821401', 'title': 'Suministro y Aplic...       es  ...   \n",
       "292285  {'id': '1821466', 'title': 'IA-050GYR041-E156-...       es  ...   \n",
       "292286  {'id': '1821529', 'title': 'IA-050GYR008-E146-...       es  ...   \n",
       "292287  {'id': '1821698', 'title': 'IA-050GYR012-E170-...       es  ...   \n",
       "292288  {'id': '1821703', 'title': 'IA-050GYR036-E126-...       es  ...   \n",
       "\n",
       "                                   publisherName            publisherUri  \\\n",
       "0       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "1       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "2       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "3       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "4       SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "...                                          ...                     ...   \n",
       "292284  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292285  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292286  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292287  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "292288  SECRETARÍA DE HACIENDA Y CRÉDITO PÚBLICO  http://www.gob.mx/shcp   \n",
       "\n",
       "        finalisation_date institution_abbreviation expeditionDay  \\\n",
       "0              2018-11-12                     SHCP            19   \n",
       "1              2018-11-12                     SHCP            19   \n",
       "2              2018-11-12                     SHCP            26   \n",
       "3              2018-11-12                     SHCP             2   \n",
       "4              2018-11-12                     SHCP             8   \n",
       "...                   ...                      ...           ...   \n",
       "292284         2018-11-13                     SHCP            12   \n",
       "292285         2018-11-13                     SHCP            12   \n",
       "292286         2018-11-13                     SHCP            12   \n",
       "292287         2018-11-13                     SHCP            12   \n",
       "292288         2018-11-13                     SHCP            12   \n",
       "\n",
       "       expeditionMonth expeditionYear  expeditionTrimester  \\\n",
       "0                    2           2016                    1   \n",
       "1                    2           2016                    1   \n",
       "2                    2           2016                    1   \n",
       "3                    3           2016                    1   \n",
       "4                    3           2016                    1   \n",
       "...                ...            ...                  ...   \n",
       "292284              11           2018                    4   \n",
       "292285              11           2018                    4   \n",
       "292286              11           2018                    4   \n",
       "292287              11           2018                    4   \n",
       "292288              11           2018                    4   \n",
       "\n",
       "                                                buyerName    buyerId  \n",
       "0                    Procuraduría General de la República    PGR-251  \n",
       "1                   Servicio de Administración Tributaria    SAT-284  \n",
       "2               Secretaría del Trabajo y Previsión Social   STPS-278  \n",
       "3       Comisión Nacional para el Uso Eficiente de la ...  CONUEE-98  \n",
       "4           Instituto Nacional de Antropología e Historia   INAH-195  \n",
       "...                                                   ...        ...  \n",
       "292284               Instituto Mexicano del Seguro Social   IMSS-192  \n",
       "292285               Instituto Mexicano del Seguro Social   IMSS-192  \n",
       "292286               Instituto Mexicano del Seguro Social   IMSS-192  \n",
       "292287               Instituto Mexicano del Seguro Social   IMSS-192  \n",
       "292288               Instituto Mexicano del Seguro Social   IMSS-192  \n",
       "\n",
       "[292289 rows x 23 columns]"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sucio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sucio.to_csv('NotYetDone.csv',index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}