wrangler.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c3147234-c898-4c49-945f-c62c519edece",
   "metadata": {},
   "source": [
    "## Cleaning and analyzing datasets\n",
    "\n",
    "Previously, I scraped `.xlsx` files from the New York Police Department's page on [Hate Crimes Reports](https://www.nyc.gov/site/nypd/stats/reports-analysis/hate-crimes.page). This time, I will be cleaning and analyzing the datasets downloaded.\n",
    "\n",
    "_(Note: I manually created a new folder to organize the files that were downloaded and I previewed the datasets to figure out which ones I wanted to use.)_"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f5d7671-6cb7-45f7-8d8e-9143f5130dcf",
   "metadata": {},
   "source": [
    "#### Hate crime complaints by motivation\n",
    "\n",
    "The datasets cover 2020-2023 by quarter and by year, and only the first quarter for 2024. I've decided to use the quarterly breakdowns to hopefully see nuances in the data, e.g., a possible spike in anti-Asian sentiments at the onset of Covid-19 and in anti-Jewish sentiments during the Israel-Palestine war."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "e56b202a-2916-4280-9a62-50432523c329",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start by setting up libraries\n",
    "\n",
    "import pandas as pd\n",
    "import numpy\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "372d561f-9d10-4c67-9f0b-50de33658855",
   "metadata": {},
   "source": [
    "#### Q1, 2020\n",
    "\n",
    "Observed issues: The dataset has four rows that contain text as title and description of the dataset. I used `skiprows` to remove these unnecessary rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "a89c4a1f-fe5f-43ee-af4e-c08b103ead9f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Precinct                                 object\n",
       "Anti-Asian                              float64\n",
       "Anti-Black                              float64\n",
       "Anti-Catholic                           float64\n",
       "Anti-Female Homosexual (Lesbian)        float64\n",
       "Anti-Hindu                              float64\n",
       "Anti-Islamic (Muslim)                   float64\n",
       "Anti-Jewish                             float64\n",
       "Anti-Male Homosexual (Gay)              float64\n",
       "Anti-Other Ethnicity/National Origin    float64\n",
       "Anti-Transgender                        float64\n",
       "Anti-White                              float64\n",
       "Total                                   float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motivation-q1-202020.xlsx', skiprows=4)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "id": "41a1f7b5-93f9-468e-9af6-eefbcc110ed2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get only the totals, which are in the last row\n",
    "\n",
    "last_row = df.tail(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "bf84ccb9-4ba5-4c1a-9b70-2355dec5c0ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new DF for totals\n",
    "\n",
    "totals_df = pd.DataFrame(last_row)\n",
    "totals_df['period'] = '2020, Q1'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "fe932b0b-ea4d-48d1-b8a0-8a34f388ea09",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This makes reindexing of later DFs easier...\n",
    "\n",
    "totals_df.columns = totals_df.columns.str.lower().str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "cbef4098-734e-42b0-996f-85c6ea67bd05",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Renaming columns for consistency... \n",
    "\n",
    "totals_df = totals_df.rename(columns={'anti-female homosexual (lesbian)': 'anti-lesbian', 'anti-male homosexual (gay)': 'anti-gay', 'anti-islamic (muslim)': 'anti-muslim', 'anti-other ethnicity/national origin': 'anti-other ethnicity'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "be1eea90-6df4-4fa7-820b-ca7999924b88",
   "metadata": {},
   "outputs": [],
   "source": [
    "# totals_df.drop(['precinct'], axis=1)\n",
    "# totals_df.drop(totals_df.columns[0], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "0cd4de6f-d536-4638-8776-b24ff6703bd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save as CSV for checkpoint\n",
    "\n",
    "totals_df.to_csv('totals.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5527c7c-cebd-468d-a6a5-30c1dff1385e",
   "metadata": {},
   "source": [
    "#### Q2, 2020\n",
    "\n",
    "Observed issues: The column names are capitalized and not in the same order as those of `Q1, 2020`. Some column names also do not exist or are stated differently than that of the previous dataset, so I had to `rename` some columns in this dataset and the previous one. \n",
    "\n",
    "Aside from the first four rows to skip, the **totals** in this dataset are also not in the last row, so I cannot simply use `.tail(1)`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "fd92c787-68da-4ff0-a632-3ed94eaa4261",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PRECINCT                              object\n",
       "60 YEARS OLD OR MORE                 float64\n",
       "ANTI-ARAB                            float64\n",
       "ANTI-ASIAN                           float64\n",
       "ANTI-BLACK                           float64\n",
       "ANTI-CATHOLIC                        float64\n",
       "ANTI-FEMALE                          float64\n",
       "ANTI-FEMALE HOMOSEXUAL\\n(LESBIAN)    float64\n",
       "ANTI-GENDER NON-CONFORMING           float64\n",
       "ANTI-HINDU                           float64\n",
       "ANTI-HISPANIC                        float64\n",
       "ANTI-ISLAMIC\\n(MUSLIM)               float64\n",
       "ANTI-JEWISH                          float64\n",
       "ANTI-MALE HOMOSEXUAL\\n(GAY)          float64\n",
       "ANTI-OTHER ETHNICITY                 float64\n",
       "ANTI-TRANSGENDER                     float64\n",
       "ANTI-WHITE                           float64\n",
       "Total                                float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motivation-q2-202020.xlsx', skiprows=4)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "948e4708-da46-471e-8f85-fbf77c96576b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = df.columns.str.lower().str.strip()\n",
    "df.columns = df.columns.str.replace('\\n', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "id": "19158056-4cae-4972-bcb1-7161ea8bb62a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop unnecessary rows\n",
    "\n",
    "df.drop(df.tail(4).index, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "be5a1d55-94e3-48d0-a192-955940e7ce00",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get only the totals, which are NOW the last row\n",
    "\n",
    "last_row = df.tail(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "fb7b749a-ddc5-46d2-959a-59a44acdbaec",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_54677/2065008592.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  last_row['period'] = '2020, Q2'\n"
     ]
    }
   ],
   "source": [
    "last_row['period'] = '2020, Q2'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "355e2f3f-48ee-4ad3-a7d9-c33b9fd54425",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Renaming columns for consistency... \n",
    "\n",
    "last_row = last_row.rename(columns={'anti-female homosexual(lesbian)': 'anti-lesbian', 'anti-male homosexual(gay)': 'anti-gay', 'anti-islamic(muslim)': 'anti-muslim'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "669bb15f-92b5-4386-85c0-3da44f5f006d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['precinct', 'anti-asian', 'anti-black', 'anti-catholic', 'anti-lesbian',\n",
       "       'anti-hindu', 'anti-muslim', 'anti-jewish', 'anti-gay',\n",
       "       'anti-other ethnicity', 'anti-transgender', 'anti-white', 'total',\n",
       "       'period'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check and align columns\n",
    "\n",
    "common_columns = totals_df.columns.intersection(last_row.columns)\n",
    "common_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "8faf62b9-b9b9-4247-98c9-ebd0ac26deea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['60 years old or more', 'anti-arab', 'anti-female',\n",
       "       'anti-gender non-conforming', 'anti-hispanic'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check different columns\n",
    "\n",
    "diff_columns1 = last_row.columns.difference(totals_df.columns)\n",
    "diff_columns1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "282bd516-3d93-4109-8154-ad079711a235",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>anti-asian</th>\n",
       "      <th>anti-black</th>\n",
       "      <th>anti-catholic</th>\n",
       "      <th>anti-lesbian</th>\n",
       "      <th>anti-hindu</th>\n",
       "      <th>anti-muslim</th>\n",
       "      <th>anti-jewish</th>\n",
       "      <th>anti-gay</th>\n",
       "      <th>anti-other ethnicity</th>\n",
       "      <th>anti-transgender</th>\n",
       "      <th>anti-white</th>\n",
       "      <th>total</th>\n",
       "      <th>period</th>\n",
       "      <th>60 years old or more</th>\n",
       "      <th>anti-arab</th>\n",
       "      <th>anti-female</th>\n",
       "      <th>anti-gender non-conforming</th>\n",
       "      <th>anti-hispanic</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Total</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>2020, Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Total</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>2020, Q2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  precinct  anti-asian  anti-black  anti-catholic  anti-lesbian  anti-hindu  \\\n",
       "0    Total        11.0        12.0            1.0           1.0         1.0   \n",
       "1    Total         8.0         5.0            3.0           2.0         1.0   \n",
       "\n",
       "   anti-muslim  anti-jewish  anti-gay  anti-other ethnicity  anti-transgender  \\\n",
       "0          1.0         45.0       5.0                   3.0               2.0   \n",
       "1          0.0         15.0       4.0                   1.0               1.0   \n",
       "\n",
       "   anti-white  total    period  60 years old or more  anti-arab  anti-female  \\\n",
       "0         2.0   84.0  2020, Q1                   NaN        NaN          NaN   \n",
       "1         2.0   46.0  2020, Q2                   1.0        1.0          0.0   \n",
       "\n",
       "   anti-gender non-conforming  anti-hispanic  \n",
       "0                         NaN            NaN  \n",
       "1                         2.0            0.0  "
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals_df = pd.concat([totals_df, last_row], ignore_index=True)\n",
    "totals_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47c9789f-7fae-450b-94d8-c155cfc372ef",
   "metadata": {},
   "source": [
    "#### Q3, 2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "4ab9af5e-857f-4665-b591-64bb5b9a4134",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PRECINCT                             object\n",
       "ANTI-ASIAN                            int64\n",
       "ANTI-BLACK                            int64\n",
       "ANTI-CATHOLIC                         int64\n",
       "ANTI-JEWISH                           int64\n",
       "ANTI-LGBT (MIXED GROUP)               int64\n",
       "ANTI-MALE HOMOSEXUAL (GAY)            int64\n",
       "ANTI-MULTI RACIAL GROUPS              int64\n",
       "ANTI-OTHER ETHNICITY                  int64\n",
       "ANTI-RELIGIOUS PRACTICE GENERALLY     int64\n",
       "ANTI-TRANSGENDER                      int64\n",
       "ANTI-WHITE                            int64\n",
       "Total                                 int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motivation-q3-2020.xlsx', skiprows=4)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "id": "83b75e06-6aef-4516-b661-3d74416d9c60",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = df.columns.str.lower().str.strip()\n",
    "df.columns = df.columns.str.replace('\\n', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "id": "979fc856-06d6-4347-a644-248874452a3f",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_54677/4190589498.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  last_row['period'] = '2020, Q3'\n"
     ]
    }
   ],
   "source": [
    "last_row = df.tail(1)\n",
    "last_row['period'] = '2020, Q3'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "69fa6259-2511-46f2-a2cc-0b84a3957120",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_row = last_row.rename(columns={'anti-lgbt (mixed group)': 'anti-lgbt', 'anti-male homosexual (gay)': 'anti-gay', 'anti-islamic(muslim)': 'anti-muslim'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "bf103876-e15f-47ff-be70-c039a33531eb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['precinct', 'anti-asian', 'anti-black', 'anti-catholic', 'anti-jewish',\n",
       "       'anti-gay', 'anti-other ethnicity', 'anti-transgender', 'anti-white',\n",
       "       'total', 'period'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "common_columns = totals_df.columns.intersection(last_row.columns)\n",
    "common_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "id": "b82c87fc-0745-4f28-be0f-0fb8f471366d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['anti-lgbt', 'anti-multi racial groups',\n",
       "       'anti-religious practice generally'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_columns1 = last_row.columns.difference(totals_df.columns)\n",
    "diff_columns1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "19de1902-0895-4838-8c3b-4695fb33246e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>anti-asian</th>\n",
       "      <th>anti-black</th>\n",
       "      <th>anti-catholic</th>\n",
       "      <th>anti-lesbian</th>\n",
       "      <th>anti-hindu</th>\n",
       "      <th>anti-muslim</th>\n",
       "      <th>anti-jewish</th>\n",
       "      <th>anti-gay</th>\n",
       "      <th>anti-other ethnicity</th>\n",
       "      <th>...</th>\n",
       "      <th>total</th>\n",
       "      <th>period</th>\n",
       "      <th>60 years old or more</th>\n",
       "      <th>anti-arab</th>\n",
       "      <th>anti-female</th>\n",
       "      <th>anti-gender non-conforming</th>\n",
       "      <th>anti-hispanic</th>\n",
       "      <th>anti-lgbt</th>\n",
       "      <th>anti-multi racial groups</th>\n",
       "      <th>anti-religious practice generally</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Total</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>84.0</td>\n",
       "      <td>2020, Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Total</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>46.0</td>\n",
       "      <td>2020, Q2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Total</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>75.0</td>\n",
       "      <td>2020, Q3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  precinct  anti-asian  anti-black  anti-catholic  anti-lesbian  anti-hindu  \\\n",
       "0    Total        11.0        12.0            1.0           1.0         1.0   \n",
       "1    Total         8.0         5.0            3.0           2.0         1.0   \n",
       "2    Total         4.0        16.0            3.0           NaN         NaN   \n",
       "\n",
       "   anti-muslim  anti-jewish  anti-gay  anti-other ethnicity  ...  total  \\\n",
       "0          1.0         45.0       5.0                   3.0  ...   84.0   \n",
       "1          0.0         15.0       4.0                   1.0  ...   46.0   \n",
       "2          NaN         28.0       8.0                   1.0  ...   75.0   \n",
       "\n",
       "     period  60 years old or more anti-arab  anti-female  \\\n",
       "0  2020, Q1                   NaN       NaN          NaN   \n",
       "1  2020, Q2                   1.0       1.0          0.0   \n",
       "2  2020, Q3                   NaN       NaN          NaN   \n",
       "\n",
       "   anti-gender non-conforming  anti-hispanic  anti-lgbt  \\\n",
       "0                         NaN            NaN        NaN   \n",
       "1                         2.0            0.0        NaN   \n",
       "2                         NaN            NaN        4.0   \n",
       "\n",
       "   anti-multi racial groups  anti-religious practice generally  \n",
       "0                       NaN                                NaN  \n",
       "1                       NaN                                NaN  \n",
       "2                       2.0                                2.0  \n",
       "\n",
       "[3 rows x 22 columns]"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals_df = pd.concat([totals_df, last_row], ignore_index=True)\n",
    "totals_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7037aceb-2997-43e2-a579-cab3a9afe00d",
   "metadata": {},
   "source": [
    "#### Q4, 2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "fce44f3a-5d20-4e49-9ecc-013d24577f16",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PRECINCT                             object\n",
       "ANTI-ASIAN                            int64\n",
       "ANTI-BLACK                            int64\n",
       "ANTI-CATHOLIC                         int64\n",
       "ANTI-FEMALE                           int64\n",
       "ANTI-FEMALE HOMOSEXUAL\\n(LESBIAN)     int64\n",
       "ANTI-HISPANIC                         int64\n",
       "ANTI-ISLAMIC\\n(MUSLIM)                int64\n",
       "ANTI-JEWISH                           int64\n",
       "ANTI-MALE HOMOSEXUAL\\n(GAY)           int64\n",
       "ANTI-OTHER ETHNICITY                  int64\n",
       "ANTI-WHITE                            int64\n",
       "Total                                 int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motivation-q4-2020.xlsx', skiprows=4)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "14f00606-966b-400c-b3f1-67ee6763e3af",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = df.columns.str.lower().str.strip()\n",
    "df.columns = df.columns.str.replace('\\n', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "412883a9-882a-4cc2-86d5-7b52a3c0ae15",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_54677/1596762532.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  last_row['period'] = '2020, Q4'\n"
     ]
    }
   ],
   "source": [
    "last_row = df.tail(1)\n",
    "last_row['period'] = '2020, Q4'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "bf3dc689-c33c-4e2a-85d4-092a03ac6d29",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_row = last_row.rename(columns={'anti-female homosexual(lesbian)': 'anti-lesbian', 'anti-male homosexual(gay)': 'anti-gay', 'anti-islamic(muslim)': 'anti-muslim'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "26094077-6e35-421a-ac74-465434283f7e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['precinct', 'anti-asian', 'anti-black', 'anti-catholic', 'anti-lesbian',\n",
       "       'anti-muslim', 'anti-jewish', 'anti-gay', 'anti-other ethnicity',\n",
       "       'anti-white', 'total', 'period', 'anti-female', 'anti-hispanic'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "common_columns = totals_df.columns.intersection(last_row.columns)\n",
    "common_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "bee64e94-282e-4c13-bbcb-1a9091a74388",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([], dtype='object')"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_columns1 = last_row.columns.difference(totals_df.columns)\n",
    "diff_columns1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "212391ab-6cad-477c-934e-b97464f456c8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>anti-asian</th>\n",
       "      <th>anti-black</th>\n",
       "      <th>anti-catholic</th>\n",
       "      <th>anti-lesbian</th>\n",
       "      <th>anti-hindu</th>\n",
       "      <th>anti-muslim</th>\n",
       "      <th>anti-jewish</th>\n",
       "      <th>anti-gay</th>\n",
       "      <th>anti-other ethnicity</th>\n",
       "      <th>...</th>\n",
       "      <th>total</th>\n",
       "      <th>period</th>\n",
       "      <th>60 years old or more</th>\n",
       "      <th>anti-arab</th>\n",
       "      <th>anti-female</th>\n",
       "      <th>anti-gender non-conforming</th>\n",
       "      <th>anti-hispanic</th>\n",
       "      <th>anti-lgbt</th>\n",
       "      <th>anti-multi racial groups</th>\n",
       "      <th>anti-religious practice generally</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Total</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>84.0</td>\n",
       "      <td>2020, Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Total</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>46.0</td>\n",
       "      <td>2020, Q2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Total</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>75.0</td>\n",
       "      <td>2020, Q3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Total</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>60.0</td>\n",
       "      <td>2020, Q4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  precinct  anti-asian  anti-black  anti-catholic  anti-lesbian  anti-hindu  \\\n",
       "0    Total        11.0        12.0            1.0           1.0         1.0   \n",
       "1    Total         8.0         5.0            3.0           2.0         1.0   \n",
       "2    Total         4.0        16.0            3.0           NaN         NaN   \n",
       "3    Total         3.0         8.0            1.0           1.0         NaN   \n",
       "\n",
       "   anti-muslim  anti-jewish  anti-gay  anti-other ethnicity  ...  total  \\\n",
       "0          1.0         45.0       5.0                   3.0  ...   84.0   \n",
       "1          0.0         15.0       4.0                   1.0  ...   46.0   \n",
       "2          NaN         28.0       8.0                   1.0  ...   75.0   \n",
       "3          3.0         28.0       8.0                   1.0  ...   60.0   \n",
       "\n",
       "     period  60 years old or more anti-arab  anti-female  \\\n",
       "0  2020, Q1                   NaN       NaN          NaN   \n",
       "1  2020, Q2                   1.0       1.0          0.0   \n",
       "2  2020, Q3                   NaN       NaN          NaN   \n",
       "3  2020, Q4                   NaN       NaN          5.0   \n",
       "\n",
       "   anti-gender non-conforming  anti-hispanic  anti-lgbt  \\\n",
       "0                         NaN            NaN        NaN   \n",
       "1                         2.0            0.0        NaN   \n",
       "2                         NaN            NaN        4.0   \n",
       "3                         NaN            1.0        NaN   \n",
       "\n",
       "   anti-multi racial groups  anti-religious practice generally  \n",
       "0                       NaN                                NaN  \n",
       "1                       NaN                                NaN  \n",
       "2                       2.0                                2.0  \n",
       "3                       NaN                                NaN  \n",
       "\n",
       "[4 rows x 22 columns]"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals_df = pd.concat([totals_df, last_row], ignore_index=True)\n",
    "totals_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7716baaa-1559-4bcc-8dbc-e2323c47ee88",
   "metadata": {},
   "source": [
    "#### Q1, 2021\n",
    "\n",
    "Observed issue: The filename of the dataset was misspelled, i.e. \"motiviation\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "71fb9ebe-dbde-4113-b598-8880188f359d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PRECINCT                            object\n",
       "ANTI-ASIAN                           int64\n",
       "ANTI-BLACK                           int64\n",
       "ANTI-FEMALE                          int64\n",
       "ANTI-FEMALE HOMOSEXUAL (LESBIAN)     int64\n",
       "ANTI-HISPANIC                        int64\n",
       "ANTI-JEWISH                          int64\n",
       "ANTI-LGBT (MIXED GROUP)              int64\n",
       "ANTI-MALE HOMOSEXUAL (GAY)           int64\n",
       "ANTI-MUSLIM                          int64\n",
       "ANTI-OTHER ETHNICITY                 int64\n",
       "ANTI-TRANSGENDER                     int64\n",
       "Grand Total                          int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motiviation-q1-2021.xlsx', skiprows=4)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "ac6705d0-53cf-48ff-a435-ba04e5138361",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = df.columns.str.lower().str.strip()\n",
    "df.columns = df.columns.str.replace('\\n', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "a3a972ef-bc40-4eb1-b442-45ba2e1a9dd5",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_54677/3486932570.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  last_row['period'] = '2021, Q1'\n"
     ]
    }
   ],
   "source": [
    "last_row = df.tail(1)\n",
    "last_row['period'] = '2021, Q1'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "7c6177e8-c722-477a-b53d-aa587f65e7a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_row = last_row.rename(columns={'anti-female homosexual (lesbian)': 'anti-lesbian', 'anti-male homosexual (gay)': 'anti-gay', 'anti-lgbt (mixed group)': 'anti-lgbt'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "8427d9c0-858c-48bf-ad28-827adba80c0f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['precinct', 'anti-asian', 'anti-black', 'anti-lesbian', 'anti-muslim',\n",
       "       'anti-jewish', 'anti-gay', 'anti-other ethnicity', 'anti-transgender',\n",
       "       'period', 'anti-female', 'anti-hispanic', 'anti-lgbt'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "common_columns = totals_df.columns.intersection(last_row.columns)\n",
    "common_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "40378801-a9e2-4185-8810-8b403b75b5bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['grand total'], dtype='object')"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_columns1 = last_row.columns.difference(totals_df.columns)\n",
    "diff_columns1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "d52a3945-bbdf-436a-a0e6-376aa3dfb497",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>anti-asian</th>\n",
       "      <th>anti-black</th>\n",
       "      <th>anti-catholic</th>\n",
       "      <th>anti-lesbian</th>\n",
       "      <th>anti-hindu</th>\n",
       "      <th>anti-muslim</th>\n",
       "      <th>anti-jewish</th>\n",
       "      <th>anti-gay</th>\n",
       "      <th>anti-other ethnicity</th>\n",
       "      <th>...</th>\n",
       "      <th>period</th>\n",
       "      <th>60 years old or more</th>\n",
       "      <th>anti-arab</th>\n",
       "      <th>anti-female</th>\n",
       "      <th>anti-gender non-conforming</th>\n",
       "      <th>anti-hispanic</th>\n",
       "      <th>anti-lgbt</th>\n",
       "      <th>anti-multi racial groups</th>\n",
       "      <th>anti-religious practice generally</th>\n",
       "      <th>grand total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Total</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Total</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Total</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Total</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>42.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2021, Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      precinct  anti-asian  anti-black  anti-catholic  anti-lesbian  \\\n",
       "0        Total        11.0        12.0            1.0           1.0   \n",
       "1        Total         8.0         5.0            3.0           2.0   \n",
       "2        Total         4.0        16.0            3.0           NaN   \n",
       "3        Total         3.0         8.0            1.0           1.0   \n",
       "4  Grand Total        42.0        12.0            NaN           1.0   \n",
       "\n",
       "   anti-hindu  anti-muslim  anti-jewish  anti-gay  anti-other ethnicity  ...  \\\n",
       "0         1.0          1.0         45.0       5.0                   3.0  ...   \n",
       "1         1.0          0.0         15.0       4.0                   1.0  ...   \n",
       "2         NaN          NaN         28.0       8.0                   1.0  ...   \n",
       "3         NaN          3.0         28.0       8.0                   1.0  ...   \n",
       "4         NaN          1.0         27.0       4.0                   2.0  ...   \n",
       "\n",
       "     period  60 years old or more  anti-arab anti-female  \\\n",
       "0  2020, Q1                   NaN        NaN         NaN   \n",
       "1  2020, Q2                   1.0        1.0         0.0   \n",
       "2  2020, Q3                   NaN        NaN         NaN   \n",
       "3  2020, Q4                   NaN        NaN         5.0   \n",
       "4  2021, Q1                   NaN        NaN         3.0   \n",
       "\n",
       "   anti-gender non-conforming  anti-hispanic  anti-lgbt  \\\n",
       "0                         NaN            NaN        NaN   \n",
       "1                         2.0            0.0        NaN   \n",
       "2                         NaN            NaN        4.0   \n",
       "3                         NaN            1.0        NaN   \n",
       "4                         NaN            1.0        1.0   \n",
       "\n",
       "   anti-multi racial groups  anti-religious practice generally  grand total  \n",
       "0                       NaN                                NaN          NaN  \n",
       "1                       NaN                                NaN          NaN  \n",
       "2                       2.0                                2.0          NaN  \n",
       "3                       NaN                                NaN          NaN  \n",
       "4                       NaN                                NaN         96.0  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals_df = pd.concat([totals_df, last_row], ignore_index=True)\n",
    "totals_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2fb6826-49e3-4184-84f5-f479a797cf7d",
   "metadata": {},
   "source": [
    "#### Q2, 2021\n",
    "\n",
    "Observed issue: Only one column name was different from that of the previous datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "id": "c721c901-8c1e-45ba-9019-9b992d7be1da",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PRECINCT                            object\n",
       "ANTI-ASIAN                           int64\n",
       "ANTI-WHITE                           int64\n",
       "ANTI-CATHOLIC                        int64\n",
       "ANTI-BLACK                           int64\n",
       "ANTI-FEMALE                          int64\n",
       "ANTI-GENDER NON CONFORMING           int64\n",
       "ANTI-FEMALE HOMOSEXUAL (LESBIAN)     int64\n",
       "ANTI-HISPANIC                        int64\n",
       "ANTI-JEWISH                          int64\n",
       "ANTI-LGBT (MIXED GROUP)              int64\n",
       "ANTI-MALE HOMOSEXUAL (GAY)           int64\n",
       "ANTI-MUSLIM                          int64\n",
       "ANTI-OTHER ETHNICITY                 int64\n",
       "ANTI-TRANSGENDER                     int64\n",
       "Grand Total                          int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motivation-q2-2021.xlsx', skiprows=4)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "f0cf3a33-a19a-4e61-ad83-d14945675dc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = df.columns.str.lower().str.strip()\n",
    "df.columns = df.columns.str.replace('\\n', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "93d44d29-4af0-48ca-bf91-cad76db63c09",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_54677/767617593.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  last_row['period'] = '2021, Q2'\n"
     ]
    }
   ],
   "source": [
    "last_row = df.tail(1)\n",
    "last_row['period'] = '2021, Q2'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "id": "f60b7b3b-1934-489d-ad84-4385d47680d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_row = last_row.rename(columns={'anti-gender non conforming': 'anti-gender non-conforming', 'anti-female homosexual (lesbian)': 'anti-lesbian', 'anti-male homosexual (gay)': 'anti-gay', 'anti-lgbt (mixed group)': 'anti-lgbt'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "3677f22c-03c7-4813-8fcc-77726fda4627",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['precinct', 'anti-asian', 'anti-black', 'anti-catholic', 'anti-lesbian',\n",
       "       'anti-muslim', 'anti-jewish', 'anti-gay', 'anti-other ethnicity',\n",
       "       'anti-transgender', 'anti-white', 'period', 'anti-female',\n",
       "       'anti-gender non-conforming', 'anti-hispanic', 'anti-lgbt',\n",
       "       'grand total'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "common_columns = totals_df.columns.intersection(last_row.columns)\n",
    "common_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "id": "a13c9d78-ae52-4ea2-9452-0f2e6ccb34ce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([], dtype='object')"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_columns1 = last_row.columns.difference(totals_df.columns)\n",
    "diff_columns1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "7d6228d3-9a9f-453b-9412-6703f5e8c51e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>anti-asian</th>\n",
       "      <th>anti-black</th>\n",
       "      <th>anti-catholic</th>\n",
       "      <th>anti-lesbian</th>\n",
       "      <th>anti-hindu</th>\n",
       "      <th>anti-muslim</th>\n",
       "      <th>anti-jewish</th>\n",
       "      <th>anti-gay</th>\n",
       "      <th>anti-other ethnicity</th>\n",
       "      <th>...</th>\n",
       "      <th>period</th>\n",
       "      <th>60 years old or more</th>\n",
       "      <th>anti-arab</th>\n",
       "      <th>anti-female</th>\n",
       "      <th>anti-gender non-conforming</th>\n",
       "      <th>anti-hispanic</th>\n",
       "      <th>anti-lgbt</th>\n",
       "      <th>anti-multi racial groups</th>\n",
       "      <th>anti-religious practice generally</th>\n",
       "      <th>grand total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Total</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Total</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Total</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Total</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>42.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2021, Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>63.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2021, Q2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>226.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      precinct  anti-asian  anti-black  anti-catholic  anti-lesbian  \\\n",
       "0        Total        11.0        12.0            1.0           1.0   \n",
       "1        Total         8.0         5.0            3.0           2.0   \n",
       "2        Total         4.0        16.0            3.0           NaN   \n",
       "3        Total         3.0         8.0            1.0           1.0   \n",
       "4  Grand Total        42.0        12.0            NaN           1.0   \n",
       "5  Grand Total        63.0        17.0            4.0           0.0   \n",
       "\n",
       "   anti-hindu  anti-muslim  anti-jewish  anti-gay  anti-other ethnicity  ...  \\\n",
       "0         1.0          1.0         45.0       5.0                   3.0  ...   \n",
       "1         1.0          0.0         15.0       4.0                   1.0  ...   \n",
       "2         NaN          NaN         28.0       8.0                   1.0  ...   \n",
       "3         NaN          3.0         28.0       8.0                   1.0  ...   \n",
       "4         NaN          1.0         27.0       4.0                   2.0  ...   \n",
       "5         NaN          5.0         85.0      29.0                   2.0  ...   \n",
       "\n",
       "     period  60 years old or more  anti-arab anti-female  \\\n",
       "0  2020, Q1                   NaN        NaN         NaN   \n",
       "1  2020, Q2                   1.0        1.0         0.0   \n",
       "2  2020, Q3                   NaN        NaN         NaN   \n",
       "3  2020, Q4                   NaN        NaN         5.0   \n",
       "4  2021, Q1                   NaN        NaN         3.0   \n",
       "5  2021, Q2                   NaN        NaN         0.0   \n",
       "\n",
       "   anti-gender non-conforming  anti-hispanic  anti-lgbt  \\\n",
       "0                         NaN            NaN        NaN   \n",
       "1                         2.0            0.0        NaN   \n",
       "2                         NaN            NaN        4.0   \n",
       "3                         NaN            1.0        NaN   \n",
       "4                         NaN            1.0        1.0   \n",
       "5                         2.0            2.0        1.0   \n",
       "\n",
       "   anti-multi racial groups  anti-religious practice generally  grand total  \n",
       "0                       NaN                                NaN          NaN  \n",
       "1                       NaN                                NaN          NaN  \n",
       "2                       2.0                                2.0          NaN  \n",
       "3                       NaN                                NaN          NaN  \n",
       "4                       NaN                                NaN         96.0  \n",
       "5                       NaN                                NaN        226.0  \n",
       "\n",
       "[6 rows x 23 columns]"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals_df = pd.concat([totals_df, last_row], ignore_index=True)\n",
    "totals_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ea67d5f-b27e-4a16-a4ce-741798881fcf",
   "metadata": {},
   "source": [
    "#### Q3, 2021"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "074ac8fb-889a-4bb0-a9ac-8dbef3a95e51",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PRECINCT                            object\n",
       "ANTI-ASIAN                           int64\n",
       "ANTI-WHITE                           int64\n",
       "ANTI-ARAB                            int64\n",
       "ANTI-CATHOLIC                        int64\n",
       "ANTI-BLACK                           int64\n",
       "ANTI-FEMALE                          int64\n",
       "ANTI-GENDER NON CONFORMING           int64\n",
       "ANTI-FEMALE HOMOSEXUAL (LESBIAN)     int64\n",
       "ANTI-HISPANIC                        int64\n",
       "ANTI-JEWISH                          int64\n",
       "ANTI-LGBT (MIXED GROUP)              int64\n",
       "ANTI-MALE HOMOSEXUAL (GAY)           int64\n",
       "ANTI-MUSLIM                          int64\n",
       "ANTI-OTHER ETHNICITY                 int64\n",
       "ANTI-TRANSGENDER                     int64\n",
       "Grand Total                          int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motivation-q3-2021.xlsx', skiprows=4)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "id": "1fb92475-c51b-4d14-b7e8-8879eeaa09cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = df.columns.str.lower().str.strip()\n",
    "df.columns = df.columns.str.replace('\\n', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "119fc72a-5aab-48b0-b0c2-ab0a33bfe9c1",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_54677/2826556745.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  last_row['period'] = '2021, Q3'\n"
     ]
    }
   ],
   "source": [
    "last_row = df.tail(1)\n",
    "last_row['period'] = '2021, Q3'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "id": "85c09388-5df9-4b35-95f7-7200c036bf96",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_row = last_row.rename(columns={'anti-gender non conforming': 'anti-gender non-conforming', 'anti-female homosexual (lesbian)': 'anti-lesbian', 'anti-male homosexual (gay)': 'anti-gay', 'anti-lgbt (mixed group)': 'anti-lgbt'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "id": "f1509cb5-8623-453c-8a63-716f158c608f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['precinct', 'anti-asian', 'anti-black', 'anti-catholic', 'anti-lesbian',\n",
       "       'anti-muslim', 'anti-jewish', 'anti-gay', 'anti-other ethnicity',\n",
       "       'anti-transgender', 'anti-white', 'period', 'anti-arab', 'anti-female',\n",
       "       'anti-gender non-conforming', 'anti-hispanic', 'anti-lgbt',\n",
       "       'grand total'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "common_columns = totals_df.columns.intersection(last_row.columns)\n",
    "common_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "d1da1196-942e-4052-9eec-6cdcf584560d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([], dtype='object')"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_columns1 = last_row.columns.difference(totals_df.columns)\n",
    "diff_columns1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "id": "7fadcbd3-781f-43be-97e3-0a8e03bd1f1d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>anti-asian</th>\n",
       "      <th>anti-black</th>\n",
       "      <th>anti-catholic</th>\n",
       "      <th>anti-lesbian</th>\n",
       "      <th>anti-hindu</th>\n",
       "      <th>anti-muslim</th>\n",
       "      <th>anti-jewish</th>\n",
       "      <th>anti-gay</th>\n",
       "      <th>anti-other ethnicity</th>\n",
       "      <th>...</th>\n",
       "      <th>period</th>\n",
       "      <th>60 years old or more</th>\n",
       "      <th>anti-arab</th>\n",
       "      <th>anti-female</th>\n",
       "      <th>anti-gender non-conforming</th>\n",
       "      <th>anti-hispanic</th>\n",
       "      <th>anti-lgbt</th>\n",
       "      <th>anti-multi racial groups</th>\n",
       "      <th>anti-religious practice generally</th>\n",
       "      <th>grand total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Total</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Total</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Total</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Total</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>42.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2021, Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>63.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2021, Q2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>226.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>20.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2021, Q3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>114.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      precinct  anti-asian  anti-black  anti-catholic  anti-lesbian  \\\n",
       "0        Total        11.0        12.0            1.0           1.0   \n",
       "1        Total         8.0         5.0            3.0           2.0   \n",
       "2        Total         4.0        16.0            3.0           NaN   \n",
       "3        Total         3.0         8.0            1.0           1.0   \n",
       "4  Grand Total        42.0        12.0            NaN           1.0   \n",
       "5  Grand Total        63.0        17.0            4.0           0.0   \n",
       "6  Grand Total        20.0         7.0            4.0           2.0   \n",
       "\n",
       "   anti-hindu  anti-muslim  anti-jewish  anti-gay  anti-other ethnicity  ...  \\\n",
       "0         1.0          1.0         45.0       5.0                   3.0  ...   \n",
       "1         1.0          0.0         15.0       4.0                   1.0  ...   \n",
       "2         NaN          NaN         28.0       8.0                   1.0  ...   \n",
       "3         NaN          3.0         28.0       8.0                   1.0  ...   \n",
       "4         NaN          1.0         27.0       4.0                   2.0  ...   \n",
       "5         NaN          5.0         85.0      29.0                   2.0  ...   \n",
       "6         NaN          4.0         38.0      27.0                   3.0  ...   \n",
       "\n",
       "     period  60 years old or more  anti-arab anti-female  \\\n",
       "0  2020, Q1                   NaN        NaN         NaN   \n",
       "1  2020, Q2                   1.0        1.0         0.0   \n",
       "2  2020, Q3                   NaN        NaN         NaN   \n",
       "3  2020, Q4                   NaN        NaN         5.0   \n",
       "4  2021, Q1                   NaN        NaN         3.0   \n",
       "5  2021, Q2                   NaN        NaN         0.0   \n",
       "6  2021, Q3                   NaN        1.0         0.0   \n",
       "\n",
       "   anti-gender non-conforming  anti-hispanic  anti-lgbt  \\\n",
       "0                         NaN            NaN        NaN   \n",
       "1                         2.0            0.0        NaN   \n",
       "2                         NaN            NaN        4.0   \n",
       "3                         NaN            1.0        NaN   \n",
       "4                         NaN            1.0        1.0   \n",
       "5                         2.0            2.0        1.0   \n",
       "6                         1.0            1.0        0.0   \n",
       "\n",
       "   anti-multi racial groups  anti-religious practice generally  grand total  \n",
       "0                       NaN                                NaN          NaN  \n",
       "1                       NaN                                NaN          NaN  \n",
       "2                       2.0                                2.0          NaN  \n",
       "3                       NaN                                NaN          NaN  \n",
       "4                       NaN                                NaN         96.0  \n",
       "5                       NaN                                NaN        226.0  \n",
       "6                       NaN                                NaN        114.0  \n",
       "\n",
       "[7 rows x 23 columns]"
      ]
     },
     "execution_count": 177,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals_df = pd.concat([totals_df, last_row], ignore_index=True)\n",
    "totals_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7444a3f8-3646-41c1-916f-720e7bd60204",
   "metadata": {},
   "source": [
    "#### Q4, 2021"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "id": "e939d879-7fe8-4215-a17a-e8322140996e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PRECINCT                            object\n",
       "ANTI-ASIAN                           int64\n",
       "ANTI-WHITE                           int64\n",
       "ANTI-ARAB                            int64\n",
       "ANTI-CATHOLIC                        int64\n",
       "ANTI-BLACK                           int64\n",
       "ANTI-FEMALE                          int64\n",
       "ANTI-GENDER NON CONFORMING           int64\n",
       "ANTI-FEMALE HOMOSEXUAL (LESBIAN)     int64\n",
       "ANTI-HISPANIC                        int64\n",
       "ANTI-JEWISH                          int64\n",
       "ANTI-LGBT (MIXED GROUP)              int64\n",
       "ANTI-MALE HOMOSEXUAL (GAY)           int64\n",
       "ANTI-MUSLIM                          int64\n",
       "ANTI-OTHER ETHNICITY                 int64\n",
       "ANTI-TRANSGENDER                     int64\n",
       "Grand Total                          int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motivation-q4-2021.xlsx', skiprows=4)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "id": "5f905a07-7e91-4946-ad3b-140b9a68eeae",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = df.columns.str.lower().str.strip()\n",
    "df.columns = df.columns.str.replace('\\n', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "id": "ac31ed0b-8db6-448b-a1a9-7e942380c700",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_54677/3900099325.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  last_row['period'] = '2021, Q4'\n"
     ]
    }
   ],
   "source": [
    "last_row = df.tail(1)\n",
    "last_row['period'] = '2021, Q4'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "id": "3a177731-c035-453b-85d7-4b96fb43a5f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_row = last_row.rename(columns={'anti-gender non conforming': 'anti-gender non-conforming', 'anti-female homosexual (lesbian)': 'anti-lesbian', 'anti-male homosexual (gay)': 'anti-gay', 'anti-lgbt (mixed group)': 'anti-lgbt'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "id": "63278d89-e5c8-4faa-a282-a3745eeeac8d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['precinct', 'anti-asian', 'anti-black', 'anti-catholic', 'anti-lesbian',\n",
       "       'anti-muslim', 'anti-jewish', 'anti-gay', 'anti-other ethnicity',\n",
       "       'anti-transgender', 'anti-white', 'period', 'anti-arab', 'anti-female',\n",
       "       'anti-gender non-conforming', 'anti-hispanic', 'anti-lgbt',\n",
       "       'grand total'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "common_columns = totals_df.columns.intersection(last_row.columns)\n",
    "common_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "id": "015e356d-4c66-4ac9-8d56-4fb6376fbde8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([], dtype='object')"
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_columns1 = last_row.columns.difference(totals_df.columns)\n",
    "diff_columns1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "id": "4071f145-7941-4d15-8dab-f53fe0561807",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>anti-asian</th>\n",
       "      <th>anti-black</th>\n",
       "      <th>anti-catholic</th>\n",
       "      <th>anti-lesbian</th>\n",
       "      <th>anti-hindu</th>\n",
       "      <th>anti-muslim</th>\n",
       "      <th>anti-jewish</th>\n",
       "      <th>anti-gay</th>\n",
       "      <th>anti-other ethnicity</th>\n",
       "      <th>...</th>\n",
       "      <th>period</th>\n",
       "      <th>60 years old or more</th>\n",
       "      <th>anti-arab</th>\n",
       "      <th>anti-female</th>\n",
       "      <th>anti-gender non-conforming</th>\n",
       "      <th>anti-hispanic</th>\n",
       "      <th>anti-lgbt</th>\n",
       "      <th>anti-multi racial groups</th>\n",
       "      <th>anti-religious practice generally</th>\n",
       "      <th>grand total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Total</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Total</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Total</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Total</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>42.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2021, Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>63.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2021, Q2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>226.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>20.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2021, Q3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>114.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2021, Q4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>112.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      precinct  anti-asian  anti-black  anti-catholic  anti-lesbian  \\\n",
       "0        Total        11.0        12.0            1.0           1.0   \n",
       "1        Total         8.0         5.0            3.0           2.0   \n",
       "2        Total         4.0        16.0            3.0           NaN   \n",
       "3        Total         3.0         8.0            1.0           1.0   \n",
       "4  Grand Total        42.0        12.0            NaN           1.0   \n",
       "5  Grand Total        63.0        17.0            4.0           0.0   \n",
       "6  Grand Total        20.0         7.0            4.0           2.0   \n",
       "7  Grand Total        13.0         9.0            1.0           1.0   \n",
       "\n",
       "   anti-hindu  anti-muslim  anti-jewish  anti-gay  anti-other ethnicity  ...  \\\n",
       "0         1.0          1.0         45.0       5.0                   3.0  ...   \n",
       "1         1.0          0.0         15.0       4.0                   1.0  ...   \n",
       "2         NaN          NaN         28.0       8.0                   1.0  ...   \n",
       "3         NaN          3.0         28.0       8.0                   1.0  ...   \n",
       "4         NaN          1.0         27.0       4.0                   2.0  ...   \n",
       "5         NaN          5.0         85.0      29.0                   2.0  ...   \n",
       "6         NaN          4.0         38.0      27.0                   3.0  ...   \n",
       "7         NaN          2.0         57.0      13.0                   3.0  ...   \n",
       "\n",
       "     period  60 years old or more  anti-arab anti-female  \\\n",
       "0  2020, Q1                   NaN        NaN         NaN   \n",
       "1  2020, Q2                   1.0        1.0         0.0   \n",
       "2  2020, Q3                   NaN        NaN         NaN   \n",
       "3  2020, Q4                   NaN        NaN         5.0   \n",
       "4  2021, Q1                   NaN        NaN         3.0   \n",
       "5  2021, Q2                   NaN        NaN         0.0   \n",
       "6  2021, Q3                   NaN        1.0         0.0   \n",
       "7  2021, Q4                   NaN        0.0         0.0   \n",
       "\n",
       "   anti-gender non-conforming  anti-hispanic  anti-lgbt  \\\n",
       "0                         NaN            NaN        NaN   \n",
       "1                         2.0            0.0        NaN   \n",
       "2                         NaN            NaN        4.0   \n",
       "3                         NaN            1.0        NaN   \n",
       "4                         NaN            1.0        1.0   \n",
       "5                         2.0            2.0        1.0   \n",
       "6                         1.0            1.0        0.0   \n",
       "7                         0.0            2.0        0.0   \n",
       "\n",
       "   anti-multi racial groups  anti-religious practice generally  grand total  \n",
       "0                       NaN                                NaN          NaN  \n",
       "1                       NaN                                NaN          NaN  \n",
       "2                       2.0                                2.0          NaN  \n",
       "3                       NaN                                NaN          NaN  \n",
       "4                       NaN                                NaN         96.0  \n",
       "5                       NaN                                NaN        226.0  \n",
       "6                       NaN                                NaN        114.0  \n",
       "7                       NaN                                NaN        112.0  \n",
       "\n",
       "[8 rows x 23 columns]"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals_df = pd.concat([totals_df, last_row], ignore_index=True)\n",
    "totals_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "id": "26e099e8-f078-423c-9ca5-e142a42e9968",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save as CSV for checkpoint\n",
    "\n",
    "totals_df.to_csv('totals.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "201369e1-2011-4ee4-a8e5-efb73aaf8188",
   "metadata": {},
   "source": [
    "### Using `def` to shorten repetitive tasks\n",
    "\n",
    "At this point, I will try setting a new function through `def` to hopefully make the routine easier."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "id": "d429ff89-71f2-477e-827d-35eebe3d82d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean(df):\n",
    "    df.columns = df.columns.str.lower().str.strip()\n",
    "    df.columns = df.columns.str.replace('\\n', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "id": "9d6ddf4c-67cd-4775-820c-feb142bb9124",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This did not work...\n",
    "\n",
    "def merge_totals(df):\n",
    "    totals_df = pd.concat([totals_df, last_row], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "id": "dc79fcf1-6755-4f1d-8ad2-d5073362a70c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This also did not work...\n",
    "\n",
    "def common(df):\n",
    "    df = df.rename(columns={'anti-other religion': 'anti-religious practice generally', 'anti-gender non conforming': 'anti-gender non-conforming', 'anti-female homosexual (lesbian)': 'anti-lesbian', 'anti-male homosexual (gay)': 'anti-gay', 'anti-lgbt (mixed group)': 'anti-lgbt'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb4e756a-d2bf-4a00-aa0c-816f7f98dcb7",
   "metadata": {},
   "source": [
    "#### Q1, 2022"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "id": "98a87e19-15ab-4a1a-8e94-31f7def0bdb8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PRECINCT                            object\n",
       "ANTI-ASIAN                           int64\n",
       "ANTI-BLACK                           int64\n",
       "ANTI-CATHOLIC                        int64\n",
       "ANTI-FEMALE HOMOSEXUAL (LESBIAN)     int64\n",
       "ANTI-HISPANIC                        int64\n",
       "ANTI-JEWISH                          int64\n",
       "ANTI-MALE HOMOSEXUAL (GAY)           int64\n",
       "ANTI-MUSLIM                          int64\n",
       "ANTI-OTHER ETHNICITY                 int64\n",
       "ANTI-OTHER RELIGION                  int64\n",
       "ANTI-TRANSGENDER                     int64\n",
       "ANTI-WHITE                           int64\n",
       "Grand Total                          int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 189,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motivation-q1-2022.xlsx', skiprows=4)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "id": "058e2290-ab82-48c5-aee8-c3523ec9b640",
   "metadata": {},
   "outputs": [],
   "source": [
    "clean(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "id": "d6435f24-594d-4403-8fff-3ee6e6d7cd60",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>anti-asian</th>\n",
       "      <th>anti-black</th>\n",
       "      <th>anti-catholic</th>\n",
       "      <th>anti-female homosexual (lesbian)</th>\n",
       "      <th>anti-hispanic</th>\n",
       "      <th>anti-jewish</th>\n",
       "      <th>anti-male homosexual (gay)</th>\n",
       "      <th>anti-muslim</th>\n",
       "      <th>anti-other ethnicity</th>\n",
       "      <th>anti-other religion</th>\n",
       "      <th>anti-transgender</th>\n",
       "      <th>anti-white</th>\n",
       "      <th>grand total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>005</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>006</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>007</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>009</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>120</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>121</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>122</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>123</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>17</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>67</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>142</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>78 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       precinct  anti-asian  anti-black  anti-catholic  \\\n",
       "0           001           0           2              0   \n",
       "1           005           1           0              0   \n",
       "2           006           0           0              0   \n",
       "3           007           2           1              0   \n",
       "4           009           2           0              0   \n",
       "..          ...         ...         ...            ...   \n",
       "73          120           0           0              0   \n",
       "74          121           0           1              0   \n",
       "75          122           0           1              0   \n",
       "76          123           0           0              0   \n",
       "77  Grand Total          17          20              3   \n",
       "\n",
       "    anti-female homosexual (lesbian)  anti-hispanic  anti-jewish  \\\n",
       "0                                  0              0            5   \n",
       "1                                  0              0            1   \n",
       "2                                  0              0            0   \n",
       "3                                  0              0            2   \n",
       "4                                  0              0            0   \n",
       "..                               ...            ...          ...   \n",
       "73                                 0              0            0   \n",
       "74                                 0              0            0   \n",
       "75                                 0              0            0   \n",
       "76                                 0              0            0   \n",
       "77                                 1              2           67   \n",
       "\n",
       "    anti-male homosexual (gay)  anti-muslim  anti-other ethnicity  \\\n",
       "0                            0            0                     0   \n",
       "1                            0            0                     0   \n",
       "2                            0            0                     0   \n",
       "3                            0            0                     0   \n",
       "4                            1            0                     0   \n",
       "..                         ...          ...                   ...   \n",
       "73                           0            0                     0   \n",
       "74                           0            0                     0   \n",
       "75                           0            0                     0   \n",
       "76                           0            0                     0   \n",
       "77                          15            3                     7   \n",
       "\n",
       "    anti-other religion  anti-transgender  anti-white  grand total  \n",
       "0                     0                 0           0            7  \n",
       "1                     0                 0           1            3  \n",
       "2                     0                 0           0            0  \n",
       "3                     0                 0           0            5  \n",
       "4                     0                 0           0            3  \n",
       "..                  ...               ...         ...          ...  \n",
       "73                    0                 0           0            0  \n",
       "74                    0                 0           0            1  \n",
       "75                    0                 0           0            1  \n",
       "76                    0                 0           0            0  \n",
       "77                    2                 4           1          142  \n",
       "\n",
       "[78 rows x 14 columns]"
      ]
     },
     "execution_count": 191,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "id": "956f777f-4724-4614-a7b2-b48ec76e6a36",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_54677/2695052722.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  last_row['period'] = '2022, Q1'\n"
     ]
    }
   ],
   "source": [
    "# I can't `def` the following because the 'period' column has to be specified.\n",
    "\n",
    "last_row = df.tail(1)\n",
    "last_row['period'] = '2022, Q1'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "id": "82a3c818-936d-4bf7-b397-98e32c9e7d5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_row = last_row.rename(columns={'anti-gender non conforming': 'anti-gender non-conforming', 'anti-female homosexual (lesbian)': 'anti-lesbian', 'anti-male homosexual (gay)': 'anti-gay', 'anti-lgbt (mixed group)': 'anti-lgbt'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "id": "0b61dfe2-7c25-4e73-9758-69f91035cdfb",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_row = last_row.rename(columns={'anti-other religion': 'anti-religious practice generally'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "id": "833f5a7e-c56b-43c1-88b0-966a15986c1f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['precinct', 'anti-asian', 'anti-black', 'anti-catholic', 'anti-lesbian',\n",
       "       'anti-muslim', 'anti-jewish', 'anti-gay', 'anti-other ethnicity',\n",
       "       'anti-transgender', 'anti-white', 'period', 'anti-hispanic',\n",
       "       'anti-religious practice generally', 'grand total'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 195,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "common_columns = totals_df.columns.intersection(last_row.columns)\n",
    "common_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "id": "44f71f82-af06-40a6-b409-6251b7cc4ce8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([], dtype='object')"
      ]
     },
     "execution_count": 196,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_columns1 = last_row.columns.difference(totals_df.columns)\n",
    "diff_columns1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "id": "8e88ea33-3bdb-4600-bc44-e6d17511f6eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "totals_df = pd.concat([totals_df, last_row], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "id": "f3a469f3-0bb1-482b-ad1f-3a8ca620f4e7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>anti-asian</th>\n",
       "      <th>anti-black</th>\n",
       "      <th>anti-catholic</th>\n",
       "      <th>anti-lesbian</th>\n",
       "      <th>anti-hindu</th>\n",
       "      <th>anti-muslim</th>\n",
       "      <th>anti-jewish</th>\n",
       "      <th>anti-gay</th>\n",
       "      <th>anti-other ethnicity</th>\n",
       "      <th>...</th>\n",
       "      <th>period</th>\n",
       "      <th>60 years old or more</th>\n",
       "      <th>anti-arab</th>\n",
       "      <th>anti-female</th>\n",
       "      <th>anti-gender non-conforming</th>\n",
       "      <th>anti-hispanic</th>\n",
       "      <th>anti-lgbt</th>\n",
       "      <th>anti-multi racial groups</th>\n",
       "      <th>anti-religious practice generally</th>\n",
       "      <th>grand total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Total</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Total</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Total</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Total</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2020, Q4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>42.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2021, Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>63.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2021, Q2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>226.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>20.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2021, Q3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>114.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2021, Q4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>112.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>17.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2022, Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>142.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      precinct  anti-asian  anti-black  anti-catholic  anti-lesbian  \\\n",
       "0        Total        11.0        12.0            1.0           1.0   \n",
       "1        Total         8.0         5.0            3.0           2.0   \n",
       "2        Total         4.0        16.0            3.0           NaN   \n",
       "3        Total         3.0         8.0            1.0           1.0   \n",
       "4  Grand Total        42.0        12.0            NaN           1.0   \n",
       "5  Grand Total        63.0        17.0            4.0           0.0   \n",
       "6  Grand Total        20.0         7.0            4.0           2.0   \n",
       "7  Grand Total        13.0         9.0            1.0           1.0   \n",
       "8  Grand Total        17.0        20.0            3.0           1.0   \n",
       "\n",
       "   anti-hindu  anti-muslim  anti-jewish  anti-gay  anti-other ethnicity  ...  \\\n",
       "0         1.0          1.0         45.0       5.0                   3.0  ...   \n",
       "1         1.0          0.0         15.0       4.0                   1.0  ...   \n",
       "2         NaN          NaN         28.0       8.0                   1.0  ...   \n",
       "3         NaN          3.0         28.0       8.0                   1.0  ...   \n",
       "4         NaN          1.0         27.0       4.0                   2.0  ...   \n",
       "5         NaN          5.0         85.0      29.0                   2.0  ...   \n",
       "6         NaN          4.0         38.0      27.0                   3.0  ...   \n",
       "7         NaN          2.0         57.0      13.0                   3.0  ...   \n",
       "8         NaN          3.0         67.0      15.0                   7.0  ...   \n",
       "\n",
       "     period  60 years old or more  anti-arab anti-female  \\\n",
       "0  2020, Q1                   NaN        NaN         NaN   \n",
       "1  2020, Q2                   1.0        1.0         0.0   \n",
       "2  2020, Q3                   NaN        NaN         NaN   \n",
       "3  2020, Q4                   NaN        NaN         5.0   \n",
       "4  2021, Q1                   NaN        NaN         3.0   \n",
       "5  2021, Q2                   NaN        NaN         0.0   \n",
       "6  2021, Q3                   NaN        1.0         0.0   \n",
       "7  2021, Q4                   NaN        0.0         0.0   \n",
       "8  2022, Q1                   NaN        NaN         NaN   \n",
       "\n",
       "   anti-gender non-conforming  anti-hispanic  anti-lgbt  \\\n",
       "0                         NaN            NaN        NaN   \n",
       "1                         2.0            0.0        NaN   \n",
       "2                         NaN            NaN        4.0   \n",
       "3                         NaN            1.0        NaN   \n",
       "4                         NaN            1.0        1.0   \n",
       "5                         2.0            2.0        1.0   \n",
       "6                         1.0            1.0        0.0   \n",
       "7                         0.0            2.0        0.0   \n",
       "8                         NaN            2.0        NaN   \n",
       "\n",
       "   anti-multi racial groups  anti-religious practice generally  grand total  \n",
       "0                       NaN                                NaN          NaN  \n",
       "1                       NaN                                NaN          NaN  \n",
       "2                       2.0                                2.0          NaN  \n",
       "3                       NaN                                NaN          NaN  \n",
       "4                       NaN                                NaN         96.0  \n",
       "5                       NaN                                NaN        226.0  \n",
       "6                       NaN                                NaN        114.0  \n",
       "7                       NaN                                NaN        112.0  \n",
       "8                       NaN                                2.0        142.0  \n",
       "\n",
       "[9 rows x 23 columns]"
      ]
     },
     "execution_count": 198,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6dcac52-e55a-4cd4-a11f-4ae2b7801aaa",
   "metadata": {},
   "source": [
    "#### Q2, 2022"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "id": "c4064770-0df0-4516-8e73-3af15be4068d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "precinct                      object\n",
       "anti-asian                     int64\n",
       "anti-black                     int64\n",
       "anti-catholic                  int64\n",
       "anti-hispanic                  int64\n",
       "anti-male homosexual (gay)     int64\n",
       "anti-muslim                    int64\n",
       "anti-other ethnicity           int64\n",
       "anti-jewish                    int64\n",
       "anti-sikh                      int64\n",
       "anti-other religion            int64\n",
       "anti-transgender               int64\n",
       "anti-white                     int64\n",
       "grand total                    int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 199,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motivation-q2-2022.xlsx', skiprows=4)\n",
    "clean(df)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "id": "520011f4-439d-49d3-9afe-beae9914e5ee",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_54677/464266979.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  last_row['period'] = '2022, Q2'\n"
     ]
    }
   ],
   "source": [
    "last_row = df.tail(1)\n",
    "last_row['period'] = '2022, Q2'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "id": "1c1a5612-a87c-4820-9f0e-dd556f12c2c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_row = last_row.rename(columns={'anti-other religion': 'anti-religious practice generally', 'anti-gender non conforming': 'anti-gender non-conforming', 'anti-female homosexual (lesbian)': 'anti-lesbian', 'anti-male homosexual (gay)': 'anti-gay', 'anti-lgbt (mixed group)': 'anti-lgbt'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "id": "b5c28a1a-7aba-4a78-af83-30b0f5b9c6ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['precinct', 'anti-asian', 'anti-black', 'anti-catholic', 'anti-muslim',\n",
       "       'anti-jewish', 'anti-gay', 'anti-other ethnicity', 'anti-transgender',\n",
       "       'anti-white', 'period', 'anti-hispanic',\n",
       "       'anti-religious practice generally', 'grand total'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 202,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "common_columns = totals_df.columns.intersection(last_row.columns)\n",
    "common_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "id": "2f2fe91e-913f-470f-a77f-12ebbe1fe714",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['anti-sikh'], dtype='object')"
      ]
     },
     "execution_count": 203,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_columns1 = last_row.columns.difference(totals_df.columns)\n",
    "diff_columns1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "id": "2a445c6a-23c2-4cc7-80e7-202282bbb07e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>anti-asian</th>\n",
       "      <th>anti-black</th>\n",
       "      <th>anti-catholic</th>\n",
       "      <th>anti-lesbian</th>\n",
       "      <th>anti-hindu</th>\n",
       "      <th>anti-muslim</th>\n",
       "      <th>anti-jewish</th>\n",
       "      <th>anti-gay</th>\n",
       "      <th>anti-other ethnicity</th>\n",
       "      <th>...</th>\n",
       "      <th>60 years old or more</th>\n",
       "      <th>anti-arab</th>\n",
       "      <th>anti-female</th>\n",
       "      <th>anti-gender non-conforming</th>\n",
       "      <th>anti-hispanic</th>\n",
       "      <th>anti-lgbt</th>\n",
       "      <th>anti-multi racial groups</th>\n",
       "      <th>anti-religious practice generally</th>\n",
       "      <th>grand total</th>\n",
       "      <th>anti-sikh</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Total</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Total</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Total</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Total</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>42.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>63.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>226.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>20.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>114.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>112.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>17.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>20.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      precinct  anti-asian  anti-black  anti-catholic  anti-lesbian  \\\n",
       "0        Total        11.0        12.0            1.0           1.0   \n",
       "1        Total         8.0         5.0            3.0           2.0   \n",
       "2        Total         4.0        16.0            3.0           NaN   \n",
       "3        Total         3.0         8.0            1.0           1.0   \n",
       "4  Grand Total        42.0        12.0            NaN           1.0   \n",
       "5  Grand Total        63.0        17.0            4.0           0.0   \n",
       "6  Grand Total        20.0         7.0            4.0           2.0   \n",
       "7  Grand Total        13.0         9.0            1.0           1.0   \n",
       "8  Grand Total        17.0        20.0            3.0           1.0   \n",
       "9  Grand Total        20.0        11.0            3.0           NaN   \n",
       "\n",
       "   anti-hindu  anti-muslim  anti-jewish  anti-gay  anti-other ethnicity  ...  \\\n",
       "0         1.0          1.0         45.0       5.0                   3.0  ...   \n",
       "1         1.0          0.0         15.0       4.0                   1.0  ...   \n",
       "2         NaN          NaN         28.0       8.0                   1.0  ...   \n",
       "3         NaN          3.0         28.0       8.0                   1.0  ...   \n",
       "4         NaN          1.0         27.0       4.0                   2.0  ...   \n",
       "5         NaN          5.0         85.0      29.0                   2.0  ...   \n",
       "6         NaN          4.0         38.0      27.0                   3.0  ...   \n",
       "7         NaN          2.0         57.0      13.0                   3.0  ...   \n",
       "8         NaN          3.0         67.0      15.0                   7.0  ...   \n",
       "9         NaN         14.0         64.0      24.0                   9.0  ...   \n",
       "\n",
       "   60 years old or more  anti-arab  anti-female anti-gender non-conforming  \\\n",
       "0                   NaN        NaN          NaN                        NaN   \n",
       "1                   1.0        1.0          0.0                        2.0   \n",
       "2                   NaN        NaN          NaN                        NaN   \n",
       "3                   NaN        NaN          5.0                        NaN   \n",
       "4                   NaN        NaN          3.0                        NaN   \n",
       "5                   NaN        NaN          0.0                        2.0   \n",
       "6                   NaN        1.0          0.0                        1.0   \n",
       "7                   NaN        0.0          0.0                        0.0   \n",
       "8                   NaN        NaN          NaN                        NaN   \n",
       "9                   NaN        NaN          NaN                        NaN   \n",
       "\n",
       "   anti-hispanic  anti-lgbt  anti-multi racial groups  \\\n",
       "0            NaN        NaN                       NaN   \n",
       "1            0.0        NaN                       NaN   \n",
       "2            NaN        4.0                       2.0   \n",
       "3            1.0        NaN                       NaN   \n",
       "4            1.0        1.0                       NaN   \n",
       "5            2.0        1.0                       NaN   \n",
       "6            1.0        0.0                       NaN   \n",
       "7            2.0        0.0                       NaN   \n",
       "8            2.0        NaN                       NaN   \n",
       "9            6.0        NaN                       NaN   \n",
       "\n",
       "   anti-religious practice generally  grand total  anti-sikh  \n",
       "0                                NaN          NaN        NaN  \n",
       "1                                NaN          NaN        NaN  \n",
       "2                                2.0          NaN        NaN  \n",
       "3                                NaN          NaN        NaN  \n",
       "4                                NaN         96.0        NaN  \n",
       "5                                NaN        226.0        NaN  \n",
       "6                                NaN        114.0        NaN  \n",
       "7                                NaN        112.0        NaN  \n",
       "8                                2.0        142.0        NaN  \n",
       "9                                0.0        155.0        2.0  \n",
       "\n",
       "[10 rows x 24 columns]"
      ]
     },
     "execution_count": 204,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals_df = pd.concat([totals_df, last_row], ignore_index=True)\n",
    "totals_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd4077b8-dee0-45fc-81a4-0c7735d630ab",
   "metadata": {},
   "source": [
    "#### Q3, 2022\n",
    "\n",
    "Observed issue: The filename was misspelled."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "id": "74761bd8-48d4-4007-b1d4-42f13add58c8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "precinct                            object\n",
       "anti-asian                           int64\n",
       "anti-black                           int64\n",
       "anti-eastern orthodox                int64\n",
       "anti-female homosexual (lesbian)     int64\n",
       "anti-hindu                           int64\n",
       "anti-hispanic                        int64\n",
       "anti-jehovahs witness                int64\n",
       "anti-jewish                          int64\n",
       "anti-male homosexual (gay)           int64\n",
       "anti-other ethnicity                 int64\n",
       "anti-transgender                     int64\n",
       "anti-white                           int64\n",
       "grand total                          int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 205,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motiviation-q3-2022.xlsx', skiprows=4)\n",
    "clean(df)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "id": "ff8364ef-b931-4401-9238-50434ebeb33e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_54677/1564026510.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  last_row['period'] = '2022, Q3'\n"
     ]
    }
   ],
   "source": [
    "last_row = df.tail(1)\n",
    "last_row['period'] = '2022, Q3'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "id": "4ccbd51f-1db9-4e7d-8340-cda03db54810",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_row = last_row.rename(columns={'anti-other religion': 'anti-religious practice generally', 'anti-gender non conforming': 'anti-gender non-conforming', 'anti-female homosexual (lesbian)': 'anti-lesbian', 'anti-male homosexual (gay)': 'anti-gay', 'anti-lgbt (mixed group)': 'anti-lgbt'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "id": "a4bbe7d1-e5ae-4381-83c3-9716d57f5db0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['anti-eastern orthodox', 'anti-jehovahs witness'], dtype='object')"
      ]
     },
     "execution_count": 208,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_columns1 = last_row.columns.difference(totals_df.columns)\n",
    "diff_columns1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "id": "2abdb51f-2d60-42e1-a0f1-79803f077b42",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['precinct', 'anti-asian', 'anti-black', 'anti-lesbian', 'anti-hindu',\n",
       "       'anti-jewish', 'anti-gay', 'anti-other ethnicity', 'anti-transgender',\n",
       "       'anti-white', 'period', 'anti-hispanic', 'grand total'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 209,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "common_columns = totals_df.columns.intersection(last_row.columns)\n",
    "common_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 210,
   "id": "52d521a7-894b-4204-936d-162eda26de66",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>anti-asian</th>\n",
       "      <th>anti-black</th>\n",
       "      <th>anti-catholic</th>\n",
       "      <th>anti-lesbian</th>\n",
       "      <th>anti-hindu</th>\n",
       "      <th>anti-muslim</th>\n",
       "      <th>anti-jewish</th>\n",
       "      <th>anti-gay</th>\n",
       "      <th>anti-other ethnicity</th>\n",
       "      <th>...</th>\n",
       "      <th>anti-female</th>\n",
       "      <th>anti-gender non-conforming</th>\n",
       "      <th>anti-hispanic</th>\n",
       "      <th>anti-lgbt</th>\n",
       "      <th>anti-multi racial groups</th>\n",
       "      <th>anti-religious practice generally</th>\n",
       "      <th>grand total</th>\n",
       "      <th>anti-sikh</th>\n",
       "      <th>anti-eastern orthodox</th>\n",
       "      <th>anti-jehovahs witness</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Total</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Total</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Total</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Total</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>42.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>63.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>226.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>20.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>114.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>112.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>17.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>20.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>21.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>131.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       precinct  anti-asian  anti-black  anti-catholic  anti-lesbian  \\\n",
       "0         Total        11.0        12.0            1.0           1.0   \n",
       "1         Total         8.0         5.0            3.0           2.0   \n",
       "2         Total         4.0        16.0            3.0           NaN   \n",
       "3         Total         3.0         8.0            1.0           1.0   \n",
       "4   Grand Total        42.0        12.0            NaN           1.0   \n",
       "5   Grand Total        63.0        17.0            4.0           0.0   \n",
       "6   Grand Total        20.0         7.0            4.0           2.0   \n",
       "7   Grand Total        13.0         9.0            1.0           1.0   \n",
       "8   Grand Total        17.0        20.0            3.0           1.0   \n",
       "9   Grand Total        20.0        11.0            3.0           NaN   \n",
       "10  Grand Total        21.0        11.0            NaN           5.0   \n",
       "\n",
       "    anti-hindu  anti-muslim  anti-jewish  anti-gay  anti-other ethnicity  ...  \\\n",
       "0          1.0          1.0         45.0       5.0                   3.0  ...   \n",
       "1          1.0          0.0         15.0       4.0                   1.0  ...   \n",
       "2          NaN          NaN         28.0       8.0                   1.0  ...   \n",
       "3          NaN          3.0         28.0       8.0                   1.0  ...   \n",
       "4          NaN          1.0         27.0       4.0                   2.0  ...   \n",
       "5          NaN          5.0         85.0      29.0                   2.0  ...   \n",
       "6          NaN          4.0         38.0      27.0                   3.0  ...   \n",
       "7          NaN          2.0         57.0      13.0                   3.0  ...   \n",
       "8          NaN          3.0         67.0      15.0                   7.0  ...   \n",
       "9          NaN         14.0         64.0      24.0                   9.0  ...   \n",
       "10         3.0          NaN         47.0      18.0                  14.0  ...   \n",
       "\n",
       "    anti-female  anti-gender non-conforming  anti-hispanic anti-lgbt  \\\n",
       "0           NaN                         NaN            NaN       NaN   \n",
       "1           0.0                         2.0            0.0       NaN   \n",
       "2           NaN                         NaN            NaN       4.0   \n",
       "3           5.0                         NaN            1.0       NaN   \n",
       "4           3.0                         NaN            1.0       1.0   \n",
       "5           0.0                         2.0            2.0       1.0   \n",
       "6           0.0                         1.0            1.0       0.0   \n",
       "7           0.0                         0.0            2.0       0.0   \n",
       "8           NaN                         NaN            2.0       NaN   \n",
       "9           NaN                         NaN            6.0       NaN   \n",
       "10          NaN                         NaN            1.0       NaN   \n",
       "\n",
       "    anti-multi racial groups  anti-religious practice generally  grand total  \\\n",
       "0                        NaN                                NaN          NaN   \n",
       "1                        NaN                                NaN          NaN   \n",
       "2                        2.0                                2.0          NaN   \n",
       "3                        NaN                                NaN          NaN   \n",
       "4                        NaN                                NaN         96.0   \n",
       "5                        NaN                                NaN        226.0   \n",
       "6                        NaN                                NaN        114.0   \n",
       "7                        NaN                                NaN        112.0   \n",
       "8                        NaN                                2.0        142.0   \n",
       "9                        NaN                                0.0        155.0   \n",
       "10                       NaN                                NaN        131.0   \n",
       "\n",
       "    anti-sikh  anti-eastern orthodox  anti-jehovahs witness  \n",
       "0         NaN                    NaN                    NaN  \n",
       "1         NaN                    NaN                    NaN  \n",
       "2         NaN                    NaN                    NaN  \n",
       "3         NaN                    NaN                    NaN  \n",
       "4         NaN                    NaN                    NaN  \n",
       "5         NaN                    NaN                    NaN  \n",
       "6         NaN                    NaN                    NaN  \n",
       "7         NaN                    NaN                    NaN  \n",
       "8         NaN                    NaN                    NaN  \n",
       "9         2.0                    NaN                    NaN  \n",
       "10        NaN                    2.0                    1.0  \n",
       "\n",
       "[11 rows x 26 columns]"
      ]
     },
     "execution_count": 210,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals_df = pd.concat([totals_df, last_row], ignore_index=True)\n",
    "totals_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3584868-eaf1-4a6b-b469-f555b4d7de93",
   "metadata": {},
   "source": [
    "#### Q4, 2022"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 211,
   "id": "4b7dd8b6-df2e-478f-943d-91245d862d49",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "precinct                             object\n",
       "anti-asian                            int64\n",
       "anti-black                            int64\n",
       "anti-buddhist                         int64\n",
       "anti-catholic                         int64\n",
       "anti-female homosexual (lesbian)      int64\n",
       "anti-hispanic                         int64\n",
       "anti-jewish                           int64\n",
       "anti-male homosexual (gay)            int64\n",
       "anti-other ethnicity                  int64\n",
       "anti-protestant                       int64\n",
       "anti-religious practice generally     int64\n",
       "anti-transgender                      int64\n",
       "anti-white                            int64\n",
       "grand total                           int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 211,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motivation-q4-2022.xlsx', skiprows=4)\n",
    "clean(df)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "id": "f4822879-0195-42cd-875a-d1060aa54443",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_54677/3543889445.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  last_row['period'] = '2022, Q4'\n"
     ]
    }
   ],
   "source": [
    "last_row = df.tail(1)\n",
    "last_row['period'] = '2022, Q4'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "id": "343e7b4d-f21c-40ed-b0fc-7e92b675342f",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_row = last_row.rename(columns={'anti-other religion': 'anti-religious practice generally', 'anti-gender non conforming': 'anti-gender non-conforming', 'anti-female homosexual (lesbian)': 'anti-lesbian', 'anti-male homosexual (gay)': 'anti-gay', 'anti-lgbt (mixed group)': 'anti-lgbt'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "id": "9babff1c-141f-480d-80f6-412a5bd34b7b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['precinct', 'anti-asian', 'anti-black', 'anti-catholic', 'anti-lesbian',\n",
       "       'anti-jewish', 'anti-gay', 'anti-other ethnicity', 'anti-transgender',\n",
       "       'anti-white', 'period', 'anti-hispanic',\n",
       "       'anti-religious practice generally', 'grand total'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 214,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "common_columns = totals_df.columns.intersection(last_row.columns)\n",
    "common_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "id": "4d5d03f8-f63c-444a-9964-dd128641abf0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['anti-buddhist', 'anti-protestant'], dtype='object')"
      ]
     },
     "execution_count": 215,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_columns1 = last_row.columns.difference(totals_df.columns)\n",
    "diff_columns1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "id": "5e285f54-01bb-4110-bcef-55bb9a9e496c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>anti-asian</th>\n",
       "      <th>anti-black</th>\n",
       "      <th>anti-catholic</th>\n",
       "      <th>anti-lesbian</th>\n",
       "      <th>anti-hindu</th>\n",
       "      <th>anti-muslim</th>\n",
       "      <th>anti-jewish</th>\n",
       "      <th>anti-gay</th>\n",
       "      <th>anti-other ethnicity</th>\n",
       "      <th>...</th>\n",
       "      <th>anti-hispanic</th>\n",
       "      <th>anti-lgbt</th>\n",
       "      <th>anti-multi racial groups</th>\n",
       "      <th>anti-religious practice generally</th>\n",
       "      <th>grand total</th>\n",
       "      <th>anti-sikh</th>\n",
       "      <th>anti-eastern orthodox</th>\n",
       "      <th>anti-jehovahs witness</th>\n",
       "      <th>anti-buddhist</th>\n",
       "      <th>anti-protestant</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Total</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Total</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Total</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Total</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>42.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>63.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>226.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>20.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>114.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>112.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>17.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>20.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>21.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>131.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>107.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>12 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       precinct  anti-asian  anti-black  anti-catholic  anti-lesbian  \\\n",
       "0         Total        11.0        12.0            1.0           1.0   \n",
       "1         Total         8.0         5.0            3.0           2.0   \n",
       "2         Total         4.0        16.0            3.0           NaN   \n",
       "3         Total         3.0         8.0            1.0           1.0   \n",
       "4   Grand Total        42.0        12.0            NaN           1.0   \n",
       "5   Grand Total        63.0        17.0            4.0           0.0   \n",
       "6   Grand Total        20.0         7.0            4.0           2.0   \n",
       "7   Grand Total        13.0         9.0            1.0           1.0   \n",
       "8   Grand Total        17.0        20.0            3.0           1.0   \n",
       "9   Grand Total        20.0        11.0            3.0           NaN   \n",
       "10  Grand Total        21.0        11.0            NaN           5.0   \n",
       "11  Grand Total         5.0         7.0            6.0           1.0   \n",
       "\n",
       "    anti-hindu  anti-muslim  anti-jewish  anti-gay  anti-other ethnicity  ...  \\\n",
       "0          1.0          1.0         45.0       5.0                   3.0  ...   \n",
       "1          1.0          0.0         15.0       4.0                   1.0  ...   \n",
       "2          NaN          NaN         28.0       8.0                   1.0  ...   \n",
       "3          NaN          3.0         28.0       8.0                   1.0  ...   \n",
       "4          NaN          1.0         27.0       4.0                   2.0  ...   \n",
       "5          NaN          5.0         85.0      29.0                   2.0  ...   \n",
       "6          NaN          4.0         38.0      27.0                   3.0  ...   \n",
       "7          NaN          2.0         57.0      13.0                   3.0  ...   \n",
       "8          NaN          3.0         67.0      15.0                   7.0  ...   \n",
       "9          NaN         14.0         64.0      24.0                   9.0  ...   \n",
       "10         3.0          NaN         47.0      18.0                  14.0  ...   \n",
       "11         NaN          NaN         59.0       6.0                   8.0  ...   \n",
       "\n",
       "    anti-hispanic  anti-lgbt  anti-multi racial groups  \\\n",
       "0             NaN        NaN                       NaN   \n",
       "1             0.0        NaN                       NaN   \n",
       "2             NaN        4.0                       2.0   \n",
       "3             1.0        NaN                       NaN   \n",
       "4             1.0        1.0                       NaN   \n",
       "5             2.0        1.0                       NaN   \n",
       "6             1.0        0.0                       NaN   \n",
       "7             2.0        0.0                       NaN   \n",
       "8             2.0        NaN                       NaN   \n",
       "9             6.0        NaN                       NaN   \n",
       "10            1.0        NaN                       NaN   \n",
       "11            5.0        NaN                       NaN   \n",
       "\n",
       "   anti-religious practice generally  grand total  anti-sikh  \\\n",
       "0                                NaN          NaN        NaN   \n",
       "1                                NaN          NaN        NaN   \n",
       "2                                2.0          NaN        NaN   \n",
       "3                                NaN          NaN        NaN   \n",
       "4                                NaN         96.0        NaN   \n",
       "5                                NaN        226.0        NaN   \n",
       "6                                NaN        114.0        NaN   \n",
       "7                                NaN        112.0        NaN   \n",
       "8                                2.0        142.0        NaN   \n",
       "9                                0.0        155.0        2.0   \n",
       "10                               NaN        131.0        NaN   \n",
       "11                               1.0        107.0        NaN   \n",
       "\n",
       "    anti-eastern orthodox  anti-jehovahs witness  anti-buddhist  \\\n",
       "0                     NaN                    NaN            NaN   \n",
       "1                     NaN                    NaN            NaN   \n",
       "2                     NaN                    NaN            NaN   \n",
       "3                     NaN                    NaN            NaN   \n",
       "4                     NaN                    NaN            NaN   \n",
       "5                     NaN                    NaN            NaN   \n",
       "6                     NaN                    NaN            NaN   \n",
       "7                     NaN                    NaN            NaN   \n",
       "8                     NaN                    NaN            NaN   \n",
       "9                     NaN                    NaN            NaN   \n",
       "10                    2.0                    1.0            NaN   \n",
       "11                    NaN                    NaN            1.0   \n",
       "\n",
       "    anti-protestant  \n",
       "0               NaN  \n",
       "1               NaN  \n",
       "2               NaN  \n",
       "3               NaN  \n",
       "4               NaN  \n",
       "5               NaN  \n",
       "6               NaN  \n",
       "7               NaN  \n",
       "8               NaN  \n",
       "9               NaN  \n",
       "10              NaN  \n",
       "11              1.0  \n",
       "\n",
       "[12 rows x 28 columns]"
      ]
     },
     "execution_count": 216,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals_df = pd.concat([totals_df, last_row], ignore_index=True)\n",
    "totals_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95d41ef5-5a14-42e3-bffd-1288bcee7023",
   "metadata": {},
   "source": [
    "#### Q1, 2023"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "id": "2e6f75d8-edf4-4f4a-89b0-95df6bf7d805",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "precinct                            object\n",
       "anti-asian                           int64\n",
       "anti-black                           int64\n",
       "anti-catholic                        int64\n",
       "anti-female homosexual (lesbian)     int64\n",
       "anti-gender non conforming           int64\n",
       "anti-hispanic                        int64\n",
       "anti-jehovahs witness                int64\n",
       "anti-jewish                          int64\n",
       "anti-male homosexual (gay)           int64\n",
       "anti-muslim                          int64\n",
       "anti-other ethnicity                 int64\n",
       "anti-transgender                     int64\n",
       "anti-white                           int64\n",
       "grand total                          int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 217,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motivation-q1-2023.xlsx', skiprows=4)\n",
    "clean(df)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "id": "51b505e8-7684-4573-8183-56babc6d4477",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_54677/493172275.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  last_row['period'] = '2023, Q1'\n"
     ]
    }
   ],
   "source": [
    "last_row = df.tail(1)\n",
    "last_row['period'] = '2023, Q1'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "id": "6978aae6-fbc3-4b83-8c45-14561b291f80",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_row = last_row.rename(columns={'anti-other religion': 'anti-religious practice generally', 'anti-gender non conforming': 'anti-gender non-conforming', 'anti-female homosexual (lesbian)': 'anti-lesbian', 'anti-male homosexual (gay)': 'anti-gay', 'anti-lgbt (mixed group)': 'anti-lgbt'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 220,
   "id": "bec7f0e7-180d-4236-a548-586fcb812c5d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['precinct', 'anti-asian', 'anti-black', 'anti-catholic', 'anti-lesbian',\n",
       "       'anti-muslim', 'anti-jewish', 'anti-gay', 'anti-other ethnicity',\n",
       "       'anti-transgender', 'anti-white', 'period',\n",
       "       'anti-gender non-conforming', 'anti-hispanic', 'grand total',\n",
       "       'anti-jehovahs witness'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 220,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "common_columns = totals_df.columns.intersection(last_row.columns)\n",
    "common_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "id": "63b8df1f-2366-4f6c-967f-28d7a6eff5ed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([], dtype='object')"
      ]
     },
     "execution_count": 221,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_columns1 = last_row.columns.difference(totals_df.columns)\n",
    "diff_columns1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "id": "ce62c684-b37c-4036-b6a0-191cc433bc2b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>anti-asian</th>\n",
       "      <th>anti-black</th>\n",
       "      <th>anti-catholic</th>\n",
       "      <th>anti-lesbian</th>\n",
       "      <th>anti-hindu</th>\n",
       "      <th>anti-muslim</th>\n",
       "      <th>anti-jewish</th>\n",
       "      <th>anti-gay</th>\n",
       "      <th>anti-other ethnicity</th>\n",
       "      <th>...</th>\n",
       "      <th>anti-hispanic</th>\n",
       "      <th>anti-lgbt</th>\n",
       "      <th>anti-multi racial groups</th>\n",
       "      <th>anti-religious practice generally</th>\n",
       "      <th>grand total</th>\n",
       "      <th>anti-sikh</th>\n",
       "      <th>anti-eastern orthodox</th>\n",
       "      <th>anti-jehovahs witness</th>\n",
       "      <th>anti-buddhist</th>\n",
       "      <th>anti-protestant</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Total</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Total</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Total</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Total</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>42.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>63.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>226.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>20.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>114.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>112.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>17.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>20.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>21.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>131.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>107.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>6.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>111.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       precinct  anti-asian  anti-black  anti-catholic  anti-lesbian  \\\n",
       "0         Total        11.0        12.0            1.0           1.0   \n",
       "1         Total         8.0         5.0            3.0           2.0   \n",
       "2         Total         4.0        16.0            3.0           NaN   \n",
       "3         Total         3.0         8.0            1.0           1.0   \n",
       "4   Grand Total        42.0        12.0            NaN           1.0   \n",
       "5   Grand Total        63.0        17.0            4.0           0.0   \n",
       "6   Grand Total        20.0         7.0            4.0           2.0   \n",
       "7   Grand Total        13.0         9.0            1.0           1.0   \n",
       "8   Grand Total        17.0        20.0            3.0           1.0   \n",
       "9   Grand Total        20.0        11.0            3.0           NaN   \n",
       "10  Grand Total        21.0        11.0            NaN           5.0   \n",
       "11  Grand Total         5.0         7.0            6.0           1.0   \n",
       "12  Grand Total         6.0        10.0            4.0           2.0   \n",
       "\n",
       "    anti-hindu  anti-muslim  anti-jewish  anti-gay  anti-other ethnicity  ...  \\\n",
       "0          1.0          1.0         45.0       5.0                   3.0  ...   \n",
       "1          1.0          0.0         15.0       4.0                   1.0  ...   \n",
       "2          NaN          NaN         28.0       8.0                   1.0  ...   \n",
       "3          NaN          3.0         28.0       8.0                   1.0  ...   \n",
       "4          NaN          1.0         27.0       4.0                   2.0  ...   \n",
       "5          NaN          5.0         85.0      29.0                   2.0  ...   \n",
       "6          NaN          4.0         38.0      27.0                   3.0  ...   \n",
       "7          NaN          2.0         57.0      13.0                   3.0  ...   \n",
       "8          NaN          3.0         67.0      15.0                   7.0  ...   \n",
       "9          NaN         14.0         64.0      24.0                   9.0  ...   \n",
       "10         3.0          NaN         47.0      18.0                  14.0  ...   \n",
       "11         NaN          NaN         59.0       6.0                   8.0  ...   \n",
       "12         NaN          1.0         60.0      11.0                   4.0  ...   \n",
       "\n",
       "    anti-hispanic  anti-lgbt  anti-multi racial groups  \\\n",
       "0             NaN        NaN                       NaN   \n",
       "1             0.0        NaN                       NaN   \n",
       "2             NaN        4.0                       2.0   \n",
       "3             1.0        NaN                       NaN   \n",
       "4             1.0        1.0                       NaN   \n",
       "5             2.0        1.0                       NaN   \n",
       "6             1.0        0.0                       NaN   \n",
       "7             2.0        0.0                       NaN   \n",
       "8             2.0        NaN                       NaN   \n",
       "9             6.0        NaN                       NaN   \n",
       "10            1.0        NaN                       NaN   \n",
       "11            5.0        NaN                       NaN   \n",
       "12            1.0        NaN                       NaN   \n",
       "\n",
       "   anti-religious practice generally  grand total  anti-sikh  \\\n",
       "0                                NaN          NaN        NaN   \n",
       "1                                NaN          NaN        NaN   \n",
       "2                                2.0          NaN        NaN   \n",
       "3                                NaN          NaN        NaN   \n",
       "4                                NaN         96.0        NaN   \n",
       "5                                NaN        226.0        NaN   \n",
       "6                                NaN        114.0        NaN   \n",
       "7                                NaN        112.0        NaN   \n",
       "8                                2.0        142.0        NaN   \n",
       "9                                0.0        155.0        2.0   \n",
       "10                               NaN        131.0        NaN   \n",
       "11                               1.0        107.0        NaN   \n",
       "12                               NaN        111.0        NaN   \n",
       "\n",
       "    anti-eastern orthodox  anti-jehovahs witness  anti-buddhist  \\\n",
       "0                     NaN                    NaN            NaN   \n",
       "1                     NaN                    NaN            NaN   \n",
       "2                     NaN                    NaN            NaN   \n",
       "3                     NaN                    NaN            NaN   \n",
       "4                     NaN                    NaN            NaN   \n",
       "5                     NaN                    NaN            NaN   \n",
       "6                     NaN                    NaN            NaN   \n",
       "7                     NaN                    NaN            NaN   \n",
       "8                     NaN                    NaN            NaN   \n",
       "9                     NaN                    NaN            NaN   \n",
       "10                    2.0                    1.0            NaN   \n",
       "11                    NaN                    NaN            1.0   \n",
       "12                    NaN                    1.0            NaN   \n",
       "\n",
       "    anti-protestant  \n",
       "0               NaN  \n",
       "1               NaN  \n",
       "2               NaN  \n",
       "3               NaN  \n",
       "4               NaN  \n",
       "5               NaN  \n",
       "6               NaN  \n",
       "7               NaN  \n",
       "8               NaN  \n",
       "9               NaN  \n",
       "10              NaN  \n",
       "11              1.0  \n",
       "12              NaN  \n",
       "\n",
       "[13 rows x 28 columns]"
      ]
     },
     "execution_count": 222,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals_df = pd.concat([totals_df, last_row], ignore_index=True)\n",
    "totals_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a46bf85f-9077-488e-ab1e-cd9212a5464a",
   "metadata": {},
   "source": [
    "#### Q2, 2023"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 223,
   "id": "9897d9eb-27b4-4489-9423-00415725b464",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "precinct                            object\n",
       "anti-asian                           int64\n",
       "anti-black                           int64\n",
       "anti-catholic                        int64\n",
       "anti-female homosexual (lesbian)     int64\n",
       "anti-gender non conforming           int64\n",
       "anti-hispanic                        int64\n",
       "anti-jewish                          int64\n",
       "anti-male homosexual (gay)           int64\n",
       "anti-muslim                          int64\n",
       "anti-other ethnicity                 int64\n",
       "anti-transgender                     int64\n",
       "anti-white                           int64\n",
       "grand total                          int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 223,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motivation-q2-2023.xlsx', skiprows=4)\n",
    "clean(df)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "id": "44098919-d9bb-4475-99de-a79fe0edd663",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_54677/4265884935.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  last_row['period'] = '2023, Q2'\n"
     ]
    }
   ],
   "source": [
    "last_row = df.tail(1)\n",
    "last_row['period'] = '2023, Q2'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "id": "72ba9df9-4dec-4fd7-b353-0ad0d964120b",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_row = last_row.rename(columns={'anti-other religion': 'anti-religious practice generally', 'anti-gender non conforming': 'anti-gender non-conforming', 'anti-female homosexual (lesbian)': 'anti-lesbian', 'anti-male homosexual (gay)': 'anti-gay', 'anti-lgbt (mixed group)': 'anti-lgbt'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "id": "d36d4b96-9189-40ba-934b-4d02ac6c43b8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([], dtype='object')"
      ]
     },
     "execution_count": 226,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_columns1 = last_row.columns.difference(totals_df.columns)\n",
    "diff_columns1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "id": "b1c4c8c1-cbe6-4870-b21a-6cdacca35919",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>anti-asian</th>\n",
       "      <th>anti-black</th>\n",
       "      <th>anti-catholic</th>\n",
       "      <th>anti-lesbian</th>\n",
       "      <th>anti-hindu</th>\n",
       "      <th>anti-muslim</th>\n",
       "      <th>anti-jewish</th>\n",
       "      <th>anti-gay</th>\n",
       "      <th>anti-other ethnicity</th>\n",
       "      <th>...</th>\n",
       "      <th>anti-hispanic</th>\n",
       "      <th>anti-lgbt</th>\n",
       "      <th>anti-multi racial groups</th>\n",
       "      <th>anti-religious practice generally</th>\n",
       "      <th>grand total</th>\n",
       "      <th>anti-sikh</th>\n",
       "      <th>anti-eastern orthodox</th>\n",
       "      <th>anti-jehovahs witness</th>\n",
       "      <th>anti-buddhist</th>\n",
       "      <th>anti-protestant</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Total</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Total</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Total</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Total</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>42.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>63.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>226.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>20.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>114.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>112.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>17.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>20.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>21.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>131.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>107.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>6.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>111.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>13.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>120.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       precinct  anti-asian  anti-black  anti-catholic  anti-lesbian  \\\n",
       "0         Total        11.0        12.0            1.0           1.0   \n",
       "1         Total         8.0         5.0            3.0           2.0   \n",
       "2         Total         4.0        16.0            3.0           NaN   \n",
       "3         Total         3.0         8.0            1.0           1.0   \n",
       "4   Grand Total        42.0        12.0            NaN           1.0   \n",
       "5   Grand Total        63.0        17.0            4.0           0.0   \n",
       "6   Grand Total        20.0         7.0            4.0           2.0   \n",
       "7   Grand Total        13.0         9.0            1.0           1.0   \n",
       "8   Grand Total        17.0        20.0            3.0           1.0   \n",
       "9   Grand Total        20.0        11.0            3.0           NaN   \n",
       "10  Grand Total        21.0        11.0            NaN           5.0   \n",
       "11  Grand Total         5.0         7.0            6.0           1.0   \n",
       "12  Grand Total         6.0        10.0            4.0           2.0   \n",
       "13  Grand Total        13.0        10.0            1.0           1.0   \n",
       "\n",
       "    anti-hindu  anti-muslim  anti-jewish  anti-gay  anti-other ethnicity  ...  \\\n",
       "0          1.0          1.0         45.0       5.0                   3.0  ...   \n",
       "1          1.0          0.0         15.0       4.0                   1.0  ...   \n",
       "2          NaN          NaN         28.0       8.0                   1.0  ...   \n",
       "3          NaN          3.0         28.0       8.0                   1.0  ...   \n",
       "4          NaN          1.0         27.0       4.0                   2.0  ...   \n",
       "5          NaN          5.0         85.0      29.0                   2.0  ...   \n",
       "6          NaN          4.0         38.0      27.0                   3.0  ...   \n",
       "7          NaN          2.0         57.0      13.0                   3.0  ...   \n",
       "8          NaN          3.0         67.0      15.0                   7.0  ...   \n",
       "9          NaN         14.0         64.0      24.0                   9.0  ...   \n",
       "10         3.0          NaN         47.0      18.0                  14.0  ...   \n",
       "11         NaN          NaN         59.0       6.0                   8.0  ...   \n",
       "12         NaN          1.0         60.0      11.0                   4.0  ...   \n",
       "13         NaN          5.0         48.0      19.0                   8.0  ...   \n",
       "\n",
       "    anti-hispanic  anti-lgbt  anti-multi racial groups  \\\n",
       "0             NaN        NaN                       NaN   \n",
       "1             0.0        NaN                       NaN   \n",
       "2             NaN        4.0                       2.0   \n",
       "3             1.0        NaN                       NaN   \n",
       "4             1.0        1.0                       NaN   \n",
       "5             2.0        1.0                       NaN   \n",
       "6             1.0        0.0                       NaN   \n",
       "7             2.0        0.0                       NaN   \n",
       "8             2.0        NaN                       NaN   \n",
       "9             6.0        NaN                       NaN   \n",
       "10            1.0        NaN                       NaN   \n",
       "11            5.0        NaN                       NaN   \n",
       "12            1.0        NaN                       NaN   \n",
       "13            4.0        NaN                       NaN   \n",
       "\n",
       "   anti-religious practice generally  grand total  anti-sikh  \\\n",
       "0                                NaN          NaN        NaN   \n",
       "1                                NaN          NaN        NaN   \n",
       "2                                2.0          NaN        NaN   \n",
       "3                                NaN          NaN        NaN   \n",
       "4                                NaN         96.0        NaN   \n",
       "5                                NaN        226.0        NaN   \n",
       "6                                NaN        114.0        NaN   \n",
       "7                                NaN        112.0        NaN   \n",
       "8                                2.0        142.0        NaN   \n",
       "9                                0.0        155.0        2.0   \n",
       "10                               NaN        131.0        NaN   \n",
       "11                               1.0        107.0        NaN   \n",
       "12                               NaN        111.0        NaN   \n",
       "13                               NaN        120.0        NaN   \n",
       "\n",
       "    anti-eastern orthodox  anti-jehovahs witness  anti-buddhist  \\\n",
       "0                     NaN                    NaN            NaN   \n",
       "1                     NaN                    NaN            NaN   \n",
       "2                     NaN                    NaN            NaN   \n",
       "3                     NaN                    NaN            NaN   \n",
       "4                     NaN                    NaN            NaN   \n",
       "5                     NaN                    NaN            NaN   \n",
       "6                     NaN                    NaN            NaN   \n",
       "7                     NaN                    NaN            NaN   \n",
       "8                     NaN                    NaN            NaN   \n",
       "9                     NaN                    NaN            NaN   \n",
       "10                    2.0                    1.0            NaN   \n",
       "11                    NaN                    NaN            1.0   \n",
       "12                    NaN                    1.0            NaN   \n",
       "13                    NaN                    NaN            NaN   \n",
       "\n",
       "    anti-protestant  \n",
       "0               NaN  \n",
       "1               NaN  \n",
       "2               NaN  \n",
       "3               NaN  \n",
       "4               NaN  \n",
       "5               NaN  \n",
       "6               NaN  \n",
       "7               NaN  \n",
       "8               NaN  \n",
       "9               NaN  \n",
       "10              NaN  \n",
       "11              1.0  \n",
       "12              NaN  \n",
       "13              NaN  \n",
       "\n",
       "[14 rows x 28 columns]"
      ]
     },
     "execution_count": 227,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals_df = pd.concat([totals_df, last_row], ignore_index=True)\n",
    "totals_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2564c9a9-e591-4581-a871-a18a5759fa43",
   "metadata": {},
   "source": [
    "#### Q3, 2023"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "id": "b5d5af93-e545-451e-aa56-26c1d641c119",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "precinct                            object\n",
       "anti-asian                           int64\n",
       "anti-black                           int64\n",
       "anti-catholic                        int64\n",
       "anti-female homosexual (lesbian)     int64\n",
       "anti-hispanic                        int64\n",
       "anti-jewish                          int64\n",
       "anti-male homosexual (gay)           int64\n",
       "anti-muslim                          int64\n",
       "anti-other ethnicity                 int64\n",
       "anti-protestant                      int64\n",
       "anti-transgender                     int64\n",
       "anti-white                           int64\n",
       "grand total                          int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 228,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motivation-q3-2023.xlsx', skiprows=4)\n",
    "clean(df)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "id": "a7c20c22-e0ae-47b3-b598-04c0ba7c5330",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_54677/4244797752.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  last_row['period'] = '2023, Q3'\n"
     ]
    }
   ],
   "source": [
    "last_row = df.tail(1)\n",
    "last_row['period'] = '2023, Q3'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "id": "0f13ab98-1fe0-480d-9f2b-1e7912ed3935",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_row = last_row.rename(columns={'anti-other religion': 'anti-religious practice generally', 'anti-gender non conforming': 'anti-gender non-conforming', 'anti-female homosexual (lesbian)': 'anti-lesbian', 'anti-male homosexual (gay)': 'anti-gay', 'anti-lgbt (mixed group)': 'anti-lgbt'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "id": "de0b8cb9-c94f-4a21-b663-d538b1501e73",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([], dtype='object')"
      ]
     },
     "execution_count": 231,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_columns1 = last_row.columns.difference(totals_df.columns)\n",
    "diff_columns1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "id": "22e33a4a-e544-477f-b097-de1e41912a5d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>anti-asian</th>\n",
       "      <th>anti-black</th>\n",
       "      <th>anti-catholic</th>\n",
       "      <th>anti-lesbian</th>\n",
       "      <th>anti-hindu</th>\n",
       "      <th>anti-muslim</th>\n",
       "      <th>anti-jewish</th>\n",
       "      <th>anti-gay</th>\n",
       "      <th>anti-other ethnicity</th>\n",
       "      <th>...</th>\n",
       "      <th>anti-hispanic</th>\n",
       "      <th>anti-lgbt</th>\n",
       "      <th>anti-multi racial groups</th>\n",
       "      <th>anti-religious practice generally</th>\n",
       "      <th>grand total</th>\n",
       "      <th>anti-sikh</th>\n",
       "      <th>anti-eastern orthodox</th>\n",
       "      <th>anti-jehovahs witness</th>\n",
       "      <th>anti-buddhist</th>\n",
       "      <th>anti-protestant</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Total</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Total</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Total</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Total</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>42.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>63.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>226.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>20.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>114.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>112.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>17.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>20.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>21.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>131.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>107.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>6.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>111.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>13.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>120.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>12.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>106.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       precinct  anti-asian  anti-black  anti-catholic  anti-lesbian  \\\n",
       "0         Total        11.0        12.0            1.0           1.0   \n",
       "1         Total         8.0         5.0            3.0           2.0   \n",
       "2         Total         4.0        16.0            3.0           NaN   \n",
       "3         Total         3.0         8.0            1.0           1.0   \n",
       "4   Grand Total        42.0        12.0            NaN           1.0   \n",
       "5   Grand Total        63.0        17.0            4.0           0.0   \n",
       "6   Grand Total        20.0         7.0            4.0           2.0   \n",
       "7   Grand Total        13.0         9.0            1.0           1.0   \n",
       "8   Grand Total        17.0        20.0            3.0           1.0   \n",
       "9   Grand Total        20.0        11.0            3.0           NaN   \n",
       "10  Grand Total        21.0        11.0            NaN           5.0   \n",
       "11  Grand Total         5.0         7.0            6.0           1.0   \n",
       "12  Grand Total         6.0        10.0            4.0           2.0   \n",
       "13  Grand Total        13.0        10.0            1.0           1.0   \n",
       "14  Grand Total        12.0        13.0            1.0           2.0   \n",
       "\n",
       "    anti-hindu  anti-muslim  anti-jewish  anti-gay  anti-other ethnicity  ...  \\\n",
       "0          1.0          1.0         45.0       5.0                   3.0  ...   \n",
       "1          1.0          0.0         15.0       4.0                   1.0  ...   \n",
       "2          NaN          NaN         28.0       8.0                   1.0  ...   \n",
       "3          NaN          3.0         28.0       8.0                   1.0  ...   \n",
       "4          NaN          1.0         27.0       4.0                   2.0  ...   \n",
       "5          NaN          5.0         85.0      29.0                   2.0  ...   \n",
       "6          NaN          4.0         38.0      27.0                   3.0  ...   \n",
       "7          NaN          2.0         57.0      13.0                   3.0  ...   \n",
       "8          NaN          3.0         67.0      15.0                   7.0  ...   \n",
       "9          NaN         14.0         64.0      24.0                   9.0  ...   \n",
       "10         3.0          NaN         47.0      18.0                  14.0  ...   \n",
       "11         NaN          NaN         59.0       6.0                   8.0  ...   \n",
       "12         NaN          1.0         60.0      11.0                   4.0  ...   \n",
       "13         NaN          5.0         48.0      19.0                   8.0  ...   \n",
       "14         NaN          2.0         38.0      22.0                   3.0  ...   \n",
       "\n",
       "    anti-hispanic  anti-lgbt  anti-multi racial groups  \\\n",
       "0             NaN        NaN                       NaN   \n",
       "1             0.0        NaN                       NaN   \n",
       "2             NaN        4.0                       2.0   \n",
       "3             1.0        NaN                       NaN   \n",
       "4             1.0        1.0                       NaN   \n",
       "5             2.0        1.0                       NaN   \n",
       "6             1.0        0.0                       NaN   \n",
       "7             2.0        0.0                       NaN   \n",
       "8             2.0        NaN                       NaN   \n",
       "9             6.0        NaN                       NaN   \n",
       "10            1.0        NaN                       NaN   \n",
       "11            5.0        NaN                       NaN   \n",
       "12            1.0        NaN                       NaN   \n",
       "13            4.0        NaN                       NaN   \n",
       "14            4.0        NaN                       NaN   \n",
       "\n",
       "   anti-religious practice generally  grand total  anti-sikh  \\\n",
       "0                                NaN          NaN        NaN   \n",
       "1                                NaN          NaN        NaN   \n",
       "2                                2.0          NaN        NaN   \n",
       "3                                NaN          NaN        NaN   \n",
       "4                                NaN         96.0        NaN   \n",
       "5                                NaN        226.0        NaN   \n",
       "6                                NaN        114.0        NaN   \n",
       "7                                NaN        112.0        NaN   \n",
       "8                                2.0        142.0        NaN   \n",
       "9                                0.0        155.0        2.0   \n",
       "10                               NaN        131.0        NaN   \n",
       "11                               1.0        107.0        NaN   \n",
       "12                               NaN        111.0        NaN   \n",
       "13                               NaN        120.0        NaN   \n",
       "14                               NaN        106.0        NaN   \n",
       "\n",
       "    anti-eastern orthodox  anti-jehovahs witness  anti-buddhist  \\\n",
       "0                     NaN                    NaN            NaN   \n",
       "1                     NaN                    NaN            NaN   \n",
       "2                     NaN                    NaN            NaN   \n",
       "3                     NaN                    NaN            NaN   \n",
       "4                     NaN                    NaN            NaN   \n",
       "5                     NaN                    NaN            NaN   \n",
       "6                     NaN                    NaN            NaN   \n",
       "7                     NaN                    NaN            NaN   \n",
       "8                     NaN                    NaN            NaN   \n",
       "9                     NaN                    NaN            NaN   \n",
       "10                    2.0                    1.0            NaN   \n",
       "11                    NaN                    NaN            1.0   \n",
       "12                    NaN                    1.0            NaN   \n",
       "13                    NaN                    NaN            NaN   \n",
       "14                    NaN                    NaN            NaN   \n",
       "\n",
       "    anti-protestant  \n",
       "0               NaN  \n",
       "1               NaN  \n",
       "2               NaN  \n",
       "3               NaN  \n",
       "4               NaN  \n",
       "5               NaN  \n",
       "6               NaN  \n",
       "7               NaN  \n",
       "8               NaN  \n",
       "9               NaN  \n",
       "10              NaN  \n",
       "11              1.0  \n",
       "12              NaN  \n",
       "13              NaN  \n",
       "14              1.0  \n",
       "\n",
       "[15 rows x 28 columns]"
      ]
     },
     "execution_count": 232,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals_df = pd.concat([totals_df, last_row], ignore_index=True)\n",
    "totals_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a28f8aa-c45c-499a-b12c-58afc896536b",
   "metadata": {},
   "source": [
    "#### Q4, 2023"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "id": "bdca59fc-89e5-4c43-818b-3f897b0e6e97",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "precinct                            object\n",
       "anti-asian                           int64\n",
       "anti-black                           int64\n",
       "anti-buddhist                        int64\n",
       "anti-catholic                        int64\n",
       "anti-female homosexual (lesbian)     int64\n",
       "anti-gender non conforming           int64\n",
       "anti-hispanic                        int64\n",
       "anti-jewish                          int64\n",
       "anti-male homosexual (gay)           int64\n",
       "anti-muslim                          int64\n",
       "anti-other ethnicity                 int64\n",
       "anti-sikh                            int64\n",
       "anti-transgender                     int64\n",
       "anti-white                           int64\n",
       "grand total                          int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 233,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motivation-q4-2023.xlsx', skiprows=4)\n",
    "clean(df)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "id": "c5e17734-f704-4bc1-bcff-1fe38bc2d463",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_54677/2130269765.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  last_row['period'] = '2023, Q4'\n"
     ]
    }
   ],
   "source": [
    "last_row = df.tail(1)\n",
    "last_row['period'] = '2023, Q4'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "id": "428c4bf6-a983-44f4-aad6-406a368b695a",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_row = last_row.rename(columns={'anti-other religion': 'anti-religious practice generally', 'anti-gender non conforming': 'anti-gender non-conforming', 'anti-female homosexual (lesbian)': 'anti-lesbian', 'anti-male homosexual (gay)': 'anti-gay', 'anti-lgbt (mixed group)': 'anti-lgbt'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "id": "db5b92c9-d476-45e5-8eb1-1b6e4bfa041f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([], dtype='object')"
      ]
     },
     "execution_count": 236,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_columns1 = last_row.columns.difference(totals_df.columns)\n",
    "diff_columns1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "id": "ad558706-d513-4788-9885-0219f736c3b2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     2020, Q1\n",
       "1     2020, Q2\n",
       "2     2020, Q3\n",
       "3     2020, Q4\n",
       "4     2021, Q1\n",
       "5     2021, Q2\n",
       "6     2021, Q3\n",
       "7     2021, Q4\n",
       "8     2022, Q1\n",
       "9     2022, Q2\n",
       "10    2022, Q3\n",
       "11    2022, Q4\n",
       "12    2023, Q1\n",
       "13    2023, Q2\n",
       "14    2023, Q3\n",
       "15    2023, Q4\n",
       "Name: period, dtype: object"
      ]
     },
     "execution_count": 237,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals_df = pd.concat([totals_df, last_row], ignore_index=True)\n",
    "totals_df['period']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01c0d6e5-04a6-4090-a868-9672e6003685",
   "metadata": {},
   "source": [
    "#### Q1, 2024\n",
    "\n",
    "Finally, we're here!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "id": "10eae05f-c14c-4a5e-a6e7-600114e16c2b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "precinct                            object\n",
       "anti-asian                           int64\n",
       "anti-black                           int64\n",
       "anti-buddhist                        int64\n",
       "anti-catholic                        int64\n",
       "anti-female homosexual (lesbian)     int64\n",
       "anti-gender non conforming           int64\n",
       "anti-hispanic                        int64\n",
       "anti-jewish                          int64\n",
       "anti-male homosexual (gay)           int64\n",
       "anti-muslim                          int64\n",
       "anti-other ethnicity                 int64\n",
       "anti-protestant                      int64\n",
       "anti-sikh                            int64\n",
       "anti-transgender                     int64\n",
       "anti-white                           int64\n",
       "grand total                          int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 238,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('downloaded-datasets/hate-crime-complaints-by-motivation-q1-2024.xlsx', skiprows=4)\n",
    "clean(df)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "id": "b8ca50a9-e258-433e-aed1-e4c218859c55",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_54677/704503961.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  last_row['period'] = '2024, Q1'\n"
     ]
    }
   ],
   "source": [
    "last_row = df.tail(1)\n",
    "last_row['period'] = '2024, Q1'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "id": "379ed2c4-112c-43b0-beb1-f976050de677",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_row = last_row.rename(columns={'anti-other religion': 'anti-religious practice generally', 'anti-gender non conforming': 'anti-gender non-conforming', 'anti-female homosexual (lesbian)': 'anti-lesbian', 'anti-male homosexual (gay)': 'anti-gay', 'anti-lgbt (mixed group)': 'anti-lgbt'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "id": "611353e1-b22d-4007-ac4b-5a7d44159360",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([], dtype='object')"
      ]
     },
     "execution_count": 241,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_columns1 = last_row.columns.difference(totals_df.columns)\n",
    "diff_columns1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "id": "9853d735-a8e3-45e1-b5fd-592323b90fba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     2020, Q1\n",
       "1     2020, Q2\n",
       "2     2020, Q3\n",
       "3     2020, Q4\n",
       "4     2021, Q1\n",
       "5     2021, Q2\n",
       "6     2021, Q3\n",
       "7     2021, Q4\n",
       "8     2022, Q1\n",
       "9     2022, Q2\n",
       "10    2022, Q3\n",
       "11    2022, Q4\n",
       "12    2023, Q1\n",
       "13    2023, Q2\n",
       "14    2023, Q3\n",
       "15    2023, Q4\n",
       "16    2024, Q1\n",
       "Name: period, dtype: object"
      ]
     },
     "execution_count": 242,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals_df = pd.concat([totals_df, last_row], ignore_index=True)\n",
    "totals_df['period']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "id": "ebd8a0c5-0f43-4641-bd71-d805d3c09436",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Final checkpoint!\n",
    "\n",
    "totals_df.to_csv('totals.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "580094f8-9ecd-4518-9ab0-5acd26283f39",
   "metadata": {},
   "source": [
    "## Now, the magic happens! ✨\n",
    "\n",
    "***Sort of...*** First, I'll check if my columns are in order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "id": "afca996e-2455-4f0a-a7e3-d14b1cffbaff",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Removing the 'precinct' column, which was totally unnecessary...\n",
    "\n",
    "totals_df.drop(columns=['precinct'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "id": "592401b1-4387-4ae7-9ed0-5fc0565f90db",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      84.0\n",
       "1      46.0\n",
       "2      75.0\n",
       "3      60.0\n",
       "4      96.0\n",
       "5     226.0\n",
       "6     114.0\n",
       "7     112.0\n",
       "8     142.0\n",
       "9     155.0\n",
       "10    131.0\n",
       "11    107.0\n",
       "12    111.0\n",
       "13    120.0\n",
       "14    106.0\n",
       "15    257.0\n",
       "16    136.0\n",
       "Name: total, dtype: float64"
      ]
     },
     "execution_count": 245,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Note: This shows totals per quarter.\n",
    "\n",
    "totals_df['total'].combine_first(totals_df['grand total'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "id": "ccdaf4d3-f3c5-4cdc-bead-5aff257eb9f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "totals_df['total'] = totals_df['total'].combine_first(totals_df['grand total'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "id": "51a2526e-8406-4b00-b643-36e642ae7a71",
   "metadata": {},
   "outputs": [],
   "source": [
    "totals_df.drop(columns=['grand total'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db7ec3fe-a175-4aff-907b-8e0f44ca8a01",
   "metadata": {},
   "source": [
    "## Magic 2.0! ✨\n",
    "\n",
    "Not yet, but we're getting there. The `total` column shows the total number of hate crime complaints reported per quarter. I also want to check the total number of hate crime complaints per \"bias motivation\".\n",
    "\n",
    "Then, I can do all the data analyses and viz that I want. Probably."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "id": "ab93c97b-35f2-43a0-9300-74bd66f014e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sum per column\n",
    "\n",
    "totals_df.loc['Total',:] = totals_df.sum(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "id": "90141c90-5f91-4cce-8d65-81094962d437",
   "metadata": {},
   "outputs": [],
   "source": [
    "# CHECKPOINT\n",
    "\n",
    "totals_df.to_csv('totals.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "208369bd-93ea-4032-b802-c56f00e3862b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}