L1 - Pandas-1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas and SQL\n",
    "\n",
    "\n",
    "`Pandas` is a widely used python package for data analysis. We will mainly focus on `pandas.DataFrame`. Informally, you can think of a `dataframe` as an advanced relational table (or a spreadsheet in an Excel file). There, you can easily perform many useful tasks quickly, such as aggregate analysis, and data enrichment or selection. \n",
    "\n",
    "In this notebook, we focus on supporting common SQL operations on `dataframe`s. More documentation can be found at \n",
    "* \"Comparison with SQL\": http://pandas.pydata.org/pandas-docs/stable/comparison_with_sql.html\n",
    "* Dataframe/Pandas documentation: http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html\n",
    "\n",
    "We also use the `plotly` plotting library. You need to install it, register an account, and perform initialization (c.f., https://plot.ly/python/getting-started/), before the following code can work. Alternatively, just comment out `plotly` import below and the code that generates the plot. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import Modules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import plotly.plotly as py\n",
    "import plotly.graph_objs as go"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import data\n",
    "\n",
    "We can easily create a `DataFrame` from a CSV or a tab-delimited file. You can creata a `dataframe` object in other ways, e.g., from multiple `Series` or from a dictionary. \n",
    "\n",
    "Then we can \n",
    "* use `shape` to see the number of rows and columns\n",
    "* use `head(x)` or `tail(x)` to view the first or last `x` rows of the dataFrame. The default value of `x` is 5. \n",
    "* `pandas` is also smart in that it will intelligently print part of the dataframe content if it is too large. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>location</th>\n",
       "      <th>time</th>\n",
       "      <th>item</th>\n",
       "      <th>dollars_sold</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q1</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q2</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q3</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q4</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q1</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>1087</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    location time                item  dollars_sold\n",
       "0  Vancouver   Q1  home entertainment           605\n",
       "1  Vancouver   Q2  home entertainment           680\n",
       "2  Vancouver   Q3  home entertainment           812\n",
       "3  Vancouver   Q4  home entertainment           927\n",
       "4   New York   Q1  home entertainment          1087"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('./asset/lecture_data.txt', sep='\\t')  # to read an excel file, use read_excel()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>location</th>\n",
       "      <th>time</th>\n",
       "      <th>item</th>\n",
       "      <th>dollars_sold</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q1</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q2</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q3</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q4</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q1</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>1087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q2</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>1130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q3</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>1034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q4</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>1142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q1</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q2</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q3</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q4</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q1</td>\n",
       "      <td>computer</td>\n",
       "      <td>825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q2</td>\n",
       "      <td>computer</td>\n",
       "      <td>952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q3</td>\n",
       "      <td>computer</td>\n",
       "      <td>1023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q4</td>\n",
       "      <td>computer</td>\n",
       "      <td>1038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q1</td>\n",
       "      <td>computer</td>\n",
       "      <td>968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q2</td>\n",
       "      <td>computer</td>\n",
       "      <td>1024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q3</td>\n",
       "      <td>computer</td>\n",
       "      <td>1048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q4</td>\n",
       "      <td>computer</td>\n",
       "      <td>1091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q1</td>\n",
       "      <td>computer</td>\n",
       "      <td>746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q2</td>\n",
       "      <td>computer</td>\n",
       "      <td>769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q3</td>\n",
       "      <td>computer</td>\n",
       "      <td>795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q4</td>\n",
       "      <td>computer</td>\n",
       "      <td>864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q1</td>\n",
       "      <td>phone</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q2</td>\n",
       "      <td>phone</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q3</td>\n",
       "      <td>phone</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q4</td>\n",
       "      <td>phone</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q1</td>\n",
       "      <td>phone</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q2</td>\n",
       "      <td>phone</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q3</td>\n",
       "      <td>phone</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q4</td>\n",
       "      <td>phone</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q1</td>\n",
       "      <td>phone</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q2</td>\n",
       "      <td>phone</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q3</td>\n",
       "      <td>phone</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q4</td>\n",
       "      <td>phone</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q1</td>\n",
       "      <td>security</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q2</td>\n",
       "      <td>security</td>\n",
       "      <td>512</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q3</td>\n",
       "      <td>security</td>\n",
       "      <td>501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q4</td>\n",
       "      <td>security</td>\n",
       "      <td>580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q1</td>\n",
       "      <td>security</td>\n",
       "      <td>872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q2</td>\n",
       "      <td>security</td>\n",
       "      <td>925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q3</td>\n",
       "      <td>security</td>\n",
       "      <td>1002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q4</td>\n",
       "      <td>security</td>\n",
       "      <td>984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q1</td>\n",
       "      <td>security</td>\n",
       "      <td>591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q2</td>\n",
       "      <td>security</td>\n",
       "      <td>682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q3</td>\n",
       "      <td>security</td>\n",
       "      <td>728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q4</td>\n",
       "      <td>security</td>\n",
       "      <td>784</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     location time                item  dollars_sold\n",
       "0   Vancouver   Q1  home entertainment           605\n",
       "1   Vancouver   Q2  home entertainment           680\n",
       "2   Vancouver   Q3  home entertainment           812\n",
       "3   Vancouver   Q4  home entertainment           927\n",
       "4    New York   Q1  home entertainment          1087\n",
       "5    New York   Q2  home entertainment          1130\n",
       "6    New York   Q3  home entertainment          1034\n",
       "7    New York   Q4  home entertainment          1142\n",
       "8     Toronto   Q1  home entertainment           818\n",
       "9     Toronto   Q2  home entertainment           894\n",
       "10    Toronto   Q3  home entertainment           940\n",
       "11    Toronto   Q4  home entertainment           978\n",
       "12  Vancouver   Q1            computer           825\n",
       "13  Vancouver   Q2            computer           952\n",
       "14  Vancouver   Q3            computer          1023\n",
       "15  Vancouver   Q4            computer          1038\n",
       "16   New York   Q1            computer           968\n",
       "17   New York   Q2            computer          1024\n",
       "18   New York   Q3            computer          1048\n",
       "19   New York   Q4            computer          1091\n",
       "20    Toronto   Q1            computer           746\n",
       "21    Toronto   Q2            computer           769\n",
       "22    Toronto   Q3            computer           795\n",
       "23    Toronto   Q4            computer           864\n",
       "24  Vancouver   Q1               phone            14\n",
       "25  Vancouver   Q2               phone            31\n",
       "26  Vancouver   Q3               phone            30\n",
       "27  Vancouver   Q4               phone            38\n",
       "28   New York   Q1               phone            38\n",
       "29   New York   Q2               phone            41\n",
       "30   New York   Q3               phone            45\n",
       "31   New York   Q4               phone            54\n",
       "32    Toronto   Q1               phone            43\n",
       "33    Toronto   Q2               phone            52\n",
       "34    Toronto   Q3               phone            58\n",
       "35    Toronto   Q4               phone            59\n",
       "36  Vancouver   Q1            security           400\n",
       "37  Vancouver   Q2            security           512\n",
       "38  Vancouver   Q3            security           501\n",
       "39  Vancouver   Q4            security           580\n",
       "40   New York   Q1            security           872\n",
       "41   New York   Q2            security           925\n",
       "42   New York   Q3            security          1002\n",
       "43   New York   Q4            security           984\n",
       "44    Toronto   Q1            security           591\n",
       "45    Toronto   Q2            security           682\n",
       "46    Toronto   Q3            security           728\n",
       "47    Toronto   Q4            security           784"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>dollars_sold</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>48.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>651.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>392.167443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>14.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>314.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>789.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>970.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1142.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       dollars_sold\n",
       "count     48.000000\n",
       "mean     651.125000\n",
       "std      392.167443\n",
       "min       14.000000\n",
       "25%      314.750000\n",
       "50%      789.500000\n",
       "75%      970.500000\n",
       "max     1142.000000"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SELECT\n",
    "\n",
    "Columns can be identified by its index (0-based) or its name.   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Vancouver\n",
       "1    Vancouver\n",
       "2    Vancouver\n",
       "3    Vancouver\n",
       "4     New York\n",
       "Name: location, dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.location.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>location</th>\n",
       "      <th>dollars_sold</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>New York</td>\n",
       "      <td>1087</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    location  dollars_sold\n",
       "0  Vancouver           605\n",
       "1  Vancouver           680\n",
       "2  Vancouver           812\n",
       "3  Vancouver           927\n",
       "4   New York          1087"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# show location and dollars_sold\n",
    "df[[0, 3]].head()\n",
    "# df[['location','dollars_sold']].head() # also okay"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## WHERE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>location</th>\n",
       "      <th>time</th>\n",
       "      <th>item</th>\n",
       "      <th>dollars_sold</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q1</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q2</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q3</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q4</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q1</td>\n",
       "      <td>computer</td>\n",
       "      <td>825</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     location time                item  dollars_sold\n",
       "0   Vancouver   Q1  home entertainment           605\n",
       "1   Vancouver   Q2  home entertainment           680\n",
       "2   Vancouver   Q3  home entertainment           812\n",
       "3   Vancouver   Q4  home entertainment           927\n",
       "12  Vancouver   Q1            computer           825"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['location'] == 'Vancouver'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When dealing with multiple conditions, `pandas` uses `&` and `|`. Each condition must be bracketed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>location</th>\n",
       "      <th>time</th>\n",
       "      <th>item</th>\n",
       "      <th>dollars_sold</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q2</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q3</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q4</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q2</td>\n",
       "      <td>computer</td>\n",
       "      <td>952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q3</td>\n",
       "      <td>computer</td>\n",
       "      <td>1023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q4</td>\n",
       "      <td>computer</td>\n",
       "      <td>1038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q2</td>\n",
       "      <td>security</td>\n",
       "      <td>512</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q3</td>\n",
       "      <td>security</td>\n",
       "      <td>501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q4</td>\n",
       "      <td>security</td>\n",
       "      <td>580</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     location time                item  dollars_sold\n",
       "1   Vancouver   Q2  home entertainment           680\n",
       "2   Vancouver   Q3  home entertainment           812\n",
       "3   Vancouver   Q4  home entertainment           927\n",
       "13  Vancouver   Q2            computer           952\n",
       "14  Vancouver   Q3            computer          1023\n",
       "15  Vancouver   Q4            computer          1038\n",
       "37  Vancouver   Q2            security           512\n",
       "38  Vancouver   Q3            security           501\n",
       "39  Vancouver   Q4            security           580"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# location at Vancouver and dollars_sold more than 500, except the 1st quarter\n",
    "df[(df['location'] == 'Vancouver') & (df['time'] != 'Q1') & (df['dollars_sold'] > 500)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Under the hood, the conditions determines a Boolean array."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      True\n",
       "1     False\n",
       "2     False\n",
       "3     False\n",
       "4      True\n",
       "5     False\n",
       "6     False\n",
       "7     False\n",
       "8      True\n",
       "9     False\n",
       "10    False\n",
       "11    False\n",
       "12     True\n",
       "13    False\n",
       "14    False\n",
       "15    False\n",
       "16     True\n",
       "17    False\n",
       "18    False\n",
       "19    False\n",
       "20     True\n",
       "21    False\n",
       "22    False\n",
       "23    False\n",
       "24     True\n",
       "25    False\n",
       "26    False\n",
       "27    False\n",
       "28     True\n",
       "29    False\n",
       "30    False\n",
       "31    False\n",
       "32     True\n",
       "33    False\n",
       "34    False\n",
       "35    False\n",
       "36     True\n",
       "37    False\n",
       "38    False\n",
       "39    False\n",
       "40     True\n",
       "41    False\n",
       "42    False\n",
       "43    False\n",
       "44     True\n",
       "45    False\n",
       "46    False\n",
       "47    False\n",
       "Name: time, dtype: bool"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['time'] == 'Q1'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## GROUP BY\n",
    "\n",
    "In `pandas`, we uses `groupby()` to split a dataset into groups; we can apply some function (e.g., aggregation) and combine the groups together."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We use `size()` to return the number of rows of each group (like `COUNT` in SQL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "location\n",
       "New York     16\n",
       "Toronto      16\n",
       "Vancouver    16\n",
       "dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('location').size()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We use `agg()` to apply multiple functions at once, and pass a list of columns to `groupby()` to grouping multiple columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">dollars_sold</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>location</th>\n",
       "      <th>item</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">New York</th>\n",
       "      <th>computer</th>\n",
       "      <td>1032.75</td>\n",
       "      <td>4131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>home entertainment</th>\n",
       "      <td>1098.25</td>\n",
       "      <td>4393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>phone</th>\n",
       "      <td>44.50</td>\n",
       "      <td>178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>security</th>\n",
       "      <td>945.75</td>\n",
       "      <td>3783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Toronto</th>\n",
       "      <th>computer</th>\n",
       "      <td>793.50</td>\n",
       "      <td>3174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>home entertainment</th>\n",
       "      <td>907.50</td>\n",
       "      <td>3630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>phone</th>\n",
       "      <td>53.00</td>\n",
       "      <td>212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>security</th>\n",
       "      <td>696.25</td>\n",
       "      <td>2785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Vancouver</th>\n",
       "      <th>computer</th>\n",
       "      <td>959.50</td>\n",
       "      <td>3838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>home entertainment</th>\n",
       "      <td>756.00</td>\n",
       "      <td>3024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>phone</th>\n",
       "      <td>28.25</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>security</th>\n",
       "      <td>498.25</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             dollars_sold      \n",
       "                                     mean   sum\n",
       "location  item                                 \n",
       "New York  computer                1032.75  4131\n",
       "          home entertainment      1098.25  4393\n",
       "          phone                     44.50   178\n",
       "          security                 945.75  3783\n",
       "Toronto   computer                 793.50  3174\n",
       "          home entertainment       907.50  3630\n",
       "          phone                     53.00   212\n",
       "          security                 696.25  2785\n",
       "Vancouver computer                 959.50  3838\n",
       "          home entertainment       756.00  3024\n",
       "          phone                     28.25   113\n",
       "          security                 498.25  1993"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['location','item']).agg({'dollars_sold': [np.mean,np.sum]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## JOIN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>location</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>New York</td>\n",
       "      <td>8406000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>630500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Sydney</td>\n",
       "      <td>4293000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    location  population\n",
       "0   New York     8406000\n",
       "1  Vancouver      630500\n",
       "2     Sydney     4293000"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import another table about the population of each city\n",
    "df2 = pd.read_csv('./asset/population_1.txt', sep='\\t')\n",
    "df2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We use ```pd.merge()``` to join two DataFrames, where you can specify the join key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>location</th>\n",
       "      <th>time</th>\n",
       "      <th>item</th>\n",
       "      <th>dollars_sold</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q1</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>605</td>\n",
       "      <td>630500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q2</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>680</td>\n",
       "      <td>630500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q3</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>812</td>\n",
       "      <td>630500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q4</td>\n",
       "      <td>home entertainment</td>\n",
       "      <td>927</td>\n",
       "      <td>630500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Q1</td>\n",
       "      <td>computer</td>\n",
       "      <td>825</td>\n",
       "      <td>630500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    location time                item  dollars_sold  population\n",
       "0  Vancouver   Q1  home entertainment           605      630500\n",
       "1  Vancouver   Q2  home entertainment           680      630500\n",
       "2  Vancouver   Q3  home entertainment           812      630500\n",
       "3  Vancouver   Q4  home entertainment           927      630500\n",
       "4  Vancouver   Q1            computer           825      630500"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df,df2,on='location').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also specify the join type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>location</th>\n",
       "      <th>time</th>\n",
       "      <th>item</th>\n",
       "      <th>dollars_sold</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>New York</td>\n",
       "      <td>Q4</td>\n",
       "      <td>security</td>\n",
       "      <td>984</td>\n",
       "      <td>8406000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q1</td>\n",
       "      <td>security</td>\n",
       "      <td>591</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q2</td>\n",
       "      <td>security</td>\n",
       "      <td>682</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q3</td>\n",
       "      <td>security</td>\n",
       "      <td>728</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>Q4</td>\n",
       "      <td>security</td>\n",
       "      <td>784</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    location time      item  dollars_sold  population\n",
       "43  New York   Q4  security           984   8406000.0\n",
       "44   Toronto   Q1  security           591         NaN\n",
       "45   Toronto   Q2  security           682         NaN\n",
       "46   Toronto   Q3  security           728         NaN\n",
       "47   Toronto   Q4  security           784         NaN"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df,df2,on='location',how='left').tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## UNION"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>location</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>New York</td>\n",
       "      <td>8406000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>2615000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   location  population\n",
       "0  New York     8406000\n",
       "1   Toronto     2615000"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import another part of the population table\n",
    "df3=pd.read_csv('./asset/population_2.txt', sep='\\t')\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "use `pd.concat()` to union two tables without removing duplicates (i.e., `UNION ALL` in SQL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>location</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>New York</td>\n",
       "      <td>8406000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>630500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Sydney</td>\n",
       "      <td>4293000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>New York</td>\n",
       "      <td>8406000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>2615000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    location  population\n",
       "0   New York     8406000\n",
       "1  Vancouver      630500\n",
       "2     Sydney     4293000\n",
       "0   New York     8406000\n",
       "1    Toronto     2615000"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df2,df3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "use `drop_duplicates()` to remove duplicate rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>location</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>New York</td>\n",
       "      <td>8406000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>630500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Sydney</td>\n",
       "      <td>4293000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>2615000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    location  population\n",
       "0   New York     8406000\n",
       "1  Vancouver      630500\n",
       "2     Sydney     4293000\n",
       "1    Toronto     2615000"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df2,df3]).drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>location</th>\n",
       "      <th>population</th>\n",
       "      <th>big city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>New York</td>\n",
       "      <td>8406000</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vancouver</td>\n",
       "      <td>630500</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Sydney</td>\n",
       "      <td>4293000</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>2615000</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    location  population big city\n",
       "0   New York     8406000     True\n",
       "1  Vancouver      630500    False\n",
       "2     Sydney     4293000     True\n",
       "1    Toronto     2615000     True"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_city = pd.concat([df2,df3]).drop_duplicates()\n",
    "df_city['big city'] = pd.Series(df_city['population'] > 1000000, index=df_city.index)\n",
    "df_city"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivoting and Visualization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">dollars_sold</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>Q1</th>\n",
       "      <th>Q2</th>\n",
       "      <th>Q3</th>\n",
       "      <th>Q4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>location</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>New York</th>\n",
       "      <td>2965</td>\n",
       "      <td>3120</td>\n",
       "      <td>3129</td>\n",
       "      <td>3271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Toronto</th>\n",
       "      <td>2198</td>\n",
       "      <td>2397</td>\n",
       "      <td>2521</td>\n",
       "      <td>2685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vancouver</th>\n",
       "      <td>1844</td>\n",
       "      <td>2175</td>\n",
       "      <td>2366</td>\n",
       "      <td>2583</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          dollars_sold                  \n",
       "time                Q1    Q2    Q3    Q4\n",
       "location                                \n",
       "New York          2965  3120  3129  3271\n",
       "Toronto           2198  2397  2521  2685\n",
       "Vancouver         1844  2175  2366  2583"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table = pd.pivot_table(df, index = 'location', columns = 'time', aggfunc=np.sum)\n",
    "table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"5\" halign=\"left\">dollars_sold</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>time</th>\n",
       "      <th>Q1</th>\n",
       "      <th>Q2</th>\n",
       "      <th>Q3</th>\n",
       "      <th>Q4</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>location</th>\n",
       "      <th>item</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">New York</th>\n",
       "      <th>computer</th>\n",
       "      <td>968.0</td>\n",
       "      <td>1024.0</td>\n",
       "      <td>1048.0</td>\n",
       "      <td>1091.0</td>\n",
       "      <td>4131.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>home entertainment</th>\n",
       "      <td>1087.0</td>\n",
       "      <td>1130.0</td>\n",
       "      <td>1034.0</td>\n",
       "      <td>1142.0</td>\n",
       "      <td>4393.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>phone</th>\n",
       "      <td>38.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>security</th>\n",
       "      <td>872.0</td>\n",
       "      <td>925.0</td>\n",
       "      <td>1002.0</td>\n",
       "      <td>984.0</td>\n",
       "      <td>3783.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Toronto</th>\n",
       "      <th>computer</th>\n",
       "      <td>746.0</td>\n",
       "      <td>769.0</td>\n",
       "      <td>795.0</td>\n",
       "      <td>864.0</td>\n",
       "      <td>3174.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>home entertainment</th>\n",
       "      <td>818.0</td>\n",
       "      <td>894.0</td>\n",
       "      <td>940.0</td>\n",
       "      <td>978.0</td>\n",
       "      <td>3630.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>phone</th>\n",
       "      <td>43.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>212.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>security</th>\n",
       "      <td>591.0</td>\n",
       "      <td>682.0</td>\n",
       "      <td>728.0</td>\n",
       "      <td>784.0</td>\n",
       "      <td>2785.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Vancouver</th>\n",
       "      <th>computer</th>\n",
       "      <td>825.0</td>\n",
       "      <td>952.0</td>\n",
       "      <td>1023.0</td>\n",
       "      <td>1038.0</td>\n",
       "      <td>3838.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>home entertainment</th>\n",
       "      <td>605.0</td>\n",
       "      <td>680.0</td>\n",
       "      <td>812.0</td>\n",
       "      <td>927.0</td>\n",
       "      <td>3024.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>phone</th>\n",
       "      <td>14.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>113.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>security</th>\n",
       "      <td>400.0</td>\n",
       "      <td>512.0</td>\n",
       "      <td>501.0</td>\n",
       "      <td>580.0</td>\n",
       "      <td>1993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <td>7007.0</td>\n",
       "      <td>7692.0</td>\n",
       "      <td>8016.0</td>\n",
       "      <td>8539.0</td>\n",
       "      <td>31254.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             dollars_sold                                 \n",
       "time                                   Q1      Q2      Q3      Q4      All\n",
       "location  item                                                            \n",
       "New York  computer                  968.0  1024.0  1048.0  1091.0   4131.0\n",
       "          home entertainment       1087.0  1130.0  1034.0  1142.0   4393.0\n",
       "          phone                      38.0    41.0    45.0    54.0    178.0\n",
       "          security                  872.0   925.0  1002.0   984.0   3783.0\n",
       "Toronto   computer                  746.0   769.0   795.0   864.0   3174.0\n",
       "          home entertainment        818.0   894.0   940.0   978.0   3630.0\n",
       "          phone                      43.0    52.0    58.0    59.0    212.0\n",
       "          security                  591.0   682.0   728.0   784.0   2785.0\n",
       "Vancouver computer                  825.0   952.0  1023.0  1038.0   3838.0\n",
       "          home entertainment        605.0   680.0   812.0   927.0   3024.0\n",
       "          phone                      14.0    31.0    30.0    38.0    113.0\n",
       "          security                  400.0   512.0   501.0   580.0   1993.0\n",
       "All                                7007.0  7692.0  8016.0  8539.0  31254.0"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, index = ['location', 'item'], columns = 'time', aggfunc=np.sum, margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\"seamless=\"seamless\" src=\"https://plot.ly/~weiwangoz/6.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trace1 = go.Bar(\n",
    "    x=table.index,\n",
    "    y=table.dollars_sold.Q1,\n",
    "    name='Q1'\n",
    ")\n",
    "trace2 = go.Bar(\n",
    "    x=table.index,\n",
    "    y=table.dollars_sold.Q2,\n",
    "    name='Q2'\n",
    ")\n",
    "trace3 = go.Bar(\n",
    "    x=table.index,\n",
    "    y=table.dollars_sold.Q3,\n",
    "    name='Q3'\n",
    ")\n",
    "trace4 = go.Bar(\n",
    "    x=table.index,\n",
    "    y=table.dollars_sold.Q4,\n",
    "    name='Q4'\n",
    ")\n",
    "\n",
    "data = [trace1, trace2, trace3, trace4]\n",
    "layout = go.Layout(\n",
    "    barmode='group'\n",
    ")\n",
    "\n",
    "fig = go.Figure(data=data, layout=layout)\n",
    "py.iplot(fig, filename='sales-plot')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercise"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For each item in each city (location), add one extra column which is the average dollars_sold per person (e.g., total dollars sold for the item over the city's population), and sort rows by the average dollars_sold.\n",
    "\n",
    "Your output should look like below. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "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.4.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}