Component 1: Procedural, Bespoke Visualization


In [33]:
## Importing necessary Libraries 
## Pandas to work on Data Frames
import pandas as pd
#Numpy for Numerical Computation 
import numpy as np
## Matplotlib for Plotting Data
import matplotlib.pyplot as plt
## Style used ggplot for all the graphs for having consistent Visulization 
plt.style.use('ggplot')
## Provide a matplotlib like interface to plotting data with Google Maps
import gmplot
## Datetime library for working with Time data
import datetime
## Magic command to for plotting graphs inline 
import matplotlib.cbook as cbook
from scipy.misc import imread
%matplotlib inline

In [2]:
##  Reading Transit Shapes for the Champaign City 
shapes = pd.read_csv("google_transit/shapes.txt")
shape1 = shapes.loc[shapes["shape_id"] == "[@2.0.86175868@]34"]

In [3]:
##  Grouping the shape by Distance Traveled
shapes.groupby(['shape_id']).max()['shape_dist_traveled']


Out[3]:
shape_id
1 YELLOW ALT 25               15081.377088
100 LATE 3                     4498.913993
100 LATE 5                    11485.402334
100N                          25849.308800
100N->IT                      16246.834096
100N-LATE                      4606.626993
100NIT->                       9602.474705
100NLTNT->                    12077.777356
100S                          24831.587307
100S->PLAZA                   10669.024538
100SLTNT->                    11573.206744
10E                           16803.622334
10W AMBP->LSG                 11488.805970
10W GOLD ALT 1                18958.286842
10WLSG->                      12119.606254
10WSV1                         3805.270409
120W TEAL LATE 12              5670.836845
12E TEAL 13                    6130.199374
12E TEAL WEEKEND 13            6130.199374
12W TEAL 12                    5683.602587
12W TEAL WEEKEND 12            5683.602587
130N SILVER EVENING 1          5788.641865
130N SILVER EVENING 2          4657.583136
130N SILVER LATE 1             5788.641865
130N SILVER WEEKEND 1          5788.641865
130N SILVER WEEKEND 2          4657.583136
130N SILVERLATE 2              4657.583136
130S SILVER EVENING 3          5896.035015
130S SILVER LATE 3             5917.032709
130S SILVER WEEKEND 3          5896.035015
                                  ...     
[@2.0.85633622@]8              6356.400728
[@2.0.85634618@]21            13453.322471
[@2.0.85634827@]37            13270.132003
[@2.0.85635071@]58             7152.719337
[@2.0.86175868@]18             4639.044770
[@2.0.86175868@]19             1699.159894
[@2.0.86175868@]25            11072.650097
[@2.0.86175868@]27              917.137154
[@2.0.86175868@]34             8737.745855
[@2.0.86175868@]42             7544.590949
[@2.0.86175868@]53             1730.113558
[@2.0.86175868@]56             3844.854143
[@2.0.86175868@]6WIT->CF      13713.191231
[@2.0.86175868@]7             11753.161384
[@2.0.86175868@]70             2847.822090
[@2.0.86175868@]75             7590.083569
[@2.0.86175868@]ORANGE 12      8813.276508
[@2.0.86175868@]ORANGE 13     13452.321278
[@2.0.86175868@]ORANGE 14      4405.205639
[@2.0.86175868@]ORANGE 16      4408.070869
[@2.0.86175868@]ORANGE 17      7590.083569
[@2.0.86175868@]ORANGE 23      3892.191471
[@2.0.86175868@]ORANGE 24      1410.545911
[@2.0.86175868@]ORANGE 31     16282.336804
[@2.0.86175868@]ORANGE 32      4975.445376
[@2.0.86175868@]ORANGE 33     11775.827539
[@2.0.86175868@]ORANGE 610     1439.129299
[@2.0.86175868@]ORANGE 63      9206.681965
[@2.0.86175868@]ORANGE 64      8737.745855
[@2.0.86232218@]426           16158.221809
Name: shape_dist_traveled, dtype: float64

In [4]:
## Max shape size with distance traveled
max(shape1["shape_dist_traveled"])


Out[4]:
8737.7458545525405

In [5]:
## Shape grouping by Shape ID 
shapes.groupby(["shape_id"]).count()


Out[5]:
shape_pt_lat shape_pt_lon shape_pt_sequence shape_dist_traveled
shape_id
1 YELLOW ALT 25 2499 2499 2499 2499
100 LATE 3 908 908 908 908
100 LATE 5 1809 1809 1809 1809
100N 4157 4157 4157 4157
100N->IT 2802 2802 2802 2802
100N-LATE 911 911 911 911
100NIT-> 1356 1356 1356 1356
100NLTNT-> 1849 1849 1849 1849
100S 3862 3862 3862 3862
100S->PLAZA 1819 1819 1819 1819
100SLTNT-> 1837 1837 1837 1837
10E 3422 3422 3422 3422
10W AMBP->LSG 3279 3279 3279 3279
10W GOLD ALT 1 5114 5114 5114 5114
10WLSG-> 2136 2136 2136 2136
10WSV1 539 539 539 539
120W TEAL LATE 12 1468 1468 1468 1468
12E TEAL 13 1542 1542 1542 1542
12E TEAL WEEKEND 13 1542 1542 1542 1542
12W TEAL 12 1478 1478 1478 1478
12W TEAL WEEKEND 12 1478 1478 1478 1478
130N SILVER EVENING 1 1396 1396 1396 1396
130N SILVER EVENING 2 1230 1230 1230 1230
130N SILVER LATE 1 1396 1396 1396 1396
130N SILVER WEEKEND 1 1396 1396 1396 1396
130N SILVER WEEKEND 2 1230 1230 1230 1230
130N SILVERLATE 2 1230 1230 1230 1230
130S SILVER EVENING 3 1396 1396 1396 1396
130S SILVER LATE 3 1399 1399 1399 1399
130S SILVER WEEKEND 3 1396 1396 1396 1396
... ... ... ... ...
[@2.0.85633622@]8 1672 1672 1672 1672
[@2.0.85634618@]21 2041 2041 2041 2041
[@2.0.85634827@]37 3002 3002 3002 3002
[@2.0.85635071@]58 1911 1911 1911 1911
[@2.0.86175868@]18 749 749 749 749
[@2.0.86175868@]19 609 609 609 609
[@2.0.86175868@]25 2258 2258 2258 2258
[@2.0.86175868@]27 295 295 295 295
[@2.0.86175868@]34 2482 2482 2482 2482
[@2.0.86175868@]42 1213 1213 1213 1213
[@2.0.86175868@]53 631 631 631 631
[@2.0.86175868@]56 746 746 746 746
[@2.0.86175868@]6WIT->CF 3296 3296 3296 3296
[@2.0.86175868@]7 2579 2579 2579 2579
[@2.0.86175868@]70 1086 1086 1086 1086
[@2.0.86175868@]75 1145 1145 1145 1145
[@2.0.86175868@]ORANGE 12 2439 2439 2439 2439
[@2.0.86175868@]ORANGE 13 3187 3187 3187 3187
[@2.0.86175868@]ORANGE 14 1309 1309 1309 1309
[@2.0.86175868@]ORANGE 16 1131 1131 1131 1131
[@2.0.86175868@]ORANGE 17 1145 1145 1145 1145
[@2.0.86175868@]ORANGE 23 749 749 749 749
[@2.0.86175868@]ORANGE 24 262 262 262 262
[@2.0.86175868@]ORANGE 31 3694 3694 3694 3694
[@2.0.86175868@]ORANGE 32 815 815 815 815
[@2.0.86175868@]ORANGE 33 2300 2300 2300 2300
[@2.0.86175868@]ORANGE 610 270 270 270 270
[@2.0.86175868@]ORANGE 63 1902 1902 1902 1902
[@2.0.86175868@]ORANGE 64 2482 2482 2482 2482
[@2.0.86232218@]426 3275 3275 3275 3275

677 rows × 4 columns


In [6]:
## 
plt.plot(shape1.shape_pt_lon, shape1.shape_pt_lat)


Out[6]:
[<matplotlib.lines.Line2D at 0x10452ded0>]

In [7]:
len(shapes.shape_id.unique())


Out[7]:
677

In [8]:
shapes.shape_id.unique()


