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