In [2]:
import pg8000
conn = pg8000.connect(user="dot_student", host="training.c1erymiua9dx.us-east-1.rds.amazonaws.com", port=5432, database="training", password="qgis")
conn.rollback()
cursor = conn.cursor()
In [3]:
cursor.execute("select column_name from information_schema.columns where table_name='noise_311'")
column_list = []
for row in cursor.fetchall():
column_list.append(row[0])
column_list
Out[3]:
['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',
'y_coordinate',
'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']
In [4]:
response = "select * from noise_311"
In [5]:
cursor.execute(response)
output = []
for row in cursor.fetchall():
output.append(row)
In [6]:
import pandas as pd
df = pd.DataFrame(data=output, columns=column_list)
print(df.head)
/Users/Monica/.virtualenvs/dataanalysis/lib/python3.5/site-packages/matplotlib/__init__.py:1035: UserWarning: Duplicate key in file "/Users/Monica/.matplotlib/matplotlibrc", line #2
(fname, cnt))
<bound method NDFrame.head of unique_key created_date closed_date agency \
0 28792167 2014-08-31 23:59:00 2014-09-01 03:52:00 NYPD
1 28789088 2014-08-31 23:56:00 2014-09-01 06:17:00 NYPD
2 28791854 2014-08-31 23:54:00 2014-09-01 01:29:00 NYPD
3 28789936 2014-08-31 23:52:00 2014-09-01 02:53:00 NYPD
4 28789931 2014-08-31 23:47:00 2014-09-01 01:06:00 NYPD
5 28790974 2014-08-31 23:46:00 2014-09-01 02:52:00 NYPD
6 28788883 2014-08-31 23:43:00 2014-09-01 00:47:00 NYPD
7 28791627 2014-08-31 23:42:00 2014-09-01 04:00:00 NYPD
8 28791754 2014-08-31 23:42:00 2014-09-01 04:30:00 NYPD
9 28791202 2014-08-31 23:40:00 2014-09-01 03:05:00 NYPD
10 28788981 2014-08-31 23:38:00 2014-09-01 04:30:00 NYPD
11 28792536 2014-08-31 23:37:00 2014-09-01 02:13:00 NYPD
12 28788983 2014-08-31 23:37:00 2014-09-01 03:31:00 NYPD
13 28792074 2014-08-31 23:34:00 2014-09-01 04:47:00 NYPD
14 28789395 2014-08-31 23:33:00 2014-09-01 02:39:00 NYPD
15 28791975 2014-08-31 23:28:00 2014-09-01 00:28:00 NYPD
16 28790952 2014-08-31 23:28:00 2014-09-01 02:55:00 NYPD
17 28790593 2014-08-31 23:27:00 2014-09-01 12:59:00 NYPD
18 28790787 2014-08-31 23:25:00 2014-09-01 01:10:00 NYPD
19 28791377 2014-08-31 23:25:00 2014-09-01 02:13:00 NYPD
20 28791028 2014-08-31 23:25:00 2014-09-01 05:19:00 NYPD
21 28791108 2014-08-31 23:23:00 2014-08-31 23:30:00 NYPD
22 28790849 2014-08-31 23:23:00 2014-08-31 23:56:00 NYPD
23 28789398 2014-08-31 23:21:00 2014-09-01 02:12:00 NYPD
24 28791004 2014-08-31 23:18:00 2014-09-01 04:45:00 NYPD
25 28791252 2014-08-31 23:17:00 2014-08-31 23:38:00 NYPD
26 28789888 2014-08-31 23:17:00 2014-09-01 01:29:00 NYPD
27 28788887 2014-08-31 23:16:00 2014-09-01 02:02:00 NYPD
28 28788858 2014-08-31 23:16:00 2014-09-01 02:12:00 NYPD
29 28791005 2014-08-31 23:15:00 2014-09-01 04:28:00 NYPD
... ... ... ... ...
37585 28157550 2014-06-01 00:24:00 2014-06-01 03:26:00 NYPD
37586 28157347 2014-06-01 00:23:00 2014-06-01 04:37:00 NYPD
37587 28159965 2014-06-01 00:23:00 2014-06-01 02:14:00 NYPD
37588 28159739 2014-06-01 00:22:00 2014-06-01 04:46:00 NYPD
37589 28162287 2014-06-01 00:21:00 2014-06-01 01:03:00 NYPD
37590 28160504 2014-06-01 00:20:00 2014-06-01 06:36:00 NYPD
37591 28158656 2014-06-01 00:19:00 2014-06-01 02:10:00 NYPD
37592 28157266 2014-06-01 00:18:00 2014-06-01 00:57:00 NYPD
37593 28161992 2014-06-01 00:17:00 2014-06-01 05:04:00 NYPD
37594 28159165 2014-06-01 00:16:00 2014-06-01 01:03:00 NYPD
37595 28159130 2014-06-01 00:14:00 2014-06-01 01:04:00 NYPD
37596 28157729 2014-06-01 00:14:00 2014-06-01 02:12:00 NYPD
37597 28156960 2014-06-01 00:14:00 2014-06-01 01:42:00 NYPD
37598 28161664 2014-06-01 00:13:00 2014-06-01 02:10:00 NYPD
37599 28160221 2014-06-01 00:12:00 2014-06-01 02:19:00 NYPD
37600 28158625 2014-06-01 00:10:00 2014-06-01 02:31:00 NYPD
37601 28161077 2014-06-01 00:09:00 2014-06-01 03:14:00 NYPD
37602 28157263 2014-06-01 00:09:00 2014-06-01 03:05:00 NYPD
37603 28159956 2014-06-01 00:09:00 2014-06-01 01:04:00 NYPD
37604 28162132 2014-06-01 00:06:00 2014-06-01 01:20:00 NYPD
37605 28159435 2014-06-01 00:06:00 2014-06-01 01:41:00 NYPD
37606 28162516 2014-06-01 00:05:00 2014-06-01 14:07:00 NYPD
37607 28157374 2014-06-01 00:03:00 2014-06-01 03:15:00 NYPD
37608 28159445 2014-06-01 00:02:00 2014-06-01 01:44:00 NYPD
37609 28156952 2014-06-01 00:02:00 2014-06-01 01:14:00 NYPD
37610 28161969 2014-06-01 00:02:00 2014-06-01 10:26:00 NYPD
37611 28157261 2014-06-01 00:02:00 2014-06-01 02:26:00 NYPD
37612 28162458 2014-06-01 00:01:00 2014-06-01 04:39:00 NYPD
37613 28162216 2014-06-01 00:00:00 2014-06-01 02:14:00 NYPD
37614 28162314 2014-06-01 00:00:00 2014-06-01 07:38:00 NYPD
agency_name complaint_type \
0 New York City Police Department Noise - Street/Sidewalk
1 New York City Police Department Noise - Vehicle
2 New York City Police Department Noise - Commercial
3 New York City Police Department Noise - Street/Sidewalk
4 New York City Police Department Noise - Street/Sidewalk
5 New York City Police Department Noise - Vehicle
6 New York City Police Department Noise - Vehicle
7 New York City Police Department Noise - Commercial
8 New York City Police Department Noise - Park
9 New York City Police Department Noise - Commercial
10 New York City Police Department Noise - Street/Sidewalk
11 New York City Police Department Noise - Vehicle
12 New York City Police Department Noise - Street/Sidewalk
13 New York City Police Department Noise - Street/Sidewalk
14 New York City Police Department Noise - Street/Sidewalk
15 New York City Police Department Noise - Street/Sidewalk
16 New York City Police Department Noise - Street/Sidewalk
17 New York City Police Department Noise - Street/Sidewalk
18 New York City Police Department Noise - Park
19 New York City Police Department Noise - Street/Sidewalk
20 New York City Police Department Noise - Street/Sidewalk
21 New York City Police Department Noise - Vehicle
22 New York City Police Department Noise - Street/Sidewalk
23 New York City Police Department Noise - Street/Sidewalk
24 New York City Police Department Noise - Park
25 New York City Police Department Noise - Commercial
26 New York City Police Department Noise - Street/Sidewalk
27 New York City Police Department Noise - Street/Sidewalk
28 New York City Police Department Noise - Street/Sidewalk
29 New York City Police Department Noise - Street/Sidewalk
... ... ...
37585 New York City Police Department Noise - Commercial
37586 New York City Police Department Noise - Vehicle
37587 New York City Police Department Noise - Commercial
37588 New York City Police Department Noise - Street/Sidewalk
37589 New York City Police Department Noise - Street/Sidewalk
37590 New York City Police Department Noise - Vehicle
37591 New York City Police Department Noise - Street/Sidewalk
37592 New York City Police Department Noise - Vehicle
37593 New York City Police Department Noise - Street/Sidewalk
37594 New York City Police Department Noise - Commercial
37595 New York City Police Department Noise - Commercial
37596 New York City Police Department Noise - Park
37597 New York City Police Department Noise - Commercial
37598 New York City Police Department Noise - Street/Sidewalk
37599 New York City Police Department Noise - Street/Sidewalk
37600 New York City Police Department Noise - Street/Sidewalk
37601 New York City Police Department Noise - Street/Sidewalk
37602 New York City Police Department Noise - Street/Sidewalk
37603 New York City Police Department Noise - Commercial
37604 New York City Police Department Noise - Commercial
37605 New York City Police Department Noise - Street/Sidewalk
37606 New York City Police Department Noise - Vehicle
37607 New York City Police Department Noise - Street/Sidewalk
37608 New York City Police Department Noise - Street/Sidewalk
37609 New York City Police Department Noise - Commercial
37610 New York City Police Department Noise - Commercial
37611 New York City Police Department Noise - Street/Sidewalk
37612 New York City Police Department Noise - Vehicle
37613 New York City Police Department Noise - Vehicle
37614 New York City Police Department Noise - Commercial
descriptor location_type incident_zip \
0 Loud Music/Party Street/Sidewalk 11222
1 Car/Truck Music Street/Sidewalk 11234
2 Loud Music/Party Club/Bar/Restaurant 10002
3 Loud Music/Party Street/Sidewalk 10033
4 Loud Music/Party Street/Sidewalk 11217
5 Car/Truck Music Street/Sidewalk 10034
6 Car/Truck Music Street/Sidewalk 10463
7 Loud Music/Party Club/Bar/Restaurant 10009
8 Loud Music/Party Park/Playground 10473
9 Loud Music/Party Store/Commercial 11216
10 Loud Music/Party Street/Sidewalk 10473
11 Car/Truck Music Street/Sidewalk 10040
12 Loud Music/Party Street/Sidewalk 11411
13 Loud Music/Party Street/Sidewalk 10452
14 Loud Music/Party Street/Sidewalk 10465
15 Loud Music/Party Street/Sidewalk 10032
16 Loud Music/Party Street/Sidewalk 10451
17 Loud Talking Street/Sidewalk 10469
18 Loud Talking Park/Playground 11215
19 Loud Music/Party Street/Sidewalk 10034
20 Loud Music/Party Street/Sidewalk 11225
21 Engine Idling Street/Sidewalk 10304
22 Loud Music/Party Street/Sidewalk 11225
23 Loud Music/Party Street/Sidewalk 10040
24 Loud Music/Party Park/Playground 10452
25 Loud Music/Party Club/Bar/Restaurant 11233
26 Loud Music/Party Street/Sidewalk 10002
27 Loud Music/Party Street/Sidewalk 11236
28 Loud Music/Party Street/Sidewalk 10034
29 Loud Music/Party Street/Sidewalk 10473
... ... ... ...
37585 Loud Music/Party Club/Bar/Restaurant 11004
37586 Car/Truck Horn Street/Sidewalk 11249
37587 Loud Music/Party Club/Bar/Restaurant 10028
37588 Loud Music/Party Street/Sidewalk 11233
37589 Loud Talking Street/Sidewalk 10030
37590 Car/Truck Music Street/Sidewalk 10451
37591 Loud Music/Party Street/Sidewalk 10026
37592 Car/Truck Music Street/Sidewalk 10025
37593 Loud Talking Street/Sidewalk 10468
37594 Loud Music/Party Club/Bar/Restaurant 11237
37595 Loud Music/Party Club/Bar/Restaurant 11103
37596 Loud Music/Party Park/Playground 10027
37597 Loud Music/Party Club/Bar/Restaurant 11432
37598 Loud Music/Party Street/Sidewalk 10026
37599 Loud Talking Street/Sidewalk 11238
37600 Loud Music/Party Street/Sidewalk 10040
37601 Loud Music/Party Street/Sidewalk 10455
37602 Loud Talking Street/Sidewalk 11218
37603 Loud Music/Party Store/Commercial 11237
37604 Loud Music/Party Club/Bar/Restaurant 11231
37605 Loud Music/Party Street/Sidewalk 10032
37606 Car/Truck Horn Street/Sidewalk 10022
37607 Loud Music/Party Street/Sidewalk 10455
37608 Loud Talking Street/Sidewalk 11226
37609 Loud Music/Party Store/Commercial 10011
37610 Loud Music/Party Club/Bar/Restaurant 11216
37611 Loud Talking Street/Sidewalk 11211
37612 Car/Truck Horn Street/Sidewalk 11208
37613 Car/Truck Horn Street/Sidewalk 10065
37614 Loud Music/Party Store/Commercial 11249
incident_address ... \
0 200 KINGSLAND AVENUE ...
1 FLATLANDS AVENUE ...
2 161 LUDLOW STREET ...
3 624 WEST 182 STREET ...
4 525 DEAN STREET ...
5 None ...
6 3815 PUTNAM AVENUE WEST ...
7 16 FIRST AVENUE ...
8 None ...
9 665 FRANKLIN AVENUE ...
10 None ...
11 ST NICHOLAS AVENUE ...
12 None ...
13 158 EAST 164 STREET ...
14 3085 WISSMAN AVENUE ...
15 516 WEST 167 STREET ...
16 None ...
17 3480 CORSA AVENUE ...
18 None ...
19 VERMILYEA AVENUE ...
20 991 PRESIDENT STREET ...
21 28 THEATER LANE ...
22 None ...
23 1 SHERMAN AVE ...
24 975 WALTON AVENUE ...
25 370 LEWIS AVENUE ...
26 170 LUDLOW STREET ...
27 None ...
28 None ...
29 825 MORRISON AVE ...
... ... ...
37585 83-47 258 STREET ...
37586 91 BROADWAY ...
37587 1640 2 AVENUE ...
37588 1621 EASTERN PARKWAY ...
37589 301-319 WEST 134TH STREET ...
37590 None ...
37591 240 WEST 112 STREET ...
37592 None ...
37593 2505 AQUEDUCT AVENUE WEST ...
37594 1495 DEKALB AVENUE ...
37595 32-72 STEINWAY STREET ...
37596 426 ST NICHOLAS AVENUE ...
37597 172-11 HILLSIDE AVENUE ...
37598 None ...
37599 90 DOWNING STREET ...
37600 None ...
37601 630 JACKSON AVENUE ...
37602 OCEAN PKWY ...
37603 None ...
37604 449 COURT STREET ...
37605 600 WEST 164 STREET ...
37606 None ...
37607 625 JACKSON AVENUE ...
37608 1823-1999 NEW YORK 27 ...
37609 51 W 8TH ST ...
37610 608 NOSTRAND AVENUE ...
37611 None ...
37612 679 ELDERT LANE ...
37613 None ...
37614 140 GRAND STREET ...
bridge_highway_name bridge_highway_direction road_ramp \
0 None None None
1 None None None
2 None None None
3 None None None
4 None None None
5 None None None
6 None None None
7 None None None
8 None None None
9 None None None
10 None None None
11 None None None
12 None None None
13 None None None
14 None None None
15 None None None
16 None None None
17 None None None
18 None None None
19 None None None
20 None None None
21 None None None
22 None None None
23 None None None
24 None None None
25 None None None
26 None None None
27 None None None
28 None None None
29 None None None
... ... ... ...
37585 None None None
37586 None None None
37587 None None None
37588 None None None
37589 None None None
37590 None None None
37591 None None None
37592 None None None
37593 None None None
37594 None None None
37595 None None None
37596 None None None
37597 None None None
37598 None None None
37599 None None None
37600 None None None
37601 None None None
37602 None None None
37603 None None None
37604 None None None
37605 None None None
37606 None None None
37607 None None None
37608 None None None
37609 None None None
37610 None None None
37611 None None None
37612 None None None
37613 None None None
37614 None None None
bridge_highway_segment garage_lot_name ferry_direction \
0 None None None
1 None None None
2 None None None
3 None None None
4 None None None
5 None None None
6 None None None
7 None None None
8 None None None
9 None None None
10 None None None
11 None None None
12 None None None
13 None None None
14 None None None
15 None None None
16 None None None
17 None None None
18 None None None
19 None None None
20 None None None
21 None None None
22 None None None
23 None None None
24 None None None
25 None None None
26 None None None
27 None None None
28 None None None
29 None None None
... ... ... ...
37585 None None None
37586 None None None
37587 None None None
37588 None None None
37589 None None None
37590 None None None
37591 None None None
37592 None None None
37593 None None None
37594 None None None
37595 None None None
37596 None None None
37597 None None None
37598 None None None
37599 None None None
37600 None None None
37601 None None None
37602 None None None
37603 None None None
37604 None None None
37605 None None None
37606 None None None
37607 None None None
37608 None None None
37609 None None None
37610 None None None
37611 None None None
37612 None None None
37613 None None None
37614 None None None
ferry_terminal_name latitude longitude \
0 None 40.7238883 -73.94134889
1 None 40.61948901 -73.93805105
2 None 40.72141034 -73.98769444
3 None 40.85016672 -73.93397221
4 None 40.68120794 -73.97277535
5 None 40.86727603 -73.91921472
6 None 40.88476278 -73.89873507
7 None 40.72349367 -73.98825325
8 None 40.82015108 -73.87246957
9 None 40.67570219 -73.95591276
10 None 40.81579898 -73.87092805
11 None 40.85399617 -73.93031384
12 None 40.69513844 -73.74013764
13 None 40.82988679 -73.92195217
14 None 40.82012317 -73.81028322
15 None 40.83958815 -73.93807016
16 None 40.8233848 -73.91833943
17 None 40.87789192 -73.84790671
18 None 40.67276747 -73.98670844
19 None 40.86732269 -73.91921467
20 None 40.66909959 -73.95953993
21 None 40.61396351 -74.08008972
22 None 40.66964121 -73.96219275
23 None 40.86201826 -73.92920279
24 None 40.8292479 -73.92289242
25 None 40.68293027 -73.93505
26 None 40.721586 -73.98758257
27 None 40.64671931 -73.90818457
28 None 40.86727603 -73.91921472
29 None 40.82059654 -73.87320938
... ... ... ...
37585 None 40.7378916 -73.71046402
37586 None 40.71045689 -73.96519228
37587 None 40.77729237 -73.95215543
37588 None 40.67170174 -73.91457415
37589 None 40.81605416 -73.94751771
37590 None 40.82995788 -73.93008607
37591 None 40.80133776 -73.9560246
37592 None 40.79398146 -73.97228141
37593 None 40.86519344 -73.90228989
37594 None 40.70169627 -73.92181373
37595 None 40.75729856 -73.92037322
37596 None 40.81455084 -73.94929632
37597 None 40.7118486 -73.78986498
37598 None 40.80509759 -73.95487345
37599 None 40.68334267 -73.9608365
37600 None 40.86215028 -73.92965094
37601 None 40.81463762 -73.9077506
37602 None 40.64079951 -73.97369626
37603 None 40.70183179 -73.91932144
37604 None 40.67775273 -73.99811085
37605 None 40.83856918 -73.94204647
37606 None 40.7596199 -73.9619648
37607 None 40.81535455 -73.90844325
37608 None 40.65184682 -73.96224247
37609 None 40.73342479 -73.99863608
37610 None 40.67780765 -73.94976442
37611 None 40.70889971 -73.95471855
37612 None 40.67341771 -73.86378447
37613 None 40.76210601 -73.96013681
37614 None 40.71469119 -73.96244134
location
0 (40.723888303549415, -73.94134888943505)
1 (40.61948901090983, -73.93805104516916)
2 (40.72141034382407, -73.98769444021134)
3 (40.85016671877659, -73.93397220795968)
4 (40.68120794066068, -73.97277535440028)
5 (40.867276028886835, -73.9192147239671)
6 (40.88476278238543, -73.89873506548597)
7 (40.72349367369547, -73.98825325266078)
8 (40.820151079334636, -73.87246956983442)
9 (40.675702188086944, -73.95591276456229)
10 (40.81579897978756, -73.87092804807793)
11 (40.8539961736313, -73.93031384051778)
12 (40.695138441616756, -73.74013763661145)
13 (40.82988678524287, -73.92195217298081)
14 (40.8201231710228, -73.81028321862068)
15 (40.839588145100706, -73.93807016387643)
16 (40.82338479560645, -73.91833943226857)
17 (40.87789192345351, -73.84790671078991)
18 (40.67276746813732, -73.98670844073)
19 (40.86732268869463, -73.91921466728289)
20 (40.66909958968033, -73.95953993434152)
21 (40.6139635127871, -74.08008972068842)
22 (40.66964121313612, -73.96219274656046)
23 (40.862018258681125, -73.92920278665066)
24 (40.829247899200524, -73.92289242169471)
25 (40.68293027183319, -73.93504999741602)
26 (40.7215859963632, -73.98758257155433)
27 (40.64671930675259, -73.90818457082274)
28 (40.867276028886835, -73.9192147239671)
29 (40.82059653996134, -73.87320937939411)
... ...
37585 (40.73789159992381, -73.71046402051948)
37586 (40.71045688892934, -73.96519227754882)
37587 (40.77729236881097, -73.95215543230414)
37588 (40.67170174011723, -73.91457415354401)
37589 (40.81605415517306, -73.94751770737254)
37590 (40.829957876314346, -73.93008607198803)
37591 (40.80133776158354, -73.95602459997275)
37592 (40.79398145623041, -73.97228140520521)
37593 (40.865193436175176, -73.90228989262104)
37594 (40.70169626675707, -73.92181372712216)
37595 (40.75729855854012, -73.92037322099308)
37596 (40.814550844132924, -73.9492963216322)
37597 (40.71184860073022, -73.78986498358812)
37598 (40.80509759117871, -73.95487345326087)
37599 (40.683342674459816, -73.9608365021689)
37600 (40.86215027835525, -73.92965093665484)
37601 (40.81463762219387, -73.90775060332443)
37602 (40.64079951115844, -73.97369625738399)
37603 (40.7018317851356, -73.91932144034594)
37604 (40.677752731414074, -73.99811084956158)
37605 (40.83856917947116, -73.94204647344907)
37606 (40.759619895228845, -73.96196479954183)
37607 (40.81535454669349, -73.90844324894069)
37608 (40.6518468223244, -73.96224247276845)
37609 (40.733424791922275, -73.99863607731642)
37610 (40.67780765486066, -73.94976441731157)
37611 (40.708899709495455, -73.9547185546482)
37612 (40.67341770952538, -73.86378446869332)
37613 (40.76210601492712, -73.96013681333878)
37614 (40.71469119215372, -73.96244134390749)
[37615 rows x 53 columns]>
In [7]:
df['time open'] = df['closed_date'] - df['created_date']
Noise open
In [8]:
df['time open'].describe()
Out[8]:
count 37615
mean 0 days 07:44:22.532500
std 1 days 12:06:09.012799
min 0 days 00:01:00
25% 0 days 00:54:00
50% 0 days 02:02:00
75% 0 days 04:15:00
max 48 days 05:45:00
Name: time open, dtype: object
DOT OPEN
Findings: Noise complaints close a lot sooner than DOT complaints.
In [ ]:
Content source: ledeprogram/algorithms
Similar notebooks: