{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ae38b4ec",
   "metadata": {},
   "source": [
    "# In-Class Exercises, Session 9"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4679bced",
   "metadata": {},
   "source": [
    "<U>Notes if you are using Jupyter Notebook</U>:  to call <B>exit()</B> from a notebook, please use <B>sys.exit()</B> (requires <B>import sys</B>).\n",
    "\n",
    "If a strange error occurs, it may be because Jupyter retains variables from all executed cells.  To reset the notebook, click 'Restart Kernel' (the circular arrow) -- this will not undo any changes made.\n",
    "\n",
    "<B>Also note</B> that 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": "f762db45",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40ce1675",
   "metadata": {},
   "source": [
    "<B>9.1:  Include some useful settings.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cfe708eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import warnings\n",
    "\n",
    "# enable multiple outputs from jupyter cells\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\"\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,\n",
    "                                  module='openpyxl')\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"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cdadc7a2",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e8b43e0",
   "metadata": {},
   "source": [
    "SERIES AND DATAFRAME DTYPES"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e7a3133",
   "metadata": {},
   "source": [
    "<B>9.2:  <U>Review:  check the <B>.dtypes</B> of a DataFrame and <B>.dtype</B> of a Series</U>.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa77f858",
   "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"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2290eac4",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a57c67fa",
   "metadata": {},
   "source": [
    "<B>9.3:  <U>Use Series <B>.astype()</B>.  Change the 'a' column to np.float64, and 'b' column to str; check dtypes before and after the operation.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9983619e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\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"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b55ebaa5",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b2bd358",
   "metadata": {},
   "source": [
    "<B>9.4:  <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": "a626bdc9",
   "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"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb6a5abb",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9adb22d",
   "metadata": {},
   "source": [
    "AGGREGATIONS WITH df.groupby() AND pd.pivot\\_table()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fad0694b",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da991c38",
   "metadata": {},
   "source": [
    "Please run this cell to view the <B>sales-funnel</B> spreadsheet:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "74842d89",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_excel(\"../sales-funnel.xlsx\")\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94c6c5bd",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63c4a08d",
   "metadata": {},
   "source": [
    "<B>9.5:  <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": "96def26f",
   "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": "3ddccc86",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "235b26dd",
   "metadata": {},
   "source": [
    "<B>9.6:  <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": "7f5bf4ae",
   "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": "b5ba6c98",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5da1775a",
   "metadata": {},
   "source": [
    "<B>9.7:  <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": "79e197f7",
   "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": "cac29a38",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "813092c8",
   "metadata": {},
   "source": [
    "<B>9.8:  <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": "5a7d7f4c",
   "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",
    "dfgbus.stack()\n",
    "\n",
    "dfgbus"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29075380",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "280a1deb",
   "metadata": {},
   "source": [
    "<B>9.9:  <U>Custom aggregation function (<B>.groupby()</B> and <B>.pivot\\_table()</B>)</U>.  Group rows by 'SalesRep' and apply a function to each group that sums up the 'SaleAmount' column.  (In other words, this replicates what <B>.groupby().sum()</B> does).  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0f9b12b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def price_sum(this_df):\n",
    "    return this_df.SaleAmount.sum()\n",
    "\n",
    "df = pd.read_excel(\"../sales-funnel.xlsx\")\n",
    "\n",
    "dfgb = df.groupby('SalesRep').apply(price_sum)\n",
    "\n",
    "dfgb\n",
    "\n",
    "\n",
    "\n",
    "def price_sum_series(series):\n",
    "    return series.sum()\n",
    "\n",
    "dfpt = pd.pivot_table(df, index='SalesRep',\n",
    "                          values='SaleAmount',\n",
    "                          aggfunc=price_sum_series)\n",
    "\n",
    "dfpt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "499bd7c2",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7ee770a",
   "metadata": {},
   "source": [
    "<B>9.10:  <U>Custom 'summary' function that refers to other columns (<B>.groupby()</B> only)</U>.  Building on the previous solution, customize the function to sum SaleAmount but reduce 'CPU' values by 20% in the sum (because of the overhead cost of handling hardware).  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d75ddc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def price_sum(this_df):\n",
    "    this_df.loc[ this_df.Product.isin(['CPU', 'Monitor']),\n",
    "                                      'SaleAmount' ] = this_df.SaleAmount * .8\n",
    "    return this_df.SaleAmount.sum()\n",
    "\n",
    "df = pd.read_excel(\"../sales-funnel.xlsx\")\n",
    "\n",
    "dfgb = df.groupby('SalesRep').apply(price_sum)\n",
    "\n",
    "dfgb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c5fb046",
   "metadata": {},
   "source": [
    "Since <B>.pivot\\_table()</B> only passes the <B>Series</B> of the column being aggregated, a custom aggregation cannot take into account the values in other columns.  (In multi-column aggregations using <B>.pivot\\_table()</B>, the <B>aggfunc=</B> function is applied separately to each column)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "018a350a",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55ee6551",
   "metadata": {},
   "source": [
    "<B>9.11:  <U>Custom 'grouping' function (<B>.groupby()</B> only)</U>.  Pass a function to <B>groupby()</B> that identifies 2 groups using the 'Product' column:  if the value is 'CPU' or 'Monitor', return 'hardware'; otherwise, return 'software'.  Count the number of rows in each group with <B>.count()</b>.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7702111",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def grouper(row_idx):\n",
    "    row = df.loc[row_idx]\n",
    "    if row['Product'] in ['Software', 'Maintenance']:\n",
    "        return 'software'\n",
    "    else:\n",
    "        return 'hardware'\n",
    "\n",
    "df = pd.read_excel(\"../sales-funnel.xlsx\")\n",
    "\n",
    "df.groupby(grouper).Account.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1cd39beb",
   "metadata": {},
   "source": [
    "The function receives a row index which you can use with <B>.loc[]</B> to find the row being grouped.  Have the function return 'Software' if the 'Product' value is 'Software' or 'Maintenance', and 'Hardware' if the Product is 'CPU'.\n",
    "\n",
    "<B>.pivot\\_table()</B> does not offer a way to customize groupings, however you can precompute a grouping key for each row in the DataFrame and then pivot on that."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f8a90c6",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd698677",
   "metadata": {},
   "source": [
    "WORKING WITH MISSING DATA"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dacf367e",
   "metadata": {},
   "source": [
    "<B>9.12:  Compare <B>None</B> to <B>np.nan</B>.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f873d89e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy\n",
    "import pandas\n",
    "\n",
    "pynone     = None            # means \"empty\"\n",
    "not_a_num  = np.nan          # means \"indeterminate number\"\n",
    "\n",
    "print(f'None:   {type(pynone)}: {bool(pynone)}')                # <class 'NoneType'>:  False\n",
    "print(f'np.nan: {type(not_a_num)}:    {bool(not_a_num)}')       # <class 'float'>: True"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b328622",
   "metadata": {},
   "source": [
    "<B>np.nan</B> is a float, principally because it needs to work in math calculations (for example, summing up a column that might have empty values).\n",
    "\n",
    "<B>np.nan</B> is <B>True</B> in a boolean context because it's a non-zero float -- so following Python's rules of \"truthiness\" is must be <B>True</B>.  To identify it, we'll use pandas' specific methods <B>.isna()</B>, etc."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "974d62bb",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5950c21b",
   "metadata": {},
   "source": [
    "<B>9.13:  First, review the merged DataFrame and note the missing values.  Then check to see if any empty values can be found in the 'course' column.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a99e362f",
   "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",
    "sj = pd.merge(sgrades, snames, on='id', how='outer')\n",
    "\n",
    "sj\n",
    "print()\n",
    "\n",
    "# see boolean column of whether missing or not\n",
    "print('sj.course.isna(): ')\n",
    "sj.course.isna()\n",
    "print()\n",
    "\n",
    "# see whether there are any missing values\n",
    "print('sj.course.isna().any(): ')\n",
    "sj.course.isna().any()\n",
    "print()\n",
    "\n",
    "# count the number of missing values\n",
    "print('sj.course.insa().count(): ')\n",
    "sj.course.isna().count()\n",
    "print()\n",
    "\n",
    "# select rows with missing data\n",
    "print('sj[ sj.course.isna() ]')\n",
    "sj[ sj.course.isna() ]\n",
    "print()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2a16ecd",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25ccbe76",
   "metadata": {},
   "source": [
    "<B>9.14:  <U>Summarize the number of missing values in each column.</U>  Use <B>.isna().sum()</B> to count missing values and <B>df.info()</B> to inspect column-wise nulls.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90ec63b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "sj.isna().sum()\n",
    "sj.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28697bc8",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d888f0f6",
   "metadata": {},
   "source": [
    "<B>9.15:  <U>Drop rows or columns that contain missing values.</U>  Use <B>.dropna()</B> with <B>axis</B> and <B>thresh</B> parameters to control behavior.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "31c41f95",
   "metadata": {},
   "outputs": [],
   "source": [
    "print('dataframe \"sj\":')\n",
    "sj\n",
    "print()\n",
    "\n",
    "print('sj.dropna():')\n",
    "sj.dropna()            # drop rows with any NaN\n",
    "print()\n",
    "\n",
    "print('sj.dropna(axis=1)')\n",
    "sj.dropna(axis=1)      # drop columns with any NaN\n",
    "print()\n",
    "\n",
    "print('sj.dropna(thresh=5):')\n",
    "sj.dropna(thresh=5)    # keep rows with at least 2 non-NaN values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b97596cc",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82fda05d",
   "metadata": {},
   "source": [
    "<B>9.16:  <U>Fill missing values using a constant, a computed value, or a method.</U>  Use <B>.fillna()</B> with scalar values, aggregates, or directional methods like 'ffill'.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4554b8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "print('df:')\n",
    "df\n",
    "print()\n",
    "\n",
    "print('df.fillna(0):')\n",
    "df.fillna(0)\n",
    "print()\n",
    "\n",
    "print(\"df.ffill()\")\n",
    "df.ffill()\n",
    "print()\n",
    "\n",
    "print(\"df['A'].fillna(  df['A'].mean()  )\")\n",
    "df['A'].fillna(  df['A'].mean()  )\n",
    "print()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0958564c",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e42aa7b",
   "metadata": {},
   "source": [
    "<B>9.17:  <U>Replace missing values using a general-purpose replacement method.</U>  Use <B>.replace()</B> to substitute <B>np.nan</B> or <B>None</B> with other values.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fac44707",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'A': [1, 2, np.nan, 4],\n",
    "    'B': [5, np.nan, np.nan, 8],\n",
    "    'C': ['x', 'y', 'z', None]\n",
    "})\n",
    "print('df:')\n",
    "df\n",
    "\n",
    "print('df.replace(np.nan, 999):')\n",
    "df.replace(np.nan, 999)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec9eb88e",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2dca198a",
   "metadata": {},
   "source": [
    "<B>9.18:  <U>Interpolate missing values in a numeric column.</U>  Use <B>.interpolate()</B> to linearly estimate missing values.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a0fff9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'A': [1, 2, np.nan, 4],\n",
    "    'B': [5, np.nan, np.nan, 8],\n",
    "    'C': ['x', 'y', 'z', None]\n",
    "})\n",
    "\n",
    "df['A'].interpolate()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f286084",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df53dd04",
   "metadata": {},
   "source": [
    "<B>9.19:  <U>Filter rows based on custom logic for missing data.</U>  Use Boolean masks with <B>.isna()</B> and <B>.notna()</B> to target specific missing value conditions.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9915a4b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "print('df:')\n",
    "df\n",
    "print()\n",
    "\n",
    "print(\"df[  df['A'].notna() & df['B'].isna()  ]\")\n",
    "df[  df['A'].notna() & df['B'].isna()  ]\n",
    "print()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91dd087f",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1daba454",
   "metadata": {},
   "source": [
    "<B>9.20:  <U>Fill missing values within groups using the group mean.</U>  Use <B>.groupby()</B> combined with <B>.transform()</B> to fill missing data contextually.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ff12eb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'A': [1, 2, np.nan, 4],\n",
    "    'B': [5, np.nan, np.nan, 8],\n",
    "    'C': ['x', 'y', 'z', None]\n",
    "})\n",
    "\n",
    "df_grouped = df.copy()\n",
    "df_grouped['Group'] = ['G1', 'G1', 'G2', 'G2']\n",
    "df_grouped['A'] = df_grouped.groupby('Group')['A'].transform(lambda x: x.fillna(x.mean()))\n",
    "df_grouped"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a48824e",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4835c2e",
   "metadata": {},
   "source": [
    "MULTIINDEX"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f150a999",
   "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": "2c014116",
   "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",
    "                           values=['SaleAmount', 'Quantity'])\n",
    "\n",
    "table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e22ca955",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f70e68f8",
   "metadata": {},
   "source": [
    "<B>9.21:  <U><B>MultiIndex</B>:  slicing at top level</U>.  Slice the multi-index for Manager 'Debra H.'.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "99d4cebf",
   "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",
    "                           values=['SaleAmount', 'Quantity'])\n",
    "\n",
    "table.loc['Debra H.']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be6b75d9",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58dfd43b",
   "metadata": {},
   "source": [
    "<B>9.22:  <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 H.' at the first level and 'Craig B.' at the 2nd level.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45e3e04a",
   "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",
    "                           values=['SaleAmount', 'Quantity'])\n",
    "\n",
    "tl = table.loc[('Debra H.', 'Craig B.')]\n",
    "\n",
    "tl"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2f78306",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e7af91c",
   "metadata": {},
   "source": [
    "<B>9.23:  <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 B.'.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3dade1d8",
   "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",
    "                           values=['SaleAmount', 'Quantity'])\n",
    "\n",
    "txs = table.xs('Craig B.', level=1)\n",
    "\n",
    "txs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "317ccc52",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f080588",
   "metadata": {},
   "source": [
    "<B>9.24:  <U><B>MultiIndex</B>:  use <B>.query('Manager == \"Debra H.\"')</B> to select rows</U>.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1698a4ac",
   "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",
    "                           values=['SaleAmount', 'Quantity'])\n",
    "\n",
    "table.query(\"Manager == 'Debra H.'\")\n",
    "\n",
    "# table.query(\"Manager == 'Debra H.' and SalesRep == 'Craig B.'\")\n",
    "\n",
    "# table.query(\"SalesRep in ['Craig B.', 'Daniel H.']\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "980894a5",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7dc73abd",
   "metadata": {},
   "source": [
    "<B>9.25:  <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": "d0f204f8",
   "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": "79f5a5bb",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86706dc5",
   "metadata": {},
   "source": [
    "TIMESERIES"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c587bab",
   "metadata": {},
   "source": [
    "<B>9.26:  <U>Create a <B>pd.Timestamp</B> object by passing any string that represents a date</U>.  Print the object's type and value.  Next, try adding a time value to the date and note the result.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7bfef043",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "ts = pd.Timestamp('January 3rd, 2020')\n",
    "\n",
    "print(type(ts))\n",
    "print(ts)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16dce2b4",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0ec0812",
   "metadata": {},
   "source": [
    "<B>9.27:  <U>Create a <B>pd.Timedelta</B> interval object</U> for one day (use the <B>days=1</B> argument).  Add it to the <B>Timestamp</B> object to produce a new Timestamp object with the advanced date.  Print the object to see the new date.  Next, try adding <B>minutes=</B> parameter to see the date and time advance.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "79b734d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "ts = pd.Timestamp('3/3/2020')\n",
    "\n",
    "td = pd.Timedelta(days=1)\n",
    "\n",
    "ts2 = ts + td\n",
    "\n",
    "print(ts2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "586530b3",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "779b8672",
   "metadata": {},
   "source": [
    "<B>9.28:  <U>Create a <B>pd.Timedelta</B> interval object</U> by subtracting one date from another.  Print the object's value and its type.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13ab4a9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "ts1 = pd.Timestamp('3/3/2020')\n",
    "ts2 = pd.Timestamp('3/5/2020')\n",
    "\n",
    "td = ts1 - ts2\n",
    "\n",
    "print(type(td))\n",
    "\n",
    "print(td)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40d11b0d",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34aac8b1",
   "metadata": {},
   "source": [
    "<B>9.29:  <U>Generate a date range</U>.  Use the <B>pd.date\\_range()</B> function with a start date, end date and <B>freq=</B> parameter indicating the frequency of dates ('D' for day, 'M' for end of month, 'MS' for start of month, 'B' for business day, etc. -- please see \"NB\\_date\\_range\\_and\\_period\\_frequency\\_key.ipynb\" for more).  Print the object to see its contents and type.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d2ca81d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "dr = pd.date_range('2020-02-03', '2021-02-03', freq='MS')\n",
    "\n",
    "print(dr)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51b1ae45",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7a2f5db",
   "metadata": {},
   "source": [
    "<B>9.30:  <U>Converting a DataFrame column/index to <B>datetime</B></U>.  View the below DataFrame and note the date column EST.  Use <B>pd.to\\_datetime()</B> or <B>Series.astype()</B> to convert the 'EST' series to Timestamps, then use <B>.set\\_index()</B> with the column name and <B>drop=True</B> to set the column as the index.  Check the dtype of the index.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da20992d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../weather_newyork.csv')\n",
    "\n",
    "df.EST = df.EST.astype('datetime64[ns]')\n",
    "\n",
    "df = df.set_index('EST', drop=True)\n",
    "\n",
    "print('df: ')\n",
    "df\n",
    "\n",
    "print('df.index.dtype')\n",
    "print(df.index.dtype)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24e8fd55",
   "metadata": {},
   "source": [
    "If your dtype shows as '&lt;M8[ns]' it means that the date is 'timezone-aware'; if it shows as 'datetime64[ns]' it signifies 'timezone-naive' (meaning it does not represent the time in a timezone).  Since timezone-aware timestamps know their timezone, they can be compared with other timezone-aware dates to do calculations and comparisons between timestamps in different timezones."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1cf00dc",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af7e2b04",
   "metadata": {},
   "source": [
    "<B>9.31:  <U>Select rows based on partial date</U>.  Given the below DataFrame, select rows using <B>.loc[]</B> for the following:  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7f22d84",
   "metadata": {},
   "source": [
    "<UL>\n",
    "  <LI>single date (put the date in any format)</LI>\n",
    "  <LI>single year-month (for example, '2016-03')</LI>\n",
    "  <LI>single year</LI>\n",
    "  <LI>date range as a slice (for example, '2016-03-03': '2016-05-02')\n",
    "</UL>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ce2d2eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../weather_newyork_2016_2018.csv')\n",
    "\n",
    "df.EST = pd.to_datetime(df.EST)\n",
    "\n",
    "df = df.set_index('EST', drop=True)\n",
    "\n",
    "\n",
    "df.loc['2017-03-01']\n",
    "print()\n",
    "\n",
    "df.loc['2016-05']\n",
    "print()\n",
    "\n",
    "df.loc['2016']\n",
    "print()\n",
    "\n",
    "df.loc['2017-12-28': '2018-01-03']\n",
    "\n",
    "# some dates for slicing\n",
    "# '2017-03-01\n",
    "# '2016-05\n",
    "# '2016\n",
    "# '2017-12-28': '2018-01-03"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "175be4aa",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1da89a24",
   "metadata": {},
   "source": [
    "<B>9.32:  <U>Generate daily stock prices</U>.  The following code generates synthetic daily stock prices for 120 days (add an integer argument to <B>.head()</B> to see more rows).  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7d8d617",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "np.random.seed(0)\n",
    "\n",
    "dates = pd.date_range(start='2023-01-01', periods=120, freq='D')\n",
    "\n",
    "prices = 100 + np.cumsum(np.random.randn(120))  # synthetic price series\n",
    "\n",
    "df = pd.DataFrame({'date': dates, 'close': prices})\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e748e292",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6e1bdb4",
   "metadata": {},
   "source": [
    "<B>9.33:  <U>Move the 'date' column to become a DateTimeIndex</U>.  Convert the 'date' column to datetime type and then set it as the DataFrame index.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7661c9d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['date'] = pd.to_datetime(df['date'])  # ensure datetime type\n",
    "df.set_index('date', inplace=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57474e3e",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d032389",
   "metadata": {},
   "source": [
    "<B>9.34:  <U>Resample to show monthly returns</U>.  Use <B>.resample('M')</B> with <B>.mean()</B> to get the average price for each month, then add <B>.pct\\_change()</B> to calculate percent change.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6833d7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_returns = df['close'].resample('M').mean().pct_change()\n",
    "\n",
    "monthly_returns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21f6c16b",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fddec3b2",
   "metadata": {},
   "source": [
    "<B>9.35:  <U>Group the data by month or quarter</U>.  Add a column representing the period, then group by that column and compute the mean.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b45ef33f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['month'] = df.index.to_period('M')\n",
    "\n",
    "# monthly_grouped = df.groupby('month')['close'].mean()\n",
    "\n",
    "# monthly_grouped\n",
    "\n",
    "\n",
    "# df['quarter'] = df.index.to_period('Q')\n",
    "# quarterly_grouped = df.groupby('quarter')['close'].mean()\n",
    "\n",
    "# quarterly_grouped"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b78e806",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4322ee88",
   "metadata": {},
   "source": [
    "<B>9.36:  <U>Compute a 30-day moving average</U>.  Use <B>.rolling(window=30).mean()</B> to calculate the 30-day moving average of closing prices.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e4b09a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['30d_ma'] = df['close'].rolling(window=30).mean()\n",
    "\n",
    "df[['close', '30d_ma']].tail(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a77ddc4a",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a218c47",
   "metadata": {},
   "source": [
    "<B>9.37:  <U>Compare, align or join with another index using date-based alignment</U>.  Create a second time series and join it to the existing DataFrame on the date index.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7109d761",
   "metadata": {},
   "outputs": [],
   "source": [
    "benchmark = pd.DataFrame({\n",
    "    'date': pd.date_range(start='2023-01-01', periods=120, freq='D'),\n",
    "    'benchmark_close': 102 + np.cumsum(np.random.randn(120))\n",
    "})\n",
    "benchmark.set_index('date', inplace=True)\n",
    "\n",
    "joined = df.join(benchmark, how='inner')\n",
    "\n",
    "joined.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d321c821",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e50c2ed",
   "metadata": {},
   "source": [
    "<B>9.38:  <U>Use .shift()</U>.  Shift the close price by one day to compare with the current price and calculate daily return.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "acf2458f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['prev_close'] = df['close'].shift(1)\n",
    "df['daily_return'] = (df['close'] - df['prev_close']) / df['prev_close']\n",
    "\n",
    "df[['close', 'prev_close', 'daily_return']].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dddd5921",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ebda3d8",
   "metadata": {},
   "source": [
    "REVIEW OF LAMBDA FUNCTIONS"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7883188d",
   "metadata": {},
   "source": [
    "<B>9.39:  <U>Convert a function to lambda</B>.  The following function takes one argument and returns the value doubled.  Convert to lambda and use in the <B>map()</B> function.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5843c93e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# def doubleit(arg):\n",
    "#     return arg * 2\n",
    "\n",
    "seq = [1, 2, 3, 4]\n",
    "\n",
    "seq2 = map(lambda arg:  arg * 2, seq)\n",
    "\n",
    "print(list(seq2))           # [2, 4, 6, 8]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccd8bdc1",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bad2990",
   "metadata": {},
   "source": [
    "<B>9.40:  <U>Convert another function to lambda</B>.  The following function takes a dict key and returns the value.  Convert to a lambda for use in the <B>sorted()</B> function.   </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ac6cd64",
   "metadata": {},
   "outputs": [],
   "source": [
    "d = {'a': 10, 'b': 2, 'c': 5}\n",
    "\n",
    "# def byvalue(arg):\n",
    "#     return d[arg]\n",
    "\n",
    "skeys = sorted(d, key=lambda dict_key:  d[dict_key])\n",
    "\n",
    "print(skeys)        # ['b', 'c', 'a'] (in order of value, low-to-high)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7123d342",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae921e6c",
   "metadata": {},
   "source": [
    "DATAFRAME .apply()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45904e69",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5be8f85",
   "metadata": {},
   "source": [
    "<B>9.41:  <U>Apply a function to each item in a Series column</U>.  Create new column 'd' that is the value of 'b' * 100, by using <B>.apply()</B> with the <B>centify</B> function (defined below).  'd' column values should be 290.0, 350.0, 490.0.  When correct, replace the function with a lambda.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39dc8f08",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# def centify(arg):\n",
    "#     return arg * 100\n",
    "\n",
    "dol = {  'a': [1, 2, 3],\n",
    "         'b': [2.9, 3.5, 4.9],\n",
    "         'c': [0.1, 0.2, 0.3]  }\n",
    "\n",
    "df = pd.DataFrame(dol, index=['w', 'x', 'y'])\n",
    "\n",
    "df['d'] = df.b.apply(lambda arg:  arg * 100)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c557021a",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4d5964a",
   "metadata": {},
   "source": [
    "<B>9.42:  <U>Apply a function to each item in a Series row</U>.  Create new row 'xc' that is the value of 'x' * 100, by using <B>.apply()</B> with the <B>centify</B> function (defined below).  'xc' row values should be <B>20.0, 35000.0, 200</B>.  When correct, replace the function with a lambda.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "009c47cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# def centify(arg):\n",
    "#     return arg * 100\n",
    "\n",
    "dol = {  'a': [1, 2, 3],\n",
    "         'b': [290.0, 350.0, 490.0],\n",
    "         'c': [0.1, 0.2, 0.3]  }\n",
    "\n",
    "df = pd.DataFrame(dol, index=['w', 'x', 'y'])\n",
    "\n",
    "df.loc['xc'] = df.loc['x'].apply(lambda arg:  arg * 100)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e379efee",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "838f26b3",
   "metadata": {},
   "source": [
    "<B>9.43:  <U>Apply a function to each Series column in a DataFrame</U>.  Call <B>.apply()</B> on the DataFrame, which will pass each column <B>Series</B> to the function in turn.  Inside the function, sum up the column values and create a new 'total' row with the sum of values.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20568ec3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def sum_col(series_column):\n",
    "    series_column.loc['total'] = series_column.sum()\n",
    "    return series_column\n",
    "\n",
    "dol = {  'a': [1, 2, 3],\n",
    "         'b': [290.0, 350.0, 490.0],\n",
    "         'c': [0.1, 0.2, 0.3]  }\n",
    "\n",
    "df = pd.DataFrame(dol, index=['w', 'x', 'y'])\n",
    "\n",
    "df = df.apply(sum_col)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ededbb5",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92704a77",
   "metadata": {},
   "source": [
    "<B>9.44:  <U>Apply a function to each Series row in a DataFrame</U>.  Call <B>.apply()</B> on the DataFrame with <B>axis=1</B>, which will pass each row <B>Series</B> to the function in turn.  Sum up the row values and create a new 'd' row with the sum of values.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c192e38",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def sum_row(series_row):\n",
    "    series_row['d'] = series_row.sum()\n",
    "    return series_row\n",
    "\n",
    "dol = {  'a': [1, 2, 3],\n",
    "         'b': [290.0, 350.0, 490.0],\n",
    "         'c': [0.1, 0.2, 0.3]  }\n",
    "\n",
    "df = pd.DataFrame(dol, index=['w', 'x', 'y'])\n",
    "\n",
    "df = df.apply(sum_row, axis=1)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e945e4a4",
   "metadata": {},
   "source": [
    " "
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 5
}
