Solutions-6.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>body {\n",
       "    margin: 0;\n",
       "    font-family: Helvetica;\n",
       "}\n",
       "table.dataframe {\n",
       "    border-collapse: collapse;\n",
       "    border: none;\n",
       "}\n",
       "table.dataframe tr {\n",
       "    border: none;\n",
       "}\n",
       "table.dataframe td, table.dataframe th {\n",
       "    margin: 0;\n",
       "    border: 1px solid white;\n",
       "    padding-left: 0.25em;\n",
       "    padding-right: 0.25em;\n",
       "}\n",
       "table.dataframe th:not(:empty) {\n",
       "    background-color: #fec;\n",
       "    text-align: left;\n",
       "    font-weight: normal;\n",
       "}\n",
       "table.dataframe tr:nth-child(2) th:empty {\n",
       "    border-left: none;\n",
       "    border-right: 1px dashed #888;\n",
       "}\n",
       "table.dataframe td {\n",
       "    border: 2px solid #ccf;\n",
       "    background-color: #f4f4ff;\n",
       "}\n",
       "h3 {\n",
       "    color: white;\n",
       "    background-color: black;\n",
       "    padding: 0.5em;\n",
       "}\n",
       "</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from IPython.core.display import HTML\n",
    "css = open('style-table.css').read() + open('style-notebook.css').read()\n",
    "HTML('<style>{}</style>'.format(css))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Book title</th>\n",
       "      <th>Number sold</th>\n",
       "      <th>Sales price</th>\n",
       "      <th>Royalty paid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Bricklayer’s Bible</td>\n",
       "      <td>8</td>\n",
       "      <td>2.99</td>\n",
       "      <td>0.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Swimrand</td>\n",
       "      <td>2</td>\n",
       "      <td>1.99</td>\n",
       "      <td>0.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Pining For The Fisheries of Yore</td>\n",
       "      <td>28</td>\n",
       "      <td>2.99</td>\n",
       "      <td>0.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>The Duck Goes Here</td>\n",
       "      <td>34</td>\n",
       "      <td>2.99</td>\n",
       "      <td>0.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>The Tower Commission Report</td>\n",
       "      <td>4</td>\n",
       "      <td>11.50</td>\n",
       "      <td>4.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Book title  Number sold  Sales price  Royalty paid\n",
       "0            The Bricklayer’s Bible            8         2.99          0.55\n",
       "1                          Swimrand            2         1.99          0.35\n",
       "2  Pining For The Fisheries of Yore           28         2.99          0.55\n",
       "3                The Duck Goes Here           34         2.99          0.55\n",
       "4       The Tower Commission Report            4        11.50          4.25"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales1 = pd.read_csv('sales1.csv')\n",
    "sales1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Title</th>\n",
       "      <th>Units sold</th>\n",
       "      <th>List price</th>\n",
       "      <th>Royalty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Sales report for Q4</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E-Book Reader US Store</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Pining for the Fisheries of Yore</td>\n",
       "      <td>80</td>\n",
       "      <td>3.5</td>\n",
       "      <td>14.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Swimrand</td>\n",
       "      <td>1</td>\n",
       "      <td>2.99</td>\n",
       "      <td>0.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>The Bricklayer's Bible</td>\n",
       "      <td>17</td>\n",
       "      <td>3.5</td>\n",
       "      <td>5.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>The Duck Goes Here</td>\n",
       "      <td>34</td>\n",
       "      <td>2.99</td>\n",
       "      <td>5.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>The Tower Commission Report</td>\n",
       "      <td>4</td>\n",
       "      <td>9.5</td>\n",
       "      <td>6.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>US royalties (USD)</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>32.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Sales report for Q4</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>E-Book Reader UK Store</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Pining for the Fisheries of Yore</td>\n",
       "      <td>47</td>\n",
       "      <td>2.99</td>\n",
       "      <td>11.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>The Bricklayer's Bible</td>\n",
       "      <td>17</td>\n",
       "      <td>2.99</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>The Tower Commission Report</td>\n",
       "      <td>4</td>\n",
       "      <td>6.5</td>\n",
       "      <td>4.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>UK royalties (GBP)</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>20.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Sales report for Q4</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>E-Book Reader France Store</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Swimrand</td>\n",
       "      <td>8</td>\n",
       "      <td>1.99</td>\n",
       "      <td>0.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>The Duck Goes Here</td>\n",
       "      <td>12</td>\n",
       "      <td>1.99</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>France royalties (EUR)</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2.38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               Title Units sold List price Royalty\n",
       "0                                                                 \n",
       "1                Sales report for Q4                              \n",
       "2             E-Book Reader US Store                              \n",
       "3   Pining for the Fisheries of Yore         80        3.5   14.98\n",
       "4                           Swimrand          1       2.99    0.14\n",
       "5             The Bricklayer's Bible         17        3.5    5.15\n",
       "6                 The Duck Goes Here         34       2.99    5.78\n",
       "7        The Tower Commission Report          4        9.5     6.2\n",
       "8                 US royalties (USD)                         32.25\n",
       "9                                                                 \n",
       "10                                                                \n",
       "11               Sales report for Q4                              \n",
       "12            E-Book Reader UK Store                              \n",
       "13  Pining for the Fisheries of Yore         47       2.99   11.98\n",
       "14            The Bricklayer's Bible         17       2.99     3.5\n",
       "15       The Tower Commission Report          4        6.5     4.8\n",
       "16                UK royalties (GBP)                         20.28\n",
       "17                                                                \n",
       "18                                                                \n",
       "19               Sales report for Q4                              \n",
       "20        E-Book Reader France Store                              \n",
       "21                          Swimrand          8       1.99    0.88\n",
       "22                The Duck Goes Here         12       1.99     1.5\n",
       "23            France royalties (EUR)                          2.38"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales2 = pd.read_csv('sales2.csv')\n",
    "sales2.fillna('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Challenge: first combine these sales together into a single dataframe,\n",
    "# then compute how much money consumers spent on each book in each currency."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>number</th>\n",
       "      <th>their_price</th>\n",
       "      <th>we_got</th>\n",
       "      <th>currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Bricklayer’s Bible</td>\n",
       "      <td>8</td>\n",
       "      <td>2.99</td>\n",
       "      <td>0.55</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Swimrand</td>\n",
       "      <td>2</td>\n",
       "      <td>1.99</td>\n",
       "      <td>0.35</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Pining For The Fisheries of Yore</td>\n",
       "      <td>28</td>\n",
       "      <td>2.99</td>\n",
       "      <td>0.55</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>The Duck Goes Here</td>\n",
       "      <td>34</td>\n",
       "      <td>2.99</td>\n",
       "      <td>0.55</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>The Tower Commission Report</td>\n",
       "      <td>4</td>\n",
       "      <td>11.50</td>\n",
       "      <td>4.25</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              title  number  their_price  we_got currency\n",
       "0            The Bricklayer’s Bible       8         2.99    0.55      USD\n",
       "1                          Swimrand       2         1.99    0.35      USD\n",
       "2  Pining For The Fisheries of Yore      28         2.99    0.55      USD\n",
       "3                The Duck Goes Here      34         2.99    0.55      USD\n",
       "4       The Tower Commission Report       4        11.50    4.25      USD"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = sales1.rename(columns={\n",
    "    'Book title': 'title',\n",
    "    'Number sold': 'number',\n",
    "    'Sales price': 'their_price',\n",
    "    'Royalty paid': 'we_got',\n",
    "    })\n",
    "df1['currency'] = 'USD'\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Title</th>\n",
       "      <th>Units sold</th>\n",
       "      <th>List price</th>\n",
       "      <th>Royalty</th>\n",
       "      <th>currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Pining for the Fisheries of Yore</td>\n",
       "      <td>80</td>\n",
       "      <td>3.50</td>\n",
       "      <td>14.98</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Swimrand</td>\n",
       "      <td>1</td>\n",
       "      <td>2.99</td>\n",
       "      <td>0.14</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>The Bricklayer's Bible</td>\n",
       "      <td>17</td>\n",
       "      <td>3.50</td>\n",
       "      <td>5.15</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>The Duck Goes Here</td>\n",
       "      <td>34</td>\n",
       "      <td>2.99</td>\n",
       "      <td>5.78</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>The Tower Commission Report</td>\n",
       "      <td>4</td>\n",
       "      <td>9.50</td>\n",
       "      <td>6.20</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Title  Units sold  List price  Royalty currency\n",
       "3  Pining for the Fisheries of Yore          80        3.50    14.98      USD\n",
       "4                          Swimrand           1        2.99     0.14      USD\n",
       "5            The Bricklayer's Bible          17        3.50     5.15      USD\n",
       "6                The Duck Goes Here          34        2.99     5.78      USD\n",
       "7       The Tower Commission Report           4        9.50     6.20      USD"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = sales2.copy()\n",
    "t = sales2['Title']\n",
    "t = t.where(t.str.endswith(')')).str.split().str[-1].str.strip('()')\n",
    "s['currency'] = t.fillna(method='bfill')\n",
    "s = s[s['List price'].notnull()]\n",
    "df2 = s.rename(columns={\n",
    "    'Units sold': 'number',\n",
    "    'Title': 'title',\n",
    "    'List price': 'their_price',\n",
    "    'Royalty': 'we_got',\n",
    "    })\n",
    "s.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>number</th>\n",
       "      <th>their_price</th>\n",
       "      <th>we_got</th>\n",
       "      <th>currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Bricklayer’s Bible</td>\n",
       "      <td>8</td>\n",
       "      <td>2.99</td>\n",
       "      <td>0.55</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Swimrand</td>\n",
       "      <td>2</td>\n",
       "      <td>1.99</td>\n",
       "      <td>0.35</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Pining For The Fisheries of Yore</td>\n",
       "      <td>28</td>\n",
       "      <td>2.99</td>\n",
       "      <td>0.55</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>The Duck Goes Here</td>\n",
       "      <td>34</td>\n",
       "      <td>2.99</td>\n",
       "      <td>0.55</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>The Tower Commission Report</td>\n",
       "      <td>4</td>\n",
       "      <td>11.50</td>\n",
       "      <td>4.25</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Pining for the Fisheries of Yore</td>\n",
       "      <td>80</td>\n",
       "      <td>3.50</td>\n",
       "      <td>14.98</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Swimrand</td>\n",
       "      <td>1</td>\n",
       "      <td>2.99</td>\n",
       "      <td>0.14</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>The Bricklayer's Bible</td>\n",
       "      <td>17</td>\n",
       "      <td>3.50</td>\n",
       "      <td>5.15</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>The Duck Goes Here</td>\n",
       "      <td>34</td>\n",
       "      <td>2.99</td>\n",
       "      <td>5.78</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>The Tower Commission Report</td>\n",
       "      <td>4</td>\n",
       "      <td>9.50</td>\n",
       "      <td>6.20</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Pining for the Fisheries of Yore</td>\n",
       "      <td>47</td>\n",
       "      <td>2.99</td>\n",
       "      <td>11.98</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>The Bricklayer's Bible</td>\n",
       "      <td>17</td>\n",
       "      <td>2.99</td>\n",
       "      <td>3.50</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>The Tower Commission Report</td>\n",
       "      <td>4</td>\n",
       "      <td>6.50</td>\n",
       "      <td>4.80</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Swimrand</td>\n",
       "      <td>8</td>\n",
       "      <td>1.99</td>\n",
       "      <td>0.88</td>\n",
       "      <td>EUR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>The Duck Goes Here</td>\n",
       "      <td>12</td>\n",
       "      <td>1.99</td>\n",
       "      <td>1.50</td>\n",
       "      <td>EUR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               title  number  their_price  we_got currency\n",
       "0             The Bricklayer’s Bible       8         2.99    0.55      USD\n",
       "1                           Swimrand       2         1.99    0.35      USD\n",
       "2   Pining For The Fisheries of Yore      28         2.99    0.55      USD\n",
       "3                 The Duck Goes Here      34         2.99    0.55      USD\n",
       "4        The Tower Commission Report       4        11.50    4.25      USD\n",
       "3   Pining for the Fisheries of Yore      80         3.50   14.98      USD\n",
       "4                           Swimrand       1         2.99    0.14      USD\n",
       "5             The Bricklayer's Bible      17         3.50    5.15      USD\n",
       "6                 The Duck Goes Here      34         2.99    5.78      USD\n",
       "7        The Tower Commission Report       4         9.50    6.20      USD\n",
       "13  Pining for the Fisheries of Yore      47         2.99   11.98      GBP\n",
       "14            The Bricklayer's Bible      17         2.99    3.50      GBP\n",
       "15       The Tower Commission Report       4         6.50    4.80      GBP\n",
       "21                          Swimrand       8         1.99    0.88      EUR\n",
       "22                The Duck Goes Here      12         1.99    1.50      EUR"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.concat([df1, df2])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>total_paid</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <th>currency</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Pining For The Fisheries of Yore</th>\n",
       "      <th>USD</th>\n",
       "      <td>83.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Pining for the Fisheries of Yore</th>\n",
       "      <th>GBP</th>\n",
       "      <td>140.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>280.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Swimrand</th>\n",
       "      <th>EUR</th>\n",
       "      <td>15.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>6.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">The Bricklayer's Bible</th>\n",
       "      <th>GBP</th>\n",
       "      <td>50.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>59.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Bricklayer’s Bible</th>\n",
       "      <th>USD</th>\n",
       "      <td>23.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">The Duck Goes Here</th>\n",
       "      <th>EUR</th>\n",
       "      <td>23.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>203.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">The Tower Commission Report</th>\n",
       "      <th>GBP</th>\n",
       "      <td>26.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>84.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           total_paid\n",
       "title                            currency            \n",
       "Pining For The Fisheries of Yore USD            83.72\n",
       "Pining for the Fisheries of Yore GBP           140.53\n",
       "                                 USD           280.00\n",
       "Swimrand                         EUR            15.92\n",
       "                                 USD             6.97\n",
       "The Bricklayer's Bible           GBP            50.83\n",
       "                                 USD            59.50\n",
       "The Bricklayer’s Bible           USD            23.92\n",
       "The Duck Goes Here               EUR            23.88\n",
       "                                 USD           203.32\n",
       "The Tower Commission Report      GBP            26.00\n",
       "                                 USD            84.00"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['total_paid'] = df.their_price * df.number\n",
    "df.groupby(['title', 'currency'])[['total_paid']].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}