exploratory.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exploratory Data Analysis for Florida Employee Salary data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "\n",
    "from utils.format_table import format_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "root = Path.cwd()\n",
    "archive_path = root / \"archive\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import data\n",
    "Eventually we'll have a whole database. For now I just have two consecutive weekly data drops. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "fl_salaries_2021-01-18.csv\n",
      "108,130 rows\n",
      "fl_salaries_2021-01-25.csv\n",
      "92,400 rows\n",
      "fl_salaries_2020-10-12.csv\n",
      "108,916 rows\n",
      "fl_salaries_2021-02-01.csv\n",
      "107,999 rows\n",
      "fl_salaries_2021-02-08.csv\n",
      "107,941 rows\n"
     ]
    }
   ],
   "source": [
    "for csv_path in archive_path.glob(\"*.csv\"):\n",
    "    print(csv_path.name)\n",
    "    print(f\"{len(pd.read_csv(csv_path)):,} rows\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Apply `format_table()` and example a sample of rows\n",
    "Looks like there is no employee ID column here. Too bad; that would have made things easier. \n",
    "\n",
    "Let's assume for now that if we take the first, last, and middle names, plus the date of hire, that's enough to unique identify the same employee across two of these data drops.\n",
    "\n",
    "The function `format_table()` puts the data into mostly analysis-ready form."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "cleaning the report\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 108130 entries, 0 to 108129\n",
      "Data columns (total 13 columns):\n",
      "agency_name        108130 non-null object\n",
      "budget_entity      108130 non-null object\n",
      "position_number    108130 non-null object\n",
      "last_name          108128 non-null object\n",
      "first_name         108129 non-null object\n",
      "middle_name        86850 non-null object\n",
      "is_salaried        108130 non-null bool\n",
      "is_full_time       108130 non-null bool\n",
      "class_code         93747 non-null object\n",
      "class_title        93747 non-null object\n",
      "state_hire_date    93750 non-null object\n",
      "salary             93747 non-null Int64\n",
      "ops_hourly_rate    93747 non-null Int64\n",
      "dtypes: Int64(2), bool(2), object(9)\n",
      "memory usage: 9.5+ MB\n"
     ]
    }
   ],
   "source": [
    "archive_path = Path.cwd() / \"archive\" / \"fl_salaries_2021-01-18.csv\"\n",
    "first_raw = pd.read_csv(archive_path, dtype=\"object\")\n",
    "_, message, first = format_table(first_raw)\n",
    "\n",
    "first.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>agency_name</th>\n",
       "      <th>budget_entity</th>\n",
       "      <th>class_code</th>\n",
       "      <th>is_salaried</th>\n",
       "      <th>is_full_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16617</th>\n",
       "      <td>DEPARTMENT OF CORRECTIONS</td>\n",
       "      <td>SPECIALTY INST OPERATIONS</td>\n",
       "      <td>08005</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39926</th>\n",
       "      <td>DEPARTMENT OF HEALTH</td>\n",
       "      <td>CTY HLTH LOC HLTH NEED</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59845</th>\n",
       "      <td>DEPT ENVIRONMENTAL PROTECTION</td>\n",
       "      <td>WASTE MANAGEMENT</td>\n",
       "      <td>04806</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         agency_name              budget_entity class_code  \\\n",
       "16617      DEPARTMENT OF CORRECTIONS  SPECIALTY INST OPERATIONS      08005   \n",
       "39926           DEPARTMENT OF HEALTH     CTY HLTH LOC HLTH NEED        NaN   \n",
       "59845  DEPT ENVIRONMENTAL PROTECTION           WASTE MANAGEMENT      04806   \n",
       "\n",
       "       is_salaried  is_full_time  \n",
       "16617         True          True  \n",
       "39926        False          True  \n",
       "59845         True          True  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first[\n",
    "    [\n",
    "        \"agency_name\",\n",
    "        \"budget_entity\",\n",
    "        \"position_number\",\n",
    "        \"class_code\",\n",
    "        \"is_salaried\",\n",
    "        \"is_full_time\",\n",
    "    ]\n",
    "].sample(3, random_state=444)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add the date of download to each table\n",
    "Where things get interesting is looking at how salaries and positions change over time. But the input data doesn't come to us with the timestamp of its own publication. That's stored in the filename. We'll want to add that to the data.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged = first.merge(\n",
    "    second[[\"Last Name\", \"First Name\", \"Middle Name\", \"State Hire Date\", \"Salary\", \"Class Title\"]],\n",
    "    how=\"inner\",\n",
    "    on=[\"Last Name\", \"First Name\", \"Middle Name\", \"State Hire Date\"],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Find all the rows where the salary changed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cond_1 = merged['Salary_x'] != merged[\"Salary_y\"]\n",
    "cond_2 = ~merged['Salary_x'].isna()\n",
    "\n",
    "changed_salaries = merged[cond_1 & cond_2]\n",
    "print(len(changed_salaries))\n",
    "changed_salaries.sample(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "ERROR:root:Internal Python error in the inspect module.\n",
      "Below is the traceback from this internal error.\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Traceback (most recent call last):\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py\", line 2963, in run_code\n",
      "    exec(code_obj, self.user_global_ns, self.user_ns)\n",
      "  File \"<ipython-input-9-6235527da291>\", line 1, in <module>\n",
      "    from faker import Faker\n",
      "ModuleNotFoundError: No module named 'faker'\n",
      "\n",
      "During handling of the above exception, another exception occurred:\n",
      "\n",
      "Traceback (most recent call last):\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py\", line 1863, in showtraceback\n",
      "    stb = value._render_traceback_()\n",
      "AttributeError: 'ModuleNotFoundError' object has no attribute '_render_traceback_'\n",
      "\n",
      "During handling of the above exception, another exception occurred:\n",
      "\n",
      "Traceback (most recent call last):\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/IPython/core/ultratb.py\", line 1095, in get_records\n",
      "    return _fixed_getinnerframes(etb, number_of_lines_of_context, tb_offset)\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/IPython/core/ultratb.py\", line 311, in wrapped\n",
      "    return f(*args, **kwargs)\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/IPython/core/ultratb.py\", line 345, in _fixed_getinnerframes\n",
      "    records = fix_frame_records_filenames(inspect.getinnerframes(etb, context))\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/inspect.py\", line 1490, in getinnerframes\n",
      "    frameinfo = (tb.tb_frame,) + getframeinfo(tb, context)\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/inspect.py\", line 1448, in getframeinfo\n",
      "    filename = getsourcefile(frame) or getfile(frame)\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/inspect.py\", line 696, in getsourcefile\n",
      "    if getattr(getmodule(object, filename), '__loader__', None) is not None:\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/inspect.py\", line 733, in getmodule\n",
      "    if ismodule(module) and hasattr(module, '__file__'):\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/py/_vendored_packages/apipkg.py\", line 195, in __getattribute__\n",
      "    return getattr(getmod(), name)\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/py/_vendored_packages/apipkg.py\", line 179, in getmod\n",
      "    x = importobj(modpath, None)\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/py/_vendored_packages/apipkg.py\", line 69, in importobj\n",
      "    module = __import__(modpath, None, None, ['__doc__'])\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/pytest.py\", line 13, in <module>\n",
      "    from _pytest.fixtures import fixture, yield_fixture\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/_pytest/fixtures.py\", line 842, in <module>\n",
      "    class FixtureFunctionMarker(object):\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/_pytest/fixtures.py\", line 844, in FixtureFunctionMarker\n",
      "    params = attr.ib(convert=attr.converters.optional(tuple))\n",
      "TypeError: attrib() got an unexpected keyword argument 'convert'\n"
     ]
    },
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'faker'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m"
     ]
    }
   ],
   "source": [
    "from faker import Faker\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cond_3 = merged['Class Title_x'] != merged[\"Class Title_y\"]\n",
    "cond_4 = ~merged['Class Title_x'].isna()\n",
    "changed_title = merged[cond_3 & cond_4]\n",
    "print(len(changed_title))\n",
    "changed_title.sample(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "changed_salaries['Agency Name'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "first['Agency Name'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'archive_path' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-2-bf2bd46230ea>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0msample_path\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0marchive_path\u001b[0m \u001b[0;34m/\u001b[0m \u001b[0;34m'fl_salaries_2021-01-18.csv'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m \u001b[0msample_df\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_csv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msample_path\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msort_values\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'First Name'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'archive_path' is not defined"
     ]
    }
   ],
   "source": [
    "sample_path = archive_path / 'fl_salaries_2021-01-18.csv'\n",
    "sample_df = pd.read_csv(sample_path).sort_values('First Name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Agency Name</th>\n",
       "      <th>Budget Entity</th>\n",
       "      <th>Position Number</th>\n",
       "      <th>Last Name</th>\n",
       "      <th>First Name</th>\n",
       "      <th>Middle Name</th>\n",
       "      <th>Employee Type</th>\n",
       "      <th>Full/Part Time</th>\n",
       "      <th>Class Code</th>\n",
       "      <th>Class Title</th>\n",
       "      <th>State Hire Date</th>\n",
       "      <th>Salary</th>\n",
       "      <th>OPS Hourly Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6944</th>\n",
       "      <td>Agriculture and Consumer Svcs</td>\n",
       "      <td>PLANT/PEST/DISEASE CONTROL</td>\n",
       "      <td>1859</td>\n",
       "      <td>WILSON</td>\n",
       "      <td>A</td>\n",
       "      <td>J</td>\n",
       "      <td>Salaried</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>7521.0</td>\n",
       "      <td>AGRICULTURAL TECHNICIAN III</td>\n",
       "      <td>2019-08-16</td>\n",
       "      <td>$     2,4686.48</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107478</th>\n",
       "      <td>State Courts System</td>\n",
       "      <td>PGM: CT OPER/CIRCUIT CTS</td>\n",
       "      <td>90705</td>\n",
       "      <td>SOUD JR</td>\n",
       "      <td>A</td>\n",
       "      <td>C</td>\n",
       "      <td>OPS</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$        375.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80861</th>\n",
       "      <td>FL Dept of Law Enforcement</td>\n",
       "      <td>INVESTIGATIVE SERVICES</td>\n",
       "      <td>255</td>\n",
       "      <td>PELLHAM</td>\n",
       "      <td>A</td>\n",
       "      <td>MARK</td>\n",
       "      <td>Salaried</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>8581.0</td>\n",
       "      <td>SPECIAL AGENT</td>\n",
       "      <td>1977-04-18</td>\n",
       "      <td>$    12,1380.74</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Agency Name               Budget Entity  \\\n",
       "6944    Agriculture and Consumer Svcs  PLANT/PEST/DISEASE CONTROL   \n",
       "107478            State Courts System    PGM: CT OPER/CIRCUIT CTS   \n",
       "80861      FL Dept of Law Enforcement      INVESTIGATIVE SERVICES   \n",
       "\n",
       "        Position Number Last Name First Name Middle Name Employee Type  \\\n",
       "6944               1859    WILSON          A           J      Salaried   \n",
       "107478            90705   SOUD JR          A           C           OPS   \n",
       "80861               255   PELLHAM          A        MARK      Salaried   \n",
       "\n",
       "       Full/Part Time  Class Code                  Class Title  \\\n",
       "6944        Full Time      7521.0  AGRICULTURAL TECHNICIAN III   \n",
       "107478      Full Time         NaN                          NaN   \n",
       "80861       Full Time      8581.0                SPECIAL AGENT   \n",
       "\n",
       "       State Hire Date           Salary  OPS Hourly Rate  \n",
       "6944        2019-08-16  $     2,4686.48              NaN  \n",
       "107478             NaN              NaN  $        375.00  \n",
       "80861       1977-04-18  $    12,1380.74              NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting faker\n",
      "  Using cached Faker-6.1.1-py3-none-any.whl (1.1 MB)\n",
      "Requirement already satisfied: python-dateutil>=2.4 in /Users/james/anaconda3/lib/python3.6/site-packages (from faker) (2.7.3)\n",
      "Requirement already satisfied: six>=1.5 in /Users/james/anaconda3/lib/python3.6/site-packages (from python-dateutil>=2.4->faker) (1.11.0)\n",
      "Collecting text-unidecode==1.3\n",
      "  Using cached text_unidecode-1.3-py2.py3-none-any.whl (78 kB)\n",
      "Installing collected packages: text-unidecode, faker\n",
      "Successfully installed faker-6.1.1 text-unidecode-1.3\n",
      "\u001b[33mWARNING: You are using pip version 20.3.1; however, version 21.0.1 is available.\n",
      "You should consider upgrading via the '/Users/james/anaconda3/bin/python -m pip install --upgrade pip' command.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "import sys\n",
    "!{sys.executable} -m pip install faker"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "from faker import Faker"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "ERROR:root:Internal Python error in the inspect module.\n",
      "Below is the traceback from this internal error.\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Traceback (most recent call last):\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py\", line 2963, in run_code\n",
      "    exec(code_obj, self.user_global_ns, self.user_ns)\n",
      "  File \"<ipython-input-6-c381ca846684>\", line 13, in <module>\n",
      "    anon_df = anonymize(sample_df).head(20)\n",
      "  File \"<ipython-input-6-c381ca846684>\", line 4, in anonymize\n",
      "    faker  = Faker()\n",
      "NameError: name 'Faker' is not defined\n",
      "\n",
      "During handling of the above exception, another exception occurred:\n",
      "\n",
      "Traceback (most recent call last):\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py\", line 1863, in showtraceback\n",
      "    stb = value._render_traceback_()\n",
      "AttributeError: 'NameError' object has no attribute '_render_traceback_'\n",
      "\n",
      "During handling of the above exception, another exception occurred:\n",
      "\n",
      "Traceback (most recent call last):\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/IPython/core/ultratb.py\", line 1095, in get_records\n",
      "    return _fixed_getinnerframes(etb, number_of_lines_of_context, tb_offset)\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/IPython/core/ultratb.py\", line 311, in wrapped\n",
      "    return f(*args, **kwargs)\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/IPython/core/ultratb.py\", line 345, in _fixed_getinnerframes\n",
      "    records = fix_frame_records_filenames(inspect.getinnerframes(etb, context))\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/inspect.py\", line 1490, in getinnerframes\n",
      "    frameinfo = (tb.tb_frame,) + getframeinfo(tb, context)\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/inspect.py\", line 1448, in getframeinfo\n",
      "    filename = getsourcefile(frame) or getfile(frame)\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/inspect.py\", line 696, in getsourcefile\n",
      "    if getattr(getmodule(object, filename), '__loader__', None) is not None:\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/inspect.py\", line 733, in getmodule\n",
      "    if ismodule(module) and hasattr(module, '__file__'):\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/py/_vendored_packages/apipkg.py\", line 195, in __getattribute__\n",
      "    return getattr(getmod(), name)\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/py/_vendored_packages/apipkg.py\", line 179, in getmod\n",
      "    x = importobj(modpath, None)\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/py/_vendored_packages/apipkg.py\", line 69, in importobj\n",
      "    module = __import__(modpath, None, None, ['__doc__'])\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/pytest.py\", line 13, in <module>\n",
      "    from _pytest.fixtures import fixture, yield_fixture\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/_pytest/fixtures.py\", line 842, in <module>\n",
      "    class FixtureFunctionMarker(object):\n",
      "  File \"/Users/james/anaconda3/lib/python3.6/site-packages/_pytest/fixtures.py\", line 844, in FixtureFunctionMarker\n",
      "    params = attr.ib(convert=attr.converters.optional(tuple))\n",
      "TypeError: attrib() got an unexpected keyword argument 'convert'\n"
     ]
    },
    {
     "ename": "NameError",
     "evalue": "name 'Faker' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m"
     ]
    }
   ],
   "source": [
    "def anonymize(df):\n",
    "    'Anonymizes the given original data to anonymized form'\n",
    "    # Load the faker and its providers\n",
    "    faker  = Faker()\n",
    "    Faker.seed(4321)\n",
    "    \n",
    "    dict_first_names = {name: faker.first_name() for name in df['First Name'].unique()}\n",
    "    dict_last_names = {name: faker.last_name() for name in df['Last Name'].unique()}\n",
    "    df['First Name'] = df['First Name'].map(dict_first_names).str.upper()\n",
    "    df['Last Name'] = df['Last Name'].map(dict_last_names).str.upper()\n",
    "    return df\n",
    "\n",
    "anon_df = anonymize(sample_df).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "\n",
    "def clean_report(df_raw, na_cols=None, return_cols=None):\n",
    "    \"\"\" Cleans a dataframe by standardizing column names, stripping whitespaces\n",
    "    around string values, dropping rows with missing data (for certain cols)\n",
    "    Args:\n",
    "        df_raw (dataframe): Input dataframe\n",
    "        na_cols (list): List of cols to check for missing values\n",
    "        return_cols (list): List of cols to return, default returns all cols\n",
    "    Returns:\n",
    "        df (dataframe): Clean dataframe\n",
    "    \"\"\"\n",
    "    print(\"cleaning the report\")\n",
    "\n",
    "    # make a copy of the columns to return\n",
    "    if return_cols:\n",
    "        df = df_raw[return_cols].copy()\n",
    "    else:\n",
    "        df = df_raw.copy()\n",
    "\n",
    "    # standardize col names\n",
    "    df.columns = [col.lower().replace(\" \", \"_\") for col in df.columns]\n",
    "    df.columns = [col.replace(\"(\", \"\") for col in df.columns]\n",
    "    df.columns = [col.replace(\")\", \"\") for col in df.columns]\n",
    "\n",
    "    # remove whitespaces around string columns\n",
    "    df = df.applymap(lambda x: x.strip() if isinstance(x, str) else x)\n",
    "\n",
    "    return df\n",
    "\n",
    "\n",
    "def format_salaries(val):\n",
    "    \"\"\"\n",
    "    \"\"\"\n",
    "    if val != val:\n",
    "        return np.nan\n",
    "    else:\n",
    "        assert isinstance(val, str)\n",
    "        if val[0] == \"$\":\n",
    "            val = val[1:].strip()\n",
    "        val = val.replace(\",\", \"\")\n",
    "        val = val.replace(\".\", \"\")\n",
    "    return val\n",
    "\n",
    "\n",
    "def numeric_string(val, fill):\n",
    "    \"\"\"\n",
    "    \"\"\"\n",
    "    if val != val:\n",
    "        print(\"nan detected\")\n",
    "        return np.nan\n",
    "    else:\n",
    "        return str(int(val)).zfill(fill)\n",
    "\n",
    "\n",
    "def format_table(df_raw):\n",
    "    \"\"\"Formats the input reports for merging and calculation\n",
    "    Args:\n",
    "        df_raw (dataframe): Input dataframe to be formatted\n",
    "    Returns:\n",
    "        df (dataframe): The formatted dataframe\n",
    "    \"\"\"\n",
    "    #     input = FORMATS[\"input\"]\n",
    "    #     output = FORMATS[\"output\"]\n",
    "    #     return_cols = input.keys()  # which columns to preserve\n",
    "\n",
    "    #     # check that the core report matches expected input format\n",
    "    #     passed, errors = check_format(df_raw, input)\n",
    "    #     if not passed:\n",
    "    #         error = f\"Table report doesn't match expected input format: {errors}\"\n",
    "    #         return False, error, None\n",
    "\n",
    "    # # clean report and standardize merge columns\n",
    "    df = clean_report(df_raw)\n",
    "    df = df.rename(\n",
    "        columns={\"employee_type\": \"is_salaried\", \"full/part_time\": \"is_full_time\"}\n",
    "    )\n",
    "    df = df.applymap(lambda x: x.upper() if isinstance(x, str) else x)\n",
    "\n",
    "    df[\"position_number\"] = df[\"position_number\"].apply(lambda x: numeric_string(x, 7))\n",
    "    df[\"class_code\"] = df[\"class_code\"].apply(lambda x: numeric_string(x, 5))\n",
    "\n",
    "    salary_map = {\"is_salaried\": {\"SALARIED\": True, \"OPS\": False}}\n",
    "    full_time_map = {\"is_full_time\": {\"FULL TIME\": True, \"PART TIME\": False}}\n",
    "    df = df.replace(salary_map)\n",
    "    df = df.replace(full_time_map)\n",
    "    df[['is_salaried', 'is_full_time']] = df[['is_salaried', 'is_full_time']].astype('bool')\n",
    "\n",
    "    # format our numeric strings to represent cents as integers\n",
    "    df[\"salary\"] = df[\"salary\"].apply(format_salaries)\n",
    "    df[\"ops_hourly_rate\"] = df[\"ops_hourly_rate\"].apply(format_salaries)\n",
    "    # cast to integers\n",
    "    df[\"salary\"] = pd.to_numeric(df[\"salary\"], errors=\"coerce\").astype(pd.Int64Dtype())\n",
    "    df[\"ops_hourly_rate\"] = pd.to_numeric(df[\"salary\"], errors=\"coerce\").astype(pd.Int64Dtype())\n",
    "\n",
    "    df[\"state_hire_date\"] = df[\"state_hire_date\"].astype(\"datetime64\").dt.date\n",
    "\n",
    "\n",
    "    # check that output dataframe\n",
    "    # passed, errors = check_format(df, output)\n",
    "    # if not passed:\n",
    "    #     error = f\"Table didn't get formatted correctly: {errors}\"\n",
    "    #     return False, error, None\n",
    "\n",
    "    message = f\"Successfully formatted Florida salaries table.\"\n",
    "    return True, message, df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "input_path = Path.cwd() / \"tests\" / \"format_table\" / \"dummy_fl_salary_input.csv\"\n",
    "input_data = pd.read_csv(input_path, dtype='object')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "cleaning the report\n",
      "nan detected\n",
      "nan detected\n"
     ]
    }
   ],
   "source": [
    "_, message, formatted = format_table(input_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Successfully formatted Florida salaries table.'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "message"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "save_path = Path.cwd() / \"tests\" / \"format_table\" / \"dummy_fl_salary_output.pkl\"\n",
    "formatted.to_pickle(save_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 20 entries, 0 to 19\n",
      "Data columns (total 13 columns):\n",
      "agency_name        20 non-null object\n",
      "budget_entity      20 non-null object\n",
      "position_number    20 non-null object\n",
      "last_name          20 non-null object\n",
      "first_name         20 non-null object\n",
      "middle_name        18 non-null object\n",
      "is_salaried        20 non-null bool\n",
      "is_full_time       20 non-null bool\n",
      "class_code         18 non-null object\n",
      "class_title        18 non-null object\n",
      "state_hire_date    18 non-null object\n",
      "salary             18 non-null Int64\n",
      "ops_hourly_rate    18 non-null Int64\n",
      "dtypes: Int64(2), bool(2), object(9)\n",
      "memory usage: 1.9+ KB\n"
     ]
    }
   ],
   "source": [
    "test = pd.read_pickle(save_path)\n",
    "test.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'00000056'"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "numeric_string('56', 8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}