In [1]:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
plt.style.use('ggplot')
import dateutil.parser

First, I made a mistake naming the data set! It's 2015 data, not 2014 data. But yes, still use 311-2014.csv. You can rename it.

Importing and preparing your data

1.Import your data, but only the first 200,000 rows. You'll also want to change the index to be a datetime based on the Created Date column - you'll want to check if it's already a datetime, and parse it if not.


In [2]:
#200,000 rows giving errors, so imported only 200,00 rows :-) to solve the loading issues and memory error.
df = pd.read_csv("small-311-2015.csv")
df.head(5)


c:\users\radhika\appdata\local\programs\python\python35-32\lib\site-packages\IPython\core\interactiveshell.py:2723: DtypeWarning: Columns (8) have mixed types. Specify dtype option on import or set low_memory=False.
  interactivity=interactivity, compiler=compiler, result=result)
Out[2]:
Unique Key Created Date Closed Date Agency Agency Name Complaint Type Descriptor Location Type Incident Zip Incident Address ... Bridge Highway Name Bridge Highway Direction Road Ramp Bridge Highway Segment Garage Lot Name Ferry Direction Ferry Terminal Name Latitude Longitude Location
0 31015465 07/06/2015 10:58:27 AM 07/22/2015 01:07:20 AM DCA Department of Consumer Affairs Consumer Complaint Demand for Cash NaN 11360 27-16 203 STREET ... NaN NaN NaN NaN NaN NaN NaN 40.773540 -73.788237 (40.773539552542, -73.78823697228408)
1 30997660 07/03/2015 01:26:29 PM 07/03/2015 02:08:20 PM NYPD New York City Police Department Vending In Prohibited Area Residential Building/House 10019 200 CENTRAL PARK SOUTH ... NaN NaN NaN NaN NaN NaN NaN 40.767021 -73.979448 (40.76702142171206, -73.97944780718524)
2 31950223 11/09/2015 03:55:09 AM 11/09/2015 08:08:57 AM NYPD New York City Police Department Blocked Driveway No Access Street/Sidewalk 10453 1993 GRAND AVENUE ... NaN NaN NaN NaN NaN NaN NaN 40.852671 -73.910608 (40.85267061877697, -73.91060771362552)
3 31000038 07/03/2015 02:18:32 AM 07/03/2015 07:54:48 AM NYPD New York City Police Department Noise - Commercial Loud Music/Party Club/Bar/Restaurant 11372 84-16 NORTHERN BOULEVARD ... NaN NaN NaN NaN NaN NaN NaN 40.755774 -73.883262 (40.755773786469966, -73.88326243225418)
4 30995614 07/04/2015 12:03:27 AM 07/04/2015 03:33:09 AM NYPD New York City Police Department Noise - Street/Sidewalk Loud Talking Street/Sidewalk 11216 1057 BERGEN STREET ... NaN NaN NaN NaN NaN NaN NaN 40.676175 -73.951269 (40.67617516102934, -73.9512690004692)

5 rows × 53 columns


In [3]:
df.columns.values


Out[3]:
array(['Unique Key', 'Created Date', 'Closed Date', 'Agency',
       'Agency Name', 'Complaint Type', 'Descriptor', 'Location Type',
       'Incident Zip', 'Incident Address', 'Street Name', 'Cross Street 1',
       'Cross Street 2', 'Intersection Street 1', 'Intersection Street 2',
       'Address Type', 'City', 'Landmark', 'Facility Type', 'Status',
       'Due Date', 'Resolution Description',
       'Resolution Action Updated Date', 'Community Board', 'Borough',
       'X Coordinate (State Plane)', 'Y Coordinate (State Plane)',
       'Park Facility Name', 'Park Borough', 'School Name',
       'School Number', 'School Region', 'School Code',
       'School Phone Number', 'School Address', 'School City',
       'School State', 'School Zip', 'School Not Found',
       'School or Citywide Complaint', 'Vehicle Type',
       'Taxi Company Borough', 'Taxi Pick Up Location',
       'Bridge Highway Name', 'Bridge Highway Direction', 'Road Ramp',
       'Bridge Highway Segment', 'Garage Lot Name', 'Ferry Direction',
       'Ferry Terminal Name', 'Latitude', 'Longitude', 'Location'], dtype=object)

In [4]:
dateutil.parser.parse("07/04/2015 03:33:09 AM")


Out[4]:
datetime.datetime(2015, 7, 4, 3, 33, 9)

In [5]:
df.info()


<class 'pandas.core.frame.DataFrame'>
RangeIndex: 19999 entries, 0 to 19998
Data columns (total 53 columns):
Unique Key                        19999 non-null int64
Created Date                      19999 non-null object
Closed Date                       18787 non-null object
Agency                            19999 non-null object
Agency Name                       19999 non-null object
Complaint Type                    19999 non-null object
Descriptor                        19744 non-null object
Location Type                     18371 non-null object
Incident Zip                      18779 non-null object
Incident Address                  15073 non-null object
Street Name                       15069 non-null object
Cross Street 1                    13224 non-null object
Cross Street 2                    13179 non-null object
Intersection Street 1             2992 non-null object
Intersection Street 2             2965 non-null object
Address Type                      18095 non-null object
City                              18788 non-null object
Landmark                          22 non-null object
Facility Type                     9699 non-null object
Status                            19999 non-null object
Due Date                          17403 non-null object
Resolution Description            19837 non-null object
Resolution Action Updated Date    18630 non-null object
Community Board                   19999 non-null object
Borough                           19999 non-null object
X Coordinate (State Plane)        18093 non-null float64
Y Coordinate (State Plane)        18093 non-null float64
Park Facility Name                19999 non-null object
Park Borough                      19999 non-null object
School Name                       19999 non-null object
School Number                     19990 non-null object
School Region                     19596 non-null object
School Code                       19596 non-null object
School Phone Number               19999 non-null object
School Address                    19999 non-null object
School City                       19999 non-null object
School State                      19999 non-null object
School Zip                        19999 non-null object
School Not Found                  17986 non-null object
School or Citywide Complaint      0 non-null float64
Vehicle Type                      6 non-null object
Taxi Company Borough              65 non-null object
Taxi Pick Up Location             494 non-null object
Bridge Highway Name               395 non-null object
Bridge Highway Direction          395 non-null object
Road Ramp                         394 non-null object
Bridge Highway Segment            395 non-null object
Garage Lot Name                   18 non-null object
Ferry Direction                   6 non-null object
Ferry Terminal Name               17 non-null object
Latitude                          18093 non-null float64
Longitude                         18093 non-null float64
Location                          18093 non-null object
dtypes: float64(5), int64(1), object(47)
memory usage: 4.5+ MB

In [6]:
def parse_date(str_date):
    return dateutil.parser.parse(str_date)

df['created_datetime'] = df['Created Date'].apply(parse_date)
df.head(2)


Out[6]:
Unique Key Created Date Closed Date Agency Agency Name Complaint Type Descriptor Location Type Incident Zip Incident Address ... Bridge Highway Direction Road Ramp Bridge Highway Segment Garage Lot Name Ferry Direction Ferry Terminal Name Latitude Longitude Location created_datetime
0 31015465 07/06/2015 10:58:27 AM 07/22/2015 01:07:20 AM DCA Department of Consumer Affairs Consumer Complaint Demand for Cash NaN 11360 27-16 203 STREET ... NaN NaN NaN NaN NaN NaN 40.773540 -73.788237 (40.773539552542, -73.78823697228408) 2015-07-06 10:58:27
1 30997660 07/03/2015 01:26:29 PM 07/03/2015 02:08:20 PM NYPD New York City Police Department Vending In Prohibited Area Residential Building/House 10019 200 CENTRAL PARK SOUTH ... NaN NaN NaN NaN NaN NaN 40.767021 -73.979448 (40.76702142171206, -73.97944780718524) 2015-07-03 13:26:29

2 rows × 54 columns


In [7]:
df.index = df['created_datetime']

In [8]:
df.head(5)


