In [2]:
import pg8000
import pandas as pd
In [4]:
conn = pg8000.connect(user='dot_student', host='training.c1erymiua9dx.us-east-1.rds.amazonaws.com', port=5432, database='training', password='qgis')
df = pd.read_sql("select * from noise_311", conn)
In [6]:
df.head()
Out[6]:
b'unique_key'
b'created_date'
b'closed_date'
b'agency'
b'agency_name'
b'complaint_type'
b'descriptor'
b'location_type'
b'incident_zip'
b'incident_address'
...
b'bridge_highway_name'
b'bridge_highway_direction'
b'road_ramp'
b'bridge_highway_segment'
b'garage_lot_name'
b'ferry_direction'
b'ferry_terminal_name'
b'latitude'
b'longitude'
b'location'
0
28792167
2014-08-31 23:59:00
2014-09-01 03:52:00
NYPD
New York City Police Department
Noise - Street/Sidewalk
Loud Music/Party
Street/Sidewalk
11222
200 KINGSLAND AVENUE
...
None
None
None
None
None
None
None
40.723888
-73.941349
(40.723888303549415, -73.94134888943505)
1
28789088
2014-08-31 23:56:00
2014-09-01 06:17:00
NYPD
New York City Police Department
Noise - Vehicle
Car/Truck Music
Street/Sidewalk
11234
FLATLANDS AVENUE
...
None
None
None
None
None
None
None
40.619489
-73.938051
(40.61948901090983, -73.93805104516916)
2
28791854
2014-08-31 23:54:00
2014-09-01 01:29:00
NYPD
New York City Police Department
Noise - Commercial
Loud Music/Party
Club/Bar/Restaurant
10002
161 LUDLOW STREET
...
None
None
None
None
None
None
None
40.721410
-73.987694
(40.72141034382407, -73.98769444021134)
3
28789936
2014-08-31 23:52:00
2014-09-01 02:53:00
NYPD
New York City Police Department
Noise - Street/Sidewalk
Loud Music/Party
Street/Sidewalk
10033
624 WEST 182 STREET
...
None
None
None
None
None
None
None
40.850167
-73.933972
(40.85016671877659, -73.93397220795968)
4
28789931
2014-08-31 23:47:00
2014-09-01 01:06:00
NYPD
New York City Police Department
Noise - Street/Sidewalk
Loud Music/Party
Street/Sidewalk
11217
525 DEAN STREET
...
None
None
None
None
None
None
None
40.681208
-73.972775
(40.68120794066068, -73.97277535440028)
5 rows × 53 columns
In [17]:
df.columns
Out[17]:
Index([ b'unique_key', b'created_date',
b'closed_date', b'agency',
b'agency_name', b'complaint_type',
b'descriptor', b'location_type',
b'incident_zip', b'incident_address',
b'street_name', b'cross_street_1',
b'cross_street_2', b'intersection_street_1',
b'intersection_street_2', b'address_type',
b'city', b'landmark',
b'facility_type', b'status',
b'due_date', b'resolution_description',
b'resolution_action_updated_date', b'community_board',
b'borough', b'x_coordinate',
b'y_coordinate', b'park_facility_name',
b'park_borough', b'school_name',
b'school_number', b'school_region',
b'school_code', b'school_phone_number',
b'school_address', b'school_city',
b'school_state', b'school_zip',
b'school_not_found', b'school_or_citywide_complaint',
b'vehicle_type', b'taxi_company_borough',
b'taxi_pick_up_location', b'bridge_highway_name',
b'bridge_highway_direction', b'road_ramp',
b'bridge_highway_segment', b'garage_lot_name',
b'ferry_direction', b'ferry_terminal_name',
b'latitude', b'longitude',
b'location'],
dtype='object')
In [18]:
df['left_open'] = df["b'closed_date'"] - df["b'created_date'"]
---------------------------------------------------------------------------
KeyError Traceback (most recent call last)
/Users/honjingyi/.virtualenvs/data-analysis/lib/python3.5/site-packages/pandas/indexes/base.py in get_loc(self, key, method, tolerance)
1944 try:
-> 1945 return self._engine.get_loc(key)
1946 except KeyError:
pandas/index.pyx in pandas.index.IndexEngine.get_loc (pandas/index.c:4154)()
pandas/index.pyx in pandas.index.IndexEngine.get_loc (pandas/index.c:4018)()
pandas/hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12368)()
pandas/hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12322)()
KeyError: "b'closed_date'"
During handling of the above exception, another exception occurred:
KeyError Traceback (most recent call last)
<ipython-input-18-799fcc51e3ca> in <module>()
----> 1 df['left_open'] = df["b'closed_date'"] - df["b'created_date'"]
/Users/honjingyi/.virtualenvs/data-analysis/lib/python3.5/site-packages/pandas/core/frame.py in __getitem__(self, key)
1995 return self._getitem_multilevel(key)
1996 else:
-> 1997 return self._getitem_column(key)
1998
1999 def _getitem_column(self, key):
/Users/honjingyi/.virtualenvs/data-analysis/lib/python3.5/site-packages/pandas/core/frame.py in _getitem_column(self, key)
2002 # get column
2003 if self.columns.is_unique:
-> 2004 return self._get_item_cache(key)
2005
2006 # duplicate columns & possible reduce dimensionality
/Users/honjingyi/.virtualenvs/data-analysis/lib/python3.5/site-packages/pandas/core/generic.py in _get_item_cache(self, item)
1348 res = cache.get(item)
1349 if res is None:
-> 1350 values = self._data.get(item)
1351 res = self._box_item_values(item, values)
1352 cache[item] = res
/Users/honjingyi/.virtualenvs/data-analysis/lib/python3.5/site-packages/pandas/core/internals.py in get(self, item, fastpath)
3288
3289 if not isnull(item):
-> 3290 loc = self.items.get_loc(item)
3291 else:
3292 indexer = np.arange(len(self.items))[isnull(self.items)]
/Users/honjingyi/.virtualenvs/data-analysis/lib/python3.5/site-packages/pandas/indexes/base.py in get_loc(self, key, method, tolerance)
1945 return self._engine.get_loc(key)
1946 except KeyError:
-> 1947 return self._engine.get_loc(self._maybe_cast_indexer(key))
1948
1949 indexer = self.get_indexer([key], method=method, tolerance=tolerance)
pandas/index.pyx in pandas.index.IndexEngine.get_loc (pandas/index.c:4154)()
pandas/index.pyx in pandas.index.IndexEngine.get_loc (pandas/index.c:4018)()
pandas/hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12368)()
pandas/hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12322)()
KeyError: "b'closed_date'"
In [ ]:
Content source: ledeprogram/algorithms
Similar notebooks: