{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Advanced Python\n",
    "## Pandas Homework 1\n",
    "\n",
    "These exercises focus on the features we discussed in our last class.  <B>You will work with this <B>.ipynb</B> (iPython/Jupyter Notebook) file</B>.   Type your answers into each cell in this workbook; execute the code in a cell with <B>Shift [Enter]</B>.  I have inserted my expected output; you can compare your results to mine.  \n",
    "\n",
    "Preparation:\n",
    "1. Please locate the file we used in class (<B>bond_feed_tiny.xls</B>) and copy/download into the same directory as the one you were in when you opened this .ipynb file (or place in a different location and include the path to the file when you open it).  The excel file can be found under the <B>source_data</B> link, inside the <B>jupyter_notebooks</B> folder.  \n",
    "2. The first step after importing pandas is to read the excel file; if it shows as \"not found\", make sure that you  saved the excel file in the same directory where you launched this .ipynb file (or are referencing the correct path).  If you're not sure, you can always close this file, move it into the same directory as the excel file, and double-click it from there.  \n",
    "3. In each cell place the code necessary to fulfill the assignment, then print the resulting DataFrame or Series.  You can compare your answers to my answers shown.  (Note that printing the variable shows the data as you see it there; simply referencing the variable on the last line will show it in a different visual format (gridlines, etc.)  \n",
    "4. Note the keyboard command <B>Shift [Enter]</B> executes a cell and drops cursor to the next cell -- running from the menu is not necessary.  \n",
    "5. <I><B>Please also note</B> that when cells are executed individually they include variables from prior cell executions, <B>but changes to a prior cell will not reflect below it until that cell is executed.</B></I>\n",
    "6. <B>Refer to the slides for an overview of the features used here, and you may also refer to the <U>Homework Discussion document</U> (on the course website) for hints on what features to use for each question.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>Notes for using Jupyter Notebook</U>:  to call <B>exit()</B> from a notebook, please use <B>sys.exit()</B> (requires <B>import sys</B>); if a strange error occurs, it may be because Jupyter retains variables from all executed cells.  To reset the notebook's variables, click 'Restart Kernel' (the circular arrow) -- this will not undo any text changes.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8.1:  execute this cell once so subsequent cells can make use of the import\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8.2:  read sheet \"Aggregate\" in bond_feed_tiny.xls\n",
    "#       assign to a variable 'df1' (we'll be referring back to it later on)\n",
    "#       if the file cannot be read, see note in instructions above"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>expected output</U>:\n",
    "<PRE>CRNCY Country Code Eurozone?   AMT_ISSUED  AMT_OUTSTANDING\n",
    "0   USD           UA       Non   1500000000       1500000000\n",
    "1   EUR           FR  Eurozone  10160000000      10160000000\n",
    "2   DKK           DE       Non  17040000000      16040000000\n",
    "3   EUR           FR  Eurozone  20195000000      19095000000\n",
    "4   EUR           GO       Non      7000000          7000000</PRE>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8.3:  rename \"Country Code\" to CTRY_CODE and \"Eurozone?\" to EUROZONE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>expected output</U>:\n",
    "<PRE>  CRNCY CTRY_CODE  EUROZONE   AMT_ISSUED  AMT_OUTSTANDING\n",
    "0   USD        UA       Non   1500000000       1500000000\n",
    "1   EUR        FR  Eurozone  10160000000      10160000000\n",
    "2   DKK        DE       Non  17040000000      16040000000\n",
    "3   EUR        FR  Eurozone  20195000000      19095000000\n",
    "4   EUR        GO       Non      7000000          7000000</PRE>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8.4:  create a slice of just CTRY_CODE, AMT_ISSUED and AMT_OUTSTANDING\n",
    "#       Note:  please assign this to a new dataframe df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>expected output</U>:\n",
    "<PRE>  CTRY_CODE   AMT_ISSUED  AMT_OUTSTANDING\n",
    "0        UA   1500000000       1500000000\n",
    "1        FR  10160000000      10160000000\n",
    "2        DE  17040000000      16040000000\n",
    "3        FR  20195000000      19095000000\n",
    "4        GO      7000000          7000000</PRE>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8.5:  for the next questions, please use dataframe df2\n",
    "#       create a new column AMT_COMPLETED that subtracts AMT_OUTSTANDING from AMT_ISSUED across all columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>expected output</U>:\n",
    "<PRE>  CTRY_CODE   AMT_ISSUED  AMT_OUTSTANDING  AMT_COMPLETED\n",
    "0        UA   1500000000       1500000000              0\n",
    "1        FR  10160000000      10160000000              0\n",
    "2        DE  17040000000      16040000000     1000000000\n",
    "3        FR  20195000000      19095000000     1100000000\n",
    "4        GO      7000000          7000000              0</PRE>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8.6:  sum up each of AMT_ISSUED, AMT_OUTSTANDING and AMT_COMPLETED.  \n",
    "#       Attempt to do this with one statement - do not sum each \n",
    "#       column separately.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>expected output</U>:\n",
    "<PRE>CTRY_CODE           UAFRDEFRGO\n",
    "AMT_ISSUED         48902000000\n",
    "AMT_OUTSTANDING    46802000000\n",
    "AMT_COMPLETED       2100000000\n",
    "dtype: object</PRE>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8.7:  show average AMT_ISSUED"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>expected output</U>:\n",
    "<PRE>average amount issued:  9780400000.0</PRE>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8.8:  going back to original df1 (all 5 columns), slice rows only for \n",
    "#       issues in currency EUR (note:  please show the results, but don't \n",
    "#       assign back to df1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>expected output</U>:\n",
    "<PRE>  CRNCY CTRY_CODE  EUROZONE   AMT_ISSUED  AMT_OUTSTANDING\n",
    "1   EUR        FR  Eurozone  10160000000      10160000000\n",
    "3   EUR        FR  Eurozone  20195000000      19095000000\n",
    "4   EUR        GO       Non      7000000          7000000</PRE>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8.9:  again using the original df1, slice rows for issues that are \n",
    "#       in currency EUR and are also EUROZONE\n",
    "#       note:  please show the results, but don't assign back to df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>expected output</U>:\n",
    "<PRE>  CRNCY CTRY_CODE  EUROZONE   AMT_ISSUED  AMT_OUTSTANDING\n",
    "1   EUR        FR  Eurozone  10160000000      10160000000\n",
    "3   EUR        FR  Eurozone  20195000000      19095000000</PRE>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8.10: in one statement, show average/mean of AMT_ISSUED and AMT_OUTSTANDING \n",
    "#       for all countries that are not currency EUR\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>expected output</U>:\n",
    "<PRE>AMT_ISSUED         9.270000e+09\n",
    "AMT_OUTSTANDING    8.770000e+09\n",
    "dtype: float64</PRE>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8.11: in the EUROZONE column, replace 'Eurozone' with 'Y' and 'Non' with 'N'\n",
    "#       attempt to accomplish this with .loc referencing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>expected output</U>:\n",
    "<PRE>  CRNCY CTRY_CODE EUROZONE   AMT_ISSUED  AMT_OUTSTANDING\n",
    "0   USD        UA        N   1500000000       1500000000\n",
    "1   EUR        FR        Y  10160000000      10160000000\n",
    "2   DKK        DE        N  17040000000      16040000000\n",
    "3   EUR        FR        Y  20195000000      19095000000\n",
    "4   EUR        GO        N      7000000          7000000</PRE>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8.12: create a groupby() aggregation that sums AMT_ISSUED and AMT_OUTSTANDING by currency"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>expected output</U>:\n",
    "<PRE>        AMT_ISSUED  AMT_OUTSTANDING\n",
    "CRNCY                              \n",
    "DKK    17040000000      16040000000\n",
    "EUR    30362000000      29262000000\n",
    "USD     1500000000       1500000000</PRE>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8.13: from the 'country_lookup' tab in the bond_feed_tiny.xls Excel workbook, create a DataFrame with CTRY_CODE index and name FULL_NAME."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<U>expected output</U>:\n",
    "<PRE>           FULL_NAME\n",
    "CTRY_CODE\n",
    "UA         United Arab Emirates\n",
    "FR         France\n",
    "DE         Denmark\n",
    "GO         Gabon</PRE>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8.14: join the df (pre-groupby) to the country lookup Series created above, to create \n",
    "#       a df with a new column df['FULL_NAME'] that is the full name of the country"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "<U>expected output</U>:\n",
    "<PRE>  CRNCY CTRY_CODE EUROZONE   AMT_ISSUED  AMT_OUTSTANDING             FULL_NAME\n",
    "0   USD        UA        N   1500000000       1500000000  United Arab Emirates\n",
    "1   EUR        FR        Y  10160000000      10160000000                France\n",
    "2   DKK        DE        N  17040000000      16040000000               Denmark\n",
    "3   EUR        FR        Y  20195000000      19095000000                France\n",
    "4   EUR        GO        N      7000000          7000000                 Gabon</PRE>"
   ]
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