Out[8]:
array(['[@2.0.86175868@]34', '[@124.0.92281033@]130S SILVER WEEKEND 3',
       'GOLD 89', 'ORANGEHOPPER 11', '[@15.0.61662606@]593', 'YELLOW 13',
       '[@124.0.92345918@]50E-LATE', 'GOLD 673',
       '[@124.0.92279199@]TEAL EVENING 12', '4W BLUE ALT PM 1440',
       '[@2.0.80546903@]58', 'RED 17', '[@2.0.84887452@]GREEN 3',
       '[@14.0.56288404@]24', '[@124.0.92334988@]50E->PC',
       '[@2.0.86175868@]25', '[@15.0.69155236@]36', '20C PS->GW',
       'LAVENDER 2', '[@124.0.92311676@]32', '[@15.0.66063553@]12',
       'TEAL 99', 'TEAL EVENING 14', '[@2.0.84907145@]7W UHS 216',
       '[@15.0.71205318@]2', '[@124.0.92276155@]120E TEAL LATE 13',
       '[@2.0.84907145@]GREY 1525', '5W-AM640', '5W-AMMNLRMN', 'ORB4PO',
       '10WSV1', '[@124.0.92286725@]130N SILVER EVENING 1',
       '[@15.0.70780822@]4', '[@124.0.92247832@]100N->IT',
       '[@15.0.66064083@]76', '70W-WKND', 'ILLINI EV 25', '5E EXP->IU',
       '[@15.0.63189138@]25', '[@124.0.92281033@]130N SILVER WEEKEND 1',
       '[@15.0.63192321@]25', 'GOLDHOPPER 2', '[@15.0.66066034@]15',
       '[@15.0.74000704@]37', '[@124.0.92311676@]GREEN EVENING 39',
       '8W BRONZE 5', '[@15.0.64600293@]1', '1N YELLOW 5', '30SBB->IT',
       '[@2.0.84876422@]LAVENDER 1415', '130S SILVER LATE 3',
       '[@124.0.92286725@]SILVER 121',
       '[@124.0.92244155@]ILLINI LIMITED WEEKEND 845', 'YELLOWHOPPER 17',
       'TEAL 45', '[@15.0.68513188@]4', '[@14.0.56288404@]33', '5WIUE->',
       '[@2.0.80548152@]2', '[@124.0.92247832@]100S',
       '[@2.0.84907145@]GREY 1415', '7E5THBRAD->', '[@15.0.63192321@]21',
       '6WIT->CF HOPPER', '[@15.0.66064083@]71', '[@15.0.66066034@]53',
       '[@15.0.61662606@]133', '[@2.0.84887452@]GREEN 336',
       '[@124.0.92334988@]50W-WKND', '[@15.0.68511981@]91', '5W HOPPER 81',
       '1525', '[@15.0.59410957@]1', '[@2.0.84927215@]GOLD 1', 'R2SATPO',
       'GREY 580', '[@15.0.59410957@]2', '[@15.0.68512960@]65',
       '4W BLUE ALT PM 310', '[@124.0.92229742@]TEAL WEEKEND 16',
       '[@124.0.92330667@]100 LATE 5', 'YELLOW 7', 'GREEN EVENING 39',
       '[@15.0.63192185@]43', 'GOLD 161', '[@2.0.81457398@]15', 'GREEN 30',
       '[@15.0.73007320@]506', '[@15.0.69155236@]57',
       '[@14.0.56288722@]35', '[@2.0.85633009@]78', 'RED 2',
       '[@15.0.63192662@]604', '[@14.0.56288722@]64', '50E->PC',
       'ILLINI LIMITED WEEKEND 845', '100SLTNT->', '5W->740',
       '[@14.0.57766396@]100S', '[@15.0.70780451@]31',
       '[@2.0.86232218@]426', '[@15.0.74000704@]41', '[@15.0.61662606@]1',
       '[@14.0.56288404@]45', 'L2SATPO', '[@2.0.84907145@]GREY 1425',
       '[@15.0.63192662@]3', '[@2.0.86175868@]27', '[@124.0.92246230@]4',
       '[@15.0.61662606@]754', '[@15.0.63192528@]43',
       '[@124.0.92279199@]TEAL EVENING 14', 'GOLD 553',
       '[@15.0.70781343@]3', '5WXPM', 'GREENHOPPER WEEKEND 07', '20U->PS',
       '[@124.0.92334988@]GN4SUNPO', '[@124.0.92311676@]GREEN EV 61',
       '100N->IT', '[@2.0.84907145@]336', '5WCF', 'GN1SATPO',
       '[@15.0.60390798@]11', 'GOLD 11', '[@15.0.61662606@]714', '1505',
       '50W HOPPER 105', '[@15.0.60391334@]37', 'NAVY 1', '70WIT->CF',
       'HOPPER EV 63', '50W GREEN 55', '20C->MP', '[@15.0.73006437@]38',
       'GREEN EX 27', '5E GREEN EXP 2', 'TEAL WEEKEND 16', '50W GREEN 57',
       '[@15.0.67916770@]3', '[@2.0.84907145@]326',
       '[@2.0.86175868@]ORANGE 33', '[@15.0.63189099@]29',
       '[@15.0.63192124@]23', '[@15.0.68512636@]18',
       '[@124.0.92254999@]45', '5WPMIT->',
       '[@124.0.92275054@]120E TEAL LATE 13', '[@15.0.63189004@]23',
       '130N SILVER EVENING 1', '[@124.0.92260187@]ILLINI EV 25',
       'GREEN EV 72', '1N YELLOW 9', '[@15.0.63191892@]26',
       '130S SILVER WEEKEND 3', '[@14.0.57586460@]38',
       '[@2.0.84953216@]GREEN EX 1425', '[@15.0.67916770@]2',
       '[@14.0.56288340@]23', '12E TEAL 13', 'GOLD 19', 'GR2PO', 'GOLD 26',
       '[@15.0.60391486@]2',
       '[@124.0.92244155@]22N ILLINI LIMITED WEEKEND',
       '[@15.0.61662606@]136', '[@15.0.73009433@]41', '4W',
       '[@15.0.79613563@]47', '[@2.0.86175868@]ORANGE 32',
       '4W BLUE ALT ]2', '[@15.0.63189208@]3', 'GOLD 14',
       '[@15.0.61662606@]590', '[@124.0.92327957@]100 LATE 5', 'BLUE 51',
       '[@2.0.84948307@]11', '[@2.0.84887452@]SCWD UMS',
       '[@15.0.64613606@]2', 'SILVER 121', 'L1SATPO',
       '[@124.0.92263401@]220N ILLINI 10', '[@2.0.86175868@]56',
       '50W HOPPER 104', '[@124.0.92229742@]12E TEAL WEEKEND 13',
       'GREEN WEEKEND 44', '[@15.0.61662606@]137', '[@124.0.92334988@]1',
       '[@124.0.92330667@]100 LATE 3', 'GN1PO',
       '[@124.0.92284627@]130N SILVER EVENING 1',
       '[@2.0.84927215@]GOLD 1535',
       '[@124.0.92281033@]130N SILVER WEEKEND 2', '[@2.0.81457398@]16',
       '3N', '130N SILVER LATE 1', '22S ILLINI 21',
       '[@2.0.84887452@]GREEN 1', '[@15.0.61662606@]138',
       '[@2.0.84948307@]133', '220N ILLINI 10', '[@2.0.84887452@]67',
       '[@15.0.68511981@]93', 'TEAL 98', '[@2.0.84907145@]GREY 1535',
       '[@2.0.82999720@]3', 'GREY 582', '[@15.0.71205318@]30SBB->IT',
       '[@15.0.73009433@]43', '50E GREEN 53', '[@15.0.68508995@]404',
       '[@15.0.61734120@]1', 'GOLD 45', '[@14.0.56288404@]42', '70E',
       '10WLSG->', '50E HOPPER 103', '[@15.0.68511845@]30', 'NAVY 48',
       '[@14.0.57766396@]Y3SATPO', '7E->740', '[@15.0.68508734@]66',
       '[@15.0.73007815@]73', '[@15.0.73007320@]111',
       '[@124.0.92236898@]100NGRNWRT->', '[@124.0.92350475@]2',
       '[@15.0.74000704@]38', '[@2.0.81838518@]100', '[@15.0.68511981@]92',
       '[@15.0.68512808@]3', '[@14.0.56288722@]36', '70E-WKND',
       'ILLINI 34', '[@2.0.84876422@]22', '5E', '[@124.0.92241454@]5',
       '[@124.0.92236898@]485', 'GOLD 424', '[@15.0.69172548@]7',
       '[@15.0.68511981@]95', '[@124.0.92275054@]120W TEAL LATE 12',
       '[@124.0.92281033@]SILVER WEEKEND 31', '5W740->',
       '[@2.0.80547772@]12', '[@2.0.84907145@]25', '100N-LATE',
       '[@124.0.92345918@]50W-LATE', '70E->50WLATE',
       '[@2.0.86175868@]ORANGE 12', '[@15.0.64657075@]1',
       '[@14.0.56289002@]26', '[@124.0.92246230@]2', '[@15.0.64613606@]3',
       '7W GREY ALT 1', '5W-PMFLPHILO->', '[@15.0.63191692@]369',
       'SILVER 42', '7W->LSE', '[@14.0.51708617@]32',
       '[@15.0.68508995@]405', 'TEAL 35', '[@124.0.92319406@]7',
       '[@2.0.86175868@]ORANGE 16', '[@124.0.92247832@]9',
       '[@2.0.81838518@]101', '[@15.0.73009483@]22', '[@15.0.68508995@]2',
       'YELLOW 52', 'YELLOW 4', '[@15.0.63189138@]10',
       '[@124.0.92275054@]TEAL LATE 14', 'GOLD 21', '[@2.0.85633009@]76',
       '[@124.0.92254999@]51', '30NIT->BB', '[@2.0.85634827@]37',
       '[@15.0.61662606@]11', '5EIU->LS', 'RED 7',
       '[@124.0.92330667@]100NLTNT->', '5WPM->LSE', '[@2.0.84876422@]885',
       '130N SILVERLATE 2', '5WXCF->', '[@124.0.92311676@]71',
       '[@15.0.69155236@]37', '100NLTNT->', 'GOLD 425',
       '[@15.0.73009433@]45', '[@15.0.64600293@]2', '[@14.0.56288404@]21',
       '[@14.0.56288498@]24', '[@124.0.92244155@]5', 'ILLINI 845',
       '5W HOPPER 84', '[@124.0.92344750@]50E-LATE', '30NCMBLQ->BB',
       'TEAL LATE 14', '[@2.0.84953216@]43', '[@15.0.73007320@]13',
       'GREEN EX 49', '[@124.0.92263401@]24',
       '[@14.0.57766396@]100NGRNWRT->', '5WPM->LSE ONLY',
       '[@124.0.92319406@]1', '[@124.0.92311676@]50E GREEN 53',
       '[@124.0.92327957@]100 LATE 3', '[@124.0.92327957@]100NLTNT->',
       'GN3SUNPO', '[@15.0.61662606@]140', '[@15.0.63189004@]25',
       '5WLSE->', '[@15.0.73007921@]22', '[@15.0.61734120@]2',
       '[@15.0.63189138@]8', '70W', '[@2.0.84887452@]7', 'NAVY 50',
       '[@15.0.68513410@]21', '[@124.0.92247832@]100S->PLAZA',
       '[@2.0.84876422@]62', 'YELLOW 62', '[@2.0.84927215@]GOLD 336',
       '[@15.0.63192993@]31', '[@2.0.80546903@]59', '12W TEAL 12',
       'GREENHOPPER 89', '5E NO CSQ', '[@124.0.92330667@]100 LATE 4', '7W',
       'BRONZE 6', '[@15.0.74000704@]51', 'YELLOW 50', 'GREEN 525',
       '10W GOLD ALT 1', '[@2.0.85635071@]58', '[@2.0.84948307@]22',
       '[@15.0.63192662@]5', 'NAVY 5', '[@15.0.63189138@]9', 'TEAL 27',
       'SILVER 120', '[@15.0.68512008@]101', '[@2.0.84907145@]36',
       'SILVER WEEKEND 42', '5WXIUE->DNST', '[@124.0.92319406@]20',
       '50W-LATE', '[@124.0.92260187@]220S ILLINI 20',
       '[@2.0.84876422@]LP1SCHOOLPO', '[@14.0.56288404@]32',
       '[@14.0.57766396@]100S->BRWSWDFLD', '[@15.0.71205318@]1',
       '[@15.0.61662606@]690', '5E HOPPER 79',
       '[@124.0.92334988@]GN5SUNPO', '[@2.0.80546279@]243',
       '[@14.0.57766396@]100N', '[@14.0.57766396@]100NY1',
       '[@14.0.56288404@]5', '5E EXP 1 ALT', '[@15.0.71205318@]30NIT->BB',
       'RED 6', '1425', 'RED 3', '[@15.0.61662606@]706', '5W HOPPER 85',
       '30NIT->CMBLQN', '[@15.0.61662606@]694', '[@124.0.92334988@]395',
       '[@15.0.64613606@]4', '[@15.0.73008754@]33',
       '[@124.0.92327957@]100 LATE 4', '[@15.0.69155236@]540',
       '[@15.0.63191692@]370', 'TEAL 24', 'GREEN 37',
       '[@124.0.92330667@]100SLTNT->', '100NIT->', '5WPMLSE->IT',
       '[@15.0.68508734@]58', '[@2.0.84907145@]BA3-3 58', 'R1SATPO',
       'GOLD 16', '50E-WKND-HP->CF', '5EX->VET', 'SILVER WEEKEND 31',
       '[@124.0.92254999@]43', '[@2.0.84887452@]S6 ALT',
       '[@124.0.92348983@]22N 316', '[@15.0.64657075@]2', 'YELLOW 14',
       '4E->GRNWRT:5', '12W TEAL WEEKEND 12', '20U ->IU', '50WLTNT->',
       '[@15.0.70780160@]363', '[@15.0.68512430@]585', '20C GW->MP',
       '[@15.0.63188916@]15', 'SILVER 2', 'HOPPER EV 62', 'SILVER LATE 30',
       '7E', '22N 316', '50ELTNT->', '[@2.0.84927215@]ORB2 UMS',
       '[@2.0.84953216@]33', '[@2.0.84927215@]GOLD 1505',
       '[@15.0.64657075@]19', 'GREEN 894', '[@124.0.92327957@]32',
       '[@2.0.84876422@]LAVENDER 1', '1S YELLOW 19', '[@2.0.84907145@]21',
       'S2PO', '[@124.0.92236898@]483', '[@15.0.69155236@]530',
       '5E GREEN EXP 1', '4W BLUE ALT 1',
       '[@124.0.92284627@]130S SILVER EVENING 3', '[@2.0.81838518@]12',
       '20U IT->PS', '[@15.0.68512430@]37', 'GNX2PO',
       '130N SILVER WEEKEND 2', 'GREEN 893', '[@15.0.61662606@]591',
       '[@124.0.92229742@]TEAL WEEKEND 45', '[@2.0.86175868@]ORANGE 31',
       '[@2.0.86175868@]ORANGE 13', 'HOPPER EV 107', '5E->IU', '5W-AM',
       '[@15.0.63192037@]4', '50E->50WLNIGHT', 'ILLINI 845 315', 'GOLD 72',
       '[@2.0.86175868@]ORANGE 610', '[@15.0.73009433@]44', 'GOLD 27',
       '3S ALT 1', '[@124.0.92263401@]220S ILLINI 20', '5WX->CF', '7EIT->',
       'GN7PO-', '[@2.0.86175868@]ORANGE 14', 'OD1PO',
       '[@15.0.73007178@]121', '[@15.0.68513015@]219', 'YELLOWHOPPER 26',
       '5W->IUE', 'GOLD 423', '[@15.0.69172548@]2', '8W',
       '[@15.0.66064923@]3', '[@124.0.92234822@]19',
       '[@2.0.86175868@]ORANGE 23', '22N ILLINI 10',
       '130N SILVER EVENING 2', '[@15.0.73008754@]39', 'GOLD 25', 'RED 4',
       '[@15.0.67924657@]7', '[@2.0.86175868@]42', '[@15.0.63192099@]7',
       '[@2.0.81838518@]102', '[@124.0.92284627@]130N SILVER EVENING 2',
       '[@15.0.68512960@]111', '[@124.0.92260187@]220N ILLINI 10',
       '[@2.0.84907145@]7W SHOW 97', '[@2.0.84887452@]GREEN 2',
       '[@124.0.92327957@]100N-LATE', '5W CCSJ SG', '7E ALT 2',
       '[@15.0.68512037@]696', '[@2.0.84876422@]LAVENDER 1425', 'GR4PO',
       '[@14.0.56288947@]26', '[@2.0.84907145@]7W SHOW 226', 'YELLOW 51',
       '[@15.0.68513410@]63', '[@2.0.84876422@]LAVENDER 1505',
       '[@15.0.66064083@]23', 'TEAL EVENING 13', '[@15.0.63192662@]4',
       '5W HOPPER 83', 'GOLDHOPPER 1', '7E GREY ALT 1', 'R3SATPO',
       '[@15.0.73009433@]51', '[@124.0.92281033@]SILVER WEEKEND 42',
       '[@124.0.92344750@]50E->50WLNIGHT', '7E740->', 'GOLD 421',
       'HOPPER EV 108', 'GN4 SU PO', '[@124.0.92319406@]4',
       '[@124.0.92263401@]ILLINI EV 25', '[@14.0.56288268@]341',
       '100S->PLAZA', 'TEAL WEEKEND 45', 'ILLINI 47',
       '[@124.0.92234822@]2', '[@124.0.92284627@]SILVER 121',
       '[@124.0.92260187@]24', '5ETOCF', '[@15.0.67916770@]1',
       '50W GREEN 59', '[@15.0.68512636@]19', '[@15.0.69155236@]1',
       'YELLOW 11', '[@15.0.73008535@]13', '1420', '[@14.0.56288404@]14',
       '[@2.0.84948307@]7', 'RED 1',
       '[@124.0.92286725@]130S SILVER EVENING 3', '[@15.0.68512960@]110',
       '50E HOPPER 102', '[@2.0.86175868@]ORANGE 63',
       '[@124.0.92279199@]TEAL EVENING 13', '70E->LSG', '8E', 'GOLD 90',
       '[@2.0.84907145@]331', '7E GREY ALT 5', '[@124.0.92234822@]1',
       '[@14.0.57766396@]Y1SATPO', '[@2.0.84907145@]34',
       '[@15.0.61662606@]364', '[@124.0.92247832@]100S->1STGRG',
       '[@2.0.81838518@]104', 'YELLOW 10', '[@15.0.68513015@]220',
       '[@124.0.92319406@]9', '[@124.0.92348983@]ILLINI 845 315',
       '[@15.0.68512361@]3', '[@2.0.86175868@]75', '22S ILLINI 20',
       '1 YELLOW ALT 25', 'RED 13', '[@15.0.68511981@]99',
       '[@15.0.61734120@]335', '[@124.0.92334988@]GN1SUNPO', 'TEAL 23',
       '[@15.0.63192037@]3', '[@15.0.68512960@]112',
       '[@124.0.92236898@]100S->1STGRG', '[@124.0.92311676@]50W GREEN 59',
       'RED 8', '[@15.0.68508734@]57', 'GREEN 905', '[@124.0.92234822@]3',
       '[@15.0.68508734@]48', '[@15.0.73008754@]35', 'GN2PO',
       '[@2.0.86175868@]7', '[@124.0.92236898@]100SPLAZA->', 'GREEN 907',
       '[@124.0.92334988@]50E->LS', '[@15.0.61662606@]114', '100S',
       '[@2.0.84907145@]88', '[@15.0.69172548@]1', '100N',
       '[@124.0.92254999@]46', 'YELLOWHOPPER 25',
       '[@124.0.92344750@]50WLTNT->', '[@2.0.84916234@]65',
       '[@15.0.64600293@]17', '50E->MNBDY', '5W-PMLSE->',
       '[@2.0.83502711@]3', '7WLSE->', '7E->LSG', '5ETOCF->',
       'GOLDHOPPER 9', '22N ILLINI LIMITED WEEKEND', 'BLUE 3',
       '[@124.0.92236898@]100N', 'TEAL 26', '[@2.0.86175868@]70',
       '130S SILVER EVENING 3', '[@2.0.86175868@]ORANGE 64',
       '[@15.0.61734120@]225', '[@124.0.92344750@]50W-LATE',
       '[@124.0.92247832@]100N', '[@124.0.92286725@]130N SILVER EVENING 2',
       'GREEN WEEKEND 23', '[@2.0.86175868@]ORANGE 24', '5W-PM',
       '[@124.0.92334988@]397', '[@15.0.73006437@]18',
       '[@124.0.92334988@]GREEN WEEKEND 32', '100 LATE 5', 'NAVY 2',
       '100 LATE 3', '10W AMBP->LSG', 'RED 9', '120W TEAL LATE 12',
       '[@2.0.84907145@]1435', '[@15.0.68511981@]39', '[@15.0.63192037@]1',
       '[@124.0.92276155@]TEAL LATE 14', '[@2.0.86175868@]18',
       '[@14.0.56288404@]40', '[@2.0.86175868@]6WIT->CF', 'NAVY 49',
       '[@2.0.84876422@]LAVENDER 43', 'YELLOWHOPPER 23', '1415',
       '[@14.0.56288404@]31', '[@124.0.92334988@]50W-WKND->IT', 'TEAL 34',
       '22N', '[@15.0.67924657@]4', '[@124.0.92236898@]100S', 'GOLD 46',
       '[@124.0.92229742@]12W TEAL WEEKEND 12', '12E TEAL WEEKEND 13',
       '[@15.0.63192662@]36', 'ILLINI 46', '[@124.0.92327957@]100SLTNT->',
       '[@124.0.92241454@]7', '[@124.0.92334988@]219',
       '[@2.0.86175868@]19', '[@124.0.92246230@]1',
       '[@2.0.84876422@]LAVENDER 1440', '3S->IT', 'SILVER 43',
       '[@15.0.63191692@]43', 'YELLOW 15', '[@14.0.56288722@]18',
       '[@124.0.92236898@]Y2SUNPO', '5W-AMIUE->', 'GREEN EVENING 52',
       '[@2.0.85634618@]21', 'GREEN EX 1', '[@2.0.86175868@]ORANGE 17',
       '[@15.0.61734120@]240', '20U IU->PS', '130N SILVER WEEKEND 1',
       '[@15.0.61734120@]215', '5W HOPPER 80', '[@14.0.56289035@]63',
       '[@15.0.66063517@]6', '[@15.0.73008754@]31', '[@15.0.73006437@]37',
       '[@15.0.61662606@]574', '[@124.0.92276155@]120W TEAL LATE 12',
       '[@2.0.84948307@]2', '[@15.0.73008754@]38', 'TEAL 25',
       '[@124.0.92236898@]Y1SATPO', 'YELLOW 29', '[@2.0.81838518@]103',
       '[@15.0.68508734@]65', '[@124.0.92241454@]4', '10E',
       '[@2.0.85633622@]8', '[@2.0.86175868@]53', 'TEAL EVENING 12',
       'GN1SUNPO', '[@15.0.61734120@]316', '[@124.0.92236898@]100N->IT',
       '[@2.0.84927215@]2', '[@124.0.92281033@]SILVER WEEKEND 43',
       'GOLD 422', '[@15.0.61662606@]366', '220S ILLINI 20',
       '[@14.0.56288722@]34', '[@15.0.70781976@]15', 'YELLOW 6',
       'GREENHOPPER WEEKEND 106', '[@124.0.92350475@]3',
       '[@15.0.64613606@]1'], dtype=object)