Out[8]:
Unique Key Created Date Closed Date Agency Agency Name Complaint Type Descriptor Location Type Incident Zip Incident Address ... Bridge Highway Direction Road Ramp Bridge Highway Segment Garage Lot Name Ferry Direction Ferry Terminal Name Latitude Longitude Location created_datetime
created_datetime
2015-07-06 10:58:27 31015465 07/06/2015 10:58:27 AM 07/22/2015 01:07:20 AM DCA Department of Consumer Affairs Consumer Complaint Demand for Cash NaN 11360 27-16 203 STREET ... NaN NaN NaN NaN NaN NaN 40.773540 -73.788237 (40.773539552542, -73.78823697228408) 2015-07-06 10:58:27
2015-07-03 13:26:29 30997660 07/03/2015 01:26:29 PM 07/03/2015 02:08:20 PM NYPD New York City Police Department Vending In Prohibited Area Residential Building/House 10019 200 CENTRAL PARK SOUTH ... NaN NaN NaN NaN NaN NaN 40.767021 -73.979448 (40.76702142171206, -73.97944780718524) 2015-07-03 13:26:29
2015-11-09 03:55:09 31950223 11/09/2015 03:55:09 AM 11/09/2015 08:08:57 AM NYPD New York City Police Department Blocked Driveway No Access Street/Sidewalk 10453 1993 GRAND AVENUE ... NaN NaN NaN NaN NaN NaN 40.852671 -73.910608 (40.85267061877697, -73.91060771362552) 2015-11-09 03:55:09
2015-07-03 02:18:32 31000038 07/03/2015 02:18:32 AM 07/03/2015 07:54:48 AM NYPD New York City Police Department Noise - Commercial Loud Music/Party Club/Bar/Restaurant 11372 84-16 NORTHERN BOULEVARD ... NaN NaN NaN NaN NaN NaN 40.755774 -73.883262 (40.755773786469966, -73.88326243225418) 2015-07-03 02:18:32
2015-07-04 00:03:27 30995614 07/04/2015 12:03:27 AM 07/04/2015 03:33:09 AM NYPD New York City Police Department Noise - Street/Sidewalk Loud Talking Street/Sidewalk 11216 1057 BERGEN STREET ... NaN NaN NaN NaN NaN NaN 40.676175 -73.951269 (40.67617516102934, -73.9512690004692) 2015-07-04 00:03:27

5 rows × 54 columns

2.What was the most popular type of complaint, and how many times was it filed?


In [9]:
df['Complaint Type'].value_counts().head(5)


Out[9]:
Blocked Driveway           2534
Illegal Parking            2410
Noise - Street/Sidewalk    1584
Street Condition           1216
Noise - Commercial         1162
Name: Complaint Type, dtype: int64

3.Make a horizontal bar graph of the top 5 most frequent complaint types.


In [10]:
df['Complaint Type'].value_counts().head(5).plot(kind='barh', y='Complaint Type')


Out[10]:
<matplotlib.axes._subplots.AxesSubplot at 0x80527d0>

4.Which borough has the most complaints per capita? Since it's only 5 boroughs, you can do the math manually.


In [11]:
columns_to_show = ['Park Borough', 'Complaint Type']
complaints = df[columns_to_show]

complaints.head()


Out[11]:
Park Borough Complaint Type
created_datetime
2015-07-06 10:58:27 QUEENS Consumer Complaint
2015-07-03 13:26:29 MANHATTAN Vending
2015-11-09 03:55:09 BRONX Blocked Driveway
2015-07-03 02:18:32 QUEENS Noise - Commercial
2015-07-04 00:03:27 BROOKLYN Noise - Street/Sidewalk

In [12]:
new_list = pd.DataFrame(complaints['Park Borough'].value_counts())
new_list.head(6)


Out[12]:
Park Borough
BROOKLYN 5761
QUEENS 5500
MANHATTAN 4491
BRONX 2446
Unspecified 987
STATEN ISLAND 814

In [13]:
new_comp = complaints.merge(new_list, left_on='Park Borough', right_index=True)
new_comp.head(3)


Out[13]:
Park Borough Park Borough_x Complaint Type Park Borough_y
created_datetime
2015-07-06 10:58:27 QUEENS QUEENS Consumer Complaint 5500
2015-07-03 02:18:32 QUEENS QUEENS Noise - Commercial 5500
2015-07-09 00:00:00 QUEENS QUEENS Standing Water 5500

In [14]:
new_comp['count'] = new_comp['Park Borough_y']

In [15]:
new_comp.head()


Out[15]:
Park Borough Park Borough_x Complaint Type Park Borough_y count
created_datetime
2015-07-06 10:58:27 QUEENS QUEENS Consumer Complaint 5500 5500
2015-07-03 02:18:32 QUEENS QUEENS Noise - Commercial 5500 5500
2015-07-09 00:00:00 QUEENS QUEENS Standing Water 5500 5500
2015-08-12 11:09:49 QUEENS QUEENS Consumer Complaint 5500 5500
2015-09-09 12:12:46 QUEENS QUEENS Overgrown Tree/Branches 5500 5500

In [16]:
new_complaints = new_comp.drop(['Park Borough_x','Park Borough_y'], axis=1)

In [17]:
new_complaints.head()


Out[17]:
Park Borough Complaint Type count
created_datetime
2015-07-06 10:58:27 QUEENS Consumer Complaint 5500
2015-07-03 02:18:32 QUEENS Noise - Commercial 5500
2015-07-09 00:00:00 QUEENS Standing Water 5500
2015-08-12 11:09:49 QUEENS Consumer Complaint 5500
2015-09-09 12:12:46 QUEENS Overgrown Tree/Branches 5500

In [18]:
new_list.head(6)


Out[18]:
Park Borough
BROOKLYN 5761
QUEENS 5500
MANHATTAN 4491
BRONX 2446
Unspecified 987
STATEN ISLAND 814

In [19]:
#mh 1,636,268, Bronx	1,438,159 brooklyn	2,621,793, queens -2,321,580 stalen 
per_capita = {'BROOKLYN':2621793,'QUEENS': 2321580,'MANHATTAN':1636268,'BRONX':1438159, 'STATEN ISLAND':472621, 'Unspecified':0}

my_list = pd.DataFrame.from_dict(per_capita,orient='index')
my_list.head()


Out[19]:
0
QUEENS 2321580
STATEN ISLAND 472621
BROOKLYN 2621793
MANHATTAN 1636268
BRONX 1438159

In [20]:
my_list.columns = ['population']

In [21]:
my_list


Out[21]:
population
QUEENS 2321580
STATEN ISLAND 472621
BROOKLYN 2621793
MANHATTAN 1636268
BRONX 1438159
Unspecified 0

In [22]:
per_cap = new_list.merge(my_list, left_index=True, right_index=True)
per_cap.head(6)


Out[22]:
Park Borough population
QUEENS 5500 2321580
STATEN ISLAND 814 472621
BROOKLYN 5761 2621793
MANHATTAN 4491 1636268
BRONX 2446 1438159
Unspecified 987 0

In [23]:
per_cap['count'] = per_cap['Park Borough']
per_cap.drop('Park Borough', axis=1)


Out[23]:
population count
QUEENS 2321580 5500
STATEN ISLAND 472621 814
BROOKLYN 2621793 5761
MANHATTAN 1636268 4491
BRONX 1438159 2446
Unspecified 0 987

In [24]:
#finally calculating complaints per capita
per_cap['per_capital'] = per_cap['Park Borough'] / per_cap['population']
per_cap.head(6).sort_values(by='per_capital', ascending=False)


Out[24]:
Park Borough population count per_capital
Unspecified 987 0 987 inf
MANHATTAN 4491 1636268 4491 0.002745
QUEENS 5500 2321580 5500 0.002369
BROOKLYN 5761 2621793 5761 0.002197
STATEN ISLAND 814 472621 814 0.001722
BRONX 2446 1438159 2446 0.001701

6.According to your selection of data, how many cases were filed in March? How about May?


In [25]:
df.head(3)


Out[25]:
Unique Key Created Date Closed Date Agency Agency Name Complaint Type Descriptor Location Type Incident Zip Incident Address ... Bridge Highway Direction Road Ramp Bridge Highway Segment Garage Lot Name Ferry Direction Ferry Terminal Name Latitude Longitude Location created_datetime
created_datetime
2015-07-06 10:58:27 31015465 07/06/2015 10:58:27 AM 07/22/2015 01:07:20 AM DCA Department of Consumer Affairs Consumer Complaint Demand for Cash NaN 11360 27-16 203 STREET ... NaN NaN NaN NaN NaN NaN 40.773540 -73.788237 (40.773539552542, -73.78823697228408) 2015-07-06 10:58:27
2015-07-03 13:26:29 30997660 07/03/2015 01:26:29 PM 07/03/2015 02:08:20 PM NYPD New York City Police Department Vending In Prohibited Area Residential Building/House 10019 200 CENTRAL PARK SOUTH ... NaN NaN NaN NaN NaN NaN 40.767021 -73.979448 (40.76702142171206, -73.97944780718524) 2015-07-03 13:26:29
2015-11-09 03:55:09 31950223 11/09/2015 03:55:09 AM 11/09/2015 08:08:57 AM NYPD New York City Police Department Blocked Driveway No Access Street/Sidewalk 10453 1993 GRAND AVENUE ... NaN NaN NaN NaN NaN NaN 40.852671 -73.910608 (40.85267061877697, -73.91060771362552) 2015-11-09 03:55:09

