examples/Complex Example 3 - Parsing OLAP Reports.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Parsing OLAP reports\n",
    "\n",
    "[OLAP](https://en.wikipedia.org/wiki/Online_analytical_processing) report format is intented to be a result of code, not an input. However, sometimes there is no other option except parsing such files. And here is an example!\n",
    "\n",
    "OLAP reports have several features that make such tables hard to parse with existing tools:  \n",
    "0. Many columns are grouped ones. It means that their values are sparse, and must be used for rows below.\n",
    "0. Tables have aggregated rows which must be removed.\n",
    "0. Upper-header rows which should be used to distinguish columns with the same name.\n",
    "0. Padding rows on the top.\n",
    "\n",
    "And all these features are handled by given parser! It can easily deal with given OLAP report with CSV, TSV or XLS extension. Moreover, it has different settings and is very customisable allowing you to control all the details – because different companies and apps create different OLAP reports with different column names, padding rows number and other features."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "0k, let's have a look on our exemplary report [`olap.csv`](https://github.com/AivanF/Lemuras/blob/master/examples/data/olap.csv):"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Area](data/olap-screen.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To create custom parser class, you need to inherit **`OlapParser`** which provides you with all the flexible stuff. It's instances have `.parse(fl, filename)` method for converting an OLAP report to a usual Lemuras table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from olap_parser import OlapParser"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To describe columns, you should use **`ColumnData`** class (which is a named tuple under the hood). And the simplest way to create it is to use `ColumnData_make(**kwargs)` function. It has the following arguments:\n",
    "\n",
    "- **`name`** – just a string name of final column. Required field.\n",
    "- **`type`** – type which will be used to convert cell values. It must be a string with type name (one of Lemuras supported types, they described in `Example 1.5 - Functions Applying`), or your own executable that takes a string and returns anything. Required field.\n",
    "- **`cache`** – if `True`, it indicates that the columns is a grouped one. It's values will be saved and used instead of empty cells. Otherwise, empty cells will be filled with the default value. `cache=True` by default.\n",
    "- **`default`** – default value for empty cells of grouped column. `default=None` by default."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from olap_parser import ColumnData_make"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If parser meets predictable troubles, it throws **`ParserError`** exceptions which means that given file is not supported. By the way, you can freely use it in custom parsers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from olap_parser import ParserError"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 1. Parsing rules definition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "class CustomParser(OlapParser):\n",
    "    def __init__(self):\n",
    "        super().__init__()\n",
    "        # The first row contains useless info\n",
    "        self.skip_header_rows = 1\n",
    "        # It will help distinguish Discount Sum and Total Sum\n",
    "        self.meta_header_rows = 1\n",
    "\n",
    "    def check_skip(self, line, row):\n",
    "        # line arg is a string, and row arg is a list of cells\n",
    "\n",
    "        # Don't skip if current row is a header\n",
    "        if self.columns is None:\n",
    "            return False\n",
    "\n",
    "        # Skip aggregated rows\n",
    "        line = line.lower()\n",
    "        return len(line) == 0 or 'total' in line\n",
    "\n",
    "    def describe_column(self, column_index, name, meta):\n",
    "        # Should return None or ColumnData object\n",
    "        name = name.lower()\n",
    "\n",
    "        if name == 'country':\n",
    "            return ColumnData_make(name='country', type='str', cache=True)\n",
    "\n",
    "        if name == 'reported':\n",
    "            return ColumnData_make(name='created', type='date', cache=True)\n",
    "        \n",
    "        # Note that meta headers spread to their right\n",
    "        # So that Discount meta header is the same for 2 columns\n",
    "        if 'sum' in name and 'Discount' in meta:\n",
    "            return ColumnData_make(name='discount_sum', type='int', cache=False)\n",
    "        \n",
    "        if 'sum' in name and 'Total' in meta:\n",
    "            return ColumnData_make(name='total_sum', type='int', cache=False)\n",
    "        \n",
    "        if 'order count' in name and 'Total' in meta:\n",
    "            return ColumnData_make(name='count', type='int', cache=False)\n",
    "\n",
    "        def parse_category(raw_value):\n",
    "            raw_value = raw_value.lower()\n",
    "            if 'vegetables' in raw_value or 'fruits' in raw_value:\n",
    "                return 1\n",
    "            if 'meat' in raw_value or 'fish' in raw_value:\n",
    "                return 2\n",
    "            return 3\n",
    "\n",
    "        if 'category' in name:\n",
    "            return ColumnData_make(name='category', type=parse_category, cache=False)\n",
    "\n",
    "        def parse_pay_type(raw_value):\n",
    "            raw_value = raw_value.lower()\n",
    "            if 'transfer' in raw_value:\n",
    "                return 1\n",
    "            if 'cash' in raw_value:\n",
    "                return 2\n",
    "            if 'debt' in raw_value:\n",
    "                return 3\n",
    "            return 0\n",
    "\n",
    "        if 'payment' in name:\n",
    "            return ColumnData_make(name='pay_type', type=parse_pay_type, cache=False)\n",
    "\n",
    "        return None\n",
    "\n",
    "    def validate_columns(self):\n",
    "        # Basic check of necessary columns\n",
    "        need_columns = ['country', 'category', 'created', 'total_sum']\n",
    "        for column in need_columns:\n",
    "            if column not in self.res_columns:\n",
    "                raise ParserError('Column \"{}\"\" not found!'.format(column))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Some visual trick, never mind...\n",
    "from lemuras import Table ; Table.maxshowrows = 16"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's it! We have just described what we see in the report and what we wanna get from it. Let's try it out."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 2. Usage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object <b>olap.csv</b>, 7 columns, 12 rows<br>\n",
       "<table>\n",
       "<tr><th>country</th><th>category</th><th>created</th><th>discount_sum</th><th>count</th><th>total_sum</th><th>pay_type</th></tr>\n",
       "<tr><td>Austria</td><td>3</td><td>2019-04-25</td><td>93</td><td>28</td><td>3080</td><td>1</td></tr>\n",
       "<tr><td>Germany</td><td>1</td><td>2019-04-27</td><td>33</td><td>18</td><td>1620</td><td>2</td></tr>\n",
       "<tr><td>Germany</td><td>2</td><td>2019-04-27</td><td>0</td><td>20</td><td>2400</td><td>1</td></tr>\n",
       "<tr><td>Germany</td><td>1</td><td>2019-04-28</td><td>46</td><td>23</td><td>2300</td><td>2</td></tr>\n",
       "<tr><td>Germany</td><td>2</td><td>2019-04-28</td><td>68</td><td>14</td><td>1680</td><td>1</td></tr>\n",
       "<tr><td>Poland</td><td>1</td><td>2019-04-29</td><td>60</td><td>12</td><td>1200</td><td>1</td></tr>\n",
       "<tr><td>Poland</td><td>1</td><td>2019-04-29</td><td>36</td><td>16</td><td>1760</td><td>1</td></tr>\n",
       "<tr><td>Poland</td><td>2</td><td>2019-04-29</td><td>72</td><td>20</td><td>2400</td><td>3</td></tr>\n",
       "<tr><td>Ukraine</td><td>3</td><td>2019-04-18</td><td>130</td><td>26</td><td>2600</td><td>3</td></tr>\n",
       "<tr><td>Spain</td><td>2</td><td>2019-04-24</td><td>0</td><td>33</td><td>3960</td><td>2</td></tr>\n",
       "<tr><td>Spain</td><td>1</td><td>2019-04-24</td><td>56</td><td>14</td><td>1400</td><td>1</td></tr>\n",
       "<tr><td>Spain</td><td>1</td><td>2019-04-24</td><td>49</td><td>22</td><td>2420</td><td>2</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "- Table object, title: \"olap.csv\", 7 columns, 12 rows.\n",
       "'country' 'category' 'created' 'discount_sum' 'count' 'total_sum' 'pay_type'\n",
       "'Austria' 3 2019-04-25 93 28 3080 1\n",
       "'Germany' 1 2019-04-27 33 18 1620 2\n",
       "'Germany' 2 2019-04-27 0 20 2400 1\n",
       "'Germany' 1 2019-04-28 46 23 2300 2\n",
       "'Germany' 2 2019-04-28 68 14 1680 1\n",
       "'Poland' 1 2019-04-29 60 12 1200 1\n",
       "'Poland' 1 2019-04-29 36 16 1760 1\n",
       "'Poland' 2 2019-04-29 72 20 2400 3\n",
       "'Ukraine' 3 2019-04-18 130 26 2600 3\n",
       "'Spain' 2 2019-04-24 0 33 3960 2\n",
       "'Spain' 1 2019-04-24 56 14 1400 1\n",
       "'Spain' 1 2019-04-24 49 22 2420 2"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parser = CustomParser()\n",
    "df = parser.parse(None, 'data/olap.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Absolutely correct Lemuras Table! Now you can parse OLAP reports really fast and easily `:)`\n",
    "\n",
    "If you need more flexibility, have a look at [**`OlapParser.init`** method](https://github.com/AivanF/Lemuras/blob/master/examples/olap_parser.py#L36), there are several helpful variables."
   ]
  }
 ],
 "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}