3_variables_almacenadas_tanto_en_filas_y_en_columnas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Variables son almacenadas tanto en filas como en columnas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## \"Housekeeping\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option(\"display.max_columns\", 40)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ejemplo: Clima\n",
    "\n",
    "El [Global Historical Climatology Network](https://www.ncdc.noaa.gov/data-access/land-based-station-data/land-based-datasets/global-historical-climatology-network-ghcn) recolecta datos de clima de manera diaria. Para este ejemplo, se usan datos de una estación climática en Mexico (MX17004)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cargando datos\n",
    "\n",
    "El raw dataset viene en un formato que es una mezcla de datos de ancho fijo con uso ocasional de caracteres como separadores. Se necesita hacer una limpieza al respecto."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract the data as one column and\n",
    "# use string slicing to obtain groups of columns.\n",
    "weather = pd.read_csv(\"data/weather.txt\", header=None, sep=\"^\")\n",
    "\n",
    "# First, remove the weird character separators,\n",
    "# then split the columns by whitespace, and\n",
    "# finally name them appropriately.\n",
    "days = (\n",
    "    weather[0]\n",
    "    .map(lambda x: x[21:])\n",
    "    .str.replace(\"OI\", \"  \")\n",
    "    .str.replace(\"OS\", \"  \")\n",
    "    .str.replace(\"SI\", \"  \")\n",
    "    .str.replace(\"I\", \" \")\n",
    "    .str.replace(\"S\", \" \")\n",
    "    .str.replace(\"B\", \" \")\n",
    "    .str.replace(\"D\", \" \")\n",
    "    .map(str.lstrip)\n",
    "    .str.split(r\"\\s+\", expand=True)\n",
    ")[list(range(31))].rename(columns={i: f\"d{i+1}\" for i in range(31)})\n",
    "\n",
    "# The non-temperature columns can be extracted as simple slices.\n",
    "weather = pd.DataFrame(\n",
    "    data={\n",
    "        \"id\": weather[0].map(lambda x: x[:11]),\n",
    "        \"year\": weather[0].map(lambda x: x[11:15]).astype(int),\n",
    "        \"month\": weather[0].map(lambda x: x[15:17]).astype(int),\n",
    "        \"element\": weather[0].map(lambda x: x[17:21]).str.lower(),\n",
    "    }\n",
    ")\n",
    "\n",
    "# The temperatures were stored as whole integers\n",
    "# with -9999 indicating missing values.\n",
    "for i in range(1, 32):\n",
    "    weather[f\"d{i}\"] = days[f\"d{i}\"].astype(float) / 10\n",
    "weather = weather.replace(-999.9, np.NaN)\n",
    "\n",
    "# Discard the non-temperature observations and\n",
    "# sort the dataset as in the paper.\n",
    "weather = (\n",
    "    weather[weather[\"element\"].isin([\"tmax\", \"tmin\"])]\n",
    "    .sort_values([\"id\", \"year\", \"month\", \"element\"])\n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Messy Data\n",
    "\n",
    "A continuación hay un dataset que asumimos que ha sido proporcionado \"crudo\" como está al analista de datos, es decir, el analista no realizó el trabajo de \"parsing\" anterior, sino un tercero anteriormente.\n",
    "\n",
    "> La forma más complicada de *messy data* ocurre cuando las variables se almacenan tanto en filas como en columnas. La Tabla 11 muestra datos meteorológicos diarios del Global Historical Climatology Network para una estación meteorológica (MX17004) en México durante cinco meses en 2010. Tiene variables en\n",
    "columnas individuales (`\"id\"`, `\"year\"`, `\"month\"`), distribuidas en columnas (`day`, `\"d1\"`–`\"d31\"`) y en filas (`\"tmin\"` y `\"tmax\"` para las temperaturas mínima y máxima). Los meses con menos de 31 días tienen valores faltantes para los últimos días del mes. La columna `\"element\"` no es una variable: almacena los *nombres* de las variables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>element</th>\n",
       "      <th>d1</th>\n",
       "      <th>d2</th>\n",
       "      <th>d3</th>\n",
       "      <th>d4</th>\n",
       "      <th>d5</th>\n",
       "      <th>d6</th>\n",
       "      <th>d7</th>\n",
       "      <th>d8</th>\n",
       "      <th>d9</th>\n",
       "      <th>d10</th>\n",
       "      <th>d11</th>\n",
       "      <th>d12</th>\n",
       "      <th>d13</th>\n",
       "      <th>d14</th>\n",
       "      <th>d15</th>\n",
       "      <th>d16</th>\n",
       "      <th>d17</th>\n",
       "      <th>d18</th>\n",
       "      <th>d19</th>\n",
       "      <th>d20</th>\n",
       "      <th>d21</th>\n",
       "      <th>d22</th>\n",
       "      <th>d23</th>\n",
       "      <th>d24</th>\n",
       "      <th>d25</th>\n",
       "      <th>d26</th>\n",
       "      <th>d27</th>\n",
       "      <th>d28</th>\n",
       "      <th>d29</th>\n",
       "      <th>d30</th>\n",
       "      <th>d31</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1099</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmax</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>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>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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>27.8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1100</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmin</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>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>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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1101</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>tmax</td>\n",
       "      <td>NaN</td>\n",
       "      <td>27.3</td>\n",
       "      <td>24.1</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>29.7</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1102</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>tmin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.4</td>\n",
       "      <td>14.4</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>13.4</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1103</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010</td>\n",
       "      <td>3</td>\n",
       "      <td>tmax</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.1</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1104</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010</td>\n",
       "      <td>3</td>\n",
       "      <td>tmin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.6</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1105</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010</td>\n",
       "      <td>4</td>\n",
       "      <td>tmax</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>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>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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1106</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010</td>\n",
       "      <td>4</td>\n",
       "      <td>tmin</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>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>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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1107</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010</td>\n",
       "      <td>5</td>\n",
       "      <td>tmax</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>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>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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1108</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010</td>\n",
       "      <td>5</td>\n",
       "      <td>tmin</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>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>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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id  year  month element  d1    d2    d3  d4    d5  d6  d7  d8  \\\n",
       "1099  MX000017004  2010      1    tmax NaN   NaN   NaN NaN   NaN NaN NaN NaN   \n",
       "1100  MX000017004  2010      1    tmin NaN   NaN   NaN NaN   NaN NaN NaN NaN   \n",
       "1101  MX000017004  2010      2    tmax NaN  27.3  24.1 NaN   NaN NaN NaN NaN   \n",
       "1102  MX000017004  2010      2    tmin NaN  14.4  14.4 NaN   NaN NaN NaN NaN   \n",
       "1103  MX000017004  2010      3    tmax NaN   NaN   NaN NaN  32.1 NaN NaN NaN   \n",
       "1104  MX000017004  2010      3    tmin NaN   NaN   NaN NaN  14.2 NaN NaN NaN   \n",
       "1105  MX000017004  2010      4    tmax NaN   NaN   NaN NaN   NaN NaN NaN NaN   \n",
       "1106  MX000017004  2010      4    tmin NaN   NaN   NaN NaN   NaN NaN NaN NaN   \n",
       "1107  MX000017004  2010      5    tmax NaN   NaN   NaN NaN   NaN NaN NaN NaN   \n",
       "1108  MX000017004  2010      5    tmin NaN   NaN   NaN NaN   NaN NaN NaN NaN   \n",
       "\n",
       "      d9   d10   d11  d12  d13  d14  d15   d16  d17  d18  d19  d20  d21  d22  \\\n",
       "1099 NaN   NaN   NaN  NaN  NaN  NaN  NaN   NaN  NaN  NaN  NaN  NaN  NaN  NaN   \n",
       "1100 NaN   NaN   NaN  NaN  NaN  NaN  NaN   NaN  NaN  NaN  NaN  NaN  NaN  NaN   \n",
       "1101 NaN   NaN  29.7  NaN  NaN  NaN  NaN   NaN  NaN  NaN  NaN  NaN  NaN  NaN   \n",
       "1102 NaN   NaN  13.4  NaN  NaN  NaN  NaN   NaN  NaN  NaN  NaN  NaN  NaN  NaN   \n",
       "1103 NaN  34.5   NaN  NaN  NaN  NaN  NaN  31.1  NaN  NaN  NaN  NaN  NaN  NaN   \n",
       "1104 NaN  16.8   NaN  NaN  NaN  NaN  NaN  17.6  NaN  NaN  NaN  NaN  NaN  NaN   \n",
       "1105 NaN   NaN   NaN  NaN  NaN  NaN  NaN   NaN  NaN  NaN  NaN  NaN  NaN  NaN   \n",
       "1106 NaN   NaN   NaN  NaN  NaN  NaN  NaN   NaN  NaN  NaN  NaN  NaN  NaN  NaN   \n",
       "1107 NaN   NaN   NaN  NaN  NaN  NaN  NaN   NaN  NaN  NaN  NaN  NaN  NaN  NaN   \n",
       "1108 NaN   NaN   NaN  NaN  NaN  NaN  NaN   NaN  NaN  NaN  NaN  NaN  NaN  NaN   \n",
       "\n",
       "       d23  d24  d25  d26   d27  d28  d29   d30  d31  \n",
       "1099   NaN  NaN  NaN  NaN   NaN  NaN  NaN  27.8  NaN  \n",
       "1100   NaN  NaN  NaN  NaN   NaN  NaN  NaN  14.5  NaN  \n",
       "1101  29.9  NaN  NaN  NaN   NaN  NaN  NaN   NaN  NaN  \n",
       "1102  10.7  NaN  NaN  NaN   NaN  NaN  NaN   NaN  NaN  \n",
       "1103   NaN  NaN  NaN  NaN   NaN  NaN  NaN   NaN  NaN  \n",
       "1104   NaN  NaN  NaN  NaN   NaN  NaN  NaN   NaN  NaN  \n",
       "1105   NaN  NaN  NaN  NaN  36.3  NaN  NaN   NaN  NaN  \n",
       "1106   NaN  NaN  NaN  NaN  16.7  NaN  NaN   NaN  NaN  \n",
       "1107   NaN  NaN  NaN  NaN  33.2  NaN  NaN   NaN  NaN  \n",
       "1108   NaN  NaN  NaN  NaN  18.2  NaN  NaN   NaN  NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather[(weather[\"year\"] == 2010)].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Molten Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Para hacer este dataset tidy, primero le hacemos un `melt`  con colvars `\"id\"`, `\"year\"`, `\"month\"`, y la columna que contiene los nombres de las variables reales, `\"element\"` [...]. Para la presentación, hemos descartado los valores faltantes, haciéndolos implícitos en lugar de explícitos. Esto es permisible porque sabemos cuántos días hay en cada mes y podemos reconstruir fácilmente los valores faltantes explícitos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Melt the dataset and extract a date column.\n",
    "molten_weather = (\n",
    "    pd.melt(weather, id_vars=[\"id\", \"year\", \"month\", \"element\"], var_name=\"day\")\n",
    "    .assign(day=lambda x: x[\"day\"].str.extract(\"(\\d+)\").astype(int))\n",
    "    .assign(date=lambda x: pd.to_datetime(x[[\"year\", \"month\", \"day\"]], errors=\"coerce\"))\n",
    ")\n",
    "molten_weather = molten_weather[[\"id\", \"date\", \"element\", \"value\"]]\n",
    "\n",
    "# Make the missing values implicit.\n",
    "molten_weather = molten_weather[molten_weather[\"value\"].notnull()]\n",
    "\n",
    "# Sort the data as in the paper.\n",
    "molten_weather = molten_weather.sort_values([\"id\", \"date\", \"element\"])\n",
    "molten_weather = molten_weather.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Este conjunto de datos está mayormente tidy, pero tenemos dos variables almacenadas en filas: `\"tmin\"` y `\"tmax\"`, el tipo de observación."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>element</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23183</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-01-30</td>\n",
       "      <td>tmax</td>\n",
       "      <td>27.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23184</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-01-30</td>\n",
       "      <td>tmin</td>\n",
       "      <td>14.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23185</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-02-02</td>\n",
       "      <td>tmax</td>\n",
       "      <td>27.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23186</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-02-02</td>\n",
       "      <td>tmin</td>\n",
       "      <td>14.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23187</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-02-03</td>\n",
       "      <td>tmax</td>\n",
       "      <td>24.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23188</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-02-03</td>\n",
       "      <td>tmin</td>\n",
       "      <td>14.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23189</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-02-11</td>\n",
       "      <td>tmax</td>\n",
       "      <td>29.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23190</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-02-11</td>\n",
       "      <td>tmin</td>\n",
       "      <td>13.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23191</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-02-23</td>\n",
       "      <td>tmax</td>\n",
       "      <td>29.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23192</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-02-23</td>\n",
       "      <td>tmin</td>\n",
       "      <td>10.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                id       date element  value\n",
       "23183  MX000017004 2010-01-30    tmax   27.8\n",
       "23184  MX000017004 2010-01-30    tmin   14.5\n",
       "23185  MX000017004 2010-02-02    tmax   27.3\n",
       "23186  MX000017004 2010-02-02    tmin   14.4\n",
       "23187  MX000017004 2010-02-03    tmax   24.1\n",
       "23188  MX000017004 2010-02-03    tmin   14.4\n",
       "23189  MX000017004 2010-02-11    tmax   29.7\n",
       "23190  MX000017004 2010-02-11    tmin   13.4\n",
       "23191  MX000017004 2010-02-23    tmax   29.9\n",
       "23192  MX000017004 2010-02-23    tmin   10.7"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "molten_weather[(molten_weather[\"date\"].dt.year == 2010)].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tidy Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Arreglar esto requiere la operación `cast`(conversión) o `unstack`(desapilar). Esto realiza el inverso del \"fundido\" (melting), rotando la variable `element` de vuelta hacia las columnas.\n",
    "\n",
    "A continuación, [pd.DataFrame.unstack()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.unstack.html) usa un DataFrame's index como columnas para poder desapilar.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "tidy_weather = molten_weather.set_index([\"id\", \"date\", \"element\"]).unstack()\n",
    "\n",
    "# Make the column headers look as in the paper.\n",
    "tidy_weather.columns = tidy_weather.columns.droplevel(0)\n",
    "tidy_weather.columns.name = None\n",
    "tidy_weather = tidy_weather.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Esta forma está tidy. Hay una variable en cada columna, y cada fila representa la observación de un día."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>tmax</th>\n",
       "      <th>tmin</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12087</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-01-30</td>\n",
       "      <td>27.8</td>\n",
       "      <td>14.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12088</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-02-02</td>\n",
       "      <td>27.3</td>\n",
       "      <td>14.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12089</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-02-03</td>\n",
       "      <td>24.1</td>\n",
       "      <td>14.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12090</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-02-11</td>\n",
       "      <td>29.7</td>\n",
       "      <td>13.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12091</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-02-23</td>\n",
       "      <td>29.9</td>\n",
       "      <td>10.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12092</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-03-05</td>\n",
       "      <td>32.1</td>\n",
       "      <td>14.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12093</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-03-10</td>\n",
       "      <td>34.5</td>\n",
       "      <td>16.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12094</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-03-16</td>\n",
       "      <td>31.1</td>\n",
       "      <td>17.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12095</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-04-27</td>\n",
       "      <td>36.3</td>\n",
       "      <td>16.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12096</th>\n",
       "      <td>MX000017004</td>\n",
       "      <td>2010-05-27</td>\n",
       "      <td>33.2</td>\n",
       "      <td>18.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                id       date  tmax  tmin\n",
       "12087  MX000017004 2010-01-30  27.8  14.5\n",
       "12088  MX000017004 2010-02-02  27.3  14.4\n",
       "12089  MX000017004 2010-02-03  24.1  14.4\n",
       "12090  MX000017004 2010-02-11  29.7  13.4\n",
       "12091  MX000017004 2010-02-23  29.9  10.7\n",
       "12092  MX000017004 2010-03-05  32.1  14.2\n",
       "12093  MX000017004 2010-03-10  34.5  16.8\n",
       "12094  MX000017004 2010-03-16  31.1  17.6\n",
       "12095  MX000017004 2010-04-27  36.3  16.7\n",
       "12096  MX000017004 2010-05-27  33.2  18.2"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tidy_weather[(tidy_weather[\"date\"].dt.year == 2010)].head(10)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.14 ('tidy')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.14"
  },
  "vscode": {
   "interpreter": {
    "hash": "af7127df06252d69ce1b5fcf0f303ad2193973c1f3767a585df649c7fbdfe99b"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}