{ "cells": [ { "attachments": {}, "cell_type": "markdown", "id": "05195b87-9d49-4afd-9a01-6185e7391918", "metadata": {}, "source": [ "# Getting Started\n", "Welcome to the quickstart guide for OpenPoliceData (OPD)! Here, you should find all you need to learn the basics of OPD.\n", "\n", "* **New to Python?**: Check out the free [first python notebook](https://firstpythonnotebook.org/) course and/or the VS Code Python [Quick Start Guide](https://code.visualstudio.com/docs/python/python-quick-start) and [Tutorial](https://code.visualstudio.com/docs/python/python-tutorial).\n", "\n", "* **Questions or Comments?**: If you questions or comments about anything related to installing or using OPD, please reach out on our [discussion board](https://github.com/openpolicedata/openpolicedata/discussions).\n", "\n", "\n", "## Installation\n", "Install OPD with pip from [PyPI](https://pypi.org/project/openpolicedata/)\n", "\n", "```bash\n", "pip install openpolicedata\n", "```\n", "For installation in a Jupyter Notebook, replace `pip` with `%pip`. \n", "\n", "See [here](installation.rst) for advanced installation including how to install [GeoPandas](https://geopandas.org/en/stable/index.html) alongside OPD to enable geospatial analysis of data loaded by OPD.\n", "\n", "## Import\n", "To use OPD, you must always start by importing it into your Python code:" ] }, { "cell_type": "code", "execution_count": 1, "id": "6eb80fff", "metadata": { "nbsphinx": "hidden" }, "outputs": [], "source": [ "# This cell should have \"nbsphinx\": \"hidden\" in its metadata and not be included in the documentation!\n", "import sys\n", "sys.path.append(\"../../../\")" ] }, { "cell_type": "code", "execution_count": 2, "id": "ac629e9e-439d-4197-a88c-f467c7cfb7a9", "metadata": {}, "outputs": [], "source": [ "import openpolicedata as opd" ] }, { "attachments": {}, "cell_type": "markdown", "id": "9b736829-ac81-4629-bf72-f83f79561e91", "metadata": {}, "source": [ "We recommend shortening openpolicedata to `opd` to make your code more readable. \n", "\n", "## The Basics\n", "OPD provides access to over 550 police datasets with just 2 simple lines of code (in most cases):" ] }, { "cell_type": "code", "execution_count": 5, "id": "ebfaf449", "metadata": {}, "outputs": [], "source": [ "# Load traffic stops data from New Orleans for the year 2022.\n", "src = opd.Source(\"New Orleans\")\n", "tbl = src.load(table_type=\"STOPS\", date=2022)" ] }, { "attachments": {}, "cell_type": "markdown", "id": "78547a7e", "metadata": {}, "source": [ "The table attribute contains the loaded data as a [pandas DataFrame](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html) so it can be analyzed with [pandas' simple and powerful capabilities](https://pandas.pydata.org/docs/user_guide/10min.html)." ] }, { "cell_type": "code", "execution_count": 6, "id": "6051c91f", "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", "
fieldinterviewidnopd_itemeventdatedistrictzoneofficerassignmentstopdescriptionvehiclemakevehiclemodelvehiclestyle...subjectidsubjectracesubjectgendersubjectagesubjectheightsubjectweightsubjecteyecolorsubjecthaircolorsubjectdriverlicstatezip
0576759A03429212022-01-04 00:46:001I1st DistrictSUSPECT VEHICLEINFINITYOTHERSPORTS UTILITY...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
1576758A03406222022-01-04 00:00:008C8th DistrictTRAFFIC VIOLATIONHONDAOTHERSPORTS UTILITY...661974BLACKMALE2171170BrownBlackLA70130
2576760A03462222022-01-04 01:38:007H7th DistrictTRAFFIC VIOLATIONGMC - GENERAL MOTORS CORP.OTHERSPORTS UTILITY...661975BLACKFEMALE3764240BrownBlackTXNaN
3576761A03499222022-01-04 02:25:007I7th DistrictCITIZEN CONTACTNaNNaNNaN...661976BLACKMALE5869158BrownBlackNaN70128
4576762A03532222022-01-04 03:30:008G8th DistrictTRAFFIC VIOLATIONOTHEROTHERFOUR DOOR...661977BLACKMALE2074140BrownBlackLA70130
\n", "

5 rows × 29 columns

\n", "
" ], "text/plain": [ " fieldinterviewid nopd_item eventdate district zone \\\n", "0 576759 A0342921 2022-01-04 00:46:00 1 I \n", "1 576758 A0340622 2022-01-04 00:00:00 8 C \n", "2 576760 A0346222 2022-01-04 01:38:00 7 H \n", "3 576761 A0349922 2022-01-04 02:25:00 7 I \n", "4 576762 A0353222 2022-01-04 03:30:00 8 G \n", "\n", " officerassignment stopdescription vehiclemake \\\n", "0 1st District SUSPECT VEHICLE INFINITY \n", "1 8th District TRAFFIC VIOLATION HONDA \n", "2 7th District TRAFFIC VIOLATION GMC - GENERAL MOTORS CORP. \n", "3 7th District CITIZEN CONTACT NaN \n", "4 8th District TRAFFIC VIOLATION OTHER \n", "\n", " vehiclemodel vehiclestyle ... subjectid subjectrace subjectgender \\\n", "0 OTHER SPORTS UTILITY ... NaN NaN NaN \n", "1 OTHER SPORTS UTILITY ... 661974 BLACK MALE \n", "2 OTHER SPORTS UTILITY ... 661975 BLACK FEMALE \n", "3 NaN NaN ... 661976 BLACK MALE \n", "4 OTHER FOUR DOOR ... 661977 BLACK MALE \n", "\n", " subjectage subjectheight subjectweight subjecteyecolor subjecthaircolor \\\n", "0 NaN NaN NaN NaN NaN \n", "1 21 71 170 Brown Black \n", "2 37 64 240 Brown Black \n", "3 58 69 158 Brown Black \n", "4 20 74 140 Brown Black \n", "\n", " subjectdriverlicstate zip \n", "0 NaN NaN \n", "1 LA 70130 \n", "2 TX NaN \n", "3 NaN 70128 \n", "4 LA 70130 \n", "\n", "[5 rows x 29 columns]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# View the 1st 5 rows with pandas' head function\n", "tbl.table.head()" ] }, { "attachments": {}, "cell_type": "markdown", "id": "262b0614", "metadata": {}, "source": [ "## Finding Datasets\n", "OPD provides the `datasets` module for querying what datasets are available in OPD. To get all available datasets, query the source table with no inputs:" ] }, { "cell_type": "code", "execution_count": 7, "id": "c39721a9", "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", "
StateSourceNameAgencyAgencyFullTableTypecoverage_startcoverage_endlast_coverage_checkYearagency_originated...source_urlreadmeURLDataTypedate_fielddataset_idagency_fieldmin_versionpy_min_versionquery
0ArizonaChandlerChandlerChandler Police DepartmentARRESTS2018-01-012025-05-3005/31/2025MULTIPLENaN...https://data.chandlerpd.com/catalog/arrest-boo...<NA>https://data.chandlerpd.com/catalog/arrest-boo...CSVarrest_date_timeNaN<NA>0.2<NA>NaN
1ArizonaChandlerChandlerChandler Police DepartmentCALLS FOR SERVICE2018-01-012025-05-3105/31/2025MULTIPLENaN...https://data.chandlerpd.com/catalog/calls-for-...<NA>https://data.chandlerpd.com/catalog/calls-for-...CSVcall_received_date_timeNaN<NA><NA><NA>NaN
2ArizonaChandlerChandlerChandler Police DepartmentINCIDENTS2018-01-012025-05-2405/31/2025MULTIPLENaN...https://data.chandlerpd.com/catalog/general-of...<NA>https://data.chandlerpd.com/catalog/general-of...CSVreport_event_dateNaN<NA>0.4.1<NA>NaN
3ArizonaGilbertGilbertGilbert Police DepartmentCALLS FOR SERVICE2006-11-152025-02-2405/31/2025MULTIPLENaN...https://data.gilbertaz.gov/maps/2dcb4c20c9a444...<NA>https://maps.gilbertaz.gov/arcgis/rest/service...ArcGISEventDateNaN<NA><NA><NA>NaN
4ArizonaGilbertGilbertGilbert Police DepartmentEMPLOYEENaTNaT07/06/2023NONENaN...https://data.gilbertaz.gov/datasets/TOG::gilbe...<NA>https://services1.arcgis.com/JLuzSHjNrLL4Okwb/...ArcGIS<NA>NaN<NA><NA><NA>{“Department”: “POLICE DEPARTMENT”}
\n", "

5 rows × 22 columns

\n", "
" ], "text/plain": [ " State SourceName Agency AgencyFull \\\n", "0 Arizona Chandler Chandler Chandler Police Department \n", "1 Arizona Chandler Chandler Chandler Police Department \n", "2 Arizona Chandler Chandler Chandler Police Department \n", "3 Arizona Gilbert Gilbert Gilbert Police Department \n", "4 Arizona Gilbert Gilbert Gilbert Police Department \n", "\n", " TableType coverage_start coverage_end last_coverage_check \\\n", "0 ARRESTS 2018-01-01 2025-05-30 05/31/2025 \n", "1 CALLS FOR SERVICE 2018-01-01 2025-05-31 05/31/2025 \n", "2 INCIDENTS 2018-01-01 2025-05-24 05/31/2025 \n", "3 CALLS FOR SERVICE 2006-11-15 2025-02-24 05/31/2025 \n", "4 EMPLOYEE NaT NaT 07/06/2023 \n", "\n", " Year agency_originated ... \\\n", "0 MULTIPLE NaN ... \n", "1 MULTIPLE NaN ... \n", "2 MULTIPLE NaN ... \n", "3 MULTIPLE NaN ... \n", "4 NONE NaN ... \n", "\n", " source_url readme \\\n", "0 https://data.chandlerpd.com/catalog/arrest-boo... \n", "1 https://data.chandlerpd.com/catalog/calls-for-... \n", "2 https://data.chandlerpd.com/catalog/general-of... \n", "3 https://data.gilbertaz.gov/maps/2dcb4c20c9a444... \n", "4 https://data.gilbertaz.gov/datasets/TOG::gilbe... \n", "\n", " URL DataType \\\n", "0 https://data.chandlerpd.com/catalog/arrest-boo... CSV \n", "1 https://data.chandlerpd.com/catalog/calls-for-... CSV \n", "2 https://data.chandlerpd.com/catalog/general-of... CSV \n", "3 https://maps.gilbertaz.gov/arcgis/rest/service... ArcGIS \n", "4 https://services1.arcgis.com/JLuzSHjNrLL4Okwb/... ArcGIS \n", "\n", " date_field dataset_id agency_field min_version py_min_version \\\n", "0 arrest_date_time NaN 0.2 \n", "1 call_received_date_time NaN \n", "2 report_event_date NaN 0.4.1 \n", "3 EventDate NaN \n", "4 NaN \n", "\n", " query \n", "0 NaN \n", "1 NaN \n", "2 NaN \n", "3 NaN \n", "4 {“Department”: “POLICE DEPARTMENT”} \n", "\n", "[5 rows x 22 columns]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "all_datasets = opd.datasets.query()\n", "all_datasets.head()" ] }, { "attachments": {}, "cell_type": "markdown", "id": "b682fcda", "metadata": {}, "source": [ "The source table provides the information needed to create sources and load data as well as background information about each dataset. It is a DataFrame that can be filtered with [pandas filtering operations](https://pandas.pydata.org/docs/getting_started/intro_tutorials/03_subset_data.html#min-tut-03-subset). Key information includes:\n", "\n", " * **State**: Optionally used when creating a `Source` to distinguish ambiguous sources (i.e. same city name in different states)\n", " * **SourceName**: Original source of the data (typically a shortened name of a police department). Used when creating a `Source`.\n", " * **Agency**: Shortened agency / police department name. Typically the same as SourceName. Value is `MULTIPLE` if a datasets contains data for multiple agencies.\n", " * **TableType**: Type of data (TRAFFIC STOPS, USE OF FORCE, etc.). Used when loading data.\n", " * **coverage_start**: Start date of data contained in dataset. Combined with coverage_end, this determines the years available for this datasets when loading data. NOTE: Often, agencies store their data in different datasets for different years so one table type may be spread across multiple datasets corresponding to each year of data.\n", " * **coverage_end**: End date of data contained in dataset at the time of the msot recent update. Combined with coverage_start, this determines the years available for this datasets when loading data. If the data has been updated by the dataset owner since the date in `last_coverage_check`, more recent data may be available. NOTE: Often, agencies store their data in different datasets for different years so one table type may be spread across multiple datasets corresponding to each year of data.\n", " * **source_url**: Homepage for dataset\n", " * **readme**: URL for data dictionary containing definitions of columns, etc. If empty, the `source_url` may also contain a data dictionary.\n", "\n", "**See the [Data Source Table Dictioanry](../datasets/source_table_dictionary.ipynb) for a description of all fields in the data source table.**\n", "\n", "With its optional inputs, `query` can be used to filter for desired data. Here is a very specific query using all optional inputs:" ] }, { "cell_type": "code", "execution_count": 8, "id": "e67f90d8-7008-4878-b3bb-99ead5653fa2", "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", "
StateSourceNameAgencyAgencyFullTableTypecoverage_startcoverage_endlast_coverage_checkYearagency_originated...source_urlreadmeURLDataTypedate_fielddataset_idagency_fieldmin_versionpy_min_versionquery
187CaliforniaMenlo ParkMenlo ParkMenlo Park Police DepartmentCALLS FOR SERVICE2018-01-012018-12-3107/06/20232018NaN...https://data.menlopark.gov/datasets/MenloPark:...<NA>https://services7.arcgis.com/uRrQ0O3z2aaiIWYU/...ArcGIS<NA>NaN<NA><NA><NA>NaN
188CaliforniaMenlo ParkMenlo ParkMenlo Park Police DepartmentCALLS FOR SERVICE2019-01-012019-12-3107/06/20232019NaN...https://data.menlopark.gov/datasets/MenloPark:...<NA>https://services7.arcgis.com/uRrQ0O3z2aaiIWYU/...ArcGIS<NA>NaN<NA><NA><NA>NaN
189CaliforniaMenlo ParkMenlo ParkMenlo Park Police DepartmentCALLS FOR SERVICE2020-01-012020-12-3107/06/20232020NaN...https://data.menlopark.gov/datasets/MenloPark:...<NA>https://services7.arcgis.com/uRrQ0O3z2aaiIWYU/...ArcGIS<NA>NaN<NA><NA><NA>NaN
190CaliforniaMenlo ParkMenlo ParkMenlo Park Police DepartmentCALLS FOR SERVICE2021-01-012021-12-3107/06/20232021NaN...https://data.menlopark.gov/datasets/MenloPark:...<NA>https://services7.arcgis.com/uRrQ0O3z2aaiIWYU/...ArcGIS<NA>NaN<NA><NA><NA>NaN
191CaliforniaMenlo ParkMenlo ParkMenlo Park Police DepartmentCALLS FOR SERVICE2022-01-012022-12-3104/06/20252022NaN...https://data.menlopark.gov/datasets/MenloPark:...<NA>https://services7.arcgis.com/uRrQ0O3z2aaiIWYU/...ArcGIS<NA>NaN<NA><NA><NA>NaN
192CaliforniaMenlo ParkMenlo ParkMenlo Park Police DepartmentCALLS FOR SERVICE2023-01-012023-12-3104/06/20252023NaN...https://data.menlopark.gov/datasets/MenloPark:...<NA>https://services7.arcgis.com/uRrQ0O3z2aaiIWYU/...ArcGIS<NA>NaN<NA><NA><NA>NaN
\n", "

6 rows × 22 columns

\n", "
" ], "text/plain": [ " State SourceName Agency AgencyFull \\\n", "187 California Menlo Park Menlo Park Menlo Park Police Department \n", "188 California Menlo Park Menlo Park Menlo Park Police Department \n", "189 California Menlo Park Menlo Park Menlo Park Police Department \n", "190 California Menlo Park Menlo Park Menlo Park Police Department \n", "191 California Menlo Park Menlo Park Menlo Park Police Department \n", "192 California Menlo Park Menlo Park Menlo Park Police Department \n", "\n", " TableType coverage_start coverage_end last_coverage_check Year \\\n", "187 CALLS FOR SERVICE 2018-01-01 2018-12-31 07/06/2023 2018 \n", "188 CALLS FOR SERVICE 2019-01-01 2019-12-31 07/06/2023 2019 \n", "189 CALLS FOR SERVICE 2020-01-01 2020-12-31 07/06/2023 2020 \n", "190 CALLS FOR SERVICE 2021-01-01 2021-12-31 07/06/2023 2021 \n", "191 CALLS FOR SERVICE 2022-01-01 2022-12-31 04/06/2025 2022 \n", "192 CALLS FOR SERVICE 2023-01-01 2023-12-31 04/06/2025 2023 \n", "\n", " agency_originated ... source_url \\\n", "187 NaN ... https://data.menlopark.gov/datasets/MenloPark:... \n", "188 NaN ... https://data.menlopark.gov/datasets/MenloPark:... \n", "189 NaN ... https://data.menlopark.gov/datasets/MenloPark:... \n", "190 NaN ... https://data.menlopark.gov/datasets/MenloPark:... \n", "191 NaN ... https://data.menlopark.gov/datasets/MenloPark:... \n", "192 NaN ... https://data.menlopark.gov/datasets/MenloPark:... \n", "\n", " readme URL DataType \\\n", "187 https://services7.arcgis.com/uRrQ0O3z2aaiIWYU/... ArcGIS \n", "188 https://services7.arcgis.com/uRrQ0O3z2aaiIWYU/... ArcGIS \n", "189 https://services7.arcgis.com/uRrQ0O3z2aaiIWYU/... ArcGIS \n", "190 https://services7.arcgis.com/uRrQ0O3z2aaiIWYU/... ArcGIS \n", "191 https://services7.arcgis.com/uRrQ0O3z2aaiIWYU/... ArcGIS \n", "192 https://services7.arcgis.com/uRrQ0O3z2aaiIWYU/... ArcGIS \n", "\n", " date_field dataset_id agency_field min_version py_min_version query \n", "187 NaN NaN \n", "188 NaN NaN \n", "189 NaN NaN \n", "190 NaN NaN \n", "191 NaN NaN \n", "192 NaN NaN \n", "\n", "[6 rows x 22 columns]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ds = opd.datasets.query(source_name=\"Menlo Park\", state=\"California\", agency=\"Menlo Park\", table_type=\"CALLS FOR SERVICE\")\n", "ds" ] }, { "attachments": {}, "cell_type": "markdown", "id": "d9d7023e-40ac-4e55-81d4-9f1efb9ec2db", "metadata": {}, "source": [ "`get_table_types` finds available table types in OPD. Here, we use optional `contains` input to only get the table types containing the word \"STOPS\":" ] }, { "cell_type": "code", "execution_count": 9, "id": "4d0a3fbd-be70-4717-8705-3339434a1c1f", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "['PEDESTRIAN STOPS',\n", " 'STOPS',\n", " 'STOPS - INCIDENTS',\n", " 'STOPS - SUBJECTS',\n", " 'TRAFFIC STOPS',\n", " 'TRAFFIC STOPS - INCIDENTS',\n", " 'TRAFFIC STOPS - SUBJECTS']" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "table_types = opd.datasets.get_table_types(contains=\"STOPS\")\n", "table_types" ] }, { "attachments": {}, "cell_type": "markdown", "id": "c9075d6b-eb04-4760-8de9-1ff4f289dbb9", "metadata": {}, "source": [ "## Loading Data\n", "The `Source` class is used to explore datasets and load data. We first need to create a source, which we can use to view all datasets from that source. Let's create a source of Columbia, South Carolina. We need to specify the state because there are datasets from Columbias from multiple states" ] }, { "cell_type": "code", "execution_count": 10, "id": "7f8680d3-ee9a-413f-935c-1eb50a4c8c50", "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", "
StateSourceNameAgencyAgencyFullTableTypecoverage_startcoverage_endlast_coverage_checkYearagency_originated...source_urlreadmeURLDataTypedate_fielddataset_idagency_fieldmin_versionpy_min_versionquery
1368South CarolinaColumbiaColumbiaColumbia Police DepartmentARRESTS2016-01-012024-12-3106/01/2025MULTIPLENaN...https://coc-colacitygis.opendata.arcgis.com/da...<NA>https://services1.arcgis.com/Mnt8FoJcogKtoVBs/...ArcGISArrest_DateNaN<NA>0.2<NA>NaN
1369South CarolinaColumbiaColumbiaColumbia Police DepartmentFIELD CONTACTS2016-01-012024-12-3106/01/2025MULTIPLENaN...https://coc-colacitygis.opendata.arcgis.com/da...<NA>https://services1.arcgis.com/Mnt8FoJcogKtoVBs/...ArcGISTOCNaN<NA><NA><NA>NaN
\n", "

2 rows × 22 columns

\n", "
" ], "text/plain": [ " State SourceName Agency AgencyFull \\\n", "1368 South Carolina Columbia Columbia Columbia Police Department \n", "1369 South Carolina Columbia Columbia Columbia Police Department \n", "\n", " TableType coverage_start coverage_end last_coverage_check \\\n", "1368 ARRESTS 2016-01-01 2024-12-31 06/01/2025 \n", "1369 FIELD CONTACTS 2016-01-01 2024-12-31 06/01/2025 \n", "\n", " Year agency_originated ... \\\n", "1368 MULTIPLE NaN ... \n", "1369 MULTIPLE NaN ... \n", "\n", " source_url readme \\\n", "1368 https://coc-colacitygis.opendata.arcgis.com/da... \n", "1369 https://coc-colacitygis.opendata.arcgis.com/da... \n", "\n", " URL DataType date_field \\\n", "1368 https://services1.arcgis.com/Mnt8FoJcogKtoVBs/... ArcGIS Arrest_Date \n", "1369 https://services1.arcgis.com/Mnt8FoJcogKtoVBs/... ArcGIS TOC \n", "\n", " dataset_id agency_field min_version py_min_version query \n", "1368 NaN 0.2 NaN \n", "1369 NaN NaN \n", "\n", "[2 rows x 22 columns]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "src = opd.Source(\"Columbia\", state=\"South Carolina\")\n", "src.datasets" ] }, { "attachments": {}, "cell_type": "markdown", "id": "cbf410b5", "metadata": {}, "source": [ "To get a list of available table types:" ] }, { "cell_type": "code", "execution_count": 11, "id": "b8dc350c", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "['ARRESTS', 'FIELD CONTACTS']" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "src.get_tables_types()" ] }, { "attachments": {}, "cell_type": "markdown", "id": "79d8047b", "metadata": {}, "source": [ "You can get the number of records for a dataset using `get_count`. Let's get the number of records in the year 2022 for the FIELD CONTACTS dataset." ] }, { "cell_type": "code", "execution_count": 12, "id": "edafbd84", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "4764" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "src.get_count(\"FIELD CONTACTS\", 2022)" ] }, { "attachments": {}, "cell_type": "markdown", "id": "8c07a6c1", "metadata": {}, "source": [ "You can find which years are available for a given table type:" ] }, { "cell_type": "code", "execution_count": 13, "id": "086ba8f4", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[2016, 2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "src.get_years(table_type=\"FIELD CONTACTS\")" ] }, { "attachments": {}, "cell_type": "markdown", "id": "b2d809bb-c661-439a-a378-81dc18fccfe5", "metadata": {}, "source": [ "Now, let's load in some field contacts data for 2022." ] }, { "cell_type": "code", "execution_count": 14, "id": "c86f7b24", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ " \r" ] }, { "data": { "text/plain": [ "src_obj: State SourceName Agency AgencyFull \\\n", "1368 South Carolina Columbia Columbia Columbia Police Department \n", "1369 South Carolina Columbia Columbia Columbia Police Department \n", "\n", " TableType coverage_start coverage_end last_coverage_check \\\n", "1368 ARRESTS 2016-01-01 2024-12-31 06/01/2025 \n", "1369 FIELD CONTACTS 2016-01-01 2024-12-31 06/01/2025 \n", "\n", " Year agency_originated ... \\\n", "1368 MULTIPLE NaN ... \n", "1369 MULTIPLE NaN ... \n", "\n", " source_url readme \\\n", "1368 https://coc-colacitygis.opendata.arcgis.com/da... \n", "1369 https://coc-colacitygis.opendata.arcgis.com/da... \n", "\n", " URL DataType date_field \\\n", "1368 https://services1.arcgis.com/Mnt8FoJcogKtoVBs/... ArcGIS Arrest_Date \n", "1369 https://services1.arcgis.com/Mnt8FoJcogKtoVBs/... ArcGIS TOC \n", "\n", " dataset_id agency_field min_version py_min_version query \n", "1368 NaN 0.2 NaN \n", "1369 NaN NaN \n", "\n", "[2 rows x 22 columns],\n", "state: South Carolina,\n", "source_name: Columbia,\n", "agency: Columbia,\n", "table_type: FIELD CONTACTS,\n", "date: 2022,\n", "description: Field Interview is a collection of data resulting from citizen contact related to suspicious activity.,\n", "url: https://services1.arcgis.com/Mnt8FoJcogKtoVBs/arcgis/rest/services/FieldInterview/FeatureServer/0,\n", "date_field: TOC,\n", "source_url: https://coc-colacitygis.opendata.arcgis.com/datasets/ColaCityGIS::field-interview-1-1-2016-3-31-2022/about,\n", "urls: {'source_url': 'https://coc-colacitygis.opendata.arcgis.com/datasets/ColaCityGIS::field-interview-1-1-2016-3-31-2022/about', 'readme': None, 'data': 'https://services1.arcgis.com/Mnt8FoJcogKtoVBs/arcgis/rest/services/FieldInterview/FeatureServer/0'}" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "tbl = src.load(\"FIELD CONTACTS\", 2022)\n", "tbl" ] }, { "attachments": {}, "cell_type": "markdown", "id": "e1ae5a67", "metadata": {}, "source": [ "The loaded data is contained in a [pandas DataFrame](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html) in the table attribute.\n", "\n", "> **NOTE**: Known date fields will automatically be converted to pandas datetime format (pandas Period in rare cases). To keep original date format, set `date_format=False` when calling `load`. With `date_format=False`, the loaded data will be *exactly* the same as the raw source data." ] }, { "cell_type": "code", "execution_count": 15, "id": "5122367e", "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", "
OBJECTIDCase_NumTOCAddressCityZipStateAgeRaceSexContact_TypeYeargeometry
0253512200001082022-01-01 21:47:0012XX Main St2920132WMField Interview2022.0POINT (1989801.776 788862.968)
1440382200001082022-01-01 21:47:0012XX Main St2920132WMField Interview2022.0POINT (1989801.776 788862.968)
\n", "
" ], "text/plain": [ " OBJECTID Case_Num TOC Address City Zip State \\\n", "0 25351 220000108 2022-01-01 21:47:00 12XX Main St 29201 \n", "1 44038 220000108 2022-01-01 21:47:00 12XX Main St 29201 \n", "\n", " Age Race Sex Contact_Type Year geometry \n", "0 32 W M Field Interview 2022.0 POINT (1989801.776 788862.968) \n", "1 32 W M Field Interview 2022.0 POINT (1989801.776 788862.968) " ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "tbl.table.head(2)" ] }, { "cell_type": "markdown", "id": "762799a5-5b99-46ca-8b68-401dfdf10100", "metadata": {}, "source": [ "To request multiple years of data, you can include the years in the \"date\" parameter in the form of `[Start Year, Stop Year]`. Date ranges can only be used for multi-year datasets.\n", "\n", "In `src.datasets` for a multi-year dataset, the column value for \"Year\" is \"MULTIPLE\", and the columns \"coverage_start\" to \"coverage_end\" specifies the dates that exist in a specific TableType data that spans multiple years. For these datasets, the \"date\" parameter can also be set to \"MULTIPLE\" to request the entire dataset. For more information on year/date filtering, see the [Date Filtering Guide](./date_filtering.ipynb)." ] }, { "cell_type": "code", "execution_count": 18, "id": "9bacf779-2fda-4aa1-a50b-bef057b6772b", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ " \r" ] }, { "data": { "text/plain": [ "Year\n", "2021.0 4786\n", "2023.0 2831\n", "2024.0 2585\n", "2022.0 984\n", "Name: count, dtype: int64" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "multiyear_tbl = src.load(\"FIELD CONTACTS\", date=[2021, 2024])\n", "multiyear_tbl.table['Year'].value_counts()" ] }, { "attachments": {}, "cell_type": "markdown", "id": "c109c211", "metadata": {}, "source": [ "Data can be saved locally as CSV, [feather](https://arrow.apache.org/docs/python/feather.html), and [parquet](https://parquet.apache.org/) files. This allows you to:\n", "\n", " * Open the data using the software of your choice\n", " * Re-open the data in OPD from a local copy" ] }, { "cell_type": "code", "execution_count": 19, "id": "de4fd694", "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", "
OBJECTIDCase_NumTOCAddressCityZipStateAgeRaceSexContact_TypeYeargeometry
025351220000108.02022-01-01 21:47:0012XX Main StNaN29201NaN32.0WMField Interview2022.0POINT (1989801.7762467265 788862.9678477645)
144038220000108.02022-01-01 21:47:0012XX Main StNaN29201NaN32.0WMField Interview2022.0POINT (1989801.7762467265 788862.9678477645)
\n", "
" ], "text/plain": [ " OBJECTID Case_Num TOC Address City Zip \\\n", "0 25351 220000108.0 2022-01-01 21:47:00 12XX Main St NaN 29201 \n", "1 44038 220000108.0 2022-01-01 21:47:00 12XX Main St NaN 29201 \n", "\n", " State Age Race Sex Contact_Type Year \\\n", "0 NaN 32.0 W M Field Interview 2022.0 \n", "1 NaN 32.0 W M Field Interview 2022.0 \n", "\n", " geometry \n", "0 POINT (1989801.7762467265 788862.9678477645) \n", "1 POINT (1989801.7762467265 788862.9678477645) " ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "tbl.to_csv() # Can also call to_feather and to_parquet with the same inputs\n", "new_src = opd.Source(\"Columbia\", state=\"South Carolina\")\n", "new_tbl = new_src.load_csv(\"FIELD CONTACTS\", 2022) # Can also call load_feather and load_parquet with the same inputs\n", "new_tbl.table.head(2)" ] }, { "cell_type": "code", "execution_count": 20, "id": "1fd16da3", "metadata": { "nbsphinx": "hidden" }, "outputs": [], "source": [ "# This cell should have nbsphinx\": \"hidden\" in its metadata and should not be included in the documentation.\n", "# Cleanup\n", "import os\n", "os.remove(\"South_Carolina_Columbia_FIELD_CONTACTS_2022.csv\")" ] }, { "attachments": {}, "cell_type": "markdown", "id": "8cd627a7", "metadata": {}, "source": [ "Some datasets contain data for every agency in a state. In this case, you may want to know what agencies are available and optionally, only want agencies containing the word Arlington." ] }, { "cell_type": "code", "execution_count": 21, "id": "dcd649e2", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "['Arlington County Police Department', \"Arlington County Sheriff's Office\"]" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "src = opd.Source(\"Virginia\")\n", "agencies = src.get_agencies(table_type=\"STOPS\", partial_name=\"Arlington\")\n", "agencies" ] }, { "attachments": {}, "cell_type": "markdown", "id": "8c8d7e82", "metadata": {}, "source": [ "We may also want only load data from a specific agency:" ] }, { "cell_type": "code", "execution_count": 22, "id": "9de443dc", "metadata": {}, "outputs": [], "source": [ "tbl = src.load(table_type=\"STOPS\", date=2022, agency=\"Arlington County Police Department\")" ] }, { "cell_type": "markdown", "id": "51aea38c", "metadata": {}, "source": [ "To request data for a range of years " ] }, { "cell_type": "markdown", "id": "361ec854", "metadata": {}, "source": [ "## Data Standardization\n", "One of the challenges in analyzing police data is that different agencies will use different column names for the same data and will use different codes and terms for the data in the columns. Particularly, if you are looking at multiple datasets, it is valuable for the data to be standardized so that you know in advance what some key columns will be called and what values will be in those columns. \n", "\n", "To provide the user with more consistent column names and data, OpenPoliceData provides powerful tools to automatically standardize column names and data in order. Columns that OpenPoliceData can standardize include:\n", "\n", "* Date\n", "* Time\n", "* Gender\n", "* Age\n", "* Race\n", "* Ethnicity \n", "\n", "In addition, OpenPoliceData will combine separate date and time columns into a single datetime column and race and ethnicity into a single combined race column.\n", "\n", "Let's examine what columns are in the Phoenix Use of Force dataset:" ] }, { "cell_type": "code", "execution_count": null, "id": "6ec227a3", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['INC_IA_NO', 'INC_IR_NO', 'EMP_BADGE_NO', 'CIT_NUMBER', 'INC_DATE',\n", " 'INC_YEAR', 'INC_HOUR', 'INC_DAY_WEEK', 'INC_LOC_COUNTY',\n", " 'HUNDRED_BLOCK', 'INC_CITY', 'INC_STATE', 'INC_ZIPCODE', 'INC_PRECINCT',\n", " 'CIT_INJURY_YN', 'CIT_GENDER', 'CIT_AGE', 'SUBJ_AGE_GROUP', 'CIT_RACE',\n", " 'CIT_ETHNICITY', 'SIMPLE_SUBJ_RE_GRP', 'SIMPLE_EMPL_RE_GRP', 'EMPL_SEX',\n", " 'CIT_RESIST_AGG_ACTIV_AGGRESSN', 'CIT_RESIST_ACTIVE_AGGRESSN',\n", " 'CIT_RESIST_ACTIVE_RESISTANCE', 'CIT_RESIST_PASSIVE_RESISTANCE',\n", " 'CIT_RESIST_PSYCH_INTIMIDATION', 'CIT_RESIST_VRBL_NONCOMPLIANCE',\n", " 'CIT_RESIST_NONE'],\n", " dtype='object')" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "src = opd.Source(\"Phoenix\")\n", "tbl = src.load(table_type=\"USE OF FORCE\", date=2022, pbar=False) # pbar=False does not show progress bar\n", "# Only showing 1st 30 due to large number of columns\n", "tbl.table.columns[:30]" ] }, { "cell_type": "markdown", "id": "6ddb81d8", "metadata": {}, "source": [ "The data has several columns related to subject demographics:\n", "- 'CIT_GENDER'\n", "- 'CIT_AGE'\n", "- 'SUBJ_AGE_GROUP'\n", "- 'CIT_RACE'\n", "- 'CIT_ETHNICITY'\n", "- 'SIMPLE_SUBJ_RE_GRP'\n", "\n", "These are not common labels used by datasets from other agencies (i.e. they cannot be predicted in advance). Additionally, when looking at them, the column labels are a bit hard to decipher because they are not all clear and are not consistent in their naming conventions. The data uses 2 different short descriptions for the same subject (CIT and SUBJ), and the *RE* in 'SIMPLE_SUBJ_RE_GRP' is for race/ethnicity so there 3 columns related to race and ethnicity. \n", "\n", "Similarly, the office demographics data uses the same *RE* abbreviation, and the user must know that *EMPL* is short for employee, which means the officer.\n", "- 'SIMPLE_EMPL_RE_GRP'\n", "- 'EMPL_SEX'\n", "\n", "OPD's data standardization will automatically identify columns and rename them to standard column names (while optionally keeping the original columns as RAW_{original name}). This enables the user to know in advance what the column names will be.\n", "\n", "Now let's examine what's in the subject race and ethnicity columns:" ] }, { "cell_type": "code", "execution_count": 20, "id": "fbe2683a", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The unique values in the race column (CIT_RACE) are ['White' 'Black' 'American Indian / Alaskan Native'\n", " 'Asian / Pacific Islander' 'Unknown' 'AmIndian']\n", "The unique values in the ethnicity column (CIT_ETHNICITY) are ['Hispanic' 'Non-Hispanic' 'Unknown']\n", "The unique values in the race ethnicity column (SIMPLE_SUBJ_RE_GRP) are ['Hispanic' 'Black or African American' 'White' 'Other']\n" ] } ], "source": [ "print(f\"The unique values in the race column (CIT_RACE) are {tbl.table['CIT_RACE'].unique()}\")\n", "print(f\"The unique values in the ethnicity column (CIT_ETHNICITY) are {tbl.table['CIT_ETHNICITY'].unique()}\")\n", "print(f\"The unique values in the race ethnicity column (SIMPLE_SUBJ_RE_GRP) are {tbl.table['SIMPLE_SUBJ_RE_GRP'].unique()}\")" ] }, { "cell_type": "markdown", "id": "9b56aec4", "metadata": {}, "source": [ "A few items to note:\n", "- Naming conventions are not consistent: Indigenous subjects are labeled 'American Indian / Alaskan Native' and 'AmIndian'. Black subjects are labeled 'Black' in the race column and 'Black or African American' in the race/ethncity column\n", "- 'Asian / Pacific Islander' and 'American Indian / Alaskan Native' appear in the race column but not the race/ethnicity column, which does not seem correct unless ALL Asian/Pacific island and Indigenous subjects were Hispanic/Latino (since Hispanic/Latino is typically used for Latinos of all races in race/ethnicity columns), which may seem unlikely.\n", "\n", "Let's look at some cases where the race is 'Asian / Pacific Islander' or 'American Indian / Alaskan Native':" ] }, { "cell_type": "code", "execution_count": 21, "id": "879bd111", "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", "
CIT_RACECIT_ETHNICITYSIMPLE_SUBJ_RE_GRP
19American Indian / Alaskan NativeNon-HispanicOther
20American Indian / Alaskan NativeNon-HispanicOther
24Asian / Pacific IslanderNon-HispanicOther
25American Indian / Alaskan NativeHispanicHispanic
27Asian / Pacific IslanderNon-HispanicOther
\n", "
" ], "text/plain": [ " CIT_RACE CIT_ETHNICITY SIMPLE_SUBJ_RE_GRP\n", "19 American Indian / Alaskan Native Non-Hispanic Other\n", "20 American Indian / Alaskan Native Non-Hispanic Other\n", "24 Asian / Pacific Islander Non-Hispanic Other\n", "25 American Indian / Alaskan Native Hispanic Hispanic\n", "27 Asian / Pacific Islander Non-Hispanic Other" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "i = tbl.table['CIT_RACE'].isin(['American Indian / Alaskan Native', 'Asian / Pacific Islander'])\n", "tbl.table[['CIT_RACE', 'CIT_ETHNICITY', 'SIMPLE_SUBJ_RE_GRP']][i].head()" ] }, { "cell_type": "markdown", "id": "d3fe7b74", "metadata": {}, "source": [ "Subjects labeled as 'Asian / Pacific Islander' and 'American Indian / Alaskan Native' in the race column are relabeled as 'OTHER' in the race/ethnicity column. Thus, although it is often preferred to use a combined race/ethnicity column, the way that 'SIMPLE_SUBJ_RE_GRP' has been generated actually removes key information.\n", "\n", "OPD's standardization allows the user to more quickly analyze data by automatically identifying columns, renaming them to standard column names, and standardizing the data in those columns." ] }, { "cell_type": "code", "execution_count": 22, "id": "e7a596c5", "metadata": {}, "outputs": [], "source": [ "tbl.standardize()" ] }, { "cell_type": "markdown", "id": "ceb75dde", "metadata": {}, "source": [ "Let's look at what the standardization did using `get_transform_map`:" ] }, { "cell_type": "code", "execution_count": 23, "id": "39563a1b", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "orig_column_name: INC_DATE,\n", "new_column_name: DATE,\n", "data_maps: None\n", "\n", "orig_column_name: CIT_RACE,\n", "new_column_name: SUBJECT_RACE,\n", "data_maps: {'White': 'WHITE', 'Black': 'BLACK', 'American Indian / Alaskan Native': 'INDIGENOUS', 'Asian / Pacific Islander': 'ASIAN/PACIFIC ISLANDER', 'Unknown': 'UNKNOWN', 'AmIndian': 'INDIGENOUS'}\n", "\n", "orig_column_name: SIMPLE_EMPL_RE_GRP,\n", "new_column_name: OFFICER_RACE/ETHNICITY,\n", "data_maps: {'White': 'WHITE', 'Hispanic': 'HISPANIC/LATINO', 'Other': 'OTHER', 'Black or African American': 'BLACK', None: 'UNSPECIFIED'}\n", "\n", "orig_column_name: CIT_ETHNICITY,\n", "new_column_name: SUBJECT_ETHNICITY,\n", "data_maps: {'Non-Hispanic': 'NON-HISPANIC/NON-LATINO', 'Hispanic': 'HISPANIC/LATINO', 'Unknown': 'UNKNOWN'}\n", "\n", "orig_column_name: ['SUBJECT_RACE', 'SUBJECT_ETHNICITY'],\n", "new_column_name: SUBJECT_RACE/ETHNICITY,\n", "data_maps: None\n", "\n", "orig_column_name: SUBJECT_RACE/ETHNICITY,\n", "new_column_name: SUBJECT_RE_GROUP,\n", "data_maps: None\n", "\n", "orig_column_name: OFFICER_RACE/ETHNICITY,\n", "new_column_name: OFFICER_RE_GROUP,\n", "data_maps: None\n", "\n", "orig_column_name: CIT_INJURY_YN,\n", "new_column_name: SUBJECT_INJURY,\n", "data_maps: {'Yes': 'INJURED', 'No': 'NO INJURY'}\n", "\n", "orig_column_name: CIT_AGE,\n", "new_column_name: SUBJECT_AGE,\n", "data_maps: None\n", "\n", "orig_column_name: SUBJ_AGE_GROUP,\n", "new_column_name: SUBJECT_AGE_RANGE,\n", "data_maps: {'30s': '30-39', '20s': '20-29', '40s': '40-49', '<20': '0-20', '50s': '50-59', '60s': '60-69', 'Not Available': 'Not Available', '70s': '70-79', '90s': '90-99', '80s': '80-89'}\n", "\n", "orig_column_name: CIT_GENDER,\n", "new_column_name: SUBJECT_GENDER,\n", "data_maps: {'Male': 'MALE', 'Female': 'FEMALE'}\n", "\n", "orig_column_name: EMPL_SEX,\n", "new_column_name: OFFICER_GENDER,\n", "data_maps: {'Male': 'MALE', 'Female': 'FEMALE', None: 'UNSPECIFIED'}\n", "\n", "orig_column_name: INC_ZIPCODE,\n", "new_column_name: ZIP_CODE,\n", "data_maps: None\n", "\n" ] } ], "source": [ "std_map = tbl.get_transform_map(minimize=True)\n", "for t in std_map:\n", " print(f\"{t}\\n\")" ] }, { "cell_type": "markdown", "id": "6e0e9b6f", "metadata": {}, "source": [ "`get_transform_map` shows changes made by standardization including the following:\n", "- Identifying all demographics columns\n", "- Identifying the more informative CIT_RACE as the race column instead of SIMPLE_SUBJ_RE_GRP\n", "- The identified race (CIT_RACE) and ethnicity columns (CIT_ETHNICITY) were converted to SUBJECT_RACE and SUBJECT_ETHNICITY, respectively and then SUBJECT_RACE and SUBJECT_ETHNICITY are combined into SUBJECT_RACE_ETHNICITY. \n", "- The SUBJECT_RACE_ETHNICITY was copied to another column called SUBJECT_RE_GROUP. RE_GROUP columns like SUBJECT_RE_GROUP and OFFICER_RE_GROUP are added for those who want to be able to easily use a RACE_ETHNICITY column if it exists or a RACE column otherwise. The RE_GROUP column will be a copy of the RACE_ETHNICITY column if a RACE_ETHNICITY column has been generated or a RACE column if a RACE column was found but a RACE_ETHNICITY was not generated.\n", "- EMPL was identified as indicating officer demographics\n", "- The cryptically named SIMPLE_EMPL_RE_GRP was identified as an OFFICER_RACE column \n", "- Values of race, gender, age, and age group are standardized to values that will be consistent across all OPD-loaded tables\n", "\n", "In `data_maps`, `get_transform_map` also includes dictionaries indicating original values (key) and the resulting standardize value (value). \n", "\n", "Printing the columns shows that the standardized columns are in the front while the original columns are prepended with RAW and moved to the back of the list." ] }, { "cell_type": "code", "execution_count": 24, "id": "c11c0796", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The 1st 10 columns after standardization are: Index(['DATE', 'SUBJECT_RACE', 'OFFICER_RACE/ETHNICITY', 'SUBJECT_ETHNICITY',\n", " 'SUBJECT_RACE/ETHNICITY', 'SUBJECT_RE_GROUP', 'OFFICER_RE_GROUP',\n", " 'SUBJECT_INJURY', 'SUBJECT_AGE', 'SUBJECT_AGE_RANGE', 'SUBJECT_GENDER',\n", " 'OFFICER_GENDER'],\n", " dtype='object')\n", "The last 11 columns after standardization are: Index(['RAW_INC_ZIPCODE', 'RAW_CIT_INJURY_YN', 'RAW_CIT_GENDER', 'RAW_CIT_AGE',\n", " 'RAW_SUBJ_AGE_GROUP', 'RAW_CIT_RACE', 'RAW_CIT_ETHNICITY',\n", " 'RAW_SIMPLE_EMPL_RE_GRP', 'RAW_EMPL_SEX'],\n", " dtype='object')\n" ] } ], "source": [ "print(f\"The 1st 10 columns after standardization are: {tbl.table.columns[:12]}\")\n", "print(f\"The last 11 columns after standardization are: {tbl.table.columns[-9:]}\")" ] }, { "cell_type": "markdown", "id": "9176e7a7", "metadata": {}, "source": [ "Finally, we can view what values that the new SUBJECT_RE_GROUP column contains:" ] }, { "cell_type": "code", "execution_count": 25, "id": "53dd1ced", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "array(['HISPANIC/LATINO', 'BLACK', 'WHITE', 'INDIGENOUS',\n", " 'ASIAN/PACIFIC ISLANDER', 'UNKNOWN'], dtype=object)" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "tbl.table[\"SUBJECT_RE_GROUP\"].unique()\n", "# NOTE: We also provide a columns enum so that the user does not have to remember the standardized column names. This would produce the same thing:\n", "# tbl.table[opd.defs.columns.SUBJECT_RE_GROUP].unique()" ] }, { "cell_type": "markdown", "id": "b7e16e61", "metadata": {}, "source": [ "## Other Topics\n", "\n", "- [Data Standardization Guide](../examples/opd-examples/standardization.ipynb) (including methods for customizing the standardization process)\n", "- [Related Tables](related_table.ipynb)" ] } ], "metadata": { "kernelspec": { "display_name": "opd-py3.13", "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.13.2" } }, "nbformat": 4, "nbformat_minor": 5 }