excel-to-pandas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "from glob import glob\n",
    "import os\n",
    "import re\n",
    "\n",
    "import pandas as pd\n",
    "import qgrid\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Excel to Pandas (and Back)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Download the data set\n",
    "\n",
    "For this demo we're using the [Online Retail Data Set](http://archive.ics.uci.edu/ml/datasets/Online+Retail/) which can be found on the UCI Machine Learning Repository. \n",
    "\n",
    "Run the cells below to download the data programmatically. \n",
    "\n",
    "**Note:** If you navigate to the page linked above, you may find that the \"Data Folder\" link is broken. It should link to the following:  https://archive.ics.uci.edu/ml/machine-learning-databases/00352. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not os.path.exists('data'):\n",
    "    os.mkdir('data')\n",
    "    \n",
    "if not os.path.exists('data/monthly'):\n",
    "    os.mkdir('data/monthly')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "!wget -nc -O data/online-retail.xlsx \\\n",
    "https://archive.ics.uci.edu/ml/machine-learning-databases/00352/Online%20Retail.xlsx"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prepare the data\n",
    "\n",
    "The data set describes online transactions taking place from December 2010 to December 2011. We split them into monthly spreadsheets for the sake of demonstration, since we'll be covering how to combine data from multiple files and how to automate tasks across multiple files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_path = 'data/online-retail.xlsx'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_to_monthly(data_path):\n",
    "    \n",
    "    printout = 'Reading data from {}:'.format(data_path)\n",
    "    print(printout)\n",
    "    print('-'*len(printout))\n",
    "    \n",
    "    df = pd.read_excel(data_path)\n",
    "    df['MonthYear'] = df['InvoiceDate'].dt.strftime('%b-%Y')\n",
    "\n",
    "    for month_year in df['MonthYear'].unique():\n",
    "        monthly_df = df[df['MonthYear'] == month_year].drop('MonthYear', 1)\n",
    "        file_path = os.path.join('data/monthly', 'transactions-' + month_year + '.xlsx')\n",
    "        print(' Writing file: {}'.format(file_path))\n",
    "        monthly_df.to_excel(file_path, index=True)\n",
    "        \n",
    "    print('-'*len(printout))\n",
    "    print('Complete.')\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "convert_to_monthly(data_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading from an Excel file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Specify the path to the file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sheet_path = 'data/monthly/transactions-Dec-2010.xlsx'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_df(sheet_path):\n",
    "    df = pd.read_excel(sheet_path, index_col=0)\n",
    "    df['InvoiceNo'] = df['InvoiceNo'].astype(str)\n",
    "    df['StockCode'] = df['StockCode'].astype(str)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = load_df(sheet_path)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Investigating missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.isna().mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df[df['Description'].isna()].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**It looks like items without a description have a unit price of 0.0. Let's confirm this and omit these, for now.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "print((df[df['Description'].isna()]['UnitPrice'] == 0).all())\n",
    "\n",
    "def remove_items_without_description(df):\n",
    "    return df[~df['Description'].isna()]\n",
    "\n",
    "df = remove_items_without_description(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's start looking into something simple: what products are selling well? We'll need to do some *aggregation* over the product identifiers - the StockCodes. First, let's build a data frame of the products we find here. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "products = df.groupby('StockCode')[['Description', 'UnitPrice']].first()\n",
    "products.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the last two rows describe different colors / variants of the same product. Should these be counted as separate products? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def separate_code_and_color(df):\n",
    "    \n",
    "    df[['StockCodeBase', 'Color']] = df['StockCode'].str.extract(r'(\\d+)([A-Z])*')\n",
    "    df['Color'] = df['Color'].fillna(-1)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = separate_code_and_color(df)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_product_df(df):\n",
    "    \n",
    "    products = (df.groupby('StockCodeBase')[['Description','Color', 'UnitPrice']]\n",
    "     .agg(\n",
    "         Description=('Description', 'first'),\n",
    "         n_colors=('Color', 'nunique'),\n",
    "         UnitPrice=('UnitPrice', 'first')\n",
    "     ))\n",
    "    \n",
    "    return products\n",
    "\n",
    "get_product_df(df).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Top-selling items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def top_selling_items(df, n_items=10):\n",
    "    df['GrossRevenue'] = df['UnitPrice'] * df['Quantity']\n",
    "    \n",
    "    top_items = (df.groupby('StockCodeBase')[['Description', 'UnitPrice', 'Quantity', 'GrossRevenue']]\n",
    "                 .agg(\n",
    "                     Description=('Description', 'first'),\n",
    "                     UnitPrice=('UnitPrice', 'first'),\n",
    "                     Quantity=('Quantity', 'sum'),\n",
    "                     TotalGrossRevenue=('GrossRevenue', 'sum')\n",
    "                 )\n",
    "                ).sort_values(by='TotalGrossRevenue', ascending=False)\n",
    "    \n",
    "    return top_items.iloc[:n_items, :]\n",
    "\n",
    "top_selling_items(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Working with strings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['Description'].str.contains('UNION')].sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['Description'].str.contains('UNION')]['Country'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Item colors with regex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Earlier, we saw that the StockCode contained some information about the colors/variants of items, but it didn't help us compare across multiple items. \n",
    "\n",
    "Now, we'll use *regular expressions* (regex) to attempt to locate and extract a standard color from the item description. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "color_reg = re.compile(r'((?:BLACK)|(?:WHITE)|(?:RED)|(?:GREEN)|(?:BLUE)|(?:PINK))')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['DescriptionColor'] = (df['Description']\n",
    "                          .str.extract(color_reg)\n",
    "                          .fillna('None'))\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def top_selling_colors(df):\n",
    "    color_reg = re.compile(r'((?:BLACK)|(?:WHITE)|(?:RED)|(?:GREEN)|(?:BLUE)|(?:PINK))')\n",
    "    df['DescriptionColor'] = df['Description'].str.extract(color_reg).fillna('None')\n",
    "    \n",
    "    top_colors = (df\n",
    "                  .groupby('DescriptionColor')['GrossRevenue']\n",
    "                  .sum()\n",
    "                  .sort_values(ascending=False)\n",
    "                  .reset_index())\n",
    "    return top_colors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "top_selling_colors(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Working with Dates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data description page mentions that many of the customers are wholesalers. We might expect that business customers exhibit different shopping patterns from non-business customers. For instance, we might wonder if business customers make more purchases on week days and during business hours.\n",
    "\n",
    "Whether or not this is the case, Pandas makes it easy to extract this temporal information and use it to filter and process data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the `.dt` accessor to utilize datetime methods like `.year`, `.month`', `.hour`, and even `.dayofweek`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['InvoiceDate'].dt.dayofweek.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Interestingly enough, it appears there are no Saturday transactions in the data set. Let's see what we can do with time of day."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['InvoiceDate'].dt.hour.plot.hist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These methods can be used to create filters for the data as well. \n",
    "\n",
    "Here we make a `business_hours` filter that keeps only transactions occurring between 9AM-5PM Monday through Friday."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_9_to_5 = (df['InvoiceDate'].dt.hour >=9) & (df['InvoiceDate'].dt.hour <17)\n",
    "filter_weekday = df['InvoiceDate'].dt.dayofweek <=5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_weekday = df[filter_9_to_5 & filter_weekday]\n",
    "df_weekday"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Writing to an Excel file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For simple Excel output, we can use the Pandas `.to_excel` method. For more advanced usage, we use the [XlsxWriter](https://xlsxwriter.readthedocs.io/) library. With the latter, we can create more sophisticated files with multiple sheets and even charts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not os.path.exists('output'):\n",
    "    os.mkdir('output')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = os.path.join('output', 'Dec-2010'+ '.xlsx')\n",
    "\n",
    "with pd.ExcelWriter(file_path) as writer:\n",
    "    df.to_excel(writer, sheet_name='original', index=False)\n",
    "    get_product_df(df).reset_index().to_excel(writer, sheet_name='products', index=False)\n",
    "    top_selling_items(df).reset_index().to_excel(writer,sheet_name='top-items', index=False)\n",
    "    top_selling_colors(df).to_excel(writer,sheet_name='top-colors', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Automation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, let's do this to all the monthly files we have!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_and_process_df(path):\n",
    "    print(\"Loading {}\".format(path))\n",
    "    df = load_df(path)\n",
    "    df = remove_items_without_description(df)\n",
    "    df = separate_code_and_color(df)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_report(df, name):\n",
    "    \n",
    "    file_path = os.path.join('output', '{}.xlsx'.format(name))\n",
    "\n",
    "    print('Writing {}'.format(file_path))\n",
    "    with pd.ExcelWriter(file_path) as writer:\n",
    "        df.to_excel(writer, sheet_name='original', index=False)\n",
    "        get_product_df(df).reset_index().to_excel(writer, sheet_name='products', index=False)\n",
    "        top_selling_items(df).reset_index().to_excel(writer,sheet_name='top-items', index=False)\n",
    "        top_selling_colors(df).to_excel(writer,sheet_name='top-colors', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_report_paths = glob('data/monthly/*')\n",
    "monthly_report_paths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_reports = {path[-13:-5]:load_and_process_df(path) for path in monthly_report_paths}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for name, report in monthly_reports.items():\n",
    "    generate_report(report, name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Concatenating data frames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all = pd.concat((df for df in monthly_reports.values()))\n",
    "df_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "top_selling_items(df_all)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "generate_report(df_all, 'all-data')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bonus: point-and-click data frames with [Qgrid](https://github.com/quantopian/qgrid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qgrid_widget = qgrid.show_grid(df,\n",
    "                               show_toolbar=True,\n",
    "                               grid_options={'forceFitColumns': False}\n",
    "                              )\n",
    "qgrid_widget"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Run this cell to get the updates into the original data frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qgrid_widget.get_changed_df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}