In [9]:
routes = pd.read_csv("google_transit/routes.txt")

In [10]:
routes


Out[10]:
route_id agency_id route_short_name route_long_name route_desc route_type route_url route_color route_text_color
0 GOLD ALT CUMTD 10 Gold 1 Alternate NaN 3 NaN c7994a 000000
1 RUBY SATURDAY CUMTD 110 Ruby Saturday NaN 3 NaN eb008b 000000
2 SILVER LIMITED SATURDAY CUMTD 130 Silver Limited Saturday NaN 3 NaN d1d3d4 000000
3 BROWN ALT PM CUMTD 9 Brown Alternate PM NaN 3 NaN 823822 ffffff
4 YELLOW LATE NIGHT SUNDAY CUMTD 100 Yellow Late Night Sunday NaN 3 NaN fcee1f 000000
5 GREEN EVENING SATURDAY CUMTD 50 Green Evening Saturday NaN 3 NaN 008063 ffffff
6 GREY ALT CUMTD 7 Grey Alternate NaN 3 NaN 808285 000000
7 TEAL LATE NIGHT SUNDAY CUMTD 120 Teal Late Night Sunday NaN 3 NaN 006991 ffffff
8 GREEN LATE NIGHT SATURDAY CUMTD 50 Green Late Night Saturday NaN 3 NaN 008063 ffffff
9 5E GREEN EXPRESS 1 ALT CUMTD 5 Green Express 1 Alternate NaN 3 NaN 008063 ffffff
10 GREEN SATURDAY CUMTD 50 Green Saturday NaN 3 NaN 008063 ffffff
11 3S LAVENDER ALT CUMTD 3 Lavender 1 Alternate NaN 3 NaN a78bc0 000000
12 SILVER LIMITED SUNDAY CUMTD 130 Silver Limited Sunday NaN 3 NaN d1d3d4 000000
13 SILVER EVENING SUNDAY CUMTD 130 Silver Evening Sunday NaN 3 NaN cccccc 000000
14 LIME EVENING SATURDAY CUMTD 180 Lime Evening Saturday NaN 3 NaN b2d235 000000
15 ILLINI EVENING SUNDAY CUMTD 220 Illini Evening Sunday NaN 3 NaN 5a1d5a ffffff
16 GREENHOPPER EVENING SATURDAY CUMTD 50 Greenhopper Evening Saturday NaN 3 NaN 008063 ffffff
17 YELLOW SUNDAY CUMTD 100 Yellow Sunday NaN 3 NaN fcee1f 000000
18 TEAL SATURDAY CUMTD 120 Teal Saturday NaN 3 NaN 006991 ffffff
19 LAVENDER ALT CUMTD 3 Lavender Alternate NaN 3 NaN a78bc0 000000
20 YELLOW CUMTD 1 Yellow NaN 3 NaN fcee1f 000000
21 RED CUMTD 2 Red NaN 3 NaN ed1c24 000000
22 5E GREEN EXPRESS ALT CUMTD 5 Green Express Alternate NaN 3 NaN 008063 ffffff
23 SILVER SUNDAY CUMTD 130 Silver Sunday NaN 3 NaN cccccc 000000
24 GREENHOPPER CUMTD 5 Greenhopper NaN 3 NaN 008063 ffffff
25 GREEN CUMTD 5 Green NaN 3 NaN 008063 ffffff
26 RUBY SUNDAY CUMTD 110 Ruby Sunday NaN 3 NaN eb008b 000000
27 LAVENDER SUNDAY CUMTD 30 Lavender Sunday NaN 3 NaN a78bc0 000000
28 BRONZE YANKEE RIDGE CUMTD 8 Bronze Yankee Ridge NaN 3 NaN 9e8966 000000
29 LAVENDER SATURDAY CUMTD 30 Lavender Saturday NaN 3 NaN a78bc0 000000
... ... ... ... ... ... ... ... ... ...
71 NAVY CUMTD 14 Navy NaN 3 NaN 2b3088 ffffff
72 LIME SUNDAY CUMTD 180 Lime Sunday NaN 3 NaN b2d235 000000
73 ILLINI LIMITED EVENING SATURDAY CUMTD 220 Illini Limited Evening Saturday NaN 3 NaN 5a1d5a ffffff
74 GREEN LATE NIGHT CUMTD 50 Green Late Night NaN 3 NaN 008063 ffffff
75 GREY SUNDAY CUMTD 70 Grey Sunday NaN 3 NaN 808285 000000
76 SILVER LATE NIGHT CUMTD 130 Silver Late Night NaN 3 NaN cccccc 000000
77 10W GOLD ALT CUMTD 10 Gold Alternate NaN 3 NaN c7994a 000000
78 TEAL SUNDAY CUMTD 120 Teal Sunday NaN 3 NaN 006991 ffffff
79 ILLINI LIMITED EVENING CUMTD 220 Illini Limited Evening NaN 3 NaN 5a1d5a ffffff
80 SILVER EVENING SATURDAY CUMTD 130 Silver Evening Saturday NaN 3 NaN cccccc 000000
81 RUBY EVENING CUMTD 110 Ruby Evening NaN 3 NaN eb008b 000000
82 GREEN SUNDAY CUMTD 50 Green Sunday NaN 3 NaN 008063 ffffff
83 GOLDHOPPER CUMTD 10 Goldhopper NaN 3 NaN c7994a 000000
84 SILVER SATURDAY CUMTD 130 Silver Saturday NaN 3 NaN cccccc 000000
85 BRONZE CUMTD 8 Bronze NaN 3 NaN 9e8966 000000
86 5W GREEN EXPRESS 2 CUMTD 5 Green West Express NaN 3 NaN 008063 ffffff
87 BROWN ALT1 CUMTD 9 Brown Alternate NaN 3 NaN 825622 ffffff
88 1N YELLOW ALT CUMTD 1 Yellow N Alternate NaN 3 NaN fcee1f 000000
89 1S YELLOW ALT CUMTD 1 Yellow S Alternate NaN 3 NaN fcee1f 000000
90 RAVEN CUMTD 21 Raven NaN 3 NaN 000000 ffffff
91 ILLINI EVENING CUMTD 220 Illini Evening NaN 3 NaN 5a1d5a ffffff
92 7W GREY ALT CUMTD 7 Grey W Alternate NaN 3 NaN 808285 000000
93 TEAL CUMTD 12 Teal NaN 3 NaN 006991 ffffff
94 ORANGE ALT CUMTD 6 Orange Alternate NaN 3 NaN f99f2a 000000
95 LAVENDER CUMTD 3 Lavender NaN 3 NaN a78bc0 000000
96 GREEN EXPRESS CUMTD 5 Green Express NaN 3 NaN 008063 ffffff
97 YELLOWHOPPER CUMTD 1 Yellowhopper NaN 3 NaN fcee1f 000000
98 SILVER LIMITED EVENING CUMTD 130 Silver Limited Evening NaN 3 NaN d1d3d4 000000
99 LIME EVENING CUMTD 180 Lime Evening NaN 3 NaN b2d235 000000
100 ILLINI LIMITED SATURDAY CUMTD 220 Illini Limited Saturday NaN 3 NaN 5a1d5a ffffff

101 rows × 9 columns


In [11]:
trips = pd.read_csv("google_transit/trips.txt")

In [12]:
trips


