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": null,
   "metadata": {
    "tags": [
     "setup"
    ]
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "pd.set_option('display.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": null,
   "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. Save\n",
    "the result as `res_1`. Your result should have the same number of records\n",
    "as `sp5_jan`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_jan = sales.set_index(\"date\").reindex(sp5_jan.index, method=\"ffill\")\n",
    "res_1 = sp5_jan.join(sales_jan)\n",
    "\n",
    "assert res_1.shape[0] == sp5_jan.shape[0]"
   ]
  },
  {
   "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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "res_2 = pd.melt(res_1.reset_index(), 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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "res_3a = res_2.pivot(index='date', columns='variable')\n",
    "res_3a.head()"
   ]
  },
  {
   "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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "res_3b = res_2.set_index(['date', 'variable']).unstack()\n",
    "res_3b.head()"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Tags",
  "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.8"
  },
  "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": 4
}