analysis-monvalley.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "77581c66-58fe-43d5-83cd-06d61ca1aee1",
   "metadata": {},
   "source": [
    "# Analyze Mon Valley property assessment appeals for 2022S and 2023"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2d6cd860-ed5f-44de-9bac-a17b96c43f0c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3407d484-bd21-4ec1-978d-21313e94a8c3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "db = create_engine('postgresql://jon@localhost:5432/propertydb')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "040e7ced-c9ff-4367-b748-00e0c626f22f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "allappeals = pd.read_sql_query(\"\"\"SELECT DISTINCT(parcelid)\n",
    "FROM appeals\n",
    "WHERE filer = 'PROPERTY OWNER'\n",
    "AND appealperiod IN ('2022S', '2023');\"\"\", db)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a676600c-9903-4f5e-80aa-fc06459703dd",
   "metadata": {},
   "source": [
    "### Select waterfront parcels, appeals by property owner"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bf27744f-ab11-42de-a137-62e13ed4fdc2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "mjwaterfrontproperties = pd.read_sql_query(\"\"\"SELECT DISTINCT(parcelid), municode, munidesc, fairmarkettotal::int, parcelboundaries.wkt\n",
    "FROM assessments\n",
    "JOIN parcelboundaries\n",
    "ON assessments.parcelid = parcelboundaries.pin\n",
    "WHERE taxcode = 'T'\n",
    "AND municode IN ('834', '840', '863')\n",
    "AND (changenoticeaddress1 LIKE '20%%S%%CLARK%%ST%%' OR changenoticeaddress1 LIKE '180%%MICHIGAN%%AVE%%');\"\"\", db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b335b762-2e73-4820-aceb-cf31c6c33840",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "mjwaterfrontproperties['appeal'] = mjwaterfrontproperties.parcelid.isin(allappeals.parcelid)\n",
    "\n",
    "mjwaterfrontproperties['fill'] = ''\n",
    "mjwaterfrontproperties.loc[mjwaterfrontproperties.appeal, 'fill'] = '#fa8c00'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "15ce0c55-69c2-41fc-9771-765f832221eb",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "94252850"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mjwaterfrontproperties.fairmarkettotal.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "1e2e961c-5909-43f0-8610-354fea38bd00",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.7474140039266717"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mjwaterfrontproperties[mjwaterfrontproperties.appeal].fairmarkettotal.sum() / mjwaterfrontproperties.fairmarkettotal.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "953d9fd2-a18e-42a9-8d7b-461c39b8393c",
   "metadata": {
    "tags": []
   },
   "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>n</th>\n",
       "      <th>y</th>\n",
       "      <th>%_appeal</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>munidesc</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Homestead</th>\n",
       "      <td>3.629730e+06</td>\n",
       "      <td>7583790.0</td>\n",
       "      <td>0.676308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Munhall</th>\n",
       "      <td>1.780567e+06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West Homestead</th>\n",
       "      <td>1.055333e+05</td>\n",
       "      <td>6505390.0</td>\n",
       "      <td>0.984037</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             n          y  %_appeal\n",
       "munidesc                                           \n",
       "Homestead         3.629730e+06  7583790.0  0.676308\n",
       "Munhall           1.780567e+06        NaN       NaN\n",
       "West Homestead    1.055333e+05  6505390.0  0.984037"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "piv = pd.pivot_table(mjwaterfrontproperties, index='munidesc', columns='appeal', values='fairmarkettotal')\n",
    "piv.columns = ['n', 'y']\n",
    "piv['%_appeal'] = piv.y / (piv.y + piv.n)\n",
    "piv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "01c42552-6940-4473-b618-18965e7ac419",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "mjwaterfrontproperties.to_csv('output/waterfront.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f382f9d-a93f-48ba-a846-52c6dfd533a4",
   "metadata": {},
   "source": [
    "### Select U.S. Steel parcels, appeals by property owner"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "51ce2edf-8224-4e95-9c12-f1b69d304be3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "steelproperties = pd.read_sql_query(\"\"\"SELECT DISTINCT(assessments.parcelid), propertyhousenum, propertyaddress, munidesc, fairmarkettotal::int, parcelcentroids.lat, parcelcentroids.lon\n",
    "FROM assessments\n",
    "JOIN parcelcentroids\n",
    "ON assessments.parcelid = parcelcentroids.parcelid\n",
    "WHERE taxcode = 'T'\n",
    "AND (changenoticeaddress1 LIKE '1%%PPG%%PL%%2810%%' OR assessments.parcelid = '0657S00020000000');\"\"\", db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "6dc2fafe-de38-4ac2-a734-044203401829",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "steelproperties['appeal'] = steelproperties.parcelid.isin(allappeals.parcelid)\n",
    "\n",
    "steelproperties['fill'] = ''\n",
    "steelproperties.loc[steelproperties.appeal, 'fill'] = '#fa8c00'\n",
    "steelproperties['symbol'] = 'locator'\n",
    "\n",
    "steelproperties['title'] = ' '\n",
    "steelproperties['tooltip'] = steelproperties.agg(lambda x: f\"<b>Address:</b> {x['propertyhousenum']} {x['propertyaddress']}\\n<b>Municipality:</b> {x['munidesc']}\\n<b>Tax value:</b> {'${:,}'.format(x['fairmarkettotal'])}\", axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3db8cb1c-dc1e-40db-b94d-2fd64a027db3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "steelproperties.to_csv('output/steel.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.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}