categorize_expenses.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": "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": [
    "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": [
    "unique_transactions = df[\"Name / Description\"].unique()\n",
    "len(unique_transactions)"
   ]
  },
  {
   "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": [
    "#take only 30 transactions at a time\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",
    "    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",
    "    ResponseChecks(data = response)\n",
    "    \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": 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": [
    "categories_df_all = pd.DataFrame()\n",
    "max_tries = 7\n",
    "\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",
    "    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": 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": [
    "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": [
    "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": [
    "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
}