categorize_expenses.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip3 install langchain_community"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain_community.llms import Ollama"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nThe first man to walk on the moon was Neil Armstrong, who stepped foot onto the lunar surface on July 20, 1969 as part of 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 moon.'"
      ]
     },
     "execution_count": 48,
     "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": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nSure! Here are the expenses you provided, along with an appropriate category for each one:\\n\\n* Spotify AB by Adyen - Entertainment\\n* Beta Boulders Ams Amsterdam Nld - Sports\\n* Taxi Utrecht - Transportation\\n* Ministerie van Justitie en Veiligheid - Government\\n* Etos AMSTERDAM NLD - Retail\\n* Bistro Bar Amsterdam - Food and Beverage\\n\\nI hope this helps! Let me know if you have any other questions.'"
      ]
     },
     "execution_count": 49,
     "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",
    "Taxi Utrecht, Ministerie van Justitie en Veiligheid, Etos AMSTERDAM NLD, Bistro Bar Amsterdam\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read transaction data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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": 54,
     "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": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "316"
      ]
     },
     "execution_count": 44,
     "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": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[0, 30, 60, 90, 120, 150, 180, 210, 240, 270, 300, 316]"
      ]
     },
     "execution_count": 23,
     "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": 52,
   "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",
    "    print(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": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['', 'Of course! Here are the appropriate categories for each of the expenses you provided:', '', '1. Spotify AB by Adyen - Entertainment', '2. Beta Boulders Ams Amsterdam Nld - Sport', '3. ISS Catering Services De Meern - Food and Beverage', '4. Taxi Utrecht - Transportation', '5. Etos AMSTERDAM NLD - Groceries', '6. Bistro Bar Amsterdam - Dining']\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></td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Of course! Here are the appropriate categories...</td>\n",
       "      <td>Of course! Here are the appropriate categories...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</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>4</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>5</th>\n",
       "      <td>3. ISS Catering Services De Meern - Food and B...</td>\n",
       "      <td>3. ISS Catering Services De Meern</td>\n",
       "      <td>Food and Beverage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4. Taxi Utrecht - Transportation</td>\n",
       "      <td>4. Taxi Utrecht</td>\n",
       "      <td>Transportation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5. Etos AMSTERDAM NLD - Groceries</td>\n",
       "      <td>5. Etos AMSTERDAM NLD</td>\n",
       "      <td>Groceries</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>6. Bistro Bar Amsterdam - Dining</td>\n",
       "      <td>6. Bistro Bar Amsterdam</td>\n",
       "      <td>Dining</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             Transaction vs category  \\\n",
       "0                                                      \n",
       "1  Of course! Here are the appropriate categories...   \n",
       "2                                                      \n",
       "3             1. Spotify AB by Adyen - Entertainment   \n",
       "4         2. Beta Boulders Ams Amsterdam Nld - Sport   \n",
       "5  3. ISS Catering Services De Meern - Food and B...   \n",
       "6                   4. Taxi Utrecht - Transportation   \n",
       "7                  5. Etos AMSTERDAM NLD - Groceries   \n",
       "8                   6. Bistro Bar Amsterdam - Dining   \n",
       "\n",
       "                                         Transaction           Category  \n",
       "0                                                                  None  \n",
       "1  Of course! Here are the appropriate categories...               None  \n",
       "2                                                                  None  \n",
       "3                             1. Spotify AB by Adyen      Entertainment  \n",
       "4                 2. Beta Boulders Ams Amsterdam Nld              Sport  \n",
       "5                  3. ISS Catering Services De Meern  Food and Beverage  \n",
       "6                                    4. Taxi Utrecht     Transportation  \n",
       "7                              5. Etos AMSTERDAM NLD          Groceries  \n",
       "8                            6. Bistro Bar Amsterdam             Dining  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Test out the function\n",
    "categorize_transactions('ISS Catering Services De Meern, Taxi Utrecht, Etos AMSTERDAM NLD, Bistro Bar Amsterdam',\n",
    "                        llm)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Intialise the categories_df_all dataframe\n",
    "categories_df_all = pd.DataFrame()\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",
    "    categories_df = categorize_transactions(transaction_names, llm)\n",
    "    categories_df_all = pd.concat([categories_df_all, categories_df], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Sure, here are the categories for each of the ...</td>\n",
       "      <td>Sure, here are the categories for each of the ...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1. Belastingdienst - Taxes</td>\n",
       "      <td>1. Belastingdienst</td>\n",
       "      <td>Taxes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2. Tesco Amstelveen - Groceries</td>\n",
       "      <td>2. 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>3. Monthly Appartment Rent</td>\n",
       "      <td>Housing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>353</th>\n",
       "      <td>16. Flowingdata Livermore Usa - Business</td>\n",
       "      <td>16. 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>17. 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>18. Gling, Inc Middletown Usa</td>\n",
       "      <td>Business</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>Note: Some of the expenses may belong to more ...</td>\n",
       "      <td>Note: Some of the expenses may belong to more ...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>358 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               Transaction vs category  \\\n",
       "0    Sure, here are the categories for each of the ...   \n",
       "1                                                  NaN   \n",
       "2                           1. Belastingdienst - Taxes   \n",
       "3                      2. Tesco Amstelveen - Groceries   \n",
       "4                 3. Monthly Appartment Rent - Housing   \n",
       "..                                                 ...   \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",
       "356                                                NaN   \n",
       "357  Note: Some of the expenses may belong to more ...   \n",
       "\n",
       "                                           Transaction       Category  \n",
       "0    Sure, here are the categories for each of the ...            NaN  \n",
       "1                                                  NaN            NaN  \n",
       "2                                   1. Belastingdienst          Taxes  \n",
       "3                                  2. Tesco Amstelveen      Groceries  \n",
       "4                           3. Monthly Appartment Rent        Housing  \n",
       "..                                                 ...            ...  \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",
       "356                                                NaN            NaN  \n",
       "357  Note: Some of the expenses may belong to more ...            NaN  \n",
       "\n",
       "[358 rows x 3 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories_df_all"
   ]
  },
  {
   "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
}