wassname/satellite_leak_detection
1
0
Fork 0
mirror of https://github.com/wassname/satellite_leak_detection.git synced 2026-09-11 12:43:43 +08:00
Code Issues Packages Projects Releases Wiki Activity
Files
master
satellite_leak_detection/notebooks/0_process_data/process_austin_leaks.ipynb
T
gitea 6ffe04a2da Draft
2017-05-01 13:58:40 +08:00

582 KiB
Raw Permalink Blame History

This notebook takes in a text file, works out the columns, timezone, and CRS, then outputs a standard geojson.

In [1]:
import uuid
import re, os
from path import Path
from pprint import pprint
from tqdm import tqdm

import pytz
import arrow 

import numpy as np
import scipy as sp
import matplotlib.pyplot as plt
import seaborn as sns
import pandas as pd
import geopandas as gpd


plt.rcParams['figure.figsize'] = (15, 5) # bigger plots
plt.style.use('fivethirtyeight')
%matplotlib inline
%precision 4
Out [1]:
'%.4f'

what are the columns?

It seems the we have 29 columns?

The top left 47486 match inspections https://services1.arcgis.com/PuB3FWUAxkScvfQy/ArcGIS/rest/services/LPI/FeatureServer/0

The bottom right match repairs https://services1.arcgis.com/PuB3FWUAxkScvfQy/ArcGIS/rest/services/Leaks_Repaired_Last_24hrs/FeatureServer/0

The combined columns that I can work out are below, the unknown just have numbers.

There is an overlap from city to Y but this is not enougth to join the events. Time might be however.

We are interested in the leak repairs.

In [2]:
columns = [
 'OBJECTID',
 'INSPFLAG',
 'PRI',
 'PROB',
 'PROBDTTM',
 'SCHEDDTTM',
 'SERVNO',
 'MAPNO',
 'STARTDTTM',
 'PROBCODE',
 'PROBDESC',
 'CITY',
 'PREDIR',
 'STNAME',
 'STNO',
 'STSUB',
 'SUFFIX',
 'ZIP',
 'ADDRKEY',
 'FullStreetName',
 'X',
 'Y',
 '22', # CMPLKEY (Complementary key?, INT)?, SERVER (nah thats 47486-627096, Objectid (no too high)?? 1913-1496485. 
 'QTYCALLS', # 1-120 mostly one, shared by both. PRob QTYCALLS. Quantity of calls?
 'INITDTTM', # initial date time
 'WONO', # work order number?
 'LOC', # needed to look at more example to determine this
 'DESCRIPT',
 'COMPDTTM' # This matches COMPDTTM from https://services1.arcgis.com/PuB3FWUAxkScvfQy/ArcGIS/rest/services/Leaks_Repaired_Last_24hrs/FeatureServer/0
]
In [ ]:
In [3]:
data_path = Path('../data/austin_leaks/PIR_Leaks2.txt')
In [4]:
# df = pd.read_csv(data_path, header=None ,names=columns, index_col=0)
df = pd.read_csv(data_path, header=None ,names=columns, index_col=0)
df
Out [4]:
/home/isisilon/.virtualenvs/py3syspck/lib/python3.4/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (1,2,3,4,5,7,8,9,10,24,26,27,28) have mixed types. Specify dtype option on import or set low_memory=False.
  interactivity=interactivity, compiler=compiler, result=result)
INSPFLAG PRI PROB PROBDTTM SCHEDDTTM SERVNO MAPNO STARTDTTM PROBCODE PROBDESC ... FullStreetName X Y 22 QTYCALLS INITDTTM WONO LOC DESCRIPT COMPDTTM
OBJECTID
1 Y 1 SERLK 7/3/2015 19:26:00 7/3/2015 19:28:00 566188.0 465Y 7/3/2015 19:47:00 SERLK LEAKING WATER SERVICE ... 11925 1/2 ALTERRA PKWY 3120697.53 10121089.21 NaN 1 NaN NaN NaN NaN NaN
2 Y 3 CCOLK 6/22/2007 12:06:00 6/22/2007 16:45:00 314762.0 615B 6/23/2007 3:41:00 CCOLK LEAKING/CHECK CITY CUT OFF ... 1305 E 4TH ST 3118411.37 10068599.12 NaN 1 NaN NaN NaN NaN NaN
3 Y 3 MTRLK 6/14/2006 14:15:00 7/3/2006 16:21:00 280152.0 585Y 7/4/2006 14:24:00 MTRLK LEAKING WATER METER ... 1400 E 4TH ST 3118802.75 10068659.00 NaN 2 NaN NaN NaN NaN NaN
4 Y 1 MTRLK 6/6/2011 17:02:00 6/6/2011 17:04:00 430378.0 585Y 6/6/2011 18:44:00 MTRLK LEAKING WATER METER ... 1400 E 4TH ST 3118802.75 10068659.00 NaN 1 NaN NaN NaN NaN NaN
5 Y 3 MTRLK 3/3/2005 16:06:00 3/8/2005 8:28:00 235202.0 614R 3/23/2005 15:13:00 MTRLK LEAKING WATER METER ... 2414 S 1ST ST 3109047.73 10061026.95 NaN 1 NaN NaN NaN NaN NaN
6 Y 1 MTRLK 4/18/2007 17:00:00 4/18/2007 17:07:00 309735.0 614Q 4/18/2007 18:43:00 MTRLK LEAKING WATER METER ... 2613 S 1ST ST 3108656.25 10059934.00 NaN 1 NaN NaN NaN NaN NaN
7 Y 1 MTRLK 3/8/2007 10:56:00 3/8/2007 11:01:00 306550.0 614Q 3/8/2007 13:33:00 MTRLK LEAKING WATER METER ... 2613 S 1ST ST 3108656.25 10059934.00 NaN 2 NaN NaN NaN NaN NaN
8 Y 3 MTRLK 7/7/2009 9:16:00 7/8/2009 7:45:00 373961.0 614U 7/8/2009 9:20:00 MTRLK LEAKING WATER METER ... 2617 S 1ST ST 3108615.50 10059798.00 NaN 1 NaN NaN NaN NaN NaN
9 Y 3 MTRLK 2/23/2009 8:24:00 2/23/2009 10:47:00 364175.0 614H 2/23/2009 21:12:00 MTRLK LEAKING WATER METER ... 1502 S 3RD ST 3109627.75 10064443.00 NaN 1 NaN NaN NaN NaN NaN
10 Y 3 MTRLK 12/20/2005 7:54:00 1/17/2006 10:35:00 263359.0 614M 1/17/2006 12:52:00 MTRLK LEAKING WATER METER ... 1602 S 3RD ST 3109574.75 10064134.00 NaN 2 NaN NaN NaN NaN NaN
11 Y 1 MTRLK 5/7/2016 9:38:00 5/7/2016 9:39:00 599121.0 615H 5/7/2016 9:49:00 MTRLK LEAKING WATER METER ... 2400 E CESAR CHAVEZ ST 3122372.19 10066137.84 NaN 1 NaN NaN NaN NaN NaN
12 Y 1 WMNLK 2/27/2015 6:48:00 2/27/2015 8:03:00 553829.0 615H 2/27/2015 8:51:00 WMNLK WATER MAIN LEAK ... 2400 E CESAR CHAVEZ ST 3122372.19 10066137.84 NaN 2 NaN NaN NaN NaN NaN
13 Y 1 WMNLK 7/10/2011 9:30:00 7/10/2011 9:30:00 432895.0 615G 7/10/2011 9:37:00 WMNLK WATER MAIN LEAK ... 2401 1/2 E CESAR CHAVEZ ST 3122478.96 10065983.00 NaN 1 NaN NaN NaN NaN NaN
14 Y 1 SERLK 4/24/2005 21:35:00 4/24/2005 21:40:00 240012.0 615H 4/24/2005 23:40:00 SERLK LEAKING WATER SERVICE ... 2411 E CESAR CHAVEZ ST 3122580.86 10065926.02 NaN 1 NaN NaN NaN NaN NaN
15 Y 1 WMNLK 8/26/2015 11:48:00 8/26/2015 11:51:00 572123.0 615H 8/26/2015 12:25:00 WMNLK WATER MAIN LEAK ... 2412 E CESAR CHAVEZ ST 3122502.45 10066098.04 NaN 1 NaN NaN NaN NaN NaN
16 Y 1 MTRLK 9/6/2013 17:13:00 9/6/2013 17:14:00 496919.0 615H 9/6/2013 19:05:00 MTRLK LEAKING WATER METER ... 2504 E CESAR CHAVEZ ST 3122825.23 10065984.09 NaN 1 NaN NaN NaN NaN NaN
17 Y 1 MTRLK 3/15/2007 12:29:00 3/15/2007 12:51:00 307176.0 615H 3/15/2007 14:06:00 MTRLK LEAKING WATER METER ... 2505 E CESAR CHAVEZ ST 3122775.00 10065870.00 NaN 3 NaN NaN NaN NaN NaN
18 Y 3 MTRLK 6/22/2005 19:31:00 6/24/2005 16:35:00 245815.0 615H 6/26/2005 22:23:00 MTRLK LEAKING WATER METER ... 2604 E CESAR CHAVEZ ST 3123230.75 10065822.00 NaN 2 NaN NaN NaN NaN NaN
19 Y 1 MTRLK 11/20/2005 17:22:00 11/20/2005 17:22:00 260441.0 673H 11/20/2005 21:00:00 MTRLK LEAKING WATER METER ... 8203 S 1ST ST 3098647.75 10038614.00 NaN 1 NaN NaN NaN NaN NaN
20 Y 1 MTRLK 7/5/2011 14:02:00 7/5/2011 14:04:00 432530.0 615H 7/5/2011 14:37:00 MTRLK LEAKING WATER METER ... 2501 E 2ND ST 3122805.00 10066222.00 NaN 1 NaN NaN NaN NaN NaN
21 Y 1 MTRLK 12/18/2006 15:10:00 12/18/2006 15:16:00 299030.0 615H 12/18/2006 17:08:00 MTRLK LEAKING WATER METER ... 2504 E 2ND ST 3122943.00 10066300.00 NaN 1 NaN NaN NaN NaN NaN
22 Y 1 MTRLK 9/27/2008 18:28:00 9/27/2008 18:29:00 351425.0 615H 9/27/2008 18:53:00 MTRLK LEAKING WATER METER ... 2507 E 2ND ST 3122948.00 10066172.00 NaN 1 NaN NaN NaN NaN NaN
23 Y 1 MTRLK 12/12/2011 9:28:00 12/12/2011 9:29:00 445632.0 615H 12/12/2011 9:47:00 MTRLK LEAKING WATER METER ... 2613 E 2ND ST 3123501.00 10065964.00 NaN 2 NaN NaN NaN NaN NaN
24 Y 1 MTRLK 1/5/2014 12:17:00 1/5/2014 12:18:00 509048.0 615H 1/5/2014 13:13:00 MTRLK LEAKING WATER METER ... 2700 E 2ND ST 3123653.00 10066051.00 NaN 1 NaN NaN NaN NaN NaN
25 Y 1 SERLK 11/20/2014 8:54:00 11/20/2014 8:56:00 542172.0 585X 11/20/2014 9:15:00 SERLK LEAKING WATER SERVICE ... 702 E 3RD ST 3116358.88 10069118.39 NaN 1 NaN NaN NaN NaN NaN
26 Y 1 SERLK 11/14/2009 9:34:00 11/14/2009 9:45:00 385181.0 585X 11/14/2009 9:56:00 SERLK LEAKING WATER SERVICE ... 903 E 3RD ST 3116972.75 10068756.00 NaN 1 NaN NaN NaN NaN NaN
27 Y 1 MTRLK 10/1/2011 13:13:00 10/1/2011 13:13:00 440169.0 615B 10/1/2011 13:51:00 MTRLK LEAKING WATER METER ... 1101 E 3RD ST 3117546.00 10068543.00 NaN 3 NaN NaN NaN NaN NaN
28 Y 3 MTRLK 8/27/2014 19:22:00 8/27/2014 19:30:00 533165.0 615B 8/28/2014 11:35:00 MTRLK LEAKING WATER METER ... 1101 E 3RD ST 3117546.00 10068543.00 NaN 1 NaN NaN NaN NaN NaN
29 Y 1 MTRLK 12/30/2005 10:00:00 12/30/2005 10:11:00 264315.0 585X 12/30/2005 12:30:00 MTRLK LEAKING WATER METER ... 1111 E 3RD ST 3117655.25 10068502.00 NaN 2 NaN NaN NaN NaN NaN
30 Y 1 MTRLK 5/23/2005 8:14:00 5/23/2005 8:14:00 242771.0 585X 5/23/2005 12:21:00 MTRLK LEAKING WATER METER ... 1112 E 3RD ST 3117687.50 10068694.00 NaN 1 NaN NaN NaN NaN NaN
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
70588 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 7212 MEADOR AVE 3128508.25 10093671.00 356219.0 1 2/3/2017 9:54:00 1812528.0 NaN WATER SERVICE LEAK 2/6/2017 14:24:00
70589 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 10308 GEORGIAN DR 3130865.75 10107812.00 414795.0 1 2/9/2017 19:06:00 1814606.0 NaN WATER SERVICE LEAK 2/10/2017 12:28:00
70590 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 8012 GREENSLOPE DR 3110552.25 10107403.00 306333.0 1 2/10/2017 13:20:00 1814722.0 NaN WATER SERVICE LEAK 2/10/2017 21:09:00
70591 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 6104 HYLAWN DR 3135834.75 10084787.00 871450.0 1 2/10/2017 14:48:00 1814736.0 NaN WATER MAIN LEAK 2/13/2017 21:06:00
70592 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 6421 WILLIAMS RIDGE WAY 3103191.00 10101877.00 1215766.0 1 2/3/2017 20:38:00 1812677.0 NaN WATER METER LEAK 2/6/2017 13:45:00
70593 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 2703 GREENLEE DR 3106564.00 10082323.00 88698.0 1 1/30/2017 13:10:00 1810930.0 NaN WATER METER LEAK 1/30/2017 17:15:00
70594 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 5316 ED BLUESTEIN BLVD SB 3138721.11 10079875.49 450316.0 1 2/1/2017 13:18:00 1811688.0 NaN WATER SERVICE LEAK 2/1/2017 14:45:00
70595 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 5209 MEADOW CREEK DR 3101614.00 10051034.00 1494819.0 1 2/1/2017 14:45:00 1812084.0 NaN WATER SERVICE LEAK 2/1/2017 22:56:00
70596 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 4017 VICTORY DR UNIT 252 3100105.50 10057854.00 501413.0 1 2/14/2017 21:07:00 1815701.0 GATE CODE: 1739 WATER SERVICE LEAK 2/15/2017 19:56:00
70597 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 4800 N LAMAR BLVD 3116911.46 10088781.19 1278289.0 1 2/2/2017 13:35:00 1812277.0 NaN WATER METER LEAK 2/3/2017 11:45:00
70598 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 4531 AVENUE F 3119239.25 10086123.00 29791.0 2 2/7/2017 17:44:00 1813823.0 NaN WTR SVC DAMAGED BY CITY CREWS 2/7/2017 17:44:00
70599 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 5101 SUMMERSET TRL 3081905.75 10055909.00 386973.0 1 2/2/2017 9:03:00 1812261.0 NaN WATER SERVICE LEAK 2/3/2017 12:11:00
70600 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 415 BASTROP HWY NB 3132059.00 10060629.88 943972.0 1 2/16/2017 12:52:00 1815766.0 NaN WATER MAIN LEAK 2/16/2017 19:07:00
70601 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 6120 FELIX AVE 3128710.75 10059473.00 315946.0 1 2/11/2017 22:57:00 1814809.0 NaN WATER SERVICE LEAK 2/12/2017 18:39:00
70602 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 1707 E 32ND ST 3122507.00 10077584.00 228104.0 1 1/10/2017 10:40:00 1806592.0 NaN WATER SERVICE LEAK 1/10/2017 17:06:00
70603 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 902 GARNER AVE 3106935.25 10067378.00 936565.0 1 2/3/2017 13:31:00 1812654.0 KERR AVE WATER MAIN LEAK 2/6/2017 16:00:00
70604 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 806 PARK BLVD UNIT 1 3120527.50 10083267.00 1108480.0 1 2/8/2017 11:58:00 1814007.0 NaN WATER METER LEAK 2/8/2017 20:00:00
70605 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 1904 CANONERO DR 3091718.50 10088531.00 1494757.0 1 1/31/2017 12:03:00 1811342.0 NaN WATER SERVICE LEAK 1/31/2017 14:48:00
70606 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 4301 SPEEDWAY 3118187.09 10084826.33 214418.0 1 2/6/2017 10:43:00 1812846.0 NaN WATER SERVICE LEAK 2/10/2017 14:42:00
70607 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 1011 W 10TH ST 3110967.00 10073208.00 1494754.0 2 1/31/2017 9:50:00 1811331.0 BETWEEN BAYLOR AND N LAMAR WATER SERVICE LEAK 1/31/2017 15:16:00
70608 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 6207 MORNING DEW DR 3081344.75 10056368.00 390620.0 2 1/27/2017 9:09:00 1810599.0 NaN WATER SERVICE LEAK 1/27/2017 15:09:00
70609 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 6207 MORNING DEW DR 3081344.75 10056368.00 390620.0 2 1/27/2017 9:09:00 1811380.0 NaN WATER SERVICE LEAK 1/27/2017 15:09:00
70610 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 1649 OHLEN RD 3121863.75 10105553.00 526992.0 1 2/9/2017 14:45:00 1814590.0 BANK OF AMERICA FINANCIAL CENTER WATER SERVICE LEAK 2/14/2017 3:13:00
70611 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 1416 ELM BROOK DR 3129251.00 10117681.00 1057527.0 1 2/9/2017 16:54:00 1814602.0 SWEARINGEN WATER MAIN LEAK 2/10/2017 1:31:00
70612 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 5728 REPUBLIC OF TEXAS BLVD 3084010.75 10064012.00 479011.0 3 2/9/2017 19:19:00 1814607.0 NaN WATER SERVICE LEAK 2/10/2017 22:41:00
70613 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 1500 BRENTWOOD ST 3117857.50 10096352.00 1194725.0 1 2/7/2017 12:51:00 1813689.0 NaN WATER METER LEAK 2/7/2017 15:19:00
70614 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 8500 KEARSARGE CV 3089442.50 10042473.00 405181.0 3 2/5/2017 12:32:00 1812801.0 NaN WATER SERVICE LEAK 2/6/2017 16:30:00
70615 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 1223 WEST LYNN ST 3109307.25 10075302.00 1493994.0 1 1/24/2017 8:38:00 1809358.0 13TH ST. WATER SERVICE LEAK 1/24/2017 22:45:00
70616 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 7220 FIREFLY DR 3113795.75 10035707.00 392294.0 1 1/24/2017 14:54:00 1809798.0 NaN WATER SERVICE LEAK 1/24/2017 19:40:00
70617 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 806 W 10TH ST 3112044.00 10073012.00 188213.0 1 2/8/2017 11:01:00 1813953.0 ON THE NORTH SIDE WATER SERVICE LEAK 2/8/2017 21:53:00

