notebook.community

Edit and run

Tram Boarding and Alighting Data Cleaner

Data Source

https://www.data.vic.gov.au/data/dataset/tram-boardings-and-alightings-at-tram-stops-2015

Data Coverage period 01/01/2011 to 31/12/2011 (7:00am to 7:00pm weighted observations)

Data faults and an assumption that is made to work around the fault

Data appears to have a the missing column 'Day Type' column, a categorical variable classifying boarding alighting result as either "Weekday", "Saturday" or "Sunday". This column is mentioned in the data definition but missing from the data. Most of the tram boarding / alighting data appears to be grouped into collections of three rows where the first nine columns are identical. Data that does not match this pattern has only a single row.

It is assumed that the first instance of a row with nine identical columns is "Weekday",
The second and third (when they exist) are assumed to be "Saturday" and "Sunday" respectively.

For the purposes of comparison with bus boarding alighting data, only Weekday columns are of interest.

Step 1: Download raw tram boarding data, save a local copy in ./raw directory

Download Tram boardings and alightings xls file manually. The web page has a 'I consent to terms and conditions / I am not a robot' button that prevents automated downloading (or at least makes it harder than I expected). Save file to './raw' directory





In [42]:



    


import pandas as pd



    











In [43]:



    


rawtram = './raw/Tram Boardings and Alightings 2011 - data.XLS'



    











In [44]:



    


