{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c4dca2df",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "3668f2b1",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/splat/venvs/jupyter-notebook/lib/python3.8/site-packages/openpyxl/styles/stylesheet.py:226: UserWarning: Workbook contains no default style, apply openpyxl's default\n",
      "  warn(\"Workbook contains no default style, apply openpyxl's default\")\n"
     ]
    }
   ],
   "source": [
    "df204 = pd.read_excel(\"Usage-443112phone-204.xlsx\").iloc[:,13:]\n",
    "df205 = pd.read_excel(\"Usage-443112phone-205.xlsx\").iloc[:,13:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "bb7ff445",
   "metadata": {},
   "outputs": [],
   "source": [
    "headers=[\"Number called\", \"Call DateTime\", \"Place called\", \"Call Duration\", \"Full Price\", \"Savings\", \"Jurisdiction\"]\n",
    "df204.rename(columns = dict(zip(df204.columns, headers)), inplace=True)\n",
    "df205.rename(columns = dict(zip(df205.columns, headers)), inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "9b1d5d73",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df204.drop(labels=[0,1], inplace=True)\n",
    "df205.drop(labels=[0,1], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "759a3d05",
   "metadata": {},
   "outputs": [],
   "source": [
    "dfphone = df204.append(df205, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "43edc727",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Number called</th>\n",
       "      <th>Call DateTime</th>\n",
       "      <th>Place called</th>\n",
       "      <th>Call Duration</th>\n",
       "      <th>Full Price</th>\n",
       "      <th>Savings</th>\n",
       "      <th>Jurisdiction</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>61894220899</td>\n",
       "      <td>19-Sep-2022 18:36</td>\n",
       "      <td>Australia</td>\n",
       "      <td>12</td>\n",
       "      <td>2.76</td>\n",
       "      <td>0.15</td>\n",
       "      <td>I</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>800559009</td>\n",
       "      <td>19-Sep-2022 09:48</td>\n",
       "      <td>Toll Free</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Number called      Call DateTime Place called Call Duration Full Price  \\\n",
       "0   61894220899  19-Sep-2022 18:36    Australia            12       2.76   \n",
       "1     800559009  19-Sep-2022 09:48    Toll Free             1          0   \n",
       "\n",
       "  Savings Jurisdiction  \n",
       "0    0.15            I  \n",
       "1       0            N  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfphone.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "50312d6b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Number called</th>\n",
       "      <th>Call DateTime</th>\n",
       "      <th>Place called</th>\n",
       "      <th>Call Duration</th>\n",
       "      <th>Full Price</th>\n",
       "      <th>Savings</th>\n",
       "      <th>Jurisdiction</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>61894220899</td>\n",
       "      <td>19-Sep-2022 18:36</td>\n",
       "      <td>Australia</td>\n",
       "      <td>12</td>\n",
       "      <td>2.76</td>\n",
       "      <td>0.15</td>\n",
       "      <td>I</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>61894220899</td>\n",
       "      <td>16-Sep-2022 13:46</td>\n",
       "      <td>Australia</td>\n",
       "      <td>14</td>\n",
       "      <td>3.22</td>\n",
       "      <td>0.61</td>\n",
       "      <td>I</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>61894220899</td>\n",
       "      <td>15-Sep-2022 11:51</td>\n",
       "      <td>Australia</td>\n",
       "      <td>14</td>\n",
       "      <td>3.22</td>\n",
       "      <td>0.61</td>\n",
       "      <td>I</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>61894220899</td>\n",
       "      <td>14-Sep-2022 12:14</td>\n",
       "      <td>Australia</td>\n",
       "      <td>20</td>\n",
       "      <td>4.6</td>\n",
       "      <td>1.99</td>\n",
       "      <td>I</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>61894220899</td>\n",
       "      <td>03-Oct-2022 12:34</td>\n",
       "      <td>Australia</td>\n",
       "      <td>13</td>\n",
       "      <td>2.99</td>\n",
       "      <td>0</td>\n",
       "      <td>I</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>61894220899</td>\n",
       "      <td>20-Sep-2022 10:15</td>\n",
       "      <td>Australia</td>\n",
       "      <td>4</td>\n",
       "      <td>0.92</td>\n",
       "      <td>0</td>\n",
       "      <td>I</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>61894220899</td>\n",
       "      <td>20-Sep-2022 08:54</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2</td>\n",
       "      <td>0.46</td>\n",
       "      <td>0</td>\n",
       "      <td>I</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Number called      Call DateTime Place called Call Duration Full Price  \\\n",
       "0     61894220899  19-Sep-2022 18:36    Australia            12       2.76   \n",
       "8     61894220899  16-Sep-2022 13:46    Australia            14       3.22   \n",
       "16    61894220899  15-Sep-2022 11:51    Australia            14       3.22   \n",
       "23    61894220899  14-Sep-2022 12:14    Australia            20        4.6   \n",
       "70    61894220899  03-Oct-2022 12:34    Australia            13       2.99   \n",
       "104   61894220899  20-Sep-2022 10:15    Australia             4       0.92   \n",
       "106   61894220899  20-Sep-2022 08:54    Australia             2       0.46   \n",
       "\n",
       "    Savings Jurisdiction  \n",
       "0      0.15            I  \n",
       "8      0.61            I  \n",
       "16     0.61            I  \n",
       "23     1.99            I  \n",
       "70        0            I  \n",
       "104       0            I  \n",
       "106       0            I  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfphone[dfphone['Place called']=='Australia']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "24883955",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18.17"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfphone[dfphone['Place called']=='Australia']['Full Price'].astype(float).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "bb6a6844",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Number called</th>\n",
       "      <th>Call DateTime</th>\n",
       "      <th>Place called</th>\n",
       "      <th>Call Duration</th>\n",
       "      <th>Full Price</th>\n",
       "      <th>Savings</th>\n",
       "      <th>Jurisdiction</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>21487497</td>\n",
       "      <td>15-Sep-2022 15:18</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>2</td>\n",
       "      <td>0.63</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>278211333</td>\n",
       "      <td>15-Sep-2022 14:44</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>2</td>\n",
       "      <td>0.63</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>273965632</td>\n",
       "      <td>14-Sep-2022 12:50</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>1</td>\n",
       "      <td>0.31</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>21482641</td>\n",
       "      <td>13-Sep-2022 15:31</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>1</td>\n",
       "      <td>0.31</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>21482641</td>\n",
       "      <td>13-Sep-2022 11:15</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>7</td>\n",
       "      <td>2.19</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>21482641</td>\n",
       "      <td>13-Sep-2022 10:57</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>10</td>\n",
       "      <td>3.13</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>276884021</td>\n",
       "      <td>11-Sep-2022 09:00</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>8</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>275744871</td>\n",
       "      <td>10-Sep-2022 10:26</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>48</td>\n",
       "      <td>15.02</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>273032565</td>\n",
       "      <td>08-Sep-2022 10:23</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>2</td>\n",
       "      <td>0.63</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>276884021</td>\n",
       "      <td>06-Sep-2022 08:36</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>5</td>\n",
       "      <td>1.56</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>276884021</td>\n",
       "      <td>27-Aug-2022 09:35</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>1</td>\n",
       "      <td>0.31</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>275744871</td>\n",
       "      <td>26-Aug-2022 13:10</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>20</td>\n",
       "      <td>6.26</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>273965632</td>\n",
       "      <td>23-Aug-2022 12:35</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>1</td>\n",
       "      <td>0.31</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>276884021</td>\n",
       "      <td>23-Aug-2022 08:31</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>4</td>\n",
       "      <td>1.25</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>21620320</td>\n",
       "      <td>01-Oct-2022 18:29</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>1</td>\n",
       "      <td>0.31</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>211043765</td>\n",
       "      <td>28-Sep-2022 09:37</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>1</td>\n",
       "      <td>0.31</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>212355132</td>\n",
       "      <td>26-Sep-2022 12:32</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>1</td>\n",
       "      <td>0.31</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>211043765</td>\n",
       "      <td>25-Sep-2022 12:02</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>1</td>\n",
       "      <td>0.31</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>278211333</td>\n",
       "      <td>24-Sep-2022 18:51</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>4</td>\n",
       "      <td>1.25</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>272726977</td>\n",
       "      <td>24-Sep-2022 16:17</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>1</td>\n",
       "      <td>0.31</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>276884021</td>\n",
       "      <td>24-Sep-2022 12:05</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>5</td>\n",
       "      <td>1.56</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>278211333</td>\n",
       "      <td>23-Sep-2022 14:36</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>5</td>\n",
       "      <td>1.56</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>276884021</td>\n",
       "      <td>23-Sep-2022 10:02</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>4</td>\n",
       "      <td>1.25</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>275878830</td>\n",
       "      <td>21-Sep-2022 11:14</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>1</td>\n",
       "      <td>0.31</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>275878830</td>\n",
       "      <td>21-Sep-2022 11:12</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>1</td>\n",
       "      <td>0.31</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Number called      Call DateTime Place called Call Duration Full Price  \\\n",
       "10       21487497  15-Sep-2022 15:18       Mobile             2       0.63   \n",
       "11      278211333  15-Sep-2022 14:44       Mobile             2       0.63   \n",
       "21      273965632  14-Sep-2022 12:50       Mobile             1       0.31   \n",
       "27       21482641  13-Sep-2022 15:31       Mobile             1       0.31   \n",
       "30       21482641  13-Sep-2022 11:15       Mobile             7       2.19   \n",
       "32       21482641  13-Sep-2022 10:57       Mobile            10       3.13   \n",
       "37      276884021  11-Sep-2022 09:00       Mobile             8        2.5   \n",
       "39      275744871  10-Sep-2022 10:26       Mobile            48      15.02   \n",
       "41      273032565  08-Sep-2022 10:23       Mobile             2       0.63   \n",
       "43      276884021  06-Sep-2022 08:36       Mobile             5       1.56   \n",
       "58      276884021  27-Aug-2022 09:35       Mobile             1       0.31   \n",
       "60      275744871  26-Aug-2022 13:10       Mobile            20       6.26   \n",
       "65      273965632  23-Aug-2022 12:35       Mobile             1       0.31   \n",
       "68      276884021  23-Aug-2022 08:31       Mobile             4       1.25   \n",
       "71       21620320  01-Oct-2022 18:29       Mobile             1       0.31   \n",
       "78      211043765  28-Sep-2022 09:37       Mobile             1       0.31   \n",
       "84      212355132  26-Sep-2022 12:32       Mobile             1       0.31   \n",
       "85      211043765  25-Sep-2022 12:02       Mobile             1       0.31   \n",
       "86      278211333  24-Sep-2022 18:51       Mobile             4       1.25   \n",
       "88      272726977  24-Sep-2022 16:17       Mobile             1       0.31   \n",
       "89      276884021  24-Sep-2022 12:05       Mobile             5       1.56   \n",
       "91      278211333  23-Sep-2022 14:36       Mobile             5       1.56   \n",
       "94      276884021  23-Sep-2022 10:02       Mobile             4       1.25   \n",
       "99      275878830  21-Sep-2022 11:14       Mobile             1       0.31   \n",
       "100     275878830  21-Sep-2022 11:12       Mobile             1       0.31   \n",
       "\n",
       "    Savings Jurisdiction  \n",
       "10        0            M  \n",
       "11        0            M  \n",
       "21        0            M  \n",
       "27        0            M  \n",
       "30        0            M  \n",
       "32        0            M  \n",
       "37        0            M  \n",
       "39        0            M  \n",
       "41        0            M  \n",
       "43        0            M  \n",
       "58        0            M  \n",
       "60        0            M  \n",
       "65        0            M  \n",
       "68        0            M  \n",
       "71        0            M  \n",
       "78        0            M  \n",
       "84        0            M  \n",
       "85        0            M  \n",
       "86        0            M  \n",
       "88        0            M  \n",
       "89        0            M  \n",
       "91        0            M  \n",
       "94        0            M  \n",
       "99        0            M  \n",
       "100       0            M  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "4\\7777777777l,,,,21ikldfphone[dfphone['Place called']=='Mobile']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7cc9ec38",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}