2021-02-04_DataExtractionFlatFiles.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import openpyxl\n",
    "import os\n",
    "import re\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### read file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "excelFolder = \"ITIS_RtoNR\"\n",
    "pandasList = []\n",
    "for file in os.listdir(excelFolder):\n",
    "    df = pd.read_excel(excelFolder + '/' + file)\n",
    "    pandasList.append(df)\n",
    "\n",
    "dfRtoNR = pd.concat(pandasList).copy()\n",
    "\n",
    "#filter completed transaction\n",
    "dfRtoNR_completed = dfRtoNR.query(\"`Combined Transaction Details.Transaction Status - Statement Status` == 'C'\").copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#read id list by sector provided by labuan\n",
    "excelFile = \"data/raw/Data for ITIS - LFSA January 2021.xlsx\"\n",
    "wb_obj = openpyxl.load_workbook(excelFile) \n",
    "\n",
    "sheetNameList = wb_obj.sheetnames\n",
    "entitiesDict = {}\n",
    "for sector in sheetNameList:\n",
    "    df = pd.read_excel(excelFile, sheet_name = sector)\n",
    "    entitiesDict[sector] = list(df['Column1'].values)\n",
    "\n",
    "#remove special chars for partnerships, trusts\n",
    "entitiesDict2 = {i:[re.sub(r'/|-|\\(|\\)', '',x) for x in j] for i,j in entitiesDict.items()}\n",
    "\n",
    "idListLabuan = [k for i,j in entitiesDict2.items() for k in j]\n",
    "\n",
    "#read id list and licence no provided by labuan - 1st time\n",
    "excelFile = \"data/raw/Data  - STR, CTRs & ITIS  - LIBFC RA 2020.xlsx\"\n",
    "dfIDYL = pd.read_excel(excelFile)\n",
    "\n",
    "idListYL = [re.sub(r'/|-|\\(|\\)', '',i) for i in dfIDYL['LL Number'].values]\n",
    "\n",
    "licenseListYL = [re.sub(r'/|-|\\(|\\)', '',str(i)) for i in dfIDYL['LicenceNo'].values]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['I', 'C', nan, 'P'], dtype=object)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Combined Transaction Details.Transaction Status - Statement Status'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "58322"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(pd.isnull(dfItis_completed['Combined Transaction Details.Transaction Date']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfItis_completed['Year'] = pd.to_datetime(dfItis_completed['Combined Transaction Details.Transaction Date']).dt.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfItis_completed2020 = dfItis_completed.query(\"Year == 2020.0\").copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### from yan ling aggregate data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#yan ling's aggregate table by entities\n",
    "excelFile = \"data/raw/Labuan_result_rtonr.xlsx\"\n",
    "dfYL = pd.read_excel(excelFile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "idListYLdf = dfYL[pd.notnull(dfYL['TOTAL_MYR'])]['ID_NO'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#get id provided by labuan fsa - version2\n",
    "excelFile = \"data/raw/Data for ITIS - LFSA January 2021.xlsx\"\n",
    "wb_obj = openpyxl.load_workbook(excelFile) \n",
    "\n",
    "sheetNameList = wb_obj.sheetnames\n",
    "entitiesDict = {}\n",
    "for sector in sheetNameList:\n",
    "    df = pd.read_excel(excelFile, sheet_name = sector)\n",
    "    entitiesDict[sector] = list(df['Column1'].values)\n",
    "\n",
    "#remove slash symbol for partnerships, trusts\n",
    "#sector: list of id\n",
    "sectorIdDict = {i:[re.sub(r'/|-|\\(|\\)', '',x) for x in j] for i,j in entitiesDict.items()}\n",
    "\n",
    "idListLabuan = [k for i,j in sectorIdDict.items() for k in j]\n",
    "\n",
    "#get id provided by labuan fsa - version1\n",
    "excelFile = \"data/raw/Data  - STR, CTRs & ITIS  - LIBFC RA 2020.xlsx\"\n",
    "dfIDYL = pd.read_excel(excelFile)\n",
    "\n",
    "idListYL = [re.sub('\\/|-|\\(|\\)', '',i) for i in dfIDYL['LL Number'].values]\n",
    "\n",
    "#LL Number: LicenceNo\n",
    "dfIDYL['cleanLicenceNo'] = [re.sub('\\/|-|\\(|\\)', '',str(i)) for i in dfIDYL['LicenceNo'].values]\n",
    "#one LL Number may have more than one licenceNo\n",
    "licenceDict = dfIDYL[pd.notnull(dfIDYL['cleanLicenceNo'])]\\\n",
    "              [['cleanLicenceNo', 'LL Number']].set_index('cleanLicenceNo').to_dict()['LL Number']\n",
    "\n",
    "#list of licenceNo\n",
    "licenseListYL = dfIDYL['cleanLicenceNo'].values\n",
    "\n",
    "#licence no./id: sector\n",
    "idLicenceToSectorDict = {k:i for i,j in sectorIdDict.items() for k in j}\n",
    "licenceSectorDict = {i:idLicenceToSectorDict[j] for i,j in licenceDict.items()}\n",
    "idLicenceToSectorDict.update(licenceSectorDict)\n",
    "\n",
    "#get licence no./id list from idLicenceToSectorDict\n",
    "idFinalList = idLicenceToSectorDict.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['LT0069',\n",
       " 'LT0035',\n",
       " 'LITC110003',\n",
       " 'LT0044',\n",
       " 'LMT0077',\n",
       " 'LT0041',\n",
       " 'LITC170058',\n",
       " 'LITC170059',\n",
       " 'LT0050',\n",
       " 'LITC170050',\n",
       " 'LITC110004',\n",
       " 'LITC200065',\n",
       " 'LITC150033',\n",
       " 'LMT0080',\n",
       " 'LT0043',\n",
       " 'LT0056',\n",
       " 'LMT0058',\n",
       " 'LT0038',\n",
       " 'LITC160041',\n",
       " 'LT0057',\n",
       " 'LITC150035',\n",
       " 'LITC170056',\n",
       " 'LITC140021',\n",
       " 'LT0027',\n",
       " 'LMT0084',\n",
       " 'LMT0075',\n",
       " 'LITC170053',\n",
       " 'LITC110001',\n",
       " 'LMT0081',\n",
       " 'LMT0068',\n",
       " 'LITC170054',\n",
       " 'LMT0082',\n",
       " 'LT0039',\n",
       " 'LMT0087',\n",
       " 'LT0034',\n",
       " 'LT0065',\n",
       " 'LT0028',\n",
       " 'LT0063',\n",
       " 'LT0032',\n",
       " 'LT0042',\n",
       " 'LITC130013',\n",
       " 'LITC130018',\n",
       " 'LT0026',\n",
       " 'LITC150036',\n",
       " 'LMT0070',\n",
       " 'LITC130017',\n",
       " 'LITC160044',\n",
       " 'LMT0083',\n",
       " 'LFAMB04',\n",
       " 'LT0072',\n",
       " 'LITC130016',\n",
       " 'LMT0059 ',\n",
       " 'LMT0065',\n",
       " 'LITC200068',\n",
       " 'LITC140027',\n",
       " 'LITC160043',\n",
       " 'LITC150030',\n",
       " 'LMT0069',\n",
       " 'LMT0088',\n",
       " 'LT0074',\n",
       " 'LFAMB03',\n",
       " 'LMT0064',\n",
       " 'LITC180061',\n",
       " 'LITC120008',\n",
       " 'LITC150031',\n",
       " 'LFAMB08',\n",
       " 'LITC140025',\n",
       " 'LMT0047',\n",
       " 'LT0030',\n",
       " 'LT0078',\n",
       " 'LT0048',\n",
       " 'LITC160046',\n",
       " 'LT0054',\n",
       " 'LT0086',\n",
       " 'LITC160039',\n",
       " 'LITC110005',\n",
       " 'LITC180060',\n",
       " 'LITC200064',\n",
       " 'LT0029',\n",
       " 'LT0025',\n",
       " 'LITC200066',\n",
       " 'LMT0079',\n",
       " 'LT0059',\n",
       " 'LITC140028',\n",
       " 'LITC180063',\n",
       " 'LITC160047',\n",
       " 'LITC110002',\n",
       " 'LMT0052',\n",
       " 'LMT0053',\n",
       " 'LITC160042',\n",
       " 'LT0060',\n",
       " 'LT0045',\n",
       " 'LMT0067',\n",
       " 'LT0049',\n",
       " 'LT0033',\n",
       " 'LT0071',\n",
       " 'LT0036',\n",
       " 'LITC150034',\n",
       " 'LITC200067',\n",
       " 'LT0023',\n",
       " 'LITC160037',\n",
       " 'LITC170052',\n",
       " 'LT0076',\n",
       " 'LITC170051',\n",
       " 'LT0031',\n",
       " 'LT0073']"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[i for i in set(idFinalList).difference(idListYL) if i[0] in ['L']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6210, 6210, 6210)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#validation\n",
    "#make sure every id/licence no. - version1 is contained in idLicenceToSectorDict\n",
    "len(idListYL), sum([True for i in idListYL if i in idLicenceToSectorDict.keys()]), sum([True for i in idListYL if i in idFinalList])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7480, 7480, 7480)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#make sure every id/licence no. from yan ling's aggregate table is contained in idLicenceToSectorDict\n",
    "len(idListYLdf),  sum([True for i in idListYLdf if i in idLicenceToSectorDict.keys()]), sum([True for i in idListYLdf if i in idFinalList])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 327,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(32725, 32705.0)"
      ]
     },
     "execution_count": 327,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#compare 2017\n",
    "dfTemp2.shape[0], dfYL.query(\"YEAR == 2017\")['TXN_COUNT'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 323,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(True, True)"
      ]
     },
     "execution_count": 323,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'LL04478' in idFinallist, 'LL04288' in idFinallist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 324,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('Leasing ', 'Leasing ')"
      ]
     },
     "execution_count": 324,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idLicenceToSectorDict['LL04478'], idLicenceToSectorDict['LL04288']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 322,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(False, False)"
      ]
     },
     "execution_count": 322,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'LL04478' in dfYL['ID_NO'].values, 'LL04288' in dfYL['ID_NO'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 336,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(11739, 6)"
      ]
     },
     "execution_count": 336,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfYL.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 338,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2389"
      ]
     },
     "execution_count": 338,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(dfYL[pd.notnull(dfYL['YEAR'])]['ID_NO'].unique()), dfTemp2['']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 313,
   "metadata": {},
   "outputs": [],
   "source": [
    "a = dfYL.query(\"YEAR == 2017\")['ID_NO'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 314,
   "metadata": {
    "scrolled": true
   },
   "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>SenderName</th>\n",
       "      <th>SenderID</th>\n",
       "      <th>SenderCountry</th>\n",
       "      <th>SenderAccount</th>\n",
       "      <th>SenderRI</th>\n",
       "      <th>BeneficiaryName</th>\n",
       "      <th>BeneficiaryID</th>\n",
       "      <th>BeneficiaryCountry</th>\n",
       "      <th>BeneficiaryAccount</th>\n",
       "      <th>BeneficiaryRI</th>\n",
       "      <th>Purpose</th>\n",
       "      <th>Date</th>\n",
       "      <th>MYR</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7761908</th>\n",
       "      <td>PERISAI PACIFIC 101 L INC</td>\n",
       "      <td>LL04478</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OCBC BANK (MALAYSIA) BERHAD</td>\n",
       "      <td>PERISAI OFFSHORE SDN BHD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LABUAN OFFSHORE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2017-08-16</td>\n",
       "      <td>4000850.91</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7761909</th>\n",
       "      <td>PERISAI PACIFIC 101 L INC</td>\n",
       "      <td>LL04478</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OCBC BANK (MALAYSIA) BERHAD</td>\n",
       "      <td>PERISAI PACIFIC 101 L INC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LABUAN OFFSHORE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2017-09-19</td>\n",
       "      <td>3844833.11</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7815224</th>\n",
       "      <td>ALAM MARITIM L INC</td>\n",
       "      <td>LL04288</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>564847104776</td>\n",
       "      <td>MALAYAN BANKING BERHAD</td>\n",
       "      <td>SINGAPORE TELECOMMUNICATIONS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OTHER BUSINESS SEVICES</td>\n",
       "      <td>2017-08-17</td>\n",
       "      <td>126146.88</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7815423</th>\n",
       "      <td>ALAM MARITIM L INC</td>\n",
       "      <td>LL04288</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>564847104776</td>\n",
       "      <td>MALAYAN BANKING BERHAD</td>\n",
       "      <td>CHESTERS TECHNOSERVICE PTE LTD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GOODS</td>\n",
       "      <td>2017-09-29</td>\n",
       "      <td>81430.30</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7815424</th>\n",
       "      <td>ALAM MARITIM L INC</td>\n",
       "      <td>LL04288</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>564847104776</td>\n",
       "      <td>MALAYAN BANKING BERHAD</td>\n",
       "      <td>CHESTER TECHNOSERVICE PTE LTD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GOODS</td>\n",
       "      <td>2017-08-15</td>\n",
       "      <td>54748.05</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7815425</th>\n",
       "      <td>ALAM MARITIM L INC</td>\n",
       "      <td>LL04288</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>564847104776</td>\n",
       "      <td>MALAYAN BANKING BERHAD</td>\n",
       "      <td>PACIFIC ENGINEERING AND SERVICE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GOODS</td>\n",
       "      <td>2017-07-06</td>\n",
       "      <td>20283.05</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7815426</th>\n",
       "      <td>ALAM MARITIM L INC</td>\n",
       "      <td>LL04288</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>564847104776</td>\n",
       "      <td>MALAYAN BANKING BERHAD</td>\n",
       "      <td>PACIFIC ENGINEERING AND SERVICE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GOODS</td>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>17214.16</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7815427</th>\n",
       "      <td>ALAM MARITIM L INC</td>\n",
       "      <td>LL04288</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>564847104776</td>\n",
       "      <td>MALAYAN BANKING BERHAD</td>\n",
       "      <td>RAILKO BEARINGS S PTE LTD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GOODS</td>\n",
       "      <td>2017-09-25</td>\n",
       "      <td>29810.03</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7815428</th>\n",
       "      <td>ALAM MARITIM L INC</td>\n",
       "      <td>LL04288</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>564847104776</td>\n",
       "      <td>MALAYAN BANKING BERHAD</td>\n",
       "      <td>RAILKO BEARINGS S PTE LTD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GOODS</td>\n",
       "      <td>2017-09-27</td>\n",
       "      <td>34003.80</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7815429</th>\n",
       "      <td>ALAM MARITIM L INC</td>\n",
       "      <td>LL04288</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>564847104776</td>\n",
       "      <td>MALAYAN BANKING BERHAD</td>\n",
       "      <td>PUMPQUIP ENGINEERING PTE LTD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GOODS</td>\n",
       "      <td>2017-07-06</td>\n",
       "      <td>11173.17</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7821843</th>\n",
       "      <td>ALAM MARITIM L INC</td>\n",
       "      <td>LL04288</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>564847104776</td>\n",
       "      <td>MALAYAN BANKING BERHAD</td>\n",
       "      <td>ALAM MARITIM L INC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LABUAN OFFSHORE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LOANS</td>\n",
       "      <td>2017-07-19</td>\n",
       "      <td>312192.88</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7836465</th>\n",
       "      <td>ALAM MARITIM L INC</td>\n",
       "      <td>LL04288</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>564847104776</td>\n",
       "      <td>MALAYAN BANKING BERHAD</td>\n",
       "      <td>SCANDIA SHIPPING S PTE LTD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WORKER'S REMITTANCES</td>\n",
       "      <td>2017-09-05</td>\n",
       "      <td>2362.45</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7836466</th>\n",
       "      <td>ALAM MARITIM L INC</td>\n",
       "      <td>LL04288</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>564847104776</td>\n",
       "      <td>MALAYAN BANKING BERHAD</td>\n",
       "      <td>MENTRADE MARINE ENGINEERING</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WORKER'S REMITTANCES</td>\n",
       "      <td>2017-09-27</td>\n",
       "      <td>6201.65</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7862978</th>\n",
       "      <td>ALAM MARITIM L INC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LABUAN OFFSHORE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ALAM MARITIM L INC</td>\n",
       "      <td>LL04288</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>66431700000237</td>\n",
       "      <td>RHB ISLAMIC BANK BERHAD</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2017-07-21</td>\n",
       "      <td>309628.60</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7929520</th>\n",
       "      <td>PERISAI PACIFIC 101 L INC</td>\n",
       "      <td>LL04478</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OCBC BANK (MALAYSIA) BERHAD</td>\n",
       "      <td>PERISAI PACIFIC 101 L INC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LABUAN OFFSHORE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2017-10-19</td>\n",
       "      <td>3800165.73</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7929521</th>\n",
       "      <td>PERISAI PACIFIC 101 L INC</td>\n",
       "      <td>LL04478</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OCBC BANK (MALAYSIA) BERHAD</td>\n",
       "      <td>PERISAI PACIFIC 101 L INC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LABUAN OFFSHORE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2017-10-19</td>\n",
       "      <td>91600.19</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7929522</th>\n",
       "      <td>PERISAI PACIFIC 101 L INC</td>\n",
       "      <td>LL04478</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OCBC BANK (MALAYSIA) BERHAD</td>\n",
       "      <td>PERISAI PACIFIC 101 L INC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LABUAN OFFSHORE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2017-11-16</td>\n",
       "      <td>3873426.60</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7929523</th>\n",
       "      <td>PERISAI PACIFIC 101 L INC</td>\n",
       "      <td>LL04478</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OCBC BANK (MALAYSIA) BERHAD</td>\n",
       "      <td>PERISAI PACIFIC 101 L INC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LABUAN OFFSHORE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2017-11-30</td>\n",
       "      <td>96952.19</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7929524</th>\n",
       "      <td>PERISAI PACIFIC 101 L INC</td>\n",
       "      <td>LL04478</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OCBC BANK (MALAYSIA) BERHAD</td>\n",
       "      <td>PERISAI PACIFIC 101 L INC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LABUAN OFFSHORE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2017-12-18</td>\n",
       "      <td>3695800.69</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7989548</th>\n",
       "      <td>ALAM MARITIM L INC</td>\n",
       "      <td>LL04288</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>564847104776</td>\n",
       "      <td>MALAYAN BANKING BERHAD</td>\n",
       "      <td>FARSTAD SUPPLY AS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Australia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OTHER BUSINESS SEVICES</td>\n",
       "      <td>2017-10-05</td>\n",
       "      <td>127941.00</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        SenderName SenderID    SenderCountry SenderAccount  \\\n",
       "7761908  PERISAI PACIFIC 101 L INC  LL04478         Malaysia           NaN   \n",
       "7761909  PERISAI PACIFIC 101 L INC  LL04478         Malaysia           NaN   \n",
       "7815224         ALAM MARITIM L INC  LL04288         Malaysia  564847104776   \n",
       "7815423         ALAM MARITIM L INC  LL04288         Malaysia  564847104776   \n",
       "7815424         ALAM MARITIM L INC  LL04288         Malaysia  564847104776   \n",
       "7815425         ALAM MARITIM L INC  LL04288         Malaysia  564847104776   \n",
       "7815426         ALAM MARITIM L INC  LL04288         Malaysia  564847104776   \n",
       "7815427         ALAM MARITIM L INC  LL04288         Malaysia  564847104776   \n",
       "7815428         ALAM MARITIM L INC  LL04288         Malaysia  564847104776   \n",
       "7815429         ALAM MARITIM L INC  LL04288         Malaysia  564847104776   \n",
       "7821843         ALAM MARITIM L INC  LL04288         Malaysia  564847104776   \n",
       "7836465         ALAM MARITIM L INC  LL04288         Malaysia  564847104776   \n",
       "7836466         ALAM MARITIM L INC  LL04288         Malaysia  564847104776   \n",
       "7862978         ALAM MARITIM L INC      NaN  LABUAN OFFSHORE           NaN   \n",
       "7929520  PERISAI PACIFIC 101 L INC  LL04478         Malaysia           NaN   \n",
       "7929521  PERISAI PACIFIC 101 L INC  LL04478         Malaysia           NaN   \n",
       "7929522  PERISAI PACIFIC 101 L INC  LL04478         Malaysia           NaN   \n",
       "7929523  PERISAI PACIFIC 101 L INC  LL04478         Malaysia           NaN   \n",
       "7929524  PERISAI PACIFIC 101 L INC  LL04478         Malaysia           NaN   \n",
       "7989548         ALAM MARITIM L INC  LL04288         Malaysia  564847104776   \n",
       "\n",
       "                            SenderRI                  BeneficiaryName  \\\n",
       "7761908  OCBC BANK (MALAYSIA) BERHAD         PERISAI OFFSHORE SDN BHD   \n",
       "7761909  OCBC BANK (MALAYSIA) BERHAD        PERISAI PACIFIC 101 L INC   \n",
       "7815224       MALAYAN BANKING BERHAD     SINGAPORE TELECOMMUNICATIONS   \n",
       "7815423       MALAYAN BANKING BERHAD   CHESTERS TECHNOSERVICE PTE LTD   \n",
       "7815424       MALAYAN BANKING BERHAD    CHESTER TECHNOSERVICE PTE LTD   \n",
       "7815425       MALAYAN BANKING BERHAD  PACIFIC ENGINEERING AND SERVICE   \n",
       "7815426       MALAYAN BANKING BERHAD  PACIFIC ENGINEERING AND SERVICE   \n",
       "7815427       MALAYAN BANKING BERHAD        RAILKO BEARINGS S PTE LTD   \n",
       "7815428       MALAYAN BANKING BERHAD        RAILKO BEARINGS S PTE LTD   \n",
       "7815429       MALAYAN BANKING BERHAD     PUMPQUIP ENGINEERING PTE LTD   \n",
       "7821843       MALAYAN BANKING BERHAD               ALAM MARITIM L INC   \n",
       "7836465       MALAYAN BANKING BERHAD       SCANDIA SHIPPING S PTE LTD   \n",
       "7836466       MALAYAN BANKING BERHAD      MENTRADE MARINE ENGINEERING   \n",
       "7862978                          NaN               ALAM MARITIM L INC   \n",
       "7929520  OCBC BANK (MALAYSIA) BERHAD        PERISAI PACIFIC 101 L INC   \n",
       "7929521  OCBC BANK (MALAYSIA) BERHAD        PERISAI PACIFIC 101 L INC   \n",
       "7929522  OCBC BANK (MALAYSIA) BERHAD        PERISAI PACIFIC 101 L INC   \n",
       "7929523  OCBC BANK (MALAYSIA) BERHAD        PERISAI PACIFIC 101 L INC   \n",
       "7929524  OCBC BANK (MALAYSIA) BERHAD        PERISAI PACIFIC 101 L INC   \n",
       "7989548       MALAYAN BANKING BERHAD                FARSTAD SUPPLY AS   \n",
       "\n",
       "        BeneficiaryID BeneficiaryCountry BeneficiaryAccount  \\\n",
       "7761908           NaN    LABUAN OFFSHORE                NaN   \n",
       "7761909           NaN    LABUAN OFFSHORE                NaN   \n",
       "7815224           NaN          Singapore                NaN   \n",
       "7815423           NaN          Singapore                NaN   \n",
       "7815424           NaN          Singapore                NaN   \n",
       "7815425           NaN          Singapore                NaN   \n",
       "7815426           NaN          Singapore                NaN   \n",
       "7815427           NaN          Singapore                NaN   \n",
       "7815428           NaN          Singapore                NaN   \n",
       "7815429           NaN          Singapore                NaN   \n",
       "7821843           NaN    LABUAN OFFSHORE                NaN   \n",
       "7836465           NaN          Singapore                NaN   \n",
       "7836466           NaN          Singapore                NaN   \n",
       "7862978       LL04288           Malaysia     66431700000237   \n",
       "7929520           NaN    LABUAN OFFSHORE                NaN   \n",
       "7929521           NaN    LABUAN OFFSHORE                NaN   \n",
       "7929522           NaN    LABUAN OFFSHORE                NaN   \n",
       "7929523           NaN    LABUAN OFFSHORE                NaN   \n",
       "7929524           NaN    LABUAN OFFSHORE                NaN   \n",
       "7989548           NaN          Australia                NaN   \n",
       "\n",
       "                   BeneficiaryRI                 Purpose        Date  \\\n",
       "7761908                      NaN                 Unknown  2017-08-16   \n",
       "7761909                      NaN                 Unknown  2017-09-19   \n",
       "7815224                      NaN  OTHER BUSINESS SEVICES  2017-08-17   \n",
       "7815423                      NaN                   GOODS  2017-09-29   \n",
       "7815424                      NaN                   GOODS  2017-08-15   \n",
       "7815425                      NaN                   GOODS  2017-07-06   \n",
       "7815426                      NaN                   GOODS  2017-07-31   \n",
       "7815427                      NaN                   GOODS  2017-09-25   \n",
       "7815428                      NaN                   GOODS  2017-09-27   \n",
       "7815429                      NaN                   GOODS  2017-07-06   \n",
       "7821843                      NaN                   LOANS  2017-07-19   \n",
       "7836465                      NaN    WORKER'S REMITTANCES  2017-09-05   \n",
       "7836466                      NaN    WORKER'S REMITTANCES  2017-09-27   \n",
       "7862978  RHB ISLAMIC BANK BERHAD                 Unknown  2017-07-21   \n",
       "7929520                      NaN                 Unknown  2017-10-19   \n",
       "7929521                      NaN                 Unknown  2017-10-19   \n",
       "7929522                      NaN                 Unknown  2017-11-16   \n",
       "7929523                      NaN                 Unknown  2017-11-30   \n",
       "7929524                      NaN                 Unknown  2017-12-18   \n",
       "7989548                      NaN  OTHER BUSINESS SEVICES  2017-10-05   \n",
       "\n",
       "                MYR    Year  \n",
       "7761908  4000850.91  2017.0  \n",
       "7761909  3844833.11  2017.0  \n",
       "7815224   126146.88  2017.0  \n",
       "7815423    81430.30  2017.0  \n",
       "7815424    54748.05  2017.0  \n",
       "7815425    20283.05  2017.0  \n",
       "7815426    17214.16  2017.0  \n",
       "7815427    29810.03  2017.0  \n",
       "7815428    34003.80  2017.0  \n",
       "7815429    11173.17  2017.0  \n",
       "7821843   312192.88  2017.0  \n",
       "7836465     2362.45  2017.0  \n",
       "7836466     6201.65  2017.0  \n",
       "7862978   309628.60  2017.0  \n",
       "7929520  3800165.73  2017.0  \n",
       "7929521    91600.19  2017.0  \n",
       "7929522  3873426.60  2017.0  \n",
       "7929523    96952.19  2017.0  \n",
       "7929524  3695800.69  2017.0  \n",
       "7989548   127941.00  2017.0  "
      ]
     },
     "execution_count": 314,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfTemp2[(~(dfTemp2['BeneficiaryID'].isin(a))) &\n",
    "                     (~(dfTemp2['SenderID'].isin(a)))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": true
   },
   "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>YEAR</th>\n",
       "      <th>TXN_TYPE</th>\n",
       "      <th>TOTAL_MYR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>2.220020e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>3.281338e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>3.254583e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>3.307535e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>7.779353e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>9.282243e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>1.315197e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>1.155764e+11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     YEAR TXN_TYPE     TOTAL_MYR\n",
       "0  2016.0  RECEIVE  2.220020e+08\n",
       "1  2016.0     SEND  3.281338e+08\n",
       "2  2017.0  RECEIVE  3.254583e+10\n",
       "3  2017.0     SEND  3.307535e+10\n",
       "4  2018.0  RECEIVE  7.779353e+10\n",
       "5  2018.0     SEND  9.282243e+10\n",
       "6  2019.0  RECEIVE  1.315197e+11\n",
       "7  2019.0     SEND  1.155764e+11"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfYL.groupby([\"YEAR\", \"TXN_TYPE\"])['TOTAL_MYR'].sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>YEAR</th>\n",
       "      <th>TXN_TYPE</th>\n",
       "      <th>TXN_COUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>1371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>1136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>1712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>1618</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     YEAR TXN_TYPE  TXN_COUNT\n",
       "0  2016.0  RECEIVE          3\n",
       "1  2016.0     SEND          5\n",
       "2  2017.0  RECEIVE        894\n",
       "3  2017.0     SEND        741\n",
       "4  2018.0  RECEIVE       1371\n",
       "5  2018.0     SEND       1136\n",
       "6  2019.0  RECEIVE       1712\n",
       "7  2019.0     SEND       1618"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfYL.groupby([\"YEAR\", \"TXN_TYPE\"])['TXN_COUNT'].count().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 301,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((223188, 14), 223127.0)"
      ]
     },
     "execution_count": 301,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfTemp2.shape, dfYL['TXN_COUNT'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 302,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(483986476107.28, 483883415939.47)"
      ]
     },
     "execution_count": 302,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfTemp2['MYR'].sum(), dfYL['TOTAL_MYR'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['SenderName', 'SenderID', 'SenderCountry', 'SenderAccount', 'SenderRI',\n",
       "       'BeneficiaryName', 'BeneficiaryID', 'BeneficiaryCountry',\n",
       "       'BeneficiaryAccount', 'BeneficiaryRI', 'Purpose', 'Date', 'MYR',\n",
       "       'Year'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 282,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfTemp2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 341,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2389"
      ]
     },
     "execution_count": 341,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "senderList = set(dfTemp2['SenderID']) & set(idFinallist)\n",
    "beneList = set(dfTemp2['BeneficiaryID']) & set(idFinallist)\n",
    "len(senderList.union(beneList) & set(dfYL[pd.notnull(dfYL['YEAR'])]['ID_NO']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>YEAR</th>\n",
       "      <th>TXN_TYPE</th>\n",
       "      <th>TXN_COUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>15118.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>17587.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>34064.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>41928.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>46107.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>68271.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     YEAR TXN_TYPE  TXN_COUNT\n",
       "0  2016.0  RECEIVE       22.0\n",
       "1  2016.0     SEND       30.0\n",
       "2  2017.0  RECEIVE    15118.0\n",
       "3  2017.0     SEND    17587.0\n",
       "4  2018.0  RECEIVE    34064.0\n",
       "5  2018.0     SEND    41928.0\n",
       "6  2019.0  RECEIVE    46107.0\n",
       "7  2019.0     SEND    68271.0"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfYL.groupby([\"YEAR\", \"TXN_TYPE\"])['TXN_COUNT'].sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "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>ID_NO</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>TXN_TYPE</th>\n",
       "      <th>TXN_COUNT</th>\n",
       "      <th>TOTAL_MYR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>140115C</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.526731e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>930012C</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.891949e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>930016C</td>\n",
       "      <td>2019.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.161400e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>940026C</td>\n",
       "      <td>2019.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.512854e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>960051C</td>\n",
       "      <td>2019.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8.976334e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ID_NO    YEAR TXN_TYPE  TXN_COUNT     TOTAL_MYR\n",
       "0  140115C  2017.0     SEND        1.0  6.526731e+05\n",
       "1  930012C  2017.0     SEND        1.0  2.891949e+04\n",
       "2  930016C  2019.0     SEND        1.0  1.161400e+08\n",
       "3  940026C  2019.0  RECEIVE        1.0  2.512854e+06\n",
       "4  960051C  2019.0     SEND        1.0  8.976334e+06"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfYL.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "metadata": {},
   "outputs": [],
   "source": [
    "#map entities into sector\n",
    "dfYL['Sector'] = dfYL['ID_NO'].map(idLicenceToSectorDict)\n",
    "\n",
    "#aggregation\n",
    "dfYLAggAmount = dfYL.groupby([\"YEAR\", \"TXN_TYPE\", \"Sector\"])['TOTAL_MYR'].sum().reset_index()\n",
    "\n",
    "dfYLAggNoTrans = dfYL.groupby([\"YEAR\", \"TXN_TYPE\", \"Sector\"])['TXN_COUNT'].sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Banks ', 'Foundations ', 'Insurance ', 'Labuan Companies', 'LITC',\n",
       "       'Leasing ', 'Money Broker ', 'Trust Company ',\n",
       "       'Securities Licensee', 'Partnerships', 'Trusts'], dtype=object)"
      ]
     },
     "execution_count": 243,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfYL['Sector'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [],
   "source": [
    "excelFile = \"data/interim/2021-02-02_ItisAggTill2019.xlsx\"\n",
    "with pd.ExcelWriter(excelFile) as writer:\n",
    "    dfYLAggNoTrans.to_excel(writer, sheet_name = 'NoTrans')\n",
    "    dfYLAggAmount.to_excel(writer, sheet_name = 'TotalAmount')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [],
   "source": [
    "#2020 - r to nr flat file\n",
    "idFinallist = list(idLicenceToSectorDict.keys())\n",
    "dfItis_match2020 = dfItis_completed2020.query(\"`Combined Transaction Details.Beneficiary - Remitter Identification Number` in @idFinallist\").copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfTemp = dfItis_completed.query(\"`Combined Transaction Details.Beneficiary - Remitter Identification Number` in @idFinallist\").copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((0, 15), (1100563, 15), (69, 15), (223195, 14), (43071, 14))"
      ]
     },
     "execution_count": 191,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfItis_match2020.shape, dfItis_completed2020.shape, dfTemp.shape, dfTemp2.shape, dfTemp3.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Users\\figohjs\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3063: DtypeWarning: Columns (3,6,8,9) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "excelFile = \"data/raw/itisuptoApr2020.csv\"\n",
    "dfItis2 = pd.read_csv(excelFile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfItis2['Year'] = pd.to_datetime(dfItis2['Date']).dt.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfItisNo2020 = dfItis2.query(\"Year != 2020\").copy()\n",
    "\n",
    "dfItis2020 = dfItis2.query(\"Year == 2020\").copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((9501850, 14), (767066, 14))"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfItisNo2020.shape, dfItis2020.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 339,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfItisNo2020 = dfItis2.query(\"Year in [2016, 2017, 2018, 2019]\").copy()\n",
    "\n",
    "dfTemp2 = dfItisNo2020[((dfItisNo2020['BeneficiaryID'].isin(idFinallist))) |\n",
    "                     ((dfItisNo2020['SenderID'].isin(idFinallist)))].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfTemp3 = dfItis2020[(dfItis2020['BeneficiaryID'].isin(idFinallist)) |\n",
    "                     (dfItis2020['SenderID'].isin(idFinallist))].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### By Country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Users\\figohjs\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3063: DtypeWarning: Columns (3,6,8,9) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "excelFile = \"data/raw/itisuptoApr2020.csv\"\n",
    "dfItisYL = pd.read_csv(excelFile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['SenderName', 'SenderID', 'SenderCountry', 'SenderAccount', 'SenderRI',\n",
       "       'BeneficiaryName', 'BeneficiaryID', 'BeneficiaryCountry',\n",
       "       'BeneficiaryAccount', 'BeneficiaryRI', 'Purpose', 'Date', 'MYR'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfItisYL.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfItisYL['Year'] = pd.to_datetime(dfItisYL['Date']).dt.year\n",
    "\n",
    "dfItisNo2020 = dfItisYL.query(\"Year in [2016, 2017, 2018, 2019]\").copy()\n",
    "\n",
    "dfItisNo2020 = dfItisNo2020[((dfItisNo2020['BeneficiaryID'].isin(idFinalList))) |\n",
    "                            ((dfItisNo2020['SenderID'].isin(idFinalList)))].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2016., 2017., 2018., 2019.])"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfItisNo2020['Year'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "tableList = []\n",
    "for year in dfItisNo2020['Year'].unique():\n",
    "    colList = ['SenderID', 'Year', 'SenderCountry', 'MYR']\n",
    "    dfTemp = dfItisNo2020[(dfItisNo2020['SenderID'].isin(idFinalList)) & \n",
    "                          (pd.notnull(dfItisNo2020['SenderID'])) &\n",
    "                          (dfItisNo2020['Year'] == year)][colList].copy()\n",
    "    dfTemp['TXN_TYPE'] = 'SEND'\n",
    "    dfTemp.rename(columns = {'SenderID':\"ID_NO\", 'Year':\"YEAR\", 'SenderCountry': 'COUNTRY'}, inplace = True)\n",
    "    tableList.append(dfTemp)\n",
    "    \n",
    "    colList = ['BeneficiaryID', 'Year', 'BeneficiaryCountry', 'MYR']\n",
    "    dfTemp = dfItisNo2020[(dfItisNo2020['BeneficiaryID'].isin(idFinalList)) & \n",
    "                          (pd.notnull(dfItisNo2020['BeneficiaryID'])) &\n",
    "                          (dfItisNo2020['Year'] == year)][colList].copy()\n",
    "    dfTemp['TXN_TYPE'] = 'RECEIPT'\n",
    "    dfTemp.rename(columns = {'BeneficiaryID':\"ID_NO\", 'Year':\"YEAR\", 'BeneficiaryCountry': 'COUNTRY'}, inplace = True)\n",
    "    tableList.append(dfTemp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfItisNo2020_v2 = pd.concat(tableList).copy()\n",
    "\n",
    "#map entities into sector\n",
    "dfItisNo2020_v2['SECTOR'] = dfItisNo2020_v2['ID_NO'].map(idLicenceToSectorDict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2389, 2391, 2389)"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = set(dfYL[pd.notnull(dfYL['YEAR'])]['ID_NO'].values)\n",
    "b = set(dfItisNo2020_v2['ID_NO'].values)\n",
    "\n",
    "#b has 2 more elements than a, a is a proper subset of b\n",
    "len(a), len(b), len(a&b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'LL04288', 'LL04478'}"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "b.difference(a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "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>YEAR</th>\n",
       "      <th>TXN_TYPE</th>\n",
       "      <th>ID_NO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>15119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>17606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>34064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>41950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>46109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>68288</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     YEAR TXN_TYPE  ID_NO\n",
       "0  2016.0  RECEIPT     22\n",
       "1  2016.0     SEND     30\n",
       "2  2017.0  RECEIPT  15119\n",
       "3  2017.0     SEND  17606\n",
       "4  2018.0  RECEIPT  34064\n",
       "5  2018.0     SEND  41950\n",
       "6  2019.0  RECEIPT  46109\n",
       "7  2019.0     SEND  68288"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#validation\n",
    "dfItisNo2020_v2.groupby([\"YEAR\", \"TXN_TYPE\"])['ID_NO'].count().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "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>YEAR</th>\n",
       "      <th>TXN_TYPE</th>\n",
       "      <th>TXN_COUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>15118.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>17587.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>34064.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>41928.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>46107.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>68271.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     YEAR TXN_TYPE  TXN_COUNT\n",
       "0  2016.0  RECEIVE       22.0\n",
       "1  2016.0     SEND       30.0\n",
       "2  2017.0  RECEIVE    15118.0\n",
       "3  2017.0     SEND    17587.0\n",
       "4  2018.0  RECEIVE    34064.0\n",
       "5  2018.0     SEND    41928.0\n",
       "6  2019.0  RECEIVE    46107.0\n",
       "7  2019.0     SEND    68271.0"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfYL.groupby([\"YEAR\", \"TXN_TYPE\"])['TXN_COUNT'].sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "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>YEAR</th>\n",
       "      <th>TXN_TYPE</th>\n",
       "      <th>ID_NO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>15119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>17606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>34064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>41950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>46109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>68288</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     YEAR TXN_TYPE  ID_NO\n",
       "0  2016.0  RECEIPT     22\n",
       "1  2016.0     SEND     30\n",
       "2  2017.0  RECEIPT  15119\n",
       "3  2017.0     SEND  17606\n",
       "4  2018.0  RECEIPT  34064\n",
       "5  2018.0     SEND  41950\n",
       "6  2019.0  RECEIPT  46109\n",
       "7  2019.0     SEND  68288"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfAggNoTrans.groupby([\"YEAR\", \"TXN_TYPE\"])['ID_NO'].sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>YEAR</th>\n",
       "      <th>TXN_TYPE</th>\n",
       "      <th>MYR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>2.220020e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>3.281338e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>3.254614e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>3.309558e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>7.779353e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>9.287342e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>1.315200e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>1.156077e+11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     YEAR TXN_TYPE           MYR\n",
       "0  2016.0  RECEIPT  2.220020e+08\n",
       "1  2016.0     SEND  3.281338e+08\n",
       "2  2017.0  RECEIPT  3.254614e+10\n",
       "3  2017.0     SEND  3.309558e+10\n",
       "4  2018.0  RECEIPT  7.779353e+10\n",
       "5  2018.0     SEND  9.287342e+10\n",
       "6  2019.0  RECEIPT  1.315200e+11\n",
       "7  2019.0     SEND  1.156077e+11"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfItisNo2020_v2.groupby([\"YEAR\", \"TXN_TYPE\"])['MYR'].sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "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>YEAR</th>\n",
       "      <th>TXN_TYPE</th>\n",
       "      <th>TOTAL_MYR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>2.220020e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>3.281338e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>3.254614e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>3.309558e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>7.779353e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>9.287342e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>1.315200e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>1.156077e+11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     YEAR TXN_TYPE     TOTAL_MYR\n",
       "0  2016.0  RECEIPT  2.220020e+08\n",
       "1  2016.0     SEND  3.281338e+08\n",
       "2  2017.0  RECEIPT  3.254614e+10\n",
       "3  2017.0     SEND  3.309558e+10\n",
       "4  2018.0  RECEIPT  7.779353e+10\n",
       "5  2018.0     SEND  9.287342e+10\n",
       "6  2019.0  RECEIPT  1.315200e+11\n",
       "7  2019.0     SEND  1.156077e+11"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfAggAmount.groupby([\"YEAR\", \"TXN_TYPE\"])['TOTAL_MYR'].sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>YEAR</th>\n",
       "      <th>TXN_TYPE</th>\n",
       "      <th>TOTAL_MYR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>2.220020e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>3.281338e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>3.254583e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>3.307535e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>7.779353e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>9.282243e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>1.315197e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>SEND</td>\n",
       "      <td>1.155764e+11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     YEAR TXN_TYPE     TOTAL_MYR\n",
       "0  2016.0  RECEIVE  2.220020e+08\n",
       "1  2016.0     SEND  3.281338e+08\n",
       "2  2017.0  RECEIVE  3.254583e+10\n",
       "3  2017.0     SEND  3.307535e+10\n",
       "4  2018.0  RECEIVE  7.779353e+10\n",
       "5  2018.0     SEND  9.282243e+10\n",
       "6  2019.0  RECEIVE  1.315197e+11\n",
       "7  2019.0     SEND  1.155764e+11"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfYL.groupby([\"YEAR\", \"TXN_TYPE\"])['TOTAL_MYR'].sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "metadata": {},
   "outputs": [],
   "source": [
    "#aggregation\n",
    "dfAggAmount = dfItisNo2020_v2.groupby([\"YEAR\", \"TXN_TYPE\", \"SECTOR\"])['MYR'].sum().reset_index()\n",
    "dfAggAmount.rename(columns = {'MYR':'TOTAL_MYR'}, inplace = True)\n",
    "\n",
    "dfAggNoTrans = dfItisNo2020_v2.groupby([\"YEAR\", \"TXN_TYPE\", \"SECTOR\"])['ID_NO'].count().reset_index()\n",
    "dfAggNoTrans.rename(columns = {'ID_NO':'TXN_COUNT'}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'COUNTRY'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexes\\base.py\u001b[0m in \u001b[0;36mget_loc\u001b[1;34m(self, key, method, tolerance)\u001b[0m\n\u001b[0;32m   2645\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2646\u001b[1;33m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2647\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: 'COUNTRY'",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-228-0515e2b6e522>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdfAggAmount\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'COUNTRY'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munique\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   2798\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mnlevels\u001b[0m \u001b[1;33m>\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2799\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_getitem_multilevel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2800\u001b[1;33m             \u001b[0mindexer\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2801\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mis_integer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mindexer\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2802\u001b[0m                 \u001b[0mindexer\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0mindexer\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexes\\base.py\u001b[0m in \u001b[0;36mget_loc\u001b[1;34m(self, key, method, tolerance)\u001b[0m\n\u001b[0;32m   2646\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2647\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2648\u001b[1;33m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_maybe_cast_indexer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2649\u001b[0m         \u001b[0mindexer\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_indexer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtolerance\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mtolerance\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2650\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mindexer\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mndim\u001b[0m \u001b[1;33m>\u001b[0m \u001b[1;36m1\u001b[0m \u001b[1;32mor\u001b[0m \u001b[0mindexer\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msize\u001b[0m \u001b[1;33m>\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: 'COUNTRY'"
     ]
    }
   ],
   "source": [
    "dfAggAmount['COUNTRY'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Malaysia'], dtype=object)"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfAggNoTrans['COUNTRY'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {},
   "outputs": [],
   "source": [
    "excelFile = \"data/interim/2021-02-04_ItisAggTill2019.xlsx\"\n",
    "with pd.ExcelWriter(excelFile) as writer:\n",
    "    dfAggNoTrans.to_excel(writer, sheet_name = 'NoTrans')\n",
    "    dfAggAmount.to_excel(writer, sheet_name = 'TotalAmount')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "tableList2 = []\n",
    "for year in dfItisNo2020['Year'].unique():\n",
    "    colList = ['SenderID', 'Year', 'BeneficiaryCountry', 'MYR']\n",
    "    dfTemp = dfItisNo2020[(dfItisNo2020['SenderID'].isin(idFinalList)) & \n",
    "                          (pd.notnull(dfItisNo2020['SenderID'])) &\n",
    "                          (dfItisNo2020['Year'] == year)][colList].copy()\n",
    "    dfTemp['TXN_TYPE'] = 'SEND'\n",
    "    dfTemp.rename(columns = {'SenderID':\"ID_NO\", 'Year':\"YEAR\", 'BeneficiaryCountry': 'COUNTRY'}, inplace = True)\n",
    "    tableList2.append(dfTemp)\n",
    "    \n",
    "    colList = ['BeneficiaryID', 'Year', 'SenderCountry', 'MYR']\n",
    "    dfTemp = dfItisNo2020[(dfItisNo2020['BeneficiaryID'].isin(idFinalList)) & \n",
    "                          (pd.notnull(dfItisNo2020['BeneficiaryID'])) &\n",
    "                          (dfItisNo2020['Year'] == year)][colList].copy()\n",
    "    dfTemp['TXN_TYPE'] = 'RECEIPT'\n",
    "    dfTemp.rename(columns = {'BeneficiaryID':\"ID_NO\", 'Year':\"YEAR\", 'SenderCountry': 'COUNTRY'}, inplace = True)\n",
    "    tableList2.append(dfTemp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfItisNo2020_v3 = pd.concat(tableList2).copy()\n",
    "\n",
    "#map entities into sector\n",
    "dfItisNo2020_v3['SECTOR'] = dfItisNo2020_v3['ID_NO'].map(idLicenceToSectorDict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['United Arab Emirates', 'Singapore', 'Hong Kong', 'India',\n",
       "       'Ireland', 'United States', 'China', 'Germany', 'Thailand',\n",
       "       'Viet Nam', 'LABUAN OFFSHORE', 'Cambodia', 'Indonesia',\n",
       "       'Netherlands', 'United Kingdom', 'Australia', 'Austria', 'Italy',\n",
       "       'Latvia', 'Oman', 'Russian Federation', 'Saudi Arabia',\n",
       "       'Sri Lanka', 'Switzerland', 'Argentina', 'Bahrain', 'Belgium',\n",
       "       'Brunei Darussalam', 'Canada', 'Chile', 'Cyprus', 'Czech Republic',\n",
       "       'Denmark', 'Estonia', 'Finland', 'Hungary', 'Isle of Man', 'Japan',\n",
       "       'Jersey', 'Kenya', 'Korea, Republic of', 'Kuwait', 'Mauritius',\n",
       "       'Moldova, Republic of', 'New Zealand', 'Norway', 'Panama',\n",
       "       'Papua New Guinea', 'Philippines', 'South Africa', 'Spain',\n",
       "       'Sweden', 'Taiwan, Province of China', 'Turkey', 'Uruguay',\n",
       "       'Colombia', 'Kyrgyzstan', 'Peru', 'Bangladesh',\n",
       "       'Bosnia and Herzegovina', 'Brazil', 'Maldives', 'France',\n",
       "       'Guernsey', 'Qatar', 'Luxembourg', 'Lithuania', 'Norfolk Island',\n",
       "       'Others', 'Saint Martin (French Part)', 'Virgin Islands, British',\n",
       "       'Egypt', 'Liechtenstein', 'Romania', 'Pakistan', 'Mongolia',\n",
       "       'Lebanon', 'Ghana', 'Ecuador', 'Cayman Islands', 'Slovenia',\n",
       "       'Poland', 'Afghanistan', 'Croatia', 'Djibouti',\n",
       "       'EUROPEAN COMMUNITY', 'Greece', 'Guatemala', 'Iceland', 'Jordan',\n",
       "       'Mexico', 'Portugal', 'Saint Lucia', 'Senegal', 'Togo', 'Nigeria',\n",
       "       'Saint Kitts and Nevis', 'Kazakhstan', 'Gibraltar', 'Macao',\n",
       "       'Mozambique', 'Nepal', 'Bermuda', 'El Salvador',\n",
       "       'Trinidad and Tobago', 'Iraq', 'Myanmar', 'Libyan Arab Jamahiriya',\n",
       "       'Puerto Rico', 'Malta', 'Serbia', 'Congo', 'Madagascar', 'Yemen',\n",
       "       'Algeria', 'Tanzania, United Republic of', 'Botswana',\n",
       "       'Solomon Islands', 'Slovakia', 'Fiji', 'Armenia', 'Bahamas',\n",
       "       'Bulgaria', 'Saint Vincent and the Grenadines',\n",
       "       'Syrian Arab Republic', 'Antarctica', 'Bolivia', 'Cape Verde',\n",
       "       'Georgia', 'Palestinian Territory, Occupied',\n",
       "       'NEUTRAL ZONE (BETWEEN SAUDI ARABIA & IRAQ)',\n",
       "       'Congo, The Democratic Republic of the',\n",
       "       \"Lao People's Democratic Republic\", 'Seychelles', 'Gabon',\n",
       "       'Marshall Islands', 'Vanuatu', 'Ukraine', 'Guyana', 'Morocco',\n",
       "       'Azerbaijan', 'Guadeloupe', 'Samoa', 'Iran, Islamic Republic of',\n",
       "       'Saint Barthélemy', 'Belize', 'Eritrea', 'Uganda', 'Costa Rica',\n",
       "       'Monaco', 'Montenegro', 'Angola', 'Burundi', 'Namibia', 'Bhutan',\n",
       "       'Venezuela', 'Jamaica', 'Uzbekistan', 'French Guiana', 'Rwanda',\n",
       "       'Zambia', 'Tunisia', 'Honduras', 'Suriname', 'Belarus',\n",
       "       'Turkmenistan', 'Barbados', 'Saint Helena', 'Equatorial Guinea',\n",
       "       'Guinea', 'Dominican Republic', 'Mauritania', 'Niger',\n",
       "       'Holy See (Vatican City State)', 'Bouvet Island', 'New Caledonia',\n",
       "       'Western Sahara', 'Paraguay', 'Albania', 'Haiti',\n",
       "       'Macedonia, The Former Yugoslav Republic of'], dtype=object)"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfItisNo2020_v3['COUNTRY'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [],
   "source": [
    "#aggregation\n",
    "dfAggAmount = dfItisNo2020_v3.groupby([\"YEAR\", \"COUNTRY\", \"TXN_TYPE\", \"SECTOR\"])['MYR'].sum().reset_index()\n",
    "dfAggAmount.rename(columns = {'MYR':'TOTAL_MYR'}, inplace = True)\n",
    "\n",
    "dfAggNoTrans = dfItisNo2020_v3.groupby([\"YEAR\", \"COUNTRY\", \"TXN_TYPE\", \"SECTOR\"])['ID_NO'].count().reset_index()\n",
    "dfAggNoTrans.rename(columns = {'ID_NO':'TXN_COUNT'}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [],
   "source": [
    "excelFile = \"data/interim/2021-02-04_ItisAggTill2019_v2.xlsx\"\n",
    "with pd.ExcelWriter(excelFile) as writer:\n",
    "    dfAggNoTrans.to_excel(writer, index = False, sheet_name = 'NoTrans')\n",
    "    dfAggAmount.to_excel(writer, index = False, sheet_name = 'TotalAmount')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### NR to NR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "excelFolder = \"ITIS_NR-and-NR/data\"\n",
    "pandasList = []\n",
    "for file in os.listdir(excelFolder):\n",
    "    df = pd.read_excel(excelFolder + '/' + file)\n",
    "    pandasList.append(df)\n",
    "    \n",
    "dfNRtoNR = pd.concat(pandasList).copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['NR - MYR Amount', 'Country.Country Description',\n",
       "       'NR Customer Details.Customer Beneficiary Remitter Account Number',\n",
       "       'NR Customer Details.Customer Beneficiary Remitter Identification Number',\n",
       "       'NR Customer Details.Customer Beneficiary Remitter Name',\n",
       "       'NR Transaction Details.Beneficiary Remitter Identification Number',\n",
       "       'NR Transaction Details.Name of Beneficiary Remitter',\n",
       "       'NR Transaction Details.Transaction Date',\n",
       "       'NR Transaction Details.Transaction Status',\n",
       "       'NR Transaction Details.Transaction Type', 'Reporting Entity.RE Name',\n",
       "       'Reporting Entity.RE Name 2', 'Reporting Period.Reporting Quarter',\n",
       "       'Reporting Period.Reporting Year',\n",
       "       'Purpose Code.Purpose Code Description',\n",
       "       'Reporting Period.Reporting Month'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfNRtoNR.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "amountCol = 'NR - MYR Amount'\n",
    "transTypeCol = 'NR Transaction Details.Transaction Type'\n",
    "yearCol = 'Reporting Period.Reporting Year'\n",
    "idCol1 = 'NR Customer Details.Customer Beneficiary Remitter Identification Number'\n",
    "idCol2 = 'NR Transaction Details.Beneficiary Remitter Identification Number'\n",
    "countryCol = 'Country.Country Description'\n",
    "\n",
    "dfNRtoNR_2020 = dfNRtoNR[(dfNRtoNR[yearCol] == 2020)].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(860577, 16)"
      ]
     },
     "execution_count": 201,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfNRtoNR_2020.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [],
   "source": [
    "tableList = []\n",
    "\n",
    "for i in ['Other Payments', 'Other Receipts']:\n",
    "    #match idCol1\n",
    "    colList = [idCol1, yearCol, transTypeCol, amountCol, countryCol]\n",
    "    dfTemp = dfNRtoNR_2020[(dfNRtoNR_2020[transTypeCol] == i) &\n",
    "                           (dfNRtoNR_2020[idCol1].astype(str).isin(idFinalList))][colList].copy()\n",
    "    dfTemp.rename(columns = {idCol1:\"ID_NO\", yearCol:\"YEAR\", \n",
    "                             transTypeCol:\"TXN_TYPE\", amountCol:\"TOTAL_MYR\",\n",
    "                            countryCol: \"COUNTRY\"}, inplace = True)\n",
    "    tableList.append(dfTemp)\n",
    "\n",
    "    #match idCol2\n",
    "    colList = [idCol2, yearCol, transTypeCol, amountCol, countryCol]\n",
    "    dfTemp = dfNRtoNR_2020[(dfNRtoNR_2020[transTypeCol] == i) &\n",
    "                           (dfNRtoNR_2020[idCol2].astype(str).isin(idFinalList))][colList].copy()\n",
    "    dfTemp.rename(columns = {idCol2:\"ID_NO\", yearCol:\"YEAR\", \n",
    "                         transTypeCol:\"TXN_TYPE\", amountCol:\"TOTAL_MYR\", countryCol: \"COUNTRY\"}, inplace = True)\n",
    "    tableList.append(dfTemp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfNRtoNR_2020Match = pd.concat(tableList).copy()\n",
    "dfNRtoNR_2020Match = dfNRtoNR_2020Match[pd.notnull(dfNRtoNR_2020Match['ID_NO'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfNRtoNR_2020Match['TXN_TYPE'] = dfNRtoNR_2020Match['TXN_TYPE'].map({\"Other Payments\":\"SEND\", \"Other Receipts\":\"RECEIPT\"})\n",
    "\n",
    "dfNRtoNR_2020Match['SECTOR'] = dfNRtoNR_2020Match['ID_NO'].map(idLicenceToSectorDict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SEND       88540\n",
       "RECEIPT    57033\n",
       "Name: TXN_TYPE, dtype: int64"
      ]
     },
     "execution_count": 201,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfNRtoNR_2020Match['TXN_TYPE'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Labuan Companies', 'Banks ', 'LITC', 'Insurance ', 'Leasing ',\n",
       "       'Money Broker ', 'Trust Company ', 'Securities Licensee',\n",
       "       'Foundations ', 'Trusts', 'Partnerships'], dtype=object)"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfNRtoNR_2020Match['SECTOR'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAggAmount2020 = dfNRtoNR_2020Match.groupby(['YEAR', 'TXN_TYPE', 'SECTOR'])['TOTAL_MYR'].sum().reset_index()\n",
    "\n",
    "dfAggNoTrans2020 = dfNRtoNR_2020Match.groupby(['YEAR', 'TXN_TYPE', 'SECTOR'])['ID_NO'].count().reset_index()\n",
    "dfAggNoTrans2020.rename(columns = {'ID_NO':'TXN_COUNT'}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 266,
   "metadata": {
    "collapsed": true
   },
   "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>YEAR</th>\n",
       "      <th>TXN_TYPE</th>\n",
       "      <th>Sector</th>\n",
       "      <th>TXN_COUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>Banks</td>\n",
       "      <td>1580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>Foundations</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>Insurance</td>\n",
       "      <td>8155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>LITC</td>\n",
       "      <td>5384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>Labuan Companies</td>\n",
       "      <td>35680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>Leasing</td>\n",
       "      <td>419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>Money Broker</td>\n",
       "      <td>477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>Partnerships</td>\n",
       "      <td>331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>Securities Licensee</td>\n",
       "      <td>164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2020</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>Trust Company</td>\n",
       "      <td>4812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2020</td>\n",
       "      <td>RECEIVE</td>\n",
       "      <td>Trusts</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2020</td>\n",
       "      <td>SEND</td>\n",
       "      <td>Banks</td>\n",
       "      <td>2170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2020</td>\n",
       "      <td>SEND</td>\n",
       "      <td>Foundations</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2020</td>\n",
       "      <td>SEND</td>\n",
       "      <td>Insurance</td>\n",
       "      <td>7106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2020</td>\n",
       "      <td>SEND</td>\n",
       "      <td>LITC</td>\n",
       "      <td>5528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2020</td>\n",
       "      <td>SEND</td>\n",
       "      <td>Labuan Companies</td>\n",
       "      <td>70066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2020</td>\n",
       "      <td>SEND</td>\n",
       "      <td>Leasing</td>\n",
       "      <td>1912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2020</td>\n",
       "      <td>SEND</td>\n",
       "      <td>Money Broker</td>\n",
       "      <td>866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2020</td>\n",
       "      <td>SEND</td>\n",
       "      <td>Partnerships</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2020</td>\n",
       "      <td>SEND</td>\n",
       "      <td>Securities Licensee</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2020</td>\n",
       "      <td>SEND</td>\n",
       "      <td>Trust Company</td>\n",
       "      <td>549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2020</td>\n",
       "      <td>SEND</td>\n",
       "      <td>Trusts</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    YEAR TXN_TYPE               Sector  TXN_COUNT\n",
       "0   2020  RECEIVE               Banks        1580\n",
       "1   2020  RECEIVE         Foundations          28\n",
       "2   2020  RECEIVE           Insurance        8155\n",
       "3   2020  RECEIVE                 LITC       5384\n",
       "4   2020  RECEIVE     Labuan Companies      35680\n",
       "5   2020  RECEIVE             Leasing         419\n",
       "6   2020  RECEIVE        Money Broker         477\n",
       "7   2020  RECEIVE         Partnerships        331\n",
       "8   2020  RECEIVE  Securities Licensee        164\n",
       "9   2020  RECEIVE       Trust Company        4812\n",
       "10  2020  RECEIVE               Trusts          3\n",
       "11  2020     SEND               Banks        2170\n",
       "12  2020     SEND         Foundations          25\n",
       "13  2020     SEND           Insurance        7106\n",
       "14  2020     SEND                 LITC       5528\n",
       "15  2020     SEND     Labuan Companies      70066\n",
       "16  2020     SEND             Leasing        1912\n",
       "17  2020     SEND        Money Broker         866\n",
       "18  2020     SEND         Partnerships         59\n",
       "19  2020     SEND  Securities Licensee        233\n",
       "20  2020     SEND       Trust Company         549\n",
       "21  2020     SEND               Trusts         26"
      ]
     },
     "execution_count": 266,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfAggNoTrans2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAggNoTransFinal = pd.concat([dfAggNoTrans, dfAggNoTrans2020], axis = 0).copy()\n",
    "dfAggAmountFinal = pd.concat([dfAggAmount, dfAggAmount2020], axis = 0).copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {},
   "outputs": [],
   "source": [
    "excelFile = \"data/interim/2021-02-04_ItisAggFinal.xlsx\"\n",
    "with pd.ExcelWriter(excelFile) as writer:\n",
    "    dfAggNoTransFinal.to_excel(writer, index = False, sheet_name = 'NoTrans')\n",
    "    dfAggAmountFinal.to_excel(writer, index = False, sheet_name = 'TotalAmount')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### by Country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAggAmount2020 = dfNRtoNR_2020Match.groupby(['YEAR', 'COUNTRY', 'TXN_TYPE', 'SECTOR'])['TOTAL_MYR'].sum().reset_index()\n",
    "dfAggNoTrans2020 = dfNRtoNR_2020Match.groupby(['YEAR', 'COUNTRY', 'TXN_TYPE', 'SECTOR'])['ID_NO'].count().reset_index()\n",
    "dfAggNoTrans2020.rename(columns = {'ID_NO':'TXN_COUNT'}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Argentina', 'Armenia', 'Australia', 'Austria', 'Azerbaijan',\n",
       "       'Bahamas', 'Bahrain', 'Bangladesh', 'Belgium', 'Belize', 'Benin',\n",
       "       'Bermuda', 'Bhutan', 'Bosnia and Herzegovina', 'Botswana',\n",
       "       'Brazil', 'Brunei Darussalam', 'Bulgaria', 'Cambodia', 'Canada',\n",
       "       'Cape Verde', 'Cayman Islands', 'Chile', 'China', \"Cote D'Ivoire\",\n",
       "       'Croatia', 'Curaçao', 'Cyprus', 'Czech Republic', 'Denmark',\n",
       "       'Dominican Republic', 'Ecuador', 'Egypt', 'Estonia', 'Ethiopia',\n",
       "       'Fiji', 'Finland', 'France', 'Gabon', 'Georgia', 'Germany',\n",
       "       'Ghana', 'Gibraltar', 'Greece', 'Grenada', 'Guernsey', 'Guinea',\n",
       "       'Hong Kong', 'Hungary', 'Iceland', 'India', 'Indonesia', 'Ireland',\n",
       "       'Isle of Man', 'Italy', 'Japan', 'Jersey', 'Kazakhstan', 'Kenya',\n",
       "       'Korea, Republic of', 'Kuwait', 'LABUAN OFFSHORE',\n",
       "       \"Lao People's Democratic Republic\", 'Latvia', 'Lebanon',\n",
       "       'Liechtenstein', 'Lithuania', 'Luxembourg', 'Macao', 'Malawi',\n",
       "       'Maldives', 'Malta', 'Mauritius', 'Mexico', 'Monaco', 'Mongolia',\n",
       "       'Montenegro', 'Morocco', 'Myanmar', 'Nepal', 'Netherlands',\n",
       "       'New Caledonia', 'New Zealand', 'Nigeria', 'Norway', 'Oman',\n",
       "       'Others', 'Pakistan', 'Panama', 'Papua New Guinea', 'Peru',\n",
       "       'Philippines', 'Poland', 'Portugal', 'Qatar', 'Romania',\n",
       "       'Russian Federation', 'Saudi Arabia', 'Serbia', 'SERBIA',\n",
       "       'Seychelles', 'Sierra Leone', 'Singapore', 'Slovakia', 'Slovenia',\n",
       "       'Solomon Islands', 'South Africa', 'Spain', 'Sri Lanka',\n",
       "       'Suriname', 'Sweden', 'Switzerland', 'Taiwan, Province of China',\n",
       "       'Tanzania, United Republic of', 'Thailand', 'Turkey', 'Uganda',\n",
       "       'Ukraine', 'United Arab Emirates', 'United Kingdom',\n",
       "       'United States', 'Uruguay', 'Vanuatu', 'Viet Nam',\n",
       "       'Virgin Islands, British', 'Virgin Islands, U.S', 'Zambia',\n",
       "       'Angola', 'Barbados', 'Belarus', 'Bolivia', 'Burkina Faso',\n",
       "       'Dominica', 'Iraq', 'Jordan', 'Kyrgyzstan', 'Paraguay',\n",
       "       'Puerto Rico', 'Senegal', 'Togo', 'Tunisia', 'Algeria', 'Cameroon',\n",
       "       'Colombia', 'Moldova, Republic of', 'Namibia', 'Rwanda',\n",
       "       'Uzbekistan', 'Anguilla', 'Costa Rica', 'Guatemala',\n",
       "       'Macedonia, The Former Yugoslav Republic of', 'Mali',\n",
       "       'Marshall Islands', 'Mozambique', 'Swaziland', 'Timor-Leste',\n",
       "       'Turkmenistan', 'Zimbabwe', 'Andorra', 'Cook Islands',\n",
       "       'Congo, The Democratic Republic of the', 'Martinique',\n",
       "       'Saint Lucia', 'Trinidad and Tobago', 'San Marino',\n",
       "       'Saint Kitts and Nevis', 'Saint Vincent and the Grenadines',\n",
       "       'Samoa', 'Tonga', 'Albania', 'Venezuela', 'El Salvador', 'Yemen',\n",
       "       'YUGOSLAVIA', 'American Samoa', 'Afghanistan', 'Djibouti',\n",
       "       'EUROPEAN COMMUNITY', 'Haiti', 'Kosovo', 'Nicaragua',\n",
       "       'SOUTH SUDAN', 'Niger', 'Guyana', 'Mauritania',\n",
       "       'Palestinian Territory, Occupied', 'Norfolk Island',\n",
       "       'Central African Republic'], dtype=object)"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfNRtoNR_2020Match['COUNTRY'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2016., 2017., 2018., 2019., 2020.])"
      ]
     },
     "execution_count": 207,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfAggNoTransFinal['YEAR'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2016., 2017., 2018., 2019., 2020.])"
      ]
     },
     "execution_count": 208,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfAggAmountFinal['YEAR'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "metadata": {},
   "outputs": [],
   "source": [
    "#take sender country if receipt and bene country if send\n",
    "dfAggNoTransFinal = pd.concat([dfAggNoTrans, dfAggNoTrans2020], axis = 0).copy()\n",
    "dfAggAmountFinal = pd.concat([dfAggAmount, dfAggAmount2020], axis = 0).copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "metadata": {},
   "outputs": [],
   "source": [
    "excelFile = \"data/interim/2021-02-04_ItisAggFinal_v2.xlsx\"\n",
    "with pd.ExcelWriter(excelFile) as writer:\n",
    "    dfAggNoTransFinal.to_excel(writer, index = False, sheet_name = 'NoTrans')\n",
    "    dfAggAmountFinal.to_excel(writer, index = False, sheet_name = 'TotalAmount')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "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>YEAR</th>\n",
       "      <th>COUNTRY</th>\n",
       "      <th>TXN_TYPE</th>\n",
       "      <th>SECTOR</th>\n",
       "      <th>TXN_COUNT</th>\n",
       "      <th>TOTAL_MYR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>China</td>\n",
       "      <td>SEND</td>\n",
       "      <td>LITC</td>\n",
       "      <td>1</td>\n",
       "      <td>13136585.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>LITC</td>\n",
       "      <td>10</td>\n",
       "      <td>2753223.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>SEND</td>\n",
       "      <td>LITC</td>\n",
       "      <td>1</td>\n",
       "      <td>28371.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>Jordan</td>\n",
       "      <td>SEND</td>\n",
       "      <td>LITC</td>\n",
       "      <td>2</td>\n",
       "      <td>23387258.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>LABUAN OFFSHORE</td>\n",
       "      <td>SEND</td>\n",
       "      <td>Labuan Companies</td>\n",
       "      <td>6</td>\n",
       "      <td>4307169.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3374</th>\n",
       "      <td>2020.0</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>SEND</td>\n",
       "      <td>Labuan Companies</td>\n",
       "      <td>4</td>\n",
       "      <td>4491.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3375</th>\n",
       "      <td>2020.0</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>LITC</td>\n",
       "      <td>1</td>\n",
       "      <td>382505.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3376</th>\n",
       "      <td>2020.0</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>RECEIPT</td>\n",
       "      <td>Labuan Companies</td>\n",
       "      <td>1</td>\n",
       "      <td>62941.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3377</th>\n",
       "      <td>2020.0</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>SEND</td>\n",
       "      <td>Labuan Companies</td>\n",
       "      <td>88</td>\n",
       "      <td>3095125.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3378</th>\n",
       "      <td>2020.0</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>SEND</td>\n",
       "      <td>Labuan Companies</td>\n",
       "      <td>6</td>\n",
       "      <td>44848.24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3379 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        YEAR          COUNTRY TXN_TYPE            SECTOR  TXN_COUNT  \\\n",
       "0     2016.0            China     SEND              LITC          1   \n",
       "1     2016.0        Hong Kong  RECEIPT              LITC         10   \n",
       "2     2016.0        Hong Kong     SEND              LITC          1   \n",
       "3     2016.0           Jordan     SEND              LITC          2   \n",
       "4     2016.0  LABUAN OFFSHORE     SEND  Labuan Companies          6   \n",
       "...      ...              ...      ...               ...        ...   \n",
       "3374  2020.0            Yemen     SEND  Labuan Companies          4   \n",
       "3375  2020.0           Zambia  RECEIPT              LITC          1   \n",
       "3376  2020.0           Zambia  RECEIPT  Labuan Companies          1   \n",
       "3377  2020.0           Zambia     SEND  Labuan Companies         88   \n",
       "3378  2020.0         Zimbabwe     SEND  Labuan Companies          6   \n",
       "\n",
       "        TOTAL_MYR  \n",
       "0     13136585.00  \n",
       "1      2753223.84  \n",
       "2        28371.39  \n",
       "3     23387258.70  \n",
       "4      4307169.26  \n",
       "...           ...  \n",
       "3374      4491.42  \n",
       "3375    382505.00  \n",
       "3376     62941.55  \n",
       "3377   3095125.79  \n",
       "3378     44848.24  \n",
       "\n",
       "[3379 rows x 6 columns]"
      ]
     },
     "execution_count": 219,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfMerged = dfAggNoTransFinal.merge(dfAggAmountFinal, how = 'outer', \n",
    "                                    on = ['TXN_TYPE', 'YEAR', 'COUNTRY', 'SECTOR'])\n",
    "dfMerged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfMergedSend = dfMerged.query(\"TXN_TYPE == 'SEND'\")\n",
    "dfMergedReceipt = dfMerged.query(\"TXN_TYPE == 'RECEIPT'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"5\" halign=\"left\">TOTAL_MYR</th>\n",
       "      <th colspan=\"5\" halign=\"left\">TXN_COUNT</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>YEAR</th>\n",
       "      <th>2016.0</th>\n",
       "      <th>2017.0</th>\n",
       "      <th>2018.0</th>\n",
       "      <th>2019.0</th>\n",
       "      <th>2020.0</th>\n",
       "      <th>2016.0</th>\n",
       "      <th>2017.0</th>\n",
       "      <th>2018.0</th>\n",
       "      <th>2019.0</th>\n",
       "      <th>2020.0</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TXN_TYPE</th>\n",
       "      <th>COUNTRY</th>\n",
       "      <th>SECTOR</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"11\" valign=\"top\">SEND</th>\n",
       "      <th>Afghanistan</th>\n",
       "      <th>Labuan Companies</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41066.84</td>\n",
       "      <td>17205.33</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Albania</th>\n",
       "      <th>Labuan Companies</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1158918.23</td>\n",
       "      <td>126960.00</td>\n",
       "      <td>8613.31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Algeria</th>\n",
       "      <th>Insurance</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>796017.46</td>\n",
       "      <td>240198.28</td>\n",
       "      <td>25794.75</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Labuan Companies</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>11923.80</td>\n",
       "      <td>141471.58</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Money Broker</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>389.40</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Yemen</th>\n",
       "      <th>Labuan Companies</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>72127.75</td>\n",
       "      <td>4491.42</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Money Broker</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>26964.08</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Zambia</th>\n",
       "      <th>Insurance</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>35162.98</td>\n",
       "      <td>418284.25</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Labuan Companies</th>\n",
       "      <td>0.0</td>\n",
       "      <td>213975.0</td>\n",
       "      <td>902339.77</td>\n",
       "      <td>1320119.53</td>\n",
       "      <td>3095125.79</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>16</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zimbabwe</th>\n",
       "      <th>Labuan Companies</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>44848.24</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>654 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      TOTAL_MYR                        \\\n",
       "YEAR                                     2016.0    2017.0      2018.0   \n",
       "TXN_TYPE COUNTRY     SECTOR                                             \n",
       "SEND     Afghanistan Labuan Companies       0.0       0.0    41066.84   \n",
       "         Albania     Labuan Companies       0.0       0.0  1158918.23   \n",
       "         Algeria     Insurance              0.0       0.0   796017.46   \n",
       "                     Labuan Companies       0.0       0.0        0.00   \n",
       "                     Money Broker           0.0       0.0        0.00   \n",
       "...                                         ...       ...         ...   \n",
       "         Yemen       Labuan Companies       0.0       0.0        0.00   \n",
       "                     Money Broker           0.0       0.0        0.00   \n",
       "         Zambia      Insurance              0.0       0.0    35162.98   \n",
       "                     Labuan Companies       0.0  213975.0   902339.77   \n",
       "         Zimbabwe    Labuan Companies       0.0       0.0        0.00   \n",
       "\n",
       "                                                              TXN_COUNT  \\\n",
       "YEAR                                       2019.0      2020.0    2016.0   \n",
       "TXN_TYPE COUNTRY     SECTOR                                               \n",
       "SEND     Afghanistan Labuan Companies    17205.33        0.00         0   \n",
       "         Albania     Labuan Companies   126960.00     8613.31         0   \n",
       "         Algeria     Insurance          240198.28    25794.75         0   \n",
       "                     Labuan Companies    11923.80   141471.58         0   \n",
       "                     Money Broker            0.00      389.40         0   \n",
       "...                                           ...         ...       ...   \n",
       "         Yemen       Labuan Companies    72127.75     4491.42         0   \n",
       "                     Money Broker        26964.08        0.00         0   \n",
       "         Zambia      Insurance          418284.25        0.00         0   \n",
       "                     Labuan Companies  1320119.53  3095125.79         0   \n",
       "         Zimbabwe    Labuan Companies        0.00    44848.24         0   \n",
       "\n",
       "                                                                   \n",
       "YEAR                                  2017.0 2018.0 2019.0 2020.0  \n",
       "TXN_TYPE COUNTRY     SECTOR                                        \n",
       "SEND     Afghanistan Labuan Companies      0      2      3      0  \n",
       "         Albania     Labuan Companies      0      3      3      3  \n",
       "         Algeria     Insurance             0      1      1      1  \n",
       "                     Labuan Companies      0      0      2      3  \n",
       "                     Money Broker          0      0      0      1  \n",
       "...                                      ...    ...    ...    ...  \n",
       "         Yemen       Labuan Companies      0      0      3      4  \n",
       "                     Money Broker          0      0      3      0  \n",
       "         Zambia      Insurance             0      2      7      0  \n",
       "                     Labuan Companies      1     15     16     88  \n",
       "         Zimbabwe    Labuan Companies      0      0      0      6  \n",
       "\n",
       "[654 rows x 10 columns]"
      ]
     },
     "execution_count": 222,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfCollapseSend = pd.pivot_table(dfMergedSend, values = ['TXN_COUNT', 'TOTAL_MYR'],\n",
    "                              index = ['TXN_TYPE', 'COUNTRY', 'SECTOR'],\n",
    "                          columns = ['YEAR'], aggfunc=np.sum, fill_value=0)\n",
    "dfCollapseSend"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2016., 2017., 2018., 2019.])"
      ]
     },
     "execution_count": 217,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfMergedReceipt['YEAR'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 223,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"5\" halign=\"left\">TOTAL_MYR</th>\n",
       "      <th colspan=\"5\" halign=\"left\">TXN_COUNT</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>YEAR</th>\n",
       "      <th>2016.0</th>\n",
       "      <th>2017.0</th>\n",
       "      <th>2018.0</th>\n",
       "      <th>2019.0</th>\n",
       "      <th>2020.0</th>\n",
       "      <th>2016.0</th>\n",
       "      <th>2017.0</th>\n",
       "      <th>2018.0</th>\n",
       "      <th>2019.0</th>\n",
       "      <th>2020.0</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TXN_TYPE</th>\n",
       "      <th>COUNTRY</th>\n",
       "      <th>SECTOR</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"11\" valign=\"top\">RECEIPT</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Afghanistan</th>\n",
       "      <th>Banks</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.390342e+08</td>\n",
       "      <td>36598741.77</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Insurance</th>\n",
       "      <td>0.0</td>\n",
       "      <td>16746.74</td>\n",
       "      <td>8.964820e+03</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LITC</th>\n",
       "      <td>0.0</td>\n",
       "      <td>342741.30</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Labuan Companies</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.670078e+07</td>\n",
       "      <td>172150.56</td>\n",
       "      <td>5959070.25</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Trust Company</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.763483e+04</td>\n",
       "      <td>0.00</td>\n",
       "      <td>4051.20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Virgin Islands, British</th>\n",
       "      <th>Trust Company</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.000000e+04</td>\n",
       "      <td>0.00</td>\n",
       "      <td>55556.28</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Western Sahara</th>\n",
       "      <th>Labuan Companies</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>21927.90</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yemen</th>\n",
       "      <th>Labuan Companies</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1069875.64</td>\n",
       "      <td>2.604744e+05</td>\n",
       "      <td>5786608.57</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Zambia</th>\n",
       "      <th>LITC</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>382505.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Labuan Companies</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.449408e+04</td>\n",
       "      <td>1541.92</td>\n",
       "      <td>62941.55</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>633 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  TOTAL_MYR              \\\n",
       "YEAR                                                 2016.0      2017.0   \n",
       "TXN_TYPE COUNTRY                 SECTOR                                   \n",
       "RECEIPT  Afghanistan             Banks                  0.0        0.00   \n",
       "                                 Insurance              0.0    16746.74   \n",
       "                                 LITC                   0.0   342741.30   \n",
       "                                 Labuan Companies       0.0        0.00   \n",
       "                                 Trust Company          0.0        0.00   \n",
       "...                                                     ...         ...   \n",
       "         Virgin Islands, British Trust Company          0.0        0.00   \n",
       "         Western Sahara          Labuan Companies       0.0        0.00   \n",
       "         Yemen                   Labuan Companies       0.0  1069875.64   \n",
       "         Zambia                  LITC                   0.0        0.00   \n",
       "                                 Labuan Companies       0.0        0.00   \n",
       "\n",
       "                                                                              \\\n",
       "YEAR                                                     2018.0       2019.0   \n",
       "TXN_TYPE COUNTRY                 SECTOR                                        \n",
       "RECEIPT  Afghanistan             Banks             1.390342e+08  36598741.77   \n",
       "                                 Insurance         8.964820e+03         0.00   \n",
       "                                 LITC              0.000000e+00         0.00   \n",
       "                                 Labuan Companies  1.670078e+07    172150.56   \n",
       "                                 Trust Company     1.763483e+04         0.00   \n",
       "...                                                         ...          ...   \n",
       "         Virgin Islands, British Trust Company     2.000000e+04         0.00   \n",
       "         Western Sahara          Labuan Companies  0.000000e+00     21927.90   \n",
       "         Yemen                   Labuan Companies  2.604744e+05   5786608.57   \n",
       "         Zambia                  LITC              0.000000e+00         0.00   \n",
       "                                 Labuan Companies  2.449408e+04      1541.92   \n",
       "\n",
       "                                                              TXN_COUNT  \\\n",
       "YEAR                                                   2020.0    2016.0   \n",
       "TXN_TYPE COUNTRY                 SECTOR                                   \n",
       "RECEIPT  Afghanistan             Banks                   0.00         0   \n",
       "                                 Insurance               0.00         0   \n",
       "                                 LITC                    0.00         0   \n",
       "                                 Labuan Companies  5959070.25         0   \n",
       "                                 Trust Company        4051.20         0   \n",
       "...                                                       ...       ...   \n",
       "         Virgin Islands, British Trust Company       55556.28         0   \n",
       "         Western Sahara          Labuan Companies        0.00         0   \n",
       "         Yemen                   Labuan Companies        0.00         0   \n",
       "         Zambia                  LITC               382505.00         0   \n",
       "                                 Labuan Companies    62941.55         0   \n",
       "\n",
       "                                                                               \n",
       "YEAR                                              2017.0 2018.0 2019.0 2020.0  \n",
       "TXN_TYPE COUNTRY                 SECTOR                                        \n",
       "RECEIPT  Afghanistan             Banks                 0      2      1      0  \n",
       "                                 Insurance             1      2      0      0  \n",
       "                                 LITC                  2      0      0      0  \n",
       "                                 Labuan Companies      0      6      2      8  \n",
       "                                 Trust Company         0      1      0      2  \n",
       "...                                                  ...    ...    ...    ...  \n",
       "         Virgin Islands, British Trust Company         0      1      0      1  \n",
       "         Western Sahara          Labuan Companies      0      0      1      0  \n",
       "         Yemen                   Labuan Companies      2      1      3      0  \n",
       "         Zambia                  LITC                  0      0      0      1  \n",
       "                                 Labuan Companies      0      1      1      1  \n",
       "\n",
       "[633 rows x 10 columns]"
      ]
     },
     "execution_count": 223,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfCollapseReceipt = pd.pivot_table(dfMergedReceipt, values = ['TXN_COUNT', 'TOTAL_MYR'],\n",
    "                              index = ['TXN_TYPE', 'COUNTRY', 'SECTOR'],\n",
    "                          columns = ['YEAR'], aggfunc=np.sum, fill_value=0)\n",
    "dfCollapseReceipt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [],
   "source": [
    "excelFile = \"data/interim/2021-02-04_ItisAggFinal_collapse.xlsx\"\n",
    "dfCollapse.to_excel(excelFile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "metadata": {},
   "outputs": [],
   "source": [
    "excelFile = \"data/interim/2021-02-04_ItisAggFinal_collapse2.xlsx\"\n",
    "with pd.ExcelWriter(excelFile) as writer:\n",
    "    dfCollapseSend.to_excel(writer, sheet_name = 'Send')\n",
    "    dfCollapseReceipt.to_excel(writer, sheet_name = 'Receipt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}