4. Merging and Joining.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merging and Joining\n",
    "\n",
    "One important task that will come up again and again is merging data together. In Real Life&#8482; we rarely have a single dataset - we need to combine data from multiple sources,\n",
    "Luckily Pandas makes it very simple to join together datasets! Let's start by reading in our previous data and we can think about some possible external data to read in"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "plt.style.use('ggplot')\n",
    "df = pd.read_csv('data/Consumo_cerveja.csv', \n",
    "                 decimal=',', \n",
    "                 thousands='.', \n",
    "                 header=0, \n",
    "                 names=['date','median_temp','min_temp','max_temp','precip','weekend','consumption'], \n",
    "                 parse_dates=['date'], \n",
    "                 nrows=365)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have a timeseries - so holidays is always relevant to look at - is beer consumption affected by holidays? So let's find some holiday data! (and show off pandas `.read_html`!)\n",
    "For all our informational needs, we turn to [wikipedia]('https://en.wikipedia.org/wiki/Public_holidays_in_Brazil'):\n",
    "\n",
    "![warning](images/warning.resized.png) `.read_html` requires `lxml` to be installed\n",
    "\n",
    "`.read_html` will read a webpage and intelligently find all `<table>` elements, converting those to dataframes - we always end up with a list of dataframes, even if there's only one!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "holidays = pd.read_html('https://en.wikipedia.org/wiki/Public_holidays_in_Brazil', header=0)[0]\n",
    "holidays = holidays[holidays[\"Date\"] != \"State dependent\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have a beautiful dataframe straight from Wikipedia! We do have one more step before we are ready to merge - we need to turn `Date` into a `DateTime` type so we can match with our data. We have to remember to add a year, else pandas will interpret it as 1900."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "holidays['Date'] = pd.to_datetime(holidays.Date + ', 2015', format='%B %d, %Y')\n",
    "holidays"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have a nicely formatted dataframe, ready to join. We don't really want all the extra information, we are mostly interested in a binary holiday/not_holiday marker, so let's add a column of ones:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "holidays['holiday'] = 1\n",
    "holidays"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To merge, we simply use `df.merge()` - it takes a number of options, so let's try merging this one first and we can work through them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.merge(holidays, left_on='date', right_on='Date')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First off, we are only getting 8 rows back - why is that? \n",
    "\n",
    "By default, `.merge` assumes you want to do an `inner join` - for those of you who know SQL, this makes perfect sense :-)\n",
    "\n",
    "An inner join returns only those rows where we can match the key in both datasets - in this case we keep only those days that are in both our consumption dataset and our holidays dataset.\n",
    "\n",
    "What we want is to keep all rows in our consumption dataset and add on the rows from holiday that match  - we want a `left join` (This is of course relative to which dataframe we call `.merge` on)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_holidays = df.merge(holidays, left_on='date', right_on='Date', how='left')\n",
    "df_holidays"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have all our rows and pandas simply fills in `NaN` in those rows that don't match. In order to get our binary holiday marker, we simply `.fillna()` our holiday column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_holidays['holiday'] = df_holidays.holiday.fillna(0).astype(int)\n",
    "df_holidays"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We got a lot of junk now that we are not interested in, particulary the duplication of the Date columns - I could simply drop them, but let's look at how we can remove them from the merge alltogether"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_holidays = holidays.rename(columns={\"Date\": \"date\"})[[\"date\", \"holiday\"]]\n",
    "df_holidays = df.merge(merge_holidays, on='date', how='left').assign(holiday=lambda x: x.holiday.fillna(0).astype(int))\n",
    "df_holidays"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the keys have the same name in both datasets, we can use the `on` parameter, which will avoid duplication - we also select out only the columns we are interested in merging in the beginning. Now we have our prepared data!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Joining\n",
    "\n",
    "Joining is a special case of merging, where we simply merge on the index - this can be useful when we know our indexes are the same.\n",
    "\n",
    "The main differences are that `.join` defaults to a `left join` and it only joins on indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.set_index('date')\n",
    "holidays = holidays.set_index('Date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.join(holidays['holiday']).assign(holiday=lambda x: x.holiday.fillna(0).astype(int))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Concatenation\n",
    "\n",
    "Concatenation also comes up often, you can concatenate on both axes - adding more columns and adding more rows.\n",
    "\n",
    "Let's say I want to look only at the top10 and bottom10 days per consumption"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "top10 = df_holidays.sort_values(by='consumption', ascending=False).reset_index(drop=True).head(10)\n",
    "bottom10 = df_holidays.sort_values(by='consumption').reset_index(drop=True).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "top10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bottom10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If I want to compare them easily, I can just concatenate them together"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([bottom10, top10]).sort_values(by='consumption', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What if I want to compare the consumption side by side?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([bottom10.consumption, top10.consumption], axis=1, keys=['bottom', 'top'])"
   ]
  }
 ],
 "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.10.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}