Tuesday, July 23, 2013

Combining VTA Light Rail or Bus Schedules into One Personalized Schedule

On the Santa Clara County's Valley Transit Agency (VTA) website, schedules are available in text format. The problem is that I want one schedule that shows me when the next light rail train is coming at a station I am going to depart from, but the VTA site doesn't provide such a schedule. You might want a single schedule to show you when the next bus is coming for a stop that serves multiple bus lines. So here below are some rough instructions as to what I do to combine two schedules into a single schedule, two times (one for northbound, and one for southbound). If you have any questions about the process, post a comment and hopefully I'll have some time to make the instructions clearer.

  1. Create a new Excel workbook.
  2. Save each text schedule to desktop. I'm only interested in weekday schedules for light rail trains which stop in downtown San Jose, so these are all of the current links.
    1. http://www.vta.org/schedules/tdl/SC_901NO_WK.tdl
    2. http://www.vta.org/schedules/tdl/SC_901SO_WK.tdl
    3. http://www.vta.org/schedules/SC_902NO_WK.html
    4. http://www.vta.org/schedules/SC_902SO_WK.html
  3. Import each text file into a separate worksheet in Excel, specifying tab as the delimiter.
  4. Do the following find/replace operations, so that Excel will suddenly "know" that the imported time values are time values, which is important to make sorting times easy.
    1. Do a find/replace operation for all worksheets to replace "A" with " AM" (excluding quotes and including a space before AM).
    2. Do a find/replace operation for all worksheets to replace "P" with " PM" (excluding quotes and including a space before AM).
  5. Create a new worksheet for all northbound trains.
  6. Copy and paste both northbound schedules to the new northbound worksheet.
  7. Delete cells which are for stations that are not of interest.
  8. Align cells for the two trains so that the stations that are shared are in the same column, and stations that are not shared are in separate columns.
  9. Sort the new combined schedule by one common station in ascending order.
  10. Cut any trains after midnight from top of schedule then insert cut cells to bottom of schedule.
  11. Delete any trains that only serve one station after deleting stations that are not of interest.
  12. Repeat steps 5 through 11 for southbound trains.

No comments: