02_analyze_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "37332e92-78ad-4cc1-9e05-294c5bf2040a",
   "metadata": {},
   "source": [
    "# Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "0d39a3e4-9ec1-4a81-9461-612572c07a5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "TRANSACTION_INDEX = 'TPN_ID'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cbf4f2f-4188-4198-91e3-399956d0d846",
   "metadata": {},
   "source": [
    "# Open data files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ad81b566-cd14-4206-841b-19d0acd5b4c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "original = pd.read_csv(\"./data/original_sorted.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "60cb3f80-1d72-4fe3-8cc5-285cd0976b87",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5568 entries, 0 to 5567\n",
      "Data columns (total 12 columns):\n",
      " #   Column       Non-Null Count  Dtype \n",
      "---  ------       --------------  ----- \n",
      " 0   RC#          5568 non-null   int64 \n",
      " 1   RC Name      5568 non-null   object\n",
      " 2   Category     5568 non-null   int64 \n",
      " 3   Vendor#      5568 non-null   int64 \n",
      " 4   Vendor Name  5568 non-null   object\n",
      " 5   Address 1    5544 non-null   object\n",
      " 6   Address 2    1290 non-null   object\n",
      " 7   City         5469 non-null   object\n",
      " 8   State        5371 non-null   object\n",
      " 9   ZIP code     5557 non-null   object\n",
      " 10  Amount       5568 non-null   int64 \n",
      " 11  TPN_ID       5568 non-null   object\n",
      "dtypes: int64(4), object(8)\n",
      "memory usage: 522.1+ KB\n"
     ]
    }
   ],
   "source": [
    "original.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "45fd62cb-ef31-49b3-a234-a832a71ca2db",
   "metadata": {},
   "outputs": [],
   "source": [
    "updated = pd.read_csv(\"./data/updated_sorted.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "71c16f27-be9c-4965-a0e0-c30e8fb2051e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5920 entries, 0 to 5919\n",
      "Data columns (total 12 columns):\n",
      " #   Column       Non-Null Count  Dtype \n",
      "---  ------       --------------  ----- \n",
      " 0   RC#          5920 non-null   int64 \n",
      " 1   RC Name      5920 non-null   object\n",
      " 2   Category     5920 non-null   int64 \n",
      " 3   Vendor#      5920 non-null   int64 \n",
      " 4   Vendor Name  5920 non-null   object\n",
      " 5   Address 1    5896 non-null   object\n",
      " 6   Address 2    1359 non-null   object\n",
      " 7   City         5821 non-null   object\n",
      " 8   State        5716 non-null   object\n",
      " 9   ZIP code     5909 non-null   object\n",
      " 10  Amount       5920 non-null   int64 \n",
      " 11  TPN_ID       5920 non-null   object\n",
      "dtypes: int64(4), object(8)\n",
      "memory usage: 555.1+ KB\n"
     ]
    }
   ],
   "source": [
    "updated.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5dd5f749-2ef1-4862-9559-01e29c46569d",
   "metadata": {},
   "source": [
    "# Top-line numbers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4e5ee955-7bcb-4de0-97f2-af1678cb5603",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30599050"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_sum = original.Amount.sum()\n",
    "updated_sum = updated.Amount.sum()\n",
    "updated_sum - original_sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "bf42a774-ee9a-4713-925f-84e86e7c540d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.08429766752297206"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(updated_sum - original_sum)/original_sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "df6bf752-2a54-4cc9-bc67-a73ffb3054af",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "352"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_transactions = len(updated.index.unique()) - len(original.index.unique())\n",
    "new_transactions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6af7f07-1fe9-4b79-b50a-494262df5901",
   "metadata": {},
   "source": [
    "# Merge transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5c9aaa99-b698-4642-8d3a-fd7345834f2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_transactions = original.merge(updated, on=TRANSACTION_INDEX, suffixes=('_o', '_u'))\n",
    "merged_transactions['AmountDiff'] = merged_transactions.Amount_u - merged_transactions.Amount_o"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75e0c29b-7dc0-4ae5-abdc-17c48b6bc392",
   "metadata": {},
   "source": [
    "# Largest amounts - change from original to updated"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "095e8e16-7c9b-41f8-9d06-b2f000892bb0",
   "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>RC#_o</th>\n",
       "      <th>RC Name_o</th>\n",
       "      <th>Category_o</th>\n",
       "      <th>Vendor#_o</th>\n",
       "      <th>Vendor Name_o</th>\n",
       "      <th>Address 1_o</th>\n",
       "      <th>Address 2_o</th>\n",
       "      <th>City_o</th>\n",
       "      <th>State_o</th>\n",
       "      <th>ZIP code_o</th>\n",
       "      <th>...</th>\n",
       "      <th>Category_u</th>\n",
       "      <th>Vendor#_u</th>\n",
       "      <th>Vendor Name_u</th>\n",
       "      <th>Address 1_u</th>\n",
       "      <th>Address 2_u</th>\n",
       "      <th>City_u</th>\n",
       "      <th>State_u</th>\n",
       "      <th>ZIP code_u</th>\n",
       "      <th>Amount_u</th>\n",
       "      <th>AmountDiff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4768</th>\n",
       "      <td>85</td>\n",
       "      <td>SOMD Administration</td>\n",
       "      <td>7300</td>\n",
       "      <td>424204</td>\n",
       "      <td>BPA II LTD</td>\n",
       "      <td>1468 WEST 9TH STREET SUITE 835</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>OH</td>\n",
       "      <td>44113</td>\n",
       "      <td>...</td>\n",
       "      <td>7300</td>\n",
       "      <td>424204</td>\n",
       "      <td>BPA II LTD</td>\n",
       "      <td>1468 WEST 9TH STREET SUITE 835</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>OH</td>\n",
       "      <td>44113</td>\n",
       "      <td>10951439</td>\n",
       "      <td>2584599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5304</th>\n",
       "      <td>92</td>\n",
       "      <td>Business and Auxiliary Services</td>\n",
       "      <td>7300</td>\n",
       "      <td>505445</td>\n",
       "      <td>WYNDHAM PITTSBURGH UNIVERSITY CENTER</td>\n",
       "      <td>545 E JOHN CARPENTER FREEWAY STE 1300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IRVING</td>\n",
       "      <td>TX</td>\n",
       "      <td>75062</td>\n",
       "      <td>...</td>\n",
       "      <td>7300</td>\n",
       "      <td>505445</td>\n",
       "      <td>WYNDHAM PITTSBURGH UNIVERSITY CENTER</td>\n",
       "      <td>545 E JOHN CARPENTER FREEWAY STE 1300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IRVING</td>\n",
       "      <td>TX</td>\n",
       "      <td>75062</td>\n",
       "      <td>11657790</td>\n",
       "      <td>2023060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4414</th>\n",
       "      <td>83</td>\n",
       "      <td>General University</td>\n",
       "      <td>7700</td>\n",
       "      <td>204776</td>\n",
       "      <td>MARSH USA, INC</td>\n",
       "      <td>P O BOX 281915</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATLANTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30384</td>\n",
       "      <td>...</td>\n",
       "      <td>7700</td>\n",
       "      <td>204776</td>\n",
       "      <td>MARSH USA, INC</td>\n",
       "      <td>P O BOX 281915</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATLANTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30384</td>\n",
       "      <td>6071082</td>\n",
       "      <td>1735802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5310</th>\n",
       "      <td>92</td>\n",
       "      <td>Business and Auxiliary Services</td>\n",
       "      <td>7300</td>\n",
       "      <td>687995</td>\n",
       "      <td>MWK FORBES II LLC</td>\n",
       "      <td>3341 FORBES AVENUE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PITTSBURGH</td>\n",
       "      <td>PA</td>\n",
       "      <td>15213</td>\n",
       "      <td>...</td>\n",
       "      <td>7300</td>\n",
       "      <td>687995</td>\n",
       "      <td>MWK FORBES II LLC</td>\n",
       "      <td>3341 FORBES AVENUE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PITTSBURGH</td>\n",
       "      <td>PA</td>\n",
       "      <td>15213</td>\n",
       "      <td>6280388</td>\n",
       "      <td>1112046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3799</th>\n",
       "      <td>67</td>\n",
       "      <td>Facilities Management</td>\n",
       "      <td>7000</td>\n",
       "      <td>103658</td>\n",
       "      <td>DUQUESNE LIGHT COMPANY</td>\n",
       "      <td>PO BOX 830012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BALTIMORE</td>\n",
       "      <td>MD</td>\n",
       "      <td>21283-0012</td>\n",
       "      <td>...</td>\n",
       "      <td>7000</td>\n",
       "      <td>103658</td>\n",
       "      <td>DUQUESNE LIGHT COMPANY</td>\n",
       "      <td>PO BOX 830012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BALTIMORE</td>\n",
       "      <td>MD</td>\n",
       "      <td>21283-0012</td>\n",
       "      <td>24578216</td>\n",
       "      <td>747194</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      RC#_o                        RC Name_o  Category_o  Vendor#_o  \\\n",
       "4768     85              SOMD Administration        7300     424204   \n",
       "5304     92  Business and Auxiliary Services        7300     505445   \n",
       "4414     83               General University        7700     204776   \n",
       "5310     92  Business and Auxiliary Services        7300     687995   \n",
       "3799     67            Facilities Management        7000     103658   \n",
       "\n",
       "                             Vendor Name_o  \\\n",
       "4768                            BPA II LTD   \n",
       "5304  WYNDHAM PITTSBURGH UNIVERSITY CENTER   \n",
       "4414                        MARSH USA, INC   \n",
       "5310                     MWK FORBES II LLC   \n",
       "3799                DUQUESNE LIGHT COMPANY   \n",
       "\n",
       "                                Address 1_o Address 2_o      City_o State_o  \\\n",
       "4768         1468 WEST 9TH STREET SUITE 835         NaN   CLEVELAND      OH   \n",
       "5304  545 E JOHN CARPENTER FREEWAY STE 1300         NaN      IRVING      TX   \n",
       "4414                         P O BOX 281915         NaN     ATLANTA      GA   \n",
       "5310                     3341 FORBES AVENUE         NaN  PITTSBURGH      PA   \n",
       "3799                          PO BOX 830012         NaN   BALTIMORE      MD   \n",
       "\n",
       "      ZIP code_o  ...  Category_u Vendor#_u  \\\n",
       "4768       44113  ...        7300    424204   \n",
       "5304       75062  ...        7300    505445   \n",
       "4414       30384  ...        7700    204776   \n",
       "5310       15213  ...        7300    687995   \n",
       "3799  21283-0012  ...        7000    103658   \n",
       "\n",
       "                             Vendor Name_u  \\\n",
       "4768                            BPA II LTD   \n",
       "5304  WYNDHAM PITTSBURGH UNIVERSITY CENTER   \n",
       "4414                        MARSH USA, INC   \n",
       "5310                     MWK FORBES II LLC   \n",
       "3799                DUQUESNE LIGHT COMPANY   \n",
       "\n",
       "                                Address 1_u  Address 2_u      City_u State_u  \\\n",
       "4768         1468 WEST 9TH STREET SUITE 835          NaN   CLEVELAND      OH   \n",
       "5304  545 E JOHN CARPENTER FREEWAY STE 1300          NaN      IRVING      TX   \n",
       "4414                         P O BOX 281915          NaN     ATLANTA      GA   \n",
       "5310                     3341 FORBES AVENUE          NaN  PITTSBURGH      PA   \n",
       "3799                          PO BOX 830012          NaN   BALTIMORE      MD   \n",
       "\n",
       "      ZIP code_u  Amount_u AmountDiff  \n",
       "4768       44113  10951439    2584599  \n",
       "5304       75062  11657790    2023060  \n",
       "4414       30384   6071082    1735802  \n",
       "5310       15213   6280388    1112046  \n",
       "3799  21283-0012  24578216     747194  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_transactions.sort_values(by=['AmountDiff'], ascending=False).head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ddb7577-2213-4ecd-b812-b9244dd97016",
   "metadata": {},
   "source": [
    "# Amounts - change from original to updated"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "898833f9-d5f1-4995-8656-78ca5bd97275",
   "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>RC#_o</th>\n",
       "      <th>RC Name_o</th>\n",
       "      <th>Category_o</th>\n",
       "      <th>Vendor#_o</th>\n",
       "      <th>Vendor Name_o</th>\n",
       "      <th>Address 1_o</th>\n",
       "      <th>Address 2_o</th>\n",
       "      <th>City_o</th>\n",
       "      <th>State_o</th>\n",
       "      <th>ZIP code_o</th>\n",
       "      <th>...</th>\n",
       "      <th>Category_u</th>\n",
       "      <th>Vendor#_u</th>\n",
       "      <th>Vendor Name_u</th>\n",
       "      <th>Address 1_u</th>\n",
       "      <th>Address 2_u</th>\n",
       "      <th>City_u</th>\n",
       "      <th>State_u</th>\n",
       "      <th>ZIP code_u</th>\n",
       "      <th>Amount_u</th>\n",
       "      <th>AmountDiff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>1</td>\n",
       "      <td>Chancellor</td>\n",
       "      <td>6400</td>\n",
       "      <td>320609</td>\n",
       "      <td>MEDIAQUEST</td>\n",
       "      <td>4891 CAMPBELLS RUN RD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PITTSBURGH</td>\n",
       "      <td>PA</td>\n",
       "      <td>15205</td>\n",
       "      <td>...</td>\n",
       "      <td>6400</td>\n",
       "      <td>320609</td>\n",
       "      <td>MEDIAQUEST</td>\n",
       "      <td>4891 CAMPBELLS RUN RD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PITTSBURGH</td>\n",
       "      <td>PA</td>\n",
       "      <td>15205</td>\n",
       "      <td>23212</td>\n",
       "      <td>-27455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>3</td>\n",
       "      <td>SVC Research</td>\n",
       "      <td>6400</td>\n",
       "      <td>424663</td>\n",
       "      <td>CPA GLOBAL LIMITED</td>\n",
       "      <td>225 REINEKERS LANE</td>\n",
       "      <td>STE. 400</td>\n",
       "      <td>ALEXANDRIA</td>\n",
       "      <td>VA</td>\n",
       "      <td>22314</td>\n",
       "      <td>...</td>\n",
       "      <td>6400</td>\n",
       "      <td>424663</td>\n",
       "      <td>CPA GLOBAL LIMITED</td>\n",
       "      <td>225 REINEKERS LANE</td>\n",
       "      <td>STE. 400</td>\n",
       "      <td>ALEXANDRIA</td>\n",
       "      <td>VA</td>\n",
       "      <td>22314</td>\n",
       "      <td>927936</td>\n",
       "      <td>-9364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1938</th>\n",
       "      <td>35</td>\n",
       "      <td>School of Medicine</td>\n",
       "      <td>7100</td>\n",
       "      <td>100897</td>\n",
       "      <td>AQUA FILTER FRESH INC</td>\n",
       "      <td>PO BOX 14128</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PITTSBURGH</td>\n",
       "      <td>PA</td>\n",
       "      <td>15239-0128</td>\n",
       "      <td>...</td>\n",
       "      <td>7100</td>\n",
       "      <td>100897</td>\n",
       "      <td>AQUA FILTER FRESH INC</td>\n",
       "      <td>PO BOX 14128</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PITTSBURGH</td>\n",
       "      <td>PA</td>\n",
       "      <td>15239-0128</td>\n",
       "      <td>4213</td>\n",
       "      <td>-5836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3558</th>\n",
       "      <td>61</td>\n",
       "      <td>Pitt Information Technology</td>\n",
       "      <td>7400</td>\n",
       "      <td>482143</td>\n",
       "      <td>COMCAST BUSINESS</td>\n",
       "      <td>ONE COMCAST CENTER 32ND FL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PHILADELPHIA</td>\n",
       "      <td>PA</td>\n",
       "      <td>19103-2838</td>\n",
       "      <td>...</td>\n",
       "      <td>7400</td>\n",
       "      <td>482143</td>\n",
       "      <td>COMCAST BUSINESS</td>\n",
       "      <td>ONE COMCAST CENTER 32ND FL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PHILADELPHIA</td>\n",
       "      <td>PA</td>\n",
       "      <td>19103-2838</td>\n",
       "      <td>40233</td>\n",
       "      <td>-3773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5541</th>\n",
       "      <td>94</td>\n",
       "      <td>School of Computing and Information</td>\n",
       "      <td>6000</td>\n",
       "      <td>100877</td>\n",
       "      <td>APPLE COMPUTER INC</td>\n",
       "      <td>PO BOX 281877</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATLANTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30384-1877</td>\n",
       "      <td>...</td>\n",
       "      <td>6000</td>\n",
       "      <td>100877</td>\n",
       "      <td>APPLE COMPUTER INC</td>\n",
       "      <td>PO BOX 281877</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATLANTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30384-1877</td>\n",
       "      <td>13142</td>\n",
       "      <td>-2142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>921</th>\n",
       "      <td>22</td>\n",
       "      <td>School of Education</td>\n",
       "      <td>6400</td>\n",
       "      <td>500751</td>\n",
       "      <td>INRESONANCE</td>\n",
       "      <td>32 INDUSTRIAL DRIVE EAST</td>\n",
       "      <td>SUITE 1</td>\n",
       "      <td>NORTHAMPTON</td>\n",
       "      <td>MA</td>\n",
       "      <td>01060</td>\n",
       "      <td>...</td>\n",
       "      <td>6400</td>\n",
       "      <td>500751</td>\n",
       "      <td>INRESONANCE</td>\n",
       "      <td>PO BOX 122145</td>\n",
       "      <td>DEPT 2145</td>\n",
       "      <td>DALLAS</td>\n",
       "      <td>TX</td>\n",
       "      <td>75312-2145</td>\n",
       "      <td>64907</td>\n",
       "      <td>-1840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>418</th>\n",
       "      <td>6</td>\n",
       "      <td>Kenneth P. Dietrich School of Arts and Sciences</td>\n",
       "      <td>6000</td>\n",
       "      <td>503874</td>\n",
       "      <td>SAI LIFE SCIENCES LIMITED</td>\n",
       "      <td>GROUND 1ST 2ND &amp; 3RD FL BLDG I PLOT NO 2</td>\n",
       "      <td>CHRYSALIS ENCLAVE INTL BIOTECH PARK</td>\n",
       "      <td>HINJEWADI</td>\n",
       "      <td>PUNE</td>\n",
       "      <td>411 057</td>\n",
       "      <td>...</td>\n",
       "      <td>6000</td>\n",
       "      <td>503874</td>\n",
       "      <td>SAI LIFE SCIENCES LIMITED</td>\n",
       "      <td>GROUND 1ST 2ND &amp; 3RD FL BLDG I PLOT NO 2</td>\n",
       "      <td>CHRYSALIS ENCLAVE INTL BIOTECH PARK</td>\n",
       "      <td>HINJEWADI</td>\n",
       "      <td>PUNE</td>\n",
       "      <td>411 057</td>\n",
       "      <td>19355</td>\n",
       "      <td>-1155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>428</th>\n",
       "      <td>6</td>\n",
       "      <td>Kenneth P. Dietrich School of Arts and Sciences</td>\n",
       "      <td>6000</td>\n",
       "      <td>555151</td>\n",
       "      <td>LABWEAR.COM</td>\n",
       "      <td>480 BARNUM AVE BUILDING 5 SUITE 30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BRIDGEPORT</td>\n",
       "      <td>CT</td>\n",
       "      <td>06608</td>\n",
       "      <td>...</td>\n",
       "      <td>6000</td>\n",
       "      <td>555151</td>\n",
       "      <td>LABWEAR.COM</td>\n",
       "      <td>480 BARNUM AVE BUILDING 5 SUITE 30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BRIDGEPORT</td>\n",
       "      <td>CT</td>\n",
       "      <td>06608</td>\n",
       "      <td>1685</td>\n",
       "      <td>-727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4178</th>\n",
       "      <td>80</td>\n",
       "      <td>Athletics</td>\n",
       "      <td>6000</td>\n",
       "      <td>675887</td>\n",
       "      <td>KINETIC PERFORMANCE TECHNOLOGY PTY LTD</td>\n",
       "      <td>UNIT 8 26-28 WINCHCOMBE CT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MITCHELL ACT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2911</td>\n",
       "      <td>...</td>\n",
       "      <td>6000</td>\n",
       "      <td>675887</td>\n",
       "      <td>KINETIC PERFORMANCE TECHNOLOGY PTY LTD</td>\n",
       "      <td>UNIT 8 26-28 WINCHCOMBE CT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MITCHELL ACT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2911</td>\n",
       "      <td>17163</td>\n",
       "      <td>-555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>Chancellor</td>\n",
       "      <td>6000</td>\n",
       "      <td>521322</td>\n",
       "      <td>SUPRA OFFICE SOLUTIONS INC</td>\n",
       "      <td>PO BOX 201</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BALA CYNWYD</td>\n",
       "      <td>PA</td>\n",
       "      <td>19004</td>\n",
       "      <td>...</td>\n",
       "      <td>6000</td>\n",
       "      <td>521322</td>\n",
       "      <td>SUPRA OFFICE SOLUTIONS INC</td>\n",
       "      <td>PO BOX 201</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BALA CYNWYD</td>\n",
       "      <td>PA</td>\n",
       "      <td>19004</td>\n",
       "      <td>6302</td>\n",
       "      <td>-298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2296</th>\n",
       "      <td>41</td>\n",
       "      <td>Johnstown Campus</td>\n",
       "      <td>7100</td>\n",
       "      <td>390128</td>\n",
       "      <td>PENSTAN SUPPLY</td>\n",
       "      <td>850 HORNER STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JOHNSTOWN</td>\n",
       "      <td>PA</td>\n",
       "      <td>15902</td>\n",
       "      <td>...</td>\n",
       "      <td>7100</td>\n",
       "      <td>390128</td>\n",
       "      <td>PENSTAN SUPPLY</td>\n",
       "      <td>850 HORNER STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JOHNSTOWN</td>\n",
       "      <td>PA</td>\n",
       "      <td>15902</td>\n",
       "      <td>24085</td>\n",
       "      <td>-188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4787</th>\n",
       "      <td>87</td>\n",
       "      <td>SVC and Chief Financial Officer</td>\n",
       "      <td>6000</td>\n",
       "      <td>521322</td>\n",
       "      <td>SUPRA OFFICE SOLUTIONS INC</td>\n",
       "      <td>PO BOX 201</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BALA CYNWYD</td>\n",
       "      <td>PA</td>\n",
       "      <td>19004</td>\n",
       "      <td>...</td>\n",
       "      <td>6000</td>\n",
       "      <td>521322</td>\n",
       "      <td>SUPRA OFFICE SOLUTIONS INC</td>\n",
       "      <td>PO BOX 201</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BALA CYNWYD</td>\n",
       "      <td>PA</td>\n",
       "      <td>19004</td>\n",
       "      <td>10895</td>\n",
       "      <td>-71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2261</th>\n",
       "      <td>41</td>\n",
       "      <td>Johnstown Campus</td>\n",
       "      <td>7000</td>\n",
       "      <td>271651</td>\n",
       "      <td>PENELEC</td>\n",
       "      <td>PO BOX 3687</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AKRON</td>\n",
       "      <td>OH</td>\n",
       "      <td>44309-3687</td>\n",
       "      <td>...</td>\n",
       "      <td>7000</td>\n",
       "      <td>271651</td>\n",
       "      <td>PENELEC</td>\n",
       "      <td>PO BOX 3687</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AKRON</td>\n",
       "      <td>OH</td>\n",
       "      <td>44309-3687</td>\n",
       "      <td>1001221</td>\n",
       "      <td>-55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2589</th>\n",
       "      <td>44</td>\n",
       "      <td>Bradford Campus</td>\n",
       "      <td>6000</td>\n",
       "      <td>311515</td>\n",
       "      <td>BISETT BUILDING CENTER</td>\n",
       "      <td>PO BOX 196</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BRADFORD</td>\n",
       "      <td>PA</td>\n",
       "      <td>16701</td>\n",
       "      <td>...</td>\n",
       "      <td>6000</td>\n",
       "      <td>311515</td>\n",
       "      <td>BISETT BUILDING CENTER</td>\n",
       "      <td>PO BOX 196</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BRADFORD</td>\n",
       "      <td>PA</td>\n",
       "      <td>16701</td>\n",
       "      <td>4888</td>\n",
       "      <td>-23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      RC#_o                                        RC Name_o  Category_o  \\\n",
       "39        1                                       Chancellor        6400   \n",
       "167       3                                     SVC Research        6400   \n",
       "1938     35                               School of Medicine        7100   \n",
       "3558     61                      Pitt Information Technology        7400   \n",
       "5541     94              School of Computing and Information        6000   \n",
       "921      22                              School of Education        6400   \n",
       "418       6  Kenneth P. Dietrich School of Arts and Sciences        6000   \n",
       "428       6  Kenneth P. Dietrich School of Arts and Sciences        6000   \n",
       "4178     80                                        Athletics        6000   \n",
       "11        1                                       Chancellor        6000   \n",
       "2296     41                                 Johnstown Campus        7100   \n",
       "4787     87                  SVC and Chief Financial Officer        6000   \n",
       "2261     41                                 Johnstown Campus        7000   \n",
       "2589     44                                  Bradford Campus        6000   \n",
       "\n",
       "      Vendor#_o                           Vendor Name_o  \\\n",
       "39       320609                              MEDIAQUEST   \n",
       "167      424663                      CPA GLOBAL LIMITED   \n",
       "1938     100897                   AQUA FILTER FRESH INC   \n",
       "3558     482143                        COMCAST BUSINESS   \n",
       "5541     100877                      APPLE COMPUTER INC   \n",
       "921      500751                             INRESONANCE   \n",
       "418      503874               SAI LIFE SCIENCES LIMITED   \n",
       "428      555151                             LABWEAR.COM   \n",
       "4178     675887  KINETIC PERFORMANCE TECHNOLOGY PTY LTD   \n",
       "11       521322              SUPRA OFFICE SOLUTIONS INC   \n",
       "2296     390128                          PENSTAN SUPPLY   \n",
       "4787     521322              SUPRA OFFICE SOLUTIONS INC   \n",
       "2261     271651                                 PENELEC   \n",
       "2589     311515                  BISETT BUILDING CENTER   \n",
       "\n",
       "                                   Address 1_o  \\\n",
       "39                       4891 CAMPBELLS RUN RD   \n",
       "167                         225 REINEKERS LANE   \n",
       "1938                              PO BOX 14128   \n",
       "3558                ONE COMCAST CENTER 32ND FL   \n",
       "5541                             PO BOX 281877   \n",
       "921                   32 INDUSTRIAL DRIVE EAST   \n",
       "418   GROUND 1ST 2ND & 3RD FL BLDG I PLOT NO 2   \n",
       "428         480 BARNUM AVE BUILDING 5 SUITE 30   \n",
       "4178                UNIT 8 26-28 WINCHCOMBE CT   \n",
       "11                                  PO BOX 201   \n",
       "2296                         850 HORNER STREET   \n",
       "4787                                PO BOX 201   \n",
       "2261                               PO BOX 3687   \n",
       "2589                                PO BOX 196   \n",
       "\n",
       "                              Address 2_o        City_o State_o  ZIP code_o  \\\n",
       "39                                    NaN    PITTSBURGH      PA       15205   \n",
       "167                              STE. 400    ALEXANDRIA      VA       22314   \n",
       "1938                                  NaN    PITTSBURGH      PA  15239-0128   \n",
       "3558                                  NaN  PHILADELPHIA      PA  19103-2838   \n",
       "5541                                  NaN       ATLANTA      GA  30384-1877   \n",
       "921                               SUITE 1   NORTHAMPTON      MA       01060   \n",
       "418   CHRYSALIS ENCLAVE INTL BIOTECH PARK     HINJEWADI    PUNE     411 057   \n",
       "428                                   NaN    BRIDGEPORT      CT       06608   \n",
       "4178                                  NaN  MITCHELL ACT     NaN        2911   \n",
       "11                                    NaN   BALA CYNWYD      PA       19004   \n",
       "2296                                  NaN     JOHNSTOWN      PA       15902   \n",
       "4787                                  NaN   BALA CYNWYD      PA       19004   \n",
       "2261                                  NaN         AKRON      OH  44309-3687   \n",
       "2589                                  NaN      BRADFORD      PA       16701   \n",
       "\n",
       "      ...  Category_u Vendor#_u                           Vendor Name_u  \\\n",
       "39    ...        6400    320609                              MEDIAQUEST   \n",
       "167   ...        6400    424663                      CPA GLOBAL LIMITED   \n",
       "1938  ...        7100    100897                   AQUA FILTER FRESH INC   \n",
       "3558  ...        7400    482143                        COMCAST BUSINESS   \n",
       "5541  ...        6000    100877                      APPLE COMPUTER INC   \n",
       "921   ...        6400    500751                             INRESONANCE   \n",
       "418   ...        6000    503874               SAI LIFE SCIENCES LIMITED   \n",
       "428   ...        6000    555151                             LABWEAR.COM   \n",
       "4178  ...        6000    675887  KINETIC PERFORMANCE TECHNOLOGY PTY LTD   \n",
       "11    ...        6000    521322              SUPRA OFFICE SOLUTIONS INC   \n",
       "2296  ...        7100    390128                          PENSTAN SUPPLY   \n",
       "4787  ...        6000    521322              SUPRA OFFICE SOLUTIONS INC   \n",
       "2261  ...        7000    271651                                 PENELEC   \n",
       "2589  ...        6000    311515                  BISETT BUILDING CENTER   \n",
       "\n",
       "                                   Address 1_u  \\\n",
       "39                       4891 CAMPBELLS RUN RD   \n",
       "167                         225 REINEKERS LANE   \n",
       "1938                              PO BOX 14128   \n",
       "3558                ONE COMCAST CENTER 32ND FL   \n",
       "5541                             PO BOX 281877   \n",
       "921                              PO BOX 122145   \n",
       "418   GROUND 1ST 2ND & 3RD FL BLDG I PLOT NO 2   \n",
       "428         480 BARNUM AVE BUILDING 5 SUITE 30   \n",
       "4178                UNIT 8 26-28 WINCHCOMBE CT   \n",
       "11                                  PO BOX 201   \n",
       "2296                         850 HORNER STREET   \n",
       "4787                                PO BOX 201   \n",
       "2261                               PO BOX 3687   \n",
       "2589                                PO BOX 196   \n",
       "\n",
       "                              Address 2_u        City_u State_u  ZIP code_u  \\\n",
       "39                                    NaN    PITTSBURGH      PA       15205   \n",
       "167                              STE. 400    ALEXANDRIA      VA       22314   \n",
       "1938                                  NaN    PITTSBURGH      PA  15239-0128   \n",
       "3558                                  NaN  PHILADELPHIA      PA  19103-2838   \n",
       "5541                                  NaN       ATLANTA      GA  30384-1877   \n",
       "921                             DEPT 2145        DALLAS      TX  75312-2145   \n",
       "418   CHRYSALIS ENCLAVE INTL BIOTECH PARK     HINJEWADI    PUNE     411 057   \n",
       "428                                   NaN    BRIDGEPORT      CT       06608   \n",
       "4178                                  NaN  MITCHELL ACT     NaN        2911   \n",
       "11                                    NaN   BALA CYNWYD      PA       19004   \n",
       "2296                                  NaN     JOHNSTOWN      PA       15902   \n",
       "4787                                  NaN   BALA CYNWYD      PA       19004   \n",
       "2261                                  NaN         AKRON      OH  44309-3687   \n",
       "2589                                  NaN      BRADFORD      PA       16701   \n",
       "\n",
       "     Amount_u AmountDiff  \n",
       "39      23212     -27455  \n",
       "167    927936      -9364  \n",
       "1938     4213      -5836  \n",
       "3558    40233      -3773  \n",
       "5541    13142      -2142  \n",
       "921     64907      -1840  \n",
       "418     19355      -1155  \n",
       "428      1685       -727  \n",
       "4178    17163       -555  \n",
       "11       6302       -298  \n",
       "2296    24085       -188  \n",
       "4787    10895        -71  \n",
       "2261  1001221        -55  \n",
       "2589     4888        -23  \n",
       "\n",
       "[14 rows x 24 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_transactions[merged_transactions['AmountDiff'] < 0].sort_values(by=['AmountDiff'], ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "5ac73814-8b1e-4b90-b30c-01db408ad4f8",
   "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>RC#_o</th>\n",
       "      <th>RC Name_o</th>\n",
       "      <th>Category_o</th>\n",
       "      <th>Vendor#_o</th>\n",
       "      <th>Vendor Name_o</th>\n",
       "      <th>Address 1_o</th>\n",
       "      <th>Address 2_o</th>\n",
       "      <th>City_o</th>\n",
       "      <th>State_o</th>\n",
       "      <th>ZIP code_o</th>\n",
       "      <th>...</th>\n",
       "      <th>Category_u</th>\n",
       "      <th>Vendor#_u</th>\n",
       "      <th>Vendor Name_u</th>\n",
       "      <th>Address 1_u</th>\n",
       "      <th>Address 2_u</th>\n",
       "      <th>City_u</th>\n",
       "      <th>State_u</th>\n",
       "      <th>ZIP code_u</th>\n",
       "      <th>Amount_u</th>\n",
       "      <th>AmountDiff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Chancellor</td>\n",
       "      <td>6000</td>\n",
       "      <td>100877</td>\n",
       "      <td>APPLE COMPUTER INC</td>\n",
       "      <td>PO BOX 281877</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATLANTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30384-1877</td>\n",
       "      <td>...</td>\n",
       "      <td>6000</td>\n",
       "      <td>100877</td>\n",
       "      <td>APPLE COMPUTER INC</td>\n",
       "      <td>PO BOX 281877</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATLANTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30384-1877</td>\n",
       "      <td>18071</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Chancellor</td>\n",
       "      <td>6000</td>\n",
       "      <td>101243</td>\n",
       "      <td>B&amp;H PHOTO VIDEO</td>\n",
       "      <td>119 WEST 17TH STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NEW YORK</td>\n",
       "      <td>NY</td>\n",
       "      <td>10011</td>\n",
       "      <td>...</td>\n",
       "      <td>6000</td>\n",
       "      <td>101243</td>\n",
       "      <td>B&amp;H PHOTO VIDEO</td>\n",
       "      <td>119 WEST 17TH STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NEW YORK</td>\n",
       "      <td>NY</td>\n",
       "      <td>10011</td>\n",
       "      <td>4019</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Chancellor</td>\n",
       "      <td>6000</td>\n",
       "      <td>104384</td>\n",
       "      <td>FRANKLIN INTERIORS</td>\n",
       "      <td>PO BOX 6107</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HERMITAGE</td>\n",
       "      <td>PA</td>\n",
       "      <td>16148-0923</td>\n",
       "      <td>...</td>\n",
       "      <td>6000</td>\n",
       "      <td>104384</td>\n",
       "      <td>FRANKLIN INTERIORS</td>\n",
       "      <td>PO BOX 6107</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HERMITAGE</td>\n",
       "      <td>PA</td>\n",
       "      <td>16148-0923</td>\n",
       "      <td>3284</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Chancellor</td>\n",
       "      <td>6000</td>\n",
       "      <td>119301</td>\n",
       "      <td>WORKSCAPE INC</td>\n",
       "      <td>KNOLL C/O WORKSCAPE</td>\n",
       "      <td>PO BOX 277778</td>\n",
       "      <td>ATLANTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30384-7778</td>\n",
       "      <td>...</td>\n",
       "      <td>6000</td>\n",
       "      <td>119301</td>\n",
       "      <td>WORKSCAPE INC</td>\n",
       "      <td>KNOLL C/O WORKSCAPE</td>\n",
       "      <td>PO BOX 277778</td>\n",
       "      <td>ATLANTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30384-7778</td>\n",
       "      <td>2051</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>Chancellor</td>\n",
       "      <td>6000</td>\n",
       "      <td>223247</td>\n",
       "      <td>GEORGIA TECH RESEARCH CORP</td>\n",
       "      <td>PO BOX 100117</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATLANTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30384</td>\n",
       "      <td>...</td>\n",
       "      <td>6000</td>\n",
       "      <td>223247</td>\n",
       "      <td>GEORGIA TECH RESEARCH CORP</td>\n",
       "      <td>PO BOX 100117</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATLANTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30384</td>\n",
       "      <td>5120</td>\n",
       "      <td>0</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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>5557</th>\n",
       "      <td>94</td>\n",
       "      <td>School of Computing and Information</td>\n",
       "      <td>6400</td>\n",
       "      <td>509937</td>\n",
       "      <td>GO-U LLC</td>\n",
       "      <td>2310 RIDGE AVENUE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EVANSTON</td>\n",
       "      <td>IL</td>\n",
       "      <td>60201</td>\n",
       "      <td>...</td>\n",
       "      <td>6400</td>\n",
       "      <td>509937</td>\n",
       "      <td>GO-U LLC</td>\n",
       "      <td>2310 RIDGE AVENUE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EVANSTON</td>\n",
       "      <td>IL</td>\n",
       "      <td>60201</td>\n",
       "      <td>8000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5558</th>\n",
       "      <td>94</td>\n",
       "      <td>School of Computing and Information</td>\n",
       "      <td>6400</td>\n",
       "      <td>562181</td>\n",
       "      <td>ADAM REGER LLC</td>\n",
       "      <td>409 EMERSON AVE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PITTSBURGH</td>\n",
       "      <td>PA</td>\n",
       "      <td>15215</td>\n",
       "      <td>...</td>\n",
       "      <td>6400</td>\n",
       "      <td>562181</td>\n",
       "      <td>ADAM REGER LLC</td>\n",
       "      <td>409 EMERSON AVE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PITTSBURGH</td>\n",
       "      <td>PA</td>\n",
       "      <td>15215</td>\n",
       "      <td>2000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5559</th>\n",
       "      <td>94</td>\n",
       "      <td>School of Computing and Information</td>\n",
       "      <td>6400</td>\n",
       "      <td>588965</td>\n",
       "      <td>EMMA INC</td>\n",
       "      <td>75 REMITTANCE DRIVE SUITE 6222</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>IL</td>\n",
       "      <td>60675-6222</td>\n",
       "      <td>...</td>\n",
       "      <td>6400</td>\n",
       "      <td>588965</td>\n",
       "      <td>EMMA INC</td>\n",
       "      <td>75 REMITTANCE DRIVE SUITE 6222</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>IL</td>\n",
       "      <td>60675-6222</td>\n",
       "      <td>2868</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560</th>\n",
       "      <td>94</td>\n",
       "      <td>School of Computing and Information</td>\n",
       "      <td>6400</td>\n",
       "      <td>687570</td>\n",
       "      <td>THOMAS, JAMES           5</td>\n",
       "      <td>2910 N VAN NESS BLVD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FRESNO</td>\n",
       "      <td>CA</td>\n",
       "      <td>93704</td>\n",
       "      <td>...</td>\n",
       "      <td>6400</td>\n",
       "      <td>687570</td>\n",
       "      <td>THOMAS, JAMES           5</td>\n",
       "      <td>2910 N VAN NESS BLVD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FRESNO</td>\n",
       "      <td>CA</td>\n",
       "      <td>93704</td>\n",
       "      <td>1999</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5561</th>\n",
       "      <td>94</td>\n",
       "      <td>School of Computing and Information</td>\n",
       "      <td>6900</td>\n",
       "      <td>328199</td>\n",
       "      <td>ASSOCIATION FOR LIBRARY AND INFORMATION</td>\n",
       "      <td>39349 TREASURY CENTER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>IL</td>\n",
       "      <td>60694-9300</td>\n",
       "      <td>...</td>\n",
       "      <td>6900</td>\n",
       "      <td>328199</td>\n",
       "      <td>ASSOCIATION FOR LIBRARY AND INFORMATION</td>\n",
       "      <td>39349 TREASURY CENTER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>IL</td>\n",
       "      <td>60694-9300</td>\n",
       "      <td>2300</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5309 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      RC#_o                            RC Name_o  Category_o  Vendor#_o  \\\n",
       "0         1                           Chancellor        6000     100877   \n",
       "1         1                           Chancellor        6000     101243   \n",
       "3         1                           Chancellor        6000     104384   \n",
       "4         1                           Chancellor        6000     119301   \n",
       "6         1                           Chancellor        6000     223247   \n",
       "...     ...                                  ...         ...        ...   \n",
       "5557     94  School of Computing and Information        6400     509937   \n",
       "5558     94  School of Computing and Information        6400     562181   \n",
       "5559     94  School of Computing and Information        6400     588965   \n",
       "5560     94  School of Computing and Information        6400     687570   \n",
       "5561     94  School of Computing and Information        6900     328199   \n",
       "\n",
       "                                Vendor Name_o                     Address 1_o  \\\n",
       "0                          APPLE COMPUTER INC                   PO BOX 281877   \n",
       "1                             B&H PHOTO VIDEO            119 WEST 17TH STREET   \n",
       "3                          FRANKLIN INTERIORS                     PO BOX 6107   \n",
       "4                               WORKSCAPE INC             KNOLL C/O WORKSCAPE   \n",
       "6                  GEORGIA TECH RESEARCH CORP                   PO BOX 100117   \n",
       "...                                       ...                             ...   \n",
       "5557                                 GO-U LLC               2310 RIDGE AVENUE   \n",
       "5558                           ADAM REGER LLC                 409 EMERSON AVE   \n",
       "5559                                 EMMA INC  75 REMITTANCE DRIVE SUITE 6222   \n",
       "5560                THOMAS, JAMES           5            2910 N VAN NESS BLVD   \n",
       "5561  ASSOCIATION FOR LIBRARY AND INFORMATION           39349 TREASURY CENTER   \n",
       "\n",
       "        Address 2_o      City_o State_o  ZIP code_o  ...  Category_u  \\\n",
       "0               NaN     ATLANTA      GA  30384-1877  ...        6000   \n",
       "1               NaN    NEW YORK      NY       10011  ...        6000   \n",
       "3               NaN   HERMITAGE      PA  16148-0923  ...        6000   \n",
       "4     PO BOX 277778     ATLANTA      GA  30384-7778  ...        6000   \n",
       "6               NaN     ATLANTA      GA       30384  ...        6000   \n",
       "...             ...         ...     ...         ...  ...         ...   \n",
       "5557            NaN    EVANSTON      IL       60201  ...        6400   \n",
       "5558            NaN  PITTSBURGH      PA       15215  ...        6400   \n",
       "5559            NaN     CHICAGO      IL  60675-6222  ...        6400   \n",
       "5560            NaN      FRESNO      CA       93704  ...        6400   \n",
       "5561            NaN     CHICAGO      IL  60694-9300  ...        6900   \n",
       "\n",
       "     Vendor#_u                            Vendor Name_u  \\\n",
       "0       100877                       APPLE COMPUTER INC   \n",
       "1       101243                          B&H PHOTO VIDEO   \n",
       "3       104384                       FRANKLIN INTERIORS   \n",
       "4       119301                            WORKSCAPE INC   \n",
       "6       223247               GEORGIA TECH RESEARCH CORP   \n",
       "...        ...                                      ...   \n",
       "5557    509937                                 GO-U LLC   \n",
       "5558    562181                           ADAM REGER LLC   \n",
       "5559    588965                                 EMMA INC   \n",
       "5560    687570                THOMAS, JAMES           5   \n",
       "5561    328199  ASSOCIATION FOR LIBRARY AND INFORMATION   \n",
       "\n",
       "                         Address 1_u    Address 2_u      City_u State_u  \\\n",
       "0                      PO BOX 281877            NaN     ATLANTA      GA   \n",
       "1               119 WEST 17TH STREET            NaN    NEW YORK      NY   \n",
       "3                        PO BOX 6107            NaN   HERMITAGE      PA   \n",
       "4                KNOLL C/O WORKSCAPE  PO BOX 277778     ATLANTA      GA   \n",
       "6                      PO BOX 100117            NaN     ATLANTA      GA   \n",
       "...                              ...            ...         ...     ...   \n",
       "5557               2310 RIDGE AVENUE            NaN    EVANSTON      IL   \n",
       "5558                 409 EMERSON AVE            NaN  PITTSBURGH      PA   \n",
       "5559  75 REMITTANCE DRIVE SUITE 6222            NaN     CHICAGO      IL   \n",
       "5560            2910 N VAN NESS BLVD            NaN      FRESNO      CA   \n",
       "5561           39349 TREASURY CENTER            NaN     CHICAGO      IL   \n",
       "\n",
       "      ZIP code_u Amount_u AmountDiff  \n",
       "0     30384-1877    18071          0  \n",
       "1          10011     4019          0  \n",
       "3     16148-0923     3284          0  \n",
       "4     30384-7778     2051          0  \n",
       "6          30384     5120          0  \n",
       "...          ...      ...        ...  \n",
       "5557       60201     8000          0  \n",
       "5558       15215     2000          0  \n",
       "5559  60675-6222     2868          0  \n",
       "5560       93704     1999          0  \n",
       "5561  60694-9300     2300          0  \n",
       "\n",
       "[5309 rows x 24 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_transactions[merged_transactions['AmountDiff'] == 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b09b0c6a-33cd-4bde-b916-d63e3084e7d4",
   "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>RC#_o</th>\n",
       "      <th>RC Name_o</th>\n",
       "      <th>Category_o</th>\n",
       "      <th>Vendor#_o</th>\n",
       "      <th>Vendor Name_o</th>\n",
       "      <th>Address 1_o</th>\n",
       "      <th>Address 2_o</th>\n",
       "      <th>City_o</th>\n",
       "      <th>State_o</th>\n",
       "      <th>ZIP code_o</th>\n",
       "      <th>...</th>\n",
       "      <th>Category_u</th>\n",
       "      <th>Vendor#_u</th>\n",
       "      <th>Vendor Name_u</th>\n",
       "      <th>Address 1_u</th>\n",
       "      <th>Address 2_u</th>\n",
       "      <th>City_u</th>\n",
       "      <th>State_u</th>\n",
       "      <th>ZIP code_u</th>\n",
       "      <th>Amount_u</th>\n",
       "      <th>AmountDiff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4768</th>\n",
       "      <td>85</td>\n",
       "      <td>SOMD Administration</td>\n",
       "      <td>7300</td>\n",
       "      <td>424204</td>\n",
       "      <td>BPA II LTD</td>\n",
       "      <td>1468 WEST 9TH STREET SUITE 835</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>OH</td>\n",
       "      <td>44113</td>\n",
       "      <td>...</td>\n",
       "      <td>7300</td>\n",
       "      <td>424204</td>\n",
       "      <td>BPA II LTD</td>\n",
       "      <td>1468 WEST 9TH STREET SUITE 835</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>OH</td>\n",
       "      <td>44113</td>\n",
       "      <td>10951439</td>\n",
       "      <td>2584599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5304</th>\n",
       "      <td>92</td>\n",
       "      <td>Business and Auxiliary Services</td>\n",
       "      <td>7300</td>\n",
       "      <td>505445</td>\n",
       "      <td>WYNDHAM PITTSBURGH UNIVERSITY CENTER</td>\n",
       "      <td>545 E JOHN CARPENTER FREEWAY STE 1300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IRVING</td>\n",
       "      <td>TX</td>\n",
       "      <td>75062</td>\n",
       "      <td>...</td>\n",
       "      <td>7300</td>\n",
       "      <td>505445</td>\n",
       "      <td>WYNDHAM PITTSBURGH UNIVERSITY CENTER</td>\n",
       "      <td>545 E JOHN CARPENTER FREEWAY STE 1300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IRVING</td>\n",
       "      <td>TX</td>\n",
       "      <td>75062</td>\n",
       "      <td>11657790</td>\n",
       "      <td>2023060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4414</th>\n",
       "      <td>83</td>\n",
       "      <td>General University</td>\n",
       "      <td>7700</td>\n",
       "      <td>204776</td>\n",
       "      <td>MARSH USA, INC</td>\n",
       "      <td>P O BOX 281915</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATLANTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30384</td>\n",
       "      <td>...</td>\n",
       "      <td>7700</td>\n",
       "      <td>204776</td>\n",
       "      <td>MARSH USA, INC</td>\n",
       "      <td>P O BOX 281915</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATLANTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30384</td>\n",
       "      <td>6071082</td>\n",
       "      <td>1735802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5310</th>\n",
       "      <td>92</td>\n",
       "      <td>Business and Auxiliary Services</td>\n",
       "      <td>7300</td>\n",
       "      <td>687995</td>\n",
       "      <td>MWK FORBES II LLC</td>\n",
       "      <td>3341 FORBES AVENUE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PITTSBURGH</td>\n",
       "      <td>PA</td>\n",
       "      <td>15213</td>\n",
       "      <td>...</td>\n",
       "      <td>7300</td>\n",
       "      <td>687995</td>\n",
       "      <td>MWK FORBES II LLC</td>\n",
       "      <td>3341 FORBES AVENUE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PITTSBURGH</td>\n",
       "      <td>PA</td>\n",
       "      <td>15213</td>\n",
       "      <td>6280388</td>\n",
       "      <td>1112046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3799</th>\n",
       "      <td>67</td>\n",
       "      <td>Facilities Management</td>\n",
       "      <td>7000</td>\n",
       "      <td>103658</td>\n",
       "      <td>DUQUESNE LIGHT COMPANY</td>\n",
       "      <td>PO BOX 830012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BALTIMORE</td>\n",
       "      <td>MD</td>\n",
       "      <td>21283-0012</td>\n",
       "      <td>...</td>\n",
       "      <td>7000</td>\n",
       "      <td>103658</td>\n",
       "      <td>DUQUESNE LIGHT COMPANY</td>\n",
       "      <td>PO BOX 830012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BALTIMORE</td>\n",
       "      <td>MD</td>\n",
       "      <td>21283-0012</td>\n",
       "      <td>24578216</td>\n",
       "      <td>747194</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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>2177</th>\n",
       "      <td>41</td>\n",
       "      <td>Johnstown Campus</td>\n",
       "      <td>6000</td>\n",
       "      <td>248635</td>\n",
       "      <td>GRAINGER</td>\n",
       "      <td>3750 BROOKSIDE PKWY</td>\n",
       "      <td>STE 260</td>\n",
       "      <td>ALPHARETTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30022-1433</td>\n",
       "      <td>...</td>\n",
       "      <td>6000</td>\n",
       "      <td>248635</td>\n",
       "      <td>GRAINGER</td>\n",
       "      <td>3750 BROOKSIDE PKWY</td>\n",
       "      <td>STE 260</td>\n",
       "      <td>ALPHARETTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30022-1433</td>\n",
       "      <td>125297</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4621</th>\n",
       "      <td>85</td>\n",
       "      <td>SOMD Administration</td>\n",
       "      <td>6600</td>\n",
       "      <td>534443</td>\n",
       "      <td>SPOK, INC</td>\n",
       "      <td>5911 KINGSTOWNE VILLAGE PARKWAY</td>\n",
       "      <td>6TH FLOOR</td>\n",
       "      <td>ALEXANDRIA</td>\n",
       "      <td>VA</td>\n",
       "      <td>22315</td>\n",
       "      <td>...</td>\n",
       "      <td>6600</td>\n",
       "      <td>534443</td>\n",
       "      <td>SPOK, INC</td>\n",
       "      <td>5911 KINGSTOWNE VILLAGE PARKWAY</td>\n",
       "      <td>6TH FLOOR</td>\n",
       "      <td>ALEXANDRIA</td>\n",
       "      <td>VA</td>\n",
       "      <td>22315</td>\n",
       "      <td>8301</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>Chancellor</td>\n",
       "      <td>6000</td>\n",
       "      <td>185133</td>\n",
       "      <td>CDW GOVERNMENT INC</td>\n",
       "      <td>75 REMITTANCE DR</td>\n",
       "      <td>SUITE 1515</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>IL</td>\n",
       "      <td>60675-1515</td>\n",
       "      <td>...</td>\n",
       "      <td>6000</td>\n",
       "      <td>185133</td>\n",
       "      <td>CDW GOVERNMENT INC</td>\n",
       "      <td>75 REMITTANCE DR</td>\n",
       "      <td>SUITE 1515</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>IL</td>\n",
       "      <td>60675-1515</td>\n",
       "      <td>7547</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>5</td>\n",
       "      <td>Student Affairs</td>\n",
       "      <td>6000</td>\n",
       "      <td>100877</td>\n",
       "      <td>APPLE COMPUTER INC</td>\n",
       "      <td>PO BOX 281877</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATLANTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30384-1877</td>\n",
       "      <td>...</td>\n",
       "      <td>6000</td>\n",
       "      <td>100877</td>\n",
       "      <td>APPLE COMPUTER INC</td>\n",
       "      <td>PO BOX 281877</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATLANTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30384-1877</td>\n",
       "      <td>18835</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3591</th>\n",
       "      <td>67</td>\n",
       "      <td>Facilities Management</td>\n",
       "      <td>6000</td>\n",
       "      <td>107557</td>\n",
       "      <td>MICROBAC LABORATORIES INC</td>\n",
       "      <td>4750 NAUTILUS CT SOUTH UNIT A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BOULDER</td>\n",
       "      <td>CO</td>\n",
       "      <td>80301</td>\n",
       "      <td>...</td>\n",
       "      <td>6000</td>\n",
       "      <td>107557</td>\n",
       "      <td>MICROBAC LABORATORIES INC</td>\n",
       "      <td>4750 NAUTILUS CT SOUTH UNIT A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BOULDER</td>\n",
       "      <td>CO</td>\n",
       "      <td>80301</td>\n",
       "      <td>1233</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>239 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      RC#_o                        RC Name_o  Category_o  Vendor#_o  \\\n",
       "4768     85              SOMD Administration        7300     424204   \n",
       "5304     92  Business and Auxiliary Services        7300     505445   \n",
       "4414     83               General University        7700     204776   \n",
       "5310     92  Business and Auxiliary Services        7300     687995   \n",
       "3799     67            Facilities Management        7000     103658   \n",
       "...     ...                              ...         ...        ...   \n",
       "2177     41                 Johnstown Campus        6000     248635   \n",
       "4621     85              SOMD Administration        6600     534443   \n",
       "5         1                       Chancellor        6000     185133   \n",
       "214       5                  Student Affairs        6000     100877   \n",
       "3591     67            Facilities Management        6000     107557   \n",
       "\n",
       "                             Vendor Name_o  \\\n",
       "4768                            BPA II LTD   \n",
       "5304  WYNDHAM PITTSBURGH UNIVERSITY CENTER   \n",
       "4414                        MARSH USA, INC   \n",
       "5310                     MWK FORBES II LLC   \n",
       "3799                DUQUESNE LIGHT COMPANY   \n",
       "...                                    ...   \n",
       "2177                              GRAINGER   \n",
       "4621                             SPOK, INC   \n",
       "5                       CDW GOVERNMENT INC   \n",
       "214                     APPLE COMPUTER INC   \n",
       "3591             MICROBAC LABORATORIES INC   \n",
       "\n",
       "                                Address 1_o Address 2_o      City_o State_o  \\\n",
       "4768         1468 WEST 9TH STREET SUITE 835         NaN   CLEVELAND      OH   \n",
       "5304  545 E JOHN CARPENTER FREEWAY STE 1300         NaN      IRVING      TX   \n",
       "4414                         P O BOX 281915         NaN     ATLANTA      GA   \n",
       "5310                     3341 FORBES AVENUE         NaN  PITTSBURGH      PA   \n",
       "3799                          PO BOX 830012         NaN   BALTIMORE      MD   \n",
       "...                                     ...         ...         ...     ...   \n",
       "2177                    3750 BROOKSIDE PKWY     STE 260  ALPHARETTA      GA   \n",
       "4621        5911 KINGSTOWNE VILLAGE PARKWAY   6TH FLOOR  ALEXANDRIA      VA   \n",
       "5                          75 REMITTANCE DR  SUITE 1515     CHICAGO      IL   \n",
       "214                           PO BOX 281877         NaN     ATLANTA      GA   \n",
       "3591          4750 NAUTILUS CT SOUTH UNIT A         NaN     BOULDER      CO   \n",
       "\n",
       "      ZIP code_o  ...  Category_u Vendor#_u  \\\n",
       "4768       44113  ...        7300    424204   \n",
       "5304       75062  ...        7300    505445   \n",
       "4414       30384  ...        7700    204776   \n",
       "5310       15213  ...        7300    687995   \n",
       "3799  21283-0012  ...        7000    103658   \n",
       "...          ...  ...         ...       ...   \n",
       "2177  30022-1433  ...        6000    248635   \n",
       "4621       22315  ...        6600    534443   \n",
       "5     60675-1515  ...        6000    185133   \n",
       "214   30384-1877  ...        6000    100877   \n",
       "3591       80301  ...        6000    107557   \n",
       "\n",
       "                             Vendor Name_u  \\\n",
       "4768                            BPA II LTD   \n",
       "5304  WYNDHAM PITTSBURGH UNIVERSITY CENTER   \n",
       "4414                        MARSH USA, INC   \n",
       "5310                     MWK FORBES II LLC   \n",
       "3799                DUQUESNE LIGHT COMPANY   \n",
       "...                                    ...   \n",
       "2177                              GRAINGER   \n",
       "4621                             SPOK, INC   \n",
       "5                       CDW GOVERNMENT INC   \n",
       "214                     APPLE COMPUTER INC   \n",
       "3591             MICROBAC LABORATORIES INC   \n",
       "\n",
       "                                Address 1_u  Address 2_u      City_u State_u  \\\n",
       "4768         1468 WEST 9TH STREET SUITE 835          NaN   CLEVELAND      OH   \n",
       "5304  545 E JOHN CARPENTER FREEWAY STE 1300          NaN      IRVING      TX   \n",
       "4414                         P O BOX 281915          NaN     ATLANTA      GA   \n",
       "5310                     3341 FORBES AVENUE          NaN  PITTSBURGH      PA   \n",
       "3799                          PO BOX 830012          NaN   BALTIMORE      MD   \n",
       "...                                     ...          ...         ...     ...   \n",
       "2177                    3750 BROOKSIDE PKWY      STE 260  ALPHARETTA      GA   \n",
       "4621        5911 KINGSTOWNE VILLAGE PARKWAY    6TH FLOOR  ALEXANDRIA      VA   \n",
       "5                          75 REMITTANCE DR   SUITE 1515     CHICAGO      IL   \n",
       "214                           PO BOX 281877          NaN     ATLANTA      GA   \n",
       "3591          4750 NAUTILUS CT SOUTH UNIT A          NaN     BOULDER      CO   \n",
       "\n",
       "      ZIP code_u  Amount_u AmountDiff  \n",
       "4768       44113  10951439    2584599  \n",
       "5304       75062  11657790    2023060  \n",
       "4414       30384   6071082    1735802  \n",
       "5310       15213   6280388    1112046  \n",
       "3799  21283-0012  24578216     747194  \n",
       "...          ...       ...        ...  \n",
       "2177  30022-1433    125297         53  \n",
       "4621       22315      8301         53  \n",
       "5     60675-1515      7547         47  \n",
       "214   30384-1877     18835         38  \n",
       "3591       80301      1233         19  \n",
       "\n",
       "[239 rows x 24 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_transactions[merged_transactions['AmountDiff'] > 0].sort_values(by=['AmountDiff'], ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0fd3f0a-7f0b-44e4-a3b4-8f80f07cc862",
   "metadata": {},
   "source": [
    "# Largest amounts - only appearing in updated"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d5f1db8b-dec5-4705-8a71-b7e727dcbd4d",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_transactions = original.merge(updated.drop_duplicates(), on=TRANSACTION_INDEX, how='right', suffixes=('_o', '_u'), indicator=True)\n",
    "new_transactions.fillna(value={\"Amount_o\": 0}, inplace=True)\n",
    "new_transactions['AmountDiff'] = new_transactions.Amount_u - new_transactions.Amount_o"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "774d1c8a-b61b-4a25-9f5d-ee939f62f8ac",
   "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>RC#_u</th>\n",
       "      <th>RC Name_u</th>\n",
       "      <th>Category_u</th>\n",
       "      <th>Vendor#_u</th>\n",
       "      <th>Vendor Name_u</th>\n",
       "      <th>Address 1_u</th>\n",
       "      <th>Address 2_u</th>\n",
       "      <th>City_u</th>\n",
       "      <th>State_u</th>\n",
       "      <th>ZIP code_u</th>\n",
       "      <th>Amount_u</th>\n",
       "      <th>TPN_ID</th>\n",
       "      <th>_merge</th>\n",
       "      <th>AmountDiff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>469</th>\n",
       "      <td>6</td>\n",
       "      <td>Kenneth P. Dietrich School of Arts and Sciences</td>\n",
       "      <td>6400</td>\n",
       "      <td>104257</td>\n",
       "      <td>FISHER SCIENTIFIC COMPANY LLC</td>\n",
       "      <td>PO BOX 406538</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATLANTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30384-6538</td>\n",
       "      <td>2344352</td>\n",
       "      <td>6-6400-104257</td>\n",
       "      <td>right_only</td>\n",
       "      <td>2344352.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5861</th>\n",
       "      <td>93</td>\n",
       "      <td>Public Safety and Emergency Management</td>\n",
       "      <td>6400</td>\n",
       "      <td>446194</td>\n",
       "      <td>LANDMARK EVENT STAFFING SERVICES, INC</td>\n",
       "      <td>4131 HARBOR WALK DRIVE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FORT COLLINS</td>\n",
       "      <td>CO</td>\n",
       "      <td>80525</td>\n",
       "      <td>1689115</td>\n",
       "      <td>93-6400-446194</td>\n",
       "      <td>right_only</td>\n",
       "      <td>1689115.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1315</th>\n",
       "      <td>30</td>\n",
       "      <td>SVC Health Sciences</td>\n",
       "      <td>6400</td>\n",
       "      <td>114200</td>\n",
       "      <td>QUEST DIAGNOSTICS</td>\n",
       "      <td>P.O. BOX 740709</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATLANTA</td>\n",
       "      <td>GA</td>\n",
       "      <td>30374-0709</td>\n",
       "      <td>1237840</td>\n",
       "      <td>30-6400-114200</td>\n",
       "      <td>right_only</td>\n",
       "      <td>1237840.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>787</th>\n",
       "      <td>10</td>\n",
       "      <td>SVC and Provost</td>\n",
       "      <td>6400</td>\n",
       "      <td>689608</td>\n",
       "      <td>PFL</td>\n",
       "      <td>100 PFL WAY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LIVINGSTON</td>\n",
       "      <td>MT</td>\n",
       "      <td>59047</td>\n",
       "      <td>790510</td>\n",
       "      <td>10-6400-689608</td>\n",
       "      <td>right_only</td>\n",
       "      <td>790510.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5063</th>\n",
       "      <td>87</td>\n",
       "      <td>SVC and Chief Financial Officer</td>\n",
       "      <td>6400</td>\n",
       "      <td>521322</td>\n",
       "      <td>SUPRA OFFICE SOLUTIONS INC</td>\n",
       "      <td>PO BOX 201</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BALA CYNWYD</td>\n",
       "      <td>PA</td>\n",
       "      <td>19004</td>\n",
       "      <td>600681</td>\n",
       "      <td>87-6400-521322</td>\n",
       "      <td>right_only</td>\n",
       "      <td>600681.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      RC#_u                                        RC Name_u  Category_u  \\\n",
       "469       6  Kenneth P. Dietrich School of Arts and Sciences        6400   \n",
       "5861     93           Public Safety and Emergency Management        6400   \n",
       "1315     30                              SVC Health Sciences        6400   \n",
       "787      10                                  SVC and Provost        6400   \n",
       "5063     87                  SVC and Chief Financial Officer        6400   \n",
       "\n",
       "      Vendor#_u                          Vendor Name_u  \\\n",
       "469      104257          FISHER SCIENTIFIC COMPANY LLC   \n",
       "5861     446194  LANDMARK EVENT STAFFING SERVICES, INC   \n",
       "1315     114200                      QUEST DIAGNOSTICS   \n",
       "787      689608                                    PFL   \n",
       "5063     521322             SUPRA OFFICE SOLUTIONS INC   \n",
       "\n",
       "                 Address 1_u Address 2_u        City_u State_u  ZIP code_u  \\\n",
       "469            PO BOX 406538         NaN       ATLANTA      GA  30384-6538   \n",
       "5861  4131 HARBOR WALK DRIVE         NaN  FORT COLLINS      CO       80525   \n",
       "1315         P.O. BOX 740709         NaN       ATLANTA      GA  30374-0709   \n",
       "787              100 PFL WAY         NaN    LIVINGSTON      MT       59047   \n",
       "5063              PO BOX 201         NaN   BALA CYNWYD      PA       19004   \n",
       "\n",
       "      Amount_u          TPN_ID      _merge  AmountDiff  \n",
       "469    2344352   6-6400-104257  right_only   2344352.0  \n",
       "5861   1689115  93-6400-446194  right_only   1689115.0  \n",
       "1315   1237840  30-6400-114200  right_only   1237840.0  \n",
       "787     790510  10-6400-689608  right_only    790510.0  \n",
       "5063    600681  87-6400-521322  right_only    600681.0  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display_columns = ['RC#_u', 'RC Name_u', 'Category_u', 'Vendor#_u', 'Vendor Name_u', 'Address 1_u', 'Address 2_u', 'City_u', 'State_u', 'ZIP code_u', 'Amount_u', 'TPN_ID', '_merge', 'AmountDiff']\n",
    "new_transactions[new_transactions._merge == \"right_only\"].sort_values(by=['AmountDiff'], ascending=False).head(5)[display_columns]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ecf27bf8-0d33-4884-933e-b73fcc0269d4",
   "metadata": {},
   "source": [
    "# Save transaction change data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "21766ffd-4b52-4965-8237-2467737b649c",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_transactions[merged_transactions.AmountDiff != 0].to_csv(\"./data/updated_changes.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "5f723f9c-a2e5-4441-becd-b0c977679570",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_transactions[new_transactions._merge == \"right_only\"].to_csv(\"./data/updated_new.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}