notebook.community

Edit and run 





In [1]:



    


import pandas as pd
import pg8000 as pg
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
%matplotlib inline
from datetime import datetime, timedelta



    











In [4]:



    


engine = create_engine('postgresql+pg8000://dot_student:qgis@training.c1erymiua9dx.us-east-1.rds.amazonaws.com:5432/training')



    











In [5]:



    


con=engine.raw_connection()



    











In [6]:



    


df=pd.read_sql_query('select * from noise_311', con=con)



    











In [7]:



    


df.head()



    


















    
Out[7]:










  
    

      
      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 [8]:



    


for bytes_name in df.columns:
    df.rename(columns = {bytes_name:bytes_name.decode('utf-8')}, inplace = True)



    











In [9]:



    


df.head()



    


















    
Out[9]:










  
    

      
      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
      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 [10]:



    


len(df)



    


















    
Out[10]:








37615

















In [13]:



    


df['open_duration']=df['closed_date']-df['created_date']



    











In [15]:



    


df['open_duration'].head(5)



    


















    
Out[15]:








0   03:53:00
1   06:21:00
2   01:35:00
3   03:01:00
4   01:19:00
Name: open_duration, dtype: timedelta64[ns]

















In [19]:



    


df.groupby(by='agency').mean()



    


















    
Out[19]:










  
    

      
      unique_key
      x_coordinate
      y_coordinate
      latitude
      longitude
    

    

      agency
      
      
      
      
      
    

  
  
    

      DEP
      28479030
      1.002513e+06
      204514.087172
      40.727968
      -73.934065
    

    

      DSNY
      28441464
      1.005781e+06
      205684.000000
      40.731180
      -73.922281
    

    

      EDC
      28463637
      9.949129e+05
      208390.951768
      40.738636
      -73.961490
    

    

      NYPD
      28480302
      1.002381e+06
      213668.653246
      40.753113
      -73.934536
    

  




















In [20]:



    


df.index=df['created_date']



    











In [21]:



    


df.head(5)



    


















    
Out[21]:










  
    

      
      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
      open_duration
    

    

      created_date
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
    

  
  
    

      2014-08-31 23:59:00
      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
      40.723888
      -73.941349
      (40.723888303549415, -73.94134888943505)
      03:53:00
    

    

      2014-08-31 23:56:00
      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
      40.619489
      -73.938051
      (40.61948901090983, -73.93805104516916)
      06:21:00
    

    

      2014-08-31 23:54:00
      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
      40.721410
      -73.987694
      (40.72141034382407, -73.98769444021134)
      01:35:00
    

    

      2014-08-31 23:52:00
      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
      40.850167
      -73.933972
      (40.85016671877659, -73.93397220795968)
      03:01:00
    

    

      2014-08-31 23:47:00
      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
      40.681208
      -73.972775
      (40.68120794066068, -73.97277535440028)
      01:19:00
    

  



5 rows × 54 columns



















In [36]:



    


df['open_duration_sec']=.total_seconds()



    


















    
Out[36]:








13980.0

















In [ ]:



    


lambda

Content source: ledeprogram/algorithms

Similar notebooks:

notebook.community | gallery | about