{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# bopiip-jun17qtr-alltables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import string\n",
    "import os\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ddir = r'C:\\Users\\Glenn\\Documents\\Stats\\Oct 2017'\n",
    "ssheet ='bopiip-jun17qtr-alltables'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Series_reference</th>\n",
       "      <th>Period</th>\n",
       "      <th>Data_value</th>\n",
       "      <th>Suppressed</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>UNITS</th>\n",
       "      <th>MAGNTUDE</th>\n",
       "      <th>Subject</th>\n",
       "      <th>Group</th>\n",
       "      <th>Series_title_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BOPA.S06AC000000000A</td>\n",
       "      <td>1972.03</td>\n",
       "      <td>1638.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>Dollars</td>\n",
       "      <td>6</td>\n",
       "      <td>Balance of Payments - BOP</td>\n",
       "      <td>BPM6 Annual, Balance of payments major components</td>\n",
       "      <td>Actual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BOPA.S06AC000000000A</td>\n",
       "      <td>1973.03</td>\n",
       "      <td>2043.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>Dollars</td>\n",
       "      <td>6</td>\n",
       "      <td>Balance of Payments - BOP</td>\n",
       "      <td>BPM6 Annual, Balance of payments major components</td>\n",
       "      <td>Actual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BOPA.S06AC000000000A</td>\n",
       "      <td>1974.03</td>\n",
       "      <td>2354.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>Dollars</td>\n",
       "      <td>6</td>\n",
       "      <td>Balance of Payments - BOP</td>\n",
       "      <td>BPM6 Annual, Balance of payments major components</td>\n",
       "      <td>Actual</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Series_reference   Period  Data_value  Suppressed STATUS    UNITS  \\\n",
       "0  BOPA.S06AC000000000A  1972.03      1638.0         NaN      F  Dollars   \n",
       "1  BOPA.S06AC000000000A  1973.03      2043.0         NaN      F  Dollars   \n",
       "2  BOPA.S06AC000000000A  1974.03      2354.0         NaN      F  Dollars   \n",
       "\n",
       "   MAGNTUDE                    Subject  \\\n",
       "0         6  Balance of Payments - BOP   \n",
       "1         6  Balance of Payments - BOP   \n",
       "2         6  Balance of Payments - BOP   \n",
       "\n",
       "                                               Group Series_title_1  \n",
       "0  BPM6 Annual, Balance of payments major components         Actual  \n",
       "1  BPM6 Annual, Balance of payments major components         Actual  \n",
       "2  BPM6 Annual, Balance of payments major components         Actual  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_csv(os.path.join(ddir,ssheet+\".csv\"))\n",
    "df1.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Subject</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Balance of Payments - BOP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98512</th>\n",
       "      <td>International Investment Position - IIP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       Subject\n",
       "0                    Balance of Payments - BOP\n",
       "98512  International Investment Position - IIP"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1[['Subject']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Group    52\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1[['Group']].drop_duplicates().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Group    27\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1[df1['Subject']=='International Investment Position - IIP'][['Group']].drop_duplicates().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>98512</th>\n",
       "      <td>BPM6 Annual, International financial assets an...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98544</th>\n",
       "      <td>BPM6 Annual, International non-equity financia...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98560</th>\n",
       "      <td>BPM6 Annual, External lending and debt all sec...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98720</th>\n",
       "      <td>BPM6 Annual, Int. non-equity financial instrum...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98928</th>\n",
       "      <td>BPM6 Annual, International investment position...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99056</th>\n",
       "      <td>BPM6 Annual, International investment position...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99248</th>\n",
       "      <td>BPM6 Annual, External lending and debt by sect...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100139</th>\n",
       "      <td>BPM6 Annual, International non-equity financia...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100347</th>\n",
       "      <td>BPM6 Annual, International financial assets an...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100971</th>\n",
       "      <td>BPM6 Annual, Directional basis stock of total ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100987</th>\n",
       "      <td>BPM6 Annual, Directional basis stock of direct...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101003</th>\n",
       "      <td>BPM6 Annual, Stock of portfolio investment by ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101019</th>\n",
       "      <td>BPM6 Annual, Stock of other investment by country</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101203</th>\n",
       "      <td>BPM6 Annual, Directional basis stock of total ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101219</th>\n",
       "      <td>BPM6 Annual, Directional basis stock of direct...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101235</th>\n",
       "      <td>BPM6 Annual, Directional basis international i...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118630</th>\n",
       "      <td>BPM6 Annual, Hedging by currency</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118646</th>\n",
       "      <td>BPM6 Annual, Hedging by sector</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135382</th>\n",
       "      <td>BPM6 Quarterly, International financial assets...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135520</th>\n",
       "      <td>BPM6 Quarterly, International non-equity finan...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135589</th>\n",
       "      <td>BPM6 Quarterly, External lending and debt all ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136210</th>\n",
       "      <td>BPM6 Quarterly, Int. non-equity financial inst...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136831</th>\n",
       "      <td>BPM6 Quarterly External lending and debt by se...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137120</th>\n",
       "      <td>BPM6 Quarterly, International investment posit...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137465</th>\n",
       "      <td>BPM6 Quarterly, International investment posit...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138293</th>\n",
       "      <td>BPM6 Quarterly, External lending and debt by s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141739</th>\n",
       "      <td>BPM6 Quarterly, International non-equity finan...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    Group\n",
       "98512   BPM6 Annual, International financial assets an...\n",
       "98544   BPM6 Annual, International non-equity financia...\n",
       "98560   BPM6 Annual, External lending and debt all sec...\n",
       "98720   BPM6 Annual, Int. non-equity financial instrum...\n",
       "98928   BPM6 Annual, International investment position...\n",
       "99056   BPM6 Annual, International investment position...\n",
       "99248   BPM6 Annual, External lending and debt by sect...\n",
       "100139  BPM6 Annual, International non-equity financia...\n",
       "100347  BPM6 Annual, International financial assets an...\n",
       "100971  BPM6 Annual, Directional basis stock of total ...\n",
       "100987  BPM6 Annual, Directional basis stock of direct...\n",
       "101003  BPM6 Annual, Stock of portfolio investment by ...\n",
       "101019  BPM6 Annual, Stock of other investment by country\n",
       "101203  BPM6 Annual, Directional basis stock of total ...\n",
       "101219  BPM6 Annual, Directional basis stock of direct...\n",
       "101235  BPM6 Annual, Directional basis international i...\n",
       "118630                   BPM6 Annual, Hedging by currency\n",
       "118646                     BPM6 Annual, Hedging by sector\n",
       "135382  BPM6 Quarterly, International financial assets...\n",
       "135520  BPM6 Quarterly, International non-equity finan...\n",
       "135589  BPM6 Quarterly, External lending and debt all ...\n",
       "136210  BPM6 Quarterly, Int. non-equity financial inst...\n",
       "136831  BPM6 Quarterly External lending and debt by se...\n",
       "137120  BPM6 Quarterly, International investment posit...\n",
       "137465  BPM6 Quarterly, International investment posit...\n",
       "138293  BPM6 Quarterly, External lending and debt by s...\n",
       "141739  BPM6 Quarterly, International non-equity finan..."
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1[df1['Subject']=='International Investment Position - IIP'][['Group']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}