Out[12]:
route_id service_id trip_id trip_headsign direction_id block_id shape_id
0 TEAL T4 UIMF [@14.0.51708725@][4][1277756770140]/0__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF TEAL 26
1 TEAL T4 UIMF [@14.0.51708725@][4][1275505811421]/0__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF TEAL 23
2 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/72__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
3 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/4__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
4 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/74__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
5 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/6__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
6 TEAL T4 UIMF [@7.0.41893871@][3][1243541488843]/110__T4_UIMF EAST - PAR 0 T4 UIMF TEAL 34
7 TEAL T4 UIMF [@14.0.51708725@][4][1275506079140]/6__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF TEAL 23
8 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/79__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
9 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/11__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
10 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/81__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
11 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/13__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
12 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/83__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
13 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/15__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
14 TEAL T4 UIMF [@7.0.41893871@][3][1243541488843]/119__T4_UIMF EAST - PAR 0 T4 UIMF TEAL 34
15 TEAL T4 UIMF [@14.0.51708725@][4][1275506079140]/12__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF TEAL 23
16 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/88__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
17 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/20__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
18 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/90__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
19 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/22__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
20 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/92__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
21 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/24__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
22 TEAL T4 UIMF [@7.0.41893871@][3][1243541488843]/128__T4_UIMF EAST - PAR 0 T4 UIMF TEAL 34
23 TEAL T4 UIMF [@14.0.51708725@][4][1275506123875]/18__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF TEAL 23
24 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/97__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
25 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/29__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
26 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/99__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
27 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/31__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
28 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/101__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
29 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/33__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
... ... ... ... ... ... ... ...
5468 LIME SATURDAY LM1SA EVE [@15.0.73009433@][12][1402682836659]/37__LM1SA... B - POMONA & BRADLEY 1 LM1SA EVE [@15.0.73009433@]43
5469 LIME SATURDAY LM1SA EVE [@15.0.73009433@][11][1402676676689]/9__LM1SA_EVE A - KIRBY & DUNCAN 0 LM1SA EVE [@15.0.73009433@]51
5470 LIME SATURDAY LM1SA EVE [@15.0.73009433@][12][1402682836659]/39__LM1SA... B - POMONA & BRADLEY 1 LM1SA EVE [@15.0.73009433@]43
5471 LIME SATURDAY LM1SA EVE [@15.0.73009433@][11][1402676676689]/11__LM1SA... A - KIRBY & DUNCAN 0 LM1SA EVE [@15.0.73009433@]51
5472 LIME SATURDAY LM1SA EVE [@15.0.73009433@][12][1402678897199]/28__LM1SA... B - POMONA & BRADLEY 1 LM1SA EVE [@15.0.73009433@]41
5473 LIME SATURDAY LM1SA EVE [@15.0.73009433@][11][1402676676689]/13__LM1SA... A - KIRBY & DUNCAN 0 LM1SA EVE [@15.0.73009433@]51
5474 LIME SATURDAY LM1SA EVE [@15.0.73009433@][12][1402679001843]/29__LM1SA... B - POMONA & BRADLEY 1 LM1SA EVE [@15.0.73009433@]41
5475 LIME SATURDAY LM1SA EVE [@15.0.73009433@][11][1402676676689]/15__LM1SA... A - KIRBY & DUNCAN 0 LM1SA EVE [@15.0.73009433@]51
5476 LIME SATURDAY LM1SA EVE [@15.0.73009433@][12][1402683433784]/47__LM1SA... B - POMONA & BRADLEY 1 LM1SA EVE [@15.0.73009433@]45
5477 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402685655732]/0__LM2SA_EVE B - POMONA & BRADLEY 1 LM2SA EVE [@14.0.57586460@]38
5478 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402682631373]/31__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]43
5479 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676435949]/0__LM2SA_EVE A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5480 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402678344466]/24__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]41
5481 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676676689]/2__LM2SA_EVE A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5482 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402682836659]/32__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]43
5483 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676676689]/4__LM2SA_EVE A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5484 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402682836659]/34__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]43
5485 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676676689]/6__LM2SA_EVE A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5486 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402682836659]/36__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]43
5487 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676676689]/8__LM2SA_EVE A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5488 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402682836659]/38__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]43
5489 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676676689]/10__LM2SA... A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5490 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402682836659]/40__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]43
5491 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676676689]/12__LM2SA... A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5492 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402682859723]/41__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]43
5493 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676676689]/14__LM2SA... A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5494 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402679035062]/30__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]41
5495 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676676689]/16__LM2SA... A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5496 ORANGE ALT 6E SCH MTTF [@15.0.69155236@][3][1368635976437]/6__6E_SCH_... EAST - EDGEWOOD 0 6E SCH MTTF [@15.0.69155236@]530
5497 ORANGE ALT 6E SCH MTTF [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... EAST - EDGEWOOD 0 6E SCH MTTF [@15.0.69155236@]540

5498 rows × 7 columns


In [13]:
trips.trip_id.unique()


Out[13]:
array(['[@14.0.51708725@][4][1277756770140]/0__T4_UIMF',
       '[@14.0.51708725@][4][1275505811421]/0__T4_UIMF',
       '[@7.0.41893871@][3][1243541396687]/72__T4_UIMF', ...,
       '[@15.0.73009433@][11][1402676676689]/16__LM2SA_EVE',
       '[@15.0.69155236@][3][1368635976437]/6__6E_SCH_MTTF',
       '[@15.0.69155236@][3][1368635065623]/5__6E_SCH_MTTF'], dtype=object)

In [14]:
calendar = pd.read_csv("google_transit/calendar.txt")

In [15]:
calendar_dates = pd.read_csv("google_transit/calendar_dates.txt")

In [16]:
fare_attributes = pd.read_csv("google_transit/fare_attributes.txt")

In [17]:
fare_attributes


Out[17]:
fare_id price currency_type payment_method transfers transfer_duration
0 ISTOP 0.0 USD 0 0 NaN
1 NORMAL 1.0 USD 0 1 NaN

In [18]:
fare_rules = pd.read_csv("google_transit/fare_rules.txt")

In [19]:
fare_rules.groupby(["fare_id"]).count()


Out[19]:
route_id origin_id destination_id contains_id
fare_id
ISTOP 45 45 0 0
NORMAL 56 57 0 0

In [20]:
plt.plot(shapes.shape_pt_lat, shapes.shape_pt_lon,'.', alpha =0.5)
plt.grid()



In [21]:
stops = pd.read_csv("google_transit/stops.txt")

In [22]:
stops


Out[22]:
stop_id stop_code stop_name stop_desc stop_lat stop_lon zone_id stop_url location_type parent_station
0 KBYWSFLD:3 MTD4346 Kirby & Westfield (South Side) NaN 40.098248 -88.290173 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
1 PHILOMI:4 MTD1026 Philo & Michigan (NW Corner) NaN 40.101792 -88.190865 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2 DNCNCLKRD:2 MTD3333 Duncan & Clark (SE Corner) NaN 40.117390 -88.295470 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
3 TRLSPHILO:1 MTD6424 Trails & Philo (NE Corner) NaN 40.077915 -88.190315 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
4 RMNERKA:4 MTD4040 Romine & Eureka (NW Corner) NaN 40.125585 -88.227525 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
5 IRNWDSTLY:4 MTD0429 Ironwood & Staley (NW Corner) NaN 40.089826 -88.314226 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
6 5THCLMBA:4 MTD2036 Fifth & Columbia (NW Corner) NaN 40.121308 -88.232153 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
7 KRBYGLNSHR:1 MTD9073 Kirby & Glenshire (NE Corner) NaN 40.098683 -88.311765 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
8 WDSRMTRY:3 MTD2774 Windsor & Monterey (SW Corner) NaN 40.083500 -88.253890 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
9 GC:2 MTD2437 Ginger Creek (North Side) NaN 40.113025 -88.292158 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
10 MTSGRN:2 MTD4362 Mattis & Green (SE Corner) NaN 40.110246 -88.276632 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
11 PKNSCROL:3 MTD6764 Perkins & Carroll (SW Corner) NaN 40.127425 -88.194897 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
12 ODSS:3 MTD7317 Orchard Downs South Shelter (East) NaN 40.090763 -88.211300 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
13 STNCRKSTLWTR:4 MTD9337 Stone Creek & Stillwater Landing (NW) NaN 40.090623 -88.168277 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
14 CLTNPMNA:3 MTD1625 Clayton & Pomona (SW Corner) NaN 40.122230 -88.293525 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
15 FOXREMAX:2 MTD3161 Fox at Remax (East Side) NaN 40.090370 -88.250592 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
16 MNLYN:1 MTD5574 Main & Lynn (NE Corner) NaN 40.113025 -88.197718 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
17 STLYCLBHS:2 MTD1137 Staley & Clubhouse (SE Corner) NaN 40.087357 -88.314085 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
18 DNCNBLR:2 MTD5051 Duncan & Blair (SE Corner) NaN 40.110759 -88.295385 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
19 SCWDCACT:2 MTD0303 Scottswood & Calif. Ct. (SE Corner) NaN 40.107903 -88.173545 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
20 MNCTGRV:1 MTD4532 Main & Cottage Grove (NE Corner) NaN 40.113018 -88.195532 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
21 FLKNCH:1 MTD0113 Florida & Kinch (NE Corner) NaN 40.098873 -88.181192 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
22 PKMCCLGH:2 MTD5873 Park & McCullough (SE Corner) NaN 40.117309 -88.212682 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
23 PSPCTPKLN:4 MTD1134 Prospect & Park Ln. (NW Corner) NaN 40.080793 -88.257512 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
24 GRN4TH:4 MTD3311 GRN4TH:4 NaN 40.110392 -88.233609 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
25 VINFRLN:6 MTD6167 Vine & Fairlawn (SE Far Side) NaN 40.103738 -88.204327 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
26 VINGRN:6 MTD7423 Vine & Green (SE Far Side) NaN 40.110747 -88.204477 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
27 MISMITH:3 MTD1315 Michigan & Smith (SW Corner) NaN 40.101822 -88.176163 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
28 VINEMI:4 MTD0982 Vine & Michigan (NW Corner) NaN 40.102072 -88.205013 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
29 LNCLNSMR:2 MTD7337 Lincoln & St. Marys (SE) NaN 40.094482 -88.218958 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
... ... ... ... ... ... ... ... ... ... ...
2466 NVLNCLN:3 MTD2463 Nevada & Lincoln (SW Corner) NaN 40.105932 -88.219690 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2467 MKTKNYN:2 MTD3064 Market & Kenyon (East Side) NaN 40.134465 -88.238833 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2468 LKVWWGWD:5 MTD2271 Lakeview & Wedgewood (SW Corner) NaN 40.077770 -88.291560 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2469 BNFLDAGNS:2 MTD3270 Brownfield & Agnes (SE Corner) NaN 40.140638 -88.170595 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2470 WLMSTFRD:1 MTD6572 William & Stratford (NE Corner) NaN 40.106076 -88.291552 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2471 STEJOHN:4 MTD1661 State & John (NW Corner) NaN 40.109070 -88.246834 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2472 BDWYLST:4 MTD2352 Broadway & Stebbins (NW) NaN 40.120148 -88.207620 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2473 WLNTCLMBA:2 MTD4677 Walnut & Columbia (SE Corner) NaN 40.120998 -88.241175 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2474 BNBLRGDMD:1 MTD5227 Bonnie Blair & Gold Medal (NE Corner) NaN 40.132350 -88.298253 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2475 RACEHIGH:2 MTD1460 Race & High St. (SE Corner) NaN 40.109833 -88.208792 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2476 MKTBELL:4 MTD4412 Market & Bellefontaine (NW Corner) NaN 40.129872 -88.238918 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2477 MHRYFLCHR:3 MTD5125 McHenry & Fletcher (South Side) NaN 40.087808 -88.192845 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2478 MNHTL:3 MTD4754 Main & Hartle (SW Corner) NaN 40.112917 -88.192403 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2479 BRTTRLMLKN:2 MTD5477 Brittany Trail & Mullikin (SE Corner) NaN 40.092361 -88.322105 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2480 BRADHGN:3 MTD6232 Bradley & Hagan (South Side) NaN 40.127122 -88.254950 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2481 WSFLDCRLTN:2 MTD0573 Westfield & Carrelton (SE Corner) NaN 40.101624 -88.289813 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2482 UNILAKE:1 MTD7243 University & Lake (NE Corner) NaN 40.116560 -88.209785 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2483 PSL:2 MTD6731 Plant Sciences Lab (East Side) NaN 40.102528 -88.221767 2 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2484 CFDPK:4 MTD3416 Country Fair Dr. & Park (NW Corner) NaN 40.117613 -88.281792 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2485 KBYWSFLD:1 MTD4346 Kirby & Westfield (NE Corner) NaN 40.098472 -88.289788 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2486 WRTDBLN:2 MTD2375 Wright & Dublin (SE Corner) NaN 40.121113 -88.228807 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2487 BRADNEIL:1 MTD5904 Bradley & Neil (NE Corner) NaN 40.127290 -88.242865 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2488 PSPCTBDMR:4 MTD3103 Prospect & Broadmoor (NW Corner) NaN 40.092835 -88.257678 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2489 CFW:1 MTD2643 Country Fair West Entrance (NE Corner) NaN 40.114282 -88.281910 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2490 NVLNCLN:8 MTD2463 Nevada & Lincoln (NW Far Side) NaN 40.106012 -88.219610 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2491 WLMDNCN:4 MTD5402 William & Duncan (NW Corner) NaN 40.106132 -88.295417 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2492 JOHNPINE:3 MTD5640 John & Pine (SW Corner) NaN 40.108992 -88.255920 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2493 NEILEGB:2 MTD1133 Neil & Edgebrook (SE Corner) NaN 40.131287 -88.243537 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2494 CTGRVGRN:2 MTD5363 Cottage Grove & Green (SE Corner) NaN 40.110847 -88.195737 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN
2495 CRSTWDBRAD:2 MTD4333 Crestwood & Bradley (SE Corner) NaN 40.127552 -88.300077 1 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN

2496 rows × 10 columns


In [24]:
# Plotting Density scatter plot on google maps using gmplot library
gmap = gmplot.GoogleMapPlotter(40.088, -88.281, 16)
gmap.scatter(stops.stop_lat, stops.stop_lon,'r', size=30, marker=False)
gmap.draw('density.html')

In [25]:
# Plotting Density heatmap plot on google maps
gmap = gmplot.GoogleMapPlotter(40.088, -88.281, 16)
gmap.heatmap(stops.stop_lat, stops.stop_lon)
gmap.draw('density_heatmap.html')

In [26]:
plt.hexbin(stops.stop_lat, stops.stop_lon, bins = 'log', cmap = 'viridis')
cb = plt.colorbar()
cb.set_label('Stop Density')
plt.title("Spatial Distribution of Stops")
plt.xlabel("Latitude")
plt.ylabel("Longitude")


Out[26]:
<matplotlib.text.Text at 0x114178a50>

In [27]:
stop_times = pd.read_csv("google_transit/stop_times.txt")

In [28]:
stop_times


Out[28]:
trip_id arrival_time departure_time stop_id stop_sequence stop_headsign pickup_type drop_off_type
0 [@14.0.51708725@][4][1277756770140]/0__T4_UIMF 07:25:00 07:25:00 DEPOT:1 0 WEST - ILLINOIS TERMINAL 0 0
1 [@14.0.51708725@][4][1277756770140]/0__T4_UIMF 07:35:00 07:35:00 PAR:2 2 WEST - ILLINOIS TERMINAL 0 0
2 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:35:00 07:35:00 PAR:2 0 WEST - ILLINOIS TERMINAL 0 0
3 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:36:35 07:36:35 PAMD:2 1 NaN 0 0
4 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:37:48 07:37:48 PSL:2 2 NaN 0 0
5 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:38:26 07:38:26 GRGDNR:2 3 NaN 0 0
6 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:39:04 07:39:04 GWNGRG:1 4 NaN 0 0
7 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:39:42 07:39:42 GWNNV:2 5 NaN 0 0
8 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:41:02 07:41:02 KRANNERT:2 6 NaN 0 0
9 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:42:00 07:42:00 GRNMAT:1 7 NaN 0 0
10 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:43:00 07:43:00 IU:2 8 NaN 0 0
11 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:44:30 07:44:30 WRTSPFLD:2 9 NaN 0 0
12 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:45:28 07:45:28 WRTSTOTN:2 10 NaN 0 0
13 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:46:13 07:46:13 WHT6TH:1 11 NaN 0 0
14 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:46:58 07:46:58 WHT4TH:1 12 NaN 0 0
15 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:47:30 07:47:30 WHT2ND:1 13 NaN 0 0
16 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:47:54 07:47:54 WHT1ST:1 14 NaN 0 0
17 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:50:00 07:50:00 IT:5 15 NaN 0 0
18 [@7.0.41893871@][3][1243541396687]/72__T4_UIMF 07:55:00 07:55:00 IT:5 0 EAST - ORCHARD DOWNS 0 0
19 [@7.0.41893871@][3][1243541396687]/72__T4_UIMF 07:56:54 07:56:54 LGN1ST:3 1 NaN 0 0
20 [@7.0.41893871@][3][1243541396687]/72__T4_UIMF 07:57:22 07:57:22 WHT2ND:3 2 NaN 0 0
21 [@7.0.41893871@][3][1243541396687]/72__T4_UIMF 07:57:46 07:57:46 WHT4TH:3 3 NaN 0 0
22 [@7.0.41893871@][3][1243541396687]/72__T4_UIMF 07:58:46 07:58:46 WHT6TH:3 4 NaN 0 0
23 [@7.0.41893871@][3][1243541396687]/72__T4_UIMF 07:59:14 07:59:14 WHTWRT:3 5 NaN 0 0
24 [@7.0.41893871@][3][1243541396687]/72__T4_UIMF 08:00:10 08:00:10 WRTSPFLD:4 6 NaN 0 0
25 [@7.0.41893871@][3][1243541396687]/72__T4_UIMF 08:00:38 08:00:38 WRTHLY:4 7 NaN 0 0
26 [@7.0.41893871@][3][1243541396687]/72__T4_UIMF 08:02:00 08:02:00 IU:1 8 NaN 0 0
27 [@7.0.41893871@][3][1243541396687]/72__T4_UIMF 08:02:40 08:02:40 GRNMAT:3 9 NaN 0 0
28 [@7.0.41893871@][3][1243541396687]/72__T4_UIMF 08:03:00 08:03:00 GRNGWN:3 10 NaN 0 0
29 [@7.0.41893871@][3][1243541396687]/72__T4_UIMF 08:03:20 08:03:20 CHEMLS:1 11 NaN 0 0
... ... ... ... ... ... ... ... ...
242828 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:45:00 15:46:00 UMS:7 1 NaN 0 0
242829 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:50:00 15:50:00 CNHMCLD:2 2 NaN 0 0
242830 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:51:00 15:51:00 KERCNHM:2 3 NaN 0 0
242831 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:52:00 15:52:00 TCA:1 4 NaN 0 0
242832 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:52:35 15:52:35 KERCROL:1 5 NaN 0 0
242833 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:53:00 15:53:00 EASTKER:3 6 NaN 0 0
242834 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:53:45 15:53:45 EAST:2 7 NaN 0 0
242835 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:55:00 15:55:00 PKNSIVAN:3 8 NaN 0 0
242836 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:56:00 15:56:00 BNFLDPKNS:2 9 NaN 0 0
242837 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:56:12 15:56:12 CROLFD:3 10 NaN 0 0
242838 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:57:27 15:57:27 PKNSIVAN:1 11 NaN 0 0
242839 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:58:22 15:58:22 PKNSCROL:1 12 NaN 0 0
242840 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:59:37 15:59:37 CROLKER:4 13 NaN 0 0
242841 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:00:12 16:00:12 TCA:2 14 NaN 0 0
242842 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:01:42 16:01:42 UNIMPL:3 15 NaN 0 0
242843 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:02:02 16:02:02 UNISCMR:3 16 NaN 0 0
242844 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:02:22 16:02:22 UNIMTD:3 17 NaN 0 0
242845 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:02:42 16:02:42 UNIHKRY:3 18 NaN 0 0
242846 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:03:02 16:03:02 UNICTGRV:3 19 NaN 0 0
242847 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:03:52 16:03:52 AMBUC:1 20 NaN 0 0
242848 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:05:52 16:05:52 SMITH150:3 21 NaN 0 0
242849 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:07:02 16:07:02 150DOD:5 22 NaN 0 0
242850 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:07:42 16:07:42 DODSLYBK:2 23 NaN 0 0
242851 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:08:02 16:08:02 SLYBKDOD:1 24 NaN 0 0
242852 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:08:22 16:08:22 SLYBKIRA:1 25 NaN 0 0
242853 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:08:37 16:08:37 SLYBKCRE:1 26 NaN 0 0
242854 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:08:47 16:08:47 SLYBKMG:1 27 NaN 0 0
242855 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:08:57 16:08:57 SLYBKSMITH:1 28 NaN 0 0
242856 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:09:17 16:09:17 SMITHCRE:4 29 NaN 0 0
242857 [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:09:37 16:09:37 SMITH150:4 30 NaN 0 0

242858 rows × 8 columns


In [29]:
stop_times.loc[stop_times['trip_id']=='[@14.0.56288404@][41][1316446361177]/2__R2_MF']


Out[29]:
trip_id arrival_time departure_time stop_id stop_sequence stop_headsign pickup_type drop_off_type
54902 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:38:00 06:38:00 LSE:1 0 C - TRAILS - IT 0 0
54903 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:38:48 06:38:48 VINGRN:6 1 NaN 0 0
54904 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:39:04 06:39:04 GRNUBNA:3 2 NaN 0 0
54905 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:39:20 06:39:20 GRNMPL:3 3 NaN 0 0
54906 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:39:36 06:39:36 GRNGRV:3 4 NaN 0 0
54907 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:39:52 06:39:52 GRNADRSN:3 5 NaN 0 0
54908 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:40:08 06:40:08 GRNWBR:3 6 NaN 0 0
54909 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:40:24 06:40:24 GRNLYN:3 7 NaN 0 0
54910 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:40:40 06:40:40 GRNJNSN:3 8 NaN 0 0
54911 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:40:51 06:40:51 CTGRVGRN:3 9 NaN 0 0
54912 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:41:07 06:41:07 CTGRVIL:4 10 NaN 0 0
54913 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:41:23 06:41:23 CTGRVCA:4 11 NaN 0 0
54914 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:41:39 06:41:39 CTGRVOR:4 12 NaN 0 0
54915 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:42:00 06:42:00 CTGRVWASH:4 13 NaN 0 0
54916 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:42:15 06:42:15 CTGRVERN:4 14 NaN 0 0
54917 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:42:30 06:42:30 CTGRVCRWD:4 15 NaN 0 0
54918 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:42:45 06:42:45 FRLNCTGRV:6 16 NaN 0 0
54919 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:43:00 06:43:00 FRLNGRNT:3 17 NaN 0 0
54920 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:43:15 06:43:15 FRLNPTN:3 18 NaN 0 0
54921 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:43:30 06:43:30 FRLNERN:3 19 NaN 0 0
54922 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:43:45 06:43:45 PHILOFRLN:3 20 NaN 0 0
54923 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:44:00 06:44:00 PHILOMI:4 21 NaN 0 0
54924 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:44:15 06:44:15 PHILOLRL:4 22 NaN 0 0
54925 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:44:30 06:44:30 PHILOBCLF:4 23 NaN 0 0
54926 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:44:45 06:44:45 PHILOPA:4 24 NaN 0 0
54927 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:45:00 06:45:00 FLPHILO:4 25 NaN 0 0
54928 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:45:40 06:45:40 SUNNYCREST:1 26 NaN 0 0
54929 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:46:20 06:46:20 PHILOCO:4 27 NaN 0 0
54930 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:47:00 06:47:00 PHILOHRDG:4 28 NaN 0 0
54931 [@14.0.56288404@][41][1316446361177]/2__R2_MF 06:47:40 06:47:40 HRDGSTP:2 29 NaN 0 0
... ... ... ... ... ... ... ... ...
55019 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:27:12 07:27:12 NEILBELL:2 117 NaN 0 0
55020 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:27:22 07:27:22 NEILEGB:2 118 NaN 0 0
55021 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:28:03 07:28:03 NEILANT:2 119 NaN 0 0
55022 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:28:20 07:28:20 NEILMKTV:2 120 NaN 0 0
55023 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:28:36 07:28:36 NEILCD:1 121 NaN 0 0
55024 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:28:53 07:28:53 MKTPLCCD:3 122 NaN 0 0
55025 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:29:10 07:29:10 MKTPLCSE:3 123 NaN 0 0
55026 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:29:24 07:29:24 MKPLCWLB:2 124 NaN 0 0
55027 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:29:44 07:29:44 MKTPLCN:2 125 NaN 0 0
55028 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:30:00 07:30:00 MKTPLC:1 126 NaN 0 0
55029 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:30:40 07:30:40 MKTPLCN:2 127 NaN 0 0
55030 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:32:40 07:32:40 MKTMERC:2 128 NaN 0 0
55031 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:33:00 07:33:00 MERCFEDEX:3 129 NaN 0 0
55032 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:34:20 07:34:20 MERCGEMCT:3 130 NaN 0 0
55033 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:35:40 07:35:40 MERCAPLO:5 131 NaN 0 0
55034 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:37:10 07:37:10 VKINGN:1 132 NaN 0 0
55035 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:37:55 07:37:55 VKINGS:2 133 NaN 0 0
55036 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:38:40 07:38:40 MERCGEMCT:1 134 NaN 0 0
55037 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:40:10 07:40:10 MERCFEDEX:2 135 NaN 0 0
55038 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:41:40 07:41:40 MKTPLCNMKT:4 136 NaN 0 0
55039 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:44:10 07:44:10 MKTPLCW:4 137 NaN 0 0
55040 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:45:35 07:45:35 TCBNEIL:1 138 NaN 0 0
55041 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:46:52 07:46:52 NEILTCA:2 139 NaN 0 0
55042 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:47:27 07:47:27 NEILWLGFRD:2 140 NaN 0 0
55043 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:48:12 07:48:12 NEILTWN:2 141 NaN 0 0
55044 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:49:12 07:49:12 NEILINTR:2 142 NaN 0 0
55045 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:50:00 07:50:00 TLSNINTER:1 143 NaN 0 0
55046 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:50:30 07:50:30 INTWALMART:1 144 NaN 0 0
55047 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:51:00 07:51:00 PSPCTINTER:1 145 NaN 0 0
55048 [@14.0.56288404@][41][1316446361177]/2__R2_MF 07:52:00 07:52:00 NWTNNBL:3 146 NaN 0 0

147 rows × 8 columns


In [30]:
len(stop_times.trip_id.unique())


Out[30]:
5498

In [31]:
yellow_100n = shapes.loc[shapes["shape_id"] == "100N"]
teal = shapes.loc[shapes["shape_id"] == "[@124.0.92275054@]120W TEAL LATE 12"]
green = shapes.loc[shapes["shape_id"] == "[@124.0.92311676@]50E GREEN 53"]
red = shapes.loc[shapes["shape_id"] == "RED 6"]
gold = shapes.loc[shapes["shape_id"] == "[@2.0.84927215@]GOLD 1"]
silver = shapes.loc[shapes["shape_id"] == "[@124.0.92286725@]130N SILVER EVENING 1"]
purple = shapes.loc[shapes["shape_id"] == "[@124.0.92263401@]220N ILLINI 10"]
orange = shapes.loc[shapes["shape_id"] == "[@2.0.86175868@]ORANGE 33"]

In [32]:
# Map showing high freq bus routed on map of CU
datafile = cbook.get_sample_data('/Users/arpitgarg/Documents/UIUC/MSIM/spring17/Data-Visualization-Class/Project/project-spring2017/part1/map.png')
img = imread(datafile)
plt.imshow(img, zorder=0, extent=[-88.32, -88.18, 40.04, 40.16])
#plt.show()
plt.rcParams["figure.figsize"] = (14,14)
plt.plot(yellow_100n.shape_pt_lon, yellow_100n.shape_pt_lat,'.y', alpha =0.5)
plt.plot(teal.shape_pt_lon, teal.shape_pt_lat,'.b', alpha =0.5)
plt.plot(green.shape_pt_lon, green.shape_pt_lat,'.g', alpha =0.5)
plt.plot(red.shape_pt_lon, red.shape_pt_lat,'.r', alpha =0.5)
plt.plot(gold.shape_pt_lon, gold.shape_pt_lat,'.', alpha =0.5, color="#c7994a")
plt.plot(silver.shape_pt_lon, silver.shape_pt_lat,'.', alpha =0.5, color="#cccccc")
plt.plot(purple.shape_pt_lon, purple.shape_pt_lat,'.', alpha =0.5,color="#5a1d5a")
plt.plot(orange.shape_pt_lon, orange.shape_pt_lat,'.', alpha =0.5,color="#f99f2a")
plt.grid()
plt.title("High Frequency Map")
plt.xlabel("Longitude")
plt.ylabel("Latitude")


Out[32]:
<matplotlib.text.Text at 0x1190bff50>

In [34]:
count=0
for i in range(len(stop_times.values)):
    if stop_times.arrival_time[i] != stop_times.departure_time[i]:
        #print "Not equal at " + str(i)
        count+=1
count


Out[34]:
642

In [35]:
freq_stops = stop_times['stop_id'].value_counts().to_dict()

In [36]:
len(freq_stops)


Out[36]:
2496

In [37]:
most_freq = sorted(freq_stops.items(), key = lambda x: x[1], reverse=True)[0:12]

In [38]:
freq_stops
type(freq_stops.values()[0])


Out[38]:
numpy.int64

In [39]:
most_freq


Out[39]:
[('PAR:2', 1732),
 ('IU:1', 1674),
 ('GRNMAT:3', 1660),
 ('IU:2', 1629),
 ('GRNMAT:1', 1617),
 ('ARYWRT:3', 1451),
 ('IT:5', 1317),
 ('PLAZA:4', 1292),
 ('LSE:8', 1286),
 ('WRTCHAL:4', 1247),
 ('PLAZA:3', 1174),
 ('PAMD:2', 1091)]

In [40]:
stops.loc[stops.stop_id == 'PAR:2']


Out[40]:
stop_id stop_code stop_name stop_desc stop_lat stop_lon zone_id stop_url location_type parent_station
2314 PAR:2 MTD5524 PAR (North Side Shelter) NaN 40.09949 -88.220416 2 http://www.cumtd.com/maps-and-schedules/bus-st... 0 NaN

In [41]:
most_freq_ids= [i[0] for i in most_freq]

In [42]:
stops.groupby('zone_id').count()['stop_id']


Out[42]:
zone_id
1    2386
2     110
Name: stop_id, dtype: int64

In [43]:
most_freq_names=[]
stop_times_freq_id = pd.DataFrame()
#most_freq = [i[1] for i in most_freq]
for i in most_freq_ids:
    most_freq_names.append(str(stops.loc[stops.stop_id==i,'stop_name']))

In [44]:
most_freq = [i[1] for i in most_freq]

In [45]:
most_freq_names[0]


Out[45]:
'2314    PAR (North Side Shelter)\nName: stop_name, dtype: object'

In [46]:
plt.rcParams["figure.figsize"] = 8,8
plt.bar(np.arange(12), most_freq,0.35,color='brown')
plt.xticks(np.arange(12), most_freq_ids, rotation=45)
plt.xlabel('Stops')
plt.ylabel('Frequency of Buses')
plt.title('High Frequency Bus Stops of CUMTD')


Out[46]:
<matplotlib.text.Text at 0x119f00350>

In [47]:
frames = [stop_times, trips]

In [48]:
df2 = trips.join(stop_times, on='trip_id', how='outer', lsuffix='_stops',rsuffix='_trip')

In [49]:
df2


Out[49]:
trip_id route_id service_id trip_id_stops trip_headsign direction_id block_id shape_id trip_id_trip arrival_time departure_time stop_id stop_sequence stop_headsign pickup_type drop_off_type
0 [@14.0.51708725@][4][1277756770140]/0__T4_UIMF TEAL T4 UIMF [@14.0.51708725@][4][1277756770140]/0__T4_UIMF WEST - ILLINOIS TERMINAL 1.0 T4 UIMF TEAL 26 NaN NaN NaN NaN NaN NaN NaN NaN
1 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF TEAL T4 UIMF [@14.0.51708725@][4][1275505811421]/0__T4_UIMF WEST - ILLINOIS TERMINAL 1.0 T4 UIMF TEAL 23 NaN NaN NaN NaN NaN NaN NaN NaN
2 [@7.0.41893871@][3][1243541396687]/72__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/72__T4_UIMF EAST - ORCHARD DOWNS 0.0 T4 UIMF 12E TEAL 13 NaN NaN NaN NaN NaN NaN NaN NaN
3 [@7.0.41893871@][4][1243540851671]/4__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/4__T4_UIMF WEST - ILLINOIS TERMINAL 1.0 T4 UIMF 12W TEAL 12 NaN NaN NaN NaN NaN NaN NaN NaN
4 [@7.0.41893871@][3][1243541396687]/74__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/74__T4_UIMF EAST - ORCHARD DOWNS 0.0 T4 UIMF 12E TEAL 13 NaN NaN NaN NaN NaN NaN NaN NaN
5 [@7.0.41893871@][4][1243540851671]/6__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/6__T4_UIMF WEST - ILLINOIS TERMINAL 1.0 T4 UIMF 12W TEAL 12 NaN NaN NaN NaN NaN NaN NaN NaN
6 [@7.0.41893871@][3][1243541488843]/110__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][3][1243541488843]/110__T4_UIMF EAST - PAR 0.0 T4 UIMF TEAL 34 NaN NaN NaN NaN NaN NaN NaN NaN
7 [@14.0.51708725@][4][1275506079140]/6__T4_UIMF TEAL T4 UIMF [@14.0.51708725@][4][1275506079140]/6__T4_UIMF WEST - ILLINOIS TERMINAL 1.0 T4 UIMF TEAL 23 NaN NaN NaN NaN NaN NaN NaN NaN
8 [@7.0.41893871@][3][1243541396687]/79__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/79__T4_UIMF EAST - ORCHARD DOWNS 0.0 T4 UIMF 12E TEAL 13 NaN NaN NaN NaN NaN NaN NaN NaN
9 [@7.0.41893871@][4][1243540851671]/11__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/11__T4_UIMF WEST - ILLINOIS TERMINAL 1.0 T4 UIMF 12W TEAL 12 NaN NaN NaN NaN NaN NaN NaN NaN
10 [@7.0.41893871@][3][1243541396687]/81__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/81__T4_UIMF EAST - ORCHARD DOWNS 0.0 T4 UIMF 12E TEAL 13 NaN NaN NaN NaN NaN NaN NaN NaN
11 [@7.0.41893871@][4][1243540851671]/13__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/13__T4_UIMF WEST - ILLINOIS TERMINAL 1.0 T4 UIMF 12W TEAL 12 NaN NaN NaN NaN NaN NaN NaN NaN
12 [@7.0.41893871@][3][1243541396687]/83__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/83__T4_UIMF EAST - ORCHARD DOWNS 0.0 T4 UIMF 12E TEAL 13 NaN NaN NaN NaN NaN NaN NaN NaN
13 [@7.0.41893871@][4][1243540851671]/15__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/15__T4_UIMF WEST - ILLINOIS TERMINAL 1.0 T4 UIMF 12W TEAL 12 NaN NaN NaN NaN NaN NaN NaN NaN
14 [@7.0.41893871@][3][1243541488843]/119__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][3][1243541488843]/119__T4_UIMF EAST - PAR 0.0 T4 UIMF TEAL 34 NaN NaN NaN NaN NaN NaN NaN NaN
15 [@14.0.51708725@][4][1275506079140]/12__T4_UIMF TEAL T4 UIMF [@14.0.51708725@][4][1275506079140]/12__T4_UIMF WEST - ILLINOIS TERMINAL 1.0 T4 UIMF TEAL 23 NaN NaN NaN NaN NaN NaN NaN NaN
16 [@7.0.41893871@][3][1243541396687]/88__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/88__T4_UIMF EAST - ORCHARD DOWNS 0.0 T4 UIMF 12E TEAL 13 NaN NaN NaN NaN NaN NaN NaN NaN
17 [@7.0.41893871@][4][1243540851671]/20__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/20__T4_UIMF WEST - ILLINOIS TERMINAL 1.0 T4 UIMF 12W TEAL 12 NaN NaN NaN NaN NaN NaN NaN NaN
18 [@7.0.41893871@][3][1243541396687]/90__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/90__T4_UIMF EAST - ORCHARD DOWNS 0.0 T4 UIMF 12E TEAL 13 NaN NaN NaN NaN NaN NaN NaN NaN
19 [@7.0.41893871@][4][1243540851671]/22__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/22__T4_UIMF WEST - ILLINOIS TERMINAL 1.0 T4 UIMF 12W TEAL 12 NaN NaN NaN NaN NaN NaN NaN NaN
20 [@7.0.41893871@][3][1243541396687]/92__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/92__T4_UIMF EAST - ORCHARD DOWNS 0.0 T4 UIMF 12E TEAL 13 NaN NaN NaN NaN NaN NaN NaN NaN
21 [@7.0.41893871@][4][1243540851671]/24__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/24__T4_UIMF WEST - ILLINOIS TERMINAL 1.0 T4 UIMF 12W TEAL 12 NaN NaN NaN NaN NaN NaN NaN NaN
22 [@7.0.41893871@][3][1243541488843]/128__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][3][1243541488843]/128__T4_UIMF EAST - PAR 0.0 T4 UIMF TEAL 34 NaN NaN NaN NaN NaN NaN NaN NaN
23 [@14.0.51708725@][4][1275506123875]/18__T4_UIMF TEAL T4 UIMF [@14.0.51708725@][4][1275506123875]/18__T4_UIMF WEST - ILLINOIS TERMINAL 1.0 T4 UIMF TEAL 23 NaN NaN NaN NaN NaN NaN NaN NaN
24 [@7.0.41893871@][3][1243541396687]/97__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/97__T4_UIMF EAST - ORCHARD DOWNS 0.0 T4 UIMF 12E TEAL 13 NaN NaN NaN NaN NaN NaN NaN NaN
25 [@7.0.41893871@][4][1243540851671]/29__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/29__T4_UIMF WEST - ILLINOIS TERMINAL 1.0 T4 UIMF 12W TEAL 12 NaN NaN NaN NaN NaN NaN NaN NaN
26 [@7.0.41893871@][3][1243541396687]/99__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/99__T4_UIMF EAST - ORCHARD DOWNS 0.0 T4 UIMF 12E TEAL 13 NaN NaN NaN NaN NaN NaN NaN NaN
27 [@7.0.41893871@][4][1243540851671]/31__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/31__T4_UIMF WEST - ILLINOIS TERMINAL 1.0 T4 UIMF 12W TEAL 12 NaN NaN NaN NaN NaN NaN NaN NaN
28 [@7.0.41893871@][3][1243541396687]/101__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/101__T4_UIMF EAST - ORCHARD DOWNS 0.0 T4 UIMF 12E TEAL 13 NaN NaN NaN NaN NaN NaN NaN NaN
29 [@7.0.41893871@][4][1243540851671]/33__T4_UIMF TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/33__T4_UIMF WEST - ILLINOIS TERMINAL 1.0 T4 UIMF 12W TEAL 12 NaN NaN NaN NaN NaN NaN NaN NaN
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
5497 242828 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:45:00 15:46:00 UMS:7 1.0 NaN 0.0 0.0
5497 242829 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:50:00 15:50:00 CNHMCLD:2 2.0 NaN 0.0 0.0
5497 242830 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:51:00 15:51:00 KERCNHM:2 3.0 NaN 0.0 0.0
5497 242831 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:52:00 15:52:00 TCA:1 4.0 NaN 0.0 0.0
5497 242832 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:52:35 15:52:35 KERCROL:1 5.0 NaN 0.0 0.0
5497 242833 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:53:00 15:53:00 EASTKER:3 6.0 NaN 0.0 0.0
5497 242834 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:53:45 15:53:45 EAST:2 7.0 NaN 0.0 0.0
5497 242835 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:55:00 15:55:00 PKNSIVAN:3 8.0 NaN 0.0 0.0
5497 242836 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:56:00 15:56:00 BNFLDPKNS:2 9.0 NaN 0.0 0.0
5497 242837 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:56:12 15:56:12 CROLFD:3 10.0 NaN 0.0 0.0
5497 242838 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:57:27 15:57:27 PKNSIVAN:1 11.0 NaN 0.0 0.0
5497 242839 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:58:22 15:58:22 PKNSCROL:1 12.0 NaN 0.0 0.0
5497 242840 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 15:59:37 15:59:37 CROLKER:4 13.0 NaN 0.0 0.0
5497 242841 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:00:12 16:00:12 TCA:2 14.0 NaN 0.0 0.0
5497 242842 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:01:42 16:01:42 UNIMPL:3 15.0 NaN 0.0 0.0
5497 242843 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:02:02 16:02:02 UNISCMR:3 16.0 NaN 0.0 0.0
5497 242844 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:02:22 16:02:22 UNIMTD:3 17.0 NaN 0.0 0.0
5497 242845 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:02:42 16:02:42 UNIHKRY:3 18.0 NaN 0.0 0.0
5497 242846 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:03:02 16:03:02 UNICTGRV:3 19.0 NaN 0.0 0.0
5497 242847 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:03:52 16:03:52 AMBUC:1 20.0 NaN 0.0 0.0
5497 242848 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:05:52 16:05:52 SMITH150:3 21.0 NaN 0.0 0.0
5497 242849 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:07:02 16:07:02 150DOD:5 22.0 NaN 0.0 0.0
5497 242850 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:07:42 16:07:42 DODSLYBK:2 23.0 NaN 0.0 0.0
5497 242851 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:08:02 16:08:02 SLYBKDOD:1 24.0 NaN 0.0 0.0
5497 242852 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:08:22 16:08:22 SLYBKIRA:1 25.0 NaN 0.0 0.0
5497 242853 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:08:37 16:08:37 SLYBKCRE:1 26.0 NaN 0.0 0.0
5497 242854 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:08:47 16:08:47 SLYBKMG:1 27.0 NaN 0.0 0.0
5497 242855 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:08:57 16:08:57 SLYBKSMITH:1 28.0 NaN 0.0 0.0
5497 242856 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:09:17 16:09:17 SMITHCRE:4 29.0 NaN 0.0 0.0
5497 242857 NaN NaN NaN NaN NaN NaN NaN [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... 16:09:37 16:09:37 SMITH150:4 30.0 NaN 0.0 0.0

248356 rows × 16 columns


In [50]:
stop_times_freq_id = pd.DataFrame()
for i in most_freq_ids:
    stop_times_freq_id = stop_times_freq_id.append(stop_times.loc[stop_times.stop_id==i])

In [51]:
most_freq_ids


Out[51]:
['PAR:2',
 'IU:1',
 'GRNMAT:3',
 'IU:2',
 'GRNMAT:1',
 'ARYWRT:3',
 'IT:5',
 'PLAZA:4',
 'LSE:8',
 'WRTCHAL:4',
 'PLAZA:3',
 'PAMD:2']

In [52]:
stop_times_freq_id


Out[52]:
trip_id arrival_time departure_time stop_id stop_sequence stop_headsign pickup_type drop_off_type
1 [@14.0.51708725@][4][1277756770140]/0__T4_UIMF 07:35:00 07:35:00 PAR:2 2 WEST - ILLINOIS TERMINAL 0 0
2 [@14.0.51708725@][4][1275505811421]/0__T4_UIMF 07:35:00 07:35:00 PAR:2 0 WEST - ILLINOIS TERMINAL 0 0
52 [@7.0.41893871@][4][1243540851671]/4__T4_UIMF 08:21:00 08:21:00 PAR:2 7 NaN 0 0
102 [@7.0.41893871@][4][1243540851671]/6__T4_UIMF 09:01:00 09:01:00 PAR:2 7 NaN 0 0
135 [@7.0.41893871@][3][1243541488843]/110__T4_UIMF 09:40:00 09:40:00 PAR:2 17 NaN 0 0
136 [@14.0.51708725@][4][1275506079140]/6__T4_UIMF 09:51:00 09:51:00 PAR:2 0 WEST - ILLINOIS TERMINAL 0 0
186 [@7.0.41893871@][4][1243540851671]/11__T4_UIMF 10:41:00 10:41:00 PAR:2 7 NaN 0 0
236 [@7.0.41893871@][4][1243540851671]/13__T4_UIMF 11:21:00 11:21:00 PAR:2 7 NaN 0 0
286 [@7.0.41893871@][4][1243540851671]/15__T4_UIMF 12:01:00 12:01:00 PAR:2 7 NaN 0 0
319 [@7.0.41893871@][3][1243541488843]/119__T4_UIMF 12:40:00 12:40:00 PAR:2 17 NaN 0 0
320 [@14.0.51708725@][4][1275506079140]/12__T4_UIMF 12:51:00 12:51:00 PAR:2 0 WEST - ILLINOIS TERMINAL 0 0
370 [@7.0.41893871@][4][1243540851671]/20__T4_UIMF 13:41:00 13:41:00 PAR:2 7 NaN 0 0
420 [@7.0.41893871@][4][1243540851671]/22__T4_UIMF 14:21:00 14:21:00 PAR:2 7 NaN 0 0
470 [@7.0.41893871@][4][1243540851671]/24__T4_UIMF 15:01:00 15:01:00 PAR:2 7 NaN 0 0
503 [@7.0.41893871@][3][1243541488843]/128__T4_UIMF 15:40:00 15:40:00 PAR:2 17 NaN 0 0
504 [@14.0.51708725@][4][1275506123875]/18__T4_UIMF 15:51:00 15:51:00 PAR:2 0 WEST - ILLINOIS TERMINAL 0 0
554 [@7.0.41893871@][4][1243540851671]/29__T4_UIMF 16:41:00 16:41:00 PAR:2 7 NaN 0 0
604 [@7.0.41893871@][4][1243540851671]/31__T4_UIMF 17:21:00 17:21:00 PAR:2 7 NaN 0 0
654 [@7.0.41893871@][4][1243540851671]/33__T4_UIMF 18:01:00 18:01:00 PAR:2 7 NaN 0 0
1804 [@7.0.41893871@][4][1243540851671]/2__T3_NONUIMF 07:41:00 07:41:00 PAR:2 7 NaN 0 0
1854 [@7.0.41893871@][4][1243540851671]/4__T3_NONUIMF 08:21:00 08:21:00 PAR:2 7 NaN 0 0
1904 [@7.0.41893871@][4][1243540851671]/6__T3_NONUIMF 09:01:00 09:01:00 PAR:2 7 NaN 0 0
1954 [@7.0.41893871@][4][1243540851671]/8__T3_NONUIMF 09:41:00 09:41:00 PAR:2 7 NaN 0 0
2004 [@7.0.41893871@][4][1243540851671]/10__T3_NONUIMF 10:21:00 10:21:00 PAR:2 7 NaN 0 0
2054 [@7.0.41893871@][4][1243540851671]/12__T3_NONUIMF 11:01:00 11:01:00 PAR:2 7 NaN 0 0
2104 [@7.0.41893871@][4][1243540851671]/14__T3_NONUIMF 11:41:00 11:41:00 PAR:2 7 NaN 0 0
2154 [@7.0.41893871@][4][1243540851671]/16__T3_NONUIMF 12:21:00 12:21:00 PAR:2 7 NaN 0 0
2204 [@7.0.41893871@][4][1243540851671]/18__T3_NONUIMF 13:01:00 13:01:00 PAR:2 7 NaN 0 0
2254 [@7.0.41893871@][4][1243540851671]/20__T3_NONUIMF 13:41:00 13:41:00 PAR:2 7 NaN 0 0
2304 [@7.0.41893871@][4][1243540851671]/22__T3_NONUIMF 14:21:00 14:21:00 PAR:2 7 NaN 0 0
... ... ... ... ... ... ... ... ...
234367 [@12.0.44194660@][1250946648828]/0__T2NONUISA_EVE 07:22:25 07:22:25 PAMD:2 8 NaN 0 0
234417 [@12.0.44194660@][1250946648828]/2__T2NONUISA_EVE 08:02:25 08:02:25 PAMD:2 8 NaN 0 0
234467 [@12.0.44194660@][1250946648828]/4__T2NONUISA_EVE 08:42:25 08:42:25 PAMD:2 8 NaN 0 0
234517 [@12.0.44194660@][1250946648828]/6__T2NONUISA_EVE 09:22:25 09:22:25 PAMD:2 8 NaN 0 0
234567 [@12.0.44194660@][1250946648828]/8__T2NONUISA_EVE 10:02:25 10:02:25 PAMD:2 8 NaN 0 0
234617 [@12.0.44194660@][1250946648828]/10__T2NONUISA... 10:42:25 10:42:25 PAMD:2 8 NaN 0 0
234667 [@12.0.44194660@][1250946648828]/12__T2NONUISA... 11:22:25 11:22:25 PAMD:2 8 NaN 0 0
234717 [@12.0.44194660@][1250946648828]/14__T2NONUISA... 12:02:25 12:02:25 PAMD:2 8 NaN 0 0
234767 [@12.0.44194660@][1250946648828]/16__T2NONUISA... 12:42:25 12:42:25 PAMD:2 8 NaN 0 0
234817 [@12.0.44194660@][1250946648828]/18__T2NONUISA... 13:22:25 13:22:25 PAMD:2 8 NaN 0 0
234867 [@12.0.44194660@][1250946648828]/20__T2NONUISA... 14:02:25 14:02:25 PAMD:2 8 NaN 0 0
234917 [@12.0.44194660@][1250946648828]/22__T2NONUISA... 14:42:25 14:42:25 PAMD:2 8 NaN 0 0
234967 [@12.0.44194660@][1250946648828]/24__T2NONUISA... 15:22:25 15:22:25 PAMD:2 8 NaN 0 0
235017 [@12.0.44194660@][1250946648828]/26__T2NONUISA... 16:02:25 16:02:25 PAMD:2 8 NaN 0 0
235067 [@12.0.44194660@][1250946648828]/28__T2NONUISA... 16:42:25 16:42:25 PAMD:2 8 NaN 0 0
235117 [@12.0.44194660@][1250946648828]/30__T2NONUISA... 17:22:25 17:22:25 PAMD:2 8 NaN 0 0
235167 [@12.0.44194660@][1250946648828]/32__T2NONUISA... 18:02:25 18:02:25 PAMD:2 8 NaN 0 0
235217 [@12.0.44194660@][1250946648828]/34__T2NONUISA... 18:42:25 18:42:25 PAMD:2 8 NaN 0 0
238882 [@7.0.41950648@][1244056065453]/78__I4-3_UIMTH 19:48:30 19:48:30 PAMD:2 1 NaN 0 0
238963 [@7.0.41950648@][1244056065453]/86__I4-3_UIMTH 21:08:30 21:08:30 PAMD:2 1 NaN 0 0
239044 [@7.0.41950648@][1244056065453]/94__I4-3_UIMTH 22:28:30 22:28:30 PAMD:2 1 NaN 0 0
239125 [@7.0.41950648@][1244056065453]/102__I4-3_UIMTH 23:48:30 23:48:30 PAMD:2 1 NaN 0 0
239206 [@7.0.41950648@][1244056065453]/110__I4-3_UIMTH 25:08:30 25:08:30 PAMD:2 1 NaN 0 0
239287 [@7.0.41950648@][1244056065453]/118__I4-3_UIMTH 26:28:30 26:28:30 PAMD:2 1 NaN 0 0
239330 [@7.0.41950648@][1244056065453]/79__I6-4_UIMTH 19:58:30 19:58:30 PAMD:2 1 NaN 0 0
239419 [@7.0.41950648@][1244056065453]/87__I6-4_UIMTH 21:18:30 21:18:30 PAMD:2 1 NaN 0 0
239500 [@7.0.41950648@][1244056065453]/95__I6-4_UIMTH 22:38:30 22:38:30 PAMD:2 1 NaN 0 0
239581 [@7.0.41950648@][1244056065453]/103__I6-4_UIMTH 23:58:30 23:58:30 PAMD:2 1 NaN 0 0
239662 [@7.0.41950648@][1244056065453]/111__I6-4_UIMTH 25:18:30 25:18:30 PAMD:2 1 NaN 0 0
239743 [@7.0.41950648@][1244056065453]/119__I6-4_UIMTH 26:38:30 26:38:30 PAMD:2 1 NaN 0 0

17170 rows × 8 columns


In [53]:
routes[routes['route_id']=='TEAL']


Out[53]:
route_id agency_id route_short_name route_long_name route_desc route_type route_url route_color route_text_color
93 TEAL CUMTD 12 Teal NaN 3 NaN 006991 ffffff

In [54]:
trips


Out[54]:
route_id service_id trip_id trip_headsign direction_id block_id shape_id
0 TEAL T4 UIMF [@14.0.51708725@][4][1277756770140]/0__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF TEAL 26
1 TEAL T4 UIMF [@14.0.51708725@][4][1275505811421]/0__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF TEAL 23
2 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/72__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
3 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/4__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
4 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/74__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
5 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/6__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
6 TEAL T4 UIMF [@7.0.41893871@][3][1243541488843]/110__T4_UIMF EAST - PAR 0 T4 UIMF TEAL 34
7 TEAL T4 UIMF [@14.0.51708725@][4][1275506079140]/6__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF TEAL 23
8 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/79__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
9 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/11__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
10 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/81__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
11 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/13__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
12 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/83__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
13 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/15__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
14 TEAL T4 UIMF [@7.0.41893871@][3][1243541488843]/119__T4_UIMF EAST - PAR 0 T4 UIMF TEAL 34
15 TEAL T4 UIMF [@14.0.51708725@][4][1275506079140]/12__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF TEAL 23
16 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/88__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
17 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/20__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
18 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/90__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
19 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/22__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
20 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/92__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
21 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/24__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
22 TEAL T4 UIMF [@7.0.41893871@][3][1243541488843]/128__T4_UIMF EAST - PAR 0 T4 UIMF TEAL 34
23 TEAL T4 UIMF [@14.0.51708725@][4][1275506123875]/18__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF TEAL 23
24 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/97__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
25 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/29__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
26 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/99__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
27 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/31__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
28 TEAL T4 UIMF [@7.0.41893871@][3][1243541396687]/101__T4_UIMF EAST - ORCHARD DOWNS 0 T4 UIMF 12E TEAL 13
29 TEAL T4 UIMF [@7.0.41893871@][4][1243540851671]/33__T4_UIMF WEST - ILLINOIS TERMINAL 1 T4 UIMF 12W TEAL 12
... ... ... ... ... ... ... ...
5468 LIME SATURDAY LM1SA EVE [@15.0.73009433@][12][1402682836659]/37__LM1SA... B - POMONA & BRADLEY 1 LM1SA EVE [@15.0.73009433@]43
5469 LIME SATURDAY LM1SA EVE [@15.0.73009433@][11][1402676676689]/9__LM1SA_EVE A - KIRBY & DUNCAN 0 LM1SA EVE [@15.0.73009433@]51
5470 LIME SATURDAY LM1SA EVE [@15.0.73009433@][12][1402682836659]/39__LM1SA... B - POMONA & BRADLEY 1 LM1SA EVE [@15.0.73009433@]43
5471 LIME SATURDAY LM1SA EVE [@15.0.73009433@][11][1402676676689]/11__LM1SA... A - KIRBY & DUNCAN 0 LM1SA EVE [@15.0.73009433@]51
5472 LIME SATURDAY LM1SA EVE [@15.0.73009433@][12][1402678897199]/28__LM1SA... B - POMONA & BRADLEY 1 LM1SA EVE [@15.0.73009433@]41
5473 LIME SATURDAY LM1SA EVE [@15.0.73009433@][11][1402676676689]/13__LM1SA... A - KIRBY & DUNCAN 0 LM1SA EVE [@15.0.73009433@]51
5474 LIME SATURDAY LM1SA EVE [@15.0.73009433@][12][1402679001843]/29__LM1SA... B - POMONA & BRADLEY 1 LM1SA EVE [@15.0.73009433@]41
5475 LIME SATURDAY LM1SA EVE [@15.0.73009433@][11][1402676676689]/15__LM1SA... A - KIRBY & DUNCAN 0 LM1SA EVE [@15.0.73009433@]51
5476 LIME SATURDAY LM1SA EVE [@15.0.73009433@][12][1402683433784]/47__LM1SA... B - POMONA & BRADLEY 1 LM1SA EVE [@15.0.73009433@]45
5477 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402685655732]/0__LM2SA_EVE B - POMONA & BRADLEY 1 LM2SA EVE [@14.0.57586460@]38
5478 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402682631373]/31__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]43
5479 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676435949]/0__LM2SA_EVE A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5480 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402678344466]/24__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]41
5481 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676676689]/2__LM2SA_EVE A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5482 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402682836659]/32__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]43
5483 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676676689]/4__LM2SA_EVE A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5484 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402682836659]/34__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]43
5485 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676676689]/6__LM2SA_EVE A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5486 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402682836659]/36__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]43
5487 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676676689]/8__LM2SA_EVE A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5488 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402682836659]/38__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]43
5489 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676676689]/10__LM2SA... A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5490 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402682836659]/40__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]43
5491 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676676689]/12__LM2SA... A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5492 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402682859723]/41__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]43
5493 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676676689]/14__LM2SA... A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5494 LIME SATURDAY LM2SA EVE [@15.0.73009433@][12][1402679035062]/30__LM2SA... B - POMONA & BRADLEY 1 LM2SA EVE [@15.0.73009433@]41
5495 LIME SATURDAY LM2SA EVE [@15.0.73009433@][11][1402676676689]/16__LM2SA... A - KIRBY & DUNCAN 0 LM2SA EVE [@15.0.73009433@]51
5496 ORANGE ALT 6E SCH MTTF [@15.0.69155236@][3][1368635976437]/6__6E_SCH_... EAST - EDGEWOOD 0 6E SCH MTTF [@15.0.69155236@]530
5497 ORANGE ALT 6E SCH MTTF [@15.0.69155236@][3][1368635065623]/5__6E_SCH_... EAST - EDGEWOOD 0 6E SCH MTTF [@15.0.69155236@]540