70617 rows × 28 columns

Transforming cols

In [5]:
# turn the american datestring (in austin timezone) into pandas utc datetime
def parse_dttm_b(s):
    if isinstance(s,str):
        return arrow.get(s,'M+/D+/YYYY H+:mm:ss',tzinfo='US/Central').astimezone(pytz.utc).isoformat()
    else:
        return s

DTTM_cols = [col for col in df.columns if col.endswith('DTTM')]
for col in tqdm(['PROBDTTM', 'SCHEDDTTM', 'STARTDTTM', 'INITDTTM', 'COMPDTTM']):
    df[col]=pd.to_datetime(df[col].apply(parse_dttm_b),utc=True)
df
Out [5]:
100%|██████████| 5/5 [00:56<00:00, 10.62s/it]
INSPFLAG PRI PROB PROBDTTM SCHEDDTTM SERVNO MAPNO STARTDTTM PROBCODE PROBDESC ... FullStreetName X Y 22 QTYCALLS INITDTTM WONO LOC DESCRIPT COMPDTTM
OBJECTID
1 Y 1 SERLK 2015-07-04 00:26:00 2015-07-04 00:28:00 566188.0 465Y 2015-07-04 00:47:00 SERLK LEAKING WATER SERVICE ... 11925 1/2 ALTERRA PKWY 3120697.53 10121089.21 NaN 1 NaT NaN NaN NaN NaT
2 Y 3 CCOLK 2007-06-22 17:06:00 2007-06-22 21:45:00 314762.0 615B 2007-06-23 08:41:00 CCOLK LEAKING/CHECK CITY CUT OFF ... 1305 E 4TH ST 3118411.37 10068599.12 NaN 1 NaT NaN NaN NaN NaT
3 Y 3 MTRLK 2006-06-14 19:15:00 2006-07-03 21:21:00 280152.0 585Y 2006-07-04 19:24:00 MTRLK LEAKING WATER METER ... 1400 E 4TH ST 3118802.75 10068659.00 NaN 2 NaT NaN NaN NaN NaT
4 Y 1 MTRLK 2011-06-06 22:02:00 2011-06-06 22:04:00 430378.0 585Y 2011-06-06 23:44:00 MTRLK LEAKING WATER METER ... 1400 E 4TH ST 3118802.75 10068659.00 NaN 1 NaT NaN NaN NaN NaT
5 Y 3 MTRLK 2005-03-03 22:06:00 2005-03-08 14:28:00 235202.0 614R 2005-03-23 21:13:00 MTRLK LEAKING WATER METER ... 2414 S 1ST ST 3109047.73 10061026.95 NaN 1 NaT NaN NaN NaN NaT
6 Y 1 MTRLK 2007-04-18 22:00:00 2007-04-18 22:07:00 309735.0 614Q 2007-04-18 23:43:00 MTRLK LEAKING WATER METER ... 2613 S 1ST ST 3108656.25 10059934.00 NaN 1 NaT NaN NaN NaN NaT
7 Y 1 MTRLK 2007-03-08 16:56:00 2007-03-08 17:01:00 306550.0 614Q 2007-03-08 19:33:00 MTRLK LEAKING WATER METER ... 2613 S 1ST ST 3108656.25 10059934.00 NaN 2 NaT NaN NaN NaN NaT
8 Y 3 MTRLK 2009-07-07 14:16:00 2009-07-08 12:45:00 373961.0 614U 2009-07-08 14:20:00 MTRLK LEAKING WATER METER ... 2617 S 1ST ST 3108615.50 10059798.00 NaN 1 NaT NaN NaN NaN NaT
9 Y 3 MTRLK 2009-02-23 14:24:00 2009-02-23 16:47:00 364175.0 614H 2009-02-24 03:12:00 MTRLK LEAKING WATER METER ... 1502 S 3RD ST 3109627.75 10064443.00 NaN 1 NaT NaN NaN NaN NaT
10 Y 3 MTRLK 2005-12-20 13:54:00 2006-01-17 16:35:00 263359.0 614M 2006-01-17 18:52:00 MTRLK LEAKING WATER METER ... 1602 S 3RD ST 3109574.75 10064134.00 NaN 2 NaT NaN NaN NaN NaT
11 Y 1 MTRLK 2016-05-07 14:38:00 2016-05-07 14:39:00 599121.0 615H 2016-05-07 14:49:00 MTRLK LEAKING WATER METER ... 2400 E CESAR CHAVEZ ST 3122372.19 10066137.84 NaN 1 NaT NaN NaN NaN NaT
12 Y 1 WMNLK 2015-02-27 12:48:00 2015-02-27 14:03:00 553829.0 615H 2015-02-27 14:51:00 WMNLK WATER MAIN LEAK ... 2400 E CESAR CHAVEZ ST 3122372.19 10066137.84 NaN 2 NaT NaN NaN NaN NaT
13 Y 1 WMNLK 2011-07-10 14:30:00 2011-07-10 14:30:00 432895.0 615G 2011-07-10 14:37:00 WMNLK WATER MAIN LEAK ... 2401 1/2 E CESAR CHAVEZ ST 3122478.96 10065983.00 NaN 1 NaT NaN NaN NaN NaT
14 Y 1 SERLK 2005-04-25 02:35:00 2005-04-25 02:40:00 240012.0 615H 2005-04-25 04:40:00 SERLK LEAKING WATER SERVICE ... 2411 E CESAR CHAVEZ ST 3122580.86 10065926.02 NaN 1 NaT NaN NaN NaN NaT
15 Y 1 WMNLK 2015-08-26 16:48:00 2015-08-26 16:51:00 572123.0 615H 2015-08-26 17:25:00 WMNLK WATER MAIN LEAK ... 2412 E CESAR CHAVEZ ST 3122502.45 10066098.04 NaN 1 NaT NaN NaN NaN NaT
16 Y 1 MTRLK 2013-09-06 22:13:00 2013-09-06 22:14:00 496919.0 615H 2013-09-07 00:05:00 MTRLK LEAKING WATER METER ... 2504 E CESAR CHAVEZ ST 3122825.23 10065984.09 NaN 1 NaT NaN NaN NaN NaT
17 Y 1 MTRLK 2007-03-15 17:29:00 2007-03-15 17:51:00 307176.0 615H 2007-03-15 19:06:00 MTRLK LEAKING WATER METER ... 2505 E CESAR CHAVEZ ST 3122775.00 10065870.00 NaN 3 NaT NaN NaN NaN NaT
18 Y 3 MTRLK 2005-06-23 00:31:00 2005-06-24 21:35:00 245815.0 615H 2005-06-27 03:23:00 MTRLK LEAKING WATER METER ... 2604 E CESAR CHAVEZ ST 3123230.75 10065822.00 NaN 2 NaT NaN NaN NaN NaT
19 Y 1 MTRLK 2005-11-20 23:22:00 2005-11-20 23:22:00 260441.0 673H 2005-11-21 03:00:00 MTRLK LEAKING WATER METER ... 8203 S 1ST ST 3098647.75 10038614.00 NaN 1 NaT NaN NaN NaN NaT
20 Y 1 MTRLK 2011-07-05 19:02:00 2011-07-05 19:04:00 432530.0 615H 2011-07-05 19:37:00 MTRLK LEAKING WATER METER ... 2501 E 2ND ST 3122805.00 10066222.00 NaN 1 NaT NaN NaN NaN NaT
21 Y 1 MTRLK 2006-12-18 21:10:00 2006-12-18 21:16:00 299030.0 615H 2006-12-18 23:08:00 MTRLK LEAKING WATER METER ... 2504 E 2ND ST 3122943.00 10066300.00 NaN 1 NaT NaN NaN NaN NaT
22 Y 1 MTRLK 2008-09-27 23:28:00 2008-09-27 23:29:00 351425.0 615H 2008-09-27 23:53:00 MTRLK LEAKING WATER METER ... 2507 E 2ND ST 3122948.00 10066172.00 NaN 1 NaT NaN NaN NaN NaT
23 Y 1 MTRLK 2011-12-12 15:28:00 2011-12-12 15:29:00 445632.0 615H 2011-12-12 15:47:00 MTRLK LEAKING WATER METER ... 2613 E 2ND ST 3123501.00 10065964.00 NaN 2 NaT NaN NaN NaN NaT
24 Y 1 MTRLK 2014-01-05 18:17:00 2014-01-05 18:18:00 509048.0 615H 2014-01-05 19:13:00 MTRLK LEAKING WATER METER ... 2700 E 2ND ST 3123653.00 10066051.00 NaN 1 NaT NaN NaN NaN NaT
25 Y 1 SERLK 2014-11-20 14:54:00 2014-11-20 14:56:00 542172.0 585X 2014-11-20 15:15:00 SERLK LEAKING WATER SERVICE ... 702 E 3RD ST 3116358.88 10069118.39 NaN 1 NaT NaN NaN NaN NaT
26 Y 1 SERLK 2009-11-14 15:34:00 2009-11-14 15:45:00 385181.0 585X 2009-11-14 15:56:00 SERLK LEAKING WATER SERVICE ... 903 E 3RD ST 3116972.75 10068756.00 NaN 1 NaT NaN NaN NaN NaT
27 Y 1 MTRLK 2011-10-01 18:13:00 2011-10-01 18:13:00 440169.0 615B 2011-10-01 18:51:00 MTRLK LEAKING WATER METER ... 1101 E 3RD ST 3117546.00 10068543.00 NaN 3 NaT NaN NaN NaN NaT
28 Y 3 MTRLK 2014-08-28 00:22:00 2014-08-28 00:30:00 533165.0 615B 2014-08-28 16:35:00 MTRLK LEAKING WATER METER ... 1101 E 3RD ST 3117546.00 10068543.00 NaN 1 NaT NaN NaN NaN NaT
29 Y 1 MTRLK 2005-12-30 16:00:00 2005-12-30 16:11:00 264315.0 585X 2005-12-30 18:30:00 MTRLK LEAKING WATER METER ... 1111 E 3RD ST 3117655.25 10068502.00 NaN 2 NaT NaN NaN NaN NaT
30 Y 1 MTRLK 2005-05-23 13:14:00 2005-05-23 13:14:00 242771.0 585X 2005-05-23 17:21:00 MTRLK LEAKING WATER METER ... 1112 E 3RD ST 3117687.50 10068694.00 NaN 1 NaT NaN NaN NaN NaT
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
70588 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 7212 MEADOR AVE 3128508.25 10093671.00 356219.0 1 2017-02-03 15:54:00 1812528.0 NaN WATER SERVICE LEAK 2017-02-06 20:24:00
70589 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10308 GEORGIAN DR 3130865.75 10107812.00 414795.0 1 2017-02-10 01:06:00 1814606.0 NaN WATER SERVICE LEAK 2017-02-10 18:28:00
70590 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 8012 GREENSLOPE DR 3110552.25 10107403.00 306333.0 1 2017-02-10 19:20:00 1814722.0 NaN WATER SERVICE LEAK 2017-02-11 03:09:00
70591 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 6104 HYLAWN DR 3135834.75 10084787.00 871450.0 1 2017-02-10 20:48:00 1814736.0 NaN WATER MAIN LEAK 2017-02-14 03:06:00
70592 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 6421 WILLIAMS RIDGE WAY 3103191.00 10101877.00 1215766.0 1 2017-02-04 02:38:00 1812677.0 NaN WATER METER LEAK 2017-02-06 19:45:00
70593 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 2703 GREENLEE DR 3106564.00 10082323.00 88698.0 1 2017-01-30 19:10:00 1810930.0 NaN WATER METER LEAK 2017-01-30 23:15:00
70594 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 5316 ED BLUESTEIN BLVD SB 3138721.11 10079875.49 450316.0 1 2017-02-01 19:18:00 1811688.0 NaN WATER SERVICE LEAK 2017-02-01 20:45:00
70595 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 5209 MEADOW CREEK DR 3101614.00 10051034.00 1494819.0 1 2017-02-01 20:45:00 1812084.0 NaN WATER SERVICE LEAK 2017-02-02 04:56:00
70596 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 4017 VICTORY DR UNIT 252 3100105.50 10057854.00 501413.0 1 2017-02-15 03:07:00 1815701.0 GATE CODE: 1739 WATER SERVICE LEAK 2017-02-16 01:56:00
70597 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 4800 N LAMAR BLVD 3116911.46 10088781.19 1278289.0 1 2017-02-02 19:35:00 1812277.0 NaN WATER METER LEAK 2017-02-03 17:45:00
70598 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 4531 AVENUE F 3119239.25 10086123.00 29791.0 2 2017-02-07 23:44:00 1813823.0 NaN WTR SVC DAMAGED BY CITY CREWS 2017-02-07 23:44:00
70599 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 5101 SUMMERSET TRL 3081905.75 10055909.00 386973.0 1 2017-02-02 15:03:00 1812261.0 NaN WATER SERVICE LEAK 2017-02-03 18:11:00
70600 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 415 BASTROP HWY NB 3132059.00 10060629.88 943972.0 1 2017-02-16 18:52:00 1815766.0 NaN WATER MAIN LEAK 2017-02-17 01:07:00
70601 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 6120 FELIX AVE 3128710.75 10059473.00 315946.0 1 2017-02-12 04:57:00 1814809.0 NaN WATER SERVICE LEAK 2017-02-13 00:39:00
70602 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 1707 E 32ND ST 3122507.00 10077584.00 228104.0 1 2017-01-10 16:40:00 1806592.0 NaN WATER SERVICE LEAK 2017-01-10 23:06:00
70603 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 902 GARNER AVE 3106935.25 10067378.00 936565.0 1 2017-02-03 19:31:00 1812654.0 KERR AVE WATER MAIN LEAK 2017-02-06 22:00:00
70604 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 806 PARK BLVD UNIT 1 3120527.50 10083267.00 1108480.0 1 2017-02-08 17:58:00 1814007.0 NaN WATER METER LEAK 2017-02-09 02:00:00
70605 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 1904 CANONERO DR 3091718.50 10088531.00 1494757.0 1 2017-01-31 18:03:00 1811342.0 NaN WATER SERVICE LEAK 2017-01-31 20:48:00
70606 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 4301 SPEEDWAY 3118187.09 10084826.33 214418.0 1 2017-02-06 16:43:00 1812846.0 NaN WATER SERVICE LEAK 2017-02-10 20:42:00
70607 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 1011 W 10TH ST 3110967.00 10073208.00 1494754.0 2 2017-01-31 15:50:00 1811331.0 BETWEEN BAYLOR AND N LAMAR WATER SERVICE LEAK 2017-01-31 21:16:00
70608 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 6207 MORNING DEW DR 3081344.75 10056368.00 390620.0 2 2017-01-27 15:09:00 1810599.0 NaN WATER SERVICE LEAK 2017-01-27 21:09:00
70609 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 6207 MORNING DEW DR 3081344.75 10056368.00 390620.0 2 2017-01-27 15:09:00 1811380.0 NaN WATER SERVICE LEAK 2017-01-27 21:09:00
70610 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 1649 OHLEN RD 3121863.75 10105553.00 526992.0 1 2017-02-09 20:45:00 1814590.0 BANK OF AMERICA FINANCIAL CENTER WATER SERVICE LEAK 2017-02-14 09:13:00
70611 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 1416 ELM BROOK DR 3129251.00 10117681.00 1057527.0 1 2017-02-09 22:54:00 1814602.0 SWEARINGEN WATER MAIN LEAK 2017-02-10 07:31:00
70612 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 5728 REPUBLIC OF TEXAS BLVD 3084010.75 10064012.00 479011.0 3 2017-02-10 01:19:00 1814607.0 NaN WATER SERVICE LEAK 2017-02-11 04:41:00
70613 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 1500 BRENTWOOD ST 3117857.50 10096352.00 1194725.0 1 2017-02-07 18:51:00 1813689.0 NaN WATER METER LEAK 2017-02-07 21:19:00
70614 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 8500 KEARSARGE CV 3089442.50 10042473.00 405181.0 3 2017-02-05 18:32:00 1812801.0 NaN WATER SERVICE LEAK 2017-02-06 22:30:00
70615 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 1223 WEST LYNN ST 3109307.25 10075302.00 1493994.0 1 2017-01-24 14:38:00 1809358.0 13TH ST. WATER SERVICE LEAK 2017-01-25 04:45:00
70616 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 7220 FIREFLY DR 3113795.75 10035707.00 392294.0 1 2017-01-24 20:54:00 1809798.0 NaN WATER SERVICE LEAK 2017-01-25 01:40:00
70617 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 806 W 10TH ST 3112044.00 10073012.00 188213.0 1 2017-02-08 17:01:00 1813953.0 ON THE NORTH SIDE WATER SERVICE LEAK 2017-02-09 03:53:00