3 rows × 54 columns


In [26]:
march = df['2015-03']

In [27]:
total_march = pd.value_counts(march['Complaint Type'],sort=True)
total_march


Out[27]:
Blocked Driveway                  275
Illegal Parking                   221
Highway Condition                 218
Street Condition                  204
Consumer Complaint                166
Benefit Card Replacement          136
Noise - Commercial                132
Taxi Complaint                     81
Food Establishment                 77
SCRIE                              67
Broken Muni Meter                  65
Noise - Street/Sidewalk            65
Derelict Vehicle                   62
Construction                       49
Noise - Vehicle                    46
Damaged Tree                       42
Indoor Air Quality                 35
Fire Safety Director - F58         33
Animal Abuse                       27
For Hire Vehicle Complaint         27
Sidewalk Condition                 23
Graffiti                           23
Overgrown Tree/Branches            22
Root/Sewer/Sidewalk Condition      22
Maintenance or Facility            18
Dead Tree                          17
Food Poisoning                     16
Taxi Report                        12
Curb Condition                     11
Street Sign - Damaged              11
                                 ... 
Animal in a Park                   10
Street Sign - Dangling              9
Homeless Encampment                 9
Traffic                             9
Violation of Park Rules             9
School Maintenance                  9
Noise - Helicopter                  9
Vending                             7
EAP Inspection - F59                6
DPR Internal                        5
Bridge Condition                    5
Non-Residential Heat                4
City Vehicle Placard Complaint      4
Public Payphone Complaint           4
Fire Alarm - Reinspection           3
Asbestos                            3
Broken Parking Meter                3
Noise - House of Worship            2
Taxi Compliment                     2
Fire Alarm - New System             2
Illegal Tree Damage                 2
Mold                                2
Drinking                            2
Urinating in Public                 1
Agency Issues                       1
Window Guard                        1
Building/Use                        1
For Hire Vehicle Report             1
Fire Alarm - Modification           1
Traffic Signal Condition            1
Name: Complaint Type, dtype: int64

In [28]:
print("The total number of complaints is", total_march.sum())


The total number of complaints is 2360

In [29]:
may = df['2015-05']

In [30]:
total_may = pd.value_counts(may['Complaint Type'],sort=True)
total_may


Out[30]:
Street Condition                     67
Noise - Street/Sidewalk              63
Illegal Parking                      62
Blocked Driveway                     52
Noise - Commercial                   40
Broken Muni Meter                    27
Graffiti                             20
Noise - Vehicle                      20
Derelict Vehicle                     16
Maintenance or Facility               8
Homeless Encampment                   7
Vending                               7
Animal Abuse                          6
Traffic                               5
Overgrown Tree/Branches               5
Taxi Complaint                        4
Building/Use                          3
Street Light Condition                3
Highway Condition                     3
Consumer Complaint                    3
Noise - Park                          3
Indoor Sewage                         3
Panhandling                           3
Drinking                              2
PAINT/PLASTER                         2
Standing Water                        2
Sidewalk Condition                    2
Street Sign - Damaged                 2
Street Sign - Missing                 2
Damaged Tree                          2
Dead Tree                             2
Disorderly Youth                      2
Sewer                                 1
Animal in a Park                      1
Fire Alarm - Reinspection             1
Beach/Pool/Sauna Complaint            1
Missed Collection (All Materials)     1
For Hire Vehicle Complaint            1
Urinating in Public                   1
Indoor Air Quality                    1
Broken Parking Meter                  1
Curb Condition                        1
Food Poisoning                        1
General Construction/Plumbing         1
School Maintenance                    1
Root/Sewer/Sidewalk Condition         1
Benefit Card Replacement              1
Taxi Compliment                       1
Food Establishment                    1
Illegal Tree Damage                   1
Name: Complaint Type, dtype: int64

In [31]:
print("The total number of complaints is", total_may.sum())


The total number of complaints is 466

7). I'd like to see all of the 311 complaints called in on April 1st.

Surprise! We couldn't do this in class, but it was just a limitation of our data set


In [32]:
April = df['2015-04']
April.sort('created_datetime').head()


c:\users\radhika\appdata\local\programs\python\python35-32\lib\site-packages\ipykernel\__main__.py:2: FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
  from ipykernel import kernelapp as app
Out[32]:
Unique Key Created Date Closed Date Agency Agency Name Complaint Type Descriptor Location Type Incident Zip Incident Address ... Bridge Highway Direction Road Ramp Bridge Highway Segment Garage Lot Name Ferry Direction Ferry Terminal Name Latitude Longitude Location created_datetime
created_datetime
2015-04-01 00:09:40 30298884 04/01/2015 12:09:40 AM 04/01/2015 02:17:16 AM NYPD New York City Police Department Blocked Driveway No Access Street/Sidewalk 11433 150-38 107 AVENUE ... NaN NaN NaN NaN NaN NaN 40.694510 -73.800763 (40.69451003870482, -73.80076336778066) 2015-04-01 00:09:40
2015-04-01 00:13:05 30298345 04/01/2015 12:13:05 AM 04/01/2015 03:03:50 AM NYPD New York City Police Department Noise - Commercial Banging/Pounding Store/Commercial 11232 129 32 STREET ... NaN NaN NaN NaN NaN NaN 40.657200 -74.003154 (40.65719981107672, -74.00315362091445) 2015-04-01 00:13:05
2015-04-01 01:28:45 30298825 04/01/2015 01:28:45 AM 04/01/2015 02:36:49 AM NYPD New York City Police Department Blocked Driveway No Access Street/Sidewalk 11232 4001 8 AVENUE ... NaN NaN NaN NaN NaN NaN 40.646630 -73.997960 (40.646629679609966, -73.99796038095705) 2015-04-01 01:28:45
2015-04-01 04:47:37 30313064 04/01/2015 04:47:37 AM 04/06/2015 04:37:53 PM DOHMH Department of Health and Mental Hygiene Food Poisoning 1 or 2 Restaurant/Bar/Deli/Bakery 11215 NaN ... NaN NaN NaN NaN NaN NaN 40.672797 -73.983284 (40.672797214041466, -73.98328370274999) 2015-04-01 04:47:37
2015-04-01 05:06:40 30312979 04/01/2015 05:06:40 AM 04/01/2015 07:05:40 AM NYPD New York City Police Department Illegal Parking Commercial Overnight Parking Street/Sidewalk 11221 717 MADISON STREET ... NaN NaN NaN NaN NaN NaN 40.687312 -73.928130 (40.68731230396636, -73.92812986821195) 2015-04-01 05:06:40

5 rows × 54 columns


In [33]:
April[:"20150401"]