5498 rows × 7 columns


In [55]:
bus_freq = trips.groupby(['route_id']).count()['trip_id'].to_dict()
route_color=[]
for i in bus_freq.keys():
    route_color.append('#'+routes.loc[routes.route_id==i,'route_color'])
    
trips.groupby(['route_id']).count()['trip_id']


Out[55]:
route_id
1 YELLOW ALT                         5
10W GOLD ALT                         1
1N YELLOW ALT                        1
1N YELLOW ALT PM                     2
1S YELLOW ALT                        4
3S LAVENDER ALT                      6
5E GREEN EXPRESS 1 ALT               3
5E GREEN EXPRESS ALT                 4
5W GREEN ALT 2                       1
5W GREEN EXPRESS 2                   4
7E GREY ALT                          4
7W GREY ALT                          1
BLUE                                69
BRONZE                              71
BRONZE ALT                           5
BROWN                               72
BROWN ALT                            6
BROWN ALT PM                         9
BROWN ALT1                           1
GOLD                                92
GOLD ALT                             9
GOLDHOPPER                          84
GREEN                              116
GREEN ALT                           23
GREEN EVENING                       37
GREEN EVENING SATURDAY              29
GREEN EXPRESS                       33
GREEN EXPRESS ALT                    3
GREEN LATE NIGHT                    38
GREEN LATE NIGHT SATURDAY           27
                                  ... 
