1_encabezados_columnas_son_valores-Copy1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Encabezados de las columnas son valores\n",
    "\n",
    "Este notebook muestra dos ejemplos de como los encabezados o nombres de las columnas muestran valores. Este tipo de \"messy datasets\" tienen uso práctico en dos tipos de situaciones:\n",
    "\n",
    "1. Presentaciones\n",
    "2. Registro de observaciones espaciadas regularmente en el tiempo."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## \"Housekeeping\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext lab_black"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'savReaderWriter'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "Input \u001b[0;32mIn [2]\u001b[0m, in \u001b[0;36m<cell line: 5>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mre\u001b[39;00m\n\u001b[1;32m      4\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mpandas\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m \u001b[38;5;21;01mpd\u001b[39;00m\n\u001b[0;32m----> 5\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01msavReaderWriter\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m \u001b[38;5;21;01mspss\u001b[39;00m\n",
      "\u001b[0;31mModuleNotFoundError\u001b[0m: No module named 'savReaderWriter'"
     ]
    }
   ],
   "source": [
    "import datetime\n",
    "import re\n",
    "\n",
    "import pandas as pd\n",
    "import savReaderWriter as spss"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ejemplo 1: Religion vs. Income\n",
    "\n",
    "> Un tipo de dataset messy común son los datos tabulares diseñados para **presentación**, donde las variables forman tanto filas y columnas, y los encabezados de las columnas son valores, y no nombres de las variables.\n",
    "\n",
    "El [Pew Research Center](http://www.pewresearch.org/) es un centro de estudios muy prolífico e influyente en investigación sobre todo tipo de aspectos de la vida en EEUU. Los siguientes ejemplos usan datos tomados del  [Religious Landscape Study](http://www.pewforum.org/religious-landscape-study/)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cargando la data\n",
    "\n",
    "Los datos son entregados en un archivo de datos de SPSS. Esta es una especificación binaria con una sección de encabezado describiendo los datos, por ejemplo, que variables/columnas están incluidas y que instancias pueden tener los datos categóricos."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cargando la \"metadata\" del dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = [\"q16\", \"reltrad\", \"income\"]\n",
    "encodings = {}\n",
    "\n",
    "# For the sake of simplicity, all data cleaning operations\n",
    "# are done within the for-loop for all columns.\n",
    "with spss.SavHeaderReader(\"data/pew.sav\") as pew:\n",
    "    for column in columns:\n",
    "        encodings[column] = {\n",
    "            int(key): (\n",
    "                re.sub(\n",
    "                    r\"\\(.*\\)\",\n",
    "                    \"\",\n",
    "                    (\n",
    "                        value.decode(\"iso-8859-1\")\n",
    "                        .replace(\"\\x92\", \"'\")\n",
    "                        .replace(\" Churches\", \"\")\n",
    "                        .replace(\"Less than $10,000\", \"<$10k\")\n",
    "                        .replace(\"10 to under $20,000\", \"$10-20k\")\n",
    "                        .replace(\"20 to under $30,000\", \"$20-30k\")\n",
    "                        .replace(\"30 to under $40,000\", \"$30-40k\")\n",
    "                        .replace(\"40 to under $50,000\", \"$40-50k\")\n",
    "                        .replace(\"50 to under $75,000\", \"$50-75k\")\n",
    "                        .replace(\"75 to under $100,000\", \"$75-100k\")\n",
    "                        .replace(\"100 to under $150,000\", \"$100-150k\")\n",
    "                        .replace(\"$150,000 or more\", \">150k\")\n",
    "                    ),\n",
    "                ).strip()\n",
    "            )\n",
    "            for (key, value) in pew.all().valueLabels[column.encode()].items()\n",
    "        }"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cargando la data y preparándola tal como está presentada en el *paper*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with spss.SavReader(\n",
    "    \"data/pew.sav\", selectVars=[column.encode() for column in columns]\n",
    ") as pew:\n",
    "    pew = list(pew)\n",
    "\n",
    "# Use the above encodings to map the numeric data\n",
    "# to the actual labels.\n",
    "pew = pd.DataFrame(pew, columns=columns, dtype=int)\n",
    "for column in columns:\n",
    "    pew[column] = pew[column].map(encodings[column])\n",
    "\n",
    "for value in (\"Atheist\", \"Agnostic\"):\n",
    "    pew.loc[(pew[\"q16\"] == value), \"reltrad\"] = value\n",
    "\n",
    "income_columns = [\n",
    "    \"<$10k\",\n",
    "    \"$10-20k\",\n",
    "    \"$20-30k\",\n",
    "    \"$30-40k\",\n",
    "    \"$40-50k\",\n",
    "    \"$50-75k\",\n",
    "    \"$75-100k\",\n",
    "    \"$100-150k\",\n",
    "    \">150k\",\n",
    "    \"Don't know/Refused\",\n",
    "]\n",
    "\n",
    "pew = pew.groupby([\"reltrad\", \"income\"]).size().unstack(\"income\")\n",
    "pew = pew[income_columns]\n",
    "pew.index.name = \"religion\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Messy Data\n",
    "\n",
    "La siguiente celda muestra la data \"sin procesar\" (\"cruda\", raw-data) (es decir, se supone que el preprocesamiento realizado anteriormente lo realiza otra persona y al analista de datos solo se le entrega el conjunto de datos a continuación)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pew.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pew.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tidy Data\n",
    "\n",
    "> Este dataset tiene  **tres** variables, **religion**, **income** y **frequency**. Para hacerlo *tidy*, necesitamos *\"fundirlo\"* (hacer un **melt**), o apilarlo. En otras palabras, necesitamos convertir columnas en filas.\n",
    "\n",
    "`pandas` provee un método[pd.melt()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.melt.html) para *des-pivotear* el dataset.\n",
    "\n",
    "**Notas:** `.reset_index()` transforma la columna de índice de religión en una columna de datos (`pd.melt()` lo necesita). Además, la tabla resultante se ordena implícitamente por la columna `\"religión\"`. Para llegar al mismo orden que en el *paper*, la tabla *fundida* se ordena explícitamente."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "molten_pew = pd.melt(pew.reset_index(), id_vars=[\"religion\"], value_name=\"frequency\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a ordered column for the income labels.\n",
    "income_dtype = pd.api.types.CategoricalDtype(income_columns, ordered=True)\n",
    "molten_pew[\"income\"] = molten_pew[\"income\"].astype(income_dtype)\n",
    "molten_pew = molten_pew.sort_values([\"religion\", \"income\"]).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "molten_pew.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "molten_pew.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ejemplo 2: Billboard\n",
    "\n",
    "> Otro uso común de este formato de datos es registrar observaciones espaciadas regularmente a lo largo del tiempo. Por ejemplo, el conjunto de datos de Billboard que se muestra en la Tabla 7, registra la fecha en que una canción ingresó por primera vez al Billboard Top 100. Tiene variables para **artist**, **track**, **date.entered**, **rank* * y **week**. El rango en cada semana después de que ingresa al top 100 se registra en 75 columnas,  `wk1` a `wk75`. Si una canción está en el Top 100 por menos de 75 semanas, las columnas restantes se llenan con valores faltantes. Esta forma de almacenamiento no es ordenada, pero es útil para la entrada de datos. Reduce la duplicación ya que, de lo contrario, cada canción de cada semana necesitaría su propia fila, y los metadatos de la canción, como el título y el artista, tendrían que repetirse.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cargando la data\n",
    "\n",
    "Los datos vienen en un archivo CSV con columnas con nombre de número de semana de una manera engorrosa."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Usage of \"1st\", \"2nd\", \"3rd\" should be forbidden by law :)\n",
    "usecols = [\"artist.inverted\", \"track\", \"time\", \"date.entered\"] + (\n",
    "    [f\"x{i}st.week\" for i in range(1, 76, 10) if i != 11]\n",
    "    + [f\"x{i}nd.week\" for i in range(2, 76, 10) if i != 12]\n",
    "    + [f\"x{i}rd.week\" for i in range(3, 76, 10) if i != 13]\n",
    "    + [f\"x{i}th.week\" for i in range(1, 76) if (i % 10) not in (1, 2, 3)]\n",
    "    + [f\"x11th.week\", f\"x12th.week\", f\"x13th.week\"]\n",
    ")\n",
    "billboard = pd.read_csv(\n",
    "    \"data/billboard.csv\",\n",
    "    encoding=\"iso-8859-1\",\n",
    "    parse_dates=[\"date.entered\"],\n",
    "    usecols=usecols,\n",
    ")\n",
    "\n",
    "billboard = billboard.assign(year=lambda x: x[\"date.entered\"].dt.year)\n",
    "\n",
    "# Rename the week columns.\n",
    "week_columns = {\n",
    "    column: (\"wk\" + re.sub(r\"[^\\d]+\", \"\", column))\n",
    "    for column in billboard.columns\n",
    "    if column.endswith(\".week\")\n",
    "}\n",
    "billboard = billboard.rename(columns={\"artist.inverted\": \"artist\", **week_columns})\n",
    "\n",
    "# Ensure the columns' order is the same as in the paper.\n",
    "columns = [\"year\", \"artist\", \"track\", \"time\", \"date.entered\"] + [\n",
    "    f\"wk{i}\" for i in range(1, 76)\n",
    "]\n",
    "billboard = billboard[columns]\n",
    "\n",
    "# Ensure the rows' order is similar as in the paper.\n",
    "# For unknown reasons the exact ordering as in the paper cannot be reconstructed.\n",
    "billboard = billboard[billboard[\"year\"] == 2000]\n",
    "billboard = billboard.sort_values([\"artist\", \"track\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Messy Data\n",
    "\n",
    "De nuevo, la siguiente celda muestra los datos como si fueran realmente entregados como datos \"crudos\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "billboard.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "billboard.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### \"Tidy\" Data\n",
    "\n",
    "Como antes, el método `pd.melt()` se usa para transformar los datos desde un formato \"wide\" (\"ancho\") a uno \"largo\" (\"long\")."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "molten_billboard = pd.melt(\n",
    "    billboard,\n",
    "    id_vars=[\"year\", \"artist\", \"track\", \"time\", \"date.entered\"],\n",
    "    var_name=\"week\",\n",
    "    value_name=\"rank\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A diferencia de `R`, `pandas` mantiene (innecesariamente según algunos) filas para semanas donde la canción ya estaba fuera del ranking. Estas observaciones son descartadas. También, una nueva columna `\"date\"` es añadida, indicando cuando exactamente una canción en particular estuvo en un cierto ranking."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pandas keeps \"wide\" variables that had missing values as rows.\n",
    "molten_billboard = molten_billboard[molten_billboard[\"rank\"].notnull()]\n",
    "\n",
    "# Cast as integer after missing values are removed.\n",
    "molten_billboard[\"week\"] = molten_billboard[\"week\"].map(lambda x: int(x[2:]))\n",
    "molten_billboard[\"rank\"] = molten_billboard[\"rank\"].map(int)\n",
    "\n",
    "# Calculate the actual week from the date of first entering the list.\n",
    "molten_billboard = molten_billboard.assign(\n",
    "    date=lambda x: x[\"date.entered\"] + (x[\"week\"] - 1) * datetime.timedelta(weeks=1)\n",
    ")\n",
    "\n",
    "# Sort rows and columns as in the paper.\n",
    "molten_billboard = molten_billboard[\n",
    "    [\"year\", \"artist\", \"time\", \"track\", \"date\", \"week\", \"rank\"]\n",
    "]\n",
    "molten_billboard = molten_billboard.sort_values([\"artist\", \"track\", \"week\"])\n",
    "molten_billboard = molten_billboard.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ten en cuenta que este conjunto de datos aún no está completamente `tidy`, como se explicará en el notebook 4."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "molten_billboard.head(15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Guardando los datos\n",
    "El dataset de bilboard ya \"ordenado\" (\"tidy\") es guardado como input para el notebook 4."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "molten_billboard.to_csv(\"data/billboard_cleaned.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}