70617 rows × 28 columns

In [6]:
# test
assert arrow.get(1474131660000/1000)==arrow.get(df.loc[69665].INITDTTM),'should be equal after conversion'
assert arrow.get(1483936920000/1000)==arrow.get(df.loc[70392].COMPDTTM),'should be equal after conversion'
assert arrow.get(1484258400000/1000)==arrow.get(df.loc[5811].SCHEDDTTM),'should be equal after conversion'
assert arrow.get(1484258160000/1000)==arrow.get(df.loc[5811].PROBDTTM),'should be equal after conversion'
assert arrow.get(1484262840000/1000)==arrow.get(df.loc[5811].STARTDTTM),'should be equal after conversion'

# assert arrow.get(LWOPR_69665['INITDTTM']/1000)==arrow.get(df.loc[69665].INITDTTM),'should be equal after conversion'
# assert arrow.get(LRL24h_70392['COMPDTTM']/1000)==arrow.get(df.loc[70392].COMPDTTM),'should be equal after conversion'
# assert arrow.get(LPI_5811['SCHEDDTTM']/1000)==arrow.get(df.loc[5811].SCHEDDTTM),'should be equal after conversion'
# assert arrow.get(LPI_5811['PROBDTTM']/1000)==arrow.get(df.loc[5811].PROBDTTM),'should be equal after conversion'
# assert arrow.get(LPI_5811['STARTDTTM']/1000)==arrow.get(df.loc[5811].STARTDTTM),'should be equal after conversion'
In [7]:
# TODO make sure there are no NaT?

Save to CSV

In [8]:
df.to_csv(data_path.dirname().joinpath('derived','austin_leaks_with_headers.csv'), date_format='iso')

Converting CRS

Convert to the google maps CRS.

It looks like the crs is pyproj.Proj(init='EPSG:6578') and it has to be multiplied by 1200.0/3937 first to convert to meters

In [9]:
import pyproj

survey_foot_to_m=1200.0 /3937.0
p0=pyproj.Proj(init='EPSG:6578') # NAD83(2011) / Texas Central (ftUS)
p1=pyproj.Proj(init='EPSG:3857') #  WGS84 Web Mercator (Auxiliary Sphere) 

def convert_to_3857(X,Y):
    return pyproj.transform(p0,p1,X*survey_foot_to_m,Y*survey_foot_to_m)

x,y=convert_to_3857(df.X.values, df.Y.values)
df['x']=x
df['y']=y
# df.drop(['X','Y'])


# test using data from thier webmap
x0,y0=convert_to_3857(3133157.5, 10085295.0)
assert (x0 - -10873914.4433)<1,'should work for test data'
assert (y0 - 3543050.4391)<1,'should work for test data'
df
Out [9]:
INSPFLAG PRI PROB PROBDTTM SCHEDDTTM SERVNO MAPNO STARTDTTM PROBCODE PROBDESC ... Y 22 QTYCALLS INITDTTM WONO LOC DESCRIPT COMPDTTM x y
OBJECTID
1 Y 1 SERLK 2015-07-04 00:26:00 2015-07-04 00:28:00 566188.0 465Y 2015-07-04 00:47:00 SERLK LEAKING WATER SERVICE ... 10121089.21 NaN 1 NaT NaN NaN NaN NaT -1.087801e+07 3.555848e+06
2 Y 3 CCOLK 2007-06-22 17:06:00 2007-06-22 21:45:00 314762.0 615B 2007-06-23 08:41:00 CCOLK LEAKING/CHECK CITY CUT OFF ... 10068599.12 NaN 1 NaT NaN NaN NaN NaT -1.087925e+07 3.537257e+06
3 Y 3 MTRLK 2006-06-14 19:15:00 2006-07-03 21:21:00 280152.0 585Y 2006-07-04 19:24:00 MTRLK LEAKING WATER METER ... 10068659.00 NaN 2 NaT NaN NaN NaN NaT -1.087911e+07 3.537275e+06
4 Y 1 MTRLK 2011-06-06 22:02:00 2011-06-06 22:04:00 430378.0 585Y 2011-06-06 23:44:00 MTRLK LEAKING WATER METER ... 10068659.00 NaN 1 NaT NaN NaN NaN NaT -1.087911e+07 3.537275e+06
5 Y 3 MTRLK 2005-03-03 22:06:00 2005-03-08 14:28:00 235202.0 614R 2005-03-23 21:13:00 MTRLK LEAKING WATER METER ... 10061026.95 NaN 1 NaT NaN NaN NaN NaT -1.088261e+07 3.534652e+06
6 Y 1 MTRLK 2007-04-18 22:00:00 2007-04-18 22:07:00 309735.0 614Q 2007-04-18 23:43:00 MTRLK LEAKING WATER METER ... 10059934.00 NaN 1 NaT NaN NaN NaN NaT -1.088276e+07 3.534268e+06
7 Y 1 MTRLK 2007-03-08 16:56:00 2007-03-08 17:01:00 306550.0 614Q 2007-03-08 19:33:00 MTRLK LEAKING WATER METER ... 10059934.00 NaN 2 NaT NaN NaN NaN NaT -1.088276e+07 3.534268e+06
8 Y 3 MTRLK 2009-07-07 14:16:00 2009-07-08 12:45:00 373961.0 614U 2009-07-08 14:20:00 MTRLK LEAKING WATER METER ... 10059798.00 NaN 1 NaT NaN NaN NaN NaT -1.088278e+07 3.534220e+06
9 Y 3 MTRLK 2009-02-23 14:24:00 2009-02-23 16:47:00 364175.0 614H 2009-02-24 03:12:00 MTRLK LEAKING WATER METER ... 10064443.00 NaN 1 NaT NaN NaN NaN NaT -1.088238e+07 3.535857e+06
10 Y 3 MTRLK 2005-12-20 13:54:00 2006-01-17 16:35:00 263359.0 614M 2006-01-17 18:52:00 MTRLK LEAKING WATER METER ... 10064134.00 NaN 2 NaT NaN NaN NaN NaT -1.088240e+07 3.535748e+06
11 Y 1 MTRLK 2016-05-07 14:38:00 2016-05-07 14:39:00 599121.0 615H 2016-05-07 14:49:00 MTRLK LEAKING WATER METER ... 10066137.84 NaN 1 NaT NaN NaN NaN NaT -1.087788e+07 3.536352e+06
12 Y 1 WMNLK 2015-02-27 12:48:00 2015-02-27 14:03:00 553829.0 615H 2015-02-27 14:51:00 WMNLK WATER MAIN LEAK ... 10066137.84 NaN 2 NaT NaN NaN NaN NaT -1.087788e+07 3.536352e+06
13 Y 1 WMNLK 2011-07-10 14:30:00 2011-07-10 14:30:00 432895.0 615G 2011-07-10 14:37:00 WMNLK WATER MAIN LEAK ... 10065983.00 NaN 1 NaT NaN NaN NaN NaT -1.087784e+07 3.536296e+06
14 Y 1 SERLK 2005-04-25 02:35:00 2005-04-25 02:40:00 240012.0 615H 2005-04-25 04:40:00 SERLK LEAKING WATER SERVICE ... 10065926.02 NaN 1 NaT NaN NaN NaN NaT -1.087780e+07 3.536275e+06
15 Y 1 WMNLK 2015-08-26 16:48:00 2015-08-26 16:51:00 572123.0 615H 2015-08-26 17:25:00 WMNLK WATER MAIN LEAK ... 10066098.04 NaN 1 NaT NaN NaN NaN NaT -1.087783e+07 3.536337e+06
16 Y 1 MTRLK 2013-09-06 22:13:00 2013-09-06 22:14:00 496919.0 615H 2013-09-07 00:05:00 MTRLK LEAKING WATER METER ... 10065984.09 NaN 1 NaT NaN NaN NaN NaT -1.087772e+07 3.536294e+06
17 Y 1 MTRLK 2007-03-15 17:29:00 2007-03-15 17:51:00 307176.0 615H 2007-03-15 19:06:00 MTRLK LEAKING WATER METER ... 10065870.00 NaN 3 NaT NaN NaN NaN NaT -1.087774e+07 3.536254e+06
18 Y 3 MTRLK 2005-06-23 00:31:00 2005-06-24 21:35:00 245815.0 615H 2005-06-27 03:23:00 MTRLK LEAKING WATER METER ... 10065822.00 NaN 2 NaT NaN NaN NaN NaT -1.087758e+07 3.536233e+06
19 Y 1 MTRLK 2005-11-20 23:22:00 2005-11-20 23:22:00 260441.0 673H 2005-11-21 03:00:00 MTRLK LEAKING WATER METER ... 10038614.00 NaN 1 NaT NaN NaN NaN NaT -1.088646e+07 3.526800e+06
20 Y 1 MTRLK 2011-07-05 19:02:00 2011-07-05 19:04:00 432530.0 615H 2011-07-05 19:37:00 MTRLK LEAKING WATER METER ... 10066222.00 NaN 1 NaT NaN NaN NaN NaT -1.087772e+07 3.536378e+06
21 Y 1 MTRLK 2006-12-18 21:10:00 2006-12-18 21:16:00 299030.0 615H 2006-12-18 23:08:00 MTRLK LEAKING WATER METER ... 10066300.00 NaN 1 NaT NaN NaN NaN NaT -1.087767e+07 3.536405e+06
22 Y 1 MTRLK 2008-09-27 23:28:00 2008-09-27 23:29:00 351425.0 615H 2008-09-27 23:53:00 MTRLK LEAKING WATER METER ... 10066172.00 NaN 1 NaT NaN NaN NaN NaT -1.087767e+07 3.536359e+06
23 Y 1 MTRLK 2011-12-12 15:28:00 2011-12-12 15:29:00 445632.0 615H 2011-12-12 15:47:00 MTRLK LEAKING WATER METER ... 10065964.00 NaN 2 NaT NaN NaN NaN NaT -1.087748e+07 3.536281e+06
24 Y 1 MTRLK 2014-01-05 18:17:00 2014-01-05 18:18:00 509048.0 615H 2014-01-05 19:13:00 MTRLK LEAKING WATER METER ... 10066051.00 NaN 1 NaT NaN NaN NaN NaT -1.087743e+07 3.536311e+06
25 Y 1 SERLK 2014-11-20 14:54:00 2014-11-20 14:56:00 542172.0 585X 2014-11-20 15:15:00 SERLK LEAKING WATER SERVICE ... 10069118.39 NaN 1 NaT NaN NaN NaN NaT -1.087997e+07 3.537458e+06
26 Y 1 SERLK 2009-11-14 15:34:00 2009-11-14 15:45:00 385181.0 585X 2009-11-14 15:56:00 SERLK LEAKING WATER SERVICE ... 10068756.00 NaN 1 NaT NaN NaN NaN NaT -1.087976e+07 3.537324e+06
27 Y 1 MTRLK 2011-10-01 18:13:00 2011-10-01 18:13:00 440169.0 615B 2011-10-01 18:51:00 MTRLK LEAKING WATER METER ... 10068543.00 NaN 3 NaT NaN NaN NaN NaT -1.087956e+07 3.537244e+06
28 Y 3 MTRLK 2014-08-28 00:22:00 2014-08-28 00:30:00 533165.0 615B 2014-08-28 16:35:00 MTRLK LEAKING WATER METER ... 10068543.00 NaN 1 NaT NaN NaN NaN NaT -1.087956e+07 3.537244e+06
29 Y 1 MTRLK 2005-12-30 16:00:00 2005-12-30 16:11:00 264315.0 585X 2005-12-30 18:30:00 MTRLK LEAKING WATER METER ... 10068502.00 NaN 2 NaT NaN NaN NaN NaT -1.087952e+07 3.537229e+06
30 Y 1 MTRLK 2005-05-23 13:14:00 2005-05-23 13:14:00 242771.0 585X 2005-05-23 17:21:00 MTRLK LEAKING WATER METER ... 10068694.00 NaN 1 NaT NaN NaN NaN NaT -1.087951e+07 3.537297e+06
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
70588 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10093671.00 356219.0 1 2017-02-03 15:54:00 1812528.0 NaN WATER SERVICE LEAK 2017-02-06 20:24:00 -1.087548e+07 3.546058e+06
70589 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10107812.00 414795.0 1 2017-02-10 01:06:00 1814606.0 NaN WATER SERVICE LEAK 2017-02-10 18:28:00 -1.087453e+07 3.551053e+06
70590 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10107403.00 306333.0 1 2017-02-10 19:20:00 1814722.0 NaN WATER SERVICE LEAK 2017-02-11 03:09:00 -1.088170e+07 3.551077e+06
70591 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10084787.00 871450.0 1 2017-02-10 20:48:00 1814736.0 NaN WATER MAIN LEAK 2017-02-14 03:06:00 -1.087297e+07 3.542847e+06
70592 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10101877.00 1215766.0 1 2017-02-04 02:38:00 1812677.0 NaN WATER METER LEAK 2017-02-06 19:45:00 -1.088435e+07 3.549177e+06
70593 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10082323.00 88698.0 1 2017-01-30 19:10:00 1810930.0 NaN WATER METER LEAK 2017-01-30 23:15:00 -1.088332e+07 3.542218e+06
70594 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10079875.49 450316.0 1 2017-02-01 19:18:00 1811688.0 NaN WATER SERVICE LEAK 2017-02-01 20:45:00 -1.087200e+07 3.541082e+06
70595 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10051034.00 1494819.0 1 2017-02-01 20:45:00 1812084.0 NaN WATER SERVICE LEAK 2017-02-02 04:56:00 -1.088532e+07 3.531173e+06
70596 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10057854.00 501413.0 1 2017-02-15 03:07:00 1815701.0 GATE CODE: 1739 WATER SERVICE LEAK 2017-02-16 01:56:00 -1.088579e+07 3.533601e+06
70597 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10088781.19 1278289.0 1 2017-02-02 19:35:00 1812277.0 NaN WATER METER LEAK 2017-02-03 17:45:00 -1.087961e+07 3.544421e+06
70598 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10086123.00 29791.0 2 2017-02-07 23:44:00 1813823.0 NaN WTR SVC DAMAGED BY CITY CREWS 2017-02-07 23:44:00 -1.087882e+07 3.543460e+06
70599 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10055909.00 386973.0 1 2017-02-02 15:03:00 1812261.0 NaN WATER SERVICE LEAK 2017-02-03 18:11:00 -1.089222e+07 3.533058e+06
70600 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10060629.88 943972.0 1 2017-02-16 18:52:00 1815766.0 NaN WATER MAIN LEAK 2017-02-17 01:07:00 -1.087451e+07 3.534320e+06
70601 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10059473.00 315946.0 1 2017-02-12 04:57:00 1814809.0 NaN WATER SERVICE LEAK 2017-02-13 00:39:00 -1.087570e+07 3.533938e+06
70602 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10077584.00 228104.0 1 2017-01-10 16:40:00 1806592.0 NaN WATER SERVICE LEAK 2017-01-10 23:06:00 -1.087773e+07 3.540406e+06
70603 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10067378.00 936565.0 1 2017-02-03 19:31:00 1812654.0 KERR AVE WATER MAIN LEAK 2017-02-06 22:00:00 -1.088331e+07 3.536919e+06
70604 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10083267.00 1108480.0 1 2017-02-08 17:58:00 1814007.0 NaN WATER METER LEAK 2017-02-09 02:00:00 -1.087838e+07 3.542437e+06
70605 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10088531.00 1494757.0 1 2017-01-31 18:03:00 1811342.0 NaN WATER SERVICE LEAK 2017-01-31 20:48:00 -1.088850e+07 3.544538e+06
70606 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10084826.33 214418.0 1 2017-02-06 16:43:00 1812846.0 NaN WATER SERVICE LEAK 2017-02-10 20:42:00 -1.087920e+07 3.543009e+06
70607 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10073208.00 1494754.0 2 2017-01-31 15:50:00 1811331.0 BETWEEN BAYLOR AND N LAMAR WATER SERVICE LEAK 2017-01-31 21:16:00 -1.088184e+07 3.538951e+06
70608 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10056368.00 390620.0 2 2017-01-27 15:09:00 1810599.0 NaN WATER SERVICE LEAK 2017-01-27 21:09:00 -1.089241e+07 3.533225e+06
70609 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10056368.00 390620.0 2 2017-01-27 15:09:00 1811380.0 NaN WATER SERVICE LEAK 2017-01-27 21:09:00 -1.089241e+07 3.533225e+06
70610 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10105553.00 526992.0 1 2017-02-09 20:45:00 1814590.0 BANK OF AMERICA FINANCIAL CENTER WATER SERVICE LEAK 2017-02-14 09:13:00 -1.087773e+07 3.550327e+06
70611 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10117681.00 1057527.0 1 2017-02-09 22:54:00 1814602.0 SWEARINGEN WATER MAIN LEAK 2017-02-10 07:31:00 -1.087502e+07 3.554567e+06
70612 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10064012.00 479011.0 3 2017-02-10 01:19:00 1814607.0 NaN WATER SERVICE LEAK 2017-02-11 04:41:00 -1.089141e+07 3.535911e+06
70613 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10096352.00 1194725.0 1 2017-02-07 18:51:00 1813689.0 NaN WATER METER LEAK 2017-02-07 21:19:00 -1.087922e+07 3.547098e+06
70614 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10042473.00 405181.0 3 2017-02-05 18:32:00 1812801.0 NaN WATER SERVICE LEAK 2017-02-06 22:30:00 -1.088967e+07 3.528240e+06
70615 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10075302.00 1493994.0 1 2017-01-24 14:38:00 1809358.0 13TH ST. WATER SERVICE LEAK 2017-01-25 04:45:00 -1.088241e+07 3.539707e+06
70616 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10035707.00 392294.0 1 2017-01-24 20:54:00 1809798.0 NaN WATER SERVICE LEAK 2017-01-25 01:40:00 -1.088115e+07 3.525647e+06
70617 NaN NaN NaN NaT NaT NaN NaN NaT NaN NaN ... 10073012.00 188213.0 1 2017-02-08 17:01:00 1813953.0 ON THE NORTH SIDE WATER SERVICE LEAK 2017-02-09 03:53:00 -1.088146e+07 3.538873e+06