Out[33]:
Unique Key Created Date Closed Date Agency Agency Name Complaint Type Descriptor Location Type Incident Zip Incident Address ... Bridge Highway Direction Road Ramp Bridge Highway Segment Garage Lot Name Ferry Direction Ferry Terminal Name Latitude Longitude Location created_datetime
created_datetime
2015-04-01 21:37:42 30311691 04/01/2015 09:37:42 PM 04/01/2015 10:49:33 PM NYPD New York City Police Department Illegal Parking Blocked Sidewalk Street/Sidewalk 11234 NaN ... NaN NaN NaN NaN NaN NaN 40.609810 -73.922498 (40.60980966645303, -73.92249759633725) 2015-04-01 21:37:42
2015-04-01 23:12:04 30307701 04/01/2015 11:12:04 PM 04/01/2015 11:32:40 PM NYPD New York City Police Department Noise - Commercial Loud Music/Party Store/Commercial 11205 700 MYRTLE AVENUE ... NaN NaN NaN NaN NaN NaN 40.694644 -73.955504 (40.694643700748486, -73.95550356170298) 2015-04-01 23:12:04
2015-04-01 13:10:35 30313389 04/01/2015 01:10:35 PM 04/07/2015 04:01:08 PM DPR Department of Parks and Recreation Root/Sewer/Sidewalk Condition Trees and Sidewalks Program Street 11422 245-16 149 AVENUE ... NaN NaN NaN NaN NaN NaN 40.653016 -73.738626 (40.653016256598534, -73.73862588133056) 2015-04-01 13:10:35
2015-04-01 17:37:38 30314393 04/01/2015 05:37:38 PM 04/03/2015 11:40:54 AM DPR Department of Parks and Recreation Maintenance or Facility Hours of Operation Park 11211 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 2015-04-01 17:37:38
2015-04-01 12:32:40 30309207 04/01/2015 12:32:40 PM 04/17/2015 01:06:49 AM DCA Department of Consumer Affairs Consumer Complaint Installation/Work Quality NaN 11423 90-71 198 STREET ... NaN NaN NaN NaN NaN NaN 40.714299 -73.761158 (40.71429859671565, -73.76115807774032) 2015-04-01 12:32:40
2015-04-01 18:44:50 30311759 04/01/2015 06:44:50 PM 06/24/2015 11:27:00 AM DPR Department of Parks and Recreation Damaged Tree Entire Tree Has Fallen Down Street 10467 862 EAST 213 STREET ... NaN NaN NaN NaN NaN NaN 40.878028 -73.860237 (40.87802828144708, -73.86023734606933) 2015-04-01 18:44:50
2015-04-01 16:30:15 30309690 04/01/2015 04:30:15 PM 04/01/2015 11:27:22 PM NYPD New York City Police Department Animal Abuse Neglected Residential Building/House 11368 107-15 NORTHERN BOULEVARD ... NaN NaN NaN NaN NaN NaN 40.757811 -73.861677 (40.757811195752154, -73.86167714731972) 2015-04-01 16:30:15
2015-04-01 09:04:07 30307990 04/01/2015 09:04:07 AM 04/06/2015 09:17:10 AM DOF Senior Citizen Rent Increase Exemption Unit SCRIE Miscellaneous Senior Address 10027 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 2015-04-01 09:04:07
2015-04-01 07:46:58 30308253 04/01/2015 07:46:58 AM 04/01/2015 09:32:31 AM NYPD New York City Police Department Blocked Driveway No Access Street/Sidewalk 11370 32-51 80 STREET ... NaN NaN NaN NaN NaN NaN 40.756412 -73.887405 (40.75641194675221, -73.88740503059863) 2015-04-01 07:46:58
2015-04-01 17:12:17 30314214 04/01/2015 05:12:17 PM 04/09/2015 02:20:11 PM DOT Department of Transportation Highway Condition Pothole - Highway Highway NaN NaN ... West/Manhattan Bound Roadway Clearview Expwy (I-295) (Exit 27 S-N) - Utopia... NaN NaN NaN NaN NaN NaN 2015-04-01 17:12:17
2015-04-01 21:30:48 30307111 04/01/2015 09:30:48 PM NaN DOHMH Department of Health and Mental Hygiene Food Establishment Food Temperature Restaurant/Bar/Deli/Bakery 11215 709 5 AVENUE ... NaN NaN NaN NaN NaN NaN 40.660699 -73.994082 (40.660699296661825, -73.99408169463258) 2015-04-01 21:30:48
2015-04-01 15:51:04 30311571 04/01/2015 03:51:04 PM 04/14/2015 09:23:30 AM DPR Department of Parks and Recreation Maintenance or Facility Hours of Operation Park 11210 NaN ... NaN NaN NaN NaN NaN NaN 40.621474 -73.950711 (40.62147413119333, -73.95071097029123) 2015-04-01 15:51:04
2015-04-01 10:43:28 30313817 04/01/2015 10:43:28 AM NaN DPR Department of Parks and Recreation Damaged Tree Branch Cracked and Will Fall NaN 10009 620 EAST 12TH STREET ... NaN NaN NaN NaN NaN NaN 40.727725 -73.978204 (40.72772462544187, -73.97820435916094) 2015-04-01 10:43:28
2015-04-01 15:12:46 30308922 04/01/2015 03:12:46 PM 06/01/2015 06:25:48 AM DOHMH Department of Health and Mental Hygiene Food Establishment Letter Grading Restaurant/Bar/Deli/Bakery 11238 663 FRANKLIN AVENUE ... NaN NaN NaN NaN NaN NaN 40.675746 -73.956122 (40.67574618440852, -73.9561218336512) 2015-04-01 15:12:46
2015-04-01 06:15:42 30311132 04/01/2015 06:15:42 AM 04/01/2015 10:28:30 AM DOT Department of Transportation Highway Condition Pothole - Highway Highway 10304 NaN ... East/Brooklyn Bound Roadway Clove Rd/Richmond Rd (Exit 13) - Lily Pond Ave... NaN NaN NaN 40.606875 -74.085408 (40.60687536641399, -74.0854077221027) 2015-04-01 06:15:42
2015-04-01 11:28:02 30308180 04/01/2015 11:28:02 AM 04/01/2015 11:42:53 AM DOT Department of Transportation Highway Condition Pothole - Highway Highway 11432 NaN ... West/Toward Triborough Br Ramp 168th St (Exit 17) NaN NaN NaN 40.719228 -73.791963 (40.71922760413319, -73.791962929951) 2015-04-01 11:28:02
2015-04-01 17:35:18 30313207 04/01/2015 05:35:18 PM 06/01/2015 06:25:54 AM DOHMH Department of Health and Mental Hygiene Food Establishment Rodents/Insects/Garbage Restaurant/Bar/Deli/Bakery 10011 140 WEST 13 STREET ... NaN NaN NaN NaN NaN NaN 40.737182 -73.998585 (40.737182358685516, -73.99858548189518) 2015-04-01 17:35:18
2015-04-01 13:54:54 30310017 04/01/2015 01:54:54 PM 04/06/2015 10:11:11 AM DOF Senior Citizen Rent Increase Exemption Unit SCRIE Miscellaneous Senior Address 11435 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 2015-04-01 13:54:54
2015-04-01 23:49:33 30306774 04/01/2015 11:49:33 PM 04/02/2015 12:20:59 AM NYPD New York City Police Department Noise - Commercial Loud Music/Party Store/Commercial 10003 36 SAINT MARKS PLACE ... NaN NaN NaN NaN NaN NaN 40.728733 -73.988011 (40.72873338955463, -73.98801059255561) 2015-04-01 23:49:33
2015-04-01 07:50:49 30313339 04/01/2015 07:50:49 AM 07/08/2015 02:19:25 PM DOT Department of Transportation Street Condition Rough, Pitted or Cracked Roads Street 11385 NaN ... NaN NaN NaN NaN NaN NaN 40.703414 -73.862854 (40.70341423569781, -73.86285397616253) 2015-04-01 07:50:49
2015-04-01 13:50:29 30312146 04/01/2015 01:50:29 PM 06/01/2015 06:25:49 AM DOHMH Department of Health and Mental Hygiene Food Establishment Rodents/Insects/Garbage Restaurant/Bar/Deli/Bakery 10028 1291 LEXINGTON AVENUE ... NaN NaN NaN NaN NaN NaN 40.780069 -73.955158 (40.78006850471446, -73.95515761412761) 2015-04-01 13:50:29
2015-04-01 16:14:19 30313259 04/01/2015 04:14:19 PM 04/01/2015 04:21:53 PM HRA HRA Benefit Card Replacement Benefit Card Replacement Medicaid NYC Street Address NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 2015-04-01 16:14:19
2015-04-01 19:27:34 30308920 04/01/2015 07:27:34 PM 04/01/2015 08:45:17 PM NYPD New York City Police Department Noise - Street/Sidewalk Loud Music/Party Street/Sidewalk 10017 210 EAST 46 STREET ... NaN NaN NaN NaN NaN NaN 40.753104 -73.972096 (40.75310402468627, -73.97209629231209) 2015-04-01 19:27:34
2015-04-01 05:30:02 30314164 04/01/2015 05:30:02 AM 04/01/2015 02:57:31 PM DOT Department of Transportation Highway Condition Pothole - Highway Highway NaN NaN ... East/Queens Bound Roadway Williamsburg Br / Metropolitan Ave (Exit 32) -... NaN NaN NaN NaN NaN NaN 2015-04-01 05:30:02
2015-04-01 10:33:26 30311790 04/01/2015 10:33:26 AM 04/01/2015 11:19:12 AM NYPD New York City Police Department Illegal Parking Blocked Sidewalk Street/Sidewalk 10033 2284 AMSTERDAM AVENUE ... NaN NaN NaN NaN NaN NaN 40.843149 -73.934539 (40.84314882753921, -73.93453937669832) 2015-04-01 10:33:26
2015-04-01 11:47:38 30310940 04/01/2015 11:47:38 AM 04/06/2015 09:23:32 AM DOF Senior Citizen Rent Increase Exemption Unit SCRIE Miscellaneous Senior Address 11355 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 2015-04-01 11:47:38
2015-04-01 11:01:27 30310409 04/01/2015 11:01:27 AM 04/17/2015 01:06:42 AM DCA Department of Consumer Affairs Consumer Complaint Exchange/Refund/Return NaN 10455 2997 3 AVENUE ... NaN NaN NaN NaN NaN NaN 40.819111 -73.913908 (40.819110789789214, -73.91390802507868) 2015-04-01 11:01:27
2015-04-01 08:51:52 30310350 04/01/2015 08:51:52 AM 04/03/2015 04:33:46 PM DCA Department of Consumer Affairs Consumer Complaint Cars Parked on Sidewalk/Street NaN 11223 1701 WEST 8 STREET ... NaN NaN NaN NaN NaN NaN 40.605657 -73.981194 (40.60565667868274, -73.98119372058547) 2015-04-01 08:51:52
2015-04-01 14:58:55 30313106 04/01/2015 02:58:55 PM 04/06/2015 10:06:35 AM DOF Senior Citizen Rent Increase Exemption Unit SCRIE Rent Discrepancy Senior Address 11201 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 2015-04-01 14:58:55
2015-04-01 16:59:19 30309324 04/01/2015 04:59:19 PM 04/01/2015 07:48:33 PM NYPD New York City Police Department Blocked Driveway Partial Access Street/Sidewalk 11210 650 EAST 24 STREET ... NaN NaN NaN NaN NaN NaN 40.634497 -73.954167 (40.63449684441219, -73.95416735372353) 2015-04-01 16:59:19
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
2015-04-01 12:14:11 30308181 04/01/2015 12:14:11 PM 04/16/2015 03:52:40 PM DOT Department of Transportation Highway Condition Unsafe Worksite Highway 11103 NaN ... East/Long Island Bound Roadway 31st (Exit 3) - Brooklyn-Queens Expwy (I-278) ... NaN NaN NaN 40.769309 -73.912236 (40.76930913453694, -73.91223589513348) 2015-04-01 12:14:11
2015-04-01 10:00:57 30312096 04/01/2015 10:00:57 AM 04/03/2015 02:35:36 PM DOF Senior Citizen Rent Increase Exemption Unit SCRIE Copy of Approval Order Senior Address 10025 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 2015-04-01 10:00:57
2015-04-01 08:56:10 30314369 04/01/2015 08:56:10 AM 04/01/2015 11:12:26 AM NYPD New York City Police Department Blocked Driveway No Access Street/Sidewalk 11377 31-36 68 STREET ... NaN NaN NaN NaN NaN NaN 40.757216 -73.899106 (40.7572160209837, -73.89910584068605) 2015-04-01 08:56:10
2015-04-01 18:19:48 30310395 04/01/2015 06:19:48 PM 04/17/2015 01:07:04 AM DCA Department of Consumer Affairs Consumer Complaint False Advertising NaN 11372 80-13 37 AVENUE ... NaN NaN NaN NaN NaN NaN 40.749584 -73.885951 (40.74958432631206, -73.88595125985013) 2015-04-01 18:19:48
2015-04-01 16:23:18 30313528 04/01/2015 04:23:18 PM 04/17/2015 01:07:00 AM DCA Department of Consumer Affairs Consumer Complaint Exchange/Refund/Return NaN 11226 850 FLATBUSH AVENUE ... NaN NaN NaN NaN NaN NaN 40.651629 -73.959035 (40.651628886860884, -73.95903518064264) 2015-04-01 16:23:18
2015-04-01 11:41:29 30312050 04/01/2015 11:41:29 AM 04/06/2015 09:09:45 AM DOF Senior Citizen Rent Increase Exemption Unit SCRIE Application Renewal Senior Address 10034 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 2015-04-01 11:41:29
2015-04-01 23:47:24 30314231 04/01/2015 11:47:24 PM 07/28/2015 01:03:24 PM DOT Department of Transportation Street Condition Rough, Pitted or Cracked Roads Street 11377 NaN ... NaN NaN NaN NaN NaN NaN 40.743456 -73.914836 (40.74345557431229, -73.91483581341043) 2015-04-01 23:47:24
2015-04-01 08:16:38 30311103 04/01/2015 08:16:38 AM 04/09/2015 09:33:26 AM TLC Taxi and Limousine Commission Taxi Complaint Driver Complaint NaN 11209 NaN ... NaN NaN NaN NaN NaN NaN 40.634792 -74.032318 (40.63479238458042, -74.03231826494591) 2015-04-01 08:16:38
2015-04-01 07:04:27 30310725 04/01/2015 07:04:27 AM 04/01/2015 03:47:39 PM NYPD New York City Police Department Illegal Parking Posted Parking Sign Violation Street/Sidewalk 11419 104-22 110 STREET ... NaN NaN NaN NaN NaN NaN 40.684012 -73.831954 (40.68401163822402, -73.83195428896114) 2015-04-01 07:04:27
2015-04-01 23:36:08 30311907 04/01/2015 11:36:08 PM 04/02/2015 07:29:05 AM NYPD New York City Police Department Illegal Parking Blocked Hydrant Street/Sidewalk 11228 1343 78 STREET ... NaN NaN NaN NaN NaN NaN 40.617990 -74.008220 (40.617990283460536, -74.00821981214455) 2015-04-01 23:36:08
2015-04-01 14:48:14 30308202 04/01/2015 02:48:14 PM 04/01/2015 02:49:13 PM HRA HRA Benefit Card Replacement Benefit Card Replacement Food Stamp NYC Street Address NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 2015-04-01 14:48:14
2015-04-01 15:26:42 30308819 04/01/2015 03:26:42 PM 04/01/2015 05:19:41 PM NYPD New York City Police Department Illegal Parking Posted Parking Sign Violation Street/Sidewalk 11101 43-10 CRESCENT STREET ... NaN NaN NaN NaN NaN NaN 40.748707 -73.942316 (40.74870685388612, -73.94231592971958) 2015-04-01 15:26:42
2015-04-01 19:08:07 30313580 04/01/2015 07:08:07 PM 04/07/2015 11:25:05 AM DOT Department of Transportation Street Condition Defective Hardware Street 11208 880 GLENMORE AVENUE ... NaN NaN NaN NaN NaN NaN 40.675876 -73.877688 (40.67587618287245, -73.87768812152434) 2015-04-01 19:08:07
2015-04-01 22:06:37 30313046 04/01/2015 10:06:37 PM 04/02/2015 12:40:09 AM NYPD New York City Police Department Noise - Street/Sidewalk Loud Music/Party Street/Sidewalk 10454 592 OAK TERRACE ... NaN NaN NaN NaN NaN NaN 40.808939 -73.914543 (40.80893932182981, -73.91454250715576) 2015-04-01 22:06:37
2015-04-01 00:09:40 30298884 04/01/2015 12:09:40 AM 04/01/2015 02:17:16 AM NYPD New York City Police Department Blocked Driveway No Access Street/Sidewalk 11433 150-38 107 AVENUE ... NaN NaN NaN NaN NaN NaN 40.694510 -73.800763 (40.69451003870482, -73.80076336778066) 2015-04-01 00:09:40
2015-04-01 11:43:50 30311054 04/01/2015 11:43:50 AM 04/06/2015 09:12:54 AM DOF Senior Citizen Rent Increase Exemption Unit SCRIE Copy of Approval Order Senior Address 10003 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 2015-04-01 11:43:50
2015-04-01 13:30:29 30307424 04/01/2015 01:30:29 PM 04/13/2015 12:20:33 PM DOT Department of Transportation Street Condition Failed Street Repair Street 11364 67-07 BELL BOULEVARD ... NaN NaN NaN NaN NaN NaN 40.743972 -73.759771 (40.74397211975241, -73.75977055909947) 2015-04-01 13:30:29
2015-04-01 07:33:41 30314352 04/01/2015 07:33:41 AM 04/13/2015 12:27:12 PM DOT Department of Transportation Street Condition Cave-in Street 11357 14-51 143 STREET ... NaN NaN NaN NaN NaN NaN 40.785713 -73.826140 (40.7857127748661, -73.82614011947928) 2015-04-01 07:33:41
2015-04-01 12:28:15 30312905 04/01/2015 12:28:15 PM 04/01/2015 02:29:53 PM NYPD New York City Police Department Illegal Parking Double Parked Blocking Traffic Street/Sidewalk 11372 72 STREET ... NaN NaN NaN NaN NaN NaN 40.749789 -73.893794 (40.74978944638325, -73.89379359227247) 2015-04-01 12:28:15
2015-04-01 12:17:19 30311302 04/01/2015 12:17:19 PM 04/17/2015 01:06:56 AM DCA Department of Consumer Affairs Consumer Complaint Illegal Tow NaN 11232 14 53 STREET ... NaN NaN NaN NaN NaN NaN 40.648964 -74.021255 (40.648963544502585, -74.02125458310132) 2015-04-01 12:17:19
2015-04-01 12:08:19 30314467 04/01/2015 12:08:19 PM 04/09/2015 02:43:13 PM DOT Department of Transportation Street Condition Failed Street Repair Street 11428 NaN ... NaN NaN NaN NaN NaN NaN 40.722832 -73.748158 (40.72283183191531, -73.74815780857023) 2015-04-01 12:08:19
2015-04-01 17:43:25 30311231 04/01/2015 05:43:25 PM 04/01/2015 10:50:39 PM NYPD New York City Police Department Blocked Driveway No Access Street/Sidewalk 11435 143-30 LAKEWOOD AVENUE ... NaN NaN NaN NaN NaN NaN 40.689215 -73.803877 (40.68921522366862, -73.80387663789386) 2015-04-01 17:43:25
2015-04-01 18:30:35 30307426 04/01/2015 06:30:35 PM 04/13/2015 12:15:11 PM DOT Department of Transportation Street Condition Failed Street Repair Street 11362 NaN ... NaN NaN NaN NaN NaN NaN 40.750641 -73.739344 (40.75064138697133, -73.7393436538413) 2015-04-01 18:30:35
2015-04-01 13:30:31 30308409 04/01/2015 01:30:31 PM 04/13/2015 12:19:28 PM DOT Department of Transportation Street Condition Failed Street Repair Street 11379 79-17 68 ROAD ... NaN NaN NaN NaN NaN NaN 40.710496 -73.872661 (40.71049602255762, -73.8726613318581) 2015-04-01 13:30:31
2015-04-01 01:28:45 30298825 04/01/2015 01:28:45 AM 04/01/2015 02:36:49 AM NYPD New York City Police Department Blocked Driveway No Access Street/Sidewalk 11232 4001 8 AVENUE ... NaN NaN NaN NaN NaN NaN 40.646630 -73.997960 (40.646629679609966, -73.99796038095705) 2015-04-01 01:28:45
2015-04-01 08:52:11 30307104 04/01/2015 08:52:11 AM 04/02/2015 04:34:46 PM DCA Department of Consumer Affairs Consumer Complaint Installation/Work Quality NaN 10469 3033 YOUNG AVENUE ... NaN NaN NaN NaN NaN NaN 40.870105 -73.847979 (40.870105314232546, -73.84797875606117) 2015-04-01 08:52:11
2015-04-01 11:12:49 30310013 04/01/2015 11:12:49 AM 04/06/2015 11:47:20 AM DOT Department of Transportation Street Sign - Damaged Street Cleaning - ASP Street 10026 17 LENOX AVENUE ... NaN NaN NaN NaN NaN NaN 40.798932 -73.951952 (40.7989317549172, -73.9519520651255) 2015-04-01 11:12:49
2015-04-01 16:18:23 30311889 04/01/2015 04:18:23 PM 04/01/2015 11:11:11 PM NYPD New York City Police Department Derelict Vehicle With License Plate Street/Sidewalk 11226 485 EAST 17 STREET ... NaN NaN NaN NaN NaN NaN 40.638946 -73.962055 (40.638946273235284, -73.96205520207174) 2015-04-01 16:18:23
2015-04-01 13:16:44 30312450 04/01/2015 01:16:44 PM 04/01/2015 02:30:55 PM NYPD New York City Police Department Blocked Driveway No Access Street/Sidewalk 11372 37-18 73 STREET ... NaN NaN NaN NaN NaN NaN 40.748262 -73.892616 (40.748262273356396, -73.89261586191228) 2015-04-01 13:16:44
2015-04-01 20:27:33 30313471 04/01/2015 08:27:33 PM 04/17/2015 01:07:09 AM DCA Department of Consumer Affairs Consumer Complaint Overcharge NaN 11226 3008 CHURCH AVENUE ... NaN NaN NaN NaN NaN NaN 40.650810 -73.949370 (40.6508098378492, -73.94937030940775) 2015-04-01 20:27:33

147 rows × 54 columns

8.What was the most popular type of complaint on April 1st?


In [34]:
april_complaints = April[:"20150401"]
pd.value_counts(april_complaints['Complaint Type'],sort=True).head(1)


Out[34]:
Street Condition    18
Name: Complaint Type, dtype: int64

9.What were the most popular three types of complaint on April 1st


In [35]:
pd.value_counts(april_complaints['Complaint Type'],sort=True).head(3)


Out[35]:
Street Condition      18
Illegal Parking       15
Consumer Complaint    12
Name: Complaint Type, dtype: int64

10) What month has the most reports filed? How many? Graph it.


In [36]:
df.head(3)


Out[36]:
Unique Key Created Date Closed Date Agency Agency Name Complaint Type Descriptor Location Type Incident Zip Incident Address ... Bridge Highway Direction Road Ramp Bridge Highway Segment Garage Lot Name Ferry Direction Ferry Terminal Name Latitude Longitude Location created_datetime
created_datetime
2015-07-06 10:58:27 31015465 07/06/2015 10:58:27 AM 07/22/2015 01:07:20 AM DCA Department of Consumer Affairs Consumer Complaint Demand for Cash NaN 11360 27-16 203 STREET ... NaN NaN NaN NaN NaN NaN 40.773540 -73.788237 (40.773539552542, -73.78823697228408) 2015-07-06 10:58:27
2015-07-03 13:26:29 30997660 07/03/2015 01:26:29 PM 07/03/2015 02:08:20 PM NYPD New York City Police Department Vending In Prohibited Area Residential Building/House 10019 200 CENTRAL PARK SOUTH ... NaN NaN NaN NaN NaN NaN 40.767021 -73.979448 (40.76702142171206, -73.97944780718524) 2015-07-03 13:26:29
2015-11-09 03:55:09 31950223 11/09/2015 03:55:09 AM 11/09/2015 08:08:57 AM NYPD New York City Police Department Blocked Driveway No Access Street/Sidewalk 10453 1993 GRAND AVENUE ... NaN NaN NaN NaN NaN NaN 40.852671 -73.910608 (40.85267061877697, -73.91060771362552) 2015-11-09 03:55:09

