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]

before removing negative time differences


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

After removing negatives


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