70617 rows × 30 columns

To geojson

In [10]:
# converting to geojson
from collections import OrderedDict
def df_to_geojson(df, properties, lat='latitude', lon='longitude'):
    geojson = {'type':'FeatureCollection', 'features':[], "crs": {"type": "name", "properties": {"name": "EPSG:3857"}}}
    for _, row in df.iterrows():
        feature = {'type':'Feature',
                   'properties':OrderedDict(),
                   'geometry':{'type':'Point',
                               'coordinates':[]}}
        feature['geometry']['coordinates'] = [row[lon],row[lat]]
        for prop in properties:
            feature['properties'][prop] = row[prop]
        geojson['features'].append(feature)
    return geojson



cols = df.columns.drop(['X','Y','x','y'])
geoj = df_to_geojson(df.copy(),properties=cols,lat='y',lon='x')
gdf = gpd.GeoDataFrame.from_features(geoj['features'], crs={'init': 'epsg:3857'})
gdf = gdf.to_crs(epsg=4326) # geojson only supports this by default
gdf
Out [10]:
22 ADDRKEY CITY COMPDTTM DESCRIPT FullStreetName INITDTTM INSPFLAG LOC MAPNO ... SCHEDDTTM SERVNO STARTDTTM STNAME STNO STSUB SUFFIX WONO ZIP geometry
0 NaN 3506121.0 AUSTIN NaT NaN 11925 1/2 ALTERRA PKWY NaT Y NaN 465Y ... 2015-07-04 00:28:00 566188.0 2015-07-04 00:47:00 ALTERRA 11925 1/2 PKWY NaN 78758- POINT (-97.71883613475558 30.40602168798523)
1 NaN 400994.0 AUSTIN NaT NaN 1305 E 4TH ST NaT Y NaN 615B ... 2007-06-22 21:45:00 314762.0 2007-06-23 08:41:00 4TH 1305 ST NaN 78702- POINT (-97.72998272625865 30.26188173484448)
2 NaN 362708.0 AUSTIN NaT NaN 1400 E 4TH ST NaT Y NaN 585Y ... 2006-07-03 21:21:00 280152.0 2006-07-04 19:24:00 4TH 1400 ST NaN 78702- POINT (-97.72873894244808 30.26202114720336)
3 NaN 362708.0 AUSTIN NaT NaN 1400 E 4TH ST NaT Y NaN 585Y ... 2011-06-06 22:04:00 430378.0 2011-06-06 23:44:00 4TH 1400 ST NaN 78702- POINT (-97.72873894244808 30.26202114720336)
4 NaN 299714.0 AUSTIN NaT NaN 2414 S 1ST ST NaT Y NaN 614R ... 2005-03-08 14:28:00 235202.0 2005-03-23 21:13:00 1ST 2414 ST NaN 78704- POINT (-97.76018855412671 30.24166596881509)
5 NaN 300902.0 AUSTIN NaT NaN 2613 S 1ST ST NaT Y NaN 614Q ... 2007-04-18 22:07:00 309735.0 2007-04-18 23:43:00 1ST 2613 ST NaN 78704- POINT (-97.76150797578026 30.23868649764332)
6 NaN 300902.0 AUSTIN NaT NaN 2613 S 1ST ST NaT Y NaN 614Q ... 2007-03-08 17:01:00 306550.0 2007-03-08 19:33:00 1ST 2613 ST NaN 78704- POINT (-97.76150797578026 30.23868649764332)
7 NaN 300896.0 AUSTIN NaT NaN 2617 S 1ST ST NaT Y NaN 614U ... 2009-07-08 12:45:00 373961.0 2009-07-08 14:20:00 1ST 2617 ST NaN 78704- POINT (-97.76164694000661 30.23831524346074)
8 NaN 259940.0 AUSTIN NaT NaN 1502 S 3RD ST NaT Y NaN 614H ... 2009-02-23 16:47:00 364175.0 2009-02-24 03:12:00 3RD 1502 ST NaN 78704- POINT (-97.75810182363229 30.2510192947657)
9 NaN 259634.0 AUSTIN NaT NaN 1602 S 3RD ST NaT Y NaN 614M ... 2006-01-17 16:35:00 263359.0 2006-01-17 18:52:00 3RD 1602 ST NaN 78704- POINT (-97.75829228724409 30.25017327150837)
10 NaN 322070.0 AUSTIN NaT NaN 2400 E CESAR CHAVEZ ST NaT Y NaN 615H ... 2016-05-07 14:39:00 599121.0 2016-05-07 14:49:00 CESAR CHAVEZ 2400 ST NaN 78702- POINT (-97.71762372635818 30.25486062333257)
11 NaN 322070.0 AUSTIN NaT NaN 2400 E CESAR CHAVEZ ST NaT Y NaN 615H ... 2015-02-27 14:03:00 553829.0 2015-02-27 14:51:00 CESAR CHAVEZ 2400 ST NaN 78702- POINT (-97.71762372635818 30.25486062333257)
12 NaN 211898.0 AUSTIN NaT NaN 2401 1/2 E CESAR CHAVEZ ST NaT Y NaN 615G ... 2011-07-10 14:30:00 432895.0 2011-07-10 14:37:00 CESAR CHAVEZ 2401 1/2 ST NaN 78702- POINT (-97.71729718594632 30.25442809116065)
13 NaN 211922.0 AUSTIN NaT NaN 2411 E CESAR CHAVEZ ST NaT Y NaN 615H ... 2005-04-25 02:40:00 240012.0 2005-04-25 04:40:00 CESAR CHAVEZ 2411 ST NaN 78702- POINT (-97.7169787794186 30.25426487385318)
14 NaN 879506.0 AUSTIN NaT NaN 2412 E CESAR CHAVEZ ST NaT Y NaN 615H ... 2015-08-26 16:51:00 572123.0 2015-08-26 17:25:00 CESAR CHAVEZ 2412 ST NaN 78702- POINT (-97.71721424047422 30.25474279812237)
15 NaN 322046.0 AUSTIN NaT NaN 2504 E CESAR CHAVEZ ST NaT Y NaN 615H ... 2013-09-06 22:14:00 496919.0 2013-09-07 00:05:00 CESAR CHAVEZ 2504 ST NaN 78702- POINT (-97.71620069417993 30.25440869438884)
16 NaN 211946.0 AUSTIN NaT NaN 2505 E CESAR CHAVEZ ST NaT Y NaN 615H ... 2007-03-15 17:51:00 307176.0 2007-03-15 19:06:00 CESAR CHAVEZ 2505 ST NaN 78702- POINT (-97.7163682396871 30.25409832901511)
17 NaN 321986.0 AUSTIN NaT NaN 2604 E CESAR CHAVEZ ST NaT Y NaN 615H ... 2005-06-24 21:35:00 245815.0 2005-06-27 03:23:00 CESAR CHAVEZ 2604 ST NaN 78702- POINT (-97.71492876098637 30.25393689970791)
18 NaN 1015862.0 AUSTIN NaT NaN 8203 S 1ST ST NaT Y NaN 673H ... 2005-11-20 23:22:00 260441.0 2005-11-21 03:00:00 1ST 8203 ST NaN 78748- POINT (-97.7947342865837 30.18071290474441)
19 NaN 321710.0 AUSTIN NaT NaN 2501 E 2ND ST NaT Y NaN 615H ... 2011-07-05 19:04:00 432530.0 2011-07-05 19:37:00 2ND 2501 ST NaN 78702- POINT (-97.71624702364329 30.25506397762937)
20 NaN 290474.0 AUSTIN NaT NaN 2504 E 2ND ST NaT Y NaN 615H ... 2006-12-18 21:16:00 299030.0 2006-12-18 23:08:00 2ND 2504 ST NaN 78702- POINT (-97.7158042526021 30.25526945974075)
21 NaN 321734.0 AUSTIN NaT NaN 2507 E 2ND ST NaT Y NaN 615H ... 2008-09-27 23:29:00 351425.0 2008-09-27 23:53:00 2ND 2507 ST NaN 78702- POINT (-97.7157979592134 30.25491728575067)
22 NaN 321806.0 AUSTIN NaT NaN 2613 E 2ND ST NaT Y NaN 615H ... 2011-12-12 15:29:00 445632.0 2011-12-12 15:47:00 2ND 2613 ST NaN 78702- POINT (-97.71406247216238 30.25430974185582)
23 NaN 290366.0 AUSTIN NaT NaN 2700 E 2ND ST NaT Y NaN 615H ... 2014-01-05 18:18:00 509048.0 2014-01-05 19:13:00 2ND 2700 ST NaN 78702- POINT (-97.71357469983644 30.25453904942606)
24 NaN 879296.0 AUSTIN NaT NaN 702 E 3RD ST NaT Y NaN 585X ... 2014-11-20 14:56:00 542172.0 2014-11-20 15:15:00 3RD 702 ST NaN 78701- POINT (-97.73644375553381 30.26344102487212)
25 NaN 363482.0 AUSTIN NaT NaN 903 E 3RD ST NaT Y NaN 585X ... 2009-11-14 15:45:00 385181.0 2009-11-14 15:56:00 3RD 903 ST NaN 78702- POINT (-97.73452665606773 30.26240545658105)
26 NaN 363542.0 AUSTIN NaT NaN 1101 E 3RD ST NaT Y NaN 615B ... 2011-10-01 18:13:00 440169.0 2011-10-01 18:51:00 3RD 1101 ST NaN 78702- POINT (-97.73272716322229 30.2617831202706)
27 NaN 363542.0 AUSTIN NaT NaN 1101 E 3RD ST NaT Y NaN 615B ... 2014-08-28 00:30:00 533165.0 2014-08-28 16:35:00 3RD 1101 ST NaN 78702- POINT (-97.73272716322229 30.2617831202706)
28 NaN 363560.0 AUSTIN NaT NaN 1111 E 3RD ST NaT Y NaN 585X ... 2005-12-30 16:11:00 264315.0 2005-12-30 18:30:00 3RD 1111 ST NaN 78702- POINT (-97.73238424855897 30.2616633952571)
29 NaN 400856.0 AUSTIN NaT NaN 1112 E 3RD ST NaT Y NaN 585X ... 2005-05-23 13:14:00 242771.0 2005-05-23 17:21:00 3RD 1112 ST NaN 78702- POINT (-97.73226790726385 30.26218909997605)
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
70587 356219.0 530798.0 AUSTIN 2017-02-06 20:24:00 WATER SERVICE LEAK 7212 MEADOR AVE 2017-02-03 15:54:00 NaN NaN NaN ... NaT NaN NaT MEADOR 7212 AVE 1812528.0 78752- POINT (-97.69612767423286 30.33014639024331)
70588 414795.0 656817.0 AUSTIN 2017-02-10 18:28:00 WATER SERVICE LEAK 10308 GEORGIAN DR 2017-02-10 01:06:00 NaN NaN NaN ... NaT NaN NaT GEORGIAN 10308 DR 1814606.0 78753- POINT (-97.68759124418112 30.36886373358098)
70589 306333.0 538694.0 AUSTIN 2017-02-11 03:09:00 WATER SERVICE LEAK 8012 GREENSLOPE DR 2017-02-10 19:20:00 NaN NaN NaN ... NaT NaN NaT GREENSLOPE 8012 DR 1814722.0 78759- POINT (-97.75201769610545 30.36905158527984)
70590 871450.0 493298.0 AUSTIN 2017-02-14 03:06:00 WATER MAIN LEAK 6104 HYLAWN DR 2017-02-10 20:48:00 NaN NaN NaN ... NaT NaN NaT HYLAWN 6104 DR 1814736.0 78723- POINT (-97.67358467634561 30.30524625454306)
70591 1215766.0 153660.0 AUSTIN 2017-02-06 19:45:00 WATER METER LEAK 6421 WILLIAMS RIDGE WAY 2017-02-04 02:38:00 NaN NaN NaN ... NaT NaN NaT WILLIAMS RIDGE 6421 WAY 1812677.0 78731- POINT (-97.77575678484202 30.35432876830911)
70592 88698.0 657512.0 AUSTIN 2017-01-30 23:15:00 WATER METER LEAK 2703 GREENLEE DR 2017-01-30 19:10:00 NaN NaN NaN ... NaT NaN NaT GREENLEE 2703 DR 1810930.0 78703- POINT (-97.76649592905592 30.30036364526744)
70593 450316.0 3101444.0 AUSTIN 2017-02-01 20:45:00 WATER SERVICE LEAK 5316 ED BLUESTEIN BLVD SB 2017-02-01 19:18:00 NaN NaN NaN ... NaT NaN NaT ED BLUESTEIN 5316 BLVD 1811688.0 78723- POINT (-97.6648140744901 30.29155538276262)
70594 1494819.0 332588.0 AUSTIN 2017-02-02 04:56:00 WATER SERVICE LEAK 5209 MEADOW CREEK DR 2017-02-01 20:45:00 NaN NaN NaN ... NaT NaN NaT MEADOW CREEK 5209 DR 1812084.0 78745- POINT (-97.78444857807514 30.21466728210482)
70595 501413.0 615363.0 AUSTIN 2017-02-16 01:56:00 WATER SERVICE LEAK 4017 VICTORY DR UNIT 252 2017-02-15 03:07:00 NaN GATE CODE: 1739 NaN ... NaT NaN NaT VICTORY 4017 252 DR 1815701.0 78704- POINT (-97.78872926072586 30.23350949986936)
70596 1278289.0 549506.0 AUSTIN 2017-02-03 17:45:00 WATER METER LEAK 4800 N LAMAR BLVD 2017-02-02 19:35:00 NaN NaN NaN ... NaT NaN NaT LAMAR 4800 BLVD 1812277.0 78756- POINT (-97.73323830541952 30.31745561879857)
70597 29791.0 555260.0 AUSTIN 2017-02-07 23:44:00 WTR SVC DAMAGED BY CITY CREWS 4531 AVENUE F 2017-02-07 23:44:00 NaN NaN NaN ... NaT NaN NaT AVENUE F 4531 1813823.0 78751- POINT (-97.72606032578145 30.30999885700736)
70598 386973.0 209847.0 AUSTIN 2017-02-03 18:11:00 WATER SERVICE LEAK 5101 SUMMERSET TRL 2017-02-02 15:03:00 NaN NaN NaN ... NaT NaN NaT SUMMERSET 5101 TRL 1812261.0 78749- POINT (-97.84648270740318 30.2292945202867)
70599 943972.0 242289.0 AUSTIN 2017-02-17 01:07:00 WATER MAIN LEAK 415 BASTROP HWY NB 2017-02-16 18:52:00 NaN NaN NaN ... NaT NaN NaT BASTROP 415 HWY 1815766.0 78742- POINT (-97.68736689196109 30.23909036737392)
70600 315946.0 615818.0 AUSTIN 2017-02-13 00:39:00 WATER SERVICE LEAK 6120 FELIX AVE 2017-02-12 04:57:00 NaN NaN NaN ... NaT NaN NaT FELIX 6120 AVE 1814809.0 78741- POINT (-97.69805366462411 30.23612883532359)
70601 228104.0 724436.0 AUSTIN 2017-01-10 23:06:00 WATER SERVICE LEAK 1707 E 32ND ST 2017-01-10 16:40:00 NaN NaN NaN ... NaT NaN NaT 32ND 1707 ST 1806592.0 78722- POINT (-97.71634409049777 30.28631552139746)
70602 936565.0 610796.0 AUSTIN 2017-02-06 22:00:00 WATER MAIN LEAK 902 GARNER AVE 2017-02-03 19:31:00 NaN KERR AVE NaN ... NaT NaN NaT GARNER 902 AVE 1812654.0 78704- POINT (-97.76641252202596 30.25925828998614)
70603 1108480.0 204332.0 AUSTIN 2017-02-09 02:00:00 WATER METER LEAK 806 PARK BLVD UNIT 1 2017-02-08 17:58:00 NaN NaN NaN ... NaT NaN NaT PARK 806 1 BLVD 1814007.0 78751- POINT (-97.72219135503057 30.30206507689073)
70604 1494757.0 741988.0 AUSTIN 2017-01-31 20:48:00 WATER SERVICE LEAK 1904 CANONERO DR 2017-01-31 18:03:00 NaN NaN NaN ... NaT NaN NaT CANONERO 1904 DR 1811342.0 78746- POINT (-97.81307959800138 30.3183619749208)
70605 214418.0 716300.0 AUSTIN 2017-02-10 20:42:00 WATER SERVICE LEAK 4301 SPEEDWAY 2017-02-06 16:43:00 NaN NaN NaN ... NaT NaN NaT SPEEDWAY 4301 1812846.0 78751- POINT (-97.72948993180425 30.30650226337686)
70606 1494754.0 170486.0 AUSTIN 2017-01-31 21:16:00 WATER SERVICE LEAK 1011 W 10TH ST 2017-01-31 15:50:00 NaN BETWEEN BAYLOR AND N LAMAR NaN ... NaT NaN NaT 10TH 1011 ST 1811331.0 78703- POINT (-97.75321761374349 30.27502770389426)
70607 390620.0 265185.0 AUSTIN 2017-01-27 21:09:00 WATER SERVICE LEAK 6207 MORNING DEW DR 2017-01-27 15:09:00 NaN NaN NaN ... NaT NaN NaT MORNING DEW 6207 DR 1810599.0 78749- POINT (-97.84822614810216 30.23059073488725)
70608 390620.0 265185.0 AUSTIN 2017-01-27 21:09:00 WATER SERVICE LEAK 6207 MORNING DEW DR 2017-01-27 15:09:00 NaN NaN NaN ... NaT NaN NaT MORNING DEW 6207 DR 1811380.0 78749- POINT (-97.84822614810216 30.23059073488725)
70609 526992.0 590422.0 AUSTIN 2017-02-14 09:13:00 WATER SERVICE LEAK 1649 OHLEN RD 2017-02-09 20:45:00 NaN BANK OF AMERICA FINANCIAL CENTER NaN ... NaT NaN NaT OHLEN 1649 RD 1814590.0 78758- POINT (-97.71629701067313 30.36323955031358)
70610 1057527.0 337504.0 AUSTIN 2017-02-10 07:31:00 WATER MAIN LEAK 1416 ELM BROOK DR 2017-02-09 22:54:00 NaN SWEARINGEN NaN ... NaT NaN NaT ELM BROOK 1416 DR 1814602.0 78758- POINT (-97.69196714063571 30.39609749377057)
70611 479011.0 204342.0 AUSTIN 2017-02-11 04:41:00 WATER SERVICE LEAK 5728 REPUBLIC OF TEXAS BLVD 2017-02-10 01:19:00 NaN NaN NaN ... NaT NaN NaT REPUBLIC OF TEXAS 5728 BLVD 1814607.0 78735- POINT (-97.83924391121697 30.25143929904074)
70612 1194725.0 355628.0 AUSTIN 2017-02-07 21:19:00 WATER METER LEAK 1500 BRENTWOOD ST 2017-02-07 18:51:00 NaN NaN NaN ... NaT NaN NaT BRENTWOOD 1500 ST 1813689.0 78757- POINT (-97.72967930881964 30.33820583105881)
70613 405181.0 534489.0 AUSTIN 2017-02-06 22:30:00 WATER SERVICE LEAK 8500 KEARSARGE CV 2017-02-05 18:32:00 NaN NaN NaN ... NaT NaN NaT KEARSARGE 8500 CV 1812801.0 78749- POINT (-97.82358419028652 30.19189506598336)
70614 1493994.0 388862.0 AUSTIN 2017-01-25 04:45:00 WATER SERVICE LEAK 1223 WEST LYNN ST 2017-01-24 14:38:00 NaN 13TH ST. NaN ... NaT NaN NaT WEST LYNN 1223 ST 1809358.0 78703- POINT (-97.75832058342988 30.28088955049816)
70615 392294.0 292227.0 AUSTIN 2017-01-25 01:40:00 WATER SERVICE LEAK 7220 FIREFLY DR 2017-01-24 20:54:00 NaN NaN NaN ... NaT NaN NaT FIREFLY 7220 DR 1809798.0 78744- POINT (-97.74702063118784 30.17176266908997)
70616 188213.0 252971.0 AUSTIN 2017-02-09 03:53:00 WATER SERVICE LEAK 806 W 10TH ST 2017-02-08 17:01:00 NaN ON THE NORTH SIDE NaN ... NaT NaN NaT 10TH 806 ST 1813953.0 78701- POINT (-97.7498211266024 30.27442020454225)

70617 rows × 27 columns

In [11]:
# the geopandas to_json can't handle dates, so lets manually convert them to isoformat
def to_isoformat(t):
    try:
        return t.isoformat()
    except Exception as e:
        return None

for col in tqdm(gdf.columns):
    if col.endswith('DTTM'):
        gdf[col]=gdf[col].apply(to_isoformat) #.astype(str)
gdf
Out [11]:
100%|██████████| 27/27 [00:00<00:00, 28.66it/s]
22 ADDRKEY CITY COMPDTTM DESCRIPT FullStreetName INITDTTM INSPFLAG LOC MAPNO ... SCHEDDTTM SERVNO STARTDTTM STNAME STNO STSUB SUFFIX WONO ZIP geometry
0 NaN 3506121.0 AUSTIN NaT NaN 11925 1/2 ALTERRA PKWY NaT Y NaN 465Y ... 2015-07-04T00:28:00 566188.0 2015-07-04T00:47:00 ALTERRA 11925 1/2 PKWY NaN 78758- POINT (-97.71883613475558 30.40602168798523)
1 NaN 400994.0 AUSTIN NaT NaN 1305 E 4TH ST NaT Y NaN 615B ... 2007-06-22T21:45:00 314762.0 2007-06-23T08:41:00 4TH 1305 ST NaN 78702- POINT (-97.72998272625865 30.26188173484448)
2 NaN 362708.0 AUSTIN NaT NaN 1400 E 4TH ST NaT Y NaN 585Y ... 2006-07-03T21:21:00 280152.0 2006-07-04T19:24:00 4TH 1400 ST NaN 78702- POINT (-97.72873894244808 30.26202114720336)
3 NaN 362708.0 AUSTIN NaT NaN 1400 E 4TH ST NaT Y NaN 585Y ... 2011-06-06T22:04:00 430378.0 2011-06-06T23:44:00 4TH 1400 ST NaN 78702- POINT (-97.72873894244808 30.26202114720336)
4 NaN 299714.0 AUSTIN NaT NaN 2414 S 1ST ST NaT Y NaN 614R ... 2005-03-08T14:28:00 235202.0 2005-03-23T21:13:00 1ST 2414 ST NaN 78704- POINT (-97.76018855412671 30.24166596881509)
5 NaN 300902.0 AUSTIN NaT NaN 2613 S 1ST ST NaT Y NaN 614Q ... 2007-04-18T22:07:00 309735.0 2007-04-18T23:43:00 1ST 2613 ST NaN 78704- POINT (-97.76150797578026 30.23868649764332)
6 NaN 300902.0 AUSTIN NaT NaN 2613 S 1ST ST NaT Y NaN 614Q ... 2007-03-08T17:01:00 306550.0 2007-03-08T19:33:00 1ST 2613 ST NaN 78704- POINT (-97.76150797578026 30.23868649764332)
7 NaN 300896.0 AUSTIN NaT NaN 2617 S 1ST ST NaT Y NaN 614U ... 2009-07-08T12:45:00 373961.0 2009-07-08T14:20:00 1ST 2617 ST NaN 78704- POINT (-97.76164694000661 30.23831524346074)
8 NaN 259940.0 AUSTIN NaT NaN 1502 S 3RD ST NaT Y NaN 614H ... 2009-02-23T16:47:00 364175.0 2009-02-24T03:12:00 3RD 1502 ST NaN 78704- POINT (-97.75810182363229 30.2510192947657)
9 NaN 259634.0 AUSTIN NaT NaN 1602 S 3RD ST NaT Y NaN 614M ... 2006-01-17T16:35:00 263359.0 2006-01-17T18:52:00 3RD 1602 ST NaN 78704- POINT (-97.75829228724409 30.25017327150837)
10 NaN 322070.0 AUSTIN NaT NaN 2400 E CESAR CHAVEZ ST NaT Y NaN 615H ... 2016-05-07T14:39:00 599121.0 2016-05-07T14:49:00 CESAR CHAVEZ 2400 ST NaN 78702- POINT (-97.71762372635818 30.25486062333257)
11 NaN 322070.0 AUSTIN NaT NaN 2400 E CESAR CHAVEZ ST NaT Y NaN 615H ... 2015-02-27T14:03:00 553829.0 2015-02-27T14:51:00 CESAR CHAVEZ 2400 ST NaN 78702- POINT (-97.71762372635818 30.25486062333257)
12 NaN 211898.0 AUSTIN NaT NaN 2401 1/2 E CESAR CHAVEZ ST NaT Y NaN 615G ... 2011-07-10T14:30:00 432895.0 2011-07-10T14:37:00 CESAR CHAVEZ 2401 1/2 ST NaN 78702- POINT (-97.71729718594632 30.25442809116065)
13 NaN 211922.0 AUSTIN NaT NaN 2411 E CESAR CHAVEZ ST NaT Y NaN 615H ... 2005-04-25T02:40:00 240012.0 2005-04-25T04:40:00 CESAR CHAVEZ 2411 ST NaN 78702- POINT (-97.7169787794186 30.25426487385318)
14 NaN 879506.0 AUSTIN NaT NaN 2412 E CESAR CHAVEZ ST NaT Y NaN 615H ... 2015-08-26T16:51:00 572123.0 2015-08-26T17:25:00 CESAR CHAVEZ 2412 ST NaN 78702- POINT (-97.71721424047422 30.25474279812237)
15 NaN 322046.0 AUSTIN NaT NaN 2504 E CESAR CHAVEZ ST NaT Y NaN 615H ... 2013-09-06T22:14:00 496919.0 2013-09-07T00:05:00 CESAR CHAVEZ 2504 ST NaN 78702- POINT (-97.71620069417993 30.25440869438884)
16 NaN 211946.0 AUSTIN NaT NaN 2505 E CESAR CHAVEZ ST NaT Y NaN 615H ... 2007-03-15T17:51:00 307176.0 2007-03-15T19:06:00 CESAR CHAVEZ 2505 ST NaN 78702- POINT (-97.7163682396871 30.25409832901511)
17 NaN 321986.0 AUSTIN NaT NaN 2604 E CESAR CHAVEZ ST NaT Y NaN 615H ... 2005-06-24T21:35:00 245815.0 2005-06-27T03:23:00 CESAR CHAVEZ 2604 ST NaN 78702- POINT (-97.71492876098637 30.25393689970791)
18 NaN 1015862.0 AUSTIN NaT NaN 8203 S 1ST ST NaT Y NaN 673H ... 2005-11-20T23:22:00 260441.0 2005-11-21T03:00:00 1ST 8203 ST NaN 78748- POINT (-97.7947342865837 30.18071290474441)
19 NaN 321710.0 AUSTIN NaT NaN 2501 E 2ND ST NaT Y NaN 615H ... 2011-07-05T19:04:00 432530.0 2011-07-05T19:37:00 2ND 2501 ST NaN 78702- POINT (-97.71624702364329 30.25506397762937)
20 NaN 290474.0 AUSTIN NaT NaN 2504 E 2ND ST NaT Y NaN 615H ... 2006-12-18T21:16:00 299030.0 2006-12-18T23:08:00 2ND 2504 ST NaN 78702- POINT (-97.7158042526021 30.25526945974075)
21 NaN 321734.0 AUSTIN NaT NaN 2507 E 2ND ST NaT Y NaN 615H ... 2008-09-27T23:29:00 351425.0 2008-09-27T23:53:00 2ND 2507 ST NaN 78702- POINT (-97.7157979592134 30.25491728575067)
22 NaN 321806.0 AUSTIN NaT NaN 2613 E 2ND ST NaT Y NaN 615H ... 2011-12-12T15:29:00 445632.0 2011-12-12T15:47:00 2ND 2613 ST NaN 78702- POINT (-97.71406247216238 30.25430974185582)
23 NaN 290366.0 AUSTIN NaT NaN 2700 E 2ND ST NaT Y NaN 615H ... 2014-01-05T18:18:00 509048.0 2014-01-05T19:13:00 2ND 2700 ST NaN 78702- POINT (-97.71357469983644 30.25453904942606)
24 NaN 879296.0 AUSTIN NaT NaN 702 E 3RD ST NaT Y NaN 585X ... 2014-11-20T14:56:00 542172.0 2014-11-20T15:15:00 3RD 702 ST NaN 78701- POINT (-97.73644375553381 30.26344102487212)
25 NaN 363482.0 AUSTIN NaT NaN 903 E 3RD ST NaT Y NaN 585X ... 2009-11-14T15:45:00 385181.0 2009-11-14T15:56:00 3RD 903 ST NaN 78702- POINT (-97.73452665606773 30.26240545658105)
26 NaN 363542.0 AUSTIN NaT NaN 1101 E 3RD ST NaT Y NaN 615B ... 2011-10-01T18:13:00 440169.0 2011-10-01T18:51:00 3RD 1101 ST NaN 78702- POINT (-97.73272716322229 30.2617831202706)
27 NaN 363542.0 AUSTIN NaT NaN 1101 E 3RD ST NaT Y NaN 615B ... 2014-08-28T00:30:00 533165.0 2014-08-28T16:35:00 3RD 1101 ST NaN 78702- POINT (-97.73272716322229 30.2617831202706)
28 NaN 363560.0 AUSTIN NaT NaN 1111 E 3RD ST NaT Y NaN 585X ... 2005-12-30T16:11:00 264315.0 2005-12-30T18:30:00 3RD 1111 ST NaN 78702- POINT (-97.73238424855897 30.2616633952571)
29 NaN 400856.0 AUSTIN NaT NaN 1112 E 3RD ST NaT Y NaN 585X ... 2005-05-23T13:14:00 242771.0 2005-05-23T17:21:00 3RD 1112 ST NaN 78702- POINT (-97.73226790726385 30.26218909997605)
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
70587 356219.0 530798.0 AUSTIN 2017-02-06T20:24:00 WATER SERVICE LEAK 7212 MEADOR AVE 2017-02-03T15:54:00 NaN NaN NaN ... NaT NaN NaT MEADOR 7212 AVE 1812528.0 78752- POINT (-97.69612767423286 30.33014639024331)
70588 414795.0 656817.0 AUSTIN 2017-02-10T18:28:00 WATER SERVICE LEAK 10308 GEORGIAN DR 2017-02-10T01:06:00 NaN NaN NaN ... NaT NaN NaT GEORGIAN 10308 DR 1814606.0 78753- POINT (-97.68759124418112 30.36886373358098)
70589 306333.0 538694.0 AUSTIN 2017-02-11T03:09:00 WATER SERVICE LEAK 8012 GREENSLOPE DR 2017-02-10T19:20:00 NaN NaN NaN ... NaT NaN NaT GREENSLOPE 8012 DR 1814722.0 78759- POINT (-97.75201769610545 30.36905158527984)
70590 871450.0 493298.0 AUSTIN 2017-02-14T03:06:00 WATER MAIN LEAK 6104 HYLAWN DR 2017-02-10T20:48:00 NaN NaN NaN ... NaT NaN NaT HYLAWN 6104 DR 1814736.0 78723- POINT (-97.67358467634561 30.30524625454306)
70591 1215766.0 153660.0 AUSTIN 2017-02-06T19:45:00 WATER METER LEAK 6421 WILLIAMS RIDGE WAY 2017-02-04T02:38:00 NaN NaN NaN ... NaT NaN NaT WILLIAMS RIDGE 6421 WAY 1812677.0 78731- POINT (-97.77575678484202 30.35432876830911)
70592 88698.0 657512.0 AUSTIN 2017-01-30T23:15:00 WATER METER LEAK 2703 GREENLEE DR 2017-01-30T19:10:00 NaN NaN NaN ... NaT NaN NaT GREENLEE 2703 DR 1810930.0 78703- POINT (-97.76649592905592 30.30036364526744)
70593 450316.0 3101444.0 AUSTIN 2017-02-01T20:45:00 WATER SERVICE LEAK 5316 ED BLUESTEIN BLVD SB 2017-02-01T19:18:00 NaN NaN NaN ... NaT NaN NaT ED BLUESTEIN 5316 BLVD 1811688.0 78723- POINT (-97.6648140744901 30.29155538276262)
70594 1494819.0 332588.0 AUSTIN 2017-02-02T04:56:00 WATER SERVICE LEAK 5209 MEADOW CREEK DR 2017-02-01T20:45:00 NaN NaN NaN ... NaT NaN NaT MEADOW CREEK 5209 DR 1812084.0 78745- POINT (-97.78444857807514 30.21466728210482)
70595 501413.0 615363.0 AUSTIN 2017-02-16T01:56:00 WATER SERVICE LEAK 4017 VICTORY DR UNIT 252 2017-02-15T03:07:00 NaN GATE CODE: 1739 NaN ... NaT NaN NaT VICTORY 4017 252 DR 1815701.0 78704- POINT (-97.78872926072586 30.23350949986936)
70596 1278289.0 549506.0 AUSTIN 2017-02-03T17:45:00 WATER METER LEAK 4800 N LAMAR BLVD 2017-02-02T19:35:00 NaN NaN NaN ... NaT NaN NaT LAMAR 4800 BLVD 1812277.0 78756- POINT (-97.73323830541952 30.31745561879857)
70597 29791.0 555260.0 AUSTIN 2017-02-07T23:44:00 WTR SVC DAMAGED BY CITY CREWS 4531 AVENUE F 2017-02-07T23:44:00 NaN NaN NaN ... NaT NaN NaT AVENUE F 4531 1813823.0 78751- POINT (-97.72606032578145 30.30999885700736)
70598 386973.0 209847.0 AUSTIN 2017-02-03T18:11:00 WATER SERVICE LEAK 5101 SUMMERSET TRL 2017-02-02T15:03:00 NaN NaN NaN ... NaT NaN NaT SUMMERSET 5101 TRL 1812261.0 78749- POINT (-97.84648270740318 30.2292945202867)
70599 943972.0 242289.0 AUSTIN 2017-02-17T01:07:00 WATER MAIN LEAK 415 BASTROP HWY NB 2017-02-16T18:52:00 NaN NaN NaN ... NaT NaN NaT BASTROP 415 HWY 1815766.0 78742- POINT (-97.68736689196109 30.23909036737392)
70600 315946.0 615818.0 AUSTIN 2017-02-13T00:39:00 WATER SERVICE LEAK 6120 FELIX AVE 2017-02-12T04:57:00 NaN NaN NaN ... NaT NaN NaT FELIX 6120 AVE 1814809.0 78741- POINT (-97.69805366462411 30.23612883532359)
70601 228104.0 724436.0 AUSTIN 2017-01-10T23:06:00 WATER SERVICE LEAK 1707 E 32ND ST 2017-01-10T16:40:00 NaN NaN NaN ... NaT NaN NaT 32ND 1707 ST 1806592.0 78722- POINT (-97.71634409049777 30.28631552139746)
70602 936565.0 610796.0 AUSTIN 2017-02-06T22:00:00 WATER MAIN LEAK 902 GARNER AVE 2017-02-03T19:31:00 NaN KERR AVE NaN ... NaT NaN NaT GARNER 902 AVE 1812654.0 78704- POINT (-97.76641252202596 30.25925828998614)
70603 1108480.0 204332.0 AUSTIN 2017-02-09T02:00:00 WATER METER LEAK 806 PARK BLVD UNIT 1 2017-02-08T17:58:00 NaN NaN NaN ... NaT NaN NaT PARK 806 1 BLVD 1814007.0 78751- POINT (-97.72219135503057 30.30206507689073)
70604 1494757.0 741988.0 AUSTIN 2017-01-31T20:48:00 WATER SERVICE LEAK 1904 CANONERO DR 2017-01-31T18:03:00 NaN NaN NaN ... NaT NaN NaT CANONERO 1904 DR 1811342.0 78746- POINT (-97.81307959800138 30.3183619749208)
70605 214418.0 716300.0 AUSTIN 2017-02-10T20:42:00 WATER SERVICE LEAK 4301 SPEEDWAY 2017-02-06T16:43:00 NaN NaN NaN ... NaT NaN NaT SPEEDWAY 4301 1812846.0 78751- POINT (-97.72948993180425 30.30650226337686)
70606 1494754.0 170486.0 AUSTIN 2017-01-31T21:16:00 WATER SERVICE LEAK 1011 W 10TH ST 2017-01-31T15:50:00 NaN BETWEEN BAYLOR AND N LAMAR NaN ... NaT NaN NaT 10TH 1011 ST 1811331.0 78703- POINT (-97.75321761374349 30.27502770389426)
70607 390620.0 265185.0 AUSTIN 2017-01-27T21:09:00 WATER SERVICE LEAK 6207 MORNING DEW DR 2017-01-27T15:09:00 NaN NaN NaN ... NaT NaN NaT MORNING DEW 6207 DR 1810599.0 78749- POINT (-97.84822614810216 30.23059073488725)
70608 390620.0 265185.0 AUSTIN 2017-01-27T21:09:00 WATER SERVICE LEAK 6207 MORNING DEW DR 2017-01-27T15:09:00 NaN NaN NaN ... NaT NaN NaT MORNING DEW 6207 DR 1811380.0 78749- POINT (-97.84822614810216 30.23059073488725)
70609 526992.0 590422.0 AUSTIN 2017-02-14T09:13:00 WATER SERVICE LEAK 1649 OHLEN RD 2017-02-09T20:45:00 NaN BANK OF AMERICA FINANCIAL CENTER NaN ... NaT NaN NaT OHLEN 1649 RD 1814590.0 78758- POINT (-97.71629701067313 30.36323955031358)
70610 1057527.0 337504.0 AUSTIN 2017-02-10T07:31:00 WATER MAIN LEAK 1416 ELM BROOK DR 2017-02-09T22:54:00 NaN SWEARINGEN NaN ... NaT NaN NaT ELM BROOK 1416 DR 1814602.0 78758- POINT (-97.69196714063571 30.39609749377057)
70611 479011.0 204342.0 AUSTIN 2017-02-11T04:41:00 WATER SERVICE LEAK 5728 REPUBLIC OF TEXAS BLVD 2017-02-10T01:19:00 NaN NaN NaN ... NaT NaN NaT REPUBLIC OF TEXAS 5728 BLVD 1814607.0 78735- POINT (-97.83924391121697 30.25143929904074)
70612 1194725.0 355628.0 AUSTIN 2017-02-07T21:19:00 WATER METER LEAK 1500 BRENTWOOD ST 2017-02-07T18:51:00 NaN NaN NaN ... NaT NaN NaT BRENTWOOD 1500 ST 1813689.0 78757- POINT (-97.72967930881964 30.33820583105881)
70613 405181.0 534489.0 AUSTIN 2017-02-06T22:30:00 WATER SERVICE LEAK 8500 KEARSARGE CV 2017-02-05T18:32:00 NaN NaN NaN ... NaT NaN NaT KEARSARGE 8500 CV 1812801.0 78749- POINT (-97.82358419028652 30.19189506598336)
70614 1493994.0 388862.0 AUSTIN 2017-01-25T04:45:00 WATER SERVICE LEAK 1223 WEST LYNN ST 2017-01-24T14:38:00 NaN 13TH ST. NaN ... NaT NaN NaT WEST LYNN 1223 ST 1809358.0 78703- POINT (-97.75832058342988 30.28088955049816)
70615 392294.0 292227.0 AUSTIN 2017-01-25T01:40:00 WATER SERVICE LEAK 7220 FIREFLY DR 2017-01-24T20:54:00 NaN NaN NaN ... NaT NaN NaT FIREFLY 7220 DR 1809798.0 78744- POINT (-97.74702063118784 30.17176266908997)
70616 188213.0 252971.0 AUSTIN 2017-02-09T03:53:00 WATER SERVICE LEAK 806 W 10TH ST 2017-02-08T17:01:00 NaN ON THE NORTH SIDE NaN ... NaT NaN NaT 10TH 806 ST 1813953.0 78701- POINT (-97.7498211266024 30.27442020454225)