3 rows × 54 columns


In [37]:
df.resample('M').count().sort_values('Created Date', ascending=False).head(1)


Out[37]:
Unique Key Created Date Closed Date Agency Agency Name Complaint Type Descriptor Location Type Incident Zip Incident Address ... Bridge Highway Direction Road Ramp Bridge Highway Segment Garage Lot Name Ferry Direction Ferry Terminal Name Latitude Longitude Location created_datetime
created_datetime
2015-09-30 3352 3352 3169 3352 3352 3352 3296 3185 3204 2642 ... 19 19 19 1 2 4 3100 3100 3100 3352

1 rows × 54 columns


In [38]:
ax = df.resample('M').count().plot(y='Created Date')
ax.set_title("Monthly Complaints")


Out[38]:
<matplotlib.text.Text at 0x829c770>

11) What week of the year has the most reports filed? How many? Graph the weekly complaints.


In [39]:
df.resample('W').count().sort_values('Created Date', ascending=False).head(1)


Out[39]:
Unique Key Created Date Closed Date Agency Agency Name Complaint Type Descriptor Location Type Incident Zip Incident Address ... Bridge Highway Direction Road Ramp Bridge Highway Segment Garage Lot Name Ferry Direction Ferry Terminal Name Latitude Longitude Location created_datetime
created_datetime
2015-09-27 982 982 937 982 982 982 966 941 940 798 ... 4 4 4 0 1 2 907 907 907 982

1 rows × 54 columns


In [40]:
ax = df.resample('W').count().plot(y='Created Date')
ax.set_title("Weekly Complaints")


Out[40]:
<matplotlib.text.Text at 0x89857b0>

12). Noise complaints are a big deal. Use .str.contains to select noise complaints, and make an chart of when they show up annually. Then make a chart about when they show up every day (cyclic).


In [41]:
columns_req =  ['Complaint Type']
find_noice= df[columns_req]

