Pandas Merge Tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas Merging Tutorial\n",
    "\n",
    "This tutorial is used in the blog post at www.shanelynn.ie to explain merging of dataframes in python pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Import\n",
    "import pandas as pd\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "user_usage = pd.read_csv(\"user_usage.csv\")\n",
    "user_device = pd.read_csv(\"user_device.csv\")\n",
    "devices = pd.read_csv(\"android_devices.csv\")\n",
    "devices.rename(columns={\"Retail Branding\": \"manufacturer\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>outgoing_mins_per_month</th>\n",
       "      <th>outgoing_sms_per_month</th>\n",
       "      <th>monthly_mb</th>\n",
       "      <th>use_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21.97</td>\n",
       "      <td>4.82</td>\n",
       "      <td>1557.33</td>\n",
       "      <td>22787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1710.08</td>\n",
       "      <td>136.88</td>\n",
       "      <td>7267.55</td>\n",
       "      <td>22788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1710.08</td>\n",
       "      <td>136.88</td>\n",
       "      <td>7267.55</td>\n",
       "      <td>22789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>94.46</td>\n",
       "      <td>35.17</td>\n",
       "      <td>519.12</td>\n",
       "      <td>22790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>71.59</td>\n",
       "      <td>79.26</td>\n",
       "      <td>1557.33</td>\n",
       "      <td>22792</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   outgoing_mins_per_month  outgoing_sms_per_month  monthly_mb  use_id\n",
       "0                    21.97                    4.82     1557.33   22787\n",
       "1                  1710.08                  136.88     7267.55   22788\n",
       "2                  1710.08                  136.88     7267.55   22789\n",
       "3                    94.46                   35.17      519.12   22790\n",
       "4                    71.59                   79.26     1557.33   22792"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_usage.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>use_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>platform</th>\n",
       "      <th>platform_version</th>\n",
       "      <th>device</th>\n",
       "      <th>use_type_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22782</td>\n",
       "      <td>26980</td>\n",
       "      <td>ios</td>\n",
       "      <td>10.2</td>\n",
       "      <td>iPhone7,2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22783</td>\n",
       "      <td>29628</td>\n",
       "      <td>android</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Nexus 5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22784</td>\n",
       "      <td>28473</td>\n",
       "      <td>android</td>\n",
       "      <td>5.1</td>\n",
       "      <td>SM-G903F</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22785</td>\n",
       "      <td>15200</td>\n",
       "      <td>ios</td>\n",
       "      <td>10.2</td>\n",
       "      <td>iPhone7,2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22786</td>\n",
       "      <td>28239</td>\n",
       "      <td>android</td>\n",
       "      <td>6.0</td>\n",
       "      <td>ONE E1003</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   use_id  user_id platform  platform_version     device  use_type_id\n",
       "0   22782    26980      ios              10.2  iPhone7,2            2\n",
       "1   22783    29628  android               6.0    Nexus 5            3\n",
       "2   22784    28473  android               5.1   SM-G903F            1\n",
       "3   22785    15200      ios              10.2  iPhone7,2            3\n",
       "4   22786    28239  android               6.0  ONE E1003            1"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_device.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Retail Branding</th>\n",
       "      <th>Marketing Name</th>\n",
       "      <th>Device</th>\n",
       "      <th>Model</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AD681H</td>\n",
       "      <td>Smartfren Andromax AD681H</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FJL21</td>\n",
       "      <td>FJL21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>T31</td>\n",
       "      <td>Panasonic T31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>hws7721g</td>\n",
       "      <td>MediaPad 7 Youth 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3Q</td>\n",
       "      <td>OC1020A</td>\n",
       "      <td>OC1020A</td>\n",
       "      <td>OC1020A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7Eleven</td>\n",
       "      <td>IN265</td>\n",
       "      <td>IN265</td>\n",
       "      <td>IN265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A.O.I. ELECTRONICS FACTORY</td>\n",
       "      <td>A.O.I.</td>\n",
       "      <td>TR10CS1_11</td>\n",
       "      <td>TR10CS1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AG Mobile</td>\n",
       "      <td>AG BOOST 2</td>\n",
       "      <td>BOOST2</td>\n",
       "      <td>E4010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AG Mobile</td>\n",
       "      <td>AG Flair</td>\n",
       "      <td>AG_Flair</td>\n",
       "      <td>Flair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AG Mobile</td>\n",
       "      <td>AG Go Tab Access 2</td>\n",
       "      <td>AG_Go_Tab_Access_2</td>\n",
       "      <td>AG_Go_Tab_Access_2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Retail Branding      Marketing Name              Device  \\\n",
       "0                         NaN                 NaN              AD681H   \n",
       "1                         NaN                 NaN               FJL21   \n",
       "2                         NaN                 NaN                 T31   \n",
       "3                         NaN                 NaN            hws7721g   \n",
       "4                          3Q             OC1020A             OC1020A   \n",
       "5                     7Eleven               IN265               IN265   \n",
       "6  A.O.I. ELECTRONICS FACTORY              A.O.I.          TR10CS1_11   \n",
       "7                   AG Mobile          AG BOOST 2              BOOST2   \n",
       "8                   AG Mobile            AG Flair            AG_Flair   \n",
       "9                   AG Mobile  AG Go Tab Access 2  AG_Go_Tab_Access_2   \n",
       "\n",
       "                       Model  \n",
       "0  Smartfren Andromax AD681H  \n",
       "1                      FJL21  \n",
       "2              Panasonic T31  \n",
       "3         MediaPad 7 Youth 2  \n",
       "4                    OC1020A  \n",
       "5                      IN265  \n",
       "6                    TR10CS1  \n",
       "7                      E4010  \n",
       "8                      Flair  \n",
       "9         AG_Go_Tab_Access_2  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "devices.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## First merge\n",
    "\n",
    "We're trying to get the average usage figures for different types of devices. So we need to get the user's device code from user_usage as a column on user_usage, and then get the device's manufacturer from devices as a column on the result.\n",
    "\n",
    "First, we merge user_usage with user_device with \"use_id\" as our common column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>outgoing_mins_per_month</th>\n",
       "      <th>outgoing_sms_per_month</th>\n",
       "      <th>monthly_mb</th>\n",
       "      <th>use_id</th>\n",
       "      <th>platform</th>\n",
       "      <th>device</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21.97</td>\n",
       "      <td>4.82</td>\n",
       "      <td>1557.33</td>\n",
       "      <td>22787</td>\n",
       "      <td>android</td>\n",
       "      <td>GT-I9505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1710.08</td>\n",
       "      <td>136.88</td>\n",
       "      <td>7267.55</td>\n",
       "      <td>22788</td>\n",
       "      <td>android</td>\n",
       "      <td>SM-G930F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1710.08</td>\n",
       "      <td>136.88</td>\n",
       "      <td>7267.55</td>\n",
       "      <td>22789</td>\n",
       "      <td>android</td>\n",
       "      <td>SM-G930F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>94.46</td>\n",
       "      <td>35.17</td>\n",
       "      <td>519.12</td>\n",
       "      <td>22790</td>\n",
       "      <td>android</td>\n",
       "      <td>D2303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>71.59</td>\n",
       "      <td>79.26</td>\n",
       "      <td>1557.33</td>\n",
       "      <td>22792</td>\n",
       "      <td>android</td>\n",
       "      <td>SM-G361F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   outgoing_mins_per_month  outgoing_sms_per_month  monthly_mb  use_id  \\\n",
       "0                    21.97                    4.82     1557.33   22787   \n",
       "1                  1710.08                  136.88     7267.55   22788   \n",
       "2                  1710.08                  136.88     7267.55   22789   \n",
       "3                    94.46                   35.17      519.12   22790   \n",
       "4                    71.59                   79.26     1557.33   22792   \n",
       "\n",
       "  platform    device  \n",
       "0  android  GT-I9505  \n",
       "1  android  SM-G930F  \n",
       "2  android  SM-G930F  \n",
       "3  android     D2303  \n",
       "4  android  SM-G361F  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.merge(user_usage,\n",
    "                 user_device[['use_id', 'platform', 'device']],\n",
    "                 on='use_id')\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "While on first glance, this may seem perfect, all is not as it seems."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "user_usage dimensions: (240, 4)\n",
      "user_device dimensions: (272, 3)\n",
      "result dimensions: (159, 6)\n"
     ]
    }
   ],
   "source": [
    "print(\"user_usage dimensions: {}\".format(user_usage.shape))\n",
    "print(\"user_device dimensions: {}\".format(user_device[['use_id', 'platform', 'device']].shape))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True     159\n",
       "False     81\n",
       "Name: use_id, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_usage['use_id'].isin(user_device['use_id']).value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Left merge example\n",
    "\n",
    "A left merge, or left join, between two dataframes keeps all of the rows and values from the left dataframe, in this case \"user_usage\". Rows from the right dataframe will be kept in the result only where there is a match in the merge variable in the right dataframe, and NaN values will be in the result where not."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "user_usage dimensions: (240, 4)\n",
      "result dimensions: (240, 6)\n",
      "There are 81 missing values in the result.\n"
     ]
    }
   ],
   "source": [
    "result = pd.merge(user_usage,\n",
    "                 user_device[['use_id', 'platform', 'device']],\n",
    "                 on='use_id', how='left')\n",
    "print(\"user_usage dimensions: {}\".format(user_usage.shape))\n",
    "print(\"result dimensions: {}\".format(result.shape))\n",
    "print(\"There are {} missing values in the result.\".format(\n",
    "        result['device'].isnull().sum()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>outgoing_mins_per_month</th>\n",
       "      <th>outgoing_sms_per_month</th>\n",
       "      <th>monthly_mb</th>\n",
       "      <th>use_id</th>\n",
       "      <th>platform</th>\n",
       "      <th>device</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21.97</td>\n",
       "      <td>4.82</td>\n",
       "      <td>1557.33</td>\n",
       "      <td>22787</td>\n",
       "      <td>android</td>\n",
       "      <td>GT-I9505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1710.08</td>\n",
       "      <td>136.88</td>\n",
       "      <td>7267.55</td>\n",
       "      <td>22788</td>\n",
       "      <td>android</td>\n",
       "      <td>SM-G930F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1710.08</td>\n",
       "      <td>136.88</td>\n",
       "      <td>7267.55</td>\n",
       "      <td>22789</td>\n",
       "      <td>android</td>\n",
       "      <td>SM-G930F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>94.46</td>\n",
       "      <td>35.17</td>\n",
       "      <td>519.12</td>\n",
       "      <td>22790</td>\n",
       "      <td>android</td>\n",
       "      <td>D2303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>71.59</td>\n",
       "      <td>79.26</td>\n",
       "      <td>1557.33</td>\n",
       "      <td>22792</td>\n",
       "      <td>android</td>\n",
       "      <td>SM-G361F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   outgoing_mins_per_month  outgoing_sms_per_month  monthly_mb  use_id  \\\n",
       "0                    21.97                    4.82     1557.33   22787   \n",
       "1                  1710.08                  136.88     7267.55   22788   \n",
       "2                  1710.08                  136.88     7267.55   22789   \n",
       "3                    94.46                   35.17      519.12   22790   \n",
       "4                    71.59                   79.26     1557.33   22792   \n",
       "\n",
       "  platform    device  \n",
       "0  android  GT-I9505  \n",
       "1  android  SM-G930F  \n",
       "2  android  SM-G930F  \n",
       "3  android     D2303  \n",
       "4  android  SM-G361F  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>outgoing_mins_per_month</th>\n",
       "      <th>outgoing_sms_per_month</th>\n",
       "      <th>monthly_mb</th>\n",
       "      <th>use_id</th>\n",
       "      <th>platform</th>\n",
       "      <th>device</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>235</th>\n",
       "      <td>260.66</td>\n",
       "      <td>68.44</td>\n",
       "      <td>896.96</td>\n",
       "      <td>25008</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>236</th>\n",
       "      <td>97.12</td>\n",
       "      <td>36.50</td>\n",
       "      <td>2815.00</td>\n",
       "      <td>25040</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237</th>\n",
       "      <td>355.93</td>\n",
       "      <td>12.37</td>\n",
       "      <td>6828.09</td>\n",
       "      <td>25046</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>238</th>\n",
       "      <td>632.06</td>\n",
       "      <td>120.46</td>\n",
       "      <td>1453.16</td>\n",
       "      <td>25058</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239</th>\n",
       "      <td>488.70</td>\n",
       "      <td>906.92</td>\n",
       "      <td>3089.85</td>\n",
       "      <td>25220</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     outgoing_mins_per_month  outgoing_sms_per_month  monthly_mb  use_id  \\\n",
       "235                   260.66                   68.44      896.96   25008   \n",
       "236                    97.12                   36.50     2815.00   25040   \n",
       "237                   355.93                   12.37     6828.09   25046   \n",
       "238                   632.06                  120.46     1453.16   25058   \n",
       "239                   488.70                  906.92     3089.85   25220   \n",
       "\n",
       "    platform device  \n",
       "235      NaN    NaN  \n",
       "236      NaN    NaN  \n",
       "237      NaN    NaN  \n",
       "238      NaN    NaN  \n",
       "239      NaN    NaN  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Right merge example\n",
    "\n",
    "A right merge, or right join, between two dataframes keeps all of the rows and values from the right dataframe, in this case \"user_device\". Rows from the left dataframe will be kept where there is a match in the merge variable, and NaN values will be in the result where not."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "user_device dimensions: (272, 6)\n",
      "result dimensions: (272, 6)\n",
      "There are 113 missing values in the 'monthly_mb' column in the result.\n",
      "There are 0 missing values in the 'platform' column in the result.\n"
     ]
    }
   ],
   "source": [
    "result = pd.merge(user_usage,\n",
    "                 user_device[['use_id', 'platform', 'device']],\n",
    "                 on='use_id', how='right')\n",
    "print(\"user_device dimensions: {}\".format(user_device.shape))\n",
    "print(\"result dimensions: {}\".format(result.shape))\n",
    "print(\"There are {} missing values in the 'monthly_mb' column in the result.\".format(\n",
    "        result['monthly_mb'].isnull().sum()))\n",
    "print(\"There are {} missing values in the 'platform' column in the result.\".format(\n",
    "        result['platform'].isnull().sum()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Outer merge example\n",
    "\n",
    "A full outer join, or outer merge, keeps all rows from the left and right dataframe in the result. Rows will be aligned where there is shared join values between the left and right, and rows with NaN values, in either the left-originating or right-originating columns will be, will be left in the result where there is no shared join value.\n",
    "\n",
    "In the final result, a subset of rows should have no missing values. These rows are the rows where there was a match between the merge column in the left and right dataframes. These rows are the same values as found by our inner merge result before."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 353 unique values of use_id in our dataframes.\n",
      "Outer merge result has (353, 7) rows.\n",
      "There are 159 rows with no missing values.\n"
     ]
    }
   ],
   "source": [
    "print(\"There are {} unique values of use_id in our dataframes.\".format(\n",
    "        pd.concat([user_usage['use_id'], user_device['use_id']]).unique().shape[0]))\n",
    "result = pd.merge(user_usage,\n",
    "                 user_device[['use_id', 'platform', 'device']],\n",
    "                 on='use_id', how='outer', indicator=True)\n",
    "\n",
    "print(\"Outer merge result has {} rows.\".format(result.shape))\n",
    "\n",
    "print(\"There are {} rows with no missing values.\".format(\n",
    "    (result.apply(lambda x: x.isnull().sum(), axis=1) == 0).sum()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>outgoing_mins_per_month</th>\n",
       "      <th>outgoing_sms_per_month</th>\n",
       "      <th>monthly_mb</th>\n",
       "      <th>use_id</th>\n",
       "      <th>platform</th>\n",
       "      <th>device</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21.97</td>\n",
       "      <td>4.82</td>\n",
       "      <td>1557.33</td>\n",
       "      <td>22787</td>\n",
       "      <td>android</td>\n",
       "      <td>GT-I9505</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1710.08</td>\n",
       "      <td>136.88</td>\n",
       "      <td>7267.55</td>\n",
       "      <td>22788</td>\n",
       "      <td>android</td>\n",
       "      <td>SM-G930F</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>28.79</td>\n",
       "      <td>29.42</td>\n",
       "      <td>3114.67</td>\n",
       "      <td>23988</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>616.56</td>\n",
       "      <td>99.85</td>\n",
       "      <td>5414.14</td>\n",
       "      <td>24006</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>350</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23050</td>\n",
       "      <td>ios</td>\n",
       "      <td>iPhone7,2</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>351</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23051</td>\n",
       "      <td>ios</td>\n",
       "      <td>iPhone7,2</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     outgoing_mins_per_month  outgoing_sms_per_month  monthly_mb  use_id  \\\n",
       "0                      21.97                    4.82     1557.33   22787   \n",
       "1                    1710.08                  136.88     7267.55   22788   \n",
       "200                    28.79                   29.42     3114.67   23988   \n",
       "201                   616.56                   99.85     5414.14   24006   \n",
       "350                      NaN                     NaN         NaN   23050   \n",
       "351                      NaN                     NaN         NaN   23051   \n",
       "\n",
       "    platform     device      _merge  \n",
       "0    android   GT-I9505        both  \n",
       "1    android   SM-G930F        both  \n",
       "200      NaN        NaN   left_only  \n",
       "201      NaN        NaN   left_only  \n",
       "350      ios  iPhone7,2  right_only  \n",
       "351      ios  iPhone7,2  right_only  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.iloc[[0, 1, 200,201, 350,351]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Final merge - adding device manufacturer\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>outgoing_mins_per_month</th>\n",
       "      <th>outgoing_sms_per_month</th>\n",
       "      <th>monthly_mb</th>\n",
       "      <th>use_id</th>\n",
       "      <th>platform</th>\n",
       "      <th>device</th>\n",
       "      <th>manufacturer</th>\n",
       "      <th>Model</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21.97</td>\n",
       "      <td>4.82</td>\n",
       "      <td>1557.33</td>\n",
       "      <td>22787</td>\n",
       "      <td>android</td>\n",
       "      <td>GT-I9505</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>GT-I9505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1710.08</td>\n",
       "      <td>136.88</td>\n",
       "      <td>7267.55</td>\n",
       "      <td>22788</td>\n",
       "      <td>android</td>\n",
       "      <td>SM-G930F</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>SM-G930F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1710.08</td>\n",
       "      <td>136.88</td>\n",
       "      <td>7267.55</td>\n",
       "      <td>22789</td>\n",
       "      <td>android</td>\n",
       "      <td>SM-G930F</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>SM-G930F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>94.46</td>\n",
       "      <td>35.17</td>\n",
       "      <td>519.12</td>\n",
       "      <td>22790</td>\n",
       "      <td>android</td>\n",
       "      <td>D2303</td>\n",
       "      <td>Sony</td>\n",
       "      <td>D2303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>71.59</td>\n",
       "      <td>79.26</td>\n",
       "      <td>1557.33</td>\n",
       "      <td>22792</td>\n",
       "      <td>android</td>\n",
       "      <td>SM-G361F</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>SM-G361F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   outgoing_mins_per_month  outgoing_sms_per_month  monthly_mb  use_id  \\\n",
       "0                    21.97                    4.82     1557.33   22787   \n",
       "1                  1710.08                  136.88     7267.55   22788   \n",
       "2                  1710.08                  136.88     7267.55   22789   \n",
       "3                    94.46                   35.17      519.12   22790   \n",
       "4                    71.59                   79.26     1557.33   22792   \n",
       "\n",
       "  platform    device manufacturer     Model  \n",
       "0  android  GT-I9505      Samsung  GT-I9505  \n",
       "1  android  SM-G930F      Samsung  SM-G930F  \n",
       "2  android  SM-G930F      Samsung  SM-G930F  \n",
       "3  android     D2303         Sony     D2303  \n",
       "4  android  SM-G361F      Samsung  SM-G361F  "
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# First, add the platform and device to the user usage.\n",
    "result = pd.merge(user_usage,\n",
    "                 user_device[['use_id', 'platform', 'device']],\n",
    "                 on='use_id',\n",
    "                 how='left')\n",
    "\n",
    "# Now, based on the \"device\" column in result, match the \"Model\" column in devices.\n",
    "devices.rename(columns={\"Retail Branding\": \"manufacturer\"}, inplace=True)\n",
    "result = pd.merge(result, \n",
    "                  devices[['manufacturer', 'Model']],\n",
    "                  left_on='device',\n",
    "                  right_on='Model',\n",
    "                  how='left')\n",
    "\n",
    "result.head()\n",
    "                  \n",
    "                               "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Retail Branding</th>\n",
       "      <th>Marketing Name</th>\n",
       "      <th>Device</th>\n",
       "      <th>Model</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10381</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy S7</td>\n",
       "      <td>herolte</td>\n",
       "      <td>SM-G930F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Retail Branding Marketing Name   Device     Model\n",
       "10381         Samsung      Galaxy S7  herolte  SM-G930F"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "devices[devices.Model == 'SM-G930F']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Retail Branding</th>\n",
       "      <th>Marketing Name</th>\n",
       "      <th>Device</th>\n",
       "      <th>Model</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1095</th>\n",
       "      <td>Bitmore</td>\n",
       "      <td>GTAB700</td>\n",
       "      <td>GTAB700</td>\n",
       "      <td>NID_7010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1096</th>\n",
       "      <td>Bitmore</td>\n",
       "      <td>GTAB900</td>\n",
       "      <td>GTAB900</td>\n",
       "      <td>S952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2402</th>\n",
       "      <td>Grundig</td>\n",
       "      <td>GTB1050</td>\n",
       "      <td>GTB1050</td>\n",
       "      <td>GTB 1050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2403</th>\n",
       "      <td>Grundig</td>\n",
       "      <td>GTB850</td>\n",
       "      <td>GTB850</td>\n",
       "      <td>GTB 850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2404</th>\n",
       "      <td>Grundig</td>\n",
       "      <td>TC69CA2</td>\n",
       "      <td>GTB801</td>\n",
       "      <td>GTB 801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9125</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GT-I5510M</td>\n",
       "      <td>GT-I5510M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9126</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GT-I5510T</td>\n",
       "      <td>GT-I5510T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9127</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GT-I5800L</td>\n",
       "      <td>GT-I5800L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9128</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GT-N7000B</td>\n",
       "      <td>GT-N7000B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9129</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GT-P7300B</td>\n",
       "      <td>GT-P7300B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9130</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GT-P7320T</td>\n",
       "      <td>GT-P7320T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9131</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GT-P7500M</td>\n",
       "      <td>GT-P7500M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9132</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GT-P7500R</td>\n",
       "      <td>GT-P7500R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9133</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GT-P7500V</td>\n",
       "      <td>GT-P7500V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9134</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GT-S5698</td>\n",
       "      <td>GT-S5698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9135</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GT-S5820</td>\n",
       "      <td>GT-S5820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9136</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GT-S5830V</td>\n",
       "      <td>GT-S5830V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9173</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Absolute</td>\n",
       "      <td>GT-B9120</td>\n",
       "      <td>GT-B9120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9184</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Europa</td>\n",
       "      <td>GT-I5500B</td>\n",
       "      <td>GT-I5500B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9185</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Europa</td>\n",
       "      <td>GT-I5500L</td>\n",
       "      <td>GT-I5500L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9186</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Europa</td>\n",
       "      <td>GT-I5500M</td>\n",
       "      <td>GT-I5500M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9187</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Europa</td>\n",
       "      <td>GT-I5503T</td>\n",
       "      <td>GT-I5503T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9188</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Europa</td>\n",
       "      <td>GT-I5510L</td>\n",
       "      <td>GT-I5510L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9191</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy (China)</td>\n",
       "      <td>GT-B9062</td>\n",
       "      <td>GT-B9062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9288</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Ace</td>\n",
       "      <td>GT-S5830</td>\n",
       "      <td>GT-S5830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9289</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Ace</td>\n",
       "      <td>GT-S5830B</td>\n",
       "      <td>GT-S5830B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9290</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Ace</td>\n",
       "      <td>GT-S5830C</td>\n",
       "      <td>GT-S5830C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9291</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Ace</td>\n",
       "      <td>GT-S5830D</td>\n",
       "      <td>GT-S5830D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9292</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Ace</td>\n",
       "      <td>GT-S5830F</td>\n",
       "      <td>GT-S5830F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9293</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Ace</td>\n",
       "      <td>GT-S5830G</td>\n",
       "      <td>GT-S5830G</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10480</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Tab 7.7</td>\n",
       "      <td>GT-P6800</td>\n",
       "      <td>GT-P6800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10481</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Tab 7.7</td>\n",
       "      <td>GT-P6810</td>\n",
       "      <td>GT-P6810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10484</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Tab 8.9</td>\n",
       "      <td>GT-P7300</td>\n",
       "      <td>GT-P7300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10485</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Tab 8.9</td>\n",
       "      <td>GT-P7310</td>\n",
       "      <td>GT-P7310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10486</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Tab 8.9</td>\n",
       "      <td>GT-P7320</td>\n",
       "      <td>GT-P7320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10778</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy W</td>\n",
       "      <td>GT-I8150</td>\n",
       "      <td>GT-I8150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10779</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy W</td>\n",
       "      <td>GT-I8150B</td>\n",
       "      <td>GT-I8150B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10780</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy W</td>\n",
       "      <td>GT-I8150T</td>\n",
       "      <td>GT-I8150T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10796</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Xcover</td>\n",
       "      <td>GT-S5690</td>\n",
       "      <td>GT-S5690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10797</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Xcover</td>\n",
       "      <td>GT-S5690L</td>\n",
       "      <td>GT-S5690L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10798</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Xcover</td>\n",
       "      <td>GT-S5690M</td>\n",
       "      <td>GT-S5690M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10799</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Xcover</td>\n",
       "      <td>GT-S5690R</td>\n",
       "      <td>GT-S5690R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10804</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y</td>\n",
       "      <td>GT-S5360</td>\n",
       "      <td>GT-S5360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10805</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y</td>\n",
       "      <td>GT-S5360B</td>\n",
       "      <td>GT-S5360B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10806</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y</td>\n",
       "      <td>GT-S5360L</td>\n",
       "      <td>GT-S5360L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10807</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y</td>\n",
       "      <td>GT-S5360T</td>\n",
       "      <td>GT-S5360T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10808</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y</td>\n",
       "      <td>GT-S5363</td>\n",
       "      <td>GT-S5363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10809</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y</td>\n",
       "      <td>GT-S5368</td>\n",
       "      <td>GT-S5368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10810</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y</td>\n",
       "      <td>GT-S5369</td>\n",
       "      <td>GT-S5369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10813</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y Duos</td>\n",
       "      <td>GT-S6102</td>\n",
       "      <td>GT-S6102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10814</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y Duos</td>\n",
       "      <td>GT-S6102B</td>\n",
       "      <td>GT-S6102B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10815</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y Duos</td>\n",
       "      <td>GT-S6102E</td>\n",
       "      <td>GT-S6102E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10818</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y Pop</td>\n",
       "      <td>GT-S6108</td>\n",
       "      <td>GT-S6108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10819</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y Pro</td>\n",
       "      <td>GT-B5510</td>\n",
       "      <td>GT-B5510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10820</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y Pro</td>\n",
       "      <td>GT-B5510B</td>\n",
       "      <td>GT-B5510B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10821</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y Pro</td>\n",
       "      <td>GT-B5510L</td>\n",
       "      <td>GT-B5510L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10822</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y Pro Duos</td>\n",
       "      <td>GT-B5512</td>\n",
       "      <td>GT-B5512</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10823</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y Pro Duos</td>\n",
       "      <td>GT-B5512B</td>\n",
       "      <td>GT-B5512B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10824</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>Galaxy Y TV</td>\n",
       "      <td>GT-S5367</td>\n",
       "      <td>GT-S5367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10979</th>\n",
       "      <td>Sharp</td>\n",
       "      <td>AQUOS SERIE mini SHV38</td>\n",
       "      <td>GTQ</td>\n",
       "      <td>SHV38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>164 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Retail Branding          Marketing Name     Device      Model\n",
       "1095          Bitmore                 GTAB700    GTAB700   NID_7010\n",
       "1096          Bitmore                 GTAB900    GTAB900       S952\n",
       "2402          Grundig                 GTB1050    GTB1050   GTB 1050\n",
       "2403          Grundig                  GTB850     GTB850    GTB 850\n",
       "2404          Grundig                 TC69CA2     GTB801    GTB 801\n",
       "9125          Samsung                     NaN  GT-I5510M  GT-I5510M\n",
       "9126          Samsung                     NaN  GT-I5510T  GT-I5510T\n",
       "9127          Samsung                     NaN  GT-I5800L  GT-I5800L\n",
       "9128          Samsung                     NaN  GT-N7000B  GT-N7000B\n",
       "9129          Samsung                     NaN  GT-P7300B  GT-P7300B\n",
       "9130          Samsung                     NaN  GT-P7320T  GT-P7320T\n",
       "9131          Samsung                     NaN  GT-P7500M  GT-P7500M\n",
       "9132          Samsung                     NaN  GT-P7500R  GT-P7500R\n",
       "9133          Samsung                     NaN  GT-P7500V  GT-P7500V\n",
       "9134          Samsung                     NaN   GT-S5698   GT-S5698\n",
       "9135          Samsung                     NaN   GT-S5820   GT-S5820\n",
       "9136          Samsung                     NaN  GT-S5830V  GT-S5830V\n",
       "9173          Samsung                Absolute   GT-B9120   GT-B9120\n",
       "9184          Samsung                  Europa  GT-I5500B  GT-I5500B\n",
       "9185          Samsung                  Europa  GT-I5500L  GT-I5500L\n",
       "9186          Samsung                  Europa  GT-I5500M  GT-I5500M\n",
       "9187          Samsung                  Europa  GT-I5503T  GT-I5503T\n",
       "9188          Samsung                  Europa  GT-I5510L  GT-I5510L\n",
       "9191          Samsung          Galaxy (China)   GT-B9062   GT-B9062\n",
       "9288          Samsung              Galaxy Ace   GT-S5830   GT-S5830\n",
       "9289          Samsung              Galaxy Ace  GT-S5830B  GT-S5830B\n",
       "9290          Samsung              Galaxy Ace  GT-S5830C  GT-S5830C\n",
       "9291          Samsung              Galaxy Ace  GT-S5830D  GT-S5830D\n",
       "9292          Samsung              Galaxy Ace  GT-S5830F  GT-S5830F\n",
       "9293          Samsung              Galaxy Ace  GT-S5830G  GT-S5830G\n",
       "...               ...                     ...        ...        ...\n",
       "10480         Samsung          Galaxy Tab 7.7   GT-P6800   GT-P6800\n",
       "10481         Samsung          Galaxy Tab 7.7   GT-P6810   GT-P6810\n",
       "10484         Samsung          Galaxy Tab 8.9   GT-P7300   GT-P7300\n",
       "10485         Samsung          Galaxy Tab 8.9   GT-P7310   GT-P7310\n",
       "10486         Samsung          Galaxy Tab 8.9   GT-P7320   GT-P7320\n",
       "10778         Samsung                Galaxy W   GT-I8150   GT-I8150\n",
       "10779         Samsung                Galaxy W  GT-I8150B  GT-I8150B\n",
       "10780         Samsung                Galaxy W  GT-I8150T  GT-I8150T\n",
       "10796         Samsung           Galaxy Xcover   GT-S5690   GT-S5690\n",
       "10797         Samsung           Galaxy Xcover  GT-S5690L  GT-S5690L\n",
       "10798         Samsung           Galaxy Xcover  GT-S5690M  GT-S5690M\n",
       "10799         Samsung           Galaxy Xcover  GT-S5690R  GT-S5690R\n",
       "10804         Samsung                Galaxy Y   GT-S5360   GT-S5360\n",
       "10805         Samsung                Galaxy Y  GT-S5360B  GT-S5360B\n",
       "10806         Samsung                Galaxy Y  GT-S5360L  GT-S5360L\n",
       "10807         Samsung                Galaxy Y  GT-S5360T  GT-S5360T\n",
       "10808         Samsung                Galaxy Y   GT-S5363   GT-S5363\n",
       "10809         Samsung                Galaxy Y   GT-S5368   GT-S5368\n",
       "10810         Samsung                Galaxy Y   GT-S5369   GT-S5369\n",
       "10813         Samsung           Galaxy Y Duos   GT-S6102   GT-S6102\n",
       "10814         Samsung           Galaxy Y Duos  GT-S6102B  GT-S6102B\n",
       "10815         Samsung           Galaxy Y Duos  GT-S6102E  GT-S6102E\n",
       "10818         Samsung            Galaxy Y Pop   GT-S6108   GT-S6108\n",
       "10819         Samsung            Galaxy Y Pro   GT-B5510   GT-B5510\n",
       "10820         Samsung            Galaxy Y Pro  GT-B5510B  GT-B5510B\n",
       "10821         Samsung            Galaxy Y Pro  GT-B5510L  GT-B5510L\n",
       "10822         Samsung       Galaxy Y Pro Duos   GT-B5512   GT-B5512\n",
       "10823         Samsung       Galaxy Y Pro Duos  GT-B5512B  GT-B5512B\n",
       "10824         Samsung             Galaxy Y TV   GT-S5367   GT-S5367\n",
       "10979           Sharp  AQUOS SERIE mini SHV38        GTQ      SHV38\n",
       "\n",
       "[164 rows x 4 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "devices[devices.Device.str.startswith('GT')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculating statistics on final result\n",
    "\n",
    "With merges complete, we can simply calculate statistics for users grouped by the manufacturer of their device."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>outgoing_mins_per_month</th>\n",
       "      <th>outgoing_sms_per_month</th>\n",
       "      <th>monthly_mb</th>\n",
       "      <th>use_id</th>\n",
       "      <th>platform</th>\n",
       "      <th>device</th>\n",
       "      <th>manufacturer</th>\n",
       "      <th>Model</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21.97</td>\n",
       "      <td>4.82</td>\n",
       "      <td>1557.33</td>\n",
       "      <td>22787</td>\n",
       "      <td>android</td>\n",
       "      <td>GT-I9505</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>GT-I9505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1710.08</td>\n",
       "      <td>136.88</td>\n",
       "      <td>7267.55</td>\n",
       "      <td>22788</td>\n",
       "      <td>android</td>\n",
       "      <td>SM-G930F</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>SM-G930F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1710.08</td>\n",
       "      <td>136.88</td>\n",
       "      <td>7267.55</td>\n",
       "      <td>22789</td>\n",
       "      <td>android</td>\n",
       "      <td>SM-G930F</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>SM-G930F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>94.46</td>\n",
       "      <td>35.17</td>\n",
       "      <td>519.12</td>\n",
       "      <td>22790</td>\n",
       "      <td>android</td>\n",
       "      <td>D2303</td>\n",
       "      <td>Sony</td>\n",
       "      <td>D2303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>71.59</td>\n",
       "      <td>79.26</td>\n",
       "      <td>1557.33</td>\n",
       "      <td>22792</td>\n",
       "      <td>android</td>\n",
       "      <td>SM-G361F</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>SM-G361F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   outgoing_mins_per_month  outgoing_sms_per_month  monthly_mb  use_id  \\\n",
       "0                    21.97                    4.82     1557.33   22787   \n",
       "1                  1710.08                  136.88     7267.55   22788   \n",
       "2                  1710.08                  136.88     7267.55   22789   \n",
       "3                    94.46                   35.17      519.12   22790   \n",
       "4                    71.59                   79.26     1557.33   22792   \n",
       "\n",
       "  platform    device manufacturer     Model  \n",
       "0  android  GT-I9505      Samsung  GT-I9505  \n",
       "1  android  SM-G930F      Samsung  SM-G930F  \n",
       "2  android  SM-G930F      Samsung  SM-G930F  \n",
       "3  android     D2303         Sony     D2303  \n",
       "4  android  SM-G361F      Samsung  SM-G361F  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>use_id</th>\n",
       "      <th>outgoing_mins_per_month</th>\n",
       "      <th>outgoing_sms_per_month</th>\n",
       "      <th>monthly_mb</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>manufacturer</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>HTC</th>\n",
       "      <td>44</td>\n",
       "      <td>299.842955</td>\n",
       "      <td>93.059318</td>\n",
       "      <td>5144.077955</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Huawei</th>\n",
       "      <td>3</td>\n",
       "      <td>81.526667</td>\n",
       "      <td>9.500000</td>\n",
       "      <td>1561.226667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LGE</th>\n",
       "      <td>2</td>\n",
       "      <td>111.530000</td>\n",
       "      <td>12.760000</td>\n",
       "      <td>1557.330000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lava</th>\n",
       "      <td>2</td>\n",
       "      <td>60.650000</td>\n",
       "      <td>261.900000</td>\n",
       "      <td>12458.670000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lenovo</th>\n",
       "      <td>2</td>\n",
       "      <td>215.920000</td>\n",
       "      <td>12.930000</td>\n",
       "      <td>1557.330000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Motorola</th>\n",
       "      <td>16</td>\n",
       "      <td>95.127500</td>\n",
       "      <td>65.666250</td>\n",
       "      <td>3946.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OnePlus</th>\n",
       "      <td>6</td>\n",
       "      <td>354.855000</td>\n",
       "      <td>48.330000</td>\n",
       "      <td>6575.410000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Samsung</th>\n",
       "      <td>108</td>\n",
       "      <td>191.010093</td>\n",
       "      <td>92.390463</td>\n",
       "      <td>4017.318889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sony</th>\n",
       "      <td>16</td>\n",
       "      <td>177.315625</td>\n",
       "      <td>40.176250</td>\n",
       "      <td>3212.000625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vodafone</th>\n",
       "      <td>1</td>\n",
       "      <td>42.750000</td>\n",
       "      <td>46.830000</td>\n",
       "      <td>5191.120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZTE</th>\n",
       "      <td>1</td>\n",
       "      <td>42.750000</td>\n",
       "      <td>46.830000</td>\n",
       "      <td>5191.120000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              use_id  outgoing_mins_per_month  outgoing_sms_per_month  \\\n",
       "manufacturer                                                            \n",
       "HTC               44               299.842955               93.059318   \n",
       "Huawei             3                81.526667                9.500000   \n",
       "LGE                2               111.530000               12.760000   \n",
       "Lava               2                60.650000              261.900000   \n",
       "Lenovo             2               215.920000               12.930000   \n",
       "Motorola          16                95.127500               65.666250   \n",
       "OnePlus            6               354.855000               48.330000   \n",
       "Samsung          108               191.010093               92.390463   \n",
       "Sony              16               177.315625               40.176250   \n",
       "Vodafone           1                42.750000               46.830000   \n",
       "ZTE                1                42.750000               46.830000   \n",
       "\n",
       "                monthly_mb  \n",
       "manufacturer                \n",
       "HTC            5144.077955  \n",
       "Huawei         1561.226667  \n",
       "LGE            1557.330000  \n",
       "Lava          12458.670000  \n",
       "Lenovo         1557.330000  \n",
       "Motorola       3946.500000  \n",
       "OnePlus        6575.410000  \n",
       "Samsung        4017.318889  \n",
       "Sony           3212.000625  \n",
       "Vodafone       5191.120000  \n",
       "ZTE            5191.120000  "
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.groupby(\"manufacturer\").agg({\n",
    "        \"outgoing_mins_per_month\": \"mean\",\n",
    "        \"outgoing_sms_per_month\": \"mean\",\n",
    "        \"monthly_mb\": \"mean\",\n",
    "        \"use_id\": \"count\"\n",
    "    })"
   ]
  }
 ],
 "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}