n2a_pandas_and_mito.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to Mito"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that you know have a solid foundation in Jupyter, Python and Pandas, it's time to do some more advanced data manipulation -- things that look and feel like report automation.\n",
    "\n",
    "To make our lives easier, we're going to use Mito to generate a lot of this Pandas code for us.\n",
    "\n",
    "Mito is a spreadsheet that lives inside your Jupyter. And for every edit you make to your data using Mito, it generates the equivalent Pandas code for you."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import mitosheet\n",
    "mitosheet.sheet()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "In the rest of this section, we'll practice using Mito to build some simple reports. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Excercise 1: Cleaning messy data\n",
    "Instructions:\n",
    "* Import the csv file `data2_messy_data.csv`.   \n",
    "* Convert the dates that are strings to timetime format\n",
    "* Remove the substring `-Stock` from the `Stock` column using spreadsheet formulas\n",
    "* Cast the `Transaction_Price` type from `string` to `float`\n",
    "* Delete columns that only have one unqiue value\n",
    "* Rename unhelpfully long column headers\n",
    "* Export the new dataset as data_2_cleaned.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import mitosheet\n",
    "mitosheet.sheet()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "data2_messy_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "💡 Notice that for every edit you made in Mito, it generated the equivalent Python code for you. \n",
    "\n",
    "There's a lot of pandas syntax in the Mito generated code that we did not learn yet. Take a couple of minutes to read the code and see if you can understand what each line does.\n",
    "\n",
    "\n",
    "🧑‍💻 Now, try running the Mito generated Python code and notice that the dataframe is exactly the same as the data in Mito.\n",
    "\n",
    "\n",
    "💡 Be aware each time you rerun the `mitosheet.sheet()` call or make an edit in the Mito spreadsheet, Mito regenerates the Python code in the following code cell. That means if you make edits to the generated code it will be overwritten as soon as you interact with Mito again. If you want to edit the generated code either delete the Mito spreadsheet or copy+paste the python code to a new cell before making changes to it.\n",
    "\n",
    "\n",
    "## Excercise 2: Building a pivot table\n",
    "\n",
    "Instructions:\n",
    "\n",
    "* Import now again the loans `data3_loans.csv` file.\n",
    "* Convert the `issue_date` column to a date\n",
    "* Calculate the difference between annual income and loan amount\n",
    "* Filter the dataset down to just the car loans \n",
    "* Create a pivot table that shows the average loan amount for each month\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "loans = pd.read_csv('data3_loans.csv')\n",
    "\n",
    "mitosheet.sheet(loans)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "💡 Notice that instead of importing data from a csv or Excel file, passed a dataframe directly to the `mitosheet.sheet()` function call. \n",
    "\n",
    "That means that as long as you can get your data into a pandas dataframe, you can edit it in Mito. You can create the dataframe by writing a sql query and connecting to a database, scraping a website, etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Excercise 3: Isolate most recent trades for each stock\n",
    "\n",
    "Instructions: Import both sheets from `data4_equity_transactions.xlsx`. Find the most recent trade for each stock by combining the transaction records for May and June."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "mitosheet.sheet()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Excercise 4: Calculate WoW Change in Price\n",
    "Instructions: Import `data5_apple_stock.csv` and calculate week over week change in price. Export the result as an Excel file.  \n",
    "\n",
    "Hint: Try using a combination of spreadsheet formulas and deduplication\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "mitosheet.sheet()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "💡  At this point, you might have a bunch of open Mito spreadsheets in your notebook. If you prefer working in a smaller document, go to `Edit` > `Clear All Outputs`. It will close all of the open Mito spreadsheets, but because you still have the `analysis_to_replay` id, the next time you run the mitosheet.sheet() call, it will repopulate the Mito spreadsheet to the same state.\n",
    "\n",
    "## Excercise 5: Recon data sources\n",
    "* Instructions:\n",
    "    * Import transaction records from two different data sources: Eagle and an Excel file that is manually tracked.\n",
    "    * Merge them together on a column called `Transaction ID`\n",
    "    * Outside of Mito, write the Python code for performing the following checks. \n",
    "        * If the either dataset does not have a value in the `Share Quantity` column, set the value of the `Check` column to \"Action Required. Missing Data\". \n",
    "        * If the Quantity numbers are the same, set the value of the `Check` column to \"Matching. No action required.\"\n",
    "        * If the numbers are not matching, set the value of the `Check` column to \"Action Required. Quantity does not match\"\n",
    "    * Import the updated dataframe to Mito and separate the data in 3 different sheets, one for each condition.\n",
    "    * Download an Excel file with three tabs  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "transactions_excel = pd.read_csv('data6_transactions_excel.csv')\n",
    "transactions_eagle = pd.read_csv('data6_transactions_eagle.csv')\n",
    "\n",
    "mitosheet.sheet(transactions_excel, transactions_eagle)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def check_row(row):\n",
    "    # TODO: Replace me with your code. \n",
    "    # Hint: The pd.isnull(x) function to check if a cell is NaN might be helpful"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df3['Check'] = df3.apply(lambda row: check_row(row), axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "mitosheet.sheet(df3)"
   ]
  }
 ],
 "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.6"
  },
  "vscode": {
   "interpreter": {
    "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}