{ "cells": [ { "cell_type": "code", "execution_count": 1, "id": "d778541e-d532-4c94-8e9c-556e1c86e5e9", "metadata": { "tags": [ "remove-cell" ] }, "outputs": [], "source": [ "import numpy as np\n", "import pandas as pd" ] }, { "cell_type": "markdown", "id": "9a9794fd-7733-4434-ad68-9c7740e3ee9d", "metadata": { "tags": [] }, "source": [ "# Edit distance example: Building inspection reports" ] }, { "cell_type": "markdown", "id": "71fa75a0-1246-41b5-a0b0-92d8b66c3f82", "metadata": {}, "source": [ "Next we will be looking at a dataset with free-form human-entered text that has a field with differences that are not meaningful. Meaningless differences between records that should be identical make it more challenging to find parts of the dataset that are similar (and should be treated the same). \n", "\n", "This dataset is from the City of Chicago data portal, and documents building inspections in the \n", "City of Chicago Department of Buildings. City of Chicago Data Portal. https://data.cityofchicago.org/Buildings/Building-Violations/22u3-xenr\n", "Accessed September 20, 2023, filtered for violations from the first half of January 2022." ] }, { "cell_type": "code", "execution_count": 2, "id": "7a50768f-c938-4ee2-9fc8-f70cd0af1a07", "metadata": {}, "outputs": [], "source": [ "violations_df = pd.read_csv(\"../../data/ChicagoBuildingViolationsEarlyJanuary2023.csv\")" ] }, { "cell_type": "markdown", "id": "06288c8c-342c-454c-9402-0850c61743e0", "metadata": {}, "source": [ "If your environment does not have the `levenshtein` package installed, see if you can install it. " ] }, { "cell_type": "code", "execution_count": 3, "id": "db3bc27a-6432-4736-badd-0a1326cc8819", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Requirement already satisfied: levenshtein in /opt/conda/lib/python3.11/site-packages (0.21.1)\n", "Requirement already satisfied: rapidfuzz<4.0.0,>=2.3.0 in /opt/conda/lib/python3.11/site-packages (from levenshtein) (3.3.0)\n" ] } ], "source": [ "%pip install levenshtein\n", "#!conda install -c conda-forge python-levenshtein" ] }, { "cell_type": "code", "execution_count": 4, "id": "ac7fb44a-38c6-441e-b16f-cbd0c87f577b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
IDVIOLATION LAST MODIFIED DATEVIOLATION DATEVIOLATION CODEVIOLATION STATUSVIOLATION STATUS DATEVIOLATION DESCRIPTIONVIOLATION LOCATIONVIOLATION INSPECTOR COMMENTSVIOLATION ORDINANCE...STREET NAMESTREET TYPEPROPERTY GROUPSSALATITUDELONGITUDELOCATIONYEARMONTHDAY
0672940406/27/202301/10/2022EV1111COMPLIED06/27/2023MAINTAIN OR REPAIR HYDRO ELEVANaNCITY FIRE RECALL TESTFailed to maintain hydraulic elevator equipmen......OAKDALEAVE80668.041.935438-87.642694(41.93543799801343, -87.64269400121434)2022110
1672671509/06/202201/03/2022CN070024COMPLIED09/06/2022REPAIR PORCH SYSTEMOTHER : :OTHER371335FRONT PORCH - ROTTED WOOD CEILING, MISSING ALU...Failed to repair or replace defective or missi......STATEST535078NaN41.675337-87.622431(41.67533708701544, -87.6224312766971)202213
2672670506/29/202301/03/2022CN071024COMPLIED06/29/2023REPAIR STOOPOTHER : :OTHERFRONT STOOP, SIDE WALLS - WASHED OUT MORTAR ( ...Failed to maintain stoop in safe condition and......ESCANABAAVE435511NaN41.749876-87.553927(41.749876029131826, -87.55392704663606)202213
3673181207/14/202301/13/2022CN062024COMPLIED07/14/2023PARAPETOTHER : :OTHERSOUTHWEST FRONT GARAGE PERAPET - WASHED OUT MO...Failed to maintain parapet wall in good repair......103RDST24594NaN41.706930-87.633353(41.70693036655445, -87.6333526539513)2022113
4673052906/13/202201/12/2022CN136026COMPLIED06/13/2022INSECTSOTHER : :OTHERBED BUG INFESTATION THROUGHOUT. - PROPERTY MUS...Exterminate insects and keep dwelling insect-f......LINCOLNAVE12595NaN41.983469-87.695449(41.98346922607376, -87.69544875484024)2022112
..................................................................
1827673080106/22/202301/12/2022NC2020COMPLIED06/22/2023PERMIT REQUIREDOTHER : CANOPY REMOVED PER PERMIT #101003272REAR YARD-OVERHEAD CANOPY ROOF INSTALLED BETWE...Failed to obtain building permit for erecting,......MAJORAVE123856NaN41.932068-87.768851(41.93206815540178, -87.76885130741938)2022112
1828673080206/22/202301/12/2022NC2071COMPLIED06/22/2023WORK NOT ALLOWEDOTHER : :OTHERREAR YARD-6-'4\" HIGH VINYL FENCE INSTALLED AT ...Remove work performed without permit and resto......MAJORAVE123856NaN41.932068-87.768851(41.93206815540178, -87.76885130741938)2022112
1829673080306/22/202301/12/2022NC2020COMPLIED06/22/2023PERMIT REQUIREDOTHER : :OTHERREAR YARD-OVERHEAD CANOPY ROOF INSTALLED BETWE...Failed to obtain building permit for erecting,......MAJORAVE123856NaN41.932068-87.768851(41.93206815540178, -87.76885130741938)2022112
1830673508406/22/202301/04/2022CN132016OPENNaNHEAT UNIT ADEQUATELYNaNLIVING ROOM 59 DEGREES, BEDROOM 60 DEGREES. GA...Heat dwelling unit adequately from September 1......WENTWORTHAVE20105NaN41.766092-87.629642(41.76609247175032, -87.62964151748226)202214
1831673508506/22/202301/04/2022CN132046OPENNaNSAFE WORKING CONDITIONSNaNFURNANCE OUT OF SERVICE AT TIME AT TIME OF INS...Provide and maintain every facility, piece of ......WENTWORTHAVE20105NaN41.766092-87.629642(41.76609247175032, -87.62964151748226)202214
\n", "

1832 rows × 29 columns

\n", "
" ], "text/plain": [ " ID VIOLATION LAST MODIFIED DATE VIOLATION DATE VIOLATION CODE \\\n", "0 6729404 06/27/2023 01/10/2022 EV1111 \n", "1 6726715 09/06/2022 01/03/2022 CN070024 \n", "2 6726705 06/29/2023 01/03/2022 CN071024 \n", "3 6731812 07/14/2023 01/13/2022 CN062024 \n", "4 6730529 06/13/2022 01/12/2022 CN136026 \n", "... ... ... ... ... \n", "1827 6730801 06/22/2023 01/12/2022 NC2020 \n", "1828 6730802 06/22/2023 01/12/2022 NC2071 \n", "1829 6730803 06/22/2023 01/12/2022 NC2020 \n", "1830 6735084 06/22/2023 01/04/2022 CN132016 \n", "1831 6735085 06/22/2023 01/04/2022 CN132046 \n", "\n", " VIOLATION STATUS VIOLATION STATUS DATE VIOLATION DESCRIPTION \\\n", "0 COMPLIED 06/27/2023 MAINTAIN OR REPAIR HYDRO ELEVA \n", "1 COMPLIED 09/06/2022 REPAIR PORCH SYSTEM \n", "2 COMPLIED 06/29/2023 REPAIR STOOP \n", "3 COMPLIED 07/14/2023 PARAPET \n", "4 COMPLIED 06/13/2022 INSECTS \n", "... ... ... ... \n", "1827 COMPLIED 06/22/2023 PERMIT REQUIRED \n", "1828 COMPLIED 06/22/2023 WORK NOT ALLOWED \n", "1829 COMPLIED 06/22/2023 PERMIT REQUIRED \n", "1830 OPEN NaN HEAT UNIT ADEQUATELY \n", "1831 OPEN NaN SAFE WORKING CONDITIONS \n", "\n", " VIOLATION LOCATION \\\n", "0 NaN \n", "1 OTHER : :OTHER371335 \n", "2 OTHER : :OTHER \n", "3 OTHER : :OTHER \n", "4 OTHER : :OTHER \n", "... ... \n", "1827 OTHER : CANOPY REMOVED PER PERMIT #101003272 \n", "1828 OTHER : :OTHER \n", "1829 OTHER : :OTHER \n", "1830 NaN \n", "1831 NaN \n", "\n", " VIOLATION INSPECTOR COMMENTS \\\n", "0 CITY FIRE RECALL TEST \n", "1 FRONT PORCH - ROTTED WOOD CEILING, MISSING ALU... \n", "2 FRONT STOOP, SIDE WALLS - WASHED OUT MORTAR ( ... \n", "3 SOUTHWEST FRONT GARAGE PERAPET - WASHED OUT MO... \n", "4 BED BUG INFESTATION THROUGHOUT. - PROPERTY MUS... \n", "... ... \n", "1827 REAR YARD-OVERHEAD CANOPY ROOF INSTALLED BETWE... \n", "1828 REAR YARD-6-'4\" HIGH VINYL FENCE INSTALLED AT ... \n", "1829 REAR YARD-OVERHEAD CANOPY ROOF INSTALLED BETWE... \n", "1830 LIVING ROOM 59 DEGREES, BEDROOM 60 DEGREES. GA... \n", "1831 FURNANCE OUT OF SERVICE AT TIME AT TIME OF INS... \n", "\n", " VIOLATION ORDINANCE ... STREET NAME \\\n", "0 Failed to maintain hydraulic elevator equipmen... ... OAKDALE \n", "1 Failed to repair or replace defective or missi... ... STATE \n", "2 Failed to maintain stoop in safe condition and... ... ESCANABA \n", "3 Failed to maintain parapet wall in good repair... ... 103RD \n", "4 Exterminate insects and keep dwelling insect-f... ... LINCOLN \n", "... ... ... ... \n", "1827 Failed to obtain building permit for erecting,... ... MAJOR \n", "1828 Remove work performed without permit and resto... ... MAJOR \n", "1829 Failed to obtain building permit for erecting,... ... MAJOR \n", "1830 Heat dwelling unit adequately from September 1... ... WENTWORTH \n", "1831 Provide and maintain every facility, piece of ... ... WENTWORTH \n", "\n", " STREET TYPE PROPERTY GROUP SSA LATITUDE LONGITUDE \\\n", "0 AVE 8066 8.0 41.935438 -87.642694 \n", "1 ST 535078 NaN 41.675337 -87.622431 \n", "2 AVE 435511 NaN 41.749876 -87.553927 \n", "3 ST 24594 NaN 41.706930 -87.633353 \n", "4 AVE 12595 NaN 41.983469 -87.695449 \n", "... ... ... ... ... ... \n", "1827 AVE 123856 NaN 41.932068 -87.768851 \n", "1828 AVE 123856 NaN 41.932068 -87.768851 \n", "1829 AVE 123856 NaN 41.932068 -87.768851 \n", "1830 AVE 20105 NaN 41.766092 -87.629642 \n", "1831 AVE 20105 NaN 41.766092 -87.629642 \n", "\n", " LOCATION YEAR MONTH DAY \n", "0 (41.93543799801343, -87.64269400121434) 2022 1 10 \n", "1 (41.67533708701544, -87.6224312766971) 2022 1 3 \n", "2 (41.749876029131826, -87.55392704663606) 2022 1 3 \n", "3 (41.70693036655445, -87.6333526539513) 2022 1 13 \n", "4 (41.98346922607376, -87.69544875484024) 2022 1 12 \n", "... ... ... ... .. \n", "1827 (41.93206815540178, -87.76885130741938) 2022 1 12 \n", "1828 (41.93206815540178, -87.76885130741938) 2022 1 12 \n", "1829 (41.93206815540178, -87.76885130741938) 2022 1 12 \n", "1830 (41.76609247175032, -87.62964151748226) 2022 1 4 \n", "1831 (41.76609247175032, -87.62964151748226) 2022 1 4 \n", "\n", "[1832 rows x 29 columns]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "violations_df" ] }, { "cell_type": "markdown", "id": "fca5c77d-7b4c-42c9-bd06-bbb2fb16a69d", "metadata": {}, "source": [ "Let's poke around the data just a little:" ] }, { "cell_type": "code", "execution_count": 5, "id": "4afc2abf-216e-4847-8fac-de02f27df8d1", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "VIOLATION DATE\n", "01/03/2022 205\n", "01/04/2022 147\n", "01/05/2022 160\n", "01/06/2022 107\n", "01/07/2022 229\n", "01/08/2022 121\n", "01/09/2022 27\n", "01/10/2022 153\n", "01/11/2022 127\n", "01/12/2022 180\n", "01/13/2022 119\n", "01/14/2022 208\n", "01/15/2022 49\n", "Name: count, dtype: int64" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "violations_df[\"VIOLATION DATE\"].value_counts().sort_index()" ] }, { "cell_type": "markdown", "id": "de9915a7-0194-4896-a5c8-35b08ac727e9", "metadata": {}, "source": [ "These dates are from the first two weeks of 2022. \n", "\n", "Building inspectors don't record many violations on Sundays. \n", "\n", "Let us look at the `VIOLATION DESCRIPTION` field:" ] }, { "cell_type": "code", "execution_count": 6, "id": "eed79f93-372e-4e94-9d7a-08b76544f541", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "VIOLATION DESCRIPTION\n", "MAINTAIN OR REPAIR ELECT ELEVA 222\n", "MAINTAIN OR REPAIR HYDRO ELEVA 123\n", "HEAT UNIT ADEQUATELY 89\n", "SAFE WORKING CONDITIONS 64\n", "ARRANGE PREMISE INSPECTION 57\n", " ... \n", "FIRE EXTNGSHR REQ, NONRESDNTL 1\n", "ARRANGE FOR INSPECTION 1\n", "ACCESS TO ROOF 1\n", "CEILING HEIGHT - 7' 1\n", "NO CLOSET/STORAGE UNDER STAIR 1\n", "Name: count, Length: 233, dtype: int64" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "violations_df[\"VIOLATION DESCRIPTION\"].value_counts()" ] }, { "cell_type": "code", "execution_count": 7, "id": "07974193-83e3-427d-a4ab-53885d7b017a", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "VIOLATION INSPECTOR COMMENTS\n", "INSPECTION 7\n", "STOP ALL WORK ON PREMISES UNTIL APPROVED PLANS AND BUILDING PERMIT ARE OBTAINED 13-12-080 13-32-035 5\n", "SCHEDULE ELEVATOR INSPECTION WITH CITY OF CHICAGO ELEVATOR BUREAU 4\n", "1ST FLOOR/RESIDENTIAL ENTRY DOOR - GAPS AT BOTTOM OF DOOR ALLOWING RATS TO ENTER BUILDING 4\n", "INTERIOR INSPECTION REQUIRED TO DETERMINE COMPLETE SCOPE OF WORK FOR NEW PERMIT APPLICATION. 4\n", " ..\n", "NO ENTRY TO INSP FOR CONDITIONS AND DETECTORS . WENT ON HEAT ALSO NO ENTRY 1\n", "EAST ELEVATION, 3 STORY OPEN WOOD PORCH SYSTEM - ALL STAIR RISER BOARDS MISSING. SOME RUSTED JOIST HANGERS AT ALL FLOORS. SOME PICKETS LOOSE. SOME NAILED CONNECTIONS RUSTED AND PULLING. 1\n", "916 APT 2 / BEDROOM RADIATOR - COLD TO TOUCH. 914 APT 1 / BEDROOM RADIATOR - ICE COLD. RADIATORS - NEED SERVICING, HEAT DOES NOT GO THROUGH AT TIME OF INSPECTION. CB132046 - SAFE WORKING CONDITIONS (14X-3-305.1, 14X-8-804.1 THRU 14X-8-804.4 AND 14X-8-804.5) 1\n", "2ND FL APT / SMOKE DETECTOR - MISSING 1\n", "FURNANCE OUT OF SERVICE AT TIME AT TIME OF INSPECTION. 1\n", "Name: count, Length: 1580, dtype: int64" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "violations_df[\"VIOLATION INSPECTOR COMMENTS\"].value_counts()" ] }, { "cell_type": "markdown", "id": "05868a44-5c79-4719-a1bc-943cf4f5d624", "metadata": {}, "source": [ "Ok, so 1580 different comments for 1832 rows. Are any values missing?" ] }, { "cell_type": "code", "execution_count": 8, "id": "9c4a9f44-9096-425e-a94b-7467ad264bae", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(1832, 98)" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "len(violations_df), violations_df[\"VIOLATION INSPECTOR COMMENTS\"].isnull().sum()" ] }, { "cell_type": "markdown", "id": "4a6a800e-43f4-47c3-9294-89b4983b61a8", "metadata": {}, "source": [ "Just counting, we have 1832 records, 98 of which (5.3%) are missing inspector comments.\n", "Of the remaining 1734 records with comments, there are 1579 different comments.\n", "This means there are 253 more records than distinct comments, so 13.8% of the \n", "comments are exact duplicates somewhere within the dataset. \n", "\n", "Some pairs of records, however, have non-identical comments that probably should \n", "be identical. We can use the Levenshtein distance to identify similar but not exactly \n", "identical comments; we will make some decisions about how to merge records, and produce a \n", "dataset that has had the text of similar comments merged." ] }, { "cell_type": "markdown", "id": "ee221f83-3637-43aa-9f20-efce8261d6fe", "metadata": {}, "source": [ "## All-against all comment comparison" ] }, { "cell_type": "markdown", "id": "6665a273-29aa-4f48-813d-094e19fb6d1b", "metadata": {}, "source": [ "First, we will compare all the distinct comments to all the other comments. \n", "(This requires $n^2$ comparisons, where n is the 1832 distinct comments\n", "in the `VIOLATION INSPECTOR COMMENTS` field.) We can afford 3.3 million comparisons between strings. " ] }, { "cell_type": "code", "execution_count": 9, "id": "7741493d-2fd9-4096-9b10-b4538debcaed", "metadata": {}, "outputs": [], "source": [ "import Levenshtein\n", "from Levenshtein import distance" ] }, { "cell_type": "code", "execution_count": 10, "id": "bd8d524a-829e-4317-925b-fa5c72ff38f2", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "5 STOP ALL WORK ON PREMISES UNTIL APPROVED PLANS AND BUILDING PERMIT ARE OBTAINED 13-12-080 13-32-035\n", "1 STOP ALL WORK ON PREMISES UNTIL APPROVED PLANS AND BUILDING PERMIT AE OBTAINED 13-12-080 13-32-035\n", "4 CHIMNEY - MISSING CAP\n", "1 CHIMNEY - MISSING CAP\n", "4 USING COOKING STOVE AS HEATING DEVICE.\n", "1 USING COOKING STOVE AS HEATING DEVICE\n", "3 WORKING DOOR RESTRICTOR REQUIRED.\n", "1 WORKING DOOR RESTRICTOR REQIRED.\n", "2 STOVE HEAT/SPACE HEATERS.\n", "1 STOVE HEAT/SPACE HEATER.\n", "2 EMPLOY LICENSED AND BONDED PLUMBING CONTRACTOR FOR PLUMBING WORK TO BE PERFORMED TO ADDRESS PLUMBING VIOLATIONS\n", "1 EMPLOY LICENSED AND BONDED PLUMBING CONTRACTOR FOR PLUMBING WORK TO BE PERFORMED TO ADDRESS PLUMBING VIOLATIONS.\n", "2 MISSING SMOKE DETECTORS.\n", "1 MISSING SMOKE DETECTOR.\n", "2 AT BASEMENT\n", "1 AT BASEMENT.\n", "2 A MAINTENANCE CONTROL PROGRAM IS REQUIRED TO BE ON SITE.\n", "1 AMAINTENANCE CONTROL PROGRAM IS REQUIRED TO BE ON SITE.\n", "1 PROVIDE ELECTRICAL PERMIT IN ORDER TO CORRECT ALL ELECTRICAL VIOLATIONS (14A-4-404.6)\n", "1 PROVIDE ELECTRICAL PERMIT IN ORDER TO CORRECT ALL ELECTRICAL VIOLATIONS (14A-4-404.6)\n", "1 STOP ALL WORK ON PREMISES UNTIL A BUILDING PERMIT IS OBTAINED 14A-3-306-1 14A-4-411-1\n", "1 STOP ALL WORK ON PREMISES UNTIL A BUILDING PERMIT IS OBTAINED 14A-3-306-4 14A-4-411-1\n", "1 MISSING SMOKE DETECTOR.\n", "2 MISSING SMOKE DETECTORS.\n", "1 MISSING SMOKE DETECTOR.\n", "1 MISSING SMOKE DETECTOR\n", "1 EMPLOY LICENSED AND BONDED PLUMBING CONTRACTOR FOR PLUMBING WORK TO BE PERFORMED TO ADDRESS PLUMBING VIOLATIONS.\n", "2 EMPLOY LICENSED AND BONDED PLUMBING CONTRACTOR FOR PLUMBING WORK TO BE PERFORMED TO ADDRESS PLUMBING VIOLATIONS\n", "1 INTERIOR OF BUILDING /NO RESPONSE. UNVERIFIED DETECTORS AND CONDITIONS. UNVERIFIED OCCUPANCY.\n", "1 INTERIOR OF BUILDING /NO RESPONSE. UNVERIFIED DETECTORS AND CONDITIONS. UNVERFIED OCCUPANCY.\n", "1 CAT. 1 TEST OVERDUE.\n", "1 CAT. 5 TEST OVERDUE.\n", "1 CAT. 5 TEST OVERDUE.\n", "1 CAT. 1 TEST OVERDUE.\n", "1 WORKING DOOR RESTRICTOR REQIRED.\n", "3 WORKING DOOR RESTRICTOR REQUIRED.\n", "1 REPAIR EMERGENCY PHONES\n", "1 REPAIR EMERGENCY PHONE.\n", "1 SOUTH ELEVATION / WINDOW SILLS - OPEN MORTAR JOINTS.\n", "1 SOUTH ELEVATION / WINDOW SILLS - OPEN MORTAR JOINT.\n", "1 STOP ALL WORK ON PREMISES UNTIL APPROVED PLANS AND BUILDING PERMIT AE OBTAINED 13-12-080 13-32-035\n", "5 STOP ALL WORK ON PREMISES UNTIL APPROVED PLANS AND BUILDING PERMIT ARE OBTAINED 13-12-080 13-32-035\n", "1 CODE DATA TAG ON CONTROLLER\n", "1 CODE DATA TAG ON CONTROLER\n", "1 ELEVATORS #4 & #5: PERFORM CAT1 & CAT5 TEST. PROVIDE TEST SHEET AND TAGS ONCE COMPLETED.\n", "1 ELEVATORS #4 & #5: PERFORM CAT! & CAT5 TEST. PROVIDE TEST SHEET AND TAGS ONCE COMPLETED.\n", "1 STOP ALL WORK ON PREMISES UNTIL A BUILDING PERMIT IS OBTAINED 14A-3-306-4 14A-4-411-1\n", "1 STOP ALL WORK ON PREMISES UNTIL A BUILDING PERMIT IS OBTAINED 14A-3-306-1 14A-4-411-1\n", "1 PERFORM CAT1 SAFETY TEST LEAVE CITY OF CHICAGO DOCUMENTATION.TRACTION ELEVATOR.\n", "1 PERFORM CAT5 SAFETY TEST LEAVE CITY OF CHICAGO DOCUMENTATION.TRACTION ELEVATOR.\n", "1 PROVIDE MAINTENANCE CONTROL PROGRAM\n", "1 PROVIDE MAINTENANCE CONTROL PROGRAM.\n", "1 PROVIDE MAINTENANCE CONTROL PROGRAM.\n", "1 PROVIDE MAINTENANCE CONTROL PROGRAM\n", "1 STOVE HEAT/SPACE HEATER.\n", "2 STOVE HEAT/SPACE HEATERS.\n", "1 PERFORM CAT5 SAFETY TEST LEAVE CITY OF CHICAGO DOCUMENTATION.TRACTION ELEVATOR.\n", "1 PERFORM CAT1 SAFETY TEST LEAVE CITY OF CHICAGO DOCUMENTATION.TRACTION ELEVATOR.\n", "1 CHIMNEY - MISSING CAP\n", "4 CHIMNEY - MISSING CAP\n", "1 AT BASEMENT.\n", "2 AT BASEMENT\n", "1 USING COOKING STOVE AS HEATING DEVICE\n", "4 USING COOKING STOVE AS HEATING DEVICE.\n", "1 REPAIR EMERGENCY PHONE.\n", "1 REPAIR EMERGENCY PHONES\n", "1 ELEVATORS #4 & #5: PERFORM CAT! & CAT5 TEST. PROVIDE TEST SHEET AND TAGS ONCE COMPLETED.\n", "1 ELEVATORS #4 & #5: PERFORM CAT1 & CAT5 TEST. PROVIDE TEST SHEET AND TAGS ONCE COMPLETED.\n", "1 CODE DATA TAG ON CONTROLER\n", "1 CODE DATA TAG ON CONTROLLER\n", "1 MISSING SMOKE DETECTOR\n", "1 MISSING SMOKE DETECTOR.\n", "1 PROVIDE ELECTRICAL PERMIT IN ORDER TO CORRECT ALL ELECTRICAL VIOLATIONS (14A-4-404.6)\n", "1 PROVIDE ELECTRICAL PERMIT IN ORDER TO CORRECT ALL ELECTRICAL VIOLATIONS (14A-4-404.6)\n", "1 AMAINTENANCE CONTROL PROGRAM IS REQUIRED TO BE ON SITE.\n", "2 A MAINTENANCE CONTROL PROGRAM IS REQUIRED TO BE ON SITE.\n", "1 INTERIOR OF BUILDING /NO RESPONSE. UNVERIFIED DETECTORS AND CONDITIONS. UNVERFIED OCCUPANCY.\n", "1 INTERIOR OF BUILDING /NO RESPONSE. UNVERIFIED DETECTORS AND CONDITIONS. UNVERIFIED OCCUPANCY.\n", "1 SOUTH ELEVATION / WINDOW SILLS - OPEN MORTAR JOINT.\n", "1 SOUTH ELEVATION / WINDOW SILLS - OPEN MORTAR JOINTS.\n" ] } ], "source": [ "distinctcomments = violations_df[\"VIOLATION INSPECTOR COMMENTS\"].value_counts().index\n", "distinctcommentnumber = violations_df[\"VIOLATION INSPECTOR COMMENTS\"].value_counts().values\n", "\n", "for i in range(len(distinctcomments)):\n", " for j in range(len(distinctcomments)):\n", " d = distance(distinctcomments[i], distinctcomments[j])\n", " # Find all the pairs that have Levenshtein distance 1 \n", " if d == 1: \n", " # And print out the number of times and each of the pair.\n", " print(distinctcommentnumber[i], distinctcomments[i])\n", " print(distinctcommentnumber[j], distinctcomments[j])\n" ] }, { "cell_type": "markdown", "id": "c11498c6-3607-47e6-b7b7-4872d149c79a", "metadata": {}, "source": [ "Note: This all-against all comparison computes both\n", "\n", "$$ d(\\textrm{comment}_i, \\textrm{comment}_j) $$\n", "\n", "and\n", "\n", "$$ d(\\textrm{comment}_j, \\textrm{comment}_i) $$\n", "\n", "which are always equal. So this double-counts all the pairs. Note also we aren't storing the distances, just calculating all of them and taking action for some values of the distance." ] }, { "cell_type": "markdown", "id": "5737fc04-ac21-46c7-940c-74d3d13b0333", "metadata": {}, "source": [ "Glancing at these pairs of comments, some are misspellings (UNVERFIED, CAT!, AE) and some are differences in \n", "punctuation. Surely, `CHIMNEY - MISSING CAP` is not meaningfully different from `CHIMNEY - MISSING CAP` \n", "with two spaces after the hyphen. These are meaningless differences.\n", "\n", "A handful of the differences, however, may be meaningful. CAT1 and CAT5 are different inspection schedules \n", "for different types of elevators, and the two comments have a distance of only 1. Differences in addresses, \n", "directions, permit numbers and cardinal directions can have small edit distances but can change the meaning.\n", "\n", "Creating a new field that merges comments that are more similar than a certain edit distance risks corrupting some\n", "of the data (SOUTH and NORTH have edit distance 2). We should always preserve the original data when \n", "\"cleaning\" in this way. But merging comments that mean the same thing will make it easier to find groups and \n", "understand the data by removing differences (in punctuation and spelling) that are in no way relevant to the \n", "inspections." ] }, { "cell_type": "markdown", "id": "b21d4ce9-fb49-4669-9d20-8e8ce467e40f", "metadata": {}, "source": [ "To merge the similar-but-not-identical comments, we will choose a threshold for merging comments, \n", "create a dictionary that maps old comments to new cleaned up comments, write a function that uses \n", "this dictionary to replace the comments that are on the list, apply this function, and finally check\n", "that we get what we expect." ] }, { "cell_type": "markdown", "id": "17eec689-53f0-48a3-af91-6f8ce0b10b59", "metadata": {}, "source": [ "First, let us decide on a threshold. What do comments look like at d=4?" ] }, { "cell_type": "code", "execution_count": 11, "id": "448d9de7-5f65-4f04-8fcd-700d5f339844", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2 INTERIOR OF BUILDING - ROACH INFESTATION\n", "2 INTERIOR OF BUILDING - MICE INFESTATION\n", "2 INTERIOR OF BUILDING - MICE INFESTATION\n", "2 INTERIOR OF BUILDING - ROACH INFESTATION\n", "2 STOP WORK UNTIL PERMIT IS OBTAINED SECTION 14A-4-411\n", "1 STOP WORK UNTIL NEW PERMIT IS OBTAINED SECTION 14A-4-411\n", "1 CHIMNEY - FRACTURES WITH LOOSE MORTAR\n", "1 CHIMNEY - FRACTURES AND LOOSE MORTAR\n", "1 CHIMNEY - FRACTURES AND LOOSE MORTAR\n", "1 CHIMNEY - FRACTURES WITH LOOSE MORTAR\n", "1 CHIMNEY - FRACTURES AND LOOSE MORTAR\n", "1 CHIMNEY - FRACTURES , LOOSE MORTAR\n", "1 CHIMNEY - FRACTURES AND LOOSE MORTAR\n", "1 CHIMNEY - FRACTURES ,LOOSE MORTAR\n", "1 NORTH CORNICE - WASHED OUT MORTAR.\n", "1 WEST CORNICE - WASHED OUT MORTAR.\n", "1 WEST CORNICE - WASHED OUT MORTAR.\n", "1 NORTH CORNICE - WASHED OUT MORTAR.\n", "1 CHIMNEY - FRACTURES , LOOSE MORTAR\n", "1 CHIMNEY - FRACTURES AND LOOSE MORTAR\n", "1 STOP WORK UNTIL NEW PERMIT IS OBTAINED SECTION 14A-4-411\n", "2 STOP WORK UNTIL PERMIT IS OBTAINED SECTION 14A-4-411\n", "1 CHIMNEY - FRACTURES ,LOOSE MORTAR\n", "1 CHIMNEY - FRACTURES AND LOOSE MORTAR\n" ] } ], "source": [ "for i in range(len(distinctcomments)):\n", " for j in range(len(distinctcomments)):\n", " d = distance(distinctcomments[i], distinctcomments[j])\n", " if d == 4: # Find all the pairs that have Levenshtein distance 1 \n", " print(distinctcommentnumber[i], distinctcomments[i])\n", " print(distinctcommentnumber[j], distinctcomments[j])" ] }, { "cell_type": "markdown", "id": "12c19153-dc5b-46ca-b707-a23954460eaa", "metadata": {}, "source": [ "These all look pretty solidly identical. The insertion of the word \"NEW\", the replacement of \" ,\" with \" AND \". We can merge these.\n", "\n", "What about d=6?" ] }, { "cell_type": "code", "execution_count": 12, "id": "7c95a131-4a94-4e20-9020-d229cdbb73f4", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "3 WEST, EAST, NORTH, ELEVATIONS - ABANDONED VEHICLE IN REAR YARD, BROKEN AWNING ON FRONT PORCH; LOTS OF JUNK, GARBAGE, APPLIANCES, BAGS OF CONCRETE, SHOPPING CARTS, AUTO PARTS AND TIRES, TELEVISION, BROKEN DOORS, TRASH, DEBRIS, ETC. THROUGHOUT; EXCESSIVE C\n", "2 WEST, EAST, NORTH, ELEVATIONS - ABANDONED VEHICLE IN REAR YARD, BROKEN AWNING ON FRONT PORCH; LOTS OF JUNK, GARBAGE, APPLIANCES, BAGS OF CONCRETE, SHOPPING CARTS, AUTO PARTS AND TIRES, TELEVISION, BROKEN DOORS, TRASH, DEBRIS, ETC. THROUGHOUT; EXCESSIVE CLUTTER\n", "2 NO SMOKE DETECTORS.\n", "2 MISSING SMOKE DETECTORS.\n", "2 WEST, EAST, NORTH, ELEVATIONS - ABANDONED VEHICLE IN REAR YARD, BROKEN AWNING ON FRONT PORCH; LOTS OF JUNK, GARBAGE, APPLIANCES, BAGS OF CONCRETE, SHOPPING CARTS, AUTO PARTS AND TIRES, TELEVISION, BROKEN DOORS, TRASH, DEBRIS, ETC. THROUGHOUT; EXCESSIVE CLUTTER\n", "3 WEST, EAST, NORTH, ELEVATIONS - ABANDONED VEHICLE IN REAR YARD, BROKEN AWNING ON FRONT PORCH; LOTS OF JUNK, GARBAGE, APPLIANCES, BAGS OF CONCRETE, SHOPPING CARTS, AUTO PARTS AND TIRES, TELEVISION, BROKEN DOORS, TRASH, DEBRIS, ETC. THROUGHOUT; EXCESSIVE C\n", "2 MISSING SMOKE DETECTORS.\n", "2 NO SMOKE DETECTORS.\n", "1 PANELS\n", "1 NO HEAT\n", "1 PANELS\n", "1 X\n", "1 NO HEAT\n", "1 PANELS\n", "1 NO HEAT\n", "1 FIRE TEST\n", "1 MISSING CARBON MONOXIDE DETECTOR.\n", "1 NO CARBON MONOXIDE DETECTOR.\n", "1 NO CARBON MONOXIDE DETECTOR.\n", "1 MISSING CARBON MONOXIDE DETECTOR.\n", "1 NORTH AND SOUTH ELEVATIONS - WASHED OUT MORTAR AND SPALLING BRICKS.\n", "1 SOUTH AND WEST ELEVATIONS - WASHED OUT MORTAR AND SPALLING BRICKS.\n", "1 EMERGENCY PHONE ISN'T WORKING. CAR 1\n", "1 EMERGENCY PHONE ISN'T WORKING. ALL CARS\n", "1 SOUTH AND WEST ELEVATIONS - WASHED OUT MORTAR AND SPALLING BRICKS.\n", "1 NORTH AND SOUTH ELEVATIONS - WASHED OUT MORTAR AND SPALLING BRICKS.\n", "1 EAST ELEVATION / PARAPET - STEP CRACKS .\n", "1 WEST ELEVATION / GARAGE - STEP CRACKS.\n", "1 REPAIR BOTH EMERGENCY PHONES\n", "1 REPAIR EMERGENCY PHONE.\n", "1 WORKING PIT LIGHTING AND AN ACCESSIBLE LIGHT SWITCH IS REQUIRED.\n", "1 WORKING PIT LIGHTING AND ACCESSIBLE LIGHT SWITCH REQUIRED.\n", "1 SECURE UNSUPPORTED CONDUITS IN BASEMENT AND WHERE REQUIRED IN TYPICAL SPACES AND AREAS THROUGHOUT PREMISES\n", "1 SECURE UNSUPPORTED BOXES IN BASEMENT AND WHERE REQUIRED IN TYPICAL SPACES AND AREAS THROUGHOUT PREMISES\n", "1 SECURE UNSUPPORTED BOXES IN BASEMENT AND WHERE REQUIRED IN TYPICAL SPACES AND AREAS THROUGHOUT PREMISES\n", "1 SECURE UNSUPPORTED CONDUITS IN BASEMENT AND WHERE REQUIRED IN TYPICAL SPACES AND AREAS THROUGHOUT PREMISES\n", "1 PROVIDE FIRE EXTINGUISHER INSIDE MACHINE ROOM.\n", "1 PROVIDE FIRE EXTINGUSHER IN MACHINE ROOM\n", "1 FIRE TEST\n", "1 NO HEAT\n", "1 WORKING PIT LIGHTING AND ACCESSIBLE LIGHT SWITCH REQUIRED.\n", "1 WORKING PIT LIGHTING AND AN ACCESSIBLE LIGHT SWITCH IS REQUIRED.\n", "1 INTERIOR OF BUILDING /NO RESPONSE. UNVERIFIED DETECTORS AND CONDITIONS. UNVERFIED COMPLAINT OF REAR PORCH SLANTED.\n", "1 INTERIOR OF BUILDING /NO RESPONSE. UNVERIFIED DETECTORS AND CONDITIONS. UNVERFIED COMPLAINT OF REAR PORCH UNSAFE.\n", "1 INTERIOR OF BUILDING /NO RESPONSE. UNVERIFIED DETECTORS AND CONDITIONS. UNVERFIED COMPLAINT OF REAR PORCH UNSAFE.\n", "1 INTERIOR OF BUILDING /NO RESPONSE. UNVERIFIED DETECTORS AND CONDITIONS. UNVERFIED COMPLAINT OF REAR PORCH SLANTED.\n", "1 WEST ELEVATION / GARAGE - STEP CRACKS.\n", "1 EAST ELEVATION / PARAPET - STEP CRACKS .\n", "1 REPAIR EMERGENCY PHONE.\n", "1 REPAIR BOTH EMERGENCY PHONES\n", "1 PROVIDE FIRE EXTINGUSHER IN MACHINE ROOM\n", "1 PROVIDE FIRE EXTINGUISHER INSIDE MACHINE ROOM.\n", "1 X\n", "1 PANELS\n", "1 EMERGENCY PHONE ISN'T WORKING. ALL CARS\n", "1 EMERGENCY PHONE ISN'T WORKING. CAR 1\n" ] } ], "source": [ "for i in range(len(distinctcomments)):\n", " for j in range(len(distinctcomments)):\n", " d = distance(distinctcomments[i], distinctcomments[j])\n", " if d == 6: # Find all the pairs that have Levenshtein distance 1 \n", " print(distinctcommentnumber[i], distinctcomments[i])\n", " print(distinctcommentnumber[j], distinctcomments[j])" ] }, { "cell_type": "markdown", "id": "3eeff123-f49f-479a-852a-f410b1911578", "metadata": {}, "source": [ "This is clearly too much. `NO HEAT` should not be merged with `PANELS` or `FIRE TEST`. \n", "\n", "What about d=5?" ] }, { "cell_type": "code", "execution_count": 13, "id": "2aa3f3bf-fbbe-48d7-8013-06779afdb04e", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2 SOUTH ELEVATION / DOWNSPOUT - MISSING.\n", "2 WEST ELEVATION / DOWNSPOUT - MISSING\n", "2 REAR EXTERIOR STAIRS,GRADE TO BASEMENT-MISSING HAND RAIL.\n", "2 FRONT EXTERIOR STAIRS-GRADE TO BASEMENT-MISSING HAND RAIL.\n", "2 FRONT EXTERIOR STAIRS-GRADE TO BASEMENT-MISSING HAND RAIL.\n", "2 REAR EXTERIOR STAIRS,GRADE TO BASEMENT-MISSING HAND RAIL.\n", "2 CODE COMPLIANT PIT LADDER REQUIRED.\n", "1 A CODE COMPLIANT PIT LADDER IS REQUIRED.\n", "2 WEST ELEVATION / DOWNSPOUT - MISSING\n", "2 SOUTH ELEVATION / DOWNSPOUT - MISSING.\n", "1 CHIMNEY - FRACTURES WITH LOOSE MORTAR\n", "1 CHIMNEY - FRACTURES , LOOSE MORTAR\n", "1 CHIMNEY - FRACTURES WITH LOOSE MORTAR\n", "1 CHIMNEY - FRACTURES ,LOOSE MORTAR\n", "1 BUILDING INFESTED WITH MICE, INCLUDING APT. 2E.\n", "1 BUILDING INFESTED WITH ROACHES, INCLUDING APT. 2E.\n", "1 BUILDING INFESTED WITH ROACHES, INCLUDING APT. 2E.\n", "1 BUILDING INFESTED WITH MICE, INCLUDING APT. 2E.\n", "1 EAST PASSENGER: REPAIR EMERGENCY PHONE\n", "1 EAST PASSENGER: REPAIR EMERGENCY ALARM\n", "1 SOUTH ELEVATION / COPING - OPEN MORTAR JOINTS.\n", "1 SOUTH ELEVATION / CORNICE - OPEN MORTAR JOINT.\n", "1 EAST PASSENGER: REPAIR EMERGENCY ALARM\n", "1 EAST PASSENGER: REPAIR EMERGENCY PHONE\n", "1 APT 2R / CARBON MONOXIDE - MISSING . PER SECOND CODE 14X-5-504.9, 14B-9-915.1.1, 14B-9-915.2 , AND 14R-11-1105)\n", "1 APT 1 / CARBON MONOXIDE - MISSING . PER SECTION CODE 14X-5-504.9, 14B-9-915.1.1, 14B-9-915.2 , AND 14R-11-1105)\n", "1 REPAIR BOTH EMERGENCY PHONES\n", "1 REPAIR EMERGENCY PHONES\n", "1 SOUTH ELEVATION / CORNICE - OPEN MORTAR JOINT.\n", "1 SOUTH ELEVATION / COPING - OPEN MORTAR JOINTS.\n", "1 REPAIR EMERGENCY PHONES\n", "1 REPAIR BOTH EMERGENCY PHONES\n", "1 (PREVIOUSLY CITED ON 8/14/18) CODE DATA PLATE REQUIRED ON CONTROLLER. BOTH ELEVATORS\n", "1 (PREVIOUSLY CITED ON 9/7/16) CODE DATA PLATE REQUIRED ON CONTROLLER. BOTH ELEVATORS.\n", "1 CHIMNEY - FRACTURES , LOOSE MORTAR\n", "1 CHIMNEY - FRACTURES WITH LOOSE MORTAR\n", "1 WEST ELEVATION / EXTERIOR WALL - MASONRY FRACTURES.\n", "1 NORTH ELEVATION / EXTERIOR WALL - MASONRY FRACTURES.\n", "1 (PREVIOUSLY CITED ON 9/7/16) CODE DATA PLATE REQUIRED ON CONTROLLER. BOTH ELEVATORS.\n", "1 (PREVIOUSLY CITED ON 8/14/18) CODE DATA PLATE REQUIRED ON CONTROLLER. BOTH ELEVATORS\n", "1 A CODE COMPLIANT PIT LADDER IS REQUIRED.\n", "2 CODE COMPLIANT PIT LADDER REQUIRED.\n", "1 NORTH ELEVATION / EXTERIOR WALL - MASONRY FRACTURES.\n", "1 WEST ELEVATION / EXTERIOR WALL - MASONRY FRACTURES.\n", "1 CHIMNEY - FRACTURES ,LOOSE MORTAR\n", "1 CHIMNEY - FRACTURES WITH LOOSE MORTAR\n", "1 APT 1 / CARBON MONOXIDE - MISSING . PER SECTION CODE 14X-5-504.9, 14B-9-915.1.1, 14B-9-915.2 , AND 14R-11-1105)\n", "1 APT 2R / CARBON MONOXIDE - MISSING . PER SECOND CODE 14X-5-504.9, 14B-9-915.1.1, 14B-9-915.2 , AND 14R-11-1105)\n" ] } ], "source": [ "for i in range(len(distinctcomments)):\n", " for j in range(len(distinctcomments)):\n", " d = distance(distinctcomments[i], distinctcomments[j])\n", " if d == 5: # Find all the pairs that have Levenshtein distance 1 \n", " print(distinctcommentnumber[i], distinctcomments[i])\n", " print(distinctcommentnumber[j], distinctcomments[j])" ] }, { "cell_type": "markdown", "id": "34436945-c1d6-4691-a5e5-4289328eb212", "metadata": {}, "source": [ "These all look similar enough to merge. Let us merge comments with d less than or equal 5." ] }, { "cell_type": "markdown", "id": "2bc33f7f-3d09-47bc-9ab3-6fac723a21b6", "metadata": {}, "source": [ "## Apply the changes" ] }, { "cell_type": "code", "execution_count": 14, "id": "44759566-2632-4629-aa67-b30973f73596", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "({'MISSING SMOKE DETECTOR.': 'MISSING SMOKE DETECTORS.',\n", " 'EMPLOY LICENSED AND BONDED PLUMBING CONTRACTOR FOR PLUMBING WORK TO BE PERFORMED TO ADDRESS PLUMBING VIOLATIONS.': 'EMPLOY LICENSED AND BONDED PLUMBING CONTRACTOR FOR PLUMBING WORK TO BE PERFORMED TO ADDRESS PLUMBING VIOLATIONS',\n", " 'STOP WORK UNTIL A PERMIT IS OBTAINED SECTION 14A-4-411': 'STOP WORK UNTIL PERMIT IS OBTAINED SECTION 14A-4-411',\n", " 'WORKING DOOR RESTRICTOR REQIRED.': 'WORKING DOOR RESTRICTOR REQUIRED.',\n", " 'STOP ALL WORK ON PREMISES UNTIL APPROVED PLANS AND BUILDING PERMIT AE OBTAINED 13-12-080 13-32-035': 'STOP ALL WORK ON PREMISES UNTIL APPROVED PLANS AND BUILDING PERMIT ARE OBTAINED 13-12-080 13-32-035',\n", " 'STOP WORK UNTIL NEW PERMIT IS OBTAINED SECTION 14A-4-411': 'STOP WORK UNTIL PERMIT IS OBTAINED SECTION 14A-4-411',\n", " 'STOVE HEAT/SPACE HEATER.': 'STOVE HEAT/SPACE HEATERS.',\n", " 'CHIMNEY - MISSING CAP': 'CHIMNEY - MISSING CAP',\n", " 'AT BASEMENT.': 'AT BASEMENT',\n", " 'USING COOKING STOVE AS HEATING DEVICE': 'USING COOKING STOVE AS HEATING DEVICE.',\n", " 'A CODE COMPLIANT PIT LADDER IS REQUIRED.': 'CODE COMPLIANT PIT LADDER REQUIRED.',\n", " 'CHIMNEYS - MISSING CAPS': 'CHIMNEY - MISSING CAP',\n", " 'MISSING SMOKE DETECTOR': 'MISSING SMOKE DETECTORS.',\n", " 'AMAINTENANCE CONTROL PROGRAM IS REQUIRED TO BE ON SITE.': 'A MAINTENANCE CONTROL PROGRAM IS REQUIRED TO BE ON SITE.'},\n", " 14)" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "renamedict = {}\n", "\n", "for i in range(len(distinctcomments)):\n", " for j in range(len(distinctcomments)):\n", " d = distance(distinctcomments[i], distinctcomments[j])\n", " # Find all the pairs that have Levenshtein distance <=5 \n", " if d <= 5 and d !=0 : \n", " # If comment i is used fewer times than j \n", " if distinctcommentnumber[i] < distinctcommentnumber[j] : \n", " # Flag comment i to be changed to comment j \n", " renamedict[distinctcomments[i]] = distinctcomments[j]\n", "renamedict, len(renamedict)" ] }, { "cell_type": "markdown", "id": "f5a01ca9-7519-4817-ae3a-f93ddfebd6d4", "metadata": {}, "source": [ "This looks reasonable. Write the function: " ] }, { "cell_type": "code", "execution_count": 15, "id": "43fd9167-3e67-4617-96a2-7631ca78fc3c", "metadata": {}, "outputs": [], "source": [ "def renamecomment(comment):\n", " if comment in renamedict.keys():\n", " return renamedict[comment]\n", " else:\n", " return comment" ] }, { "cell_type": "markdown", "id": "a3477104-6bc8-4639-9720-ffbbb6209738", "metadata": {}, "source": [ "I don't want to write a function without some kind of testing to see that it does what I think it does.\n", "I'll write two tests, simple invocations of `renamecomment` with known correct answers." ] }, { "cell_type": "code", "execution_count": 16, "id": "349aa921-8c4b-4a4f-86c3-692bc9ac332f", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "True" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "renamecomment(\"NOT IN DICTIONARY\") == \"NOT IN DICTIONARY\"" ] }, { "cell_type": "code", "execution_count": 17, "id": "4ba7afe0-a742-43c4-93b6-ff5c8ac628fd", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "True" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "renamecomment('MISSING SMOKE DETECTOR') == 'MISSING SMOKE DETECTORS.'" ] }, { "cell_type": "markdown", "id": "d21630a5-f5db-4bec-b709-34510a5bc5ea", "metadata": {}, "source": [ "Ok, seems to work. Let us create a new column with `apply`: " ] }, { "cell_type": "code", "execution_count": 18, "id": "8e63fbe6-685b-4fbd-9943-33637473bb55", "metadata": {}, "outputs": [], "source": [ "violations_df[\"COMMENTS FIXED\"] = violations_df[\"VIOLATION INSPECTOR COMMENTS\"].apply(renamecomment)" ] }, { "cell_type": "markdown", "id": "c0db2425-059b-4e8b-9197-9fbe487f359f", "metadata": {}, "source": [ "And how do we know that this did anything? Let's check which rows were changed: " ] }, { "cell_type": "code", "execution_count": 19, "id": "22c97700-4bb1-4b30-8dbf-94408aa7920a", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
IDVIOLATION LAST MODIFIED DATEVIOLATION DATEVIOLATION CODEVIOLATION STATUSVIOLATION STATUS DATEVIOLATION DESCRIPTIONVIOLATION LOCATIONVIOLATION INSPECTOR COMMENTSVIOLATION ORDINANCE...STREET TYPEPROPERTY GROUPSSALATITUDELONGITUDELOCATIONYEARMONTHDAYCOMMENTS FIXED
5672641503/08/202201/03/2022VT1010COMPLIED03/08/2022ARRANGE COMPLETED PERMIT INSP.NaNNaNArrange mechanical ventilation or warm air hea......AVE19834822.041.980342-87.669580(41.98034244999325, -87.66958046014629)202213NaN
22673232509/21/202201/11/2022VT1030COMPLIED09/20/2022ARRANGE PERMIT INSP.1115 N CHRISTIANA AVNaNArrange in writing for mechanical ventilating ......AVE51247NaN41.901508-87.710433(41.901507612598834, -87.71043339750256)2022111NaN
23673514603/11/202201/09/2022CN046013COMPLIED03/11/2022UNAPPROVED HEATING DEVICENaNSTOVE HEAT/SPACE HEATER.Stop using cooking or water heating device as ......AVE21765NaN41.691177-87.612259(41.691177471896076, -87.61225906980805)202219STOVE HEAT/SPACE HEATERS.
24672733401/04/202201/04/2022PL151137OPENNaNOPENNaNNaNNaN...AVE40061414.041.770689-87.685700(41.77068909096362, -87.68569998583654)202214NaN
25673039901/12/202201/11/2022PLD2290OPENNaNPROVIDE AUTOMATIC MIXING DEVICNaNNaNProvide automatic mixing device for public lav......ST1596NaN41.887222-87.757826(41.887221537144235, -87.75782634562349)2022111NaN
..................................................................
1598672911608/31/202201/10/2022VT1010COMPLIED08/31/2022ARRANGE COMPLETED PERMIT INSP.NaNNaNArrange mechanical ventilation or warm air hea......AVE156823NaN41.947644-87.781798(41.947644039902265, -87.78179844629967)2022110NaN
1618673038511/21/202201/11/2022VT1010COMPLIED11/18/2022ARRANGE COMPLETED PERMIT INSP.NaNNaNArrange mechanical ventilation or warm air hea......ST286211NaN41.841823-87.701135(41.84182295978573, -87.70113477596593)2022111NaN
1621672698412/07/202201/03/2022PL151137COMPLIED12/07/2022OPENNaNNaNNaN...AVE60561NaN41.904908-87.676385(41.904907565709664, -87.67638534234142)202213NaN
1627672883311/15/202201/07/2022VT1010OPENNaNARRANGE COMPLETED PERMIT INSP.NaNNaNArrange mechanical ventilation or warm air hea......AVE117688NaN41.933936-87.715618(41.933936312229164, -87.71561838752238)202217NaN
1774672803305/25/202301/03/2022EV1111OPENNaNMAINTAIN OR REPAIR HYDRO ELEVANaNWORKING DOOR RESTRICTOR REQIRED.Failed to maintain hydraulic elevator equipmen......BLVD14097NaN42.018888-87.684722(42.01888785624424, -87.68472174840898)202213WORKING DOOR RESTRICTOR REQUIRED.
\n", "

112 rows × 30 columns

\n", "
" ], "text/plain": [ " ID VIOLATION LAST MODIFIED DATE VIOLATION DATE VIOLATION CODE \\\n", "5 6726415 03/08/2022 01/03/2022 VT1010 \n", "22 6732325 09/21/2022 01/11/2022 VT1030 \n", "23 6735146 03/11/2022 01/09/2022 CN046013 \n", "24 6727334 01/04/2022 01/04/2022 PL151137 \n", "25 6730399 01/12/2022 01/11/2022 PLD2290 \n", "... ... ... ... ... \n", "1598 6729116 08/31/2022 01/10/2022 VT1010 \n", "1618 6730385 11/21/2022 01/11/2022 VT1010 \n", "1621 6726984 12/07/2022 01/03/2022 PL151137 \n", "1627 6728833 11/15/2022 01/07/2022 VT1010 \n", "1774 6728033 05/25/2023 01/03/2022 EV1111 \n", "\n", " VIOLATION STATUS VIOLATION STATUS DATE VIOLATION DESCRIPTION \\\n", "5 COMPLIED 03/08/2022 ARRANGE COMPLETED PERMIT INSP. \n", "22 COMPLIED 09/20/2022 ARRANGE PERMIT INSP. \n", "23 COMPLIED 03/11/2022 UNAPPROVED HEATING DEVICE \n", "24 OPEN NaN OPEN \n", "25 OPEN NaN PROVIDE AUTOMATIC MIXING DEVIC \n", "... ... ... ... \n", "1598 COMPLIED 08/31/2022 ARRANGE COMPLETED PERMIT INSP. \n", "1618 COMPLIED 11/18/2022 ARRANGE COMPLETED PERMIT INSP. \n", "1621 COMPLIED 12/07/2022 OPEN \n", "1627 OPEN NaN ARRANGE COMPLETED PERMIT INSP. \n", "1774 OPEN NaN MAINTAIN OR REPAIR HYDRO ELEVA \n", "\n", " VIOLATION LOCATION VIOLATION INSPECTOR COMMENTS \\\n", "5 NaN NaN \n", "22 1115 N CHRISTIANA AV NaN \n", "23 NaN STOVE HEAT/SPACE HEATER. \n", "24 NaN NaN \n", "25 NaN NaN \n", "... ... ... \n", "1598 NaN NaN \n", "1618 NaN NaN \n", "1621 NaN NaN \n", "1627 NaN NaN \n", "1774 NaN WORKING DOOR RESTRICTOR REQIRED. \n", "\n", " VIOLATION ORDINANCE ... STREET TYPE \\\n", "5 Arrange mechanical ventilation or warm air hea... ... AVE \n", "22 Arrange in writing for mechanical ventilating ... ... AVE \n", "23 Stop using cooking or water heating device as ... ... AVE \n", "24 NaN ... AVE \n", "25 Provide automatic mixing device for public lav... ... ST \n", "... ... ... ... \n", "1598 Arrange mechanical ventilation or warm air hea... ... AVE \n", "1618 Arrange mechanical ventilation or warm air hea... ... ST \n", "1621 NaN ... AVE \n", "1627 Arrange mechanical ventilation or warm air hea... ... AVE \n", "1774 Failed to maintain hydraulic elevator equipmen... ... BLVD \n", "\n", " PROPERTY GROUP SSA LATITUDE LONGITUDE \\\n", "5 198348 22.0 41.980342 -87.669580 \n", "22 51247 NaN 41.901508 -87.710433 \n", "23 21765 NaN 41.691177 -87.612259 \n", "24 400614 14.0 41.770689 -87.685700 \n", "25 1596 NaN 41.887222 -87.757826 \n", "... ... ... ... ... \n", "1598 156823 NaN 41.947644 -87.781798 \n", "1618 286211 NaN 41.841823 -87.701135 \n", "1621 60561 NaN 41.904908 -87.676385 \n", "1627 117688 NaN 41.933936 -87.715618 \n", "1774 14097 NaN 42.018888 -87.684722 \n", "\n", " LOCATION YEAR MONTH DAY \\\n", "5 (41.98034244999325, -87.66958046014629) 2022 1 3 \n", "22 (41.901507612598834, -87.71043339750256) 2022 1 11 \n", "23 (41.691177471896076, -87.61225906980805) 2022 1 9 \n", "24 (41.77068909096362, -87.68569998583654) 2022 1 4 \n", "25 (41.887221537144235, -87.75782634562349) 2022 1 11 \n", "... ... ... ... .. \n", "1598 (41.947644039902265, -87.78179844629967) 2022 1 10 \n", "1618 (41.84182295978573, -87.70113477596593) 2022 1 11 \n", "1621 (41.904907565709664, -87.67638534234142) 2022 1 3 \n", "1627 (41.933936312229164, -87.71561838752238) 2022 1 7 \n", "1774 (42.01888785624424, -87.68472174840898) 2022 1 3 \n", "\n", " COMMENTS FIXED \n", "5 NaN \n", "22 NaN \n", "23 STOVE HEAT/SPACE HEATERS. \n", "24 NaN \n", "25 NaN \n", "... ... \n", "1598 NaN \n", "1618 NaN \n", "1621 NaN \n", "1627 NaN \n", "1774 WORKING DOOR RESTRICTOR REQUIRED. \n", "\n", "[112 rows x 30 columns]" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "violations_df.query(\"`COMMENTS FIXED` != `VIOLATION INSPECTOR COMMENTS`\")" ] }, { "cell_type": "markdown", "id": "c42f57b5-3a36-460f-9866-b536bf8a6457", "metadata": {}, "source": [ "112 rows were changed. Hmm. So what did this do to the number of unique comments? We expect something like 100 fewer unique comments.. right?" ] }, { "cell_type": "code", "execution_count": 20, "id": "21c90abb-be32-42a4-9e91-a6f93a1b03c4", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Records: 1832\n", "Blank records: 98\n", "Distinct records: 1580\n", "Distinct records fixed: 1566\n" ] } ], "source": [ "print(\"Records: \", len(violations_df))\n", "print(\"Blank records: \", violations_df[\"VIOLATION INSPECTOR COMMENTS\"].isnull().sum())\n", "print(\"Distinct records: \", len(violations_df[\"VIOLATION INSPECTOR COMMENTS\"].value_counts()) ) \n", "print(\"Distinct records fixed:\", len(violations_df[\"COMMENTS FIXED\"].value_counts()))" ] }, { "cell_type": "markdown", "id": "e2c4b094-0db7-4726-a7ae-f5e87c84331f", "metadata": {}, "source": [ "This is a little puzzling. The renaming changed 112 rows but only reduced the number of unique entries by 14. \n", "This is not what I was expecting; something is not working quite as it should. Let's look at some of the 112\n", "rows that were changed more closely:" ] }, { "cell_type": "code", "execution_count": 21, "id": "41c64d47-d0c5-47cb-ae04-5af81a239283", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
VIOLATION INSPECTOR COMMENTSCOMMENTS FIXED
5NaNNaN
22NaNNaN
23STOVE HEAT/SPACE HEATER.STOVE HEAT/SPACE HEATERS.
24NaNNaN
25NaNNaN
.........
1598NaNNaN
1618NaNNaN
1621NaNNaN
1627NaNNaN
1774WORKING DOOR RESTRICTOR REQIRED.WORKING DOOR RESTRICTOR REQUIRED.
\n", "

112 rows × 2 columns

\n", "
" ], "text/plain": [ " VIOLATION INSPECTOR COMMENTS COMMENTS FIXED\n", "5 NaN NaN\n", "22 NaN NaN\n", "23 STOVE HEAT/SPACE HEATER. STOVE HEAT/SPACE HEATERS.\n", "24 NaN NaN\n", "25 NaN NaN\n", "... ... ...\n", "1598 NaN NaN\n", "1618 NaN NaN\n", "1621 NaN NaN\n", "1627 NaN NaN\n", "1774 WORKING DOOR RESTRICTOR REQIRED. WORKING DOOR RESTRICTOR REQUIRED.\n", "\n", "[112 rows x 2 columns]" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "changed = violations_df.query(\"`COMMENTS FIXED` != `VIOLATION INSPECTOR COMMENTS`\") \n", "changed[[\"VIOLATION INSPECTOR COMMENTS\", \"COMMENTS FIXED\"]] " ] }, { "cell_type": "markdown", "id": "c1337421-7c46-4496-9066-28c0b44bddd0", "metadata": {}, "source": [ "This is disappointing. The 112 includes NaNs, since NaN does not match with NaN. Show only the non-NaN rows: " ] }, { "cell_type": "code", "execution_count": 22, "id": "a2dc5ca6-e54a-45d1-8d44-0d3c3429dc39", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
VIOLATION INSPECTOR COMMENTSCOMMENTS FIXED
23STOVE HEAT/SPACE HEATER.STOVE HEAT/SPACE HEATERS.
34STOP WORK UNTIL NEW PERMIT IS OBTAINED SECTION...STOP WORK UNTIL PERMIT IS OBTAINED SECTION 14A...
168AT BASEMENT.AT BASEMENT
204CHIMNEY - MISSING CAPCHIMNEY - MISSING CAP
529AMAINTENANCE CONTROL PROGRAM IS REQUIRED TO BE...A MAINTENANCE CONTROL PROGRAM IS REQUIRED TO B...
777USING COOKING STOVE AS HEATING DEVICEUSING COOKING STOVE AS HEATING DEVICE.
802MISSING SMOKE DETECTORMISSING SMOKE DETECTORS.
876A CODE COMPLIANT PIT LADDER IS REQUIRED.CODE COMPLIANT PIT LADDER REQUIRED.
898CHIMNEYS - MISSING CAPSCHIMNEY - MISSING CAP
1104STOP WORK UNTIL A PERMIT IS OBTAINED SECTION 1...STOP WORK UNTIL PERMIT IS OBTAINED SECTION 14A...
1188EMPLOY LICENSED AND BONDED PLUMBING CONTRACTOR...EMPLOY LICENSED AND BONDED PLUMBING CONTRACTOR...
1201MISSING SMOKE DETECTOR.MISSING SMOKE DETECTORS.
1485STOP ALL WORK ON PREMISES UNTIL APPROVED PLANS...STOP ALL WORK ON PREMISES UNTIL APPROVED PLANS...
1774WORKING DOOR RESTRICTOR REQIRED.WORKING DOOR RESTRICTOR REQUIRED.
\n", "
" ], "text/plain": [ " VIOLATION INSPECTOR COMMENTS \\\n", "23 STOVE HEAT/SPACE HEATER. \n", "34 STOP WORK UNTIL NEW PERMIT IS OBTAINED SECTION... \n", "168 AT BASEMENT. \n", "204 CHIMNEY - MISSING CAP \n", "529 AMAINTENANCE CONTROL PROGRAM IS REQUIRED TO BE... \n", "777 USING COOKING STOVE AS HEATING DEVICE \n", "802 MISSING SMOKE DETECTOR \n", "876 A CODE COMPLIANT PIT LADDER IS REQUIRED. \n", "898 CHIMNEYS - MISSING CAPS \n", "1104 STOP WORK UNTIL A PERMIT IS OBTAINED SECTION 1... \n", "1188 EMPLOY LICENSED AND BONDED PLUMBING CONTRACTOR... \n", "1201 MISSING SMOKE DETECTOR. \n", "1485 STOP ALL WORK ON PREMISES UNTIL APPROVED PLANS... \n", "1774 WORKING DOOR RESTRICTOR REQIRED. \n", "\n", " COMMENTS FIXED \n", "23 STOVE HEAT/SPACE HEATERS. \n", "34 STOP WORK UNTIL PERMIT IS OBTAINED SECTION 14A... \n", "168 AT BASEMENT \n", "204 CHIMNEY - MISSING CAP \n", "529 A MAINTENANCE CONTROL PROGRAM IS REQUIRED TO B... \n", "777 USING COOKING STOVE AS HEATING DEVICE. \n", "802 MISSING SMOKE DETECTORS. \n", "876 CODE COMPLIANT PIT LADDER REQUIRED. \n", "898 CHIMNEY - MISSING CAP \n", "1104 STOP WORK UNTIL PERMIT IS OBTAINED SECTION 14A... \n", "1188 EMPLOY LICENSED AND BONDED PLUMBING CONTRACTOR... \n", "1201 MISSING SMOKE DETECTORS. \n", "1485 STOP ALL WORK ON PREMISES UNTIL APPROVED PLANS... \n", "1774 WORKING DOOR RESTRICTOR REQUIRED. " ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "changed[~changed[\"COMMENTS FIXED\"].isnull()][[\"VIOLATION INSPECTOR COMMENTS\", \"COMMENTS FIXED\"]] " ] }, { "cell_type": "markdown", "id": "2619cc9a-3adb-4359-8739-1cbf3e2453b7", "metadata": {}, "source": [ "These are the 14 that were changed. " ] }, { "cell_type": "markdown", "id": "78da9d38-a3a6-480c-b949-8a6b0077bdc6", "metadata": {}, "source": [ "## Fix doesn't fix enough comments\n", "Something doesn't quite add up here. Let me count the number of pairs\n", "that were found that were close enough to be merged." ] }, { "cell_type": "code", "execution_count": 23, "id": "155228a6-b1ac-4307-b26c-e145f8bc9327", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "({'MISSING SMOKE DETECTOR.': 'MISSING SMOKE DETECTORS.',\n", " 'EMPLOY LICENSED AND BONDED PLUMBING CONTRACTOR FOR PLUMBING WORK TO BE PERFORMED TO ADDRESS PLUMBING VIOLATIONS.': 'EMPLOY LICENSED AND BONDED PLUMBING CONTRACTOR FOR PLUMBING WORK TO BE PERFORMED TO ADDRESS PLUMBING VIOLATIONS',\n", " 'STOP WORK UNTIL A PERMIT IS OBTAINED SECTION 14A-4-411': 'STOP WORK UNTIL PERMIT IS OBTAINED SECTION 14A-4-411',\n", " 'WORKING DOOR RESTRICTOR REQIRED.': 'WORKING DOOR RESTRICTOR REQUIRED.',\n", " 'STOP ALL WORK ON PREMISES UNTIL APPROVED PLANS AND BUILDING PERMIT AE OBTAINED 13-12-080 13-32-035': 'STOP ALL WORK ON PREMISES UNTIL APPROVED PLANS AND BUILDING PERMIT ARE OBTAINED 13-12-080 13-32-035',\n", " 'STOP WORK UNTIL NEW PERMIT IS OBTAINED SECTION 14A-4-411': 'STOP WORK UNTIL PERMIT IS OBTAINED SECTION 14A-4-411',\n", " 'STOVE HEAT/SPACE HEATER.': 'STOVE HEAT/SPACE HEATERS.',\n", " 'CHIMNEY - MISSING CAP': 'CHIMNEY - MISSING CAP',\n", " 'AT BASEMENT.': 'AT BASEMENT',\n", " 'USING COOKING STOVE AS HEATING DEVICE': 'USING COOKING STOVE AS HEATING DEVICE.',\n", " 'A CODE COMPLIANT PIT LADDER IS REQUIRED.': 'CODE COMPLIANT PIT LADDER REQUIRED.',\n", " 'CHIMNEYS - MISSING CAPS': 'CHIMNEY - MISSING CAP',\n", " 'MISSING SMOKE DETECTOR': 'MISSING SMOKE DETECTORS.',\n", " 'AMAINTENANCE CONTROL PROGRAM IS REQUIRED TO BE ON SITE.': 'A MAINTENANCE CONTROL PROGRAM IS REQUIRED TO BE ON SITE.'},\n", " 14,\n", " 116)" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "renamedict = {}\n", "n=0\n", "for i in range(len(distinctcomments)):\n", " for j in range(len(distinctcomments)):\n", " d = distance(distinctcomments[i], distinctcomments[j])\n", " # Find all the pairs that have Levenshtein distance <=5 \n", " if d <= 5 and d !=0 :\n", " n = n+1\n", " # If comment i is used fewer times than j \n", " if distinctcommentnumber[i] < distinctcommentnumber[j] : \n", " # Flag comment i to be changed to comment j \n", " renamedict[distinctcomments[i]] = distinctcomments[j]\n", "renamedict, len(renamedict), n" ] }, { "cell_type": "markdown", "id": "5e81db9a-db57-49e9-835c-98cfcdeb5fcc", "metadata": {}, "source": [ "There were 116 pairs of comments (out of 3.3 million) that were closer than d = 5 but the loop only left\n", "instructions to rename 14 comments? I need to look closely at the logic here:" ] }, { "cell_type": "raw", "id": "ab1c35f5-a5d2-4e16-8e54-4fd093dec029", "metadata": {}, "source": [ " if d <= 5 and d !=0 :\n", " n = n+1\n", " # If comment i is used fewer times than j \n", " if distinctcommentnumber[i] < distinctcommentnumber[j] : \n", " # Flag comment i to be changed to comment j \n", " renamedict[distinctcomments[i]] = distinctcomments[j]" ] }, { "cell_type": "markdown", "id": "e6d6e224-bab7-40b3-83a6-21478c22910e", "metadata": {}, "source": [ "Let's talk through the logic here.\n", "\n", "Which comments get renamed? Comments that are more than 0 but 5 or fewer edits away from another comment, and\n", "for which the other comment has strictly greater abundance. \n", "\n", "Aha. For this dataset, most \n", "of the comments are unique, and most of the pairs of similar comments have both comments unique.\n", "Comments that could be merged, but that are present only once are ignored. \n", "\n", "Let us add logic that\n", "merges comments even if they have the same number of occurrences. We need an arbitrary rule to \n", "pick one comment when the counts are tied: let us choose the one that is first in alphabetical \n", "order. This is a canonicalization choice: we introduce an arbitrary choice between two similar\n", "comments to allow consolidation without having to make a choice that requires human input, as \n", "choosing the correct spelling would. " ] }, { "cell_type": "code", "execution_count": 24, "id": "a9372fe5-10c7-4ebe-9055-228a705f01cf", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "({'REAR EXTERIOR STAIRS,GRADE TO BASEMENT-MISSING HAND RAIL.': 'FRONT EXTERIOR STAIRS-GRADE TO BASEMENT-MISSING HAND RAIL.',\n", " 'INTERIOR OF BUILDING - ROACH INFESTATION': 'INTERIOR OF BUILDING - MICE INFESTATION',\n", " 'WEST ELEVATION / DOWNSPOUT - MISSING': 'SOUTH ELEVATION / DOWNSPOUT - MISSING.',\n", " 'MISSING CARBON MONOXIDE DETECTOR.': 'MISSING CARBOB MONOXIDE DETECTOR',\n", " 'MISSING SMOKE DETECTOR.': 'MISSING SMOKE DETECTOR',\n", " 'EMPLOY LICENSED AND BONDED PLUMBING CONTRACTOR FOR PLUMBING WORK TO BE PERFORMED TO ADDRESS PLUMBING VIOLATIONS.': 'EMPLOY LICENSED AND BONDED PLUMBING CONTRACTOR FOR PLUMBING WORK TO BE PERFORMED TO ADDRESS PLUMBING VIOLATIONS',\n", " 'CHIMNEY - FRACTURES WITH LOOSE MORTAR': 'CHIMNEY - FRACTURES ,LOOSE MORTAR',\n", " 'CHIMNEY - FRACTURES AND LOOSE MORTAR': 'CHIMNEY - FRACTURES ,LOOSE MORTAR',\n", " 'INTERIOR OF BUILDING /NO RESPONSE. UNVERIFIED DETECTORS AND CONDITIONS. UNVERIFIED OCCUPANCY.': 'INTERIOR OF BUILDING /NO RESPONSE. UNVERIFIED DETECTORS AND CONDITIONS. UNVERFIED OCCUPANCY.',\n", " 'BUILDING INFESTED WITH ROACHES, INCLUDING APT. 2E.': 'BUILDING INFESTED WITH MICE, INCLUDING APT. 2E.',\n", " 'STOP WORK UNTIL A PERMIT IS OBTAINED SECTION 14A-4-411': 'STOP WORK UNTIL PERMIT IS OBTAINED SECTION 14A-4-411',\n", " 'EAST PASSENGER: REPAIR EMERGENCY PHONE': 'EAST PASSENGER: REPAIR EMERGENCY ALARM',\n", " 'ERECTING NEW INTERIOR PARTITIONS': 'ERECTED NEW INTERIOR PARTITIONS',\n", " 'APT 2R / CARBON MONOXIDE - MISSING . PER SECOND CODE 14X-5-504.9, 14B-9-915.1.1, 14B-9-915.2 , AND 14R-11-1105)': 'APT 1 / CARBON MONOXIDE - MISSING . PER SECTION CODE 14X-5-504.9, 14B-9-915.1.1, 14B-9-915.2 , AND 14R-11-1105)',\n", " '#15 & #16 ELEVATORS MUST BE REPRESENTED ON THE ELEVATOR CONTROL PANEL (LOBBY PANEL) INCLUDING AUXILARY PHASE I KEY SWITCH(TO RECALL #15, #16, AND #17 ALL SHARING THE SAME MACHINE ROOM), POSITION INDICATOR, INDICATOR OF OPERATING MODES, AND REPRESENTATION ON THE ETCHED DEPICTION (BUILDING LAYOUT MAP) OF THE BUILDING WITH ALL OF THE ELEVATORS WITHIN': '#15 & #16 ELEVATORS MUST BE REPRESENTED ON THE ELEVATOR CONTROL PANEL (LOBBY PANEL) INCLUDING AUXILARY PHASE I KEY SWITCH(TO RECALL #15, #16, AND #17 ALL SHARING THE SAME MACHINE ROOM), POSITION INDICATOR, INDICATOR OF OPERATING MODES, AND REEPRESENTATION ON THE ETCHED DEPICTION (BUILDING LAYOUT MAP) OF THE BUILDING WITH ALL OF THE ELEVATORS WITHIN.',\n", " 'CAT. 5 TEST OVERDUE.': 'CAT. 1 TEST OVERDUE.',\n", " 'NEXT CATEGORY 5 TEST DUE BY 06/02/2022.': 'NEXT CATEGORY 1 TEST DUE BY 02/04/2022.',\n", " 'WEST CORNICE - WASHED OUT MORTAR.': 'NORTH CORNICE - WASHED OUT MORTAR.',\n", " 'SOUTH ELEVATION / CORNICE - OPEN MORTAR JOINT.': 'SOUTH ELEVATION / COPING - OPEN MORTAR JOINTS.',\n", " 'WORKING DOOR RESTRICTOR REQIRED.': 'WORKING DOOR RESTRICTOR REQUIRED.',\n", " 'WORKING PIT LIGHTING AND AN ACCESSIBLE LIGHT SWITCH IS REQUIRED.': 'WORKING PIT LIGHTING AND ACCESSIBLE LIGHT SWITCH IS REQUIRED.',\n", " 'REPAIR EMERGENCY PHONES': 'REPAIR EMERGENCY PHONE.',\n", " 'SOUTH ELEVATION / WINDOW SILLS - OPEN MORTAR JOINTS.': 'SOUTH ELEVATION / WINDOW SILLS - OPEN MORTAR JOINT.',\n", " 'STENCIL PIT FLOOR INFRONT OF COUNTERWEIGHT/PROVIDE SIGNAGE ON PIT WALL RUNBY/DANGER': 'STENCIL PIT FLOOR IN FROT OF COUNTERWEIGHT/PROVIDE SIGNAGE ON PIT WALL RUNBY/DANGER',\n", " 'STOP ALL WORK ON PREMISES UNTIL APPROVED PLANS AND BUILDING PERMIT AE OBTAINED 13-12-080 13-32-035': 'STOP ALL WORK ON PREMISES UNTIL APPROVED PLANS AND BUILDING PERMIT ARE OBTAINED 13-12-080 13-32-035',\n", " 'CODE DATA TAG ON CONTROLLER': 'CODE DATA TAG ON CONTROLER',\n", " 'ELEVATORS #4 & #5: PERFORM CAT1 & CAT5 TEST. PROVIDE TEST SHEET AND TAGS ONCE COMPLETED.': 'ELEVATORS #4 & #5: PERFORM CAT! & CAT5 TEST. PROVIDE TEST SHEET AND TAGS ONCE COMPLETED.',\n", " 'WORKING PIT LIGHTING AND ACCESSIBLE LIGHT SWITCH REQUIRED.': 'WORKING PIT LIGHTING AND ACCESSIBLE LIGHT SWITCH IS REQUIRED.',\n", " 'STOP ALL WORK ON PREMISES UNTIL A BUILDING PERMIT IS OBTAINED 14A-3-306-4 14A-4-411-1': 'STOP ALL WORK ON PREMISES UNTIL A BUILDING PERMIT IS OBTAINED 14A-3-306-1 14A-4-411-1',\n", " 'PERFORM CAT5 SAFETY TEST AND LEAVE CITY DOCUMENTATION.': 'PERFORM CAT1 SAFETY TEST AND LEAVE CITY DOCUMENTATION',\n", " 'PROVIDE MAINTENANCE CONTROL PROGRAM.': 'PROVIDE MAINTENANCE CONTROL PROGRAM',\n", " 'STOP WORK UNTIL NEW PERMIT IS OBTAINED SECTION 14A-4-411': 'STOP WORK UNTIL A PERMIT IS OBTAINED SECTION 14A-4-411',\n", " 'STOVE HEAT/SPACE HEATER.': 'STOVE HEAT/SPACE HEATERS.',\n", " 'WEST ELEVATION / EXTERIOR WALL - MASONRY FRACTURES.': 'NORTH ELEVATION / EXTERIOR WALL - MASONRY FRACTURES.',\n", " 'PERFORM CAT5 SAFETY TEST LEAVE CITY OF CHICAGO DOCUMENTATION.TRACTION ELEVATOR.': 'PERFORM CAT1 SAFETY TEST LEAVE CITY OF CHICAGO DOCUMENTATION.TRACTION ELEVATOR.',\n", " 'CHIMNEY - MISSING CAP': 'CHIMNEY - MISSING CAP',\n", " 'WEST WINDOW SILLS - WASHED OUT MORTAR.': 'EAST WINDOW SILLS - WASHED OUT MORTAR.',\n", " 'REAR YARD - ABANDONED CARS': 'REAR YARD - ABANADONED CAR.',\n", " 'AT BASEMENT.': 'AT BASEMENT',\n", " 'USING COOKING STOVE AS HEATING DEVICE': 'USING COOKING STOVE AS HEATING DEVICE.',\n", " '(PREVIOUSLY CITED ON 9/7/16) CODE DATA PLATE REQUIRED ON CONTROLLER. BOTH ELEVATORS.': '(PREVIOUSLY CITED ON 8/14/18) CODE DATA PLATE REQUIRED ON CONTROLLER. BOTH ELEVATORS',\n", " 'A CODE COMPLIANT PIT LADDER IS REQUIRED.': 'CODE COMPLIANT PIT LADDER REQUIRED.',\n", " 'CHIMNEY - FRACTURES ,LOOSE MORTAR': 'CHIMNEY - FRACTURES , LOOSE MORTAR',\n", " 'CHIMNEYS - MISSING CAPS': 'CHIMNEY - MISSING CAP',\n", " 'MISSING SMOKE DETECTOR': 'MISSING SMOKE DETECTORS.',\n", " 'PROVIDE ELECTRICAL PERMIT IN ORDER TO CORRECT ALL ELECTRICAL VIOLATIONS (14A-4-404.6)': 'PROVIDE ELECTRICAL PERMIT IN ORDER TO CORRECT ALL ELECTRICAL VIOLATIONS (14A-4-404.6)',\n", " 'AMAINTENANCE CONTROL PROGRAM IS REQUIRED TO BE ON SITE.': 'A MAINTENANCE CONTROL PROGRAM IS REQUIRED TO BE ON SITE.',\n", " 'APT 3 / SAFE WORK CONDITIONS - BOILER NOT WORKING . PER SECTION CODE (14X-3-305.1, 14X-8-804.1, 14X-8-804.4 AND 14X-8-804.5) BHA CODES': 'APT 103 / SAFE WORK CONDITIONS - BOILER NOT WORKING . PER SECTION CODE 14X-3-305.1, 14X-8-804.1, 14X-8-804.4 AND 14X-8-804.5) BHA CODES',\n", " 'APT 1W / UNAPPROVE HEATING DEVICE = ELECT HEATER USED FOR HEAT . PER SECTION CODE . (14X-7-705.4 BHA CODES': 'APT 103 / UNAPPROVE HEATING DEVICE - ELECT HEATER USED FOR HEAT . PER SECTION CODE . (14X-7-705.4 BHA CODES',\n", " 'REAR PORCHES - STORING BBQ GRILLS.': 'REAR PORCH - STORING BBQ GRILLS.',\n", " '914 / APT 1 / SMOKE DETECTOR - MISSING. CB197019 - SMOKE ALARMS / DETECTORS - INSTALL (14X-5-504.8, 14X-5-504.8.4,14B-9-907.2.10.1 THROUGH 14B-9-907.2.10.7, AND 14R-11-1104)': '2343 / APT 1 / SMOKE DETECTOR - MISSING. CB197019 - SMOKE ALARMS / DETECTORS - INSTALL (14X-5-504.8, 14X-5-504.8.4,14B-9-907.2.10.1 THROUGH 14B-9-907.2.10.7, AND 14R-11-1104)'},\n", " 51,\n", " 116)" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "renamedict = {}\n", "n=0\n", "for i in range(len(distinctcomments)):\n", " for j in range(len(distinctcomments)):\n", " d = distance(distinctcomments[i], distinctcomments[j])\n", " # Find all the pairs that have Levenshtein distance <=5 \n", " if d <= 5 and d !=0 :\n", " n = n+1\n", " # If comment i is used fewer times than j \n", " if distinctcommentnumber[i] < distinctcommentnumber[j] : \n", " # Flag comment i to be changed to comment j \n", " renamedict[distinctcomments[i]] = distinctcomments[j]\n", " # If two comments have the same number of counts:\n", " if distinctcommentnumber[i] == distinctcommentnumber[j]:\n", " # Rename if j comes first in lexicographic (alphabetical) order:\n", " if distinctcomments[j] < distinctcomments[i]:\n", " renamedict[distinctcomments[i]] = distinctcomments[j]\n", "renamedict, len(renamedict), n" ] }, { "cell_type": "markdown", "id": "c30449a8-d898-4fe0-9084-95b4608f3a5b", "metadata": {}, "source": [ "This is more believable. The 116 pairs should represent 56 real pairs of comments, and some comments might have more than one partner, so there are only 51 renaming rules.\n", "\n", "Let us apply this (larger!) comment-renaming fix, and see if it puts a larger dent in the number of unique comments:" ] }, { "cell_type": "code", "execution_count": 25, "id": "90b93a1f-8c56-4fe2-b831-2b6ba5f5d24f", "metadata": {}, "outputs": [], "source": [ "violations_df[\"COMMENTS FIXED2\"] = violations_df[\"VIOLATION INSPECTOR COMMENTS\"].apply(renamecomment)" ] }, { "cell_type": "code", "execution_count": 26, "id": "6d5ddc75-5f91-41f9-ac7b-7a5174b433db", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Records: 1832\n", "Distinct records: 1580\n", "Distinct records fixed: 1533\n" ] } ], "source": [ "print(\"Records: \", len(violations_df))\n", "print(\"Distinct records: \", len(violations_df[\"VIOLATION INSPECTOR COMMENTS\"].value_counts()) ) \n", "print(\"Distinct records fixed:\", len(violations_df[\"COMMENTS FIXED2\"].value_counts()))" ] }, { "cell_type": "markdown", "id": "715c94ce-1ec6-424f-b8db-3957662ba7d2", "metadata": {}, "source": [ "Now, although there were 51 comments slated to be renamed, it only reduced the number of unique comments\n", "from 1580 to 1544 (by 47). The comments are still 5.3% not-a-number, but now 11.0% have non-unique comments." ] }, { "cell_type": "markdown", "id": "54fa0104-6a57-42b5-8608-6287cd54e79d", "metadata": {}, "source": [ "So we were able to \"clean up\" 54 out of 1832 (2.9%) of the comment records. The effect on the unique comments \n", "is a bit more dramatic than the effect on distinct comments. Unique comments dropped from " ] }, { "cell_type": "code", "execution_count": 27, "id": "27454ee7-7825-4edc-956a-38c961146174", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Unique comments before 1451\n", "Unique comments after 1375\n", "Out of 1832\n" ] } ], "source": [ "print (\"Unique comments before\", violations_df[\"VIOLATION INSPECTOR COMMENTS\"].value_counts().value_counts()[1])\n", "print (\"Unique comments after \", violations_df[\"COMMENTS FIXED2\"].value_counts().value_counts()[1])\n", "print (\"Out of \", len(violations_df))" ] }, { "cell_type": "markdown", "id": "c4ee9971-851a-4b95-9d1e-4fa8e5825805", "metadata": {}, "source": [ "The renaming-cleaning procedure reduced the fraction of the data in unique-comment categories by 4%. These are records \n", "that can presumably be better matched with other rows with similar content." ] }, { "cell_type": "markdown", "id": "46c8c49a-6006-48d4-bbb3-d348dfcdf90c", "metadata": {}, "source": [ "## Clean data" ] }, { "cell_type": "markdown", "id": "ab8d75bd-efbf-43d7-8365-8cc77b99efb8", "metadata": {}, "source": [ "This exercise tried to clean up (meaningless) typographic differences in comment fields; in real-world problems, you are likely to encounter two databases with sometimes-matching and sometimes-mismatching data on more than one field. You might have a database of people from the HR department and another from the directory, or might have a database of addresses from a municipal database and a commercial database. \n", "\n", "Some fields match more reliably than others; if your job is to somehow retrieve the most likely N matches to a test query, your solution will likely try to match on high-quality identifiers first (national ID numbers for people, FIPS codes for places, transaction ID numbers where appropriate) and attempt fuzzy matching only after exhausting the pool of more-likely-correct hits on identifiers, using a score that is a mixture of fuzzy-matching goodness-of-match scores from different fields. " ] } ], "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.11.5" } }, "nbformat": 4, "nbformat_minor": 5 }