section04_aggregations.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42bfbe20",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "71a0a3e6",
   "metadata": {},
   "source": [
    "# Assignment 1: Groupby\n",
    "\n",
    "Can you return a table containing the top 10 stores by total transactions in the data?\n",
    "\n",
    "Make sure they’re sorted from highest to lowest.\n",
    "\n",
    "Thanks!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c52e4a31",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in transactions data -- parse dates specified here for help with later problem\n",
    "\n",
    "transactions = pd.read_csv(\"../retail/transactions.csv\", parse_dates=[\"date\"])\n",
    "\n",
    "transactions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58e14f99",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "9fdc3658",
   "metadata": {},
   "source": [
    "# Assignment 2: Groupby Multiple Columns\n",
    "\n",
    "Can you get me the total transactions by store and month? \n",
    "\n",
    "Sort the table from first month to last, then by highest transactions to lowest within each month. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff0828da",
   "metadata": {},
   "outputs": [],
   "source": [
    "# helper code to extract month date part from date column\n",
    "\n",
    "transactions[\"month\"] = transactions[\"date\"].dt.month\n",
    "\n",
    "transactions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6ec2358",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "f92dc03e",
   "metadata": {},
   "source": [
    "# Assignment 3: Multi-Index DataFrames\n",
    "\n",
    "\n",
    "Can you help me access rows and columns with multiple indices? I’ve been struggling with multi-index DataFrames.\n",
    "\n",
    "Access:\n",
    "* Grab Store 3, Month 1\n",
    "* Then, select the column storing the mean of transactions\n",
    "\n",
    "Fix:\n",
    "* Drop the outer layer of the column Index\n",
    "* Reset the row index so it is the default integer index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "423fdd8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Ross' grouped DataFrame code, run this first\n",
    "\n",
    "grouped = (\n",
    "    transactions.groupby([\"store_nbr\", \"month\"])\n",
    "    .agg({\"transactions\": [\"sum\", \"mean\"]})\n",
    "    .sort_values(by=[\"month\", (\"transactions\", \"sum\")], ascending=[True, False])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7000de75",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df grouped by store and month with two aggregation columns\n",
    "\n",
    "grouped.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21b04cc7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0cdb7dd1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03e78a08",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "532e5e7b",
   "metadata": {},
   "source": [
    "# Assignment 4: The Agg Method\n",
    "\n",
    "Calculate the mean of target met by store, and the sum of bonuses to be paid to each store.\n",
    "\n",
    "Sort them by highest to lowest bonus payout.\n",
    "\n",
    "Then, do the same for day of week and month."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "219cf295",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Recreate table from section 3\n",
    "\n",
    "transactions = transactions.assign(\n",
    "    target_pct=transactions[\"transactions\"] / 2500,\n",
    "    met_target=(transactions[\"transactions\"] / 2500) >= 1,\n",
    "    bonus_payable=((transactions[\"transactions\"] / 2500) >= 1) * 100,\n",
    "    month=transactions.date.dt.month,\n",
    "    day_of_week=transactions.date.dt.dayofweek,\n",
    ")\n",
    "\n",
    "transactions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eda6cb0f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "835d3db9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e34f4360",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "12be3319",
   "metadata": {},
   "source": [
    "# Assignment 4: Transform\n",
    "\n",
    "Calculate the mean of transactions by store number and day of week while keeping row numbers. \n",
    "\n",
    "Then compare the performance of each row to its day of week average. (difference between transactions and daily avg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "040ea04b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cfa0f37a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a769ea79",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "b1bce8a6",
   "metadata": {},
   "source": [
    "# Assignment 5: Pivot\n",
    "\n",
    "Pivot transactions with store number as index, columns day of week, with the sum of bonus payable as cells.\n",
    "\n",
    "Filter to stores that had a non-zero bonus payable and create a heatmap.\n",
    "\n",
    "Then unpivot (melt) the table so we have one row for each store and day of the week with the corresponding total owed. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dff61add",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Use transactions table (ok if includes columns from assignment 4 or not)\n",
    "\n",
    "transactions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0fabd14c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2750178",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}