df = pd.read_excel(rawtram,sheetname='Data', header=0,converters={'Route_Number':str,'Tram_Tracker_ID':str, 'Metlink_Stop_ID':str, 'VicgridX':str, 'VicgridY':str})
df



    


















    
Out[44]:










  
    

      
      Route_Number
      Route_Name
      Direction
      Tram_Stop_Name
      Stop_Sequence
      Tram_Tracker_ID
      Metlink_Stop_ID
      VicgridX
      VicgridY
      Boardings
      Alightings
    

  
  
    

      0
      75
      City(La Trobe/Spencer St) to Vermont South
      Down
      119-La Trobe St/Spencer St (Melbourne City)
      0000
      3251
      18191
      2495721.632
      2409768.571
      46.736461
      NaN
    

    

      1
      75
      City(La Trobe/Spencer St) to Vermont South
      Down
      119-La Trobe St/Spencer St (Melbourne City)
      0000
      3251
      18191
      2495721.632
      2409768.571
      137.093617
      NaN
    

    

      2
      75
      City(La Trobe/Spencer St) to Vermont South
      Down
      119-La Trobe St/Spencer St (Melbourne City)
      0000
      3251
      18191
      2495721.632
      2409768.571
      186.945841
      NaN
    

    

      3
      1
      East Coburg to South Melbourne Beach
      Up
      135-Bell St/Nicholson St (Coburg)
      0001
      1201
      19368
      2498080.919
      2417568.07
      216.671173
      NaN
    

    

      4
      1
      East Coburg to South Melbourne Beach
      Up
      135-Bell St/Nicholson St (Coburg)
      0001
      1201
      19368
      2498080.919
      2417568.07
      247.140552
      NaN
    

    

      5
      1
      East Coburg to South Melbourne Beach
      Up
      135-Bell St/Nicholson St (Coburg)
      0001
      1201
      19368
      2498080.919
      2417568.07
      179.430811
      NaN
    

    

      6
      1
      South Melbourne Beach to Coburg
      Down
      32-Beaconsfield Pde/Victoria Ave (Albert Park)
      0001
      1242
      18663
      2495481.536
      2405982.644
      111.817795
      NaN
    

    

      7
      1
      South Melbourne Beach to Coburg
      Down
      32-Beaconsfield Pde/Victoria Ave (Albert Park)
      0001
      1242
      18663
      2495481.536
      2405982.644
      131.550350
      NaN
    

    

      8
      1
      South Melbourne Beach to Coburg
      Down
      32-Beaconsfield Pde/Victoria Ave (Albert Park)
      0001
      1242
      18663
      2495481.536
      2405982.644
      49.331382
      NaN
    

    

      9
      109
      Box Hill to Port Melbourne
      Up
      58-Box Hill Central/Whitehorse Rd (Box Hill)
      0001
      1758
      19781
      2510772.189
      2409231.223
      646.567543
      NaN
    

    

      10
      109
      Box Hill to Port Melbourne
      Up
      58-Box Hill Central/Whitehorse Rd (Box Hill)
      0001
      1758
      19781
      2510772.189
      2409231.223
      706.304779
      NaN
    

    

      11
      109
      Box Hill to Port Melbourne
      Up
      58-Box Hill Central/Whitehorse Rd (Box Hill)
      0001
      1758
      19781
      2510772.189
      2409231.223
      470.869858
      NaN
    

    

      12
      109
      Port Melbourne to Box Hill
      Down
      129-Beacon Cove/Light Rail (Port Melbourne)
      0001
      1712
      19725
      2494092.313
      2406696.324
      604.740179
      NaN
    

    

      13
      109
      Port Melbourne to Box Hill
      Down
      129-Beacon Cove/Light Rail (Port Melbourne)
      0001
      1712
      19725
      2494092.313
      2406696.324
      513.112875
      NaN
    

    

      14
      109
      Port Melbourne to Box Hill
      Down
      129-Beacon Cove/Light Rail (Port Melbourne)
      0001
      1712
      19725
      2494092.313
      2406696.324
      168.594229
      NaN
    

    

      15
      11
      Preston to Victoria Harbour (Docklands)
      Up
      47-West Preston/Gilbert Rd (Preston)
      0001
      1387
      4484
      2499258.586
      2419082.075
      8.463717
      NaN
    

    

      16
      11
      Victoria Harbour (Docklands) to Preston
      Down
      D15-Batmans Hill Dr/Collins St (Docklands)
      0001
      3399
      43476
      2495639.091
      2408996.056
      369.985352
      NaN
    

    

      17
      112
      Preston to St Kilda
      Up
      47-West Preston/Gilbert Rd (Preston)
      0001
      1387
      4484
      2499258.586
      2419082.075
      401.044179
      NaN
    

    

      18
      112
      Preston to St Kilda
      Up
      47-West Preston/Gilbert Rd (Preston)
      0001
      1387
      4484
      2499258.586
      2419082.075
      170.936866
      NaN
    

    

      19
      112
      Preston to St Kilda
      Up
      47-West Preston/Gilbert Rd (Preston)
      0001
      1387
      4484
      2499258.586
      2419082.075
      98.617422
      NaN
    

    

      20
      112
      St Kilda to Preston
      Down
      143-Fitzroy St/Park St (St Kilda)
      0001
      1410
      4135
      2497740.739
      2404459.121
      125.255468
      NaN
    

    

      21
      112
      St Kilda to Preston
      Down
      143-Fitzroy St/Park St (St Kilda)
      0001
      1410
      4135
      2497740.739
      2404459.121
      215.182466
      NaN
    

    

      22
      112
      St Kilda to Preston
      Down
      143-Fitzroy St/Park St (St Kilda)
      0001
      1410
      4135
      2497740.739
      2404459.121
      179.853998
      NaN
    

    

      23
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      80-Cotham Rd/Glenferrie Rd (Kew)
      0001
      1480
      20595
      2503355.565
      2410342.906
      222.054297
      NaN
    

    

      24
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      80-Cotham Rd/Glenferrie Rd (Kew)
      0001
      1480
      20595
      2503355.565
      2410342.906
      322.988079
      NaN
    

    

      25
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      80-Cotham Rd/Glenferrie Rd (Kew)
      0001
      1480
      20595
      2503355.565
      2410342.906
      356.632666
      NaN
    

    

      26
      16
      Melbourne University to Kew via St Kilda Beach
      Up
      1-University of Melbourne/Swanston St (Carlton)
      0001
      3001
      19489
      2496866.285
      2411423.337
      73.176990
      NaN
    

    

      27
      16
      Melbourne University to Kew via St Kilda Beach
      Up
      1-University of Melbourne/Swanston St (Carlton)
      0001
      3001
      19489
      2496866.285
      2411423.337
      384.179187
      NaN
    

    

      28
      16
      Melbourne University to Kew via St Kilda Beach
      Up
      1-University of Melbourne/Swanston St (Carlton)
      0001
      3001
      19489
      2496866.285
      2411423.337
      771.407396
      NaN
    

    

      29
      19
      City(Elizabeth/Flinders st) to North Coburg
      Down
      1-Flinders Street Railway Station/Elizabeth St...
      0001
      3801
      17850
      2496909.194
      2409219.817
      903.142006
      NaN
    

    

      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
    

    

      8008
      112
      Preston to St Kilda
      Up
      143-Fitzroy St/Park St (St Kilda)
      0080
      1410
      4135
      2497740.739
      2404459.121
      NaN
      75.606691
    

    

      8009
      112
      Preston to St Kilda
      Up
      143-Fitzroy St/Park St (St Kilda)
      0080
      1410
      4135
      2497740.739
      2404459.121
      NaN
      197.234844
    

    

      8010
      112
      Preston to St Kilda
      Up
      143-Fitzroy St/Park St (St Kilda)
      0080
      1410
      4135
      2497740.739
      2404459.121
      NaN
      315.575754
    

    

      8011
      112
      St Kilda to Preston
      Down
      46-McNamara St/Gilbert Rd (Preston)
      0080
      2385
      4173
      2499232.682
      2418951.112
      NaN
      NaN
    

    

      8012
      112
      St Kilda to Preston
      Down
      46-McNamara St/Gilbert Rd (Preston)
      0080
      2385
      4173
      2499232.682
      2418951.112
      NaN
      28.905107
    

    

      8013
      112
      St Kilda to Preston
      Down
      46-McNamara St/Gilbert Rd (Preston)
      0080
      2385
      4173
      2499232.682
      2418951.112
      NaN
      38.540142
    

    

      8014
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      6-Victoria St/Swanston St (Melbourne City)
      0080
      3106
      19692
      2496726.631
      2410466.356
      NaN
      23.551214
    

    

      8015
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      6-Victoria St/Swanston St (Melbourne City)
      0080
      3106
      19692
      2496726.631
      2410466.356
      43.737971
      74.018100
    

    

      8016
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      6-Victoria St/Swanston St (Melbourne City)
      0080
      3106
      19692
      2496726.631
      2410466.356
      10.093378
      50.466888
    

    

      8017
      16
      Melbourne University to Kew via St Kilda Beach
      Up
      79-Wellington St/Glenferrie Rd (Hawthorn)
      0080
      2479
      20592
      2503330.206
      2410123.162
      NaN
      21.343288
    

    

      8018
      16
      Melbourne University to Kew via St Kilda Beach
      Up
      79-Wellington St/Glenferrie Rd (Hawthorn)
      0080
      2479
      20592
      2503330.206
      2410123.162
      3.049041
      64.029866
    

    

      8019
      16
      Melbourne University to Kew via St Kilda Beach
      Up
      79-Wellington St/Glenferrie Rd (Hawthorn)
      0080
      2479
      20592
      2503330.206
      2410123.162
      NaN
      70.127948
    

    

      8020
      112
      St Kilda to Preston
      Down
      47-West Preston/Gilbert Rd (Preston)
      0081
      1387
      4484
      2499258.586
      2419082.075
      NaN
      16.058393
    

    

      8021
      112
      St Kilda to Preston
      Down
      47-West Preston/Gilbert Rd (Preston)
      0081
      1387
      4484
      2499258.586
      2419082.075
      NaN
      224.817498
    

    

      8022
      112
      St Kilda to Preston
      Down
      47-West Preston/Gilbert Rd (Preston)
      0081
      1387
      4484
      2499258.586
      2419082.075
      NaN
      186.277352
    

    

      8023
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      4-Queensberry St/Swanston St (Carlton)
      0081
      3104
      19693
      2496758.87
      2410668.475
      3.364459
      164.858494
    

    

      8024
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      4-Queensberry St/Swanston St (Carlton)
      0081
      3104
      19693
      2496758.87
      2410668.475
      13.457836
      87.475938
    

    

      8025
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      4-Queensberry St/Swanston St (Carlton)
      0081
      3104
      19693
      2496758.87
      2410668.475
      23.551215
      84.111480
    

    

      8026
      16
      Melbourne University to Kew via St Kilda Beach
      Up
      80-Cotham Rd/Glenferrie Rd (Kew)
      0081
      2480
      20594
      2503355.829
      2410342.795
      NaN
      82.324110
    

    

      8027
      16
      Melbourne University to Kew via St Kilda Beach
      Up
      80-Cotham Rd/Glenferrie Rd (Kew)
      0081
      2480
      20594
      2503355.829
      2410342.795
      NaN
      423.816726
    

    

      8028
      16
      Melbourne University to Kew via St Kilda Beach
      Up
      80-Cotham Rd/Glenferrie Rd (Kew)
      0081
      2480
      20594
      2503355.829
      2410342.795
      NaN
      524.435081
    

    

      8029
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      3-Lincoln Square/Swanston St (Carlton)
      0082
      3103
      19694
      2496790.912
      2410930.528
      NaN
      50.466884
    

    

      8030
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      3-Lincoln Square/Swanston St (Carlton)
      0082
      3103
      19694
      2496790.912
      2410930.528
      3.364459
      127.849447
    

    

      8031
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      3-Lincoln Square/Swanston St (Carlton)
      0082
      3103
      19694
      2496790.912
      2410930.528
      10.093378
      63.924724
    

    

      8032
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      2-Grattan St/Swanston St (Carlton)
      0083
      3102
      19695
      2496821.825
      2411155.065
      NaN
      57.195802
    

    

      8033
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      2-Grattan St/Swanston St (Carlton)
      0083
      3102
      19695
      2496821.825
      2411155.065
      NaN
      141.307288
    

    

      8034
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      2-Grattan St/Swanston St (Carlton)
      0083
      3102
      19695
      2496821.825
      2411155.065
      NaN
      60.560263
    

    

      8035
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      1-University of Melbourne/Swanston St (Carlton)
      0084
      3101
      19696
      2496850.891
      2411375.496
      NaN
      124.484987
    

    

      8036
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      1-University of Melbourne/Swanston St (Carlton)
      0084
      3101
      19696
      2496850.891
      2411375.496
      NaN
      508.033325
    

    

      8037
      16
      Kew to Melbourne University via St Kilda Beach
      Down
      1-University of Melbourne/Swanston St (Carlton)
      0084
      3101
      19696
      2496850.891
      2411375.496
      NaN
      417.192941
    

  



