TidyDataAdvanced.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Tidy Data in Python\n",
    "by [Jean-Nicholas Hould](http://www.jeannicholashould.com/)\n",
    "\n",
    "\n",
    "from the blog post of the same name\n",
    "[http://www.jeannicholashould.com/tidy-data-in-python.html](http://www.jeannicholashould.com/tidy-data-in-python.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tidying messy datasets (Advanced)\n",
    "\n",
    "The five most common problems with messy datasets are:\n",
    "\n",
    "- Column headers are values, not variable names\n",
    "- Multiple variables are stored in on column\n",
    "- Variables are stored in both rows and columns\n",
    "- Multiple types of observational units are stored in the same table\n",
    "- A single observational unit is stored in multiple tables\n",
    "\n",
    "We ran through how `melt()` column headers to their own column in the first Tidy Data Work section. Now we'll go over a more complicated version, plus we'll cover the final four types of problems, which are all more complicated.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime\n",
    "from os import listdir\n",
    "from os.path import isfile, join\n",
    "import glob\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### Billboard Top 100 Dataset\n",
    "\n",
    "This dataset represents the weekly rank of songs from the moment they enter the Billboard Top 100 to the subsequent 75 weeks.\n",
    "\n",
    "Problems:\n",
    "\n",
    "- The columns headers are composed of values: the week number (x1st.week, …)\n",
    "- If a song is in the Top 100 for less than 75 weeks, the remaining columns are filled with missing values.\n",
    "\n",
    "Solutions:\n",
    "\n",
    "- Melt weeks from multiple columns\n",
    "- Get rid of NAs\n",
    "- Extract week number from former column headers\n",
    "- Create real dates out of date.entered + number of weeks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>year</th>\n",
       "      <th>artist.inverted</th>\n",
       "      <th>track</th>\n",
       "      <th>time</th>\n",
       "      <th>genre</th>\n",
       "      <th>date.entered</th>\n",
       "      <th>date.peaked</th>\n",
       "      <th>x1st.week</th>\n",
       "      <th>x2nd.week</th>\n",
       "      <th>x3rd.week</th>\n",
       "      <th>...</th>\n",
       "      <th>x67th.week</th>\n",
       "      <th>x68th.week</th>\n",
       "      <th>x69th.week</th>\n",
       "      <th>x70th.week</th>\n",
       "      <th>x71st.week</th>\n",
       "      <th>x72nd.week</th>\n",
       "      <th>x73rd.week</th>\n",
       "      <th>x74th.week</th>\n",
       "      <th>x75th.week</th>\n",
       "      <th>x76th.week</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000</td>\n",
       "      <td>Destiny's Child</td>\n",
       "      <td>Independent Women Part I</td>\n",
       "      <td>3:38</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-09-23</td>\n",
       "      <td>2000-11-18</td>\n",
       "      <td>78</td>\n",
       "      <td>63.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>...</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>1</th>\n",
       "      <td>2000</td>\n",
       "      <td>Santana</td>\n",
       "      <td>Maria, Maria</td>\n",
       "      <td>4:18</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-02-12</td>\n",
       "      <td>2000-04-08</td>\n",
       "      <td>15</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</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>2</th>\n",
       "      <td>2000</td>\n",
       "      <td>Savage Garden</td>\n",
       "      <td>I Knew I Loved You</td>\n",
       "      <td>4:07</td>\n",
       "      <td>Rock</td>\n",
       "      <td>1999-10-23</td>\n",
       "      <td>2000-01-29</td>\n",
       "      <td>71</td>\n",
       "      <td>48.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>...</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>3</th>\n",
       "      <td>2000</td>\n",
       "      <td>Madonna</td>\n",
       "      <td>Music</td>\n",
       "      <td>3:45</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-08-12</td>\n",
       "      <td>2000-09-16</td>\n",
       "      <td>41</td>\n",
       "      <td>23.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>...</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>4</th>\n",
       "      <td>2000</td>\n",
       "      <td>Aguilera, Christina</td>\n",
       "      <td>Come On Over Baby (All I Want Is You)</td>\n",
       "      <td>3:38</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-08-05</td>\n",
       "      <td>2000-10-14</td>\n",
       "      <td>57</td>\n",
       "      <td>47.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>...</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>5</th>\n",
       "      <td>2000</td>\n",
       "      <td>Janet</td>\n",
       "      <td>Doesn't Really Matter</td>\n",
       "      <td>4:17</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-06-17</td>\n",
       "      <td>2000-08-26</td>\n",
       "      <td>59</td>\n",
       "      <td>52.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>...</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>6</th>\n",
       "      <td>2000</td>\n",
       "      <td>Destiny's Child</td>\n",
       "      <td>Say My Name</td>\n",
       "      <td>4:31</td>\n",
       "      <td>Rock</td>\n",
       "      <td>1999-12-25</td>\n",
       "      <td>2000-03-18</td>\n",
       "      <td>83</td>\n",
       "      <td>83.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>...</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>7</th>\n",
       "      <td>2000</td>\n",
       "      <td>Iglesias, Enrique</td>\n",
       "      <td>Be With You</td>\n",
       "      <td>3:36</td>\n",
       "      <td>Latin</td>\n",
       "      <td>2000-04-01</td>\n",
       "      <td>2000-06-24</td>\n",
       "      <td>63</td>\n",
       "      <td>45.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>...</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>8</th>\n",
       "      <td>2000</td>\n",
       "      <td>Sisqo</td>\n",
       "      <td>Incomplete</td>\n",
       "      <td>3:52</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-06-24</td>\n",
       "      <td>2000-08-12</td>\n",
       "      <td>77</td>\n",
       "      <td>66.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>...</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>9</th>\n",
       "      <td>2000</td>\n",
       "      <td>Lonestar</td>\n",
       "      <td>Amazed</td>\n",
       "      <td>4:25</td>\n",
       "      <td>Country</td>\n",
       "      <td>1999-06-05</td>\n",
       "      <td>2000-03-04</td>\n",
       "      <td>81</td>\n",
       "      <td>54.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>...</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 83 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   year      artist.inverted                                  track  time  \\\n",
       "0  2000      Destiny's Child               Independent Women Part I  3:38   \n",
       "1  2000              Santana                           Maria, Maria  4:18   \n",
       "2  2000        Savage Garden                     I Knew I Loved You  4:07   \n",
       "3  2000              Madonna                                  Music  3:45   \n",
       "4  2000  Aguilera, Christina  Come On Over Baby (All I Want Is You)  3:38   \n",
       "5  2000                Janet                  Doesn't Really Matter  4:17   \n",
       "6  2000      Destiny's Child                            Say My Name  4:31   \n",
       "7  2000    Iglesias, Enrique                            Be With You  3:36   \n",
       "8  2000                Sisqo                             Incomplete  3:52   \n",
       "9  2000             Lonestar                                 Amazed  4:25   \n",
       "\n",
       "     genre date.entered date.peaked  x1st.week  x2nd.week  x3rd.week  \\\n",
       "0     Rock   2000-09-23  2000-11-18         78       63.0       49.0   \n",
       "1     Rock   2000-02-12  2000-04-08         15        8.0        6.0   \n",
       "2     Rock   1999-10-23  2000-01-29         71       48.0       43.0   \n",
       "3     Rock   2000-08-12  2000-09-16         41       23.0       18.0   \n",
       "4     Rock   2000-08-05  2000-10-14         57       47.0       45.0   \n",
       "5     Rock   2000-06-17  2000-08-26         59       52.0       43.0   \n",
       "6     Rock   1999-12-25  2000-03-18         83       83.0       44.0   \n",
       "7    Latin   2000-04-01  2000-06-24         63       45.0       34.0   \n",
       "8     Rock   2000-06-24  2000-08-12         77       66.0       61.0   \n",
       "9  Country   1999-06-05  2000-03-04         81       54.0       44.0   \n",
       "\n",
       "      ...      x67th.week  x68th.week  x69th.week  x70th.week  x71st.week  \\\n",
       "0     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "1     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "2     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "3     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "4     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "5     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "6     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "7     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "8     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "9     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "\n",
       "   x72nd.week  x73rd.week  x74th.week  x75th.week  x76th.week  \n",
       "0         NaN         NaN         NaN         NaN         NaN  \n",
       "1         NaN         NaN         NaN         NaN         NaN  \n",
       "2         NaN         NaN         NaN         NaN         NaN  \n",
       "3         NaN         NaN         NaN         NaN         NaN  \n",
       "4         NaN         NaN         NaN         NaN         NaN  \n",
       "5         NaN         NaN         NaN         NaN         NaN  \n",
       "6         NaN         NaN         NaN         NaN         NaN  \n",
       "7         NaN         NaN         NaN         NaN         NaN  \n",
       "8         NaN         NaN         NaN         NaN         NaN  \n",
       "9         NaN         NaN         NaN         NaN         NaN  \n",
       "\n",
       "[10 rows x 83 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"./data/billboard.csv\", encoding=\"mac_latin2\",dtype='str')\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Melting, or \"unpivoting\"\n",
    "\n",
    "# Need to specify the columns you don't want to unpivot, called the id_variables,\n",
    "# and the names of the new columns\n",
    "id_vars = [\"year\",\"artist.inverted\",\"track\",\"time\",\"genre\",\"date.entered\",\"date.peaked\"]\n",
    "df = pd.melt(frame=df, id_vars=id_vars, var_name=\"week\", value_name=\"rank\")\n",
    "\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cleaning out unnecessary rows before formatting numbers properly\n",
    "# since weeks where the track wasn't on the chart were filled with NAs\n",
    "df = df.dropna()\n",
    "\n",
    "# Formatting \n",
    "df[\"week\"] = df['week'].str.extract('(\\d+)', expand=False).astype(int)\n",
    "df[\"rank\"] = df[\"rank\"].astype(int)\n",
    "\n",
    "# Create \"date\" columns\n",
    "df['date'] = pd.to_datetime(df['date.entered']) + pd.to_timedelta(df['week'], unit='w') - pd.DateOffset(weeks=1)\n",
    "\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Only keeping interesting columns\n",
    "df = df[[\"year\", \"artist.inverted\", \"track\", \"time\", \"genre\", \"week\", \"rank\", \"date\"]]\n",
    "\n",
    "# Reassign after sort rather than sort in place\n",
    "df = df.sort_values(ascending=True, by=[\"year\",\"artist.inverted\",\"track\",\"week\",\"rank\"])\n",
    "\n",
    "# Assigning the tidy dataset to a variable for future usage\n",
    "billboard = df\n",
    "\n",
    "billboard.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A tidier version of the dataset is shown below. There is still a lot of repetition of the song details: the track name, time and genre. For this reason, this dataset is still not completely tidy as per Wickham’s definition. We will address this in the next example."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Multiple types in one table\n",
    "\n",
    "Following up on the Billboard dataset, we’ll now address the repetition problem of the previous table.\n",
    "\n",
    "Problem:\n",
    "\n",
    "* Multiple observational units (the `song` and its `rank`) in a single table.\n",
    "\n",
    "Solution:\n",
    "\n",
    "* Create a new `songs` table with a primary key, `song_id`\n",
    "* Join the `songs` table with the original `billboard` table to transfer over the `song_id` which acts as a foreign key, then only keep the columns related to the song rankings\n",
    "\n",
    "We’ll first create a `songs` table which contains the details of each song:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Keep only the columns from the `billboard` table which are attributes of the songs \n",
    "# themselves, not anything about their rankings\n",
    "songs_cols = [\"year\", \"artist.inverted\", \"track\", \"time\", \"genre\"]\n",
    "\n",
    "# Drop all the duplicate rows which used to be separate weeks in the rankings\n",
    "songs = billboard[songs_cols].drop_duplicates()\n",
    "\n",
    "# Reindex that new table so each row gets an integer index in order\n",
    "songs = songs.reset_index(drop=True)\n",
    "\n",
    "# Create a new `song_id` column \n",
    "songs[\"song_id\"] = songs.index\n",
    "\n",
    "songs.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We’ll then create a `ranks` table which only contains the `song_id`, `date` and the `rank`.\n",
    "\n",
    "Doing a \"merge\" here between `billboard` and `songs` and then in the end only keeping three of the columns seems like a lot of extra overhead or wasted work, but it's a foolproof way to get the `song_id` foreign key associations correct in the new `ranks` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ranks = pd.merge(billboard, songs, on=[\"year\",\"artist.inverted\", \"track\", \"time\", \"genre\"])\n",
    "ranks = ranks[[\"song_id\", \"date\",\"rank\"]]\n",
    "ranks.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Multiple variables stored in one column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tubercolosis Example"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A few notes on the raw data set:\n",
    "\n",
    "- The columns starting with \"m\" or \"f\" contain multiple variables: \n",
    "    - Sex (\"m\" or \"f\")\n",
    "    - Age Group (\"0-14\",\"15-24\", \"25-34\", \"45-54\", \"55-64\", \"65\", \"unknown\")\n",
    "- Mixture of 0s and missing values(\"NaN\"). This is due to the data collection process and the distinction is important for this dataset.\n",
    "\n",
    "In order to tidy this dataset, we need to remove the different values from the header and unpivot them into rows. We’ll first need to melt the `sex + age` group columns into a single one. Once we have that single column, we’ll derive three columns from it: `sex`, `age_lower` and `age_upper`. With those, we’ll be able to properly build a tidy dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"./data/tb-raw.csv\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.melt(df, id_vars=[\"country\",\"year\"], value_name=\"cases\", var_name=\"sex_and_age\")\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract Sex, Age lower bound and Age upper bound group\n",
    "# Regex pattern: Match a non-digit character at the beginning\n",
    "#                Match exactly two digit characters at the end\n",
    "#                Match one or more digits in between\n",
    "tmp_df = df[\"sex_and_age\"].str.extract(\"(\\D)(\\d+)(\\d{2})\", expand=False)    \n",
    "\n",
    "# Name columns\n",
    "tmp_df.columns = [\"sex\", \"age_lower\", \"age_upper\"]\n",
    "\n",
    "# Create `age` range column based on `age_lower` and `age_upper`\n",
    "tmp_df[\"age\"] = tmp_df[\"age_lower\"] + \"-\" + tmp_df[\"age_upper\"]\n",
    "\n",
    "# Concatenate the original dataframe with this new dataframe\n",
    "# axis=0 is down, axis=1 is across\n",
    "df = pd.concat([df, tmp_df], axis=1)\n",
    "\n",
    "# Drop unnecessary columns\n",
    "df = df.drop(['sex_and_age',\"age_lower\",\"age_upper\"], axis=1)\n",
    "# Drop unnecessary rows\n",
    "df = df.dropna()\n",
    "# Sort\n",
    "df = df.sort_values(ascending=True,by=[\"country\", \"year\", \"sex\", \"age\"])\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Variables are stored in both rows and columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Global Historical Climatology Network Dataset\n",
    "\n",
    "This dataset represents the daily weather records for a weather station (MX17004) in Mexico for five months in 2010.\n",
    "\n",
    "Problem:\n",
    "\n",
    "- Variables are stored in both rows (tmin, tmax) and columns (days).\n",
    "\n",
    "In order to make this dataset tidy, we want to move the three misplaced variables as three individual columns: `tmin`, `tmax` and `date`.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"./data/weather-raw.csv\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.melt(df, id_vars=[\"id\", \"year\",\"month\",\"element\"], var_name=\"day_raw\")\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# There is also a small problem where some of the IDs have a space afterwards\n",
    "set(df.id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "# Clean by assigning the same value to all rows\n",
    "df[\"id\"] = \"MX17004\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extracting day\n",
    "# Regex pattern finds the first d, but only captures the digits after that\n",
    "df[\"day\"] = df[\"day_raw\"].str.extract(\"d(\\d+)\", expand=False)  \n",
    "\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# To numeric values (original conversion)\n",
    "# df[[\"year\",\"month\",\"day\"]] = df[[\"year\",\"month\",\"day\"]].apply(lambda x: pd.to_numeric(x, errors='ignore'))\n",
    "\n",
    "# To integers\n",
    "df[[\"year\",\"month\",\"day\"]] = df[[\"year\",\"month\",\"day\"]].astype(int)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating a date from the different columns\n",
    "df[\"date\"] = pd.to_datetime(df[[\"year\", \"month\", \"day\"]])\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(['year',\"month\",\"day\", \"day_raw\"], axis=1)\n",
    "df = df.dropna()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Unmelting column \"element\"\n",
    "df = df.pivot_table(index=[\"id\",\"date\"], columns=\"element\", values=\"value\")\n",
    "df.reset_index(drop=False, inplace=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## One type in multiple tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Baby Names in North Carolina and Minnesota\n",
    "\n",
    "Dataset: Top 10 male and female baby names in MN and NC for 1916 & 2016\n",
    "\n",
    "Problems:\n",
    "\n",
    "- The data is spread across multiple tables/files.\n",
    "- The “Year” and \"State\" variables are present in the file name.\n",
    "\n",
    "In order to load those different files into a single DataFrame, we can run a custom script that will append the files together. Furthermore, we’ll need to extract the “Year” and \"State\" variables from the file name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = './data'\n",
    "allFiles = glob.glob(path + \"/*_baby_names_*.csv\")\n",
    "allFiles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_variables_from_filename(string):\n",
    "    match = re.match(\".*([A-Z]{2})_baby_names_(\\d{4}).*\", string) \n",
    "    if match != None: \n",
    "        return {\"state\":match.group(1), \"year\":match.group(2)}\n",
    "    \n",
    "frame = pd.DataFrame()\n",
    "df_list= []\n",
    "for file_ in allFiles:\n",
    "    df = pd.read_csv(file_, index_col=None, header=0)\n",
    "    df.columns = map(str.lower, df.columns)\n",
    "    variables_dict = extract_variables_from_filename(file_)\n",
    "    df[\"year\"] = variables_dict[\"year\"]\n",
    "    df[\"state\"] = variables_dict[\"state\"]\n",
    "    df_list.append(df)\n",
    "\n",
    "# axis=0 is the default for pd.concat()\n",
    "df = pd.concat(df_list)\n",
    "df.head(10)"
   ]
  }
 ],
 "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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}