02_standardize.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b585c8df-50cb-4674-a9e1-82672579e189",
   "metadata": {},
   "source": [
    "# Standardize\n",
    "\n",
    "Pull together our different data sources and combine them into a single dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29f02b94-1162-4cff-a051-47ddd5a88dc0",
   "metadata": {},
   "source": [
    "Import Python tools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "91e40a0f-f605-4491-b4f9-cef6e62df4cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54cc6050-2ee8-4831-abf1-453911f4130d",
   "metadata": {},
   "source": [
    "## Department of Streets and Sanitation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cdc3176-025b-48ef-a6a6-e4db9106befd",
   "metadata": {},
   "source": [
    "Read in the raw data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "6f2e2ec6-a570-4022-a137-8721604eea8b",
   "metadata": {},
   "outputs": [],
   "source": [
    "dss_df = pd.read_excel(\n",
    "    \"input/FOIA_Trees_Planted_2011_thru_2021.xls\",\n",
    "    skipfooter=2,  # The last row is junk and needs to be cut\n",
    "    parse_dates=[\"Plant Date\"],\n",
    "    dtype={\n",
    "        \"Ward\": str\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57dfa087-5019-4f07-b34a-11b5f7a22797",
   "metadata": {},
   "source": [
    "Clean up the columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "bd7a2d38-5096-4bcb-b611-ab81b56ee0f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "dss_columns = {\n",
    "    \"WorkOrderId\": \"id\",\n",
    "    \"Plant Date\": \"date\",\n",
    "    \"Address\": \"address\",\n",
    "    \"Description\": \"description\",\n",
    "    \"Ward\": \"ward\",\n",
    "    \"APS:\": \"trees\",\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4cfc5008-ae4b-4e24-8f84-f16d84063a83",
   "metadata": {},
   "outputs": [],
   "source": [
    "trimmed_dss_df = (\n",
    "    dss_df[dss_columns.keys()]\n",
    "    .rename(columns=dss_columns)\n",
    "    .sort_values(\"date\")\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60c307f4-bacc-4c2a-87ec-b1bd9bbcade9",
   "metadata": {},
   "source": [
    "Tag the source"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "71264e92-6c2f-4025-9e7e-09d305279619",
   "metadata": {},
   "outputs": [],
   "source": [
    "trimmed_dss_df['source'] = 'dss'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f19c789a-3ad3-4f03-8206-44e92a9357fc",
   "metadata": {},
   "source": [
    "Filter it down to tree plants, excluding other types of actions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "64710a69-70e9-451b-9f90-243a38ccdfdb",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_dss_df = (\n",
    "    trimmed_dss_df[trimmed_dss_df.description == 'SED_Tree Planting']\n",
    "    .drop('description', axis=1)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5623653-090b-4f10-9cbd-aedc6b807b95",
   "metadata": {},
   "source": [
    "Remove duplicate trees using the identifier field"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "efcf04bd-0e84-4ecb-955d-b43284488c7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "deduped_dss_df = filtered_dss_df.drop_duplicates(\"id\", keep=\"first\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7a39d36-e58a-4461-aa61-2aba87959425",
   "metadata": {},
   "source": [
    "## Department of Transportation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56d053e3-891e-42bb-8439-d0a0cd7596ba",
   "metadata": {},
   "source": [
    "Read in the two raw data files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "740f47c3-5468-49a7-a78f-672dbd69b19a",
   "metadata": {},
   "outputs": [],
   "source": [
    "cdot_df = pd.read_csv(\n",
    "    \"input/parsed-01.01.2008_12.31.2020311CSR.1.csv\",\n",
    "    parse_dates=[\"created_date\", \"activity_date\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c6a5b872-67c3-4aed-b1e4-bb808536ca1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "cdot2_df = pd.read_csv(\n",
    "    \"input/tabula-11.30.2018-12.31.2020311CSR.2.csv\",\n",
    "    dtype={\n",
    "        \"Ward\": str\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5e856b8-0619-4635-a3ce-23b806d458aa",
   "metadata": {},
   "source": [
    "Create a date field based on a fallback scheme. If the activity date is there, use that. If not, use the created date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "95f03aff-93c3-4a7c-af17-d62d3d6a38a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "cdot_df['date'] = cdot_df.activity_date.mask(pd.isnull, cdot_df['created_date'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e2742f6-8f88-48d6-9a9c-aa314351b8a3",
   "metadata": {},
   "source": [
    "Clean up the columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e9588376-a9d2-454b-b52f-832e3a1dce66",
   "metadata": {},
   "outputs": [],
   "source": [
    "cdot_columns = {\n",
    "    \"sr_number\": \"id\",\n",
    "    \"date\": \"date\",\n",
    "    \"address\": \"address\",\n",
    "    \"ward\": \"ward\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "bdbc85b9-f451-49c5-8b26-efe525555b32",
   "metadata": {},
   "outputs": [],
   "source": [
    "trimmed_cdot_df = (\n",
    "    cdot_df[cdot_columns.keys()]\n",
    "    .rename(columns=cdot_columns)\n",
    "    .sort_values(\"date\")\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad5bb801-1cd9-46a2-8c61-5f7e660a40b4",
   "metadata": {},
   "source": [
    "Clean up the ward field so it only contains a number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "910d7277-d3c4-4852-ac12-eb7f5ed4578f",
   "metadata": {},
   "outputs": [],
   "source": [
    "trimmed_cdot_df.ward = trimmed_cdot_df.ward.apply(lambda x: x.replace(\"-Ward\", \"\").strip() if not pd.isnull(x) else pd.NA)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "788f06cc-225f-46b5-8be1-4f260b872d42",
   "metadata": {},
   "outputs": [],
   "source": [
    "trimmed_cdot_df.loc[\n",
    "    trimmed_cdot_df[\"ward\"] == \"0\",\n",
    "    'ward'\n",
    "] = pd.NA"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98967102-18ac-4a53-af49-6ee9a91a59f2",
   "metadata": {},
   "source": [
    "Since each row represents a single tree, set the value as 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "0d1c1b8a-12c3-4624-81c3-258389a53331",
   "metadata": {},
   "outputs": [],
   "source": [
    "trimmed_cdot_df['trees'] = 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "033d80b7-e39f-4b6c-a38e-5226a4b38f15",
   "metadata": {},
   "source": [
    "Tag the source"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "16cdd68d-e2c2-4de9-a9c5-0ebc90b2a2dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "trimmed_cdot_df['source'] = 'cdot'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c319b037-b64a-4cdd-9fb7-f025a39d793a",
   "metadata": {},
   "source": [
    "Get rid of really old dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "0130dbb5-338c-4bea-8c42-287afa9961ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_cdot_df = trimmed_cdot_df[trimmed_cdot_df.date >= \"2008-01-01\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f01ac4a9-4bc4-4980-86ce-26001906b8c5",
   "metadata": {},
   "source": [
    "Calculate an address column for CDOT's second sheet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "7ea8aacc-6f2d-4f4a-89f1-49b54806f53b",
   "metadata": {},
   "outputs": [],
   "source": [
    "cdot2_df['address'] = cdot2_df['Work\\nOrder:\\nStreet\\nDirection'] + \" \" + cdot2_df['Work Order:\\nStreet Name'] + \" \" + cdot2_df['Work\\nOrder:\\nStreet Type']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfdcf909-24db-4f94-b170-b45ba9c7badc",
   "metadata": {},
   "source": [
    "Clean up the columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "ae6e130d-d30e-4c86-8ad9-7f70cf5d67cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "cdot2_columns = {\n",
    "    \"Work Order Number\": \"id\",\n",
    "    \"address\": \"address\",\n",
    "    \"Ward\": \"ward\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "cc63c438-68c3-4150-859d-cc1898c8071a",
   "metadata": {},
   "outputs": [],
   "source": [
    "trimmed_cdot2_df = (\n",
    "    cdot2_df[cdot2_columns.keys()]\n",
    "    .rename(columns=cdot2_columns)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9d0d8e7-3ce6-460b-89a7-de2b8ef095ca",
   "metadata": {},
   "source": [
    "Set the tree number again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "96609a5c-5b04-46c9-b468-0331be9ebab6",
   "metadata": {},
   "outputs": [],
   "source": [
    "trimmed_cdot2_df['trees'] = 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29421a0d-31f9-477d-aa4f-759ff7ea8861",
   "metadata": {},
   "source": [
    "Tag the source again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "e275727d-4cd4-4500-b01f-258aa48b9055",
   "metadata": {},
   "outputs": [],
   "source": [
    "trimmed_cdot2_df['source'] = 'cdot'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93a12767-b49f-45f1-9846-b6c3d84c00dc",
   "metadata": {},
   "source": [
    "## Parks Department"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "507aed82-028c-4bf4-a6c0-8059ba88d522",
   "metadata": {},
   "source": [
    "Read in the raw data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "216b62aa-42a4-46a3-b837-a2e4ff328739",
   "metadata": {},
   "outputs": [],
   "source": [
    "parks_df = pd.read_csv(\n",
    "    \"input/tabula-Park District Tree Plantings.csv\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b27eb128-778e-42ce-acee-905945b735e3",
   "metadata": {},
   "source": [
    "Standardize the columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "bcbc505a-66ff-43f1-ab56-5979d444a5af",
   "metadata": {},
   "outputs": [],
   "source": [
    "parks_columns = {\n",
    "    \"Park\": \"address\",\n",
    "    \"Tree Total\": \"trees\",\n",
    "    \"Year\": \"year\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "8aec31e3-8914-475f-af7d-8284f2483386",
   "metadata": {},
   "outputs": [],
   "source": [
    "trimmed_parks_df = (\n",
    "    parks_df[parks_columns.keys()]\n",
    "    .rename(columns=parks_columns)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c390700a-eba0-4033-8b2d-1d0ae04b73b2",
   "metadata": {},
   "source": [
    "Tag the source"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "d0f7240a-f96f-4cc3-bc46-65e27f24a6b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "trimmed_parks_df['source'] = 'parks'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "638b18bf-15ec-46c5-9fe2-8fd69709b7e2",
   "metadata": {},
   "source": [
    "Get rid of junky old dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "a0ecfa56-fe6c-4936-9a17-0c5d2d3c329d",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_parks_df = trimmed_parks_df[trimmed_parks_df.year >= 2008]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24ababcf-3b88-487b-8f34-ec957b814ea5",
   "metadata": {},
   "source": [
    "## Concatenate"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "677978b3-8791-4787-9a11-0dcf8ebd5692",
   "metadata": {},
   "source": [
    "Combine all the dataframes together"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "41f2a4a6-1ae2-44f0-8a89-0af2c9d301db",
   "metadata": {},
   "outputs": [],
   "source": [
    "concat_df = pd.concat([deduped_dss_df, filtered_cdot_df, trimmed_cdot2_df, filtered_parks_df])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51e6ec55-a23c-4fc1-a8af-3d2e2a0bbdc9",
   "metadata": {},
   "source": [
    "Add a year column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "bb031975-d6fd-414f-8244-84984fe60feb",
   "metadata": {},
   "outputs": [],
   "source": [
    "concat_df['year'] = concat_df['date'].dt.year"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efb0a623-a6c9-49f1-8548-4e3551cf6446",
   "metadata": {},
   "source": [
    "## Export"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8bfeb449-84bc-4ff9-b107-5d3fb4f76bfe",
   "metadata": {},
   "source": [
    "Output the results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "4b12a9ab-6747-43da-b98f-236e3bdb3c19",
   "metadata": {},
   "outputs": [],
   "source": [
    "concat_df.to_csv(\"./output/trees.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}