notebooks/precinct_hate_crime_map.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "03ba55df-60e8-4f7c-ab06-cb18b740b3ce",
   "metadata": {},
   "source": [
    "### Mapping Hate Crime Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58cda935-2e90-4fa9-ba7d-a35768d4fdab",
   "metadata": {},
   "source": [
    "In this notebook, we'll be working toward using this large dataset to present a LOT of data per-precinct using a datawrapper map of NYPD precincts paired to our dataset's `Complaint Precinct Code` column. You'll need a datawrapper account to generate your own."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8135111b-2a55-42af-8016-1e96f78d6a57",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "import math \n",
    "import re \n",
    "import datawrapper \n",
    "from IPython.display import HTML"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1bc7b8b0-a823-45a3-abe6-d5d81ce078f2",
   "metadata": {},
   "source": [
    "Grab an API key for your datawrapper personal (free!) account before continuing. Instructions are available on [their website here](https://developer.datawrapper.de/docs/getting-started)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d4d4b6a9-e919-4ca0-a2e6-0fd7fbbc039d",
   "metadata": {},
   "outputs": [],
   "source": [
    "API_TOKEN = \"\" #replace with your API Key "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "70272767-cefb-4f22-8dc0-cb2ba0818e52",
   "metadata": {},
   "outputs": [],
   "source": [
    "#helper function because datawrapper hates anything but alphanumeric snakecase for the tooltip variables \n",
    "#https://en.wikipedia.org/wiki/Snake_case \n",
    "def to_snake_case(text):\n",
    "    # Converts column names to datawrapper-compatible format\n",
    "    # e.g. \"Ethnicity/National Origin/Ancestry\" -> \"ethnicity_national_origin_ancestry\"\n",
    "    # and \"60 YRS AND OLDER\" -> \"n_60_yrs_and_older\"\n",
    "    text = re.sub(r'[^a-zA-Z0-9\\s]', ' ', text)\n",
    "    result = re.sub(r'[\\s_]+', '_', text.lower()).strip('_')\n",
    "    return f\"n_{result}\" if result[0].isdigit() else result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac702c9b-6938-42f3-b7af-8f06203d9adb",
   "metadata": {},
   "source": [
    "Since we're in a new notebook, we need to load up our prepared dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4b8bd635-bf5e-4df7-8165-1232902167f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "hate_crimes_over_time = pd.read_csv('./output/hate_crimes_w_codecats.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80fb5cce-b523-40f2-a9cd-3f22f1b32911",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "#### calculator functions \n",
    "These are the same calculator functions we've been using in our main notebook. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c59b0f6e-9191-49ce-9569-1618a61019f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def calc_stats(df):\n",
    "    total = len(df)\n",
    "    violent_incidents = len(df[df['Violent'] == 1])\n",
    "    violent_arrests = len(df[(df['Violent'] == 1) & ((df['Arrest Date'].notna()) | (df['Arrest Id'].notna()))])\n",
    "    non_violent_arrests = len(df[(df['Violent'] == 0) & ((df['Arrest Date'].notna()) | (df['Arrest Id'].notna()))])\n",
    "    total_arrests = violent_arrests + non_violent_arrests\n",
    "    \n",
    "    return {\n",
    "        'Total': total,\n",
    "        'Violent': violent_incidents,\n",
    "        '% Violent': round(violent_incidents / total * 100, 2) if total > 0 else 0,\n",
    "        'Non-Violent Arrests': non_violent_arrests,\n",
    "        'Violent Arrests': violent_arrests,\n",
    "        'Total Arrests': total_arrests,\n",
    "        '% Arrests': round(total_arrests / total * 100, 2) if total > 0 else 0,\n",
    "        '% Violent Arrests': round(violent_arrests / violent_incidents * 100, 2) if violent_incidents > 0 else 0\n",
    "    }\n",
    "    \n",
    "def calc_by_cat(df, category_column='Offense Category', group_column='Complaint Precinct Code'):\n",
    "    categories = sorted(df[category_column].unique())\n",
    "    all_metrics = []\n",
    "    \n",
    "    for precinct in df[group_column].unique():\n",
    "        precinct_data = df[df[group_column] == precinct]\n",
    "        precinct_metrics = {'Precinct code': precinct}\n",
    "        overall_metrics = calc_stats(precinct_data)\n",
    "        precinct_metrics.update({\n",
    "            'total_incidents': overall_metrics['Total'],\n",
    "            'violent_incidents': overall_metrics['Violent'],\n",
    "            'violent_percentage': overall_metrics['% Violent'],\n",
    "            'arrests': overall_metrics['Total Arrests'],\n",
    "            'arrest_percentage': overall_metrics['% Arrests']\n",
    "        })\n",
    "        \n",
    "        for category in categories:\n",
    "            cat_snake = to_snake_case(category)\n",
    "            cat_df = precinct_data[precinct_data[category_column] == category]\n",
    "            cat_metrics = calc_stats(cat_df)\n",
    "            precinct_metrics.update({\n",
    "                f'{cat_snake}_incidents': cat_metrics['Total'],\n",
    "                f'{cat_snake}_percentage': round(cat_metrics['Total'] / overall_metrics['Total'] * 100, 1) if overall_metrics['Total'] > 0 else 0,\n",
    "                f'{cat_snake}_violent_percentage': cat_metrics['% Violent'],\n",
    "                f'{cat_snake}_arrest_percentage': cat_metrics['% Arrests']\n",
    "            })\n",
    "        \n",
    "        all_metrics.append(precinct_metrics)\n",
    "    \n",
    "    metrics_df = pd.DataFrame(all_metrics)\n",
    "    return metrics_df, {cat: to_snake_case(cat) for cat in categories}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84cbbbea-0ac4-410d-a5d0-49d54c7f83ea",
   "metadata": {},
   "source": [
    "#### mapping to datawrapper"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ec855fd-01f4-4aab-b4f7-2ded90623dd9",
   "metadata": {},
   "source": [
    "This function preps metrics that we'll use in our choropleth visualization of the hate crime data by running our calculator functions against specific categories and for every precinct in the dataset. \n",
    "\n",
    "- We pull a column from our imported dataframe (`category_column`) to use for the tooltip breakdown of additional statistics per-precinct.\n",
    "- We calculate the total incidents per precinct by counting the unique rows in the `Full Complaint ID` column.\n",
    "- Similarly, we then use the value in `Violent` to caluclate both a violent incident count and percentage per precinct.\n",
    "- Using the same method, we calculate the number of non-empty `Arrest Id` values per row per precinct for our arrest count and arrest percentage.\n",
    "\n",
    "Then, using a **for** loop, we take every variable from the `category_column` variable column and run each through the same calculations - incidents, violence, arrests and percentages, all sorted by precinct code and then by category. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "71ebb091-aac8-4271-a05f-850abf52742e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def calculate_hate_crimes_by_category(df, category_column='Offense Category', group_column='Complaint Precinct Code'):\n",
    "    categories = sorted(df[category_column].unique())\n",
    "    category_mapping = {cat: to_snake_case(cat) for cat in categories}\n",
    "    \n",
    "    all_precincts = sorted(df[group_column].unique())\n",
    "    metrics_list = []\n",
    "    \n",
    "    for precinct in all_precincts:\n",
    "        precinct_df = df[df[group_column] == precinct]\n",
    "        precinct_stats = calc_stats(precinct_df)\n",
    "        row_data = {\n",
    "            'Precinct code': precinct,\n",
    "            'total_incidents': precinct_stats['Total'],\n",
    "            'violent_incidents': precinct_stats['Violent'],\n",
    "            'violent_percentage': precinct_stats['% Violent'],\n",
    "            'arrests': precinct_stats['Total Arrests'],\n",
    "            'arrest_percentage': precinct_stats['% Arrests']\n",
    "        }\n",
    "        \n",
    "        for category in categories:\n",
    "            cat_snake = to_snake_case(category)\n",
    "            cat_df = precinct_df[precinct_df[category_column] == category]\n",
    "            cat_stats = calc_stats(cat_df)\n",
    "            \n",
    "            row_data.update({\n",
    "                f'{cat_snake}_incidents': cat_stats['Total'],\n",
    "                f'{cat_snake}_percentage': round(cat_stats['Total'] / precinct_stats['Total'] * 100, 1) if precinct_stats['Total'] > 0 else 0,\n",
    "                f'{cat_snake}_violent_percentage': cat_stats['% Violent'],\n",
    "                f'{cat_snake}_violent_count': cat_stats['Violent'],  \n",
    "                f'{cat_snake}_arrest_percentage': cat_stats['% Arrests'],\n",
    "                f'{cat_snake}_arrest_count': cat_stats['Total Arrests'] \n",
    "            })\n",
    "        \n",
    "        metrics_list.append(row_data)\n",
    "    \n",
    "    metrics = pd.DataFrame(metrics_list)\n",
    "    \n",
    "    return metrics, category_mapping"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa3954dc-8564-4593-b0ed-5614085c4e40",
   "metadata": {},
   "source": [
    "This function creates our choropleth visualization using datawrapper's library through their API. The focus of most of this function is on the advanced tooltip we're prepping based on Datawrapper's guide to [Embedded Graphs in Tooltips](https://academy.datawrapper.de/article/282-how-to-embed-charts-into-tooltips). "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "488b3276-8ea0-4149-bb0d-b5e8eb5fe384",
   "metadata": {},
   "source": [
    "<img src=\"https://d33v4339jhl8k0.cloudfront.net/docs/assets/588089eddd8c8e484b24e90a/images/6155dae012c07c18afdd93b8/file-BCaBsmKANx.png\" width=\"75%\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8b3ded8-2dd7-41ac-b87c-427058937216",
   "metadata": {},
   "source": [
    "- We loop through each category pulled from our selected `category_column` to prep html and datawrapper syntax for the tooltip for matching barcharts for the incident count, violent incidents and arrests.\n",
    "- We prep some basic `chart_config` settings for datawrapper that can be edited on their website later.\n",
    "- We set the data source (though this can be edited later)\n",
    "- Crucially, we tell datawrapper which basemap we're going to be using and how to pair that basemap to our data (in this case, by precinct code)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1cfccf10-e654-4164-a741-6372ca9a4c6b",
   "metadata": {},
   "source": [
    "**Note:** Datawrapper only allows a limited [subset of HTML tags](https://academy.datawrapper.de/article/237-i-want-to-change-how-my-data-appears-in-tooltips) be used in the tooltip section and only for certain kinds of charts, including maps and scatterplots: \n",
    "\n",
    "```html\n",
    "<a> <abbr> <address> <audio> <b> <big> <blockquote> <br/> <br> <caption> <cite> <code> <col> <colgroup> <dd> <del> <details> <dfn> <div> <dl> <dt> <em> <figure> <h1> <h2> <h3> <h4> <h5> <h6> <hr> <hgroup> <i> <img> <ins> <kbd> <li> <mark> <meter> <ol> <p> <pre> <q> <s> <small> <span> <strike> <strong> <sub> <summary> <sup> <table> <tbody> <td> <th> <thead> <tfoot> <tr> <tt> <u> <ul> <wbr><br>\n",
    "```\n",
    "\n",
    "So you can't start building a form or embedding additional multimedia inside of your tooltip, but you *can* still get creative. \n",
    "\n",
    "Here's what one of Datwrapper's examples looks like in html: \n",
    "\n",
    "```html\n",
    "<div> <div style=\"margin-bottom:11px;\"> <div style='margin-bottom:4px;'>Women</div> <div style='background:#F2F2F2; width: 150px; height:15px; display:flex; align-items: center;'> <div style='display:flex; align-items:center; background:#BFA817; height:100%; width: {{women}}%'> {{ women >= 20 ? CONCAT(\"<span style='color:white; font-weight:bold;margin:0px 5px;'>\", FORMAT(women, '0'), \"%</span>\") : \"\" }} </div> \t{{ women < 20 ? CONCAT(\"<span style='color:black; font-weight:bold;margin:0px 5px;'>\", FORMAT(women, '0'), \"%</span>\") : \"\" }} </div> </div> <div> <div style='margin-bottom:4px;'>Men</div> <div style='background:#F2F2F2; width: 150px; height:15px; display:flex; align-items: center;'> <div style='display:flex; align-items:center; background:#7AAFCB; height:100%; width: {{ men }}%'> {{ men >= 20 ? CONCAT(\"<span style='color:white; font-weight:bold;margin:0px 5px;'>\", FORMAT(men, '0'), \"%</span>\") : \"\" }} </div> \t{{ men < 20 ? CONCAT(\"<span style='color:black; font-weight:bold;margin:0px 5px;'>\", FORMAT(men, '0'), \"%</span>\") : \"\" }} </div> </div> </div><br>\n",
    "```\n",
    "\n",
    "For our purposes, we're generating html for the tooltip section that will end up looking something like this (using the `age` category as an example):   \n",
    "\n",
    "```html\n",
    "{{ age_incidents > 0 ? CONCAT(\"<div style=margin-bottom:16px><div style=margin-bottom:4px><b>Age</b></div><div style=display:flex><div style=background:#4e79a7;width:\", FORMAT(age_percentage * 2, \"0\"), \"px>\", age_percentage >= 80 ? CONCAT(\"<span style=color:white;font-weight:bold;padding:4px 4px 0px 4px;display:inline-block>\", FORMAT(age_percentage, \"0\"), \"%</span>\") : \"\" ,\"</div><div style=background:#F2F2F2;width:\", FORMAT(200-(age_percentage * 2), \"0\"), \"px>\", age_percentage < 80 ? CONCAT(\"<span style=color:#4e79a7;font-weight:bold;padding:4px 4px 0px 4px;display:inline-block>\", FORMAT(age_percentage, \"0\"), \"%</span>\") : \"\" ,\"</div></div><div style=font-size:12px;color:#666;margin-top:2px>\", age_incidents, \" incidents</div><div style=display:flex;margin-top:8px><div style=background:#ff4444;width:\", FORMAT(age_violent_percentage * 2, \"0\"), \"px>\", age_violent_percentage >= 80 ? CONCAT(\"<span style=color:white;font-weight:bold;padding:4px 4px 0px 4px;display:inline-block>\", FORMAT(age_violent_percentage, \"0\"), \"%</span>\") : \"\" ,\"</div><div style=background:#F2F2F2;width:\", FORMAT(200-(age_violent_percentage * 2), \"0\"), \"px>\", age_violent_percentage < 80 ? CONCAT(\"<span style=color:#ff4444;font-weight:bold;padding:4px 4px 0px 4px;display:inline-block>\", FORMAT(age_violent_percentage, \"0\"), \"%</span>\") : \"\" ,\"</div></div><div style=font-size:12px;color:#666;margin-top:2px>\", age_violent_count, \" violent incidents</div><div style=display:flex;margin-top:8px><div style=background:#2ca02c;width:\", FORMAT(age_arrest_percentage * 2, \"0\"), \"px>\", age_arrest_percentage >= 80 ? CONCAT(\"<span style=color:white;font-weight:bold;padding:4px 4px 0px 4px;display:inline-block>\", FORMAT(age_arrest_percentage, \"0\"), \"%</span>\") : \"\" ,\"</div><div style=background:#F2F2F2;width:\", FORMAT(200-(age_arrest_percentage * 2), \"0\"), \"px>\", age_arrest_percentage < 80 ? CONCAT(\"<span style=color:#2ca02c;font-weight:bold;padding:4px 4px 0px 4px;display:inline-block>\", FORMAT(age_arrest_percentage, \"0\"), \"%</span>\") : \"\" ,\"</div></div><div style=font-size:12px;color:#666;margin-top:2px>\", age_arrest_count, \" resulted in arrest</div></div>\") : \"\" }}\n",
    "\n",
    "```\n",
    "\n",
    "In our function, we loop through every category variable to generate a version of this code block which: \n",
    "- scales our bar graphs to fill as a percentage of the total bar (200px), because we're kind of hacking a bar graph together we're generating the color and the grey section of the bar separately with `FORMAT(age_percentage * 2, \"0\")` and `FORMAT(200-(age_percentage * 2), \"0\")`\n",
    "- pulling variable data we calculated for each category and calling it both as text and as the dimensions of each bar graph. \n",
    "\n",
    "Within the interactive, that will display as a dynamic tooltip like this below. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a1638a7-b0a3-4c22-b794-8e1449685a92",
   "metadata": {},
   "source": [
    "<img src=\"../reference/tooltip.png\" width=\"220px\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7cf83854-5df5-44d5-8d65-ed3983230543",
   "metadata": {},
   "source": [
    "In addition to bog-standard html, in our function below you'll see that we're using an if-else conditional here for each category (`{{ age_incidents > 0 ? CONCAT(` etc as above). Datawrapper uses js-y ternary conditional syntax for their tooltips, which allows us to do some helpful things. \n",
    "\n",
    "Datawrapper explains a little bit about their if else statement syntax for tooltips in [this article](https://academy.datawrapper.de/article/237-i-want-to-change-how-my-data-appears-in-tooltips). If you want to experiment with it, you might also reference GeeksforGeeks' [JS Ternary Operator tutorial too](https://www.geeksforgeeks.org/javascript-ternary-operator/).\n",
    "\n",
    "In this case, we're telling the tooltip settings to check each variable we prepare to see if the total incident number for that category is greater than zero. \n",
    "\n",
    "```js \n",
    "category > 0 ? CONCAT\n",
    "```\n",
    "\n",
    "If it is, it will shove our custom html together and display the sub-bar charts for that category. If it isn't, it won't show that category for that precinct at all. This is especially helpful for the rarest categories in the dataset which would otherwise display blank 0% bars over and over. \n",
    "\n",
    "\n",
    "**NOTE**: For this map, we're using datawrapper's built in basemap for NYC precinct data, or `us-nyc-policeprecincts`. If you're not sure the shorthand for the basemap you need you can run [this request](https://developer.datawrapper.de/reference/getbasemaps) in a terminal to pull the full list: \n",
    "\n",
    "```shell\n",
    "curl --request GET \\\n",
    "     --url https://api.datawrapper.de/v3/basemaps \\\n",
    "     --header 'accept: */*'\n",
    "```\n",
    "\n",
    "Your dataset must have a key column to associated with the basemap's geographical data. In this case, our dataset has a column of precinct codes that matches the precinct basemap. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7c716add-c1c4-41af-a678-8252d5fdd4cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_offense_category_map(df, category_mapping, api_token, title, description):\n",
    "    dw = datawrapper.Datawrapper(access_token=api_token)\n",
    "    \n",
    "    fields = {\n",
    "        \"precinct_code\": \"Precinct code\",\n",
    "        \"total_incidents\": \"total_incidents\",\n",
    "        \"violent_incidents\": \"violent_incidents\",\n",
    "        \"violent_percentage\": \"violent_percentage\",\n",
    "        \"arrests\": \"arrests\",\n",
    "        \"arrest_percentage\": \"arrest_percentage\"\n",
    "    }\n",
    "    \n",
    "    for cat_snake in category_mapping.values():\n",
    "        for suffix in [\"incidents\", \"percentage\", \"violent_percentage\", \"arrest_percentage\"]:\n",
    "            fields[f\"{cat_snake}_{suffix}\"] = f\"{cat_snake}_{suffix}\" #adds our calculated fields as fields datawrapper can call on in the tooltip \n",
    "    \n",
    "    category_sections = []\n",
    "    for orig_cat, cat_snake in category_mapping.items():\n",
    "        category_sections.append( #this becomes the html in the tooltip section of datawrapper's visualization editor - we'll generate this same codeblock for every category in our category column and then append them all after our precinct data intro.\n",
    "            f'{{{{ {cat_snake}_incidents > 0 ? CONCAT('\\\n",
    "                f'\\\"<div style=margin-bottom:16px>'\\\n",
    "                    f'<div style=margin-bottom:4px>'\\\n",
    "                        f'<b>{orig_cat}</b>'\\\n",
    "                    f'</div>'\\\n",
    "                    \n",
    "                    # Incidents bargraph\n",
    "                    f'<div style=display:flex>'\\\n",
    "                        f'<div style=background:#4e79a7;width:\\\", FORMAT({cat_snake}_percentage * 2, \\\"0\\\"), \\\"px>'\\\n",
    "                            f'\\\", {cat_snake}_percentage >= 80 ? CONCAT(\\\"<span style=color:white;font-weight:bold;padding:4px 4px 0px 4px;display:inline-block>'\\\n",
    "                                f'\\\", FORMAT({cat_snake}_percentage, \\\"0\\\"), \\\"%</span>\\\") : \\\"\\\" ,\\\"'\\\n",
    "                        f'</div>'\\\n",
    "                        f'<div style=background:#F2F2F2;width:\\\", FORMAT(200-({cat_snake}_percentage * 2), \\\"0\\\"), \\\"px>'\\\n",
    "                            f'\\\", {cat_snake}_percentage < 80 ? CONCAT(\\\"<span style=color:#4e79a7;font-weight:bold;padding:4px 4px 0px 4px;display:inline-block>'\\\n",
    "                                f'\\\", FORMAT({cat_snake}_percentage, \\\"0\\\"), \\\"%</span>\\\") : \\\"\\\" ,\\\"'\\\n",
    "                        f'</div>'\\\n",
    "                    f'</div>'\\\n",
    "                    f'<div style=font-size:12px;color:#666;margin-top:2px>'\\\n",
    "                        f'\\\", {cat_snake}_incidents, \\\" incidents'\\\n",
    "                    f'</div>'\\\n",
    "                    \n",
    "                    # Violent incidents bargraph\n",
    "                    f'<div style=display:flex;margin-top:8px>'\\\n",
    "                        f'<div style=background:#ff4444;width:\\\", FORMAT({cat_snake}_violent_percentage * 2, \\\"0\\\"), \\\"px>'\\\n",
    "                            f'\\\", {cat_snake}_violent_percentage >= 80 ? CONCAT(\\\"<span style=color:white;font-weight:bold;padding:4px 4px 0px 4px;display:inline-block>'\\\n",
    "                                f'\\\", FORMAT({cat_snake}_violent_percentage, \\\"0\\\"), \\\"%</span>\\\") : \\\"\\\" ,\\\"'\\\n",
    "                        f'</div>'\\\n",
    "                        f'<div style=background:#F2F2F2;width:\\\", FORMAT(200-({cat_snake}_violent_percentage * 2), \\\"0\\\"), \\\"px>'\\\n",
    "                            f'\\\", {cat_snake}_violent_percentage < 80 ? CONCAT(\\\"<span style=color:#ff4444;font-weight:bold;padding:4px 4px 0px 4px;display:inline-block>'\\\n",
    "                                f'\\\", FORMAT({cat_snake}_violent_percentage, \\\"0\\\"), \\\"%</span>\\\") : \\\"\\\" ,\\\"'\\\n",
    "                        f'</div>'\\\n",
    "                    f'</div>'\\\n",
    "                    f'<div style=font-size:12px;color:#666;margin-top:2px>\\\", {cat_snake}_violent_count, \\\" violent incidents</div>'\\\n",
    "                    \n",
    "                    # Arrests bargraph\n",
    "                    f'<div style=display:flex;margin-top:8px>'\\\n",
    "                        f'<div style=background:#2ca02c;width:\\\", FORMAT({cat_snake}_arrest_percentage * 2, \\\"0\\\"), \\\"px>'\\\n",
    "                            f'\\\", {cat_snake}_arrest_percentage >= 80 ? CONCAT(\\\"<span style=color:white;font-weight:bold;padding:4px 4px 0px 4px;display:inline-block>'\\\n",
    "                                f'\\\", FORMAT({cat_snake}_arrest_percentage, \\\"0\\\"), \\\"%</span>\\\") : \\\"\\\" ,\\\"'\\\n",
    "                        f'</div>'\\\n",
    "                        f'<div style=background:#F2F2F2;width:\\\", FORMAT(200-({cat_snake}_arrest_percentage * 2), \\\"0\\\"), \\\"px>'\\\n",
    "                            f'\\\", {cat_snake}_arrest_percentage < 80 ? CONCAT(\\\"<span style=color:#2ca02c;font-weight:bold;padding:4px 4px 0px 4px;display:inline-block>'\\\n",
    "                                f'\\\", FORMAT({cat_snake}_arrest_percentage, \\\"0\\\"), \\\"%</span>\\\") : \\\"\\\" ,\\\"'\\\n",
    "                        f'</div>'\\\n",
    "                    f'</div>'\\\n",
    "                    f'<div style=font-size:12px;color:#666;margin-top:2px>\\\", {cat_snake}_arrest_count, \\\" resulted in arrest</div>'\\\n",
    "                f'</div>\\\") : \\\"\\\" }}}}'\\\n",
    "            )\n",
    "\n",
    "    tooltip_template = { #this does the initial setup for the tooltip with our intro which will summarize the hate crime data for each precinct\n",
    "        \"title\": \"<big>Precinct {{ precinct_code }}</big>\",\n",
    "        \"body\": (\n",
    "                    '<b>{{ total_incidents }}</b> hate crimes were reported. <b>{{ violent_incidents }}</b> were for allegedly violent crimes. <b>{{ arrests }}</b> resulted in arrest.'\\\n",
    "                '<hr style=\"border-top: 1px solid #CFCFCF;width:400px;margin-left:-20px\">'\\\n",
    "                '<div style=margin-bottom:12px>'\\\n",
    "                    '<b><big>Breakdown by Category</big></b>'\\\n",
    "                '</div>'\\\n",
    "                + ''.join(category_sections) +\\\n",
    "            '</div>'\\\n",
    "        ),\n",
    "        \"fields\": fields\n",
    "    }\n",
    "\n",
    "    chart_config = { #everything here can be edited after you create the graph back in datawrapper's UI, we're just skipping making the data work and writing the tooltip code manually\n",
    "        \"describe\": {\n",
    "            \"intro\": description,\n",
    "            \"source-name\": \"NYPD Data\",\n",
    "            \"source-url\": \"https://data.cityofnewyork.us\"\n",
    "        },\n",
    "        \"visualize\": {\n",
    "            \"basemap\": \"us-nyc-policeprecincts\",\n",
    "            \"map-key-column\": \"Precinct code\",\n",
    "            \"map-value-column\": \"total_incidents\",\n",
    "            \"map-color-scale\": \"Blues\",\n",
    "            \"tooltip\": tooltip_template\n",
    "        },\n",
    "        \"data\": {\n",
    "            \"column-format\": {\n",
    "                \"Precinct code\": {\"type\": \"text\", \"role\": \"key\"}, \n",
    "                \"total_incidents\": {\"type\": \"number\", \"role\": \"value\"}\n",
    "            }\n",
    "        }\n",
    "    }\n",
    "    \n",
    "    chart = dw.create_chart(title=title, chart_type='d3-maps-choropleth') #you can call this variable from datawrapper to see all your options for other graphs\n",
    "    chart_id = chart['id']\n",
    "    \n",
    "    dw.update_chart(\n",
    "        chart_id=chart_id,\n",
    "        title=title,\n",
    "        metadata=chart_config\n",
    "    )\n",
    "    \n",
    "    dw.add_data(chart_id, df.to_csv(index=False))\n",
    "    \n",
    "    return chart_id, df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "137de5c9-f413-4518-b238-7d74fdae613b",
   "metadata": {},
   "source": [
    "Now we apply our dynamic calculator to our precinct data and generate our map. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "bdbf3770-8695-4398-a908-1b277c01e936",
   "metadata": {},
   "outputs": [],
   "source": [
    "precinct_metrics, category_mapping = calculate_hate_crimes_by_category(\n",
    "    hate_crimes_over_time, \n",
    "    category_column='Offense Category'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f261235a-4e48-420d-be3c-bd3e28d35497",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Created chart with ID: tryE9\n",
      "https://app.datawrapper.de/edit/tryE9/visualize#refine\n"
     ]
    }
   ],
   "source": [
    "chart_id, df = create_offense_category_map(\n",
    "    df=precinct_metrics,\n",
    "    category_mapping=category_mapping,\n",
    "    api_token=API_TOKEN,\n",
    "    title=\"DRAFT: NYPD Hate Crime Data by Precinct and Target Category - Python Gen\",\n",
    "    description=\"Every hate crime in New York City reported by the New York City Police Department from Jan 2019- Sep 2024. <br> Scroll to see <b style='color:white; background-color:#2468f0;box-decoration-break:clone;-webkit-box-decoration-break: clone;padding:0px 3px;'>total</b> reported incidents, <b style='color:white; background-color:#ff1744;box-decoration-break:clone;-webkit-box-decoration-break: clone;padding:0px 3px;'>violent</b> hate crimes and <b style='color:white; background-color:#2ca02c;box-decoration-break:clone;-webkit-box-decoration-break: clone;padding:0px 3px;'>arrests</b> broken down by bias category.<br><br>\"\n",
    ")\n",
    "\n",
    "print(f\"Created chart with ID: {chart_id}\")\n",
    "print(f\"https://app.datawrapper.de/edit/{chart_id}/visualize#refine\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4cc6bc3a-77d8-44b5-92e5-b6780cc9daee",
   "metadata": {},
   "source": [
    "You can push the chart to publish from the datawrapper python library but for this workflow, copy and paste the link to your logged-in browser and doublecheck your final settings in the web interface. Once you confirm \"publish\" in the web interface, run the next cell with your `chart_id` swapped in to see our map. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "1375b6a3-f19d-402b-ba3d-c17566d3ff69",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe title=\"DRAFT: NYPD Hate Crime Data by Precinct and Target Category - Python Gen\" aria-label=\"Map\" id=\"datawrapper-chart-tryE9\" src=\"https://datawrapper.dwcdn.net/tryE9/1/\" scrolling=\"no\" frameborder=\"0\" style=\"width: 0; min-width: 100% !important; border: none;\" height=\"825\" data-external=\"1\"></iframe><script type=\"text/javascript\">!function(){\"use strict\";window.addEventListener(\"message\",(function(a){if(void 0!==a.data[\"datawrapper-height\"]){var e=document.querySelectorAll(\"iframe\");for(var t in a.data[\"datawrapper-height\"])for(var r=0;r<e.length;r++)if(e[r].contentWindow===a.source){var i=a.data[\"datawrapper-height\"][t]+\"px\";e[r].style.height=i}}}))}();</script>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "HTML('<iframe title=\"DRAFT: NYPD Hate Crime Data by Precinct and Target Category - Python Gen\" aria-label=\"Map\" id=\"datawrapper-chart-tryE9\" src=\"https://datawrapper.dwcdn.net/tryE9/1/\" scrolling=\"no\" frameborder=\"0\" style=\"width: 0; min-width: 100% !important; border: none;\" height=\"825\" data-external=\"1\"></iframe><script type=\"text/javascript\">!function(){\"use strict\";window.addEventListener(\"message\",(function(a){if(void 0!==a.data[\"datawrapper-height\"]){var e=document.querySelectorAll(\"iframe\");for(var t in a.data[\"datawrapper-height\"])for(var r=0;r<e.length;r++)if(e[r].contentWindow===a.source){var i=a.data[\"datawrapper-height\"][t]+\"px\";e[r].style.height=i}}}))}();</script>')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c7b5f7e-965c-4d0b-9869-bfc4f6515c30",
   "metadata": {},
   "source": [
    "Github blocks HTML rendering so if you're previewing this notebook on Github, you can see an example of what we've generated here: \n",
    "\n",
    "<img src=\"../reference/choropleth_demo.gif\" width=\"700px\"><br> "
   ]
  }
 ],
 "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.12.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}