exercises/monthly-sales-reporting/Monthly_Sales_Reporting_Exercise.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Monthly Sales Reporting Exercise",
      "provenance": [],
      "authorship_tag": "ABX9TyNk+j71JbBz3Jbl9aSjvYSi",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/prof-rossetti/intro-to-python/blob/master/exercises/monthly-sales-reporting/Monthly_Sales_Reporting_Exercise.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Rv6f5uPhzRib"
      },
      "source": [
        "# Learning Objectives\n",
        "\n",
        "  + Practice processing CSV files in Python.\n",
        "  + Practice using the Pandas package, so you can add that skill to your resume.\n",
        "  + Practice automating a real life business process.\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "61Mj-AHyKlIv"
      },
      "source": [
        "# Business Prompt"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HqhU_dHyKmve"
      },
      "source": [
        "Assume you own and operate a successful small business, selling artisan clothing products through an online platform like Amazon, Etsy, or eBay.\n",
        "\n",
        "![hoodie for sale on etsy](https://user-images.githubusercontent.com/1328807/51781151-cb7a5300-20e2-11e9-863f-3b82aaa5f5a9.png)\n",
        "\n",
        "Due to certain investment interests in your business, you are obligated to hold monthly board meetings with your investors and advisors to discuss the company's strategic direction, set priorities, and allocate resources. To aid the board's decision-making processes, they expect you to provide them with a monthly summary report of business insights, including the aggregation of total sales and the identification of top-selling products.\n",
        "\n",
        "Luckily, at the end of each calendar month, the online platform your company uses to sell its products makes available for download from its admin interface a CSV file representing all individual sales orders for that month.\n",
        "\n",
        "But usually you only have a few hours from when the data becomes available to when you are required to turn it around and share the resulting insights with the board. Over the past few months, this report compilation process has been somewhat stressful and vulnerable to manual error. And the board's confidence in your operational leadership depends not only on your ability to meet sales goals, but also on your ability to provide them with accurate and timely information.\n",
        "\n",
        "So your objective is to create a tool which automates the process of transforming monthly sales data into a report of business insights. "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Mq5XIgZP0Wyu"
      },
      "source": [
        "# Instructions\n",
        "\n",
        "Write Python code to process any of the provided monthly sales CSV file to produce the desired information outputs.\n",
        "\n",
        "Start by developing against the \"sales-201803.csv\" file, and check your answers using that file. Then, once you are done, you should be able to simply change / update the selected filename to point to a different monthly sales file, and after re-running the notebook, it should automatically process that month's sales.\n",
        "\n",
        "\n",
        "> NOTE: You can assume any of these monthly CSV files will have a file name resembling \"sales-YYYYMM.csv\" (where \"YYYY\" represents the four digit year and \"MM\" represents the zero-padded month). \n",
        "\n",
        "> NOTE: You can assume each of these CSV files will have the same header row (`date`, `product`, `unit price`, `units sold`, `sales price`).\n",
        "\n",
        "\n",
        "## Basic Requirements\n",
        "\n",
        "  1. Display a human-friendly representation of the selected month and year, based on which file was selected (e.g. \"Selected Month: March 2018\")\n",
        "  2. Display the total sales for that month, formatted as USD with a dollar sign and two decimal places (e.g. \"Total Monthly Sales: \\$12,000.71\")\n",
        "  3. All price-related information should be formatted as USD, with a dollar sign and two decimal places. \n",
        "\n",
        "\n",
        "```\n",
        "SELECTED MONTH: MARCH 2018\n",
        "TOTAL SALES: $12,000.71\n",
        "```\n",
        "\n",
        "## Further Exploration Challenge\n",
        "\n",
        "\n",
        "Also write Python code to determine which products are the top-sellers, and print the total sales for each product:\n",
        "\n",
        "```\n",
        "TOP SELLING PRODUCTS:\n",
        "  1. Button-Down Shirt: $6,960.35\n",
        "  2. Super Soft Hoodie: $1,875.00\n",
        "  3. etc.\n",
        "```\n",
        "\n",
        "To test the accuracy of your program's calculations, compare its \"March 2018\" results against the table of expected values below:\n",
        "\n",
        "Product | Sum of sales price\n",
        "--- | ---\n",
        "Button-Down Shirt | \\$6,960.35\n",
        "Super Soft Hoodie | \\$1,875.00\n",
        "Khaki Pants | \\$1,602.00\n",
        "Vintage Logo Tee | \\$941.05\n",
        "Brown Boots | \\$250.00\n",
        "Sticker Pack | \\$216.00\n",
        "Baseball Cap | \\$156.31\n",
        "**Total Monthly Sales** | **$12,000.71**\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ppk0mSyczSAD"
      },
      "source": [
        "\n",
        "# References\n",
        "\n",
        "You may find the following reference material helpful in completing this exercise:\n",
        "\n",
        "  + [`pandas.read_csv()`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html)\n",
        "  + [`pandas.DataFrame`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html):\n",
        "    + [`pandas.DataFrame.groupby()`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.groupby.html)\n",
        "  + [`pandas.Series`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.html):\n",
        "\n",
        "\n",
        "> FYI - The basic requirements can be acheived using the Pandas `DataFrame` and `Series` datatypes. However the Further Exploration Challenge requires usage of the `GroupBy` datatype for pivot table - style aggrigation purposes.\n",
        "\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tvwsARJ1Eym-"
      },
      "source": [
        "# Setup"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nGbRcwzl_ar0",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "816caf68-8b35-4c6e-a92c-1ab7c793763e"
      },
      "source": [
        "#\n",
        "# SETUP\n",
        "#\n",
        "\n",
        "# DOWNLOAD FILES\n",
        "\n",
        "import os\n",
        "\n",
        "def filepath_for(url):\n",
        "    return url.split(\"/\")[-1] \n",
        "\n",
        "MONTHS = [\"201710\", \"201711\", \"201712\", \"201801\", \"201802\", \"201803\", \"201804\"]\n",
        "\n",
        "for month in MONTHS:\n",
        "    data_url = f\"https://raw.githubusercontent.com/prof-rossetti/data-analytics-in-python/main/data/unit-2/monthly-sales/sales-{month}.csv\"\n",
        "    csv_filepath = filepath_for(data_url)\n",
        "    print(\"CSV FILEPATH:\", csv_filepath)\n",
        "    if not os.path.isfile(csv_filepath):\n",
        "        print(\"DOWNLOADING\", data_url)\n",
        "        # FYI: this wget command is a terminal command, NOT python\n",
        "        # ... in colab, we can execute terminal commands by prefixing them with an exclamation point\n",
        "        # ... students are not responsible for knowing terminal commands like this\n",
        "        !wget -q $data_url \n",
        "\n",
        "\n",
        "# HERE IS A PRICE FORMATTING FUNCTION FOR YOU TO USE LATER\n",
        "\n",
        "def to_usd(my_price):\n",
        "    \"\"\"\n",
        "    Converts a numeric value to usd-formatted string, for printing and display purposes.\n",
        "    \n",
        "    Param: my_price (int or float) like 4000.444444\n",
        "    \n",
        "    Example: to_usd(4000.444444)\n",
        "    \n",
        "    Returns: $4,000.44\n",
        "    \"\"\"\n",
        "    return f\"${my_price:,.2f}\" #> $12,000.71\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "CSV FILEPATH: sales-201710.csv\n",
            "CSV FILEPATH: sales-201711.csv\n",
            "CSV FILEPATH: sales-201712.csv\n",
            "CSV FILEPATH: sales-201801.csv\n",
            "CSV FILEPATH: sales-201802.csv\n",
            "CSV FILEPATH: sales-201803.csv\n",
            "CSV FILEPATH: sales-201804.csv\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5b06VpNVE4C5"
      },
      "source": [
        "# Implementation\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-oWJE6oyJIae"
      },
      "source": [
        "#\n",
        "# FILE SELECTION\n",
        "#\n",
        "# ... start by leaving this cell as-is, and write the desired Python code in the cells below\n",
        "# ... then come back later and update this filename as desired, and re-run the notebook to process the selected month\n",
        "\n",
        "csv_filename = \"sales-201803.csv\" # OR ANY ONE OF THE OTHER VALID FILES\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2Xm_a4xF_Nwh"
      },
      "source": [
        "\n",
        "#\n",
        "# BASIC REQUIREMENTS\n",
        "#\n",
        "# todo: process the monthly sales CSV file!\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QDzPKrLGdJZj"
      },
      "source": [
        "\n",
        "#\n",
        "# FURTHER EXPLORATION CHALLENGE\n",
        "#\n",
        "# todo: further process the monthly sales CSV file!\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}