find_duplicate_transactions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Find Duplicate Transactions\n",
    "This notebook provides a test bed for finding the most efficient code to ingest a list of transactions and find potential duplicates.   \n",
    "\n",
    "It's necessary to periodically perform this task when adding downloaded mint transactions to an existing set of previously exported transactions, because there does not seem to be a way to exclude \"pending\" transactions from the mint export.   These pending transactions can morph a bit before they settle and can get imported twice in certain circumstances."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the number of days to look for duplicates\n",
    "DAYS = 3\n",
    "\n",
    "# Set a dollar threshold where it's not worth the work\n",
    "TOO_SMALL = 10.00\n",
    "\n",
    "# If desired set a year to focus on\n",
    "YEAR = 2024\n",
    "\n",
    "# Define some categories that are ignored in our analyses\n",
    "IGNORED_CATEGORIES = ['Credit Card Payment', 'Transfer']\n",
    "\n",
    "# I know that my data has lots of duplicate 529 contributions\n",
    "IGNORED_CATEGORIES.append('529 Contribution')\n",
    "IGNORED_CATEGORIES.append('Kids Spending')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2774"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import read_mint_transaction_data as rmtd\n",
    "\n",
    "# Import shared configuration file\n",
    "import expenses_config as ec\n",
    "\n",
    "# Read in the categories we'll ignore in our analysis\n",
    "ignored_groups = pd.read_csv(ec.PATH_TO_GROUPS_TO_EXCLUDE)\n",
    "\n",
    "# Read in the transaction data on disk and ensure its sorted by date\n",
    "df = rmtd.read_mint_transaction_csv(ec.PATH_TO_YOUR_TRANSACTIONS, index_on_date=False)\n",
    "if YEAR > 2010:\n",
    "    df = df[df['Date'].dt.year == 2023]\n",
    "\n",
    "# Ensure dates are sorted from top to bottom\n",
    "df = df.sort_values('Date', ascending=False)\n",
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Break transactions into groups with the same Amount, Account and Original Description\n",
    "filtered_groups = df.groupby(['Original Description', 'Account Name', 'Amount'])\n",
    "# for name, group in filtered_groups:\n",
    "#     if len(group) > 1:\n",
    "#         print(group)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define a function that checks if a group has multiple transactions within a 3 day window"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def has_duplicate_within_days(group, days=DAYS):\n",
    "    # We'll assume that the group remains sorted\n",
    "    # group = group.sort_values('Date')\n",
    "\n",
    "    # Iterate over the group and check if adjacent dates are within the specified range\n",
    "    for i in range(len(group) - 1):\n",
    "        if (group['Date'].iloc[i] - group['Date'].iloc[i + 1]) <= pd.Timedelta(days=days):\n",
    "            return True\n",
    "    return False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define a function that will eliminate groups from analysis based on certain criteria"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def group_is_excluded(group):\n",
    "    # Ignore if multiple transaction types..probably a refund\n",
    "    if len(group) == 2 and len(group['Transaction Type'].unique()) == 2:\n",
    "        return True\n",
    "\n",
    "    # Ignore if specifically called out as a non duplicate\n",
    "    if 'Not-Duplicate' in group.Labels.unique():\n",
    "        return True\n",
    "\n",
    "    return False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define a function for breaking up groups with more than 3 transactions into smaller possible subgroups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_large_group(group):\n",
    "    '''\n",
    "        Break up a group of > 3 transactions into possible subgroups with\n",
    "        one or more transactions within a window of DAYS\n",
    "\n",
    "        Note that this requires the group to be sorted by date decending\n",
    "    '''\n",
    "    # print(f'Breaking up group for: ${name}')\n",
    "    # print(group)\n",
    "    group_number = 0\n",
    "    group_numbers = [0]\n",
    "    previous_date = group['Date'].iloc[0]  # Start with the first date\n",
    "\n",
    "    # Iterate over the DataFrame, starting from the second row\n",
    "    for date in group['Date'].iloc[1:]:\n",
    "        if (previous_date - date).days > DAYS:\n",
    "            group_number += 1\n",
    "        group_numbers.append(group_number)\n",
    "        previous_date = date\n",
    "\n",
    "    # Add the sub group numbers into the group's dataframe and group again\n",
    "    group['Group'] = group_numbers\n",
    "    sub_groups = group.groupby('Group')\n",
    "    for sub_name, sub_group in sub_groups:\n",
    "        size = len(sub_group)\n",
    "        if size == 1:\n",
    "            continue\n",
    "        if group_is_excluded(sub_group):\n",
    "            continue\n",
    "        if size == 2:\n",
    "            print(sub_group)\n",
    "        else:\n",
    "            print('This is a big group')\n",
    "            print(sub_group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Reviewing transactions from the year 2024:\n",
      "Will print out any possible duplicates where there are multiple transactions\n",
      "that have the same value for \"Original Description\", \"Account Name\", and \"Amount\"\n",
      "within a window of 3 days\n",
      "\n",
      "Transactions less than 10.00 are ignored\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Check groups to see if multiple elements are within +/- 3 Days of each other\n",
    "if (YEAR < 2010):\n",
    "    print('Reviewing all transactions:')\n",
    "else:\n",
    "    print(f'Reviewing transactions from the year {YEAR}:')\n",
    "print('Will print out any possible duplicates where there are multiple transactions')\n",
    "print('that have the same value for \"Original Description\", \"Account Name\", and \"Amount\"')\n",
    "print(f'within a window of {DAYS} days\\n')\n",
    "if TOO_SMALL > 0:\n",
    "    print(f'Transactions less than {TOO_SMALL:.2f} are ignored\\n')\n",
    "\n",
    "\n",
    "for name, group in filtered_groups:\n",
    "    # Skip categories that we ingnore in analysis\n",
    "    if len(group.Category.unique()) == 1:\n",
    "        if group.Category.unique()[0] in IGNORED_CATEGORIES:\n",
    "            continue\n",
    "\n",
    "    # skip small dollar amounts -- set TOO_SMALL in first cell\n",
    "    if group.Amount.unique()[0] < TOO_SMALL:\n",
    "        continue\n",
    "\n",
    "    # Ignore Rents from Apt 2F which are often the same thing twice\n",
    "    if group.Labels.unique()[0] == 'Label-Rent Unit-2F' or group.Labels.unique()[0] == 'Label-Utilities Unit-2F':\n",
    "        continue\n",
    "    \n",
    "    if len(group) > 2:\n",
    "        process_large_group(group)\n",
    "    elif len(group) == 2:\n",
    "        # print('Evaluating group of two')\n",
    "        # print(group)\n",
    "        if not group_is_excluded(group) and has_duplicate_within_days(group):\n",
    "            print('Possible Duplicate')\n",
    "            print(group)\n",
    "        # else:\n",
    "        #     print('Too far apart')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "analyze-mint-transactions",
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}