03_format_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",
    "CATEGORY_MAP = {\n",
    "    6000: 'Supplies/Equipment',\n",
    "    6400: 'Professional Services',\n",
    "    6500: 'Library Acquisitions',\n",
    "    6600: 'Telephone',\n",
    "    6700: 'Mail',\n",
    "    6800: 'Printing/Publishing',\n",
    "    6900: 'Professional Memberships',\n",
    "    7000: 'Utilities',\n",
    "    7100: 'Routine Maintenance',\n",
    "    7135: 'Facility Operating Costs',\n",
    "    7140: 'Facility Alterations',\n",
    "    7300: 'Space Rental',\n",
    "    7400: 'Purchases for Resale',\n",
    "    7700: 'Insurance'\n",
    "}\n",
    "COLUMNS_CHANGED = [\"RC Name_u\", \"Vendor Name_u\", \"Category_u\", \"Amount_o\", \"Amount_u\", \"AmountDiff\"]\n",
    "COLUMNS_NEW = [\"RC Name_u\", \"Vendor Name_u\", \"Category_u\", \"Amount_u\"]\n",
    "RENAMED_COLUMNS = {\"RC Name_u\": \"RC Name\", \"Vendor Name_u\": \"Vendor Name\", \"Category_u\": \"Category\", \"Amount_o\": \"Old Amount\", \"Amount_u\": \"New Amount\", \"AmountDiff\": \"Difference\"}"
   ]
  },
  {
   "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": [
    "transactions_changed = pd.read_csv(\"./data/updated_changes.csv\")\n",
    "transactions_new = pd.read_csv(\"./data/updated_new.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: 253 entries, 0 to 252\n",
      "Data columns (total 24 columns):\n",
      " #   Column         Non-Null Count  Dtype \n",
      "---  ------         --------------  ----- \n",
      " 0   RC#_o          253 non-null    int64 \n",
      " 1   RC Name_o      253 non-null    object\n",
      " 2   Category_o     253 non-null    int64 \n",
      " 3   Vendor#_o      253 non-null    int64 \n",
      " 4   Vendor Name_o  253 non-null    object\n",
      " 5   Address 1_o    253 non-null    object\n",
      " 6   Address 2_o    66 non-null     object\n",
      " 7   City_o         253 non-null    object\n",
      " 8   State_o        249 non-null    object\n",
      " 9   ZIP code_o     251 non-null    object\n",
      " 10  Amount_o       253 non-null    int64 \n",
      " 11  TPN_ID         253 non-null    object\n",
      " 12  RC#_u          253 non-null    int64 \n",
      " 13  RC Name_u      253 non-null    object\n",
      " 14  Category_u     253 non-null    int64 \n",
      " 15  Vendor#_u      253 non-null    int64 \n",
      " 16  Vendor Name_u  253 non-null    object\n",
      " 17  Address 1_u    253 non-null    object\n",
      " 18  Address 2_u    66 non-null     object\n",
      " 19  City_u         253 non-null    object\n",
      " 20  State_u        248 non-null    object\n",
      " 21  ZIP code_u     251 non-null    object\n",
      " 22  Amount_u       253 non-null    int64 \n",
      " 23  AmountDiff     253 non-null    int64 \n",
      "dtypes: int64(9), object(15)\n",
      "memory usage: 47.6+ KB\n"
     ]
    }
   ],
   "source": [
    "transactions_changed.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c9ec4037-048a-450e-aed9-2cb7e63f4662",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 358 entries, 0 to 357\n",
      "Data columns (total 25 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   RC#_o          0 non-null      float64\n",
      " 1   RC Name_o      0 non-null      float64\n",
      " 2   Category_o     0 non-null      float64\n",
      " 3   Vendor#_o      0 non-null      float64\n",
      " 4   Vendor Name_o  0 non-null      float64\n",
      " 5   Address 1_o    0 non-null      float64\n",
      " 6   Address 2_o    0 non-null      float64\n",
      " 7   City_o         0 non-null      float64\n",
      " 8   State_o        0 non-null      float64\n",
      " 9   ZIP code_o     0 non-null      float64\n",
      " 10  Amount_o       358 non-null    float64\n",
      " 11  TPN_ID         358 non-null    object \n",
      " 12  RC#_u          358 non-null    int64  \n",
      " 13  RC Name_u      358 non-null    object \n",
      " 14  Category_u     358 non-null    int64  \n",
      " 15  Vendor#_u      358 non-null    int64  \n",
      " 16  Vendor Name_u  358 non-null    object \n",
      " 17  Address 1_u    358 non-null    object \n",
      " 18  Address 2_u    66 non-null     object \n",
      " 19  City_u         358 non-null    object \n",
      " 20  State_u        357 non-null    object \n",
      " 21  ZIP code_u     357 non-null    object \n",
      " 22  Amount_u       358 non-null    int64  \n",
      " 23  _merge         358 non-null    object \n",
      " 24  AmountDiff     358 non-null    float64\n",
      "dtypes: float64(12), int64(4), object(9)\n",
      "memory usage: 70.0+ KB\n"
     ]
    }
   ],
   "source": [
    "transactions_new.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03428688-37c7-430a-ad52-14b93fed23a0",
   "metadata": {},
   "source": [
    "# Format changed transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1d1287c0-2367-435a-82a7-ab8f32f4dbf8",
   "metadata": {},
   "outputs": [],
   "source": [
    "transactions_changed = transactions_changed[COLUMNS_CHANGED].rename(columns=RENAMED_COLUMNS)\n",
    "transactions_changed['Category'] = transactions_changed['Category'].apply(lambda c: CATEGORY_MAP[c])\n",
    "\n",
    "transactions_new = transactions_new[COLUMNS_NEW].rename(columns=RENAMED_COLUMNS)\n",
    "transactions_new['Category'] = transactions_new['Category'].apply(lambda c: CATEGORY_MAP[c])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e928f56-a417-4cf0-a4a8-0e79d736ae70",
   "metadata": {},
   "source": [
    "# Save data files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "17014194-7f5f-4d8c-8526-8bef082e9aaa",
   "metadata": {},
   "outputs": [],
   "source": [
    "transactions_changed.to_csv(\"./data/updated_changes_dw.csv\", index=False)\n",
    "transactions_new.to_csv(\"./data/updated_new_dw.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
}