notebook.community

Edit and run 





In [6]:



    


import pandas as pd
import os



    











In [7]:



    


files = os.listdir('../raw_data')



    











In [8]:



    


data = {f: pd.read_excel('../raw_data/'+f) for f in files }



    











In [10]:



    


parking = data['Hack4Cause_Parking Citations FY 2008.xls']
parking.columns



    


















    
Out[10]:








Index(['Date', 'Make', 'Color', 'Amount Due', 'Violation', 'Location', 'Meter',
       'Officer Comment'],
      dtype='object')

















In [15]:



    


clean = parking.dropna(how='all')



    


















    
Out[15]:










  
    

      
      Date
      Make
      Color
      Amount Due
      Violation
      Location
      Meter
      Officer Comment
    

  
  
    

      0
      2007-07-01
      ACUR
      SIL
      0.0
      RESTRICTED BY SIGN
      LOT 07  - EUGENE AIRPORT
      NaN
      TERMINAL LOADING / UNLOADING
    

    

      2
      2007-07-02
      SAAB
      BLK
      0.0
      METER VIOLATION
      E/S KINCAID - 14TH TO 13TH
      227-67
      NO TIME ON METER
    

    

      4
      2007-07-02
      SAAB
      BLK
      0.0
      LEVEL 4
      E/S KINCAID - 14TH TO 13TH
      NaN
      NaN
    

    

      6
      2007-07-02
      VOLV
      WHT
      0.0
      METER VIOLATION
      N/S 13TH - ALDER TO KINCAID
      214-42
      NO TIME ON METER
    

    

      8
      2007-07-02
      TOYT
      WHT
      0.0
      METER VIOLATION
      ALDER ST 13TH AVE 12TH A
      214 52
      NO TIME ON METER
    

    

      10
      2007-07-02
      LEXS
      GRY
      24.0
      METER VIOLATION
      KINCAID ST 12TH AVE 13TH
      214 15
      NO TIME ON METER
    

    

      12
      2007-07-02
      TOYT
      GRY
      0.0
      METER VIOLATION
      13TH AVE KINCAID ST ALDE
      226 6
      NO TIME ON METER
    

    

      14
      2007-07-02
      DODG
      BLU
      0.0
      WRONG SIDE PARKING
      GARDEN AVE VILLARD ST MO
      NaN
      MUST PARK WITH FLOW OF TRAFFIC2 WAY
    

    

      16
      2007-07-02
      GMC
      BLK
      0.0
      METER VIOLATION
      KINCAID ST 11TH AVE 12TH
      209 21
      NO TIME ON METER
    

    

      18
      2007-07-02
      HOND
      GRY
      0.0
      METER VIOLATION
      13TH AVE COLUMBIA ST AGA
      234 4
      NO TIME ON METER
    

    

      20
      2007-07-02
      CHEV
      BLK
      0.0
      METER VIOLATION
      13TH AVE AGATE ST COLUMB
      222 46
      NO TIME ON METER
    

    

      22
      2007-07-02
      SUBA
      BLU
      0.0
      METER VIOLATION
      15TH AVE MOSS ST AGATE S
      256 6
      NO TIME ON METERNO DECAL
    

    

      24
      2007-07-02
      HOND
      MAR
      0.0
      METER VIOLATION
      ALDER ST 12TH AVE 11TH A
      209 51
      NO TIME ON METER
    

    

      26
      2007-07-02
      ACUR
      BLU
      0.0
      METER VIOLATION
      ALDER ST 12TH AVE 11TH A
      208 25
      NO TIME ON METER
    

    

      28
      2007-07-02
      FORD
      RED
      0.0
      METER VIOLATION
      ALDER ST 13TH AVE 12TH A
      214 53
      NO TIME ON METER
    

    

      30
      2007-07-02
      MAZD
      RED
      0.0
      METER VIOLATION
      CHARNELTON ST 8TH AVE 7T
      31 51
      NO TIME ON METER
    

    

      32
      2007-07-02
      HOND
      BLK
      0.0
      METER VIOLATION
      OLIVE ST BROADWAY ST 10T
      50 50
      NO TIME ON METER
    

    

      34
      2007-07-02
      HOND
      BLK
      24.0
      METER VIOLATION
      WILLAMETTE BROADWAY ST 8
      41 18
      NO TIME ON METER
    

    

      36
      2007-07-02
      TOYT
      GRN
      30.0
      RESTRICTED BY SIGN
      PEARL ST BROADWAY ST 8TH
      NaN
      COMMERCIAL MARKED VEHICLE ONLYNV
    

    

      38
      2007-07-02
      SUBA
      WHT
      0.0
      METER VIOLATION
      BROADWAY ST OAK ST PEARL
      43 34
      NO TIME ON METER
    

    

      40
      2007-07-02
      NISS
      GRN
      0.0
      METER VIOLATION
      BROADWAY ST LINCOLN ST C
      39 31
      NO TIME ON METER
    

    

      42
      2007-07-02
      TOYT
      BLU
      0.0
      METER VIOLATION
      CHARNELTON ST 8TH AVE 7T
      31 46
      NO TIME ON METER
    

    

      44
      2007-07-02
      CHEV
      WHT
      0.0
      METER VIOLATION
      LOT 31 ALLEY
      39 98
      NO TIME ON METER
    

    

      46
      2007-07-02
      CHEV
      GRY
      0.0
      METER VIOLATION
      LOT 21
      SP.81
      NO TIME ON METER
    

    

      48
      2007-07-02
      VOLK
      BLU
      0.0
      METER VIOLATION
      LOT 31
      SP.49
      NO TIME ON METER
    

    

      50
      2007-07-02
      FORD
      GRN
      0.0
      METER VIOLATION
      WEST PARK-8
      42-66
      NO TIME ON METER
    

    

      52
      2007-07-02
      FORD
      RED
      0.0
      METER VIOLATION
      SOUTH PARK-1E
      43-64
      NO TIME ON METER
    

    

      54
      2007-07-02
      TOYT
      BEI
      0.0
      METER VIOLATION
      8TH AVE OLIVE ST WILLAME
      41-12
      NO TIME ON METER
    

    

      56
      2007-07-02
      HOND
      BLU
      0.0
      METER VIOLATION
      SOUTH PARK-1E
      43-67
      NO TIME ON METER
    

    

      58
      2007-07-02
      ISUZ
      RED
      0.0
      METER VIOLATION
      OAK ST 6TH AVE 7TH AVE
      25-46
      NO TIME ON METER
    

    

      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
    

    

      65458
      2008-01-07
      SUBA
      RED
      0.0
      METER VIOLATION
      KINCAID ST 14TH AVE 13TH
      226-20
      NO TIME ON METER
    

    

      65460
      2008-01-07
      TOYT
      BLK
      0.0
      METER VIOLATION
      13TH AVE KINCAID ST ALDE
      226-10
      NO TIME ON METER
    

    

      65462
      2008-01-07
      FORD
      GRY
      24.0
      METER VIOLATION
      AGATE ST 17TH AVE 15TH A
      255-32
      NO TIME ON METER
    

    

      65464
      2008-01-07
      HOND
      BLU
      0.0
      RESTRICTED BY SIGN
      15TH AVE ALDER ST KINCAI
      NaN
      COMMERCIAL MARKED VEHICLE ONLYNO COM
    

    

      65466
      2008-01-07
      VOLK
      GRY
      0.0
      TIME LIMIT ZONE
      WALNUT ST 15TH AVE 17TH
      NaN
      NO PERMIT VISIBLECHALK MARK STILL VI
    

    

      65468
      2008-01-07
      DODG
      WHT
      0.0
      TIME LIMIT ZONE
      COLUMBIA ST 17TH AVE 19T
      NaN
      NO PERMIT VISIBLECHALK MARK STILL VI
    

    

      65470
      2008-01-07
      VOLK
      WHT
      0.0
      TIME LIMIT ZONE
      17TH AVE MOSS ST COLUMBI
      NaN
      NO PERMIT VISIBLECHALK MARK STILL VI
    

    

      65472
      2008-01-07
      TOYT
      BLK
      0.0
      TIME LIMIT ZONE
      17TH AVE VILLARD ST MOSS
      NaN
      NO PERMIT VISIBLECHALK MARK STILL VI
    

    

      65474
      2008-01-07
      SUBA
      BLU
      0.0
      TIME LIMIT ZONE
      VILLARD ST 15TH AVE 17TH
      NaN
      NO PERMIT VISIBLECHALK MARK STILL VI
    

    

      65476
      2008-01-07
      SUBA
      BLU
      0.0
      METER VIOLATION
      BROADWAY ST PEARL ST OAK
      52 2
      NO TIME ON METER
    

    

      65478
      2008-01-07
      CHEV
      GRY
      0.0
      METER VIOLATION
      LOT 15
      17
      NO TIME ON METER9.05  O.K.
    

    

      65480
      2008-01-07
      TOYT
      GRY
      0.0
      METER VIOLATION
      BROADWAY ST CHARNELTON S
      40 37
      NO TIME ON METER
    

    

      65482
      2008-01-07
      FORD
      WHT
      0.0
      TIME LIMIT ZONE
      ALLEY OVERPARK S
      NaN
      CHALK MARK STILL VISIBLE30 MINUTES O
    

    

      65484
      2008-01-07
      SUBA
      BLU
      0.0
      METER VIOLATION
      LOT 21
      51
      NO TIME ON METER
    

    

      65486
      2008-01-07
      ISUZ
      GRY
      0.0
      METER VIOLATION
      LINCOLN ST 10TH AVE 11TH
      56 17
      NO TIME ON METER
    

    

      65488
      2008-01-07
      TOYT
      WHT
      0.0
      METER VIOLATION
      10TH AVE OLIVE ST CHARNE
      58 13
      NO TIME ON METER
    

    

      65490
      2008-01-07
      FORD
      GRY
      0.0
      METER VIOLATION
      PEARL ST 14TH AVE 13TH A
      91 18
      NO TIME ON METER
    

    

      65492
      2008-01-07
      JEEP
      GRY
      0.0
      TIME LIMIT ZONE
      KINCAID ST 18TH AVE 19TH
      NaN
      CHALK MARK STILL VISIBLENO PERMIT VI
    

    

      65494
      2008-01-07
      HOND
      MAR
      0.0
      TIME LIMIT ZONE
      19TH AVE EMERALD ST AGAT
      NaN
      CHALK MARK STILL VISIBLE
    

    

      65496
      2008-01-07
      AUDI
      RED
      0.0
      TIME LIMIT ZONE
      ONYX ST 18TH AVE 19TH AV
      NaN
      CHALK MARK STILL VISIBLENO PERMIT VI
    

    

      65498
      2008-01-07
      HOND
      BLU
      0.0
      TIME LIMIT ZONE
      ONYX ST 18TH AVE 19TH AV
      NaN
      CHALK MARK STILL VISIBLENO PERMIT VI
    

    

      65500
      2008-01-07
      TOYT
      RED
      24.0
      TIME LIMIT ZONE
      ONYX ST 21ST AVE 20TH AV
      NaN
      CHALK MARK STILL VISIBLENO PERMIT VI
    

    

      65502
      2008-01-07
      DODG
      SIL
      0.0
      YELLOW ZONE
      14TH AVE KINCAID ST ALDE
      NaN
      NOT A PARKING SPACE
    

    

      65504
      2008-01-07
      HOND
      GRN
      0.0
      METER VIOLATION
      KINCAID ST 15TH AVE 14TH
      239-16
      NO TIME ON METER
    

    

      65506
      2008-01-07
      HOND
      GRN
      0.0
      RESTRICTED BY SIGN
      15TH AVE ALDER ST KINCAI
      NaN
      COMMERCIAL MARKED VEHICLE ONLYNO COM
    

    

      65508
      2008-01-07
      SUZU
      MAR
      0.0
      METER VIOLATION
      KINCAID ST END 15TH AVE
      250-20
      NO TIME ON METER
    

    

      65510
      2008-01-07
      COOP
      GRN
      0.0
      TIME LIMIT ZONE
      HARRIS ST 19TH AVE 20TH
      NaN
      CHALK MARK STILL VISIBLENO PERMIT VI
    

    

      65512
      2008-01-07
      FORD
      GRN
      0.0
      TIME LIMIT ZONE
      20TH AVE UNIVERSITY ST O
      NaN
      CHALK MARK STILL VISIBLENO PERMIT VI
    

    

      65514
      2008-01-07
      SUBA
      WHT
      0.0
      TIME LIMIT ZONE
      20TH AVE ONYX ST UNIVERS
      NaN
      CHALK MARK STILL VISIBLENO PERMIT VI
    

    

      65516
      2008-01-07
      SUBA
      GRY
      0.0
      METER VIOLATION
      18TH AVE HARRIS ST POTTE
      272-30
      NO TIME ON METER
    

  



32759 rows × 8 columns



















In [19]:



    


from sqlalchemy import create_engine
engine = create_engine("postgresql://cityview:testthis@localhost/cityview")
clean.to_sql(con=engine,name='parking_violations',if_exists='fail')
select v."Date",v."Make",v."Color",v."Amount Due", v."Violation", v."Location", v."Meter", m.meter_id, st\ _asgeojson(m.wkb_geometry) meter_point from parking_violations v join meter m on v."Meter"=m.meter_id order by v."Date", v."Meter"; 




In [ ]:

Content source: civiclee/Hack4Cause2017

Similar notebooks:

notebook.community | gallery | about