{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "24431bce",
   "metadata": {},
   "source": [
    "# In-Class Exercises, Session 8"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9873883",
   "metadata": {},
   "source": [
    "<B>Please note</B> that data files are located in the parent directory.  This means that you must prepend <B>../</B> to each filename to find it."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00ca96a9",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8f0469d",
   "metadata": {},
   "source": [
    "IMPORTING PANDAS AND NUMPY"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f96ce95d",
   "metadata": {},
   "source": [
    "<B>8.1:  Import pandas and numpy.  Check the .\\_\\_version\\_\\_ attribute of the pandas module.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9729d4de",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "print(pd.__version__)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f29c5fbe",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5a0e840",
   "metadata": {},
   "source": [
    "<B>8.2:  Include some useful settings.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0fff33fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "## Enable multiple outputs from jupyter cells\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\"\n",
    "\n",
    "# set the \"Copy-on-Write\" functionality to avoid\n",
    "# \"copy of a slice\" warning and behavior\n",
    "pd.options.mode.copy_on_write = True\n",
    "\n",
    "## set default number of DataFrame rows printed to 8\n",
    "pd.set_option('display.max_rows', 8)\n",
    "\n",
    "# suppress excel reader warning:  'extension is unsupported'\n",
    "warnings.filterwarnings('ignore', category=UserWarning, module='openpyxl')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0743b657",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca6a3eb2",
   "metadata": {},
   "source": [
    "INITIALIZING A DATAFRAME"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "709be78c",
   "metadata": {},
   "source": [
    "<B>8.3:  <U>DataFrame from python struct</U>.  Use this 3x3 list of lists to build a DataFrame.  Add a keyword argument <B>columns=[]</B> to the DataFrame to assign column labels, and <B>index=[]</B> to assign row labels.  Print the DataFrame.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "629d0b1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Sample data in the form of a list of lists\n",
    "data = [\n",
    "    [1, 2.5, 'apple'],\n",
    "    [2, 3.0, 'banana'],\n",
    "    [3, 4.2, 'orange']\n",
    "]\n",
    "\n",
    "df = pd.DataFrame(data, columns=['num', 'float', 'text'],\n",
    "                        index=['a', 'b', 'c'])\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e57c8f9e",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8296d1f",
   "metadata": {},
   "source": [
    "DATAFRAME FROM DATA FILES: CSV, JSON, SQL, HTML"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "513852d5",
   "metadata": {},
   "source": [
    "<B>8.4:  <U>DataFrame from csv or excel</U>.  Read <B>../revenue.csv</B> using the <B>pd.read\\_csv()</B> function.  Then read <B>../revenue.xlsx</B> using the <B>pd.read\\_excel()</B> function.  Print each DataFrame.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d0bd23d",
   "metadata": {},
   "source": [
    "(Note that the <B>openpyxl</B> module is required for reading <B>.xls</B> and <B>xlrd</B> module is required for eading <B>.xlsx</B> files.  The <B>engine=</B> argument may be required, specifying one of the two, although in tests it appeared that this is no longer necessary.)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a65515da",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname     = '../revenue.csv'\n",
    "xlsx_name = '../revenue.xlsx'\n",
    "xls_name  = '../revenue.xls'\n",
    "\n",
    "df = pd.read_csv(fname)\n",
    "df2 = pd.read_excel(xlsx_name)\n",
    "df3 = pd.read_excel(xls_name)\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ca09c98",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2bf26cb9",
   "metadata": {},
   "source": [
    "<B>8.5:  <U>Customize a data import</U>.  Add keyword arguments to configure the <B>pd.read\\_csv()</B> function.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7025a235",
   "metadata": {},
   "source": [
    "The <B>revenue\\_extended.csv</B> file has 2 header lines (use <B>skiprows=</B>) and 2 footer lines (use <B>skipfooter=</B>).  The column we'd like to use as the index is the <B>'id'</B> column (use <B>index\\_col=</B>).\n",
    "\n",
    "Skipping rows means we must use the 'python' engine -- configure with <B>engine='python'</B>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "47fad592",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../revenue_extended.csv'  # has extra headers, id field\n",
    "\n",
    "df = pd.read_csv(fname, skiprows=2, skipfooter=2, index_col='id', engine='python')\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4016f671",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "061d6c19",
   "metadata": {},
   "source": [
    "<B>8.6:  <U>DataFrame from SQL</U>.  Query the <B>revenue</B> table in <B>../testdb.db</B>, passing the database connection object and the query below in the <B>pd.read\\_sql()</B> function.  Print the DataFrame.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "627d5785",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3\n",
    "\n",
    "db_fname = '../testdb.db'\n",
    "\n",
    "query = 'SELECT * FROM revenue'\n",
    "\n",
    "# database connection object\n",
    "conn = sqlite3.connect(db_fname)\n",
    "\n",
    "df = pd.read_sql(query, conn)\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8be5b76",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04bb0ea5",
   "metadata": {},
   "source": [
    "<B>8.7:  <U>DataFrame from JSON</U>.  Read the JSON file organized as a list of dicts, as well as one organized as a dict of dicts, into a DataFrame using the <B>.read\\_json()</B> function.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d503d9a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# list of dicts\n",
    "lod_filename = '../weather_newyork_lod.json'\n",
    "\n",
    "# dict of dicts\n",
    "dod_filename = '../weather_newyork_dod.json'\n",
    "\n",
    "\n",
    "df = pd.read_json(lod_filename, orient='records')\n",
    "print(df)\n",
    "\n",
    "df = pd.read_json(dod_filename, orient='index')\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25fad7bc",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1333efbc",
   "metadata": {},
   "source": [
    "<B>8.8:  <U>DataFrame from embedded JSON</U>.  Look at the typical output of an <B>Alphavantage</B> API query, which contains a dict of dicts \"embedded\" within a larger JSON struct (this is saved in a local file is called <B>alphavantage\\_5min.json</B>).  \"Drill down\" to the dict of dicts by using a subscript, and assign the dod by itself to a variable.  Then read the dict of dicts object into a DataFrame using <B>pd.DataFrame.from\\_dict()</B> function, with orient='index'.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df3fe2f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "\n",
    "\n",
    "fname = '../alphavantage_5min.json'\n",
    "\n",
    "fh = open(fname)\n",
    "\n",
    "obj = json.load(fh)\n",
    "\n",
    "dod = obj['Time Series (5min)']\n",
    "\n",
    "df = pd.DataFrame.from_dict(dod, orient='index')\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a19b5d7f",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9324de9a",
   "metadata": {},
   "source": [
    "<B>8.9:  <U>DataFrame from HTML table</U>.  Read the HTML table into a DataFrame using the <B>.read\\_html()</B> function.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "149b0a67",
   "metadata": {},
   "source": [
    "The function returns a list of DataFrames, one for each table found within the HTML page.  Subscript this list to get the 1st (and only) table.\n",
    "\n",
    "For a bonus, when reading the dataframe, set the 'EST' column to the index and use the <B>parse\\_dates=True</B> parameter to convert the dates in the index to date-type objects (pandas <B>Timestamp</B>.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "008411e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "fname = '../weather_newyork.html'\n",
    "\n",
    "df_list = pd.read_html(fname, index_col='EST', parse_dates=True)\n",
    "\n",
    "df = df_list[0]\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab1d8e14",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a89f8fb7",
   "metadata": {},
   "source": [
    "QUICK APPRAISAL OF DATAFRAME"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "201ca142",
   "metadata": {},
   "source": [
    "<B>8.10:  <U>Get an overview of the DataFrame</U>:</B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f87f509",
   "metadata": {},
   "source": [
    "<UL>\n",
    "  <LI>Print <B>df.shape</B> to see the number of columns and rows</LI>\n",
    "  <LI>Print the DataFrame and look at the stats listed below it</LI>\n",
    "</UL>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6bc80aeb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../weather_newyork_narrow.csv'\n",
    "\n",
    "df = pd.read_csv(fname)\n",
    "\n",
    "print(df.shape)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6e7c255",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49715fc1",
   "metadata": {},
   "source": [
    "<B>8.11:  <U>See the head and tail of a DataFrame</U>:</B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9627da38",
   "metadata": {},
   "source": [
    "<UL>\n",
    "  <LI>Use <B>.head()</B> and <B>.tail()</B></LI>\n",
    "  <LI>Use <B>.columns</B> and <B>.index</B></LI>\n",
    "</UL>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d1578ff4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../weather_newyork_narrow.csv'\n",
    "\n",
    "df = pd.read_csv(fname)\n",
    "\n",
    "print(df.head())\n",
    "print()\n",
    "\n",
    "print(df.tail())\n",
    "print()\n",
    "\n",
    "print(df.columns)\n",
    "print()\n",
    "\n",
    "print(df.index)\n",
    "print()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d23a42ac",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e213e4b5",
   "metadata": {},
   "source": [
    "<B>8.12:  <U>Get another overview on a larger DataFrame</U>:</B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dfb25c6",
   "metadata": {},
   "source": [
    "<UL>\n",
    "  <LI>Look at <B>.dtypes</B></LI>\n",
    "  <LI>Look at <B>.info()</B></LI>\n",
    "</UL>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a98ef8bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../weather_newyork_narrow.csv'\n",
    "\n",
    "df = pd.read_csv(fname)\n",
    "\n",
    "print(df)\n",
    "print()\n",
    "\n",
    "print(df.dtypes)\n",
    "print()\n",
    "\n",
    "print(df.info())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "306e9eb5",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cffee841",
   "metadata": {},
   "source": [
    "DATAFRAME SERIES"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b77a907d",
   "metadata": {},
   "source": [
    "<B>8.13:  <U>View a DataFrame column</U>.  View the 'company' column using subscript syntax (df['company']), and the same column using attribute syntax (df.company).  Print the type of this object, as well as the object itself.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "867be7d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../revenue_extended.csv'  # has extra headers, id field\n",
    "\n",
    "df = pd.read_csv(fname, skiprows=2,\n",
    "                        skipfooter=2,\n",
    "                        index_col='id',\n",
    "                        engine='python')\n",
    "\n",
    "print(df['company'])\n",
    "print()\n",
    "\n",
    "print(df.company)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "977fc630",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "480fdedb",
   "metadata": {},
   "source": [
    "<B>8.14:  <U>View a DataFrame row</U>.  View the 'hd34' row using <B>.loc['hd34']</B>, and the same row using <B>.iloc[0]</B>.  Print the object.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4544a7be",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../revenue_extended.csv'  # has extra headers, id field\n",
    "\n",
    "df = pd.read_csv(fname, skiprows=2,\n",
    "                        skipfooter=2,\n",
    "                        index_col='id',\n",
    "                        engine='python')\n",
    "\n",
    "print(df.loc['hd34'])\n",
    "print()\n",
    "\n",
    "print(df.iloc[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b7194a2",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48e3e99d",
   "metadata": {},
   "source": [
    "<B>8.15:  <U>View Series .name and .index</U>.  First assign the 'company' column Series to a variable.  Then read these attributes from the Series.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b78c6f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../revenue_extended.csv'  # has extra headers, id field\n",
    "\n",
    "df = pd.read_csv(fname, skiprows=2,\n",
    "                        skipfooter=2,\n",
    "                        index_col='id',\n",
    "                        engine='python')\n",
    "\n",
    "s = df.company\n",
    "\n",
    "print(s.name)\n",
    "\n",
    "print(s.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c63eaa9",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b23025d",
   "metadata": {},
   "source": [
    "MANIPULATING THE INDEX"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cbff0100",
   "metadata": {},
   "source": [
    "<B>8.16:  Print the .index and .columns attributes on this DataFrame.  Then reorder the rows to 'a', 'b', 'c' using <B>.reindex()</B>.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86fed061",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "\n",
    "data = [\n",
    "    [1, 2.5, 'apple'],\n",
    "    [2, 3.0, 'banana'],\n",
    "    [3, 4.2, 'orange']\n",
    "]\n",
    "\n",
    "df = pd.DataFrame(data, columns=['num', 'float', 'text'],\n",
    "                        index=['c', 'b', 'a'])\n",
    "\n",
    "\n",
    "print(df.index)\n",
    "print(df.columns)\n",
    "\n",
    "df = df.reindex(['a', 'b', 'c'])\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43060a64",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf127f58",
   "metadata": {},
   "source": [
    "<B>8.17:  Reading from <B>weather\\_newyork.csv</B>, use <B>.reindex<B> with the argument <B>axis=1</B> to select the 'EST', 'Max TemperatureF' and 'Min TemperatureF' columns.  (Question: what happens if you leave off the <b>axis=1</B>?  Why would pandas respond that way??)</B></B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18b9f6dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../weather_newyork.csv')\n",
    "\n",
    "\n",
    "df = df.reindex(['EST', 'Max TemperatureF','Min TemperatureF'], axis=1)\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7a56a13",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15ce0b22",
   "metadata": {},
   "source": [
    "<B>8.18:  Use <B>.reindex()</B> to reorder the columns (again, with <B>axis=1</B> on the DataFrame to 'float', 'num', and 'terxt' (deliberately misspell 'text').  Note that the 'terxt' column springs into existence with empty values.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1281903b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "data = [\n",
    "    [1, 2.5, 'apple'],\n",
    "    [2, 3.0, 'banana'],\n",
    "    [3, 4.2, 'orange']\n",
    "]\n",
    "\n",
    "df = pd.DataFrame(data, columns=['num', 'float', 'text'],\n",
    "                        index=['c', 'b', 'a'])\n",
    "\n",
    "\n",
    "df = df.reindex(['float', 'num', 'terxt'], axis=1)\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3861883f",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bf5b36b",
   "metadata": {},
   "source": [
    "<B>8.19:  Compare <B>.set\\_index()</B> to <B>.reset\\_index()</B>. </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "beedb3fa",
   "metadata": {},
   "source": [
    "Use <B>.set\\_index()</B> to set the <B>id</B> column for the DataFrame as the index.  Print or display the DataFrame.  Next, use <B>.reset\\_index()</B> to reset the index to the default (integers), while moving the index to a new column.  Lastly, add the <B>drop=True</B> parameter argument to drop the index entirely, and note that the old index has been dropped.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5bb6d971",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../revenue_extended.csv'\n",
    "\n",
    "df = pd.read_csv(fname, skiprows=2,\n",
    "                        skipfooter=2,\n",
    "                        engine='python')\n",
    "\n",
    "\n",
    "df = df.set_index('id')\n",
    "\n",
    "print(df)\n",
    "print()\n",
    "\n",
    "df = df.reset_index(drop=True)\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3813d02",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8526d5c4",
   "metadata": {},
   "source": [
    "<B>8.20:  Using <B>.rename()</B>, rename column labels: 'EST' -> 'date', 'Max TemperatureF' -> 'max\\_temp' and 'Min TemperatureF' -> 'min\\_temp'.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b1d83228",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../weather_newyork.csv')\n",
    "\n",
    "dfs = df.reindex(['EST', 'Max TemperatureF', 'Min TemperatureF'], axis=1)\n",
    "\n",
    "\n",
    "dfs = dfs.rename(columns={'EST': 'date', 'Max TemperatureF': 'max_temp',\n",
    "                                         'Min TemperatureF': 'min_temp'})\n",
    "\n",
    "print(dfs)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47536ae6",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be672f15",
   "metadata": {},
   "source": [
    "DATAFRAME SLICING"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09d16d5d",
   "metadata": {},
   "source": [
    "<B>8.21:  Slice rows using .loc[] and a list of labels.  Select just 'hd34', 'hp21' and 'tc77'.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72829eb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../revenue_extended.csv'\n",
    "\n",
    "df = pd.read_csv(fname, skiprows=2,\n",
    "                        skipfooter=2,\n",
    "                        index_col='id',\n",
    "                        engine='python')\n",
    "\n",
    "\n",
    "print(df.loc[['hd34', 'hp21', 'tc77']])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ddffd8ea",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04b22971",
   "metadata": {},
   "source": [
    "<B>8.22:  Slice rows using .loc[] and a range of labels.  Select 'ts06' through 'df8'.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60bf282b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../revenue_extended.csv'\n",
    "\n",
    "df = pd.read_csv(fname, skiprows=2,\n",
    "                        skipfooter=2,\n",
    "                        index_col='id',\n",
    "                        engine='python')\n",
    "\n",
    "\n",
    "print(df.loc['ts06': 'df8'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85142d34",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1e80dc0",
   "metadata": {},
   "source": [
    "<B>8.23:  Use <B>.loc[]</B> to slice columns and rows.  Select all rows but just the 'company' and 'revenue' columns (use ':' for the row selector, i.e. the 1st argument to <B>.loc[]</B>, and a list of column labels for the columns).</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06fcbf80",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../revenue_extended.csv'\n",
    "\n",
    "df = pd.read_csv(fname, skiprows=2,\n",
    "                        skipfooter=2,\n",
    "                        index_col='id',\n",
    "                        engine='python')\n",
    "\n",
    "\n",
    "print(df.loc[:, ['company', 'revenue']])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84e43b98",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0254c8dd",
   "metadata": {},
   "source": [
    "<B>8.24:  Use <B>.iloc[]</B> indexing to select columns and rows.  Select a 2x2 grid of the DataFrame using indices.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0646a52a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../weather_newyork.csv'\n",
    "\n",
    "df = pd.read_csv(fname)\n",
    "\n",
    "\n",
    "print(df.iloc[0:2, 0:2])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75a6b632",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49d3840a",
   "metadata": {},
   "source": [
    "CONDITIONAL SLICING"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "795555d3",
   "metadata": {},
   "source": [
    "<B>8.25:  Slice based on single condition.  Using <B>.loc[]</B> with a condition, look for all 'high' temps above 100, then try above 90.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63866e74",
   "metadata": {},
   "source": [
    "Then, add 2nd 'bucket' to <B>.loc[]</B> to select the 'high' and 'precip' columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d9b4f209",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../weather_newyork_narrow.csv'\n",
    "\n",
    "df = pd.read_csv(fname)\n",
    "\n",
    "\n",
    "print(df.loc[  df.high > 90, ['high', 'precip']  ])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b6fbd99",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2fe4dc7",
   "metadata": {},
   "source": [
    "<B>8.26:  Slice based on dual condition.  Select for a 'high' of > 90 and a 'precip' of > 0.5.  Separate the tests with an <B>&</B> and put parentheses around each.  (This will fail, and uncover an anomaly that we'll identify next.)</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28be686a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../weather_newyork_narrow.csv'\n",
    "\n",
    "df = pd.read_csv(fname)\n",
    "\n",
    "hitemps = df.loc[ (df.high > 90) & (df.precip > 0.5) ]\n",
    "   # fails with this message:\n",
    "   # TypeError: '>' not supported between instances of 'str' and 'float'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbfaf6f5",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1655f067",
   "metadata": {},
   "source": [
    "<B>8.27:  Use <B>.unique()</B> to see all unique values for a Series.  Apply this method to the <B>.precip</B> column to see all values this column may contain.  See if you can identify the field that caused the numeric comparison to fail.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4c4cf09",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../weather_newyork_narrow.csv'\n",
    "\n",
    "df = pd.read_csv(fname)\n",
    "\n",
    "\n",
    "print(df.precip.unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab66b92d",
   "metadata": {},
   "source": [
    "Can you see the anomalous value that caused the '<' comparison to fail?  We'll fix this issue after we've learned a few more pandas features."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dbe90a0e",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6aa248d0",
   "metadata": {},
   "source": [
    "<B>8.28:  Use <B>.isin()</B> as a condition to <B>.loc[]</B>.  Select states 'NY' and 'NJ' from revenue.csv -- the argument to <B>.isin()</B> should be a list of the state values.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b9c9ab4d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../revenue.csv')\n",
    "\n",
    "\n",
    "print(df.loc[  df.state.isin(['NY', 'NJ'])  ])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a0bdf70",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d161a234",
   "metadata": {},
   "source": [
    "<B>8.29:  <U>Use a <B>str</B> method condition</U>.  Use the <B>str.isdigit()</B> method (accessed through the <B>.str</B> accessor) to filter rows with digits in the 'transactions' column.  Following this put a tilde in front of the test to show those rows with transactions that are not all digits.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14725e61",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../revenue_extended.csv', skiprows=2,\n",
    "                                            skipfooter=2,\n",
    "                                            engine='python')\n",
    "\n",
    "print(df.loc[  ~df.transactions.str.isdigit()  ])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12c16236",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9fe72fb",
   "metadata": {},
   "source": [
    "\"COPY ON WRITE\" / \"COPY OF A SLICE\" WARNING"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c70c65a",
   "metadata": {},
   "source": [
    "<B>8.30:  <U>Trigger a \"Copy of a Slice\" Warning</U>.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a81efc9",
   "metadata": {},
   "source": [
    "Here we are creating a slice by using a double subscript:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5eafbc92",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# set the \"Copy-on-Write\" functionality to avoid\n",
    "# \"copy of a slice\" warning and behavior\n",
    "# pd.options.mode.copy_on_write = True\n",
    "\n",
    "\n",
    "dfi = pd.DataFrame({'c1': [ 1,  2, 3,  4],\n",
    "                    'c2': [16, 17, 18, 19],\n",
    "                    'c3': [21, 22, 23, 24],\n",
    "                    'c4': [26, 27, 28, 29] },\n",
    "           index = ['r1', 'r2', 'r3', 'r4'])\n",
    "\n",
    "\n",
    "dfis = dfi[ dfi.c1 > 2 ]\n",
    "print(dfis)\n",
    "\n",
    "                  #     c1  c2  c3  c4\n",
    "                  # r4   3  18  23  28\n",
    "                  # r5   4  19  24  29\n",
    "\n",
    "\n",
    "dfis.c3 = dfis.c1 * dfis.c2\n",
    "\n",
    "\n",
    "# alternative: using .loc[] indexing\n",
    "# dfis.loc[ :, 'c3' ] = dfis.c1 * dfis.c2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a25eb31",
   "metadata": {},
   "source": [
    "This setting forces pandas to create a new copy of the DataFrame any time we attempt to write to a slice (which usually triggers the tricky and confusing \"copy of a slice\" warning)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5626b24f",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3eb9d9fb",
   "metadata": {},
   "source": [
    "VECTORIZED OPERATIONS"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3341fbcb",
   "metadata": {},
   "source": [
    "<B>8.31:  Use a subscript to add a new column 'tax', set to 0.04</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad1d6f7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "\n",
    "df = pd.read_csv('../revenue.csv')\n",
    "\n",
    "\n",
    "df['tax'] = 0.04\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e222f1a4",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53e72f40",
   "metadata": {},
   "source": [
    "<B>8.32:  <U>Vectorize column value</U>.  Add a new column 'tax amt', set to the 'revenue' value * 'tax' amount.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b904a3ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../revenue.csv')\n",
    "\n",
    "df['tax'] = 0.04\n",
    "\n",
    "df['tax amt'] = df.revenue * df.tax\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9aff6ea",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82163a6c",
   "metadata": {},
   "source": [
    "<B>8.33:  <U>Vectorizing with condition</U>.  Continuing the above solution, use <B>.loc[]</B> with a condition to set 'tax' to 0.0625 for all 'NJ' rows, then recalculate tax amount.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c363246",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../revenue.csv')\n",
    "\n",
    "df['tax'] = 0.04\n",
    "\n",
    "df.loc[ df.state == 'NJ', 'tax' ] = 0.06\n",
    "\n",
    "df['tax amt'] = df.revenue * df.tax\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ef0d4c9",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0cfc14cf",
   "metadata": {},
   "source": [
    "<B>8.34:  <U>.replace() method, or .loc[] assignment</U>.  Returning to the 'precip' column, replace the non-numeric value we identified previously with 0.  Use vectorized condition as in previous, or the Series <B>.replace()</b> method.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b41c63b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../weather_newyork_narrow.csv')\n",
    "\n",
    "\n",
    "df = df.replace('T', 0)\n",
    "\n",
    "# alt. solution (instead of above) using .loc[]\n",
    "# df.loc[ df.precip == 'T', 'precip' ] = 0\n",
    "\n",
    "print(df.precip.unique())\n",
    "\n",
    "# once the value has been replaced,\n",
    "# uncomment to try the comparison again\n",
    "# hitemps = df.loc[ (df.high > 90) & (df.precip > 0.5) ]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82356ef6",
   "metadata": {},
   "source": [
    "After replacing the value, run <B>.unique()</B> to see that the anomalous value is no longer there.\n",
    "\n",
    "Then you may uncomment the <B>.loc[]</B> to see that it <I>still</I> doesn't work.  Any idea why?  We'll come back to this."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ba6605a",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "018d46dc",
   "metadata": {},
   "source": [
    "<B>8.35:  <U>Vectorizing with function</U>.  Use <B>.apply()</B> with a condition to create column 'revf' converting 'revenue' to a string with formatting.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2d55cbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "def format(n):\n",
    "    return f'${n:,.2f}'   # makes 2.3 into '$2.30'\n",
    "\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../revenue.csv')\n",
    "\n",
    "df['revf'] = df.revenue.apply(format)\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2103fd10",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d8815d6",
   "metadata": {},
   "source": [
    "'SUMMARY' FUNCTION ON SERIES"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1452ab17",
   "metadata": {},
   "source": [
    "<B>8.36:  <U>Use the <B>.sum()</B> method</U> to sum up the 'revenue' column.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42a92a90",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../revenue.csv')\n",
    "\n",
    "\n",
    "print(df.revenue.sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e19757e",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4687acb2",
   "metadata": {},
   "source": [
    "<B>8.37:  <U>Use the <B>.mean()</B> method on a single column.  Calculate the average of the 'revenue' column.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d6184bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../revenue.csv')\n",
    "\n",
    "\n",
    "print(df.revenue.mean())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "484fdad2",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1441c8c0",
   "metadata": {},
   "source": [
    "<B>8.38:  <U>Use the <B>.mean()</B> method on numeric columns</U>.  Now use <B>.mean()</B> on the DataFrame itself to calculate the average of the 'revenue' and 'transactions' columns.  You must include the argument <B>numeric\\_only=True</B>, or the method will fail on the columns that contain strings.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "722f0973",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../revenue.csv')\n",
    "\n",
    "\n",
    "print(df.mean(numeric_only=True))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df9f7eea",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90c2fd5b",
   "metadata": {},
   "source": [
    "<B>8.39:  <U>Use summary function with condition</U>.  Show the average of the 'revenue' column, but only for those rows with 'NY' in the 'state' column.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "386b460f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../revenue.csv')\n",
    "\n",
    "\n",
    "print(  df.loc[  df.state == 'NY' ].revenue.mean())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8ad90b8",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fec884ea",
   "metadata": {},
   "source": [
    "WORKING WITH DTYPES"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7650391",
   "metadata": {},
   "source": [
    "<B>8.40:  <U>Set the dtype for a column</U>.  First, use <B>.dtypes</B> to show the column types.  You may use <B>.unique()</B> to determine the range of values for the 'transactions' column, which explains why tahat column is type <B>object</B>.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2432570",
   "metadata": {},
   "source": [
    "Next, replace the '-' values in the 'transactions' column with 0's, then again check the <B>.dtypes</B> (and use <B>.unique()</B> if you wish.\n",
    "\n",
    "Finally, use <B>.astype()</B> to set the column dtype to 'int', then check the <B>.dtypes</B> again.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb79b012",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../revenue_extended.csv', skiprows=2,\n",
    "                                            skipfooter=2,\n",
    "                                            engine='python')\n",
    "\n",
    "print(df.dtypes)\n",
    "\n",
    "\n",
    "print(df.transactions.unique())\n",
    "\n",
    "df.transactions = df.transactions.replace('-', 0)\n",
    "\n",
    "df.transactions = df.transactions.astype(int)\n",
    "\n",
    "print(df.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a80b160",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "098e2497",
   "metadata": {},
   "source": [
    "<B>8.41:  <U>use pd.to\\_numeric</U>.  Change the 'a' and 'b' columns to a numeric type, and show dtypes afterwards.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cec04b88",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "dol = {  'a': ['1', '2', '3'],\n",
    "         'b': ['2.9', '3.5', '4.9'],\n",
    "         'c': ['yourstr', 'mystr', 'theirstr']  }\n",
    "\n",
    "df = pd.DataFrame(dol, index=['w', 'x', 'y'])\n",
    "\n",
    "\n",
    "print(df.dtypes)\n",
    "\n",
    "\n",
    "df.a = pd.to_numeric(df.a)\n",
    "df.b = pd.to_numeric(df.b)\n",
    "\n",
    "print(df.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37f97d02",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb5a9edd",
   "metadata": {},
   "source": [
    "<B>8.42:  <U>Solve the earlier problem</U>.  Now that we can 'cast' or change dtypes, change the <B>.precip</B> column to numeric so we can complete the compound test.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b0826e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../weather_newyork_narrow.csv'\n",
    "\n",
    "df = pd.read_csv(fname)\n",
    "\n",
    "df.precip = df.precip.replace('T', 0)\n",
    "\n",
    "\n",
    "df.precip = df.precip.astype(float)\n",
    "\n",
    "hitemps = df.loc[ (df.high > 90) & (df.precip > 0.5) ]\n",
    "\n",
    "print(hitemps)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b9e44b7",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2679d37",
   "metadata": {},
   "source": [
    "SORTING"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "741c7719",
   "metadata": {},
   "source": [
    "<B>8.43:  Read this csv, set the 'date' to the index column, and sort by index, with 'ascending=False'.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f885414",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../weather_newyork_narrow.csv'\n",
    "\n",
    "df = pd.read_csv(fname, index_col='date')\n",
    "\n",
    "dfs = df.sort_index(ascending=False)\n",
    "\n",
    "dfs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68e9755c",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34311a7a",
   "metadata": {},
   "source": [
    "<B>8.44:  Use <B>.sort\\_values()</B> to sort the DataFrame by the 'high' value.  Use <B>ascending=False</B> to reverse the sort.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "703708c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../weather_newyork_narrow.csv'\n",
    "\n",
    "df = pd.read_csv(fname, index_col='date')\n",
    "\n",
    "dfs = df.sort_values('high', ascending=False)\n",
    "\n",
    "dfs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a9be5bf",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d36d891e",
   "metadata": {},
   "source": [
    "<B>8.45:  Use <B>axis=1</B> to <B>.sort\\_index()</B> to sort the DataFrame by its columns.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "acff0a2d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "fname = '../revenue.csv'\n",
    "\n",
    "df = pd.read_csv(fname)\n",
    "\n",
    "dfs = df.sort_index(axis=1)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b486f95",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab70c82b",
   "metadata": {},
   "source": [
    "CONCATENATING / APPENDING"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a02e6194",
   "metadata": {},
   "source": [
    "<B>8.46:  First view, then concatenate the two DataFrames vertically.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3548a279",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame({ 'a': [1, 2, 3],\n",
    "                    'b': [2.9, 3.5, 4.9],\n",
    "                    'c': ['yourstr', 'mystr', 'theirstr']  },\n",
    "                    index=['r1', 'r2', 'r3'])\n",
    "\n",
    "df2 = pd.DataFrame({ 'b': [4, 5, 6],\n",
    "                     'c': [7.8, 8.2, 9.1],\n",
    "                     'd': ['thingy', 'thatty', 'flunky']  },\n",
    "                     index=['r2', 'r3', 'r4'])\n",
    "\n",
    "print(df)\n",
    "print()\n",
    "print(df2)\n",
    "\n",
    "df3 = pd.concat([df, df2])\n",
    "\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2579eaef",
   "metadata": {},
   "source": [
    "Note that the indices are repeated - this can be changed using index manipulation (<B>.rename()</B>, <B>.reindex()</B>, <B>.set\\_index()</B>."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e2d3d8c",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa359760",
   "metadata": {},
   "source": [
    "<B>8.47:  First view, then concatenate the two DataFrames horizontally.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7475c31c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame({ 'a': [1, 2, 3],\n",
    "                    'b': [2.9, 3.5, 4.9],\n",
    "                    'c': ['yourstr', 'mystr', 'theirstr']  },\n",
    "                    index=['r1', 'r2', 'r3'])\n",
    "\n",
    "df2 = pd.DataFrame({ 'd': ['xylo', 'yellow', 'zello'],\n",
    "                     'e': [0, 0, 0],\n",
    "                     'f': [1.1, 1.1, 1.2]  },\n",
    "                     index=['r1', 'r2', 'r3'])\n",
    "\n",
    "print(df)\n",
    "print()\n",
    "print(df2)\n",
    "print()\n",
    "\n",
    "df3 = pd.concat([df, df2], axis=1)\n",
    "\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c40660e",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4fcb384",
   "metadata": {},
   "source": [
    "MERGE / JOIN"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3647c7f3",
   "metadata": {},
   "source": [
    "<B>8.48:  View the two below DataFrames.  Then use <B>pd.merge()</B> to join the <B>student\\_db\\_grades.csv</B> and <B>student\\_db\\_names.csv</B> files into a single DataFrame, joining on the id (use <B>on='id'</B>).  Compare \"how='outer'\" (19 rows, including missing data at the end) to \"how='inner'\" (only 15 rows, with ids common to both tables) to effect an outer join.   </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea390d94",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "sgrades = pd.read_csv('../student_db_grades.csv', sep=':')\n",
    "snames = pd.read_csv('../student_db_names.csv')\n",
    "\n",
    "print(sgrades.head(2))\n",
    "print('===')\n",
    "print(snames.head(2))\n",
    "print('===')\n",
    "sm = pd.merge(sgrades, snames, how='inner', on='id')\n",
    "\n",
    "sm"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b221c9e",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59853a4d",
   "metadata": {},
   "source": [
    "<B>8.49:  Reading the same files but setting the 'id' column to the indexes of each DataFrame, use <B>df.join()</B> (the same operation as <B>pd.merge()</B>) to perform the above joins.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1cde47d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "sgrades = pd.read_csv('../student_db_grades.csv', sep=':', index_col='id')\n",
    "snames = pd.read_csv('../student_db_names.csv', index_col='id')\n",
    "\n",
    "sj = sgrades.join(snames)\n",
    "\n",
    "sj"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4afa1c37",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0328165b",
   "metadata": {},
   "source": [
    "AGGREGATIONS WITH df.groupby() AND pd.pivot\\_table()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd5bfea3",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9367e30",
   "metadata": {},
   "source": [
    "Please run this cell to view the <B>sales-funnel</B> spreadsheet:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bfd1d0da",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_excel(\"../sales-funnel.xlsx\")\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "049db7ec",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf1cdde7",
   "metadata": {},
   "source": [
    "<B>8.50:  <U>Single-column aggregation (<B>.groupby()</B> and <B>.pivot\\_table()</B></U>.  Show sum of 'SaleAmount' grouped by 'SalesRep'.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6e0bea2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_excel(\"../sales-funnel.xlsx\")\n",
    "\n",
    "dfgb = df.groupby('SalesRep').SaleAmount.sum()\n",
    "\n",
    "dfgb\n",
    "\n",
    "\n",
    "dfpt = pd.pivot_table(df, index='SalesRep',\n",
    "                          values='SaleAmount',\n",
    "                          aggfunc='sum')\n",
    "\n",
    "dfpt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "897f2332",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2621744d",
   "metadata": {},
   "source": [
    "<B>8.51:  <U>Multi-column aggregation (<B>.groupby()</B> and <B>.pivot\\_table()</B>)</U>.  Show sums of 'SaleAmount' and 'Quantity' grouped by 'SalesRep' and by 'Product'.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd383ff3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_excel(\"../sales-funnel.xlsx\")\n",
    "\n",
    "dfgb = df.groupby(['SalesRep', 'Product'])[['SaleAmount', 'Quantity']].sum()\n",
    "\n",
    "dfgb\n",
    "\n",
    "\n",
    "dfpt = pd.pivot_table(df, index=['SalesRep', 'Product'],\n",
    "                          values=['SaleAmount', 'Quantity'], aggfunc='sum')\n",
    "\n",
    "dfpt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76ee10b1",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc5b164b",
   "metadata": {},
   "source": [
    "<B>8.52:  <U>Further agg by secondary value (<B>.groupby()</B> and <B>.pivot\\_table()</B>)</U>.  Perform the above pivot on just 'SalesRep', but use <B>columns=</B> with a list to show the results broken down by 'Product'.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f200504",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.read_excel(\"../sales-funnel.xlsx\")\n",
    "\n",
    "dfpt = pd.pivot_table(df, index='SalesRep',\n",
    "                          aggfunc='sum',\n",
    "                          values=['SaleAmount', 'Quantity'],\n",
    "                          columns='Product')\n",
    "\n",
    "dfpt\n",
    "\n",
    "dfgb = df.groupby(['SalesRep', 'Product'])[['SaleAmount', 'Quantity']].sum()\n",
    "\n",
    "dfgb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55f17862",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a404e43d",
   "metadata": {},
   "source": [
    "<B>8.53:  <U>Use <B>.stack()</B> and <B>.unstack()</B> (with <B>.groupby()</B>)</U>.  Looking at the above example, note that <B>.groupby()</B> produces results in \"long\" format, while <B>.pivot\\_table()</B> renders them in \"wide\" format.  Use <B>.unstack()</B> and <B>.stack()</B> to move between the two.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "09667fce",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.read_excel(\"../sales-funnel.xlsx\")\n",
    "\n",
    "dfgb = df.groupby(['SalesRep', 'Product'])[['SaleAmount', 'Quantity']].sum()\n",
    "\n",
    "dfgb\n",
    "\n",
    "dfgbus = dfgb.unstack()\n",
    "\n",
    "dfgbus\n",
    "\n",
    "dfgbss = dfgbus.stack()\n",
    "\n",
    "dfgbss"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5006a56",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "803d666f",
   "metadata": {},
   "source": [
    "MULTIINDEX"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "113e7f7a",
   "metadata": {},
   "source": [
    "Please begin by noting the multi-level index that is produced by the below multi-column aggregation.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27bea9c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.read_excel(\"../sales-funnel.xlsx\")\n",
    "\n",
    "table = pd.pivot_table(df, index=['Manager', 'SalesRep'],\n",
    "                           aggfunc='sum')\n",
    "\n",
    "table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6b0c40f",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed3f58b1",
   "metadata": {},
   "source": [
    "<B>8.54:  <U><B>MultiIndex</B>:  slicing at top level</U>.  Slice the multi-index to show just 'Account' and 'Quantity', and just for Manager 'Debra Henley'.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a1c621d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.read_excel(\"../sales-funnel.xlsx\")\n",
    "\n",
    "table = pd.pivot_table(df, index=['Manager', 'SalesRep'],\n",
    "                           aggfunc='sum')\n",
    "\n",
    "table.loc['Debra Henley', ['Account', 'Quantity']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53388d66",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9cc88832",
   "metadata": {},
   "source": [
    "<B>8.55:  <U><B>MultiIndex</B>:  slicing at 2nd level</U>.  Use a 2-item tuple (first-level label, second-level label) to slice for just 'Debra Henley' at the first level and 'Craig Booker' at the 2nd level.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f911dacb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.read_excel(\"../sales-funnel.xlsx\")\n",
    "\n",
    "table = pd.pivot_table(df, index=['Manager', 'SalesRep'],\n",
    "                           aggfunc='sum')\n",
    "\n",
    "tl = table.loc[('Debra Henley', 'Craig Booker')]\n",
    "\n",
    "tl"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fcd72d4",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6dde6087",
   "metadata": {},
   "source": [
    "<B>8.56:  <U><B>MultiIndex</B>:  \"cross section\" slicing</U>.  Use the <B>.xs()</B> method with <B>level=1</B> to show rows just for 'Craig Booker'.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4e0013e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.read_excel(\"../sales-funnel.xlsx\")\n",
    "\n",
    "table = pd.pivot_table(df, index=['Manager', 'SalesRep'],\n",
    "                           aggfunc='sum')\n",
    "\n",
    "txs = table.xs('Craig Booker', level=1)\n",
    "\n",
    "txs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b531487d",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8443a36",
   "metadata": {},
   "source": [
    "<B>8.57:  <U><B>MultiIndex</B>:  use <B>.query('Manager == \"Debra Henley\"')</B> to select rows</U>.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8624cbea",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.read_excel(\"../sales-funnel.xlsx\")\n",
    "\n",
    "table = pd.pivot_table(df, index=['Manager', 'SalesRep'],\n",
    "                           aggfunc='sum')\n",
    "\n",
    "table.query(\"Manager == 'Debra Henley'\")\n",
    "\n",
    "# table.query(\"Manager == 'Debra Henley' and SalesRep == 'Craig Booker'\")\n",
    "\n",
    "#table.query(\"SalesRep in ['Craig Booker', 'Daniel Hilton']\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82e17ca8",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e4bffaa",
   "metadata": {},
   "source": [
    "<B>8.58:  <U><B>MultiIndex</B>:  First review the below DataFrame; then use <B>.query('Status in [\"pending\", \"won\"]')</B> to select rows</U>.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b6e376d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_excel(\"../sales-funnel.xlsx\")\n",
    "\n",
    "dfq = df.query('Status in [\"pending\", \"won\"]')\n",
    "\n",
    "dfq"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1bcc55a2",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de571e77",
   "metadata": {},
   "source": [
    "<B>8.59:  <U><B>MultiIndex</B>:  use <B>.loc[]</B> with a 1- or 2-item tuple to select rows</U>.  Keep in mind that a 1-item tuple must have a comma after the item, for example <B>('Manager',)</B></B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03cb6369",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.read_excel(\"../sales-funnel.xlsx\")\n",
    "\n",
    "table = pd.pivot_table(df, index=['Manager', 'SalesRep'],\n",
    "                           aggfunc='sum')\n",
    "\n",
    "table.loc[('Fred Anderson', 'Wendy Yule')]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a84d761",
   "metadata": {},
   "source": [
    " "
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 5
}
