categorize_expenses_with_validation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip3 install langchain_community"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain_community.llms import Ollama"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nThe first man on the moon was Neil Armstrong. He stepped foot on the moon\\'s surface on July 20, 1969, during the Apollo 11 mission. Armstrong famously declared \"That\\'s one small step for man, one giant leap for mankind\" as he became the first person to walk on the lunar surface.'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "llm = Ollama(model=\"llama2\")\n",
    "llm.invoke(\"The first man on the moon was ...\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nCertainly! Here are the categories for each of the expenses you provided:\\n\\n* Spotify AB by Adyen - Entertainment\\n* Beta Boulders Ams Amsterdam Nld - Sports\\n* ISS Catering Services De Meern - Food and Beverage\\n* Vishandel Sier AMSTELVEEN - Retail\\n* Ministerie van Justitie en Veiligheid - Government\\n* Etos AMSTERDAM NLD - Retail\\n* Bistro Bar Amsterdam - Food and Beverage'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "llm.invoke(\"Can you add an appropriate category next to each of the following expenses. Respond with a list of categories separated by commas. For example, Spotify AB by Adyen - \\\n",
    "Entertainment, Beta Boulders Ams Amsterdam Nld - Sports, etc.: \\\n",
    "ISS Catering Services De Meern, Vishandel Sier AMSTELVEEN, Ministerie van Justitie en Veiligheid, Etos AMSTERDAM NLD, Bistro Bar Amsterdam\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read transaction data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Date</th>\n",
       "      <th>Name / Description</th>\n",
       "      <th>Expense/Income</th>\n",
       "      <th>Amount (EUR)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-12-30</td>\n",
       "      <td>Belastingdienst</td>\n",
       "      <td>Expense</td>\n",
       "      <td>9.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-12-30</td>\n",
       "      <td>Tesco Breda</td>\n",
       "      <td>Expense</td>\n",
       "      <td>17.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-12-30</td>\n",
       "      <td>Monthly Appartment Rent</td>\n",
       "      <td>Expense</td>\n",
       "      <td>451.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-12-30</td>\n",
       "      <td>Vishandel Sier Amsterdam</td>\n",
       "      <td>Expense</td>\n",
       "      <td>12.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-12-29</td>\n",
       "      <td>Selling Paintings</td>\n",
       "      <td>Income</td>\n",
       "      <td>13.63</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date        Name / Description Expense/Income  Amount (EUR)\n",
       "0  2023-12-30           Belastingdienst        Expense          9.96\n",
       "1  2023-12-30               Tesco Breda        Expense         17.53\n",
       "2  2023-12-30   Monthly Appartment Rent        Expense        451.00\n",
       "3  2023-12-30  Vishandel Sier Amsterdam        Expense         12.46\n",
       "4  2023-12-29         Selling Paintings         Income         13.63"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read the transactions_2022_2023.csv file \n",
    "import pandas as pd\n",
    "df = pd.read_csv(\"transactions_2022_2023.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "23"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get unique transactions in the Name / Description column\n",
    "unique_transactions = df[\"Name / Description\"].unique()\n",
    "len(unique_transactions)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Tesco Amstelveen', 'Monthly Appartment Rent',\n",
       "       'Vishandel Sier Amstelveen', 'Selling Paintings',\n",
       "       'Spotify Ab By Adyen', 'Tls Bv Inz Ov-Chipkaart',\n",
       "       'Tikkie Zakelijk', 'Tk Maxx Amsterdam Da', 'Consulting'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_transactions[1:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Categorise bank transactions with Llama2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[0, 23]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get index list\n",
    "#https://stackoverflow.com/questions/47518609/for-loop-range-and-interval-how-to-include-last-step\n",
    "def hop(start, stop, step):\n",
    "    for i in range(start, stop, step):\n",
    "        yield i\n",
    "    yield stop\n",
    "\n",
    "index_list = list(hop(0, len(unique_transactions), 30))\n",
    "index_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ResponseChecks(data=None)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Output validation\n",
    "from pydantic import BaseModel, field_validator\n",
    "from typing import List\n",
    "\n",
    "# Validate response format - check if it actually contains hyphen (\"-\")\n",
    "class ResponseChecks(BaseModel):\n",
    "    data: List[str]\n",
    "\n",
    "    @field_validator(\"data\")\n",
    "    def check(cls, value):\n",
    "        for item in value:\n",
    "            if len(item) > 0:\n",
    "                assert \"-\" in item, \"String does not contain hyphen.\"\n",
    "\n",
    "# Test validation\n",
    "ResponseChecks(data = ['Hello - World', 'Hello - there!'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def categorize_transactions(transaction_names, llm):\n",
    "    response = llm.invoke(\"Can you add an appropriate category to the following expenses. For example: Spotify AB by Adyen - Entertainment, Beta Boulders Ams Amsterdam Nld - Sport, etc.. Categories should be less than 4 words. \" + transaction_names)\n",
    "    response = response.split('\\n')\n",
    "\n",
    "    # Keep only the lines in between blank lines (removing the explaination lines at the beginning and end of the response)\n",
    "    blank_indexes = [index for index in range(len(response)) if response[index] == '']\n",
    "    if len(blank_indexes) == 1:\n",
    "        response = response[(blank_indexes[0] + 1):]\n",
    "    else:\n",
    "        response = response[(blank_indexes[0] + 1) : blank_indexes[1]]\n",
    "\n",
    "    # Print response and validate if it is in the correct format\n",
    "    print(response)\n",
    "    ResponseChecks(data = response)\n",
    "    \n",
    "    # Put in dataframe\n",
    "    categories_df = pd.DataFrame({'Transaction vs category': response})\n",
    "    categories_df[['Transaction', 'Category']] = categories_df['Transaction vs category'].str.split(' - ', expand=True)\n",
    "    \n",
    "    return categories_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['1. Spotify AB by Adyen - Entertainment', '2. Beta Boulders Ams Amsterdam Nld - Sport', '3. ISS Catering Services De Meern - Food', '4. Vishandel Sier AMSTELVEEN - Grocery', '5. Etos AMSTERDAM NLD - Convenience', '6. Bistro Bar Amsterdam - Food']\n"
     ]
    },
    {
     "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>Transaction vs category</th>\n",
       "      <th>Transaction</th>\n",
       "      <th>Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1. Spotify AB by Adyen - Entertainment</td>\n",
       "      <td>1. Spotify AB by Adyen</td>\n",
       "      <td>Entertainment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2. Beta Boulders Ams Amsterdam Nld - Sport</td>\n",
       "      <td>2. Beta Boulders Ams Amsterdam Nld</td>\n",
       "      <td>Sport</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3. ISS Catering Services De Meern - Food</td>\n",
       "      <td>3. ISS Catering Services De Meern</td>\n",
       "      <td>Food</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4. Vishandel Sier AMSTELVEEN - Grocery</td>\n",
       "      <td>4. Vishandel Sier AMSTELVEEN</td>\n",
       "      <td>Grocery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5. Etos AMSTERDAM NLD - Convenience</td>\n",
       "      <td>5. Etos AMSTERDAM NLD</td>\n",
       "      <td>Convenience</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6. Bistro Bar Amsterdam - Food</td>\n",
       "      <td>6. Bistro Bar Amsterdam</td>\n",
       "      <td>Food</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Transaction vs category  \\\n",
       "0      1. Spotify AB by Adyen - Entertainment   \n",
       "1  2. Beta Boulders Ams Amsterdam Nld - Sport   \n",
       "2    3. ISS Catering Services De Meern - Food   \n",
       "3      4. Vishandel Sier AMSTELVEEN - Grocery   \n",
       "4         5. Etos AMSTERDAM NLD - Convenience   \n",
       "5              6. Bistro Bar Amsterdam - Food   \n",
       "\n",
       "                          Transaction       Category  \n",
       "0              1. Spotify AB by Adyen  Entertainment  \n",
       "1  2. Beta Boulders Ams Amsterdam Nld          Sport  \n",
       "2   3. ISS Catering Services De Meern           Food  \n",
       "3        4. Vishandel Sier AMSTELVEEN        Grocery  \n",
       "4               5. Etos AMSTERDAM NLD    Convenience  \n",
       "5             6. Bistro Bar Amsterdam           Food  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Test out the function\n",
    "categorize_transactions('ISS Catering Services De Meern, Vishandel Sier AMSTELVEEN, Etos AMSTERDAM NLD, Bistro Bar Amsterdam',\n",
    "                        llm)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['1. Belastingdienst - Taxes', '2. Tesco Breda - Groceries', '3. Monthly Appartment Rent - Housing', '4. Vishandel Sier Amsterdam - Food', '5. Selling Paintings - Art/Crafts', '6. Spotify Ab By Adyen - Entertainment', '7. Tk Maxx Amsterdam Da - Shopping', '8. Consulting - Professional Services', '9. Aidsfonds - Charity', '10. TLS BV Inz Ov-Chipkaart - Transportation', '11. Etos Amsterdam - Groceries', '12. Beta Boulders Ams Amsterdam - Fitness', '13. Salary - Personal Finance', '14. Bouldermuur Bv Amsterdam - Professional Services', '15. Birtat Restaurant Amsterdam - Dining', '16. Freelancing - Personal Finance', '17. Tikkie - Shopping', '18. Blogging - Personal Finance', '19. Taxi Utrecht - Transportation', '20. Apple Services - Technology', '21. Amazon Lux - Online Shopping', '22. Classpass* Monthly - Fitness', '23. Audible Uk AdblCo/Pymt Gbr - Entertainment']\n"
     ]
    }
   ],
   "source": [
    "# Intialise the categories_df_all dataframe\n",
    "categories_df_all = pd.DataFrame()\n",
    "max_tries = 7\n",
    "\n",
    "# Loop through the index_list\n",
    "for i in range(0, len(index_list)-1):\n",
    "    transaction_names = unique_transactions[index_list[i]:index_list[i+1]]\n",
    "    transaction_names = ','.join(transaction_names)\n",
    "\n",
    "    # Try and validate output, if it fails, try again for max_tries=7 times\n",
    "    for j in range(1, max_tries):\n",
    "        try:\n",
    "            categories_df = categorize_transactions(transaction_names, llm)\n",
    "            categories_df_all = pd.concat([categories_df_all, categories_df], ignore_index=True)\n",
    "            \n",
    "        except:\n",
    "            if j < max_tries:\n",
    "                continue\n",
    "            else:\n",
    "                raise Exception(f\"Cannot categorise transactions indexes {i} to {i+1}.\")\n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>Transaction vs category</th>\n",
       "      <th>Transaction</th>\n",
       "      <th>Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1. Belastingdienst - Taxes</td>\n",
       "      <td>1. Belastingdienst</td>\n",
       "      <td>Taxes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2. Tesco Breda - Groceries</td>\n",
       "      <td>2. Tesco Breda</td>\n",
       "      <td>Groceries</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3. Monthly Appartment Rent - Housing</td>\n",
       "      <td>3. Monthly Appartment Rent</td>\n",
       "      <td>Housing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4. Vishandel Sier Amsterdam - Food</td>\n",
       "      <td>4. Vishandel Sier Amsterdam</td>\n",
       "      <td>Food</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5. Selling Paintings - Art/Crafts</td>\n",
       "      <td>5. Selling Paintings</td>\n",
       "      <td>Art/Crafts</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Transaction vs category                  Transaction  \\\n",
       "0            1. Belastingdienst - Taxes           1. Belastingdienst   \n",
       "1            2. Tesco Breda - Groceries               2. Tesco Breda   \n",
       "2  3. Monthly Appartment Rent - Housing   3. Monthly Appartment Rent   \n",
       "3    4. Vishandel Sier Amsterdam - Food  4. Vishandel Sier Amsterdam   \n",
       "4     5. Selling Paintings - Art/Crafts         5. Selling Paintings   \n",
       "\n",
       "     Category  \n",
       "0       Taxes  \n",
       "1   Groceries  \n",
       "2     Housing  \n",
       "3        Food  \n",
       "4  Art/Crafts  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories_df_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "# categories_df_all.to_csv(\"categories_df_all.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([nan, 'Taxes', 'Groceries', 'Housing', 'Shopping', 'Art/Crafts',\n",
       "       'Entertainment', 'Transportation', 'Business Services',\n",
       "       'Professional Services', 'Charity/Donations', 'Food/Beverage',\n",
       "       'Travel', 'Self-Employment', 'Financial Services', 'Technology',\n",
       "       'Home Improvement', 'Business', 'Miscellaneous', 'Food & Beverage',\n",
       "       'Health & Beauty', 'Grocery', 'Finance', 'Sport',\n",
       "       'Food and Beverage', 'Travel and Transportation', 'Retail',\n",
       "       'Health and Wellness', 'Education', 'Construction and Maintenance',\n",
       "       'Health and Beauty', 'Clothing', 'Clothing and Accessories',\n",
       "       'Travel and Leisure', 'Art & Food', 'Local Government',\n",
       "       'E-commerce', 'Shipping', 'Sports & Fitness', 'Food and Drink',\n",
       "       'Accommodation', 'Energy', 'Marketing and Advertising',\n",
       "       'Convenience Store', 'Home and Garden', 'Real Estate',\n",
       "       'Language Services', 'Legal Services', 'Sports and Fitness',\n",
       "       'Business and Services', 'Travel and Tourism', 'Food & Dining',\n",
       "       'Arts & Culture', 'Recreation', 'Nature & Outdoors',\n",
       "       'Beauty & Wellness', 'Health & Wellness', 'Food',\n",
       "       'Online Shopping', 'Subscription'], dtype=object)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get unique categories in categories_df_all\n",
    "unique_categories = categories_df_all[\"Category\"].unique()\n",
    "unique_categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/2t/5c_z4g5j7z77jprgswwd1htr0000gn/T/ipykernel_95551/1238705727.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  categories_df_all.loc[categories_df_all['Category'].str.contains(\"Food\"), 'Category'] = \"Food and Drinks\"\n",
      "/var/folders/2t/5c_z4g5j7z77jprgswwd1htr0000gn/T/ipykernel_95551/1238705727.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  categories_df_all.loc[categories_df_all['Category'].str.contains(\"Clothing\"), 'Category'] = \"Clothing\"\n",
      "/var/folders/2t/5c_z4g5j7z77jprgswwd1htr0000gn/T/ipykernel_95551/1238705727.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  categories_df_all.loc[categories_df_all['Category'].str.contains(\"Services\"), 'Category'] = \"Services\"\n",
      "/var/folders/2t/5c_z4g5j7z77jprgswwd1htr0000gn/T/ipykernel_95551/1238705727.py:11: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  categories_df_all.loc[categories_df_all['Category'].str.contains(\"Health|Wellness\"), 'Category'] = \"Health and Wellness\"\n",
      "/var/folders/2t/5c_z4g5j7z77jprgswwd1htr0000gn/T/ipykernel_95551/1238705727.py:14: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  categories_df_all.loc[categories_df_all['Category'].str.contains(\"Sport\"), 'Category'] = \"Sport and Fitness\"\n",
      "/var/folders/2t/5c_z4g5j7z77jprgswwd1htr0000gn/T/ipykernel_95551/1238705727.py:16: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  categories_df_all.loc[categories_df_all['Category'].str.contains(\"Travel\"), 'Category'] = \"Travel\"\n"
     ]
    }
   ],
   "source": [
    "# Drop NA values\n",
    "categories_df_all = categories_df_all.dropna()\n",
    "\n",
    "# If category contains \"Food\", then categorise as \"Food and Drinks\"\n",
    "categories_df_all.loc[categories_df_all['Category'].str.contains(\"Food\"), 'Category'] = \"Food and Drinks\"\n",
    "# If category contains \"Clothing\", then categorise as \"Clothing\"\n",
    "categories_df_all.loc[categories_df_all['Category'].str.contains(\"Clothing\"), 'Category'] = \"Clothing\"\n",
    "# If category contains \"Services\", then categorise as \"Services\"\n",
    "categories_df_all.loc[categories_df_all['Category'].str.contains(\"Services\"), 'Category'] = \"Services\"\n",
    "# If category contains \"Health\" or \"Wellness\", then categorise as \"Health and Wellness\"\n",
    "categories_df_all.loc[categories_df_all['Category'].str.contains(\"Health|Wellness\"), 'Category'] = \"Health and Wellness\"\n",
    "# If category contains \"Sport\", then categorise as \"Sport\n",
    "#  and Fitness\"\n",
    "categories_df_all.loc[categories_df_all['Category'].str.contains(\"Sport\"), 'Category'] = \"Sport and Fitness\"\n",
    "# If category contains \"Travel\", then categorise as \"Travel\"\n",
    "categories_df_all.loc[categories_df_all['Category'].str.contains(\"Travel\"), 'Category'] = \"Travel\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/2t/5c_z4g5j7z77jprgswwd1htr0000gn/T/ipykernel_95551/2440992137.py:2: FutureWarning: The default value of regex will change from True to False in a future version.\n",
      "  categories_df_all['Transaction'] = categories_df_all['Transaction'].str.replace(r'\\d+\\.\\s+', '')\n",
      "/var/folders/2t/5c_z4g5j7z77jprgswwd1htr0000gn/T/ipykernel_95551/2440992137.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  categories_df_all['Transaction'] = categories_df_all['Transaction'].str.replace(r'\\d+\\.\\s+', '')\n"
     ]
    },
    {
     "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>Transaction vs category</th>\n",
       "      <th>Transaction</th>\n",
       "      <th>Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1. Belastingdienst - Taxes</td>\n",
       "      <td>Belastingdienst</td>\n",
       "      <td>Taxes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2. Tesco Amstelveen - Groceries</td>\n",
       "      <td>Tesco Amstelveen</td>\n",
       "      <td>Groceries</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3. Monthly Appartment Rent - Housing</td>\n",
       "      <td>Monthly Appartment Rent</td>\n",
       "      <td>Housing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4. Vishandel Sier Amstelveen - Shopping</td>\n",
       "      <td>Vishandel Sier Amstelveen</td>\n",
       "      <td>Shopping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5. Selling Paintings - Art/Crafts</td>\n",
       "      <td>Selling Paintings</td>\n",
       "      <td>Art/Crafts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>351</th>\n",
       "      <td>14. Amazon Lux - Online Shopping</td>\n",
       "      <td>Amazon Lux</td>\n",
       "      <td>Online Shopping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>352</th>\n",
       "      <td>15. Classpass* Monthly Missoula Usa - Subscrip...</td>\n",
       "      <td>Classpass* Monthly Missoula Usa</td>\n",
       "      <td>Subscription</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>353</th>\n",
       "      <td>16. Flowingdata Livermore Usa - Business</td>\n",
       "      <td>Flowingdata Livermore Usa</td>\n",
       "      <td>Business</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>354</th>\n",
       "      <td>17. Audible Uk AdblCo/Pymt Gbr - Entertainment</td>\n",
       "      <td>Audible Uk AdblCo/Pymt Gbr</td>\n",
       "      <td>Entertainment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355</th>\n",
       "      <td>18. Gling, Inc Middletown Usa - Business</td>\n",
       "      <td>Gling, Inc Middletown Usa</td>\n",
       "      <td>Business</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>313 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               Transaction vs category  \\\n",
       "2                           1. Belastingdienst - Taxes   \n",
       "3                      2. Tesco Amstelveen - Groceries   \n",
       "4                 3. Monthly Appartment Rent - Housing   \n",
       "5              4. Vishandel Sier Amstelveen - Shopping   \n",
       "6                    5. Selling Paintings - Art/Crafts   \n",
       "..                                                 ...   \n",
       "351                   14. Amazon Lux - Online Shopping   \n",
       "352  15. Classpass* Monthly Missoula Usa - Subscrip...   \n",
       "353           16. Flowingdata Livermore Usa - Business   \n",
       "354     17. Audible Uk AdblCo/Pymt Gbr - Entertainment   \n",
       "355           18. Gling, Inc Middletown Usa - Business   \n",
       "\n",
       "                         Transaction         Category  \n",
       "2                    Belastingdienst            Taxes  \n",
       "3                   Tesco Amstelveen        Groceries  \n",
       "4            Monthly Appartment Rent          Housing  \n",
       "5          Vishandel Sier Amstelveen         Shopping  \n",
       "6                  Selling Paintings       Art/Crafts  \n",
       "..                               ...              ...  \n",
       "351                       Amazon Lux  Online Shopping  \n",
       "352  Classpass* Monthly Missoula Usa     Subscription  \n",
       "353        Flowingdata Livermore Usa         Business  \n",
       "354       Audible Uk AdblCo/Pymt Gbr    Entertainment  \n",
       "355        Gling, Inc Middletown Usa         Business  \n",
       "\n",
       "[313 rows x 3 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remove the numbering eg \"1. \" from Transaction column\n",
    "categories_df_all['Transaction'] = categories_df_all['Transaction'].str.replace(r'\\d+\\.\\s+', '')\n",
    "categories_df_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>Date</th>\n",
       "      <th>Name / Description</th>\n",
       "      <th>Expense/Income</th>\n",
       "      <th>Amount (EUR)</th>\n",
       "      <th>Transaction vs category</th>\n",
       "      <th>Transaction</th>\n",
       "      <th>Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-12-30</td>\n",
       "      <td>Belastingdienst</td>\n",
       "      <td>Expense</td>\n",
       "      <td>9.96</td>\n",
       "      <td>1. Belastingdienst - Taxes</td>\n",
       "      <td>Belastingdienst</td>\n",
       "      <td>Taxes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-12-30</td>\n",
       "      <td>Tesco Amstelveen</td>\n",
       "      <td>Expense</td>\n",
       "      <td>17.53</td>\n",
       "      <td>2. Tesco Amstelveen - Groceries</td>\n",
       "      <td>Tesco Amstelveen</td>\n",
       "      <td>Groceries</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-12-30</td>\n",
       "      <td>Monthly Appartment Rent</td>\n",
       "      <td>Expense</td>\n",
       "      <td>451.00</td>\n",
       "      <td>3. Monthly Appartment Rent - Housing</td>\n",
       "      <td>Monthly Appartment Rent</td>\n",
       "      <td>Housing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-12-30</td>\n",
       "      <td>Vishandel Sier Amstelveen</td>\n",
       "      <td>Expense</td>\n",
       "      <td>12.46</td>\n",
       "      <td>4. Vishandel Sier Amstelveen - Shopping</td>\n",
       "      <td>Vishandel Sier Amstelveen</td>\n",
       "      <td>Shopping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-12-29</td>\n",
       "      <td>Selling Paintings</td>\n",
       "      <td>Income</td>\n",
       "      <td>13.63</td>\n",
       "      <td>5. Selling Paintings - Art/Crafts</td>\n",
       "      <td>Selling Paintings</td>\n",
       "      <td>Art/Crafts</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1561</th>\n",
       "      <td>2022-01-16</td>\n",
       "      <td>Amazon Lux</td>\n",
       "      <td>Expense</td>\n",
       "      <td>24.11</td>\n",
       "      <td>14. Amazon Lux - Online Shopping</td>\n",
       "      <td>Amazon Lux</td>\n",
       "      <td>Online Shopping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1562</th>\n",
       "      <td>2022-01-15</td>\n",
       "      <td>Classpass* Monthly Missoula Usa</td>\n",
       "      <td>Expense</td>\n",
       "      <td>30.08</td>\n",
       "      <td>15. Classpass* Monthly Missoula Usa - Subscrip...</td>\n",
       "      <td>Classpass* Monthly Missoula Usa</td>\n",
       "      <td>Subscription</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1563</th>\n",
       "      <td>2022-01-15</td>\n",
       "      <td>Flowingdata Livermore Usa</td>\n",
       "      <td>Expense</td>\n",
       "      <td>17.98</td>\n",
       "      <td>16. Flowingdata Livermore Usa - Business</td>\n",
       "      <td>Flowingdata Livermore Usa</td>\n",
       "      <td>Business</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1564</th>\n",
       "      <td>2022-01-14</td>\n",
       "      <td>Audible Uk AdblCo/Pymt Gbr</td>\n",
       "      <td>Expense</td>\n",
       "      <td>11.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1565</th>\n",
       "      <td>2022-01-14</td>\n",
       "      <td>Gling, Inc Middletown Usa</td>\n",
       "      <td>Expense</td>\n",
       "      <td>14.30</td>\n",
       "      <td>18. Gling, Inc Middletown Usa - Business</td>\n",
       "      <td>Gling, Inc Middletown Usa</td>\n",
       "      <td>Business</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1566 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Date               Name / Description Expense/Income  \\\n",
       "0     2023-12-30                  Belastingdienst        Expense   \n",
       "1     2023-12-30                 Tesco Amstelveen        Expense   \n",
       "2     2023-12-30          Monthly Appartment Rent        Expense   \n",
       "3     2023-12-30        Vishandel Sier Amstelveen        Expense   \n",
       "4     2023-12-29                Selling Paintings         Income   \n",
       "...          ...                              ...            ...   \n",
       "1561  2022-01-16                       Amazon Lux        Expense   \n",
       "1562  2022-01-15  Classpass* Monthly Missoula Usa        Expense   \n",
       "1563  2022-01-15        Flowingdata Livermore Usa        Expense   \n",
       "1564  2022-01-14       Audible Uk AdblCo/Pymt Gbr        Expense   \n",
       "1565  2022-01-14        Gling, Inc Middletown Usa        Expense   \n",
       "\n",
       "      Amount (EUR)                            Transaction vs category  \\\n",
       "0             9.96                         1. Belastingdienst - Taxes   \n",
       "1            17.53                    2. Tesco Amstelveen - Groceries   \n",
       "2           451.00               3. Monthly Appartment Rent - Housing   \n",
       "3            12.46            4. Vishandel Sier Amstelveen - Shopping   \n",
       "4            13.63                  5. Selling Paintings - Art/Crafts   \n",
       "...            ...                                                ...   \n",
       "1561         24.11                   14. Amazon Lux - Online Shopping   \n",
       "1562         30.08  15. Classpass* Monthly Missoula Usa - Subscrip...   \n",
       "1563         17.98           16. Flowingdata Livermore Usa - Business   \n",
       "1564         11.00                                                NaN   \n",
       "1565         14.30           18. Gling, Inc Middletown Usa - Business   \n",
       "\n",
       "                          Transaction         Category  \n",
       "0                     Belastingdienst            Taxes  \n",
       "1                    Tesco Amstelveen        Groceries  \n",
       "2             Monthly Appartment Rent          Housing  \n",
       "3           Vishandel Sier Amstelveen         Shopping  \n",
       "4                   Selling Paintings       Art/Crafts  \n",
       "...                               ...              ...  \n",
       "1561                       Amazon Lux  Online Shopping  \n",
       "1562  Classpass* Monthly Missoula Usa     Subscription  \n",
       "1563        Flowingdata Livermore Usa         Business  \n",
       "1564                              NaN              NaN  \n",
       "1565        Gling, Inc Middletown Usa         Business  \n",
       "\n",
       "[1566 rows x 7 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge the categories_df_all with the transactions_2022_2023.csv dataframe (df)\n",
    "df = pd.read_csv(\"transactions_2022_2023.csv\")\n",
    "df.loc[df['Name / Description'].str.contains(\"Spotify\"), 'Name / Description'] = \"Spotify Ab By Adyen\"\n",
    "df = pd.merge(df, categories_df_all, left_on='Name / Description', right_on='Transaction', how='left')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"transactions_2022_2023_categorized.csv\", index=False)"
   ]
  }
 ],
 "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.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}