aws_marketplace/using_model_packages/financial_transaction_processing/Extracting_insights_from_your_credit_card_statement.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Extracting insights from your credit card statements"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Manually identifying additional tax deductibles from your family’s credit card statements is quite tedious as the data in the statement is often unstructured. In this notebook, you will learn how to classify, categorize, and convert your transaction data from unstructured to structured format. You will learn how to use a pre-trained machine learning model to identify candidate merchant names and locations and make the process of matching candidates against a dictionary, efficient. Once transaction data is converted into a structured format, you will be able to use it for multiple purposes such as:\n",
    "1. To identify precise amounts you paid for items that are tax deductible.\n",
    "2. If you have enough data, to train a model that learns your expenditure patterns and identifies fraudulent activity.\n",
    "3. To understand your expenditure patterns.\n",
    "\n",
    "#### Overview:\n",
    "In [Step 1](#Step-1:-Perform-preliminary-analysis-on-the-dataset) of this notebook, you will load a sample transaction file into a data-frame and perform preliminary analysis such as identifying the transaction date and extracting subscription fees from your statement. Other interesting features in the transaction log are city name and merchant name, which are often multi-term entities. Identifying merchant name as well as city requires huge lookups in the dictionary as considerable number of permutations and combinations need to be searched if you take brute force approach.\n",
    "\n",
    "Let us consider following transaction log entry.\n",
    "\n",
    "```\n",
    "3/25/15,6.43,PURCHASE AUTHORIZED ON 03/23 XXXX CONCH TOUR TRAIN XXX XXXXX 1034 G KEY WEST FL XXXXXXXX\n",
    "```\n",
    "\n",
    "The transaction entry contains a multi-termed merchant name entity (conch tour train) and a multi-termed city (KEY WEST) that you want to extract. To do so confidently, you have to identify all unigrams, bi-grams, tri-grams from the transaction log and match them against a dictionary. If you ignore the anonymized data and all other words occurring before the date 03/23, you get 8 unique words [CONCH TOUR TRAIN 1034 G KEY WEST FL] leading to following 21 unique ngrams(For this use-case, we will stick to unigram/bigrams/trigrams). \n",
    "\n",
    "```\n",
    "{'TRAIN 1034', 'CONCH', 'TOUR', '1034 G', 'G KEY WEST', 'CONCH TOUR TRAIN', 'G KEY', 'KEY WEST', 'CONCH TOUR', 'WEST', 'FL', 'KEY WEST FL', 'KEY', '1034 G KEY', 'TOUR TRAIN 1034', 'TRAIN', 'TOUR TRAIN', '1034', 'TRAIN 1034 G', 'G', 'WEST FL'}\n",
    "```\n",
    "\n",
    "To identify the merchant name from this transaction log entry, you would need to do a maximum of **21 X (size of merchant info dictionary)**  lookups. Similarly, to identify the city name from this transaction log entry, you would need to do a maximum of **21 X (size of city name dictionary)**  lookups. Given that each transaction log entry will have a variable number of words, the merchant/location computation becomes a computationally expensive task.\n",
    "\n",
    "You can reduce the time required for doing such lookups with an ML model that identifies potential candidate merchant/city names. E.g. the [Transaction Data Parsing (NER)](https://aws.amazon.com/marketplace/pp/prodview-sqnwjvzzqntn2) ML model returned the following output for \"PURCHASE AUTHORIZED ON 03/23  CONCH TOUR TRAIN 1034 G KEY WEST FL\" as input. \n",
    "\n",
    "```\n",
    "[{'key': 'CONCH', 'type': 'NE_MERCHANT', 'start_pos': 30, 'end_pos': 35},\n",
    " {'key': 'TOUR', 'type': 'NE_MERCHANT', 'start_pos': 36, 'end_pos': 40},\n",
    " {'key': 'TRAIN', 'type': 'NE_MERCHANT', 'start_pos': 41, 'end_pos': 46},\n",
    " {'key': 'KEY', 'type': 'NE_STORE_LOCATION', 'start_pos': 55, 'end_pos': 58},\n",
    " {'key': 'WEST', 'type': 'NE_STORE_LOCATION', 'start_pos': 59, 'end_pos': 63},\n",
    " {'key': 'FL', 'type': 'NE_STORE_LOCATION', 'start_pos': 64, 'end_pos': 66}]\n",
    " ```\n",
    "Given that we now have 3 candidate words indicating merchant name, the name-space (a total of 6 unique unigrams/bigrams/trigrams) for doing dictionary lookups is much smaller.\n",
    "\n",
    "In [Step 2](#Step-2:-Use-an-ML-model-to-identify-potential-merchants-and-locations-for-each-transaction), you will perform a prediction on an ML Model to identify candidate merchant and location names from each transaction log entry.  In [Step 3](#Step-3:-Identify-merchant-name-from-transaction-log), you will identify a precise merchant name by doing a lookup on candidate merchant names and in [Step 4](#Step-4:-Identify-state-and-city-for-each-transaction-log-entry), you will identify city and state information by doing lookups on candidate city names. Finally your will do cleanup in [Step 5](#Step-5:-Next-steps-and-cleanup).\n",
    "\n",
    "#### Contents:\n",
    "* [Pre-requisites](#Pre-requisites)\n",
    "* [Step 1: Perform preliminary analysis and data extraction on the dataset](#Step-1:-Perform-preliminary-analysis-on-the-dataset)\n",
    "    * [Step 1.1: Load and View the dataset](#Step-1.1-Load-and-View-the-dataset)\n",
    "    * [Step 1.2 Identify Transaction date](#Step-1.2-Identify-Transaction-date)\n",
    "    * [Step 1.3 Identify subscriptions](#Step-1.3-Identify-subscriptions)\n",
    "* [Step 2: Use an ML model to identify potential merchants and locations for each transaction](#Step-2:-Use-an-ML-model-to-identify-potential-merchants-and-locations-for-each-transaction)\n",
    "    * [Step 2.1: Deploy the model](#Step-2.1:-Deploy-the-model)\n",
    "    * [Step 2.2: Populate potential merchants and locations in dataframe](#Step-2.2:-Populate-candidate-merchants-and-locations-in-dataframe)\n",
    "* [Step 3: Identify merchant name from transaction log](#Step-3:-Identify-merchant-name-from-transaction-log)\n",
    "    * [Step 3.1: Identify merchant name](#Step-3.1:-Identify-merchant-name)\n",
    "    * [Step 3.2: Visualize expenses](#Step-3.2:-Visualize-expenses)\n",
    "* [Step 4: Identify state and city for each transaction log entry](#Step-4:-Identify-state-and-city-for-each-transaction-log-entry)\n",
    "    * [Step 4.1: Populate state in which transaction took place](#Step-4.1:-Populate-state-in-which-transaction-took-place)\n",
    "    * [Step 4.2: Populate city and country](#Step-4.2:-Populate-city-and-country)\n",
    "* [Step 5: Next steps and cleanup](#Step-5:-Next-steps-and-cleanup)\n",
    "\n",
    "\n",
    "#### Usage instructions\n",
    "You can run this notebook one cell at a time (By using Shift+Enter for running a cell)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pre-requisites**\n",
    "\n",
    "This sample notebook requires subscription to **[Transaction Data Parsing (NER)](https://aws.amazon.com/marketplace/pp/prodview-sqnwjvzzqntn2)**, a pre-trained machine learning model package from AWS Marketplace. \n",
    "If your AWS account has not been subscribed to this listing, here is the process you can follow: \n",
    "1. Open the [listing](https://aws.amazon.com/marketplace/pp/prodview-sqnwjvzzqntn2) from AWS Marketplace\n",
    "1. Read the **Highlights** section and then **product overview** section of the listing.\n",
    "1. View **usage information** and then **additional resources.**\n",
    "1. Note the supported instance types.\n",
    "1. Next, click on **Continue to subscribe.**\n",
    "1. Review **End user license agreement, support terms**, as well as **pricing information.**\n",
    "1. **\"Accept Offer\"** button needs to be clicked if your organization agrees with EULA, pricing information as well as support terms.  If **Continue to configuration** button is active, it means your account already has a subscription to this listing. Once you click on **Continue to configuration** button and then choose region, you will see that a Product Arn will appear. This is the model package ARN that you need to specify while creating a deployable model. However, for this notebook, the Model Package ARN has been specified in **src/model_package_arns.py** file and you do not need to specify the same explicitly.\n",
    "\n",
    "2. This notebook requires the IAM role associated with this notebook to have *__comprehend:DetectEntities__* IAM permission."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1: Perform preliminary analysis on the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#In this section, we will import necessary libraries and define variables such as S3 bucket, etc.\n",
    "import json\n",
    "import re\n",
    "import datetime\n",
    "import calendar\n",
    "\n",
    "import boto3\n",
    "import sagemaker as sage\n",
    "from sagemaker import get_execution_role\n",
    "from sagemaker import ModelPackage\n",
    "from src.model_package_arns import ModelPackageArnProvider\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "import nltk\n",
    "from nltk.corpus import wordnet\n",
    "nltk.download('wordnet')\n",
    "\n",
    "\n",
    "role = get_execution_role()\n",
    "sagemaker_session = sage.Session()\n",
    "comprehend = boto3.client('comprehend')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Lets define a utility function thats accepts text and returns trigrams,bigrams,and unigrams.\n",
    "def get_grams(text):\n",
    "    \n",
    "    potential_product_names=[]\n",
    "    \n",
    "    #Identify trigrams\n",
    "    trigrams = [text for text in zip(text.split(\" \")[:-1], text.split(\" \")[1:],text.split(\" \")[2:])]\n",
    "    for trigram in trigrams:\n",
    "        potential_product_names.append(' '.join(trigram))\n",
    "    \n",
    "    #Identify bigrams    \n",
    "    bigrams = [text for text in zip(text.split(\" \")[:-1], text.split(\" \")[1:])]\n",
    "    for bigram in bigrams:\n",
    "        potential_product_names.append(' '.join(bigram))\n",
    "    #Identify unigrams\n",
    "    potential_product_names=potential_product_names+ text.split(\" \")\n",
    "    \n",
    "    return set(potential_product_names)\n",
    "\n",
    "text='CONCH TOUR TRAIN 1034 G KEY WEST FL'\n",
    "\n",
    "print('Number of unigrams/bigrams/tri-grams:',len(get_grams(text)))\n",
    "print('unigrams/bigrams/tri-grams found: ',get_grams(text))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 1.1 Load and View the dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "During inspection of the dataset, you will see that the transaction description consists of following parts:\n",
    "* Transaction type\n",
    "* Date of transaction\n",
    "* Merchant name\n",
    "* Transaction/Vendor Location"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/raw/sample-transaction-data.csv', index_col=None)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that **description** column contains anonymized data (sequences of character 'X'), let us remove the anonymous text and special characters from the description."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#The following method accepts a text and performs following tasks:\n",
    "#1. Removes anonymized words(In this dataset, anonymized values are sequences of letter X).\n",
    "#2. Removes all special characters.\n",
    "def clean_text(text):\n",
    "    text=text.strip()\n",
    "    \n",
    "    #Remove special characters\n",
    "    text = re.sub('[^A-Za-z0-9. /]+','', text)\n",
    "    \n",
    "    #Remove anonymized values\n",
    "    text = re.sub('(^X+)|( X+ )|(X+$)',' ', text)\n",
    "    return text.strip()\n",
    "\n",
    "#Let's test the function\n",
    "text=\"XXX RECURRING TRANSFER TO CHIKXXKI K XXXXXXXX SAVINGS REF XXXXXXXX XXXXXXXXX\"\n",
    "print(clean_text(text))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Let us clean values from the description column\n",
    "df['description']=df['description'].apply(lambda x:clean_text(x))\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 1.2 Identify Transaction date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The date available on the ledger is the transaction posted date. Let us rename the 'date' column to reflect the same."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={\"date\": \"transaction_posted_date\"},inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us extract the transaction date available in the description field. Based on preliminary examination, it is clear that this information is available in mm/dd format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#extract_date function extracts month and day from a text that contains date in mm/dd format\n",
    "def extract_date(text):\n",
    "    DATE_EXTRACTION_REGEX='ON ([\\d]?\\d)/([\\d]?\\d)'\n",
    "    return re.findall(DATE_EXTRACTION_REGEX,text)\n",
    "\n",
    "#extract_date('PURCHASE AUTHORIZED ON 03/23 XXXX CONCH TOUR TRAIN XXX XXXXX 1034 G KEY WEST FL XXXXXXXX')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#x1 = datetime.datetime(2020, 12, 31)\n",
    "#x2 = datetime.datetime(2021, 10, 1)\n",
    "#abs((x1-x2).days)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#This function extracts the date on which transaction occured. \n",
    "def set_transaction_date(row):\n",
    "    \n",
    "    posted_date=datetime.datetime.strptime(row['transaction_posted_date'], '%m/%d/%y')\n",
    "    \n",
    "    result=extract_date(row['description'])\n",
    "    if result:\n",
    "        month=int(result[0][0])\n",
    "        day= int(result[0][1])\n",
    "        \n",
    "        row['transaction_month']=month\n",
    "        row['transaction_date']=day\n",
    "        transaction_date=datetime.datetime(int(posted_date.year), month, day)\n",
    "        \n",
    "        #Logic to carry forward the year. \n",
    "        # Here we assume that the transaction gets posted in less than 20 days from the actual transaction date.\n",
    "        if(abs((posted_date-transaction_date).days))<20:\n",
    "            row['transaction_year']=posted_date.year\n",
    "        else:\n",
    "            row['transaction_year']=(posted_date.year -1)\n",
    "    return row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.apply(lambda row:set_transaction_date(row),axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['transaction_month'].isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 1.3 Identify subscriptions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Based on preliminary analysis of the data, we can see that susbcription log entries contain word \"RECURRING\". We will write a rule based on this to identify subscriptions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#This method accepts a row and identifies subscriptions\n",
    "def identify_subscriptions(row):\n",
    "    if 'RECURRING' in row['description']:\n",
    "        row['subscription']='True'\n",
    "        row['state_name']='N/A'\n",
    "        row['state_code']='N/A'\n",
    "        row['country_code']='N/A'\n",
    "        row['city_name']='N/A'\n",
    "    else:\n",
    "        row['subscription']='False'\n",
    "    return row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.apply(lambda row:identify_subscriptions(row),axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['subscription'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us take a look at the subscription fees paid."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['subscription']=='True'][['description','amount']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Print the total subscription fees paid.\n",
    "df[df['subscription']=='True']['amount'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2: Use an ML model to identify potential merchants and locations for each transaction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The transaction description log is machine generated and does not follow grammar. A rule-based part-of-speech tagger might not yield best results, which is why we will take a different approach here. We will feed this information to a Machine learning model specifically developed for extracting the merchant and location information from a transaction log. For more information, see the **Product overview** of the [Transaction Data Parsing (NER)](https://aws.amazon.com/marketplace/pp/prodview-sqnwjvzzqntn2?qid=1580859301012&sr=0-2&ref_=srh_res_product_title) machine learning model."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 2.1: Deploy the model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get model_package_arn\n",
    "modelpackage_arn = ModelPackageArnProvider.get_transactional_NER_model_package_arn(sagemaker_session.boto_region_name)\n",
    "\n",
    "# Define predictor wrapper class\n",
    "def ner_detection_predict_wrapper(endpoint, session):\n",
    "    return sage.RealTimePredictor(endpoint, session, content_type='application/json')\n",
    "\n",
    "# Create a deployable model for the transaction data parsing model package.\n",
    "ner_model = ModelPackage(role=role,\n",
    "                         model_package_arn=modelpackage_arn,\n",
    "                         sagemaker_session=sagemaker_session,\n",
    "                         predictor_cls=ner_detection_predict_wrapper)\n",
    "\n",
    "# Deploy the model\n",
    "ner_predictor = ner_model.deploy(initial_instance_count=1, \n",
    "                                 instance_type='ml.m5.xlarge',\n",
    "                                 endpoint_name='transaction-processing')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note**: For ease of demonstration, this notebook deploys an endpoint. However, instead of deploying an Amazon SageMaker endpoint, you can also run a batch transform job to perform inference on an ML model. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "payload = {'instance': 'PURCHASE AUTHORIZED ON 03/23  CONCH TOUR TRAIN  1034 G KEY WEST FL'}\n",
    "json_val=json.loads(ner_predictor.predict(json.dumps(payload)).decode('utf-8'))['ner']\n",
    "json_val"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 2.2: Populate candidate merchants and locations in dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#This function populates 'prediction' column with prediction performed on description of each transaction log.\n",
    "def identify_merchant_and_location(row):\n",
    "    \n",
    "    payload = {'instance': row['description']}\n",
    "    prediction=json.loads(ner_predictor.predict(json.dumps(payload)).decode('utf-8'))['ner']\n",
    "    \n",
    "    #delete start_pos and end_pos as we do not require them.\n",
    "    for value in prediction:\n",
    "        del value['start_pos']\n",
    "        del value['end_pos']\n",
    "    \n",
    "    row['prediction']=prediction\n",
    "    return row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.apply(lambda row:identify_merchant_and_location(row),axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since the prediction has been saved in the dataframe itself, you do not need the endpoint anymore. Let us delete the endpoint as well as the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ner_predictor.delete_endpoint(delete_endpoint_config=True)\n",
    "ner_predictor.delete_model()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 3: Identify merchant name from transaction log"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the purpose of this experiment, you will be doing lookups on a manually curated list of businesses. However, for real-world finance data processing, you would look into a dataset such as commercial version of [7+ Million Company Dataset](https://www.peopledatalabs.com/company-dataset) or  products from [AWS Data Exchange](https://aws.amazon.com/marketplace/search/results?page=1&filters=FulfillmentOptionType&FulfillmentOptionType=AWSDataExchange&ref_=header_nav_dm_aws_data_exchange) such as [Canada corporate registrations](https://aws.amazon.com/marketplace/pp/prodview-4u57ozcd5b56e?ref_=srh_res_product_title), [UK registered companies](https://aws.amazon.com/marketplace/pp/prodview-sydh5kttmyiag?ref_=srh_res_product_title#overview)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Let us load a sample list of businesses into a dataframe for lookup.\n",
    "merchants=pd.read_csv('data/config/businesses.csv')\n",
    "merchants.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 3.1: Identify merchant name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#This function accepts name and returns merchant type as well as sub_type\n",
    "def get_business_type(name):\n",
    "    if(len(name)>2):\n",
    "        results = merchants[merchants['name'].str.contains(name.lower())]\n",
    "        if len(results)>0:\n",
    "            merchant_type=results.iloc[0]['merchant_type']\n",
    "            merchant_sub_type=results.iloc[0]['sub_type']\n",
    "            return [merchant_type,merchant_sub_type]\n",
    "    return []\n",
    "#print(get_business_type('CONCH TOUR TRAIN'))\n",
    "\n",
    "#This function populates name, type, and sub_type for each row.\n",
    "def populate_merchant_info(row):\n",
    "    \n",
    "    #We will populate three columns in the dataframe - name, type and subtype.\n",
    "    #Based on preliminary analysis, if the purchase was made online, description contains a short domain name.\n",
    "    #Let us identify all online trasactions.\n",
    "    for name in row['description'].split(\" \"):\n",
    "        if (('.' in name) & (len(name)>2)):\n",
    "            row['vendor_website']=name.lower()\n",
    "            row['merchant_name']=name.lower()\n",
    "\n",
    "            #Since we are not interested in the state in which website was hosted, lets mark it as N/A\n",
    "            row['state_code']='N/A'\n",
    "            row['state_name']='N/A'\n",
    "            row['city_name']='N/A'\n",
    "            row['country_code']='N/A'\n",
    "            business_type=get_business_type(name)\n",
    "            if len(business_type) >0:\n",
    "                row['merchant_type']=business_type[0]\n",
    "                row['merchant_sub_type']=business_type[1]\n",
    "            return row\n",
    "    \n",
    "    #Note that the ML model returned all possible candidates for the business name. \n",
    "    #Given that business names could be multi-termed entities, we need to do a lookup for all ngrams generated from \n",
    "    #candidate merchants - for this experiment, we will stick to trigrams,bigrams, and unigrams.\n",
    "    #print(row['prediction'])\n",
    "    \n",
    "    row['vendor_website']='N/A'\n",
    "    names=[]\n",
    "    \n",
    "    for result in row['prediction']:\n",
    "        if result['type'] == 'NE_MERCHANT':\n",
    "            names.append(result['key'])\n",
    "\n",
    "    if len(names) >=1:\n",
    "        ngrams=get_grams(' '.join(names))\n",
    "        for ngram in ngrams:\n",
    "            business_type=get_business_type(ngram)\n",
    "            if len(business_type) >0:\n",
    "                row['merchant_name']=ngram.lower()\n",
    "                row['merchant_type']=business_type[0]\n",
    "                row['merchant_sub_type']=business_type[1]\n",
    "                return row\n",
    "        \n",
    "        #If direct lookup of the business name was not successful, then let us use Amazon Comprehend to\n",
    "        #identify the name of the business.\n",
    "        for ngram in ngrams:\n",
    "            result=comprehend.detect_entities(Text=' '.join(ngram),LanguageCode='en')\n",
    "            if len(result ['Entities']) >0 and result['Entities'][0]['Score']>0.7 and result['Entities'][0]['Type'] == 'ORGANIZATION':\n",
    "                row['merchant_name']=result['Entities'][0]['Text']\n",
    "                business_type=get_business_type(row['merchant_name'])\n",
    "                if len(business_type) >0:\n",
    "                    row['merchant_type']=business_type[0]\n",
    "                    row['merchant_sub_type']=business_type[1]\n",
    "                    return row\n",
    "    return row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "df=df.apply(lambda row:populate_merchant_info(row),axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['vendor_website'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print((df.isna().sum()/df.shape[0])*100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that ~49% expenses in the data are either recurring charges or are happening online."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['merchant_type']=df['merchant_type'].fillna('Unknown')\n",
    "df['merchant_sub_type']=df['merchant_sub_type'].fillna('Unknown')\n",
    "df['merchant_name']=df['merchant_name'].fillna('Unknown')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Merchant name not available for',df[df['merchant_name']=='Unknown']['description'].count(),'transactions')\n",
    "print('Total amount spent in unknown transactions is',df[df['merchant_name']=='Unknown']['amount'].sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us take a look at these records. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['merchant_name']=='Unknown']['description']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 3.2: Visualize expenses"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets plot amount of money spent with a aspecific merchant."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby(['merchant_name']).sum()['amount'].sort_values().plot.bar(subplots=True, figsize=(18, 4))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets plot amount of money spent based on merchant type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby(['merchant_type']).sum()['amount'].sort_values().plot.pie( figsize=(7, 7),legend=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets plot amount of money spent based on merchant sub-type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby(['merchant_sub_type']).sum()['amount'].sort_values().plot.bar(subplots=True, legend=True,figsize=(15, 4))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets plot a graph that shows amount of money spent each month on a specific expense-sub-type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "months= df['transaction_month'].unique()\n",
    "\n",
    "fig, axes = plt.subplots(nrows=6, ncols=2,figsize=(14,24))\n",
    "for i,month in enumerate(months):    \n",
    "    row=int(i/2)\n",
    "    col=i%2\n",
    "    df[df['transaction_month']==month].groupby(['merchant_sub_type']).sum()['amount'].plot.barh(title=calendar.month_name[month],ax=axes[row][col], legend=True)\n",
    "fig.tight_layout()\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 4: Identify state and city for each transaction log entry"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have identified merchant information, let us populate location information. We will use two resources for this lookup:\n",
    "1. [wordnet](https://wordnet.princeton.edu/) database.\n",
    "2. [geonames](https://www.geonames.org/) dataset. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 4.1: Populate state in which transaction took place"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Wordnet](https://wordnet.princeton.edu/) is a lexical database for english language. In Wordnet, a synset is a distinct concept that is interlinked with other synsets based on lexical, conceptual, and semantic relationships. This is an important characteristic of a synset we will use to lookup the state information.\n",
    "\n",
    "Let us see how state/country synsets look like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_synset = wordnet.synsets(\"State\",'n')[0]\n",
    "print('State:',state_synset.definition())\n",
    "country_synset = wordnet.synsets(\"Country\",'n')[0]\n",
    "print('Country:',country_synset.definition())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These are the right word synsets! We will use these synsets to identify state-codes from candidate location information. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#This function populates state_code as well as state_name for each transaction log entry.\n",
    "def populate_state(row):\n",
    "    #Populate state information for non-web/non-subscription transactions\n",
    "    if (( row['vendor_website'] =='N/A') & (row['subscription']  == 'False')):\n",
    "\n",
    "        #Since state code is towards the end in transaction log, we will iterate prediction in reverse order.\n",
    "        for result in reversed(row['prediction']):\n",
    "            if result['type'] == 'NE_STORE_LOCATION':\n",
    "                synsets = wordnet.synsets(result['key'])\n",
    "                for synset in synsets:\n",
    "                    #Adjust threshold incase correct state codes are not getting populated.\n",
    "                    if synset.path_similarity(state_synset) and synset.path_similarity(state_synset)> 0.3:\n",
    "                        row['state_name']=synset.lemmas()[0].name().strip()\n",
    "                        row['state_code']=result['key'].strip()\n",
    "                        return row   \n",
    "    return row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.apply(lambda row:populate_state(row),axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['state_code'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 4.2: Populate city and country"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, Let us download the dictionary of cities in the world that have population greater than 500 people from http://download.geonames.org/export/dump/."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "wget -O  data/config/cities500.zip 'http://download.geonames.org/export/dump/cities500.zip' -nv \n",
    "unzip -q data/config/cities500.zip -d ./data/config/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we  load the data into a dataframe for easier lookup."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "location_df = pd.read_csv('data/config/cities500.txt', header=None,names=['geonameid','name','countrycode','potential_state_code'],usecols=[0,1,8,10], encoding='utf-8', sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "location_df['name']=location_df['name'].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "location_df[(location_df['name'].str.contains('san antonio'))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that city name is not unique. We will need to couple city_name with state_code to uniquely identify the city in which purchase was made."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#This method identifies city from the transaction log description.\n",
    "def populate_city(row):\n",
    "    #Populate state information for non-web/non-subscription transactions\n",
    "    if (( row['vendor_website'] =='N/A') & (row['subscription']  == 'False')):\n",
    "        locations=[]\n",
    "        for result in row['prediction']:\n",
    "            if result['type'] == 'NE_STORE_LOCATION':\n",
    "                locations.append(result['key'])\n",
    "        \n",
    "        ngrams=get_grams(' '.join(locations))\n",
    "        #print(ngrams)\n",
    "        \n",
    "        if row['state_code'] =='N/A':\n",
    "            #Description does not contain statecode, identify the city only if a perfect match is found.\n",
    "            for ngram in ngrams:    \n",
    "                results=location_df[(location_df['name']==ngram.lower())]\n",
    "                \n",
    "                if len(results)==1:\n",
    "                    row['city_name']=ngram.strip()\n",
    "                    row['country_code']=results['countrycode']\n",
    "                    row['state_code'] =results['potential_state_code']\n",
    "                    #print(':found->' +ngram)\n",
    "                    return row\n",
    "                elif len(results)>1:\n",
    "                    print('No statecode available: Multiple candidates found. Aborting : '+results)\n",
    "        else:\n",
    "            #Description contains statecode, use the same to uniquely identify the city.\n",
    "            for ngram in ngrams:\n",
    "                results=location_df[(location_df['name'] ==ngram.lower()) &(location_df['potential_state_code'] == row['state_code'])]\n",
    "                if len(results)==1:\n",
    "                    row['city_name']=ngram.strip()\n",
    "                    row['country_code']=results['countrycode']\n",
    "                    #print('found->' +ngram)\n",
    "                    return row\n",
    "                elif len(results)>1:\n",
    "                    print(row['state_code']+'multiple candidates found. Aborting : '+results)\n",
    "\n",
    "        print('City not found in Transaction: :',row['description'],': State identified: '+row['state_name'])\n",
    "    return row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "df=df.apply(lambda row:populate_city(row),axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "City name was not populated for those records for which city information is not available in the transaction log.\n",
    "\n",
    "Next, let us visualize the expenditure by city"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "months= df['transaction_month'].unique()\n",
    "\n",
    "fig, axes = plt.subplots(nrows=4, ncols=3,figsize=(14,8))\n",
    "for i,month in enumerate(months):    \n",
    "    row=int(i/3)\n",
    "    col=i%3\n",
    "    df[df['transaction_month']==month].groupby(['city_name']).sum()['amount'].plot.barh(title=calendar.month_name[month],ax=axes[row][col], legend=True)\n",
    "    axes[row][col].yaxis.set_label_text(\"\")\n",
    "\n",
    "fig.tight_layout(pad=3.0)\n",
    "fig.show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 5: Next steps and cleanup\n",
    "\n",
    "Now that transaction data is available in a structured format, you can use it for multiple purposes such as:\n",
    "1. To identify amounts you paid for items/services that are tax deductible.\n",
    "2. If you have enough data, train a model that learns your expenditure patterns and identifies fraudulent activity.\n",
    "3. Identify expenditure patterns from the data.\n",
    "\n",
    "Here are some other models from AWS Marketlace you could potentially explore to do more with ML on your financial data:\n",
    "1. [Mphasis DeepInsights Card Fraud Analyzer](https://aws.amazon.com/marketplace/pp/prodview-cgigha6wcty26?qid=1584052648768&sr=0-2&ref_=srh_res_product_title) to identify fraudulent activity.\n",
    "2. [Credit Default Prediction](https://aws.amazon.com/marketplace/pp/prodview-ivuqcwb5yrrh2?qid=1584052502210&sr=0-1&ref_=srh_res_product_title) to help support your loan process.\n",
    "3. [Loan Approval Prediction](https://aws.amazon.com/marketplace/pp/prodview-wjoa4tqle6ism?qid=1584052983476&sr=0-5&ref_=brs_res_product_title) to help support loan approval process.\n",
    "4. [DeepInsights Branch Location Predictor](https://aws.amazon.com/marketplace/pp/prodview-b4drdxcomdyvg?qid=1584053422977&sr=0-11&ref_=brs_res_product_title) to help identify potential location for a new branch."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, if the AWS Marketplace subscription was created just for an experiment and you would like to unsubscribe, here are the steps that can be followed. Before you cancel the subscription, ensure that you do not have any [deployable model](https://console.aws.amazon.com/sagemaker/home#/models) created from the model package or using the algorithm. Note - You can find this information by looking at the container name associated with the model.\n",
    "\n",
    "**Steps to unsubscribe from the product on AWS Marketplace:**\n",
    "\n",
    "Navigate to Machine Learning tab on Your [Software subscriptions page](https://aws.amazon.com/marketplace/ai/library?productType=ml&ref_=lbr_tab_ml).\n",
    "Locate the listing that you would need to cancel, and click Cancel Subscription."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}