70617 rows × 27 columns

In [12]:
# Add main columns
gdf['OBJECTID']=gdf.index # want to keep this so add as a property
gdf['REPO_Date']=gdf['COMPDTTM']
gdf['leak_id']=gdf.OBJECTID.apply(lambda x:'ATX-%s'%x)
gdf
In [90]:
# export all
gdf.REPO_Date = gdf.COMPDTTM
geojson = gdf.to_json()
geojson_file1 = data_path.dirname().joinpath('derived','austin_leaks.geojson')
open(geojson_file1,'w').write(geojson)
geojson_file1
Out [90]:
Path('../data/austin_leaks/derived/austin_leaks.geojson')
In [13]:
# split into inspections vs repairs
columns_inspections = ['leak_id','REPO_Date','ADDRKEY', 'CITY', 'FullStreetName', 'INSPFLAG', 'MAPNO', 'OBJECTID',
       'PREDIR', 'PRI', 'PROB', 'PROBCODE', 'PROBDESC', 'PROBDTTM', 'QTYCALLS',
       'SCHEDDTTM', 'SERVNO', 'STARTDTTM', 'STNAME', 'STNO', 'STSUB', 'SUFFIX',
       'ZIP', 'geometry']
columns_repairs = ['leak_id','REPO_Date','22', 'ADDRKEY', 'CITY', 'COMPDTTM', 'DESCRIPT', 'FullStreetName',
       'INITDTTM', 'LOC', 'OBJECTID', 'PREDIR', 'QTYCALLS', 'STNAME', 'STNO',
       'STSUB', 'SUFFIX', 'WONO', 'ZIP', 'geometry']

# gdf_inspections=gdf[pd.isnull(gdf.COMPDTTM)][columns_inspections]
# gdf_repairs=gdf[pd.isnull(gdf.COMPDTTM)==False][columns_repairs]
gdf_inspections=gdf['NaT'==gdf.COMPDTTM][columns_inspections]
gdf_repairs=gdf[('NaT'==gdf.COMPDTTM)==False][columns_repairs]


# export
geojson = gdf_inspections.to_json()
geojson_file_inspections = data_path.dirname().joinpath('derived','austin_leaks-inspections.geojson')
open(geojson_file_inspections,'w').write(geojson)
print(geojson_file_inspections)

geojson = gdf_repairs.to_json()
geojson_file_repairs = data_path.dirname().joinpath('derived','austin_leaks-repairs.geojson')
open(geojson_file_repairs,'w').write(geojson)
geojson_file_repairs
Out [13]:
../data/austin_leaks/derived/austin_leaks-inspections.geojson

