Lecture 18: Combining data#
import pandas as pd
Discussion: Let’s get messy!#
Concatenation#
revenue_q1 = pd.DataFrame(
{
"month": ["Jan", "Feb", "Mar"],
"revenue": [175589, 189645, 163423],
}
)
revenue_q1
| month | revenue | |
|---|---|---|
| 0 | Jan | 175589 |
| 1 | Feb | 189645 |
| 2 | Mar | 163423 |
revenue_q2 = pd.DataFrame(
{
"month": ["Apr", "May", "Jun"],
"revenue": [14567, 15863, 17999],
}
)
revenue_q2
| month | revenue | |
|---|---|---|
| 0 | Apr | 14567 |
| 1 | May | 15863 |
| 2 | Jun | 17999 |
pd.concat([revenue_q1, revenue_q2])
| month | revenue | |
|---|---|---|
| 0 | Jan | 175589 |
| 1 | Feb | 189645 |
| 2 | Mar | 163423 |
| 0 | Apr | 14567 |
| 1 | May | 15863 |
| 2 | Jun | 17999 |
Simple merge#
I had Copilot generate the DataFrames, so no idea if the numbers are real.
populations = pd.DataFrame(
{
"Country": ["China", "India", "Pakistan"],
"Population": [1444216107, 1393409038, 220892331],
}
)
populations
| Country | Population | |
|---|---|---|
| 0 | China | 1444216107 |
| 1 | India | 1393409038 |
| 2 | Pakistan | 220892331 |
gdps = pd.DataFrame(
{
"Country": ["China", "India", "United States", "Indonesia", "Pakistan"],
"GDP": [14342903, 2875142, 21433226, 1058393, 263687],
}
)
gdps
| Country | GDP | |
|---|---|---|
| 0 | China | 14342903 |
| 1 | India | 2875142 |
| 2 | United States | 21433226 |
| 3 | Indonesia | 1058393 |
| 4 | Pakistan | 263687 |
pd.merge(populations, gdps, on="Country")
| Country | Population | GDP | |
|---|---|---|---|
| 0 | China | 1444216107 | 14342903 |
| 1 | India | 1393409038 | 2875142 |
| 2 | Pakistan | 220892331 | 263687 |
pd.merge(populations, gdps, on="Country", how="outer")
| Country | Population | GDP | |
|---|---|---|---|
| 0 | China | 1.444216e+09 | 14342903 |
| 1 | India | 1.393409e+09 | 2875142 |
| 2 | Indonesia | NaN | 1058393 |
| 3 | Pakistan | 2.208923e+08 | 263687 |
| 4 | United States | NaN | 21433226 |
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 up to 50 volunteer members appointed by the local borough president, half from nominations by the local City Council members.
Setup#
# Display more rows and columns in the DataFrames
pd.options.display.max_columns = 100
pd.options.display.max_rows = 100
Read our cleaned 311 Service Requests dataset#
url = "https://storage.googleapis.com/python-public-policy2/data/311_requests_2018-19_sample_clean.csv.zip"
requests = pd.read_csv(url)
/var/folders/kr/nx0m1j811kz5vy8c87ffchzr0000gn/T/ipykernel_12586/610958175.py:2: DtypeWarning: Columns (8,20,31,34) have mixed types. Specify dtype option on import or set low_memory=False.
requests = pd.read_csv(url)
View the contents of the community_board column in our 311 data#
requests["Community Board"].unique()
array(['15 BROOKLYN', '03 BROOKLYN', '14 QUEENS', '10 BRONX', '08 QUEENS',
'02 BRONX', '01 QUEENS', '11 QUEENS', '02 MANHATTAN',
'18 BROOKLYN', '12 QUEENS', '01 STATEN ISLAND', '12 BRONX',
'05 BROOKLYN', '01 BRONX', '09 QUEENS', '04 BROOKLYN',
'10 BROOKLYN', '02 STATEN ISLAND', '05 QUEENS', '04 MANHATTAN',
'11 BRONX', 'Unspecified BROOKLYN', '09 BRONX', '12 MANHATTAN',
'09 BROOKLYN', '14 BROOKLYN', '06 MANHATTAN', '10 MANHATTAN',
'Unspecified QUEENS', '01 MANHATTAN', '03 MANHATTAN', '05 BRONX',
'08 BROOKLYN', '02 QUEENS', '12 BROOKLYN', '01 BROOKLYN',
'16 BROOKLYN', '13 BROOKLYN', '06 QUEENS', '07 MANHATTAN',
'11 BROOKLYN', 'Unspecified BRONX', '08 MANHATTAN',
'03 STATEN ISLAND', '06 BROOKLYN', '03 BRONX', '05 MANHATTAN',
'07 QUEENS', '13 QUEENS', '17 BROOKLYN', '06 BRONX', '02 BROOKLYN',
'10 QUEENS', 'Unspecified MANHATTAN', '03 QUEENS', '04 BRONX',
'11 MANHATTAN', '08 BRONX', '07 BROOKLYN', '07 BRONX',
'0 Unspecified', '09 MANHATTAN', '04 QUEENS', '83 QUEENS',
'80 QUEENS', 'Unspecified STATEN ISLAND', '55 BROOKLYN',
'82 QUEENS', '64 MANHATTAN', '28 BRONX', '95 STATEN ISLAND',
'81 QUEENS', '27 BRONX', '26 BRONX', '56 BROOKLYN', '84 QUEENS'],
dtype=object)
Get the count of 311 requests per Community District#
cb_counts = requests.groupby("Community Board").size().reset_index(name="num_311_requests")
cb_counts = cb_counts.sort_values("num_311_requests", ascending=False)
cb_counts
| Community Board | num_311_requests | |
|---|---|---|
| 50 | 12 MANHATTAN | 14110 |
| 23 | 05 QUEENS | 12487 |
| 51 | 12 QUEENS | 12228 |
| 2 | 01 BROOKLYN | 11863 |
| 12 | 03 BROOKLYN | 11615 |
| 5 | 01 STATEN ISLAND | 11438 |
| 31 | 07 QUEENS | 11210 |
| 21 | 05 BROOKLYN | 10862 |
| 16 | 04 BRONX | 10628 |
| 4 | 01 QUEENS | 10410 |
| 58 | 17 BROOKLYN | 10208 |
| 54 | 14 BROOKLYN | 10179 |
| 28 | 07 BRONX | 9841 |
| 59 | 18 BROOKLYN | 9717 |
| 39 | 09 QUEENS | 9599 |
| 53 | 13 QUEENS | 9459 |
| 48 | 12 BRONX | 9412 |
| 45 | 11 BROOKLYN | 9309 |
| 20 | 05 BRONX | 9094 |
| 13 | 03 MANHATTAN | 8905 |
| 35 | 08 QUEENS | 8661 |
| 17 | 04 BROOKLYN | 8639 |
| 42 | 10 MANHATTAN | 8592 |
| 15 | 03 STATEN ISLAND | 8524 |
| 56 | 15 BROOKLYN | 8508 |
| 43 | 10 QUEENS | 8333 |
| 49 | 12 BROOKLYN | 8214 |
| 30 | 07 MANHATTAN | 8141 |
| 36 | 09 BRONX | 8092 |
| 41 | 10 BROOKLYN | 7808 |
| 33 | 08 BROOKLYN | 7797 |
| 7 | 02 BROOKLYN | 7747 |
| 37 | 09 BROOKLYN | 7571 |
| 25 | 06 BROOKLYN | 7373 |
| 10 | 02 STATEN ISLAND | 7059 |
| 0 | 0 Unspecified | 6882 |
| 14 | 03 QUEENS | 6799 |
| 38 | 09 MANHATTAN | 6792 |
| 73 | Unspecified BROOKLYN | 6771 |
| 18 | 04 MANHATTAN | 6765 |
| 22 | 05 MANHATTAN | 6599 |
| 34 | 08 MANHATTAN | 6579 |
| 8 | 02 MANHATTAN | 6514 |
| 44 | 11 BRONX | 6448 |
| 74 | Unspecified MANHATTAN | 6427 |
| 29 | 07 BROOKLYN | 6364 |
| 9 | 02 QUEENS | 6142 |
| 19 | 04 QUEENS | 5824 |
| 32 | 08 BRONX | 5753 |
| 47 | 11 QUEENS | 5748 |
| 24 | 06 BRONX | 5746 |
| 40 | 10 BRONX | 5719 |
| 46 | 11 MANHATTAN | 5714 |
| 72 | Unspecified BRONX | 5349 |
| 55 | 14 QUEENS | 5284 |
| 11 | 03 BRONX | 5262 |
| 26 | 06 MANHATTAN | 5158 |
| 57 | 16 BROOKLYN | 5100 |
| 1 | 01 BRONX | 4915 |
| 75 | Unspecified QUEENS | 4870 |
| 27 | 06 QUEENS | 4607 |
| 52 | 13 BROOKLYN | 3840 |
| 3 | 01 MANHATTAN | 3623 |
| 6 | 02 BRONX | 3470 |
| 76 | Unspecified STATEN ISLAND | 541 |
| 69 | 83 QUEENS | 193 |
| 65 | 64 MANHATTAN | 126 |
| 66 | 80 QUEENS | 102 |
| 68 | 82 QUEENS | 72 |
| 67 | 81 QUEENS | 54 |
| 63 | 55 BROOKLYN | 53 |
| 62 | 28 BRONX | 26 |
| 60 | 26 BRONX | 25 |
| 71 | 95 STATEN ISLAND | 21 |
| 70 | 84 QUEENS | 18 |
| 61 | 27 BRONX | 17 |
| 64 | 56 BROOKLYN | 13 |
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.
We’ll use pandas’ .merge(), comparable to:
In general, called “record linkage” or “entity resolution”.
Let’s load the population dataset and check out its contents#
Data source for population by Community District
population = pd.read_csv("https://data.cityofnewyork.us/api/views/xi7c-iiu2/rows.csv")
population.head()
| Borough | CD Number | CD Name | 1970 Population | 1980 Population | 1990 Population | 2000 Population | 2010 Population | |
|---|---|---|---|---|---|---|---|---|
| 0 | Bronx | 1 | Melrose, Mott Haven, Port Morris | 138557 | 78441 | 77214 | 82159 | 91497 |
| 1 | Bronx | 2 | Hunts Point, Longwood | 99493 | 34399 | 39443 | 46824 | 52246 |
| 2 | Bronx | 3 | Morrisania, Crotona Park East | 150636 | 53635 | 57162 | 68574 | 79762 |
| 3 | Bronx | 4 | Highbridge, Concourse Village | 144207 | 114312 | 119962 | 139563 | 146441 |
| 4 | Bronx | 5 | University Hts., Fordham, Mt. Hope | 121807 | 107995 | 118435 | 128313 | 128200 |
We need a composite key#
BORO CODE (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 Island
Ex:
Manhattan 12 –> 112
Brooklyn 6 –> 306
First, let’s create a borocd column in cb_counts DataFrame#
cb_counts.head()
| Community Board | num_311_requests | |
|---|---|---|
| 50 | 12 MANHATTAN | 14110 |
| 23 | 05 QUEENS | 12487 |
| 51 | 12 QUEENS | 12228 |
| 2 | 01 BROOKLYN | 11863 |
| 12 | 03 BROOKLYN | 11615 |
apply() can be used for transforming data with a custom function. How does it work?
def my_function(row):
# do stuff
return some_value
new_values = dataframe.apply(my_function, axis=1)
While pandas generally operates on an entire column at once, apply() is similar to looping through rows with the csv module.
Let’s create a function called recode_borocd_counts that takes a row and converts the Community Board value into a borocd value.
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"
Let’s test out that function in isolation. We’ll grab one of the rows and pass it into the function.
sample_row = cb_counts.iloc[0]
sample_row
Community Board 12 MANHATTAN
num_311_requests 14110
Name: 50, dtype: object
recode_borocd_counts(sample_row)
'112'
Now we use apply() to do that across all the rows.
cb_counts["boro_cd"] = cb_counts.apply(recode_borocd_counts, axis=1)
apply()(the way we’re using it) takes a function and runs it against each row of a DataFrame, returning the results as a Seriesaxis=1specifies that you want to apply the function across the rows instead of columnscb_counts['borocd'] = …creates a new column in the DataFrame calledborocd
cb_counts
| Community Board | num_311_requests | boro_cd | |
|---|---|---|---|
| 50 | 12 MANHATTAN | 14110 | 112 |
| 23 | 05 QUEENS | 12487 | 405 |
| 51 | 12 QUEENS | 12228 | 412 |
| 2 | 01 BROOKLYN | 11863 | 301 |
| 12 | 03 BROOKLYN | 11615 | 303 |
| 5 | 01 STATEN ISLAND | 11438 | 501 |
| 31 | 07 QUEENS | 11210 | 407 |
| 21 | 05 BROOKLYN | 10862 | 305 |
| 16 | 04 BRONX | 10628 | 204 |
| 4 | 01 QUEENS | 10410 | 401 |
| 58 | 17 BROOKLYN | 10208 | 317 |
| 54 | 14 BROOKLYN | 10179 | 314 |
| 28 | 07 BRONX | 9841 | 207 |
| 59 | 18 BROOKLYN | 9717 | 318 |
| 39 | 09 QUEENS | 9599 | 409 |
| 53 | 13 QUEENS | 9459 | 413 |
| 48 | 12 BRONX | 9412 | 212 |
| 45 | 11 BROOKLYN | 9309 | 311 |
| 20 | 05 BRONX | 9094 | 205 |
| 13 | 03 MANHATTAN | 8905 | 103 |
| 35 | 08 QUEENS | 8661 | 408 |
| 17 | 04 BROOKLYN | 8639 | 304 |
| 42 | 10 MANHATTAN | 8592 | 110 |
| 15 | 03 STATEN ISLAND | 8524 | 503 |
| 56 | 15 BROOKLYN | 8508 | 315 |
| 43 | 10 QUEENS | 8333 | 410 |
| 49 | 12 BROOKLYN | 8214 | 312 |
| 30 | 07 MANHATTAN | 8141 | 107 |
| 36 | 09 BRONX | 8092 | 209 |
| 41 | 10 BROOKLYN | 7808 | 310 |
| 33 | 08 BROOKLYN | 7797 | 308 |
| 7 | 02 BROOKLYN | 7747 | 302 |
| 37 | 09 BROOKLYN | 7571 | 309 |
| 25 | 06 BROOKLYN | 7373 | 306 |
| 10 | 02 STATEN ISLAND | 7059 | 502 |
| 0 | 0 Unspecified | 6882 | Invalid BoroCD |
| 14 | 03 QUEENS | 6799 | 403 |
| 38 | 09 MANHATTAN | 6792 | 109 |
| 73 | Unspecified BROOKLYN | 6771 | 3Un |
| 18 | 04 MANHATTAN | 6765 | 104 |
| 22 | 05 MANHATTAN | 6599 | 105 |
| 34 | 08 MANHATTAN | 6579 | 108 |
| 8 | 02 MANHATTAN | 6514 | 102 |
| 44 | 11 BRONX | 6448 | 211 |
| 74 | Unspecified MANHATTAN | 6427 | 1Un |
| 29 | 07 BROOKLYN | 6364 | 307 |
| 9 | 02 QUEENS | 6142 | 402 |
| 19 | 04 QUEENS | 5824 | 404 |
| 32 | 08 BRONX | 5753 | 208 |
| 47 | 11 QUEENS | 5748 | 411 |
| 24 | 06 BRONX | 5746 | 206 |
| 40 | 10 BRONX | 5719 | 210 |
| 46 | 11 MANHATTAN | 5714 | 111 |
| 72 | Unspecified BRONX | 5349 | 2Un |
| 55 | 14 QUEENS | 5284 | 414 |
| 11 | 03 BRONX | 5262 | 203 |
| 26 | 06 MANHATTAN | 5158 | 106 |
| 57 | 16 BROOKLYN | 5100 | 316 |
| 1 | 01 BRONX | 4915 | 201 |
| 75 | Unspecified QUEENS | 4870 | 4Un |
| 27 | 06 QUEENS | 4607 | 406 |
| 52 | 13 BROOKLYN | 3840 | 313 |
| 3 | 01 MANHATTAN | 3623 | 101 |
| 6 | 02 BRONX | 3470 | 202 |
| 76 | Unspecified STATEN ISLAND | 541 | 5Un |
| 69 | 83 QUEENS | 193 | 483 |
| 65 | 64 MANHATTAN | 126 | 164 |
| 66 | 80 QUEENS | 102 | 480 |
| 68 | 82 QUEENS | 72 | 482 |
| 67 | 81 QUEENS | 54 | 481 |
| 63 | 55 BROOKLYN | 53 | 355 |
| 62 | 28 BRONX | 26 | 228 |
| 60 | 26 BRONX | 25 | 226 |
| 71 | 95 STATEN ISLAND | 21 | 595 |
| 70 | 84 QUEENS | 18 | 484 |
| 61 | 27 BRONX | 17 | 227 |
| 64 | 56 BROOKLYN | 13 | 356 |
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”.
def recode_borocd_counts(row):
if "MANHATTAN" in row["Community Board"] and row["Community Board"][0] != "U":
return "1" + row["Community Board"][0:2]
elif "BRONX" in row["Community Board"] and row["Community Board"][0] != "U":
return "2" + row["Community Board"][0:2]
elif "BROOKLYN" in row["Community Board"] and row["Community Board"][0] != "U":
return "3" + row["Community Board"][0:2]
elif "QUEENS" in row["Community Board"] and row["Community Board"][0] != "U":
return "4" + row["Community Board"][0:2]
elif "STATEN ISLAND" in row["Community Board"] and row["Community Board"][0] != "U":
return "5" + row["Community Board"][0:2]
else:
return "Invalid BoroCD"
cb_counts["boro_cd"] = cb_counts.apply(recode_borocd_counts, axis=1)
cb_counts
| Community Board | num_311_requests | boro_cd | |
|---|---|---|---|
| 50 | 12 MANHATTAN | 14110 | 112 |
| 23 | 05 QUEENS | 12487 | 405 |
| 51 | 12 QUEENS | 12228 | 412 |
| 2 | 01 BROOKLYN | 11863 | 301 |
| 12 | 03 BROOKLYN | 11615 | 303 |
| 5 | 01 STATEN ISLAND | 11438 | 501 |
| 31 | 07 QUEENS | 11210 | 407 |
| 21 | 05 BROOKLYN | 10862 | 305 |
| 16 | 04 BRONX | 10628 | 204 |
| 4 | 01 QUEENS | 10410 | 401 |
| 58 | 17 BROOKLYN | 10208 | 317 |
| 54 | 14 BROOKLYN | 10179 | 314 |
| 28 | 07 BRONX | 9841 | 207 |
| 59 | 18 BROOKLYN | 9717 | 318 |
| 39 | 09 QUEENS | 9599 | 409 |
| 53 | 13 QUEENS | 9459 | 413 |
| 48 | 12 BRONX | 9412 | 212 |
| 45 | 11 BROOKLYN | 9309 | 311 |
| 20 | 05 BRONX | 9094 | 205 |
| 13 | 03 MANHATTAN | 8905 | 103 |
| 35 | 08 QUEENS | 8661 | 408 |
| 17 | 04 BROOKLYN | 8639 | 304 |
| 42 | 10 MANHATTAN | 8592 | 110 |
| 15 | 03 STATEN ISLAND | 8524 | 503 |
| 56 | 15 BROOKLYN | 8508 | 315 |
| 43 | 10 QUEENS | 8333 | 410 |
| 49 | 12 BROOKLYN | 8214 | 312 |
| 30 | 07 MANHATTAN | 8141 | 107 |
| 36 | 09 BRONX | 8092 | 209 |
| 41 | 10 BROOKLYN | 7808 | 310 |
| 33 | 08 BROOKLYN | 7797 | 308 |
| 7 | 02 BROOKLYN | 7747 | 302 |
| 37 | 09 BROOKLYN | 7571 | 309 |
| 25 | 06 BROOKLYN | 7373 | 306 |
| 10 | 02 STATEN ISLAND | 7059 | 502 |
| 0 | 0 Unspecified | 6882 | Invalid BoroCD |
| 14 | 03 QUEENS | 6799 | 403 |
| 38 | 09 MANHATTAN | 6792 | 109 |
| 73 | Unspecified BROOKLYN | 6771 | Invalid BoroCD |
| 18 | 04 MANHATTAN | 6765 | 104 |
| 22 | 05 MANHATTAN | 6599 | 105 |
| 34 | 08 MANHATTAN | 6579 | 108 |
| 8 | 02 MANHATTAN | 6514 | 102 |
| 44 | 11 BRONX | 6448 | 211 |
| 74 | Unspecified MANHATTAN | 6427 | Invalid BoroCD |
| 29 | 07 BROOKLYN | 6364 | 307 |
| 9 | 02 QUEENS | 6142 | 402 |
| 19 | 04 QUEENS | 5824 | 404 |
| 32 | 08 BRONX | 5753 | 208 |
| 47 | 11 QUEENS | 5748 | 411 |
| 24 | 06 BRONX | 5746 | 206 |
| 40 | 10 BRONX | 5719 | 210 |
| 46 | 11 MANHATTAN | 5714 | 111 |
| 72 | Unspecified BRONX | 5349 | Invalid BoroCD |
| 55 | 14 QUEENS | 5284 | 414 |
| 11 | 03 BRONX | 5262 | 203 |
| 26 | 06 MANHATTAN | 5158 | 106 |
| 57 | 16 BROOKLYN | 5100 | 316 |
| 1 | 01 BRONX | 4915 | 201 |
| 75 | Unspecified QUEENS | 4870 | Invalid BoroCD |
| 27 | 06 QUEENS | 4607 | 406 |
| 52 | 13 BROOKLYN | 3840 | 313 |
| 3 | 01 MANHATTAN | 3623 | 101 |
| 6 | 02 BRONX | 3470 | 202 |
| 76 | Unspecified STATEN ISLAND | 541 | Invalid BoroCD |
| 69 | 83 QUEENS | 193 | 483 |
| 65 | 64 MANHATTAN | 126 | 164 |
| 66 | 80 QUEENS | 102 | 480 |
| 68 | 82 QUEENS | 72 | 482 |
| 67 | 81 QUEENS | 54 | 481 |
| 63 | 55 BROOKLYN | 53 | 355 |
| 62 | 28 BRONX | 26 | 228 |
| 60 | 26 BRONX | 25 | 226 |
| 71 | 95 STATEN ISLAND | 21 | 595 |
| 70 | 84 QUEENS | 18 | 484 |
| 61 | 27 BRONX | 17 | 227 |
| 64 | 56 BROOKLYN | 13 | 356 |
We can make this function easier to read by isolating the logic that applies to all the conditions. This is called “refactoring”.
def recode_borocd_counts(row):
board = row["Community Board"]
# doing a check and then returning from a function early is known as a "guard clause"
if board.startswith("U"):
return "Invalid BoroCD"
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["boro_cd"] = cb_counts.apply(recode_borocd_counts, axis=1)
cb_counts
| Community Board | num_311_requests | boro_cd | |
|---|---|---|---|
| 50 | 12 MANHATTAN | 14110 | 112 |
| 23 | 05 QUEENS | 12487 | 405 |
| 51 | 12 QUEENS | 12228 | 412 |
| 2 | 01 BROOKLYN | 11863 | 301 |
| 12 | 03 BROOKLYN | 11615 | 303 |
| 5 | 01 STATEN ISLAND | 11438 | 501 |
| 31 | 07 QUEENS | 11210 | 407 |
| 21 | 05 BROOKLYN | 10862 | 305 |
| 16 | 04 BRONX | 10628 | 204 |
| 4 | 01 QUEENS | 10410 | 401 |
| 58 | 17 BROOKLYN | 10208 | 317 |
| 54 | 14 BROOKLYN | 10179 | 314 |
| 28 | 07 BRONX | 9841 | 207 |
| 59 | 18 BROOKLYN | 9717 | 318 |
| 39 | 09 QUEENS | 9599 | 409 |
| 53 | 13 QUEENS | 9459 | 413 |
| 48 | 12 BRONX | 9412 | 212 |
| 45 | 11 BROOKLYN | 9309 | 311 |
| 20 | 05 BRONX | 9094 | 205 |
| 13 | 03 MANHATTAN | 8905 | 103 |
| 35 | 08 QUEENS | 8661 | 408 |
| 17 | 04 BROOKLYN | 8639 | 304 |
| 42 | 10 MANHATTAN | 8592 | 110 |
| 15 | 03 STATEN ISLAND | 8524 | 503 |
| 56 | 15 BROOKLYN | 8508 | 315 |
| 43 | 10 QUEENS | 8333 | 410 |
| 49 | 12 BROOKLYN | 8214 | 312 |
| 30 | 07 MANHATTAN | 8141 | 107 |
| 36 | 09 BRONX | 8092 | 209 |
| 41 | 10 BROOKLYN | 7808 | 310 |
| 33 | 08 BROOKLYN | 7797 | 308 |
| 7 | 02 BROOKLYN | 7747 | 302 |
| 37 | 09 BROOKLYN | 7571 | 309 |
| 25 | 06 BROOKLYN | 7373 | 306 |
| 10 | 02 STATEN ISLAND | 7059 | 502 |
| 0 | 0 Unspecified | 6882 | Invalid BoroCD |
| 14 | 03 QUEENS | 6799 | 403 |
| 38 | 09 MANHATTAN | 6792 | 109 |
| 73 | Unspecified BROOKLYN | 6771 | Invalid BoroCD |
| 18 | 04 MANHATTAN | 6765 | 104 |
| 22 | 05 MANHATTAN | 6599 | 105 |
| 34 | 08 MANHATTAN | 6579 | 108 |
| 8 | 02 MANHATTAN | 6514 | 102 |
| 44 | 11 BRONX | 6448 | 211 |
| 74 | Unspecified MANHATTAN | 6427 | Invalid BoroCD |
| 29 | 07 BROOKLYN | 6364 | 307 |
| 9 | 02 QUEENS | 6142 | 402 |
| 19 | 04 QUEENS | 5824 | 404 |
| 32 | 08 BRONX | 5753 | 208 |
| 47 | 11 QUEENS | 5748 | 411 |
| 24 | 06 BRONX | 5746 | 206 |
| 40 | 10 BRONX | 5719 | 210 |
| 46 | 11 MANHATTAN | 5714 | 111 |
| 72 | Unspecified BRONX | 5349 | Invalid BoroCD |
| 55 | 14 QUEENS | 5284 | 414 |
| 11 | 03 BRONX | 5262 | 203 |
| 26 | 06 MANHATTAN | 5158 | 106 |
| 57 | 16 BROOKLYN | 5100 | 316 |
| 1 | 01 BRONX | 4915 | 201 |
| 75 | Unspecified QUEENS | 4870 | Invalid BoroCD |
| 27 | 06 QUEENS | 4607 | 406 |
| 52 | 13 BROOKLYN | 3840 | 313 |
| 3 | 01 MANHATTAN | 3623 | 101 |
| 6 | 02 BRONX | 3470 | 202 |
| 76 | Unspecified STATEN ISLAND | 541 | Invalid BoroCD |
| 69 | 83 QUEENS | 193 | 483 |
| 65 | 64 MANHATTAN | 126 | 164 |
| 66 | 80 QUEENS | 102 | 480 |
| 68 | 82 QUEENS | 72 | 482 |
| 67 | 81 QUEENS | 54 | 481 |
| 63 | 55 BROOKLYN | 53 | 355 |
| 62 | 28 BRONX | 26 | 228 |
| 60 | 26 BRONX | 25 | 226 |
| 71 | 95 STATEN ISLAND | 21 | 595 |
| 70 | 84 QUEENS | 18 | 484 |
| 61 | 27 BRONX | 17 | 227 |
| 64 | 56 BROOKLYN | 13 | 356 |
Next, let’s create the borocd column in the population dataset#
population.head()
| Borough | CD Number | CD Name | 1970 Population | 1980 Population | 1990 Population | 2000 Population | 2010 Population | |
|---|---|---|---|---|---|---|---|---|
| 0 | Bronx | 1 | Melrose, Mott Haven, Port Morris | 138557 | 78441 | 77214 | 82159 | 91497 |
| 1 | Bronx | 2 | Hunts Point, Longwood | 99493 | 34399 | 39443 | 46824 | 52246 |
| 2 | Bronx | 3 | Morrisania, Crotona Park East | 150636 | 53635 | 57162 | 68574 | 79762 |
| 3 | Bronx | 4 | Highbridge, Concourse Village | 144207 | 114312 | 119962 | 139563 | 146441 |
| 4 | Bronx | 5 | University Hts., Fordham, Mt. Hope | 121807 | 107995 | 118435 | 128313 | 128200 |
population.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 59 entries, 0 to 58
Data columns (total 8 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Borough 59 non-null object
1 CD Number 59 non-null int64
2 CD Name 59 non-null object
3 1970 Population 59 non-null int64
4 1980 Population 59 non-null int64
5 1990 Population 59 non-null int64
6 2000 Population 59 non-null int64
7 2010 Population 59 non-null int64
dtypes: int64(6), object(2)
memory usage: 3.8+ KB
Create a function recode_borocd_pop that combines and recodes the Borough and CD Number values to create a BoroCD unique ID.
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"
This is different than recode_borocd_counts() because:
The
BoroughandCD Numberare seprate columns in thepopulationDataFrame, rather than combined in one like the 311 dataWe are working with the
CD Numberas an integer rather than a string
population["borocd"] = population.apply(recode_borocd_pop, axis=1)
population
| Borough | CD Number | CD Name | 1970 Population | 1980 Population | 1990 Population | 2000 Population | 2010 Population | borocd | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | Bronx | 1 | Melrose, Mott Haven, Port Morris | 138557 | 78441 | 77214 | 82159 | 91497 | 201 |
| 1 | Bronx | 2 | Hunts Point, Longwood | 99493 | 34399 | 39443 | 46824 | 52246 | 202 |
| 2 | Bronx | 3 | Morrisania, Crotona Park East | 150636 | 53635 | 57162 | 68574 | 79762 | 203 |
| 3 | Bronx | 4 | Highbridge, Concourse Village | 144207 | 114312 | 119962 | 139563 | 146441 | 204 |
| 4 | Bronx | 5 | University Hts., Fordham, Mt. Hope | 121807 | 107995 | 118435 | 128313 | 128200 | 205 |
| 5 | Bronx | 6 | East Tremont, Belmont | 114137 | 65016 | 68061 | 75688 | 83268 | 206 |
| 6 | Bronx | 7 | Bedford Park, Norwood, Fordham | 113764 | 116827 | 128588 | 141411 | 139286 | 207 |
| 7 | Bronx | 8 | Riverdale, Kingsbridge, Marble Hill | 103543 | 98275 | 97030 | 101332 | 101731 | 208 |
| 8 | Bronx | 9 | Soundview, Parkchester | 166442 | 167627 | 155970 | 167859 | 172298 | 209 |
| 9 | Bronx | 10 | Throgs Nk., Co-op City, Pelham Bay | 84948 | 106516 | 108093 | 115948 | 120392 | 210 |
| 10 | Bronx | 11 | Pelham Pkwy, Morris Park, Laconia | 105980 | 99080 | 97842 | 110706 | 113232 | 211 |
| 11 | Bronx | 12 | Wakefield, Williamsbridge | 135010 | 128226 | 129620 | 149077 | 152344 | 212 |
| 12 | Brooklyn | 1 | Williamsburg, Greenpoint | 179390 | 142942 | 155972 | 160338 | 173083 | 301 |
| 13 | Brooklyn | 2 | Brooklyn Heights, Fort Greene | 110221 | 92732 | 94534 | 98620 | 99617 | 302 |
| 14 | Brooklyn | 3 | Bedford Stuyvesant | 203380 | 133379 | 138696 | 143867 | 152985 | 303 |
| 15 | Brooklyn | 4 | Bushwick | 137902 | 92497 | 102572 | 104358 | 112634 | 304 |
| 16 | Brooklyn | 5 | East New York, Starrett City | 170791 | 154931 | 161350 | 173198 | 182896 | 305 |
| 17 | Brooklyn | 6 | Park Slope, Carroll Gardens | 138933 | 110228 | 102724 | 104054 | 104709 | 306 |
| 18 | Brooklyn | 7 | Sunset Park, Windsor Terrace | 111607 | 98567 | 102553 | 120063 | 126230 | 307 |
| 19 | Brooklyn | 8 | Crown Heights North | 121821 | 88796 | 96400 | 96076 | 96317 | 308 |
| 20 | Brooklyn | 9 | Crown Heights South, Wingate | 101047 | 96669 | 110715 | 104014 | 98429 | 309 |
| 21 | Brooklyn | 10 | Bay Ridge, Dyker Heights | 129822 | 118187 | 110612 | 122542 | 124491 | 310 |
| 22 | Brooklyn | 11 | Bensonhurst, Bath Beach | 170119 | 155072 | 149994 | 172129 | 181981 | 311 |
| 23 | Brooklyn | 12 | Borough Park, Ocean Parkway | 166301 | 155899 | 160018 | 185046 | 191382 | 312 |
| 24 | Brooklyn | 13 | Coney Island, Brighton Beach | 97750 | 100030 | 102596 | 106120 | 104278 | 313 |
| 25 | Brooklyn | 14 | Flatbush, Midwood | 137041 | 143859 | 159825 | 168806 | 160664 | 314 |
| 26 | Brooklyn | 15 | Sheepshead Bay, Gerritsen Beach | 164815 | 149572 | 143477 | 160319 | 159650 | 315 |
| 27 | Brooklyn | 16 | Brownsville, Ocean Hill | 122589 | 73801 | 84923 | 85343 | 86468 | 316 |
| 28 | Brooklyn | 17 | East Flatbush, Rugby, Farragut | 149496 | 154596 | 161261 | 165753 | 155252 | 317 |
| 29 | Brooklyn | 18 | Canarsie, Flatlands | 188643 | 169092 | 162428 | 194653 | 193543 | 318 |
| 30 | Manhattan | 1 | Battery Park City, Tribeca | 7706 | 15918 | 25366 | 34420 | 60978 | 101 |
| 31 | Manhattan | 2 | Greenwich Village, Soho | 84337 | 87069 | 94105 | 93119 | 90016 | 102 |
| 32 | Manhattan | 3 | Lower East Side, Chinatown | 181845 | 154848 | 161617 | 164407 | 163277 | 103 |
| 33 | Manhattan | 4 | Chelsea, Clinton | 83601 | 82164 | 84431 | 87479 | 103245 | 104 |
| 34 | Manhattan | 5 | Midtown Business District | 31076 | 39544 | 43507 | 44028 | 51673 | 105 |
| 35 | Manhattan | 6 | Stuyvesant Town, Turtle Bay | 122465 | 127554 | 133748 | 136152 | 142745 | 106 |
| 36 | Manhattan | 7 | West Side, Upper West Side | 212422 | 206669 | 210993 | 207699 | 209084 | 107 |
| 37 | Manhattan | 8 | Upper East Side | 200851 | 204305 | 210880 | 217063 | 219920 | 108 |
| 38 | Manhattan | 9 | Manhattanville, Hamilton Heights | 113606 | 103038 | 106978 | 111724 | 110193 | 109 |
| 39 | Manhattan | 10 | Central Harlem | 159267 | 105641 | 99519 | 107109 | 115723 | 110 |
| 40 | Manhattan | 11 | East Harlem | 154662 | 114569 | 110508 | 117743 | 120511 | 111 |
| 41 | Manhattan | 12 | Washington Heights, Inwood | 180561 | 179941 | 198192 | 208414 | 190020 | 112 |
| 42 | Queens | 1 | Astoria, Long Island City | 185925 | 185198 | 188549 | 211220 | 191105 | 401 |
| 43 | Queens | 2 | Sunnyside, Woodside | 95073 | 88927 | 94845 | 109920 | 113200 | 402 |
| 44 | Queens | 3 | Jackson Heights, North Corona | 123635 | 122090 | 128924 | 169083 | 171576 | 403 |
| 45 | Queens | 4 | Elmhurst, South Corona | 108233 | 118430 | 137023 | 167005 | 172598 | 404 |
| 46 | Queens | 5 | Ridgewood, Glendale, Maspeth | 161022 | 150142 | 149126 | 165911 | 169190 | 405 |
| 47 | Queens | 6 | Forest Hills, Rego Park | 120429 | 112245 | 106996 | 115967 | 113257 | 406 |
| 48 | Queens | 7 | Flushing, Bay Terrace | 207589 | 204785 | 220508 | 242952 | 247354 | 407 |
| 49 | Queens | 8 | Fresh Meadows, Briarwood | 142468 | 125312 | 132101 | 146594 | 151107 | 408 |
| 50 | Queens | 9 | Woodhaven, Richmond Hill | 110367 | 109505 | 112151 | 141608 | 143317 | 409 |
| 51 | Queens | 10 | Ozone Park, Howard Beach | 113857 | 105651 | 107768 | 127274 | 122396 | 410 |
| 52 | Queens | 11 | Bayside, Douglaston, Little Neck | 127883 | 110963 | 108056 | 116404 | 116431 | 411 |
| 53 | Queens | 12 | Jamaica, St. Albans, Hollis | 206639 | 189383 | 201293 | 223602 | 225919 | 412 |
| 54 | Queens | 13 | Queens Village, Rosedale | 184647 | 173178 | 177535 | 196284 | 188593 | 413 |
| 55 | Queens | 14 | The Rockaways, Broad Channel | 98228 | 100592 | 100596 | 106686 | 114978 | 414 |
| 56 | Staten Island | 1 | Stapleton, Port Richmond | 135875 | 138489 | 137806 | 162609 | 175756 | 501 |
| 57 | Staten Island | 2 | New Springville, South Beach | 85985 | 105128 | 113944 | 127071 | 132003 | 502 |
| 58 | Staten Island | 3 | Tottenville, Woodrow, Great Kills | 72815 | 108249 | 126956 | 152908 | 160209 | 503 |
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.
merged_data = pd.merge(left=cb_counts, right=population, left_on="boro_cd", right_on="borocd")
merged_data
| Community Board | num_311_requests | boro_cd | Borough | CD Number | CD Name | 1970 Population | 1980 Population | 1990 Population | 2000 Population | 2010 Population | borocd | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 12 MANHATTAN | 14110 | 112 | Manhattan | 12 | Washington Heights, Inwood | 180561 | 179941 | 198192 | 208414 | 190020 | 112 |
| 1 | 05 QUEENS | 12487 | 405 | Queens | 5 | Ridgewood, Glendale, Maspeth | 161022 | 150142 | 149126 | 165911 | 169190 | 405 |
| 2 | 12 QUEENS | 12228 | 412 | Queens | 12 | Jamaica, St. Albans, Hollis | 206639 | 189383 | 201293 | 223602 | 225919 | 412 |
| 3 | 01 BROOKLYN | 11863 | 301 | Brooklyn | 1 | Williamsburg, Greenpoint | 179390 | 142942 | 155972 | 160338 | 173083 | 301 |
| 4 | 03 BROOKLYN | 11615 | 303 | Brooklyn | 3 | Bedford Stuyvesant | 203380 | 133379 | 138696 | 143867 | 152985 | 303 |
| 5 | 01 STATEN ISLAND | 11438 | 501 | Staten Island | 1 | Stapleton, Port Richmond | 135875 | 138489 | 137806 | 162609 | 175756 | 501 |
| 6 | 07 QUEENS | 11210 | 407 | Queens | 7 | Flushing, Bay Terrace | 207589 | 204785 | 220508 | 242952 | 247354 | 407 |
| 7 | 05 BROOKLYN | 10862 | 305 | Brooklyn | 5 | East New York, Starrett City | 170791 | 154931 | 161350 | 173198 | 182896 | 305 |
| 8 | 04 BRONX | 10628 | 204 | Bronx | 4 | Highbridge, Concourse Village | 144207 | 114312 | 119962 | 139563 | 146441 | 204 |
| 9 | 01 QUEENS | 10410 | 401 | Queens | 1 | Astoria, Long Island City | 185925 | 185198 | 188549 | 211220 | 191105 | 401 |
| 10 | 17 BROOKLYN | 10208 | 317 | Brooklyn | 17 | East Flatbush, Rugby, Farragut | 149496 | 154596 | 161261 | 165753 | 155252 | 317 |
| 11 | 14 BROOKLYN | 10179 | 314 | Brooklyn | 14 | Flatbush, Midwood | 137041 | 143859 | 159825 | 168806 | 160664 | 314 |
| 12 | 07 BRONX | 9841 | 207 | Bronx | 7 | Bedford Park, Norwood, Fordham | 113764 | 116827 | 128588 | 141411 | 139286 | 207 |
| 13 | 18 BROOKLYN | 9717 | 318 | Brooklyn | 18 | Canarsie, Flatlands | 188643 | 169092 | 162428 | 194653 | 193543 | 318 |
| 14 | 09 QUEENS | 9599 | 409 | Queens | 9 | Woodhaven, Richmond Hill | 110367 | 109505 | 112151 | 141608 | 143317 | 409 |
| 15 | 13 QUEENS | 9459 | 413 | Queens | 13 | Queens Village, Rosedale | 184647 | 173178 | 177535 | 196284 | 188593 | 413 |
| 16 | 12 BRONX | 9412 | 212 | Bronx | 12 | Wakefield, Williamsbridge | 135010 | 128226 | 129620 | 149077 | 152344 | 212 |
| 17 | 11 BROOKLYN | 9309 | 311 | Brooklyn | 11 | Bensonhurst, Bath Beach | 170119 | 155072 | 149994 | 172129 | 181981 | 311 |
| 18 | 05 BRONX | 9094 | 205 | Bronx | 5 | University Hts., Fordham, Mt. Hope | 121807 | 107995 | 118435 | 128313 | 128200 | 205 |
| 19 | 03 MANHATTAN | 8905 | 103 | Manhattan | 3 | Lower East Side, Chinatown | 181845 | 154848 | 161617 | 164407 | 163277 | 103 |
| 20 | 08 QUEENS | 8661 | 408 | Queens | 8 | Fresh Meadows, Briarwood | 142468 | 125312 | 132101 | 146594 | 151107 | 408 |
| 21 | 04 BROOKLYN | 8639 | 304 | Brooklyn | 4 | Bushwick | 137902 | 92497 | 102572 | 104358 | 112634 | 304 |
| 22 | 10 MANHATTAN | 8592 | 110 | Manhattan | 10 | Central Harlem | 159267 | 105641 | 99519 | 107109 | 115723 | 110 |
| 23 | 03 STATEN ISLAND | 8524 | 503 | Staten Island | 3 | Tottenville, Woodrow, Great Kills | 72815 | 108249 | 126956 | 152908 | 160209 | 503 |
| 24 | 15 BROOKLYN | 8508 | 315 | Brooklyn | 15 | Sheepshead Bay, Gerritsen Beach | 164815 | 149572 | 143477 | 160319 | 159650 | 315 |
| 25 | 10 QUEENS | 8333 | 410 | Queens | 10 | Ozone Park, Howard Beach | 113857 | 105651 | 107768 | 127274 | 122396 | 410 |
| 26 | 12 BROOKLYN | 8214 | 312 | Brooklyn | 12 | Borough Park, Ocean Parkway | 166301 | 155899 | 160018 | 185046 | 191382 | 312 |
| 27 | 07 MANHATTAN | 8141 | 107 | Manhattan | 7 | West Side, Upper West Side | 212422 | 206669 | 210993 | 207699 | 209084 | 107 |
| 28 | 09 BRONX | 8092 | 209 | Bronx | 9 | Soundview, Parkchester | 166442 | 167627 | 155970 | 167859 | 172298 | 209 |
| 29 | 10 BROOKLYN | 7808 | 310 | Brooklyn | 10 | Bay Ridge, Dyker Heights | 129822 | 118187 | 110612 | 122542 | 124491 | 310 |
| 30 | 08 BROOKLYN | 7797 | 308 | Brooklyn | 8 | Crown Heights North | 121821 | 88796 | 96400 | 96076 | 96317 | 308 |
| 31 | 02 BROOKLYN | 7747 | 302 | Brooklyn | 2 | Brooklyn Heights, Fort Greene | 110221 | 92732 | 94534 | 98620 | 99617 | 302 |
| 32 | 09 BROOKLYN | 7571 | 309 | Brooklyn | 9 | Crown Heights South, Wingate | 101047 | 96669 | 110715 | 104014 | 98429 | 309 |
| 33 | 06 BROOKLYN | 7373 | 306 | Brooklyn | 6 | Park Slope, Carroll Gardens | 138933 | 110228 | 102724 | 104054 | 104709 | 306 |
| 34 | 02 STATEN ISLAND | 7059 | 502 | Staten Island | 2 | New Springville, South Beach | 85985 | 105128 | 113944 | 127071 | 132003 | 502 |
| 35 | 03 QUEENS | 6799 | 403 | Queens | 3 | Jackson Heights, North Corona | 123635 | 122090 | 128924 | 169083 | 171576 | 403 |
| 36 | 09 MANHATTAN | 6792 | 109 | Manhattan | 9 | Manhattanville, Hamilton Heights | 113606 | 103038 | 106978 | 111724 | 110193 | 109 |
| 37 | 04 MANHATTAN | 6765 | 104 | Manhattan | 4 | Chelsea, Clinton | 83601 | 82164 | 84431 | 87479 | 103245 | 104 |
| 38 | 05 MANHATTAN | 6599 | 105 | Manhattan | 5 | Midtown Business District | 31076 | 39544 | 43507 | 44028 | 51673 | 105 |
| 39 | 08 MANHATTAN | 6579 | 108 | Manhattan | 8 | Upper East Side | 200851 | 204305 | 210880 | 217063 | 219920 | 108 |
| 40 | 02 MANHATTAN | 6514 | 102 | Manhattan | 2 | Greenwich Village, Soho | 84337 | 87069 | 94105 | 93119 | 90016 | 102 |
| 41 | 11 BRONX | 6448 | 211 | Bronx | 11 | Pelham Pkwy, Morris Park, Laconia | 105980 | 99080 | 97842 | 110706 | 113232 | 211 |
| 42 | 07 BROOKLYN | 6364 | 307 | Brooklyn | 7 | Sunset Park, Windsor Terrace | 111607 | 98567 | 102553 | 120063 | 126230 | 307 |
| 43 | 02 QUEENS | 6142 | 402 | Queens | 2 | Sunnyside, Woodside | 95073 | 88927 | 94845 | 109920 | 113200 | 402 |
| 44 | 04 QUEENS | 5824 | 404 | Queens | 4 | Elmhurst, South Corona | 108233 | 118430 | 137023 | 167005 | 172598 | 404 |
| 45 | 08 BRONX | 5753 | 208 | Bronx | 8 | Riverdale, Kingsbridge, Marble Hill | 103543 | 98275 | 97030 | 101332 | 101731 | 208 |
| 46 | 11 QUEENS | 5748 | 411 | Queens | 11 | Bayside, Douglaston, Little Neck | 127883 | 110963 | 108056 | 116404 | 116431 | 411 |
| 47 | 06 BRONX | 5746 | 206 | Bronx | 6 | East Tremont, Belmont | 114137 | 65016 | 68061 | 75688 | 83268 | 206 |
| 48 | 10 BRONX | 5719 | 210 | Bronx | 10 | Throgs Nk., Co-op City, Pelham Bay | 84948 | 106516 | 108093 | 115948 | 120392 | 210 |
| 49 | 11 MANHATTAN | 5714 | 111 | Manhattan | 11 | East Harlem | 154662 | 114569 | 110508 | 117743 | 120511 | 111 |
| 50 | 14 QUEENS | 5284 | 414 | Queens | 14 | The Rockaways, Broad Channel | 98228 | 100592 | 100596 | 106686 | 114978 | 414 |
| 51 | 03 BRONX | 5262 | 203 | Bronx | 3 | Morrisania, Crotona Park East | 150636 | 53635 | 57162 | 68574 | 79762 | 203 |
| 52 | 06 MANHATTAN | 5158 | 106 | Manhattan | 6 | Stuyvesant Town, Turtle Bay | 122465 | 127554 | 133748 | 136152 | 142745 | 106 |
| 53 | 16 BROOKLYN | 5100 | 316 | Brooklyn | 16 | Brownsville, Ocean Hill | 122589 | 73801 | 84923 | 85343 | 86468 | 316 |
| 54 | 01 BRONX | 4915 | 201 | Bronx | 1 | Melrose, Mott Haven, Port Morris | 138557 | 78441 | 77214 | 82159 | 91497 | 201 |
| 55 | 06 QUEENS | 4607 | 406 | Queens | 6 | Forest Hills, Rego Park | 120429 | 112245 | 106996 | 115967 | 113257 | 406 |
| 56 | 13 BROOKLYN | 3840 | 313 | Brooklyn | 13 | Coney Island, Brighton Beach | 97750 | 100030 | 102596 | 106120 | 104278 | 313 |
| 57 | 01 MANHATTAN | 3623 | 101 | Manhattan | 1 | Battery Park City, Tribeca | 7706 | 15918 | 25366 | 34420 | 60978 | 101 |
| 58 | 02 BRONX | 3470 | 202 | Bronx | 2 | Hunts Point, Longwood | 99493 | 34399 | 39443 | 46824 | 52246 | 202 |
# remove the redundant column
merged_data = merged_data.drop("borocd", axis="columns")
# save the data to a file
# merged_data.to_csv("data/community_district_311.csv", index=False)