vaex/vaex-joins.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9416689c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import vaex\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fdde7ab3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead>\n",
       "<tr><th>#                            </th><th>a  </th><th style=\"text-align: right;\">  x</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr><td><i style='opacity: 0.6'>0</i></td><td>a  </td><td style=\"text-align: right;\">  1</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>1</i></td><td>b  </td><td style=\"text-align: right;\">  2</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>2</i></td><td>c  </td><td style=\"text-align: right;\">  3</td></tr>\n",
       "</tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "  #  a      x\n",
       "  0  a      1\n",
       "  1  b      2\n",
       "  2  c      3"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = np.array(['a', 'b', 'c'])\n",
    "x = np.array([1, 2, 3])\n",
    "\n",
    "df1 = vaex.from_arrays(a=a, x=x)\n",
    "\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "585a93db",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead>\n",
       "<tr><th>#                            </th><th>b  </th><th style=\"text-align: right;\">  y</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr><td><i style='opacity: 0.6'>0</i></td><td>a  </td><td style=\"text-align: right;\">  4</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>1</i></td><td>b  </td><td style=\"text-align: right;\">  5</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>2</i></td><td>d  </td><td style=\"text-align: right;\">  6</td></tr>\n",
       "</tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "  #  b      y\n",
       "  0  a      4\n",
       "  1  b      5\n",
       "  2  d      6"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "b = np.array(['a', 'b', 'd'])\n",
    "y = np.array([4, 5, 6])\n",
    "\n",
    "df2 = vaex.from_arrays(b=b, y=y)\n",
    "\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa9f1fb8",
   "metadata": {},
   "source": [
    "The default join, is a **left** join, where all rows for the left DataFrame (`df1`) are kept, and matching rows of the right DataFrame (`df2`) are added.\n",
    "We see that for the columns `b` and `y`, some values are missing, as expected."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1347e99f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead>\n",
       "<tr><th>#                            </th><th>a  </th><th style=\"text-align: right;\">  x</th><th>b  </th><th>y  </th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr><td><i style='opacity: 0.6'>0</i></td><td>a  </td><td style=\"text-align: right;\">  1</td><td>a  </td><td>4  </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>1</i></td><td>b  </td><td style=\"text-align: right;\">  2</td><td>b  </td><td>5  </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>2</i></td><td>c  </td><td style=\"text-align: right;\">  3</td><td>-- </td><td>-- </td></tr>\n",
       "</tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "  #  a      x  b    y\n",
       "  0  a      1  a    4\n",
       "  1  b      2  b    5\n",
       "  2  c      3  --   --"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.join(df2, left_on='a', right_on='b')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "051f8e8e",
   "metadata": {},
   "source": [
    "A **right** join, is basically the same, but now the roles of the left and right label swapped, so now we have some values from columns `x` and `a` missing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "cc482cca",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead>\n",
       "<tr><th>#                            </th><th>b  </th><th style=\"text-align: right;\">  y</th><th>a  </th><th>x  </th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr><td><i style='opacity: 0.6'>0</i></td><td>a  </td><td style=\"text-align: right;\">  4</td><td>a  </td><td>1  </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>1</i></td><td>b  </td><td style=\"text-align: right;\">  5</td><td>b  </td><td>2  </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>2</i></td><td>d  </td><td style=\"text-align: right;\">  6</td><td>-- </td><td>-- </td></tr>\n",
       "</tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "  #  b      y  a    x\n",
       "  0  a      4  a    1\n",
       "  1  b      5  b    2\n",
       "  2  d      6  --   --"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.join(df2, left_on='a', right_on='b', how='right')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76b90049",
   "metadata": {},
   "source": [
    "We can also do **inner** join, in which the output DataFrame has only the rows common between `df1` and `df2`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6e757dd4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead>\n",
       "<tr><th>#                            </th><th>a  </th><th style=\"text-align: right;\">  x</th><th>b  </th><th style=\"text-align: right;\">  y</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr><td><i style='opacity: 0.6'>0</i></td><td>a  </td><td style=\"text-align: right;\">  1</td><td>a  </td><td style=\"text-align: right;\">  4</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>1</i></td><td>b  </td><td style=\"text-align: right;\">  2</td><td>b  </td><td style=\"text-align: right;\">  5</td></tr>\n",
       "</tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "  #  a      x  b      y\n",
       "  0  a      1  a      4\n",
       "  1  b      2  b      5"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.join(df2, left_on='a', right_on='b', how='inner')"
   ]
  }
 ],
 "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}