Path('../data/austin_leaks/derived/austin_leaks-repairs.geojson')
In [ ]:
In [14]:
# small version of leaks
columns_whitelist = [
#  'INSPFLAG', # only 'Y' or NaN
#  'PRI',
#  'PROB',
#  'PROBDTTM',
 'SCHEDDTTM',
#  'SERVNO',
#  'MAPNO',
 'STARTDTTM',
 'PROBCODE',
 'PROBDESC',
#  'CITY',
#  'PREDIR',
#  'STNAME',
#  'STNO',
#  'STSUB',
#  'SUFFIX',
#  'ZIP',
#  'ADDRKEY',
 'FullStreetName',
#  'X',
#  'Y',
#  '22',
#  'QTYCALLS',
#  'INITDTTM',
#  'WONO',
 'LOC',
 'DESCRIPT',
 'COMPDTTM',
 'geometry'
]
geojson = gdf[columns_whitelist].to_json()
geojson_file = data_path.dirname().joinpath('derived','austin_leaks-small.geojson')
open(geojson_file,'w').write(geojson)
geojson_file
Out [14]:
Path('../data/austin_leaks/derived/austin_leaks-small.geojson')
In [15]:
# small version of repairs
columns_whitelist = [
#  'INSPFLAG', # only 'Y' or NaN
#  'PRI',
#  'PROB',
#  'PROBDTTM',
#  'SCHEDDTTM',
#  'SERVNO',
#  'MAPNO',
#  'STARTDTTM',
#  'PROBCODE',
#  'PROBDESC',
#  'CITY',
#  'PREDIR',
#  'STNAME',
#  'STNO',
#  'STSUB',
#  'SUFFIX',
#  'ZIP',
#  'ADDRKEY',
 'FullStreetName',
#  'X',
#  'Y',
#  '22',
#  'QTYCALLS',
#  'INITDTTM',
#  'WONO',
 'LOC',
 'DESCRIPT',
 'COMPDTTM',
 'geometry'
]
geojson = gdf_repairs[columns_whitelist].to_json()
geojson_file = data_path.dirname().joinpath('derived','austin_leaks-repairs-small.geojson')
open(geojson_file,'w').write(geojson)
geojson_file
Out [15]:
Path('../data/austin_leaks/derived/austin_leaks-repairs-small.geojson')
In [ ]:
In [16]:
# test
gdf2=gpd.read_file(geojson_file_repairs)
gdf2
Out [16]:
22 ADDRKEY CITY COMPDTTM DESCRIPT FullStreetName INITDTTM LOC OBJECTID PREDIR ... REPO_Date STNAME STNO STSUB SUFFIX WONO ZIP geometry id leak_id
0 195724.0 918296.0 AUSTIN 2009-08-24T19:56:00 WATER SERVICE LEAK 4703 MESA OAKS CIR 2009-08-23T00:20:00 TRAVIS COUNTRY CIR 47486 ... 2009-08-24T19:56:00 MESA OAKS 4703 CIR 580930.0 78735- POINT (-97.82460426524551 30.24299059253743) 47486 ATX-47486
1 353552.0 918014.0 AUSTIN 2009-08-24T20:00:00 WATER SERVICE LEAK 4842 CANYONBEND CIR 2009-08-23T00:50:00 None 47487 ... 2009-08-24T20:00:00 CANYONBEND 4842 CIR 580932.0 78735- POINT (-97.82675029997988 30.24219926503761) 47487 ATX-47487
2 959696.0 951692.0 AUSTIN 2009-08-22T06:05:00 WATER METER LEAK 9200 SAN JUAN PASS 2009-08-22T03:32:00 None 47488 ... 2009-08-22T06:05:00 SAN JUAN 9200 PASS 580915.0 78737- POINT (-97.90543523261422 30.21938883975655) 47488 ATX-47488
3 669430.0 201806.0 AUSTIN 2008-12-11T15:47:00 WATER METER LEAK 1503 WEST AVE 2008-12-08T16:54:00 None 47489 ... 2008-12-11T15:47:00 WEST 1503 AVE 444845.0 78701- POINT (-97.74688985035503 30.27942444961227) 47489 ATX-47489
4 979049.0 799934.0 AUSTIN 2008-12-12T03:09:00 WATER MAIN LEAK 4506 OAKMONT BLVD 2008-12-05T21:58:00 None 47490 ... 2008-12-12T03:09:00 OAKMONT 4506 BLVD 444473.0 78731- POINT (-97.75148894169132 30.32082458189908) 47490 ATX-47490
5 946496.0 656144.0 AUSTIN 2008-12-16T16:24:00 WATER METER LEAK 505 FORT DRUM DR 2008-12-12T16:54:00 None 47491 ... 2008-12-16T16:24:00 FORT DRUM 505 DR 445300.0 78745- POINT (-97.76771015540359 30.2064011266074) 47491 ATX-47491
6 902320.0 372398.0 AUSTIN 2008-12-04T05:52:00 WATER MAIN LEAK 2523 WINSTED LN 2008-12-03T23:54:00 None 47492 ... 2008-12-04T05:52:00 WINSTED 2523 LN 444166.0 78703- POINT (-97.75918111620598 30.29698530319657) 47492 ATX-47492
7 872558.0 221001.0 AUSTIN 2008-12-18T11:41:00 WATER MAIN LEAK 4101 GUADALUPE ST 2008-12-03T23:15:00 BETWEEN AVENUE A & W 41ST ST 47493 ... 2008-12-18T11:41:00 GUADALUPE 4101 ST 444169.0 78751- POINT (-97.7354500990549 30.30641876306587) 47493 ATX-47493
8 363824.0 919562.0 AUSTIN 2010-03-08T00:30:00 WATER SERVICE LEAK 4408 KNOB OAK LN 2010-03-07T16:11:00 None 47494 ... 2010-03-08T00:30:00 KNOB OAK 4408 LN 669389.0 78735- POINT (-97.82803137965742 30.24776340772963) 47494 ATX-47494
9 899641.0 107600.0 AUSTIN 2008-12-08T03:05:00 WATER MAIN LEAK 2613 FRANCISCO ST 2008-12-07T15:12:00 WEBBERVILLE RD 47495 ... 2008-12-08T03:05:00 FRANCISCO 2613 ST 444604.0 78702- POINT (-97.71051676533885 30.26298823681678) 47495 ATX-47495
10 936586.0 1153503.0 AUSTIN 2008-12-10T01:50:00 WATER MAIN LEAK 1508 NORRIS DR 2008-12-08T15:34:00 None 47496 ... 2008-12-10T01:50:00 NORRIS 1508 DR 444625.0 78704- POINT (-97.7750080315964 30.25760950079775) 47496 ATX-47496
11 489201.0 372434.0 AUSTIN 2009-08-24T21:52:00 WATER SERVICE LEAK 2204 TOWER DR 2009-08-22T16:04:00 None 47497 ... 2009-08-24T21:52:00 TOWER 2204 DR 580919.0 78703- POINT (-97.76163158879571 30.29738378720173) 47497 ATX-47497
12 1160881.0 825272.0 AUSTIN 2008-12-09T02:39:00 WATER SERVICE LEAK 1100 FLANAGAN CV 2008-12-08T17:27:00 None 47498 ... 2008-12-09T02:39:00 FLANAGAN 1100 CV 444848.0 78704- POINT (-97.76944922024389 30.23972304521572) 47498 ATX-47498
13 1160867.0 798422.0 AUSTIN 2008-12-07T18:49:00 WATER SERVICE LEAK 5212 GROVER AVE 2008-12-07T18:49:00 None 47499 ... 2008-12-07T18:49:00 GROVER 5212 AVE 444606.0 78756- POINT (-97.73446407455189 30.32291721101286) 47499 ATX-47499
14 1052524.0 729452.0 AUSTIN 2008-12-15T19:39:00 WATER MAIN LEAK 2015 CHESHIRE DR 2008-12-07T21:23:00 WELLINGTON DR 47500 ... 2008-12-15T19:39:00 CHESHIRE 2015 DR 444611.0 78723- POINT (-97.6885156595565 30.30999981781025) 47500 ATX-47500
15 898455.0 366044.0 AUSTIN 2009-08-23T01:25:00 WATER MAIN LEAK 4306 SCOTTSDALE RD 2009-08-22T18:54:00 HILLS SPRINGS CIR 47501 ... 2009-08-23T01:25:00 SCOTTSDALE 4306 RD 580924.0 78721- POINT (-97.6795479811319 30.28518479171067) 47501 ATX-47501
16 873286.0 520754.0 AUSTIN 2008-12-07T06:27:00 WATER MAIN LEAK 3104 BRYKER DR 2008-12-06T19:22:00 W 32ND ST 47502 ... 2008-12-07T06:27:00 BRYKER 3104 DR 444587.0 78703- POINT (-97.75502045361979 30.30422032928627) 47502 ATX-47502
17 874046.0 235514.0 AUSTIN 2009-08-25T01:15:00 WATER MAIN LEAK 6109 PARLIAMENT DR 2009-08-23T18:15:00 REGENCY DR 47503 ... 2009-08-25T01:15:00 PARLIAMENT 6109 DR 580947.0 78724- POINT (-97.6616555831656 30.28834178198006) 47503 ATX-47503
18 871510.0 179462.0 AUSTIN 2008-12-15T20:01:00 WATER MAIN LEAK 5304 MIDDALE LN 2008-12-11T00:33:00 None 47504 ... 2008-12-15T20:01:00 MIDDALE 5304 LN 445092.0 78723- POINT (-97.68836457533361 30.30438042438331) 47504 ATX-47504
19 181110.0 186050.0 AUSTIN 2008-12-11T12:27:00 WATER SERVICE LEAK 2612 BARTON HILLS DR 2008-12-11T02:17:00 None 47505 ... 2008-12-11T12:27:00 BARTON HILLS 2612 DR 445094.0 78704- POINT (-97.79419587998068 30.25309566083325) 47505 ATX-47505
20 249713.0 721136.0 AUSTIN 2008-12-13T04:54:00 WATER SERVICE LEAK 2100 MALDON PL 2008-12-12T17:54:00 None 47506 ... 2008-12-13T04:54:00 MALDON 2100 PL 445311.0 78722- POINT (-97.71585179399861 30.28287851870463) 47506 ATX-47506
21 1161013.0 783147.0 AUSTIN 2008-12-19T21:03:00 WATER SERVICE LEAK 8800 SPARKLING CREEK DR 2008-12-12T17:35:00 & PINERIDGE DR. 47507 ... 2008-12-19T21:03:00 SPARKLING CREEK 8800 DR 445312.0 78729- POINT (-97.77735835885383 30.44914289108704) 47507 ATX-47507
22 299119.0 804590.0 AUSTIN 2008-12-11T23:00:00 WATER SERVICE LEAK 2603 STECK AVE 2008-12-11T22:05:00 None 47508 ... 2008-12-11T23:00:00 STECK 2603 AVE 445248.0 78757- POINT (-97.73211207109489 30.36299194868041) 47508 ATX-47508
23 948437.0 431384.0 AUSTIN 2008-12-22T19:16:00 WATER METER LEAK 2610 MC CALLUM DR BLDG A 2008-12-16T15:39:00 None 47509 ... 2008-12-22T19:16:00 MC CALLUM 2610 A DR 445592.0 78703- POINT (-97.75207189419315 30.29547815644571) 47509 ATX-47509
24 353096.0 823412.0 AUSTIN 2008-12-16T21:40:00 WTR SVC DAMAGED BY CITY CREWS 5905 GLEN MEADOW DR 2008-12-16T17:38:00 None 47510 ... 2008-12-16T21:40:00 GLEN MEADOW 5905 DR 445594.0 78745- POINT (-97.77834994035153 30.20288444435117) 47510 ATX-47510
25 1131212.0 769148.0 AUSTIN 2008-12-24T17:54:00 WATER MAIN LEAK 3112 DANCY ST 2008-12-24T05:49:00 E. 32ND ST. 47511 ... 2008-12-24T17:54:00 DANCY 3112 ST 446675.0 78722- POINT (-97.72093549877751 30.2885654778701) 47511 ATX-47511
26 188003.0 756668.0 AUSTIN 2008-12-24T11:01:00 WATER MAIN LEAK 5008 WEST PARK DR 2008-12-24T05:45:00 None 47512 ... 2008-12-24T11:01:00 WEST PARK 5008 DR 446676.0 78731- POINT (-97.75333237392334 30.32847464805146) 47512 ATX-47512
27 938711.0 228981.0 AUSTIN 2009-01-06T21:17:00 WATER MAIN LEAK 3005 CATALINA DR 2008-12-23T00:33:00 None 47513 ... 2009-01-06T21:17:00 CATALINA 3005 DR 446706.0 78741- POINT (-97.73638362112345 30.22231564570604) 47513 ATX-47513
28 864689.0 633500.0 AUSTIN 2008-12-30T21:11:00 WATER MAIN LEAK 5615 SHOAL CREEK BLVD 2008-12-23T03:29:00 SHADY OAK CT 47514 ... 2008-12-30T21:11:00 SHOAL CREEK 5615 BLVD 446647.0 78756- POINT (-97.74840584359411 30.33333979644249) 47514 ATX-47514
29 938875.0 239062.0 AUSTIN 2007-02-09T09:41:00 WATER MAIN LEAK 600 W ST ELMO RD 2007-02-05T05:11:00 BETWEEN S 1ST ST & S 2ND ST 47515 W ... 2007-02-09T09:41:00 ST ELMO 600 RD 350826.0 78745- POINT (-97.77356215237674 30.22163467591938) 47515 ATX-47515
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
23101 356219.0 530798.0 AUSTIN 2017-02-06T20:24:00 WATER SERVICE LEAK 7212 MEADOR AVE 2017-02-03T15:54:00 None 70587 ... 2017-02-06T20:24:00 MEADOR 7212 AVE 1812528.0 78752- POINT (-97.69612767423286 30.33014639024331) 70587 ATX-70587
23102 414795.0 656817.0 AUSTIN 2017-02-10T18:28:00 WATER SERVICE LEAK 10308 GEORGIAN DR 2017-02-10T01:06:00 None 70588 ... 2017-02-10T18:28:00 GEORGIAN 10308 DR 1814606.0 78753- POINT (-97.68759124418112 30.36886373358098) 70588 ATX-70588
23103 306333.0 538694.0 AUSTIN 2017-02-11T03:09:00 WATER SERVICE LEAK 8012 GREENSLOPE DR 2017-02-10T19:20:00 None 70589 ... 2017-02-11T03:09:00 GREENSLOPE 8012 DR 1814722.0 78759- POINT (-97.75201769610545 30.36905158527984) 70589 ATX-70589
23104 871450.0 493298.0 AUSTIN 2017-02-14T03:06:00 WATER MAIN LEAK 6104 HYLAWN DR 2017-02-10T20:48:00 None 70590 ... 2017-02-14T03:06:00 HYLAWN 6104 DR 1814736.0 78723- POINT (-97.67358467634561 30.30524625454306) 70590 ATX-70590
23105 1215766.0 153660.0 AUSTIN 2017-02-06T19:45:00 WATER METER LEAK 6421 WILLIAMS RIDGE WAY 2017-02-04T02:38:00 None 70591 ... 2017-02-06T19:45:00 WILLIAMS RIDGE 6421 WAY 1812677.0 78731- POINT (-97.77575678484202 30.35432876830911) 70591 ATX-70591
23106 88698.0 657512.0 AUSTIN 2017-01-30T23:15:00 WATER METER LEAK 2703 GREENLEE DR 2017-01-30T19:10:00 None 70592 ... 2017-01-30T23:15:00 GREENLEE 2703 DR 1810930.0 78703- POINT (-97.76649592905592 30.30036364526744) 70592 ATX-70592
23107 450316.0 3101444.0 AUSTIN 2017-02-01T20:45:00 WATER SERVICE LEAK 5316 ED BLUESTEIN BLVD SB 2017-02-01T19:18:00 None 70593 ... 2017-02-01T20:45:00 ED BLUESTEIN 5316 BLVD 1811688.0 78723- POINT (-97.6648140744901 30.29155538276262) 70593 ATX-70593
23108 1494819.0 332588.0 AUSTIN 2017-02-02T04:56:00 WATER SERVICE LEAK 5209 MEADOW CREEK DR 2017-02-01T20:45:00 None 70594 ... 2017-02-02T04:56:00 MEADOW CREEK 5209 DR 1812084.0 78745- POINT (-97.78444857807514 30.21466728210482) 70594 ATX-70594
23109 501413.0 615363.0 AUSTIN 2017-02-16T01:56:00 WATER SERVICE LEAK 4017 VICTORY DR UNIT 252 2017-02-15T03:07:00 GATE CODE: 1739 70595 ... 2017-02-16T01:56:00 VICTORY 4017 252 DR 1815701.0 78704- POINT (-97.78872926072586 30.23350949986936) 70595 ATX-70595
23110 1278289.0 549506.0 AUSTIN 2017-02-03T17:45:00 WATER METER LEAK 4800 N LAMAR BLVD 2017-02-02T19:35:00 None 70596 N ... 2017-02-03T17:45:00 LAMAR 4800 BLVD 1812277.0 78756- POINT (-97.73323830541952 30.31745561879857) 70596 ATX-70596
23111 29791.0 555260.0 AUSTIN 2017-02-07T23:44:00 WTR SVC DAMAGED BY CITY CREWS 4531 AVENUE F 2017-02-07T23:44:00 None 70597 ... 2017-02-07T23:44:00 AVENUE F 4531 1813823.0 78751- POINT (-97.72606032578145 30.30999885700736) 70597 ATX-70597
23112 386973.0 209847.0 AUSTIN 2017-02-03T18:11:00 WATER SERVICE LEAK 5101 SUMMERSET TRL 2017-02-02T15:03:00 None 70598 ... 2017-02-03T18:11:00 SUMMERSET 5101 TRL 1812261.0 78749- POINT (-97.84648270740318 30.2292945202867) 70598 ATX-70598
23113 943972.0 242289.0 AUSTIN 2017-02-17T01:07:00 WATER MAIN LEAK 415 BASTROP HWY NB 2017-02-16T18:52:00 None 70599 ... 2017-02-17T01:07:00 BASTROP 415 HWY 1815766.0 78742- POINT (-97.68736689196109 30.23909036737392) 70599 ATX-70599
23114 315946.0 615818.0 AUSTIN 2017-02-13T00:39:00 WATER SERVICE LEAK 6120 FELIX AVE 2017-02-12T04:57:00 None 70600 ... 2017-02-13T00:39:00 FELIX 6120 AVE 1814809.0 78741- POINT (-97.69805366462411 30.23612883532359) 70600 ATX-70600
23115 228104.0 724436.0 AUSTIN 2017-01-10T23:06:00 WATER SERVICE LEAK 1707 E 32ND ST 2017-01-10T16:40:00 None 70601 E ... 2017-01-10T23:06:00 32ND 1707 ST 1806592.0 78722- POINT (-97.71634409049777 30.28631552139746) 70601 ATX-70601
23116 936565.0 610796.0 AUSTIN 2017-02-06T22:00:00 WATER MAIN LEAK 902 GARNER AVE 2017-02-03T19:31:00 KERR AVE 70602 ... 2017-02-06T22:00:00 GARNER 902 AVE 1812654.0 78704- POINT (-97.76641252202596 30.25925828998614) 70602 ATX-70602
23117 1108480.0 204332.0 AUSTIN 2017-02-09T02:00:00 WATER METER LEAK 806 PARK BLVD UNIT 1 2017-02-08T17:58:00 None 70603 ... 2017-02-09T02:00:00 PARK 806 1 BLVD 1814007.0 78751- POINT (-97.72219135503057 30.30206507689073) 70603 ATX-70603
23118 1494757.0 741988.0 AUSTIN 2017-01-31T20:48:00 WATER SERVICE LEAK 1904 CANONERO DR 2017-01-31T18:03:00 None 70604 ... 2017-01-31T20:48:00 CANONERO 1904 DR 1811342.0 78746- POINT (-97.81307959800138 30.3183619749208) 70604 ATX-70604
23119 214418.0 716300.0 AUSTIN 2017-02-10T20:42:00 WATER SERVICE LEAK 4301 SPEEDWAY 2017-02-06T16:43:00 None 70605 ... 2017-02-10T20:42:00 SPEEDWAY 4301 1812846.0 78751- POINT (-97.72948993180425 30.30650226337686) 70605 ATX-70605
23120 1494754.0 170486.0 AUSTIN 2017-01-31T21:16:00 WATER SERVICE LEAK 1011 W 10TH ST 2017-01-31T15:50:00 BETWEEN BAYLOR AND N LAMAR 70606 W ... 2017-01-31T21:16:00 10TH 1011 ST 1811331.0 78703- POINT (-97.75321761374349 30.27502770389426) 70606 ATX-70606
23121 390620.0 265185.0 AUSTIN 2017-01-27T21:09:00 WATER SERVICE LEAK 6207 MORNING DEW DR 2017-01-27T15:09:00 None 70607 ... 2017-01-27T21:09:00 MORNING DEW 6207 DR 1810599.0 78749- POINT (-97.84822614810216 30.23059073488725) 70607 ATX-70607
23122 390620.0 265185.0 AUSTIN 2017-01-27T21:09:00 WATER SERVICE LEAK 6207 MORNING DEW DR 2017-01-27T15:09:00 None 70608 ... 2017-01-27T21:09:00 MORNING DEW 6207 DR 1811380.0 78749- POINT (-97.84822614810216 30.23059073488725) 70608 ATX-70608
23123 526992.0 590422.0 AUSTIN 2017-02-14T09:13:00 WATER SERVICE LEAK 1649 OHLEN RD 2017-02-09T20:45:00 BANK OF AMERICA FINANCIAL CENTER 70609 ... 2017-02-14T09:13:00 OHLEN 1649 RD 1814590.0 78758- POINT (-97.71629701067313 30.36323955031358) 70609 ATX-70609
23124 1057527.0 337504.0 AUSTIN 2017-02-10T07:31:00 WATER MAIN LEAK 1416 ELM BROOK DR 2017-02-09T22:54:00 SWEARINGEN 70610 ... 2017-02-10T07:31:00 ELM BROOK 1416 DR 1814602.0 78758- POINT (-97.69196714063571 30.39609749377057) 70610 ATX-70610
23125 479011.0 204342.0 AUSTIN 2017-02-11T04:41:00 WATER SERVICE LEAK 5728 REPUBLIC OF TEXAS BLVD 2017-02-10T01:19:00 None 70611 ... 2017-02-11T04:41:00 REPUBLIC OF TEXAS 5728 BLVD 1814607.0 78735- POINT (-97.83924391121697 30.25143929904074) 70611 ATX-70611
23126 1194725.0 355628.0 AUSTIN 2017-02-07T21:19:00 WATER METER LEAK 1500 BRENTWOOD ST 2017-02-07T18:51:00 None 70612 ... 2017-02-07T21:19:00 BRENTWOOD 1500 ST 1813689.0 78757- POINT (-97.72967930881964 30.33820583105881) 70612 ATX-70612
23127 405181.0 534489.0 AUSTIN 2017-02-06T22:30:00 WATER SERVICE LEAK 8500 KEARSARGE CV 2017-02-05T18:32:00 None 70613 ... 2017-02-06T22:30:00 KEARSARGE 8500 CV 1812801.0 78749- POINT (-97.82358419028652 30.19189506598336) 70613 ATX-70613
23128 1493994.0 388862.0 AUSTIN 2017-01-25T04:45:00 WATER SERVICE LEAK 1223 WEST LYNN ST 2017-01-24T14:38:00 13TH ST. 70614 ... 2017-01-25T04:45:00 WEST LYNN 1223 ST 1809358.0 78703- POINT (-97.75832058342988 30.28088955049816) 70614 ATX-70614
23129 392294.0 292227.0 AUSTIN 2017-01-25T01:40:00 WATER SERVICE LEAK 7220 FIREFLY DR 2017-01-24T20:54:00 None 70615 ... 2017-01-25T01:40:00 FIREFLY 7220 DR 1809798.0 78744- POINT (-97.74702063118784 30.17176266908997) 70615 ATX-70615
23130 188213.0 252971.0 AUSTIN 2017-02-09T03:53:00 WATER SERVICE LEAK 806 W 10TH ST 2017-02-08T17:01:00 ON THE NORTH SIDE 70616 W ... 2017-02-09T03:53:00 10TH 806 ST 1813953.0 78701- POINT (-97.7498211266024 30.27442020454225) 70616 ATX-70616

