In [1]:
!pip install pg8000
!pip install config
Requirement already satisfied (use --upgrade to upgrade): pg8000 in /Users/Monica/.virtualenvs/dataanalysis/lib/python3.5/site-packages
Requirement already satisfied (use --upgrade to upgrade): six>=1.10.0 in /Users/Monica/.virtualenvs/dataanalysis/lib/python3.5/site-packages (from pg8000)
Requirement already satisfied (use --upgrade to upgrade): config in /Users/Monica/.virtualenvs/dataanalysis/lib/python3.5/site-packages
In [2]:
# import config
In [3]:
import pg8000
conn = pg8000.connect(user="dot_student", host="training.c1erymiua9dx.us-east-1.rds.amazonaws.com", port=5432, database="training", password="qgis")
In [4]:
conn.rollback()
In [5]:
cursor = conn.cursor()
In [6]:
cursor.execute("select column_name from information_schema.columns where table_name='dot_311'")
column_list = []
for row in cursor.fetchall():
column_list.append(row[0])
column_list
Out[6]:
['gid',
'unique_key',
'agency',
'agency nam',
'complaint',
'descriptor',
'location t',
'incident z',
'incident a',
'street nam',
'cross stre',
'cross st_1',
'intersecti',
'intersec_1',
'address ty',
'city',
'landmark',
'facility t',
'status',
'due date',
'resolution',
'resoluti_1',
'community',
'borough',
'x coordina',
'y coordina',
'park facil',
'park borou',
'school nam',
'school num',
'school reg',
'school cod',
'school pho',
'school add',
'school cit',
'school sta',
'school zip',
'school not',
'school or',
'vehicle ty',
'taxi compa',
'taxi pick',
'bridge hig',
'bridge h_1',
'road ramp',
'bridge h_2',
'garage lot',
'ferry dire',
'ferry term',
'latitude',
'longitude',
'location',
'geom',
'created_date',
'closed_date']
In [7]:
response = "select * from dot_311"
In [8]:
cursor.execute("select * from dot_311")
output = []
for row in cursor.fetchall():
output.append(row)
In [9]:
import pandas as pd
/Users/Monica/.virtualenvs/dataanalysis/lib/python3.5/site-packages/matplotlib/__init__.py:1035: UserWarning: Duplicate key in file "/Users/Monica/.matplotlib/matplotlibrc", line #2
(fname, cnt))
In [10]:
df = pd.DataFrame(data=output, columns=column_list)
In [11]:
print(df)
gid unique_key agency agency nam \
0 2 32570549 DOT Department of Transportation
1 3 32572958 DOT Department of Transportation
2 4 32573576 DOT Department of Transportation
3 5 32572389 DOT Department of Transportation
4 6 32570548 DOT Department of Transportation
5 7 32574044 DOT Department of Transportation
6 8 32573047 DOT Department of Transportation
7 9 32572971 DOT Department of Transportation
8 10 32575571 DOT Department of Transportation
9 11 32576008 DOT Department of Transportation
10 12 32573945 DOT Department of Transportation
11 13 32570682 DOT Department of Transportation
12 14 32572906 DOT Department of Transportation
13 15 32579102 DOT Department of Transportation
14 519 32578446 DOT Department of Transportation
15 16 32573686 DOT Department of Transportation
16 18 32570317 DOT Department of Transportation
17 19 32581124 DOT Department of Transportation
18 20 32579277 DOT Department of Transportation
19 21 32580720 DOT Department of Transportation
20 22 32571895 DOT Department of Transportation
21 23 32580285 DOT Department of Transportation
22 24 32575419 DOT Department of Transportation
23 25 32570671 DOT Department of Transportation
24 26 32581170 DOT Department of Transportation
25 27 32581434 DOT Department of Transportation
26 28 32575447 DOT Department of Transportation
27 29 32577440 DOT Department of Transportation
28 30 32577560 DOT Department of Transportation
29 31 32575033 DOT Department of Transportation
... ... ... ... ...
5821 5808 32614085 DOT Department of Transportation
5822 5815 32612339 DOT Department of Transportation
5823 5816 32612333 DOT Department of Transportation
5824 5817 32614717 DOT Department of Transportation
5825 5818 32617685 DOT Department of Transportation
5826 5819 32615195 DOT Department of Transportation
5827 5820 32613947 DOT Department of Transportation
5828 5821 32612566 DOT Department of Transportation
5829 5822 32614725 DOT Department of Transportation
5830 5823 32617705 DOT Department of Transportation
5831 5837 32620216 DOT Department of Transportation
5832 5824 32617828 DOT Department of Transportation
5833 5825 32618570 DOT Department of Transportation
5834 5826 32616052 DOT Department of Transportation
5835 5827 32612279 DOT Department of Transportation
5836 5828 32614241 DOT Department of Transportation
5837 5829 32617383 DOT Department of Transportation
5838 5830 32618573 DOT Department of Transportation
5839 5831 32617669 DOT Department of Transportation
5840 5832 32612533 DOT Department of Transportation
5841 5834 32618378 DOT Department of Transportation
5842 5835 32622707 DOT Department of Transportation
5843 5836 32622721 DOT Department of Transportation
5844 5838 32613217 DOT Department of Transportation
5845 5839 32623981 DOT Department of Transportation
5846 5840 32617301 DOT Department of Transportation
5847 5848 32614083 DOT Department of Transportation
5848 5849 32612807 DOT Department of Transportation
5849 5850 32617735 DOT Department of Transportation
5850 5851 32619242 DOT Department of Transportation
complaint descriptor location t \
0 Traffic Signal Condition Controller None
1 Traffic Signal Condition Controller None
2 Traffic Signal Condition Controller None
3 Traffic Signal Condition Controller None
4 Traffic Signal Condition Controller None
5 Traffic Signal Condition Controller None
6 Traffic Signal Condition Controller None
7 Traffic Signal Condition Controller None
8 Traffic Signal Condition Base Door None
9 Street Condition Pothole None
10 Street Condition Plate Condition - Noisy Street
11 Street Light Condition Street Light Out None
12 Traffic Signal Condition Post None
13 Traffic Signal Condition Pedestrian Signal None
14 Street Light Condition Lamppost Knocked Down None
15 Street Light Condition Street Light Out None
16 Street Condition Line/Marking - Faded Street
17 Street Light Condition Street Light Out None
18 Street Light Condition Street Light Out None
19 Street Light Condition Street Light Out None
20 Street Condition Pothole None
21 Street Light Condition Street Light Out None
22 Sidewalk Condition Newspaper Box Complaint Sidewalk
23 Street Condition Pothole None
24 Street Light Condition Street Light Out None
25 Traffic Signal Condition Controller None
26 Street Light Condition Street Light Out None
27 Street Light Condition Street Light Out None
28 Traffic Signal Condition LED Pedestrian Unit None
29 Street Condition Pothole None
... ... ... ...
5821 Street Condition Plate Condition - Shifted Street
5822 Street Condition Plate Condition - Noisy Street
5823 Street Condition Plate Condition - Noisy Street
5824 Street Light Condition Street Light Out None
5825 Street Light Condition Street Light Out None
5826 Street Condition Plate Condition - Noisy Street
5827 Highway Condition Pothole - Highway Highway
5828 Street Light Condition Lamppost Knocked Down None
5829 Street Light Condition Street Light Out None
5830 Street Sign - Missing Bus Stop Street
5831 Street Condition Pothole None
5832 Curb Condition Defacement Curb
5833 Street Sign - Missing Bus Stop Street
5834 Street Condition Cave-in Street
5835 Street Condition Plate Condition - Shifted Street
5836 Street Condition Plate Condition - Noisy Street
5837 Street Light Condition Street Light Out None
5838 Street Condition Pothole None
5839 Street Condition Pothole None
5840 Street Light Condition Street Light Out None
5841 Traffic Signal Condition Controller None
5842 Street Condition Pothole None
5843 Street Condition Pothole None
5844 Street Condition Plate Condition - Shifted Street
5845 Street Condition Pothole None
5846 Street Light Condition Street Light Out None
5847 Street Condition Cave-in Street
5848 Street Light Condition Street Light Out None
5849 Bridge Condition Pothole Bridge
5850 Street Light Condition Street Light Out None
incident z incident a street nam \
0 10301 None None
1 10301 None None
2 10310 None None
3 None None None
4 None None None
5 None None None
6 None None None
7 11235 None None
8 None None None
9 10011 30 WEST 15 STREET WEST 15 STREET
10 10014 None None
11 11234 2015 MILL AVENUE MILL AVENUE
12 11237 None None
13 None None None
14 None None None
15 11231 455 UNION STREET UNION STREET
16 11234 None None
17 None VAN WYCK EXPY VAN WYCK EXPY
18 10458 None None
19 10312 167 WOODROW ROAD WOODROW ROAD
20 11363 41-53 GLENWOOD STREET GLENWOOD STREET
21 None None None
22 11226 1501 FOSTER AVENUE FOSTER AVENUE
23 11223 87 AVENUE U AVENUE U
24 None WILLIAMSBURG BRIDGE WILLIAMSBURG BRIDGE
25 None None None
26 11209 None None
27 10006 LIBERTY STREET LIBERTY STREET
28 None None None
29 11223 2076 WEST 7 STREET WEST 7 STREET
... ... ... ...
5821 10463 3530 HENRY HUDSON PARKWAY HENRY HUDSON PARKWAY
5822 10023 None None
5823 10023 111 WEST 71 STREET WEST 71 STREET
5824 11385 90-60 UNION TURNPIKE UNION TURNPIKE
5825 11201 155 ATLANTIC AVENUE ATLANTIC AVENUE
5826 10023 None None
5827 11231 None None
5828 None PROSPECT EXPY PROSPECT EXPY
5829 11434 114-15 174 STREET 174 STREET
5830 10022 None None
5831 10032 3933 BROADWAY BROADWAY
5832 10463 3533 RIVERDALE AVENUE RIVERDALE AVENUE
5833 10022 None None
5834 11208 586 SOUTH CONDUIT AVENUE SOUTH CONDUIT AVENUE
5835 10024 132 WEST 82 STREET WEST 82 STREET
5836 10014 None None
5837 11421 None None
5838 10018 476 11 AVENUE 11 AVENUE
5839 10018 462 11 AVENUE 11 AVENUE
5840 None None None
5841 10467 None None
5842 10465 None None
5843 10033 None None
5844 10025 None None
5845 10031 600 WEST 141 STREET WEST 141 STREET
5846 None None None
5847 10019 236 WEST 52 STREET WEST 52 STREET
5848 None 226 111 ST W 111 ST W
5849 11378 None None
5850 10459 None None
... bridge h_2 \
0 ... None
1 ... None
2 ... None
3 ... None
4 ... None
5 ... None
6 ... None
7 ... None
8 ... None
9 ... None
10 ... None
11 ... None
12 ... None
13 ... None
14 ... None
15 ... None
16 ... None
17 ... None
18 ... None
19 ... None
20 ... None
21 ... None
22 ... None
23 ... None
24 ... None
25 ... None
26 ... None
27 ... None
28 ... None
29 ... None
... ... ...
5821 ... None
5822 ... None
5823 ... None
5824 ... None
5825 ... None
5826 ... None
5827 ... Atlantic Ave (Exit 27) - Hamilton Ave (Exit 26)
5828 ... None
5829 ... None
5830 ... None
5831 ... None
5832 ... None
5833 ... None
5834 ... None
5835 ... None
5836 ... None
5837 ... None
5838 ... None
5839 ... None
5840 ... None
5841 ... None
5842 ... None
5843 ... None
5844 ... None
5845 ... None
5846 ... None
5847 ... None
5848 ... None
5849 ... Roadway
5850 ... None
garage lot ferry dire ferry term latitude \
0 None None None 40.613803753157917
1 None None None 40.626545297464929
2 None None None 40.629249554787492
3 None None None None
4 None None None None
5 None None None None
6 None None None None
7 None None None 40.581970000748562
8 None None None None
9 None None None 40.737163011181920
10 None None None 40.736839213422464
11 None None None 40.615433104276697
12 None None None 40.705217029080806
13 None None None None
14 None None None None
15 None None None 40.680332444518470
16 None None None 40.615433104276697
17 None None None None
18 None None None 40.871343985427906
19 None None None 40.560603307473166
20 None None None 40.773767941165900
21 None None None None
22 None None None 40.634088319895888
23 None None None 40.596118490543070
24 None None None None
25 None None None None
26 None None None 40.636800952626160
27 None None None 40.709687536520519
28 None None None None
29 None None None 40.597073252537918
... ... ... ... ...
5821 None None None 40.886428893610784
5822 None None None 40.776106560588481
5823 None None None 40.777001370729927
5824 None None None 40.705987411554943
5825 None None None 40.690757413845581
5826 None None None 40.776106560588481
5827 None None None 40.685471044784713
5828 None None None None
5829 None None None 40.690730612393871
5830 None None None 40.759308045196732
5831 None None None 40.838769242057694
5832 None None None 40.886043273551003
5833 None None None 40.762288004988541
5834 None None None 40.676433814244696
5835 None None None 40.784345355522767
5836 None None None 40.731775196703985
5837 None None None 40.697558165916313
5838 None None None 40.758500829065163
5839 None None None 40.757940900545982
5840 None None None None
5841 None None None 40.884348470484561
5842 None None None 40.834402513003816
5843 None None None 40.847998812236256
5844 None None None 40.796739381719725
5845 None None None 40.824046272945296
5846 None None None None
5847 None None None 40.762968139787574
5848 None None None None
5849 None None None 40.728220546482930
5850 None None None 40.832344487846150
longitude location \
0 -74.113373738864198 (40.61380375315792, -74.1133737388642)
1 -74.091582334480577 (40.62654529746493, -74.09158233448058)
2 -74.123220752604595 (40.62924955478749, -74.1232207526046)
3 None None
4 None None
5 None None
6 None None
7 -73.954941416795819 (40.58197000074856, -73.95494141679582)
8 None None
9 -73.994262543022671 (40.73716301118192, -73.99426254302267)
10 -74.003713090903730 (40.73683921342246, -74.00371309090373)
11 -73.913875359186989 (40.6154331042767, -73.91387535918699)
12 -73.920684296190075 (40.705217029080806, -73.92068429619007)
13 None None
14 None None
15 -73.990532250064405 (40.68033244451847, -73.9905322500644)
16 -73.913875359186989 (40.6154331042767, -73.91387535918699)
17 None None
18 -73.886917542990247 (40.871343985427906, -73.88691754299025)
19 -74.180428734412558 (40.560603307473166, -74.18042873441256)
20 -73.736599442397463 (40.7737679411659, -73.73659944239746)
21 None None
22 -73.963037950455359 (40.63408831989589, -73.96303795045536)
23 -73.981084780913918 (40.59611849054307, -73.98108478091392)
24 None None
25 None None
26 -74.034441426326580 (40.63680095262616, -74.03444142632658)
27 -74.011672174837173 (40.70968753652052, -74.01167217483717)
28 None None
29 -73.978643105123780 (40.59707325253792, -73.97864310512378)
... ... ...
5821 -73.912247640634590 (40.886428893610784, -73.91224764063459)
5822 -73.979744680840554 (40.77610656058848, -73.97974468084055)
5823 -73.979892444176258 (40.77700137072993, -73.97989244417626)
5824 -73.857175216458685 (40.70598741155494, -73.85717521645869)
5825 -73.996231780059787 (40.69075741384558, -73.99623178005979)
5826 -73.979744680840554 (40.77610656058848, -73.97974468084055)
5827 -74.000811274900897 (40.68547104478471, -74.0008112749009)
5828 None None
5829 -73.775489661073280 (40.69073061239387, -73.77548966107328)
5830 -73.965293087379791 (40.75930804519673, -73.96529308737979)
5831 -73.941449993230449 (40.838769242057694, -73.94144999323045)
5832 -73.907000540789284 (40.886043273551, -73.90700054078928)
5833 -73.972363181607278 (40.76228800498854, -73.97236318160728)
5834 -73.868147788780064 (40.676433814244696, -73.86814778878006)
5835 -73.975141734145680 (40.78434535552277, -73.97514173414568)
5836 -74.001165439473155 (40.731775196703985, -74.00116543947316)
5837 -73.852771919825358 (40.69755816591631, -73.85277191982536)
5838 -73.999985561522166 (40.75850082906516, -73.99998556152217)
5839 -74.000389835624617 (40.75794090054598, -74.00038983562462)
5840 None None
5841 -73.887050930441191 (40.88434847048456, -73.88705093044119)
5842 -73.823194164076611 (40.834402513003816, -73.82319416407661)
5843 -73.934693640817514 (40.847998812236256, -73.93469364081751)
5844 -73.960979269074201 (40.796739381719725, -73.9609792690742)
5845 -73.952699924071808 (40.824046272945296, -73.95269992407181)
5846 None None
5847 -73.983853089123315 (40.762968139787574, -73.98385308912331)
5848 None None
5849 -73.928569218350148 (40.72822054648293, -73.92856921835015)
5850 -73.892686133209040 (40.83234448784615, -73.89268613320904)
geom created_date \
0 0101000020E61000006829ED83418752C0D7FC121F914E... 2016-02-01 00:12:00
1 0101000020E610000011DF267CDC8552C08809E5A23250... 2016-02-01 00:14:00
2 0101000020E610000004A84BD9E28752C0DC6AD93F8B50... 2016-02-01 00:15:00
3 0101000020E610000004A84BD9E28752C0DC6AD93F8B50... 2016-02-01 00:17:00
4 0101000020E610000004A84BD9E28752C0DC6AD93F8B50... 2016-02-01 00:19:00
5 0101000020E610000004A84BD9E28752C0DC6AD93F8B50... 2016-02-01 00:21:00
6 0101000020E610000004A84BD9E28752C0DC6AD93F8B50... 2016-02-01 00:22:00
7 0101000020E6100000F9AE9AC21D7D52C0ED3B34FE7D4A... 2016-02-01 00:27:00
8 0101000020E6100000F9AE9AC21D7D52C0ED3B34FE7D4A... 2016-02-01 00:32:00
9 0101000020E6100000E47A5CFFA17F52C0726C885B5B5E... 2016-02-01 00:32:16
10 0101000020E6100000E9FFD4D53C8052C0A90752BF505E... 2016-02-01 00:40:25
11 0101000020E6100000041513EF7C7A52C041DF0F83C64E... 2016-02-01 00:44:00
12 0101000020E6100000F084D37DEC7A52C0FF3D368D445A... 2016-02-01 00:50:00
13 0101000020E6100000F084D37DEC7A52C0FF3D368D445A... 2016-02-01 00:53:00
14 0101000020E61000004E87A4F7C28B52C03CEA0200E146... 2016-02-01 14:26:00
15 0101000020E61000003CEA60E1647F52C0AACE2F221557... 2016-02-01 00:57:00
16 0101000020E6100000041513EF7C7A52C041DF0F83C64E... 2016-02-01 01:18:49
17 0101000020E6100000041513EF7C7A52C041DF0F83C64E... 2016-02-01 01:20:00
18 0101000020E61000001359CC41C37852C0557D2033886F... 2016-02-01 01:36:00
19 0101000020E6100000E163F6248C8B52C037D063D9C147... 2016-02-01 01:36:00
20 0101000020E610000055D6FC71246F52C01A00F1D30A63... 2016-02-01 01:51:36
21 0101000020E610000055D6FC71246F52C01A00F1D30A63... 2016-02-01 02:07:00
22 0101000020E6100000CF80ED69A27D52C03D5D5ACE2951... 2016-02-01 02:32:08
23 0101000020E61000003B28D217CA7E52C031B6569C4D4C... 2016-02-01 02:43:01
24 0101000020E61000003B28D217CA7E52C031B6569C4D4C... 2016-02-01 03:21:00
25 0101000020E61000003B28D217CA7E52C031B6569C4D4C... 2016-02-01 03:24:00
26 0101000020E6100000D1ECCF49348252C0A6CB90B18251... 2016-02-01 03:25:00
27 0101000020E6100000BA4CA63CBF8052C002DE8B0AD75A... 2016-02-01 03:26:00
28 0101000020E6100000BA4CA63CBF8052C002DE8B0AD75A... 2016-02-01 03:37:00
29 0101000020E61000009ABDB016A27E52C0BD7B76E56C4C... 2016-02-01 03:49:22
... ... ...
5821 0101000020E61000003D98ED43627A52C0D62482807671... 2016-02-06 22:07:24
5822 0101000020E6100000F8A80823B47E52C023F8B3755763... 2016-02-06 22:23:07
5823 0101000020E61000006955CC8EB67E52C0B91DEAC77463... 2016-02-06 22:25:28
5824 0101000020E61000006F6870F5DB7652C01202A6CB5D5A... 2016-02-06 22:27:00
5825 0101000020E6100000EEA5F042C27F52C0D9F72ABD6A58... 2016-02-06 22:32:00
5826 0101000020E6100000F8A80823B47E52C023F8B3755763... 2016-02-06 22:34:14
5827 0101000020E6100000B7CABB4A0D8052C047DAE383BD57... 2016-02-06 22:34:34
5828 0101000020E6100000B7CABB4A0D8052C047DAE383BD57... 2016-02-06 22:35:00
5829 0101000020E6100000892C639FA17152C0F34957DC6958... 2016-02-06 22:41:00
5830 0101000020E61000007856A85CC77D52C0D6DA8A013161... 2016-02-06 22:42:29
5831 0101000020E6100000A1EF78B7407C52C0A6C35FCA5C6B... 2016-02-06 23:35:42
5832 0101000020E61000003C09FF4B0C7A52C0055FB1DD6971... 2016-02-06 22:47:30
5833 0101000020E61000009E35C8323B7E52C089C741A79261... 2016-02-06 22:49:43
5834 0101000020E6100000ED39BEBB8F7752C0750B1B629556... 2016-02-06 22:53:35
5835 0101000020E6100000B547E0B8687E52C0B15EB96D6564... 2016-02-06 22:55:05
5836 0101000020E61000000D1B3518138052C00DEF44CFAA5D... 2016-02-06 22:56:01
5837 0101000020E610000036A6ACD0937652C08BD502964959... 2016-02-06 22:59:00
5838 0101000020E61000006FCD70C3FF7F52C071691F8E1661... 2016-02-06 23:02:17
5839 0101000020E61000008CD01663068052C0A9121A350461... 2016-02-06 23:02:35
5840 0101000020E61000008CD01663068052C0A9121A350461... 2016-02-06 23:06:00
5841 0101000020E610000067084471C57852C0D97FA7543271... 2016-02-06 23:09:00
5842 0101000020E6100000E53D9336AF7452C09B8698B3CD6A... 2016-02-06 23:33:49
5843 0101000020E6100000C9C54605D27B52C000CD9E398B6C... 2016-02-06 23:35:02
5844 0101000020E6100000B13331AF807D52C0F3F5598EFB65... 2016-02-06 23:36:15
5845 0101000020E610000091321A09F97C52C06A5828597A69... 2016-02-06 23:36:20
5846 0101000020E610000091321A09F97C52C06A5828597A69... 2016-02-06 23:37:00
5847 0101000020E61000009B76F272F77E52C08823A4F0A861... 2016-02-06 23:46:22
5848 0101000020E61000009B76F272F77E52C08823A4F0A861... 2016-02-06 23:53:00
5849 0101000020E6100000B73896AD6D7B52C0B0B5B354365D... 2016-02-06 23:54:02
5850 0101000020E61000006FEE04C5217952C00D27A1438A6A... 2016-02-07 00:00:00
closed_date
0 2016-02-01 01:15:00
1 2016-02-01 00:54:00
2 2016-02-01 01:30:00
3 2016-02-01 02:35:00
4 2016-02-01 01:00:00
5 2016-02-01 02:00:00
6 2016-02-01 01:30:00
7 2016-02-01 01:05:00
8 2016-02-01 00:42:00
9 2016-02-01 22:30:00
10 2016-02-01 20:44:42
11 2016-02-08 12:29:00
12 2016-02-10 09:10:00
13 2016-02-01 01:13:00
14 NaT
15 2016-02-01 13:45:00
16 2016-02-03 16:59:00
17 2016-02-05 12:04:00
18 2016-02-04 10:37:00
19 2016-02-02 13:09:00
20 2016-02-01 10:23:00
21 2016-02-08 15:25:00
22 2016-02-04 12:06:14
23 2016-02-01 09:40:00
24 2016-02-02 14:57:00
25 2016-02-01 11:30:00
26 2016-02-01 13:44:00
27 2016-02-03 10:03:00
28 2016-02-01 04:00:00
29 2016-02-01 09:57:00
... ...
5821 2016-02-09 15:23:13
5822 2016-02-08 03:08:37
5823 2016-02-08 03:09:49
5824 2016-02-08 11:26:00
5825 2016-02-09 12:07:00
5826 2016-02-08 03:08:37
5827 2016-02-08 14:39:00
5828 2016-03-16 10:39:00
5829 2016-02-11 09:43:00
5830 2016-02-26 10:13:26
5831 2016-02-07 20:40:00
5832 2016-02-12 15:18:14
5833 2016-02-22 10:04:38
5834 2016-02-17 09:21:23
5835 2016-02-08 04:09:00
5836 2016-02-08 13:32:54
5837 2016-02-08 11:27:00
5838 2016-02-08 23:46:00
5839 2016-02-16 09:45:00
5840 2016-02-08 21:50:00
5841 2016-02-07 03:30:00
5842 2016-02-07 17:05:00
5843 2016-02-07 19:40:00
5844 2016-02-08 06:52:08
5845 2016-02-07 21:10:00
5846 2016-02-08 22:50:00
5847 NaT
5848 2016-02-12 22:30:00
5849 2016-02-08 10:07:29
5850 2016-02-10 15:19:00
[5851 rows x 55 columns]
In [12]:
df['created_date'].head()
Out[12]:
0 2016-02-01 00:12:00
1 2016-02-01 00:14:00
2 2016-02-01 00:15:00
3 2016-02-01 00:17:00
4 2016-02-01 00:19:00
Name: created_date, dtype: datetime64[ns]
In [52]:
df['closed_date'].head()
Out[52]:
0 2016-02-01 01:15:00
1 2016-02-01 00:54:00
2 2016-02-01 01:30:00
3 2016-02-01 02:35:00
4 2016-02-01 01:00:00
Name: closed_date, dtype: datetime64[ns]
In [46]:
df['time open'] = df['closed_date'] - df['created_date']
In [47]:
# df.plot(kind='scatter',y='date_created',x='closed_date')
In [76]:
df['time open'].describe()
Out[76]:
count 5650
mean 6 days 15:05:05.589380
std 14 days 12:05:38.260805
min -19 days +09:29:00
25% 0 days 01:14:00
50% 0 days 21:48:15
75% 4 days 00:30:48.500000
max 89 days 18:54:00
Name: time open, dtype: object
In [77]:
df['time open'].head()
Out[77]:
0 01:03:00
1 00:40:00
2 01:15:00
3 02:18:00
4 00:41:00
Name: time open, dtype: timedelta64[ns]
In [78]:
# source to remove negative values
# http://www.datasciencebytes.com/bytes/2015/05/16/pandas-timedelta-histograms-unit-conversion-and-overflow-danger/
time_diff = df['time open'].diff()
time_diff.describe()
Out[78]:
count 5467
mean -1 days +22:13:18.931040
std 19 days 02:45:11.441675
min -88 days +06:38:00
25% -2 days +06:55:34.500000
50% 0 days 00:00:00
75% 1 days 10:50:27.500000
max 85 days 14:33:50
Name: time open, dtype: object
In [79]:
time_diff = time_diff[time_diff > pd.Timedelta(hours=0)].dropna()
time_diff.describe()
Out[79]:
count 2480
mean 11 days 01:40:55.583064
std 16 days 16:20:47.742352
min 0 days 00:00:18
25% 0 days 05:44:29
50% 2 days 02:32:34.500000
75% 14 days 04:47:13
max 85 days 14:33:50
Name: time open, dtype: object
Content source: ledeprogram/algorithms
Similar notebooks: