merge.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# setting file names\n",
    "FILE_MASTERS = 'data/masters.xlsx'\n",
    "FILE_INCOMING = 'data/incoming.xlsx'\n",
    "\n",
    "FILE_MASTERS_DUPS = 'data/masters_dups.xlsx'\n",
    "FILE_OUT = 'data/output.xlsx'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Read Masters File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Lname</th>\n",
       "      <th>Fname</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>Bogey</td>\n",
       "      <td>Hyberts</td>\n",
       "      <td>Bogey, Hyberts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100002</td>\n",
       "      <td>Mireielle</td>\n",
       "      <td>Lindauer</td>\n",
       "      <td>Mireielle, Lindauer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>100002</td>\n",
       "      <td>Mireielle</td>\n",
       "      <td>Lindauer</td>\n",
       "      <td>Mireielle, Lindauer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100003</td>\n",
       "      <td>Claiborn</td>\n",
       "      <td>Schacter</td>\n",
       "      <td>Claiborn, Schacter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100004</td>\n",
       "      <td>Hildagard</td>\n",
       "      <td>Eberhart</td>\n",
       "      <td>Hildagard, Eberhart</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100005</td>\n",
       "      <td>Welsh</td>\n",
       "      <td>Westerman</td>\n",
       "      <td>Welsh, Westerman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100006</td>\n",
       "      <td>Wrennie</td>\n",
       "      <td>Platt</td>\n",
       "      <td>Wrennie, Platt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>100006</td>\n",
       "      <td>Wrennie</td>\n",
       "      <td>Platt</td>\n",
       "      <td>Wrennie, Platt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>100007</td>\n",
       "      <td>Barris</td>\n",
       "      <td>Reynales</td>\n",
       "      <td>Barris, Reynales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>100008</td>\n",
       "      <td>Wendye</td>\n",
       "      <td>Cicinelli</td>\n",
       "      <td>Wendye, Cicinelli</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>100009</td>\n",
       "      <td>Ethe</td>\n",
       "      <td>Slasor</td>\n",
       "      <td>Ethe, Slasor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>100010</td>\n",
       "      <td>Valentine</td>\n",
       "      <td>Horsley</td>\n",
       "      <td>Valentine, Horsley</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        ID      Lname      Fname                 Name\n",
       "0   100001      Bogey    Hyberts       Bogey, Hyberts\n",
       "1   100002  Mireielle   Lindauer  Mireielle, Lindauer\n",
       "10  100002  Mireielle   Lindauer  Mireielle, Lindauer\n",
       "2   100003   Claiborn   Schacter   Claiborn, Schacter\n",
       "3   100004  Hildagard   Eberhart  Hildagard, Eberhart\n",
       "4   100005      Welsh  Westerman     Welsh, Westerman\n",
       "5   100006    Wrennie      Platt       Wrennie, Platt\n",
       "11  100006    Wrennie      Platt       Wrennie, Platt\n",
       "6   100007     Barris   Reynales     Barris, Reynales\n",
       "7   100008     Wendye  Cicinelli    Wendye, Cicinelli\n",
       "8   100009       Ethe     Slasor         Ethe, Slasor\n",
       "9   100010  Valentine    Horsley   Valentine, Horsley"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read spreadsheet\n",
    "df_masters = pd.read_excel(FILE_MASTERS, sheetname='input')\n",
    "\n",
    "# sort records by ascending IDs \n",
    "df_masters.sort_values('ID', ascending=True, inplace=True)\n",
    "\n",
    "# disaply dataframe\n",
    "# note 100002 and 100006 are duplicates\n",
    "df_masters"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Extract Duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Lname</th>\n",
       "      <th>Fname</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>100002</td>\n",
       "      <td>Mireielle</td>\n",
       "      <td>Lindauer</td>\n",
       "      <td>Mireielle, Lindauer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>100006</td>\n",
       "      <td>Wrennie</td>\n",
       "      <td>Platt</td>\n",
       "      <td>Wrennie, Platt</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        ID      Lname     Fname                 Name\n",
       "10  100002  Mireielle  Lindauer  Mireielle, Lindauer\n",
       "11  100006    Wrennie     Platt       Wrennie, Platt"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dups = df_masters[df_masters.duplicated(keep='first')]\n",
    "\n",
    "df_dups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# output duplicates to spreadsheet before deletion\n",
    "writer = pd.ExcelWriter(FILE_MASTERS_DUPS)\n",
    "df_dups.to_excel(writer, 'output')\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Remove Duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Lname</th>\n",
       "      <th>Fname</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>Bogey</td>\n",
       "      <td>Hyberts</td>\n",
       "      <td>Bogey, Hyberts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100002</td>\n",
       "      <td>Mireielle</td>\n",
       "      <td>Lindauer</td>\n",
       "      <td>Mireielle, Lindauer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100003</td>\n",
       "      <td>Claiborn</td>\n",
       "      <td>Schacter</td>\n",
       "      <td>Claiborn, Schacter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100004</td>\n",
       "      <td>Hildagard</td>\n",
       "      <td>Eberhart</td>\n",
       "      <td>Hildagard, Eberhart</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100005</td>\n",
       "      <td>Welsh</td>\n",
       "      <td>Westerman</td>\n",
       "      <td>Welsh, Westerman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100006</td>\n",
       "      <td>Wrennie</td>\n",
       "      <td>Platt</td>\n",
       "      <td>Wrennie, Platt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>100007</td>\n",
       "      <td>Barris</td>\n",
       "      <td>Reynales</td>\n",
       "      <td>Barris, Reynales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>100008</td>\n",
       "      <td>Wendye</td>\n",
       "      <td>Cicinelli</td>\n",
       "      <td>Wendye, Cicinelli</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>100009</td>\n",
       "      <td>Ethe</td>\n",
       "      <td>Slasor</td>\n",
       "      <td>Ethe, Slasor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>100010</td>\n",
       "      <td>Valentine</td>\n",
       "      <td>Horsley</td>\n",
       "      <td>Valentine, Horsley</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ID      Lname      Fname                 Name\n",
       "0  100001      Bogey    Hyberts       Bogey, Hyberts\n",
       "1  100002  Mireielle   Lindauer  Mireielle, Lindauer\n",
       "2  100003   Claiborn   Schacter   Claiborn, Schacter\n",
       "3  100004  Hildagard   Eberhart  Hildagard, Eberhart\n",
       "4  100005      Welsh  Westerman     Welsh, Westerman\n",
       "5  100006    Wrennie      Platt       Wrennie, Platt\n",
       "6  100007     Barris   Reynales     Barris, Reynales\n",
       "7  100008     Wendye  Cicinelli    Wendye, Cicinelli\n",
       "8  100009       Ethe     Slasor         Ethe, Slasor\n",
       "9  100010  Valentine    Horsley   Valentine, Horsley"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove duplicates of IDs except for the first occurence\n",
    "df_masters.drop_duplicates(subset='ID', keep='first', inplace=True)\n",
    "\n",
    "df_masters"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Read Secondary Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100002</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100003</td>\n",
       "      <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100008</td>\n",
       "      <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100009</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100010</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ID Country\n",
       "0  100001     USA\n",
       "1  100002     USA\n",
       "2  100003      UK\n",
       "3  100008      UK\n",
       "4  100009   China\n",
       "5  100010     USA"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Country info is stored in a second spreadsheet\n",
    "df_incoming = pd.read_excel(FILE_INCOMING, sheetname='input')\n",
    "\n",
    "df_incoming"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge (Join) Two Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Lname</th>\n",
       "      <th>Fname</th>\n",
       "      <th>Name</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>Bogey</td>\n",
       "      <td>Hyberts</td>\n",
       "      <td>Bogey, Hyberts</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100002</td>\n",
       "      <td>Mireielle</td>\n",
       "      <td>Lindauer</td>\n",
       "      <td>Mireielle, Lindauer</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100003</td>\n",
       "      <td>Claiborn</td>\n",
       "      <td>Schacter</td>\n",
       "      <td>Claiborn, Schacter</td>\n",
       "      <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100008</td>\n",
       "      <td>Wendye</td>\n",
       "      <td>Cicinelli</td>\n",
       "      <td>Wendye, Cicinelli</td>\n",
       "      <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100009</td>\n",
       "      <td>Ethe</td>\n",
       "      <td>Slasor</td>\n",
       "      <td>Ethe, Slasor</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100010</td>\n",
       "      <td>Valentine</td>\n",
       "      <td>Horsley</td>\n",
       "      <td>Valentine, Horsley</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ID      Lname      Fname                 Name Country\n",
       "0  100001      Bogey    Hyberts       Bogey, Hyberts     USA\n",
       "1  100002  Mireielle   Lindauer  Mireielle, Lindauer     USA\n",
       "2  100003   Claiborn   Schacter   Claiborn, Schacter      UK\n",
       "3  100008     Wendye  Cicinelli    Wendye, Cicinelli      UK\n",
       "4  100009       Ethe     Slasor         Ethe, Slasor   China\n",
       "5  100010  Valentine    Horsley   Valentine, Horsley     USA"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df_masters is on left, df_incoming is on right\n",
    "df_join_right = pd.merge(df_masters, df_incoming, how=\"right\", on=['ID'])\n",
    "\n",
    "df_join_right"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Lname</th>\n",
       "      <th>Fname</th>\n",
       "      <th>Name</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>Bogey</td>\n",
       "      <td>Hyberts</td>\n",
       "      <td>Bogey, Hyberts</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100002</td>\n",
       "      <td>Mireielle</td>\n",
       "      <td>Lindauer</td>\n",
       "      <td>Mireielle, Lindauer</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100003</td>\n",
       "      <td>Claiborn</td>\n",
       "      <td>Schacter</td>\n",
       "      <td>Claiborn, Schacter</td>\n",
       "      <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100004</td>\n",
       "      <td>Hildagard</td>\n",
       "      <td>Eberhart</td>\n",
       "      <td>Hildagard, Eberhart</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100005</td>\n",
       "      <td>Welsh</td>\n",
       "      <td>Westerman</td>\n",
       "      <td>Welsh, Westerman</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100006</td>\n",
       "      <td>Wrennie</td>\n",
       "      <td>Platt</td>\n",
       "      <td>Wrennie, Platt</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>100007</td>\n",
       "      <td>Barris</td>\n",
       "      <td>Reynales</td>\n",
       "      <td>Barris, Reynales</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>100008</td>\n",
       "      <td>Wendye</td>\n",
       "      <td>Cicinelli</td>\n",
       "      <td>Wendye, Cicinelli</td>\n",
       "      <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>100009</td>\n",
       "      <td>Ethe</td>\n",
       "      <td>Slasor</td>\n",
       "      <td>Ethe, Slasor</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>100010</td>\n",
       "      <td>Valentine</td>\n",
       "      <td>Horsley</td>\n",
       "      <td>Valentine, Horsley</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ID      Lname      Fname                 Name Country\n",
       "0  100001      Bogey    Hyberts       Bogey, Hyberts     USA\n",
       "1  100002  Mireielle   Lindauer  Mireielle, Lindauer     USA\n",
       "2  100003   Claiborn   Schacter   Claiborn, Schacter      UK\n",
       "3  100004  Hildagard   Eberhart  Hildagard, Eberhart     NaN\n",
       "4  100005      Welsh  Westerman     Welsh, Westerman     NaN\n",
       "5  100006    Wrennie      Platt       Wrennie, Platt     NaN\n",
       "6  100007     Barris   Reynales     Barris, Reynales     NaN\n",
       "7  100008     Wendye  Cicinelli    Wendye, Cicinelli      UK\n",
       "8  100009       Ethe     Slasor         Ethe, Slasor   China\n",
       "9  100010  Valentine    Horsley   Valentine, Horsley     USA"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df_masters is on left, df_incoming is on right\n",
    "df_join_left = pd.merge(df_masters, df_incoming, how=\"left\", on=['ID'])\n",
    "\n",
    "df_join_left"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Drop Column(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Lname</th>\n",
       "      <th>Fname</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>Bogey</td>\n",
       "      <td>Hyberts</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100002</td>\n",
       "      <td>Mireielle</td>\n",
       "      <td>Lindauer</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100003</td>\n",
       "      <td>Claiborn</td>\n",
       "      <td>Schacter</td>\n",
       "      <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100004</td>\n",
       "      <td>Hildagard</td>\n",
       "      <td>Eberhart</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100005</td>\n",
       "      <td>Welsh</td>\n",
       "      <td>Westerman</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100006</td>\n",
       "      <td>Wrennie</td>\n",
       "      <td>Platt</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>100007</td>\n",
       "      <td>Barris</td>\n",
       "      <td>Reynales</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>100008</td>\n",
       "      <td>Wendye</td>\n",
       "      <td>Cicinelli</td>\n",
       "      <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>100009</td>\n",
       "      <td>Ethe</td>\n",
       "      <td>Slasor</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>100010</td>\n",
       "      <td>Valentine</td>\n",
       "      <td>Horsley</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ID      Lname      Fname Country\n",
       "0  100001      Bogey    Hyberts     USA\n",
       "1  100002  Mireielle   Lindauer     USA\n",
       "2  100003   Claiborn   Schacter      UK\n",
       "3  100004  Hildagard   Eberhart     NaN\n",
       "4  100005      Welsh  Westerman     NaN\n",
       "5  100006    Wrennie      Platt     NaN\n",
       "6  100007     Barris   Reynales     NaN\n",
       "7  100008     Wendye  Cicinelli      UK\n",
       "8  100009       Ethe     Slasor   China\n",
       "9  100010  Valentine    Horsley     USA"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_join_left.drop(['Name'], axis=1, inplace=True)\n",
    "\n",
    "df_join_left"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Save Export Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "writer = pd.ExcelWriter(FILE_OUT)\n",
    "df_join_left.to_excel(writer, 'output')\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}