RUBY SATURDAY                       27
RUBY SUNDAY                         21
SILVER                             333
SILVER EVENING                      56
SILVER EVENING SATURDAY             61
SILVER EVENING SUNDAY               49
SILVER LATE NIGHT                   48
SILVER LIMITED EVENING              32
SILVER LIMITED EVENING SATURDAY     32
SILVER LIMITED SATURDAY             69
SILVER LIMITED SUNDAY               51
SILVER SATURDAY                     68
SILVER SUNDAY                       59
TEAL                               238
TEAL EVENING                        76
TEAL EVENING SATURDAY               60
TEAL LATE NIGHT                     24
TEAL LATE NIGHT SATURDAY            16
TEAL LATE NIGHT SUNDAY              54
TEAL SATURDAY                      222
TEAL SUNDAY                        108
YELLOW                             122
YELLOW EVENING                      39
YELLOW EVENING SATURDAY             33
YELLOW LATE NIGHT                   19
YELLOW LATE NIGHT SATURDAY          21
YELLOW LATE NIGHT SUNDAY            34
YELLOW SATURDAY                    127
YELLOW SUNDAY                       63
YELLOWHOPPER                       141
Name: trip_id, dtype: int64

In [67]:
plt.scatter(np.arange(100),trips['route_id'].value_counts())
plt.title('Bus frequencies')
plt.ylabel("Number of buses")
plt.xlabel("Route Name")


