examples/Complex Example 2 - Filtering by Location Area.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Rows filtration by location in an area\n",
    "\n",
    "Here is new interesting sample code that was used to process real business data.\n",
    "\n",
    "Now then, we have a table with orders coordinates, latitude and longitude, and we want to check which ones are located in some given area (some region of a city). Let's first describe this area. In general, it looks this way:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Area](data/ce2-1.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This irregular hexagonal shape may look a little bit complicated from the first sight, but it can be easily devided into 3 triangles as shown by white lines. Also, checking whether a point is inside a triangle is easy. Here I use own simple code to describe points, triangles, and to check if a point is inside triangle:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import namedtuple\n",
    "Point = namedtuple('Point', 'x y')\n",
    "\n",
    "class Triangle:\n",
    "    def __init__(self, p1, p2, p3):\n",
    "        self.p1 = p1\n",
    "        self.p2 = p2\n",
    "        self.p3 = p3\n",
    "\n",
    "    @staticmethod\n",
    "    def calc_area(p1, p2, p3):\n",
    "        return abs((p1.x - p3.x) * (p2.y - p1.y) - (p1.x - p2.x) * (p3.y - p1.y)) / 2.0\n",
    "\n",
    "    def area(self):\n",
    "        return self.calc_area(self.p1, self.p2, self.p3)\n",
    "\n",
    "    def is_inside(self, pnt):\n",
    "        # The idea is that if the point is inside a triangle,\n",
    "        # it must split it into 3 smaller triangles which sum of areas\n",
    "        # must be equal to the area of original triangle\n",
    "        A = self.area()\n",
    "        A1 = self.calc_area(pnt, self.p2, self.p3)\n",
    "        A2 = self.calc_area(self.p1, pnt, self.p3)\n",
    "        A3 = self.calc_area(self.p1, self.p2, pnt)\n",
    "        # A1+A2+A3 must be equal to A, but we can allow computer some tiny error, 1%\n",
    "        if A1 + A2 + A3 - A < A / 100:\n",
    "            return True\n",
    "        else: \n",
    "            return False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, now we can describe the area using points and triangles:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "A, B = Point(47.571537, 42.5274088), Point(47.5846214, 42.557278)\n",
    "C, D, E = Point(47.5216788, 42.5436832), Point(47.5354716, 42.573346), Point(47.5505958, 42.6069556)\n",
    "F = Point(47.490442, 42.6267124)\n",
    "shape = [Triangle(C, E, F), Triangle(A, B, D), Triangle(B, D, E)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And the function to check if a point is inside any triangle:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def is_inside_shape(pnt):\n",
    "    return sum(map(lambda x: x.is_inside(pnt), shape)) > 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, let's process the data!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object <b>orders-pos.csv</b>, 6 columns, 3092 rows<br>\n",
       "<table>\n",
       "<tr><th>order_id</th><th>client_id</th><th>total_sum</th><th>date_created</th><th>lat</th><th>lon</th></tr>\n",
       "<tr><td>79938</td><td>459583</td><td>988.0</td><td>2018-01-02 05:09:30</td><td>47.661152400000006</td><td>62.2224172</td></tr>\n",
       "<tr><td>79954</td><td>305848</td><td>1450.0</td><td>2018-01-02 07:11:45</td><td>47.619584999999994</td><td>62.1688624</td></tr>\n",
       "<tr><td>79950</td><td>2943832</td><td>904.0</td><td>2018-01-02 07:14:01</td><td>49.859165000000004</td><td>47.653378</td></tr>\n",
       "<tr><td>79957</td><td>668815</td><td>1622.8</td><td>2018-01-02 07:27:52</td><td>51.849238400000004</td><td>44.2043104</td></tr>\n",
       "<tr><th>...</th><th>...</th><th>...</th><th>...</th><th>...</th><th>...</th></tr></table>"
      ],
      "text/plain": [
       "- Table object, title: \"orders-pos.csv\", 6 columns, 3092 rows.\n",
       "'order_id' 'client_id' 'total_sum' 'date_created' 'lat' 'lon'\n",
       "79938 459583 988.0 2018-01-02 05:09:30 47.661152400000006 62.2224172\n",
       "79954 305848 1450.0 2018-01-02 07:11:45 47.619584999999994 62.1688624\n",
       "79950 2943832 904.0 2018-01-02 07:14:01 49.859165000000004 47.653378\n",
       "79957 668815 1622.8 2018-01-02 07:27:52 51.849238400000004 44.2043104\n",
       ". . ."
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from lemuras import Table\n",
    "\n",
    "orders = Table.from_csv('data/orders-pos.csv', False)\n",
    "orders"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The filtration code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Column</b> object, title: \"Calc\" values: [False, False, False, False, False, False, False, False, False, False]. . ."
      ],
      "text/plain": [
       "- Column object, title: \"Calc\" values: [False, False, False, False, False, False, False, False, False, False]. . ."
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def check_row(row):\n",
    "    if row['lat'] is None:\n",
    "        return None\n",
    "    else:\n",
    "        return is_inside_shape(Point(row['lat'], row['lon']))\n",
    "# The `calc` function works similar to `apply`\n",
    "# but allows to deal with entire rows\n",
    "checker = orders.calc(check_row)\n",
    "\n",
    "# Or, the functional style if you prefer:\n",
    "checker = orders.calc(lambda row: 0 if row['lat'] is None else is_inside_shape(Point(row['lat'], row['lon'])))\n",
    "\n",
    "checker"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's it! 1% of orders is located in the area."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object <b>Filtered orders-pos.csv</b>, 6 columns, 35 rows<br>\n",
       "<table>\n",
       "<tr><th>order_id</th><th>client_id</th><th>total_sum</th><th>date_created</th><th>lat</th><th>lon</th></tr>\n",
       "<tr><td>79977</td><td>419155</td><td>730.0</td><td>2018-01-02 12:41:45</td><td>47.5291702</td><td>42.59985880000001</td></tr>\n",
       "<tr><td>79323</td><td>2906574</td><td>1102.0</td><td>2018-01-03 15:35:12</td><td>47.517124599999995</td><td>42.579290799999995</td></tr>\n",
       "<tr><td>79410</td><td>419155</td><td>1036.0</td><td>2018-01-05 15:23:47</td><td>47.5291702</td><td>42.59985880000001</td></tr>\n",
       "<tr><td>79840</td><td>2966116</td><td>478.0</td><td>2018-01-07 09:36:14</td><td>47.5317938</td><td>42.568736799999996</td></tr>\n",
       "<tr><th>...</th><th>...</th><th>...</th><th>...</th><th>...</th><th>...</th></tr></table>"
      ],
      "text/plain": [
       "- Table object, title: \"Filtered orders-pos.csv\", 6 columns, 35 rows.\n",
       "'order_id' 'client_id' 'total_sum' 'date_created' 'lat' 'lon'\n",
       "79977 419155 730.0 2018-01-02 12:41:45 47.5291702 42.59985880000001\n",
       "79323 2906574 1102.0 2018-01-03 15:35:12 47.517124599999995 42.579290799999995\n",
       "79410 419155 1036.0 2018-01-05 15:23:47 47.5291702 42.59985880000001\n",
       "79840 2966116 478.0 2018-01-07 09:36:14 47.5317938 42.568736799999996\n",
       ". . ."
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered = orders.loc(checker)\n",
    "filtered"
   ]
  }
 ],
 "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
}