8038 rows × 11 columns

Step 2: Subset out the weekday data:

The first nine columns (the non - 'boarding, alighting' values) are repeated three times. This appears to be a fault relating to missing 'Day Type' column. The first instance is assumed to be the "weekday" total (so comparable to the bus data.





In [46]:



    


bystop = df.groupby('Metlink_Stop_ID').first()

Step 3: Create a .csv file with boarding and alighting data for each stop

This script groups all the reported tram boardings and alightings for a given stop If multiple routes use the same stop the results from multiple routes will be combined into a single "boarding" value and a single "alighting" value.

Results are saved as

'./clean/TramStopTraffic.csv'





In [47]:



    


# Group by stop, populate NA fields with zeros
bystop['wk7am7pm'] = bystop.Boardings.fillna(0) + bystop.Alightings.fillna(0)
# Write to CSV file
bystop.to_csv('./clean/TramStopTraffic.csv')
bystop



    


















    
Out[47]:










  
    

      
      Route_Number
      Route_Name
      Direction
      Tram_Stop_Name
      Stop_Sequence
      Tram_Tracker_ID
      VicgridX
      VicgridY
      Boardings
      Alightings
      wk7am7pm
    

    

      Metlink_Stop_ID
      
      
      
      
      
      
      
      
      
      
      
    

  
  
    

      10311
      5
      Malvern to Melbourne University
      Down
      45-Glenferrie Rd/Wattletree Rd (Malvern)
      0008
      1140
      2502480.523
      2404308.224
      43.582648
      26.820091
      70.402740
    

    

      10371
      5
      Malvern to Melbourne University
      Down
      44-Duncraig Ave/Wattletree Rd (Armadale)
      0009
      1139
      2502207.668
      2404352.585
      13.410046
      3.352511
      16.762557
    

    

      1083
      57
      West Maribyrnong to City(Elizabeth/Flinders st)
      Up
      42-Clyde St/Raleigh Rd (Maribyrnong)
      0007
      1342
      2491088.237
      2414580.621
      3.476170
      3.476170
      6.952339
    

    

      11285
      5
      Malvern to Melbourne University
      Down
      43-Egerton Rd/Wattletree Rd (Armadale)
      0010
      1138
      2501997.46
      2404386.49
      23.467580
      3.352511
      26.820091
    

    

      1185
      5
      Melbourne University to Malvern
      Up
      50-Vincent St/Wattletree Rd (Malvern East)
      0046
      2145
      2503846.068
      2404075.296
      6.595105
      9.892657
      16.487762
    

    

      12599
      5
      Malvern to Melbourne University
      Down
      42-Kooyong Rd/Wattletree Rd (Armadale)
      0011
      1137
      2501726.184
      2404431.501
      33.525114
      16.762557
      50.287671
    

    

      12777
      19
      City(Elizabeth/Flinders st) to North Coburg
      Down
      35-O'Hea St/Sydney Rd (Coburg)
      0035
      2435
      2497083.422
      2418119.817
      NaN
      7.139462
      7.139462
    

    

      1291
      5
      Melbourne University to Malvern
      Up
      51-Erica Ave/Wattletree Rd (Malvern East)
      0047
      2146
      2504135.271
      2404031.317
      NaN
      36.273076
      36.273076
    

    

      1292
      19
      City(Elizabeth/Flinders st) to North Coburg
      Down
      38-Renown St/Sydney Rd (Coburg)
      0038
      2438
      2496849.685
      2418811.05
      7.139462
      14.278925
      21.418387
    

    

      13401
      6
      Melbourne University to Glen Iris
      Up
      28-Punt Rd/High St (Prahran)
      0025
      2150
      2498624.723
      2405588.851
      13.037279
      35.852518
      48.889797
    

    

      13995
      57
      West Maribyrnong to City(Elizabeth/Flinders st)
      Up
      44-Barb St/Raleigh Rd (Maribyrnong)
      0005
      1344
      2490483.235
      2414669.299
      100.808918
      8.463717
      109.272635
    

    

      14033
      6
      Melbourne University to Glen Iris
      Up
      29-Perth St/High St (Prahran)
      0026
      2151
      2498937.592
      2405538.618
      9.777959
      29.333879
      39.111838
    

    

      14614
      6
      Melbourne University to Glen Iris
      Up
      30-Porter St/High St (Prahran)
      0027
      2152
      2499058.515
      2405518.987
      32.593198
      16.296599
      48.889797
    

    

      14728
      6
      Melbourne University to Glen Iris
      Up
      31-Chapel St/High St (Prahran)
      0028
      2153
      2499431.047
      2405456.867
      32.593199
      48.889797
      81.482996
    

    

      15559
      57
      West Maribyrnong to City(Elizabeth/Flinders st)
      Up
      45-Randall St/Raleigh Rd (Maribyrnong)
      0004
      1345
      2490215.107
      2414698.291
      55.618713
      8.463717
      64.082431
    

    

      15699
      6
      Melbourne University to Glen Iris
      Up
      32-Bangs St/High St (Prahran)
      0029
      2154
      2499694.712
      2405411.93
      29.333879
      9.777959
      39.111838
    

    

      1575
      5
      Malvern to Melbourne University
      Down
      52-Burke Rd/Wattletree Rd (Malvern East)
      0001
      1147
      2504405.559
      2403998.66
      77.107762
      52.760836
      129.868599
    

    

      16610
      6
      Melbourne University to Glen Iris
      Up
      33-The Avenue/High St (Prahran)
      0030
      2155
      2499877.322
      2405382.079
      16.296599
      19.555919
      35.852519
    

    

      16713
      19
      City(Elizabeth/Flinders st) to North Coburg
      Down
      34-Bell St/Sydney Rd (Coburg)
      0034
      2434
      2497044.96
      2417717.487
      35.697312
      67.824893
      103.522205
    

    

      16714
      19
      City(Elizabeth/Flinders st) to North Coburg
      Down
      33-Coburg Market/Sydney Rd (Coburg)
      0033
      2433
      2497016.915
      2417519.592
      17.848656
      67.824893
      85.673549
    

    

      16715
      19
      City(Elizabeth/Flinders st) to North Coburg
      Down
      32-Munro St/Sydney Rd (Coburg)
      0032
      2432
      2496988.961
      2417318.923
      10.709194
      42.836774
      53.545968
    

    

      16716
      19
      City(Elizabeth/Flinders st) to North Coburg
      Down
      31-Reynard St/Sydney Rd (Coburg)
      0031
      2431
      2496933.941
      2416911.48
      10.709194
      10.709194
      21.418387
    

    

      16717
      19
      City(Elizabeth/Flinders st) to North Coburg
      Down
      30-The Avenue/Sydney Rd (Coburg)
      0030
      2430
      2496883.847
      2416541.328
      7.139462
      24.988119
      32.127581
    

    

      16718
      6
      Melbourne University to Glen Iris
      Up
      34-Lewisham Rd/High St (Prahran)
      0031
      2156
      2500009.504
      2405358.883
      6.518640
      3.259320
      9.777959
    

    

      16719
      19
      City(Elizabeth/Flinders st) to North Coburg
      Down
      28-Moreland Rd/Sydney Rd (Brunswick)
      0028
      2428
      2496829.185
      2416145.537
      46.406506
      49.976237
      96.382743
    

    

      16720
      19
      City(Elizabeth/Flinders st) to North Coburg
      Down
      27-Brunswick Tram Depot/Sydney Rd (Brunswick)
      0027
      2427
      2496795.881
      2415904.133
      7.139462
      17.848656
      24.988119
    

    

      16721
      19
      City(Elizabeth/Flinders st) to North Coburg
      Down
      26-Albion St/Sydney Rd (Brunswick)
      0026
      2426
      2496747.906
      2415554.401
      46.406506
      42.836775
      89.243280
    

    

      16722
      19
      City(Elizabeth/Flinders st) to North Coburg
      Down
      25-Stewart St/Sydney Rd (Brunswick)
      0025
      2425
      2496713.377
      2415302.452
      14.278925
      35.697312
      49.976237
    

    

      16723
      19
      City(Elizabeth/Flinders st) to North Coburg
      Down
      24-Brunswick Baptist Church/Sydney Rd (Brunswick)
      0024
      2424
      2496678.765
      2415041.846
      17.848656
      10.709194
      28.557850
    

    

      16724
      19
      City(Elizabeth/Flinders st) to North Coburg
      Down
      23-Victoria St/Sydney Rd (Brunswick)
      0023
      2423
      2496654.87
      2414868.588
      10.709194
      24.988119
      35.697312
    

    

      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
    

    

      6129
      86
      Bundoora to City (Docklands)
      Up
      16-Peel St/Smith St (Collingwood)
      0056
      1816
      2498533.802
      2410682.958
      73.095744
      24.365249
      97.460993
    

    

      6130
      86
      Bundoora to City (Docklands)
      Up
      15-Gertrude St/Smith St (Fitzroy)
      0057
      1815
      2498511.814
      2410529.792
      31.326749
      59.172747
      90.499496
    

    

      6131
      86
      Bundoora to City (Docklands)
      Up
      14-Napier St/Gertrude St (Fitzroy)
      0058
      1814
      2498222.098
      2410546.384
      66.134245
      80.057245
      146.191490
    

    

      6132
      86
      Bundoora to City (Docklands)
      Up
      13-Brunswick St/Gertrude St (Fitzroy)
      0059
      1813
      2498003.706
      2410575.191
      73.095746
      80.057246
      153.152992
    

    

      6133
      96
      St Kilda Beach to East Brunswick
      Down
      13-Hanover St/Nicholson St (Fitzroy)
      0035
      2513
      2497711.262
      2410900.308
      11.625986
      58.129926
      69.755912
    

    

      6134
      96
      St Kilda Beach to East Brunswick
      Down
      14-Murchison St/Nicholson St (Carlton)
      0036
      2514
      2497749.053
      2411183.334
      4.650394
      32.552759
      37.203153
    

    

      6135
      96
      St Kilda Beach to East Brunswick
      Down
      15-Elgin St/Nicholson St (Carlton)
      0037
      2515
      2497792.123
      2411495.218
      6.975591
      58.129928
      65.105519
    

    

      6136
      96
      St Kilda Beach to East Brunswick
      Down
      16-Kay St/Nicholson St (Carlton)
      0038
      2516
      2497826.403
      2411750.496
      25.577167
      11.625985
      37.203152
    

    

      6137
      96
      St Kilda Beach to East Brunswick
      Down
      17-Princes St/Nicholson St (Carlton)
      0039
      2517
      2497858.051
      2411975.806
      4.650394
      58.129927
      62.780321
    

    

      6138
      96
      St Kilda Beach to East Brunswick
      Down
      18-Curtain St/Nicholson St (Carlton North)
      0040
      2518
      2497915.201
      2412369.822
      2.325197
      6.975591
      9.300788
    

    

      6139
      96
      St Kilda Beach to East Brunswick
      Down
      19-Tempany St/Nicholson St (Fitzroy North)
      0041
      2519
      2497954.772
      2412630.649
      11.625985
      11.625985
      23.251970
    

    

      6140
      96
      St Kilda Beach to East Brunswick
      Down
      20-Richardson St/Nicholson St (Carlton North)
      0042
      2520
      2497987.307
      2412859.288
      2.325197
      30.227561
      32.552758
    

    

      6141
      96
      St Kilda Beach to East Brunswick
      Down
      21-Pigdon St/Nicholson St (Carlton North)
      0043
      2521
      2498028.641
      2413137.872
      11.625986
      20.926773
      32.552758
    

    

      6142
      96
      St Kilda Beach to East Brunswick
      Down
      22-Brunswick Rd/Nicholson St (Fitzroy)
      0044
      2522
      2498081.402
      2413532.994
      2.325197
      25.577168
      27.902365
    

    

      6143
      96
      St Kilda Beach to East Brunswick
      Down
      23-Miller St/Nicholson St (Brunswick East)
      0045
      2523
      2498116.568
      2413844.871
      11.625985
      9.300788
      20.926773
    

    

      6144
      96
      St Kilda Beach to East Brunswick
      Down
      24-Glenlyon Rd/Nicholson St (Brunswick East)
      0046
      2524
      2498157.023
      2414151.2
      13.951182
      6.975591
      20.926773
    

    

      6145
      96
      St Kilda Beach to East Brunswick
      Down
      25-Albert St/Nicholson St (Brunswick East)
      0047
      2525
      2498205.403
      2414493.046
      2.325197
      23.251971
      25.577168
    

    

      6146
      96
      St Kilda Beach to East Brunswick
      Down
      26-Victoria St/Nicholson St (Brunswick East)
      0048
      2526
      2498227.393
      2414660.638
      2.325197
      4.650394
      6.975591
    

    

      6147
      96
      East Brunswick to St Kilda Beach
      Up
      27-Blyth St/Nicholson St (Brunswick East)
      0001
      1527
      2498257.312
      2414832.671
      466.273895
      27.902365
      494.176260
    

    

      7354
      5
      Malvern to Melbourne University
      Down
      48-Dixon St/Wattletree Rd (Malvern)
      0005
      1143
      2503223.313
      2404185.883
      53.640182
      NaN
      53.640182
    

    

      8345
      5
      Malvern to Melbourne University
      Down
      47-Cabrini Hospital/Wattletree Rd (Malvern)
      0006
      1142
      2502942.02
      2404232.713
      20.115068
      6.705023
      26.820091
    

    

      9156
      5
      Malvern to Melbourne University
      Down
      46-Soudan St/Wattletree Rd (Malvern)
      0007
      1141
      2502723.811
      2404267.859
      10.057534
      26.820091
      36.877625
    

    

      940
      5
      Melbourne University to Malvern
      Up
      43-Egerton Rd/Wattletree Rd (Armadale)
      0039
      2138
      2501997.281
      2404374.503
      6.595105
      3.297552
      9.892657
    

    

      946
      5
      Melbourne University to Malvern
      Up
      44-Duncraig Ave/Wattletree Rd (Armadale)
      0040
      2139
      2502241.365
      2404333.819
      3.297552
      6.595105
      9.892657
    

    

      947
      5
      Melbourne University to Malvern
      Up
      45-Glenferrie Rd/Wattletree Rd (Malvern)
      0041
      2140
      2502508.675
      2404290.124
      13.190209
      26.380419
      39.570628
    

    

      950
      5
      Melbourne University to Malvern
      Up
      46-Nicholls St/Wattletree Rd (Malvern)
      0042
      2141
      2502762.169
      2404248.757
      13.190209
      19.785314
      32.975523
    

    

      955
      5
      Melbourne University to Malvern
      Up
      47-Cabrini Hospital/Wattletree Rd (Malvern)
      0043
      2142
      2502978.969
      2404212.833
      6.595105
      19.785313
      26.380418
    

    

      968
      5
      Melbourne University to Malvern
      Up
      48-Dixon St/Wattletree Rd (Malvern)
      0044
      2143
      2503263.341
      2404166
      3.297552
      13.190209
      16.487762
    

    

      980
      5
      Melbourne University to Malvern
      Up
      49-Tooronga Rd/Wattletree Rd (Malvern East)
      0045
      2144
      2503544.279
      2404120.047
      NaN
      3.297552
      3.297552
    

    

      992
      57
      West Maribyrnong to City(Elizabeth/Flinders st)
      Up
      41-Maribyrnong Park/Maribyrnong Rd (Ascot Vale)
      0008
      1341
      2491370.997
      2414538.409
      24.333187
      10.428509
      34.761696
    

  



1768 rows × 11 columns

Step 4: Map tram specific results

Use QGIS to join TramStopTraffic.csv to 'layer ptv_tram_stop' using the common column 'Metlink Stop ID'

I was not able to use the coordinates in columns 'VicgridX' and 'VicgridY' VicGrid appears to be to be a subset of GDA2020 EPSG:7899. As of August 2017 I've not been able to find support for this coordinate reference system under QGIS.

The 'layer ptv_tram_stop' uses CRS=GDA94 and includes a 'Metlink Stop ID' column.

Use Display properties to colourcode tram stops by wk7am7pm to find the busiest stop.

Data quality notes (from data vic gov au)

https://www.data.vic.gov.au/data/dataset/tram-boardings-and-alightings-at-tram-stops-2015 Data Coverage period 01/01/2011 to 31/12/2011 All data has been weighted post hoc to ensure that is representative for each route, direction and day type for the 7:00am to 7:00pm period.

This results in a sample that is representative of all scheduled trips within the survey period for a typical Weekday Saturday And Sunday This was an attempt to build a consistent picture of an average day across the network. The data collected is designed to represent a 'typical' weekday, Saturday, Sunday in the broadest sense of the word; it is not a true average created over many days. Rather it is a snapshot of a single or couple of days, with all the risks regarding seasonality that entails.





In [ ]:

Content source: FunOnTheUpfield/DataVicGovAuPTDataCleaner

Similar notebooks:

notebook.community | gallery | about