Out[67]:
<matplotlib.text.Text at 0x119e4efd0>

In [58]:
len(trips.shape_id.unique())


Out[58]:
677

In [59]:
len(stop_times.trip_id.unique())


Out[59]:
5498

In [60]:
stops.columns


Out[60]:
Index([u'stop_id', u'stop_code', u'stop_name', u'stop_desc', u'stop_lat',
       u'stop_lon', u'zone_id', u'stop_url', u'location_type',
       u'parent_station'],
      dtype='object')

In [61]:
stop_times.groupby(['trip_id']).count()


Out[61]:
arrival_time departure_time stop_id stop_sequence stop_headsign pickup_type drop_off_type
trip_id
1GN500__GN1_MF 42 42 42 42 1 42 42
1GN507__GN1_MF 89 89 89 89 1 89 89
1GN508__GN1_MF 94 94 94 94 1 94 94
1GN509__GN1_MF 89 89 89 89 1 89 89
1GN510__GN1_MF 96 96 96 96 1 96 96
1GN511__GN1_MF 88 88 88 88 1 88 88
1GN512__GN1_MF 96 96 96 96 1 96 96
1GN513__GN1_MF 88 88 88 88 1 88 88
1GN514__GN1_MF 18 18 18 18 0 18 18
1GN515__GN1_MF 81 81 81 81 3 81 81
1GN516__GN1_MF 47 47 47 47 1 47 47
1GN517__GN1_MF 63 63 63 63 1 63 63
1GNX100__Y4_MF 70 70 70 70 1 70 70
1GNX102__GNX1_NOSCHMF 70 70 70 70 1 70 70
1GNX102__GNX1_SCHMF 70 70 70 70 1 70 70
20RED001__R1SA 41 41 41 41 1 41 41
20RED002__R1SA 67 67 67 67 1 67 67
20RED003__R1SA 73 73 73 73 1 73 73
20RED004__R1SA 67 67 67 67 1 67 67
20RED005__R1SA 73 73 73 73 1 73 73
20RED006__R1SA 67 67 67 67 1 67 67
20RED007__R1SA 73 73 73 73 1 73 73
20RED008__R1SA 67 67 67 67 1 67 67
20RED009__R1SA 73 73 73 73 1 73 73
20RED010__R1SA 67 67 67 67 1 67 67
20RED011__R1SA 73 73 73 73 1 73 73
20RED012__R1SA 67 67 67 67 1 67 67
20RED013__R1SA 73 73 73 73 1 73 73
20RED014__R1SA 67 67 67 67 1 67 67
20RED015__R1SA 73 73 73 73 1 73 73
... ... ... ... ... ... ... ...
[@7.0.41987615@][4][1246632678125]/27__G6_MF 16 16 16 16 0 16 16
[@7.0.41987615@][4][1248899521703]/0__G2_MF 16 16 16 16 0 16 16
[@7.0.41987615@][4][1248901410593]/0__G6_MF 16 16 16 16 0 16 16
[@7.0.41987615@][4][1248903846671]/0__G3_MF 16 16 16 16 0 16 16
[@7.0.42019048@][1245177724421]/4__GN3NONUISA 59 59 59 59 1 59 59
[@7.0.42019048@][1245177724421]/4__GN3UISA 59 59 59 59 1 59 59
[@7.0.42019048@][1245177724421]/5__GR2SA 59 59 59 59 1 59 59
[@7.0.42019048@][1245177724421]/6__GN1SA 59 59 59 59 1 59 59
[@7.0.42019048@][1245177724421]/71__GN1SA 55 55 55 55 1 55 55
[@7.0.42019048@][1245177724421]/72__GR2SA 55 55 55 55 1 55 55
[@7.0.42019048@][1245177724421]/73__GN2NONUISA 55 55 55 55 1 55 55
[@7.0.42019048@][1245177724421]/73__GN2UISA 55 55 55 55 1 55 55
[@7.0.42019048@][1245177724421]/74__GR4SA 55 55 55 55 1 55 55
[@7.0.42019048@][1245177724421]/75__GN5SA 55 55 55 55 1 55 55
[@7.0.42019048@][1245177724421]/76__GN3NONUISA 55 55 55 55 1 55 55
[@7.0.42019048@][1245177724421]/76__GN3UISA 55 55 55 55 1 55 55
[@7.0.42019048@][1245177724421]/77__GN2NONUISA 55 55 55 55 1 55 55
[@7.0.42019048@][1245177724421]/77__GN2UISA 55 55 55 55 1 55 55
[@7.0.42019048@][1245177724421]/78__GN1SA 55 55 55 55 1 55 55
[@7.0.42019048@][1245177724421]/7__GN1SA 36 36 36 36 1 36 36
[@7.0.42019048@][3][1256063201375]/0__GN1SA 38 38 38 38 1 38 38
[@7.0.42019048@][4][1256062571281]/0__GN2NONUISA 59 59 59 59 1 59 59
[@7.0.42019048@][4][1256062571281]/0__GN2UISA 59 59 59 59 1 59 59
[@7.0.42019048@][4][1256062611953]/1__GR4SA 59 59 59 59 1 59 59
[@7.0.42019048@][4][1256062611953]/2__GN5SA 59 59 59 59 1 59 59
[@7.0.42019048@][4][1256062611953]/3__GN2NONUISA 36 36 36 36 1 36 36
[@7.0.42019048@][4][1256062611953]/3__GN2UISA 36 36 36 36 1 36 36
[@7.0.42019048@][4][1256062670781]/4__GN5SA 43 43 43 43 1 43 43
[@7.0.42019048@][4][1256063002359]/0__GN2NONUISA 20 20 20 20 1 20 20
[@7.0.42019048@][4][1256063002359]/0__GN2UISA 20 20 20 20 1 20 20

