{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ef80f77",
   "metadata": {},
   "outputs": [],
   "source": [
    "import IPython\n",
    "import os\n",
    "import pandas as pd\n",
    "import pathlib\n",
    "import sys\n",
    "\n",
    "module_path = os.path.abspath(os.path.join(\"../..\"))\n",
    "if module_path not in sys.path:\n",
    "    sys.path.append(module_path)\n",
    "\n",
    "from data_pipeline.etl.base import ExtractTransformLoad\n",
    "from data_pipeline.score import field_names\n",
    "\n",
    "DATA_DIR = pathlib.Path.cwd().parent / \"data\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9838abab",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load\n",
    "path_to_score_file_1 = DATA_DIR / \"compare_two_score_csvs/usa (pre 970).csv\"\n",
    "path_to_score_file_2 = DATA_DIR / \"compare_two_score_csvs/usa (post 970).csv\"\n",
    "\n",
    "score_1_df = pd.read_csv(\n",
    "    path_to_score_file_1,\n",
    "    dtype={ExtractTransformLoad.GEOID_TRACT_FIELD_NAME: \"string\"},\n",
    ")\n",
    "\n",
    "score_2_df = pd.read_csv(\n",
    "    path_to_score_file_2,\n",
    "    dtype={ExtractTransformLoad.GEOID_TRACT_FIELD_NAME: \"string\"},\n",
    ")\n",
    "\n",
    "score_2_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76781a40",
   "metadata": {},
   "outputs": [],
   "source": [
    "# List columns in one but not the other\n",
    "score_2_df.columns.difference(score_1_df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05615567",
   "metadata": {},
   "outputs": [],
   "source": [
    "# List rows in one but not the other\n",
    "\n",
    "if len(score_2_df[ExtractTransformLoad.GEOID_TRACT_FIELD_NAME]) != len(\n",
    "    score_1_df[ExtractTransformLoad.GEOID_TRACT_FIELD_NAME]\n",
    "):\n",
    "    print(\"Different lengths!\")\n",
    "\n",
    "print(\"Difference in tract IDs:\")\n",
    "print(\n",
    "    set(score_2_df[ExtractTransformLoad.GEOID_TRACT_FIELD_NAME])\n",
    "    ^ set(score_1_df[ExtractTransformLoad.GEOID_TRACT_FIELD_NAME])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1407910",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join\n",
    "merged_df = score_1_df.merge(\n",
    "    score_2_df,\n",
    "    how=\"outer\",\n",
    "    on=ExtractTransformLoad.GEOID_TRACT_FIELD_NAME,\n",
    "    suffixes=(\"_1\", \"_2\"),\n",
    ")\n",
    "merged_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b4f5bfd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check each duplicate column:\n",
    "# Remove the suffix \"_1\"\n",
    "duplicate_columns = [x[:-2] for x in merged_df.columns if \"_1\" in x]\n",
    "\n",
    "columns_to_exclude_from_duplicates_check = [\"Total threshold criteria exceeded\"]\n",
    "\n",
    "columns_to_check = [\n",
    "    column\n",
    "    for column in duplicate_columns\n",
    "    if column not in columns_to_exclude_from_duplicates_check\n",
    "]\n",
    "\n",
    "any_errors_found = False\n",
    "for column_to_check in columns_to_check:\n",
    "    print(f\"Checking duplicate column {column_to_check}\")\n",
    "    if not merged_df[f\"{column_to_check}_1\"].equals(\n",
    "        merged_df[f\"{column_to_check}_2\"]\n",
    "    ):\n",
    "        print(f\"Error! Different values in {column_to_check}\")\n",
    "        print(\n",
    "            merged_df[f\"{column_to_check}_1\"].compare(\n",
    "                merged_df[f\"{column_to_check}_2\"]\n",
    "            )\n",
    "        )\n",
    "        any_errors_found = True\n",
    "\n",
    "if any_errors_found:\n",
    "    raise ValueError(f\"Error! Different values in one or more columns.\")"
   ]
  }
 ],
 "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
}
