Solucion_Tarea3Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Analítica y Ciencia de Datos\n",
    "\n",
    "## CIDE - Otoño 2015\n",
    "\n",
    "### Solución Tarea 3: Pandas\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Primer Ejercicio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* **Parte a**: El error se obtiene porque el DataFrame `mat_mun` tiene 522 filas, una por cada código de municipio incluido.  En el loop, cuando llegamos al último municipio y se ejecuta la línea:\n",
    "\n",
    "> `ind_sig = mat_mun.Indice.ix[int(preind + 1)]`\n",
    "\n",
    "obtenemos un error cuando Pandas intenta acceder al índice *523*.\n",
    "\n",
    "* **Parte b**: La solución es muy simple.  Con un `if-else` basta:\n",
    "\n",
    "> ```\n",
    "> if int(preind + 1) < mat_mun.shape[0]:\n",
    "      ind_sig = mat_mun.Indice.ix[int(preind + 1)]\n",
    ">```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Segundo Ejercicio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Parte a:"
   ]
  },
  {
   "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>columna1</th>\n",
       "      <th>columna2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.331587</td>\n",
       "      <td>0.715279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-1.545400</td>\n",
       "      <td>-0.008384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.621336</td>\n",
       "      <td>-0.720086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.265512</td>\n",
       "      <td>0.108549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.004291</td>\n",
       "      <td>-0.174600</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   columna1  columna2\n",
       "0  1.331587  0.715279\n",
       "1 -1.545400 -0.008384\n",
       "2  0.621336 -0.720086\n",
       "3  0.265512  0.108549\n",
       "4  0.004291 -0.174600"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "np.random.seed(10)\n",
    "newdf = pd.DataFrame(np.random.randn(20,2), columns=['columna1', 'columna2'])\n",
    "newdf.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Parte b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>columna1</th>\n",
       "      <th>columna2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.331587</td>\n",
       "      <td>0.715279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.008384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.720086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.108549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.174600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.433026</td>\n",
       "      <td>1.203037</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   columna1  columna2\n",
       "0  1.331587  0.715279\n",
       "1       NaN -0.008384\n",
       "2       NaN -0.720086\n",
       "3       NaN  0.108549\n",
       "4       NaN -0.174600\n",
       "5  0.433026  1.203037"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Una forma es con la función .ix\n",
    "newdf.columna1.ix[1:4] = np.nan\n",
    "newdf.columna1.ix[7:9] = np.nan\n",
    "newdf.columna1.ix[15:19] = np.nan\n",
    "newdf.head(6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>columna1</th>\n",
       "      <th>columna2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.331587</td>\n",
       "      <td>0.715279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.008384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.720086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.108549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.174600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.433026</td>\n",
       "      <td>1.203037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>-0.965066</td>\n",
       "      <td>1.028274</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   columna1  columna2\n",
       "0  1.331587  0.715279\n",
       "1       NaN -0.008384\n",
       "2       NaN -0.720086\n",
       "3       NaN  0.108549\n",
       "4       NaN -0.174600\n",
       "5  0.433026  1.203037\n",
       "6 -0.965066  1.028274"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Alternativamente se puede hacer con la función .loc y de un sólo paso\n",
    "sel_arr = np.concatenate((np.arange(1,5), np.arange(7,10), np.arange(15,20)),axis=0)\n",
    "newdf.columna1.loc[sel_arr] = np.nan\n",
    "newdf.head(7)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Parte c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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>columna1</th>\n",
       "      <th>columna2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.331587</td>\n",
       "      <td>0.715279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.331587</td>\n",
       "      <td>-0.008384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.331587</td>\n",
       "      <td>-0.720086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.331587</td>\n",
       "      <td>0.108549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.331587</td>\n",
       "      <td>-0.174600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.433026</td>\n",
       "      <td>1.203037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>-0.965066</td>\n",
       "      <td>1.028274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-0.965066</td>\n",
       "      <td>0.445138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>-0.965066</td>\n",
       "      <td>0.135137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>-0.965066</td>\n",
       "      <td>-1.079805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>-1.977728</td>\n",
       "      <td>-1.743372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0.266070</td>\n",
       "      <td>2.384967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1.123691</td>\n",
       "      <td>1.672622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0.099149</td>\n",
       "      <td>1.397996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>-0.271248</td>\n",
       "      <td>0.613204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>-0.271248</td>\n",
       "      <td>-0.549309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>-0.271248</td>\n",
       "      <td>-0.476142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>-0.271248</td>\n",
       "      <td>0.195013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>-0.271248</td>\n",
       "      <td>-0.337632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>-0.271248</td>\n",
       "      <td>-0.731970</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    columna1  columna2\n",
       "0   1.331587  0.715279\n",
       "1   1.331587 -0.008384\n",
       "2   1.331587 -0.720086\n",
       "3   1.331587  0.108549\n",
       "4   1.331587 -0.174600\n",
       "5   0.433026  1.203037\n",
       "6  -0.965066  1.028274\n",
       "7  -0.965066  0.445138\n",
       "8  -0.965066  0.135137\n",
       "9  -0.965066 -1.079805\n",
       "10 -1.977728 -1.743372\n",
       "11  0.266070  2.384967\n",
       "12  1.123691  1.672622\n",
       "13  0.099149  1.397996\n",
       "14 -0.271248  0.613204\n",
       "15 -0.271248 -0.549309\n",
       "16 -0.271248 -0.476142\n",
       "17 -0.271248  0.195013\n",
       "18 -0.271248 -0.337632\n",
       "19 -0.271248 -0.731970"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ind_not_nan = newdf[newdf.columna1.isnull()==False].index\n",
    "#ind_not_nan\n",
    "for i in range(ind_not_nan.shape[0]):\n",
    "    # seleccionemos el actual:\n",
    "    ind_actual = ind_not_nan[i]\n",
    "    # seleccionemos el siguiente:\n",
    "    # para evitarnos un else: podemos incializarlo: con el default\n",
    "    ind_siguiente = newdf.shape[0]\n",
    "    if i+1 < ind_not_nan.shape[0]:\n",
    "        ind_siguiente = ind_not_nan[i+1]-1\n",
    "    #else:\n",
    "    #    ind_siguiente = newdf.shape[0]\n",
    "\n",
    "    # listo para copiar:\n",
    "    newdf.columna1.ix[ind_actual:ind_siguiente] = newdf.columna1.ix[ind_actual]    \n",
    "newdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "100 loops, best of 3: 5.3 ms per loop\n"
     ]
    }
   ],
   "source": [
    "%timeit exec In[73]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Parte d: otro método\n",
    "\n",
    "El método más natural es hacer un loop sobre las celdas, verificar si hay un NaN.  Si si, copio la celda anterior, si no, sigo.  Por supuesto, este método es muy costoso en términos computacionales.\n",
    "\n",
    "En este caso la matriz es tan pequeña que este segundo método es menos costos (2.08ms vs. 5.3ms).  Las ganancias se ven cuando la matriz es mucho más grande.  El loop de este método se repite $N$ veces, donde $N$ es el tamaño del dataframe.  El loop del otro método se repite $M$ veces, donde $M$ es el número de elementos no nulos.  La ganancia se da cuando $M<<N$ como en el caso de los datos utilizados en las Notas de Clase."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>columna1</th>\n",
       "      <th>columna2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.331587</td>\n",
       "      <td>0.715279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.008384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.720086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.108549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.174600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.433026</td>\n",
       "      <td>1.203037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>-0.965066</td>\n",
       "      <td>1.028274</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   columna1  columna2\n",
       "0  1.331587  0.715279\n",
       "1       NaN -0.008384\n",
       "2       NaN -0.720086\n",
       "3       NaN  0.108549\n",
       "4       NaN -0.174600\n",
       "5  0.433026  1.203037\n",
       "6 -0.965066  1.028274"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Alternativamente se puede hacer con la función .loc y de un sólo paso\n",
    "sel_arr = np.concatenate((np.arange(1,5), np.arange(7,10), np.arange(15,20)),axis=0)\n",
    "newdf.columna1.loc[sel_arr] = np.nan\n",
    "newdf.head(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>columna1</th>\n",
       "      <th>columna2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.331587</td>\n",
       "      <td>0.715279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.331587</td>\n",
       "      <td>-0.008384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.331587</td>\n",
       "      <td>-0.720086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.331587</td>\n",
       "      <td>0.108549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.331587</td>\n",
       "      <td>-0.174600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.433026</td>\n",
       "      <td>1.203037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>-0.965066</td>\n",
       "      <td>1.028274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-0.965066</td>\n",
       "      <td>0.445138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>-0.965066</td>\n",
       "      <td>0.135137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>-0.965066</td>\n",
       "      <td>-1.079805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>-1.977728</td>\n",
       "      <td>-1.743372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0.266070</td>\n",
       "      <td>2.384967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1.123691</td>\n",
       "      <td>1.672622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0.099149</td>\n",
       "      <td>1.397996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>-0.271248</td>\n",
       "      <td>0.613204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>-0.271248</td>\n",
       "      <td>-0.549309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>-0.271248</td>\n",
       "      <td>-0.476142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>-0.271248</td>\n",
       "      <td>0.195013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>-0.271248</td>\n",
       "      <td>-0.337632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>-0.271248</td>\n",
       "      <td>-0.731970</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    columna1  columna2\n",
       "0   1.331587  0.715279\n",
       "1   1.331587 -0.008384\n",
       "2   1.331587 -0.720086\n",
       "3   1.331587  0.108549\n",
       "4   1.331587 -0.174600\n",
       "5   0.433026  1.203037\n",
       "6  -0.965066  1.028274\n",
       "7  -0.965066  0.445138\n",
       "8  -0.965066  0.135137\n",
       "9  -0.965066 -1.079805\n",
       "10 -1.977728 -1.743372\n",
       "11  0.266070  2.384967\n",
       "12  1.123691  1.672622\n",
       "13  0.099149  1.397996\n",
       "14 -0.271248  0.613204\n",
       "15 -0.271248 -0.549309\n",
       "16 -0.271248 -0.476142\n",
       "17 -0.271248  0.195013\n",
       "18 -0.271248 -0.337632\n",
       "19 -0.271248 -0.731970"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for i in range(newdf.shape[0]):\n",
    "    if newdf.columna1.isnull().ix[i]==True:\n",
    "        newdf.columna1.ix[i] =  newdf.columna1.ix[i-1]\n",
    "newdf        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "100 loops, best of 3: 2.3 ms per loop\n"
     ]
    }
   ],
   "source": [
    "%timeit exec In[69]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ejercicio tres: merge, importar y exportar datos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[u'enero', u'febrero', u'marzo', u'abril']\n"
     ]
    }
   ],
   "source": [
    "# Primer importemos los nombres de las hojas:\n",
    "archivo = pd.ExcelFile('data_tarea3.xlsx')\n",
    "hojas   = archivo.sheet_names\n",
    "print hojas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# vamos a hacer un loop sobre las hojas\n",
    "# La primera hoja inicializa todo:\n",
    "counter = 0\n",
    "for h in hojas:\n",
    "    if counter==0: # la primera hoja tiene un tratamiento especial, sin merge:\n",
    "        data = pd.read_excel('data_tarea3.xlsx',h)\n",
    "        # lo único que tenemos que hacer es cambiar el nombre de la columna VENTAS por el mes\n",
    "        data.rename(columns={'ventas':h}, inplace=True)\n",
    "    else: \n",
    "        # para las siguientes tenemos que importar, cambiar el nombre y hacer un merge\n",
    "        # importemos\n",
    "        predata = pd.read_excel('data_tarea3.xlsx',h)\n",
    "        # cambio de nombre\n",
    "        predata.rename(columns={'ventas':h}, inplace=True)\n",
    "        # listo para el merge\n",
    "        data = data.merge(predata, on='id_tienda', how='outer')\n",
    "        # actualicemos el contador\n",
    "    counter +=1\n",
    "# terminamos\n",
    "# limpiemos los NaNs... son ventas cero\n",
    "data.fillna(0, inplace= True)\n",
    "data\n",
    "# exportemos los datos\n",
    "data.to_excel('ventas_enero_abril.xlsx')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}