notebooks/01_consolidate_and_check_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import re\n",
    "from datetime import datetime\n",
    "from scripts.clean_data import * #Local Module"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Loading file: texas_border_report_2017.xlsx\n",
      "Loading file: texas_border_report_2018.xlsx\n",
      "Loading file: texas_border_report_2019.xlsx\n",
      "Loading file: texas_border_report_2020.xlsx\n",
      "Loading file: texas_border_report_2021.xlsx\n",
      "Loading file: texas_border_report_2022.xlsx\n",
      "Loading file: texas_border_report_2023.xlsx\n"
     ]
    }
   ],
   "source": [
    "#Iterate over the data in the folder and store the cleaned data as a dataframe in the 'dataframes' dictionary.\n",
    "folder_path = 'data\\\\1_raw'\n",
    "\n",
    "# Initialize a dictionary to hold the DataFrames\n",
    "dataframes = {}\n",
    "\n",
    "# List all files in the folder\n",
    "files = os.listdir(folder_path)\n",
    "\n",
    "# Regular expression to match the file names and extract the year\n",
    "pattern = r'texas_border_report_(\\d{4}).xlsx'\n",
    "\n",
    "for file_name in files:\n",
    "    match = re.match(pattern, file_name)\n",
    "    if match:\n",
    "        year = match.group(1)\n",
    "        file_path = os.path.join(folder_path, file_name)\n",
    "        \n",
    "        print(f\"Loading file: {file_name}\")\n",
    "        \n",
    "        # Load the Excel file into a DataFrame\n",
    "        df = pd.read_excel(\n",
    "            file_path, \n",
    "            sheet_name='By Agency', \n",
    "            skiprows=1, \n",
    "            skipfooter=6,\n",
    "            dtype={'NIBRS Start Date': 'object'}\n",
    "        )\n",
    "        \n",
    "        #Date is formated as MMM-YY in excel, but the underlying data has the day. Going to stick with the MMM-YY formating for now\n",
    "        df['NIBRS Start Date'] = pd.to_datetime(df['NIBRS Start Date'], format='%b-%y')\n",
    "        df['Population'] = df['Population'].str.replace(',', '').astype(int)\n",
    "        df.drop(columns=['Total'], inplace=True)\n",
    "        \n",
    "        superscript_removal_map = str.maketrans('', '', '¹²³⁴⁵⁶⁷⁸⁹⁰')\n",
    "        df.columns = [col.lower().replace('  ', '_').replace(' ', '_').replace(',','').translate(superscript_removal_map) for col in df.columns]\n",
    "\n",
    "        # Store the DataFrame in the dictionary with a dynamic key\n",
    "        dataframes[f'report_{year}'] = df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All dataframes have the same shape.\n",
      "The columns in the old reports are the same as the columns in the reference report\n"
     ]
    }
   ],
   "source": [
    "#Create a reference report and define it as the most recently completed report as a reference\n",
    "reference_report, old_reports = extract_reference_df('report_2023', dataframes)\n",
    "\n",
    "#Checking to make sure all the shapes of the dataframes are the same\n",
    "check_shape(reference_report, old_reports)\n",
    "\n",
    "#Generate list of columns in the current report\n",
    "current_report_columns = list(reference_report.columns)\n",
    "\n",
    "#Check the columns\n",
    "check_columns(current_report_columns, old_reports)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column values in the reference dataframe match the values in the 'report_2017' dataframe.\n",
      "Column values in the reference dataframe match the values in the 'report_2018' dataframe.\n",
      "Column values in the reference dataframe match the values in the 'report_2019' dataframe.\n",
      "Column values in the reference dataframe match the values in the 'report_2020' dataframe.\n",
      "Column values in the reference dataframe match the values in the 'report_2021' dataframe.\n",
      "Column values in the reference dataframe match the values in the 'report_2022' dataframe.\n"
     ]
    }
   ],
   "source": [
    "#Check to make sure all of the agency names are consistent across all of the reports\n",
    "agencies_names = check_column_values('agency_name', reference_report, old_reports)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create a dataframe with all of the data from the reports\n",
    "\n",
    "#Adding the current report dataframe back into the dictionary\n",
    "dataframes['report_2023'] = reference_report\n",
    "\n",
    "#Extract and combine the dataframes \n",
    "for key, df in dataframes.items():\n",
    "    year = int(key.split('_')[1])  # Extract the year from the key\n",
    "    date_str = f'12/31/{year}' # Create a string representing '12/31/YYYY' for each row\n",
    "    \n",
    "    # Convert the string to datetime and assign to the new column 'report_year'\n",
    "    df['report_year'] = pd.to_datetime(date_str, format='%m/%d/%Y')\n",
    "    \n",
    "    globals()[key] = df  # Update the global variable with the modified DataFrame\n",
    "\n",
    "\n",
    "# Combine all DataFrames into a single DataFrame\n",
    "border_report = pd.concat(dataframes.values(), ignore_index=True)\n",
    "\n",
    "#Check to make sure all of the years are in the report\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Years in the report:  7\n",
      "Counties in the report:  14\n",
      "Agencies in the report:  85\n",
      "Number of NIBRS Crime Types: 11\n"
     ]
    }
   ],
   "source": [
    "#Data Checks\n",
    "print(\"Years in the report: \", border_report['report_year'].dt.year.nunique())\n",
    "\n",
    "print(\"Counties in the report: \", border_report['county'].nunique())\n",
    "\n",
    "print(\"Agencies in the report: \", border_report['agency_name'].nunique())\n",
    "\n",
    "nibrs_crime_types =  [\n",
    "                'murder_and_nonnegligent_manslaughter', 'negligent_manslaughter',\n",
    "                'rape', 'robbery', 'assault', 'burglary', 'larceny_theft',\n",
    "                'motor_vehicle_theft', 'arson', 'human_trafficking_commercial_sex_acts',\n",
    "                'human_trafficking_involuntary_servitude'\n",
    "                ]\n",
    "print(\"Number of NIBRS Crime Types:\", len(nibrs_crime_types))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Store resulting dataframe into 2_staging\n",
    "border_report.to_csv('data/2_staging/stg_texas_border_report.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "udacity_ds",
   "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.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}