Merging DataFrames.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merging DataFrames\n",
    "\n",
    "```pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None,\n",
    "left_index=False, right_index=False, sort=True)```\n",
    "\n",
    "```merge``` and ```update``` have a lot in common.\n",
    "\n",
    "[There's a whole family of methods.](https://pandas.pydata.org/pandas-docs/stable/merging.html)\n",
    "\n",
    "I can understand where a busy IT worker, with the job of helping numerous IT workers, might get frustrated with the redundancy of it all.  How many times must we reinvent the same wheels?  That's the skeptic's view.\n",
    "\n",
    "The good news the same as the bad news:  redundancy means generic fluency is possible.  Some background in SQL helps with pandas.  Some background in pandas helps with SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfA = pd.DataFrame({'A':[1,1,1,1,1,1,1,1],\n",
    "                    'B':[2,2,2,2,2,2,2,2]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfB = pd.DataFrame({'C':[3,3,3,3,3,3,3,3],\n",
    "                    'D':[4,4,4,4,4,4,4,4]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You get to choose which columns, left and right, serve as \"gear teeth\" for synchronizing rows (sewing them together).  Or choose the index, not a column.\n",
    "\n",
    "In the expression below, we go with the one synchronizing element:  the index, on both input tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(dfA, dfB, left_index=True, right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import string\n",
    "dfA.index = list(string.ascii_lowercase[:8]) # new index, of letters instead"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfB.index = list(string.ascii_lowercase[5:8+5])  # overlapping letters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(dfA, dfB, left_index=True, right_index=True)  # intersection, not the union"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(dfA, dfB, left_index=True, right_index=True, how=\"left\") # left side governs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(dfA, dfB, left_index=True, right_index=True, how=\"right\") # right side governs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(dfA, dfB, left_index=True, right_index=True, how=\"outer\")  # the full union"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(dfA, dfB, left_index=True, right_index=True, how=\"inner\") # same as intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfA = pd.DataFrame({'A':[1,2,3,4,5,6,7,8],\n",
    "                    'B':[2,2,2,2,2,2,2,2],\n",
    "                    'key':['dog', 'pig', 'rooster', 'monkey', \n",
    "                           'hen', 'cat', 'slug', 'human']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from numpy.random import shuffle\n",
    "keys = dfA.key.values.copy()  # copy or dfA key will also reorder\n",
    "shuffle(keys)                 # in place\n",
    "dfB = pd.DataFrame({'C':[1,2,3,4,5,6,7,8],\n",
    "                    'D':[4,4,4,4,4,4,4,4],\n",
    "                    'key': keys})\n",
    "keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(dfA, dfB, on='key')  # like \"zipping together\" on a common column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfB.rename({\"C\":\"A\", \"D\":\"B\"}, axis=1, inplace = True)\n",
    "dfB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(dfA, dfB, on='key', sort=False) # sort on key if sort is True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(dfA, dfB, on='key', sort=True) # sort on key if sort is True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(dfA, dfB, on='A')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(dfA, dfB, left_index=True, right_on=\"A\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged = pd.merge(dfA, dfB, left_index=True, right_on=\"A\")\n",
    "merged.to_json(\"merged.json\")  # save for later"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### LAB CHALLENGE\n",
    "\n",
    "How might one simply swap the axes?  That's a hint."
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}