23131 rows × 21 columns

In [ ]:

Working out columns

I have some labeled data I scraped from thier server, so I can use it to work out matching columns (and what db they come from).

In [ ]:
columns=range(29)
df1 = pd.read_csv(data_path, header=None ,nrows=47486, names=columns, index_col=0)
df1.T
df2 = pd.read_csv(data_path, header=None ,skiprows=47486, names=columns, index_col=0)
df2
In [11]:
# how to parse file times to to utc time
t=arrow.get('1/12/2017 15:56:00','D+/M+/YYYY HH:mm:ss',tzinfo='US/Central').astimezone(pytz.utc)
t.isoformat()
Out [11]:
'2017-12-01T21:56:00+00:00'
In [136]:
# compare this LWOPR
LWOPR_69665 = json.loads('''{
            "Editor": "InterAGO",
            "CreationDate": 1474259828703,
            "PROB": "WSLK",
            "STNO": " 11500",
            "QTYCALLS": "2",
            "STARTFLAG": "N",
            "CITY": "DEL VALLE",
            "SUFFIX": "ST",
            "SCHEDDTTM": null,
            "LOC": null,
            "ADDRKEY": 201879,
            "PROBDTTM": 1474124400000,
            "STSUB": " ",
            "DESCRIPT": "WATER SERVICE LEAK",
            "ZIP": "78617-    ",
            "COMPKEY": null,
            "Creator": "InterAGO",
            "EditDate": 1474259828703,
            "STNAME": "GEMMER",
            "INITDTTM": 1474131660000,
            "CMPLKEY": 612058,
            "PREDIR": " ",
            "WONO": "  1779246",
            "STARTDTTM": null,
            "COMPFLAG": "N",
            "OBJECTID": 808366,
            "X": 3144909.75,
            "FullStreetName": "11500 GEMMER ST",
            "MAPNO": "677X",
            "Y": 10031944
        }''')
LWOPR_69665
df2[df2.index==69665].T

# convert the times
# CreationDate 1474259828703 <Arrow [2016-09-18T23:37:08.703000-05:00]>
# PROBDTTM <Arrow [2016-09-17T10:00:00-05:00]>
# EditDate <Arrow [2016-09-18T23:37:08.703000-05:00]>
# INITDTTM <Arrow [2016-09-17T12:01:00-05:00]>
# arrow.get(arrow.get(1474131660000/1000).astimezone(pytz.timezone('Etc/GMT+5')))
Out [136]:
OBJECTID 69665
INSPFLAG NaN
PRI NaN
PROB NaN
PROBDTTM NaN
SCHEDDTTM NaN
SERVNO NaN
MAPNO NaN
STARTDTTM NaN
PROBCODE NaN
PROBDESC NaN
CITY DEL VALLE
PREDIR
STNAME GEMMER
STNO 11500
STSUB
SUFFIX ST
ZIP 78617-
ADDRKEY 201879
FullStreetName 11500 GEMMER ST
X 3.14491e+06
Y 1.00319e+07
22 475678
QTYCALLS 2
INITDTTM 9/17/2016 12:01:00
WONO 1779246
LOC NaN
DESCRIPT WATER SERVICE LEAK
COMPDTTM 9/19/2016 14:14:00
In [137]:
import json
# here's some lablled data from the server, it matche object id 5811
LPI_5811=json.loads('''{
            "SUFFIX": "DR",
            "EditDate": 1484668313471,
            "PROBDTTM": 1484258160000,
            "Editor": "InterAGO",
            "COMPKEY": null,
            "STARTDTTM": 1484262840000,
            "ZIP": "78745-    ",
            "ADDRKEY": 151694,
            "PROBDESC": "WATER MAIN LEAK",
            "PRI": "1",
            "PROBCODE": "WMNLK",
            "SERVNO": 623398,
            "FullStreetName": "5901 CHERRY CREEK DR",
            "PREDIR": " ",
            "OBJECTID": 393223,
            "CITY": "AUSTIN",
            "STNAME": "CHERRY CREEK",
            "QTYCALLS": "1",
            "STNO": "  5901",
            "Y": 10052310,
            "X": 3093377.5,
            "SCHEDDTTM": 1484258400000,
            "INSPDTTM": null,
            "Creator": "InterAGO",
            "MAPNO": "643G",
            "INSPFLAG": "N",
            "CreationDate": 1484668313471,
            "STSUB": " ",
            "PROB": "WMNLK"
        }''')
df1[df1.index==5811].T
Out [137]:
OBJECTID 5811
INSPFLAG Y
PRI 1
PROB WMNLK
PROBDTTM 1/12/2017 15:56:00
SCHEDDTTM 1/12/2017 16:00:00
SERVNO 623398
MAPNO 643G
STARTDTTM 1/12/2017 17:14:00
PROBCODE WMNLK
PROBDESC WATER MAIN LEAK
CITY AUSTIN
PREDIR
STNAME CHERRY CREEK
STNO 5901
STSUB
SUFFIX DR
ZIP 78745-
ADDRKEY 151694
FullStreetName 5901 CHERRY CREEK DR
X 3.09338e+06
Y 1.00523e+07
22 NaN
QTYCALLS 1
INITDTTM NaN
WONO NaN
LOC NaN
DESCRIPT NaN
COMPDTTM NaN

It all matches we are just missing some derived columns (editor, creationdate, creator). INSPDTTM is filled in as a flag not a date, it seems like it should be inspection datetime but perhaps it's used as something else.

In [14]:
df1[df1.FullStreetName=='2550 S IH 35 SVRD SB']
Out [14]:
INSPFLAG PRI PROB PROBDTTM SCHEDDTTM SERVNO MAPNO STARTDTTM PROBCODE PROBDESC ... FullStreetName X Y 22 QTYCALLS INITDTTM WONO LOC DESCRIPT COMPDTTM
OBJECTID
31782 Y 1 MTRLK 5/7/2008 10:40:00 5/7/2008 10:44:00 339071 615W 5/7/2008 13:10:00 MTRLK LEAKING WATER METER ... 2550 S IH 35 SVRD SB 3114324.01 10057239.4 NaN 1 NaN NaN NaN NaN NaN
31783 Y 1 SERLK 7/19/2016 18:11:00 7/19/2016 18:12:00 606278 615W 7/19/2016 19:06:00 SERLK LEAKING WATER SERVICE ... 2550 S IH 35 SVRD SB 3114324.01 10057239.4 NaN 2 NaN NaN NaN NaN NaN

2 rows × 28 columns

In [15]:
# # is it LRL24? no
# df2[df2[19]=='6100 TURTLE DOVE DR']
# # is it LWOPR yes, and the times match
# df2[df2[19]=='2550 S IH 35 SVRD SB']


s='''{
            "Editor": "InterAGO",
            "CreationDate": 1474259828703,
            "PROB": "WSLK",
            "STNO": "  2550",
            "QTYCALLS": "1",
            "STARTFLAG": "N",
            "CITY": "AUSTIN",
            "SUFFIX": "SVRD",
            "SCHEDDTTM": 1474036620000,
            "LOC": null,
            "ADDRKEY": 120610,
            "PROBDTTM": 1474032840000,
            "STSUB": " ",
            "DESCRIPT": "WATER SERVICE LEAK",
            "ZIP": "78704-    ",
            "COMPKEY": null,
            "Creator": "InterAGO",
            "EditDate": 1474259828703,
            "STNAME": "IH 35",
            "INITDTTM": 1474036620000,
            "CMPLKEY": 611961,
            "PREDIR": "S",
            "WONO": "  1779055",
            "STARTDTTM": null,
            "COMPFLAG": "N",
            "OBJECTID": 808365,
            "X": 3114324.01,
            "FullStreetName": "2550 S IH 35 SVRD SB",
            "MAPNO": "615W",
            "Y": 10057239.4
        }'''
LWOPR_69482 = json.loads(s)
LWOPR_69482
df2[df2.index==69482].T
Out [15]:
OBJECTID 69482
INSPFLAG NaN
PRI NaN
PROB NaN
PROBDTTM NaN
SCHEDDTTM NaN
SERVNO NaN
MAPNO NaN
STARTDTTM NaN
PROBCODE NaN
PROBDESC NaN
CITY AUSTIN
PREDIR S
STNAME IH 35
STNO 2550
STSUB
SUFFIX SVRD
ZIP 78704-
ADDRKEY 120610
FullStreetName 2550 S IH 35 SVRD SB
X 3.11432e+06
Y 1.00572e+07
22 1482278
QTYCALLS 2
INITDTTM 9/16/2016 9:37:00
WONO 1779055
LOC NaN
DESCRIPT WATER SERVICE LEAK
COMPDTTM 9/22/2016 23:20:00
In [16]:
LRL24h_70392=json.loads('''{
            "LOC": null,
            "SUFFIX": "AVE",
            "EditDate": 1483977171052,
            "PROBDTTM": null,
            "Editor": "InterAGO",
            "DescriptionPROB": null,
            "STARTDTTM": null,
            "ZIP": "78759-    ",
            "WO_SR": null,
            "ADDRKEY": 251075,
            "PROBDESC": null,
            "DESCRIPT": "WATER SERVICE LEAK",
            "SERVNO": null,
            "FullStreetName": "3921 STECK AVE",
            "WONO": "  1802421",
            "PREDIR": " ",
            "OBJECTID": 1146678,
            "CITY": "AUSTIN",
            "STNAME": "STECK",
            "QTYCALLS": "3",
            "STNO": "  3921",
            "EndDTTM": null,
            "Y": 10108685,
            "X": 3110431.5,
            "SCHEDDTTM": null,
            "INSPDTTM": 1483890420000,
            "Creator": "InterAGO",
            "COMPDTTM": 1483936920000,
            "MAPNO": null,
            "INITDTTM": 1483889280000,
            "CreationDate": 1483977171052,
            "STSUB": " "
        }''')
df[df.ADDRKEY==251075].T
# 1483936920000 => <Arrow [2017-01-08T23:42:00-05:00]>
Out [16]:
OBJECTID 70392
INSPFLAG NaN
PRI NaN
PROB NaN
PROBDTTM NaN
SCHEDDTTM NaN
SERVNO NaN
MAPNO NaN
STARTDTTM NaN
PROBCODE NaN
PROBDESC NaN
CITY AUSTIN
PREDIR
STNAME STECK
STNO 3921
STSUB
SUFFIX AVE
ZIP 78759-
ADDRKEY 251075
FullStreetName 3921 STECK AVE
X 3.11043e+06
Y 1.01087e+07
22 1.49293e+06
QTYCALLS 3
INITDTTM 1/8/2017 9:28:00
WONO 1.80242e+06
LOC NaN
DESCRIPT WATER SERVICE LEAK
COMPDTTM 1/8/2017 22:42:00

Analysing times

In [332]:
# difference between problem time and scheduled time in days
t=(df.SCHEDDTTM-df.PROBDTTM).dropna()

# remove outliers then plot
t2=t[np.abs(t.astype(int))<(26*864e11)]
t2.astype(int).hist(bins=200)

t.describe()
Out [332]:
count                      47444
mean      1 days 15:52:42.681898
std      26 days 00:56:38.563898
min         -2122 days +21:09:00
25%              0 days 00:03:00
50%              0 days 00:09:00
75%              0 days 01:33:00
max           3054 days 09:23:00
dtype: object
In [336]:
# difference between problem time and scheduled time in days
t=(df.STARTDTTM-df.SCHEDDTTM).dropna()

# remove outliers then plot
t2=t[np.abs(t.astype(int))<(26*864e11)]
t2.astype(int).hist(bins=200)

t.describe()
Out [336]:
count                      47444
mean      0 days 19:56:44.951606
std      17 days 20:32:48.486246
min           -49 days +02:43:00
25%              0 days 00:29:00
50%              0 days 00:56:00
75%              0 days 03:20:00
max           2121 days 04:11:00
dtype: object
In [333]:
t=(df.SCHEDDTTM-df.PROBDTTM).dropna()
# remove outliers then plot
t2=t[np.abs(t.astype(int))<(26*864e11)]
t2.astype(int).hist(bins=200)

t.describe()
Out [333]:
count                      47444
mean      1 days 15:52:42.681898
std      26 days 00:56:38.563898
min         -2122 days +21:09:00
25%              0 days 00:03:00
50%              0 days 00:09:00
75%              0 days 01:33:00
max           3054 days 09:23:00
dtype: object
In [334]:
# difference between initial time and completed time in days
t=(df.COMPDTTM-df.INITDTTM).dropna()

# remove outliers then plot
t2=t[np.abs(t.astype(int))<(26*864e11)]
t2.astype(int).hist(bins=200)

t.describe()
Out [334]:
count                      23131
mean      4 days 08:26:02.819592
std      25 days 10:48:00.505569
min              0 days 00:00:00
25%              0 days 06:42:00
50%              0 days 14:36:00
75%              2 days 07:54:00
max           1065 days 05:42:00
dtype: object
Reference in New Issue View Git Blame Copy Permalink