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": {},
   "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": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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.0</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.0</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.0</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.0</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.0</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.0        3.50    14.98      USD\n",
       "4                          Swimrand         1.0        2.99     0.14      USD\n",
       "5            The Bricklayer's Bible        17.0        3.50     5.15      USD\n",
       "6                The Duck Goes Here        34.0        2.99     5.78      USD\n",
       "7       The Tower Commission Report         4.0        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": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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.0</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.0</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.0</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.0</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.0</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.0</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.0</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.0</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.0</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.0</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.0</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.0</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.0</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.0</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.0</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.0         2.99    0.55      USD\n",
       "1                           Swimrand     2.0         1.99    0.35      USD\n",
       "2   Pining For The Fisheries of Yore    28.0         2.99    0.55      USD\n",
       "3                 The Duck Goes Here    34.0         2.99    0.55      USD\n",
       "4        The Tower Commission Report     4.0        11.50    4.25      USD\n",
       "3   Pining for the Fisheries of Yore    80.0         3.50   14.98      USD\n",
       "4                           Swimrand     1.0         2.99    0.14      USD\n",
       "5             The Bricklayer's Bible    17.0         3.50    5.15      USD\n",
       "6                 The Duck Goes Here    34.0         2.99    5.78      USD\n",
       "7        The Tower Commission Report     4.0         9.50    6.20      USD\n",
       "13  Pining for the Fisheries of Yore    47.0         2.99   11.98      GBP\n",
       "14            The Bricklayer's Bible    17.0         2.99    3.50      GBP\n",
       "15       The Tower Commission Report     4.0         6.50    4.80      GBP\n",
       "21                          Swimrand     8.0         1.99    0.88      EUR\n",
       "22                The Duck Goes Here    12.0         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": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></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": [
    "# First try: differences in case and punctuation\n",
    "# make some books look like two different entries.\n",
    "\n",
    "t = df\n",
    "t = t.assign(total_paid=t.their_price * t.number)\n",
    "t.groupby(['title', 'currency'])[['total_paid']].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>total_paid</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title_fixed</th>\n",
       "      <th>currency</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\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>363.72</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>83.42</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_fixed                      currency            \n",
       "pining for the fisheries of yore GBP           140.53\n",
       "                                 USD           363.72\n",
       "swimrand                         EUR            15.92\n",
       "                                 USD             6.97\n",
       "the bricklayer's bible           GBP            50.83\n",
       "                                 USD            83.42\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": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Second try: make book titles match by fixing differences\n",
    "# in casing and punctuation.\n",
    "\n",
    "t = df\n",
    "t = t.assign(total_paid=t.their_price * t.number)\n",
    "t = t.assign(title_fixed=t['title'].str.lower().str.replace(\"’\", \"'\"))\n",
    "t.groupby(['title_fixed', '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": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}