examples/Example 3 - Merge Join.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from lemuras import Table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Increase the output size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Table.minshowrows = 9\n",
    "Table.maxshowrows = 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## First table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Sample1</b>, 4 columns, 6 rows.<br><table><tr><th>'type'</th><th>'size'</th><th>'weight'</th><th>'tel'</th></tr><tr><td>'A'</td><td>2</td><td>12</td><td>'+79260360139'</td></tr><tr><td>'B'</td><td>1</td><td>12</td><td>84505505151</td></tr><tr><td>'D'</td><td>1</td><td>10</td><td>'31415926535'</td></tr><tr><td>'B'</td><td>2</td><td>14</td><td>''</td></tr><tr><td>'A'</td><td>1</td><td>15</td><td>'23816326412'</td></tr><tr><td>'D'</td><td>2</td><td>11</td><td>0</td></tr></table>"
      ],
      "text/plain": [
       "- Table object, title: \"Sample1\", 4 columns, 6 rows.\n",
       "'type' 'size' 'weight' 'tel'\n",
       "'A' 2 12 '+79260360139'\n",
       "'B' 1 12 84505505151\n",
       "'D' 1 10 '31415926535'\n",
       "'B' 2 14 ''\n",
       "'A' 1 15 '23816326412'\n",
       "'D' 2 11 0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = ['type', 'size', 'weight', 'tel']\n",
    "rows = [\n",
    "    ['A', 2, 12, '+79260360139'],\n",
    "    ['B', 1, 12, 84505505151],\n",
    "    ['D', 1, 10, '31415926535'],\n",
    "    ['B', 2, 14, ''],\n",
    "    ['A', 1, 15, '23816326412'],\n",
    "    ['D', 2, 11, 0],\n",
    "]\n",
    "df1 = Table(cols, rows, 'Sample1')\n",
    "\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Second table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Sample2</b>, 3 columns, 4 rows.<br><table><tr><th>'type'</th><th>'size'</th><th>'cost'</th></tr><tr><td>'A'</td><td>2</td><td>3.1415</td></tr><tr><td>'B'</td><td>1</td><td>2.7182</td></tr><tr><td>'C'</td><td>2</td><td>1.2345</td></tr><tr><td>'D'</td><td>1</td><td>1.2345</td></tr></table>"
      ],
      "text/plain": [
       "- Table object, title: \"Sample2\", 3 columns, 4 rows.\n",
       "'type' 'size' 'cost'\n",
       "'A' 2 3.1415\n",
       "'B' 1 2.7182\n",
       "'C' 2 1.2345\n",
       "'D' 1 1.2345"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = ['type', 'size', 'cost']\n",
    "rows = [\n",
    "    ['A', 2, 3.1415],\n",
    "    ['B', 1, 2.7182],\n",
    "    ['C', 2, 1.2345],\n",
    "    ['D', 1, 1.2345],\n",
    "]\n",
    "df2 = Table(cols, rows, 'Sample2')\n",
    "\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tables JOIN by single column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It works as a usual **`JOIN`** with columns equality check in **SQL** or **`merge`** in **pandas**.\n",
    "\n",
    "You can specify single column name or a list of names.\n",
    "\n",
    "Both tables must have the specified columns, otherwise you should rename them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Merged inner Sample1 Sample2</b>, 6 columns, 6 rows.<br><table><tr><th>'type'</th><th>'size'</th><th>'weight'</th><th>'tel'</th><th>'size'</th><th>'cost'</th></tr><tr><td>'A'</td><td>2</td><td>12</td><td>'+79260360139'</td><td>2</td><td>3.1415</td></tr><tr><td>'B'</td><td>1</td><td>12</td><td>84505505151</td><td>1</td><td>2.7182</td></tr><tr><td>'D'</td><td>1</td><td>10</td><td>'31415926535'</td><td>1</td><td>1.2345</td></tr><tr><td>'B'</td><td>2</td><td>14</td><td>''</td><td>1</td><td>2.7182</td></tr><tr><td>'A'</td><td>1</td><td>15</td><td>'23816326412'</td><td>2</td><td>3.1415</td></tr><tr><td>'D'</td><td>2</td><td>11</td><td>0</td><td>1</td><td>1.2345</td></tr></table>"
      ],
      "text/plain": [
       "- Table object, title: \"Merged inner Sample1 Sample2\", 6 columns, 6 rows.\n",
       "'type' 'size' 'weight' 'tel' 'size' 'cost'\n",
       "'A' 2 12 '+79260360139' 2 3.1415\n",
       "'B' 1 12 84505505151 1 2.7182\n",
       "'D' 1 10 '31415926535' 1 1.2345\n",
       "'B' 2 14 '' 1 2.7182\n",
       "'A' 1 15 '23816326412' 2 3.1415\n",
       "'D' 2 11 0 1 1.2345"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = Table.merge(df1, df2, 'type', 'inner')\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge tables inner"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Merged inner Sample1 Sample2</b>, 5 columns, 3 rows.<br><table><tr><th>'type'</th><th>'size'</th><th>'weight'</th><th>'tel'</th><th>'cost'</th></tr><tr><td>'A'</td><td>2</td><td>12</td><td>'+79260360139'</td><td>3.1415</td></tr><tr><td>'B'</td><td>1</td><td>12</td><td>84505505151</td><td>2.7182</td></tr><tr><td>'D'</td><td>1</td><td>10</td><td>'31415926535'</td><td>1.2345</td></tr></table>"
      ],
      "text/plain": [
       "- Table object, title: \"Merged inner Sample1 Sample2\", 5 columns, 3 rows.\n",
       "'type' 'size' 'weight' 'tel' 'cost'\n",
       "'A' 2 12 '+79260360139' 3.1415\n",
       "'B' 1 12 84505505151 2.7182\n",
       "'D' 1 10 '31415926535' 1.2345"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = Table.merge(df1, df2, ['type', 'size'], 'inner')\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge tables left"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Merged left Sample1 Sample2</b>, 5 columns, 6 rows.<br><table><tr><th>'type'</th><th>'size'</th><th>'weight'</th><th>'tel'</th><th>'cost'</th></tr><tr><td>'A'</td><td>2</td><td>12</td><td>'+79260360139'</td><td>3.1415</td></tr><tr><td>'B'</td><td>1</td><td>12</td><td>84505505151</td><td>2.7182</td></tr><tr><td>'D'</td><td>1</td><td>10</td><td>'31415926535'</td><td>1.2345</td></tr><tr><td>'B'</td><td>2</td><td>14</td><td>''</td><td>None</td></tr><tr><td>'A'</td><td>1</td><td>15</td><td>'23816326412'</td><td>None</td></tr><tr><td>'D'</td><td>2</td><td>11</td><td>0</td><td>None</td></tr></table>"
      ],
      "text/plain": [
       "- Table object, title: \"Merged left Sample1 Sample2\", 5 columns, 6 rows.\n",
       "'type' 'size' 'weight' 'tel' 'cost'\n",
       "'A' 2 12 '+79260360139' 3.1415\n",
       "'B' 1 12 84505505151 2.7182\n",
       "'D' 1 10 '31415926535' 1.2345\n",
       "'B' 2 14 '' None\n",
       "'A' 1 15 '23816326412' None\n",
       "'D' 2 11 0 None"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = Table.merge(df1, df2, ['type', 'size'], 'left')\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge tables right"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Merged right Sample1 Sample2</b>, 5 columns, 4 rows.<br><table><tr><th>'type'</th><th>'size'</th><th>'weight'</th><th>'tel'</th><th>'cost'</th></tr><tr><td>'A'</td><td>2</td><td>12</td><td>'+79260360139'</td><td>3.1415</td></tr><tr><td>'B'</td><td>1</td><td>12</td><td>84505505151</td><td>2.7182</td></tr><tr><td>'D'</td><td>1</td><td>10</td><td>'31415926535'</td><td>1.2345</td></tr><tr><td>'D'</td><td>1</td><td>None</td><td>None</td><td>1.2345</td></tr></table>"
      ],
      "text/plain": [
       "- Table object, title: \"Merged right Sample1 Sample2\", 5 columns, 4 rows.\n",
       "'type' 'size' 'weight' 'tel' 'cost'\n",
       "'A' 2 12 '+79260360139' 3.1415\n",
       "'B' 1 12 84505505151 2.7182\n",
       "'D' 1 10 '31415926535' 1.2345\n",
       "'D' 1 None None 1.2345"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = Table.merge(df1, df2, ['type', 'size'], 'right')\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge tables outer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In addition, you can provide a value that will be used for empty cells."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Merged outer Sample1 Sample2</b>, 5 columns, 7 rows.<br><table><tr><th>'type'</th><th>'size'</th><th>'weight'</th><th>'tel'</th><th>'cost'</th></tr><tr><td>'A'</td><td>2</td><td>12</td><td>'+79260360139'</td><td>3.1415</td></tr><tr><td>'B'</td><td>1</td><td>12</td><td>84505505151</td><td>2.7182</td></tr><tr><td>'D'</td><td>1</td><td>10</td><td>'31415926535'</td><td>1.2345</td></tr><tr><td>'B'</td><td>2</td><td>14</td><td>''</td><td>0</td></tr><tr><td>'A'</td><td>1</td><td>15</td><td>'23816326412'</td><td>0</td></tr><tr><td>'D'</td><td>2</td><td>11</td><td>0</td><td>0</td></tr><tr><td>'D'</td><td>1</td><td>0</td><td>0</td><td>1.2345</td></tr></table>"
      ],
      "text/plain": [
       "- Table object, title: \"Merged outer Sample1 Sample2\", 5 columns, 7 rows.\n",
       "'type' 'size' 'weight' 'tel' 'cost'\n",
       "'A' 2 12 '+79260360139' 3.1415\n",
       "'B' 1 12 84505505151 2.7182\n",
       "'D' 1 10 '31415926535' 1.2345\n",
       "'B' 2 14 '' 0\n",
       "'A' 1 15 '23816326412' 0\n",
       "'D' 2 11 0 0\n",
       "'D' 1 0 0 1.2345"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = Table.merge(df1, df2, ['type', 'size'], how='outer', empty=0)\n",
    "df3"
   ]
  }
 ],
 "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}