pipeline.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Mexican federal budget pre-processing pipeline\n",
    "\n",
    "## Instructions\n",
    "\n",
    "To you run the notebook:\n",
    "\n",
    "1. choose a unique `ITERATION_LABEL` for each pipeline run\n",
    "2. specify and describe your input files (`INPUT_FILES`)\n",
    "3. make sure your column mapping (`COLUMN_ALIASES`) is correct\n",
    "3. run the whole notebook by clicking on __Kernel > Restart & Run All__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sys import stdout\n",
    "from pandas import read_csv, concat, DataFrame, ExcelWriter, ExcelFile, Series\n",
    "from numpy import nan, isnan\n",
    "from os.path import join, isdir\n",
    "from os import mkdir\n",
    "from json import dumps, loads\n",
    "from pprint import pprint\n",
    "from csv import DictReader\n",
    "from shutil import copyfile"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Settings\n",
    "\n",
    "Choose a unique iteration label for each pipeline run."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ITERATION_LABEL = 'iteration-20-post-launch-bug-fixes-test-run2'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Put your input files inside the `pipeline.in` folder and describe them here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "INPUT_FILES = {\n",
    "    2008: {'name': 'Cuenta_Publica_2008.csv', 'encoding': 'windows-1252'},\n",
    "    2009: {'name': 'Cuenta_Publica_2009.csv', 'encoding': 'windows-1252'},\n",
    "    2010: {'name': 'Cuenta_Publica_2010.csv', 'encoding': 'windows-1252'},\n",
    "    2011: {'name': 'Cuenta_Publica_2011.csv', 'encoding': 'windows-1252'},\n",
    "    2012: {'name': 'Cuenta_Publica_2012.csv', 'encoding': 'windows-1252'},\n",
    "    2013: {'name': 'CP_2013.csv', 'encoding': 'windows-1252'},\n",
    "    2014: {'name': 'CP_2014.csv', 'encoding': 'windows-1252'},\n",
    "    2015: {'name': 'CP_2015.csv', 'encoding': 'windows-1252'},\n",
    "    2016: {'name': 'PEF_AC01_2t_2016.csv', 'encoding': 'windows-1252'} \n",
    "}\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define your mappings in `columns.aliases.json`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following hierarchical categories will have IDs prefixed with the parent categories:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "HIERARCHIES = {\n",
    "    'functional': [\n",
    "        'GPO_FUNCIONAL', \n",
    "        'ID_FUNCION', \n",
    "        'ID_SUBFUNCION', \n",
    "        'ID_AI'\n",
    "    ],\n",
    "    'administrative': [\n",
    "        'ID_RAMO',\n",
    "        'ID_UR',\n",
    "        'ID_PP',\n",
    "        'ID_MODALIDAD'\n",
    "    ]\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following columns are unsused and removed at the end of the pipeline:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "REMOVE_OUTPUT_COLUMNS = [\n",
    "    'Reasignacion',\n",
    "    'Objeto del Gasto',\n",
    "    'Descripción de Reasignacion',\n",
    "    'Descripción de Objeto del Gasto'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "REMOVE_INPUT_COLUMNS = {}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's it. Now just run the notebook from beginning to end."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Configuration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "BASENAME = 'mexican_federal_budget'\n",
    "INPUT_FOLDER = 'pipeline.in.v2'\n",
    "OUTPUT_FOLDER = 'pipeline.out'\n",
    "ITERATION_FOLDER = join(OUTPUT_FOLDER, ITERATION_LABEL)\n",
    "MERGED_FILE = join(ITERATION_FOLDER, BASENAME + '.merged.csv')\n",
    "CATALOGS_FILE = 'objeto_del_gasto.catalog.xlsx'\n",
    "COLUMN_ALIASES_FILE = 'columns.aliases.json'\n",
    "DATAPACKAGE_FILE = 'datapackage.default.json'\n",
    "DESCRIPTIONS_FILE = 'columns.descriptions.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "if isdir(ITERATION_FOLDER):\n",
    "    raise ValueError('Please enter a unique iteration label')\n",
    "    \n",
    "mkdir(ITERATION_FOLDER)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Encoding inspection\n",
    "\n",
    "Detect the file encodings of the input files using the `cChardet` utility library. __Warning:__ it's not always accurate. This is meant only as an indication only. In the end, encodings will be taken from `INPUT_FILES`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def detect_encodings():\n",
    "    \"\"\"Detect CSV file encoding with the cChardet library\"\"\"\n",
    "\n",
    "    try:\n",
    "        import cchardet as chardet\n",
    "    except ImportError:\n",
    "        cChardet = 'https://github.com/PyYoshi/cChardet'\n",
    "        print('Encoding inspection skipped: install %s', cChardet)\n",
    "        return\n",
    "\n",
    "    results = {}\n",
    "    results_file = join(OUTPUT_FOLDER, ITERATION_LABEL, 'encodings.detected.json')\n",
    "    \n",
    "    for year, file in sorted(INPUT_FILES.items()):\n",
    "        datafile = join(INPUT_FOLDER, file['name'])\n",
    "        \n",
    "        with open(datafile, 'rb') as f:\n",
    "            text = f.read()\n",
    "            \n",
    "        result = chardet.detect(text)\n",
    "        results.update({year: result})\n",
    "        print(year, 'Inspected', file['name'], result)\n",
    "    \n",
    "    with open(results_file, 'w+') as json:\n",
    "        json.write(dumps(results, indent=4))\n",
    "        print('\\nSaved encoding detection report to', results_file)\n",
    "        \n",
    "# detect_encodings()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def read_columns(file, encoding):\n",
    "    \"\"\"Return clean CSV file headers\"\"\"\n",
    "    \n",
    "    with open(file, encoding=encoding) as csv:\n",
    "        header = csv.readline()\n",
    "        return header.replace('\\n', '').split(',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def force_strings(columns):\n",
    "    \"\"\"Return string enforcement for each column of a CSV file\"\"\"\n",
    "    \n",
    "    for column in columns:\n",
    "        yield column, str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def load_csv_files():\n",
    "    \"\"\"Load raw data (CSV) files\"\"\"\n",
    "    \n",
    "    batch = {}\n",
    "    \n",
    "    for year, file in sorted(INPUT_FILES.items()):\n",
    "        filepath = join(INPUT_FOLDER, file['name'])\n",
    "        column_names = read_columns(filepath, file['encoding'])\n",
    "        column_types = dict(force_strings(column_names))\n",
    "        \n",
    "        batch[year] = read_csv(filepath, encoding=file['encoding'], dtype=column_types)\n",
    "        print('Loaded', file['name'], 'with encoding', file['encoding'])\n",
    "    \n",
    "    print()\n",
    "    stdout.flush()\n",
    "\n",
    "    for year in sorted(INPUT_FILES.keys()):\n",
    "        if year in REMOVE_INPUT_COLUMNS:\n",
    "            for column in REMOVE_INPUT_COLUMNS[year]:\n",
    "                try:\n",
    "                    del batch[year][column]\n",
    "                    print(year, 'deleted', column)\n",
    "                except KeyError:\n",
    "                    print(year, column, 'not found in', file['name'])\n",
    "\n",
    "        stdout.flush()\n",
    "\n",
    "    return batch"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with open(COLUMN_ALIASES_FILE) as json:\n",
    "    COLUMN_ALIASES = loads(json.read())\n",
    "    \n",
    "with open(DATAPACKAGE_FILE) as json:\n",
    "    DATAPACKAGE = loads(json.read())\n",
    "\n",
    "def update_datapackage():\n",
    "    with open(DESCRIPTIONS_FILE) as stream:\n",
    "        rows = DictReader(stream)\n",
    "        reference = {row.pop('Backend Header'): row for row in rows}\n",
    "   \n",
    "    fields = DATAPACKAGE['resources'][0]['schema']['fields']\n",
    "    field_names = []\n",
    "    \n",
    "    for field in fields:\n",
    "        field_name = field['name']\n",
    "        field['title'] = reference[field_name]['Concept']\n",
    "        field['description'] = reference[field_name]['Description']\n",
    "        field_names.append(field_name)\n",
    "        print('updated datapackage title and description for %s' % field_name)\n",
    "        \n",
    "    not_matched = set(field_names) ^ set(reference.keys())\n",
    "    if not_matched:\n",
    "        raise ValueError('Fields in {} do not match fields in {}: {}'.format(\n",
    "            DATAPACKAGE_FILE, \n",
    "            DESCRIPTIONS_FILE,\n",
    "            not_matched))\n",
    "        \n",
    "    not_matched = set(field_names) ^ set(COLUMN_ALIASES.keys())\n",
    "    if not_matched:\n",
    "        raise ValueError('Fields in {} do not match fields in {}: {}'.format(\n",
    "            DATAPACKAGE_FILE, \n",
    "            DESCRIPTIONS_FILE,\n",
    "            not_matched))\n",
    "        \n",
    "    with open(DATAPACKAGE_FILE, 'w+') as stream:\n",
    "        stream.write(dumps(DATAPACKAGE, indent=2, ensure_ascii=True))\n",
    "        \n",
    "    copyfile(DATAPACKAGE_FILE, join(ITERATION_FOLDER, DATAPACKAGE_FILE.replace('.default', '')))\n",
    "        \n",
    "    print('checked columns consistency for {}, {} and {}: okay!'.format(\n",
    "            DATAPACKAGE_FILE, COLUMN_ALIASES_FILE, DESCRIPTIONS_FILE))\n",
    "    print('copied', DATAPACKAGE_FILE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def strip_cell_padding(batch):\n",
    "    for year in sorted(batch.keys()):\n",
    "        for column in batch[year].columns:\n",
    "            batch[year].rename(columns={column: column.strip()}, inplace=True)\n",
    "            batch[year][column] = batch[year][column].apply(lambda x: x.strip() if x is not nan else x)\n",
    "        print(year, 'stripped cell paddings')\n",
    "        stdout.flush()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def delete_empty_columns(batch):\n",
    "    for year in batch.keys():\n",
    "        for column in batch[year].columns:\n",
    "            if 'Unnamed:' in column:\n",
    "                try:\n",
    "                    del batch[year][column]\n",
    "                    print(year, column, 'deleted')\n",
    "                    stdout.flush()\n",
    "                except KeyError:\n",
    "                    pass  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def count_missing_values(batch):\n",
    "    collector = {}\n",
    "    table = []\n",
    "\n",
    "    for column in get_union_of_columns(batch):\n",
    "        row = {'Column': column}\n",
    "        collector.update({column: []})\n",
    "        \n",
    "        for year in batch.keys():\n",
    "            if column in batch[year].columns:\n",
    "                is_empty = batch[year][column].isnull()\n",
    "                empty_lines = batch[year].where(is_empty).dropna(how='all')\n",
    "                collector[column].extend(empty_lines.to_dict(orient='records'))\n",
    "                nb_empty_cells = len(empty_lines)\n",
    "            else:\n",
    "                nb_empty_cells = nan\n",
    "                \n",
    "            row.update({year: nb_empty_cells})\n",
    "            if nb_empty_cells not in (nan, 0):\n",
    "                print(year, 'found', nb_empty_cells, 'missing values in', column)\n",
    "\n",
    "        table.append(row)\n",
    "        \n",
    "    ordered_columns = ['Column']\n",
    "    ordered_columns.extend(sorted(batch.keys()))\n",
    "    empty_values_overview_table = DataFrame(table).reindex_axis(ordered_columns, axis=1)\n",
    "    \n",
    "    return empty_values_overview_table, collector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def count_duplicates(batch):\n",
    "    for year, df in sorted(batch.items()):\n",
    "        nb_duplicate_lines = df.duplicated().apply(lambda x: 1 if x is True else 0).sum()\n",
    "        print(year, 'found', nb_duplicate_lines, 'duplicate lines')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Alias column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_union_of_columns(batch):\n",
    "    union = set()\n",
    "    for year in batch.keys():\n",
    "        union = union | set(batch[year].columns)\n",
    "    return union"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from yaml import load\n",
    "\n",
    "def load_aliases(file):\n",
    "    with open(file) as yaml:\n",
    "        aliases = load(yaml.read())\n",
    "        return aliases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def map_columns_to_aliases(batch, list_of_aliases):\n",
    "    for year in sorted(batch.keys()):\n",
    "        for column in sorted(batch[year].columns):\n",
    "            if not column in list_of_aliases:\n",
    "                for reference, aliases in list_of_aliases.items():\n",
    "                    if aliases:\n",
    "                        if column in aliases:\n",
    "                            batch[year].rename(columns={column: reference}, inplace=True)\n",
    "                            print(year, column, 'replaced with', reference)\n",
    "                            stdout.flush()\n",
    "                            break  \n",
    "                else:\n",
    "                    print(year, 'NO ALIAS REGISTERED FOR', column)\n",
    "                    stdout.flush()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def build_overview(batch):\n",
    "    table = []\n",
    "    \n",
    "    for column in get_union_of_columns(batch):\n",
    "        row = {'Column': column}\n",
    "        for year in batch.keys():\n",
    "            row.update({year: column in batch[year].columns})\n",
    "        table.append(row)\n",
    "        \n",
    "    ordered_columns = ['Column']\n",
    "    ordered_columns.extend(sorted(batch.keys()))\n",
    "    \n",
    "    overview = DataFrame(table).reindex_axis(ordered_columns, axis=1)\n",
    "    print('Column mapping overview: done')\n",
    "    return overview"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check expenditure sums\n",
    "\n",
    "There's a little cleaning to do on the amount columns (zeros represented by a dash). Assume thousands are seperated by a comma."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "EXPENDITURE_COLUMNS = [\n",
    "    'MONTO_EJERCIDO', \n",
    "    'MONTO_DEVENGADO', \n",
    "    'MONTO_APROBADO', \n",
    "    'MONTO_PAGADO', \n",
    "    'MONTO_MODIFICADO', \n",
    "    'MONTO_ADEFAS', \n",
    "    'MONTO_EJERCICIO'\n",
    "]\n",
    "count = 0\n",
    "\n",
    "def clean_expenditure_columns(batch):\n",
    "    check_sums = []\n",
    "\n",
    "    for column in EXPENDITURE_COLUMNS:\n",
    "        row = {'Column': column}\n",
    "        \n",
    "        for year in sorted(batch.keys()):\n",
    "            try:\n",
    "                series = batch[year][column]\n",
    "                \n",
    "                # I'm assuming a single '-' represents zero\n",
    "                series = series.apply(lambda x: '0' if x == '-' else x)\n",
    "                try:\n",
    "                    series = series.apply(lambda x: x.replace(',', '') if x is not nan else x)    \n",
    "                except AttributeError:\n",
    "                    if count < 10:\n",
    "                        print(year, column)\n",
    "                batch[year][column] = series.astype(float)\n",
    "                check_sum = batch[year][column].sum()\n",
    "                \n",
    "                print(year, 'cleaned and summed', column, '=', check_sum, 'pesos')\n",
    "                \n",
    "            except KeyError:\n",
    "                check_sum = nan\n",
    "                \n",
    "            row.update({year: check_sum})\n",
    "        \n",
    "        check_sums.append(row)\n",
    "\n",
    "    ordered_columns = ['Column']\n",
    "    ordered_columns.extend(sorted(batch.keys()))\n",
    "    return DataFrame(check_sums).reindex_axis(ordered_columns, axis=1)    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Objeto del Gasto Column split"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from os.path import join\n",
    "\n",
    "def generate_catalog(file):\n",
    "    \n",
    "    catalog_ = {}\n",
    "    catalog_file = ExcelFile(file)\n",
    "    INDEX_COLUMN = 0\n",
    "    \n",
    "    for sheet in catalog_file.sheet_names:\n",
    "        if sheet != 'Concatenated':\n",
    "            name = sheet.lower().replace(' ', '_')\n",
    "            output = join('objeto_del_gasto.catalog', name + '.csv')\n",
    "\n",
    "            df = catalog_file.parse(sheet).dropna()\n",
    "            index = df.columns[INDEX_COLUMN]\n",
    "\n",
    "            df[index] =  df[index].astype(str)\n",
    "            df.set_index(index, inplace=True)\n",
    "            df = df.groupby(df.index).first()\n",
    "            df.sort_index(inplace=True)\n",
    "            \n",
    "            message = 'Loaded catalog {sheet} into \"{name}\" ({nb} lines)'\n",
    "            parameters = dict(sheet=sheet, name=name, nb=len(df))\n",
    "\n",
    "            print(message.format(**parameters))\n",
    "            catalog_[name] = df['DESCRIPCION']\n",
    "    \n",
    "    print()\n",
    "    return catalog_"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Note!__ Years are hard coded in the script below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def split_objeto_del_gasto(batch):\n",
    "    catalog = generate_catalog(CATALOGS_FILE)\n",
    "    missing_in_catalog = []\n",
    "    \n",
    "    def has_digits(n, N):\n",
    "        return not isinstance(n, float) and len(n) >= N \n",
    "            \n",
    "    def lookup(n, table, year):\n",
    "        try:\n",
    "            return catalog[table].loc[n]\n",
    "        except KeyError:\n",
    "            missing_in_catalog.append({'year': year, 'table': table, 'ID': n})\n",
    "            return nan\n",
    "        except TypeError:\n",
    "            # n is nan\n",
    "            return nan\n",
    "    \n",
    "    for year in sorted(batch.keys()):\n",
    "        if year == 2016:\n",
    "            print('Skipping', year, 'because the raw CSV already has the required columns')\n",
    "        \n",
    "        else:\n",
    "            objeto = batch[year]['ID_CONCEPTO'].astype(str)\n",
    "\n",
    "            batch[year]['ID_CAPITULO'] = objeto.apply(lambda x: x[0] + '000' if x not in (nan, 'nan') else nan)\n",
    "            batch[year]['ID_CONCEPTO'] = objeto.apply(lambda x: x[:2] + '00' if x not in (nan, 'nan') else nan)\n",
    "            batch[year]['DESC_CAPITULO'] = batch[year]['ID_CAPITULO'].map(lambda x: lookup(x, 'capitulo', year))  \n",
    "            batch[year]['DESC_CONCEPTO'] = batch[year]['ID_CONCEPTO'].map(lambda x: lookup(x, 'concepto', year))  \n",
    "            \n",
    "            nb_generica_digits = 4 if year in (2008, 2009, 2010) else 3\n",
    "            \n",
    "            # Skip the LAST year of the dataset (currently 2016) it has split columns already\n",
    "            batch[year]['ID_PARTIDA_GENERICA'] = objeto.apply(lambda x: x[:nb_generica_digits] if has_digits(x, 4) else nan)\n",
    "            batch[year]['DESC_PARTIDA_GENERICA'] = batch[year]['ID_PARTIDA_GENERICA'].map(lambda x: lookup(x, 'partida_generica', year))  \n",
    "            \n",
    "            if year not in (2008, 2009, 2010):\n",
    "                batch[year]['ID_PARTIDA_ESPECIFICA'] = objeto.apply(lambda x: x if has_digits(x, 5) else nan)\n",
    "                batch[year]['DESC_PARTIDA_ESPECIFICA'] = batch[year]['ID_PARTIDA_ESPECIFICA'].map(lambda x: lookup(x, 'partida_específica', year) if has_digits(x, 5) else nan)  \n",
    "            else:\n",
    "                batch[year]['ID_PARTIDA_ESPECIFICA'] = nan\n",
    "                batch[year]['DESC_PARTIDA_ESPECIFICA'] = nan\n",
    "\n",
    "            print(year, 'broke down \"Objeto del Gasto\" column')\n",
    "        \n",
    "    return DataFrame(missing_in_catalog).drop_duplicates(['ID', 'table'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Prefix IDs \n",
    "Disambiguating sub-categories may require prefixing their IDs with their parents' IDs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def prefix_ids(batch):\n",
    "    for year in batch.keys():       \n",
    "        for hierarchy, levels in HIERARCHIES.items():\n",
    "            prefix = batch[year]['CICLO'].apply(lambda x: '')\n",
    "            for n, level in enumerate(levels):\n",
    "                dash = '.' if n > 0 else ''\n",
    "                prefix = prefix + dash + batch[year][level]  \n",
    "                batch[year][level] = prefix\n",
    "                \n",
    "                print(year, 'prefixed', hierarchy, 'level', n, level)\n",
    "                stdout.flush()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove unused columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def remove_unused_columns(batch):\n",
    "    for year, budget in batch.items():\n",
    "        for column in REMOVE_OUTPUT_COLUMNS:\n",
    "            try:\n",
    "                del budget[column]\n",
    "                print(year, 'deleted', column)\n",
    "            except KeyError:\n",
    "                print(column, ': no such column to delete')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add descriptions and titles to datapackage.json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  Pipeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def do_pipeline():\n",
    "\n",
    "    def echo_section(section):\n",
    "        print('\\n', section, '\\n')\n",
    "\n",
    "    echo_section('Update datapackage.json')\n",
    "    update_datapackage()\n",
    "\n",
    "    echo_section('Loading files')\n",
    "    datasets = load_csv_files()\n",
    "    \n",
    "    echo_section('Delete empty columns')\n",
    "    delete_empty_columns(datasets)\n",
    "\n",
    "    echo_section('Stripping padding from cells')\n",
    "    strip_cell_padding(datasets)\n",
    "    \n",
    "    echo_section('Counting duplicate lines (NOT de-duplicating)')\n",
    "    count_duplicates(datasets)\n",
    "    \n",
    "    echo_section('Mapping column to aliases')\n",
    "    map_columns_to_aliases(datasets, COLUMN_ALIASES)\n",
    "\n",
    "    echo_section('Counting missing values')\n",
    "    missing_values_report, bad_records = count_missing_values(datasets)\n",
    "    \n",
    "    echo_section('Building column mapping overview')\n",
    "    column_mapping_report = build_overview(datasets)\n",
    "    \n",
    "    echo_section('Cleaning expenditure columns')\n",
    "    sums_report = clean_expenditure_columns(datasets)\n",
    "    \n",
    "    echo_section('Breaking down Objeto del Gasto column')\n",
    "    missing_catalog_ids = split_objeto_del_gasto(datasets)\n",
    "        \n",
    "    echo_section('Prefixing IDs of certain category hierarchies')\n",
    "    prefix_ids(datasets)\n",
    "\n",
    "    echo_section('Removing unused columns')\n",
    "    remove_unused_columns(datasets)\n",
    "\n",
    "    echo_section('Saving pipeline configuration')\n",
    "\n",
    "    reports_file = join(ITERATION_FOLDER, BASENAME + '.reports.xlsx')\n",
    "    writer = ExcelWriter(reports_file)    \n",
    "    missing_values_report.to_excel(writer, 'missing values', encoding='utf-8', index=False)\n",
    "    column_mapping_report.to_excel(writer, 'column mapping', encoding='utf-8', index=False)\n",
    "    sums_report.to_excel(writer, 'check sums', encoding='utf-8', index=False)\n",
    "    missing_catalog_ids.to_excel(writer, 'missing_catalog_IDs', encoding='utf-8', index=False)    \n",
    "    print('Saved 4 reports to', reports_file)    \n",
    "\n",
    "    aliases_file = join(ITERATION_FOLDER, BASENAME + '.aliases.json')\n",
    "    inputs_file = join(ITERATION_FOLDER, BASENAME + '.inputs.json')\n",
    "    levels_file = join(ITERATION_FOLDER, BASENAME + '.levels.json')\n",
    "    bad_records_file = join(ITERATION_FOLDER, BASENAME + '.missing.json')\n",
    "\n",
    "    with open(bad_records_file, 'w+') as json:\n",
    "        json.write(dumps(bad_records, indent=4))\n",
    "        \n",
    "    with open(aliases_file, 'w+') as json:\n",
    "        json.write(dumps(COLUMN_ALIASES, indent=4))\n",
    "        \n",
    "    with open(levels_file, 'w+') as json:\n",
    "        json.write(dumps(HIERARCHIES, indent=4))\n",
    "        \n",
    "    with open(inputs_file, 'w+') as json:\n",
    "        json.write(dumps(INPUT_FILES, indent=4))\n",
    "    \n",
    "    print('Saved input configuration to', inputs_file)    \n",
    "    print('Saved column aliases to', aliases_file) \n",
    "    print('Saved bad records (those with empty cells) to', bad_records_file)    \n",
    "    print('Saved hierarchy levels used for prefixing to', levels_file) \n",
    "    \n",
    "    echo_section('Pipeline run \"%s\" done' % ITERATION_LABEL)\n",
    "\n",
    "    return datasets, missing_catalog_ids, column_mapping_report, missing_values_report, sums_report"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run the pipeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      " Update datapackage.json \n",
      "\n",
      "updated datapackage title and description for CICLO\n",
      "updated datapackage title and description for DESC_AI\n",
      "updated datapackage title and description for DESC_CAPITULO\n",
      "updated datapackage title and description for DESC_CONCEPTO\n",
      "updated datapackage title and description for ENTIDAD_FEDERATIVA\n",
      "updated datapackage title and description for DESC_FF\n",
      "updated datapackage title and description for DESC_FUNCION\n",
      "updated datapackage title and description for DESC_GPO_FUNCIONAL\n",
      "updated datapackage title and description for DESC_MODALIDAD\n",
      "updated datapackage title and description for DESC_PARTIDA_ESPECIFICA\n",
      "updated datapackage title and description for DESC_PARTIDA_GENERICA\n",
      "updated datapackage title and description for DESC_PP\n",
      "updated datapackage title and description for DESC_RAMO\n",
      "updated datapackage title and description for DESC_SUBFUNCION\n",
      "updated datapackage title and description for DESC_TIPOGASTO\n",
      "updated datapackage title and description for DESC_UR\n",
      "updated datapackage title and description for ID_ENTIDAD_FEDERATIVA\n",
      "updated datapackage title and description for GPO_FUNCIONAL\n",
      "updated datapackage title and description for ID_AI\n",
      "updated datapackage title and description for ID_CAPITULO\n",
      "updated datapackage title and description for ID_CONCEPTO\n",
      "updated datapackage title and description for ID_FF\n",
      "updated datapackage title and description for ID_FUNCION\n",
      "updated datapackage title and description for ID_MODALIDAD\n",
      "updated datapackage title and description for ID_CLAVE_CARTERA\n",
      "updated datapackage title and description for ID_RAMO\n",
      "updated datapackage title and description for ID_SUBFUNCION\n",
      "updated datapackage title and description for ID_TIPOGASTO\n",
      "updated datapackage title and description for ID_UR\n",
      "updated datapackage title and description for MONTO_ADEFAS\n",
      "updated datapackage title and description for MONTO_APROBADO\n",
      "updated datapackage title and description for MONTO_DEVENGADO\n",
      "updated datapackage title and description for MONTO_EJERCICIO\n",
      "updated datapackage title and description for MONTO_EJERCIDO\n",
      "updated datapackage title and description for MONTO_MODIFICADO\n",
      "updated datapackage title and description for MONTO_PAGADO\n",
      "updated datapackage title and description for ID_PARTIDA_ESPECIFICA\n",
      "updated datapackage title and description for ID_PARTIDA_GENERICA\n",
      "updated datapackage title and description for ID_PP\n",
      "checked columns consistency for datapackage.default.json, columns.aliases.json and columns.descriptions.csv: okay!\n",
      "copied datapackage.default.json\n",
      "\n",
      " Loading files \n",
      "\n",
      "Loaded Cuenta_Publica_2008.csv with encoding windows-1252\n",
      "Loaded Cuenta_Publica_2009.csv with encoding windows-1252\n",
      "Loaded Cuenta_Publica_2010.csv with encoding windows-1252\n",
      "Loaded Cuenta_Publica_2011.csv with encoding windows-1252\n",
      "Loaded Cuenta_Publica_2012.csv with encoding windows-1252\n",
      "Loaded CP_2013.csv with encoding windows-1252\n",
      "Loaded CP_2014.csv with encoding windows-1252\n",
      "Loaded CP_2015.csv with encoding windows-1252\n",
      "Loaded PEF_AC01_2t_2016.csv with encoding windows-1252\n",
      "\n",
      "\n",
      " Delete empty columns \n",
      "\n",
      "2009 Unnamed: 25 deleted\n",
      "2009 Unnamed: 26 deleted\n",
      "2009 Unnamed: 27 deleted\n",
      "2009 Unnamed: 28 deleted\n",
      "2009 Unnamed: 29 deleted\n",
      "2009 Unnamed: 30 deleted\n",
      "2009 Unnamed: 31 deleted\n",
      "2009 Unnamed: 32 deleted\n",
      "2009 Unnamed: 33 deleted\n",
      "2009 Unnamed: 34 deleted\n",
      "2009 Unnamed: 35 deleted\n",
      "2009 Unnamed: 36 deleted\n",
      "2009 Unnamed: 37 deleted\n",
      "2009 Unnamed: 38 deleted\n",
      "2009 Unnamed: 39 deleted\n",
      "2011 Unnamed: 25 deleted\n",
      "2011 Unnamed: 26 deleted\n",
      "2011 Unnamed: 27 deleted\n",
      "2011 Unnamed: 28 deleted\n",
      "2011 Unnamed: 29 deleted\n",
      "2011 Unnamed: 30 deleted\n",
      "2011 Unnamed: 31 deleted\n",
      "2011 Unnamed: 32 deleted\n",
      "2011 Unnamed: 33 deleted\n",
      "2011 Unnamed: 34 deleted\n",
      "2011 Unnamed: 35 deleted\n",
      "2011 Unnamed: 36 deleted\n",
      "2011 Unnamed: 37 deleted\n",
      "2011 Unnamed: 38 deleted\n",
      "2011 Unnamed: 39 deleted\n",
      "2011 Unnamed: 40 deleted\n",
      "2011 Unnamed: 41 deleted\n",
      "\n",
      " Stripping padding from cells \n",
      "\n",
      "2008 stripped cell paddings\n",
      "2009 stripped cell paddings\n",
      "2010 stripped cell paddings\n",
      "2011 stripped cell paddings\n",
      "2012 stripped cell paddings\n",
      "2013 stripped cell paddings\n",
      "2014 stripped cell paddings\n",
      "2015 stripped cell paddings\n",
      "2016 stripped cell paddings\n",
      "\n",
      " Counting duplicate lines (NOT de-duplicating) \n",
      "\n",
      "2008 found 0 duplicate lines\n",
      "2009 found 218 duplicate lines\n",
      "2010 found 34 duplicate lines\n",
      "2011 found 37 duplicate lines\n",
      "2012 found 558 duplicate lines\n",
      "2013 found 0 duplicate lines\n",
      "2014 found 47 duplicate lines\n",
      "2015 found 16 duplicate lines\n",
      "2016 found 20 duplicate lines\n",
      "\n",
      " Mapping column to aliases \n",
      "\n",
      "2008 Actividad Institucional replaced with ID_AI\n",
      "2008 Aprobado replaced with MONTO_APROBADO\n",
      "2008 Ciclo replaced with CICLO\n",
      "2008 Descripción de Finalidad replaced with DESC_GPO_FUNCIONAL\n",
      "2008 Descripción de Fuente de Financiamiento replaced with DESC_FF\n",
      "2008 Descripción de Función replaced with DESC_FUNCION\n",
      "2008 Descripción de Objeto del Gasto replaced with DESC_CONCEPTO\n",
      "2008 Descripción de Programa Presupuestario replaced with DESC_PP\n",
      "2008 Descripción de Ramo replaced with DESC_RAMO\n",
      "2008 Descripción de Subfunción replaced with DESC_SUBFUNCION\n",
      "2008 Descripción de Tipo de Gasto replaced with DESC_TIPOGASTO\n",
      "2008 Descripción de Unidad Responsable replaced with DESC_UR\n",
      "2008 Descripción de la Actividad Institucional replaced with DESC_AI\n",
      "2008 Descripción de la modalidad del programa presupuestario replaced with DESC_MODALIDAD\n",
      "2008 Ejercido replaced with MONTO_EJERCIDO\n",
      "2008 Finalidad replaced with GPO_FUNCIONAL\n",
      "2008 Fuente de Financiamiento replaced with ID_FF\n",
      "2008 Función replaced with ID_FUNCION\n",
      "2008 Modalidad del Programa presupuestario replaced with ID_MODALIDAD\n",
      "2008 Objeto del Gasto replaced with ID_CONCEPTO\n",
      "2008 Programa Presupuestario replaced with ID_PP\n",
      "2008 Ramo replaced with ID_RAMO\n",
      "2008 Subfunción replaced with ID_SUBFUNCION\n",
      "2008 Tipo de Gasto replaced with ID_TIPOGASTO\n",
      "2008 Unidad Responsable replaced with ID_UR\n",
      "2009 Actividad Institucional replaced with ID_AI\n",
      "2009 Aprobado replaced with MONTO_APROBADO\n",
      "2009 Ciclo replaced with CICLO\n",
      "2009 Descripción de Finalidad replaced with DESC_GPO_FUNCIONAL\n",
      "2009 Descripción de Fuente de Financiamiento replaced with DESC_FF\n",
      "2009 Descripción de Función replaced with DESC_FUNCION\n",
      "2009 Descripción de Objeto del Gasto replaced with DESC_CONCEPTO\n",
      "2009 Descripción de Programa Presupuestario replaced with DESC_PP\n",
      "2009 Descripción de Ramo replaced with DESC_RAMO\n",
      "2009 Descripción de Subfunción replaced with DESC_SUBFUNCION\n",
      "2009 Descripción de Tipo de Gasto replaced with DESC_TIPOGASTO\n",
      "2009 Descripción de Unidad Responsable replaced with DESC_UR\n",
      "2009 Descripción de la Actividad Institucional replaced with DESC_AI\n",
      "2009 Descripción de la modalidad del programa presupuestario replaced with DESC_MODALIDAD\n",
      "2009 Ejercido replaced with MONTO_EJERCIDO\n",
      "2009 Finalidad replaced with GPO_FUNCIONAL\n",
      "2009 Fuente de Financiamiento replaced with ID_FF\n",
      "2009 Función replaced with ID_FUNCION\n",
      "2009 Modalidad del Programa presupuestario replaced with ID_MODALIDAD\n",
      "2009 Objeto del Gasto replaced with ID_CONCEPTO\n",
      "2009 Programa Presupuestario replaced with ID_PP\n",
      "2009 Ramo replaced with ID_RAMO\n",
      "2009 Subfunción replaced with ID_SUBFUNCION\n",
      "2009 Tipo de Gasto replaced with ID_TIPOGASTO\n",
      "2009 Unidad Responsable replaced with ID_UR\n",
      "2010 Actividad Institucional replaced with ID_AI\n",
      "2010 Aprobado replaced with MONTO_APROBADO\n",
      "2010 Ciclo replaced with CICLO\n",
      "2010 Descripción de Finalidad replaced with DESC_GPO_FUNCIONAL\n",
      "2010 Descripción de Fuente de Financiamiento replaced with DESC_FF\n",
      "2010 Descripción de Función replaced with DESC_FUNCION\n",
      "2010 Descripción de Objeto del Gasto replaced with DESC_CONCEPTO\n",
      "2010 Descripción de Programa Presupuestario replaced with DESC_PP\n",
      "2010 Descripción de Ramo replaced with DESC_RAMO\n",
      "2010 Descripción de Subfunción replaced with DESC_SUBFUNCION\n",
      "2010 Descripción de Tipo de Gasto replaced with DESC_TIPOGASTO\n",
      "2010 Descripción de Unidad Responsable replaced with DESC_UR\n",
      "2010 Descripción de la Actividad Institucional replaced with DESC_AI\n",
      "2010 Descripción de la modalidad del programa presupuestario replaced with DESC_MODALIDAD\n",
      "2010 Ejercido replaced with MONTO_EJERCIDO\n",
      "2010 Finalidad replaced with GPO_FUNCIONAL\n",
      "2010 Fuente de Financiamiento replaced with ID_FF\n",
      "2010 Función replaced with ID_FUNCION\n",
      "2010 Modalidad del Programa presupuestario replaced with ID_MODALIDAD\n",
      "2010 Objeto del Gasto replaced with ID_CONCEPTO\n",
      "2010 Programa Presupuestario replaced with ID_PP\n",
      "2010 Ramo replaced with ID_RAMO\n",
      "2010 Subfunción replaced with ID_SUBFUNCION\n",
      "2010 Tipo de Gasto replaced with ID_TIPOGASTO\n",
      "2010 Unidad Responsable replaced with ID_UR\n",
      "2011 Actividad Institucional replaced with ID_AI\n",
      "2011 Aprobado replaced with MONTO_APROBADO\n",
      "2011 Ciclo replaced with CICLO\n",
      "2011 Descripción de Finalidad replaced with DESC_GPO_FUNCIONAL\n",
      "2011 Descripción de Fuente de Financiamiento replaced with DESC_FF\n",
      "2011 Descripción de Función replaced with DESC_FUNCION\n",
      "2011 Descripción de Objeto del Gasto replaced with DESC_CONCEPTO\n",
      "2011 Descripción de Programa Presupuestario replaced with DESC_PP\n",
      "2011 Descripción de Ramo replaced with DESC_RAMO\n",
      "2011 Descripción de Subfunción replaced with DESC_SUBFUNCION\n",
      "2011 Descripción de Tipo de Gasto replaced with DESC_TIPOGASTO\n",
      "2011 Descripción de Unidad Responsable replaced with DESC_UR\n",
      "2011 Descripción de la Actividad Institucional replaced with DESC_AI\n",
      "2011 Descripción de la modalidad del programa presupuestario replaced with DESC_MODALIDAD\n",
      "2011 Ejercido replaced with MONTO_EJERCIDO\n",
      "2011 Finalidad replaced with GPO_FUNCIONAL\n",
      "2011 Fuente de Financiamiento replaced with ID_FF\n",
      "2011 Función replaced with ID_FUNCION\n",
      "2011 Modalidad del Programa presupuestario replaced with ID_MODALIDAD\n",
      "2011 Objeto del Gasto replaced with ID_CONCEPTO\n",
      "2011 Programa Presupuestario replaced with ID_PP\n",
      "2011 Ramo replaced with ID_RAMO\n",
      "2011 Subfunción replaced with ID_SUBFUNCION\n",
      "2011 Tipo de Gasto replaced with ID_TIPOGASTO\n",
      "2011 Unidad Responsable replaced with ID_UR\n",
      "2012 Actividad Institucional replaced with ID_AI\n",
      "2012 Aprobado replaced with MONTO_APROBADO\n",
      "2012 Ciclo replaced with CICLO\n",
      "2012 Clave de cartera replaced with ID_CLAVE_CARTERA\n",
      "2012 Descripción de Finalidad replaced with DESC_GPO_FUNCIONAL\n",
      "2012 Descripción de Fuente de Financiamiento replaced with DESC_FF\n",
      "2012 Descripción de Función replaced with DESC_FUNCION\n",
      "2012 Descripción de Objeto del Gasto replaced with DESC_CONCEPTO\n",
      "2012 Descripción de Programa Presupuestario replaced with DESC_PP\n",
      "2012 Descripción de Ramo replaced with DESC_RAMO\n",
      "2012 Descripción de Subfunción replaced with DESC_SUBFUNCION\n",
      "2012 Descripción de Tipo de Gasto replaced with DESC_TIPOGASTO\n",
      "2012 Descripción de Unidad Responsable replaced with DESC_UR\n",
      "2012 Descripción de la Actividad Institucional replaced with DESC_AI\n",
      "2012 Descripción de la entidad federativa replaced with ENTIDAD_FEDERATIVA\n",
      "2012 Descripción de la modalidad del programa presupuestario replaced with DESC_MODALIDAD\n",
      "2012 Ejercido replaced with MONTO_EJERCIDO\n",
      "2012 Entidad Federativa replaced with ID_ENTIDAD_FEDERATIVA\n",
      "2012 Finalidad replaced with GPO_FUNCIONAL\n",
      "2012 Fuente de Financiamiento replaced with ID_FF\n",
      "2012 Función replaced with ID_FUNCION\n",
      "2012 Modalidad del Programa presupuestario replaced with ID_MODALIDAD\n",
      "2012 Objeto del Gasto replaced with ID_CONCEPTO\n",
      "2012 Programa Presupuestario replaced with ID_PP\n",
      "2012 Ramo replaced with ID_RAMO\n",
      "2012 Subfunción replaced with ID_SUBFUNCION\n",
      "2012 Tipo de Gasto replaced with ID_TIPOGASTO\n",
      "2012 Unidad Responsable replaced with ID_UR\n",
      "2013 Actividad Institucional replaced with ID_AI\n",
      "2013 Aprobado replaced with MONTO_APROBADO\n",
      "2013 Ciclo replaced with CICLO\n",
      "2013 Clave de cartera replaced with ID_CLAVE_CARTERA\n",
      "2013 Descripción de Finalidad replaced with DESC_GPO_FUNCIONAL\n",
      "2013 Descripción de Fuente de Financiamiento replaced with DESC_FF\n",
      "2013 Descripción de Función replaced with DESC_FUNCION\n",
      "2013 Descripción de Objeto del Gasto replaced with DESC_CONCEPTO\n",
      "2013 Descripción de Programa Presupuestario replaced with DESC_PP\n",
      "2013 Descripción de Ramo replaced with DESC_RAMO\n",
      "2013 Descripción de Subfunción replaced with DESC_SUBFUNCION\n",
      "2013 Descripción de Tipo de Gasto replaced with DESC_TIPOGASTO\n",
      "2013 Descripción de Unidad Responsable replaced with DESC_UR\n",
      "2013 Descripción de la Actividad Institucional replaced with DESC_AI\n",
      "2013 Descripción de la entidad federativa replaced with ENTIDAD_FEDERATIVA\n",
      "2013 Descripción de la modalidad del programa presupuestario replaced with DESC_MODALIDAD\n",
      "2013 Ejercicio replaced with MONTO_EJERCICIO\n",
      "2013 Entidad Federativa replaced with ID_ENTIDAD_FEDERATIVA\n",
      "2013 Finalidad replaced with GPO_FUNCIONAL\n",
      "2013 Fuente de Financiamiento replaced with ID_FF\n",
      "2013 Función replaced with ID_FUNCION\n",
      "2013 Modalidad del Programa presupuestario replaced with ID_MODALIDAD\n",
      "2013 Objeto del Gasto replaced with ID_CONCEPTO\n",
      "2013 Programa Presupuestario replaced with ID_PP\n",
      "2013 Ramo replaced with ID_RAMO\n",
      "2013 Subfunción replaced with ID_SUBFUNCION\n",
      "2013 Tipo de Gasto replaced with ID_TIPOGASTO\n",
      "2013 Unidad Responsable replaced with ID_UR\n",
      "2014 ADEFAS replaced with MONTO_ADEFAS\n",
      "2014 Actividad Institucional replaced with ID_AI\n",
      "2014 Aprobado replaced with MONTO_APROBADO\n",
      "2014 Ciclo replaced with CICLO\n",
      "2014 Clave de cartera replaced with ID_CLAVE_CARTERA\n",
      "2014 Descripción de Fuente de Financiamiento replaced with DESC_FF\n",
      "2014 Descripción de Función replaced with DESC_FUNCION\n",
      "2014 Descripción de Grupo Funcional replaced with DESC_GPO_FUNCIONAL\n",
      "2014 Descripción de Objeto del Gasto replaced with DESC_CONCEPTO\n",
      "2014 Descripción de Programa Presupuestario replaced with DESC_PP\n",
      "2014 Descripción de Ramo replaced with DESC_RAMO\n",
      "2014 Descripción de Subfunción replaced with DESC_SUBFUNCION\n",
      "2014 Descripción de Tipo de Gasto replaced with DESC_TIPOGASTO\n",
      "2014 Descripción de Unidad Responsable replaced with DESC_UR\n",
      "2014 Descripción de la Actividad Institucional replaced with DESC_AI\n",
      "2014 Descripción de la entidad federativa replaced with ENTIDAD_FEDERATIVA\n",
      "2014 Descripción de la modalidad del programa presupuestario replaced with DESC_MODALIDAD\n",
      "2014 Devengado replaced with MONTO_DEVENGADO\n",
      "2014 Ejercicio replaced with MONTO_EJERCICIO\n",
      "2014 Entidad Federativa replaced with ID_ENTIDAD_FEDERATIVA\n",
      "2014 Fuente de Financiamiento replaced with ID_FF\n",
      "2014 Función replaced with ID_FUNCION\n",
      "2014 Grupo Funcional replaced with GPO_FUNCIONAL\n",
      "2014 Modalidad del Programa presupuestario replaced with ID_MODALIDAD\n",
      "2014 Modificado replaced with MONTO_MODIFICADO\n",
      "2014 Objeto del Gasto replaced with ID_CONCEPTO\n",
      "2014 Pagado replaced with MONTO_PAGADO\n",
      "2014 Programa Presupuestario replaced with ID_PP\n",
      "2014 Ramo replaced with ID_RAMO\n",
      "2014 Subfunción replaced with ID_SUBFUNCION\n",
      "2014 Tipo de Gasto replaced with ID_TIPOGASTO\n",
      "2014 Unidad Responsable replaced with ID_UR\n",
      "2015 Actividad Institucional replaced with ID_AI\n",
      "2015 Adefas replaced with MONTO_ADEFAS\n",
      "2015 Aprobado replaced with MONTO_APROBADO\n",
      "2015 Ciclo replaced with CICLO\n",
      "2015 Clave de cartera replaced with ID_CLAVE_CARTERA\n",
      "2015 Descripción de Fuente de Financiamiento replaced with DESC_FF\n",
      "2015 Descripción de Función replaced with DESC_FUNCION\n",
      "2015 Descripción de Grupo Funcional replaced with DESC_GPO_FUNCIONAL\n",
      "2015 Descripción de Objeto del Gasto replaced with DESC_CONCEPTO\n",
      "2015 Descripción de Programa Presupuestario replaced with DESC_PP\n",
      "2015 Descripción de Ramo replaced with DESC_RAMO\n",
      "2015 Descripción de Subfunción replaced with DESC_SUBFUNCION\n",
      "2015 Descripción de Tipo de Gasto replaced with DESC_TIPOGASTO\n",
      "2015 Descripción de Unidad Responsable replaced with DESC_UR\n",
      "2015 Descripción de la Actividad Institucional replaced with DESC_AI\n",
      "2015 Descripción de la entidad federativa replaced with ENTIDAD_FEDERATIVA\n",
      "2015 Descripción de la modalidad del programa presupuestario replaced with DESC_MODALIDAD\n",
      "2015 Devengado replaced with MONTO_DEVENGADO\n",
      "2015 Ejercicio replaced with MONTO_EJERCICIO\n",
      "2015 Entidad Federativa replaced with ID_ENTIDAD_FEDERATIVA\n",
      "2015 Fuente de Financiamiento replaced with ID_FF\n",
      "2015 Función replaced with ID_FUNCION\n",
      "2015 Grupo Funcional replaced with GPO_FUNCIONAL\n",
      "2015 Modalidad del Programa presupuestario replaced with ID_MODALIDAD\n",
      "2015 Modificado replaced with MONTO_MODIFICADO\n",
      "2015 Objeto del Gasto replaced with ID_CONCEPTO\n",
      "2015 Pagado replaced with MONTO_PAGADO\n",
      "2015 Programa Presupuestario replaced with ID_PP\n",
      "2015 Ramo replaced with ID_RAMO\n",
      "2015 Subfunción replaced with ID_SUBFUNCION\n",
      "2015 Tipo de Gasto replaced with ID_TIPOGASTO\n",
      "2015 Unidad Responsable replaced with ID_UR\n",
      "2016 Actividad Institucional replaced with ID_AI\n",
      "2016 Aprobado replaced with MONTO_APROBADO\n",
      "2016 Ciclo replaced with CICLO\n",
      "2016 Clave de cartera replaced with ID_CLAVE_CARTERA\n",
      "2016 Descripción de Fuente de Financiamiento replaced with DESC_FF\n",
      "2016 Descripción de Función replaced with DESC_FUNCION\n",
      "2016 Descripción de Grupo Funcional replaced with DESC_GPO_FUNCIONAL\n",
      "2016 Descripción de Programa Presupuestario replaced with DESC_PP\n",
      "2016 Descripción de Ramo replaced with DESC_RAMO\n",
      "2016 Descripción de Subfunción replaced with DESC_SUBFUNCION\n",
      "2016 Descripción de Tipo de Gasto replaced with DESC_TIPOGASTO\n",
      "2016 Descripción de Unidad Responsable replaced with DESC_UR\n",
      "2016 Descripción de la Actividad Institucional replaced with DESC_AI\n",
      "2016 Descripción de la entidad federativa replaced with ENTIDAD_FEDERATIVA\n",
      "2016 Descripción de la modalidad del programa presupuestario replaced with DESC_MODALIDAD\n",
      "2016 Ejercido replaced with MONTO_EJERCIDO\n",
      "2016 Entidad Federativa replaced with ID_ENTIDAD_FEDERATIVA\n",
      "2016 Fuente de Financiamiento replaced with ID_FF\n",
      "2016 Función replaced with ID_FUNCION\n",
      "2016 Grupo Funcional replaced with GPO_FUNCIONAL\n",
      "2016 Modalidad del Programa presupuestario replaced with ID_MODALIDAD\n",
      "2016 Pagado replaced with MONTO_PAGADO\n",
      "2016 Programa Presupuestario replaced with ID_PP\n",
      "2016 Ramo replaced with ID_RAMO\n",
      "2016 Subfunción replaced with ID_SUBFUNCION\n",
      "2016 Tipo de Gasto replaced with ID_TIPOGASTO\n",
      "2016 Unidad Responsable replaced with ID_UR\n",
      "\n",
      " Counting missing values \n",
      "\n",
      "2012 found 1 missing values in MONTO_APROBADO\n",
      "2012 found 172 missing values in ENTIDAD_FEDERATIVA\n",
      "2016 found 170 missing values in MONTO_PAGADO\n",
      "2016 found 170 missing values in MONTO_EJERCIDO\n",
      "2008 found 2 missing values in DESC_CONCEPTO\n",
      "2009 found 2 missing values in DESC_CONCEPTO\n",
      "2012 found 28 missing values in DESC_CONCEPTO\n",
      "\n",
      " Building column mapping overview \n",
      "\n",
      "Column mapping overview: done\n",
      "\n",
      " Cleaning expenditure columns \n",
      "\n",
      "2008 cleaned and summed MONTO_EJERCIDO = 2.5766918301e+12 pesos\n",
      "2009 cleaned and summed MONTO_EJERCIDO = 2.29608578324e+12 pesos\n",
      "2010 cleaned and summed MONTO_EJERCIDO = 2.47409995292e+12 pesos\n",
      "2011 cleaned and summed MONTO_EJERCIDO = 2.7173722131e+12 pesos\n",
      "2012 cleaned and summed MONTO_EJERCIDO = 2.89633102379e+12 pesos\n",
      "2016 cleaned and summed MONTO_EJERCIDO = 2.16093940434e+12 pesos\n",
      "2014 cleaned and summed MONTO_DEVENGADO = 5.07680959218e+12 pesos\n",
      "2015 cleaned and summed MONTO_DEVENGADO = 5.50898738409e+12 pesos\n",
      "2008 cleaned and summed MONTO_APROBADO = 1.99235625716e+12 pesos\n",
      "2009 cleaned and summed MONTO_APROBADO = 2.28971502663e+12 pesos\n",
      "2010 cleaned and summed MONTO_APROBADO = 2.37691503329e+12 pesos\n",
      "2011 cleaned and summed MONTO_APROBADO = 2.56019587954e+12 pesos\n",
      "2012 cleaned and summed MONTO_APROBADO = 2.75486845421e+12 pesos\n",
      "2013 cleaned and summed MONTO_APROBADO = 4.32261895107e+12 pesos\n",
      "2014 cleaned and summed MONTO_APROBADO = 4.90540104801e+12 pesos\n",
      "2015 cleaned and summed MONTO_APROBADO = 5.13844187618e+12 pesos\n",
      "2016 cleaned and summed MONTO_APROBADO = 5.29712624151e+12 pesos\n",
      "2014 cleaned and summed MONTO_PAGADO = 4.97271188823e+12 pesos\n",
      "2015 cleaned and summed MONTO_PAGADO = 5.36665236595e+12 pesos\n",
      "2016 cleaned and summed MONTO_PAGADO = 2.13767909961e+12 pesos\n",
      "2014 cleaned and summed MONTO_MODIFICADO = 5.01398959052e+12 pesos\n",
      "2015 cleaned and summed MONTO_MODIFICADO = 5.39860796016e+12 pesos\n",
      "2014 cleaned and summed MONTO_ADEFAS = 36941614665.9 pesos\n",
      "2015 cleaned and summed MONTO_ADEFAS = 31122648942.8 pesos\n",
      "2013 cleaned and summed MONTO_EJERCICIO = 4.6176176865e+12 pesos\n",
      "2014 cleaned and summed MONTO_EJERCICIO = 5.01087684943e+12 pesos\n",
      "2015 cleaned and summed MONTO_EJERCICIO = 5.39901795156e+12 pesos\n",
      "\n",
      " Breaking down Objeto del Gasto column \n",
      "\n",
      "Loaded catalog CAPITULO into \"capitulo\" (9 lines)\n",
      "Loaded catalog CONCEPTO into \"concepto\" (63 lines)\n",
      "Loaded catalog PARTIDA GENERICA into \"partida_generica\" (866 lines)\n",
      "Loaded catalog PARTIDA ESPECÍFICA into \"partida_específica\" (668 lines)\n",
      "\n",
      "2008 broke down \"Objeto del Gasto\" column\n",
      "2009 broke down \"Objeto del Gasto\" column\n",
      "2010 broke down \"Objeto del Gasto\" column\n",
      "2011 broke down \"Objeto del Gasto\" column\n",
      "2012 broke down \"Objeto del Gasto\" column\n",
      "2013 broke down \"Objeto del Gasto\" column\n",
      "2014 broke down \"Objeto del Gasto\" column\n",
      "2015 broke down \"Objeto del Gasto\" column\n",
      "Skipping 2016 because the raw CSV already has the required columns\n",
      "\n",
      " Prefixing IDs of certain category hierarchies \n",
      "\n",
      "2016 prefixed functional level 0 GPO_FUNCIONAL\n",
      "2016 prefixed functional level 1 ID_FUNCION\n",
      "2016 prefixed functional level 2 ID_SUBFUNCION\n",
      "2016 prefixed functional level 3 ID_AI\n",
      "2016 prefixed administrative level 0 ID_RAMO\n",
      "2016 prefixed administrative level 1 ID_UR\n",
      "2016 prefixed administrative level 2 ID_PP\n",
      "2016 prefixed administrative level 3 ID_MODALIDAD\n",
      "2008 prefixed functional level 0 GPO_FUNCIONAL\n",
      "2008 prefixed functional level 1 ID_FUNCION\n",
      "2008 prefixed functional level 2 ID_SUBFUNCION\n",
      "2008 prefixed functional level 3 ID_AI\n",
      "2008 prefixed administrative level 0 ID_RAMO\n",
      "2008 prefixed administrative level 1 ID_UR\n",
      "2008 prefixed administrative level 2 ID_PP\n",
      "2008 prefixed administrative level 3 ID_MODALIDAD\n",
      "2009 prefixed functional level 0 GPO_FUNCIONAL\n",
      "2009 prefixed functional level 1 ID_FUNCION\n",
      "2009 prefixed functional level 2 ID_SUBFUNCION\n",
      "2009 prefixed functional level 3 ID_AI\n",
      "2009 prefixed administrative level 0 ID_RAMO\n",
      "2009 prefixed administrative level 1 ID_UR\n",
      "2009 prefixed administrative level 2 ID_PP\n",
      "2009 prefixed administrative level 3 ID_MODALIDAD\n",
      "2010 prefixed functional level 0 GPO_FUNCIONAL\n",
      "2010 prefixed functional level 1 ID_FUNCION\n",
      "2010 prefixed functional level 2 ID_SUBFUNCION\n",
      "2010 prefixed functional level 3 ID_AI\n",
      "2010 prefixed administrative level 0 ID_RAMO\n",
      "2010 prefixed administrative level 1 ID_UR\n",
      "2010 prefixed administrative level 2 ID_PP\n",
      "2010 prefixed administrative level 3 ID_MODALIDAD\n",
      "2011 prefixed functional level 0 GPO_FUNCIONAL\n",
      "2011 prefixed functional level 1 ID_FUNCION\n",
      "2011 prefixed functional level 2 ID_SUBFUNCION\n",
      "2011 prefixed functional level 3 ID_AI\n",
      "2011 prefixed administrative level 0 ID_RAMO\n",
      "2011 prefixed administrative level 1 ID_UR\n",
      "2011 prefixed administrative level 2 ID_PP\n",
      "2011 prefixed administrative level 3 ID_MODALIDAD\n",
      "2012 prefixed functional level 0 GPO_FUNCIONAL\n",
      "2012 prefixed functional level 1 ID_FUNCION\n",
      "2012 prefixed functional level 2 ID_SUBFUNCION\n",
      "2012 prefixed functional level 3 ID_AI\n",
      "2012 prefixed administrative level 0 ID_RAMO\n",
      "2012 prefixed administrative level 1 ID_UR\n",
      "2012 prefixed administrative level 2 ID_PP\n",
      "2012 prefixed administrative level 3 ID_MODALIDAD\n",
      "2013 prefixed functional level 0 GPO_FUNCIONAL\n",
      "2013 prefixed functional level 1 ID_FUNCION\n",
      "2013 prefixed functional level 2 ID_SUBFUNCION\n",
      "2013 prefixed functional level 3 ID_AI\n",
      "2013 prefixed administrative level 0 ID_RAMO\n",
      "2013 prefixed administrative level 1 ID_UR\n",
      "2013 prefixed administrative level 2 ID_PP\n",
      "2013 prefixed administrative level 3 ID_MODALIDAD\n",
      "2014 prefixed functional level 0 GPO_FUNCIONAL\n",
      "2014 prefixed functional level 1 ID_FUNCION\n",
      "2014 prefixed functional level 2 ID_SUBFUNCION\n",
      "2014 prefixed functional level 3 ID_AI\n",
      "2014 prefixed administrative level 0 ID_RAMO\n",
      "2014 prefixed administrative level 1 ID_UR\n",
      "2014 prefixed administrative level 2 ID_PP\n",
      "2014 prefixed administrative level 3 ID_MODALIDAD\n",
      "2015 prefixed functional level 0 GPO_FUNCIONAL\n",
      "2015 prefixed functional level 1 ID_FUNCION\n",
      "2015 prefixed functional level 2 ID_SUBFUNCION\n",
      "2015 prefixed functional level 3 ID_AI\n",
      "2015 prefixed administrative level 0 ID_RAMO\n",
      "2015 prefixed administrative level 1 ID_UR\n",
      "2015 prefixed administrative level 2 ID_PP\n",
      "2015 prefixed administrative level 3 ID_MODALIDAD\n",
      "\n",
      " Removing unused columns \n",
      "\n",
      "Reasignacion : no such column to delete\n",
      "Objeto del Gasto : no such column to delete\n",
      "Descripción de Reasignacion : no such column to delete\n",
      "Descripción de Objeto del Gasto : no such column to delete\n",
      "Reasignacion : no such column to delete\n",
      "Objeto del Gasto : no such column to delete\n",
      "Descripción de Reasignacion : no such column to delete\n",
      "Descripción de Objeto del Gasto : no such column to delete\n",
      "Reasignacion : no such column to delete\n",
      "Objeto del Gasto : no such column to delete\n",
      "Descripción de Reasignacion : no such column to delete\n",
      "Descripción de Objeto del Gasto : no such column to delete\n",
      "Reasignacion : no such column to delete\n",
      "Objeto del Gasto : no such column to delete\n",
      "Descripción de Reasignacion : no such column to delete\n",
      "Descripción de Objeto del Gasto : no such column to delete\n",
      "Reasignacion : no such column to delete\n",
      "Objeto del Gasto : no such column to delete\n",
      "Descripción de Reasignacion : no such column to delete\n",
      "Descripción de Objeto del Gasto : no such column to delete\n",
      "Reasignacion : no such column to delete\n",
      "Objeto del Gasto : no such column to delete\n",
      "Descripción de Reasignacion : no such column to delete\n",
      "Descripción de Objeto del Gasto : no such column to delete\n",
      "Reasignacion : no such column to delete\n",
      "Objeto del Gasto : no such column to delete\n",
      "Descripción de Reasignacion : no such column to delete\n",
      "Descripción de Objeto del Gasto : no such column to delete\n",
      "Reasignacion : no such column to delete\n",
      "Objeto del Gasto : no such column to delete\n",
      "Descripción de Reasignacion : no such column to delete\n",
      "Descripción de Objeto del Gasto : no such column to delete\n",
      "Reasignacion : no such column to delete\n",
      "Objeto del Gasto : no such column to delete\n",
      "Descripción de Reasignacion : no such column to delete\n",
      "Descripción de Objeto del Gasto : no such column to delete\n",
      "\n",
      " Saving pipeline configuration \n",
      "\n",
      "Saved 4 reports to pipeline.out/iteration-20-post-launch-bug-fixes-test-run2/mexican_federal_budget.reports.xlsx\n",
      "Saved input configuration to pipeline.out/iteration-20-post-launch-bug-fixes-test-run2/mexican_federal_budget.inputs.json\n",
      "Saved column aliases to pipeline.out/iteration-20-post-launch-bug-fixes-test-run2/mexican_federal_budget.aliases.json\n",
      "Saved bad records (those with empty cells) to pipeline.out/iteration-20-post-launch-bug-fixes-test-run2/mexican_federal_budget.missing.json\n",
      "Saved hierarchy levels used for prefixing to pipeline.out/iteration-20-post-launch-bug-fixes-test-run2/mexican_federal_budget.levels.json\n",
      "\n",
      " Pipeline run \"iteration-20-post-launch-bug-fixes-test-run2\" done \n",
      "\n"
     ]
    }
   ],
   "source": [
    "budgets, missing_ids, column_mapping, missing_values, sums = do_pipeline()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3033"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from gc import collect\n",
    "collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Saved pipeline.out/iteration-20-post-launch-bug-fixes-test-run2/mexican_federal_budget.2016.csv\n",
      "Saved pipeline.out/iteration-20-post-launch-bug-fixes-test-run2/mexican_federal_budget.2008.csv\n",
      "Saved pipeline.out/iteration-20-post-launch-bug-fixes-test-run2/mexican_federal_budget.2009.csv\n",
      "Saved pipeline.out/iteration-20-post-launch-bug-fixes-test-run2/mexican_federal_budget.2010.csv\n",
      "Saved pipeline.out/iteration-20-post-launch-bug-fixes-test-run2/mexican_federal_budget.2011.csv\n",
      "Saved pipeline.out/iteration-20-post-launch-bug-fixes-test-run2/mexican_federal_budget.2012.csv\n",
      "Saved pipeline.out/iteration-20-post-launch-bug-fixes-test-run2/mexican_federal_budget.2013.csv\n",
      "Saved pipeline.out/iteration-20-post-launch-bug-fixes-test-run2/mexican_federal_budget.2014.csv\n",
      "Saved pipeline.out/iteration-20-post-launch-bug-fixes-test-run2/mexican_federal_budget.2015.csv\n"
     ]
    }
   ],
   "source": [
    "for year, budget in budgets.items():\n",
    "    filepath = MERGED_FILE.replace('merged', str(year))\n",
    "    budget.to_csv(filepath, encoding='utf-8', index=False)\n",
    "    print('Saved', filepath)\n",
    "    stdout.flush()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Saved merged dataset to pipeline.out/iteration-20-post-launch-bug-fixes-test-run2/mexican_federal_budget.merged.csv\n"
     ]
    }
   ],
   "source": [
    "merged = concat(list(budgets.values()))\n",
    "merged.to_csv(MERGED_FILE, encoding='utf-8', index=False)\n",
    "print('Saved merged dataset to', MERGED_FILE)    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Quality control"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['CICLO',\n",
       " 'DESC_AI',\n",
       " 'DESC_CAPITULO',\n",
       " 'DESC_CONCEPTO',\n",
       " 'DESC_FF',\n",
       " 'DESC_FUNCION',\n",
       " 'DESC_GPO_FUNCIONAL',\n",
       " 'DESC_MODALIDAD',\n",
       " 'DESC_PARTIDA_ESPECIFICA',\n",
       " 'DESC_PARTIDA_GENERICA',\n",
       " 'DESC_PP',\n",
       " 'DESC_RAMO',\n",
       " 'DESC_SUBFUNCION',\n",
       " 'DESC_TIPOGASTO',\n",
       " 'DESC_UR',\n",
       " 'ENTIDAD_FEDERATIVA',\n",
       " 'GPO_FUNCIONAL',\n",
       " 'ID_AI',\n",
       " 'ID_CAPITULO',\n",
       " 'ID_CLAVE_CARTERA',\n",
       " 'ID_CONCEPTO',\n",
       " 'ID_ENTIDAD_FEDERATIVA',\n",
       " 'ID_FF',\n",
       " 'ID_FUNCION',\n",
       " 'ID_MODALIDAD',\n",
       " 'ID_PARTIDA_ESPECIFICA',\n",
       " 'ID_PARTIDA_GENERICA',\n",
       " 'ID_PP',\n",
       " 'ID_RAMO',\n",
       " 'ID_SUBFUNCION',\n",
       " 'ID_TIPOGASTO',\n",
       " 'ID_UR',\n",
       " 'MONTO_ADEFAS',\n",
       " 'MONTO_APROBADO',\n",
       " 'MONTO_DEVENGADO',\n",
       " 'MONTO_EJERCICIO',\n",
       " 'MONTO_MODIFICADO',\n",
       " 'MONTO_PAGADO']"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sorted(list(budget.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CICLO</th>\n",
       "      <th>DESC_AI</th>\n",
       "      <th>DESC_CAPITULO</th>\n",
       "      <th>DESC_CONCEPTO</th>\n",
       "      <th>DESC_FF</th>\n",
       "      <th>DESC_FUNCION</th>\n",
       "      <th>DESC_GPO_FUNCIONAL</th>\n",
       "      <th>DESC_MODALIDAD</th>\n",
       "      <th>DESC_PARTIDA_ESPECIFICA</th>\n",
       "      <th>DESC_PARTIDA_GENERICA</th>\n",
       "      <th>...</th>\n",
       "      <th>ID_SUBFUNCION</th>\n",
       "      <th>ID_TIPOGASTO</th>\n",
       "      <th>ID_UR</th>\n",
       "      <th>MONTO_ADEFAS</th>\n",
       "      <th>MONTO_APROBADO</th>\n",
       "      <th>MONTO_DEVENGADO</th>\n",
       "      <th>MONTO_EJERCICIO</th>\n",
       "      <th>MONTO_EJERCIDO</th>\n",
       "      <th>MONTO_MODIFICADO</th>\n",
       "      <th>MONTO_PAGADO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>90027</th>\n",
       "      <td>2010</td>\n",
       "      <td>Manejo eficiente y sustentable del agua y prev...</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios financieros, bancarios y comerciales</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Agua Potable y Alcantarillado</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Otros Subsidios</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Otros impuestos y derechos</td>\n",
       "      <td>...</td>\n",
       "      <td>2.4.1</td>\n",
       "      <td>1</td>\n",
       "      <td>16.B32</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1603.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198079</th>\n",
       "      <td>2015</td>\n",
       "      <td>Impartición de Justicia en materia agraria</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Materiales y articulos de construccion y de re...</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Justicia</td>\n",
       "      <td>Gobierno</td>\n",
       "      <td>Prestación de Servicios Públicos</td>\n",
       "      <td>Productos minerales no metálicos</td>\n",
       "      <td>Productos minerales no metálicos</td>\n",
       "      <td>...</td>\n",
       "      <td>1.2.1</td>\n",
       "      <td>1</td>\n",
       "      <td>31.200</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1680.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47174</th>\n",
       "      <td>2008</td>\n",
       "      <td>Regulación eficiente de las comunicaciones y l...</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Alimentos y utensilios</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Comunicaciones y  Transportes</td>\n",
       "      <td>Desarrollo Económico</td>\n",
       "      <td>Regulación y supervisión</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Productos alimenticios</td>\n",
       "      <td>...</td>\n",
       "      <td>3.1.6</td>\n",
       "      <td>1</td>\n",
       "      <td>9.647</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>302094</th>\n",
       "      <td>2015</td>\n",
       "      <td>Cobertura de la atención médica preventiva</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Remuneraciones al personal de carácter transit...</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Salud</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Prestación de Servicios Públicos</td>\n",
       "      <td>Remuneraciones al personal eventual</td>\n",
       "      <td>Sueldos base al personal eventual</td>\n",
       "      <td>...</td>\n",
       "      <td>2.3.2</td>\n",
       "      <td>1</td>\n",
       "      <td>51.GYN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>133624.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144799</th>\n",
       "      <td>2014</td>\n",
       "      <td>Ordenamiento y regularización de la propiedad ...</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Remuneraciones adicionales y especiales</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Agropecuaria, Silvicultura, Pesca y Caza</td>\n",
       "      <td>Desarrollo Económico</td>\n",
       "      <td>Planeación, seguimiento y evaluación de políti...</td>\n",
       "      <td>Primas de vacaciones y dominical</td>\n",
       "      <td>Primas de vacaciones, dominical y gratificació...</td>\n",
       "      <td>...</td>\n",
       "      <td>3.2.1</td>\n",
       "      <td>1</td>\n",
       "      <td>15.145</td>\n",
       "      <td>0.0</td>\n",
       "      <td>69010.0</td>\n",
       "      <td>62613.96</td>\n",
       "      <td>62613.96</td>\n",
       "      <td>NaN</td>\n",
       "      <td>62613.96</td>\n",
       "      <td>62613.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17015</th>\n",
       "      <td>2010</td>\n",
       "      <td>Función pública y buen gobierno</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios financieros, bancarios y comerciales</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Administración Pública</td>\n",
       "      <td>Gobierno</td>\n",
       "      <td>Apoyo a la función pública y al mejoramiento d...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Otros impuestos y derechos</td>\n",
       "      <td>...</td>\n",
       "      <td>1.8.3</td>\n",
       "      <td>1</td>\n",
       "      <td>8.136</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14850.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14653.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336067</th>\n",
       "      <td>2014</td>\n",
       "      <td>Cobertura de la atención médica preventiva</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios basicos</td>\n",
       "      <td>Ingresos Propios</td>\n",
       "      <td>Salud</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Prestación de Servicios Públicos</td>\n",
       "      <td>Servicio de energía eléctrica</td>\n",
       "      <td>Energía eléctrica</td>\n",
       "      <td>...</td>\n",
       "      <td>2.3.2</td>\n",
       "      <td>1</td>\n",
       "      <td>GYN.GYN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>421626.0</td>\n",
       "      <td>1072399.00</td>\n",
       "      <td>1072399.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1072399.00</td>\n",
       "      <td>1072399.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160256</th>\n",
       "      <td>2015</td>\n",
       "      <td>Manejo eficiente y sustentable del agua y prev...</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios de instalacion, reparacion, mantenim...</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Protección Ambiental</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Regulación y supervisión</td>\n",
       "      <td>Mantenimiento y conservación de mobiliario y e...</td>\n",
       "      <td>Instalación, reparación y mantenimiento de mob...</td>\n",
       "      <td>...</td>\n",
       "      <td>2.1.2</td>\n",
       "      <td>1</td>\n",
       "      <td>16.B00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38183.0</td>\n",
       "      <td>17980.00</td>\n",
       "      <td>17980.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17980.00</td>\n",
       "      <td>17980.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228439</th>\n",
       "      <td>2015</td>\n",
       "      <td>Democracia preservada y fortalecida mediante l...</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Herramientas, refacciones y accesorios menores</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Coordinación de la Política de Gobierno</td>\n",
       "      <td>Gobierno</td>\n",
       "      <td>Específicos</td>\n",
       "      <td>Refacciones y accesorios menores de equipo de ...</td>\n",
       "      <td>Refacciones y accesorios menores de equipo de ...</td>\n",
       "      <td>...</td>\n",
       "      <td>1.3.6</td>\n",
       "      <td>1</td>\n",
       "      <td>22.300</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1452.90</td>\n",
       "      <td>1452.90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1452.90</td>\n",
       "      <td>1452.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55133</th>\n",
       "      <td>2014</td>\n",
       "      <td>Carreteras eficientes, seguras y suficientes</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Combustibles, lubricantes y aditivos</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Transporte</td>\n",
       "      <td>Desarrollo Económico</td>\n",
       "      <td>Proyectos de Inversión</td>\n",
       "      <td>Combustibles, lubricantes y aditivos para vehí...</td>\n",
       "      <td>Combustibles, lubricantes y aditivos</td>\n",
       "      <td>...</td>\n",
       "      <td>3.5.1</td>\n",
       "      <td>3</td>\n",
       "      <td>9.630</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1093500.0</td>\n",
       "      <td>2313972.38</td>\n",
       "      <td>2313972.38</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2313972.38</td>\n",
       "      <td>2313972.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115682</th>\n",
       "      <td>2013</td>\n",
       "      <td>Servicios de apoyo administrativo</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Seguridad social</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Salud</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Apoyo al proceso presupuestario y para mejorar...</td>\n",
       "      <td>Aportaciones al seguro de cesantía en edad ava...</td>\n",
       "      <td>Aportaciones de seguridad social</td>\n",
       "      <td>...</td>\n",
       "      <td>2.3.4</td>\n",
       "      <td>1</td>\n",
       "      <td>12.NBV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>39654.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>248322.21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41068</th>\n",
       "      <td>2011</td>\n",
       "      <td>Carreteras Alimentadoras y Caminos Rurales efi...</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Vestuario, blancos, prendas de protección y ar...</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Comunicaciones y Transportes</td>\n",
       "      <td>Desarrollo Económico</td>\n",
       "      <td>Proyectos de Inversión</td>\n",
       "      <td>Prendas de protección personal</td>\n",
       "      <td>Prendas de seguridad y protección personal</td>\n",
       "      <td>...</td>\n",
       "      <td>3.1.1</td>\n",
       "      <td>3</td>\n",
       "      <td>9.643</td>\n",
       "      <td>NaN</td>\n",
       "      <td>788.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2546.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300510</th>\n",
       "      <td>2015</td>\n",
       "      <td>Pago de Pensiones por Invalidez y Vida</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Seguridad social</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Protección Social</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Pensiones y jubilaciones</td>\n",
       "      <td>Aportaciones al Sistema de Ahorro para el Retiro</td>\n",
       "      <td>Aportaciones al sistema para el retiro</td>\n",
       "      <td>...</td>\n",
       "      <td>2.6.1</td>\n",
       "      <td>1</td>\n",
       "      <td>51.GYN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3364.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323440</th>\n",
       "      <td>2014</td>\n",
       "      <td>Cobertura de la atención médica curativa</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Remuneraciones adicionales y especiales</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Salud</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Prestación de Servicios Públicos</td>\n",
       "      <td>Compensaciones por servicios eventuales</td>\n",
       "      <td>Compensaciones</td>\n",
       "      <td>...</td>\n",
       "      <td>2.3.2</td>\n",
       "      <td>1</td>\n",
       "      <td>GYN.GYN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>121676.00</td>\n",
       "      <td>121676.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>121676.00</td>\n",
       "      <td>121676.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134674</th>\n",
       "      <td>2012</td>\n",
       "      <td>Servicios de apoyo administrativo</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios de instalacion, reparacion, mantenim...</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Salud</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Apoyo al proceso presupuestario y para mejorar...</td>\n",
       "      <td>Servicios de jardinería y fumigación</td>\n",
       "      <td>Servicios de jardinería y fumigación</td>\n",
       "      <td>...</td>\n",
       "      <td>2.3.2</td>\n",
       "      <td>1</td>\n",
       "      <td>12.N00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3480.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9090</th>\n",
       "      <td>2011</td>\n",
       "      <td>Gasto público transparente y orientado a resul...</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios basicos</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Hacienda</td>\n",
       "      <td>Gobierno</td>\n",
       "      <td>Planeación, seguimiento y evaluación de políti...</td>\n",
       "      <td>Servicio postal</td>\n",
       "      <td>Servicios postales y telegráficos</td>\n",
       "      <td>...</td>\n",
       "      <td>1.3.2</td>\n",
       "      <td>1</td>\n",
       "      <td>6.418</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51461</th>\n",
       "      <td>2013</td>\n",
       "      <td>Carreteras alimentadoras y caminos rurales efi...</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios basicos</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Transporte</td>\n",
       "      <td>Desarrollo Económico</td>\n",
       "      <td>Proyectos de Inversión</td>\n",
       "      <td>Servicio telefónico convencional</td>\n",
       "      <td>Telefonía tradicional</td>\n",
       "      <td>...</td>\n",
       "      <td>3.5.1</td>\n",
       "      <td>3</td>\n",
       "      <td>9.633</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5360.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116921</th>\n",
       "      <td>2008</td>\n",
       "      <td>Democracia preservada y fortalecida mediante l...</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Productos químicos, farmacéuticos y de laborat...</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Gobernación</td>\n",
       "      <td>Gobierno</td>\n",
       "      <td>Otras actividades relevantes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Materiales, accesorios y suministros médicos</td>\n",
       "      <td>...</td>\n",
       "      <td>1.4.1</td>\n",
       "      <td>1</td>\n",
       "      <td>22.113</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1772.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>158.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292863</th>\n",
       "      <td>2015</td>\n",
       "      <td>Otros Servicios de Salud</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Remuneraciones adicionales y especiales</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Salud</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Apoyo al proceso presupuestario y para mejorar...</td>\n",
       "      <td>Compensaciones por servicios eventuales</td>\n",
       "      <td>Compensaciones</td>\n",
       "      <td>...</td>\n",
       "      <td>2.3.2</td>\n",
       "      <td>1</td>\n",
       "      <td>51.GYN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1301261.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221961</th>\n",
       "      <td>2014</td>\n",
       "      <td>Función pública y buen gobierno</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Materiales de administracion, emision de docum...</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Coordinación de la Política de Gobierno</td>\n",
       "      <td>Gobierno</td>\n",
       "      <td>Apoyo a la función pública y al mejoramiento d...</td>\n",
       "      <td>Materiales y útiles de oficina</td>\n",
       "      <td>Materiales, útiles y equipos menores de oficina</td>\n",
       "      <td>...</td>\n",
       "      <td>1.3.4</td>\n",
       "      <td>1</td>\n",
       "      <td>31.300</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 39 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       CICLO                                            DESC_AI  \\\n",
       "90027   2010  Manejo eficiente y sustentable del agua y prev...   \n",
       "198079  2015         Impartición de Justicia en materia agraria   \n",
       "47174   2008  Regulación eficiente de las comunicaciones y l...   \n",
       "302094  2015         Cobertura de la atención médica preventiva   \n",
       "144799  2014  Ordenamiento y regularización de la propiedad ...   \n",
       "17015   2010                    Función pública y buen gobierno   \n",
       "336067  2014         Cobertura de la atención médica preventiva   \n",
       "160256  2015  Manejo eficiente y sustentable del agua y prev...   \n",
       "228439  2015  Democracia preservada y fortalecida mediante l...   \n",
       "55133   2014       Carreteras eficientes, seguras y suficientes   \n",
       "115682  2013                  Servicios de apoyo administrativo   \n",
       "41068   2011  Carreteras Alimentadoras y Caminos Rurales efi...   \n",
       "300510  2015             Pago de Pensiones por Invalidez y Vida   \n",
       "323440  2014           Cobertura de la atención médica curativa   \n",
       "134674  2012                  Servicios de apoyo administrativo   \n",
       "9090    2011  Gasto público transparente y orientado a resul...   \n",
       "51461   2013  Carreteras alimentadoras y caminos rurales efi...   \n",
       "116921  2008  Democracia preservada y fortalecida mediante l...   \n",
       "292863  2015                           Otros Servicios de Salud   \n",
       "221961  2014                    Función pública y buen gobierno   \n",
       "\n",
       "                   DESC_CAPITULO  \\\n",
       "90027        Servicios generales   \n",
       "198079  Materiales y suministros   \n",
       "47174   Materiales y suministros   \n",
       "302094      Servicios personales   \n",
       "144799      Servicios personales   \n",
       "17015        Servicios generales   \n",
       "336067       Servicios generales   \n",
       "160256       Servicios generales   \n",
       "228439  Materiales y suministros   \n",
       "55133   Materiales y suministros   \n",
       "115682      Servicios personales   \n",
       "41068   Materiales y suministros   \n",
       "300510      Servicios personales   \n",
       "323440      Servicios personales   \n",
       "134674       Servicios generales   \n",
       "9090         Servicios generales   \n",
       "51461        Servicios generales   \n",
       "116921  Materiales y suministros   \n",
       "292863      Servicios personales   \n",
       "221961  Materiales y suministros   \n",
       "\n",
       "                                            DESC_CONCEPTO            DESC_FF  \\\n",
       "90027      Servicios financieros, bancarios y comerciales  Recursos fiscales   \n",
       "198079  Materiales y articulos de construccion y de re...  Recursos fiscales   \n",
       "47174                              Alimentos y utensilios  Recursos fiscales   \n",
       "302094  Remuneraciones al personal de carácter transit...  Recursos fiscales   \n",
       "144799            Remuneraciones adicionales y especiales  Recursos fiscales   \n",
       "17015      Servicios financieros, bancarios y comerciales  Recursos fiscales   \n",
       "336067                                  Servicios basicos   Ingresos Propios   \n",
       "160256  Servicios de instalacion, reparacion, mantenim...  Recursos fiscales   \n",
       "228439     Herramientas, refacciones y accesorios menores  Recursos fiscales   \n",
       "55133                Combustibles, lubricantes y aditivos  Recursos fiscales   \n",
       "115682                                   Seguridad social  Recursos fiscales   \n",
       "41068   Vestuario, blancos, prendas de protección y ar...  Recursos fiscales   \n",
       "300510                                   Seguridad social  Recursos fiscales   \n",
       "323440            Remuneraciones adicionales y especiales  Recursos fiscales   \n",
       "134674  Servicios de instalacion, reparacion, mantenim...  Recursos fiscales   \n",
       "9090                                    Servicios basicos  Recursos fiscales   \n",
       "51461                                   Servicios basicos  Recursos fiscales   \n",
       "116921  Productos químicos, farmacéuticos y de laborat...  Recursos fiscales   \n",
       "292863            Remuneraciones adicionales y especiales  Recursos fiscales   \n",
       "221961  Materiales de administracion, emision de docum...  Recursos fiscales   \n",
       "\n",
       "                                    DESC_FUNCION    DESC_GPO_FUNCIONAL  \\\n",
       "90027              Agua Potable y Alcantarillado     Desarrollo Social   \n",
       "198079                                  Justicia              Gobierno   \n",
       "47174              Comunicaciones y  Transportes  Desarrollo Económico   \n",
       "302094                                     Salud     Desarrollo Social   \n",
       "144799  Agropecuaria, Silvicultura, Pesca y Caza  Desarrollo Económico   \n",
       "17015                     Administración Pública              Gobierno   \n",
       "336067                                     Salud     Desarrollo Social   \n",
       "160256                      Protección Ambiental     Desarrollo Social   \n",
       "228439   Coordinación de la Política de Gobierno              Gobierno   \n",
       "55133                                 Transporte  Desarrollo Económico   \n",
       "115682                                     Salud     Desarrollo Social   \n",
       "41068               Comunicaciones y Transportes  Desarrollo Económico   \n",
       "300510                         Protección Social     Desarrollo Social   \n",
       "323440                                     Salud     Desarrollo Social   \n",
       "134674                                     Salud     Desarrollo Social   \n",
       "9090                                    Hacienda              Gobierno   \n",
       "51461                                 Transporte  Desarrollo Económico   \n",
       "116921                               Gobernación              Gobierno   \n",
       "292863                                     Salud     Desarrollo Social   \n",
       "221961   Coordinación de la Política de Gobierno              Gobierno   \n",
       "\n",
       "                                           DESC_MODALIDAD  \\\n",
       "90027                                     Otros Subsidios   \n",
       "198079                   Prestación de Servicios Públicos   \n",
       "47174                            Regulación y supervisión   \n",
       "302094                   Prestación de Servicios Públicos   \n",
       "144799  Planeación, seguimiento y evaluación de políti...   \n",
       "17015   Apoyo a la función pública y al mejoramiento d...   \n",
       "336067                   Prestación de Servicios Públicos   \n",
       "160256                           Regulación y supervisión   \n",
       "228439                                        Específicos   \n",
       "55133                              Proyectos de Inversión   \n",
       "115682  Apoyo al proceso presupuestario y para mejorar...   \n",
       "41068                              Proyectos de Inversión   \n",
       "300510                           Pensiones y jubilaciones   \n",
       "323440                   Prestación de Servicios Públicos   \n",
       "134674  Apoyo al proceso presupuestario y para mejorar...   \n",
       "9090    Planeación, seguimiento y evaluación de políti...   \n",
       "51461                              Proyectos de Inversión   \n",
       "116921                       Otras actividades relevantes   \n",
       "292863  Apoyo al proceso presupuestario y para mejorar...   \n",
       "221961  Apoyo a la función pública y al mejoramiento d...   \n",
       "\n",
       "                                  DESC_PARTIDA_ESPECIFICA  \\\n",
       "90027                                                 NaN   \n",
       "198079                   Productos minerales no metálicos   \n",
       "47174                                                 NaN   \n",
       "302094                Remuneraciones al personal eventual   \n",
       "144799                   Primas de vacaciones y dominical   \n",
       "17015                                                 NaN   \n",
       "336067                      Servicio de energía eléctrica   \n",
       "160256  Mantenimiento y conservación de mobiliario y e...   \n",
       "228439  Refacciones y accesorios menores de equipo de ...   \n",
       "55133   Combustibles, lubricantes y aditivos para vehí...   \n",
       "115682  Aportaciones al seguro de cesantía en edad ava...   \n",
       "41068                      Prendas de protección personal   \n",
       "300510   Aportaciones al Sistema de Ahorro para el Retiro   \n",
       "323440            Compensaciones por servicios eventuales   \n",
       "134674               Servicios de jardinería y fumigación   \n",
       "9090                                      Servicio postal   \n",
       "51461                    Servicio telefónico convencional   \n",
       "116921                                                NaN   \n",
       "292863            Compensaciones por servicios eventuales   \n",
       "221961                     Materiales y útiles de oficina   \n",
       "\n",
       "                                    DESC_PARTIDA_GENERICA     ...       \\\n",
       "90027                          Otros impuestos y derechos     ...        \n",
       "198079                   Productos minerales no metálicos     ...        \n",
       "47174                              Productos alimenticios     ...        \n",
       "302094                  Sueldos base al personal eventual     ...        \n",
       "144799  Primas de vacaciones, dominical y gratificació...     ...        \n",
       "17015                          Otros impuestos y derechos     ...        \n",
       "336067                                  Energía eléctrica     ...        \n",
       "160256  Instalación, reparación y mantenimiento de mob...     ...        \n",
       "228439  Refacciones y accesorios menores de equipo de ...     ...        \n",
       "55133                Combustibles, lubricantes y aditivos     ...        \n",
       "115682                   Aportaciones de seguridad social     ...        \n",
       "41068          Prendas de seguridad y protección personal     ...        \n",
       "300510             Aportaciones al sistema para el retiro     ...        \n",
       "323440                                     Compensaciones     ...        \n",
       "134674               Servicios de jardinería y fumigación     ...        \n",
       "9090                    Servicios postales y telegráficos     ...        \n",
       "51461                               Telefonía tradicional     ...        \n",
       "116921       Materiales, accesorios y suministros médicos     ...        \n",
       "292863                                     Compensaciones     ...        \n",
       "221961    Materiales, útiles y equipos menores de oficina     ...        \n",
       "\n",
       "       ID_SUBFUNCION ID_TIPOGASTO    ID_UR MONTO_ADEFAS MONTO_APROBADO  \\\n",
       "90027          2.4.1            1   16.B32          NaN            0.0   \n",
       "198079         1.2.1            1   31.200          0.0         1680.0   \n",
       "47174          3.1.6            1    9.647          NaN            0.0   \n",
       "302094         2.3.2            1   51.GYN          0.0       133624.0   \n",
       "144799         3.2.1            1   15.145          0.0        69010.0   \n",
       "17015          1.8.3            1    8.136          NaN        14850.0   \n",
       "336067         2.3.2            1  GYN.GYN          0.0       421626.0   \n",
       "160256         2.1.2            1   16.B00          0.0        38183.0   \n",
       "228439         1.3.6            1   22.300          0.0            0.0   \n",
       "55133          3.5.1            3    9.630          0.0      1093500.0   \n",
       "115682         2.3.4            1   12.NBV          NaN        39654.0   \n",
       "41068          3.1.1            3    9.643          NaN          788.0   \n",
       "300510         2.6.1            1   51.GYN          0.0         3364.0   \n",
       "323440         2.3.2            1  GYN.GYN          0.0            0.0   \n",
       "134674         2.3.2            1   12.N00          NaN        18000.0   \n",
       "9090           1.3.2            1    6.418          NaN         1000.0   \n",
       "51461          3.5.1            3    9.633          NaN         5360.0   \n",
       "116921         1.4.1            1   22.113          NaN         1772.0   \n",
       "292863         2.3.2            1   51.GYN          0.0      1301261.0   \n",
       "221961         1.3.4            1   31.300          0.0         1000.0   \n",
       "\n",
       "       MONTO_DEVENGADO MONTO_EJERCICIO MONTO_EJERCIDO MONTO_MODIFICADO  \\\n",
       "90027              NaN             NaN         1603.0              NaN   \n",
       "198079            0.00            0.00            NaN             0.00   \n",
       "47174              NaN             NaN            0.0              NaN   \n",
       "302094            0.00            0.00            NaN             0.00   \n",
       "144799        62613.96        62613.96            NaN         62613.96   \n",
       "17015              NaN             NaN        14653.0              NaN   \n",
       "336067      1072399.00      1072399.00            NaN       1072399.00   \n",
       "160256        17980.00        17980.00            NaN         17980.00   \n",
       "228439         1452.90         1452.90            NaN          1452.90   \n",
       "55133       2313972.38      2313972.38            NaN       2313972.38   \n",
       "115682             NaN       248322.21            NaN              NaN   \n",
       "41068              NaN             NaN         2546.0              NaN   \n",
       "300510            0.00            0.00            NaN             0.00   \n",
       "323440       121676.00       121676.00            NaN        121676.00   \n",
       "134674             NaN             NaN         3480.0              NaN   \n",
       "9090               NaN             NaN            0.0              NaN   \n",
       "51461              NaN            0.00            NaN              NaN   \n",
       "116921             NaN             NaN          158.0              NaN   \n",
       "292863            0.00            0.00            NaN             0.00   \n",
       "221961            0.00            0.00            NaN             0.00   \n",
       "\n",
       "       MONTO_PAGADO  \n",
       "90027           NaN  \n",
       "198079         0.00  \n",
       "47174           NaN  \n",
       "302094         0.00  \n",
       "144799     62613.96  \n",
       "17015           NaN  \n",
       "336067   1072399.00  \n",
       "160256     17980.00  \n",
       "228439      1452.90  \n",
       "55133    2313972.38  \n",
       "115682          NaN  \n",
       "41068           NaN  \n",
       "300510         0.00  \n",
       "323440    121676.00  \n",
       "134674          NaN  \n",
       "9090            NaN  \n",
       "51461           NaN  \n",
       "116921          NaN  \n",
       "292863         0.00  \n",
       "221961         0.00  \n",
       "\n",
       "[20 rows x 39 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.sample(n=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CICLO</th>\n",
       "      <th>ID_CAPITULO</th>\n",
       "      <th>ID_CONCEPTO</th>\n",
       "      <th>ID_PARTIDA_ESPECIFICA</th>\n",
       "      <th>ID_PARTIDA_GENERICA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>167521</th>\n",
       "      <td>2014</td>\n",
       "      <td>3000</td>\n",
       "      <td>3300</td>\n",
       "      <td>33603</td>\n",
       "      <td>336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190539</th>\n",
       "      <td>2013</td>\n",
       "      <td>2000</td>\n",
       "      <td>2100</td>\n",
       "      <td>21601</td>\n",
       "      <td>216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>333412</th>\n",
       "      <td>2015</td>\n",
       "      <td>1000</td>\n",
       "      <td>1400</td>\n",
       "      <td>14301</td>\n",
       "      <td>143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15201</th>\n",
       "      <td>2015</td>\n",
       "      <td>3000</td>\n",
       "      <td>3100</td>\n",
       "      <td>31801</td>\n",
       "      <td>318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242102</th>\n",
       "      <td>2014</td>\n",
       "      <td>3000</td>\n",
       "      <td>3500</td>\n",
       "      <td>35501</td>\n",
       "      <td>355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85818</th>\n",
       "      <td>2012</td>\n",
       "      <td>3000</td>\n",
       "      <td>3100</td>\n",
       "      <td>31801</td>\n",
       "      <td>318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22347</th>\n",
       "      <td>2015</td>\n",
       "      <td>3000</td>\n",
       "      <td>3500</td>\n",
       "      <td>35101</td>\n",
       "      <td>351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24269</th>\n",
       "      <td>2008</td>\n",
       "      <td>1000</td>\n",
       "      <td>1500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>238499</th>\n",
       "      <td>2015</td>\n",
       "      <td>1000</td>\n",
       "      <td>1400</td>\n",
       "      <td>14101</td>\n",
       "      <td>141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52692</th>\n",
       "      <td>2015</td>\n",
       "      <td>2000</td>\n",
       "      <td>2200</td>\n",
       "      <td>22104</td>\n",
       "      <td>221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249498</th>\n",
       "      <td>2014</td>\n",
       "      <td>1000</td>\n",
       "      <td>1400</td>\n",
       "      <td>14103</td>\n",
       "      <td>141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70410</th>\n",
       "      <td>2012</td>\n",
       "      <td>3000</td>\n",
       "      <td>3700</td>\n",
       "      <td>37104</td>\n",
       "      <td>371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13697</th>\n",
       "      <td>2012</td>\n",
       "      <td>3000</td>\n",
       "      <td>3100</td>\n",
       "      <td>31501</td>\n",
       "      <td>315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53404</th>\n",
       "      <td>2014</td>\n",
       "      <td>3000</td>\n",
       "      <td>3100</td>\n",
       "      <td>31101</td>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57081</th>\n",
       "      <td>2014</td>\n",
       "      <td>3000</td>\n",
       "      <td>3100</td>\n",
       "      <td>31401</td>\n",
       "      <td>314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>335680</th>\n",
       "      <td>2015</td>\n",
       "      <td>3000</td>\n",
       "      <td>3900</td>\n",
       "      <td>39202</td>\n",
       "      <td>392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78361</th>\n",
       "      <td>2011</td>\n",
       "      <td>1000</td>\n",
       "      <td>1400</td>\n",
       "      <td>14301</td>\n",
       "      <td>143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206194</th>\n",
       "      <td>2012</td>\n",
       "      <td>3000</td>\n",
       "      <td>3800</td>\n",
       "      <td>38501</td>\n",
       "      <td>385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14024</th>\n",
       "      <td>2008</td>\n",
       "      <td>5000</td>\n",
       "      <td>5100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30512</th>\n",
       "      <td>2013</td>\n",
       "      <td>1000</td>\n",
       "      <td>1400</td>\n",
       "      <td>14405</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       CICLO ID_CAPITULO ID_CONCEPTO ID_PARTIDA_ESPECIFICA ID_PARTIDA_GENERICA\n",
       "167521  2014        3000        3300                 33603                 336\n",
       "190539  2013        2000        2100                 21601                 216\n",
       "333412  2015        1000        1400                 14301                 143\n",
       "15201   2015        3000        3100                 31801                 318\n",
       "242102  2014        3000        3500                 35501                 355\n",
       "85818   2012        3000        3100                 31801                 318\n",
       "22347   2015        3000        3500                 35101                 351\n",
       "24269   2008        1000        1500                   NaN                1511\n",
       "238499  2015        1000        1400                 14101                 141\n",
       "52692   2015        2000        2200                 22104                 221\n",
       "249498  2014        1000        1400                 14103                 141\n",
       "70410   2012        3000        3700                 37104                 371\n",
       "13697   2012        3000        3100                 31501                 315\n",
       "53404   2014        3000        3100                 31101                 311\n",
       "57081   2014        3000        3100                 31401                 314\n",
       "335680  2015        3000        3900                 39202                 392\n",
       "78361   2011        1000        1400                 14301                 143\n",
       "206194  2012        3000        3800                 38501                 385\n",
       "14024   2008        5000        5100                   NaN                5101\n",
       "30512   2013        1000        1400                 14405                 144"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "objeto_breakdown = [\n",
    "    'CICLO', \n",
    "    'ID_CAPITULO', \n",
    "    'ID_CONCEPTO', \n",
    "    'ID_PARTIDA_ESPECIFICA', \n",
    "    'ID_PARTIDA_GENERICA'\n",
    "]\n",
    "merged[objeto_breakdown].sample(n=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total: missing 76 catalog IDs to breakdown the \"Objeto del Gasto\" column\n",
      "Tables: {'partida_específica': 24, 'partida_generica': 45, 'concepto': 7}\n",
      "Years: {2008: 48, 2009: 4, 2012: 22, 2013: 1, 2015: 1}\n"
     ]
    }
   ],
   "source": [
    "print('Total: missing', len(missing_ids), 'catalog IDs to breakdown the \"Objeto del Gasto\" column')\n",
    "print('Tables:', dict(missing_ids.groupby('table').count()['ID']))\n",
    "print('Years:', dict(missing_ids.groupby('year').count()['ID']))\n",
    "try:\n",
    "    missing_ids.sample(n=20)\n",
    "except ValueError:\n",
    "    pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 76 entries, 0 to 18706\n",
      "Data columns (total 3 columns):\n",
      "ID       76 non-null object\n",
      "table    76 non-null object\n",
      "year     76 non-null int64\n",
      "dtypes: int64(1), object(2)\n",
      "memory usage: 2.4+ KB\n"
     ]
    }
   ],
   "source": [
    "missing_ids.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Column</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DESC_FUNCION</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MONTO_APROBADO</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DESC_CAPITULO</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DESC_AI</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ID_RAMO</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MONTO_MODIFICADO</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ID_CLAVE_CARTERA</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MONTO_EJERCICIO</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DESC_FF</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ENTIDAD_FEDERATIVA</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>GPO_FUNCIONAL</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ID_UR</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>DESC_MODALIDAD</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ID_FUNCION</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>ID_ENTIDAD_FEDERATIVA</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>MONTO_PAGADO</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>MONTO_EJERCIDO</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>DESC_PP</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>DESC_GPO_FUNCIONAL</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>ID_MODALIDAD</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>DESC_CONCEPTO</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>ID_CONCEPTO</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>ID_TIPOGASTO</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>DESC_RAMO</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>ID_CAPITULO</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>CICLO</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>DESC_SUBFUNCION</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>MONTO_ADEFAS</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>MONTO_DEVENGADO</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>ID_SUBFUNCION</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>ID_AI</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>DESC_UR</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>ID_FF</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>DESC_TIPOGASTO</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>ID_PP</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Column   2008   2009   2010   2011   2012   2013   2014  \\\n",
       "0            DESC_FUNCION   True   True   True   True   True   True   True   \n",
       "1          MONTO_APROBADO   True   True   True   True   True   True   True   \n",
       "2           DESC_CAPITULO  False  False  False  False  False  False  False   \n",
       "3                 DESC_AI   True   True   True   True   True   True   True   \n",
       "4                 ID_RAMO   True   True   True   True   True   True   True   \n",
       "5        MONTO_MODIFICADO  False  False  False  False  False  False   True   \n",
       "6        ID_CLAVE_CARTERA  False  False  False  False   True   True   True   \n",
       "7         MONTO_EJERCICIO  False  False  False  False  False   True   True   \n",
       "8                 DESC_FF   True   True   True   True   True   True   True   \n",
       "9      ENTIDAD_FEDERATIVA  False  False  False  False   True   True   True   \n",
       "10          GPO_FUNCIONAL   True   True   True   True   True   True   True   \n",
       "11                  ID_UR   True   True   True   True   True   True   True   \n",
       "12         DESC_MODALIDAD   True   True   True   True   True   True   True   \n",
       "13             ID_FUNCION   True   True   True   True   True   True   True   \n",
       "14  ID_ENTIDAD_FEDERATIVA  False  False  False  False   True   True   True   \n",
       "15           MONTO_PAGADO  False  False  False  False  False  False   True   \n",
       "16         MONTO_EJERCIDO   True   True   True   True   True  False  False   \n",
       "17                DESC_PP   True   True   True   True   True   True   True   \n",
       "18     DESC_GPO_FUNCIONAL   True   True   True   True   True   True   True   \n",
       "19           ID_MODALIDAD   True   True   True   True   True   True   True   \n",
       "20          DESC_CONCEPTO   True   True   True   True   True   True   True   \n",
       "21            ID_CONCEPTO   True   True   True   True   True   True   True   \n",
       "22           ID_TIPOGASTO   True   True   True   True   True   True   True   \n",
       "23              DESC_RAMO   True   True   True   True   True   True   True   \n",
       "24            ID_CAPITULO  False  False  False  False  False  False  False   \n",
       "25                  CICLO   True   True   True   True   True   True   True   \n",
       "26        DESC_SUBFUNCION   True   True   True   True   True   True   True   \n",
       "27           MONTO_ADEFAS  False  False  False  False  False  False   True   \n",
       "28        MONTO_DEVENGADO  False  False  False  False  False  False   True   \n",
       "29          ID_SUBFUNCION   True   True   True   True   True   True   True   \n",
       "30                  ID_AI   True   True   True   True   True   True   True   \n",
       "31                DESC_UR   True   True   True   True   True   True   True   \n",
       "32                  ID_FF   True   True   True   True   True   True   True   \n",
       "33         DESC_TIPOGASTO   True   True   True   True   True   True   True   \n",
       "34                  ID_PP   True   True   True   True   True   True   True   \n",
       "\n",
       "     2015   2016  \n",
       "0    True   True  \n",
       "1    True   True  \n",
       "2   False   True  \n",
       "3    True   True  \n",
       "4    True   True  \n",
       "5    True  False  \n",
       "6    True   True  \n",
       "7    True  False  \n",
       "8    True   True  \n",
       "9    True   True  \n",
       "10   True   True  \n",
       "11   True   True  \n",
       "12   True   True  \n",
       "13   True   True  \n",
       "14   True   True  \n",
       "15   True   True  \n",
       "16  False   True  \n",
       "17   True   True  \n",
       "18   True   True  \n",
       "19   True   True  \n",
       "20   True   True  \n",
       "21   True   True  \n",
       "22   True   True  \n",
       "23   True   True  \n",
       "24  False   True  \n",
       "25   True   True  \n",
       "26   True   True  \n",
       "27   True  False  \n",
       "28   True  False  \n",
       "29   True   True  \n",
       "30   True   True  \n",
       "31   True   True  \n",
       "32   True   True  \n",
       "33   True   True  \n",
       "34   True   True  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "column_mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Column</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DESC_FUNCION</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MONTO_APROBADO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DESC_CAPITULO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DESC_AI</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ID_RAMO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MONTO_MODIFICADO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ID_CLAVE_CARTERA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MONTO_EJERCICIO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DESC_FF</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ENTIDAD_FEDERATIVA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>172.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>GPO_FUNCIONAL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ID_UR</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>DESC_MODALIDAD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ID_FUNCION</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>ID_ENTIDAD_FEDERATIVA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>MONTO_PAGADO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>170.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>MONTO_EJERCIDO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>170.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>DESC_PP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>DESC_GPO_FUNCIONAL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>ID_MODALIDAD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>DESC_CONCEPTO</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>ID_CONCEPTO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>ID_TIPOGASTO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>DESC_RAMO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>ID_CAPITULO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>CICLO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>DESC_SUBFUNCION</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>MONTO_ADEFAS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>MONTO_DEVENGADO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>ID_SUBFUNCION</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>ID_AI</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>DESC_UR</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>ID_FF</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>DESC_TIPOGASTO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>ID_PP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Column  2008  2009  2010  2011   2012  2013  2014  2015  \\\n",
       "0            DESC_FUNCION   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "1          MONTO_APROBADO   0.0   0.0   0.0   0.0    1.0   0.0   0.0   0.0   \n",
       "2           DESC_CAPITULO   NaN   NaN   NaN   NaN    NaN   NaN   NaN   NaN   \n",
       "3                 DESC_AI   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "4                 ID_RAMO   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "5        MONTO_MODIFICADO   NaN   NaN   NaN   NaN    NaN   NaN   0.0   0.0   \n",
       "6        ID_CLAVE_CARTERA   NaN   NaN   NaN   NaN    0.0   0.0   0.0   0.0   \n",
       "7         MONTO_EJERCICIO   NaN   NaN   NaN   NaN    NaN   0.0   0.0   0.0   \n",
       "8                 DESC_FF   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "9      ENTIDAD_FEDERATIVA   NaN   NaN   NaN   NaN  172.0   0.0   0.0   0.0   \n",
       "10          GPO_FUNCIONAL   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "11                  ID_UR   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "12         DESC_MODALIDAD   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "13             ID_FUNCION   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "14  ID_ENTIDAD_FEDERATIVA   NaN   NaN   NaN   NaN    0.0   0.0   0.0   0.0   \n",
       "15           MONTO_PAGADO   NaN   NaN   NaN   NaN    NaN   NaN   0.0   0.0   \n",
       "16         MONTO_EJERCIDO   0.0   0.0   0.0   0.0    0.0   NaN   NaN   NaN   \n",
       "17                DESC_PP   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "18     DESC_GPO_FUNCIONAL   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "19           ID_MODALIDAD   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "20          DESC_CONCEPTO   2.0   2.0   0.0   0.0   28.0   0.0   0.0   0.0   \n",
       "21            ID_CONCEPTO   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "22           ID_TIPOGASTO   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "23              DESC_RAMO   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "24            ID_CAPITULO   NaN   NaN   NaN   NaN    NaN   NaN   NaN   NaN   \n",
       "25                  CICLO   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "26        DESC_SUBFUNCION   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "27           MONTO_ADEFAS   NaN   NaN   NaN   NaN    NaN   NaN   0.0   0.0   \n",
       "28        MONTO_DEVENGADO   NaN   NaN   NaN   NaN    NaN   NaN   0.0   0.0   \n",
       "29          ID_SUBFUNCION   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "30                  ID_AI   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "31                DESC_UR   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "32                  ID_FF   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "33         DESC_TIPOGASTO   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "34                  ID_PP   0.0   0.0   0.0   0.0    0.0   0.0   0.0   0.0   \n",
       "\n",
       "     2016  \n",
       "0     0.0  \n",
       "1     0.0  \n",
       "2     0.0  \n",
       "3     0.0  \n",
       "4     0.0  \n",
       "5     NaN  \n",
       "6     0.0  \n",
       "7     NaN  \n",
       "8     0.0  \n",
       "9     0.0  \n",
       "10    0.0  \n",
       "11    0.0  \n",
       "12    0.0  \n",
       "13    0.0  \n",
       "14    0.0  \n",
       "15  170.0  \n",
       "16  170.0  \n",
       "17    0.0  \n",
       "18    0.0  \n",
       "19    0.0  \n",
       "20    0.0  \n",
       "21    0.0  \n",
       "22    0.0  \n",
       "23    0.0  \n",
       "24    0.0  \n",
       "25    0.0  \n",
       "26    0.0  \n",
       "27    NaN  \n",
       "28    NaN  \n",
       "29    0.0  \n",
       "30    0.0  \n",
       "31    0.0  \n",
       "32    0.0  \n",
       "33    0.0  \n",
       "34    0.0  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Column</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MONTO_EJERCIDO</td>\n",
       "      <td>2.576692e+12</td>\n",
       "      <td>2.296086e+12</td>\n",
       "      <td>2.474100e+12</td>\n",
       "      <td>2.717372e+12</td>\n",
       "      <td>2.896331e+12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.160939e+12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MONTO_DEVENGADO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.076810e+12</td>\n",
       "      <td>5.508987e+12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MONTO_APROBADO</td>\n",
       "      <td>1.992356e+12</td>\n",
       "      <td>2.289715e+12</td>\n",
       "      <td>2.376915e+12</td>\n",
       "      <td>2.560196e+12</td>\n",
       "      <td>2.754868e+12</td>\n",
       "      <td>4.322619e+12</td>\n",
       "      <td>4.905401e+12</td>\n",
       "      <td>5.138442e+12</td>\n",
       "      <td>5.297126e+12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MONTO_PAGADO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.972712e+12</td>\n",
       "      <td>5.366652e+12</td>\n",
       "      <td>2.137679e+12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MONTO_MODIFICADO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.013990e+12</td>\n",
       "      <td>5.398608e+12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MONTO_ADEFAS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.694161e+10</td>\n",
       "      <td>3.112265e+10</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>MONTO_EJERCICIO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.617618e+12</td>\n",
       "      <td>5.010877e+12</td>\n",
       "      <td>5.399018e+12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Column          2008          2009          2010          2011  \\\n",
       "0    MONTO_EJERCIDO  2.576692e+12  2.296086e+12  2.474100e+12  2.717372e+12   \n",
       "1   MONTO_DEVENGADO           NaN           NaN           NaN           NaN   \n",
       "2    MONTO_APROBADO  1.992356e+12  2.289715e+12  2.376915e+12  2.560196e+12   \n",
       "3      MONTO_PAGADO           NaN           NaN           NaN           NaN   \n",
       "4  MONTO_MODIFICADO           NaN           NaN           NaN           NaN   \n",
       "5      MONTO_ADEFAS           NaN           NaN           NaN           NaN   \n",
       "6   MONTO_EJERCICIO           NaN           NaN           NaN           NaN   \n",
       "\n",
       "           2012          2013          2014          2015          2016  \n",
       "0  2.896331e+12           NaN           NaN           NaN  2.160939e+12  \n",
       "1           NaN           NaN  5.076810e+12  5.508987e+12           NaN  \n",
       "2  2.754868e+12  4.322619e+12  4.905401e+12  5.138442e+12  5.297126e+12  \n",
       "3           NaN           NaN  4.972712e+12  5.366652e+12  2.137679e+12  \n",
       "4           NaN           NaN  5.013990e+12  5.398608e+12           NaN  \n",
       "5           NaN           NaN  3.694161e+10  3.112265e+10           NaN  \n",
       "6           NaN  4.617618e+12  5.010877e+12  5.399018e+12           NaN  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sums"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CICLO</th>\n",
       "      <th>DESC_AI</th>\n",
       "      <th>DESC_CAPITULO</th>\n",
       "      <th>DESC_CONCEPTO</th>\n",
       "      <th>DESC_FF</th>\n",
       "      <th>DESC_FUNCION</th>\n",
       "      <th>DESC_GPO_FUNCIONAL</th>\n",
       "      <th>DESC_MODALIDAD</th>\n",
       "      <th>DESC_PARTIDA_ESPECIFICA</th>\n",
       "      <th>DESC_PARTIDA_GENERICA</th>\n",
       "      <th>...</th>\n",
       "      <th>ID_SUBFUNCION</th>\n",
       "      <th>ID_TIPOGASTO</th>\n",
       "      <th>ID_UR</th>\n",
       "      <th>MONTO_ADEFAS</th>\n",
       "      <th>MONTO_APROBADO</th>\n",
       "      <th>MONTO_DEVENGADO</th>\n",
       "      <th>MONTO_EJERCICIO</th>\n",
       "      <th>MONTO_EJERCIDO</th>\n",
       "      <th>MONTO_MODIFICADO</th>\n",
       "      <th>MONTO_PAGADO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23751</th>\n",
       "      <td>2016</td>\n",
       "      <td>Investigación del delito federal</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios básicos</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Justicia</td>\n",
       "      <td>Gobierno</td>\n",
       "      <td>Prestación de Servicios Públicos</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1.2.2</td>\n",
       "      <td>1</td>\n",
       "      <td>17.324</td>\n",
       "      <td>NaN</td>\n",
       "      <td>185600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>109847.60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>109847.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8727</th>\n",
       "      <td>2008</td>\n",
       "      <td>Política de ingresos equitativa y promotora de...</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Vestuario, blancos, prendas de protección y ar...</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Hacienda</td>\n",
       "      <td>Gobierno</td>\n",
       "      <td>Planeación, formulación, implementación, segui...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Vestuario, uniformes y blancos</td>\n",
       "      <td>...</td>\n",
       "      <td>1.3.1</td>\n",
       "      <td>1</td>\n",
       "      <td>6.300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4702.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26993.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28394</th>\n",
       "      <td>2013</td>\n",
       "      <td>Impulso a la reconversión productiva en materi...</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Seguridad social</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Agropecuaria, Silvicultura, Pesca y Caza</td>\n",
       "      <td>Desarrollo Económico</td>\n",
       "      <td>Planeación, seguimiento y evaluación de políti...</td>\n",
       "      <td>Cuotas para el seguro colectivo de retiro</td>\n",
       "      <td>Aportaciones para seguros</td>\n",
       "      <td>...</td>\n",
       "      <td>3.2.1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.138</td>\n",
       "      <td>NaN</td>\n",
       "      <td>95153.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>95034.65</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119718</th>\n",
       "      <td>2010</td>\n",
       "      <td>Fondo de Aportaciones para la Educación Tecnol...</td>\n",
       "      <td>Participaciones y aportaciones</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Educación</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Gasto Federalizado</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Aportaciones Federales a las entidades federat...</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0.2</td>\n",
       "      <td>1</td>\n",
       "      <td>33.416</td>\n",
       "      <td>NaN</td>\n",
       "      <td>178074.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>208797.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>275526</th>\n",
       "      <td>2015</td>\n",
       "      <td>Apoyo a la función pública y buen gobierno</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Seguridad social</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Coordinación de la Política de Gobierno</td>\n",
       "      <td>Gobierno</td>\n",
       "      <td>Apoyo a la función pública y al mejoramiento d...</td>\n",
       "      <td>Aportaciones al seguro de cesantía en edad ava...</td>\n",
       "      <td>Aportaciones de seguridad social</td>\n",
       "      <td>...</td>\n",
       "      <td>1.3.4</td>\n",
       "      <td>1</td>\n",
       "      <td>51.GYN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>44546.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122755</th>\n",
       "      <td>2014</td>\n",
       "      <td>Servicios de apoyo administrativo</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Remuneraciones adicionales y especiales</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Salud</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Apoyo al proceso presupuestario y para mejorar...</td>\n",
       "      <td>Compensaciones por servicios eventuales</td>\n",
       "      <td>Compensaciones</td>\n",
       "      <td>...</td>\n",
       "      <td>2.3.4</td>\n",
       "      <td>1</td>\n",
       "      <td>12.NBV</td>\n",
       "      <td>0.0</td>\n",
       "      <td>339368.0</td>\n",
       "      <td>339368.00</td>\n",
       "      <td>339368.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>339368.00</td>\n",
       "      <td>339368.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176570</th>\n",
       "      <td>2015</td>\n",
       "      <td>Investigación del delito federal</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Seguridad social</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Justicia</td>\n",
       "      <td>Gobierno</td>\n",
       "      <td>Prestación de Servicios Públicos</td>\n",
       "      <td>Aportaciones al FOVISSSTE</td>\n",
       "      <td>Aportaciones a fondos de vivienda</td>\n",
       "      <td>...</td>\n",
       "      <td>1.2.2</td>\n",
       "      <td>1</td>\n",
       "      <td>17.343</td>\n",
       "      <td>0.0</td>\n",
       "      <td>363069.0</td>\n",
       "      <td>311639.53</td>\n",
       "      <td>311639.53</td>\n",
       "      <td>NaN</td>\n",
       "      <td>311639.53</td>\n",
       "      <td>311639.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150069</th>\n",
       "      <td>2014</td>\n",
       "      <td>Manejo eficiente y sustentable del agua y prev...</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Seguridad social</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Agropecuaria, Silvicultura, Pesca y Caza</td>\n",
       "      <td>Desarrollo Económico</td>\n",
       "      <td>Regulación y supervisión</td>\n",
       "      <td>Depósitos para el ahorro solidario</td>\n",
       "      <td>Aportaciones al sistema para el retiro</td>\n",
       "      <td>...</td>\n",
       "      <td>3.2.5</td>\n",
       "      <td>1</td>\n",
       "      <td>16.B00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>170706.0</td>\n",
       "      <td>170706.00</td>\n",
       "      <td>170706.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>170706.00</td>\n",
       "      <td>170706.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112106</th>\n",
       "      <td>2013</td>\n",
       "      <td>Servicios de apoyo administrativo</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Herramientas, refacciones y accesorios menores</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Salud</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Apoyo al proceso presupuestario y para mejorar...</td>\n",
       "      <td>Refacciones y accesorios para equipo de cómput...</td>\n",
       "      <td>Refacciones y accesorios menores de equipo de ...</td>\n",
       "      <td>...</td>\n",
       "      <td>2.3.4</td>\n",
       "      <td>1</td>\n",
       "      <td>12.611</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3132.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118404</th>\n",
       "      <td>2011</td>\n",
       "      <td>Apoyo a pequeñas comunidades rurales</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios profesionales, cientificos, tecnicos...</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Urbanización, Vivienda y Desarrollo Regional</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Sujetos a Reglas de Operación</td>\n",
       "      <td>Subcontratación de servicios con terceros</td>\n",
       "      <td>Servicios profesionales, científicos y técnico...</td>\n",
       "      <td>...</td>\n",
       "      <td>2.3.3</td>\n",
       "      <td>7</td>\n",
       "      <td>20.143</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160373</th>\n",
       "      <td>2013</td>\n",
       "      <td>Investigación del delito federal</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Herramientas, refacciones y accesorios menores</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Justicia</td>\n",
       "      <td>Gobierno</td>\n",
       "      <td>Prestación de Servicios Públicos</td>\n",
       "      <td>Refacciones y accesorios menores de edificios</td>\n",
       "      <td>Refacciones y accesorios menores de edificios</td>\n",
       "      <td>...</td>\n",
       "      <td>1.2.2</td>\n",
       "      <td>1</td>\n",
       "      <td>17.324</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8610.30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250194</th>\n",
       "      <td>2015</td>\n",
       "      <td>TURISSSTE, Agencias de Servicios Turísticos</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Pago de estímulos a servidores públicos</td>\n",
       "      <td>Ingresos Propios</td>\n",
       "      <td>Protección Social</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Apoyo al proceso presupuestario y para mejorar...</td>\n",
       "      <td>Estímulos al personal operativo</td>\n",
       "      <td>Estímulos</td>\n",
       "      <td>...</td>\n",
       "      <td>2.6.9</td>\n",
       "      <td>1</td>\n",
       "      <td>51.GYN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6390.00</td>\n",
       "      <td>6390.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6390.00</td>\n",
       "      <td>6390.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19800</th>\n",
       "      <td>2011</td>\n",
       "      <td>Impulso a la Reconversión Productiva en Materi...</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Seguridad social</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Desarrollo Agropecuario y Forestal</td>\n",
       "      <td>Desarrollo Económico</td>\n",
       "      <td>Planeación, seguimiento y evaluación de políti...</td>\n",
       "      <td>Cuotas para el seguro de vida del personal civil</td>\n",
       "      <td>Aportaciones para seguros</td>\n",
       "      <td>...</td>\n",
       "      <td>3.2.1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.137</td>\n",
       "      <td>NaN</td>\n",
       "      <td>219214.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>186200.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61812</th>\n",
       "      <td>2011</td>\n",
       "      <td>Educación media superior de calidad</td>\n",
       "      <td>Transferencias, asignaciones, subsidios y otra...</td>\n",
       "      <td>Subsidios y subvenciones</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Educación</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Otros Subsidios</td>\n",
       "      <td>Sinaloa</td>\n",
       "      <td>Subsidios a Entidades Federativas y Municipios</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0.2</td>\n",
       "      <td>1</td>\n",
       "      <td>11.600</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7592258.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17751</th>\n",
       "      <td>2012</td>\n",
       "      <td>Promoción y coordinación de las políticas publ...</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Materiales de administracion, emision de docum...</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Protección Social</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Promoción y fomento</td>\n",
       "      <td>Materiales y útiles de oficina</td>\n",
       "      <td>Materiales, útiles y equipos menores de oficina</td>\n",
       "      <td>...</td>\n",
       "      <td>2.6.7</td>\n",
       "      <td>1</td>\n",
       "      <td>6.AYB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29200.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86416</th>\n",
       "      <td>2015</td>\n",
       "      <td>Carreteras eficientes, seguras y suficientes</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Materiales de administracion, emision de docum...</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Transporte</td>\n",
       "      <td>Desarrollo Económico</td>\n",
       "      <td>Proyectos de Inversión</td>\n",
       "      <td>Material de limpieza</td>\n",
       "      <td>Material de limpieza</td>\n",
       "      <td>...</td>\n",
       "      <td>3.5.1</td>\n",
       "      <td>3</td>\n",
       "      <td>9.641</td>\n",
       "      <td>0.0</td>\n",
       "      <td>87100.0</td>\n",
       "      <td>26993.65</td>\n",
       "      <td>26993.65</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26993.65</td>\n",
       "      <td>26993.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130212</th>\n",
       "      <td>2009</td>\n",
       "      <td>Apego a la Legalidad</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Otras prestaciones sociales y económicas</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Administración Pública</td>\n",
       "      <td>Gobierno</td>\n",
       "      <td>Apoyo a la función pública y al mejoramiento d...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pago por riesgo</td>\n",
       "      <td>...</td>\n",
       "      <td>1.8.3</td>\n",
       "      <td>1</td>\n",
       "      <td>27.110</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140606.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44434</th>\n",
       "      <td>2009</td>\n",
       "      <td>Puertos eficientes y competitivos</td>\n",
       "      <td>Inversion publica</td>\n",
       "      <td>Obra publica en bienes de dominio publico</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Comunicaciones y Transportes</td>\n",
       "      <td>Desarrollo Económico</td>\n",
       "      <td>Proyectos de Inversión</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>3.1.2</td>\n",
       "      <td>3</td>\n",
       "      <td>9.643</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161623</th>\n",
       "      <td>2012</td>\n",
       "      <td>Manejo eficiente y sustentable del agua y prev...</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Seguridad social</td>\n",
       "      <td>Recursos fiscales</td>\n",
       "      <td>Agropecuaria, Silvicultura, Pesca y Caza</td>\n",
       "      <td>Desarrollo Económico</td>\n",
       "      <td>Regulación y supervisión</td>\n",
       "      <td>Cuotas para el seguro de vida del personal civil</td>\n",
       "      <td>Aportaciones para seguros</td>\n",
       "      <td>...</td>\n",
       "      <td>3.2.5</td>\n",
       "      <td>1</td>\n",
       "      <td>16.B00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>123326.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>125228.31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>335707</th>\n",
       "      <td>2015</td>\n",
       "      <td>Servicios de apoyo administrativo</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Otros servicios generales</td>\n",
       "      <td>Ingresos Propios</td>\n",
       "      <td>Salud</td>\n",
       "      <td>Desarrollo Social</td>\n",
       "      <td>Apoyo al proceso presupuestario y para mejorar...</td>\n",
       "      <td>Otros impuestos y derechos</td>\n",
       "      <td>Impuestos y derechos</td>\n",
       "      <td>...</td>\n",
       "      <td>2.3.2</td>\n",
       "      <td>1</td>\n",
       "      <td>50.GYR</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5520156.0</td>\n",
       "      <td>4834243.00</td>\n",
       "      <td>4834244.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4834243.00</td>\n",
       "      <td>4834244.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 39 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       CICLO                                            DESC_AI  \\\n",
       "23751   2016                   Investigación del delito federal   \n",
       "8727    2008  Política de ingresos equitativa y promotora de...   \n",
       "28394   2013  Impulso a la reconversión productiva en materi...   \n",
       "119718  2010  Fondo de Aportaciones para la Educación Tecnol...   \n",
       "275526  2015         Apoyo a la función pública y buen gobierno   \n",
       "122755  2014                  Servicios de apoyo administrativo   \n",
       "176570  2015                   Investigación del delito federal   \n",
       "150069  2014  Manejo eficiente y sustentable del agua y prev...   \n",
       "112106  2013                  Servicios de apoyo administrativo   \n",
       "118404  2011               Apoyo a pequeñas comunidades rurales   \n",
       "160373  2013                   Investigación del delito federal   \n",
       "250194  2015        TURISSSTE, Agencias de Servicios Turísticos   \n",
       "19800   2011  Impulso a la Reconversión Productiva en Materi...   \n",
       "61812   2011                Educación media superior de calidad   \n",
       "17751   2012  Promoción y coordinación de las políticas publ...   \n",
       "86416   2015       Carreteras eficientes, seguras y suficientes   \n",
       "130212  2009                               Apego a la Legalidad   \n",
       "44434   2009                  Puertos eficientes y competitivos   \n",
       "161623  2012  Manejo eficiente y sustentable del agua y prev...   \n",
       "335707  2015                  Servicios de apoyo administrativo   \n",
       "\n",
       "                                            DESC_CAPITULO  \\\n",
       "23751                                 Servicios generales   \n",
       "8727                             Materiales y suministros   \n",
       "28394                                Servicios personales   \n",
       "119718                     Participaciones y aportaciones   \n",
       "275526                               Servicios personales   \n",
       "122755                               Servicios personales   \n",
       "176570                               Servicios personales   \n",
       "150069                               Servicios personales   \n",
       "112106                           Materiales y suministros   \n",
       "118404                                Servicios generales   \n",
       "160373                           Materiales y suministros   \n",
       "250194                               Servicios personales   \n",
       "19800                                Servicios personales   \n",
       "61812   Transferencias, asignaciones, subsidios y otra...   \n",
       "17751                            Materiales y suministros   \n",
       "86416                            Materiales y suministros   \n",
       "130212                               Servicios personales   \n",
       "44434                                   Inversion publica   \n",
       "161623                               Servicios personales   \n",
       "335707                                Servicios generales   \n",
       "\n",
       "                                            DESC_CONCEPTO            DESC_FF  \\\n",
       "23751                                   Servicios básicos  Recursos fiscales   \n",
       "8727    Vestuario, blancos, prendas de protección y ar...  Recursos fiscales   \n",
       "28394                                    Seguridad social  Recursos fiscales   \n",
       "119718                                                NaN  Recursos fiscales   \n",
       "275526                                   Seguridad social  Recursos fiscales   \n",
       "122755            Remuneraciones adicionales y especiales  Recursos fiscales   \n",
       "176570                                   Seguridad social  Recursos fiscales   \n",
       "150069                                   Seguridad social  Recursos fiscales   \n",
       "112106     Herramientas, refacciones y accesorios menores  Recursos fiscales   \n",
       "118404  Servicios profesionales, cientificos, tecnicos...  Recursos fiscales   \n",
       "160373     Herramientas, refacciones y accesorios menores  Recursos fiscales   \n",
       "250194            Pago de estímulos a servidores públicos   Ingresos Propios   \n",
       "19800                                    Seguridad social  Recursos fiscales   \n",
       "61812                            Subsidios y subvenciones  Recursos fiscales   \n",
       "17751   Materiales de administracion, emision de docum...  Recursos fiscales   \n",
       "86416   Materiales de administracion, emision de docum...  Recursos fiscales   \n",
       "130212           Otras prestaciones sociales y económicas  Recursos fiscales   \n",
       "44434           Obra publica en bienes de dominio publico  Recursos fiscales   \n",
       "161623                                   Seguridad social  Recursos fiscales   \n",
       "335707                          Otros servicios generales   Ingresos Propios   \n",
       "\n",
       "                                        DESC_FUNCION    DESC_GPO_FUNCIONAL  \\\n",
       "23751                                       Justicia              Gobierno   \n",
       "8727                                        Hacienda              Gobierno   \n",
       "28394       Agropecuaria, Silvicultura, Pesca y Caza  Desarrollo Económico   \n",
       "119718                                     Educación     Desarrollo Social   \n",
       "275526       Coordinación de la Política de Gobierno              Gobierno   \n",
       "122755                                         Salud     Desarrollo Social   \n",
       "176570                                      Justicia              Gobierno   \n",
       "150069      Agropecuaria, Silvicultura, Pesca y Caza  Desarrollo Económico   \n",
       "112106                                         Salud     Desarrollo Social   \n",
       "118404  Urbanización, Vivienda y Desarrollo Regional     Desarrollo Social   \n",
       "160373                                      Justicia              Gobierno   \n",
       "250194                             Protección Social     Desarrollo Social   \n",
       "19800             Desarrollo Agropecuario y Forestal  Desarrollo Económico   \n",
       "61812                                      Educación     Desarrollo Social   \n",
       "17751                              Protección Social     Desarrollo Social   \n",
       "86416                                     Transporte  Desarrollo Económico   \n",
       "130212                        Administración Pública              Gobierno   \n",
       "44434                   Comunicaciones y Transportes  Desarrollo Económico   \n",
       "161623      Agropecuaria, Silvicultura, Pesca y Caza  Desarrollo Económico   \n",
       "335707                                         Salud     Desarrollo Social   \n",
       "\n",
       "                                           DESC_MODALIDAD  \\\n",
       "23751                    Prestación de Servicios Públicos   \n",
       "8727    Planeación, formulación, implementación, segui...   \n",
       "28394   Planeación, seguimiento y evaluación de políti...   \n",
       "119718                                 Gasto Federalizado   \n",
       "275526  Apoyo a la función pública y al mejoramiento d...   \n",
       "122755  Apoyo al proceso presupuestario y para mejorar...   \n",
       "176570                   Prestación de Servicios Públicos   \n",
       "150069                           Regulación y supervisión   \n",
       "112106  Apoyo al proceso presupuestario y para mejorar...   \n",
       "118404                      Sujetos a Reglas de Operación   \n",
       "160373                   Prestación de Servicios Públicos   \n",
       "250194  Apoyo al proceso presupuestario y para mejorar...   \n",
       "19800   Planeación, seguimiento y evaluación de políti...   \n",
       "61812                                     Otros Subsidios   \n",
       "17751                                 Promoción y fomento   \n",
       "86416                              Proyectos de Inversión   \n",
       "130212  Apoyo a la función pública y al mejoramiento d...   \n",
       "44434                              Proyectos de Inversión   \n",
       "161623                           Regulación y supervisión   \n",
       "335707  Apoyo al proceso presupuestario y para mejorar...   \n",
       "\n",
       "                                  DESC_PARTIDA_ESPECIFICA  \\\n",
       "23751                                                 NaN   \n",
       "8727                                                  NaN   \n",
       "28394           Cuotas para el seguro colectivo de retiro   \n",
       "119718                                                NaN   \n",
       "275526  Aportaciones al seguro de cesantía en edad ava...   \n",
       "122755            Compensaciones por servicios eventuales   \n",
       "176570                          Aportaciones al FOVISSSTE   \n",
       "150069                 Depósitos para el ahorro solidario   \n",
       "112106  Refacciones y accesorios para equipo de cómput...   \n",
       "118404          Subcontratación de servicios con terceros   \n",
       "160373      Refacciones y accesorios menores de edificios   \n",
       "250194                    Estímulos al personal operativo   \n",
       "19800    Cuotas para el seguro de vida del personal civil   \n",
       "61812                                             Sinaloa   \n",
       "17751                      Materiales y útiles de oficina   \n",
       "86416                                Material de limpieza   \n",
       "130212                                                NaN   \n",
       "44434                                                 NaN   \n",
       "161623   Cuotas para el seguro de vida del personal civil   \n",
       "335707                         Otros impuestos y derechos   \n",
       "\n",
       "                                    DESC_PARTIDA_GENERICA     ...       \\\n",
       "23751                                                 NaN     ...        \n",
       "8727                       Vestuario, uniformes y blancos     ...        \n",
       "28394                           Aportaciones para seguros     ...        \n",
       "119718  Aportaciones Federales a las entidades federat...     ...        \n",
       "275526                   Aportaciones de seguridad social     ...        \n",
       "122755                                     Compensaciones     ...        \n",
       "176570                  Aportaciones a fondos de vivienda     ...        \n",
       "150069             Aportaciones al sistema para el retiro     ...        \n",
       "112106  Refacciones y accesorios menores de equipo de ...     ...        \n",
       "118404  Servicios profesionales, científicos y técnico...     ...        \n",
       "160373      Refacciones y accesorios menores de edificios     ...        \n",
       "250194                                          Estímulos     ...        \n",
       "19800                           Aportaciones para seguros     ...        \n",
       "61812      Subsidios a Entidades Federativas y Municipios     ...        \n",
       "17751     Materiales, útiles y equipos menores de oficina     ...        \n",
       "86416                                Material de limpieza     ...        \n",
       "130212                                    Pago por riesgo     ...        \n",
       "44434                                                 NaN     ...        \n",
       "161623                          Aportaciones para seguros     ...        \n",
       "335707                               Impuestos y derechos     ...        \n",
       "\n",
       "       ID_SUBFUNCION ID_TIPOGASTO   ID_UR MONTO_ADEFAS MONTO_APROBADO  \\\n",
       "23751          1.2.2            1  17.324          NaN       185600.0   \n",
       "8727           1.3.1            1   6.300          NaN         4702.0   \n",
       "28394          3.2.1            1   8.138          NaN        95153.0   \n",
       "119718         2.0.2            1  33.416          NaN       178074.0   \n",
       "275526         1.3.4            1  51.GYN          0.0        44546.0   \n",
       "122755         2.3.4            1  12.NBV          0.0       339368.0   \n",
       "176570         1.2.2            1  17.343          0.0       363069.0   \n",
       "150069         3.2.5            1  16.B00          0.0       170706.0   \n",
       "112106         2.3.4            1  12.611          NaN        12000.0   \n",
       "118404         2.3.3            7  20.143          NaN            0.0   \n",
       "160373         1.2.2            1  17.324          NaN            0.0   \n",
       "250194         2.6.9            1  51.GYN          0.0            0.0   \n",
       "19800          3.2.1            1   8.137          NaN       219214.0   \n",
       "61812          2.0.2            1  11.600          NaN            0.0   \n",
       "17751          2.6.7            1   6.AYB          NaN        30000.0   \n",
       "86416          3.5.1            3   9.641          0.0        87100.0   \n",
       "130212         1.8.3            1  27.110          NaN            0.0   \n",
       "44434          3.1.2            3   9.643          NaN            0.0   \n",
       "161623         3.2.5            1  16.B00          NaN       123326.0   \n",
       "335707         2.3.2            1  50.GYR          0.0      5520156.0   \n",
       "\n",
       "       MONTO_DEVENGADO MONTO_EJERCICIO MONTO_EJERCIDO MONTO_MODIFICADO  \\\n",
       "23751              NaN             NaN      109847.60              NaN   \n",
       "8727               NaN             NaN       26993.00              NaN   \n",
       "28394              NaN        95034.65            NaN              NaN   \n",
       "119718             NaN             NaN      208797.00              NaN   \n",
       "275526            0.00            0.00            NaN             0.00   \n",
       "122755       339368.00       339368.00            NaN        339368.00   \n",
       "176570       311639.53       311639.53            NaN        311639.53   \n",
       "150069       170706.00       170706.00            NaN        170706.00   \n",
       "112106             NaN         3132.00            NaN              NaN   \n",
       "118404             NaN             NaN           0.00              NaN   \n",
       "160373             NaN         8610.30            NaN              NaN   \n",
       "250194         6390.00         6390.00            NaN          6390.00   \n",
       "19800              NaN             NaN      186200.00              NaN   \n",
       "61812              NaN             NaN     7592258.00              NaN   \n",
       "17751              NaN             NaN       29200.00              NaN   \n",
       "86416         26993.65        26993.65            NaN         26993.65   \n",
       "130212             NaN             NaN      140606.00              NaN   \n",
       "44434              NaN             NaN           0.00              NaN   \n",
       "161623             NaN             NaN      125228.31              NaN   \n",
       "335707      4834243.00      4834244.00            NaN       4834243.00   \n",
       "\n",
       "       MONTO_PAGADO  \n",
       "23751     109847.60  \n",
       "8727            NaN  \n",
       "28394           NaN  \n",
       "119718          NaN  \n",
       "275526         0.00  \n",
       "122755    339368.00  \n",
       "176570    311639.53  \n",
       "150069    170706.00  \n",
       "112106          NaN  \n",
       "118404          NaN  \n",
       "160373          NaN  \n",
       "250194      6390.00  \n",
       "19800           NaN  \n",
       "61812           NaN  \n",
       "17751           NaN  \n",
       "86416      26993.65  \n",
       "130212          NaN  \n",
       "44434           NaN  \n",
       "161623          NaN  \n",
       "335707   4834244.00  \n",
       "\n",
       "[20 rows x 39 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.sample(n=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CICLO</th>\n",
       "      <th>ID_CAPITULO</th>\n",
       "      <th>ID_CONCEPTO</th>\n",
       "      <th>ID_PARTIDA_GENERICA</th>\n",
       "      <th>ID_PARTIDA_ESPECIFICA</th>\n",
       "      <th>DESC_CAPITULO</th>\n",
       "      <th>DESC_CONCEPTO</th>\n",
       "      <th>DESC_PARTIDA_GENERICA</th>\n",
       "      <th>DESC_PARTIDA_ESPECIFICA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>235920</th>\n",
       "      <td>2013</td>\n",
       "      <td>2000</td>\n",
       "      <td>2200</td>\n",
       "      <td>221</td>\n",
       "      <td>22104</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Alimentos y utensilios</td>\n",
       "      <td>Productos alimenticios para personas</td>\n",
       "      <td>Productos alimenticios para el personal en las...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166236</th>\n",
       "      <td>2015</td>\n",
       "      <td>3000</td>\n",
       "      <td>3700</td>\n",
       "      <td>375</td>\n",
       "      <td>37501</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios de traslado y viáticos</td>\n",
       "      <td>Viáticos en el país</td>\n",
       "      <td>Viáticos nacionales para labores en campo y de...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60437</th>\n",
       "      <td>2015</td>\n",
       "      <td>3000</td>\n",
       "      <td>3200</td>\n",
       "      <td>325</td>\n",
       "      <td>32505</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios de arrendamiento</td>\n",
       "      <td>Arrendamiento de equipo de transporte</td>\n",
       "      <td>Arrendamiento de vehículos terrestres, aéreos,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22466</th>\n",
       "      <td>2013</td>\n",
       "      <td>2000</td>\n",
       "      <td>2600</td>\n",
       "      <td>261</td>\n",
       "      <td>26103</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Combustibles, lubricantes y aditivos</td>\n",
       "      <td>Combustibles, lubricantes y aditivos</td>\n",
       "      <td>Combustibles, lubricantes y aditivos para vehí...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41044</th>\n",
       "      <td>2012</td>\n",
       "      <td>3000</td>\n",
       "      <td>3100</td>\n",
       "      <td>313</td>\n",
       "      <td>31301</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios basicos</td>\n",
       "      <td>Agua</td>\n",
       "      <td>Servicio de agua</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44210</th>\n",
       "      <td>2012</td>\n",
       "      <td>3000</td>\n",
       "      <td>3100</td>\n",
       "      <td>314</td>\n",
       "      <td>31401</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios basicos</td>\n",
       "      <td>Telefonía tradicional</td>\n",
       "      <td>Servicio telefónico convencional</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93598</th>\n",
       "      <td>2016</td>\n",
       "      <td>2000</td>\n",
       "      <td>2200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Alimentos y utensilios</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121252</th>\n",
       "      <td>2010</td>\n",
       "      <td>8000</td>\n",
       "      <td>8400</td>\n",
       "      <td>8403</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Participaciones y aportaciones</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Aportaciones Federales a las entidades federat...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34175</th>\n",
       "      <td>2013</td>\n",
       "      <td>3000</td>\n",
       "      <td>3200</td>\n",
       "      <td>327</td>\n",
       "      <td>32701</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios de arrendamiento</td>\n",
       "      <td>Arrendamiento de activos intangibles</td>\n",
       "      <td>Patentes, derechos de autor, regalías y otros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206076</th>\n",
       "      <td>2013</td>\n",
       "      <td>1000</td>\n",
       "      <td>1600</td>\n",
       "      <td>161</td>\n",
       "      <td>16101</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Previsiones</td>\n",
       "      <td>Previsiones de carácter laboral, económica y d...</td>\n",
       "      <td>Incrementos a las percepciones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39924</th>\n",
       "      <td>2016</td>\n",
       "      <td>3000</td>\n",
       "      <td>3900</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Otros servicios generales</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120151</th>\n",
       "      <td>2015</td>\n",
       "      <td>1000</td>\n",
       "      <td>1400</td>\n",
       "      <td>142</td>\n",
       "      <td>14201</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Seguridad social</td>\n",
       "      <td>Aportaciones a fondos de vivienda</td>\n",
       "      <td>Aportaciones al FOVISSSTE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49461</th>\n",
       "      <td>2010</td>\n",
       "      <td>1000</td>\n",
       "      <td>1400</td>\n",
       "      <td>1406</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Seguridad social</td>\n",
       "      <td>Cuotas para el seguro de gastos médicos del pe...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36137</th>\n",
       "      <td>2010</td>\n",
       "      <td>1000</td>\n",
       "      <td>1500</td>\n",
       "      <td>1509</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Otras prestaciones sociales y económicas</td>\n",
       "      <td>Compensación garantizada</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36164</th>\n",
       "      <td>2011</td>\n",
       "      <td>2000</td>\n",
       "      <td>2400</td>\n",
       "      <td>249</td>\n",
       "      <td>24901</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Materiales y articulos de construccion y de re...</td>\n",
       "      <td>Otros materiales y artículos de construcción y...</td>\n",
       "      <td>Otros materiales y artículos de construcción y...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68762</th>\n",
       "      <td>2013</td>\n",
       "      <td>1000</td>\n",
       "      <td>1400</td>\n",
       "      <td>144</td>\n",
       "      <td>14403</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Seguridad social</td>\n",
       "      <td>Aportaciones para seguros</td>\n",
       "      <td>Cuotas para el seguro de gastos médicos del pe...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201948</th>\n",
       "      <td>2015</td>\n",
       "      <td>3000</td>\n",
       "      <td>3100</td>\n",
       "      <td>315</td>\n",
       "      <td>31501</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios basicos</td>\n",
       "      <td>Telefonía celular</td>\n",
       "      <td>Servicio de telefonía celular</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135390</th>\n",
       "      <td>2012</td>\n",
       "      <td>2000</td>\n",
       "      <td>2500</td>\n",
       "      <td>259</td>\n",
       "      <td>25901</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Productos químicos, farmacéuticos y de laborat...</td>\n",
       "      <td>Otros productos químicos</td>\n",
       "      <td>Otros productos químicos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47416</th>\n",
       "      <td>2014</td>\n",
       "      <td>3000</td>\n",
       "      <td>3500</td>\n",
       "      <td>358</td>\n",
       "      <td>35801</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios de instalacion, reparacion, mantenim...</td>\n",
       "      <td>Servicios de limpieza y manejo de desechos</td>\n",
       "      <td>Servicios de lavandería, limpieza e higiene</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30527</th>\n",
       "      <td>2015</td>\n",
       "      <td>2000</td>\n",
       "      <td>2700</td>\n",
       "      <td>274</td>\n",
       "      <td>27401</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Vestuario, blancos, prendas de protección y ar...</td>\n",
       "      <td>Productos textiles</td>\n",
       "      <td>Productos textiles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22891</th>\n",
       "      <td>2011</td>\n",
       "      <td>3000</td>\n",
       "      <td>3700</td>\n",
       "      <td>372</td>\n",
       "      <td>37206</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios de traslado y viáticos</td>\n",
       "      <td>Pasajes terrestres</td>\n",
       "      <td>Pasajes terrestres internacionales para servid...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7300</th>\n",
       "      <td>2009</td>\n",
       "      <td>1000</td>\n",
       "      <td>1700</td>\n",
       "      <td>1702</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Pago de estímulos a servidores públicos</td>\n",
       "      <td>Estímulos al personal operativo</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73105</th>\n",
       "      <td>2011</td>\n",
       "      <td>3000</td>\n",
       "      <td>3100</td>\n",
       "      <td>315</td>\n",
       "      <td>31501</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios basicos</td>\n",
       "      <td>Telefonía celular</td>\n",
       "      <td>Servicio de telefonía celular</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76902</th>\n",
       "      <td>2011</td>\n",
       "      <td>2000</td>\n",
       "      <td>2500</td>\n",
       "      <td>254</td>\n",
       "      <td>25401</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Productos químicos, farmacéuticos y de laborat...</td>\n",
       "      <td>Materiales, accesorios y suministros médicos</td>\n",
       "      <td>Materiales, accesorios y suministros médicos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121961</th>\n",
       "      <td>2015</td>\n",
       "      <td>3000</td>\n",
       "      <td>3200</td>\n",
       "      <td>322</td>\n",
       "      <td>32201</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios de arrendamiento</td>\n",
       "      <td>Arrendamiento de edificios</td>\n",
       "      <td>Arrendamiento de edificios y locales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82707</th>\n",
       "      <td>2012</td>\n",
       "      <td>3000</td>\n",
       "      <td>3700</td>\n",
       "      <td>375</td>\n",
       "      <td>37501</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios de traslado y viáticos</td>\n",
       "      <td>Viáticos en el país</td>\n",
       "      <td>Viáticos nacionales para labores en campo y de...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115594</th>\n",
       "      <td>2008</td>\n",
       "      <td>3000</td>\n",
       "      <td>3500</td>\n",
       "      <td>3501</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios de instalacion, reparacion, mantenim...</td>\n",
       "      <td>Mantenimiento y conservación de mobiliario y e...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269444</th>\n",
       "      <td>2014</td>\n",
       "      <td>3000</td>\n",
       "      <td>3300</td>\n",
       "      <td>338</td>\n",
       "      <td>33801</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios profesionales, cientificos, tecnicos...</td>\n",
       "      <td>Servicios de vigilancia</td>\n",
       "      <td>Servicios de vigilancia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16717</th>\n",
       "      <td>2016</td>\n",
       "      <td>2000</td>\n",
       "      <td>2200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Alimentos y utensilios</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89529</th>\n",
       "      <td>2012</td>\n",
       "      <td>2000</td>\n",
       "      <td>2200</td>\n",
       "      <td>221</td>\n",
       "      <td>22104</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Alimentos y utensilios</td>\n",
       "      <td>Productos alimenticios para personas</td>\n",
       "      <td>Productos alimenticios para el personal en las...</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46612</th>\n",
       "      <td>2011</td>\n",
       "      <td>4000</td>\n",
       "      <td>4300</td>\n",
       "      <td>434</td>\n",
       "      <td>43401</td>\n",
       "      <td>Transferencias, asignaciones, subsidios y otra...</td>\n",
       "      <td>Subsidios y subvenciones</td>\n",
       "      <td>Subsidios a la prestación de servicios públicos</td>\n",
       "      <td>Subsidios a la prestación de servicios públicos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36976</th>\n",
       "      <td>2016</td>\n",
       "      <td>3000</td>\n",
       "      <td>3300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios profesionales, científicos, técnicos...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249879</th>\n",
       "      <td>2013</td>\n",
       "      <td>3000</td>\n",
       "      <td>3100</td>\n",
       "      <td>319</td>\n",
       "      <td>31902</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios basicos</td>\n",
       "      <td>Servicios integrales y otros servicios</td>\n",
       "      <td>Contratación de otros servicios</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50672</th>\n",
       "      <td>2014</td>\n",
       "      <td>3000</td>\n",
       "      <td>3100</td>\n",
       "      <td>314</td>\n",
       "      <td>31401</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios basicos</td>\n",
       "      <td>Telefonía tradicional</td>\n",
       "      <td>Servicio telefónico convencional</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171878</th>\n",
       "      <td>2015</td>\n",
       "      <td>4000</td>\n",
       "      <td>4300</td>\n",
       "      <td>438</td>\n",
       "      <td>43801</td>\n",
       "      <td>Transferencias, asignaciones, subsidios y otra...</td>\n",
       "      <td>Subsidios y subvenciones</td>\n",
       "      <td>Subsidios a Entidades Federativas y Municipios</td>\n",
       "      <td>Subsidios a Entidades Federativas y Municipios</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76772</th>\n",
       "      <td>2012</td>\n",
       "      <td>2000</td>\n",
       "      <td>2100</td>\n",
       "      <td>211</td>\n",
       "      <td>21101</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Materiales de administracion, emision de docum...</td>\n",
       "      <td>Materiales, útiles y equipos menores de oficina</td>\n",
       "      <td>Materiales y útiles de oficina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135644</th>\n",
       "      <td>2014</td>\n",
       "      <td>2000</td>\n",
       "      <td>2100</td>\n",
       "      <td>217</td>\n",
       "      <td>21701</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Materiales de administracion, emision de docum...</td>\n",
       "      <td>Materiales y útiles de enseñanza</td>\n",
       "      <td>Materiales y suministros para planteles educat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148324</th>\n",
       "      <td>2012</td>\n",
       "      <td>4000</td>\n",
       "      <td>4300</td>\n",
       "      <td>439</td>\n",
       "      <td>43901</td>\n",
       "      <td>Transferencias, asignaciones, subsidios y otra...</td>\n",
       "      <td>Subsidios y subvenciones</td>\n",
       "      <td>Otros Subsidios</td>\n",
       "      <td>Subsidios para capacitación y becas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96309</th>\n",
       "      <td>2010</td>\n",
       "      <td>3000</td>\n",
       "      <td>3600</td>\n",
       "      <td>3607</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios de comunicación social y publicidad</td>\n",
       "      <td>Servicios relacionados con monitoreo de inform...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7923</th>\n",
       "      <td>2013</td>\n",
       "      <td>3000</td>\n",
       "      <td>3700</td>\n",
       "      <td>371</td>\n",
       "      <td>37101</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios de traslado y viáticos</td>\n",
       "      <td>Pasajes aéreos</td>\n",
       "      <td>Pasajes aéreos nacionales para labores en camp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104367</th>\n",
       "      <td>2010</td>\n",
       "      <td>1000</td>\n",
       "      <td>1400</td>\n",
       "      <td>1413</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Seguridad social</td>\n",
       "      <td>Aportaciones al Sistema de Ahorro para el Retiro</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24074</th>\n",
       "      <td>2012</td>\n",
       "      <td>3000</td>\n",
       "      <td>3200</td>\n",
       "      <td>322</td>\n",
       "      <td>32201</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios de arrendamiento</td>\n",
       "      <td>Arrendamiento de edificios</td>\n",
       "      <td>Arrendamiento de edificios y locales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35739</th>\n",
       "      <td>2015</td>\n",
       "      <td>2000</td>\n",
       "      <td>2400</td>\n",
       "      <td>242</td>\n",
       "      <td>24201</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Materiales y articulos de construccion y de re...</td>\n",
       "      <td>Cemento y productos de concreto</td>\n",
       "      <td>Cemento y productos de concreto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84408</th>\n",
       "      <td>2009</td>\n",
       "      <td>1000</td>\n",
       "      <td>1400</td>\n",
       "      <td>1404</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Seguridad social</td>\n",
       "      <td>Cuotas para el seguro de vida del personal civil</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40747</th>\n",
       "      <td>2009</td>\n",
       "      <td>1000</td>\n",
       "      <td>1300</td>\n",
       "      <td>1306</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Remuneraciones adicionales y especiales</td>\n",
       "      <td>Gratificación de fin de año</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140028</th>\n",
       "      <td>2009</td>\n",
       "      <td>7000</td>\n",
       "      <td>7500</td>\n",
       "      <td>7502</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Inversiones financieras y otras provisiones</td>\n",
       "      <td>Inversiones en fideicomisos, mandatos y otros ...</td>\n",
       "      <td>Gastos por servicios de traslado de personas</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59283</th>\n",
       "      <td>2010</td>\n",
       "      <td>1000</td>\n",
       "      <td>1700</td>\n",
       "      <td>1702</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Pago de estímulos a servidores públicos</td>\n",
       "      <td>Estímulos al personal operativo</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53211</th>\n",
       "      <td>2015</td>\n",
       "      <td>2000</td>\n",
       "      <td>2100</td>\n",
       "      <td>211</td>\n",
       "      <td>21101</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Materiales de administracion, emision de docum...</td>\n",
       "      <td>Materiales, útiles y equipos menores de oficina</td>\n",
       "      <td>Materiales y útiles de oficina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300158</th>\n",
       "      <td>2014</td>\n",
       "      <td>1000</td>\n",
       "      <td>1500</td>\n",
       "      <td>159</td>\n",
       "      <td>15901</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Otras prestaciones sociales y económicas</td>\n",
       "      <td>Otras prestaciones sociales y económicas</td>\n",
       "      <td>Otras prestaciones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90243</th>\n",
       "      <td>2009</td>\n",
       "      <td>3000</td>\n",
       "      <td>3400</td>\n",
       "      <td>3402</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios financieros, bancarios y comerciales</td>\n",
       "      <td>Fletes y maniobras</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303087</th>\n",
       "      <td>2015</td>\n",
       "      <td>1000</td>\n",
       "      <td>1400</td>\n",
       "      <td>144</td>\n",
       "      <td>14404</td>\n",
       "      <td>Servicios personales</td>\n",
       "      <td>Seguridad social</td>\n",
       "      <td>Aportaciones para seguros</td>\n",
       "      <td>Cuotas para el seguro de separación individual...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170265</th>\n",
       "      <td>2014</td>\n",
       "      <td>3000</td>\n",
       "      <td>3700</td>\n",
       "      <td>375</td>\n",
       "      <td>37504</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios de traslado y viáticos</td>\n",
       "      <td>Viáticos en el país</td>\n",
       "      <td>Viáticos nacionales para servidores públicos e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142524</th>\n",
       "      <td>2015</td>\n",
       "      <td>2000</td>\n",
       "      <td>2100</td>\n",
       "      <td>214</td>\n",
       "      <td>21401</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Materiales de administracion, emision de docum...</td>\n",
       "      <td>Materiales, útiles y equipos menores de tecnol...</td>\n",
       "      <td>Materiales y útiles consumibles para el proces...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116548</th>\n",
       "      <td>2013</td>\n",
       "      <td>2000</td>\n",
       "      <td>2700</td>\n",
       "      <td>271</td>\n",
       "      <td>27101</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Vestuario, blancos, prendas de protección y ar...</td>\n",
       "      <td>Vestuario y uniformes</td>\n",
       "      <td>Vestuario y uniformes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116026</th>\n",
       "      <td>2008</td>\n",
       "      <td>2000</td>\n",
       "      <td>2100</td>\n",
       "      <td>2105</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Materiales de administracion, emision de docum...</td>\n",
       "      <td>Materiales y útiles de impresión y reproducción</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23655</th>\n",
       "      <td>2008</td>\n",
       "      <td>2000</td>\n",
       "      <td>2400</td>\n",
       "      <td>2403</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Materiales y articulos de construccion y de re...</td>\n",
       "      <td>Materiales complementarios</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118661</th>\n",
       "      <td>2013</td>\n",
       "      <td>2000</td>\n",
       "      <td>2200</td>\n",
       "      <td>221</td>\n",
       "      <td>22104</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Alimentos y utensilios</td>\n",
       "      <td>Productos alimenticios para personas</td>\n",
       "      <td>Productos alimenticios para el personal en las...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282352</th>\n",
       "      <td>2014</td>\n",
       "      <td>3000</td>\n",
       "      <td>3900</td>\n",
       "      <td>394</td>\n",
       "      <td>39401</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Otros servicios generales</td>\n",
       "      <td>Sentencias y resoluciones por autoridad compet...</td>\n",
       "      <td>Erogaciones por resoluciones por autoridad com...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126348</th>\n",
       "      <td>2014</td>\n",
       "      <td>3000</td>\n",
       "      <td>3300</td>\n",
       "      <td>339</td>\n",
       "      <td>33903</td>\n",
       "      <td>Servicios generales</td>\n",
       "      <td>Servicios profesionales, cientificos, tecnicos...</td>\n",
       "      <td>Servicios profesionales, científicos y técnico...</td>\n",
       "      <td>Servicios integrales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181138</th>\n",
       "      <td>2015</td>\n",
       "      <td>2000</td>\n",
       "      <td>2700</td>\n",
       "      <td>271</td>\n",
       "      <td>27101</td>\n",
       "      <td>Materiales y suministros</td>\n",
       "      <td>Vestuario, blancos, prendas de protección y ar...</td>\n",
       "      <td>Vestuario y uniformes</td>\n",
       "      <td>Vestuario y uniformes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>200 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       CICLO ID_CAPITULO ID_CONCEPTO ID_PARTIDA_GENERICA  \\\n",
       "235920  2013        2000        2200                 221   \n",
       "166236  2015        3000        3700                 375   \n",
       "60437   2015        3000        3200                 325   \n",
       "22466   2013        2000        2600                 261   \n",
       "41044   2012        3000        3100                 313   \n",
       "44210   2012        3000        3100                 314   \n",
       "93598   2016        2000        2200                 NaN   \n",
       "121252  2010        8000        8400                8403   \n",
       "34175   2013        3000        3200                 327   \n",
       "206076  2013        1000        1600                 161   \n",
       "39924   2016        3000        3900                 NaN   \n",
       "120151  2015        1000        1400                 142   \n",
       "49461   2010        1000        1400                1406   \n",
       "36137   2010        1000        1500                1509   \n",
       "36164   2011        2000        2400                 249   \n",
       "68762   2013        1000        1400                 144   \n",
       "201948  2015        3000        3100                 315   \n",
       "135390  2012        2000        2500                 259   \n",
       "47416   2014        3000        3500                 358   \n",
       "30527   2015        2000        2700                 274   \n",
       "22891   2011        3000        3700                 372   \n",
       "7300    2009        1000        1700                1702   \n",
       "73105   2011        3000        3100                 315   \n",
       "76902   2011        2000        2500                 254   \n",
       "121961  2015        3000        3200                 322   \n",
       "82707   2012        3000        3700                 375   \n",
       "115594  2008        3000        3500                3501   \n",
       "269444  2014        3000        3300                 338   \n",
       "16717   2016        2000        2200                 NaN   \n",
       "89529   2012        2000        2200                 221   \n",
       "...      ...         ...         ...                 ...   \n",
       "46612   2011        4000        4300                 434   \n",
       "36976   2016        3000        3300                 NaN   \n",
       "249879  2013        3000        3100                 319   \n",
       "50672   2014        3000        3100                 314   \n",
       "171878  2015        4000        4300                 438   \n",
       "76772   2012        2000        2100                 211   \n",
       "135644  2014        2000        2100                 217   \n",
       "148324  2012        4000        4300                 439   \n",
       "96309   2010        3000        3600                3607   \n",
       "7923    2013        3000        3700                 371   \n",
       "104367  2010        1000        1400                1413   \n",
       "24074   2012        3000        3200                 322   \n",
       "35739   2015        2000        2400                 242   \n",
       "84408   2009        1000        1400                1404   \n",
       "40747   2009        1000        1300                1306   \n",
       "140028  2009        7000        7500                7502   \n",
       "59283   2010        1000        1700                1702   \n",
       "53211   2015        2000        2100                 211   \n",
       "300158  2014        1000        1500                 159   \n",
       "90243   2009        3000        3400                3402   \n",
       "303087  2015        1000        1400                 144   \n",
       "170265  2014        3000        3700                 375   \n",
       "142524  2015        2000        2100                 214   \n",
       "116548  2013        2000        2700                 271   \n",
       "116026  2008        2000        2100                2105   \n",
       "23655   2008        2000        2400                2403   \n",
       "118661  2013        2000        2200                 221   \n",
       "282352  2014        3000        3900                 394   \n",
       "126348  2014        3000        3300                 339   \n",
       "181138  2015        2000        2700                 271   \n",
       "\n",
       "       ID_PARTIDA_ESPECIFICA  \\\n",
       "235920                 22104   \n",
       "166236                 37501   \n",
       "60437                  32505   \n",
       "22466                  26103   \n",
       "41044                  31301   \n",
       "44210                  31401   \n",
       "93598                    NaN   \n",
       "121252                   NaN   \n",
       "34175                  32701   \n",
       "206076                 16101   \n",
       "39924                    NaN   \n",
       "120151                 14201   \n",
       "49461                    NaN   \n",
       "36137                    NaN   \n",
       "36164                  24901   \n",
       "68762                  14403   \n",
       "201948                 31501   \n",
       "135390                 25901   \n",
       "47416                  35801   \n",
       "30527                  27401   \n",
       "22891                  37206   \n",
       "7300                     NaN   \n",
       "73105                  31501   \n",
       "76902                  25401   \n",
       "121961                 32201   \n",
       "82707                  37501   \n",
       "115594                   NaN   \n",
       "269444                 33801   \n",
       "16717                    NaN   \n",
       "89529                  22104   \n",
       "...                      ...   \n",
       "46612                  43401   \n",
       "36976                    NaN   \n",
       "249879                 31902   \n",
       "50672                  31401   \n",
       "171878                 43801   \n",
       "76772                  21101   \n",
       "135644                 21701   \n",
       "148324                 43901   \n",
       "96309                    NaN   \n",
       "7923                   37101   \n",
       "104367                   NaN   \n",
       "24074                  32201   \n",
       "35739                  24201   \n",
       "84408                    NaN   \n",
       "40747                    NaN   \n",
       "140028                   NaN   \n",
       "59283                    NaN   \n",
       "53211                  21101   \n",
       "300158                 15901   \n",
       "90243                    NaN   \n",
       "303087                 14404   \n",
       "170265                 37504   \n",
       "142524                 21401   \n",
       "116548                 27101   \n",
       "116026                   NaN   \n",
       "23655                    NaN   \n",
       "118661                 22104   \n",
       "282352                 39401   \n",
       "126348                 33903   \n",
       "181138                 27101   \n",
       "\n",
       "                                            DESC_CAPITULO  \\\n",
       "235920                           Materiales y suministros   \n",
       "166236                                Servicios generales   \n",
       "60437                                 Servicios generales   \n",
       "22466                            Materiales y suministros   \n",
       "41044                                 Servicios generales   \n",
       "44210                                 Servicios generales   \n",
       "93598                            Materiales y suministros   \n",
       "121252                     Participaciones y aportaciones   \n",
       "34175                                 Servicios generales   \n",
       "206076                               Servicios personales   \n",
       "39924                                 Servicios generales   \n",
       "120151                               Servicios personales   \n",
       "49461                                Servicios personales   \n",
       "36137                                Servicios personales   \n",
       "36164                            Materiales y suministros   \n",
       "68762                                Servicios personales   \n",
       "201948                                Servicios generales   \n",
       "135390                           Materiales y suministros   \n",
       "47416                                 Servicios generales   \n",
       "30527                            Materiales y suministros   \n",
       "22891                                 Servicios generales   \n",
       "7300                                 Servicios personales   \n",
       "73105                                 Servicios generales   \n",
       "76902                            Materiales y suministros   \n",
       "121961                                Servicios generales   \n",
       "82707                                 Servicios generales   \n",
       "115594                                Servicios generales   \n",
       "269444                                Servicios generales   \n",
       "16717                            Materiales y suministros   \n",
       "89529                            Materiales y suministros   \n",
       "...                                                   ...   \n",
       "46612   Transferencias, asignaciones, subsidios y otra...   \n",
       "36976                                 Servicios generales   \n",
       "249879                                Servicios generales   \n",
       "50672                                 Servicios generales   \n",
       "171878  Transferencias, asignaciones, subsidios y otra...   \n",
       "76772                            Materiales y suministros   \n",
       "135644                           Materiales y suministros   \n",
       "148324  Transferencias, asignaciones, subsidios y otra...   \n",
       "96309                                 Servicios generales   \n",
       "7923                                  Servicios generales   \n",
       "104367                               Servicios personales   \n",
       "24074                                 Servicios generales   \n",
       "35739                            Materiales y suministros   \n",
       "84408                                Servicios personales   \n",
       "40747                                Servicios personales   \n",
       "140028        Inversiones financieras y otras provisiones   \n",
       "59283                                Servicios personales   \n",
       "53211                            Materiales y suministros   \n",
       "300158                               Servicios personales   \n",
       "90243                                 Servicios generales   \n",
       "303087                               Servicios personales   \n",
       "170265                                Servicios generales   \n",
       "142524                           Materiales y suministros   \n",
       "116548                           Materiales y suministros   \n",
       "116026                           Materiales y suministros   \n",
       "23655                            Materiales y suministros   \n",
       "118661                           Materiales y suministros   \n",
       "282352                                Servicios generales   \n",
       "126348                                Servicios generales   \n",
       "181138                           Materiales y suministros   \n",
       "\n",
       "                                            DESC_CONCEPTO  \\\n",
       "235920                             Alimentos y utensilios   \n",
       "166236                   Servicios de traslado y viáticos   \n",
       "60437                          Servicios de arrendamiento   \n",
       "22466                Combustibles, lubricantes y aditivos   \n",
       "41044                                   Servicios basicos   \n",
       "44210                                   Servicios basicos   \n",
       "93598                              Alimentos y utensilios   \n",
       "121252                                                NaN   \n",
       "34175                          Servicios de arrendamiento   \n",
       "206076                                        Previsiones   \n",
       "39924                           Otros servicios generales   \n",
       "120151                                   Seguridad social   \n",
       "49461                                    Seguridad social   \n",
       "36137            Otras prestaciones sociales y económicas   \n",
       "36164   Materiales y articulos de construccion y de re...   \n",
       "68762                                    Seguridad social   \n",
       "201948                                  Servicios basicos   \n",
       "135390  Productos químicos, farmacéuticos y de laborat...   \n",
       "47416   Servicios de instalacion, reparacion, mantenim...   \n",
       "30527   Vestuario, blancos, prendas de protección y ar...   \n",
       "22891                    Servicios de traslado y viáticos   \n",
       "7300              Pago de estímulos a servidores públicos   \n",
       "73105                                   Servicios basicos   \n",
       "76902   Productos químicos, farmacéuticos y de laborat...   \n",
       "121961                         Servicios de arrendamiento   \n",
       "82707                    Servicios de traslado y viáticos   \n",
       "115594  Servicios de instalacion, reparacion, mantenim...   \n",
       "269444  Servicios profesionales, cientificos, tecnicos...   \n",
       "16717                              Alimentos y utensilios   \n",
       "89529                              Alimentos y utensilios   \n",
       "...                                                   ...   \n",
       "46612                            Subsidios y subvenciones   \n",
       "36976   Servicios profesionales, científicos, técnicos...   \n",
       "249879                                  Servicios basicos   \n",
       "50672                                   Servicios basicos   \n",
       "171878                           Subsidios y subvenciones   \n",
       "76772   Materiales de administracion, emision de docum...   \n",
       "135644  Materiales de administracion, emision de docum...   \n",
       "148324                           Subsidios y subvenciones   \n",
       "96309       Servicios de comunicación social y publicidad   \n",
       "7923                     Servicios de traslado y viáticos   \n",
       "104367                                   Seguridad social   \n",
       "24074                          Servicios de arrendamiento   \n",
       "35739   Materiales y articulos de construccion y de re...   \n",
       "84408                                    Seguridad social   \n",
       "40747             Remuneraciones adicionales y especiales   \n",
       "140028  Inversiones en fideicomisos, mandatos y otros ...   \n",
       "59283             Pago de estímulos a servidores públicos   \n",
       "53211   Materiales de administracion, emision de docum...   \n",
       "300158           Otras prestaciones sociales y económicas   \n",
       "90243      Servicios financieros, bancarios y comerciales   \n",
       "303087                                   Seguridad social   \n",
       "170265                   Servicios de traslado y viáticos   \n",
       "142524  Materiales de administracion, emision de docum...   \n",
       "116548  Vestuario, blancos, prendas de protección y ar...   \n",
       "116026  Materiales de administracion, emision de docum...   \n",
       "23655   Materiales y articulos de construccion y de re...   \n",
       "118661                             Alimentos y utensilios   \n",
       "282352                          Otros servicios generales   \n",
       "126348  Servicios profesionales, cientificos, tecnicos...   \n",
       "181138  Vestuario, blancos, prendas de protección y ar...   \n",
       "\n",
       "                                    DESC_PARTIDA_GENERICA  \\\n",
       "235920               Productos alimenticios para personas   \n",
       "166236                                Viáticos en el país   \n",
       "60437               Arrendamiento de equipo de transporte   \n",
       "22466                Combustibles, lubricantes y aditivos   \n",
       "41044                                                Agua   \n",
       "44210                               Telefonía tradicional   \n",
       "93598                                                 NaN   \n",
       "121252  Aportaciones Federales a las entidades federat...   \n",
       "34175                Arrendamiento de activos intangibles   \n",
       "206076  Previsiones de carácter laboral, económica y d...   \n",
       "39924                                                 NaN   \n",
       "120151                  Aportaciones a fondos de vivienda   \n",
       "49461   Cuotas para el seguro de gastos médicos del pe...   \n",
       "36137                            Compensación garantizada   \n",
       "36164   Otros materiales y artículos de construcción y...   \n",
       "68762                           Aportaciones para seguros   \n",
       "201948                                  Telefonía celular   \n",
       "135390                           Otros productos químicos   \n",
       "47416          Servicios de limpieza y manejo de desechos   \n",
       "30527                                  Productos textiles   \n",
       "22891                                  Pasajes terrestres   \n",
       "7300                      Estímulos al personal operativo   \n",
       "73105                                   Telefonía celular   \n",
       "76902        Materiales, accesorios y suministros médicos   \n",
       "121961                         Arrendamiento de edificios   \n",
       "82707                                 Viáticos en el país   \n",
       "115594  Mantenimiento y conservación de mobiliario y e...   \n",
       "269444                            Servicios de vigilancia   \n",
       "16717                                                 NaN   \n",
       "89529                Productos alimenticios para personas   \n",
       "...                                                   ...   \n",
       "46612     Subsidios a la prestación de servicios públicos   \n",
       "36976                                                 NaN   \n",
       "249879             Servicios integrales y otros servicios   \n",
       "50672                               Telefonía tradicional   \n",
       "171878     Subsidios a Entidades Federativas y Municipios   \n",
       "76772     Materiales, útiles y equipos menores de oficina   \n",
       "135644                   Materiales y útiles de enseñanza   \n",
       "148324                                    Otros Subsidios   \n",
       "96309   Servicios relacionados con monitoreo de inform...   \n",
       "7923                                       Pasajes aéreos   \n",
       "104367   Aportaciones al Sistema de Ahorro para el Retiro   \n",
       "24074                          Arrendamiento de edificios   \n",
       "35739                     Cemento y productos de concreto   \n",
       "84408    Cuotas para el seguro de vida del personal civil   \n",
       "40747                         Gratificación de fin de año   \n",
       "140028       Gastos por servicios de traslado de personas   \n",
       "59283                     Estímulos al personal operativo   \n",
       "53211     Materiales, útiles y equipos menores de oficina   \n",
       "300158           Otras prestaciones sociales y económicas   \n",
       "90243                                  Fletes y maniobras   \n",
       "303087                          Aportaciones para seguros   \n",
       "170265                                Viáticos en el país   \n",
       "142524  Materiales, útiles y equipos menores de tecnol...   \n",
       "116548                              Vestuario y uniformes   \n",
       "116026    Materiales y útiles de impresión y reproducción   \n",
       "23655                          Materiales complementarios   \n",
       "118661               Productos alimenticios para personas   \n",
       "282352  Sentencias y resoluciones por autoridad compet...   \n",
       "126348  Servicios profesionales, científicos y técnico...   \n",
       "181138                              Vestuario y uniformes   \n",
       "\n",
       "                                  DESC_PARTIDA_ESPECIFICA  \n",
       "235920  Productos alimenticios para el personal en las...  \n",
       "166236  Viáticos nacionales para labores en campo y de...  \n",
       "60437   Arrendamiento de vehículos terrestres, aéreos,...  \n",
       "22466   Combustibles, lubricantes y aditivos para vehí...  \n",
       "41044                                    Servicio de agua  \n",
       "44210                    Servicio telefónico convencional  \n",
       "93598                                                 NaN  \n",
       "121252                                                NaN  \n",
       "34175       Patentes, derechos de autor, regalías y otros  \n",
       "206076                     Incrementos a las percepciones  \n",
       "39924                                                 NaN  \n",
       "120151                          Aportaciones al FOVISSSTE  \n",
       "49461                                                 NaN  \n",
       "36137                                                 NaN  \n",
       "36164   Otros materiales y artículos de construcción y...  \n",
       "68762   Cuotas para el seguro de gastos médicos del pe...  \n",
       "201948                      Servicio de telefonía celular  \n",
       "135390                           Otros productos químicos  \n",
       "47416         Servicios de lavandería, limpieza e higiene  \n",
       "30527                                  Productos textiles  \n",
       "22891   Pasajes terrestres internacionales para servid...  \n",
       "7300                                                  NaN  \n",
       "73105                       Servicio de telefonía celular  \n",
       "76902        Materiales, accesorios y suministros médicos  \n",
       "121961               Arrendamiento de edificios y locales  \n",
       "82707   Viáticos nacionales para labores en campo y de...  \n",
       "115594                                                NaN  \n",
       "269444                            Servicios de vigilancia  \n",
       "16717                                                 NaN  \n",
       "89529   Productos alimenticios para el personal en las...  \n",
       "...                                                   ...  \n",
       "46612     Subsidios a la prestación de servicios públicos  \n",
       "36976                                                 NaN  \n",
       "249879                    Contratación de otros servicios  \n",
       "50672                    Servicio telefónico convencional  \n",
       "171878     Subsidios a Entidades Federativas y Municipios  \n",
       "76772                      Materiales y útiles de oficina  \n",
       "135644  Materiales y suministros para planteles educat...  \n",
       "148324                Subsidios para capacitación y becas  \n",
       "96309                                                 NaN  \n",
       "7923    Pasajes aéreos nacionales para labores en camp...  \n",
       "104367                                                NaN  \n",
       "24074                Arrendamiento de edificios y locales  \n",
       "35739                     Cemento y productos de concreto  \n",
       "84408                                                 NaN  \n",
       "40747                                                 NaN  \n",
       "140028                                                NaN  \n",
       "59283                                                 NaN  \n",
       "53211                      Materiales y útiles de oficina  \n",
       "300158                                 Otras prestaciones  \n",
       "90243                                                 NaN  \n",
       "303087  Cuotas para el seguro de separación individual...  \n",
       "170265  Viáticos nacionales para servidores públicos e...  \n",
       "142524  Materiales y útiles consumibles para el proces...  \n",
       "116548                              Vestuario y uniformes  \n",
       "116026                                                NaN  \n",
       "23655                                                 NaN  \n",
       "118661  Productos alimenticios para el personal en las...  \n",
       "282352  Erogaciones por resoluciones por autoridad com...  \n",
       "126348                               Servicios integrales  \n",
       "181138                              Vestuario y uniformes  \n",
       "\n",
       "[200 rows x 9 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "breakdown = [\n",
    "    'CICLO', \n",
    "    'ID_CAPITULO', \n",
    "    'ID_CONCEPTO', \n",
    "    'ID_PARTIDA_GENERICA',        \n",
    "    'ID_PARTIDA_ESPECIFICA', \n",
    "    'DESC_CAPITULO',\n",
    "    'DESC_CONCEPTO', \n",
    "    'DESC_PARTIDA_GENERICA',\n",
    "    'DESC_PARTIDA_ESPECIFICA'\n",
    "]\n",
    "\n",
    "merged[breakdown].sample(n=200)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1909649 entries, 0 to 341255\n",
      "Data columns (total 39 columns):\n",
      "CICLO                      object\n",
      "DESC_AI                    object\n",
      "DESC_CAPITULO              object\n",
      "DESC_CONCEPTO              object\n",
      "DESC_FF                    object\n",
      "DESC_FUNCION               object\n",
      "DESC_GPO_FUNCIONAL         object\n",
      "DESC_MODALIDAD             object\n",
      "DESC_PARTIDA_ESPECIFICA    object\n",
      "DESC_PARTIDA_GENERICA      object\n",
      "DESC_PP                    object\n",
      "DESC_RAMO                  object\n",
      "DESC_SUBFUNCION            object\n",
      "DESC_TIPOGASTO             object\n",
      "DESC_UR                    object\n",
      "ENTIDAD_FEDERATIVA         object\n",
      "GPO_FUNCIONAL              object\n",
      "ID_AI                      object\n",
      "ID_CAPITULO                object\n",
      "ID_CLAVE_CARTERA           object\n",
      "ID_CONCEPTO                object\n",
      "ID_ENTIDAD_FEDERATIVA      object\n",
      "ID_FF                      object\n",
      "ID_FUNCION                 object\n",
      "ID_MODALIDAD               object\n",
      "ID_PARTIDA_ESPECIFICA      object\n",
      "ID_PARTIDA_GENERICA        object\n",
      "ID_PP                      object\n",
      "ID_RAMO                    object\n",
      "ID_SUBFUNCION              object\n",
      "ID_TIPOGASTO               object\n",
      "ID_UR                      object\n",
      "MONTO_ADEFAS               float64\n",
      "MONTO_APROBADO             float64\n",
      "MONTO_DEVENGADO            float64\n",
      "MONTO_EJERCICIO            float64\n",
      "MONTO_EJERCIDO             float64\n",
      "MONTO_MODIFICADO           float64\n",
      "MONTO_PAGADO               float64\n",
      "dtypes: float64(7), object(32)\n",
      "memory usage: 582.8+ MB\n"
     ]
    }
   ],
   "source": [
    "merged.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1909649"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(merged)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CICLO', 'DESC_AI', 'DESC_CAPITULO', 'DESC_CONCEPTO', 'DESC_FF',\n",
       "       'DESC_FUNCION', 'DESC_GPO_FUNCIONAL', 'DESC_MODALIDAD',\n",
       "       'DESC_PARTIDA_ESPECIFICA', 'DESC_PARTIDA_GENERICA', 'DESC_PP',\n",
       "       'DESC_RAMO', 'DESC_SUBFUNCION', 'DESC_TIPOGASTO', 'DESC_UR',\n",
       "       'ENTIDAD_FEDERATIVA', 'GPO_FUNCIONAL', 'ID_AI', 'ID_CAPITULO',\n",
       "       'ID_CLAVE_CARTERA', 'ID_CONCEPTO', 'ID_ENTIDAD_FEDERATIVA', 'ID_FF',\n",
       "       'ID_FUNCION', 'ID_MODALIDAD', 'ID_PARTIDA_ESPECIFICA',\n",
       "       'ID_PARTIDA_GENERICA', 'ID_PP', 'ID_RAMO', 'ID_SUBFUNCION',\n",
       "       'ID_TIPOGASTO', 'ID_UR', 'MONTO_ADEFAS', 'MONTO_APROBADO',\n",
       "       'MONTO_DEVENGADO', 'MONTO_EJERCICIO', 'MONTO_EJERCIDO',\n",
       "       'MONTO_MODIFICADO', 'MONTO_PAGADO'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2015    341256\n",
       "2014    340699\n",
       "2013    308453\n",
       "2012    272142\n",
       "2009    146382\n",
       "2011    142549\n",
       "2008    130950\n",
       "2010    129014\n",
       "2016     98204\n",
       "Name: CICLO, dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged['CICLO'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/home/loic/repos/mexico'"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pwd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged.sample(n=100000).to_csv('pipeline.out/iteration-20-post-launch-bug-fixes-test-run2/mexican_federal_budget_sample.csv', encoding='utf-8', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}