06-advanced-merge-reshape-answers.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "setup"
    ]
   },
   "source": [
    "(c) 2016 - present. Enplus Advisors, Inc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "tags": [
     "setup"
    ]
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "pd.set_option('precision', 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "setup"
    ]
   },
   "source": [
    "**Data**\n",
    "\n",
    "* `sp5_jan` is SP500 market close prices and trading volume for\n",
    "  January 2015.\n",
    "* `sales` is weekly sales data for Acme Widgets Co. for January\n",
    "  2015 in thousands of widgets sold and \\$ millions in revenue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "tags": [
     "setup"
    ]
   },
   "outputs": [],
   "source": [
    "np.random.seed(100)\n",
    "\n",
    "sp5 = pd.read_csv(\n",
    "    'sp500.csv', parse_dates=['date'], index_col=['date'],\n",
    "    usecols=['date', 'close', 'volume'])\\\n",
    "    .sort_index()\n",
    "\n",
    "sp5_jan = sp5.loc['2015-01', :].copy()\n",
    "sp5_jan['volume'] = sp5_jan['volume'] / 1e6\n",
    "sales = pd.DataFrame({\n",
    "    'date': pd.date_range('2015-01-01', '2015-01-31', freq='W'),\n",
    "})\n",
    "sales['widgets_sold'] = abs(10 * np.random.randn(sales.shape[0])).round()\n",
    "sales['revenue'] = sales.widgets_sold * 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "exercise"
    ]
   },
   "source": [
    "**Exercise:**\n",
    "\n",
    "Merge `sp5_jan` with `sales`, filling sales data forward, saving\n",
    "the result as `res_1`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>date</th>\n",
       "      <th>widgets_sold</th>\n",
       "      <th>revenue</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2058.20</td>\n",
       "      <td>2708.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>17.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>2058.20</td>\n",
       "      <td>2708.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-01-05</td>\n",
       "      <td>17.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>2020.58</td>\n",
       "      <td>3799.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-01-06</td>\n",
       "      <td>17.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>2002.61</td>\n",
       "      <td>4460.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-01-07</td>\n",
       "      <td>17.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>2025.90</td>\n",
       "      <td>3805.48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  widgets_sold  revenue    close   volume\n",
       "0 2015-01-02           NaN      NaN  2058.20  2708.70\n",
       "1 2015-01-04          17.0    340.0  2058.20  2708.70\n",
       "2 2015-01-05          17.0    340.0  2020.58  3799.12\n",
       "3 2015-01-06          17.0    340.0  2002.61  4460.11\n",
       "4 2015-01-07          17.0    340.0  2025.90  3805.48"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res_1 = pd.merge_ordered(sales, sp5_jan.reset_index(), on='date', fill_method='ffill')\n",
    "res_1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "exercise"
    ]
   },
   "source": [
    "**Exercise:**\n",
    "\n",
    "Convert the output from the previous exercise to long format with\n",
    "`date` as the ID variable, saving the result as `res_2`"
   ]
  },
  {
   "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>date</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-02</td>\n",
       "      <td>widgets_sold</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>widgets_sold</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-01-05</td>\n",
       "      <td>widgets_sold</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-01-06</td>\n",
       "      <td>widgets_sold</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-01-07</td>\n",
       "      <td>widgets_sold</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date      variable  value\n",
       "0 2015-01-02  widgets_sold    NaN\n",
       "1 2015-01-04  widgets_sold   17.0\n",
       "2 2015-01-05  widgets_sold   17.0\n",
       "3 2015-01-06  widgets_sold   17.0\n",
       "4 2015-01-07  widgets_sold   17.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res_2 = pd.melt(res_1, id_vars='date')\n",
    "res_2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "exercise"
    ]
   },
   "source": [
    "**Exercise**\n",
    "\n",
    "Convert `res_2` back to wide format using the `pivot` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "res_3a = res_2.pivot(index='date', columns='variable')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [
     "exercise"
    ]
   },
   "source": [
    "**Exercise**\n",
    "\n",
    "Convert `res_2` back to wide format using the `unstack` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "res_3b = res_2.set_index(['date', 'variable']).unstack()"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Tags",
  "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.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}