find_noice.head()


Out[41]:
Complaint Type
created_datetime
2015-07-06 10:58:27 Consumer Complaint
2015-07-03 13:26:29 Vending
2015-11-09 03:55:09 Blocked Driveway
2015-07-03 02:18:32 Noise - Commercial
2015-07-04 00:03:27 Noise - Street/Sidewalk

In [42]:
df_noice = find_noice[find_noice['Complaint Type'].str.contains("Noise", case=False)]

In [43]:
df_noice.head(3)


Out[43]:
Complaint Type
created_datetime
2015-07-03 02:18:32 Noise - Commercial
2015-07-04 00:03:27 Noise - Street/Sidewalk
2015-09-09 21:59:03 Noise - Street/Sidewalk

In [44]:
df_noice.resample('D').count().head(4)


Out[44]:
Complaint Type
created_datetime
2015-01-01 10
2015-01-02 3
2015-01-03 4
2015-01-04 8

In [45]:
ax = df_noice.resample('M').count().plot(y='Complaint Type')
ax.set_title("Noice Complaints in Year 2015")


Out[45]:
<matplotlib.text.Text at 0x8f48670>

In [46]:
ax = df_noice.resample('D').count().plot(y='Complaint Type')
ax.set_title("Daily Noice Complaints")


Out[46]:
<matplotlib.text.Text at 0x8e9dd90>

13) Which were the top five days of the year for filing complaints? How many on each of those days? Graph it.


In [47]:
top_days = find_noice.resample('D').count().sort_values('Complaint Type', ascending=False)
top_days.head(5)


Out[47]:
Complaint Type
created_datetime
2015-10-19 338
2015-09-25 230
2015-08-03 227
2015-08-06 191
2015-09-10 187

In [48]:
top_days['complaint count'] = top_days['Complaint Type']

In [49]:
t_days = top_days.drop('Complaint Type', axis=1).head(5)

In [50]:
ax = t_days['complaint count'].plot(kind='bar',x= 't_days.index', y='complaint count')

ax.set_title("Top Five Days with the Highest Number of Complaints")


Out[50]:
<matplotlib.text.Text at 0x9058730>

14) What hour of the day are the most complaints? Graph a day of complaints.


In [51]:
df['Unique Key'].groupby(by=df.index.hour).count()


Out[51]:
0      829
1      506
2      299
3      181
4      186
5      158
6      319
7      556
8      820
9     1160
10    1238
11    1253
12    1320
13    1261
14    1218
15    1175
16    1119
17     996
18    1014
19     824
20     840
21     883
22     949
23     895
Name: Unique Key, dtype: int64

In [52]:
ax = df['Unique Key'].groupby(by=df.index.hour).count().plot()
ax.set_title("Complaint Flow in a Typical Day")


Out[52]:
<matplotlib.text.Text at 0x909c990>

15) . One of the hours has an odd number of complaints. What are the most common complaints at that hour, and what are the most common complaints the hour before and after?


In [53]:
df.head(3)


Out[53]:
Unique Key Created Date Closed Date Agency Agency Name Complaint Type Descriptor Location Type Incident Zip Incident Address ... Bridge Highway Direction Road Ramp Bridge Highway Segment Garage Lot Name Ferry Direction Ferry Terminal Name Latitude Longitude Location created_datetime
created_datetime
2015-07-06 10:58:27 31015465 07/06/2015 10:58:27 AM 07/22/2015 01:07:20 AM DCA Department of Consumer Affairs Consumer Complaint Demand for Cash NaN 11360 27-16 203 STREET ... NaN NaN NaN NaN NaN NaN 40.773540 -73.788237 (40.773539552542, -73.78823697228408) 2015-07-06 10:58:27
2015-07-03 13:26:29 30997660 07/03/2015 01:26:29 PM 07/03/2015 02:08:20 PM NYPD New York City Police Department Vending In Prohibited Area Residential Building/House 10019 200 CENTRAL PARK SOUTH ... NaN NaN NaN NaN NaN NaN 40.767021 -73.979448 (40.76702142171206, -73.97944780718524) 2015-07-03 13:26:29
2015-11-09 03:55:09 31950223 11/09/2015 03:55:09 AM 11/09/2015 08:08:57 AM NYPD New York City Police Department Blocked Driveway No Access Street/Sidewalk 10453 1993 GRAND AVENUE ... NaN NaN NaN NaN NaN NaN 40.852671 -73.910608 (40.85267061877697, -73.91060771362552) 2015-11-09 03:55:09

3 rows × 54 columns


In [54]:
# 0 stands for 12.0 midnight
df[df.index.hour==0].head(3)


Out[54]:
Unique Key Created Date Closed Date Agency Agency Name Complaint Type Descriptor Location Type Incident Zip Incident Address ... Bridge Highway Direction Road Ramp Bridge Highway Segment Garage Lot Name Ferry Direction Ferry Terminal Name Latitude Longitude Location created_datetime
created_datetime
2015-07-04 00:03:27 30995614 07/04/2015 12:03:27 AM 07/04/2015 03:33:09 AM NYPD New York City Police Department Noise - Street/Sidewalk Loud Talking Street/Sidewalk 11216 1057 BERGEN STREET ... NaN NaN NaN NaN NaN NaN 40.676175 -73.951269 (40.67617516102934, -73.9512690004692) 2015-07-04 00:03:27
2015-07-09 00:00:00 31042454 07/09/2015 12:00:00 AM 07/20/2015 12:00:00 AM DOHMH Department of Health and Mental Hygiene Standing Water Other - Explain Below Other NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 2015-07-09 00:00:00
2015-07-09 00:00:00 31037751 07/09/2015 12:00:00 AM NaN DOHMH Department of Health and Mental Hygiene Standing Water Puddle in Ground 3+ Family Apartment Building 10016 379 THIRD AVENUE ... NaN NaN NaN NaN NaN NaN 40.741537 -73.981163 (40.741536747969185, -73.98116258383294) 2015-07-09 00:00:00

3 rows × 54 columns


In [55]:
most_comp = df[df.index.hour==0]

In [56]:
# after filtering out, counted all the unique values in the Complaint Type.
most_comp['Complaint Type'].value_counts().head(3)


Out[56]:
Noise - Street/Sidewalk    185
Noise - Commercial         179
Illegal Parking             86
Name: Complaint Type, dtype: int64

In [57]:
most_comp_before = df[df.index.hour==23]

In [58]:
most_comp_before['Complaint Type'].value_counts().head(3)


Out[58]:
Noise - Street/Sidewalk    206
Noise - Commercial         199
Blocked Driveway           116
Name: Complaint Type, dtype: int64

In [59]:
most_comp_after = df[df.index.hour==1]

In [60]:
most_comp_after['Complaint Type'].value_counts().head(3)


Out[60]:
Noise - Street/Sidewalk    156
Noise - Commercial         114
Blocked Driveway            63
Name: Complaint Type, dtype: int64

16 ) So odd. What's the per-minute breakdown of complaints between 12am and 1am? You don't need to include 1am.


In [61]:
most_comp.head(3)


Out[61]:
Unique Key Created Date Closed Date Agency Agency Name Complaint Type Descriptor Location Type Incident Zip Incident Address ... Bridge Highway Direction Road Ramp Bridge Highway Segment Garage Lot Name Ferry Direction Ferry Terminal Name Latitude Longitude Location created_datetime
created_datetime
2015-07-04 00:03:27 30995614 07/04/2015 12:03:27 AM 07/04/2015 03:33:09 AM NYPD New York City Police Department Noise - Street/Sidewalk Loud Talking Street/Sidewalk 11216 1057 BERGEN STREET ... NaN NaN NaN NaN NaN NaN 40.676175 -73.951269 (40.67617516102934, -73.9512690004692) 2015-07-04 00:03:27
2015-07-09 00:00:00 31042454 07/09/2015 12:00:00 AM 07/20/2015 12:00:00 AM DOHMH Department of Health and Mental Hygiene Standing Water Other - Explain Below Other NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 2015-07-09 00:00:00
2015-07-09 00:00:00 31037751 07/09/2015 12:00:00 AM NaN DOHMH Department of Health and Mental Hygiene Standing Water Puddle in Ground 3+ Family Apartment Building 10016 379 THIRD AVENUE ... NaN NaN NaN NaN NaN NaN 40.741537 -73.981163 (40.741536747969185, -73.98116258383294) 2015-07-09 00:00:00

3 rows × 54 columns