5498 rows × 7 columns


In [62]:
stop_times['trip_id'].value_counts()


Out[62]:
[@14.0.56288404@][41][1316446361177]/2__R2_MF          147
[@14.0.56288404@][41][1316446240536]/1__R1_MF          147
[@15.0.61662606@][20][1329164411769]/27__BB2_MF        142
[@14.0.56288404@][2][1303315931316]/9__R1_MF           139
[@14.0.56288404@][2][1303313142824]/8__R5_MF           139
[@14.0.56288404@][1][1302709598751]/2__R4_MF           139
[@14.0.56288404@][1][1302710871468]/7__R2_MF           139
[@14.0.56288404@][1][1302710871468]/6__R1_MF           138
[@14.0.56288404@][1][1302710188407]/3__R5_MF           138
[@14.0.56288404@][1][1302710320617]/5__R5_MF           138
[@14.0.56288404@][1][1302702394200]/1__R3_MF           138
[@15.0.61662606@][20][1329164073841]/1__BB2_MF         133
[@14.0.56288404@][2][1303306853233]/3__R2_MF           133
[@15.0.61662606@][20][1329164126655]/4__BB1_MF         133
[@15.0.61662606@][20][1329164155233]/6__BB1_MF         133
[@14.0.56288404@][2][1304020391461]/0__R3_MF           133
[@14.0.56288404@][2][1303306853233]/2__R1_MF           133
[@2.0.85633657@][32][1463429698711]/11__GN1_SU         132
[@15.0.73008535@][32][1401899327184]/44__GN3NONUISA    132
[@15.0.73008535@][32][1401899327184]/41__GR4SA         132
[@2.0.85633657@][32][1463429698711]/6__GN4_SU          132
[@124.0.92319406@][1484577670364]/33__GN4SA            132
[@15.0.73008535@][32][1401899327184]/31__GR1SA         132
[@2.0.85633657@][32][1463429698711]/4__GN2_SU          132
[@15.0.73008535@][32][1401899327184]/25__GR4SA_EVE     132
[@15.0.73008535@][32][1401899327184]/39__GR1SA_EVE     132
[@2.0.85633657@][32][1463429698711]/5__GN3_SU          132
[@2.0.80548152@][12][1425572286750]/20__BB2_MF         132
[@2.0.80548152@][12][1425572286750]/21__BB1_MF         132
[@15.0.73008535@][32][1401899327184]/40__GR3_SA_EVE    132
                                                      ... 
[@15.0.63192640@][1][1344885900558]/0__I6-4_UIMTH        2
[@15.0.63192640@][2][1344884575709]/3__I7-2_UIF          2
[@14.0.51709480@][2][1277756843828]/0__SV3_UIMTH         2
[@2.0.85635427@][31][1465336017002]/0__BZ2_SCHWED        2
[@14.0.56288722@][1][1310478786521]/0__Y5_MF             2
[@124.0.92236898@][1484325332995]/2__Y5UISU              2
[@15.0.73008535@][32][1403817444144]/2__GR4SA_EVE        2
[@12.0.44194660@][1250946648828]/265__T1NONUISA_EVE      2
[@15.0.63192124@][1][1342109289752]/4__Y2NONUISA         2
[@14.0.56288722@][2][1305735542326]/0__Y1UIMF            2
[@15.0.61662606@][10][1329152261282]/39__BA2_SCHMF       2
[@15.0.68512430@][4][1373388704601]/0__GR1_SCHMF         2
[@15.0.74000704@][1402672352563]/3__LM2SU                2
[@124.0.92263401@][1484328944432]/0__I4UISU              2
[@2.0.86175868@][1458585713139]/111__O4_MF               2
[@14.0.56288268@][3][1305742830052]/0__B2_MF             2
[@2.0.86175868@][1458585713139]/109__O1_MF               2
GN3SATPO__GN2NONUISA_EVE                                 2
[@12.0.44194660@][1250946648828]/265__T1UISA             2
[@7.0.41200832@][1][1248458424140]/4__I5_UIMTH           2
[@15.0.60391723@][3][1324305983536]/0__I4_UIMF           2
[@124.0.92263401@][1484328944432]/186__I5UISU            2
[@12.0.44202283@][1250947454031]/1__I1UISA               2
[@15.0.73009433@][12][1402685655732]/0__LM2SA_EVE        2
[@2.0.84876422@][1455575336053]/60__3N_SHOW#1            2
[@15.0.63192662@][2][1343847939085]/0__Y3UIMF            2
[@124.0.92260187@][1484328831455]/10__I4UISA             2
[@124.0.92234822@][1484324980985]/0__RUBY1SU             2
[@15.0.60391334@][10][1321368364859]/0__LM1_MF           2
[@15.0.73008535@][32][1403817444144]/2__GR4SA            2
Name: trip_id, dtype: int64

In [63]:
stop_times.shape


Out[63]:
(242858, 8)

In [69]:
plt.plot(most_freq, trips['route_id'].value_counts()[0:12],)
plt.title("Correlation between high freq bus stops and most frequent buses")
plt.xlabel("Frequency at bus stops")
plt.ylabel("Number of buses")
# Here we can see that the correlation betweeen the freq of bus stops and the number of buses at each route is 
# linearly correlated, i.e. both are in increasing order.


Out[69]:
<matplotlib.text.Text at 0x12243bc50>

In [ ]: