<jupyter_start><jupyter_text>**<NAME> - Python Coding for Public Policy**  Class 2: Manipulating/combining DataFrames and writing functions  LECTURE  Operator precedencea.k.a. order of operations ```pythonanswer = "No"answer == "Yes" or "yes"```What will this evaluate to? [Evaluation order](https://docs.python.org/3/reference/expressions.htmlevaluation-order) and [operator precedence](https://docs.python.org/3/reference/expressions.htmloperator-precedence) Does Python follow [PEMDAS](https://en.wikipedia.org/wiki/Order_of_operationsMnemonics)? ```pythonanswer = "No"result =  answer == "Yes"  or "yes"           ↓result =   "No"  == "Yes"  or "yes"``` ```python                 ↓result = ( "No"  == "Yes") or "yes" `==` has higher precedence than `or```` ```python                 ↓result =        False      or "yes"``` ```python                          ↓result =                 "yes"```<jupyter_code>answer = "No"

result = answer == "Yes" or "yes"
result<jupyter_output><empty_output><jupyter_text>**Today's goal**: Which Community Districts have the most 311 requests? Why might that be?  What's a Community District?- 59 local governance districts each run by an appointed Community Board- Community boards advise on land use and zoning, participate in the city budget process, and address service delivery in their district.- Community boards are each composed of <= 50 volunteer members appointed by the local borough president, half from nominations by the local City Council members.[More info](https://en.wikipedia.org/wiki/Community_boards_of_New_York_City) ![Map of community districts from Wikipedia](https://upload.wikimedia.org/wikipedia/commons/4/41/New_York_City_community_districts.svg)  Setup<jupyter_code>import pandas as pd
# Display more rows and columns in the DataFrames
pd.options.display.max_columns = 100
pd.options.display.max_rows = 100<jupyter_output><empty_output><jupyter_text>Read our cleaned 311 Service Requests dataset<jupyter_code>url = 'https://storage.googleapis.com/python-public-policy/data/311_Service_Requests_2018-19_clean.csv.zip'
df = pd.read_csv(url)<jupyter_output>/usr/local/Caskroom/miniconda/base/envs/python-public-policy/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3155: DtypeWarning: Columns (8,17,20,31,34) have mixed types.Specify dtype option on import or set low_memory=False.
  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,<jupyter_text>Dealing with dtypesMore data cleaning!![Minion character vacuuming](https://impulsecreative.com/hs-fs/hubfs/cleaning-minion-gif.gif?width=490&name=cleaning-minion-gif.gif) ```DtypeWarning: Columns (8,17,20,31,34) have mixed types.```<jupyter_code>df.info()
df["Incident Zip"].unique().tolist()<jupyter_output><empty_output><jupyter_text>ZIP codes _look_ numeric, but aren't really. Read the ZIP codes in as strings.<jupyter_code>df2 = pd.read_csv(url, dtype={"Incident Zip": object})<jupyter_output>/usr/local/Caskroom/miniconda/base/envs/python-public-policy/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3155: DtypeWarning: Columns (17,20,31,34) have mixed types.Specify dtype option on import or set low_memory=False.
  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,<jupyter_text>We fixed the dtype warning for column 8 (`Incident Zip`).<jupyter_code>df2["Incident Zip"].unique().tolist()<jupyter_output><empty_output><jupyter_text>Find invalid ZIP codesUse a [regular expression (regex)](https://regexone.com/):```^\d{5}(?:-\d{4})?$│ │ │  │        │└─ end of string│ │ │  │        └─ optional│ │ │  └─ capture group│ │ └─ count│ └─ numeric/digit character└─ start of string```<jupyter_code>invalid_zips = df2["Incident Zip"].str.contains(r'^\d{5}(?:-\d{4})?$') == False
df2[invalid_zips]["Incident Zip"]<jupyter_output><empty_output><jupyter_text>Clear any invalid ZIP codes:<jupyter_code>import numpy as np

df2.loc[invalid_zips, "Incident Zip"] = np.nan<jupyter_output><empty_output><jupyter_text>General data cleaning tips:- Hard part is finding what needs to be done- Will be specific to your use case- Document what you did, since it will affect your results  View the contents of the `community_board` column in our 311 data<jupyter_code>df["Community Board"].unique()<jupyter_output><empty_output><jupyter_text>Get the count of 311 requests per Community District<jupyter_code>cb_counts = df.groupby('Community Board').size().reset_index(name='count_of_311_requests')
cb_counts = cb_counts.sort_values('count_of_311_requests', ascending=False)
cb_counts<jupyter_output><empty_output><jupyter_text>**Research Question:** What may account for the variance in count of requests per community district?  **Hypothesis:** Population size may help explain the variance.We can combine the counts per community district dataset with population data for each community district.  Let's load the population dataset and check out its contents[Data source for population by Community District](https://data.cityofnewyork.us/City-Government/New-York-City-Population-By-Community-Districts/xi7c-iiu2/data)<jupyter_code>population = pd.read_csv('https://data.cityofnewyork.us/api/views/xi7c-iiu2/rows.csv')
population.head()<jupyter_output><empty_output><jupyter_text>In order to join the two dataframes, we need to create a common ID in each.[`BORO CODE`](https://www1.nyc.gov/assets/planning/download/pdf/data-maps/open-data/pluto_datadictionary.pdfpage=38) (a.k.a. `BoroCode`, `borocd`, and `boro_cd`) is a commonly-used a unique ID for community districts. Let's create functions that create that unique ID in our datasets. **BoroCD** is a 3 digit integer that captures the borough and district number. The borough is represented by the first digit. The district number is padded with zeros so it's always two digits long.Boroughs are recoded into the following numbers:- 1: Manhattan- 2: Bronx- 3: Brooklyn- 4: Queens- 5: Staten IslandEx: - Manhattan 12 --> 112- Brooklyn 6 --> 306  First, let's create a `borocd` column in `cb_counts` dataframe<jupyter_code>cb_counts.head()<jupyter_output><empty_output><jupyter_text>Let's create a function called `recode_borocd_counts` that converts the `Community Board` value into a `borocd` value.<jupyter_code>def recode_borocd_counts(row):
  if 'MANHATTAN' in row["Community Board"]:
    return '1' + row["Community Board"][0:2]
    # [0:2] provides the first 2 characters, i.e. characters at indexes 0 and 1.
    # you could also use [:2] without the zero.
  elif 'BRONX' in row["Community Board"]:
    return '2' + row["Community Board"][0:2]
  elif 'BROOKLYN' in row["Community Board"]:
    return '3' + row["Community Board"][0:2]
  elif 'QUEENS' in row["Community Board"]:
    return '4' + row["Community Board"][0:2]
  elif 'STATEN ISLAND' in row["Community Board"]:
    return '5' + row["Community Board"][0:2]
  else:
    return 'Invalid BoroCD'<jupyter_output><empty_output><jupyter_text>As we can see, `def` creates functions:<jupyter_code>type(recode_borocd_counts)<jupyter_output><empty_output><jupyter_text>**Note:** The order of steps is important. You have to define a function before you can apply it to a dataframe.<jupyter_code>cb_counts['borocd'] = cb_counts.apply(recode_borocd_counts, axis=1)<jupyter_output><empty_output><jupyter_text>- `axis = 1` specifies that you want to apply the function across the rows instead of columns- `cb_counts['borocd']` creates a new column in the dataframe called `borocd`- `cb_counts.apply(function, axis)` applies the function we defined across the specified axis<jupyter_code>cb_counts<jupyter_output><empty_output><jupyter_text>Uh oh, there are some unexpected `Unspecified` values in here - how can we get around them? Let's only recode records that don't start with "U".<jupyter_code>def recode_borocd_counts(row):
  if 'MANHATTAN' in row["Community Board"] and row["Community Board"][0] != 'U':
      return '1' + row["Community Board"][:2]
  elif 'BRONX' in row["Community Board"] and row["Community Board"][0] != 'U':
      return '2' + row["Community Board"][:2]
  elif 'BROOKLYN' in row["Community Board"] and row["Community Board"][0] != 'U':
      return '3' + row["Community Board"][:2]
  elif 'QUEENS' in row["Community Board"] and row["Community Board"][0] != 'U':
      return '4' + row["Community Board"][:2]
  elif 'STATEN ISLAND' in row["Community Board"] and row["Community Board"][0] != 'U':
      return '5' + row["Community Board"][:2]
  else:
    return 'Invalid BoroCD'

cb_counts['borocd'] = cb_counts.apply(recode_borocd_counts, axis=1)
cb_counts<jupyter_output><empty_output><jupyter_text>We can make this function easier to read by isolating the logic that applies to all the conditions. This is called "refactoring".<jupyter_code>def recode_borocd_counts(row):
    board = row["Community Board"]

    if board[0] != 'U':
        num = board[0:2]
        
        if 'MANHATTAN' in board:
            return '1' + num
        elif 'BRONX' in board:
            return '2' + num
        elif 'BROOKLYN' in board:
            return '3' + num
        elif 'QUEENS' in board:
            return '4' + num
        elif 'STATEN ISLAND' in board:
            return '5' + num
    else:
        return 'Invalid BoroCD'
cb_counts['borocd'] = cb_counts.apply(recode_borocd_counts, axis=1)
cb_counts<jupyter_output><empty_output><jupyter_text>Next, let's create the `borocd` column in the population dataset<jupyter_code>population.head()<jupyter_output><empty_output><jupyter_text>Create a function `recode_borocd_pop` that combines and recodes the Borough and CD Number values to create a BoroCD unique ID.<jupyter_code>def recode_borocd_pop(row):
  if row.Borough == 'Manhattan':
    return str(100 + row['CD Number'])
  elif row.Borough == 'Bronx':
    return str(200 + row['CD Number'])
  elif row.Borough == 'Brooklyn':
    return str(300 + row['CD Number'])
  elif row.Borough == 'Queens':
    return str(400 + row['CD Number'])
  elif row.Borough == 'Staten Island':
    return str(500 + row['CD Number'])
  else:
    return 'Invalid BoroCD'
population['borocd'] = population.apply(recode_borocd_pop, axis=1)
population<jupyter_output><empty_output><jupyter_text>Join the population data onto the counts data after creating shared `borocd` unique ID To join dataframes together, we will use the [pandas `.merge()` function](https://pandas.pydata.org/pandas-docs/stable/getting_started/intro_tutorials/08_combine_dataframes.htmljoin-tables-using-a-common-identifier).![merge diagram](https://pandas.pydata.org/pandas-docs/stable/_images/08_merge_left.svg)<jupyter_code>merged_data = pd.merge(left=cb_counts, right=population, left_on='borocd', right_on='borocd')
merged_data<jupyter_output><empty_output><jupyter_text>Calculate 311 requests per capita<jupyter_code># divide request count by 2010 population to get request per capita

merged_data['request_per_capita'] = merged_data.count_of_311_requests / merged_data['2010 Population']

merged_data.head()<jupyter_output><empty_output><jupyter_text>Let's create a simplified new dataframe that only include the columns we care about and in a better order.<jupyter_code>columns = ['borocd', 'Borough', 'CD Name', '2010 Population', 'count_of_311_requests', 'request_per_capita']
cd_data = merged_data[columns]

cd_data<jupyter_output><empty_output><jupyter_text>Let's check out which Community Districts have the highest complaints per capita<jupyter_code>cd_data.sort_values('request_per_capita', ascending=False).head(10)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12376065402678726, 'fraction_numerical': 0.018003130979300747, 'mean_word_length': 5.467379077615298, 'pattern_counts': {'":': 1, '<': 97, '<?xml version=': 0, '>': 98, 'https://': 13, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '24', 'dir': 'jupyter-structured-clean-dedup', 'id': '180557', 'license': 'CC0-1.0', 'n_tokens_mistral': 3972, 'n_tokens_neox': 3734, 'n_words': 1009, 'path': 'lecture_2.ipynb', 'repo_name': 'afeld/python-public-policy', 'size': '11502'}