In [62]:
most_comp['Complaint Type'].groupby(by=most_comp.index.minute).value_counts()


Out[62]:
    Complaint Type                
0   Rodent                            66
    Standing Water                    39
    Food Poisoning                     8
    Unsanitary Animal Pvt Property     4
    Noise - Commercial                 3
    Noise - Street/Sidewalk            3
    Blocked Driveway                   2
    APPLIANCE                          1
    Noise - Park                       1
    Noise - Vehicle                    1
    Street Condition                   1
    Traffic Signal Condition           1
    Unsanitary Pigeon Condition        1
1   Noise - Commercial                 6
    Noise - Street/Sidewalk            6
    Blocked Driveway                   2
    Illegal Parking                    1
    PLUMBING                           1
2   Noise - Commercial                 3
    Noise - Street/Sidewalk            3
    Blocked Driveway                   2
    Construction                       1
    Illegal Parking                    1
    Taxi Complaint                     1
3   Noise - Commercial                 4
    Illegal Parking                    3
    Noise - Street/Sidewalk            2
    Benefit Card Replacement           1
    Blocked Driveway                   1
    Noise - Vehicle                    1
                                      ..
54  Blocked Driveway                   1
    Graffiti                           1
    Noise - Vehicle                    1
    Taxi Complaint                     1
55  Noise - Commercial                 5
    Noise - House of Worship           1
    Noise - Vehicle                    1
    Taxi Complaint                     1
56  Noise - Street/Sidewalk            6
    Noise - Commercial                 2
    Drinking                           1
    Illegal Parking                    1
    Street Condition                   1
57  Illegal Parking                    3
    Noise - Commercial                 3
    Noise - Street/Sidewalk            2
    Blocked Driveway                   1
    Noise - Vehicle                    1
    UNSANITARY CONDITION               1
58  Noise - Street/Sidewalk            4
    Blocked Driveway                   3
    Illegal Parking                    3
    Noise - Commercial                 2
    Highway Condition                  1
    Smoking                            1
59  Blocked Driveway                   3
    Illegal Parking                    2
    Noise - Street/Sidewalk            2
    Noise - Commercial                 1
    Taxi Complaint                     1
Name: Complaint Type, dtype: int64

17) Looks like midnight is a little bit of an outlier. Why might that be? Take the 5 most common agencies and graph the times they file reports at (all day, not just midnight).


In [63]:
df['Agency'].value_counts().head(5)


Out[63]:
NYPD     9745
DOT      2838
DPR      1800
HPD      1232
DOHMH     893
Name: Agency, dtype: int64

In [87]:
agency = df[(df['Agency'] == 'NYPD') | (df['Agency'] == 'DOT') | (df['Agency'] == 'DPR') | (df['Agency'] == 'HPD') | (df['Agency'] == 'DOHMH')]

In [65]:
agency.head(3)


Out[65]:
Unique Key Created Date Closed Date Agency Agency Name Complaint Type Descriptor Location Type Incident Zip Incident Address ... Bridge Highway Direction Road Ramp Bridge Highway Segment Garage Lot Name Ferry Direction Ferry Terminal Name Latitude Longitude Location created_datetime
created_datetime
2015-07-03 13:26:29 30997660 07/03/2015 01:26:29 PM 07/03/2015 02:08:20 PM NYPD New York City Police Department Vending In Prohibited Area Residential Building/House 10019 200 CENTRAL PARK SOUTH ... NaN NaN NaN NaN NaN NaN 40.767021 -73.979448 (40.76702142171206, -73.97944780718524) 2015-07-03 13:26:29
2015-11-09 03:55:09 31950223 11/09/2015 03:55:09 AM 11/09/2015 08:08:57 AM NYPD New York City Police Department Blocked Driveway No Access Street/Sidewalk 10453 1993 GRAND AVENUE ... NaN NaN NaN NaN NaN NaN 40.852671 -73.910608 (40.85267061877697, -73.91060771362552) 2015-11-09 03:55:09
2015-07-03 02:18:32 31000038 07/03/2015 02:18:32 AM 07/03/2015 07:54:48 AM NYPD New York City Police Department Noise - Commercial Loud Music/Party Club/Bar/Restaurant 11372 84-16 NORTHERN BOULEVARD ... NaN NaN NaN NaN NaN NaN 40.755774 -73.883262 (40.755773786469966, -73.88326243225418) 2015-07-03 02:18:32

3 rows × 54 columns


In [88]:
nypd=agency[(agency['Agency'] == 'NYPD')]
dot=agency[(agency['Agency'] == 'DOT')]
dpr=agency[(agency['Agency'] == 'DPR')]
hpd=agency[(agency['Agency'] == 'HPD')]
dohmh=agency[(agency['Agency'] == 'DOHMH')]

In [89]:
dohmh.head(3)


Out[89]:
Unique Key Created Date Closed Date Agency Agency Name Complaint Type Descriptor Location Type Incident Zip Incident Address ... Bridge Highway Direction Road Ramp Bridge Highway Segment Garage Lot Name Ferry Direction Ferry Terminal Name Latitude Longitude Location created_datetime
created_datetime
2015-07-09 00:00:00 31042454 07/09/2015 12:00:00 AM 07/20/2015 12:00:00 AM DOHMH Department of Health and Mental Hygiene Standing Water Other - Explain Below Other NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 2015-07-09 00:00:00
2015-07-09 00:00:00 31037751 07/09/2015 12:00:00 AM NaN DOHMH Department of Health and Mental Hygiene Standing Water Puddle in Ground 3+ Family Apartment Building 10016 379 THIRD AVENUE ... NaN NaN NaN NaN NaN NaN 40.741537 -73.981163 (40.741536747969185, -73.98116258383294) 2015-07-09 00:00:00
2015-07-04 16:57:07 31006258 07/04/2015 04:57:07 PM 09/03/2015 06:26:41 AM DOHMH Department of Health and Mental Hygiene Food Establishment Food Contaminated Restaurant/Bar/Deli/Bakery 10024 519 COLUMBUS AVENUE ... NaN NaN NaN NaN NaN NaN 40.785904 -73.972588 (40.785903785020274, -73.97258810089549) 2015-07-04 16:57:07

3 rows × 54 columns


In [94]:
ax=nypd.groupby(by=nypd.index.hour).count().plot(y='Unique Key', label='NYPD')
dot.groupby(by=dot.index.hour).count().plot(y='Unique Key', ax=ax, label='DOT')
dpr.groupby(by=dpr.index.hour).count().plot(y='Unique Key',ax=ax, label='DPR')
hpd.groupby(by=hpd.index.hour).count().plot(y='Unique Key', ax=ax, label='HPD')
dohmh.groupby(by=dohmh.index.hour).count().plot(y='Unique Key', ax=ax, label='DOHMH')


Out[94]:
<matplotlib.axes._subplots.AxesSubplot at 0xe52c870>

In [ ]:

18) Graph those same agencies on an annual basis - make it weekly. When do people like to complain? When does the NYPD have an odd number of complaints?


In [95]:
ax=nypd.groupby(by=nypd.index.week).count().plot(y='Unique Key', label='NYPD')
dot.groupby(by=dot.index.week).count().plot(y='Unique Key', ax=ax, label='DOT')
dpr.groupby(by=dpr.index.week).count().plot(y='Unique Key',ax=ax, label='DPR')
hpd.groupby(by=hpd.index.week).count().plot(y='Unique Key', ax=ax, label='HPD')
dohmh.groupby(by=dohmh.index.week).count().plot(y='Unique Key', ax=ax, label='DOHMH')


Out[95]:
<matplotlib.axes._subplots.AxesSubplot at 0xe58e3b0>

19) Maybe the NYPD deals with different issues at different times? Check the most popular complaints in July and August vs the month of May. Also check the most common complaints for the Housing Preservation Bureau (HPD) in winter vs. summer.


In [ ]:
july_aug = nypd[(nypd.index.month==7)| (nypd.index.month==8)]

In [ ]:
july_aug['Complaint Type'].value_counts().head(5)

In [ ]:
may = nypd[(nypd.index.month==5)]

In [ ]:
may['Complaint Type'].value_counts().head(5)

In [ ]:
winter = hpd[(hpd.index.month==12)| (hpd.index.month==1)| (hpd.index.month==2)]

In [ ]:
winter['Complaint Type'].value_counts().head(5)

In [ ]:
summer = hpd[(hpd.index.month==6)| (hpd.index.month==7)| (hpd.index.month==8)]

In [ ]:
summer['Complaint Type'].value_counts().head(5)

In [ ]: