{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lab 2\n",
    "\n",
    "In this lab, you'll perform some directed data analysis on two datasets. The first is a dataset of service requests from New York City, reported to a 311 citizen hotline. The second is a dataset of Washington State employee salaries.\n",
    "\n",
    "Many code cells are one-liners, while a few might require as many as 3 or 4. If you find yourself using more lines than that, you should probably look for a simpler approach."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "-7ZoJvJyZhSx"
   },
   "source": [
    "\n",
    "## Part 1: New York City 311 Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "YD9_DOeqaecM"
   },
   "source": [
    "A little setup - import pandas and define the url where we'll pull our data from:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "kmcLTaNxadFP"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "complaints_url = \"/cluster/academic/DATA311/202620/311-service-requests.csv\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "vPEHxz21atY7"
   },
   "source": [
    "Read in the dataset of 311 (citizen hotline - no relation to our course number) requests:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "N_YZzryRZloQ"
   },
   "outputs": [],
   "source": [
    "complaints = pd.read_csv(complaints_url, low_memory=False)\n",
    "complaints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HOEonG5ocnLG"
   },
   "source": [
    "### Basic Selection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "6S_G20Uwa_Ky"
   },
   "source": [
    "1.1 Display the first 8 rows of the dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "N374MPZksdYJ"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HgJo0QcBbQPv"
   },
   "source": [
    "1.2 Extract and display just the \"Complaint Type\" column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "UWKF0P24sd_N"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "8p9zUVcEbLiX"
   },
   "source": [
    "1.3 Combine the techniques of the above two questions to get the first 8 rows of just the complaint type column. Does it matter which order you select them in (column then rows, or rows then column)?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "7Beiq537sear"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "95OgdDs7bx5u"
   },
   "source": [
    "1.4 Extract a DataFrame containing only the \"Complaint Type\" and \"Borough\" columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "6SdW4GCLse3s"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "WQfiW8-Tb97o"
   },
   "source": [
    "1.5 Display a tally of how many of each complaint type appears in the dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "cEWfDGursfgO"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "hvyBwBIcbzTi"
   },
   "source": [
    "### Which borough has the most noise complaints?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "l6nJ18Ybddti"
   },
   "source": [
    "1.6 Create a new Series using == that stores True if the complaint type is equal to \"Noise - Street/Sidewalk\", and False otherwise. Assign it to a variable called `is_noise`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "fTM9x8jHsgYd"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "BUS9eRaNd2Re"
   },
   "source": [
    "1.7 Create a new DataFrame that contains only the noise complaints by indexing `complaints` with `is_noise`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "v3_nmqQXsg2K"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5QP9QWqZeCmj"
   },
   "source": [
    "1.8 Display a summary of the noise complaints; one call should tell you at a glance how many complaints there were, how many unique zip codes there were, and the most common Descriptor associated with noise complaints."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Rm7tsOIZshXD"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "D7R-J5_ye_37"
   },
   "source": [
    "1.9 Display the count of noise complaints for each borough."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "v54Qru38sjFl"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "XwNOQ-AxfK-e"
   },
   "source": [
    "So it looks like Manhattan has the most noise complaints. Not too surprising! But Manhattan might also just have more complaints overall, so let's look at the *fraction* of complaints that were noise complaints."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "z8k7-oANfbxs"
   },
   "source": [
    "1.10 Calculate the total count of complaints (of all types) for each borough. Then divide the noise complaint counts by the total complaint counts to get the fraction of noise complaints. Finally, multiply the result by 100 and store in a DataFrame called `complaint_percents`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "zSyEk0wxsj_M"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "NSPKgNGVgEvB"
   },
   "source": [
    "So yep, it looks like Manhattan is just noisy. Who knew?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "FXpn_7HIevFZ"
   },
   "source": [
    "## Part 2: Washington State Employee Salary Data\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "L7Tf1fmgt8es"
   },
   "source": [
    "This section  performs some directed analyses on a dataset obtained from the Washington State Fiscal Information website.\n",
    "Washington state employee salary information is public by state law and one may obtain a spreadsheet of about 450,0000 salaries from the last five years by simply requesting the data in an email.\n",
    "The first code cell below loads an Excel spreadsheet containing five years of data into a Pandas DataFrame.\n",
    "Additional information about the dataset can be found in the [FAQ](https://fiscal.wa.gov/staffing/SalaryDataFAQ.pdf). Agency codes you may find useful in completing the lab\n",
    " can be found in the [Washington State Agency Codes directory](https://ofm.wa.gov/sites/default/files/public/accounting/singleaudit/2022/24_FY22_Washington_State_Agency_Codes_By_Agency_Assigned_Number.pdf). There are 9 columns in the\n",
    " dataset:\n",
    " 1.  agy -  Integer agency code\n",
    " 2. AgyTitle - Name of agency\n",
    " 3. Name - Lastname, first name (All caps)\n",
    " 4. JobTitle - Name of job (All caps)\n",
    " 5. Sal2019 - 2019 salary\n",
    " 6. Sal2020 - 2020 salary\n",
    " 7. Sal2021 - 2021 salary\n",
    " 8. Sal2022 - 2022 salary\n",
    " 9. Sal2023 - 2023 salary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "K7Xom10ngNp-"
   },
   "source": [
    "### Reading the Data\n",
    "\n",
    "Use the `read_excel` function with its default arguments to load the data into a DataFrame. It's about 26 MB, so may take some time to load; as usual, try to avoid re-running this cell often."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "wK8xsj4Xuyqr"
   },
   "outputs": [],
   "source": [
    "# TODO update\n",
    "df = pd.read_excel('/cluster/academic/DATA311/202620/AnnualEmployeeSalary.xlsx')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "jsnAOrVVtT3U"
   },
   "source": [
    "First, let’s explore the data to answer questions about state employee turnover.\n",
    "A zero salary for a salary year column indicates that the individual did not work for that particular state agency that year.\n",
    "For the purposes of our analyses, use the following definitions:\n",
    "\n",
    "*   A ***new employee*** is someone who has worked for a particular agency in ***at least one year*** , but has not worked for the agency ***in any prior years***.\n",
    "For example, Mekdes Abate in row 0 is a new employee in 2020, and Peter Abbarno in row 1 is a new employee in 2021.\n",
    "Note that for this definition we cannot determine the number of new employees for 2019.\n",
    "\n",
    "*   A ***permanent leave employee*** in a particular year is someone who, during the previous year, worked for the agency but did not work for the agency for ***all following years (for at least two years)***.\n",
    "Mekdes Abate in row 0 is a permanent leave employee in 2021.\n",
    "Note that for this definition we cannot determine the number of permanent leave employees for either 2020 or 2024. Notice that an employee is considered permanent leave only in the year(s) *following* the last year they worked for the agency.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "vCbecSOIv_gc"
   },
   "source": [
    "There are two rows corresponding to Meynun Abdalla working for the House of Representatives. This is far from the only instance of an individual occupying different roles in the same agency over the course of several years (think promotions and lateral career\n",
    "moves). Since we are concerned with employee turnover, it doesn’t seem right to treat Meynun as a new employee for the House of Representatives in both 2022 and 2023. For the purposes of a turnover analysis we should combine all rows which share the same agency and employee by summing the respective yearly salaries for those rows. With our original data loaded into the DataFrame df we can aggregate matching employee/agency rows with the following code snippet:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "SP-_zaPHwJtR"
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "# Make a column that is the cross product of name and agency\n",
    "agys = df['agy'].unique()\n",
    "name_agy = np.array([agy.strip() +'%' + name.strip()\n",
    "for agy, name in df[['AgyTitle', 'Name']].to_numpy()])\n",
    "df['nagy'] = name_agy\n",
    "\n",
    "# Aggregate their pay for each year they worked at the same agency\n",
    "agg = df.groupby('nagy')\n",
    "agg = agg.aggregate({f'Sal{str(year)}': \"sum\" for year in range(2019, 2024)})\n",
    "\n",
    "# Create agency column from nagy (short for name/agency) index\n",
    "agg['agy'] = agg.index\n",
    "agg['agy'] = agg['agy'].apply(lambda x: x.split('%')[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "PfmfTdMswsiB"
   },
   "outputs": [],
   "source": [
    "agg[agg['agy'] == 'House of Representatives'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "K5KBcGe_wpaA"
   },
   "source": [
    "Notice the aggregated income data for Meynun Abdalla and Katherine Abernathy in the resulting DataFrame. Perform the following analyses using the aggregated DataFrame `agg`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "tdSu7wKixMUa"
   },
   "source": [
    "2.1 In total for all agencies, how many new employees were there in each year 2020-2023?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "iGom8P9Axj8p"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ove4eWPJxS5l"
   },
   "source": [
    "2.2 What is the average number of new employees from an agency for each year 2020-2023?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "UkIO9nCdxkVY"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0i5H6hrrxWHA"
   },
   "source": [
    "2.3 In total for all agencies, how many permanent leave employees were there from each year 2020-2022?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "glLQVyfaxkz4"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "QAGZvZyYxZE5"
   },
   "source": [
    "2.4 What is the average number of permanent leave employees from an agency for each year 2020-2022?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "S2HFVWuTxlST"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "IvblgAvyxcLs"
   },
   "source": [
    "2.5 What percentage of state employees have worked for more than one agency over the years 2019-2023?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "4cNenQMMxfDW"
   },
   "source": [
    "2.6 In a markdown cell, synthesize your findings from questions 2.1-2.5 to summarize trends in state employment over the years 2020-2024."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "fECuWBy_xjXM"
   },
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "MJd-NgdJx5F3"
   },
   "source": [
    "Next we will use the dataset to investigate what might be good state jobs to apply for.\n",
    "**Exclude salaries less than $1000 from these analyses.**\n",
    "We will look at median salary for each agency using AgyTitle field, i.e. the institution a person worked for."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "to2KWD2Yx-8K"
   },
   "source": [
    "2.7 List the top ten paid agencies (by median salary) for each year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "sFTqijNyyMqs"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5FtO9rFDyD3c"
   },
   "source": [
    "2.8 List the bottom ten paid agencies (by median salary) for each year. Did this change over time?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "h2Xy1YGLyNGe"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "GvIC2NXdyG5f"
   },
   "source": [
    "2.9 List the ten agencies with the most employees in the dataset. Do any of these overlap with the top ten paid agencies?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "LJr8CZtMyJxh"
   },
   "source": [
    "2.10 In a markdown cell discuss how the results of these analyses might inform a person’s decision to apply to prospective state employers."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "wlx8e5huyL2p"
   },
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}