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 [ ]:
Content source: arpit-garg/project-spring2017
Similar notebooks: